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