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