From 86f5b962b5a46ad83fe8d376c82b097f85b97d51 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Sven=20Sch=C3=B6ling?= Date: Tue, 7 Jan 2014 18:42:36 +0100 Subject: [PATCH] =?utf8?q?Neue=20version=20vom=20lieferplan,=20die=20Split?= =?utf8?q?lieferungen=20besser=20unterst=C3=BCtzt.?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit - Stresstest in 2 Livedatenbanken mit je ~100000 Aufträgen liefert einen erwarten Load von 5000 in Postgresql. - In verschiedenen Lieferscheinen gelieferte Positionen sollten jetzt korrekt behandelt werden. --- SL/Controller/DeliveryPlan.pm | 49 +++++++++++++++++++++++++---------- 1 file changed, 35 insertions(+), 14 deletions(-) diff --git a/SL/Controller/DeliveryPlan.pm b/SL/Controller/DeliveryPlan.pm index ebf6fa6b2..fc402fcc6 100644 --- a/SL/Controller/DeliveryPlan.pm +++ b/SL/Controller/DeliveryPlan.pm @@ -179,24 +179,45 @@ sub delivery_plan_query { UNION ALL - -- 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 - oi.trans_id = rl.from_id AND - oi.parts_id NOT IN ( - SELECT doi.parts_id FROM delivery_order_items AS doi WHERE doi.delivery_order_id = rl.to_id + oe.customer_id IS NOT NULL AND + $oe_owner + (oe.quotation = 'f' OR oe.quotation IS NULL) AND NOT oe.closed + ) rl + LEFT JOIN delivery_order_items doi ON (rl.to_id = doi.delivery_order_id) + GROUP BY rl.from_id, doi.parts_id + ) agg ON (agg.oid = oi.trans_id AND agg.parts_id = oi.parts_id) + LEFT JOIN oe ON oe.id = oi.trans_id + WHERE + EXISTS ( + SELECT to_id + FROM record_links rl + WHERE oi.trans_id = rl.from_id AND rl.from_table = 'oe' AND rl.to_table = 'delivery_orders' ) AND - - oe.id = oi.trans_id AND - + coalesce(sum, 0) < oi.qty AND oe.customer_id IS NOT NULL AND - (oe.quotation = 'f' OR oe.quotation IS NULL) AND $oe_owner - NOT oe.closed + (oe.quotation = 'f' OR oe.quotation IS NULL) AND NOT oe.closed " ], ] } -- 2.20.1