summaryrefslogtreecommitdiff
path: root/LedgerSMB/IC.pm
blob: 9bda70326b7e4511e962010ca516939c38d0fc46 (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) 2000
  18. #
  19. # Author: DWS Systems Inc.
  20. # Web: http://www.sql-ledger.org
  21. #
  22. # Contributors:
  23. #
  24. #======================================================================
  25. #
  26. # This file has NOT undergone whitespace cleanup.
  27. #
  28. #======================================================================
  29. #
  30. # Inventory Control backend
  31. #
  32. #======================================================================
  33. package IC;
  34. sub get_part {
  35. my ($self, $myconfig, $form) = @_;
  36. # connect to db
  37. my $dbh = $form->{dbh};
  38. my $i;
  39. my $query = qq|
  40. SELECT p.*, c1.accno AS inventory_accno,
  41. c1.description AS inventory_description,
  42. c2.accno AS income_accno,
  43. c2.description AS income_description,
  44. c3.accno AS expense_accno,
  45. c3.description AS expense_description, pg.partsgroup
  46. FROM parts p
  47. LEFT JOIN chart c1 ON (p.inventory_accno_id = c1.id)
  48. LEFT JOIN chart c2 ON (p.income_accno_id = c2.id)
  49. LEFT JOIN chart c3 ON (p.expense_accno_id = c3.id)
  50. LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
  51. WHERE p.id = ?|;
  52. my $sth = $dbh->prepare($query);
  53. $sth->execute($form->{id}) || $form->dberror($query);
  54. my $ref = $sth->fetchrow_hashref(NAME_lc);
  55. # copy to $form variables
  56. for (keys %$ref) { $form->{$_} = $ref->{$_} }
  57. $sth->finish;
  58. # part, service item or labor
  59. $form->{item} = ($form->{inventory_accno_id}) ? 'part' : 'service';
  60. $form->{item} = 'labor' if ! $form->{income_accno_id};
  61. if ($form->{assembly}) {
  62. $form->{item} = 'assembly';
  63. # retrieve assembly items
  64. $query = qq|
  65. SELECT p.id, p.partnumber, p.description,
  66. p.sellprice, p.weight, a.qty, a.bom, a.adj,
  67. p.unit, p.lastcost, p.listprice,
  68. pg.partsgroup, p.assembly, p.partsgroup_id
  69. FROM parts p
  70. JOIN assembly a ON (a.parts_id = p.id)
  71. LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
  72. WHERE a.id = ?|;
  73. $sth = $dbh->prepare($query);
  74. $sth->execute($form->{id}) || $form->dberror($query);
  75. $form->{assembly_rows} = 0;
  76. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  77. $form->{assembly_rows}++;
  78. foreach my $key ( keys %{ $ref } ) {
  79. $form->{"${key}_$form->{assembly_rows}"}
  80. = $ref->{$key};
  81. }
  82. }
  83. $sth->finish;
  84. }
  85. # setup accno hash for <option checked>
  86. # {amount} is used in create_links
  87. for (qw(inventory income expense)) {
  88. $form->{amount}{"IC_$_"}
  89. = {
  90. accno => $form->{"${_}_accno"},
  91. description => $form->{"${_}_description"}
  92. };
  93. };
  94. if ($form->{item} =~ /(part|assembly)/) {
  95. if ($form->{makemodel} ne "") {
  96. $query = qq|
  97. SELECT make, model
  98. FROM makemodel
  99. WHERE parts_id = ?|;
  100. $sth = $dbh->prepare($query);
  101. $sth->execute($form->{id}) || $form->dberror($query);
  102. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  103. push @{ $form->{makemodels} }, $ref;
  104. }
  105. $sth->finish;
  106. }
  107. }
  108. # now get accno for taxes
  109. $query = qq|
  110. SELECT c.accno FROM chart c, partstax pt
  111. WHERE pt.chart_id = c.id AND pt.parts_id = ?|;
  112. $sth = $dbh->prepare($query);
  113. $sth->execute($form->{id}) || $form->dberror($query);
  114. while (($key) = $sth->fetchrow_array) {
  115. $form->{amount}{$key} = $key;
  116. }
  117. $sth->finish;
  118. my $id = $dbh->quote($form->{id});
  119. # is it an orphan
  120. $query = qq|
  121. SELECT parts_id FROM invoice WHERE parts_id = $id
  122. UNION
  123. SELECT parts_id FROM orderitems WHERE parts_id = $id
  124. UNION
  125. SELECT parts_id FROM assembly WHERE parts_id = $id
  126. UNION
  127. SELECT parts_id FROM jcitems WHERE parts_id = $id|;
  128. ($form->{orphaned}) = $dbh->selectrow_array($query);
  129. $form->{orphaned} = !$form->{orphaned};
  130. $form->{orphaned} = 0 if $form->{project_id};
  131. if ($form->{item} eq 'assembly') {
  132. if ($form->{orphaned}) {
  133. $form->{orphaned} = !$form->{onhand};
  134. }
  135. }
  136. if ($form->{item} =~ /(part|service)/) {
  137. # get vendors
  138. $query = qq|
  139. SELECT v.id, v.name, pv.partnumber,
  140. pv.lastcost, pv.leadtime,
  141. pv.curr AS vendorcurr
  142. FROM partsvendor pv
  143. JOIN vendor v ON (v.id = pv.vendor_id)
  144. WHERE pv.parts_id = ?
  145. ORDER BY 2|;
  146. $sth = $dbh->prepare($query);
  147. $sth->execute($form->{id}) || $form->dberror($query);
  148. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  149. push @{ $form->{vendormatrix} }, $ref;
  150. }
  151. $sth->finish;
  152. }
  153. # get matrix
  154. if ($form->{item} ne 'labor') {
  155. $query = qq|
  156. SELECT pc.pricebreak, pc.sellprice AS customerprice,
  157. pc.curr AS customercurr, pc.validfrom,
  158. pc.validto, c.name, c.id AS cid,
  159. g.pricegroup, g.id AS gid
  160. FROM partscustomer pc
  161. LEFT JOIN customer c ON (c.id = pc.customer_id)
  162. LEFT JOIN pricegroup g ON (g.id = pc.pricegroup_id)
  163. WHERE pc.parts_id = ?
  164. ORDER BY c.name, g.pricegroup, pc.pricebreak|;
  165. $sth = $dbh->prepare($query);
  166. $sth->execute($form->{id}) || $form->dberror($query);
  167. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  168. push @{ $form->{customermatrix} }, $ref;
  169. }
  170. $sth->finish;
  171. }
  172. $form->run_custom_queries('parts', 'SELECT');
  173. }
  174. sub save {
  175. my ($self, $myconfig, $form) = @_;
  176. ($form->{inventory_accno}) = split(/--/, $form->{IC_inventory});
  177. ($form->{expense_accno}) = split(/--/, $form->{IC_expense});
  178. ($form->{income_accno}) = split(/--/, $form->{IC_income});
  179. my $dbh = $form->{dbh};
  180. # undo amount formatting
  181. for (qw(rop weight listprice sellprice lastcost stock)) {
  182. $form->{$_} = $form->parse_amount($myconfig, $form->{$_});
  183. }
  184. $form->{makemodel} = (($form->{make_1}) || ($form->{model_1})) ? 1 : 0;
  185. $form->{assembly} = ($form->{item} eq 'assembly') ? 1 : 0;
  186. for (qw(alternate obsolete onhand)) { $form->{$_} *= 1 }
  187. my $query;
  188. my $sth;
  189. my $i;
  190. my $null;
  191. my $vendor_id;
  192. my $customer_id;
  193. if ($form->{id}) {
  194. # get old price
  195. $query = qq|
  196. SELECT id, listprice, sellprice, lastcost, weight,
  197. project_id
  198. FROM parts
  199. WHERE id = ?|;
  200. my $sth = $dbh->prepare($query);
  201. $sth->execute($form->{id});
  202. my ($id, $listprice, $sellprice, $lastcost, $weight,
  203. $project_id)
  204. = $dbh->fetchrow_array();
  205. if ($id) {
  206. if (!$project_id) {
  207. # if item is part of an assembly
  208. # adjust all assemblies
  209. $query = qq|
  210. SELECT id, qty, adj
  211. FROM assembly
  212. WHERE parts_id = ?|;
  213. $sth = $dbh->prepare($query);
  214. $sth->execute($form->{id}) ||
  215. $form->dberror($query);
  216. while (my ($id, $qty, $adj)
  217. = $sth->fetchrow_array) {
  218. &update_assembly(
  219. $dbh, $form, $id, $qty, $adj,
  220. $listprice * 1, $sellprice * 1,
  221. $lastcost * 1, $weight * 1);
  222. }
  223. $sth->finish;
  224. }
  225. if ($form->{item} =~ /(part|service)/) {
  226. # delete partsvendor records
  227. $query = qq|
  228. DELETE FROM partsvendor
  229. WHERE parts_id = ?|;
  230. $sth = $dbh->prepare($query);
  231. $sth->execute($form->{id})
  232. || $form->dberror($query);
  233. }
  234. if ($form->{item} !~ /(service|labor)/) {
  235. # delete makemodel records
  236. $query = qq|
  237. DELETE FROM makemodel
  238. WHERE parts_id = ?|;
  239. $sth = $dbh->prepare($query);
  240. $sth->execute($form->{id})
  241. || $form->dberror($query);
  242. }
  243. if ($form->{item} eq 'assembly') {
  244. if ($form->{onhand}) {
  245. &adjust_inventory(
  246. $dbh, $form, $form->{id},
  247. $form->{onhand} * -1);
  248. }
  249. if ($form->{orphaned}) {
  250. # delete assembly records
  251. $query = qq|
  252. DELETE FROM assembly
  253. WHERE id = ?|;
  254. $sth = $dbh->prepare($query);
  255. $sth->execute($form->{id})
  256. || $form->dberror($query);
  257. } else {
  258. for $i (1 ..
  259. $form->{assembly_rows} - 1) {
  260. # update BOM, A only
  261. for (qw(bom adj)) {
  262. $form->{"${_}_$i"}
  263. *= 1;
  264. }
  265. $query = qq|
  266. UPDATE assembly
  267. SET bom = ?,
  268. adj = ?
  269. WHERE id = ?
  270. AND parts_id = ?|;
  271. $sth = $dbh->prepare($query);
  272. $sth->execute(
  273. $form->{"bom_$i"},
  274. $form->{"adj_$i"},
  275. $form->{id},
  276. $form->{"id_$i"}
  277. )|| $form->dberror(
  278. $query);
  279. }
  280. }
  281. $form->{onhand} += $form->{stock};
  282. }
  283. # delete tax records
  284. $query = qq|DELETE FROM partstax WHERE parts_id = ?|;
  285. $sth = $dbh->prepare($query);
  286. $sth->execute($form->{id})|| $form->dberror($query);
  287. # delete matrix
  288. $query = qq|
  289. DELETE FROM partscustomer
  290. WHERE parts_id = ?|;
  291. $sth = $dbh->prepare($query);
  292. $sth->execute($form->{id})|| $form->dberror($query);
  293. } else {
  294. $query = qq|INSERT INTO parts (id) VALUES (?)|;
  295. $sth = $dbh->prepare($query);
  296. $sth->execute($form->{id})|| $form->dberror($query);
  297. }
  298. }
  299. if (!$form->{id}) {
  300. my $uid = localtime;
  301. $uid .= "$$";
  302. $query = qq|INSERT INTO parts (partnumber) VALUES ('$uid')|;
  303. $dbh->do($query) || $form->dberror($query);
  304. $query = qq|SELECT id FROM parts WHERE partnumber = '$uid'|;
  305. $sth = $dbh->prepare($query);
  306. $sth->execute || $form->dberror($query);
  307. ($form->{id}) = $sth->fetchrow_array;
  308. $sth->finish;
  309. $form->{orphaned} = 1;
  310. $form->{onhand} = ($form->{stock} * 1)
  311. if $form->{item} eq 'assembly';
  312. }
  313. my $partsgroup_id;
  314. ($null, $partsgroup_id) = split /--/, $form->{partsgroup};
  315. $partsgroup_id *= 1;
  316. $form->{partnumber} = $form->update_defaults(
  317. $myconfig, "partnumber", $dbh) if ! $form->{partnumber};
  318. $query = qq|
  319. UPDATE parts
  320. SET partnumber = ?,
  321. description = ?,
  322. makemodel = ?,
  323. alternate = ?,
  324. assembly = ?,
  325. listprice = ?,
  326. sellprice = ?,
  327. lastcost = ?,
  328. weight = ?,
  329. priceupdate = ?,
  330. unit = ?,
  331. notes = ?,
  332. rop = ?,
  333. bin = ?,
  334. inventory_accno_id = (SELECT id FROM chart
  335. WHERE accno = ?),
  336. income_accno_id = (SELECT id FROM chart
  337. WHERE accno = ?),
  338. expense_accno_id = (SELECT id FROM chart
  339. WHERE accno = ?),
  340. obsolete = ?,
  341. image = ?,
  342. drawing = ?,
  343. microfiche = ?,
  344. partsgroup_id = ?
  345. WHERE id = ?|;
  346. $sth = $dbh->prepare($query);
  347. $sth->execute(
  348. $form->{partnumber}, $form->{description}, $form->{makemodel},
  349. $form->{alternate}, $form->{assembly}, $form->{listprice},
  350. $form->{sellprice}, $form->{lastcost}, $form->{weight},
  351. $form->{priceupdate}, $form->{unit}, $form->{notes},
  352. $form->{rop}, $form->{bin}, $form->{inventory_accno},
  353. $form->{income_accno}, $form->{expense_accno},
  354. $form->{obsolete}, $form->{image}, $form->{drawing},
  355. $form->{microfiche}, $partsgroup_id, $form->{id}
  356. ) || $form->dberror($query);
  357. # insert makemodel records
  358. if ($form->{item} =~ /(part|assembly)/) {
  359. $query = qq|
  360. INSERT INTO makemodel (parts_id, make, model)
  361. VALUES (?, ?, ?)|;
  362. $sth = $dbh->prepare($query) || $form->dberror($query);
  363. for $i (1 .. $form->{makemodel_rows}) {
  364. if (($form->{"make_$i"} ne "")
  365. || ($form->{"model_$i"} ne "")) {
  366. $sth->execute(
  367. $form->{id}, $form->{"make_$i"},
  368. $form->{"model_$i"}
  369. ) || $form->dberror($query);
  370. }
  371. }
  372. }
  373. # insert taxes
  374. $query = qq|
  375. INSERT INTO partstax (parts_id, chart_id)
  376. VALUES (?, (SELECT id FROM chart WHERE accno = ?))|;
  377. $sth = $dbh->prepare($query);
  378. for (split / /, $form->{taxaccounts}) {
  379. if ($form->{"IC_tax_$_"}) {
  380. $sth->execute($form->{id}, $_)
  381. || $form->dberror($query);
  382. }
  383. }
  384. @a = localtime;
  385. $a[5] += 1900;
  386. $a[4]++;
  387. $a[4] = substr("0$a[4]", -2);
  388. $a[3] = substr("0$a[3]", -2);
  389. my $shippingdate = "$a[5]$a[4]$a[3]";
  390. ($form->{employee}, $form->{employee_id}) = $form->get_employee($dbh);
  391. # add assembly records
  392. if ($form->{item} eq 'assembly' && !$project_id) {
  393. if ($form->{orphaned}) {
  394. $query = qq|
  395. INSERT INTO assembly
  396. (id, parts_id, qty, bom, adj)
  397. VALUES (?, ?, ?, ?, ?)|;
  398. $sth = $dbh->prepare($query);
  399. for $i (1 .. $form->{assembly_rows}) {
  400. $form->{"qty_$i"} = $form->parse_amount(
  401. $myconfig, $form->{"qty_$i"});
  402. $sth->execute(
  403. $form->{id}, $form->{"id_$i"},
  404. $form->{"qty_$i"}, $form->{"bom_$i"},
  405. $form->{"adj_$i"}
  406. ) || $form->dberror($query);
  407. }
  408. }
  409. # adjust onhand for the parts
  410. if ($form->{onhand}) {
  411. &adjust_inventory(
  412. $dbh, $form, $form->{id}, $form->{onhand});
  413. }
  414. }
  415. # add vendors
  416. if ($form->{item} ne 'assembly') {
  417. $updparts{$form->{id}} = 1;
  418. for $i (1 .. $form->{vendor_rows}) {
  419. if (($form->{"vendor_$i"} ne "")
  420. && $form->{"lastcost_$i"}) {
  421. ($null, $vendor_id)
  422. = split /--/, $form->{"vendor_$i"};
  423. for (qw(lastcost leadtime)) {
  424. $form->{"${_}_$i"}
  425. = $form->parse_amount(
  426. $myconfig,
  427. $form->{"${_}_$i"});
  428. }
  429. $query = qq|
  430. INSERT INTO partsvendor
  431. (vendor_id, parts_id,
  432. partnumber, lastcost,
  433. leadtime, curr)
  434. VALUES (?, ?, ?, ?, ?, ?)|;
  435. $sth = $dbh->prepare($query);
  436. $sth->execute(
  437. $vendor_id, $form->{id},
  438. $form->{"partnumber_$i"},
  439. $form->{"lastcost_$i"},
  440. $form->{"leadtime_$i"},
  441. $form->{"vendorcurr_$i"}
  442. )|| $form->dberror($query);
  443. }
  444. }
  445. }
  446. # add pricematrix
  447. for $i (1 .. $form->{customer_rows}) {
  448. for (qw(pricebreak customerprice)) {
  449. $form->{"${_}_$i"} = $form->parse_amount(
  450. $myconfig, $form->{"${_}_$i"});
  451. }
  452. if ($form->{"customerprice_$i"}) {
  453. ($null, $customer_id)
  454. = split /--/, $form->{"customer_$i"};
  455. $customer_id *= 1;
  456. ($null, $pricegroup_id)
  457. = split /--/, $form->{"pricegroup_$i"};
  458. $query = qq|
  459. INSERT INTO partscustomer
  460. (parts_id, customer_id,
  461. pricegroup_id, pricebreak,
  462. sellprice, curr,
  463. validfrom, validto)
  464. VALUES (?, ?, ?, ?, ?, ?, ?, ?)|;
  465. $sth = $dbh->prepare($query);
  466. $sth->execute(
  467. $form->{id}, $customer_id, $pricegroup_id,
  468. $form->{"pricebreak_$i"},
  469. $form->{"customerprice_$i"},
  470. $form->{"customercurr_$i"},
  471. $form->{"validfrom_$i"}, $form->{"validto_$i"}
  472. )|| $form->dberror($query);
  473. }
  474. }
  475. my $rc = $dbh->commit;
  476. $form->run_custom_queries('parts', 'UPDATE');
  477. $rc;
  478. }
  479. sub update_assembly {
  480. my ($dbh, $form, $id, $qty, $adj, $listprice, $sellprice, $lastcost,
  481. $weight) = @_;
  482. my $formlistprice = $form->{listprice};
  483. my $formsellprice = $form->{sellprice};
  484. if (!$adj) {
  485. $formlistprice = $listprice;
  486. $formsellprice = $sellprice;
  487. }
  488. my $query = qq|SELECT id, qty, adj FROM assembly WHERE parts_id = ?|;
  489. my $sth = $dbh->prepare($query);
  490. $sth->execute($id) || $form->dberror($query);
  491. $form->{$id} = 1; # Not sure what this is for...
  492. # In fact, we don't seem to use it... Chris T
  493. while (my ($pid, $aqty, $aadj) = $sth->fetchrow_array) {
  494. &update_assembly($dbh, $form, $pid, $aqty * $qty, $aadj,
  495. $listprice, $sellprice, $lastcost, $weight)
  496. if !$form->{$pid};
  497. }
  498. $sth->finish;
  499. $qty = $dbh->quote($qty);
  500. $formlistprice = $dbh->quote($formlistprice );
  501. $listprice = $dbh->quote($listprice );
  502. $formsellprice = $dbh->quote($formsellprice );
  503. $formlastcost = $dbh->quote($form->{lastcost});
  504. $lastcost = $dbh->quote($lastcost);
  505. $weight = $dbh->quote($weight);
  506. $id = $dbh->quote($id);
  507. $query = qq|
  508. UPDATE parts
  509. SET listprice = listprice +
  510. $qty * ($formlistprice - $listprice),
  511. sellprice = sellprice +
  512. $qty * ($formsellprice - $sellprice),
  513. lastcost = lastcost +
  514. $qty * ($form->{lastcost} - $lastcost),
  515. weight = weight +
  516. $qty * ($form->{weight} - $weight)
  517. WHERE id = $id|;
  518. $dbh->do($query) || $form->dberror($query);
  519. delete $form->{$id};
  520. }
  521. sub retrieve_assemblies {
  522. my ($self, $myconfig, $form) = @_;
  523. # connect to database
  524. my $dbh = $form->{dbh};
  525. my $where = '1 = 1';
  526. if ($form->{partnumber} ne "") {
  527. my $partnumber = $dbh->quote($form->like(
  528. lc $form->{partnumber}));
  529. $where .= " AND lower(p.partnumber) LIKE $partnumber";
  530. }
  531. if ($form->{description} ne "") {
  532. my $description = $dbh->($form->like(lc $form->{description}));
  533. $where .= " AND lower(p.description) LIKE $description";
  534. }
  535. $where .= qq| AND p.obsolete = '0'
  536. AND p.project_id IS NULL|;
  537. my %ordinal = (
  538. 'partnumber' => 2,
  539. 'description' => 3,
  540. 'bin' => 4
  541. );
  542. my @a = qw(partnumber description bin);
  543. my $sortorder = $form->sort_order(\@a, \%ordinal);
  544. # retrieve assembly items
  545. my $query = qq|
  546. SELECT p.id, p.partnumber, p.description, p.bin, p.onhand,
  547. p.rop
  548. FROM parts p
  549. WHERE $where
  550. AND p.assembly = '1'
  551. ORDER BY $sortorder|;
  552. my $sth = $dbh->prepare($query);
  553. $sth->execute || $form->dberror($query);
  554. $query = qq|
  555. SELECT sum(p.inventory_accno_id), p.assembly
  556. FROM parts p
  557. JOIN assembly a ON (a.parts_id = p.id)
  558. WHERE a.id = ?
  559. GROUP BY p.assembly|;
  560. my $svh = $dbh->prepare($query) || $form->dberror($query);
  561. my $inh;
  562. if ($form->{checkinventory}) {
  563. $query = qq|
  564. SELECT p.id, p.onhand, a.qty
  565. FROM parts p
  566. JOIN assembly a ON (a.parts_id = p.id)
  567. WHERE (p.inventory_accno_id > 0 OR p.assembly)
  568. AND p.income_accno_id > 0 AND a.id = ?|;
  569. $inh = $dbh->prepare($query) || $form->dberror($query);
  570. }
  571. my %available = ();
  572. my %required;
  573. my $ref;
  574. my $aref;
  575. my $stock;
  576. my $howmany;
  577. my $ok;
  578. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  579. $svh->execute($ref->{id});
  580. ($ref->{inventory}, $ref->{assembly}) = $svh->fetchrow_array;
  581. $svh->finish;
  582. if ($ref->{inventory} || $ref->{assembly}) {
  583. $ok = 1;
  584. if ($form->{checkinventory}) {
  585. $inh->execute($ref->{id})
  586. || $form->dberror($query);;
  587. $ok = 0;
  588. %required = ();
  589. while ($aref
  590. = $inh->fetchrow_hashref(NAME_lc)) {
  591. $available{$aref->{id}} =
  592. (exists $available{$aref->{id}})
  593. ? $available{$aref->{id}}
  594. : $aref->{onhand};
  595. $required{$aref->{id}} = $aref->{qty};
  596. if ($available{$aref->{id}}
  597. >= $aref->{qty}) {
  598. $howmany =
  599. ($aref->{qty})
  600. ? int $available{
  601. $aref->{id}}
  602. /$aref->{qty}
  603. : 1;
  604. if ($stock) {
  605. $stock =
  606. ($stock
  607. > $howmany)
  608. ? $howmany
  609. : $stock;
  610. } else {
  611. $stock = $howmany;
  612. }
  613. $ok = 1;
  614. $available{$aref->{id}}
  615. -= $aref->{qty}
  616. * $stock;
  617. } else {
  618. $ok = 0;
  619. for (keys %required) {
  620. $available{$_} +=
  621. $required{$_}
  622. * $stock;
  623. }
  624. $stock = 0;
  625. last;
  626. }
  627. }
  628. $inh->finish;
  629. $ref->{stock} = $stock;
  630. }
  631. push @{ $form->{assembly_items} }, $ref if $ok;
  632. }
  633. }
  634. $sth->finish;
  635. $dbh->commit;
  636. }
  637. sub restock_assemblies {
  638. my ($self, $myconfig, $form) = @_;
  639. # connect to database
  640. my $dbh = $form->{dbh};
  641. for my $i (1 .. $form->{rowcount}) {
  642. $form->{"qty_$i"} = $form->parse_amount(
  643. $myconfig, $form->{"qty_$i"});
  644. if ($form->{"qty_$i"}) {
  645. &adjust_inventory(
  646. $dbh, $form, $form->{"id_$i"},
  647. $form->{"qty_$i"});
  648. }
  649. }
  650. my $rc = $dbh->commit;
  651. $rc;
  652. }
  653. sub adjust_inventory {
  654. # Private method. Do not commit transaction at end of function...
  655. my ($dbh, $form, $id, $qty) = @_;
  656. my $query = qq|
  657. SELECT p.id, p.inventory_accno_id, p.assembly, a.qty
  658. FROM parts p
  659. JOIN assembly a ON (a.parts_id = p.id)
  660. WHERE a.id = ?|;
  661. my $sth = $dbh->prepare($query);
  662. $sth->execute($id) || $form->dberror($query);
  663. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  664. # is it a service item then loop
  665. if (! $ref->{inventory_accno_id}) {
  666. next if ! $ref->{assembly};
  667. }
  668. # adjust parts onhand
  669. $form->update_balance(
  670. $dbh, "parts", "onhand", qq|id = $ref->{id}|,
  671. $qty * $ref->{qty} * -1);
  672. }
  673. $sth->finish;
  674. # update assembly
  675. $form->update_balance($dbh, "parts", "onhand", qq|id = $id|, $qty);
  676. }
  677. sub delete {
  678. my ($self, $myconfig, $form) = @_;
  679. my $dbh = $form->{dbh};
  680. my $query;
  681. $query = qq|DELETE FROM parts WHERE id = ?|;
  682. $sth = $dbh->prepare($query);
  683. $sth->execute($form->{id}) || $form->dberror($query);
  684. $query = qq|DELETE FROM partstax WHERE parts_id = ?|;
  685. $sth = $dbh->prepare($query);
  686. $sth->execute($form->{id}) || $form->dberror($query);
  687. if ($form->{item} ne 'assembly') {
  688. $query = qq|DELETE FROM partsvendor WHERE parts_id = ?|;
  689. $sth = $dbh->prepare($query);
  690. $sth->execute($form->{id}) || $form->dberror($query);
  691. }
  692. # check if it is a part, assembly or service
  693. if ($form->{item} ne 'service') {
  694. $query = qq|DELETE FROM makemodel WHERE parts_id = ?|;
  695. $sth = $dbh->prepare($query);
  696. $sth->execute($form->{id}) || $form->dberror($query);
  697. }
  698. if ($form->{item} eq 'assembly') {
  699. $query = qq|DELETE FROM assembly WHERE id = ?|;
  700. $sth = $dbh->prepare($query);
  701. $sth->execute($form->{id}) || $form->dberror($query);
  702. }
  703. $query = qq|DELETE FROM inventory WHERE parts_id = ?|;
  704. $sth = $dbh->prepare($query);
  705. $sth->execute($form->{id}) || $form->dberror($query);
  706. $query = qq|DELETE FROM partscustomer WHERE parts_id = ?|;
  707. $sth = $dbh->prepare($query);
  708. $sth->execute($form->{id}) || $form->dberror($query);
  709. $query = qq|DELETE FROM translation WHERE trans_id = ?|;
  710. $sth = $dbh->prepare($query);
  711. $sth->execute($form->{id}) || $form->dberror($query);
  712. # commit
  713. my $rc = $dbh->commit;
  714. $rc;
  715. }
  716. sub assembly_item {
  717. my ($self, $myconfig, $form) = @_;
  718. my $dbh = $form->{dbh};
  719. my $i = $form->{assembly_rows};
  720. my $var;
  721. my $null;
  722. my $where = "p.obsolete = '0'";
  723. if ($form->{"partnumber_$i"} ne "") {
  724. $var = $dbh->quote($form->like(lc $form->{"partnumber_$i"}));
  725. $where .= " AND lower(p.partnumber) LIKE $var";
  726. }
  727. if ($form->{"description_$i"} ne "") {
  728. $var = $dbh->quote($form->like(lc $form->{"description_$i"}));
  729. $where .= " AND lower(p.description) LIKE $var";
  730. }
  731. if ($form->{"partsgroup_$i"} ne "") {
  732. ($null, $var) = split /--/, $form->{"partsgroup_$i"};
  733. $var = $dbh->quote($var);
  734. $where .= qq| AND p.partsgroup_id = $var|;
  735. }
  736. if ($form->{id}) {
  737. $where .= " AND p.id != ".$dbh->quote($form->{id});
  738. }
  739. if ($form->{"description_$i"} ne "") {
  740. $where .= " ORDER BY p.description";
  741. } else {
  742. $where .= " ORDER BY p.partnumber";
  743. }
  744. my $query = qq|
  745. SELECT p.id, p.partnumber, p.description, p.sellprice,
  746. p.weight, p.onhand, p.unit, p.lastcost,
  747. pg.partsgroup, p.partsgroup_id
  748. FROM parts p
  749. LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
  750. WHERE $where|;
  751. my $sth = $dbh->prepare($query);
  752. $sth->execute || $form->dberror($query);
  753. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  754. push @{ $form->{item_list} }, $ref;
  755. }
  756. $sth->finish;
  757. $dbh->disconnect;
  758. }
  759. sub all_parts {
  760. my ($self, $myconfig, $form) = @_;
  761. $dbh = $form->{dbh};
  762. my $where = '1 = 1';
  763. my $null;
  764. my $var;
  765. my $ref;
  766. for (qw(partnumber drawing microfiche)) {
  767. if ($form->{$_} ne "") {
  768. $var = $dbh->quote($form->like(lc $form->{$_}));
  769. $where .= " AND lower(p.$_) LIKE $var";
  770. }
  771. }
  772. # special case for description
  773. if ($form->{description} ne "") {
  774. unless ($form->{bought} || $form->{sold} || $form->{onorder}
  775. || $form->{ordered} || $form->{rfq} || $form->{quoted}
  776. ) {
  777. $var = $dbh->quote($form->like(
  778. lc $form->{description}));
  779. $where .= " AND lower(p.description) LIKE $var";
  780. }
  781. }
  782. # assembly components
  783. my $assemblyflds;
  784. if ($form->{searchitems} eq 'component') {
  785. $assemblyflds = qq|, p1.partnumber AS assemblypartnumber,
  786. a.id AS assembly_id|;
  787. }
  788. # special case for serialnumber
  789. if ($form->{l_serialnumber}) {
  790. if ($form->{serialnumber} ne "") {
  791. $var = $dbh->quote(
  792. $form->like(lc $form->{serialnumber}));
  793. $where .= " AND lower(i.serialnumber) LIKE $var";
  794. }
  795. }
  796. if (($form->{warehouse} ne "") || $form->{l_warehouse}) {
  797. $form->{l_warehouse} = 1;
  798. }
  799. if ($form->{searchitems} eq 'part') {
  800. $where .= " AND p.inventory_accno_id > 0 AND p.income_accno_id > 0";
  801. }
  802. if ($form->{searchitems} eq 'assembly') {
  803. $form->{bought} = "";
  804. $where .= " AND p.assembly = '1'";
  805. }
  806. if ($form->{searchitems} eq 'service') {
  807. $where .= " AND p.assembly = '0' AND p.inventory_accno_id IS NULL";
  808. }
  809. if ($form->{searchitems} eq 'labor') {
  810. $where .= " AND p.inventory_accno_id > 0 AND p.income_accno_id IS NULL";
  811. }
  812. # items which were never bought, sold or on an order
  813. if ($form->{itemstatus} eq 'orphaned') {
  814. $where .= qq|
  815. AND p.onhand = 0
  816. AND p.id NOT IN (SELECT p.id FROM parts p
  817. JOIN invoice i
  818. ON (p.id = i.parts_id))
  819. AND p.id NOT IN (SELECT p.id FROM parts p
  820. JOIN assembly a
  821. ON (p.id = a.parts_id))
  822. AND p.id NOT IN (SELECT p.id FROM parts p
  823. JOIN orderitems o
  824. ON (p.id = o.parts_id))
  825. AND p.id NOT IN (SELECT p.id FROM parts p
  826. JOIN jcitems j
  827. ON (p.id = j.parts_id))|;
  828. }
  829. if ($form->{itemstatus} eq 'active') {
  830. $where .= " AND p.obsolete = '0'";
  831. }
  832. if ($form->{itemstatus} eq 'obsolete') {
  833. $where .= " AND p.obsolete = '1'";
  834. }
  835. if ($form->{itemstatus} eq 'onhand') {
  836. $where .= " AND p.onhand > 0";
  837. }
  838. if ($form->{itemstatus} eq 'short') {
  839. $where .= " AND p.onhand < p.rop";
  840. }
  841. my $makemodelflds = qq|, '', ''|;;
  842. my $makemodeljoin;
  843. if (($form->{make} ne "") || $form->{l_make} || ($form->{model} ne "")
  844. || $form->{l_model}) {
  845. $makemodelflds = qq|, m.make, m.model|;
  846. $makemodeljoin = qq|LEFT JOIN makemodel m ON (m.parts_id = p.id)|;
  847. if ($form->{make} ne "") {
  848. $var = $dbh->quote($form->like(lc $form->{make}));
  849. $where .= " AND lower(m.make) LIKE $var";
  850. }
  851. if ($form->{model} ne "") {
  852. $var = $dbh->quote($form->like(lc $form->{model}));
  853. $where .= " AND lower(m.model) LIKE $var";
  854. }
  855. }
  856. if ($form->{partsgroup} ne "") {
  857. ($null, $var) = split /--/, $form->{partsgroup};
  858. $where .= qq| AND p.partsgroup_id = | . $dbh->quote($var);
  859. }
  860. my %ordinal = (
  861. 'partnumber' => 2,
  862. 'description' => 3,
  863. 'bin' => 6,
  864. 'priceupdate' => 13,
  865. 'drawing' => 15,
  866. 'microfiche' => 16,
  867. 'partsgroup' => 18,
  868. 'make' => 21,
  869. 'model' => 22,
  870. 'assemblypartnumber' => 23
  871. );
  872. my @a = qw(partnumber description);
  873. my $sortorder = $form->sort_order(\@a, \%ordinal);
  874. my $query = qq|SELECT value FROM defaults WHERE setting_key = 'curr'|;
  875. my ($curr) = $dbh->selectrow_array($query);
  876. $curr =~ s/:.*//;
  877. $curr = $dbh->quote($curr);
  878. my $flds = qq|
  879. p.id, p.partnumber, p.description, p.onhand, p.unit,
  880. p.bin, p.sellprice, p.listprice, p.lastcost, p.rop,
  881. p.avgcost,
  882. p.weight, p.priceupdate, p.image, p.drawing, p.microfiche,
  883. p.assembly, pg.partsgroup, $curr AS curr,
  884. c1.accno AS inventory, c2.accno AS income, c3.accno AS expense,
  885. p.notes
  886. $makemodelflds $assemblyflds
  887. |;
  888. $query = qq|
  889. SELECT $flds
  890. FROM parts p
  891. LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
  892. LEFT JOIN chart c1 ON (c1.id = p.inventory_accno_id)
  893. LEFT JOIN chart c2 ON (c2.id = p.income_accno_id)
  894. LEFT JOIN chart c3 ON (c3.id = p.expense_accno_id)
  895. $makemodeljoin
  896. WHERE $where
  897. ORDER BY $sortorder|;
  898. # redo query for components report
  899. if ($form->{searchitems} eq 'component') {
  900. $flds =~ s/p.onhand/a.qty AS onhand/;
  901. $query = qq|
  902. SELECT $flds
  903. FROM assembly a
  904. JOIN parts p ON (a.parts_id = p.id)
  905. JOIN parts p1 ON (a.id = p1.id)
  906. LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
  907. LEFT JOIN chart c1 ON (c1.id = p.inventory_accno_id)
  908. LEFT JOIN chart c2 ON (c2.id = p.income_accno_id)
  909. LEFT JOIN chart c3 ON (c3.id = p.expense_accno_id)
  910. $makemodeljoin
  911. WHERE $where
  912. ORDER BY $sortorder|;
  913. }
  914. # rebuild query for bought and sold items
  915. if ($form->{bought} || $form->{sold} || $form->{onorder}
  916. || $form->{ordered} || $form->{rfq} || $form->{quoted}
  917. ) {
  918. $form->sort_order();
  919. @a = qw(partnumber description curr employee name
  920. serialnumber id);
  921. push @a, "invnumber" if ($form->{bought} || $form->{sold});
  922. push @a, "ordnumber" if ($form->{onorder} || $form->{ordered});
  923. push @a, "quonumber" if ($form->{rfq} || $form->{quoted});
  924. %ordinal = (
  925. 'partnumber' => 2,
  926. 'description' => 3,
  927. 'serialnumber' => 4,
  928. 'bin' => 7,
  929. 'priceupdate' => 14,
  930. 'partsgroup' => 19,
  931. 'invnumber' => 20,
  932. 'ordnumber' => 21,
  933. 'quonumber' => 22,
  934. 'name' => 24,
  935. 'employee' => 25,
  936. 'curr' => 26,
  937. 'make' => 29,
  938. 'model' => 30
  939. );
  940. $sortorder = $form->sort_order(\@a, \%ordinal);
  941. my $union = "";
  942. $query = "";
  943. if ($form->{bought} || $form->{sold}) {
  944. my $invwhere = "$where";
  945. my $transdate =
  946. ($form->{method} eq 'accrual')
  947. ? "transdate"
  948. : "datepaid";
  949. $invwhere .= " AND i.assemblyitem = '0'";
  950. $invwhere .= " AND a.$transdate >= " .
  951. $dbh->quote($form->{transdatefrom})
  952. if $form->{transdatefrom};
  953. $invwhere .= " AND a.$transdate <= " .
  954. $dbh->quote($form->{transdateto})
  955. if $form->{transdateto};
  956. if ($form->{description} ne "") {
  957. $var = dbh->quote(
  958. $form->like(lc $form->{description}));
  959. $invwhere .=
  960. " AND lower(i.description) LIKE $var";
  961. }
  962. if ($form->{open} || $form->{closed}) {
  963. if ($form->{open} && $form->{closed}) {
  964. if ($form->{method} eq 'cash') {
  965. $invwhere .=
  966. " AND a.amount = a.paid";
  967. }
  968. } else {
  969. if ($form->{open}) {
  970. if ($form->{method} eq 'cash') {
  971. $invwhere .=
  972. " AND a.id = 0";
  973. } else {
  974. $invwhere .=
  975. " AND NOT a.amount = a.paid";
  976. }
  977. } else {
  978. $invwhere .=
  979. " AND a.amount = a.paid";
  980. }
  981. }
  982. } else {
  983. $invwhere .= " AND a.id = 0";
  984. }
  985. my $flds = qq|
  986. p.id, p.partnumber, i.description,
  987. i.serialnumber, i.qty AS onhand, i.unit, p.bin,
  988. i.sellprice, p.listprice, p.lastcost, p.rop,
  989. p.weight, p.avgcost, p.priceupdate, p.image,
  990. p.drawing, p.microfiche, p.assembly,
  991. pg.partsgroup, a.invnumber, a.ordnumber,
  992. a.quonumber, i.trans_id, ct.name,
  993. e.name AS employee, a.curr, a.till, p.notes
  994. $makemodelfld|;
  995. if ($form->{bought}) {
  996. my $rflds = $flds;
  997. $rflds =~
  998. s/i.qty AS onhand/i.qty * -1 AS onhand/;
  999. $query = qq|
  1000. SELECT $rflds, 'ir' AS module,
  1001. '' AS type,
  1002. (SELECT sell
  1003. FROM exchangerate ex
  1004. WHERE ex.curr = a.curr
  1005. AND ex.transdate
  1006. = a.$transdate)
  1007. AS exchangerate, i.discount
  1008. FROM invoice i
  1009. JOIN parts p
  1010. ON (p.id = i.parts_id)
  1011. JOIN ap a ON (a.id = i.trans_id)
  1012. JOIN vendor ct
  1013. ON (a.vendor_id = ct.id)
  1014. LEFT JOIN partsgroup pg
  1015. ON (p.partsgroup_id = pg.id)
  1016. LEFT JOIN employee e
  1017. ON (a.employee_id = e.id)
  1018. $makemodeljoin
  1019. WHERE $invwhere|;
  1020. $union = "
  1021. UNION ALL";
  1022. }
  1023. if ($form->{sold}) {
  1024. $query .= qq|
  1025. $union
  1026. SELECT $flds, 'is' AS module,
  1027. '' AS type,
  1028. (SELECT buy
  1029. FROM exchangerate ex
  1030. WHERE ex.curr = a.curr
  1031. AND ex.transdate
  1032. = a.$transdate)
  1033. AS exchangerate, i.discount
  1034. FROM invoice i
  1035. JOIN parts p
  1036. ON (p.id = i.parts_id)
  1037. JOIN ar a ON (a.id = i.trans_id)
  1038. JOIN customer ct
  1039. ON (a.customer_id = ct.id)
  1040. LEFT JOIN partsgroup pg
  1041. ON (p.partsgroup_id = pg.id)
  1042. LEFT JOIN employee e
  1043. ON (a.employee_id = e.id)
  1044. $makemodeljoin
  1045. WHERE $invwhere|;
  1046. $union = "
  1047. UNION ALL";
  1048. }
  1049. }
  1050. if ($form->{onorder} || $form->{ordered}) {
  1051. my $ordwhere = "$where
  1052. AND a.quotation = '0'";
  1053. $ordwhere .= " AND a.transdate >= ".
  1054. $dbh->quote($form->{transdatefrom})
  1055. if $form->{transdatefrom};
  1056. $ordwhere .= " AND a.transdate <= ".
  1057. $dbh->quote($form->{transdateto})
  1058. if $form->{transdateto};
  1059. if ($form->{description} ne "") {
  1060. $var = $dbh->quote(
  1061. $form->like(lc $form->{description}));
  1062. $ordwhere .= " AND lower(i.description) LIKE $var";
  1063. }
  1064. if ($form->{open} || $form->{closed}) {
  1065. unless ($form->{open} && $form->{closed}) {
  1066. $ordwhere .= " AND a.closed = '0'"
  1067. if $form->{open};
  1068. $ordwhere .= " AND a.closed = '1'"
  1069. if $form->{closed};
  1070. }
  1071. } else {
  1072. $ordwhere .= " AND a.id = 0";
  1073. }
  1074. $flds = qq|
  1075. p.id, p.partnumber, i.description,
  1076. i.serialnumber, i.qty AS onhand, i.unit, p.bin,
  1077. i.sellprice, p.listprice, p.lastcost, p.rop,
  1078. p.weight, p.avgcost, p.priceupdate, p.image,
  1079. p.drawing, p.microfiche, p.assembly,
  1080. pg.partsgroup, '' AS invnumber, a.ordnumber,
  1081. a.quonumber, i.trans_id, ct.name,
  1082. e.name AS employee, a.curr, '0' AS till,
  1083. p.notes
  1084. $makemodelfld|;
  1085. if ($form->{ordered}) {
  1086. $query .= qq|
  1087. $union
  1088. SELECT $flds, 'oe' AS module,
  1089. 'sales_order' AS type,
  1090. (SELECT buy
  1091. FROM exchangerate ex
  1092. WHERE ex.curr = a.curr
  1093. AND ex.transdate
  1094. = a.transdate)
  1095. AS exchangerate, i.discount
  1096. FROM orderitems i
  1097. JOIN parts p ON (i.parts_id = p.id)
  1098. JOIN oe a ON (i.trans_id = a.id)
  1099. JOIN customer ct
  1100. ON (a.customer_id = ct.id)
  1101. LEFT JOIN partsgroup pg
  1102. ON (p.partsgroup_id = pg.id)
  1103. LEFT JOIN employee e
  1104. ON (a.employee_id = e.id)
  1105. $makemodeljoin
  1106. WHERE $ordwhere
  1107. AND a.customer_id > 0|;
  1108. $union = "
  1109. UNION ALL";
  1110. }
  1111. if ($form->{onorder}) {
  1112. $flds = qq|
  1113. p.id, p.partnumber, i.description,
  1114. i.serialnumber, i.qty AS onhand, i.unit,
  1115. p.bin, i.sellprice, p.listprice,
  1116. p.lastcost, p.rop, p.weight, p.avgcost,
  1117. p.priceupdate, p.image, p.drawing,
  1118. p.microfiche, p.assembly,
  1119. pg.partsgroup, '' AS invnumber,
  1120. a.ordnumber, a.quonumber,
  1121. i.trans_id, ct.name,e.name AS employee,
  1122. a.curr, '0' AS till, p.notes
  1123. $makemodelfld|;
  1124. $query .= qq|
  1125. $union
  1126. SELECT $flds, 'oe' AS module,
  1127. 'purchase_order' AS type,
  1128. (SELECT sell
  1129. FROM exchangerate ex
  1130. WHERE ex.curr = a.curr
  1131. AND ex.transdate
  1132. = a.transdate)
  1133. AS exchangerate, i.discount
  1134. FROM orderitems i
  1135. JOIN parts p ON (i.parts_id = p.id)
  1136. JOIN oe a ON (i.trans_id = a.id)
  1137. JOIN vendor ct
  1138. ON (a.vendor_id = ct.id)
  1139. LEFT JOIN partsgroup pg
  1140. ON (p.partsgroup_id = pg.id)
  1141. LEFT JOIN employee e
  1142. ON (a.employee_id = e.id)
  1143. $makemodeljoin
  1144. WHERE $ordwhere
  1145. AND a.vendor_id > 0|;
  1146. }
  1147. }
  1148. if ($form->{rfq} || $form->{quoted}) {
  1149. my $quowhere = "$where
  1150. AND a.quotation = '1'";
  1151. $quowhere .= " AND a.transdate >= ".
  1152. $dbh->quote($form->{transdatefrom})
  1153. if $form->{transdatefrom};
  1154. $quowhere .= " AND a.transdate <= ".
  1155. $dbh->quote($form->{transdateto})
  1156. if $form->{transdateto};
  1157. if ($form->{description} ne "") {
  1158. $var = $dbh->quote(
  1159. $form->like(lc $form->{description}));
  1160. $quowhere .= " AND lower(i.description) LIKE $var";
  1161. }
  1162. if ($form->{open} || $form->{closed}) {
  1163. unless ($form->{open} && $form->{closed}) {
  1164. $ordwhere .= " AND a.closed = '0'"
  1165. if $form->{open};
  1166. $ordwhere .= " AND a.closed = '1'"
  1167. if $form->{closed};
  1168. }
  1169. } else {
  1170. $ordwhere .= " AND a.id = 0";
  1171. }
  1172. $flds = qq|
  1173. p.id, p.partnumber, i.description,
  1174. i.serialnumber, i.qty AS onhand, i.unit, p.bin,
  1175. i.sellprice, p.listprice, p.lastcost, p.rop,
  1176. p.weight, p.avgcost, p.priceupdate, p.image,
  1177. p.drawing, p.microfiche, p.assembly,
  1178. pg.partsgroup, '' AS invnumber, a.ordnumber,
  1179. a.quonumber, i.trans_id, ct.name,
  1180. e.name AS employee, a.curr, '0' AS till, p.notes
  1181. $makemodelfld|;
  1182. if ($form->{quoted}) {
  1183. $query .= qq|
  1184. $union
  1185. SELECT $flds, 'oe' AS module,
  1186. 'sales_quotation' AS type,
  1187. (SELECT buy
  1188. FROM exchangerate ex
  1189. WHERE ex.curr = a.curr
  1190. AND ex.transdate
  1191. = a.transdate)
  1192. AS exchangerate,
  1193. i.discount
  1194. FROM orderitems i
  1195. JOIN parts p ON (i.parts_id = p.id)
  1196. JOIN oe a ON (i.trans_id = a.id)
  1197. JOIN customer ct
  1198. ON (a.customer_id = ct.id)
  1199. LEFT JOIN partsgroup pg
  1200. ON (p.partsgroup_id = pg.id)
  1201. LEFT JOIN employee e
  1202. ON (a.employee_id = e.id)
  1203. $makemodeljoin
  1204. WHERE $quowhere
  1205. AND a.customer_id > 0|;
  1206. $union = "
  1207. UNION ALL";
  1208. }
  1209. if ($form->{rfq}) {
  1210. $flds = qq|
  1211. p.id, p.partnumber, i.description,
  1212. i.serialnumber, i.qty AS onhand,
  1213. i.unit, p.bin, i.sellprice, p.listprice,
  1214. p.lastcost, p.rop, p.weight, p.avgcost,
  1215. p.priceupdate, p.image, p.drawing,
  1216. p.microfiche, p.assembly,
  1217. pg.partsgroup, '' AS invnumber,
  1218. a.ordnumber, a.quonumber,
  1219. i.trans_id, ct.name, e.name AS employee,
  1220. a.curr, '0' AS till, p.notes
  1221. $makemodelfld|;
  1222. $query .= qq|
  1223. $union
  1224. SELECT $flds, 'oe' AS module,
  1225. 'request_quotation' AS type,
  1226. (SELECT sell
  1227. FROM exchangerate ex
  1228. WHERE ex.curr = a.curr
  1229. AND ex.transdate
  1230. = a.transdate)
  1231. AS exchangerate, i.discount
  1232. FROM orderitems i
  1233. JOIN parts p ON (i.parts_id = p.id)
  1234. JOIN oe a ON (i.trans_id = a.id)
  1235. JOIN vendor ct
  1236. ON (a.vendor_id = ct.id)
  1237. LEFT JOIN partsgroup pg
  1238. ON (p.partsgroup_id = pg.id)
  1239. LEFT JOIN employee e
  1240. ON (a.employee_id = e.id)
  1241. $makemodeljoin
  1242. WHERE $quowhere
  1243. AND a.vendor_id > 0|;
  1244. }
  1245. }
  1246. $query .= qq|
  1247. ORDER BY $sortorder|;
  1248. }
  1249. my $sth = $dbh->prepare($query);
  1250. $sth->execute || $form->dberror($query);
  1251. $query = qq|
  1252. SELECT c.accno
  1253. FROM chart c
  1254. JOIN partstax pt ON (pt.chart_id = c.id)
  1255. WHERE pt.parts_id = ?
  1256. ORDER BY accno|;
  1257. my $pth = $dbh->prepare($query) || $form->dberror($query);
  1258. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  1259. $pth->execute($ref->{id});
  1260. while (($accno) = $pth->fetchrow_array) {
  1261. $ref->{tax} .= "$accno ";
  1262. }
  1263. $pth->finish;
  1264. push @{ $form->{parts} }, $ref;
  1265. }
  1266. $sth->finish;
  1267. @a = ();
  1268. # include individual items for assembly
  1269. if (($form->{searchitems} eq 'assembly') && $form->{individual}) {
  1270. if ($form->{sold} || $form->{ordered} || $form->{quoted}) {
  1271. $flds = qq|
  1272. p.id, p.partnumber, p.description,
  1273. p.onhand AS perassembly, p.unit, p.bin,
  1274. p.sellprice, p.listprice, p.lastcost, p.rop,
  1275. p.avgcost, p.weight, p.priceupdate, p.image,
  1276. p.drawing, p.microfiche, p.assembly,
  1277. pg.partsgroup, p.notes
  1278. $makemodelflds $assemblyflds |;
  1279. } else {
  1280. # replace p.onhand with a.qty AS onhand
  1281. $flds =~ s/p\.onhand/a.qty AS perassembly/;
  1282. }
  1283. for (@{ $form->{parts} }) {
  1284. push @a, $_;
  1285. $_->{perassembly} = 1;
  1286. $flds =~ s/p\.onhand*AS perassembly/p\.onhand, a\.qty AS perassembly/;
  1287. push @a, &include_assembly(
  1288. $dbh, $myconfig, $form, $_->{id}, $flds,
  1289. $makemodeljoin);
  1290. push @a, {id => $_->{id}, assemblyitem => 1};
  1291. }
  1292. # copy assemblies to $form->{parts}
  1293. @{ $form->{parts} } = @a;
  1294. }
  1295. @a = ();
  1296. if (($form->{warehouse} ne "") || $form->{l_warehouse}) {
  1297. if ($form->{warehouse} ne "") {
  1298. my ($desc, $var) = split /--/, $form->{warehouse};
  1299. $var = $dbh->quote($var);
  1300. $desc = $dbh->quote($desc);
  1301. $query = qq|
  1302. SELECT SUM(qty) AS onhand,
  1303. $desc AS description
  1304. FROM inventory
  1305. WHERE warehouse_id = $var
  1306. AND parts_id = ?|;
  1307. } else {
  1308. $query = qq|
  1309. SELECT SUM(i.qty) AS onhand,
  1310. w.description AS warehouse
  1311. FROM inventory i
  1312. JOIN warehouse w ON (w.id = i.warehouse_id)
  1313. WHERE i.parts_id = ?
  1314. GROUP BY w.description|;
  1315. }
  1316. $sth = $dbh->prepare($query) || $form->dberror($query);
  1317. for (@{ $form->{parts} }) {
  1318. $sth->execute($_->{id}) || $form->dberror($query);
  1319. if ($form->{warehouse} ne "") {
  1320. $ref = $sth->fetchrow_hashref(NAME_lc);
  1321. if ($ref->{onhand} != 0) {
  1322. $_->{onhand} = $ref->{onhand};
  1323. push @a, $_;
  1324. }
  1325. } else {
  1326. push @a, $_;
  1327. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  1328. if ($ref->{onhand} > 0) {
  1329. push @a, $ref;
  1330. }
  1331. }
  1332. }
  1333. $sth->finish;
  1334. }
  1335. @{ $form->{parts} } = @a;
  1336. }
  1337. $dbh->commit;
  1338. }
  1339. sub include_assembly {
  1340. my ($dbh, $myconfig, $form, $id, $flds, $makemodeljoin) = @_;
  1341. $form->{stagger}++;
  1342. if ($form->{stagger} > $form->{pncol}) {
  1343. $form->{pncol} = $form->{stagger};
  1344. }
  1345. $form->{$id} = 1;
  1346. my @a = qw(partnumber description bin);
  1347. if ($form->{sort} eq 'partnumber') {
  1348. $sortorder = "TRUE";
  1349. } else {
  1350. @a = grep !/$form->{sort}/, @a;
  1351. $sortorder = "$form->{sort} $form->{direction}, ". join ',', @a;
  1352. }
  1353. @a = ();
  1354. my $query = qq|
  1355. SELECT $flds
  1356. FROM parts p
  1357. JOIN assembly a ON (a.parts_id = p.id)
  1358. LEFT JOIN partsgroup pg ON (pg.id = p.id)
  1359. LEFT JOIN chart c1 ON (c1.id = p.inventory_accno_id)
  1360. LEFT JOIN chart c2 ON (c2.id = p.income_accno_id)
  1361. LEFT JOIN chart c3 ON (c3.id = p.expense_accno_id)
  1362. $makemodeljoin
  1363. WHERE a.id = ?
  1364. ORDER BY $sortorder|;
  1365. my $sth = $dbh->prepare($query);
  1366. $sth->execute($id) || $form->dberror($query);
  1367. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  1368. $ref->{assemblyitem} = 1;
  1369. $ref->{stagger} = $form->{stagger};
  1370. push @a, $ref;
  1371. if ($ref->{assembly} && !$form->{$ref->{id}}) {
  1372. push @a, &include_assembly(
  1373. $dbh, $myconfig, $form, $ref->{id}, $flds,
  1374. $makemodeljoin);
  1375. if ($form->{stagger} > $form->{pncol}) {
  1376. $form->{pncol} = $form->{stagger};
  1377. }
  1378. }
  1379. }
  1380. $sth->finish;
  1381. $form->{$id} = 0;
  1382. $form->{stagger}--;
  1383. @a;
  1384. }
  1385. sub requirements {
  1386. my ($self, $myconfig, $form) = @_;
  1387. my $dbh = $form->{dbh};
  1388. my $null;
  1389. my $var;
  1390. my $ref;
  1391. my $where = qq|p.obsolete = '0'|;
  1392. my $dwhere;
  1393. for (qw(partnumber description)) {
  1394. if ($form->{$_} ne "") {
  1395. $var = $dbh->quote($form->like(lc $form->{$_}));
  1396. $where .= qq| AND lower(p.$_) LIKE $var|;
  1397. }
  1398. }
  1399. if ($form->{partsgroup} ne "") {
  1400. ($null, $var) = split /--/, $form->{partsgroup};
  1401. $var = $dbh->quote($var);
  1402. $where .= qq| AND p.partsgroup_id = $var|;
  1403. }
  1404. # connect to database
  1405. my ($transdatefrom, $transdateto);
  1406. if ($form->{year}) {
  1407. ($transdatefrom, $transdateto)
  1408. = $form->from_to($form->{year}, '01', 12);
  1409. $dwhere = qq| AND a.transdate >= '$transdatefrom'
  1410. AND a.transdate <= '$transdateto'|;
  1411. }
  1412. $query = qq|
  1413. SELECT p.id, p.partnumber, p.description, sum(i.qty) AS qty,
  1414. p.onhand, extract(MONTH FROM a.transdate) AS month,
  1415. '0' AS so, '0' AS po
  1416. FROM invoice i
  1417. JOIN parts p ON (p.id = i.parts_id)
  1418. JOIN ar a ON (a.id = i.trans_id)
  1419. WHERE $where $dwhere AND p.inventory_accno_id > 0
  1420. GROUP BY p.id, p.partnumber, p.description, p.onhand,
  1421. extract(MONTH FROM a.transdate)|;
  1422. my $sth = $dbh->prepare($query);
  1423. $sth->execute || $form->dberror($query);
  1424. my %parts;
  1425. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  1426. $parts{$ref->{id}} = $ref;
  1427. }
  1428. $sth->finish;
  1429. my %ofld = ( customer => so, vendor => po );
  1430. for (qw(customer vendor)) {
  1431. $query = qq|
  1432. SELECT p.id, p.partnumber, p.description,
  1433. sum(qty) - sum(ship) AS $ofld{$_}, p.onhand,
  1434. 0 AS month
  1435. FROM orderitems i
  1436. JOIN parts p ON (p.id = i.parts_id)
  1437. JOIN oe a ON (a.id = i.trans_id)
  1438. WHERE $where AND p.inventory_accno_id > 0
  1439. AND p.assembly = '0' AND a.closed = '0'
  1440. AND a.${_}_id > 0
  1441. GROUP BY p.id, p.partnumber, p.description, p.onhand,
  1442. month|;
  1443. $sth = $dbh->prepare($query);
  1444. $sth->execute || $form->dberror($query);
  1445. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  1446. if (exists $parts{$ref->{id}}->{$ofld{$_}}) {
  1447. $parts{$ref->{id}}->{$ofld{$_}}
  1448. += $ref->{$ofld{$_}};
  1449. } else {
  1450. $parts{$ref->{id}} = $ref;
  1451. }
  1452. }
  1453. $sth->finish;
  1454. }
  1455. # add assemblies from open sales orders
  1456. $query = qq|
  1457. SELECT DISTINCT a.id AS orderid, b.id, i.qty - i.ship AS qty
  1458. FROM parts p
  1459. JOIN assembly b ON (b.parts_id = p.id)
  1460. JOIN orderitems i ON (i.parts_id = b.id)
  1461. JOIN oe a ON (a.id = i.trans_id)
  1462. WHERE $where
  1463. AND (p.inventory_accno_id > 0 OR p.assembly = '1')
  1464. AND a.closed = '0'|;
  1465. $sth = $dbh->prepare($query);
  1466. $sth->execute || $form->dberror($query);
  1467. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  1468. &requirements_assembly(
  1469. $dbh, $form, \%parts, $ref->{id}, $ref->{qty}, $where)
  1470. if $ref->{qty};
  1471. }
  1472. $sth->finish;
  1473. $dbh->commit;
  1474. for (sort { $parts{$a}->{$form->{sort}} cmp $parts{$b}->{$form->{sort}}} keys %parts) {
  1475. push @{ $form->{parts} }, $parts{$_};
  1476. }
  1477. }
  1478. sub requirements_assembly {
  1479. my ($dbh, $form, $parts, $id, $qty, $where) = @_;
  1480. # assemblies
  1481. my $query = qq|
  1482. SELECT p.id, p.partnumber, p.description, a.qty * ? AS so,
  1483. p.onhand, p.assembly, p.partsgroup_id
  1484. FROM assembly a
  1485. JOIN parts p ON (p.id = a.parts_id)
  1486. WHERE $where AND a.id = ? AND p.inventory_accno_id > 0
  1487. UNION
  1488. SELECT p.id, p.partnumber, p.description, a.qty * ? AS so,
  1489. p.onhand, p.assembly, p.partsgroup_id
  1490. FROM assembly a
  1491. JOIN parts p ON (p.id = a.parts_id)
  1492. WHERE a.id = ? AND p.assembly = '1'|;
  1493. my $sth = $dbh->prepare($query);
  1494. $sth->execute($qty, $id, $qty, $id) || $form->dberror($query);
  1495. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  1496. if ($ref->{assembly}) {
  1497. &requirements_assembly(
  1498. $dbh, $form, $parts, $ref->{id}, $ref->{so},
  1499. $where);
  1500. next;
  1501. }
  1502. if (exists $parts->{$ref->{id}}{so}) {
  1503. $parts->{$ref->{id}}{so} += $ref->{so};
  1504. } else {
  1505. $parts->{$ref->{id}} = $ref;
  1506. }
  1507. }
  1508. $sth->finish;
  1509. }
  1510. sub create_links {
  1511. my ($self, $module, $myconfig, $form) = @_;
  1512. # connect to database
  1513. my $dbh = $form->{dbh};
  1514. my $ref;
  1515. my $query = qq|
  1516. SELECT accno, description, link
  1517. FROM chart
  1518. WHERE link LIKE ?
  1519. ORDER BY accno|;
  1520. my $sth = $dbh->prepare($query);
  1521. $sth->execute("%$module%") || $form->dberror($query);
  1522. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  1523. foreach my $key (split /:/, $ref->{link}) {
  1524. if ($key =~ /$module/) {
  1525. push @{ $form->{"${module}_links"}{$key} },
  1526. { accno => $ref->{accno},
  1527. description => $ref->{description} };
  1528. }
  1529. }
  1530. }
  1531. $sth->finish;
  1532. if ($form->{item} ne 'assembly') {
  1533. $query = qq|SELECT count(*) FROM vendor|;
  1534. my ($count) = $dbh->selectrow_array($query);
  1535. if ($count < $myconfig->{vclimit}) {
  1536. $query = qq|SELECT id, name FROM vendor ORDER BY name|;
  1537. $sth = $dbh->prepare($query);
  1538. $sth->execute || $form->dberror($query);
  1539. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  1540. push @{ $form->{all_vendor} }, $ref;
  1541. }
  1542. $sth->finish;
  1543. }
  1544. }
  1545. # pricegroups, customers
  1546. $query = qq|SELECT count(*) FROM customer|;
  1547. ($count) = $dbh->selectrow_array($query);
  1548. if ($count < $myconfig->{vclimit}) {
  1549. $query = qq|SELECT id, name FROM customer ORDER BY name|;
  1550. $sth = $dbh->prepare($query);
  1551. $sth->execute || $form->dberror($query);
  1552. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  1553. push @{ $form->{all_customer} }, $ref;
  1554. }
  1555. $sth->finish;
  1556. }
  1557. $query = qq|SELECT id, pricegroup FROM pricegroup ORDER BY pricegroup|;
  1558. $sth = $dbh->prepare($query);
  1559. $sth->execute || $form->dberror($query);
  1560. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  1561. push @{ $form->{all_pricegroup} }, $ref;
  1562. }
  1563. $sth->finish;
  1564. if ($form->{id}) {
  1565. $query = qq|
  1566. SELECT value FROM defaults
  1567. WHERE setting_key = 'weightunit'|;
  1568. ($form->{weightunit}) = $dbh->selectrow_array($query);
  1569. $query = qq|
  1570. SELECT value FROM defaults
  1571. WHERE setting_key = 'curr'|;
  1572. ($form->{currencies}) = $dbh->selectrow_array($query);
  1573. } else {
  1574. # Dieter: FIXME left joins not working
  1575. $query = qq|
  1576. SELECT (SELECT value FROM defaults
  1577. WHERE setting_key = 'weightunit')
  1578. AS weightunit, current_date AS priceupdate,
  1579. (SELECT value FROM defaults
  1580. WHERE setting_key = 'curr') AS currencies,
  1581. c1.accno AS inventory_accno,
  1582. c1.description AS inventory_description,
  1583. c2.accno AS income_accno,
  1584. c2.description AS income_description,
  1585. c3.accno AS expense_accno,
  1586. c3.description AS expense_description
  1587. FROM chart c1, chart c2, chart c3
  1588. WHERE c1.id IN (SELECT value FROM defaults
  1589. WHERE setting_key = 'inventory_accno_id')
  1590. AND c2.id IN (SELECT value FROM defaults
  1591. WHERE setting_key = 'income_accno_id')
  1592. AND c3.id IN (SELECT value FROM defaults
  1593. WHERE setting_key
  1594. = 'expense_accno_id')|;
  1595. $sth = $dbh->prepare($query);
  1596. $sth->execute || $form->dberror($query);
  1597. $ref = $sth->fetchrow_hashref(NAME_lc);
  1598. for (qw(weightunit priceupdate currencies)) {
  1599. $form->{$_} = $ref->{$_};
  1600. }
  1601. # setup accno hash, {amount} is used in create_links
  1602. for (qw(inventory income expense)) {
  1603. $form->{amount}{"IC_$_"}
  1604. = {
  1605. accno => $ref->{"${_}_accno"},
  1606. description => $ref->{"${_}_description"}
  1607. };
  1608. }
  1609. $sth->finish;
  1610. }
  1611. $dbh->connect;
  1612. }
  1613. sub get_warehouses {
  1614. my ($self, $myconfig, $form) = @_;
  1615. my $dbh = $form->{dbh};
  1616. my $query = qq|SELECT id, description FROM warehouse|;
  1617. my $sth = $dbh->prepare($query);
  1618. $sth->execute || $form->dberror($query);
  1619. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  1620. push @{ $form->{all_warehouse} }, $ref;
  1621. }
  1622. $sth->finish;
  1623. $dbh->commit;
  1624. }
  1625. 1;