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