- -- 5. In case someone deleted a line of the delivery_order there will be a record_link (4 fails)
- -- but there won't be a delivery_order_items to find (3 fails too). Search for orphaned orderitems this way
- SELECT oi.id FROM orderitems AS oi, oe, record_links AS rl
- WHERE
- rl.from_table = 'oe' AND
- rl.to_table = 'delivery_orders' AND
+ -- 5. now for the really nasty cases.
+ -- If someone partially delivered an order in several delivery orders,
+ -- there will be lots of record_links (4 doesn't catch those) but those
+ -- won't have matching part_ids in delivery_order_items, so 1-3 can't
+ -- find anything
+ -- In this case aggreg record_links - delivery_order - delivery_order_items
+ -- slice only oe.id, parts_id and sum of of qty
+ -- left join that onto orderitems to get matching qtys in doi while retaining
+ -- entrys without matches and then throw out those without record_links
+ -- TODO: join this and 1-3 into a general case
+ -- need debug info? uncomment these:
+ SELECT oi.id -- ,oi.trans_id, oi.parts_id, coalesce(sum, 0), agg.parts_id
+ FROM orderitems oi LEFT JOIN (
+ SELECT rl.from_id as oid, doi.parts_id, sum(doi.qty) FROM (
+ SELECT from_id, to_id
+ FROM record_links rl
+ LEFT JOIN oe ON oe.id = from_id
+ WHERE
+ rl.from_table = 'oe' AND
+ rl.to_table = 'delivery_orders' AND