summaryrefslogtreecommitdiff
path: root/LedgerSMB/IC.pm
blob: 4a98f8a4e2d2d33c58fe48ec06264437b4e12af8 (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 partstax WHERE parts_id = ?|;
  681. $sth = $dbh->prepare($query);
  682. $sth->execute( $form->{id} ) || $form->dberror($query);
  683. if ( $form->{item} ne 'assembly' ) {
  684. $query = qq|DELETE FROM partsvendor WHERE parts_id = ?|;
  685. $sth = $dbh->prepare($query);
  686. $sth->execute( $form->{id} ) || $form->dberror($query);
  687. }
  688. # check if it is a part, assembly or service
  689. if ( $form->{item} ne 'service' ) {
  690. $query = qq|DELETE FROM makemodel WHERE parts_id = ?|;
  691. $sth = $dbh->prepare($query);
  692. $sth->execute( $form->{id} ) || $form->dberror($query);
  693. }
  694. if ( $form->{item} eq 'assembly' ) {
  695. $query = qq|DELETE FROM assembly WHERE id = ?|;
  696. $sth = $dbh->prepare($query);
  697. $sth->execute( $form->{id} ) || $form->dberror($query);
  698. }
  699. $query = qq|DELETE FROM inventory WHERE parts_id = ?|;
  700. $sth = $dbh->prepare($query);
  701. $sth->execute( $form->{id} ) || $form->dberror($query);
  702. $query = qq|DELETE FROM partscustomer WHERE parts_id = ?|;
  703. $sth = $dbh->prepare($query);
  704. $sth->execute( $form->{id} ) || $form->dberror($query);
  705. $query = qq|DELETE FROM translation WHERE trans_id = ?|;
  706. $sth = $dbh->prepare($query);
  707. $sth->execute( $form->{id} ) || $form->dberror($query);
  708. $query = qq|DELETE FROM parts WHERE 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 'obsolete' ) {
  831. $where .= " AND p.obsolete = '1'";
  832. }
  833. else {
  834. # Obsolete items should not show up on onhand, short, or active
  835. # reports --CT
  836. $where .= " AND p.obsolete = '0'";
  837. }
  838. if ( $form->{itemstatus} eq 'onhand' ) {
  839. $where .= " AND p.onhand > 0";
  840. }
  841. elsif ( $form->{itemstatus} eq 'short' ) {
  842. $where .= " AND p.onhand < p.rop";
  843. }
  844. my $makemodelflds = qq|, '', ''|;
  845. my $makemodeljoin;
  846. if ( ( $form->{make} ne "" )
  847. || $form->{l_make}
  848. || ( $form->{model} ne "" )
  849. || $form->{l_model} )
  850. {
  851. $makemodelflds = qq|, m.make, m.model|;
  852. $makemodeljoin = qq|LEFT JOIN makemodel m ON (m.parts_id = p.id)|;
  853. if ( $form->{make} ne "" ) {
  854. $var = $dbh->quote( $form->like( lc $form->{make} ) );
  855. $where .= " AND lower(m.make) LIKE $var";
  856. }
  857. if ( $form->{model} ne "" ) {
  858. $var = $dbh->quote( $form->like( lc $form->{model} ) );
  859. $where .= " AND lower(m.model) LIKE $var";
  860. }
  861. }
  862. if ( $form->{partsgroup} ne "" ) {
  863. ( $null, $var ) = split /--/, $form->{partsgroup};
  864. $where .= qq| AND p.partsgroup_id = | . $dbh->quote($var);
  865. }
  866. my %ordinal = (
  867. 'partnumber' => 2,
  868. 'description' => 3,
  869. 'bin' => 6,
  870. 'priceupdate' => 13,
  871. 'drawing' => 15,
  872. 'microfiche' => 16,
  873. 'partsgroup' => 18,
  874. 'make' => 21,
  875. 'model' => 22,
  876. 'assemblypartnumber' => 23
  877. );
  878. my @a = qw(partnumber description);
  879. my $sortorder = $form->sort_order( \@a, \%ordinal );
  880. my $query = qq|SELECT value FROM defaults WHERE setting_key = 'curr'|;
  881. my ($curr) = $dbh->selectrow_array($query);
  882. $curr =~ s/:.*//;
  883. $curr = $dbh->quote($curr);
  884. my $flds = qq|
  885. p.id, p.partnumber, p.description, p.onhand, p.unit,
  886. p.bin, p.sellprice, p.listprice, p.lastcost, p.rop,
  887. p.avgcost,
  888. p.weight, p.priceupdate, p.image, p.drawing, p.microfiche,
  889. p.assembly, pg.partsgroup, $curr AS curr,
  890. c1.accno AS inventory, c2.accno AS income, c3.accno AS expense,
  891. p.notes
  892. $makemodelflds $assemblyflds
  893. |;
  894. $query = qq|
  895. SELECT $flds
  896. FROM parts p
  897. LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
  898. LEFT JOIN chart c1 ON (c1.id = p.inventory_accno_id)
  899. LEFT JOIN chart c2 ON (c2.id = p.income_accno_id)
  900. LEFT JOIN chart c3 ON (c3.id = p.expense_accno_id)
  901. $makemodeljoin
  902. WHERE $where
  903. ORDER BY $sortorder|;
  904. # redo query for components report
  905. if ( $form->{searchitems} eq 'component' ) {
  906. $flds =~ s/p.onhand/a.qty AS onhand/;
  907. $query = qq|
  908. SELECT $flds
  909. FROM assembly a
  910. JOIN parts p ON (a.parts_id = p.id)
  911. JOIN parts p1 ON (a.id = p1.id)
  912. LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
  913. LEFT JOIN chart c1 ON (c1.id = p.inventory_accno_id)
  914. LEFT JOIN chart c2 ON (c2.id = p.income_accno_id)
  915. LEFT JOIN chart c3 ON (c3.id = p.expense_accno_id)
  916. $makemodeljoin
  917. WHERE $where
  918. ORDER BY $sortorder|;
  919. }
  920. # rebuild query for bought and sold items
  921. if ( $form->{bought}
  922. || $form->{sold}
  923. || $form->{onorder}
  924. || $form->{ordered}
  925. || $form->{rfq}
  926. || $form->{quoted} )
  927. {
  928. $form->sort_order();
  929. @a = qw(partnumber description curr employee name
  930. serialnumber id);
  931. push @a, "invnumber" if ( $form->{bought} || $form->{sold} );
  932. push @a, "ordnumber" if ( $form->{onorder} || $form->{ordered} );
  933. push @a, "quonumber" if ( $form->{rfq} || $form->{quoted} );
  934. %ordinal = (
  935. 'partnumber' => 2,
  936. 'description' => 3,
  937. 'serialnumber' => 4,
  938. 'bin' => 7,
  939. 'priceupdate' => 14,
  940. 'partsgroup' => 19,
  941. 'invnumber' => 20,
  942. 'ordnumber' => 21,
  943. 'quonumber' => 22,
  944. 'name' => 24,
  945. 'employee' => 25,
  946. 'curr' => 26,
  947. 'make' => 29,
  948. 'model' => 30
  949. );
  950. $sortorder = $form->sort_order( \@a, \%ordinal );
  951. my $union = "";
  952. $query = "";
  953. if ( $form->{bought} || $form->{sold} ) {
  954. my $invwhere = "$where";
  955. my $transdate =
  956. ( $form->{method} eq 'accrual' )
  957. ? "transdate"
  958. : "datepaid";
  959. $invwhere .= " AND i.assemblyitem = '0'";
  960. $invwhere .=
  961. " AND a.$transdate >= " . $dbh->quote( $form->{transdatefrom} )
  962. if $form->{transdatefrom};
  963. $invwhere .=
  964. " AND a.$transdate <= " . $dbh->quote( $form->{transdateto} )
  965. if $form->{transdateto};
  966. if ( $form->{description} ne "" ) {
  967. $var = $dbh->quote( $form->like( lc $form->{description} ) );
  968. $invwhere .= " AND lower(i.description) LIKE $var";
  969. }
  970. if ( $form->{open} || $form->{closed} ) {
  971. if ( $form->{open} && $form->{closed} ) {
  972. if ( $form->{method} eq 'cash' ) {
  973. $invwhere .= " AND a.amount = a.paid";
  974. }
  975. }
  976. else {
  977. if ( $form->{open} ) {
  978. if ( $form->{method} eq 'cash' ) {
  979. $invwhere .= " AND a.id = 0";
  980. }
  981. else {
  982. $invwhere .= " AND NOT a.amount = a.paid";
  983. }
  984. }
  985. else {
  986. $invwhere .= " AND a.amount = a.paid";
  987. }
  988. }
  989. }
  990. else {
  991. $invwhere .= " AND a.id = 0";
  992. }
  993. my $flds = qq|
  994. p.id, p.partnumber, i.description,
  995. i.serialnumber, i.qty AS onhand, i.unit, p.bin,
  996. i.sellprice, p.listprice, p.lastcost, p.rop,
  997. p.weight, p.avgcost, p.priceupdate, p.image,
  998. p.drawing, p.microfiche, p.assembly,
  999. pg.partsgroup, a.invnumber, a.ordnumber,
  1000. a.quonumber, i.trans_id, ct.name,
  1001. e.name AS employee, a.curr, a.till, p.notes
  1002. $makemodelfld|;
  1003. if ( $form->{bought} ) {
  1004. my $rflds = $flds;
  1005. $rflds =~ s/i.qty AS onhand/i.qty * -1 AS onhand/;
  1006. $query = qq|
  1007. SELECT $rflds, 'ir' AS module,
  1008. '' AS type,
  1009. (SELECT sell
  1010. FROM exchangerate ex
  1011. WHERE ex.curr = a.curr
  1012. AND ex.transdate
  1013. = a.$transdate)
  1014. AS exchangerate, i.discount
  1015. FROM invoice i
  1016. JOIN parts p
  1017. ON (p.id = i.parts_id)
  1018. JOIN ap a ON (a.id = i.trans_id)
  1019. JOIN vendor ct
  1020. ON (a.vendor_id = ct.id)
  1021. LEFT JOIN partsgroup pg
  1022. ON (p.partsgroup_id = pg.id)
  1023. LEFT JOIN employee e
  1024. ON (a.employee_id = e.id)
  1025. $makemodeljoin
  1026. WHERE $invwhere|;
  1027. $union = "
  1028. UNION ALL";
  1029. }
  1030. if ( $form->{sold} ) {
  1031. $query .= qq|
  1032. $union
  1033. SELECT $flds, 'is' AS module,
  1034. '' AS type,
  1035. (SELECT buy
  1036. FROM exchangerate ex
  1037. WHERE ex.curr = a.curr
  1038. AND ex.transdate
  1039. = a.$transdate)
  1040. AS exchangerate, i.discount
  1041. FROM invoice i
  1042. JOIN parts p
  1043. ON (p.id = i.parts_id)
  1044. JOIN ar a ON (a.id = i.trans_id)
  1045. JOIN customer ct
  1046. ON (a.customer_id = ct.id)
  1047. LEFT JOIN partsgroup pg
  1048. ON (p.partsgroup_id = pg.id)
  1049. LEFT JOIN employee e
  1050. ON (a.employee_id = e.id)
  1051. $makemodeljoin
  1052. WHERE $invwhere|;
  1053. $union = "
  1054. UNION ALL";
  1055. }
  1056. }
  1057. if ( $form->{onorder} || $form->{ordered} ) {
  1058. my $ordwhere = "$where
  1059. AND a.quotation = '0'";
  1060. $ordwhere .=
  1061. " AND a.transdate >= " . $dbh->quote( $form->{transdatefrom} )
  1062. if $form->{transdatefrom};
  1063. $ordwhere .=
  1064. " AND a.transdate <= " . $dbh->quote( $form->{transdateto} )
  1065. if $form->{transdateto};
  1066. if ( $form->{description} ne "" ) {
  1067. $var = $dbh->quote( $form->like( lc $form->{description} ) );
  1068. $ordwhere .= " AND lower(i.description) LIKE $var";
  1069. }
  1070. if ( $form->{open} || $form->{closed} ) {
  1071. unless ( $form->{open} && $form->{closed} ) {
  1072. $ordwhere .= " AND a.closed = '0'"
  1073. if $form->{open};
  1074. $ordwhere .= " AND a.closed = '1'"
  1075. if $form->{closed};
  1076. }
  1077. }
  1078. else {
  1079. $ordwhere .= " AND a.id = 0";
  1080. }
  1081. $flds = qq|
  1082. p.id, p.partnumber, i.description,
  1083. i.serialnumber, i.qty AS onhand, i.unit, p.bin,
  1084. i.sellprice, p.listprice, p.lastcost, p.rop,
  1085. p.weight, p.avgcost, p.priceupdate, p.image,
  1086. p.drawing, p.microfiche, p.assembly,
  1087. pg.partsgroup, '' AS invnumber, a.ordnumber,
  1088. a.quonumber, i.trans_id, ct.name,
  1089. e.name AS employee, a.curr, '0' AS till,
  1090. p.notes
  1091. $makemodelfld|;
  1092. if ( $form->{ordered} ) {
  1093. $query .= qq|
  1094. $union
  1095. SELECT $flds, 'oe' AS module,
  1096. 'sales_order' AS type,
  1097. (SELECT buy
  1098. FROM exchangerate ex
  1099. WHERE ex.curr = a.curr
  1100. AND ex.transdate
  1101. = a.transdate)
  1102. AS exchangerate, i.discount
  1103. FROM orderitems i
  1104. JOIN parts p ON (i.parts_id = p.id)
  1105. JOIN oe a ON (i.trans_id = a.id)
  1106. JOIN customer ct
  1107. ON (a.customer_id = ct.id)
  1108. LEFT JOIN partsgroup pg
  1109. ON (p.partsgroup_id = pg.id)
  1110. LEFT JOIN employee e
  1111. ON (a.employee_id = e.id)
  1112. $makemodeljoin
  1113. WHERE $ordwhere
  1114. AND a.customer_id > 0|;
  1115. $union = "
  1116. UNION ALL";
  1117. }
  1118. if ( $form->{onorder} ) {
  1119. $flds = qq|
  1120. p.id, p.partnumber, i.description,
  1121. i.serialnumber, i.qty AS onhand, i.unit,
  1122. p.bin, i.sellprice, p.listprice,
  1123. p.lastcost, p.rop, p.weight, p.avgcost,
  1124. p.priceupdate, p.image, p.drawing,
  1125. p.microfiche, p.assembly,
  1126. pg.partsgroup, '' AS invnumber,
  1127. a.ordnumber, a.quonumber,
  1128. i.trans_id, ct.name,e.name AS employee,
  1129. a.curr, '0' AS till, p.notes
  1130. $makemodelfld|;
  1131. $query .= qq|
  1132. $union
  1133. SELECT $flds, 'oe' AS module,
  1134. 'purchase_order' AS type,
  1135. (SELECT sell
  1136. FROM exchangerate ex
  1137. WHERE ex.curr = a.curr
  1138. AND ex.transdate
  1139. = a.transdate)
  1140. AS exchangerate, i.discount
  1141. FROM orderitems i
  1142. JOIN parts p ON (i.parts_id = p.id)
  1143. JOIN oe a ON (i.trans_id = a.id)
  1144. JOIN vendor ct
  1145. ON (a.vendor_id = ct.id)
  1146. LEFT JOIN partsgroup pg
  1147. ON (p.partsgroup_id = pg.id)
  1148. LEFT JOIN employee e
  1149. ON (a.employee_id = e.id)
  1150. $makemodeljoin
  1151. WHERE $ordwhere
  1152. AND a.vendor_id > 0|;
  1153. }
  1154. }
  1155. if ( $form->{rfq} || $form->{quoted} ) {
  1156. my $quowhere = "$where
  1157. AND a.quotation = '1'";
  1158. $quowhere .=
  1159. " AND a.transdate >= " . $dbh->quote( $form->{transdatefrom} )
  1160. if $form->{transdatefrom};
  1161. $quowhere .=
  1162. " AND a.transdate <= " . $dbh->quote( $form->{transdateto} )
  1163. if $form->{transdateto};
  1164. if ( $form->{description} ne "" ) {
  1165. $var = $dbh->quote( $form->like( lc $form->{description} ) );
  1166. $quowhere .= " AND lower(i.description) LIKE $var";
  1167. }
  1168. if ( $form->{open} || $form->{closed} ) {
  1169. unless ( $form->{open} && $form->{closed} ) {
  1170. $ordwhere .= " AND a.closed = '0'"
  1171. if $form->{open};
  1172. $ordwhere .= " AND a.closed = '1'"
  1173. if $form->{closed};
  1174. }
  1175. }
  1176. else {
  1177. $ordwhere .= " AND a.id = 0";
  1178. }
  1179. $flds = qq|
  1180. p.id, p.partnumber, i.description,
  1181. i.serialnumber, i.qty AS onhand, i.unit, p.bin,
  1182. i.sellprice, p.listprice, p.lastcost, p.rop,
  1183. p.weight, p.avgcost, p.priceupdate, p.image,
  1184. p.drawing, p.microfiche, p.assembly,
  1185. pg.partsgroup, '' AS invnumber, a.ordnumber,
  1186. a.quonumber, i.trans_id, ct.name,
  1187. e.name AS employee, a.curr, '0' AS till, p.notes
  1188. $makemodelfld|;
  1189. if ( $form->{quoted} ) {
  1190. $query .= qq|
  1191. $union
  1192. SELECT $flds, 'oe' AS module,
  1193. 'sales_quotation' AS type,
  1194. (SELECT buy
  1195. FROM exchangerate ex
  1196. WHERE ex.curr = a.curr
  1197. AND ex.transdate
  1198. = a.transdate)
  1199. AS exchangerate,
  1200. i.discount
  1201. FROM orderitems i
  1202. JOIN parts p ON (i.parts_id = p.id)
  1203. JOIN oe a ON (i.trans_id = a.id)
  1204. JOIN customer ct
  1205. ON (a.customer_id = ct.id)
  1206. LEFT JOIN partsgroup pg
  1207. ON (p.partsgroup_id = pg.id)
  1208. LEFT JOIN employee e
  1209. ON (a.employee_id = e.id)
  1210. $makemodeljoin
  1211. WHERE $quowhere
  1212. AND a.customer_id > 0|;
  1213. $union = "
  1214. UNION ALL";
  1215. }
  1216. if ( $form->{rfq} ) {
  1217. $flds = qq|
  1218. p.id, p.partnumber, i.description,
  1219. i.serialnumber, i.qty AS onhand,
  1220. i.unit, p.bin, i.sellprice, p.listprice,
  1221. p.lastcost, p.rop, p.weight, p.avgcost,
  1222. p.priceupdate, p.image, p.drawing,
  1223. p.microfiche, p.assembly,
  1224. pg.partsgroup, '' AS invnumber,
  1225. a.ordnumber, a.quonumber,
  1226. i.trans_id, ct.name, e.name AS employee,
  1227. a.curr, '0' AS till, p.notes
  1228. $makemodelfld|;
  1229. $query .= qq|
  1230. $union
  1231. SELECT $flds, 'oe' AS module,
  1232. 'request_quotation' AS type,
  1233. (SELECT sell
  1234. FROM exchangerate ex
  1235. WHERE ex.curr = a.curr
  1236. AND ex.transdate
  1237. = a.transdate)
  1238. AS exchangerate, i.discount
  1239. FROM orderitems i
  1240. JOIN parts p ON (i.parts_id = p.id)
  1241. JOIN oe a ON (i.trans_id = a.id)
  1242. JOIN vendor ct
  1243. ON (a.vendor_id = ct.id)
  1244. LEFT JOIN partsgroup pg
  1245. ON (p.partsgroup_id = pg.id)
  1246. LEFT JOIN employee e
  1247. ON (a.employee_id = e.id)
  1248. $makemodeljoin
  1249. WHERE $quowhere
  1250. AND a.vendor_id > 0|;
  1251. }
  1252. }
  1253. $query .= qq|
  1254. ORDER BY $sortorder|;
  1255. }
  1256. my $sth = $dbh->prepare($query);
  1257. $sth->execute || $form->dberror($query);
  1258. $query = qq|
  1259. SELECT c.accno
  1260. FROM chart c
  1261. JOIN partstax pt ON (pt.chart_id = c.id)
  1262. WHERE pt.parts_id = ?
  1263. ORDER BY accno|;
  1264. my $pth = $dbh->prepare($query) || $form->dberror($query);
  1265. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1266. $pth->execute( $ref->{id} );
  1267. while ( ($accno) = $pth->fetchrow_array ) {
  1268. $ref->{tax} .= "$accno ";
  1269. }
  1270. $pth->finish;
  1271. push @{ $form->{parts} }, $ref;
  1272. }
  1273. $sth->finish;
  1274. @a = ();
  1275. # include individual items for assembly
  1276. if ( ( $form->{searchitems} eq 'assembly' ) && $form->{individual} ) {
  1277. if ( $form->{sold} || $form->{ordered} || $form->{quoted} ) {
  1278. $flds = qq|
  1279. p.id, p.partnumber, p.description,
  1280. p.onhand AS perassembly, p.unit, p.bin,
  1281. p.sellprice, p.listprice, p.lastcost, p.rop,
  1282. p.avgcost, p.weight, p.priceupdate, p.image,
  1283. p.drawing, p.microfiche, p.assembly,
  1284. pg.partsgroup, p.notes
  1285. $makemodelflds $assemblyflds |;
  1286. }
  1287. else {
  1288. # replace p.onhand with a.qty AS onhand
  1289. $flds =~ s/p\.onhand/a.qty AS perassembly/;
  1290. }
  1291. for ( @{ $form->{parts} } ) {
  1292. push @a, $_;
  1293. $_->{perassembly} = 1;
  1294. $flds =~
  1295. s/p\.onhand*AS perassembly/p\.onhand, a\.qty AS perassembly/;
  1296. push @a,
  1297. &include_assembly( $dbh, $myconfig, $form, $_->{id}, $flds,
  1298. $makemodeljoin );
  1299. push @a, { id => $_->{id}, assemblyitem => 1 };
  1300. }
  1301. # copy assemblies to $form->{parts}
  1302. @{ $form->{parts} } = @a;
  1303. }
  1304. @a = ();
  1305. if ( ( $form->{warehouse} ne "" ) || $form->{l_warehouse} ) {
  1306. if ( $form->{warehouse} ne "" ) {
  1307. my ( $desc, $var ) = split /--/, $form->{warehouse};
  1308. $var = $dbh->quote($var);
  1309. $desc = $dbh->quote($desc);
  1310. $query = qq|
  1311. SELECT SUM(qty) AS onhand,
  1312. $desc AS description
  1313. FROM inventory
  1314. WHERE warehouse_id = $var
  1315. AND parts_id = ?|;
  1316. }
  1317. else {
  1318. $query = qq|
  1319. SELECT SUM(i.qty) AS onhand,
  1320. w.description AS warehouse
  1321. FROM inventory i
  1322. JOIN warehouse w ON (w.id = i.warehouse_id)
  1323. WHERE i.parts_id = ?
  1324. GROUP BY w.description|;
  1325. }
  1326. $sth = $dbh->prepare($query) || $form->dberror($query);
  1327. for ( @{ $form->{parts} } ) {
  1328. $sth->execute( $_->{id} ) || $form->dberror($query);
  1329. if ( $form->{warehouse} ne "" ) {
  1330. $ref = $sth->fetchrow_hashref(NAME_lc);
  1331. if ( $ref->{onhand} != 0 ) {
  1332. $_->{onhand} = $ref->{onhand};
  1333. push @a, $_;
  1334. }
  1335. }
  1336. else {
  1337. push @a, $_;
  1338. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1339. if ( $ref->{onhand} > 0 ) {
  1340. push @a, $ref;
  1341. }
  1342. }
  1343. }
  1344. $sth->finish;
  1345. }
  1346. @{ $form->{parts} } = @a;
  1347. }
  1348. $dbh->commit;
  1349. }
  1350. sub include_assembly {
  1351. my ( $dbh, $myconfig, $form, $id, $flds, $makemodeljoin ) = @_;
  1352. $form->{stagger}++;
  1353. if ( $form->{stagger} > $form->{pncol} ) {
  1354. $form->{pncol} = $form->{stagger};
  1355. }
  1356. $form->{$id} = 1;
  1357. my @a = qw(partnumber description bin);
  1358. @a = grep !/$form->{sort}/, @a;
  1359. $sortorder = "$form->{sort} $form->{direction}, " . join ',', @a;
  1360. @a = ();
  1361. my $query = qq|
  1362. SELECT $flds
  1363. FROM parts p
  1364. JOIN assembly a ON (a.parts_id = p.id)
  1365. LEFT JOIN partsgroup pg ON (pg.id = p.id)
  1366. LEFT JOIN chart c1 ON (c1.id = p.inventory_accno_id)
  1367. LEFT JOIN chart c2 ON (c2.id = p.income_accno_id)
  1368. LEFT JOIN chart c3 ON (c3.id = p.expense_accno_id)
  1369. $makemodeljoin
  1370. WHERE a.id = ?
  1371. ORDER BY $sortorder|;
  1372. my $sth = $dbh->prepare($query);
  1373. $sth->execute($id) || $form->dberror($query);
  1374. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1375. $ref->{assemblyitem} = 1;
  1376. $ref->{stagger} = $form->{stagger};
  1377. push @a, $ref;
  1378. if ( $ref->{assembly} && !$form->{ $ref->{id} } ) {
  1379. push @a,
  1380. &include_assembly( $dbh, $myconfig, $form, $ref->{id}, $flds,
  1381. $makemodeljoin );
  1382. if ( $form->{stagger} > $form->{pncol} ) {
  1383. $form->{pncol} = $form->{stagger};
  1384. }
  1385. }
  1386. }
  1387. $sth->finish;
  1388. $form->{$id} = 0;
  1389. $form->{stagger}--;
  1390. @a;
  1391. }
  1392. sub requirements {
  1393. my ( $self, $myconfig, $form ) = @_;
  1394. my $dbh = $form->{dbh};
  1395. my $null;
  1396. my $var;
  1397. my $ref;
  1398. my $where = qq|p.obsolete = '0'|;
  1399. my $dwhere;
  1400. for (qw(partnumber description)) {
  1401. if ( $form->{$_} ne "" ) {
  1402. $var = $dbh->quote( $form->like( lc $form->{$_} ) );
  1403. $where .= qq| AND lower(p.$_) LIKE $var|;
  1404. }
  1405. }
  1406. if ( $form->{partsgroup} ne "" ) {
  1407. ( $null, $var ) = split /--/, $form->{partsgroup};
  1408. $var = $dbh->quote($var);
  1409. $where .= qq| AND p.partsgroup_id = $var|;
  1410. }
  1411. # connect to database
  1412. my ( $transdatefrom, $transdateto );
  1413. if ( $form->{year} ) {
  1414. ( $transdatefrom, $transdateto ) =
  1415. $form->from_to( $form->{year}, '01', 12 );
  1416. $dwhere = qq| AND a.transdate >= '$transdatefrom'
  1417. AND a.transdate <= '$transdateto'|;
  1418. }
  1419. $query = qq|
  1420. SELECT p.id, p.partnumber, p.description, sum(i.qty) AS qty,
  1421. p.onhand, extract(MONTH FROM a.transdate) AS month,
  1422. '0' AS so, '0' AS po
  1423. FROM invoice i
  1424. JOIN parts p ON (p.id = i.parts_id)
  1425. JOIN ar a ON (a.id = i.trans_id)
  1426. WHERE $where $dwhere AND p.inventory_accno_id > 0
  1427. GROUP BY p.id, p.partnumber, p.description, p.onhand,
  1428. extract(MONTH FROM a.transdate)|;
  1429. my $sth = $dbh->prepare($query);
  1430. $sth->execute || $form->dberror($query);
  1431. my %parts;
  1432. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1433. $parts{ $ref->{id} } = $ref;
  1434. }
  1435. $sth->finish;
  1436. my %ofld = ( customer => so, vendor => po );
  1437. for (qw(customer vendor)) {
  1438. $query = qq|
  1439. SELECT p.id, p.partnumber, p.description,
  1440. sum(qty) - sum(ship) AS $ofld{$_}, p.onhand,
  1441. 0 AS month
  1442. FROM orderitems i
  1443. JOIN parts p ON (p.id = i.parts_id)
  1444. JOIN oe a ON (a.id = i.trans_id)
  1445. WHERE $where AND p.inventory_accno_id > 0
  1446. AND p.assembly = '0' AND a.closed = '0'
  1447. AND a.${_}_id > 0
  1448. GROUP BY p.id, p.partnumber, p.description, p.onhand,
  1449. month|;
  1450. $sth = $dbh->prepare($query);
  1451. $sth->execute || $form->dberror($query);
  1452. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1453. if ( exists $parts{ $ref->{id} }->{ $ofld{$_} } ) {
  1454. $parts{ $ref->{id} }->{ $ofld{$_} } += $ref->{ $ofld{$_} };
  1455. }
  1456. else {
  1457. $parts{ $ref->{id} } = $ref;
  1458. }
  1459. }
  1460. $sth->finish;
  1461. }
  1462. # add assemblies from open sales orders
  1463. $query = qq|
  1464. SELECT DISTINCT a.id AS orderid, b.id, i.qty - i.ship AS qty
  1465. FROM parts p
  1466. JOIN assembly b ON (b.parts_id = p.id)
  1467. JOIN orderitems i ON (i.parts_id = b.id)
  1468. JOIN oe a ON (a.id = i.trans_id)
  1469. WHERE $where
  1470. AND (p.inventory_accno_id > 0 OR p.assembly = '1')
  1471. AND a.closed = '0'|;
  1472. $sth = $dbh->prepare($query);
  1473. $sth->execute || $form->dberror($query);
  1474. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1475. &requirements_assembly( $dbh, $form, \%parts, $ref->{id}, $ref->{qty},
  1476. $where )
  1477. if $ref->{qty};
  1478. }
  1479. $sth->finish;
  1480. $dbh->commit;
  1481. for (
  1482. sort { $parts{$a}->{ $form->{sort} } cmp $parts{$b}->{ $form->{sort} } }
  1483. keys %parts
  1484. )
  1485. {
  1486. push @{ $form->{parts} }, $parts{$_};
  1487. }
  1488. }
  1489. sub requirements_assembly {
  1490. my ( $dbh, $form, $parts, $id, $qty, $where ) = @_;
  1491. # assemblies
  1492. my $query = qq|
  1493. SELECT p.id, p.partnumber, p.description, a.qty * ? AS so,
  1494. p.onhand, p.assembly, p.partsgroup_id
  1495. FROM assembly a
  1496. JOIN parts p ON (p.id = a.parts_id)
  1497. WHERE $where AND a.id = ? AND p.inventory_accno_id > 0
  1498. UNION
  1499. SELECT p.id, p.partnumber, p.description, a.qty * ? AS so,
  1500. p.onhand, p.assembly, p.partsgroup_id
  1501. FROM assembly a
  1502. JOIN parts p ON (p.id = a.parts_id)
  1503. WHERE a.id = ? AND p.assembly = '1'|;
  1504. my $sth = $dbh->prepare($query);
  1505. $sth->execute( $qty, $id, $qty, $id ) || $form->dberror($query);
  1506. while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1507. if ( $ref->{assembly} ) {
  1508. &requirements_assembly( $dbh, $form, $parts, $ref->{id}, $ref->{so},
  1509. $where );
  1510. next;
  1511. }
  1512. if ( exists $parts->{ $ref->{id} }{so} ) {
  1513. $parts->{ $ref->{id} }{so} += $ref->{so};
  1514. }
  1515. else {
  1516. $parts->{ $ref->{id} } = $ref;
  1517. }
  1518. }
  1519. $sth->finish;
  1520. }
  1521. sub create_links {
  1522. my ( $self, $module, $myconfig, $form ) = @_;
  1523. # connect to database
  1524. my $dbh = $form->{dbh};
  1525. my $ref;
  1526. my $query = qq|
  1527. SELECT accno, description, link
  1528. FROM chart
  1529. WHERE link LIKE ?
  1530. ORDER BY accno|;
  1531. my $sth = $dbh->prepare($query);
  1532. $sth->execute("%$module%") || $form->dberror($query);
  1533. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1534. foreach my $key ( split /:/, $ref->{link} ) {
  1535. if ( $key =~ /$module/ ) {
  1536. push @{ $form->{"${module}_links"}{$key} },
  1537. {
  1538. accno => $ref->{accno},
  1539. description => $ref->{description}
  1540. };
  1541. }
  1542. }
  1543. }
  1544. $sth->finish;
  1545. if ( $form->{item} ne 'assembly' ) {
  1546. $query = qq|SELECT count(*) FROM vendor|;
  1547. my ($count) = $dbh->selectrow_array($query);
  1548. if ( $count < $myconfig->{vclimit} ) {
  1549. $query = qq|SELECT id, name FROM vendor ORDER BY name|;
  1550. $sth = $dbh->prepare($query);
  1551. $sth->execute || $form->dberror($query);
  1552. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1553. push @{ $form->{all_vendor} }, $ref;
  1554. }
  1555. $sth->finish;
  1556. }
  1557. }
  1558. # pricegroups, customers
  1559. $query = qq|SELECT count(*) FROM customer|;
  1560. ($count) = $dbh->selectrow_array($query);
  1561. if ( $count < $myconfig->{vclimit} ) {
  1562. $query = qq|SELECT id, name FROM customer ORDER BY name|;
  1563. $sth = $dbh->prepare($query);
  1564. $sth->execute || $form->dberror($query);
  1565. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1566. push @{ $form->{all_customer} }, $ref;
  1567. }
  1568. $sth->finish;
  1569. }
  1570. $query = qq|SELECT id, pricegroup FROM pricegroup ORDER BY pricegroup|;
  1571. $sth = $dbh->prepare($query);
  1572. $sth->execute || $form->dberror($query);
  1573. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1574. push @{ $form->{all_pricegroup} }, $ref;
  1575. }
  1576. $sth->finish;
  1577. if ( $form->{id} ) {
  1578. $query = qq|
  1579. SELECT value FROM defaults
  1580. WHERE setting_key = 'weightunit'|;
  1581. ( $form->{weightunit} ) = $dbh->selectrow_array($query);
  1582. $query = qq|
  1583. SELECT value FROM defaults
  1584. WHERE setting_key = 'curr'|;
  1585. ( $form->{currencies} ) = $dbh->selectrow_array($query);
  1586. }
  1587. else {
  1588. # Dieter: FIXME left joins not working
  1589. $query = qq|
  1590. SELECT (SELECT value FROM defaults
  1591. WHERE setting_key = 'weightunit')
  1592. AS weightunit, current_date AS priceupdate,
  1593. (SELECT value FROM defaults
  1594. WHERE setting_key = 'curr') AS currencies,
  1595. c1.accno AS inventory_accno,
  1596. c1.description AS inventory_description,
  1597. c2.accno AS income_accno,
  1598. c2.description AS income_description,
  1599. c3.accno AS expense_accno,
  1600. c3.description AS expense_description
  1601. FROM chart c1, chart c2, chart c3
  1602. WHERE c1.id IN (SELECT value FROM defaults
  1603. WHERE setting_key = 'inventory_accno_id')
  1604. AND c2.id IN (SELECT value FROM defaults
  1605. WHERE setting_key = 'income_accno_id')
  1606. AND c3.id IN (SELECT value FROM defaults
  1607. WHERE setting_key
  1608. = 'expense_accno_id')|;
  1609. $sth = $dbh->prepare($query);
  1610. $sth->execute || $form->dberror($query);
  1611. $ref = $sth->fetchrow_hashref(NAME_lc);
  1612. for (qw(weightunit priceupdate currencies)) {
  1613. $form->{$_} = $ref->{$_};
  1614. }
  1615. # setup accno hash, {amount} is used in create_links
  1616. for (qw(inventory income expense)) {
  1617. $form->{amount}{"IC_$_"} = {
  1618. accno => $ref->{"${_}_accno"},
  1619. description => $ref->{"${_}_description"}
  1620. };
  1621. }
  1622. $sth->finish;
  1623. }
  1624. $dbh->commit;
  1625. }
  1626. sub get_warehouses {
  1627. my ( $self, $myconfig, $form ) = @_;
  1628. my $dbh = $form->{dbh};
  1629. my $query = qq|SELECT id, description FROM warehouse|;
  1630. my $sth = $dbh->prepare($query);
  1631. $sth->execute || $form->dberror($query);
  1632. while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1633. push @{ $form->{all_warehouse} }, $ref;
  1634. }
  1635. $sth->finish;
  1636. $dbh->commit;
  1637. }
  1638. 1;