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