+ #============= build query ================#
+
+ %table_prefix = (
+ %table_prefix,
+ deliverydate => 'apoe.', serialnumber => 'ioi.',
+ transdate => 'apoe.', trans_id => 'ioi.',
+ module => 'apoe.', name => 'cv.',
+ ordnumber => 'apoe.', make => 'mm.',
+ quonumber => 'apoe.', model => 'mm.',
+ invnumber => 'apoe.', partsgroup => 'pg.',
+ factor => 'pfac.',
+ 'SUM(ioi.qty)' => ' ',
+ );
+
+ my %renamed_columns = (
+ 'factor' => 'price_factor',
+ 'SUM(ioi.qty)' => 'soldtotal',
+ );
+
+ map { $table_prefix{$_} = 'ioi.' } qw(description serialnumber qty unit) if $joins_needed{invoice_oi};
+ map { $renamed_columns{$_} = ' AS ' . $renamed_columns{$_} } keys %renamed_columns;
+
+ my $select_clause = join ', ', map { ($table_prefix{$_} || "p.") . $_ . $renamed_columns{$_} } @select_tokens;
+ my $join_clause = join ' ', @joins{ grep $joins_needed{$_}, @join_order };
+ my $where_clause = join ' AND ', map { "($_)" } @where_tokens;
+ my $group_clause = ' GROUP BY ' . join ', ', map { ($table_prefix{$_} || "p.") . $_ } @group_tokens if scalar @group_tokens;
+
+ my $query = qq|SELECT DISTINCT $select_clause FROM parts p $join_clause WHERE $where_clause $group_clause $order_clause $limit_clause|;
+
+ $form->{parts} = selectall_hashref_query($form, $dbh, $query, @bind_vars);
+
+ map { $_->{onhand} *= 1 } @{ $form->{parts} };
+
+## my $where = qq|1 = 1|;
+## my (@values, $var, $flds, $group, $limit);
+##
+## foreach my $item (qw(partnumber drawing microfiche ean pg.partsgroup)) {
+## my $column = $item;
+## $column =~ s/.*\.//; # get rid of table prefixes
+## if ($form->{$column}) {
+## $where .= qq| AND ($item ILIKE ?)|;
+## push(@values, "%$form->{$column}%");
+## }
+## }
+##
+## # special case for description
+## if ($form->{description}
+## && !( $form->{bought} || $form->{sold} || $form->{onorder}
+## || $form->{ordered} || $form->{rfq} || $form->{quoted})) {
+## $where .= qq| AND (p.description ILIKE ?)|;
+## push(@values, "%$form->{description}%");
+## }
+##
+## # special case for serialnumber
+## if ($form->{l_serialnumber} && $form->{serialnumber}) {
+## $where .= qq| AND (serialnumber ILIKE ?)|;
+## push(@values, "%$form->{serialnumber}%");
+## }
+##
+## if ($form->{searchitems} eq 'part') {
+## $where .= qq| AND (p.inventory_accno_id > 0) |;
+## }
+##
+## if ($form->{searchitems} eq 'assembly') {
+## $form->{bought} = "";
+## $where .= qq| AND p.assembly|;
+## }
+##
+## if ($form->{searchitems} eq 'service') {
+## $where .= qq| AND (p.inventory_accno_id IS NULL) AND NOT (p.assembly = '1')|;
+## # irrelevant for services
+## map { $form->{$_} = '' } qw(make model);
+## }
+##
+## # items which were never bought, sold or on an order
+## if ($form->{itemstatus} eq 'orphaned') {
+## map { $form->{$_} = 0 } qw(onhand short bought sold onorder ordered rfq quoted);
+## map { $form->{$_} = '' } qw(transdatefrom transdateto);
+##
+## $where .=
+## qq| AND (p.onhand = 0)
+## AND p.id NOT IN
+## (
+## SELECT DISTINCT parts_id FROM invoice
+## UNION
+## SELECT DISTINCT parts_id FROM assembly
+## UNION
+## SELECT DISTINCT parts_id FROM orderitems
+## )|;
+## }
+##
+## my %status2condition = (
+## active => " AND (p.obsolete = '0')",
+## obsolete => " AND (p.obsolete = '1')",
+## onhand => " AND (p.onhand > 0)",
+## short => " AND (p.onhand < p.rop)",
+## );
+## $where .= $status2condition{$form->{itemstatus}};
+##
+## $form->{onhand} = $form->{short} = 0 if ($form->{itemstatus} eq 'obsolete');
+##
+## my @subcolumns;
+## foreach my $column (qw(make model)) {
+## push @subcolumns, $column if $form->{$column};
+## }
+## if (@subcolumns) {
+## $where .= qq| AND p.id IN (SELECT DISTINCT parts_id FROM makemodel WHERE | . (join " AND ", map { "($_ ILIKE ?)"; } @subcolumns) . ")";
+## push @values, map { '%' . $form->{$_} . '%' } @subcolumns;
+## }
+##
+## if ($form->{l_soldtotal}) {
+## $where .= qq| AND (p.id = i.parts_id) AND (i.qty >= 0)|;
+## $group = qq| GROUP BY p.id, p.partnumber, p.description, p.onhand, p.unit, p.bin, p.sellprice, p.listprice, p.lastcost, p.priceupdate, pg.partsgroup|;
+## }
+##
+## $limit = qq| LIMIT 100| if ($form->{top100});
+##
+## # connect to database
+## my $dbh = $form->dbconnect($myconfig);
+##
+## my @sort_cols = qw(id partnumber description partsgroup bin priceupdate onhand
+## invnumber ordnumber quonumber name drawing microfiche
+## serialnumber soldtotal deliverydate);
+##
+## my $sortorder = "partnumber";
+## $sortorder = $form->{sort} if ($form->{sort} && grep({ $_ eq $form->{sort} } @sort_cols));
+## $sortorder .= " DESC" if ($form->{revers});
+##
+## my $query = "";
+##
+## if ($form->{l_soldtotal}) {
+## $form->{soldtotal} = 'soldtotal';
+## $query =
+## qq|SELECT p.id, p.partnumber, p.description, p.onhand, p.unit,
+## p.bin, p.sellprice, p.listprice, p.lastcost,
+## p.priceupdate, pg.partsgroup,sum(i.qty) AS soldtotal
+## FROM parts p
+## LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id), invoice i
+## WHERE $where
+## $group
+## ORDER BY $sortorder $limit|;
+## } else {
+## $query =
+## qq|SELECT p.id, p.partnumber, p.description, p.onhand, p.unit,
+## p.bin, p.sellprice, p.listprice, p.lastcost, p.rop, p.weight,
+## p.priceupdate, p.image, p.drawing, p.microfiche,
+## pg.partsgroup
+## FROM parts p
+## LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
+## WHERE $where
+## $group
+## ORDER BY $sortorder $limit|;
+## }
+##
+## my @all_values = @values;
+##
+## # rebuild query for bought and sold items
+## if ( $form->{bought}
+## || $form->{sold}
+## || $form->{onorder}
+## || $form->{ordered}
+## || $form->{rfq}
+## || $form->{quoted}) {
+## my $union = "";
+## $query = "";
+## @all_values = ();
+##
+## if ($form->{bought} || $form->{sold}) {
+##
+## my @invvalues = @values;
+## my $invwhere = "$where";
+# $invwhere .= qq| AND i.assemblyitem = '0'|;
+##
+## if ($form->{transdatefrom}) {
+## $invwhere .= qq| AND a.transdate >= ?|;
+## push(@invvalues, $form->{transdatefrom});
+## }
+##
+## if ($form->{transdateto}) {
+## $invwhere .= qq| AND a.transdate <= ?|;
+## push(@invvalues, $form->{transdateto});
+## }
+##
+## if ($form->{description}) {
+## $invwhere .= qq| AND i.description ILIKE ?|;
+## push(@invvalues, '%' . $form->{description} . '%');
+## }
+##
+## $flds =
+## qq|p.id, p.partnumber, i.description, i.serialnumber,
+# i.qty AS onhand, i.unit, p.bin, i.sellprice,
+## p.listprice, p.lastcost, p.rop, p.weight,
+## p.priceupdate, p.image, p.drawing, p.microfiche,
+## pg.partsgroup,
+## a.invnumber, a.ordnumber, a.quonumber, i.trans_id,
+## ct.name, i.deliverydate|;
+##
+## if ($form->{bought}) {
+## $query =
+## qq|SELECT $flds, 'ir' AS module, '' AS type, 1 AS exchangerate
+## FROM invoice i
+## JOIN parts p ON (p.id = i.parts_id)
+## JOIN ap a ON (a.id = i.trans_id)
+## JOIN vendor ct ON (a.vendor_id = ct.id)
+## LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
+## WHERE $invwhere|;
+##
+## $union = qq| UNION |;
+##
+## push(@all_values, @invvalues);
+## }
+##
+## if ($form->{sold}) {
+## $query .=
+## qq|$union
+##
+## SELECT $flds, 'is' AS module, '' AS type, 1 As exchangerate
+## FROM invoice i
+## JOIN parts p ON (p.id = i.parts_id)
+## JOIN ar a ON (a.id = i.trans_id)
+## JOIN customer ct ON (a.customer_id = ct.id)
+## LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
+## WHERE $invwhere|;
+## $union = qq| UNION |;
+##
+## push(@all_values, @invvalues);
+## }
+## }
+##
+## if ($form->{onorder} || $form->{ordered}) {
+## my @ordvalues = @values;
+## my $ordwhere = $where . qq| AND o.quotation = '0'|;
+##
+## if ($form->{transdatefrom}) {
+## $ordwhere .= qq| AND o.transdate >= ?|;
+## push(@ordvalues, $form->{transdatefrom});
+## }
+##
+## if ($form->{transdateto}) {
+## $ordwhere .= qq| AND o.transdate <= ?|;
+## push(@ordvalues, $form->{transdateto});
+## }
+##
+## if ($form->{description}) {
+## $ordwhere .= qq| AND oi.description ILIKE ?|;
+## push(@ordvalues, '%' . $form->{description} . '%');
+## }
+##
+## if ($form->{ordered}) {
+## $query .=
+## qq|$union
+##
+## SELECT p.id, p.partnumber, oi.description, oi.serialnumber AS serialnumber,
+## oi.qty AS onhand, oi.unit, p.bin, oi.sellprice,
+## p.listprice, p.lastcost, p.rop, p.weight,
+## p.priceupdate, p.image, p.drawing, p.microfiche,
+## pg.partsgroup,
+## '' AS invnumber, o.ordnumber, o.quonumber, oi.trans_id,
+## ct.name, NULL AS deliverydate,
+## 'oe' AS module, 'sales_order' AS type,
+## (SELECT buy FROM exchangerate ex
+## WHERE ex.curr = o.curr AND ex.transdate = o.transdate) AS exchangerate
+## FROM orderitems oi
+## JOIN parts p ON (oi.parts_id = p.id)
+## JOIN oe o ON (oi.trans_id = o.id)
+## JOIN customer ct ON (o.customer_id = ct.id)
+## LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
+## WHERE $ordwhere AND (o.customer_id > 0)|;
+## $union = qq| UNION |;
+##
+## push(@all_values, @ordvalues);
+## }
+##
+## if ($form->{onorder}) {
+## $query .=
+## qq|$union
+##
+## SELECT p.id, p.partnumber, oi.description, oi.serialnumber AS serialnumber,
+## oi.qty * -1 AS onhand, oi.unit, p.bin, oi.sellprice,
+## p.listprice, p.lastcost, p.rop, p.weight,
+## p.priceupdate, p.image, p.drawing, p.microfiche,
+## pg.partsgroup,
+## '' AS invnumber, o.ordnumber, o.quonumber, oi.trans_id,
+## ct.name, NULL AS deliverydate,
+## 'oe' AS module, 'purchase_order' AS type,
+## (SELECT sell FROM exchangerate ex
+## WHERE ex.curr = o.curr AND (ex.transdate = o.transdate)) AS exchangerate
+## FROM orderitems oi
+## JOIN parts p ON (oi.parts_id = p.id)
+## JOIN oe o ON (oi.trans_id = o.id)
+## JOIN vendor ct ON (o.vendor_id = ct.id)
+## LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
+## WHERE $ordwhere AND (o.vendor_id > 0)|;
+## $union = qq| UNION |;
+##
+## push(@all_values, @ordvalues);
+## }
+##
+## }
+##
+## if ($form->{rfq} || $form->{quoted}) {
+## my $quowhere = $where . qq| AND o.quotation = '1'|;
+## my @quovalues = @values;
+##
+## if ($form->{transdatefrom}) {
+## $quowhere .= qq| AND o.transdate >= ?|;
+## push(@quovalues, $form->{transdatefrom});
+## }
+##
+## if ($form->{transdateto}) {
+## $quowhere .= qq| AND o.transdate <= ?|;
+## push(@quovalues, $form->{transdateto});
+## }
+##
+## if ($form->{description}) {
+## $quowhere .= qq| AND oi.description ILIKE ?|;
+## push(@quovalues, '%' . $form->{description} . '%');
+## }
+##
+## if ($form->{quoted}) {
+## $query .=
+## qq|$union
+##
+## SELECT
+## p.id, p.partnumber, oi.description, oi.serialnumber AS serialnumber,
+## oi.qty AS onhand, oi.unit, p.bin, oi.sellprice,
+## p.listprice, p.lastcost, p.rop, p.weight,
+## p.priceupdate, p.image, p.drawing, p.microfiche,
+## pg.partsgroup,
+## '' AS invnumber, o.ordnumber, o.quonumber, oi.trans_id,
+## ct.name, NULL AS deliverydate, 'oe' AS module, 'sales_quotation' AS type,
+## (SELECT buy FROM exchangerate ex
+## WHERE (ex.curr = o.curr) AND (ex.transdate = o.transdate)) AS exchangerate
+## FROM orderitems oi
+## JOIN parts p ON (oi.parts_id = p.id)
+## JOIN oe o ON (oi.trans_id = o.id)
+## JOIN customer ct ON (o.customer_id = ct.id)
+## LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
+## WHERE $quowhere
+## AND o.customer_id > 0|;
+## $union = qq| UNION |;
+##
+## push(@all_values, @quovalues);
+## }
+##
+## if ($form->{rfq}) {
+## $query .=
+## qq|$union
+##
+## SELECT p.id, p.partnumber, oi.description, oi.serialnumber AS serialnumber,
+## oi.qty * -1 AS onhand, oi.unit, p.bin, oi.sellprice,
+## p.listprice, p.lastcost, p.rop, p.weight,
+## p.priceupdate, p.image, p.drawing, p.microfiche,
+## pg.partsgroup,
+## '' AS invnumber, o.ordnumber, o.quonumber, oi.trans_id,
+## ct.name, NULL AS deliverydate,
+## 'oe' AS module, 'request_quotation' AS type,
+## (SELECT sell FROM exchangerate ex
+## WHERE (ex.curr = o.curr) AND (ex.transdate = o.transdate)) AS exchangerate
+## FROM orderitems oi
+## JOIN parts p ON (oi.parts_id = p.id)
+## JOIN oe o ON (oi.trans_id = o.id)
+## JOIN vendor ct ON (o.vendor_id = ct.id)
+## LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
+## WHERE $quowhere
+## AND o.vendor_id > 0|;
+##
+## push(@all_values, @quovalues);
+## }
+##
+## }
+## $query .= qq| ORDER BY | . $sortorder;
+##
+## }
+##
+## $form->{parts} = selectall_hashref_query($form, $dbh, $query, @all_values);
+#
+ my @assemblies;
+ # include individual items for assemblies
+ if ($form->{searchitems} eq 'assembly' && $form->{bom}) {
+ $query =
+ qq|SELECT p.id, p.partnumber, p.description, a.qty AS onhand,
+ p.unit, p.bin,
+ p.sellprice, p.listprice, p.lastcost,
+ p.rop, p.weight, p.priceupdate,
+ p.image, p.drawing, p.microfiche
+ FROM parts p, assembly a
+ WHERE (p.id = a.parts_id) AND (a.id = ?)|;
+ $sth = prepare_query($form, $dbh, $query);