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);
38 my $query = qq|SELECT p.*,
39 c1.accno AS inventory_accno,
40 c2.accno AS income_accno,
41 c3.accno AS expense_accno,
44 LEFT JOIN chart c1 ON (p.inventory_accno_id = c1.id)
45 LEFT JOIN chart c2 ON (p.income_accno_id = c2.id)
46 LEFT JOIN chart c3 ON (p.expense_accno_id = c3.id)
47 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
48 WHERE p.id = $form->{id}|;
49 my $sth = $dbh->prepare($query);
50 $sth->execute || $form->dberror($query);
51 my $ref = $sth->fetchrow_hashref(NAME_lc);
53 # copy to $form variables
54 map { $form->{$_} = $ref->{$_} } ( keys %{ $ref } );
58 my %oid = ('Pg' => 'a.oid',
63 # part or service item
64 $form->{item} = ($form->{inventory_accno}) ? 'part' : 'service';
65 if ($form->{assembly}) {
66 $form->{item} = 'assembly';
68 # retrieve assembly items
69 $query = qq|SELECT p.id, p.partnumber, p.description,
70 p.sellprice, p.weight, a.qty, a.bom, p.unit,
73 JOIN assembly a ON (a.parts_id = p.id)
74 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
75 WHERE a.id = $form->{id}
76 ORDER BY $oid{$myconfig->{dbdriver}}|;
78 $sth = $dbh->prepare($query);
79 $sth->execute || $form->dberror($query);
81 $form->{assembly_rows} = 0;
82 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
83 $form->{assembly_rows}++;
84 foreach my $key ( keys %{ $ref } ) {
85 $form->{"${key}_$form->{assembly_rows}"} = $ref->{$key};
92 # setup accno hash for <option checked> {amount} is used in create_links
93 $form->{amount}{IC} = $form->{inventory_accno};
94 $form->{amount}{IC_income} = $form->{income_accno};
95 $form->{amount}{IC_sale} = $form->{income_accno};
96 $form->{amount}{IC_expense} = $form->{expense_accno};
97 $form->{amount}{IC_cogs} = $form->{expense_accno};
100 if ($form->{item} ne 'service') {
102 if ($form->{makemodel}) {
103 $query = qq|SELECT name FROM makemodel
104 WHERE parts_id = $form->{id}|;
106 $sth = $dbh->prepare($query);
107 $sth->execute || $form->dberror($query);
110 while (($form->{"make_$i"}, $form->{"model_$i"}) = split(/:/, $sth->fetchrow_array)) {
114 $form->{makemodel_rows} = $i - 1;
119 # now get accno for taxes
120 $query = qq|SELECT c.accno
121 FROM chart c, partstax pt
122 WHERE pt.chart_id = c.id
123 AND pt.parts_id = $form->{id}|;
125 $sth = $dbh->prepare($query);
126 $sth->execute || $form->dberror($query);
128 while (($key) = $sth->fetchrow_array) {
129 $form->{amount}{$key} = $key;
135 $query = qq|SELECT parts_id
137 WHERE parts_id = $form->{id}
141 WHERE parts_id = $form->{id}
145 WHERE parts_id = $form->{id}|;
146 $sth = $dbh->prepare($query);
147 $sth->execute || $form->dberror($query);
149 ($form->{orphaned}) = $sth->fetchrow_array;
150 $form->{orphaned} = !$form->{orphaned};
160 my ($self, $myconfig, $form) = @_;
162 ($form->{inventory_accno}) = split(/--/, $form->{IC});
163 ($form->{expense_accno}) = split(/--/, $form->{IC_expense});
164 ($form->{income_accno}) = split(/--/, $form->{IC_income});
166 # connect to database, turn off AutoCommit
167 my $dbh = $form->dbconnect_noauto($myconfig);
170 # make up a unique handle and store in partnumber field
171 # then retrieve the record based on the unique handle to get the id
172 # replace the partnumber field with the actual variable
173 # add records for makemodel
175 # if there is a $form->{id} then replace the old entry
176 # delete all makemodel entries and add the new ones
179 map { $form->{$_} =~ s/'/''/g } qw(partnumber description notes unit bin);
181 # undo amount formatting
182 map { $form->{$_} = $form->parse_amount($myconfig, $form->{$_}) } qw(rop weight listprice sellprice lastcost stock);
184 # set date to NULL if nothing entered
185 $form->{priceupdate} = ($form->{priceupdate}) ? qq|'$form->{priceupdate}'| : "NULL";
187 $form->{makemodel} = (($form->{make_1}) || ($form->{model_1})) ? 1 : 0;
189 $form->{alternate} = 0;
190 $form->{assembly} = ($form->{item} eq 'assembly') ? 1 : 0;
191 $form->{obsolete} *= 1;
192 $form->{onhand} *= 1;
199 $query = qq|SELECT sellprice, weight
201 WHERE id = $form->{id}|;
202 $sth = $dbh->prepare($query);
203 $sth->execute || $form->dberror($query);
205 my ($sellprice, $weight) = $sth->fetchrow_array;
208 # if item is part of an assembly adjust all assemblies
209 $query = qq|SELECT id, qty
211 WHERE parts_id = $form->{id}|;
212 $sth = $dbh->prepare($query);
213 $sth->execute || $form->dberror($query);
215 while (my ($id, $qty) = $sth->fetchrow_array) {
216 &update_assembly($dbh, $form, $id, $qty * 1, $sellprice * 1, $weight * 1);
221 if ($form->{item} ne 'service') {
222 # delete makemodel records
223 $query = qq|DELETE FROM makemodel
224 WHERE parts_id = $form->{id}|;
225 $dbh->do($query) || $form->dberror($query);
228 if ($form->{item} eq 'assembly') {
229 if ($form->{onhand} != 0) {
230 &adjust_inventory($dbh, $form, $form->{id}, $form->{onhand} * -1);
233 if ($form->{orphaned}) {
234 # delete assembly records
235 $query = qq|DELETE FROM assembly
236 WHERE id = $form->{id}|;
237 $dbh->do($query) || $form->dberror($query);
240 $query = qq|UPDATE assembly
244 $sth = $dbh->prepare($query);
246 for $i (1 .. $form->{assembly_rows} - 1) {
247 $sth->execute(($form->{"bom_$i"}) ? '1' : '0', $form->{id}, $form->{"id_$i"}) || $form->dberror($query);
252 $form->{onhand} += $form->{stock};
256 $query = qq|DELETE FROM partstax
257 WHERE parts_id = $form->{id}|;
258 $dbh->do($query) || $form->dberror($query);
262 $uid .= $form->{login};
264 $query = qq|INSERT INTO parts (partnumber)
266 $dbh->do($query) || $form->dberror($query);
268 $query = qq|SELECT id FROM parts
269 WHERE partnumber = '$uid'|;
270 $sth = $dbh->prepare($query);
271 $sth->execute || $form->dberror($query);
273 ($form->{id}) = $sth->fetchrow_array;
276 $form->{orphaned} = 1;
277 $form->{onhand} = ($form->{stock} * 1) if $form->{item} eq 'assembly';
281 my $partsgroup_id = 0;
282 if ($form->{partsgroup}) {
283 my $partsgroup = lc $form->{partsgroup};
284 $query = qq|SELECT DISTINCT id FROM partsgroup
285 WHERE lower(partsgroup) = '$partsgroup'|;
286 $sth = $dbh->prepare($query);
287 $sth->execute || $form->dberror($query);
289 ($partsgroup_id) = $sth->fetchrow_array;
292 if (!$partsgroup_id) {
293 $query = qq|INSERT INTO partsgroup (partsgroup)
294 VALUES ('$form->{partsgroup}')|;
295 $dbh->do($query) || $form->dberror($query);
297 $query = qq|SELECT id FROM partsgroup
298 WHERE partsgroup = '$form->{partsgroup}'|;
299 $sth = $dbh->prepare($query);
300 $sth->execute || $form->dberror($query);
302 ($partsgroup_id) = $sth->fetchrow_array;
308 $query = qq|UPDATE parts SET
309 partnumber = '$form->{partnumber}',
310 description = '$form->{description}',
311 makemodel = '$form->{makemodel}',
312 alternate = '$form->{alternate}',
313 assembly = '$form->{assembly}',
314 listprice = $form->{listprice},
315 sellprice = $form->{sellprice},
316 lastcost = $form->{lastcost},
317 weight = $form->{weight},
318 priceupdate = $form->{priceupdate},
319 unit = '$form->{unit}',
320 notes = '$form->{notes}',
322 bin = '$form->{bin}',
323 inventory_accno_id = (SELECT id FROM chart
324 WHERE accno = '$form->{inventory_accno}'),
325 income_accno_id = (SELECT id FROM chart
326 WHERE accno = '$form->{income_accno}'),
327 expense_accno_id = (SELECT id FROM chart
328 WHERE accno = '$form->{expense_accno}'),
329 obsolete = '$form->{obsolete}',
330 image = '$form->{image}',
331 drawing = '$form->{drawing}',
332 microfiche = '$form->{microfiche}',
333 partsgroup_id = $partsgroup_id
334 WHERE id = $form->{id}|;
335 $dbh->do($query) || $form->dberror($query);
338 # insert makemodel records
339 unless ($form->{item} eq 'service') {
340 for my $i (1 .. $form->{makemodel_rows}) {
341 # put make and model together
342 if (($form->{"make_$i"}) || ($form->{"model_$i"})) {
343 map { $form->{"${_}_$i"} =~ s/'/''/g } qw(make model);
345 $query = qq|INSERT INTO makemodel (parts_id, name)
347 '$form->{"make_$i"}:$form->{"model_$i"}')|;
348 $dbh->do($query) || $form->dberror($query);
355 foreach $item (split / /, $form->{taxaccounts}) {
356 if ($form->{"IC_tax_$item"}) {
357 $query = qq|INSERT INTO partstax (parts_id, chart_id)
361 WHERE accno = '$item'))|;
362 $dbh->do($query) || $form->dberror($query);
366 # add assembly records
367 if ($form->{item} eq 'assembly') {
369 if ($form->{orphaned}) {
370 for my $i (1 .. $form->{assembly_rows}) {
371 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
373 if ($form->{"qty_$i"} != 0) {
374 $form->{"bom_$i"} *= 1;
375 $query = qq|INSERT INTO assembly (id, parts_id, qty, bom)
376 VALUES ($form->{id}, $form->{"id_$i"},
377 $form->{"qty_$i"}, '$form->{"bom_$i"}')|;
378 $dbh->do($query) || $form->dberror($query);
383 # adjust onhand for the assembly
384 if ($form->{onhand} != 0) {
385 &adjust_inventory($dbh, $form, $form->{id}, $form->{onhand});
392 my $rc = $dbh->commit;
401 sub update_assembly {
402 my ($dbh, $form, $id, $qty, $sellprice, $weight) = @_;
404 my $query = qq|SELECT id, qty
406 WHERE parts_id = $id|;
407 my $sth = $dbh->prepare($query);
408 $sth->execute || $form->dberror($query);
410 while (my ($pid, $aqty) = $sth->fetchrow_array) {
411 &update_assembly($dbh, $form, $pid, $aqty * $qty, $sellprice, $weight);
415 $query = qq|UPDATE parts
416 SET sellprice = sellprice +
417 $qty * ($form->{sellprice} - $sellprice),
419 $qty * ($form->{weight} - $weight)
421 $dbh->do($query) || $form->dberror($query);
427 sub retrieve_assemblies {
428 my ($self, $myconfig, $form) = @_;
430 # connect to database
431 my $dbh = $form->dbconnect($myconfig);
435 if ($form->{partnumber}) {
436 my $partnumber = $form->like(lc $form->{partnumber});
437 $where .= " AND lower(p.partnumber) LIKE '$partnumber'";
440 if ($form->{description}) {
441 my $description = $form->like(lc $form->{description});
442 $where .= " AND lower(p.description) LIKE '$description'";
444 $where .= " AND NOT p.obsolete = '1'";
446 # retrieve assembly items
447 my $query = qq|SELECT p.id, p.partnumber, p.description,
448 p.bin, p.onhand, p.rop,
449 (SELECT sum(p2.inventory_accno_id)
450 FROM parts p2, assembly a
451 WHERE p2.id = a.parts_id
452 AND a.id = p.id) AS inventory
457 my $sth = $dbh->prepare($query);
458 $sth->execute || $form->dberror($query);
460 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
461 push @{ $form->{assembly_items} }, $ref if $ref->{inventory};
470 sub restock_assemblies {
471 my ($self, $myconfig, $form) = @_;
473 # connect to database
474 my $dbh = $form->dbconnect_noauto($myconfig);
476 for my $i (1 .. $form->{rowcount}) {
478 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
480 if ($form->{"qty_$i"} != 0) {
481 &adjust_inventory($dbh, $form, $form->{"id_$i"}, $form->{"qty_$i"});
486 my $rc = $dbh->commit;
494 sub adjust_inventory {
495 my ($dbh, $form, $id, $qty) = @_;
497 my $query = qq|SELECT p.id, p.inventory_accno_id, p.assembly, a.qty
499 JOIN assembly a ON (a.parts_id = p.id)
501 my $sth = $dbh->prepare($query);
502 $sth->execute || $form->dberror($query);
504 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
506 my $allocate = $qty * $ref->{qty};
508 # is it a service item, then loop
509 $ref->{inventory_accno_id} *= 1;
510 next if (($ref->{inventory_accno_id} == 0) && !$ref->{assembly});
512 # adjust parts onhand
513 $form->update_balance($dbh,
523 $form->update_balance($dbh,
533 my ($self, $myconfig, $form) = @_;
535 # connect to database, turn off AutoCommit
536 my $dbh = $form->dbconnect_noauto($myconfig);
538 if ($form->{item} eq 'assembly' && $form->{onhand} != 0) {
539 # adjust onhand for the assembly
540 &adjust_inventory($dbh, $form, $form->{id}, $form->{onhand} * -1);
543 my $query = qq|DELETE FROM parts
544 WHERE id = $form->{id}|;
545 $dbh->do($query) || $form->dberror($query);
547 $query = qq|DELETE FROM partstax
548 WHERE parts_id = $form->{id}|;
549 $dbh->do($query) || $form->dberror($query);
551 # check if it is a part, assembly or service
552 if ($form->{item} eq 'part') {
553 $query = qq|DELETE FROM makemodel
554 WHERE parts_id = $form->{id}|;
555 $dbh->do($query) || $form->dberror($query);
558 if ($form->{item} eq 'assembly') {
559 $query = qq|DELETE FROM assembly
560 WHERE id = $form->{id}|;
561 $dbh->do($query) || $form->dberror($query);
564 if ($form->{item} eq 'alternate') {
565 $query = qq|DELETE FROM alternate
566 WHERE id = $form->{id}|;
567 $dbh->do($query) || $form->dberror($query);
571 my $rc = $dbh->commit;
580 my ($self, $myconfig, $form) = @_;
582 my $i = $form->{assembly_rows};
586 if ($form->{"partnumber_$i"}) {
587 $var = $form->like(lc $form->{"partnumber_$i"});
588 $where .= " AND lower(p.partnumber) LIKE '$var'";
590 if ($form->{"description_$i"}) {
591 $var = $form->like(lc $form->{"description_$i"});
592 $where .= " AND lower(p.description) LIKE '$var'";
594 if ($form->{"partsgroup_$i"}) {
595 $var = $form->like(lc $form->{"partsgroup_$i"});
596 $where .= " AND lower(pg.partsgroup) LIKE '$var'";
600 $where .= " AND NOT p.id = $form->{id}";
604 $where .= " ORDER BY p.partnumber";
606 $where .= " ORDER BY p.description";
609 # connect to database
610 my $dbh = $form->dbconnect($myconfig);
612 my $query = qq|SELECT p.id, p.partnumber, p.description, p.sellprice,
613 p.weight, p.onhand, p.unit,
616 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
618 my $sth = $dbh->prepare($query);
619 $sth->execute || $form->dberror($query);
621 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
622 push @{ $form->{item_list} }, $ref;
632 my ($self, $myconfig, $form) = @_;
637 foreach my $item (qw(partnumber drawing microfiche)) {
638 if ($form->{$item}) {
639 $var = $form->like(lc $form->{$item});
640 $where .= " AND lower(p.$item) LIKE '$var'";
643 # special case for description
644 if ($form->{description}) {
645 unless ($form->{bought} || $form->{sold} || $form->{onorder} || $form->{ordered}) {
646 $var = $form->like(lc $form->{description});
647 $where .= " AND lower(p.description) LIKE '$var'";
651 if ($form->{searchitems} eq 'part') {
652 $where .= " AND p.inventory_accno_id > 0";
654 if ($form->{searchitems} eq 'assembly') {
655 $form->{bought} = "";
656 $where .= " AND p.assembly = '1'";
658 if ($form->{searchitems} eq 'service') {
659 $where .= " AND p.inventory_accno_id IS NULL AND NOT p.assembly = '1'";
660 # irrelevant for services
661 $form->{make} = $form->{model} = "";
664 # items which were never bought, sold or on an order
665 if ($form->{itemstatus} eq 'orphaned') {
666 $form->{onhand} = $form->{short} = 0;
667 $form->{bought} = $form->{sold} = 0;
668 $form->{onorder} = $form->{ordered} = 0;
669 $form->{transdatefrom} = $form->{transdateto} = "";
671 $where .= " AND p.onhand = 0
672 AND p.id NOT IN (SELECT p.id FROM parts p, invoice i
673 WHERE p.id = i.parts_id)
674 AND p.id NOT IN (SELECT p.id FROM parts p, assembly a
675 WHERE p.id = a.parts_id)
676 AND p.id NOT IN (SELECT p.id FROM parts p, orderitems o
677 WHERE p.id = o.parts_id)";
680 if ($form->{itemstatus} eq 'active') {
681 $where .= " AND p.obsolete = '0'";
683 if ($form->{itemstatus} eq 'obsolete') {
684 $where .= " AND p.obsolete = '1'";
685 $form->{onhand} = $form->{short} = 0;
687 if ($form->{itemstatus} eq 'onhand') {
688 $where .= " AND p.onhand > 0";
690 if ($form->{itemstatus} eq 'short') {
691 $where .= " AND p.onhand < 0";
695 $var = $form->like(lc $form->{make}).":%";
696 $where .= " AND p.id IN (SELECT DISTINCT ON (m.parts_id) m.parts_id
697 FROM makemodel m WHERE lower(m.name) LIKE '$var')";
699 if ($form->{model}) {
700 $var = "%:".$form->like($form->{model});
701 $where .= " AND p.id IN (SELECT DISTINCT ON (m.parts_id) m.parts_id
702 FROM makemodel m WHERE lower(m.name) LIKE '$var')";
704 if ($form->{partsgroup}) {
705 $var = $form->like(lc $form->{partsgroup});
706 $where .= " AND lower(pg.partsgroup) LIKE '$var'";
710 # connect to database
711 my $dbh = $form->dbconnect($myconfig);
714 my $sortorder = join ', ', $form->sort_columns(qw(partnumber description bin priceupdate partsgroup));
715 $sortorder = $form->{sort} unless $sortorder;
717 my $query = qq|SELECT p.id, p.partnumber, p.description, p.onhand, p.unit,
718 p.bin, p.sellprice, p.listprice, p.lastcost, p.rop, p.weight,
719 p.priceupdate, p.image, p.drawing, p.microfiche,
722 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
724 ORDER BY $sortorder|;
726 # rebuild query for bought and sold items
727 if ($form->{bought} || $form->{sold} || $form->{onorder} || $form->{ordered}) {
732 if ($form->{bought} || $form->{sold}) {
734 my $invwhere = "$where";
735 $invwhere .= " AND i.assemblyitem = '0'";
736 $invwhere .= " AND a.transdate >= '$form->{transdatefrom}'" if $form->{transdatefrom};
737 $invwhere .= " AND a.transdate <= '$form->{transdateto}'" if $form->{transdateto};
739 if ($form->{description}) {
740 $var = $form->like(lc $form->{description});
741 $invwhere .= " AND lower(i.description) LIKE '$var'";
744 my $flds = qq|p.id, p.partnumber, i.description,
745 i.qty AS onhand, i.unit, p.bin, i.sellprice,
746 p.listprice, p.lastcost, p.rop, p.weight,
747 p.priceupdate, p.image, p.drawing, p.microfiche,
749 a.invnumber, a.ordnumber, i.trans_id|;
751 if ($form->{bought}) {
753 SELECT $flds, 'ir' AS module, '' AS type,
756 JOIN invoice i ON (i.parts_id = p.id)
757 JOIN ap a ON (i.trans_id = a.id)
758 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
766 SELECT $flds, 'is' AS module, '' AS type,
769 JOIN invoice i ON (i.parts_id = p.id)
770 JOIN ar a ON (i.trans_id = a.id)
771 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
778 if ($form->{onorder} || $form->{ordered}) {
779 my $ordwhere = "$where";
780 $ordwhere .= " AND o.closed = '0'" unless $form->{closed};
782 $ordwhere .= " AND o.transdate >= '$form->{transdatefrom}'" if $form->{transdatefrom};
783 $ordwhere .= " AND o.transdate <= '$form->{transdateto}'" if $form->{transdateto};
785 if ($form->{description}) {
786 $var = $form->like(lc $form->{description});
787 $ordwhere .= " AND lower(oi.description) LIKE '$var'";
790 $flds = qq|p.id, p.partnumber, oi.description,
791 oi.qty AS onhand, oi.unit, p.bin, oi.sellprice,
792 p.listprice, p.lastcost, p.rop, p.weight,
793 p.priceupdate, p.image, p.drawing, p.microfiche,
795 '' AS invnumber, o.ordnumber, oi.trans_id|;
797 if ($form->{ordered}) {
799 SELECT $flds, 'oe' AS module, 'sales_order' AS type,
800 (SELECT buy FROM exchangerate ex
801 WHERE ex.curr = o.curr
802 AND ex.transdate = o.transdate) AS exchangerate
804 JOIN orderitems oi ON (oi.parts_id = p.id)
805 JOIN oe o ON (oi.trans_id = o.id)
806 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
808 AND o.customer_id > 0|;
813 if ($form->{onorder}) {
814 $flds = qq|p.id, p.partnumber, oi.description,
815 oi.qty * -1 AS onhand, oi.unit, p.bin, oi.sellprice,
816 p.listprice, p.lastcost, p.rop, p.weight,
817 p.priceupdate, p.image, p.drawing, p.microfiche,
819 '' AS invnumber, o.ordnumber, oi.trans_id|;
822 SELECT $flds, 'oe' AS module, 'purchase_order' AS type,
823 (SELECT sell FROM exchangerate ex
824 WHERE ex.curr = o.curr
825 AND ex.transdate = o.transdate) AS exchangerate
827 JOIN orderitems oi ON (oi.parts_id = p.id)
828 JOIN oe o ON (oi.trans_id = o.id)
829 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
831 AND o.vendor_id > 0|;
837 ORDER BY $sortorder|;
841 my $sth = $dbh->prepare($query);
842 $sth->execute || $form->dberror($query);
844 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
845 push @{ $form->{parts} }, $ref;
851 # include individual items for assemblies
852 if ($form->{searchitems} eq 'assembly' && $form->{bom}) {
853 foreach $item (@{ $form->{parts} }) {
854 push @assemblies, $item;
855 $query = qq|SELECT p.id, p.partnumber, p.description, a.qty AS onhand,
857 p.sellprice, p.listprice, p.lastcost,
858 p.rop, p.weight, p.priceupdate,
859 p.image, p.drawing, p.microfiche
861 JOIN assembly a ON (p.id = a.parts_id)
862 WHERE a.id = $item->{id}|;
864 $sth = $dbh->prepare($query);
865 $sth->execute || $form->dberror($query);
867 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
868 $ref->{assemblyitem} = 1;
869 push @assemblies, $ref;
873 push @assemblies, {id => $item->{id}};
877 # copy assemblies to $form->{parts}
878 @{ $form->{parts} } = @assemblies;
888 my ($self, $module, $myconfig, $form) = @_;
890 # connect to database
891 my $dbh = $form->dbconnect($myconfig);
893 my $query = qq|SELECT accno, description, link
895 WHERE link LIKE '%$module%'
897 my $sth = $dbh->prepare($query);
898 $sth->execute || $form->dberror($query);
900 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
901 foreach my $key (split(/:/, $ref->{link})) {
902 if ($key =~ /$module/) {
903 push @{ $form->{"${module}_links"}{$key} }, { accno => $ref->{accno},
904 description => $ref->{description} };
912 $query = qq|SELECT weightunit
914 $sth = $dbh->prepare($query);
915 $sth->execute || $form->dberror($query);
917 ($form->{weightunit}) = $sth->fetchrow_array;
921 $query = qq|SELECT weightunit, current_date
923 $sth = $dbh->prepare($query);
924 $sth->execute || $form->dberror($query);
926 ($form->{weightunit}, $form->{priceupdate}) = $sth->fetchrow_array;