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