- 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;