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