summaryrefslogtreecommitdiff
path: root/LedgerSMB/OE.pm
blob: ae4cd635e61a2b00b6a222bd30e637606f3a17b3 (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) 2001
  18. #
  19. # Author: DWS Systems Inc.
  20. # Web: http://www.sql-ledger.org
  21. #
  22. # Contributors:
  23. #
  24. #======================================================================
  25. #
  26. # This file has NOT undergone whitespace cleanup.
  27. #
  28. #======================================================================
  29. #
  30. # Order entry module
  31. # Quotation
  32. #
  33. #======================================================================
  34. package OE;
  35. sub transactions {
  36. my ($self, $myconfig, $form) = @_;
  37. # connect to database
  38. my $dbh = $form->dbconnect($myconfig);
  39. my $query;
  40. my $null;
  41. my $var;
  42. my $ordnumber = 'ordnumber';
  43. my $quotation = '0';
  44. my $department;
  45. my $rate = ($form->{vc} eq 'customer') ? 'buy' : 'sell';
  46. ($form->{transdatefrom}, $form->{transdateto}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month};
  47. if ($form->{type} =~ /_quotation$/) {
  48. $quotation = '1';
  49. $ordnumber = 'quonumber';
  50. }
  51. my $number = $form->like(lc $form->{$ordnumber});
  52. my $name = $form->like(lc $form->{$form->{vc}});
  53. for (qw(department employee)) {
  54. if ($form->{$_}) {
  55. ($null, $var) = split /--/, $form->{$_};
  56. $department .= " AND o.${_}_id = $var";
  57. }
  58. }
  59. my $query = qq|SELECT o.id, o.ordnumber, o.transdate, o.reqdate,
  60. o.amount, ct.name, o.netamount, o.$form->{vc}_id,
  61. ex.$rate AS exchangerate,
  62. o.closed, o.quonumber, o.shippingpoint, o.shipvia,
  63. e.name AS employee, m.name AS manager, o.curr, o.ponumber
  64. FROM oe o
  65. JOIN $form->{vc} ct ON (o.$form->{vc}_id = ct.id)
  66. LEFT JOIN employee e ON (o.employee_id = e.id)
  67. LEFT JOIN employee m ON (e.managerid = m.id)
  68. LEFT JOIN exchangerate ex ON (ex.curr = o.curr
  69. AND ex.transdate = o.transdate)
  70. WHERE o.quotation = '$quotation'
  71. $department|;
  72. my %ordinal = ( id => 1,
  73. ordnumber => 2,
  74. transdate => 3,
  75. reqdate => 4,
  76. name => 6,
  77. quonumber => 11,
  78. shipvia => 13,
  79. employee => 14,
  80. manager => 15,
  81. curr => 16,
  82. ponumber => 17
  83. );
  84. my @a = (transdate, $ordnumber, name);
  85. push @a, "employee" if $form->{l_employee};
  86. if ($form->{type} !~ /(ship|receive)_order/) {
  87. push @a, "manager" if $form->{l_manager};
  88. }
  89. my $sortorder = $form->sort_order(\@a, \%ordinal);
  90. # build query if type eq (ship|receive)_order
  91. if ($form->{type} =~ /(ship|receive)_order/) {
  92. my ($warehouse, $warehouse_id) = split /--/, $form->{warehouse};
  93. $query = qq|SELECT DISTINCT o.id, o.ordnumber, o.transdate,
  94. o.reqdate, o.amount, ct.name, o.netamount, o.$form->{vc}_id,
  95. ex.$rate AS exchangerate,
  96. o.closed, o.quonumber, o.shippingpoint, o.shipvia,
  97. e.name AS employee, o.curr, o.ponumber
  98. FROM oe o
  99. JOIN $form->{vc} ct ON (o.$form->{vc}_id = ct.id)
  100. JOIN orderitems oi ON (oi.trans_id = o.id)
  101. JOIN parts p ON (p.id = oi.parts_id)|;
  102. if ($warehouse_id && $form->{type} eq 'ship_order') {
  103. $query .= qq|
  104. JOIN inventory i ON (oi.parts_id = i.parts_id)
  105. |;
  106. }
  107. $query .= qq|
  108. LEFT JOIN employee e ON (o.employee_id = e.id)
  109. LEFT JOIN exchangerate ex ON (ex.curr = o.curr
  110. AND ex.transdate = o.transdate)
  111. WHERE o.quotation = '0'
  112. AND (p.inventory_accno_id > 0 OR p.assembly = '1')
  113. AND oi.qty != oi.ship
  114. $department|;
  115. if ($warehouse_id && $form->{type} eq 'ship_order') {
  116. $query .= qq|
  117. AND i.warehouse_id = $warehouse_id
  118. AND ( SELECT SUM(i.qty)
  119. FROM inventory i
  120. WHERE oi.parts_id = i.parts_id
  121. AND i.warehouse_id = $warehouse_id ) > 0
  122. |;
  123. }
  124. }
  125. if ($form->{"$form->{vc}_id"}) {
  126. $query .= qq| AND o.$form->{vc}_id = $form->{"$form->{vc}_id"}|;
  127. } else {
  128. if ($form->{$form->{vc}} ne "") {
  129. $query .= " AND lower(ct.name) LIKE '$name'";
  130. }
  131. }
  132. if ($form->{$ordnumber} ne "") {
  133. $query .= " AND lower($ordnumber) LIKE '$number'";
  134. $form->{open} = 1;
  135. $form->{closed} = 1;
  136. }
  137. if ($form->{ponumber} ne "") {
  138. $query .= " AND lower(ponumber) LIKE '$form->{ponumber}'";
  139. }
  140. if (!$form->{open} && !$form->{closed}) {
  141. $query .= " AND o.id = 0";
  142. } elsif (!($form->{open} && $form->{closed})) {
  143. $query .= ($form->{open}) ? " AND o.closed = '0'" : " AND o.closed = '1'";
  144. }
  145. if ($form->{shipvia} ne "") {
  146. $var = $form->like(lc $form->{shipvia});
  147. $query .= " AND lower(o.shipvia) LIKE '$var'";
  148. }
  149. if ($form->{description} ne "") {
  150. $var = $form->like(lc $form->{description});
  151. $query .= " AND o.id IN (SELECT DISTINCT trans_id
  152. FROM orderitems
  153. WHERE lower(description) LIKE '$var')";
  154. }
  155. if ($form->{transdatefrom}) {
  156. $query .= " AND o.transdate >= '$form->{transdatefrom}'";
  157. }
  158. if ($form->{transdateto}) {
  159. $query .= " AND o.transdate <= '$form->{transdateto}'";
  160. }
  161. $query .= " ORDER by $sortorder";
  162. my $sth = $dbh->prepare($query);
  163. $sth->execute || $form->dberror($query);
  164. my %oid = ();
  165. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  166. $ref->{exchangerate} = 1 unless $ref->{exchangerate};
  167. if ($ref->{id} != $oid{id}{$ref->{id}}) {
  168. push @{ $form->{OE} }, $ref;
  169. $oid{vc}{$ref->{curr}}{$ref->{"$form->{vc}_id"}}++;
  170. }
  171. $oid{id}{$ref->{id}} = $ref->{id};
  172. }
  173. $sth->finish;
  174. $dbh->disconnect;
  175. if ($form->{type} =~ /^consolidate_/) {
  176. @a = ();
  177. foreach $ref (@{ $form->{OE} }) { push @a, $ref if $oid{vc}{$ref->{curr}}{$ref->{"$form->{vc}_id"}} > 1 }
  178. @{ $form->{OE} } = @a;
  179. }
  180. }
  181. sub save {
  182. my ($self, $myconfig, $form) = @_;
  183. # connect to database, turn off autocommit
  184. my $dbh = $form->dbconnect_noauto($myconfig);
  185. my $query;
  186. my $sth;
  187. my $null;
  188. my $exchangerate = 0;
  189. ($null, $form->{employee_id}) = split /--/, $form->{employee};
  190. if (! $form->{employee_id}) {
  191. ($form->{employee}, $form->{employee_id}) = $form->get_employee($dbh);
  192. $form->{employee} = "$form->{employee}--$form->{employee_id}";
  193. }
  194. my $ml = ($form->{type} eq 'sales_order') ? 1 : -1;
  195. $query = qq|SELECT p.assembly, p.project_id
  196. FROM parts p
  197. WHERE p.id = ?|;
  198. my $pth = $dbh->prepare($query) || $form->dberror($query);
  199. if ($form->{id}) {
  200. $query = qq|SELECT id FROM oe
  201. WHERE id = $form->{id}|;
  202. if ($dbh->selectrow_array($query)) {
  203. &adj_onhand($dbh, $form, $ml) if $form->{type} =~ /_order$/;
  204. $query = qq|DELETE FROM orderitems
  205. WHERE trans_id = $form->{id}|;
  206. $dbh->do($query) || $form->dberror($query);
  207. $query = qq|DELETE FROM shipto
  208. WHERE trans_id = $form->{id}|;
  209. $dbh->do($query) || $form->dberror($query);
  210. } else {
  211. $query = qq|INSERT INTO oe (id)
  212. VALUES ($form->{id})|;
  213. $dbh->do($query) || $form->dberror($query);
  214. }
  215. }
  216. if (! $form->{id}) {
  217. my $uid = localtime;
  218. $uid .= "$$";
  219. $query = qq|INSERT INTO oe (ordnumber, employee_id)
  220. VALUES ('$uid', $form->{employee_id})|;
  221. $dbh->do($query) || $form->dberror($query);
  222. $query = qq|SELECT id FROM oe
  223. WHERE ordnumber = '$uid'|;
  224. $sth = $dbh->prepare($query);
  225. $sth->execute || $form->dberror($query);
  226. ($form->{id}) = $sth->fetchrow_array;
  227. $sth->finish;
  228. }
  229. my $amount;
  230. my $linetotal;
  231. my $discount;
  232. my $project_id;
  233. my $taxrate;
  234. my $taxamount;
  235. my $fxsellprice;
  236. my %taxbase;
  237. my @taxaccounts;
  238. my %taxaccounts;
  239. my $netamount = 0;
  240. for my $i (1 .. $form->{rowcount}) {
  241. for (qw(qty ship)) { $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) }
  242. $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
  243. $form->{"sellprice_$i"} = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
  244. if ($form->{"qty_$i"}) {
  245. $pth->execute($form->{"id_$i"});
  246. $ref = $pth->fetchrow_hashref(NAME_lc);
  247. for (keys %$ref) { $form->{"${_}_$i"} = $ref->{$_} }
  248. $pth->finish;
  249. $fxsellprice = $form->{"sellprice_$i"};
  250. my ($dec) = ($form->{"sellprice_$i"} =~ /\.(\d+)/);
  251. $dec = length $dec;
  252. my $decimalplaces = ($dec > 2) ? $dec : 2;
  253. $discount = $form->round_amount($form->{"sellprice_$i"} * $form->{"discount_$i"}, $decimalplaces);
  254. $form->{"sellprice_$i"} = $form->round_amount($form->{"sellprice_$i"} - $discount, $decimalplaces);
  255. $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2);
  256. @taxaccounts = split / /, $form->{"taxaccounts_$i"};
  257. $taxrate = 0;
  258. $taxdiff = 0;
  259. for (@taxaccounts) { $taxrate += $form->{"${_}_rate"} }
  260. if ($form->{taxincluded}) {
  261. $taxamount = $linetotal * $taxrate / (1 + $taxrate);
  262. $taxbase = $linetotal - $taxamount;
  263. # we are not keeping a natural price, do not round
  264. $form->{"sellprice_$i"} = $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
  265. } else {
  266. $taxamount = $linetotal * $taxrate;
  267. $taxbase = $linetotal;
  268. }
  269. if (@taxaccounts && $form->round_amount($taxamount, 2) == 0) {
  270. if ($form->{taxincluded}) {
  271. foreach $item (@taxaccounts) {
  272. $taxamount = $form->round_amount($linetotal * $form->{"${item}_rate"} / (1 + abs($form->{"${item}_rate"})), 2);
  273. $taxaccounts{$item} += $taxamount;
  274. $taxdiff += $taxamount;
  275. $taxbase{$item} += $taxbase;
  276. }
  277. $taxaccounts{$taxaccounts[0]} += $taxdiff;
  278. } else {
  279. foreach $item (@taxaccounts) {
  280. $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
  281. $taxbase{$item} += $taxbase;
  282. }
  283. }
  284. } else {
  285. foreach $item (@taxaccounts) {
  286. $taxaccounts{$item} += $taxamount * $form->{"${item}_rate"} / $taxrate;
  287. $taxbase{$item} += $taxbase;
  288. }
  289. }
  290. $netamount += $form->{"sellprice_$i"} * $form->{"qty_$i"};
  291. $project_id = 'NULL';
  292. if ($form->{"projectnumber_$i"} ne "") {
  293. ($null, $project_id) = split /--/, $form->{"projectnumber_$i"};
  294. }
  295. $project_id = $form->{"project_id_$i"} if $form->{"project_id_$i"};
  296. # save detail record in orderitems table
  297. $query = qq|INSERT INTO orderitems (|;
  298. $query .= "id, " if $form->{"orderitems_id_$i"};
  299. $query .= qq|trans_id, parts_id, description, qty, sellprice, discount,
  300. unit, reqdate, project_id, ship, serialnumber, notes)
  301. VALUES (|;
  302. $query .= qq|$form->{"orderitems_id_$i"},| if $form->{"orderitems_id_$i"};
  303. $query .= qq|$form->{id}, $form->{"id_$i"}, |
  304. .$dbh->quote($form->{"description_$i"}).qq|,
  305. $form->{"qty_$i"}, $fxsellprice, $form->{"discount_$i"}, |
  306. .$dbh->quote($form->{"unit_$i"}).qq|, |
  307. .$form->dbquote($form->{"reqdate_$i"}, SQL_DATE).qq|,
  308. $project_id, $form->{"ship_$i"}, |
  309. .$dbh->quote($form->{"serialnumber_$i"}).qq|, |
  310. .$dbh->quote($form->{"notes_$i"}).qq|)|;
  311. $dbh->do($query) || $form->dberror($query);
  312. $form->{"sellprice_$i"} = $fxsellprice;
  313. }
  314. $form->{"discount_$i"} *= 100;
  315. }
  316. # set values which could be empty
  317. for (qw(vendor_id customer_id taxincluded closed quotation)) { $form->{$_} *= 1 }
  318. # add up the tax
  319. my $tax = 0;
  320. for (keys %taxaccounts) { $tax += $taxaccounts{$_} }
  321. $amount = $form->round_amount($netamount + $tax, 2);
  322. $netamount = $form->round_amount($netamount, 2);
  323. if ($form->{currency} eq $form->{defaultcurrency}) {
  324. $form->{exchangerate} = 1;
  325. } else {
  326. $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? 'buy' : 'sell');
  327. }
  328. $form->{exchangerate} = ($exchangerate) ? $exchangerate : $form->parse_amount($myconfig, $form->{exchangerate});
  329. my $quotation;
  330. my $ordnumber;
  331. my $numberfld;
  332. if ($form->{type} =~ /_order$/) {
  333. $quotation = "0";
  334. $ordnumber = "ordnumber";
  335. $numberfld = ($form->{vc} eq 'customer') ? "sonumber" : "ponumber";
  336. } else {
  337. $quotation = "1";
  338. $ordnumber = "quonumber";
  339. $numberfld = ($form->{vc} eq 'customer') ? "sqnumber" : "rfqnumber";
  340. }
  341. $form->{$ordnumber} = $form->update_defaults($myconfig, $numberfld, $dbh) unless $form->{$ordnumber};
  342. ($null, $form->{department_id}) = split(/--/, $form->{department});
  343. for (qw(department_id terms)) { $form->{$_} *= 1 }
  344. # save OE record
  345. $query = qq|UPDATE oe set
  346. ordnumber = |.$dbh->quote($form->{ordnumber}).qq|,
  347. quonumber = |.$dbh->quote($form->{quonumber}).qq|,
  348. transdate = '$form->{transdate}',
  349. vendor_id = $form->{vendor_id},
  350. customer_id = $form->{customer_id},
  351. amount = $amount,
  352. netamount = $netamount,
  353. reqdate = |.$form->dbquote($form->{reqdate}, SQL_DATE).qq|,
  354. taxincluded = '$form->{taxincluded}',
  355. shippingpoint = |.$dbh->quote($form->{shippingpoint}).qq|,
  356. shipvia = |.$dbh->quote($form->{shipvia}).qq|,
  357. notes = |.$dbh->quote($form->{notes}).qq|,
  358. intnotes = |.$dbh->quote($form->{intnotes}).qq|,
  359. curr = '$form->{currency}',
  360. closed = '$form->{closed}',
  361. quotation = '$quotation',
  362. department_id = $form->{department_id},
  363. employee_id = $form->{employee_id},
  364. language_code = '$form->{language_code}',
  365. ponumber = |.$dbh->quote($form->{ponumber}).qq|,
  366. terms = $form->{terms}
  367. WHERE id = $form->{id}|;
  368. $dbh->do($query) || $form->dberror($query);
  369. $form->{ordtotal} = $amount;
  370. # add shipto
  371. $form->{name} = $form->{$form->{vc}};
  372. $form->{name} =~ s/--$form->{"$form->{vc}_id"}//;
  373. $form->add_shipto($dbh, $form->{id});
  374. # save printed, emailed, queued
  375. $form->save_status($dbh);
  376. if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
  377. if ($form->{vc} eq 'customer') {
  378. $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, $form->{exchangerate}, 0);
  379. }
  380. if ($form->{vc} eq 'vendor') {
  381. $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0, $form->{exchangerate});
  382. }
  383. }
  384. if ($form->{type} =~ /_order$/) {
  385. # adjust onhand
  386. &adj_onhand($dbh, $form, $ml * -1);
  387. &adj_inventory($dbh, $myconfig, $form);
  388. }
  389. my %audittrail = ( tablename => 'oe',
  390. reference => ($form->{type} =~ /_order$/) ? $form->{ordnumber} : $form->{quonumber},
  391. formname => $form->{type},
  392. action => 'saved',
  393. id => $form->{id} );
  394. $form->audittrail($dbh, "", \%audittrail);
  395. $form->save_recurring($dbh, $myconfig);
  396. my $rc = $dbh->commit;
  397. $dbh->disconnect;
  398. $rc;
  399. }
  400. sub delete {
  401. my ($self, $myconfig, $form, $spool) = @_;
  402. # connect to database
  403. my $dbh = $form->dbconnect_noauto($myconfig);
  404. # delete spool files
  405. my $query = qq|SELECT spoolfile FROM status
  406. WHERE trans_id = $form->{id}
  407. AND spoolfile IS NOT NULL|;
  408. $sth = $dbh->prepare($query);
  409. $sth->execute || $form->dberror($query);
  410. my $spoolfile;
  411. my @spoolfiles = ();
  412. while (($spoolfile) = $sth->fetchrow_array) {
  413. push @spoolfiles, $spoolfile;
  414. }
  415. $sth->finish;
  416. $query = qq|SELECT o.parts_id, o.ship, p.inventory_accno_id, p.assembly
  417. FROM orderitems o
  418. JOIN parts p ON (p.id = o.parts_id)
  419. WHERE trans_id = $form->{id}|;
  420. $sth = $dbh->prepare($query);
  421. $sth->execute || $form->dberror($query);
  422. if ($form->{type} =~ /_order$/) {
  423. $ml = ($form->{type} eq 'purchase_order') ? -1 : 1;
  424. while (my ($id, $ship, $inv, $assembly) = $sth->fetchrow_array) {
  425. $form->update_balance($dbh,
  426. "parts",
  427. "onhand",
  428. qq|id = $id|,
  429. $ship * $ml) if ($inv || $assembly);
  430. }
  431. }
  432. $sth->finish;
  433. # delete inventory
  434. $query = qq|DELETE FROM inventory
  435. WHERE trans_id = $form->{id}|;
  436. $dbh->do($query) || $form->dberror($query);
  437. # delete status entries
  438. $query = qq|DELETE FROM status
  439. WHERE trans_id = $form->{id}|;
  440. $dbh->do($query) || $form->dberror($query);
  441. # delete OE record
  442. $query = qq|DELETE FROM oe
  443. WHERE id = $form->{id}|;
  444. $dbh->do($query) || $form->dberror($query);
  445. # delete individual entries
  446. $query = qq|DELETE FROM orderitems
  447. WHERE trans_id = $form->{id}|;
  448. $dbh->do($query) || $form->dberror($query);
  449. $query = qq|DELETE FROM shipto
  450. WHERE trans_id = $form->{id}|;
  451. $dbh->do($query) || $form->dberror($query);
  452. my %audittrail = ( tablename => 'oe',
  453. reference => ($form->{type} =~ /_order$/) ? $form->{ordnumber} : $form->{quonumber},
  454. formname => $form->{type},
  455. action => 'deleted',
  456. id => $form->{id} );
  457. $form->audittrail($dbh, "", \%audittrail);
  458. my $rc = $dbh->commit;
  459. $dbh->disconnect;
  460. if ($rc) {
  461. foreach $spoolfile (@spoolfiles) {
  462. unlink "$spool/$spoolfile" if $spoolfile;
  463. }
  464. }
  465. $rc;
  466. }
  467. sub retrieve {
  468. my ($self, $myconfig, $form) = @_;
  469. # connect to database
  470. my $dbh = $form->dbconnect($myconfig);
  471. my $query;
  472. my $sth;
  473. my $var;
  474. my $ref;
  475. $query = qq|SELECT curr, current_date
  476. FROM defaults|;
  477. ($form->{currencies}, $form->{transdate}) = $dbh->selectrow_array($query);
  478. if ($form->{id}) {
  479. # retrieve order
  480. $query = qq|SELECT o.ordnumber, o.transdate, o.reqdate, o.terms,
  481. o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes,
  482. o.curr AS currency, e.name AS employee, o.employee_id,
  483. o.$form->{vc}_id, vc.name AS $form->{vc}, o.amount AS invtotal,
  484. o.closed, o.reqdate, o.quonumber, o.department_id,
  485. d.description AS department, o.language_code, o.ponumber
  486. FROM oe o
  487. JOIN $form->{vc} vc ON (o.$form->{vc}_id = vc.id)
  488. LEFT JOIN employee e ON (o.employee_id = e.id)
  489. LEFT JOIN department d ON (o.department_id = d.id)
  490. WHERE o.id = $form->{id}|;
  491. $sth = $dbh->prepare($query);
  492. $sth->execute || $form->dberror($query);
  493. $ref = $sth->fetchrow_hashref(NAME_lc);
  494. for (keys %$ref) { $form->{$_} = $ref->{$_} }
  495. $sth->finish;
  496. $query = qq|SELECT * FROM shipto
  497. WHERE trans_id = $form->{id}|;
  498. $sth = $dbh->prepare($query);
  499. $sth->execute || $form->dberror($query);
  500. $ref = $sth->fetchrow_hashref(NAME_lc);
  501. for (keys %$ref) { $form->{$_} = $ref->{$_} }
  502. $sth->finish;
  503. # get printed, emailed and queued
  504. $query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname
  505. FROM status s
  506. WHERE s.trans_id = $form->{id}|;
  507. $sth = $dbh->prepare($query);
  508. $sth->execute || $form->dberror($query);
  509. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  510. $form->{printed} .= "$ref->{formname} " if $ref->{printed};
  511. $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
  512. $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
  513. }
  514. $sth->finish;
  515. for (qw(printed emailed queued)) { $form->{$_} =~ s/ +$//g }
  516. my %oid = ( 'Pg' => 'id',
  517. 'Oracle' => 'rowid',
  518. 'DB2' => '1=1'
  519. );
  520. # retrieve individual items
  521. $query = qq|SELECT o.id AS orderitems_id,
  522. p.partnumber, p.assembly, o.description, o.qty,
  523. o.sellprice, o.parts_id AS id, o.unit, o.discount, p.bin,
  524. o.reqdate, o.project_id, o.ship, o.serialnumber, o.notes,
  525. pr.projectnumber,
  526. pg.partsgroup, p.partsgroup_id, p.partnumber AS sku,
  527. p.listprice, p.lastcost, p.weight, p.onhand,
  528. p.inventory_accno_id, p.income_accno_id, p.expense_accno_id,
  529. t.description AS partsgrouptranslation
  530. FROM orderitems o
  531. JOIN parts p ON (o.parts_id = p.id)
  532. LEFT JOIN project pr ON (o.project_id = pr.id)
  533. LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
  534. LEFT JOIN translation t ON (t.trans_id = p.partsgroup_id AND t.language_code = '$form->{language_code}')
  535. WHERE o.trans_id = $form->{id}
  536. ORDER BY o.$oid{$myconfig->{dbdriver}}|;
  537. $sth = $dbh->prepare($query);
  538. $sth->execute || $form->dberror($query);
  539. # foreign exchange rates
  540. &exchangerate_defaults($dbh, $form);
  541. # query for price matrix
  542. my $pmh = &price_matrix_query($dbh, $form);
  543. # taxes
  544. $query = qq|SELECT c.accno
  545. FROM chart c
  546. JOIN partstax pt ON (pt.chart_id = c.id)
  547. WHERE pt.parts_id = ?|;
  548. my $tth = $dbh->prepare($query) || $form->dberror($query);
  549. my $taxrate;
  550. my $ptref;
  551. my $sellprice;
  552. my $listprice;
  553. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  554. ($decimalplaces) = ($ref->{sellprice} =~ /\.(\d+)/);
  555. $decimalplaces = length $decimalplaces;
  556. $decimalplaces = ($decimalplaces > 2) ? $decimalplaces : 2;
  557. $tth->execute($ref->{id});
  558. $ref->{taxaccounts} = "";
  559. $taxrate = 0;
  560. while ($ptref = $tth->fetchrow_hashref(NAME_lc)) {
  561. $ref->{taxaccounts} .= "$ptref->{accno} ";
  562. $taxrate += $form->{"$ptref->{accno}_rate"};
  563. }
  564. $tth->finish;
  565. chop $ref->{taxaccounts};
  566. # preserve price
  567. $sellprice = $ref->{sellprice};
  568. # multiply by exchangerate
  569. $ref->{sellprice} = $form->round_amount($ref->{sellprice} * $form->{$form->{currency}}, $decimalplaces);
  570. for (qw(listprice lastcost)) { $ref->{$_} = $form->round_amount($ref->{$_} / $form->{$form->{currency}}, $decimalplaces) }
  571. # partnumber and price matrix
  572. &price_matrix($pmh, $ref, $form->{transdate}, $decimalplaces, $form, $myconfig);
  573. $ref->{sellprice} = $sellprice;
  574. $ref->{partsgroup} = $ref->{partsgrouptranslation} if $ref->{partsgrouptranslation};
  575. push @{ $form->{form_details} }, $ref;
  576. }
  577. $sth->finish;
  578. # get recurring transaction
  579. $form->get_recurring($dbh);
  580. } else {
  581. # get last name used
  582. $form->lastname_used($myconfig, $dbh, $form->{vc}) unless $form->{"$form->{vc}_id"};
  583. delete $form->{notes};
  584. }
  585. $dbh->disconnect;
  586. }
  587. sub price_matrix_query {
  588. my ($dbh, $form) = @_;
  589. my $query;
  590. my $sth;
  591. if ($form->{customer_id}) {
  592. $query = qq|SELECT p.id AS parts_id, 0 AS customer_id, 0 AS pricegroup_id,
  593. 0 AS pricebreak, p.sellprice, NULL AS validfrom, NULL AS validto,
  594. '$form->{defaultcurrency}' AS curr, '' AS pricegroup
  595. FROM parts p
  596. WHERE p.id = ?
  597. UNION
  598. SELECT p.*, g.pricegroup
  599. FROM partscustomer p
  600. LEFT JOIN pricegroup g ON (g.id = p.pricegroup_id)
  601. WHERE p.parts_id = ?
  602. AND p.customer_id = $form->{customer_id}
  603. UNION
  604. SELECT p.*, g.pricegroup
  605. FROM partscustomer p
  606. LEFT JOIN pricegroup g ON (g.id = p.pricegroup_id)
  607. JOIN customer c ON (c.pricegroup_id = g.id)
  608. WHERE p.parts_id = ?
  609. AND c.id = $form->{customer_id}
  610. UNION
  611. SELECT p.*, '' AS pricegroup
  612. FROM partscustomer p
  613. WHERE p.customer_id = 0
  614. AND p.pricegroup_id = 0
  615. AND p.parts_id = ?
  616. ORDER BY customer_id DESC, pricegroup_id DESC, pricebreak
  617. |;
  618. $sth = $dbh->prepare($query) || $form->dberror($query);
  619. }
  620. if ($form->{vendor_id}) {
  621. # price matrix and vendor's partnumber
  622. $query = qq|SELECT partnumber
  623. FROM partsvendor
  624. WHERE parts_id = ?
  625. AND vendor_id = $form->{vendor_id}|;
  626. $sth = $dbh->prepare($query) || $form->dberror($query);
  627. }
  628. $sth;
  629. }
  630. sub price_matrix {
  631. my ($pmh, $ref, $transdate, $decimalplaces, $form, $myconfig) = @_;
  632. $ref->{pricematrix} = "";
  633. my $customerprice;
  634. my $pricegroupprice;
  635. my $sellprice;
  636. my $mref;
  637. my %p = ();
  638. # depends if this is a customer or vendor
  639. if ($form->{customer_id}) {
  640. $pmh->execute($ref->{id}, $ref->{id}, $ref->{id}, $ref->{id});
  641. while ($mref = $pmh->fetchrow_hashref(NAME_lc)) {
  642. # check date
  643. if ($mref->{validfrom}) {
  644. next if $transdate < $form->datetonum($myconfig, $mref->{validfrom});
  645. }
  646. if ($mref->{validto}) {
  647. next if $transdate > $form->datetonum($myconfig, $mref->{validto});
  648. }
  649. # convert price
  650. $sellprice = $form->round_amount($mref->{sellprice} * $form->{$mref->{curr}}, $decimalplaces);
  651. if ($mref->{customer_id}) {
  652. $ref->{sellprice} = $sellprice if !$mref->{pricebreak};
  653. $p{$mref->{pricebreak}} = $sellprice;
  654. $customerprice = 1;
  655. }
  656. if ($mref->{pricegroup_id}) {
  657. if (! $customerprice) {
  658. $ref->{sellprice} = $sellprice if !$mref->{pricebreak};
  659. $p{$mref->{pricebreak}} = $sellprice;
  660. }
  661. $pricegroupprice = 1;
  662. }
  663. if (!$customerprice && !$pricegroupprice) {
  664. $p{$mref->{pricebreak}} = $sellprice;
  665. }
  666. }
  667. $pmh->finish;
  668. if (%p) {
  669. if ($ref->{sellprice}) {
  670. $p{0} = $ref->{sellprice};
  671. }
  672. for (sort { $a <=> $b } keys %p) { $ref->{pricematrix} .= "${_}:$p{$_} " }
  673. } else {
  674. if ($init) {
  675. $ref->{sellprice} = $form->round_amount($ref->{sellprice}, $decimalplaces);
  676. } else {
  677. $ref->{sellprice} = $form->round_amount($ref->{sellprice} * (1 - $form->{tradediscount}), $decimalplaces);
  678. }
  679. $ref->{pricematrix} = "0:$ref->{sellprice} " if $ref->{sellprice};
  680. }
  681. chop $ref->{pricematrix};
  682. }
  683. if ($form->{vendor_id}) {
  684. $pmh->execute($ref->{id});
  685. $mref = $pmh->fetchrow_hashref(NAME_lc);
  686. if ($mref->{partnumber} ne "") {
  687. $ref->{partnumber} = $mref->{partnumber};
  688. }
  689. if ($mref->{lastcost}) {
  690. # do a conversion
  691. $ref->{sellprice} = $form->round_amount($mref->{lastcost} * $form->{$mref->{curr}}, $decimalplaces);
  692. }
  693. $pmh->finish;
  694. $ref->{sellprice} *= 1;
  695. # add 0:price to matrix
  696. $ref->{pricematrix} = "0:$ref->{sellprice}";
  697. }
  698. }
  699. sub exchangerate_defaults {
  700. my ($dbh, $form) = @_;
  701. my $var;
  702. my $buysell = ($form->{vc} eq "customer") ? "buy" : "sell";
  703. # get default currencies
  704. my $query = qq|SELECT substr(curr,1,3), curr FROM defaults|;
  705. ($form->{defaultcurrency}, $form->{currencies}) = $dbh->selectrow_array($query);
  706. $query = qq|SELECT $buysell
  707. FROM exchangerate
  708. WHERE curr = ?
  709. AND transdate = ?|;
  710. my $eth1 = $dbh->prepare($query) || $form->dberror($query);
  711. $query = qq~SELECT max(transdate || ' ' || $buysell || ' ' || curr)
  712. FROM exchangerate
  713. WHERE curr = ?~;
  714. my $eth2 = $dbh->prepare($query) || $form->dberror($query);
  715. # get exchange rates for transdate or max
  716. foreach $var (split /:/, substr($form->{currencies},4)) {
  717. $eth1->execute($var, $form->{transdate});
  718. ($form->{$var}) = $eth1->fetchrow_array;
  719. if (! $form->{$var} ) {
  720. $eth2->execute($var);
  721. ($form->{$var}) = $eth2->fetchrow_array;
  722. ($null, $form->{$var}) = split / /, $form->{$var};
  723. $form->{$var} = 1 unless $form->{$var};
  724. $eth2->finish;
  725. }
  726. $eth1->finish;
  727. }
  728. $form->{$form->{currency}} = $form->{exchangerate} if $form->{exchangerate};
  729. $form->{$form->{currency}} ||= 1;
  730. $form->{$form->{defaultcurrency}} = 1;
  731. }
  732. sub order_details {
  733. my ($self, $myconfig, $form) = @_;
  734. # connect to database
  735. my $dbh = $form->dbconnect($myconfig);
  736. my $query;
  737. my $sth;
  738. my $item;
  739. my $i;
  740. my @sortlist = ();
  741. my $projectnumber;
  742. my $projectdescription;
  743. my $projectnumber_id;
  744. my $translation;
  745. my $partsgroup;
  746. my %oid = ( 'Pg' => 'TRUE',
  747. 'PgPP' => 'TRUE',
  748. 'Oracle' => 'rowid',
  749. 'DB2' => '1=1'
  750. );
  751. my @taxaccounts;
  752. my %taxaccounts;
  753. my $tax;
  754. my $taxrate;
  755. my $taxamount;
  756. my %translations;
  757. $query = qq|SELECT p.description, t.description
  758. FROM project p
  759. LEFT JOIN translation t ON (t.trans_id = p.id AND t.language_code = '$form->{language_code}')
  760. WHERE id = ?|;
  761. my $prh = $dbh->prepare($query) || $form->dberror($query);
  762. $query = qq|SELECT inventory_accno_id, income_accno_id,
  763. expense_accno_id, assembly FROM parts
  764. WHERE id = ?|;
  765. my $pth = $dbh->prepare($query) || $form->dberror($query);
  766. my $sortby;
  767. # sort items by project and partsgroup
  768. for $i (1 .. $form->{rowcount}) {
  769. if ($form->{"id_$i"}) {
  770. # account numbers
  771. $pth->execute($form->{"id_$i"});
  772. $ref = $pth->fetchrow_hashref(NAME_lc);
  773. for (keys %$ref) { $form->{"${_}_$i"} = $ref->{$_} }
  774. $pth->finish;
  775. $projectnumber_id = 0;
  776. $projectnumber = "";
  777. $form->{partsgroup} = "";
  778. $form->{projectnumber} = "";
  779. if ($form->{groupprojectnumber} || $form->{grouppartsgroup}) {
  780. $inventory_accno_id = ($form->{"inventory_accno_id_$i"} || $form->{"assembly_$i"}) ? "1" : "";
  781. if ($form->{groupprojectnumber}) {
  782. ($projectnumber, $projectnumber_id) = split /--/, $form->{"projectnumber_$i"};
  783. }
  784. if ($form->{grouppartsgroup}) {
  785. ($form->{partsgroup}) = split /--/, $form->{"partsgroup_$i"};
  786. }
  787. if ($projectnumber_id && $form->{groupprojectnumber}) {
  788. if ($translation{$projectnumber_id}) {
  789. $form->{projectnumber} = $translation{$projectnumber_id};
  790. } else {
  791. # get project description
  792. $prh->execute($projectnumber_id);
  793. ($projectdescription, $translation) = $prh->fetchrow_array;
  794. $prh->finish;
  795. $form->{projectnumber} = ($translation) ? "$projectnumber, $translation" : "$projectnumber, $projectdescription";
  796. $translation{$projectnumber_id} = $form->{projectnumber};
  797. }
  798. }
  799. if ($form->{grouppartsgroup} && $form->{partsgroup}) {
  800. $form->{projectnumber} .= " / " if $projectnumber_id;
  801. $form->{projectnumber} .= $form->{partsgroup};
  802. }
  803. $form->format_string(projectnumber);
  804. }
  805. $sortby = qq|$projectnumber$form->{partsgroup}|;
  806. if ($form->{sortby} ne 'runningnumber') {
  807. for (qw(partnumber description bin)) {
  808. $sortby .= $form->{"${_}_$i"} if $form->{sortby} eq $_;
  809. }
  810. }
  811. push @sortlist, [ $i, qq|$projectnumber$form->{partsgroup}$inventory_accno_id|, $form->{projectnumber}, $projectnumber_id, $form->{partsgroup}, $sortby ];
  812. }
  813. }
  814. delete $form->{projectnumber};
  815. # sort the whole thing by project and group
  816. @sortlist = sort { $a->[5] cmp $b->[5] } @sortlist;
  817. # if there is a warehouse limit picking
  818. if ($form->{warehouse_id} && $form->{formname} =~ /(pick|packing)_list/) {
  819. # run query to check for inventory
  820. $query = qq|SELECT sum(qty) AS qty
  821. FROM inventory
  822. WHERE parts_id = ?
  823. AND warehouse_id = ?|;
  824. $sth = $dbh->prepare($query) || $form->dberror($query);
  825. for $i (1 .. $form->{rowcount}) {
  826. $sth->execute($form->{"id_$i"}, $form->{warehouse_id}) || $form->dberror;
  827. ($qty) = $sth->fetchrow_array;
  828. $sth->finish;
  829. $form->{"qty_$i"} = 0 if $qty == 0;
  830. if ($form->parse_amount($myconfig, $form->{"ship_$i"}) > $qty) {
  831. $form->{"ship_$i"} = $form->format_amount($myconfig, $qty);
  832. }
  833. }
  834. }
  835. my $runningnumber = 1;
  836. my $sameitem = "";
  837. my $subtotal;
  838. my $k = scalar @sortlist;
  839. my $j = 0;
  840. foreach $item (@sortlist) {
  841. $i = $item->[0];
  842. $j++;
  843. if ($form->{groupprojectnumber} || $form->{grouppartsgroup}) {
  844. if ($item->[1] ne $sameitem) {
  845. $sameitem = $item->[1];
  846. $ok = 0;
  847. if ($form->{groupprojectnumber}) {
  848. $ok = $form->{"projectnumber_$i"};
  849. }
  850. if ($form->{grouppartsgroup}) {
  851. $ok = $form->{"partsgroup_$i"} unless $ok;
  852. }
  853. if ($ok) {
  854. if ($form->{"inventory_accno_id_$i"} || $form->{"assembly_$i"}) {
  855. push(@{ $form->{part} }, "");
  856. push(@{ $form->{service} }, NULL);
  857. } else {
  858. push(@{ $form->{part} }, NULL);
  859. push(@{ $form->{service} }, "");
  860. }
  861. push(@{ $form->{description} }, $item->[2]);
  862. for (qw(taxrates runningnumber number sku qty ship unit bin serialnumber requiredate projectnumber sellprice listprice netprice discount discountrate linetotal weight itemnotes)) { push(@{ $form->{$_} }, "") }
  863. push(@{ $form->{lineitems} }, { amount => 0, tax => 0 });
  864. }
  865. }
  866. }
  867. $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
  868. $form->{"ship_$i"} = $form->parse_amount($myconfig, $form->{"ship_$i"});
  869. if ($form->{"qty_$i"}) {
  870. $form->{totalqty} += $form->{"qty_$i"};
  871. $form->{totalship} += $form->{"ship_$i"};
  872. $form->{totalweight} += ($form->{"weight_$i"} * $form->{"qty_$i"});
  873. $form->{totalweightship} += ($form->{"weight_$i"} * $form->{"ship_$i"});
  874. # add number, description and qty to $form->{number}, ....
  875. push(@{ $form->{runningnumber} }, $runningnumber++);
  876. push(@{ $form->{number} }, qq|$form->{"partnumber_$i"}|);
  877. push(@{ $form->{sku} }, qq|$form->{"sku_$i"}|);
  878. push(@{ $form->{description} }, qq|$form->{"description_$i"}|);
  879. push(@{ $form->{itemnotes} }, $form->{"notes_$i"});
  880. push(@{ $form->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"}));
  881. push(@{ $form->{ship} }, $form->format_amount($myconfig, $form->{"ship_$i"}));
  882. push(@{ $form->{unit} }, qq|$form->{"unit_$i"}|);
  883. push(@{ $form->{bin} }, qq|$form->{"bin_$i"}|);
  884. push(@{ $form->{serialnumber} }, qq|$form->{"serialnumber_$i"}|);
  885. push(@{ $form->{requiredate} }, qq|$form->{"reqdate_$i"}|);
  886. push(@{ $form->{projectnumber} }, qq|$form->{"projectnumber_$i"}|);
  887. push(@{ $form->{sellprice} }, $form->{"sellprice_$i"});
  888. push(@{ $form->{listprice} }, $form->{"listprice_$i"});
  889. push(@{ $form->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"} * $form->{"ship_$i"}));
  890. my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
  891. my ($dec) = ($sellprice =~ /\.(\d+)/);
  892. $dec = length $dec;
  893. my $decimalplaces = ($dec > 2) ? $dec : 2;
  894. my $discount = $form->round_amount($sellprice * $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100, $decimalplaces);
  895. # keep a netprice as well, (sellprice - discount)
  896. $form->{"netprice_$i"} = $sellprice - $discount;
  897. my $linetotal = $form->round_amount($form->{"qty_$i"} * $form->{"netprice_$i"}, 2);
  898. if ($form->{"inventory_accno_id_$i"} || $form->{"assembly_$i"}) {
  899. push(@{ $form->{part} }, $form->{"sku_$i"});
  900. push(@{ $form->{service} }, NULL);
  901. $form->{totalparts} += $linetotal;
  902. } else {
  903. push(@{ $form->{service} }, $form->{"sku_$i"});
  904. push(@{ $form->{part} }, NULL);
  905. $form->{totalservices} += $linetotal;
  906. }
  907. push(@{ $form->{netprice} }, ($form->{"netprice_$i"}) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : " ");
  908. $discount = ($discount) ? $form->format_amount($myconfig, $discount * -1, $decimalplaces) : " ";
  909. push(@{ $form->{discount} }, $discount);
  910. push(@{ $form->{discountrate} }, $form->format_amount($myconfig, $form->{"discount_$i"}));
  911. $form->{ordtotal} += $linetotal;
  912. # this is for the subtotals for grouping
  913. $subtotal += $linetotal;
  914. $form->{"linetotal_$i"} = $form->format_amount($myconfig, $linetotal, 2);
  915. push(@{ $form->{linetotal} }, $form->{"linetotal_$i"});
  916. @taxaccounts = split / /, $form->{"taxaccounts_$i"};
  917. my $ml = 1;
  918. my @taxrates = ();
  919. $tax = 0;
  920. for (0 .. 1) {
  921. $taxrate = 0;
  922. for (@taxaccounts) { $taxrate += $form->{"${_}_rate"} if ($form->{"${_}_rate"} * $ml) > 0 }
  923. $taxrate *= $ml;
  924. $taxamount = $linetotal * $taxrate / (1 + $taxrate);
  925. $taxbase = ($linetotal - $taxamount);
  926. foreach $item (@taxaccounts) {
  927. if (($form->{"${item}_rate"} * $ml) > 0) {
  928. push @taxrates, $form->{"${item}_rate"} * 100;
  929. if ($form->{taxincluded}) {
  930. $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"} / (1 + $taxrate);
  931. $taxbase{$item} += $taxbase;
  932. } else {
  933. $taxbase{$item} += $linetotal;
  934. $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
  935. }
  936. }
  937. }
  938. if ($form->{taxincluded}) {
  939. $tax += $linetotal * ($taxrate / (1 + ($taxrate * $ml)));
  940. } else {
  941. $tax += $linetotal * $taxrate;
  942. }
  943. $ml *= -1;
  944. }
  945. push(@{ $form->{lineitems} }, { amount => $linetotal, tax => $form->round_amount($tax, 2) });
  946. push(@{ $form->{taxrates} }, join ' ', sort { $a <=> $b } @taxrates);
  947. if ($form->{"assembly_$i"}) {
  948. $form->{stagger} = -1;
  949. &assembly_details($myconfig, $form, $dbh, $form->{"id_$i"}, $oid{$myconfig->{dbdriver}}, $form->{"qty_$i"});
  950. }
  951. }
  952. # add subtotal
  953. if ($form->{groupprojectnumber} || $form->{grouppartsgroup}) {
  954. if ($subtotal) {
  955. if ($j < $k) {
  956. # look at next item
  957. if ($sortlist[$j]->[1] ne $sameitem) {
  958. if ($form->{"inventory_accno_id_$i"} || $form->{"assembly_$i"}) {
  959. push(@{ $form->{part} }, "");
  960. push(@{ $form->{service} }, NULL);
  961. } else {
  962. push(@{ $form->{service} }, "");
  963. push(@{ $form->{part} }, NULL);
  964. }
  965. for (qw(taxrates runningnumber number sku qty ship unit bin serialnumber requiredate projectnumber sellprice listprice netprice discount discountrate weight itemnotes)) { push(@{ $form->{$_} }, "") }
  966. push(@{ $form->{description} }, $form->{groupsubtotaldescription});
  967. push(@{ $form->{lineitems} }, { amount => 0, tax => 0 });
  968. if ($form->{groupsubtotaldescription} ne "") {
  969. push(@{ $form->{linetotal} }, $form->format_amount($myconfig, $subtotal, 2));
  970. } else {
  971. push(@{ $form->{linetotal} }, "");
  972. }
  973. $subtotal = 0;
  974. }
  975. } else {
  976. # got last item
  977. if ($form->{groupsubtotaldescription} ne "") {
  978. if ($form->{"inventory_accno_id_$i"} || $form->{"assembly_$i"}) {
  979. push(@{ $form->{part} }, "");
  980. push(@{ $form->{service} }, NULL);
  981. } else {
  982. push(@{ $form->{service} }, "");
  983. push(@{ $form->{part} }, NULL);
  984. }
  985. for (qw(taxrates runningnumber number sku qty ship unit bin serialnumber requiredate projectnumber sellprice listprice netprice discount discountrate weight itemnotes)) { push(@{ $form->{$_} }, "") }
  986. push(@{ $form->{description} }, $form->{groupsubtotaldescription});
  987. push(@{ $form->{linetotal} }, $form->format_amount($myconfig, $subtotal, 2));
  988. push(@{ $form->{lineitems} }, { amount => 0, tax => 0 });
  989. }
  990. }
  991. }
  992. }
  993. }
  994. $tax = 0;
  995. foreach $item (sort keys %taxaccounts) {
  996. if ($form->round_amount($taxaccounts{$item}, 2)) {
  997. $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
  998. push(@{ $form->{taxbaseinclusive} }, $form->{"${item}_taxbaseinclusive"} = $form->round_amount($taxbase{$item} + $tax, 2));
  999. push(@{ $form->{taxbase} }, $form->{"${item}_taxbase"} = $form->format_amount($myconfig, $taxbase{$item}, 2));
  1000. push(@{ $form->{tax} }, $form->{"${item}_tax"} = $form->format_amount($myconfig, $taxamount, 2));
  1001. push(@{ $form->{taxdescription} }, $form->{"${item}_description"});
  1002. $form->{"${item}_taxrate"} = $form->format_amount($myconfig, $form->{"${item}_rate"} * 100);
  1003. push(@{ $form->{taxrate} }, $form->{"${item}_taxrate"});
  1004. push(@{ $form->{taxnumber} }, $form->{"${item}_taxnumber"});
  1005. }
  1006. }
  1007. # adjust taxes for lineitems
  1008. my $total = 0;
  1009. for (@{ $form->{lineitems} }) {
  1010. $total += $_->{tax};
  1011. }
  1012. if ($form->round_amount($total,2) != $form->round_amount($tax,2)) {
  1013. # get largest amount
  1014. for (reverse sort { $a->{tax} <=> $b->{tax} } @{ $form->{lineitems} }) {
  1015. $_->{tax} -= $total - $tax;
  1016. last;
  1017. }
  1018. }
  1019. $i = 1;
  1020. for (@{ $form->{lineitems} }) {
  1021. push(@{ $form->{linetax} }, $form->format_amount($myconfig, $_->{tax}, 2, ""));
  1022. }
  1023. for (qw(totalparts totalservices)) { $form->{$_} = $form->format_amount($myconfig, $form->{$_}, 2) }
  1024. for (qw(totalqty totalship totalweight)) { $form->{$_} = $form->format_amount($myconfig, $form->{$_}) }
  1025. $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
  1026. $form->{ordtotal} = ($form->{taxincluded}) ? $form->{ordtotal} : $form->{ordtotal} + $tax;
  1027. use LedgerSMB::CP;
  1028. my $c;
  1029. if ($form->{language_code} ne "") {
  1030. $c = new CP $form->{language_code};
  1031. } else {
  1032. $c = new CP $myconfig->{countrycode};
  1033. }
  1034. $c->init;
  1035. my $whole;
  1036. ($whole, $form->{decimal}) = split /\./, $form->{ordtotal};
  1037. $form->{decimal} .= "00";
  1038. $form->{decimal} = substr($form->{decimal}, 0, 2);
  1039. $form->{text_decimal} = $c->num2text($form->{decimal} * 1);
  1040. $form->{text_amount} = $c->num2text($whole);
  1041. $form->{integer_amount} = $form->format_amount($myconfig, $whole);
  1042. # format amounts
  1043. $form->{quototal} = $form->{ordtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
  1044. $form->format_string(qw(text_amount text_decimal));
  1045. $query = qq|SELECT weightunit FROM defaults|;
  1046. ($form->{weightunit}) = $dbh->selectrow_array($query);
  1047. $dbh->disconnect;
  1048. }
  1049. sub assembly_details {
  1050. my ($myconfig, $form, $dbh, $id, $oid, $qty) = @_;
  1051. my $sm = "";
  1052. my $spacer;
  1053. $form->{stagger}++;
  1054. if ($form->{format} eq 'html') {
  1055. $spacer = "&nbsp;" x (3 * ($form->{stagger} - 1)) if $form->{stagger} > 1;
  1056. }
  1057. if ($form->{format} =~ /(postscript|pdf)/) {
  1058. if ($form->{stagger} > 1) {
  1059. $spacer = ($form->{stagger} - 1) * 3;
  1060. $spacer = '\rule{'.$spacer.'mm}{0mm}';
  1061. }
  1062. }
  1063. # get parts and push them onto the stack
  1064. my $sortorder = "";
  1065. if ($form->{grouppartsgroup}) {
  1066. $sortorder = qq|ORDER BY pg.partsgroup, a.$oid|;
  1067. } else {
  1068. $sortorder = qq|ORDER BY a.$oid|;
  1069. }
  1070. my $where = ($form->{formname} eq 'work_order') ? "1 = 1" : "a.bom = '1'";
  1071. my $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty,
  1072. pg.partsgroup, p.partnumber AS sku, p.assembly, p.id, p.bin
  1073. FROM assembly a
  1074. JOIN parts p ON (a.parts_id = p.id)
  1075. LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
  1076. WHERE $where
  1077. AND a.id = '$id'
  1078. $sortorder|;
  1079. my $sth = $dbh->prepare($query);
  1080. $sth->execute || $form->dberror($query);
  1081. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  1082. for (qw(partnumber description partsgroup)) {
  1083. $form->{"a_$_"} = $ref->{$_};
  1084. $form->format_string("a_$_");
  1085. }
  1086. if ($form->{grouppartsgroup} && $ref->{partsgroup} ne $sm) {
  1087. for (qw(taxrates number sku unit qty runningnumber ship bin serialnumber requiredate projectnumber sellprice listprice netprice discount discountrate linetotal weight itemnotes)) { push(@{ $form->{$_} }, "") }
  1088. $sm = ($form->{"a_partsgroup"}) ? $form->{"a_partsgroup"} : "";
  1089. push(@{ $form->{description} }, "$spacer$sm");
  1090. push(@{ $form->{lineitems} }, { amount => 0, tax => 0 });
  1091. }
  1092. if ($form->{stagger}) {
  1093. push(@{ $form->{description} }, qq|$spacer$form->{"a_partnumber"}, $form->{"a_description"}|);
  1094. for (qw(taxrates number sku runningnumber ship serialnumber requiredate projectnumber sellprice listprice netprice discount discountrate linetotal weight itemnotes)) { push(@{ $form->{$_} }, "") }
  1095. } else {
  1096. push(@{ $form->{description} }, qq|$form->{"a_description"}|);
  1097. push(@{ $form->{sku} }, $form->{"a_partnumber"});
  1098. push(@{ $form->{number} }, $form->{"a_partnumber"});
  1099. for (qw(taxrates runningnumber ship serialnumber requiredate projectnumber sellprice listprice netprice discount discountrate linetotal weight itemnotes)) { push(@{ $form->{$_} }, "") }
  1100. }
  1101. push(@{ $form->{lineitems} }, { amount => 0, tax => 0 });
  1102. push(@{ $form->{qty} }, $form->format_amount($myconfig, $ref->{qty} * $qty));
  1103. for (qw(unit bin)) {
  1104. $form->{"a_$_"} = $ref->{$_};
  1105. $form->format_string("a_$_");
  1106. push(@{ $form->{$_} }, $form->{"a_$_"});
  1107. }
  1108. if ($ref->{assembly} && $form->{formname} eq 'work_order') {
  1109. &assembly_details($myconfig, $form, $dbh, $ref->{id}, $oid, $ref->{qty} * $qty);
  1110. }
  1111. }
  1112. $sth->finish;
  1113. $form->{stagger}--;
  1114. }
  1115. sub project_description {
  1116. my ($self, $dbh, $id) = @_;
  1117. my $query = qq|SELECT description
  1118. FROM project
  1119. WHERE id = $id|;
  1120. ($_) = $dbh->selectrow_array($query);
  1121. $_;
  1122. }
  1123. sub get_warehouses {
  1124. my ($self, $myconfig, $form) = @_;
  1125. my $dbh = $form->dbconnect($myconfig);
  1126. # setup warehouses
  1127. my $query = qq|SELECT id, description
  1128. FROM warehouse
  1129. ORDER BY 2|;
  1130. my $sth = $dbh->prepare($query);
  1131. $sth->execute || $form->dberror($query);
  1132. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  1133. push @{ $form->{all_warehouse} }, $ref;
  1134. }
  1135. $sth->finish;
  1136. $dbh->disconnect;
  1137. }
  1138. sub save_inventory {
  1139. my ($self, $myconfig, $form) = @_;
  1140. my ($null, $warehouse_id) = split /--/, $form->{warehouse};
  1141. $warehouse_id *= 1;
  1142. my $ml = ($form->{type} eq 'ship_order') ? -1 : 1;
  1143. my $dbh = $form->dbconnect_noauto($myconfig);
  1144. my $sth;
  1145. my $wth;
  1146. my $serialnumber;
  1147. my $ship;
  1148. my ($null, $employee_id) = split /--/, $form->{employee};
  1149. ($null, $employee_id) = $form->get_employee($dbh) if ! $employee_id;
  1150. $query = qq|SELECT serialnumber, ship
  1151. FROM orderitems
  1152. WHERE trans_id = ?
  1153. AND id = ?
  1154. FOR UPDATE|;
  1155. $sth = $dbh->prepare($query) || $form->dberror($query);
  1156. $query = qq|SELECT sum(qty)
  1157. FROM inventory
  1158. WHERE parts_id = ?
  1159. AND warehouse_id = ?|;
  1160. $wth = $dbh->prepare($query) || $form->dberror($query);
  1161. for my $i (1 .. $form->{rowcount}) {
  1162. $ship = (abs($form->{"ship_$i"}) > abs($form->{"qty_$i"})) ? $form->{"qty_$i"} : $form->{"ship_$i"};
  1163. if ($warehouse_id && $form->{type} eq 'ship_order') {
  1164. $wth->execute($form->{"id_$i"}, $warehouse_id) || $form->dberror;
  1165. ($qty) = $wth->fetchrow_array;
  1166. $wth->finish;
  1167. if ($ship > $qty) {
  1168. $ship = $qty;
  1169. }
  1170. }
  1171. if ($ship) {
  1172. $ship *= $ml;
  1173. $query = qq|INSERT INTO inventory (parts_id, warehouse_id,
  1174. qty, trans_id, orderitems_id, shippingdate, employee_id)
  1175. VALUES ($form->{"id_$i"}, $warehouse_id,
  1176. $ship, $form->{"id"},
  1177. $form->{"orderitems_id_$i"}, '$form->{shippingdate}',
  1178. $employee_id)|;
  1179. $dbh->do($query) || $form->dberror($query);
  1180. # add serialnumber, ship to orderitems
  1181. $sth->execute($form->{id}, $form->{"orderitems_id_$i"}) || $form->dberror;
  1182. ($serialnumber, $ship) = $sth->fetchrow_array;
  1183. $sth->finish;
  1184. $serialnumber .= " " if $serialnumber;
  1185. $serialnumber .= qq|$form->{"serialnumber_$i"}|;
  1186. $ship += $form->{"ship_$i"};
  1187. $query = qq|UPDATE orderitems SET
  1188. serialnumber = '$serialnumber',
  1189. ship = $ship,
  1190. reqdate = '$form->{shippingdate}'
  1191. WHERE trans_id = $form->{id}
  1192. AND id = $form->{"orderitems_id_$i"}|;
  1193. $dbh->do($query) || $form->dberror($query);
  1194. # update order with ship via
  1195. $query = qq|UPDATE oe SET
  1196. shippingpoint = |.$dbh->quote($form->{shippingpoint}).qq|,
  1197. shipvia = |.$dbh->quote($form->{shipvia}).qq|
  1198. WHERE id = $form->{id}|;
  1199. $dbh->do($query) || $form->dberror($query);
  1200. # update onhand for parts
  1201. $form->update_balance($dbh,
  1202. "parts",
  1203. "onhand",
  1204. qq|id = $form->{"id_$i"}|,
  1205. $form->{"ship_$i"} * $ml);
  1206. }
  1207. }
  1208. my $rc = $dbh->commit;
  1209. $dbh->disconnect;
  1210. $rc;
  1211. }
  1212. sub adj_onhand {
  1213. my ($dbh, $form, $ml) = @_;
  1214. my $query = qq|SELECT oi.parts_id, oi.ship, p.inventory_accno_id, p.assembly
  1215. FROM orderitems oi
  1216. JOIN parts p ON (p.id = oi.parts_id)
  1217. WHERE oi.trans_id = $form->{id}|;
  1218. my $sth = $dbh->prepare($query);
  1219. $sth->execute || $form->dberror($query);
  1220. $query = qq|SELECT sum(p.inventory_accno_id), p.assembly
  1221. FROM parts p
  1222. JOIN assembly a ON (a.parts_id = p.id)
  1223. WHERE a.id = ?
  1224. GROUP BY p.assembly|;
  1225. my $ath = $dbh->prepare($query) || $form->dberror($query);
  1226. my $ref;
  1227. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  1228. if ($ref->{inventory_accno_id} || $ref->{assembly}) {
  1229. # do not update if assembly consists of all services
  1230. if ($ref->{assembly}) {
  1231. $ath->execute($ref->{parts_id}) || $form->dberror($query);
  1232. my ($inv, $assembly) = $ath->fetchrow_array;
  1233. $ath->finish;
  1234. next unless ($inv || $assembly);
  1235. }
  1236. # adjust onhand in parts table
  1237. $form->update_balance($dbh,
  1238. "parts",
  1239. "onhand",
  1240. qq|id = $ref->{parts_id}|,
  1241. $ref->{ship} * $ml);
  1242. }
  1243. }
  1244. $sth->finish;
  1245. }
  1246. sub adj_inventory {
  1247. my ($dbh, $myconfig, $form) = @_;
  1248. my %oid = ( 'Pg' => 'id',
  1249. 'PgPP' => 'id',
  1250. 'Oracle' => 'rowid',
  1251. 'DB2' => '1=1'
  1252. );
  1253. # increase/reduce qty in inventory table
  1254. my $query = qq|SELECT oi.id, oi.parts_id, oi.ship
  1255. FROM orderitems oi
  1256. WHERE oi.trans_id = $form->{id}|;
  1257. my $sth = $dbh->prepare($query);
  1258. $sth->execute || $form->dberror($query);
  1259. $query = qq|SELECT qty,
  1260. (SELECT SUM(qty) FROM inventory
  1261. WHERE trans_id = $form->{id}
  1262. AND orderitems_id = ?) AS total
  1263. FROM inventory
  1264. WHERE trans_id = $form->{id}
  1265. AND orderitems_id = ?|;
  1266. my $ith = $dbh->prepare($query) || $form->dberror($query);
  1267. my $qty;
  1268. my $ml = ($form->{type} =~ /(ship|sales)_order/) ? -1 : 1;
  1269. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  1270. $ith->execute($ref->{id}, $ref->{id}) || $form->dberror($query);
  1271. my $ship = $ref->{ship};
  1272. while (my $inv = $ith->fetchrow_hashref(NAME_lc)) {
  1273. if (($qty = (($inv->{total} * $ml) - $ship)) >= 0) {
  1274. $qty = $inv->{qty} * $ml if ($qty > ($inv->{qty} * $ml));
  1275. $form->update_balance($dbh,
  1276. "inventory",
  1277. "qty",
  1278. qq|$oid{$myconfig->{dbdriver}} = $inv->{oid}|,
  1279. $qty * -1 * $ml);
  1280. $ship -= $qty;
  1281. }
  1282. }
  1283. $ith->finish;
  1284. }
  1285. $sth->finish;
  1286. # delete inventory entries if qty = 0
  1287. $query = qq|DELETE FROM inventory
  1288. WHERE trans_id = $form->{id}
  1289. AND qty = 0|;
  1290. $dbh->do($query) || $form->dberror($query);
  1291. }
  1292. sub get_inventory {
  1293. my ($self, $myconfig, $form) = @_;
  1294. my $where;
  1295. my $query;
  1296. my $null;
  1297. my $fromwarehouse_id;
  1298. my $towarehouse_id;
  1299. my $var;
  1300. my $dbh = $form->dbconnect($myconfig);
  1301. if ($form->{partnumber} ne "") {
  1302. $var = $form->like(lc $form->{partnumber});
  1303. $where .= "
  1304. AND lower(p.partnumber) LIKE '$var'";
  1305. }
  1306. if ($form->{description} ne "") {
  1307. $var = $form->like(lc $form->{description});
  1308. $where .= "
  1309. AND lower(p.description) LIKE '$var'";
  1310. }
  1311. if ($form->{partsgroup} ne "") {
  1312. ($null, $var) = split /--/, $form->{partsgroup};
  1313. $where .= "
  1314. AND pg.id = $var";
  1315. }
  1316. ($null, $fromwarehouse_id) = split /--/, $form->{fromwarehouse};
  1317. $fromwarehouse_id *= 1;
  1318. ($null, $towarehouse_id) = split /--/, $form->{towarehouse};
  1319. $towarehouse_id *= 1;
  1320. my %ordinal = ( partnumber => 2,
  1321. description => 3,
  1322. partsgroup => 5,
  1323. warehouse => 6,
  1324. );
  1325. my @a = (partnumber, warehouse);
  1326. my $sortorder = $form->sort_order(\@a, \%ordinal);
  1327. if ($fromwarehouse_id) {
  1328. if ($towarehouse_id) {
  1329. $where .= "
  1330. AND NOT i.warehouse_id = $towarehouse_id";
  1331. }
  1332. $query = qq|SELECT p.id, p.partnumber, p.description,
  1333. sum(i.qty) * 2 AS onhand, sum(i.qty) AS qty,
  1334. pg.partsgroup, w.description AS warehouse, i.warehouse_id
  1335. FROM inventory i
  1336. JOIN parts p ON (p.id = i.parts_id)
  1337. LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
  1338. JOIN warehouse w ON (w.id = i.warehouse_id)
  1339. WHERE i.warehouse_id = $fromwarehouse_id
  1340. $where
  1341. GROUP BY p.id, p.partnumber, p.description, pg.partsgroup, w.description, i.warehouse_id
  1342. ORDER BY $sortorder|;
  1343. } else {
  1344. if ($towarehouse_id) {
  1345. $query = qq|
  1346. SELECT p.id, p.partnumber, p.description,
  1347. p.onhand, (SELECT SUM(qty) FROM inventory i WHERE i.parts_id = p.id) AS qty,
  1348. pg.partsgroup, '' AS warehouse, 0 AS warehouse_id
  1349. FROM parts p
  1350. LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
  1351. WHERE p.onhand > 0
  1352. $where
  1353. UNION|;
  1354. }
  1355. $query .= qq|
  1356. SELECT p.id, p.partnumber, p.description,
  1357. sum(i.qty) * 2 AS onhand, sum(i.qty) AS qty,
  1358. pg.partsgroup, w.description AS warehouse, i.warehouse_id
  1359. FROM inventory i
  1360. JOIN parts p ON (p.id = i.parts_id)
  1361. LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
  1362. JOIN warehouse w ON (w.id = i.warehouse_id)
  1363. WHERE i.warehouse_id != $towarehouse_id
  1364. $where
  1365. GROUP BY p.id, p.partnumber, p.description, pg.partsgroup, w.description, i.warehouse_id
  1366. ORDER BY $sortorder|;
  1367. }
  1368. my $sth = $dbh->prepare($query);
  1369. $sth->execute || $form->dberror($query);
  1370. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  1371. $ref->{qty} = $ref->{onhand} - $ref->{qty};
  1372. push @{ $form->{all_inventory} }, $ref if $ref->{qty} > 0;
  1373. }
  1374. $sth->finish;
  1375. $dbh->disconnect;
  1376. }
  1377. sub transfer {
  1378. my ($self, $myconfig, $form) = @_;
  1379. my $dbh = $form->dbconnect_noauto($myconfig);
  1380. ($form->{employee}, $form->{employee_id}) = $form->get_employee($dbh);
  1381. my @a = localtime;
  1382. $a[5] += 1900;
  1383. $a[4]++;
  1384. $a[4] = substr("0$a[4]", -2);
  1385. $a[3] = substr("0$a[3]", -2);
  1386. $shippingdate = "$a[5]$a[4]$a[3]";
  1387. my %total = ();
  1388. my $query = qq|INSERT INTO inventory
  1389. (warehouse_id, parts_id, qty, shippingdate, employee_id)
  1390. VALUES (?, ?, ?, '$shippingdate', $form->{employee_id})|;
  1391. $sth = $dbh->prepare($query) || $form->dberror($query);
  1392. my $qty;
  1393. for my $i (1 .. $form->{rowcount}) {
  1394. $qty = $form->parse_amount($myconfig, $form->{"transfer_$i"});
  1395. $qty = $form->{"qty_$i"} if ($qty > $form->{"qty_$i"});
  1396. if ($qty > 0) {
  1397. # to warehouse
  1398. if ($form->{warehouse_id}) {
  1399. $sth->execute($form->{warehouse_id}, $form->{"id_$i"}, $qty) || $form->dberror;
  1400. $sth->finish;
  1401. }
  1402. # from warehouse
  1403. if ($form->{"warehouse_id_$i"}) {
  1404. $sth->execute($form->{"warehouse_id_$i"}, $form->{"id_$i"}, $qty * -1) || $form->dberror;
  1405. $sth->finish;
  1406. }
  1407. }
  1408. }
  1409. my $rc = $dbh->commit;
  1410. $dbh->disconnect;
  1411. $rc;
  1412. }
  1413. sub get_soparts {
  1414. my ($self, $myconfig, $form) = @_;
  1415. # connect to database
  1416. my $dbh = $form->dbconnect($myconfig);
  1417. my $id;
  1418. my $ref;
  1419. # store required items from selected sales orders
  1420. my $query = qq|SELECT p.id, oi.qty - oi.ship AS required,
  1421. p.assembly
  1422. FROM orderitems oi
  1423. JOIN parts p ON (p.id = oi.parts_id)
  1424. WHERE oi.trans_id = ?|;
  1425. my $sth = $dbh->prepare($query) || $form->dberror($query);
  1426. for (my $i = 1; $i <= $form->{rowcount}; $i++) {
  1427. if ($form->{"ndx_$i"}) {
  1428. $sth->execute($form->{"ndx_$i"});
  1429. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  1430. &add_items_required("", $dbh, $form, $ref->{id}, $ref->{required}, $ref->{assembly});
  1431. }
  1432. $sth->finish;
  1433. }
  1434. }
  1435. $query = qq|SELECT current_date FROM defaults|;
  1436. ($form->{transdate}) = $dbh->selectrow_array($query);
  1437. # foreign exchange rates
  1438. &exchangerate_defaults($dbh, $form);
  1439. $dbh->disconnect;
  1440. }
  1441. sub add_items_required {
  1442. my ($self, $dbh, $form, $parts_id, $required, $assembly) = @_;
  1443. my $query;
  1444. my $sth;
  1445. my $ref;
  1446. if ($assembly) {
  1447. $query = qq|SELECT p.id, a.qty, p.assembly
  1448. FROM assembly a
  1449. JOIN parts p ON (p.id = a.parts_id)
  1450. WHERE a.id = $parts_id|;
  1451. $sth = $dbh->prepare($query);
  1452. $sth->execute || $form->dberror($query);
  1453. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  1454. &add_items_required("", $dbh, $form, $ref->{id}, $required * $ref->{qty}, $ref->{assembly});
  1455. }
  1456. $sth->finish;
  1457. } else {
  1458. $query = qq|SELECT partnumber, description, lastcost
  1459. FROM parts
  1460. WHERE id = $parts_id|;
  1461. $sth = $dbh->prepare($query);
  1462. $sth->execute || $form->dberror($query);
  1463. $ref = $sth->fetchrow_hashref(NAME_lc);
  1464. for (keys %$ref) { $form->{orderitems}{$parts_id}{$_} = $ref->{$_} }
  1465. $sth->finish;
  1466. $form->{orderitems}{$parts_id}{required} += $required;
  1467. $query = qq|SELECT pv.partnumber, pv.leadtime, pv.lastcost, pv.curr,
  1468. pv.vendor_id, v.name
  1469. FROM partsvendor pv
  1470. JOIN vendor v ON (v.id = pv.vendor_id)
  1471. WHERE pv.parts_id = ?|;
  1472. $sth = $dbh->prepare($query) || $form->dberror($query);
  1473. # get cost and vendor
  1474. $sth->execute($parts_id);
  1475. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  1476. for (keys %$ref) { $form->{orderitems}{$parts_id}{partsvendor}{$ref->{vendor_id}}{$_} = $ref->{$_} }
  1477. }
  1478. $sth->finish;
  1479. }
  1480. }
  1481. sub generate_orders {
  1482. my ($self, $myconfig, $form) = @_;
  1483. my $i;
  1484. my %a;
  1485. my $query;
  1486. my $sth;
  1487. for ($i = 1; $i <= $form->{rowcount}; $i++) {
  1488. for (qw(qty lastcost)) { $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) }
  1489. if ($form->{"qty_$i"}) {
  1490. ($vendor, $vendor_id) = split /--/, $form->{"vendor_$i"};
  1491. if ($vendor_id) {
  1492. $a{$vendor_id}{$form->{"id_$i"}}{qty} += $form->{"qty_$i"};
  1493. for (qw(curr lastcost)) { $a{$vendor_id}{$form->{"id_$i"}}{$_} = $form->{"${_}_$i"} }
  1494. }
  1495. }
  1496. }
  1497. # connect to database
  1498. my $dbh = $form->dbconnect_noauto($myconfig);
  1499. # foreign exchange rates
  1500. &exchangerate_defaults($dbh, $form);
  1501. my $amount;
  1502. my $netamount;
  1503. my $curr = "";
  1504. my %tax;
  1505. my $taxincluded = 0;
  1506. my $vendor_id;
  1507. my $description;
  1508. my $unit;
  1509. my $sellprice;
  1510. foreach $vendor_id (keys %a) {
  1511. %tax = ();
  1512. $query = qq|SELECT v.curr, v.taxincluded, t.rate, c.accno
  1513. FROM vendor v
  1514. LEFT JOIN vendortax vt ON (v.id = vt.vendor_id)
  1515. LEFT JOIN tax t ON (t.chart_id = vt.chart_id)
  1516. LEFT JOIN chart c ON (c.id = t.chart_id)
  1517. WHERE v.id = $vendor_id|;
  1518. $sth = $dbh->prepare($query);
  1519. $sth->execute || $form->dberror($query);
  1520. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  1521. $curr = $ref->{curr};
  1522. $taxincluded = $ref->{taxincluded};
  1523. $tax{$ref->{accno}} = $ref->{rate};
  1524. }
  1525. $sth->finish;
  1526. $curr ||= $form->{defaultcurrency};
  1527. $taxincluded *= 1;
  1528. my $uid = localtime;
  1529. $uid .= "$$";
  1530. $query = qq|INSERT INTO oe (ordnumber)
  1531. VALUES ('$uid')|;
  1532. $dbh->do($query) || $form->dberror($query);
  1533. $query = qq|SELECT id FROM oe
  1534. WHERE ordnumber = '$uid'|;
  1535. $sth = $dbh->prepare($query);
  1536. $sth->execute || $form->dberror($query);
  1537. my ($id) = $sth->fetchrow_array;
  1538. $sth->finish;
  1539. $amount = 0;
  1540. $netamount = 0;
  1541. foreach my $parts_id (keys %{ $a{$vendor_id} }) {
  1542. if (($form->{$curr} * $form->{$a{$vendor_id}{$parts_id}{curr}}) > 0) {
  1543. $sellprice = $a{$vendor_id}{$parts_id}{lastcost} / $form->{$curr} * $form->{$a{$vendor_id}{$parts_id}{curr}};
  1544. } else {
  1545. $sellprice = $a{$vendor_id}{$parts_id}{lastcost};
  1546. }
  1547. $sellprice = $form->round_amount($sellprice, 2);
  1548. my $linetotal = $form->round_amount($sellprice * $a{$vendor_id}{$parts_id}{qty}, 2);
  1549. $query = qq|SELECT p.description, p.unit, c.accno FROM parts p
  1550. LEFT JOIN partstax pt ON (p.id = pt.parts_id)
  1551. LEFT JOIN chart c ON (c.id = pt.chart_id)
  1552. WHERE p.id = $parts_id|;
  1553. $sth = $dbh->prepare($query);
  1554. $sth->execute || $form->dberror($query);
  1555. my $rate = 0;
  1556. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  1557. $description = $ref->{description};
  1558. $unit = $ref->{unit};
  1559. $rate += $tax{$ref->{accno}};
  1560. }
  1561. $sth->finish;
  1562. $netamount += $linetotal;
  1563. if ($taxincluded) {
  1564. $amount += $linetotal;
  1565. } else {
  1566. $amount += $form->round_amount($linetotal * (1 + $rate), 2);
  1567. }
  1568. $description = $dbh->quote($description);
  1569. $unit = $dbh->quote($unit);
  1570. $query = qq|INSERT INTO orderitems (trans_id, parts_id, description,
  1571. qty, ship, sellprice, unit) VALUES
  1572. ($id, $parts_id, $description,
  1573. $a{$vendor_id}{$parts_id}{qty}, 0, $sellprice, $unit)|;
  1574. $dbh->do($query) || $form->dberror($query);
  1575. }
  1576. my $ordnumber = $form->update_defaults($myconfig, 'ponumber');
  1577. my $null;
  1578. my $employee_id;
  1579. my $department_id;
  1580. ($null, $employee_id) = $form->get_employee($dbh);
  1581. ($null, $department_id) = split /--/, $form->{department};
  1582. $department_id *= 1;
  1583. $query = qq|UPDATE oe SET
  1584. ordnumber = |.$dbh->quote($ordnumber).qq|,
  1585. transdate = current_date,
  1586. vendor_id = $vendor_id,
  1587. customer_id = 0,
  1588. amount = $amount,
  1589. netamount = $netamount,
  1590. taxincluded = '$taxincluded',
  1591. curr = '$curr',
  1592. employee_id = $employee_id,
  1593. department_id = '$department_id',
  1594. ponumber = |.$dbh->quote($form->{ponumber}).qq|
  1595. WHERE id = $id|;
  1596. $dbh->do($query) || $form->dberror($query);
  1597. }
  1598. my $rc = $dbh->commit;
  1599. $dbh->disconnect;
  1600. $rc;
  1601. }
  1602. sub consolidate_orders {
  1603. my ($self, $myconfig, $form) = @_;
  1604. # connect to database
  1605. my $dbh = $form->dbconnect_noauto($myconfig);
  1606. my $i;
  1607. my $id;
  1608. my $ref;
  1609. my %oe = ();
  1610. my $query = qq|SELECT * FROM oe
  1611. WHERE id = ?|;
  1612. my $sth = $dbh->prepare($query) || $form->dberror($query);
  1613. for ($i = 1; $i <= $form->{rowcount}; $i++) {
  1614. # retrieve order
  1615. if ($form->{"ndx_$i"}) {
  1616. $sth->execute($form->{"ndx_$i"});
  1617. $ref = $sth->fetchrow_hashref(NAME_lc);
  1618. $ref->{ndx} = $i;
  1619. $oe{oe}{$ref->{curr}}{$ref->{id}} = $ref;
  1620. $oe{vc}{$ref->{curr}}{$ref->{"$form->{vc}_id"}}++;
  1621. $sth->finish;
  1622. }
  1623. }
  1624. $query = qq|SELECT * FROM orderitems
  1625. WHERE trans_id = ?|;
  1626. $sth = $dbh->prepare($query) || $form->dberror($query);
  1627. foreach $curr (keys %{ $oe{oe} }) {
  1628. foreach $id (sort { $oe{oe}{$curr}{$a}->{ndx} <=> $oe{oe}{$curr}{$b}->{ndx} } keys %{ $oe{oe}{$curr} }) {
  1629. # retrieve order
  1630. $vc_id = $oe{oe}{$curr}{$id}->{"$form->{vc}_id"};
  1631. if ($oe{vc}{$oe{oe}{$curr}{$id}->{curr}}{$vc_id} > 1) {
  1632. push @{ $oe{orders}{$curr}{$vc_id} }, $id;
  1633. $sth->execute($id);
  1634. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  1635. push @{ $oe{orderitems}{$curr}{$id} }, $ref;
  1636. }
  1637. $sth->finish;
  1638. }
  1639. }
  1640. }
  1641. my $ordnumber = $form->{ordnumber};
  1642. my $numberfld = ($form->{vc} eq 'customer') ? 'sonumber' : 'ponumber';
  1643. my ($department, $department_id) = $form->{department};
  1644. $department_id *= 1;
  1645. my $uid = localtime;
  1646. $uid .= "$$";
  1647. my @orderitems = ();
  1648. foreach $curr (keys %{ $oe{orders} }) {
  1649. foreach $vc_id (sort { $a <=> $b } keys %{ $oe{orders}{$curr} }) {
  1650. # the orders
  1651. @orderitems = ();
  1652. $form->{customer_id} = $form->{vendor_id} = 0;
  1653. $form->{"$form->{vc}_id"} = $vc_id;
  1654. $amount = 0;
  1655. $netamount = 0;
  1656. foreach $id (@{ $oe{orders}{$curr}{$vc_id} }) {
  1657. # header
  1658. $ref = $oe{oe}{$curr}{$id};
  1659. $amount += $ref->{amount};
  1660. $netamount += $ref->{netamount};
  1661. foreach $item (@{ $oe{orderitems}{$curr}{$id} }) {
  1662. push @orderitems, $item;
  1663. }
  1664. # close order
  1665. $query = qq|UPDATE oe SET
  1666. closed = '1'
  1667. WHERE id = $id|;
  1668. $dbh->do($query) || $form->dberror($query);
  1669. # reset shipped
  1670. $query = qq|UPDATE orderitems SET
  1671. ship = 0
  1672. WHERE trans_id = $id|;
  1673. $dbh->do($query) || $form->dberror($query);
  1674. }
  1675. $ordnumber ||= $form->update_defaults($myconfig, $numberfld, $dbh);
  1676. $query = qq|INSERT INTO oe (ordnumber)
  1677. VALUES ($uid)|;
  1678. $dbh->do($query) || $form->dberror($query);
  1679. $query = qq|SELECT id
  1680. FROM oe
  1681. WHERE ordnumber = '$uid'|;
  1682. ($id) = $dbh->selectrow_array($query);
  1683. $ref->{employee_id} *= 1;
  1684. $query = qq|UPDATE oe SET
  1685. ordnumber = |.$dbh->quote($ordnumber).qq|,
  1686. transdate = current_date,
  1687. vendor_id = $form->{vendor_id},
  1688. customer_id = $form->{customer_id},
  1689. amount = $amount,
  1690. netamount = $netamount,
  1691. reqdate = |.$form->dbquote($ref->{reqdate}, SQL_DATE).qq|,
  1692. taxincluded = '$ref->{taxincluded}',
  1693. shippingpoint = |.$dbh->quote($ref->{shippingpoint}).qq|,
  1694. notes = |.$dbh->quote($ref->{notes}).qq|,
  1695. curr = '$curr',
  1696. employee_id = $ref->{employee_id},
  1697. intnotes = |.$dbh->quote($ref->{intnotes}).qq|,
  1698. shipvia = |.$dbh->quote($ref->{shipvia}).qq|,
  1699. language_code = '$ref->{language_code}',
  1700. ponumber = |.$dbh->quote($form->{ponumber}).qq|,
  1701. department_id = $department_id
  1702. WHERE id = $id|;
  1703. $dbh->do($query) || $form->dberror($query);
  1704. # add items
  1705. foreach $item (@orderitems) {
  1706. for (qw(qty sellprice discount project_id ship)) { $item->{$_} *= 1 }
  1707. $query = qq|INSERT INTO orderitems (
  1708. trans_id, parts_id, description,
  1709. qty, sellprice, discount,
  1710. unit, reqdate, project_id,
  1711. ship, serialnumber, notes)
  1712. VALUES (
  1713. $id, $item->{parts_id}, |.$dbh->quote($item->{description}).qq|,
  1714. $item->{qty}, $item->{sellprice}, $item->{discount},
  1715. |.$dbh->quote($item->{unit}).qq|, |.$form->dbquote($item->{reqdate}, SQL_DATE).qq|, $item->{project_id},
  1716. $item->{ship}, |.$dbh->quote($item->{serialnumber}).qq|, |.$dbh->quote($item->{notes}).qq|)|;
  1717. $dbh->do($query) || $form->dberror($query);
  1718. }
  1719. }
  1720. }
  1721. $rc = $dbh->commit;
  1722. $dbh->disconnect;
  1723. $rc;
  1724. }
  1725. 1;