summaryrefslogtreecommitdiff
path: root/LedgerSMB/IR.pm
blob: 62eea7c60dfed1ff3733507d7aaeaf52e702e8eb (plain)
  1. #=====================================================================
  2. # LedgerSMB
  3. # Small Medium Business Accounting software
  4. # http://www.ledgersmb.org/
  5. #
  6. # Copyright (C) 2006
  7. # This work contains copyrighted information from a number of sources all used
  8. # with permission.
  9. #
  10. # This file contains source code included with or based on SQL-Ledger which
  11. # is Copyright Dieter Simader and DWS Systems Inc. 2000-2005 and licensed
  12. # under the GNU General Public License version 2 or, at your option, any later
  13. # version. For a full list including contact information of contributors,
  14. # maintainers, and copyright holders, see the CONTRIBUTORS file.
  15. #
  16. # Original Copyright Notice from SQL-Ledger 2.6.17 (before the fork):
  17. # Copyright (C) 2000
  18. #
  19. # Author: DWS Systems Inc.
  20. # Web: http://www.sql-ledger.org
  21. #
  22. # Contributors: Jim Rawlings <jim@your-dba.com>
  23. #
  24. #======================================================================
  25. #
  26. # This file has undergone whitespace cleanup.
  27. #
  28. #======================================================================
  29. #
  30. # Inventory received module
  31. #
  32. #======================================================================
  33. package IR;
  34. use LedgerSMB::Tax;
  35. use LedgerSMB::PriceMatrix;
  36. use LedgerSMB::Sysconfig;
  37. use Math::BigFloat;
  38. sub post_invoice {
  39. my ( $self, $myconfig, $form ) = @_;
  40. my $dbh = $form->{dbh};
  41. $form->{invnumber} = $form->update_defaults( $myconfig, "vinumber", $dbh )
  42. unless $form->{invnumber};
  43. for ( 1 .. $form->{rowcount} ) {
  44. unless ( $form->{"deliverydate_$_"} ) {
  45. $form->{"deliverydate_$_"} = $form->{transdate};
  46. }
  47. }
  48. my $query;
  49. my $sth;
  50. my $ref;
  51. my $null;
  52. my $project_id;
  53. my $exchangerate = 0;
  54. my $allocated;
  55. my $taxrate;
  56. my $taxamount;
  57. my $diff = 0;
  58. my $item;
  59. my $invoice_id;
  60. my $keepcleared;
  61. ( $null, $form->{employee_id} ) = split /--/, $form->{employee};
  62. unless ( $form->{employee_id} ) {
  63. ( $form->{employee}, $form->{employee_id} ) = $form->get_employee($dbh);
  64. }
  65. ( $null, $form->{department_id} ) = split( /--/, $form->{department} );
  66. $form->{department_id} *= 1;
  67. $query = qq|
  68. SELECT (SELECT value FROM defaults
  69. WHERE setting_key = 'fxgain_accno_id')
  70. AS fxgain_accno_id,
  71. (SELECT value FROM defaults
  72. WHERE setting_key = 'fxloss_accno_id')
  73. AS fxloss_accno_id|;
  74. my ( $fxgain_accno_id, $fxloss_accno_id ) = $dbh->selectrow_array($query);
  75. $query = qq|
  76. SELECT inventory_accno_id, income_accno_id, expense_accno_id
  77. FROM parts
  78. WHERE id = ?|;
  79. my $pth = $dbh->prepare($query) || $form->dberror($query);
  80. my %updparts = ();
  81. if ( $form->{id} ) {
  82. my $sth;
  83. $keepcleared = 1;
  84. $query = qq|SELECT id FROM ap WHERE id = ?|;
  85. $sth = $dbh->prepare($query);
  86. $sth->execute( $form->{id} );
  87. if ( $sth->fetchrow_array ) {
  88. $query = qq|
  89. SELECT p.id, p.inventory_accno_id,
  90. p.income_accno_id
  91. FROM invoice i
  92. JOIN parts p ON (p.id = i.parts_id)
  93. WHERE i.trans_id = ?|;
  94. $sth = $dbh->prepare($query);
  95. $sth->execute( $form->{id} ) || $form->dberror($query);
  96. while ( $ref = $sth->fetchrow_hashref ) {
  97. if ( $ref->{inventory_accno_id}
  98. && $ref->{income_accno_id} )
  99. {
  100. $updparts{ $ref->{id} } = 1;
  101. }
  102. }
  103. $sth->finish;
  104. &reverse_invoice( $dbh, $form );
  105. }
  106. else {
  107. $query = qq|INSERT INTO ap (id, vendor_id) VALUES (?, ?)|;
  108. $sth = $dbh->prepare($query);
  109. $sth->execute( $form->{id}, $form->{vendor_id} )
  110. || $form->dberror($query);
  111. }
  112. }
  113. my $uid = localtime;
  114. $uid .= "$$";
  115. if ( !$form->{id} ) {
  116. $query = qq|
  117. INSERT INTO ap (invnumber, vendor_id, employee_id)
  118. VALUES ('$uid', ?, (SELECT id FROM employee
  119. WHERE login = ?))|;
  120. $sth = $dbh->prepare($query);
  121. $sth->execute( $form->{vendor_id}, $form->{login})
  122. || $form->dberror($query);
  123. $query = qq|SELECT id FROM ap WHERE invnumber = '$uid'|;
  124. $sth = $dbh->prepare($query);
  125. $sth->execute || $form->dberror($query);
  126. ( $form->{id} ) = $sth->fetchrow_array;
  127. $sth->finish;
  128. }
  129. my $amount;
  130. my $grossamount;
  131. my $allocated;
  132. my $invamount = 0;
  133. my $invnetamount = 0;
  134. if ( $form->{currency} eq $form->{defaultcurrency} ) {
  135. $form->{exchangerate} = 1;
  136. }
  137. else {
  138. $exchangerate =
  139. $form->check_exchangerate( $myconfig, $form->{currency},
  140. $form->{transdate}, 'sell' );
  141. }
  142. $form->{exchangerate} =
  143. ($exchangerate)
  144. ? $exchangerate
  145. : $form->parse_amount( $myconfig, $form->{exchangerate} );
  146. for my $i ( 1 .. $form->{rowcount} ) {
  147. $form->{"qty_$i"} = $form->parse_amount( $myconfig, $form->{"qty_$i"} );
  148. if ( $form->{"qty_$i"} ) {
  149. $pth->execute( $form->{"id_$i"} );
  150. $ref = $pth->fetchrow_hashref(NAME_lc);
  151. for ( keys %$ref ) {
  152. $form->{"${_}_$i"} = $ref->{$_};
  153. }
  154. $pth->finish;
  155. # project
  156. if ( $form->{"projectnumber_$i"} ne "" ) {
  157. ( $null, $project_id ) =
  158. split /--/, $form->{"projectnumber_$i"};
  159. }
  160. # undo discount formatting
  161. $form->{"discount_$i"} =
  162. $form->parse_amount( $myconfig, $form->{"discount_$i"} ) / 100;
  163. # keep entered selling price
  164. my $fxsellprice =
  165. $form->parse_amount( $myconfig, $form->{"sellprice_$i"} );
  166. my ($dec) = ( $fxsellprice =~ /\.(\d+)/ );
  167. $dec = length $dec;
  168. my $decimalplaces = ( $dec > 2 ) ? $dec : 2;
  169. # deduct discount
  170. $form->{"sellprice_$i"} = $fxsellprice -
  171. $form->round_amount( $fxsellprice * $form->{"discount_$i"},
  172. $decimalplaces );
  173. # linetotal
  174. my $fxlinetotal =
  175. $form->round_amount( $form->{"sellprice_$i"} * $form->{"qty_$i"},
  176. 2 );
  177. $amount = $fxlinetotal * $form->{exchangerate};
  178. my $linetotal = $form->round_amount( $amount, 2 );
  179. $fxdiff += $amount - $linetotal;
  180. @taxaccounts = Tax::init_taxes(
  181. $form,
  182. $form->{"taxaccounts_$i"},
  183. $form->{'taxaccounts'}
  184. );
  185. $tax = Math::BigFloat->bzero();
  186. $fxtax = Math::BigFloat->bzero();
  187. if ( $form->{taxincluded} ) {
  188. $tax += $amount =
  189. Tax::calculate_taxes( \@taxaccounts, $form, $linetotal, 1 );
  190. $form->{"sellprice_$i"} -= $amount / $form->{"qty_$i"};
  191. }
  192. else {
  193. $tax += $amount =
  194. Tax::calculate_taxes( \@taxaccounts, $form, $linetotal, 0 );
  195. $fxtax +=
  196. Tax::calculate_taxes( \@taxaccounts, $form, $fxlinetotal, 0 );
  197. }
  198. for (@taxaccounts) {
  199. $form->{acc_trans}{ $form->{id} }{ $_->account }{amount} +=
  200. $_->value;
  201. }
  202. $grossamount = $form->round_amount( $linetotal, 2 );
  203. if ( $form->{taxincluded} ) {
  204. $amount = $form->round_amount( $tax, 2 );
  205. $linetotal -= $form->round_amount( $tax - $diff, 2 );
  206. $diff = ( $amount - $tax );
  207. }
  208. $amount = $form->round_amount( $linetotal, 2 );
  209. $allocated = 0;
  210. # adjust and round sellprice
  211. $form->{"sellprice_$i"} =
  212. $form->round_amount(
  213. $form->{"sellprice_$i"} * $form->{exchangerate},
  214. $decimalplaces );
  215. # save detail record in invoice table
  216. $query = qq|
  217. INSERT INTO invoice (description)
  218. VALUES ('$uid')|;
  219. $dbh->do($query) || $form->dberror($query);
  220. $query = qq|
  221. SELECT id FROM invoice
  222. WHERE description = '$uid'|;
  223. ($invoice_id) = $dbh->selectrow_array($query);
  224. if ( $form->{"inventory_accno_id_$i"} ) {
  225. my $totalqty = $form->{"qty_$i"};
  226. # if this is a exit for the product
  227. if($form->{"qty_$i"}<0) {
  228. $query = qq|
  229. SELECT i.id, i.qty, i.allocated, a.transdate
  230. FROM invoice i
  231. JOIN parts p ON (p.id = i.parts_id)
  232. JOIN ap a ON (a.id = i.trans_id)
  233. WHERE i.parts_id = ? AND (i.qty + i.allocated) < 0 AND i.sellprice = ?
  234. ORDER BY transdate
  235. |;
  236. $sth = $dbh->prepare($query);
  237. $sth->execute( $form->{"id_$i"}, $form->{"sellprice_$i"}) || $form->dberror($query);
  238. my $totalqty = $form->{"qty_$i"};
  239. while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  240. $form->db_parse_numeric(sth=>$sth, hashref => $ref);
  241. my $qty = $ref->{qty} + $ref->{allocated};
  242. if ( ( $qty - $totalqty ) < 0 ) { $qty = $totalqty; }
  243. # update allocated for sold item
  244. $form->update_balance( $dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty * -1 );
  245. $allocated += $qty;
  246. last if ( ( $totalqty -= $qty ) >= 0 );
  247. }
  248. } else {
  249. # check for unallocated entries atthe same price to match our entry
  250. $query = qq|
  251. SELECT i.id, i.qty, i.allocated, a.transdate
  252. FROM invoice i
  253. JOIN parts p ON (p.id = i.parts_id)
  254. JOIN ap a ON (a.id = i.trans_id)
  255. WHERE i.parts_id = ? AND (i.qty + i.allocated) > 0 AND i.sellprice = ?
  256. ORDER BY transdate|;
  257. $sth = $dbh->prepare($query);
  258. $sth->execute( $form->{"id_$i"}, $form->{"sellprice_$i"}) || $form->dberror($query);
  259. while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  260. $form->db_parse_numeric(sth=>$sth, hashref => $ref);
  261. my $qty = $ref->{qty} + $ref->{allocated};
  262. if ( ( $qty - $totalqty ) > 0 ) { $qty = $totalqty; }
  263. # update allocated for sold item
  264. $form->update_balance( $dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty * -1 );
  265. $allocated += $qty;
  266. last if ( ( $totalqty -= $qty ) <= 0 );
  267. }
  268. }
  269. # add purchase to inventory
  270. push @{ $form->{acc_trans}{lineitems} },
  271. {
  272. chart_id => $form->{"inventory_accno_id_$i"},
  273. amount => $amount,
  274. fxgrossamount => $fxlinetotal +
  275. $form->round_amount( $fxtax, 2 ),
  276. grossamount => $grossamount,
  277. project_id => $project_id,
  278. invoice_id => $invoice_id
  279. };
  280. $updparts{ $form->{"id_$i"} } = 1;
  281. # update parts table
  282. $form->update_balance( $dbh, "parts", "onhand",
  283. qq|id = $form->{"id_$i"}|,
  284. $form->{"qty_$i"} )
  285. unless $form->{shipped};
  286. # check if we sold the item
  287. $query = qq|
  288. SELECT i.id, i.qty, i.allocated,
  289. i.trans_id, i.project_id,
  290. p.inventory_accno_id,
  291. p.expense_accno_id, a.transdate
  292. FROM invoice i
  293. JOIN parts p ON (p.id = i.parts_id)
  294. JOIN ar a ON (a.id = i.trans_id)
  295. WHERE i.parts_id = ?
  296. AND (i.qty + i.allocated) > 0
  297. ORDER BY transdate|;
  298. my $sth = $dbh->prepare($query);
  299. $sth->execute( $form->{"id_$i"} )
  300. || $form->dberror($query);
  301. while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  302. $form->db_parse_numeric(sth=>$sth, hashref => $ref);
  303. my $qty = $ref->{qty} + $ref->{allocated};
  304. if ( ( $qty - $totalqty ) > 0 ) {
  305. $qty = $totalqty;
  306. }
  307. $linetotal =
  308. $form->round_amount( $form->{"sellprice_$i"} * $qty, 2 );
  309. if ($linetotal) {
  310. $query = qq|
  311. INSERT INTO acc_trans
  312. (trans_id,
  313. chart_id,
  314. amount,
  315. project_id,
  316. invoice_id,
  317. transdate)
  318. VALUES (?, ?, ?, ?,
  319. ?, (SELECT CASE WHEN ? > value::date
  320. THEN ?
  321. ELSE value::date +
  322. '1 day'::interval
  323. END AS value
  324. FROM defaults
  325. WHERE setting_key = 'closedto'
  326. ))|;
  327. my $sth = $dbh->prepare($query);
  328. $sth->execute(
  329. $ref->{trans_id}, $ref->{inventory_accno_id},
  330. $linetotal,
  331. $ref->{project_id}, $invoice_id,
  332. $ref->{transdate}, $ref->{transdate},
  333. ) || $form->dberror($query);
  334. # add expense
  335. $query = qq|
  336. INSERT INTO acc_trans
  337. (trans_id, chart_id, amount,
  338. project_id, invoice_id,
  339. transdate)
  340. VALUES (?, ?, ?, ?,
  341. ?, (SELECT CASE WHEN ? > value::date
  342. THEN ?
  343. ELSE value::date +
  344. '1 day'::interval
  345. END AS value
  346. FROM defaults
  347. WHERE setting_key = 'closedto'
  348. ))|;
  349. $sth = $dbh->prepare($query);
  350. $sth->execute(
  351. $ref->{trans_id}, $ref->{expense_accno_id},
  352. $linetotal * -1,
  353. $ref->{project_id}, $invoice_id,
  354. $ref->{transdate}, $ref->{transdate},
  355. ) || $form->dberror($query);
  356. }
  357. # update allocated for sold item
  358. $form->update_balance( $dbh, "invoice", "allocated",
  359. qq|id = $ref->{id}|,
  360. $qty * -1 );
  361. $allocated += $qty;
  362. last if ( ( $totalqty -= $qty ) <= 0 );
  363. }
  364. $sth->finish;
  365. }
  366. else {
  367. # add purchase to expense
  368. push @{ $form->{acc_trans}{lineitems} },
  369. {
  370. chart_id => $form->{"expense_accno_id_$i"},
  371. amount => $amount,
  372. fxgrossamount => $fxlinetotal +
  373. $form->round_amount( $fxtax, 2 ),
  374. grossamount => $grossamount,
  375. project_id => $project_id,
  376. invoice_id => $invoice_id
  377. };
  378. }
  379. $query = qq|
  380. UPDATE invoice
  381. SET trans_id = ?,
  382. parts_id = ?,
  383. description = ?,
  384. qty = ?,
  385. sellprice = ?,
  386. fxsellprice = ?,
  387. discount = ?,
  388. allocated = ?,
  389. unit = ?,
  390. deliverydate = ?,
  391. project_id = ?,
  392. serialnumber = ?,
  393. notes = ?
  394. WHERE id = ?|;
  395. $sth = $dbh->prepare($query);
  396. $sth->execute(
  397. $form->{id}, $form->{"id_$i"},
  398. $form->{"description_$i"}, $form->{"qty_$i"} * -1,
  399. $form->{"sellprice_$i"}, $fxsellprice,
  400. $form->{"discount_$i"}, $allocated,
  401. $form->{"unit_$i"}, $form->{"deliverydate_$i"},
  402. $project_id, $form->{"serialnumber_$i"},
  403. $form->{"notes_$i"}, $invoice_id
  404. ) || $form->dberror($query);
  405. }
  406. }
  407. $form->{paid} = 0;
  408. for $i ( 1 .. $form->{paidaccounts} ) {
  409. $form->{"paid_$i"} =
  410. $form->parse_amount( $myconfig, $form->{"paid_$i"} );
  411. $form->{paid} += $form->{"paid_$i"};
  412. $form->{datepaid} = $form->{"datepaid_$i"}
  413. if ( $form->{"datepaid_$i"} );
  414. }
  415. # add lineitems + tax
  416. $amount = 0;
  417. $grossamount = 0;
  418. $fxgrossamount = 0;
  419. for ( @{ $form->{acc_trans}{lineitems} } ) {
  420. $amount += $_->{amount};
  421. $grossamount += $_->{grossamount};
  422. $fxgrossamount += $_->{fxgrossamount};
  423. }
  424. $invnetamount = $amount;
  425. $amount = 0;
  426. for ( split / /, $form->{taxaccounts} ) {
  427. $amount += $form->{acc_trans}{ $form->{id} }{$_}{amount} =
  428. $form->round_amount( $form->{acc_trans}{ $form->{id} }{$_}{amount},
  429. 2 );
  430. $form->{acc_trans}{ $form->{id} }{$_}{amount} *= -1;
  431. }
  432. $invamount = $invnetamount + $amount;
  433. $diff = 0;
  434. if ( $form->{taxincluded} ) {
  435. $diff = $form->round_amount( $grossamount - $invamount, 2 );
  436. $invamount += $diff;
  437. }
  438. $fxdiff = $form->round_amount( $fxdiff, 2 );
  439. $invnetamount += $fxdiff;
  440. $invamount += $fxdiff;
  441. if ( $form->round_amount( $form->{paid} - $fxgrossamount, 2 ) == 0 ) {
  442. $form->{paid} = $invamount;
  443. }
  444. else {
  445. $form->{paid} =
  446. $form->round_amount( $form->{paid} * $form->{exchangerate}, 2 );
  447. }
  448. foreach $ref ( sort { $b->{amount} <=> $a->{amount} }
  449. @{ $form->{acc_trans}{lineitems} } )
  450. {
  451. $amount = $ref->{amount} + $diff + $fxdiff;
  452. $query = qq|
  453. INSERT INTO acc_trans (trans_id, chart_id, amount,
  454. transdate, project_id, invoice_id)
  455. VALUES (?, ?, ?, ?, ?, ?)|;
  456. $sth = $dbh->prepare($query);
  457. $sth->execute(
  458. $form->{id}, $ref->{chart_id}, $amount * -1,
  459. $form->{transdate}, $ref->{project_id}, $ref->{invoice_id}
  460. ) || $form->dberror($query);
  461. $diff = 0;
  462. $fxdiff = 0;
  463. }
  464. $form->{payables} = $invamount;
  465. delete $form->{acc_trans}{lineitems};
  466. # update exchangerate
  467. if ( ( $form->{currency} ne $form->{defaultcurrency} ) && !$exchangerate ) {
  468. $form->update_exchangerate( $dbh, $form->{currency}, $form->{transdate},
  469. 0, $form->{exchangerate} );
  470. }
  471. # record payable
  472. if ( $form->{payables} ) {
  473. ($accno) = split /--/, $form->{AP};
  474. $query = qq|
  475. INSERT INTO acc_trans (trans_id, chart_id, amount,
  476. transdate)
  477. VALUES (?, (SELECT id FROM chart WHERE accno = ?),
  478. ?, ?)|;
  479. $sth = $dbh->prepare($query);
  480. $sth->execute( $form->{id}, $accno, $form->{payables},
  481. $form->{transdate} )
  482. || $form->dberror($query);
  483. }
  484. foreach my $trans_id ( keys %{ $form->{acc_trans} } ) {
  485. foreach my $accno ( keys %{ $form->{acc_trans}{$trans_id} } ) {
  486. $amount =
  487. $form->round_amount(
  488. $form->{acc_trans}{$trans_id}{$accno}{amount}, 2 );
  489. if ($amount) {
  490. $query = qq|
  491. INSERT INTO acc_trans
  492. (trans_id, chart_id, amount,
  493. transdate)
  494. VALUES (?, (SELECT id FROM chart
  495. WHERE accno = ?),
  496. ?, ?)|;
  497. $sth = $dbh->prepare($query);
  498. $sth->execute( $trans_id, $accno, $amount, $form->{transdate} )
  499. || $form->dberror($query);
  500. }
  501. }
  502. }
  503. # if there is no amount but a payment record payable
  504. if ( $invamount == 0 ) {
  505. $form->{payables} = 1;
  506. }
  507. my $cleared = 0;
  508. # record payments and offsetting AP
  509. for my $i ( 1 .. $form->{paidaccounts} ) {
  510. if ( $form->{"paid_$i"} ) {
  511. my ($accno) = split /--/, $form->{"AP_paid_$i"};
  512. $form->{"datepaid_$i"} = $form->{transdate}
  513. unless ( $form->{"datepaid_$i"} );
  514. $form->{datepaid} = $form->{"datepaid_$i"};
  515. $exchangerate = 0;
  516. if ( $form->{currency} eq $form->{defaultcurrency} ) {
  517. $form->{"exchangerate_$i"} = 1;
  518. }
  519. else {
  520. $exchangerate =
  521. $form->check_exchangerate( $myconfig, $form->{currency},
  522. $form->{"datepaid_$i"}, 'sell' );
  523. $form->{"exchangerate_$i"} =
  524. ($exchangerate)
  525. ? $exchangerate
  526. : $form->parse_amount( $myconfig,
  527. $form->{"exchangerate_$i"} );
  528. }
  529. # record AP
  530. $amount = (
  531. $form->round_amount(
  532. $form->{"paid_$i"} * $form->{exchangerate}, 2
  533. )
  534. ) * -1;
  535. if ( $form->{payables} ) {
  536. $query = qq|
  537. INSERT INTO acc_trans
  538. (trans_id, chart_id, amount,
  539. transdate)
  540. VALUES (?, (SELECT id FROM chart
  541. WHERE accno = ?),
  542. ?, ?)|;
  543. $sth = $dbh->prepare($query);
  544. $sth->execute( $form->{id}, $form->{AP}, $amount,
  545. $form->{"datepaid_$i"} )
  546. || $form->dberror($query);
  547. }
  548. if ($keepcleared) {
  549. $cleared = ( $form->{"cleared_$i"} ) ? 1 : 0;
  550. }
  551. # record payment
  552. $query = qq|
  553. INSERT INTO acc_trans
  554. (trans_id, chart_id, amount,
  555. transdate, source, memo, cleared)
  556. VALUES (?, (SELECT id FROM chart
  557. WHERE accno = ?),
  558. ?, ?, ?, ?, ?)|;
  559. $sth = $dbh->prepare($query);
  560. $sth->execute( $form->{id}, $accno, $form->{"paid_$i"},
  561. $form->{"datepaid_$i"},
  562. $form->{"source_$i"}, $form->{"memo_$i"}, $cleared )
  563. || $form->dberror($query);
  564. # exchangerate difference
  565. $amount = $form->round_amount(
  566. $form->{"paid_$i"} * $form->{"exchangerate_$i"} -
  567. $form->{"paid_$i"},
  568. 2
  569. );
  570. if ($amount) {
  571. $query = qq|
  572. INSERT INTO acc_trans
  573. (trans_id, chart_id, amount,
  574. transdate, source,
  575. fx_transaction, cleared)
  576. VALUES (?, (SELECT id FROM chart
  577. WHERE accno = ?),
  578. ?, ?, ?, '1', ?)|;
  579. $sth = $dbh->prepare($query);
  580. $sth->execute( $form->{id}, $accno, $amount,
  581. $form->{"datepaid_$i"},
  582. $form->{"source_$i"}, $cleared )
  583. || $form->dberror($query);
  584. }
  585. # gain/loss
  586. $amount = $form->round_amount(
  587. $form->round_amount( $form->{"paid_$i"} * $form->{exchangerate},
  588. 2 ) - $form->round_amount(
  589. $form->{"paid_$i"} * $form->{"exchangerate_$i"}, 2
  590. ),
  591. 2
  592. );
  593. if ($amount) {
  594. my $accno_id =
  595. ( $amount > 0 )
  596. ? $fxgain_accno_id
  597. : $fxloss_accno_id;
  598. $query = qq|
  599. INSERT INTO acc_trans
  600. (trans_id, chart_id, amount,
  601. transdate, fx_transaction,
  602. cleared)
  603. VALUES (?, ?, ?, ?, '1', ?)|;
  604. $sth = $dbh->prepare($query);
  605. $sth->execute( $form->{id}, $accno_id, $amount,
  606. $form->{"datepaid_$i"}, $cleared )
  607. || $form->dberror($query);
  608. }
  609. # update exchange rate
  610. if ( ( $form->{currency} ne $form->{defaultcurrency} )
  611. && !$exchangerate )
  612. {
  613. $form->update_exchangerate( $dbh, $form->{currency},
  614. $form->{"datepaid_$i"},
  615. 0, $form->{"exchangerate_$i"} );
  616. }
  617. }
  618. }
  619. # set values which could be empty
  620. $form->{taxincluded} *= 1;
  621. # save AP record
  622. $query = qq|
  623. UPDATE ap
  624. SET invnumber = ?,
  625. ordnumber = ?,
  626. quonumber = ?,
  627. transdate = ?,
  628. vendor_id = ?,
  629. amount = ?,
  630. netamount = ?,
  631. paid = ?,
  632. datepaid = ?,
  633. duedate = ?,
  634. invoice = '1',
  635. shippingpoint = ?,
  636. shipvia = ?,
  637. taxincluded = ?,
  638. notes = ?,
  639. intnotes = ?,
  640. curr = ?,
  641. department_id = ?,
  642. employee_id = ?,
  643. language_code = ?,
  644. ponumber = ?
  645. WHERE id = ?|;
  646. $sth = $dbh->prepare($query);
  647. $sth->execute(
  648. $form->{invnumber}, $form->{ordnumber}, $form->{quonumber},
  649. $form->{transdate}, $form->{vendor_id}, $invamount,
  650. $invnetamount, $form->{paid}, $form->{datepaid},
  651. $form->{duedate}, $form->{shippingpoint}, $form->{shipvia},
  652. $form->{taxincluded}, $form->{notes}, $form->{intnotes},
  653. $form->{currency}, $form->{department_id}, $form->{employee_id},
  654. $form->{language_code}, $form->{ponumber}, $form->{id}
  655. ) || $form->dberror($query);
  656. # add shipto
  657. $form->{name} = $form->{vendor};
  658. $form->{name} =~ s/--$form->{vendor_id}//;
  659. $form->add_shipto( $dbh, $form->{id} );
  660. my %audittrail = (
  661. tablename => 'ap',
  662. reference => $form->{invnumber},
  663. formname => $form->{type},
  664. action => 'posted',
  665. id => $form->{id}
  666. );
  667. $form->audittrail( $dbh, "", \%audittrail );
  668. foreach $item ( keys %updparts ) {
  669. $item = $dbh->quote($item);
  670. $query = qq|
  671. UPDATE parts
  672. SET avgcost = avgcost($item),
  673. lastcost = lastcost($item)
  674. WHERE id = $item|;
  675. $dbh->prepare($query) || $form->dberror($query);
  676. }
  677. my $rc = $dbh->commit;
  678. $rc;
  679. }
  680. sub reverse_invoice {
  681. my ( $dbh, $form ) = @_;
  682. my $query = qq|SELECT id FROM ap
  683. WHERE id = $form->{id}|;
  684. my ($id) = $dbh->selectrow_array($query);
  685. return unless $id;
  686. # reverse inventory items
  687. $query = qq|
  688. SELECT i.parts_id, p.inventory_accno_id, p.expense_accno_id,
  689. i.qty, i.allocated, i.sellprice, i.project_id
  690. FROM invoice i, parts p
  691. WHERE i.parts_id = p.id
  692. AND i.trans_id = ?|;
  693. my $sth = $dbh->prepare($query);
  694. $sth->execute( $form->{id} ) || $form->dberror($query);
  695. my $netamount = 0;
  696. while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  697. $netamount +=
  698. $form->round_amount( $ref->{sellprice} * $ref->{qty} * -1, 2 );
  699. if ( $ref->{inventory_accno_id} ) {
  700. # update onhand
  701. $form->update_balance( $dbh, "parts", "onhand",
  702. qq|id = $ref->{parts_id}|,
  703. $ref->{qty} );
  704. # if $ref->{allocated} > 0 than we sold that many items
  705. if ( $ref->{allocated} > 0 ) {
  706. # get references for sold items
  707. $query = qq|
  708. SELECT i.id, i.trans_id, i.allocated,
  709. a.transdate
  710. FROM invoice i, ar a
  711. WHERE i.parts_id = ?
  712. AND i.allocated < 0
  713. AND i.trans_id = a.id
  714. ORDER BY transdate DESC|;
  715. my $sth = $dbh->prepare($query);
  716. $sth->execute( $ref->{parts_id} )
  717. || $form->dberror($query);
  718. while ( my $pthref = $sth->fetchrow_hashref(NAME_lc) ) {
  719. my $qty = $ref->{allocated};
  720. if ( ( $ref->{allocated} + $pthref->{allocated} ) > 0 ) {
  721. $qty = $pthref->{allocated} * -1;
  722. }
  723. my $amount =
  724. $form->round_amount( $ref->{sellprice} * $qty, 2 );
  725. #adjust allocated
  726. $form->update_balance( $dbh, "invoice", "allocated",
  727. qq|id = $pthref->{id}|, $qty );
  728. # add reversal for sale
  729. $ref->{project_id} *= 1;
  730. $query = qq|
  731. INSERT INTO acc_trans
  732. (trans_id,
  733. chart_id, amount,
  734. transdate,
  735. project_id)
  736. VALUES (?, ?, ?, ?, ?)|;
  737. my $sth = $dbh->prepare($query);
  738. $sth->execute( $pthref->{trans_id},
  739. $ref->{expense_accno_id},
  740. $amount, $form->{transdate}, $ref->{project_id} )
  741. || $form->dberror($query);
  742. $query = qq|
  743. INSERT INTO acc_trans
  744. (trans_id, chart_id,
  745. amount, transdate,
  746. project_id)
  747. VALUES (?, ?, ?, ?, ?)|;
  748. $sth = $dbh->do($query);
  749. $sth->execute(
  750. $pthref->{trans_id},
  751. $ref->{inventory_accno_id},
  752. $amount * -1,
  753. $form->{transdate}, $ref->{project_id}
  754. ) || $form->dberror($query);
  755. last if ( ( $ref->{allocated} -= $qty ) <= 0 );
  756. }
  757. $sth->finish;
  758. }
  759. }
  760. }
  761. $sth->finish;
  762. # delete acc_trans
  763. $query = qq|DELETE FROM acc_trans WHERE trans_id = ?|;
  764. $dbh->prepare($query);
  765. $sth->execute( $form->{id} ) || $form->dberror($query);
  766. # delete invoice entries
  767. $query = qq|DELETE FROM invoice WHERE trans_id = ?|;
  768. $dbh->prepare($query);
  769. $sth->execute( $form->{id} ) || $form->dberror($query);
  770. $query = qq|DELETE FROM shipto WHERE trans_id = ?|;
  771. $dbh->prepare($query);
  772. $sth->execute( $form->{id} ) || $form->dberror($query);
  773. $dbh->commit;
  774. }
  775. sub delete_invoice {
  776. my ( $self, $myconfig, $form ) = @_;
  777. # connect to database
  778. my $dbh = $form->{dbh};
  779. my %audittrail = (
  780. tablename => 'ap',
  781. reference => $form->{invnumber},
  782. formname => $form->{type},
  783. action => 'deleted',
  784. id => $form->{id}
  785. );
  786. $form->audittrail( $dbh, "", \%audittrail );
  787. my $query = qq|SELECT parts_id FROM invoice WHERE trans_id = ?|;
  788. my $sth = $dbh->prepare($query);
  789. $sth->execute( $form->{id} ) || $form->dberror($query);
  790. my $item;
  791. my %updparts = ();
  792. while ( ($item) = $sth->fetchrow_array ) {
  793. $updparts{$item} = 1;
  794. }
  795. $sth->finish;
  796. &reverse_invoice( $dbh, $form );
  797. # delete AP record
  798. $query = qq|DELETE FROM ap WHERE id = ?|;
  799. my $sth = $dbh->prepare($query);
  800. $sth->execute( $form->{id} ) || $form->dberror($query);
  801. # delete spool files
  802. $query = qq|
  803. SELECT spoolfile FROM status
  804. WHERE trans_id = ?
  805. AND spoolfile IS NOT NULL|;
  806. my $sth = $dbh->prepare($query);
  807. $sth->execute( $form->{id} ) || $form->dberror($query);
  808. my $spoolfile;
  809. my @spoolfiles = ();
  810. while ( ($spoolfile) = $sth->fetchrow_array ) {
  811. push @spoolfiles, $spoolfile;
  812. }
  813. $sth->finish;
  814. # delete status entries
  815. $query = qq|DELETE FROM status WHERE trans_id = ?|;
  816. my $sth = $dbh->prepare($query);
  817. $sth->execute( $form->{id} ) || $form->dberror($query);
  818. if ($rc) {
  819. foreach $item ( keys %updparts ) {
  820. $item = $dbh->quote($item);
  821. $query = qq|
  822. UPDATE parts
  823. SET avgcost = avgcost($item),
  824. lastcost = lastcost($item)
  825. WHERE id = $item|;
  826. $dbh->do($query) || $form->dberror($query);
  827. }
  828. foreach $spoolfile (@spoolfiles) {
  829. unlink "${LedgerSMB::Sysconfig::spool}/$spoolfile"
  830. if $spoolfile;
  831. }
  832. }
  833. my $rc = $dbh->commit;
  834. $rc;
  835. }
  836. sub retrieve_invoice {
  837. my ( $self, $myconfig, $form ) = @_;
  838. # connect to database
  839. my $dbh = $form->{dbh};
  840. my $query;
  841. if ( $form->{id} ) {
  842. # get default accounts and last invoice number
  843. $query = qq|
  844. SELECT (select c.accno FROM chart c
  845. WHERE c.id = (SELECT value FROM defaults
  846. WHERE setting_key =
  847. 'inventory_accno_id'))
  848. AS inventory_accno,
  849. (SELECT c.accno FROM chart c
  850. WHERE c.id = (SELECT value FROM defaults
  851. WHERE setting_key =
  852. 'income_accno_id'))
  853. AS income_accno,
  854. (SELECT c.accno FROM chart c
  855. WHERE c.id = (SELECT value FROM defaults
  856. WHERE setting_key =
  857. 'expense_accno_id'))
  858. AS expense_accno,
  859. (SELECT c.accno FROM chart c
  860. WHERE c.id = (SELECT value FROM defaults
  861. WHERE setting_key =
  862. 'fxgain_accno_id'))
  863. AS fxgain_accno,
  864. (SELECT c.accno FROM chart c
  865. WHERE c.id = (SELECT value FROM defaults
  866. WHERE setting_key =
  867. 'fxloss_accno_id'))
  868. AS fxloss_accno,
  869. (SELECT value FROM defaults
  870. WHERE setting_key = 'curr') AS currencies|;
  871. }
  872. else {
  873. $query = qq|
  874. SELECT (select c.accno FROM chart c
  875. WHERE c.id = (SELECT value FROM defaults
  876. WHERE setting_key =
  877. 'inventory_accno_id'))
  878. AS inventory_accno,
  879. (SELECT c.accno FROM chart c
  880. WHERE c.id = (SELECT value FROM defaults
  881. WHERE setting_key =
  882. 'income_accno_id'))
  883. AS income_accno,
  884. (SELECT c.accno FROM chart c
  885. WHERE c.id = (SELECT value FROM defaults
  886. WHERE setting_key =
  887. 'expense_accno_id'))
  888. AS expense_accno,
  889. (SELECT c.accno FROM chart c
  890. WHERE c.id = (SELECT value FROM defaults
  891. WHERE setting_key =
  892. 'fxgain_accno_id'))
  893. AS fxgain_accno,
  894. (SELECT c.accno FROM chart c
  895. WHERE c.id = (SELECT value FROM defaults
  896. WHERE setting_key =
  897. 'fxloss_accno_id'))
  898. AS fxloss_accno,
  899. (SELECT value FROM defaults
  900. WHERE setting_key = 'curr') AS currencies,
  901. current_date AS transdate|;
  902. }
  903. my $sth = $dbh->prepare($query);
  904. $sth->execute || $form->dberror($query);
  905. my $ref = $sth->fetchrow_hashref(NAME_lc);
  906. for ( keys %$ref ) {
  907. $form->{$_} = $ref->{$_};
  908. }
  909. $sth->finish;
  910. if ( $form->{id} ) {
  911. $query = qq|
  912. SELECT a.invnumber, a.transdate, a.duedate,
  913. a.ordnumber, a.quonumber, a.paid, a.taxincluded,
  914. a.notes, a.intnotes, a.curr AS currency,
  915. a.vendor_id, a.language_code, a.ponumber
  916. FROM ap a
  917. WHERE id = ?|;
  918. $sth = $dbh->prepare($query);
  919. $sth->execute( $form->{id} ) || $form->dberror($query);
  920. $ref = $sth->fetchrow_hashref(NAME_lc);
  921. $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
  922. for ( keys %$ref ) {
  923. $form->{$_} = $ref->{$_};
  924. }
  925. $sth->finish;
  926. # get shipto
  927. $query = qq|SELECT * FROM shipto WHERE trans_id = ?|;
  928. $sth = $dbh->prepare($query);
  929. $sth->execute( $form->{id} ) || $form->dberror($query);
  930. $ref = $sth->fetchrow_hashref(NAME_lc);
  931. for ( keys %$ref ) {
  932. $form->{$_} = $ref->{$_};
  933. }
  934. $sth->finish;
  935. # retrieve individual items
  936. $query = qq|
  937. SELECT p.partnumber, i.description, i.qty,
  938. i.fxsellprice, i.sellprice,
  939. i.parts_id AS id, i.unit, p.bin,
  940. i.deliverydate,
  941. pr.projectnumber, i.project_id,
  942. i.serialnumber,
  943. i.discount, i.notes, pg.partsgroup,
  944. p.partsgroup_id, p.partnumber AS sku,
  945. p.weight, p.onhand, p.inventory_accno_id,
  946. p.income_accno_id, p.expense_accno_id,
  947. t.description AS partsgrouptranslation
  948. FROM invoice i
  949. JOIN parts p ON (i.parts_id = p.id)
  950. LEFT JOIN project pr ON (i.project_id = pr.id)
  951. LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
  952. LEFT JOIN translation t
  953. ON (t.trans_id = p.partsgroup_id
  954. AND t.language_code = ?)
  955. WHERE i.trans_id = ?
  956. ORDER BY i.id|;
  957. $sth = $dbh->prepare($query);
  958. $sth->execute( $form->{language_code}, $form->{id} )
  959. || $form->dberror($query);
  960. # exchangerate defaults
  961. &exchangerate_defaults( $dbh, $form );
  962. # price matrix and vendor partnumber
  963. my $pmh = PriceMatrix::price_matrix_query( $dbh, $form );
  964. # tax rates for part
  965. $query = qq|
  966. SELECT c.accno
  967. FROM chart c
  968. JOIN partstax pt ON (pt.chart_id = c.id)
  969. WHERE pt.parts_id = ?|;
  970. my $tth = $dbh->prepare($query);
  971. my $ptref;
  972. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  973. $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
  974. my ($dec) = ( $ref->{fxsellprice} =~ /\.(\d+)/ );
  975. $dec = length $dec;
  976. my $decimalplaces = ( $dec > 2 ) ? $dec : 2;
  977. $tth->execute( $ref->{id} );
  978. $ref->{taxaccounts} = "";
  979. my $taxrate = 0;
  980. while ( $ptref = $tth->fetchrow_hashref(NAME_lc) ) {
  981. $form->db_parse_numeric(sth => $tth, hashref => $ptref);
  982. $ref->{taxaccounts} .= "$ptref->{accno} ";
  983. $taxrate += $form->{"$ptref->{accno}_rate"};
  984. }
  985. $tth->finish;
  986. chop $ref->{taxaccounts};
  987. # price matrix
  988. $ref->{sellprice} =
  989. $form->round_amount(
  990. $ref->{fxsellprice} * $form->{ $form->{currency} },
  991. $decimalplaces );
  992. PriceMatrix::price_matrix( $pmh, $ref, $decimalplaces, $form,
  993. $myconfig );
  994. $ref->{sellprice} = $ref->{fxsellprice};
  995. $ref->{qty} *= -1;
  996. $ref->{partsgroup} = $ref->{partsgrouptranslation}
  997. if $ref->{partsgrouptranslation};
  998. push @{ $form->{invoice_details} }, $ref;
  999. }
  1000. $sth->finish;
  1001. }
  1002. my $rc = $dbh->commit;
  1003. $rc;
  1004. }
  1005. sub retrieve_item {
  1006. my ( $self, $myconfig, $form ) = @_;
  1007. $dbh = $form->{dbh};
  1008. my $i = $form->{rowcount};
  1009. my $null;
  1010. my $var;
  1011. # don't include assemblies or obsolete parts
  1012. my $where = "WHERE p.assembly = '0' AND p.obsolete = '0'";
  1013. if ( $form->{"partnumber_$i"} ne "" ) {
  1014. $var = $dbh->quote( $form->like( lc $form->{"partnumber_$i"} ) );
  1015. $where .= " AND lower(p.partnumber) LIKE $var";
  1016. }
  1017. if ( $form->{"description_$i"} ne "" ) {
  1018. $var = $dbh->quote( $form->like( lc $form->{"description_$i"} ) );
  1019. if ( $form->{language_code} ne "" ) {
  1020. $where .= " AND lower(t1.description) LIKE $var";
  1021. }
  1022. else {
  1023. $where .= " AND lower(p.description) LIKE $var";
  1024. }
  1025. }
  1026. if ( $form->{"partsgroup_$i"} ne "" ) {
  1027. ( $null, $var ) = split /--/, $form->{"partsgroup_$i"};
  1028. $var = $dbh->quote($var);
  1029. $where .= qq| AND p.partsgroup_id = $var|;
  1030. }
  1031. if ( $form->{"description_$i"} ne "" ) {
  1032. $where .= " ORDER BY 3";
  1033. }
  1034. else {
  1035. $where .= " ORDER BY 2";
  1036. }
  1037. my $query = qq|
  1038. SELECT p.id, p.partnumber, p.description,
  1039. pg.partsgroup, p.partsgroup_id,
  1040. p.lastcost AS sellprice, p.unit, p.bin, p.onhand,
  1041. p.notes, p.inventory_accno_id, p.income_accno_id,
  1042. p.expense_accno_id, p.partnumber AS sku, p.weight,
  1043. t1.description AS translation,
  1044. t2.description AS grouptranslation
  1045. FROM parts p
  1046. LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
  1047. LEFT JOIN translation t1
  1048. ON (t1.trans_id = p.id AND t1.language_code = ?)
  1049. LEFT JOIN translation t2
  1050. ON (t2.trans_id = p.partsgroup_id
  1051. AND t2.language_code = ?)
  1052. $where|;
  1053. my $sth = $dbh->prepare($query);
  1054. $sth->execute( $form->{language_code}, $form->{language_code} )
  1055. || $form->dberror($query);
  1056. # foreign currency
  1057. &exchangerate_defaults( $dbh, $form );
  1058. # taxes
  1059. $query = qq|
  1060. SELECT c.accno
  1061. FROM chart c
  1062. JOIN partstax pt ON (pt.chart_id = c.id)
  1063. WHERE pt.parts_id = ?|;
  1064. my $tth = $dbh->prepare($query) || $form->dberror($query);
  1065. # price matrix
  1066. my $pmh = PriceMatrix::price_matrix_query( $dbh, $form );
  1067. my $ref;
  1068. my $ptref;
  1069. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1070. $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
  1071. my ($dec) = ( $ref->{sellprice} =~ /\.(\d+)/ );
  1072. $dec = length $dec;
  1073. my $decimalplaces = ( $dec > 2 ) ? $dec : 2;
  1074. # get taxes for part
  1075. $tth->execute( $ref->{id} );
  1076. $ref->{taxaccounts} = "";
  1077. while ( $ptref = $tth->fetchrow_hashref(NAME_lc) ) {
  1078. $ref->{taxaccounts} .= "$ptref->{accno} ";
  1079. }
  1080. $tth->finish;
  1081. chop $ref->{taxaccounts};
  1082. # get vendor price and partnumber
  1083. PriceMatrix::price_matrix( $pmh, $ref, $decimalplaces, $form,
  1084. $myconfig );
  1085. $ref->{description} = $ref->{translation}
  1086. if $ref->{translation};
  1087. $ref->{partsgroup} = $ref->{grouptranslation}
  1088. if $ref->{grouptranslation};
  1089. push @{ $form->{item_list} }, $ref;
  1090. }
  1091. $sth->finish;
  1092. }
  1093. sub exchangerate_defaults {
  1094. my ( $dbh, $form ) = @_;
  1095. my $var;
  1096. # get default currencies
  1097. my $query = qq|
  1098. SELECT substr(value,1,3), value FROM defaults
  1099. WHERE setting_key = 'curr'|;
  1100. my $eth = $dbh->prepare($query) || $form->dberror($query);
  1101. $eth->execute;
  1102. ( $form->{defaultcurrency}, $form->{currencies} ) = $eth->fetchrow_array;
  1103. $eth->finish;
  1104. $query = qq|
  1105. SELECT sell
  1106. FROM exchangerate
  1107. WHERE curr = ?
  1108. AND transdate = ?|;
  1109. my $eth1 = $dbh->prepare($query) || $form->dberror($query);
  1110. $query = qq/
  1111. SELECT max(transdate || ' ' || sell || ' ' || curr)
  1112. FROM exchangerate
  1113. WHERE curr = ?/;
  1114. my $eth2 = $dbh->prepare($query) || $form->dberror($query);
  1115. # get exchange rates for transdate or max
  1116. foreach $var ( split /:/, substr( $form->{currencies}, 4 ) ) {
  1117. $eth1->execute( $var, $form->{transdate} );
  1118. @array = $eth1->fetchrow_array;
  1119. $form->db_parse_numeric(sth=> $eth1, arrayref=>\@array);
  1120. $form->{$var} = shift @array;
  1121. if ( !$form->{$var} ) {
  1122. $eth2->execute($var);
  1123. ( $form->{$var} ) = $eth2->fetchrow_array;
  1124. ( $null, $form->{$var} ) = split / /, $form->{$var};
  1125. $form->{$var} = 1 unless $form->{$var};
  1126. $eth2->finish;
  1127. }
  1128. $eth1->finish;
  1129. }
  1130. $form->{ $form->{currency} } = $form->{exchangerate}
  1131. if $form->{exchangerate};
  1132. $form->{ $form->{currency} } ||= 1;
  1133. $form->{ $form->{defaultcurrency} } = 1;
  1134. }
  1135. sub vendor_details {
  1136. my ( $self, $myconfig, $form ) = @_;
  1137. # connect to database
  1138. my $dbh = $form->{dbh};
  1139. # get rest for the vendor
  1140. my $query = qq|
  1141. SELECT vendornumber, name, address1, address2, city, state,
  1142. zipcode, country, contact, phone as vendorphone,
  1143. fax as vendorfax, vendornumber,
  1144. taxnumber AS vendortaxnumber, sic_code AS sic, iban, bic,
  1145. gifi_accno AS gifi, startdate, enddate
  1146. FROM vendor
  1147. WHERE id = ?|;
  1148. my $sth = $dbh->prepare($query);
  1149. $sth->execute( $form->{vendor_id} ) || $form->dberror($query);
  1150. $ref = $sth->fetchrow_hashref(NAME_lc);
  1151. for ( keys %$ref ) {
  1152. $form->{$_} = $ref->{$_};
  1153. }
  1154. $sth->finish;
  1155. }
  1156. sub item_links {
  1157. my ( $self, $myconfig, $form ) = @_;
  1158. # connect to database
  1159. my $dbh = $form->{dbh};
  1160. my $query = qq|
  1161. SELECT accno, description, link
  1162. FROM chart
  1163. WHERE link LIKE '%IC%'
  1164. ORDER BY accno|;
  1165. my $sth = $dbh->prepare($query);
  1166. $sth->execute || $form->dberror($query);
  1167. while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1168. foreach my $key ( split( /:/, $ref->{link} ) ) {
  1169. if ( $key =~ /IC/ ) {
  1170. push @{ $form->{IC_links}{$key} },
  1171. {
  1172. accno => $ref->{accno},
  1173. description => $ref->{description}
  1174. };
  1175. }
  1176. }
  1177. }
  1178. $sth->finish;
  1179. }
  1180. 1;