1 #=====================================================================
2 # SQL-Ledger Accounting
5 # Author: Dieter Simader
6 # Email: dsimader@sql-ledger.org
7 # Web: http://www.sql-ledger.org
11 # This program is free software; you can redistribute it and/or modify
12 # it under the terms of the GNU General Public License as published by
13 # the Free Software Foundation; either version 2 of the License, or
14 # (at your option) any later version.
16 # This program is distributed in the hope that it will be useful,
17 # but WITHOUT ANY WARRANTY; without even the implied warranty of
18 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
19 # GNU General Public License for more details.
20 # You should have received a copy of the GNU General Public License
21 # along with this program; if not, write to the Free Software
22 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
23 #======================================================================
25 # Inventory Control backend
27 #======================================================================
33 my ($self, $myconfig, $form) = @_;
36 my $dbh = $form->dbconnect($myconfig);
39 my $query = qq|SELECT p.*,
40 c1.accno AS inventory_accno,
41 c2.accno AS income_accno,
42 c3.accno AS expense_accno,
45 LEFT JOIN chart c1 ON (p.inventory_accno_id = c1.id)
46 LEFT JOIN chart c2 ON (p.income_accno_id = c2.id)
47 LEFT JOIN chart c3 ON (p.expense_accno_id = c3.id)
48 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
49 WHERE p.id = $form->{id}|;
50 my $sth = $dbh->prepare($query);
51 $sth->execute || $form->dberror($query);
52 my $ref = $sth->fetchrow_hashref(NAME_lc);
54 # copy to $form variables
55 map { $form->{$_} = $ref->{$_} } ( keys %{ $ref } );
59 my %oid = ('Pg' => 'a.oid',
61 'Oracle' => 'a.rowid',
65 # part, service item or labor
66 $form->{item} = ($form->{inventory_accno}) ? 'part' : 'service';
67 $form->{item} = 'labor' if ! $form->{income_accno};
69 if ($form->{assembly}) {
70 $form->{item} = 'assembly';
72 # retrieve assembly items
73 $query = qq|SELECT p.id, p.partnumber, p.description,
74 p.sellprice, p.weight, a.qty, a.bom, a.adj, p.unit,
75 p.lastcost, p.listprice,
76 pg.partsgroup, p.assembly, p.partsgroup_id
78 JOIN assembly a ON (a.parts_id = p.id)
79 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
80 WHERE a.id = $form->{id}
81 ORDER BY $oid{$myconfig->{dbdriver}}|;
83 $sth = $dbh->prepare($query);
84 $sth->execute || $form->dberror($query);
86 $form->{assembly_rows} = 0;
87 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
88 $form->{assembly_rows}++;
89 foreach my $key ( keys %{ $ref } ) {
90 $form->{"${key}_$form->{assembly_rows}"} = $ref->{$key};
97 # setup accno hash for <option checked> {amount} is used in create_links
98 $form->{amount}{IC} = $form->{inventory_accno};
99 $form->{amount}{IC_income} = $form->{income_accno};
100 $form->{amount}{IC_sale} = $form->{income_accno};
101 $form->{amount}{IC_expense} = $form->{expense_accno};
102 $form->{amount}{IC_cogs} = $form->{expense_accno};
105 if ($form->{item} =~ /(part|assembly)/) {
107 if ($form->{makemodel}) {
108 $query = qq|SELECT make, model
110 WHERE parts_id = $form->{id}|;
112 $sth = $dbh->prepare($query);
113 $sth->execute || $form->dberror($query);
115 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
116 push @{ $form->{makemodels} }, $ref;
122 # now get accno for taxes
123 $query = qq|SELECT c.accno
124 FROM chart c, partstax pt
125 WHERE pt.chart_id = c.id
126 AND pt.parts_id = $form->{id}|;
128 $sth = $dbh->prepare($query);
129 $sth->execute || $form->dberror($query);
131 while (($key) = $sth->fetchrow_array) {
132 $form->{amount}{$key} = $key;
138 $query = qq|SELECT parts_id
140 WHERE parts_id = $form->{id}
144 WHERE parts_id = $form->{id}
148 WHERE parts_id = $form->{id}|;
149 $sth = $dbh->prepare($query);
150 $sth->execute || $form->dberror($query);
152 ($form->{orphaned}) = $sth->fetchrow_array;
153 $form->{orphaned} = !$form->{orphaned};
157 if ($form->{item} =~ /(part|service)/) {
159 $query = qq|SELECT v.id, v.name, pv.partnumber,
160 pv.lastcost, pv.leadtime, pv.curr AS vendorcurr
162 JOIN vendor v ON (v.id = pv.vendor_id)
163 WHERE pv.parts_id = $form->{id}
166 $sth = $dbh->prepare($query);
167 $sth->execute || $form->dberror($query);
169 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
170 push @{ $form->{vendormatrix} }, $ref;
176 if ($form->{item} ne 'labor') {
177 $query = qq|SELECT pc.pricebreak, pc.sellprice AS customerprice,
178 pc.curr AS customercurr,
179 pc.validfrom, pc.validto,
180 c.name, c.id AS cid, g.pricegroup, g.id AS gid
181 FROM partscustomer pc
182 LEFT JOIN customer c ON (c.id = pc.customer_id)
183 LEFT JOIN pricegroup g ON (g.id = pc.pricegroup_id)
184 WHERE pc.parts_id = $form->{id}
185 ORDER BY c.name, g.pricegroup, pc.pricebreak|;
186 $sth = $dbh->prepare($query);
187 $sth->execute || $form->dberror($query);
189 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
190 push @{ $form->{customermatrix} }, $ref;
201 my ($self, $myconfig, $form) = @_;
203 ($form->{inventory_accno}) = split(/--/, $form->{IC});
204 ($form->{expense_accno}) = split(/--/, $form->{IC_expense});
205 ($form->{income_accno}) = split(/--/, $form->{IC_income});
207 # connect to database, turn off AutoCommit
208 my $dbh = $form->dbconnect_noauto($myconfig);
211 # make up a unique handle and store in partnumber field
212 # then retrieve the record based on the unique handle to get the id
213 # replace the partnumber field with the actual variable
214 # add records for makemodel
216 # if there is a $form->{id} then replace the old entry
217 # delete all makemodel entries and add the new ones
219 # undo amount formatting
220 map { $form->{$_} = $form->parse_amount($myconfig, $form->{$_}) } qw(rop weight listprice sellprice lastcost stock);
222 $form->{lastcost} = $form->{sellprice} if $form->{item} eq 'labor';
224 $form->{makemodel} = (($form->{make_1}) || ($form->{model_1})) ? 1 : 0;
226 $form->{assembly} = ($form->{item} eq 'assembly') ? 1 : 0;
227 map { $form->{$_} *= 1 } qw(alternate obsolete onhand);
239 $query = qq|SELECT listprice, sellprice, lastcost, weight
241 WHERE id = $form->{id}|;
242 $sth = $dbh->prepare($query);
243 $sth->execute || $form->dberror($query);
245 my ($listprice, $sellprice, $lastcost, $weight) = $sth->fetchrow_array;
248 # if item is part of an assembly adjust all assemblies
249 $query = qq|SELECT id, qty, adj
251 WHERE parts_id = $form->{id}|;
252 $sth = $dbh->prepare($query);
253 $sth->execute || $form->dberror($query);
255 while (my ($id, $qty, $adj) = $sth->fetchrow_array) {
256 &update_assembly($dbh, $form, $id, $qty, $adj, $listprice * 1, $sellprice * 1, $lastcost * 1, $weight * 1);
260 if ($form->{item} =~ /(part|service)/) {
261 # delete partsvendor records
262 $query = qq|DELETE FROM partsvendor
263 WHERE parts_id = $form->{id}|;
264 $dbh->do($query) || $form->dberror($query);
267 if ($form->{item} !~ /(service|labor)/) {
268 # delete makemodel records
269 $query = qq|DELETE FROM makemodel
270 WHERE parts_id = $form->{id}|;
271 $dbh->do($query) || $form->dberror($query);
274 if ($form->{item} eq 'assembly') {
275 if ($form->{onhand} != 0) {
276 &adjust_inventory($dbh, $form, $form->{id}, $form->{onhand} * -1);
279 if ($form->{orphaned}) {
280 # delete assembly records
281 $query = qq|DELETE FROM assembly
282 WHERE id = $form->{id}|;
283 $dbh->do($query) || $form->dberror($query);
286 $query = qq|UPDATE assembly
290 $sth = $dbh->prepare($query);
292 for $i (1 .. $form->{assembly_rows} - 1) {
293 $sth->execute(($form->{"bom_$i"}) ? '1' : '0', ($form->{"adj_$i"}) ? '1' : '0', $form->{id}, $form->{"id_$i"});
298 $form->{onhand} += $form->{stock};
303 $query = qq|DELETE FROM partstax
304 WHERE parts_id = $form->{id}|;
305 $dbh->do($query) || $form->dberror($query);
308 $query = qq|DELETE FROM partscustomer
309 WHERE parts_id = $form->{id}|;
310 $dbh->do($query) || $form->dberror($query);
314 $uid .= $form->{login};
316 $query = qq|INSERT INTO parts (partnumber)
318 $dbh->do($query) || $form->dberror($query);
320 $query = qq|SELECT id FROM parts
321 WHERE partnumber = '$uid'|;
322 $sth = $dbh->prepare($query);
323 $sth->execute || $form->dberror($query);
325 ($form->{id}) = $sth->fetchrow_array;
328 $form->{orphaned} = 1;
329 $form->{onhand} = ($form->{stock} * 1) if $form->{item} eq 'assembly';
334 ($null, $partsgroup_id) = split /--/, $form->{partsgroup};
337 $form->{partnumber} = $form->update_defaults($myconfig, "partnumber", $dbh) if ! $form->{partnumber};
339 $query = qq|UPDATE parts SET
340 partnumber = |.$dbh->quote($form->{partnumber}).qq|,
341 description = |.$dbh->quote($form->{description}).qq|,
342 makemodel = '$form->{makemodel}',
343 alternate = '$form->{alternate}',
344 assembly = '$form->{assembly}',
345 listprice = $form->{listprice},
346 sellprice = $form->{sellprice},
347 lastcost = $form->{lastcost},
348 weight = $form->{weight},
349 priceupdate = |.$form->dbquote($form->{priceupdate}, SQL_DATE).qq|,
350 unit = |.$dbh->quote($form->{unit}).qq|,
351 notes = |.$dbh->quote($form->{notes}).qq|,
353 bin = |.$dbh->quote($form->{bin}).qq|,
354 inventory_accno_id = (SELECT id FROM chart
355 WHERE accno = '$form->{inventory_accno}'),
356 income_accno_id = (SELECT id FROM chart
357 WHERE accno = '$form->{income_accno}'),
358 expense_accno_id = (SELECT id FROM chart
359 WHERE accno = '$form->{expense_accno}'),
360 obsolete = '$form->{obsolete}',
361 image = '$form->{image}',
362 drawing = '$form->{drawing}',
363 microfiche = '$form->{microfiche}',
364 partsgroup_id = $partsgroup_id
365 WHERE id = $form->{id}|;
366 $dbh->do($query) || $form->dberror($query);
369 # insert makemodel records
370 if ($form->{item} =~ /(part|assembly)/) {
371 for $i (1 .. $form->{makemodel_rows}) {
372 if (($form->{"make_$i"}) || ($form->{"model_$i"})) {
373 $query = qq|INSERT INTO makemodel (parts_id, make, model)
374 VALUES ($form->{id},|
375 .$dbh->quote($form->{"make_$i"}).qq|, |
376 .$dbh->quote($form->{"model_$i"}).qq|)|;
377 $dbh->do($query) || $form->dberror($query);
384 foreach $item (split / /, $form->{taxaccounts}) {
385 if ($form->{"IC_tax_$item"}) {
386 $query = qq|INSERT INTO partstax (parts_id, chart_id)
390 WHERE accno = '$item'))|;
391 $dbh->do($query) || $form->dberror($query);
395 # add assembly records
396 if ($form->{item} eq 'assembly') {
398 if ($form->{orphaned}) {
399 for $i (1 .. $form->{assembly_rows}) {
400 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
402 if ($form->{"qty_$i"} != 0) {
403 map { $form->{"${_}_$i"} *= 1 } qw(bom adj);
404 $query = qq|INSERT INTO assembly (id, parts_id, qty, bom, adj)
405 VALUES ($form->{id}, $form->{"id_$i"},
406 $form->{"qty_$i"}, '$form->{"bom_$i"}',
407 '$form->{"adj_$i"}')|;
408 $dbh->do($query) || $form->dberror($query);
413 # adjust onhand for the parts
414 if ($form->{onhand} != 0) {
415 &adjust_inventory($dbh, $form, $form->{id}, $form->{onhand});
418 @a = localtime; $a[5] += 1900; $a[4]++;
419 my $shippingdate = "$a[5]-$a[4]-$a[3]";
421 ($form->{employee}, $form->{employee_id}) = $form->get_employee($dbh);
423 # add inventory record
424 if ($form->{stock} != 0) {
425 $query = qq|INSERT INTO inventory (warehouse_id, parts_id, qty,
426 shippingdate, employee_id) VALUES (
427 0, $form->{id}, $form->{stock}, '$shippingdate',
428 $form->{employee_id})|;
429 $dbh->do($query) || $form->dberror($query);
436 if ($form->{item} ne 'assembly') {
437 for $i (1 .. $form->{vendor_rows}) {
438 if ($form->{"vendor_$i"} && $form->{"lastcost_$i"}) {
440 ($null, $vendor_id) = split /--/, $form->{"vendor_$i"};
442 map { $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"})} qw(lastcost leadtime);
444 $query = qq|INSERT INTO partsvendor (vendor_id, parts_id, partnumber,
445 lastcost, leadtime, curr)
446 VALUES ($vendor_id, $form->{id},|
447 .$dbh->quote($form->{"partnumber_$i"}).qq|,
448 $form->{"lastcost_$i"},
449 $form->{"leadtime_$i"}, '$form->{"vendorcurr_$i"}')|;
450 $dbh->do($query) || $form->dberror($query);
457 for $i (1 .. $form->{customer_rows}) {
459 map { $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"})} qw(pricebreak customerprice);
461 if ($form->{"customerprice_$i"}) {
463 ($null, $customer_id) = split /--/, $form->{"customer_$i"};
466 ($null, $pricegroup_id) = split /--/, $form->{"pricegroup_$i"};
469 $query = qq|INSERT INTO partscustomer (parts_id, customer_id,
470 pricegroup_id, pricebreak, sellprice, curr,
472 VALUES ($form->{id}, $customer_id,
473 $pricegroup_id, $form->{"pricebreak_$i"},
474 $form->{"customerprice_$i"}, '$form->{"customercurr_$i"}',|
475 .$form->dbquote($form->{"validfrom_$i"}, SQL_DATE).qq|, |
476 .$form->dbquote($form->{"validto_$i"}, SQL_DATE).qq|)|;
477 $dbh->do($query) || $form->dberror($query);
482 my $rc = $dbh->commit;
491 sub update_assembly {
492 my ($dbh, $form, $id, $qty, $adj, $listprice, $sellprice, $lastcost, $weight) = @_;
494 my $formlistprice = $form->{listprice};
495 my $formsellprice = $form->{sellprice};
498 $formlistprice = $listprice;
499 $formsellprice = $sellprice;
502 my $query = qq|SELECT id, qty, adj
504 WHERE parts_id = $id|;
505 my $sth = $dbh->prepare($query);
506 $sth->execute || $form->dberror($query);
510 while (my ($pid, $aqty, $aadj) = $sth->fetchrow_array) {
511 &update_assembly($dbh, $form, $pid, $aqty * $qty, $aadj, $listprice, $sellprice, $lastcost, $weight) if !$form->{$pid};
515 $query = qq|UPDATE parts
516 SET listprice = listprice +
517 $qty * ($formlistprice - $listprice),
518 sellprice = sellprice +
519 $qty * ($formsellprice - $sellprice),
520 lastcost = lastcost +
521 $qty * ($form->{lastcost} - $lastcost),
523 $qty * ($form->{weight} - $weight)
525 $dbh->do($query) || $form->dberror($query);
533 sub retrieve_assemblies {
534 my ($self, $myconfig, $form) = @_;
536 # connect to database
537 my $dbh = $form->dbconnect($myconfig);
541 if ($form->{partnumber}) {
542 my $partnumber = $form->like(lc $form->{partnumber});
543 $where .= " AND lower(p.partnumber) LIKE '$partnumber'";
546 if ($form->{description}) {
547 my $description = $form->like(lc $form->{description});
548 $where .= " AND lower(p.description) LIKE '$description'";
550 $where .= " AND NOT p.obsolete = '1'";
552 my %ordinal = ( 'partnumber' => 2,
557 my @a = qw(partnumber description bin);
558 my $sortorder = $form->sort_order(\@a, \%ordinal);
561 # retrieve assembly items
562 my $query = qq|SELECT p.id, p.partnumber, p.description,
563 p.bin, p.onhand, p.rop,
564 (SELECT sum(p2.inventory_accno_id)
565 FROM parts p2, assembly a
566 WHERE p2.id = a.parts_id
567 AND a.id = p.id) AS inventory
571 ORDER BY $sortorder|;
573 my $sth = $dbh->prepare($query);
574 $sth->execute || $form->dberror($query);
577 if ($form->{checkinventory}) {
578 $query = qq|SELECT p.id, p.onhand, a.qty FROM parts p
579 JOIN assembly a ON (a.parts_id = p.id)
581 $inh = $dbh->prepare($query) || $form->dberror($query);
591 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
592 if ($ref->{inventory}) {
594 if ($form->{checkinventory}) {
595 $inh->execute($ref->{id}) || $form->dberror($query);;
597 while ($aref = $inh->fetchrow_hashref(NAME_lc)) {
598 $onhand{$aref->{id}} = (exists $onhand{$aref->{id}}) ? $onhand{$aref->{id}} : $aref->{onhand};
600 if ($aref->{onhand} >= $aref->{qty}) {
602 $howmany = ($aref->{qty}) ? $aref->{onhand}/$aref->{qty} : 1;
604 $stock = ($stock > $howmany) ? $howmany : $stock;
610 $onhand{$aref->{id}} -= ($aref->{qty} * $stock);
618 $ref->{stock} = (($ref->{rop} - $ref->{qty}) > $stock) ? int $stock : $ref->{rop};
620 push @{ $form->{assembly_items} }, $ref if $ok;
630 sub restock_assemblies {
631 my ($self, $myconfig, $form) = @_;
633 # connect to database
634 my $dbh = $form->dbconnect_noauto($myconfig);
636 @a = localtime; $a[5] += 1900; $a[4]++;
637 my $shippingdate = "$a[5]-$a[4]-$a[3]";
639 ($form->{employee}, $form->{employee_id}) = $form->get_employee($dbh);
641 for my $i (1 .. $form->{rowcount}) {
643 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
645 if ($form->{"qty_$i"} != 0) {
646 &adjust_inventory($dbh, $form, $form->{"id_$i"}, $form->{"qty_$i"});
649 # add inventory record
650 if ($form->{"qty_$i"} != 0) {
651 $query = qq|INSERT INTO inventory (warehouse_id, parts_id, qty,
652 shippingdate, employee_id) VALUES (
653 0, $form->{"id_$i"}, $form->{"qty_$i"}, '$shippingdate',
654 $form->{employee_id})|;
655 $dbh->do($query) || $form->dberror($query);
660 my $rc = $dbh->commit;
668 sub adjust_inventory {
669 my ($dbh, $form, $id, $qty) = @_;
671 my $query = qq|SELECT p.id, p.inventory_accno_id, p.assembly, a.qty
672 FROM parts p, assembly a
673 WHERE a.parts_id = p.id
675 my $sth = $dbh->prepare($query);
676 $sth->execute || $form->dberror($query);
678 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
680 my $allocate = $qty * $ref->{qty};
682 # is it a service item then loop
683 if (($ref->{inventory_accno_id} *= 1) == 0) {
684 next unless $ref->{assembly}; # assembly
687 # adjust parts onhand
688 $form->update_balance($dbh,
698 $form->update_balance($dbh,
708 my ($self, $myconfig, $form) = @_;
710 # connect to database, turn off AutoCommit
711 my $dbh = $form->dbconnect_noauto($myconfig);
713 my $query = qq|DELETE FROM parts
714 WHERE id = $form->{id}|;
715 $dbh->do($query) || $form->dberror($query);
717 $query = qq|DELETE FROM partstax
718 WHERE parts_id = $form->{id}|;
719 $dbh->do($query) || $form->dberror($query);
722 if ($form->{item} ne 'assembly') {
723 $query = qq|DELETE FROM partsvendor
724 WHERE parts_id = $form->{id}|;
725 $dbh->do($query) || $form->dberror($query);
728 # check if it is a part, assembly or service
729 if ($form->{item} ne 'service') {
730 $query = qq|DELETE FROM makemodel
731 WHERE parts_id = $form->{id}|;
732 $dbh->do($query) || $form->dberror($query);
735 if ($form->{item} eq 'assembly') {
737 $query = qq|DELETE FROM inventory
738 WHERE parts_id = $form->{id}|;
739 $dbh->do($query) || $form->dberror($query);
741 $query = qq|DELETE FROM assembly
742 WHERE id = $form->{id}|;
743 $dbh->do($query) || $form->dberror($query);
746 if ($form->{item} eq 'alternate') {
747 $query = qq|DELETE FROM alternate
748 WHERE id = $form->{id}|;
749 $dbh->do($query) || $form->dberror($query);
752 $query = qq|DELETE FROM partscustomer
753 WHERE parts_id = $form->{id}|;
754 $dbh->do($query) || $form->dberror($query);
756 $query = qq|DELETE FROM translation
757 WHERE trans_id = $form->{id}|;
758 $dbh->do($query) || $form->dberror($query);
761 my $rc = $dbh->commit;
770 my ($self, $myconfig, $form) = @_;
772 my $i = $form->{assembly_rows};
775 my $where = "p.obsolete = '0'";
777 if ($form->{"partnumber_$i"}) {
778 $var = $form->like(lc $form->{"partnumber_$i"});
779 $where .= " AND lower(p.partnumber) LIKE '$var'";
781 if ($form->{"description_$i"}) {
782 $var = $form->like(lc $form->{"description_$i"});
783 $where .= " AND lower(p.description) LIKE '$var'";
785 if ($form->{"partsgroup_$i"}) {
786 ($null, $var) = split /--/, $form->{"partsgroup_$i"};
787 $where .= qq| AND p.partsgroup_id = $var|;
791 $where .= " AND p.id != $form->{id}";
795 $where .= " ORDER BY p.partnumber";
797 $where .= " ORDER BY p.description";
800 # connect to database
801 my $dbh = $form->dbconnect($myconfig);
803 my $query = qq|SELECT p.id, p.partnumber, p.description, p.sellprice,
804 p.weight, p.onhand, p.unit, p.lastcost,
805 pg.partsgroup, p.partsgroup_id
807 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
809 my $sth = $dbh->prepare($query);
810 $sth->execute || $form->dberror($query);
812 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
813 push @{ $form->{item_list} }, $ref;
823 my ($self, $myconfig, $form) = @_;
831 foreach $item (qw(partnumber drawing microfiche)) {
832 if ($form->{$item}) {
833 $var = $form->like(lc $form->{$item});
834 $where .= " AND lower(p.$item) LIKE '$var'";
837 # special case for description
838 if ($form->{description}) {
839 unless ($form->{bought} || $form->{sold} || $form->{onorder} || $form->{ordered} || $form->{rfq} || $form->{quoted}) {
840 $var = $form->like(lc $form->{description});
841 $where .= " AND lower(p.description) LIKE '$var'";
845 # assembly components
847 if ($form->{searchitems} eq 'component') {
848 $assemblyflds = qq|, p1.partnumber AS assemblypartnumber, a.id AS assembly_id|;
851 # special case for serialnumber
852 if ($form->{l_serialnumber}) {
853 if ($form->{serialnumber}) {
854 $var = $form->like(lc $form->{serialnumber});
855 $where .= " AND lower(i.serialnumber) LIKE '$var'";
859 if ($form->{warehouse} || $form->{l_warehouse}) {
860 $form->{l_warehouse} = 1;
863 if ($form->{searchitems} eq 'part') {
864 $where .= " AND p.inventory_accno_id > 0 AND p.assembly = '0' AND p.income_accno_id > 0";
866 if ($form->{searchitems} eq 'assembly') {
867 $form->{bought} = "";
868 $where .= " AND p.assembly = '1'";
870 if ($form->{searchitems} eq 'service') {
871 $where .= " AND p.inventory_accno_id IS NULL AND p.assembly = '0'";
873 if ($form->{searchitems} eq 'labor') {
874 $where .= " AND p.inventory_accno_id > 0 AND p.income_accno_id IS NULL";
877 # items which were never bought, sold or on an order
878 if ($form->{itemstatus} eq 'orphaned') {
879 $where .= " AND p.onhand = 0
880 AND p.id NOT IN (SELECT p.id FROM parts p, invoice i
881 WHERE p.id = i.parts_id)
882 AND p.id NOT IN (SELECT p.id FROM parts p, assembly a
883 WHERE p.id = a.parts_id)
884 AND p.id NOT IN (SELECT p.id FROM parts p, orderitems o
885 WHERE p.id = o.parts_id)";
888 if ($form->{itemstatus} eq 'active') {
889 $where .= " AND p.obsolete = '0'";
891 if ($form->{itemstatus} eq 'obsolete') {
892 $where .= " AND p.obsolete = '1'";
894 if ($form->{itemstatus} eq 'onhand') {
895 $where .= " AND p.onhand > 0";
897 if ($form->{itemstatus} eq 'short') {
898 $where .= " AND p.onhand < p.rop";
901 my $makemodelflds = qq|, '', ''|;;
904 if ($form->{make} || $form->{l_make} || $form->{model} || $form->{l_model}) {
905 $makemodelflds = qq|, m.make, m.model|;
906 $makemodeljoin = qq|LEFT JOIN makemodel m ON (m.parts_id = p.id)|;
909 $var = $form->like(lc $form->{make});
910 $where .= " AND lower(m.make) LIKE '$var'";
912 if ($form->{model}) {
913 $var = $form->like(lc $form->{model});
914 $where .= " AND lower(m.model) LIKE '$var'";
917 if ($form->{partsgroup}) {
918 ($null, $var) = split /--/, $form->{partsgroup};
919 $where .= qq| AND p.partsgroup_id = $var|;
922 # connect to database
923 my $dbh = $form->dbconnect($myconfig);
925 my %ordinal = ( 'partnumber' => 2,
934 'assemblypartnumber' => 21
937 my @a = qw(partnumber description);
938 my $sortorder = $form->sort_order(\@a, \%ordinal);
940 my $query = qq|SELECT curr FROM defaults|;
941 my ($curr) = $dbh->selectrow_array($query);
944 my $flds = qq|p.id, p.partnumber, p.description, p.onhand, p.unit,
945 p.bin, p.sellprice, p.listprice, p.lastcost, p.rop,
946 p.weight, p.priceupdate, p.image, p.drawing, p.microfiche,
947 p.assembly, pg.partsgroup, '$curr' AS curr
948 $makemodelflds $assemblyflds
951 $query = qq|SELECT $flds
953 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
956 ORDER BY $sortorder|;
958 # redo query for components report
959 if ($form->{searchitems} eq 'component') {
961 $query = qq|SELECT $flds
963 JOIN parts p ON (a.parts_id = p.id)
964 JOIN parts p1 ON (a.id = p1.id)
965 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
968 ORDER BY $sortorder|;
973 # rebuild query for bought and sold items
974 if ($form->{bought} || $form->{sold} || $form->{onorder} || $form->{ordered} || $form->{rfq} || $form->{quoted}) {
977 my @a = qw(partnumber description employee);
979 push @a, qw(invnumber serialnumber) if ($form->{bought} || $form->{sold});
980 push @a, "ordnumber" if ($form->{onorder} || $form->{ordered});
981 push @a, "quonumber" if ($form->{rfq} || $form->{quoted});
983 %ordinal = ( 'partnumber' => 2,
998 $sortorder = $form->sort_order(\@a, \%ordinal);
1003 if ($form->{bought} || $form->{sold}) {
1005 my $invwhere = "$where";
1006 my $transdate = ($form->{method} eq 'accrual') ? "transdate" : "datepaid";
1008 $invwhere .= " AND i.assemblyitem = '0'";
1009 $invwhere .= " AND a.$transdate >= '$form->{transdatefrom}'" if $form->{transdatefrom};
1010 $invwhere .= " AND a.$transdate <= '$form->{transdateto}'" if $form->{transdateto};
1012 if ($form->{description}) {
1013 $var = $form->like(lc $form->{description});
1014 $invwhere .= " AND lower(i.description) LIKE '$var'";
1017 if ($form->{open} || $form->{closed}) {
1018 if ($form->{open} && $form->{closed}) {
1019 if ($form->{method} eq 'cash') {
1020 $invwhere .= " AND a.amount = a.paid";
1023 if ($form->{open}) {
1024 if ($form->{method} eq 'cash') {
1025 $invwhere .= " AND a.id = 0";
1027 $invwhere .= " AND NOT a.amount = a.paid";
1030 $invwhere .= " AND a.amount = a.paid";
1034 $invwhere .= " AND a.id = 0";
1037 my $flds = qq|p.id, p.partnumber, i.description, i.serialnumber,
1038 i.qty AS onhand, i.unit, p.bin, i.sellprice,
1039 p.listprice, p.lastcost, p.rop, p.weight,
1040 p.priceupdate, p.image, p.drawing, p.microfiche,
1042 pg.partsgroup, a.invnumber, a.ordnumber, a.quonumber,
1043 i.trans_id, ct.name, e.name AS employee, a.curr, a.till
1047 if ($form->{bought}) {
1049 SELECT $flds, 'ir' AS module, '' AS type,
1050 (SELECT sell FROM exchangerate ex
1051 WHERE ex.curr = a.curr
1052 AND ex.transdate = a.$transdate) AS exchangerate,
1055 JOIN parts p ON (p.id = i.parts_id)
1056 JOIN ap a ON (a.id = i.trans_id)
1057 JOIN vendor ct ON (a.vendor_id = ct.id)
1058 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1059 LEFT JOIN employee e ON (a.employee_id = e.id)
1066 if ($form->{sold}) {
1068 SELECT $flds, 'is' AS module, '' AS type,
1069 (SELECT buy FROM exchangerate ex
1070 WHERE ex.curr = a.curr
1071 AND ex.transdate = a.$transdate) AS exchangerate,
1074 JOIN parts p ON (p.id = i.parts_id)
1075 JOIN ar a ON (a.id = i.trans_id)
1076 JOIN customer ct ON (a.customer_id = ct.id)
1077 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1078 LEFT JOIN employee e ON (a.employee_id = e.id)
1086 if ($form->{onorder} || $form->{ordered}) {
1087 my $ordwhere = "$where
1088 AND a.quotation = '0'";
1089 $ordwhere .= " AND a.transdate >= '$form->{transdatefrom}'" if $form->{transdatefrom};
1090 $ordwhere .= " AND a.transdate <= '$form->{transdateto}'" if $form->{transdateto};
1092 if ($form->{description}) {
1093 $var = $form->like(lc $form->{description});
1094 $ordwhere .= " AND lower(i.description) LIKE '$var'";
1097 if ($form->{open} || $form->{closed}) {
1098 unless ($form->{open} && $form->{closed}) {
1099 $ordwhere .= " AND a.closed = '0'" if $form->{open};
1100 $ordwhere .= " AND a.closed = '1'" if $form->{closed};
1103 $ordwhere .= " AND a.id = 0";
1106 $flds = qq|p.id, p.partnumber, i.description, '' AS serialnumber,
1107 i.qty AS onhand, i.unit, p.bin, i.sellprice,
1108 p.listprice, p.lastcost, p.rop, p.weight,
1109 p.priceupdate, p.image, p.drawing, p.microfiche,
1111 pg.partsgroup, '' AS invnumber, a.ordnumber, a.quonumber,
1112 i.trans_id, ct.name,e.name AS employee, a.curr, '0' AS till
1115 if ($form->{ordered}) {
1117 SELECT $flds, 'oe' AS module, 'sales_order' AS type,
1118 (SELECT buy FROM exchangerate ex
1119 WHERE ex.curr = a.curr
1120 AND ex.transdate = a.transdate) AS exchangerate,
1123 JOIN parts p ON (i.parts_id = p.id)
1124 JOIN oe a ON (i.trans_id = a.id)
1125 JOIN customer ct ON (a.customer_id = ct.id)
1126 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1127 LEFT JOIN employee e ON (a.employee_id = e.id)
1130 AND a.customer_id > 0|;
1135 if ($form->{onorder}) {
1136 $flds = qq|p.id, p.partnumber, i.description, '' AS serialnumber,
1137 i.qty AS onhand, i.unit, p.bin, i.sellprice,
1138 p.listprice, p.lastcost, p.rop, p.weight,
1139 p.priceupdate, p.image, p.drawing, p.microfiche,
1141 pg.partsgroup, '' AS invnumber, a.ordnumber, a.quonumber,
1142 i.trans_id, ct.name,e.name AS employee, a.curr, '0' AS till
1146 SELECT $flds, 'oe' AS module, 'purchase_order' AS type,
1147 (SELECT sell FROM exchangerate ex
1148 WHERE ex.curr = a.curr
1149 AND ex.transdate = a.transdate) AS exchangerate,
1152 JOIN parts p ON (i.parts_id = p.id)
1153 JOIN oe a ON (i.trans_id = a.id)
1154 JOIN vendor ct ON (a.vendor_id = ct.id)
1155 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1156 LEFT JOIN employee e ON (a.employee_id = e.id)
1159 AND a.vendor_id > 0|;
1164 if ($form->{rfq} || $form->{quoted}) {
1165 my $quowhere = "$where
1166 AND a.quotation = '1'";
1167 $quowhere .= " AND a.transdate >= '$form->{transdatefrom}'" if $form->{transdatefrom};
1168 $quowhere .= " AND a.transdate <= '$form->{transdateto}'" if $form->{transdateto};
1170 if ($form->{description}) {
1171 $var = $form->like(lc $form->{description});
1172 $quowhere .= " AND lower(i.description) LIKE '$var'";
1175 if ($form->{open} || $form->{closed}) {
1176 unless ($form->{open} && $form->{closed}) {
1177 $ordwhere .= " AND a.closed = '0'" if $form->{open};
1178 $ordwhere .= " AND a.closed = '1'" if $form->{closed};
1181 $ordwhere .= " AND a.id = 0";
1185 $flds = qq|p.id, p.partnumber, i.description, '' AS serialnumber,
1186 i.qty AS onhand, i.unit, p.bin, i.sellprice,
1187 p.listprice, p.lastcost, p.rop, p.weight,
1188 p.priceupdate, p.image, p.drawing, p.microfiche,
1190 pg.partsgroup, '' AS invnumber, a.ordnumber, a.quonumber,
1191 i.trans_id, ct.name, e.name AS employee, a.curr, '0' AS till
1194 if ($form->{quoted}) {
1196 SELECT $flds, 'oe' AS module, 'sales_quotation' AS type,
1197 (SELECT buy FROM exchangerate ex
1198 WHERE ex.curr = a.curr
1199 AND ex.transdate = a.transdate) AS exchangerate,
1202 JOIN parts p ON (i.parts_id = p.id)
1203 JOIN oe a ON (i.trans_id = a.id)
1204 JOIN customer ct ON (a.customer_id = ct.id)
1205 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1206 LEFT JOIN employee e ON (a.employee_id = e.id)
1209 AND a.customer_id > 0|;
1215 $flds = qq|p.id, p.partnumber, i.description, '' AS serialnumber,
1216 i.qty AS onhand, i.unit, p.bin, i.sellprice,
1217 p.listprice, p.lastcost, p.rop, p.weight,
1218 p.priceupdate, p.image, p.drawing, p.microfiche,
1220 pg.partsgroup, '' AS invnumber, a.ordnumber, a.quonumber,
1221 i.trans_id, ct.name, e.name AS employee, a.curr, '0' AS till
1225 SELECT $flds, 'oe' AS module, 'request_quotation' AS type,
1226 (SELECT sell FROM exchangerate ex
1227 WHERE ex.curr = a.curr
1228 AND ex.transdate = a.transdate) AS exchangerate,
1231 JOIN parts p ON (i.parts_id = p.id)
1232 JOIN oe a ON (i.trans_id = a.id)
1233 JOIN vendor ct ON (a.vendor_id = ct.id)
1234 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1235 LEFT JOIN employee e ON (a.employee_id = e.id)
1238 AND a.vendor_id > 0|;
1244 ORDER BY $sortorder|;
1249 my $sth = $dbh->prepare($query);
1250 $sth->execute || $form->dberror($query);
1252 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1253 push @{ $form->{parts} }, $ref;
1259 # include individual items for assembly
1260 if ($form->{searchitems} eq 'assembly' && $form->{bom}) {
1262 if ($form->{sold} || $form->{ordered} || $form->{quoted}) {
1263 $flds = qq|p.id, p.partnumber, p.description, a.qty AS onhand, p.unit,
1264 p.bin, p.sellprice, p.listprice, p.lastcost, p.rop,
1265 p.weight, p.priceupdate, p.image, p.drawing, p.microfiche,
1266 p.assembly, pg.partsgroup
1267 $makemodelflds $assemblyflds
1270 # replace p.onhand with a.qty AS onhand
1271 $flds =~ s/p.onhand/a.qty AS onhand/;
1274 while ($item = shift @{ $form->{parts} }) {
1276 $flds =~ s/a\.qty.*AS onhand/a\.qty * $item->{onhand} AS onhand/;
1277 push @a, &include_assembly($dbh, $form, $item->{id}, $flds, $makemodeljoin);
1278 push @a, {id => $item->{id}};
1281 # copy assemblies to $form->{parts}
1282 @{ $form->{parts} } = @a;
1288 if ($form->{l_warehouse} || $form->{l_warehouse}) {
1290 if ($form->{warehouse}) {
1291 ($null, $var) = split /--/, $form->{warehouse};
1293 $query = qq|SELECT SUM(qty) AS onhand, '$null' AS description
1295 WHERE warehouse_id = $var
1299 $query = qq|SELECT SUM(i.qty) AS onhand, w.description AS warehouse
1301 JOIN warehouse w ON (w.id = i.warehouse_id)
1302 WHERE i.parts_id = ?
1303 GROUP BY w.description|;
1306 $sth = $dbh->prepare($query) || $form->dberror($query);
1308 foreach $item (@{ $form->{parts} }) {
1310 if ($item->{onhand} <= 0 && ! $form->{warehouse}) {
1315 $sth->execute($item->{id}) || $form->dberror($query);
1317 if ($form->{warehouse}) {
1319 $ref = $sth->fetchrow_hashref(NAME_lc);
1320 if ($ref->{onhand} > 0) {
1321 $item->{onhand} = $ref->{onhand};
1329 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1330 if ($ref->{onhand} > 0) {
1339 @{ $form->{parts} } = @a;
1348 sub include_assembly {
1349 my ($dbh, $form, $id, $flds, $makemodeljoin) = @_;
1352 if ($form->{stagger} > $form->{pncol}) {
1353 $form->{pncol} = $form->{stagger};
1359 my $query = qq|SELECT $flds
1361 JOIN assembly a ON (a.parts_id = p.id)
1362 LEFT JOIN partsgroup pg ON (pg.id = p.id)
1365 my $sth = $dbh->prepare($query);
1366 $sth->execute || $form->dberror($query);
1368 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1369 $ref->{assemblyitem} = 1;
1370 $ref->{stagger} = $form->{stagger};
1372 if ($ref->{assembly} && !$form->{$ref->{id}}) {
1373 push @a, &include_assembly($dbh, $form, $ref->{id}, $flds, $makemodeljoin);
1374 if ($form->{stagger} > $form->{pncol}) {
1375 $form->{pncol} = $form->{stagger};
1390 my ($self, $module, $myconfig, $form) = @_;
1392 # connect to database
1393 my $dbh = $form->dbconnect($myconfig);
1397 my $query = qq|SELECT accno, description, link
1399 WHERE link LIKE '%$module%'
1401 my $sth = $dbh->prepare($query);
1402 $sth->execute || $form->dberror($query);
1404 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1405 foreach my $key (split /:/, $ref->{link}) {
1406 if ($key =~ /$module/) {
1407 push @{ $form->{"${module}_links"}{$key} }, { accno => $ref->{accno},
1408 description => $ref->{description} };
1414 if ($form->{item} ne 'assembly') {
1415 $query = qq|SELECT count(*) FROM vendor|;
1416 $sth = $dbh->prepare($query);
1417 $sth->execute || $form->dberror($query);
1418 my ($count) = $sth->fetchrow_array;
1421 if ($count < $myconfig->{vclimit}) {
1422 $query = qq|SELECT id, name
1425 $sth = $dbh->prepare($query);
1426 $sth->execute || $form->dberror($query);
1428 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1429 push @{ $form->{all_vendor} }, $ref;
1436 # pricegroups, customers
1437 $query = qq|SELECT count(*) FROM customer|;
1438 $sth = $dbh->prepare($query);
1439 $sth->execute || $form->dberror($query);
1440 my ($count) = $sth->fetchrow_array;
1443 if ($count < $myconfig->{vclimit}) {
1444 $query = qq|SELECT id, name
1447 $sth = $dbh->prepare($query);
1448 $sth->execute || $form->dberror($query);
1450 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1451 push @{ $form->{all_customer} }, $ref;
1456 $query = qq|SELECT id, pricegroup
1458 ORDER BY pricegroup|;
1459 $sth = $dbh->prepare($query);
1460 $sth->execute || $form->dberror($query);
1462 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1463 push @{ $form->{all_pricegroup} }, $ref;
1469 $query = qq|SELECT weightunit, curr AS currencies
1471 $sth = $dbh->prepare($query);
1472 $sth->execute || $form->dberror($query);
1474 ($form->{weightunit}, $form->{currencies}) = $sth->fetchrow_array;
1478 $query = qq|SELECT weightunit, current_date, curr AS currencies
1480 $sth = $dbh->prepare($query);
1481 $sth->execute || $form->dberror($query);
1483 ($form->{weightunit}, $form->{priceupdate}, $form->{currencies}) = $sth->fetchrow_array;
1492 sub get_warehouses {
1493 my ($self, $myconfig, $form) = @_;
1495 my $dbh = $form->dbconnect($myconfig);
1497 my $query = qq|SELECT id, description
1500 my $sth = $dbh->prepare($query);
1501 $sth->execute || $form->dberror($query);
1503 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1504 push @{ $form->{all_warehouses} }, $ref;