summaryrefslogtreecommitdiff
path: root/LedgerSMB/IR.pm
blob: 2d3c57767d512f6698e2fcb54b22ff259c20cc5e (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. $form->{"qty_$_"} *= -1 if $form->{reverse};
  45. unless ( $form->{"deliverydate_$_"} ) {
  46. $form->{"deliverydate_$_"} = $form->{transdate};
  47. }
  48. }
  49. my $query;
  50. my $sth;
  51. my $ref;
  52. my $null;
  53. my $project_id;
  54. my $exchangerate = 0;
  55. my $allocated;
  56. my $taxrate;
  57. my $taxamount;
  58. my $diff = 0;
  59. my $item;
  60. my $invoice_id;
  61. my $keepcleared;
  62. ( $null, $form->{employee_id} ) = split /--/, $form->{employee};
  63. unless ( $form->{employee_id} ) {
  64. ( $form->{employee}, $form->{employee_id} ) = $form->get_employee($dbh);
  65. }
  66. ( $null, $form->{department_id} ) = split( /--/, $form->{department} );
  67. $form->{department_id} *= 1;
  68. $query = qq|
  69. SELECT (SELECT value FROM defaults
  70. WHERE setting_key = 'fxgain_accno_id')
  71. AS fxgain_accno_id,
  72. (SELECT value FROM defaults
  73. WHERE setting_key = 'fxloss_accno_id')
  74. AS fxloss_accno_id|;
  75. my ( $fxgain_accno_id, $fxloss_accno_id ) = $dbh->selectrow_array($query);
  76. $query = qq|
  77. SELECT inventory_accno_id, income_accno_id, expense_accno_id
  78. FROM parts
  79. WHERE id = ?|;
  80. my $pth = $dbh->prepare($query) || $form->dberror($query);
  81. my %updparts = ();
  82. if ( $form->{id} ) {
  83. my $sth;
  84. $keepcleared = 1;
  85. $query = qq|SELECT id FROM ap WHERE id = ?|;
  86. $sth = $dbh->prepare($query);
  87. $sth->execute( $form->{id} );
  88. if ( $sth->fetchrow_array ) {
  89. $query = qq|
  90. SELECT p.id, p.inventory_accno_id,
  91. p.income_accno_id
  92. FROM invoice i
  93. JOIN parts p ON (p.id = i.parts_id)
  94. WHERE i.trans_id = ?|;
  95. $sth = $dbh->prepare($query);
  96. $sth->execute( $form->{id} ) || $form->dberror($query);
  97. while ( $ref = $sth->fetchrow_hashref ) {
  98. if ( $ref->{inventory_accno_id}
  99. && $ref->{income_accno_id} )
  100. {
  101. $updparts{ $ref->{id} } = 1;
  102. }
  103. }
  104. $sth->finish;
  105. &reverse_invoice( $dbh, $form );
  106. }
  107. else {
  108. $query = qq|INSERT INTO ap (id) VALUES (?)|;
  109. $sth = $dbh->prepare($query);
  110. $sth->execute( $form->{id} ) || $form->dberror($query);
  111. }
  112. }
  113. my $uid = localtime;
  114. $uid .= "$$";
  115. if ( !$form->{id} ) {
  116. $query = qq|
  117. INSERT INTO ap (invnumber, employee_id)
  118. VALUES ('$uid', (SELECT id FROM employee
  119. WHERE login = ?))|;
  120. $sth = $dbh->prepare($query);
  121. $sth->execute( $form->{login} ) || $form->dberror($query);
  122. $query = qq|SELECT id FROM ap WHERE invnumber = '$uid'|;
  123. $sth = $dbh->prepare($query);
  124. $sth->execute || $form->dberror($query);
  125. ( $form->{id} ) = $sth->fetchrow_array;
  126. $sth->finish;
  127. }
  128. my $amount;
  129. my $grossamount;
  130. my $allocated;
  131. my $invamount = 0;
  132. my $invnetamount = 0;
  133. if ( $form->{currency} eq $form->{defaultcurrency} ) {
  134. $form->{exchangerate} = 1;
  135. }
  136. else {
  137. $exchangerate =
  138. $form->check_exchangerate( $myconfig, $form->{currency},
  139. $form->{transdate}, 'sell' );
  140. }
  141. $form->{exchangerate} =
  142. ($exchangerate)
  143. ? $exchangerate
  144. : $form->parse_amount( $myconfig, $form->{exchangerate} );
  145. for my $i ( 1 .. $form->{rowcount} ) {
  146. $form->{"qty_$i"} = $form->parse_amount( $myconfig, $form->{"qty_$i"} );
  147. if ( $form->{"qty_$i"} ) {
  148. $pth->execute( $form->{"id_$i"} );
  149. $ref = $pth->fetchrow_hashref(NAME_lc);
  150. for ( keys %$ref ) {
  151. $form->{"${_}_$i"} = $ref->{$_};
  152. }
  153. $pth->finish;
  154. # project
  155. if ( $form->{"projectnumber_$i"} ne "" ) {
  156. ( $null, $project_id ) =
  157. split /--/, $form->{"projectnumber_$i"};
  158. }
  159. # undo discount formatting
  160. $form->{"discount_$i"} =
  161. $form->parse_amount( $myconfig, $form->{"discount_$i"} ) / 100;
  162. # keep entered selling price
  163. my $fxsellprice =
  164. $form->parse_amount( $myconfig, $form->{"sellprice_$i"} );
  165. my ($dec) = ( $fxsellprice =~ /\.(\d+)/ );
  166. $dec = length $dec;
  167. my $decimalplaces = ( $dec > 2 ) ? $dec : 2;
  168. # deduct discount
  169. $form->{"sellprice_$i"} = $fxsellprice -
  170. $form->round_amount( $fxsellprice * $form->{"discount_$i"},
  171. $decimalplaces );
  172. # linetotal
  173. my $fxlinetotal =
  174. $form->round_amount( $form->{"sellprice_$i"} * $form->{"qty_$i"},
  175. 2 );
  176. $amount = $fxlinetotal * $form->{exchangerate};
  177. my $linetotal = $form->round_amount( $amount, 2 );
  178. $fxdiff += $amount - $linetotal;
  179. @taxaccounts = Tax::init_taxes(
  180. $form,
  181. $form->{"taxaccounts_$i"},
  182. $form->{'taxaccounts'}
  183. );
  184. $tax = Math::BigFloat->bzero();
  185. $fxtax = Math::BigFloat->bzero();
  186. if ( $form->{taxincluded} ) {
  187. $tax += $amount =
  188. Tax::calculate_taxes( \@taxaccounts, $form, $linetotal, 1 );
  189. $form->{"sellprice_$i"} -= $amount / $form->{"qty_$i"};
  190. }
  191. else {
  192. $tax += $amount =
  193. Tax::calculate_taxes( \@taxaccounts, $form, $linetotal, 0 );
  194. $fxtax +=
  195. Tax::calculate_taxes( \@taxaccounts, $form, $fxlinetotal, 0 );
  196. }
  197. for (@taxaccounts) {
  198. $form->{acc_trans}{ $form->{id} }{ $_->account }{amount} +=
  199. $_->value;
  200. }
  201. $grossamount = $form->round_amount( $linetotal, 2 );
  202. if ( $form->{taxincluded} ) {
  203. $amount = $form->round_amount( $tax, 2 );
  204. $linetotal -= $form->round_amount( $tax - $diff, 2 );
  205. $diff = ( $amount - $tax );
  206. }
  207. $amount = $form->round_amount( $linetotal, 2 );
  208. $allocated = 0;
  209. # adjust and round sellprice
  210. $form->{"sellprice_$i"} =
  211. $form->round_amount(
  212. $form->{"sellprice_$i"} * $form->{exchangerate},
  213. $decimalplaces );
  214. # save detail record in invoice table
  215. $query = qq|
  216. INSERT INTO invoice (description)
  217. VALUES ('$uid')|;
  218. $dbh->do($query) || $form->dberror($query);
  219. $query = qq|
  220. SELECT id FROM invoice
  221. WHERE description = '$uid'|;
  222. ($invoice_id) = $dbh->selectrow_array($query);
  223. $query = qq|
  224. UPDATE invoice
  225. SET trans_id = ?,
  226. parts_id = ?,
  227. description = ?,
  228. qty = ?,
  229. sellprice = ?,
  230. fxsellprice = ?,
  231. discount = ?,
  232. allocated = ?,
  233. unit = ?,
  234. deliverydate = ?,
  235. project_id = ?,
  236. serialnumber = ?,
  237. reverse = ?
  238. notes = ?
  239. WHERE id = ?|;
  240. $sth = $dbh->prepare($query);
  241. $sth->execute(
  242. $form->{id}, $form->{"id_$i"},
  243. $form->{"description_$i"}, $form->{"qty_$i"} * -1,
  244. $form->{"sellprice_$i"}, $fxsellprice,
  245. $form->{"discount_$i"}, $allocated,
  246. $form->{"unit_$i"}, $form->{"deliverydate_$i"},
  247. $project_id, $form->{"serialnumber_$i"},
  248. $form->{reverse}, $form->{"notes_$i"},
  249. $invoice_id
  250. ) || $form->dberror($query);
  251. if (defined $form->{approved}) {
  252. $query = qq| UPDATE ap SET approved = ? WHERE id = ?|;
  253. $dbh->prepare($query)->execute($form->{approved}, $form->{id})
  254. || $form->dberror($query);
  255. if (!$form->{approved}){
  256. if (not defined $form->{batch_id}){
  257. $form->error($locale->text('Batch ID Missing'));
  258. }
  259. $query = qq|
  260. INSERT INTO voucher (batch_id, trans_id) VALUES (?, ?)|;
  261. $sth = $dbh->prepare($query);
  262. $sth->execute($form->{batch_id}, $form->{id}) ||
  263. $form->dberror($query);
  264. }
  265. }
  266. if ( $form->{"inventory_accno_id_$i"} ) {
  267. my $totalqty = $form->{"qty_$i"};
  268. if($form->{"qty_$i"}<0) {
  269. # check for unallocated entries at the same price to match our entry
  270. $query = qq|
  271. SELECT i.id, i.qty, i.allocated, a.transdate
  272. FROM invoice i
  273. JOIN parts p ON (p.id = i.parts_id)
  274. JOIN ap a ON (a.id = i.trans_id)
  275. WHERE i.parts_id = ? AND (i.qty + i.allocated) < 0 AND i.sellprice = ?
  276. ORDER BY transdate
  277. |;
  278. $sth = $dbh->prepare($query);
  279. $sth->execute( $form->{"id_$i"}, $form->{"sellprice_$i"}) || $form->dberror($query);
  280. my $totalqty = $form->{"qty_$i"};
  281. while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  282. $form->db_parse_numeric(sth=>$sth, hashref => $ref);
  283. my $qty = $ref->{qty} + $ref->{allocated};
  284. if ( ( $qty - $totalqty ) < 0 ) { $qty = $totalqty; }
  285. # update allocated for sold item
  286. $form->update_balance( $dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty * -1 );
  287. $allocated += $qty;
  288. last if ( ( $totalqty -= $qty ) >= 0 );
  289. }
  290. } else {
  291. # start patch bug 1755355 ###############################################################################
  292. # check for unallocated entries atthe same price to match our entry
  293. $query = qq|
  294. SELECT i.id, i.qty, i.allocated, a.transdate
  295. FROM invoice i
  296. JOIN parts p ON (p.id = i.parts_id)
  297. JOIN ap a ON (a.id = i.trans_id)
  298. WHERE i.parts_id = ? AND (i.qty + i.allocated) > 0 AND i.sellprice = ?
  299. ORDER BY transdate|;
  300. $sth = $dbh->prepare($query);
  301. $sth->execute( $form->{"id_$i"}, $form->{"sellprice_$i"}) || $form->dberror($query);
  302. while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  303. $form->db_parse_numeric(sth=>$sth, hashref => $ref);
  304. my $qty = $ref->{qty} + $ref->{allocated};
  305. if ( ( $qty - $totalqty ) > 0 ) { $qty = $totalqty; }
  306. # update allocated for sold item
  307. $form->update_balance( $dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty * -1 );
  308. $allocated += $qty;
  309. last if ( ( $totalqty -= $qty ) <= 0 );
  310. }
  311. # stop patch bug 1755355 ###############################################################################
  312. }
  313. # add purchase to inventory
  314. push @{ $form->{acc_trans}{lineitems} },
  315. {
  316. chart_id => $form->{"inventory_accno_id_$i"},
  317. amount => $amount,
  318. fxgrossamount => $fxlinetotal +
  319. $form->round_amount( $fxtax, 2 ),
  320. grossamount => $grossamount,
  321. project_id => $project_id,
  322. invoice_id => $invoice_id
  323. };
  324. $updparts{ $form->{"id_$i"} } = 1;
  325. # update parts table
  326. $form->update_balance( $dbh, "parts", "onhand",
  327. qq|id = $form->{"id_$i"}|,
  328. $form->{"qty_$i"} )
  329. unless $form->{shipped};
  330. # check if we sold the item
  331. $query = qq|
  332. SELECT i.id, i.qty, i.allocated,
  333. i.trans_id, i.project_id,
  334. p.inventory_accno_id,
  335. p.expense_accno_id, a.transdate
  336. FROM invoice i
  337. JOIN parts p ON (p.id = i.parts_id)
  338. JOIN ar a ON (a.id = i.trans_id)
  339. WHERE i.parts_id = ?
  340. AND (i.qty + i.allocated) > 0
  341. ORDER BY transdate|;
  342. $sth = $dbh->prepare($query);
  343. $sth->execute( $form->{"id_$i"} )
  344. || $form->dberror($query);
  345. while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  346. $form->db_parse_numeric(sth=>$sth, hashref => $ref);
  347. my $qty = $ref->{qty} + $ref->{allocated};
  348. if ( ( $qty - $totalqty ) > 0 ) {
  349. $qty = $totalqty;
  350. }
  351. $linetotal =
  352. $form->round_amount( $form->{"sellprice_$i"} * $qty, 2 );
  353. if ($linetotal) {
  354. $query = qq|
  355. INSERT INTO acc_trans
  356. (trans_id,
  357. chart_id,
  358. amount,
  359. transdate,
  360. project_id,
  361. invoice_id)
  362. VALUES (?, ?, ?, ?,
  363. ?, ?)|;
  364. my $sth = $dbh->prepare($query);
  365. $sth->execute(
  366. $ref->{trans_id}, $ref->{inventory_accno_id},
  367. $linetotal, $ref->{transdate},
  368. $ref->{project_id}, $invoice_id
  369. ) || $form->dberror($query);
  370. # add expense
  371. $query = qq|
  372. INSERT INTO acc_trans
  373. (trans_id,
  374. chart_id,
  375. amount,
  376. transdate,
  377. project_id,
  378. invoice_id)
  379. VALUES (?, ?, ?, ?,
  380. ?, ?)|;
  381. $sth = $dbh->prepare($query);
  382. $sth->execute(
  383. $ref->{trans_id}, $ref->{expense_accno_id},
  384. $linetotal * -1, $ref->{transdate},
  385. $ref->{project_id}, $invoice_id
  386. ) || $form->dberror($query);
  387. }
  388. # update allocated for sold item
  389. $form->update_balance( $dbh, "invoice", "allocated",
  390. qq|id = $ref->{id}|,
  391. $qty * -1 );
  392. $form->update_balance( $dbh, "invoice", "allocated",
  393. qq|id =$invoice_id|,$qty);
  394. $allocated += $qty;
  395. last if ( ( $totalqty -= $qty ) <= 0 );
  396. }
  397. $sth->finish;
  398. }
  399. else {
  400. # add purchase to expense
  401. push @{ $form->{acc_trans}{lineitems} },
  402. {
  403. chart_id => $form->{"expense_accno_id_$i"},
  404. amount => $amount,
  405. fxgrossamount => $fxlinetotal +
  406. $form->round_amount( $fxtax, 2 ),
  407. grossamount => $grossamount,
  408. project_id => $project_id,
  409. invoice_id => $invoice_id
  410. };
  411. }
  412. }
  413. }
  414. $form->{paid} = 0;
  415. for $i ( 1 .. $form->{paidaccounts} ) {
  416. $form->{"paid_$i"} =
  417. $form->parse_amount( $myconfig, $form->{"paid_$i"} );
  418. $form->{"paid_$i"} *= -1 if $form->{reverse};
  419. $form->{paid} += $form->{"paid_$i"};
  420. $form->{datepaid} = $form->{"datepaid_$i"}
  421. if ( $form->{"datepaid_$i"} );
  422. }
  423. # add lineitems + tax
  424. $amount = 0;
  425. $grossamount = 0;
  426. $fxgrossamount = 0;
  427. for ( @{ $form->{acc_trans}{lineitems} } ) {
  428. $amount += $_->{amount};
  429. $grossamount += $_->{grossamount};
  430. $fxgrossamount += $_->{fxgrossamount};
  431. }
  432. $invnetamount = $amount;
  433. $amount = 0;
  434. for ( split / /, $form->{taxaccounts} ) {
  435. $amount += $form->{acc_trans}{ $form->{id} }{$_}{amount} =
  436. $form->round_amount( $form->{acc_trans}{ $form->{id} }{$_}{amount},
  437. 2 );
  438. $form->{acc_trans}{ $form->{id} }{$_}{amount} *= -1;
  439. }
  440. $invamount = $invnetamount + $amount;
  441. $diff = 0;
  442. if ( $form->{taxincluded} ) {
  443. $diff = $form->round_amount( $grossamount - $invamount, 2 );
  444. $invamount += $diff;
  445. }
  446. $fxdiff = $form->round_amount( $fxdiff, 2 );
  447. $invnetamount += $fxdiff;
  448. $invamount += $fxdiff;
  449. if ( $form->round_amount( $form->{paid} - $fxgrossamount, 2 ) == 0 ) {
  450. $form->{paid} = $invamount;
  451. }
  452. else {
  453. $form->{paid} =
  454. $form->round_amount( $form->{paid} * $form->{exchangerate}, 2 );
  455. }
  456. foreach $ref ( sort { $b->{amount} <=> $a->{amount} }
  457. @{ $form->{acc_trans}{lineitems} } )
  458. {
  459. $amount = $ref->{amount} + $diff + $fxdiff;
  460. $query = qq|
  461. INSERT INTO acc_trans (trans_id, chart_id, amount,
  462. transdate, project_id, invoice_id)
  463. VALUES (?, ?, ?, ?, ?, ?)|;
  464. $sth = $dbh->prepare($query);
  465. $sth->execute(
  466. $form->{id}, $ref->{chart_id}, $amount * -1,
  467. $form->{transdate}, $ref->{project_id}, $ref->{invoice_id}
  468. ) || $form->dberror($query);
  469. $diff = 0;
  470. $fxdiff = 0;
  471. }
  472. $form->{payables} = $invamount;
  473. delete $form->{acc_trans}{lineitems};
  474. # update exchangerate
  475. if ( ( $form->{currency} ne $form->{defaultcurrency} ) && !$exchangerate ) {
  476. $form->update_exchangerate( $dbh, $form->{currency}, $form->{transdate},
  477. 0, $form->{exchangerate} );
  478. }
  479. # record payable
  480. if ( $form->{payables} ) {
  481. ($accno) = split /--/, $form->{AP};
  482. $query = qq|
  483. INSERT INTO acc_trans (trans_id, chart_id, amount,
  484. transdate)
  485. VALUES (?, (SELECT id FROM chart WHERE accno = ?),
  486. ?, ?)|;
  487. $sth = $dbh->prepare($query);
  488. $sth->execute( $form->{id}, $accno, $form->{payables},
  489. $form->{transdate} )
  490. || $form->dberror($query);
  491. }
  492. foreach my $trans_id ( keys %{ $form->{acc_trans} } ) {
  493. foreach my $accno ( keys %{ $form->{acc_trans}{$trans_id} } ) {
  494. $amount =
  495. $form->round_amount(
  496. $form->{acc_trans}{$trans_id}{$accno}{amount}, 2 );
  497. if ($amount) {
  498. $query = qq|
  499. INSERT INTO acc_trans
  500. (trans_id, chart_id, amount,
  501. transdate)
  502. VALUES (?, (SELECT id FROM chart
  503. WHERE accno = ?),
  504. ?, ?)|;
  505. $sth = $dbh->prepare($query);
  506. $sth->execute( $trans_id, $accno, $amount, $form->{transdate} )
  507. || $form->dberror($query);
  508. }
  509. }
  510. }
  511. # if there is no amount but a payment record payable
  512. if ( $invamount == 0 ) {
  513. $form->{payables} = 1;
  514. }
  515. my $cleared = 0;
  516. # record payments and offsetting AP
  517. for my $i ( 1 .. $form->{paidaccounts} ) {
  518. if ( $form->{"paid_$i"} ) {
  519. my ($accno) = split /--/, $form->{"AP_paid_$i"};
  520. $form->{"datepaid_$i"} = $form->{transdate}
  521. unless ( $form->{"datepaid_$i"} );
  522. $form->{datepaid} = $form->{"datepaid_$i"};
  523. $exchangerate = 0;
  524. if ( $form->{currency} eq $form->{defaultcurrency} ) {
  525. $form->{"exchangerate_$i"} = 1;
  526. }
  527. else {
  528. $exchangerate =
  529. $form->check_exchangerate( $myconfig, $form->{currency},
  530. $form->{"datepaid_$i"}, 'sell' );
  531. $form->{"exchangerate_$i"} =
  532. ($exchangerate)
  533. ? $exchangerate
  534. : $form->parse_amount( $myconfig,
  535. $form->{"exchangerate_$i"} );
  536. }
  537. # record AP
  538. $amount = (
  539. $form->round_amount(
  540. $form->{"paid_$i"} * $form->{exchangerate}, 2
  541. )
  542. ) * -1;
  543. if ( $form->{payables} ) {
  544. $query = qq|
  545. INSERT INTO acc_trans
  546. (trans_id, chart_id, amount,
  547. transdate)
  548. VALUES (?, (SELECT id FROM chart
  549. WHERE accno = ?),
  550. ?, ?)|;
  551. $sth = $dbh->prepare($query);
  552. $sth->execute( $form->{id}, $form->{AP}, $amount,
  553. $form->{"datepaid_$i"} )
  554. || $form->dberror($query);
  555. }
  556. if ($keepcleared) {
  557. $cleared = ( $form->{"cleared_$i"} ) ? 1 : 0;
  558. }
  559. # record payment
  560. $query = qq|
  561. INSERT INTO acc_trans
  562. (trans_id, chart_id, amount,
  563. transdate, source, memo, cleared)
  564. VALUES (?, (SELECT id FROM chart
  565. WHERE accno = ?),
  566. ?, ?, ?, ?, ?)|;
  567. $sth = $dbh->prepare($query);
  568. $sth->execute( $form->{id}, $accno, $form->{"paid_$i"},
  569. $form->{"datepaid_$i"},
  570. $form->{"source_$i"}, $form->{"memo_$i"}, $cleared )
  571. || $form->dberror($query);
  572. # exchangerate difference
  573. $amount = $form->round_amount(
  574. $form->{"paid_$i"} * $form->{"exchangerate_$i"} -
  575. $form->{"paid_$i"},
  576. 2
  577. );
  578. if ($amount) {
  579. $query = qq|
  580. INSERT INTO acc_trans
  581. (trans_id, chart_id, amount,
  582. transdate, source,
  583. fx_transaction, cleared)
  584. VALUES (?, (SELECT id FROM chart
  585. WHERE accno = ?),
  586. ?, ?, ?, '1', ?)|;
  587. $sth = $dbh->prepare($query);
  588. $sth->execute( $form->{id}, $accno, $amount,
  589. $form->{"datepaid_$i"},
  590. $form->{"source_$i"}, $cleared )
  591. || $form->dberror($query);
  592. }
  593. # gain/loss
  594. $amount = $form->round_amount(
  595. $form->round_amount( $form->{"paid_$i"} * $form->{exchangerate},
  596. 2 ) - $form->round_amount(
  597. $form->{"paid_$i"} * $form->{"exchangerate_$i"}, 2
  598. ),
  599. 2
  600. );
  601. if ($amount) {
  602. my $accno_id =
  603. ( $amount > 0 )
  604. ? $fxgain_accno_id
  605. : $fxloss_accno_id;
  606. $query = qq|
  607. INSERT INTO acc_trans
  608. (trans_id, chart_id, amount,
  609. transdate, fx_transaction,
  610. cleared)
  611. VALUES (?, ?, ?, ?, '1', ?)|;
  612. $sth = $dbh->prepare($query);
  613. $sth->execute( $form->{id}, $accno_id, $amount,
  614. $form->{"datepaid_$i"}, $cleared )
  615. || $form->dberror($query);
  616. }
  617. # update exchange rate
  618. if ( ( $form->{currency} ne $form->{defaultcurrency} )
  619. && !$exchangerate )
  620. {
  621. $form->update_exchangerate( $dbh, $form->{currency},
  622. $form->{"datepaid_$i"},
  623. 0, $form->{"exchangerate_$i"} );
  624. }
  625. }
  626. }
  627. # set values which could be empty
  628. $form->{taxincluded} *= 1;
  629. # save AP record
  630. $query = qq|
  631. UPDATE ap
  632. SET invnumber = ?,
  633. ordnumber = ?,
  634. quonumber = ?,
  635. transdate = ?,
  636. vendor_id = ?,
  637. amount = ?,
  638. netamount = ?,
  639. paid = ?,
  640. datepaid = ?,
  641. duedate = ?,
  642. invoice = '1',
  643. shippingpoint = ?,
  644. shipvia = ?,
  645. taxincluded = ?,
  646. notes = ?,
  647. intnotes = ?,
  648. curr = ?,
  649. department_id = ?,
  650. employee_id = ?,
  651. language_code = ?,
  652. ponumber = ?
  653. WHERE id = ?|;
  654. $sth = $dbh->prepare($query);
  655. $sth->execute(
  656. $form->{invnumber}, $form->{ordnumber}, $form->{quonumber},
  657. $form->{transdate}, $form->{vendor_id}, $invamount,
  658. $invnetamount, $form->{paid}, $form->{datepaid},
  659. $form->{duedate}, $form->{shippingpoint}, $form->{shipvia},
  660. $form->{taxincluded}, $form->{notes}, $form->{intnotes},
  661. $form->{currency}, $form->{department_id}, $form->{employee_id},
  662. $form->{language_code}, $form->{ponumber}, $form->{id}
  663. ) || $form->dberror($query);
  664. # add shipto
  665. $form->{name} = $form->{vendor};
  666. $form->{name} =~ s/--$form->{vendor_id}//;
  667. $form->add_shipto( $dbh, $form->{id} );
  668. my %audittrail = (
  669. tablename => 'ap',
  670. reference => $form->{invnumber},
  671. formname => $form->{type},
  672. action => 'posted',
  673. id => $form->{id}
  674. );
  675. $form->audittrail( $dbh, "", \%audittrail );
  676. foreach $item ( keys %updparts ) {
  677. $item = $dbh->quote($item);
  678. $query = qq|
  679. UPDATE parts
  680. SET avgcost = avgcost($item),
  681. lastcost = lastcost($item)
  682. WHERE id = $item|;
  683. $dbh->prepare($query) || $form->dberror($query);
  684. }
  685. my $rc = $dbh->commit;
  686. $rc;
  687. }
  688. sub reverse_invoice {
  689. my ( $dbh, $form ) = @_;
  690. my $query = qq|SELECT id FROM ap
  691. WHERE id = $form->{id}|;
  692. my ($id) = $dbh->selectrow_array($query);
  693. return unless $id;
  694. # reverse inventory items
  695. $query = qq|
  696. SELECT i.parts_id, p.inventory_accno_id, p.expense_accno_id,
  697. i.qty, i.allocated, i.sellprice, i.project_id
  698. FROM invoice i, parts p
  699. WHERE i.parts_id = p.id
  700. AND i.trans_id = ?|;
  701. my $sth = $dbh->prepare($query);
  702. $sth->execute( $form->{id} ) || $form->dberror($query);
  703. my $netamount = 0;
  704. while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  705. $netamount +=
  706. $form->round_amount( $ref->{sellprice} * $ref->{qty} * -1, 2 );
  707. if ( $ref->{inventory_accno_id} ) {
  708. # update onhand
  709. $form->update_balance( $dbh, "parts", "onhand",
  710. qq|id = $ref->{parts_id}|,
  711. $ref->{qty} );
  712. # if $ref->{allocated} > 0 than we sold that many items
  713. if ( $ref->{allocated} > 0 ) {
  714. # get references for sold items
  715. $query = qq|
  716. SELECT i.id, i.trans_id, i.allocated,
  717. a.transdate
  718. FROM invoice i, ar a
  719. WHERE i.parts_id = ?
  720. AND i.allocated < 0
  721. AND i.trans_id = a.id
  722. ORDER BY transdate DESC|;
  723. my $sth = $dbh->prepare($query);
  724. $sth->execute( $ref->{parts_id} )
  725. || $form->dberror($query);
  726. while ( my $pthref = $sth->fetchrow_hashref(NAME_lc) ) {
  727. my $qty = $ref->{allocated};
  728. if ( ( $ref->{allocated} + $pthref->{allocated} ) > 0 ) {
  729. $qty = $pthref->{allocated} * -1;
  730. }
  731. my $amount =
  732. $form->round_amount( $ref->{sellprice} * $qty, 2 );
  733. #adjust allocated
  734. $form->update_balance( $dbh, "invoice", "allocated",
  735. qq|id = $pthref->{id}|, $qty );
  736. # add reversal for sale
  737. $ref->{project_id} *= 1;
  738. $query = qq|
  739. INSERT INTO acc_trans
  740. (trans_id,
  741. chart_id, amount,
  742. transdate,
  743. project_id)
  744. VALUES (?, ?, ?, ?, ?)|;
  745. $sth = $dbh->prepare($query);
  746. $sth->execute( $pthref->{trans_id},
  747. $ref->{expense_accno_id},
  748. $amount, $form->{transdate}, $ref->{project_id} )
  749. || $form->dberror($query);
  750. $query = qq|
  751. INSERT INTO acc_trans
  752. (trans_id, chart_id,
  753. amount, transdate,
  754. project_id)
  755. VALUES (?, ?, ?, ?, ?)|;
  756. $sth = $dbh->do($query);
  757. $sth->execute(
  758. $pthref->{trans_id},
  759. $ref->{inventory_accno_id},
  760. $amount * -1,
  761. $form->{transdate}, $ref->{project_id}
  762. ) || $form->dberror($query);
  763. last if ( ( $ref->{allocated} -= $qty ) <= 0 );
  764. }
  765. $sth->finish;
  766. }
  767. }
  768. }
  769. $sth->finish;
  770. # delete acc_trans
  771. $query = qq|DELETE FROM acc_trans WHERE trans_id = ?|;
  772. $dbh->prepare($query);
  773. $sth->execute( $form->{id} ) || $form->dberror($query);
  774. # delete invoice entries
  775. $query = qq|DELETE FROM invoice WHERE trans_id = ?|;
  776. $dbh->prepare($query);
  777. $sth->execute( $form->{id} ) || $form->dberror($query);
  778. $query = qq|DELETE FROM shipto WHERE trans_id = ?|;
  779. $dbh->prepare($query);
  780. $sth->execute( $form->{id} ) || $form->dberror($query);
  781. $dbh->commit;
  782. }
  783. sub delete_invoice {
  784. my ( $self, $myconfig, $form ) = @_;
  785. # connect to database
  786. my $dbh = $form->{dbh};
  787. my %audittrail = (
  788. tablename => 'ap',
  789. reference => $form->{invnumber},
  790. formname => $form->{type},
  791. action => 'deleted',
  792. id => $form->{id}
  793. );
  794. $form->audittrail( $dbh, "", \%audittrail );
  795. my $query = qq|SELECT parts_id FROM invoice WHERE trans_id = ?|;
  796. my $sth = $dbh->prepare($query);
  797. $sth->execute( $form->{id} ) || $form->dberror($query);
  798. my $item;
  799. my %updparts = ();
  800. while ( ($item) = $sth->fetchrow_array ) {
  801. $updparts{$item} = 1;
  802. }
  803. $sth->finish;
  804. &reverse_invoice( $dbh, $form );
  805. # delete AP record
  806. $query = qq|DELETE FROM ap WHERE id = ?|;
  807. my $sth = $dbh->prepare($query);
  808. $sth->execute( $form->{id} ) || $form->dberror($query);
  809. # delete spool files
  810. $query = qq|
  811. SELECT spoolfile FROM status
  812. WHERE trans_id = ?
  813. AND spoolfile IS NOT NULL|;
  814. my $sth = $dbh->prepare($query);
  815. $sth->execute( $form->{id} ) || $form->dberror($query);
  816. my $spoolfile;
  817. my @spoolfiles = ();
  818. while ( ($spoolfile) = $sth->fetchrow_array ) {
  819. push @spoolfiles, $spoolfile;
  820. }
  821. $sth->finish;
  822. # delete status entries
  823. $query = qq|DELETE FROM status WHERE trans_id = ?|;
  824. my $sth = $dbh->prepare($query);
  825. $sth->execute( $form->{id} ) || $form->dberror($query);
  826. if ($rc) {
  827. foreach $item ( keys %updparts ) {
  828. $item = $dbh->quote($item);
  829. $query = qq|
  830. UPDATE parts
  831. SET avgcost = avgcost($item),
  832. lastcost = lastcost($item)
  833. WHERE id = $item|;
  834. $dbh->do($query) || $form->dberror($query);
  835. }
  836. foreach $spoolfile (@spoolfiles) {
  837. unlink "${LedgerSMB::Sysconfig::spool}/$spoolfile"
  838. if $spoolfile;
  839. }
  840. }
  841. my $rc = $dbh->commit;
  842. $rc;
  843. }
  844. sub retrieve_invoice {
  845. my ( $self, $myconfig, $form ) = @_;
  846. # connect to database
  847. my $dbh = $form->{dbh};
  848. my $query;
  849. if ( $form->{id} ) {
  850. # get default accounts and last invoice number
  851. $query = qq|
  852. SELECT (select c.accno FROM chart c
  853. WHERE c.id = (SELECT value FROM defaults
  854. WHERE setting_key =
  855. 'inventory_accno_id'))
  856. AS inventory_accno,
  857. (SELECT c.accno FROM chart c
  858. WHERE c.id = (SELECT value FROM defaults
  859. WHERE setting_key =
  860. 'income_accno_id'))
  861. AS income_accno,
  862. (SELECT c.accno FROM chart c
  863. WHERE c.id = (SELECT value FROM defaults
  864. WHERE setting_key =
  865. 'expense_accno_id'))
  866. AS expense_accno,
  867. (SELECT c.accno FROM chart c
  868. WHERE c.id = (SELECT value FROM defaults
  869. WHERE setting_key =
  870. 'fxgain_accno_id'))
  871. AS fxgain_accno,
  872. (SELECT c.accno FROM chart c
  873. WHERE c.id = (SELECT value FROM defaults
  874. WHERE setting_key =
  875. 'fxloss_accno_id'))
  876. AS fxloss_accno,
  877. (SELECT value FROM defaults
  878. WHERE setting_key = 'curr') AS currencies|;
  879. }
  880. else {
  881. $query = qq|
  882. SELECT (select c.accno FROM chart c
  883. WHERE c.id = (SELECT value FROM defaults
  884. WHERE setting_key =
  885. 'inventory_accno_id'))
  886. AS inventory_accno,
  887. (SELECT c.accno FROM chart c
  888. WHERE c.id = (SELECT value FROM defaults
  889. WHERE setting_key =
  890. 'income_accno_id'))
  891. AS income_accno,
  892. (SELECT c.accno FROM chart c
  893. WHERE c.id = (SELECT value FROM defaults
  894. WHERE setting_key =
  895. 'expense_accno_id'))
  896. AS expense_accno,
  897. (SELECT c.accno FROM chart c
  898. WHERE c.id = (SELECT value FROM defaults
  899. WHERE setting_key =
  900. 'fxgain_accno_id'))
  901. AS fxgain_accno,
  902. (SELECT c.accno FROM chart c
  903. WHERE c.id = (SELECT value FROM defaults
  904. WHERE setting_key =
  905. 'fxloss_accno_id'))
  906. AS fxloss_accno,
  907. (SELECT value FROM defaults
  908. WHERE setting_key = 'curr') AS currencies,
  909. current_date AS transdate|;
  910. }
  911. my $sth = $dbh->prepare($query);
  912. $sth->execute || $form->dberror($query);
  913. my $ref = $sth->fetchrow_hashref(NAME_lc);
  914. for ( keys %$ref ) {
  915. $form->{$_} = $ref->{$_};
  916. }
  917. $sth->finish;
  918. if ( $form->{id} ) {
  919. $query = qq|
  920. SELECT a.invnumber, a.transdate, a.duedate,
  921. a.ordnumber, a.quonumber, a.paid, a.taxincluded,
  922. a.notes, a.intnotes, a.curr AS currency,
  923. a.vendor_id, a.language_code, a.ponumber,
  924. a.on_hold, a.reverse
  925. FROM ap a
  926. WHERE id = ?|;
  927. $sth = $dbh->prepare($query);
  928. $sth->execute( $form->{id} ) || $form->dberror($query);
  929. $ref = $sth->fetchrow_hashref(NAME_lc);
  930. $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
  931. for ( keys %$ref ) {
  932. $form->{$_} = $ref->{$_};
  933. }
  934. $sth->finish;
  935. # get shipto
  936. $query = qq|SELECT * FROM shipto WHERE trans_id = ?|;
  937. $sth = $dbh->prepare($query);
  938. $sth->execute( $form->{id} ) || $form->dberror($query);
  939. $ref = $sth->fetchrow_hashref(NAME_lc);
  940. for ( keys %$ref ) {
  941. $form->{$_} = $ref->{$_};
  942. }
  943. $sth->finish;
  944. # retrieve individual items
  945. $query = qq|
  946. SELECT p.partnumber, i.description, i.qty,
  947. i.fxsellprice, i.sellprice,
  948. i.parts_id AS id, i.unit, p.bin,
  949. i.deliverydate,
  950. pr.projectnumber, i.project_id,
  951. i.serialnumber,
  952. i.discount, i.notes, pg.partsgroup,
  953. p.partsgroup_id, p.partnumber AS sku,
  954. p.weight, p.onhand, p.inventory_accno_id,
  955. p.income_accno_id, p.expense_accno_id,
  956. t.description AS partsgrouptranslation
  957. FROM invoice i
  958. JOIN parts p ON (i.parts_id = p.id)
  959. LEFT JOIN project pr ON (i.project_id = pr.id)
  960. LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
  961. LEFT JOIN translation t
  962. ON (t.trans_id = p.partsgroup_id
  963. AND t.language_code = ?)
  964. WHERE i.trans_id = ?
  965. ORDER BY i.id|;
  966. $sth = $dbh->prepare($query);
  967. $sth->execute( $form->{language_code}, $form->{id} )
  968. || $form->dberror($query);
  969. # exchangerate defaults
  970. &exchangerate_defaults( $dbh, $form );
  971. # price matrix and vendor partnumber
  972. my $pmh = PriceMatrix::price_matrix_query( $dbh, $form );
  973. # tax rates for part
  974. $query = qq|
  975. SELECT c.accno
  976. FROM chart c
  977. JOIN partstax pt ON (pt.chart_id = c.id)
  978. WHERE pt.parts_id = ?|;
  979. my $tth = $dbh->prepare($query);
  980. my $ptref;
  981. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  982. $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
  983. $ref->{qty} *= -1 if $form->{reverse};
  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. sub toggle_on_hold {
  1191. my $self = shift @_;
  1192. my $form = shift @_;
  1193. if ($form->{id}) { # it's an existing (.. probably) invoice.
  1194. my $dbh = $form->{dbh};
  1195. my $sth = $dbh->prepare("SELECT on_hold from ar where ar.id = ?");
  1196. $sth->execute($form->{id});
  1197. my $state = $sth->fetchrow_array;
  1198. my $sth;
  1199. my $n_s; # new state
  1200. if ($state[0] == 't') {
  1201. # Turn it off
  1202. $n_s = 'f';
  1203. } else {
  1204. $n_s = 't';
  1205. }
  1206. my $sth = $dbh->prepare("update ar set on_hold = ?::boolean where ar.id = ?");
  1207. my $code = $dbh->execute($ns, $form->{id});
  1208. return 1;
  1209. } else { # This shouldn't even be possible, but check for it anyway.
  1210. # Definitely, DEFINITELY check it.
  1211. # happily return 0. Find out about proper error states.
  1212. return 0;
  1213. }
  1214. }
  1215. 1;