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