projekt kivitendo

--  WITH

--  open_qty as ( SELECT parts_id, sum(oi.qty) as sum

--  FROM orderitems oi

--  LEFT OUTER JOIN oe o ON (oi.trans_id = o.id)

--  WHERE

--  oi.parts_id = ?

--  AND (NOT COALESCE(o.quotation, FALSE))

--  AND (NOT COALESCE(o.closed,    FALSE))

--  AND (NOT COALESCE(o.delivered, FALSE))

--  AND (COALESCE(o.vendor_id, 0) <> 0)

--  GROUP BY oi.parts_id),

--  open_orderitems_ids AS ( SELECT oi.id, parts_id

--  FROM orderitems oi

--  LEFT OUTER JOIN oe o ON (oi.trans_id = o.id)

--  WHERE

--  oi.parts_id = ?

--  AND (NOT COALESCE(o.quotation, FALSE))

--  AND (NOT COALESCE(o.closed,    FALSE))

--  AND (NOT COALESCE(o.delivered, FALSE))

--  AND (COALESCE(o.vendor_id, 0) <> 0)

--  ),

--  delivered_qty AS ( SELECT parts_id, sum(qty) AS sum FROM delivery_order_items

--  WHERE id IN (SELECT to_id FROM record_links WHERE from_id IN (select id FROM open_orderitems_ids) AND from_table = 'orderitems' AND to_table = 'delivery_order_items') AND parts_id = ? GROUP BY parts_id),

--  open_ordered_qty AS ( SELECT dq.parts_id, oq.sum AS ordered_sum, dq.sum, sum(oq.sum - dq.sum) AS open_qty

--  FROM open_qty oq

--  RIGHT JOIN delivered_qty dq ON dq.parts_id = oq.parts_id

--  GROUP BY dq.parts_id, oq.sum, dq.sum)

--  SELECT open_qty FROM open_ordered_qty

with

open_qty as ( SELECT parts_id, sum(oi.qty) as sum

open_orderitems_ids as ( SELECT oi.id, parts_id

delivered_qty as ( select parts_id, sum(qty) as sum from delivery_order_items

where id in (select to_id from record_links where from_id in (select id from open_orderitems_ids) and from_table = 'orderitems' and to_table = 'delivery_order_items') and parts_id = ? group by parts_id),

open_ordered_qty as ( select oq.parts_id, oq.sum as ordered_sum, iif(dq.sum is null,0.00,dq.sum) as sum, sum(iif(oq.sum is null,0.00,oq.sum) - iif(dq.sum is null,0.00,dq.sum)) as open_qty

from open_qty oq

left join delivered_qty dq on dq.parts_id = oq.parts_id

group by oq.parts_id, oq.sum, dq.sum)

select open_qty from open_ordered_qty

  $main::lxdebug->dump(0, 'WH: iOPENQTY', $open_qty);

  return $open_qty;