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