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