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