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