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