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