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