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