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