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