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