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;