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