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