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