X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FController%2FDeliveryPlan.pm;h=fc402fcc6fd75682a87b4c8d64edbe66ab018eca;hb=9cddaf376822b4229457212a27d5d98958f11368;hp=ee4a29265ae1428bb0c298b493b3a355e8532eac;hpb=6f74e4e27fcc35d7fb8955fe0de3b4ae5687df2d;p=kivitendo-erp.git

diff --git a/SL/Controller/DeliveryPlan.pm b/SL/Controller/DeliveryPlan.pm
index ee4a29265..fc402fcc6 100644
--- a/SL/Controller/DeliveryPlan.pm
+++ b/SL/Controller/DeliveryPlan.pm
@@ -11,10 +11,10 @@ use SL::Locale::String;
 
 use Rose::Object::MakeMethods::Generic (
   scalar => [ qw(db_args flat_filter) ],
-  'scalar --get_set_init' => [ qw(models) ],
+  'scalar --get_set_init' => [ qw(models all_edit_right) ],
 );
 
-__PACKAGE__->run_before(sub { $::auth->assert('sales_order_edit'); });
+__PACKAGE__->run_before(sub { $::auth->assert('delivery_plan'); });
 
 my %sort_columns = (
   reqdate           => t8('Reqdate'),
@@ -27,78 +27,6 @@ my %sort_columns = (
   customer          => t8('Customer'),
 );
 
-my $delivery_plan_query = [
-  'order.customer_id' => { gt => 0 },
-  'order.closed' => 0,
-  or => [ 'order.quotation' => 0, 'order.quotation' => undef ],
-
-  # filter by shipped_qty < qty, read from innermost to outermost
-  'id' => [ \"
-    -- 3. resolve the desired information about those
-    SELECT oi.id FROM (
-      -- 2. slice only part, orderitem and both quantities from it
-      SELECT parts_id, trans_id, qty, SUM(doi_qty) AS doi_qty FROM (
-        -- 1. join orderitems and deliverorder items via record_links.
-        --    also add customer data to filter for sales_orders
-        SELECT oi.parts_id, oi.trans_id, oi.id, oi.qty, doi.qty AS doi_qty
-        FROM orderitems oi, oe, record_links rl, delivery_order_items doi
-        WHERE
-          oe.id = oi.trans_id AND
-          oe.customer_id IS NOT NULL AND
-          (oe.quotation = 'f' OR oe.quotation IS NULL) AND
-          NOT oe.closed AND
-          rl.from_id = oe.id AND
-          rl.from_id = oi.trans_id AND
-          oe.id = oi.trans_id AND
-          rl.from_table = 'oe' AND
-          rl.to_table = 'delivery_orders' AND
-          rl.to_id = doi.delivery_order_id AND
-          oi.parts_id = doi.parts_id
-      ) tuples GROUP BY parts_id, trans_id, qty
-    ) partials
-    LEFT JOIN orderitems oi ON partials.parts_id = oi.parts_id AND partials.trans_id = oi.trans_id
-    WHERE oi.qty > doi_qty
-
-    UNION ALL
-
-    -- 4. since the join over record_links fails for sales_orders wihtout any delivery order
-    --    retrieve those without record_links at all
-    SELECT oi.id FROM orderitems oi, oe
-    WHERE
-      oe.id = oi.trans_id AND
-      oe.customer_id IS NOT NULL AND
-      (oe.quotation = 'f' OR oe.quotation IS NULL) AND
-      NOT oe.closed AND
-      oi.trans_id NOT IN (
-        SELECT from_id
-        FROM record_links rl
-        WHERE
-          rl.from_table ='oe' AND
-          rl.to_table = 'delivery_orders'
-      )
-
-    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
-
-      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
-      ) AND
-
-      oe.id = oi.trans_id AND
-
-      oe.customer_id IS NOT NULL AND
-      (oe.quotation = 'f' OR oe.quotation IS NULL) AND
-      NOT oe.closed
-  " ],
-];
-
 sub action_list {
   my ($self) = @_;
 
@@ -194,6 +122,106 @@ sub make_filter_summary {
   $self->{filter_summary} = join ', ', @filter_strings;
 }
 
+sub delivery_plan_query {
+  my $employee_id = SL::DB::Manager::Employee->current->id;
+  my $oe_owner = $_[0]->all_edit_right ? '' : " oe.eployee_id = $employee_id AND";
+  [
+  'order.customer_id' => { gt => 0 },
+  'order.closed' => 0,
+  or => [ 'order.quotation' => 0, 'order.quotation' => undef ],
+
+  # filter by shipped_qty < qty, read from innermost to outermost
+  'id' => [ \"
+    -- 3. resolve the desired information about those
+    SELECT oi.id FROM (
+      -- 2. slice only part, orderitem and both quantities from it
+      SELECT parts_id, trans_id, qty, SUM(doi_qty) AS doi_qty FROM (
+        -- 1. join orderitems and deliverorder items via record_links.
+        --    also add customer data to filter for sales_orders
+        SELECT oi.parts_id, oi.trans_id, oi.id, oi.qty, doi.qty AS doi_qty
+        FROM orderitems oi, oe, record_links rl, delivery_order_items doi
+        WHERE
+          oe.id = oi.trans_id AND
+          oe.customer_id IS NOT NULL AND
+          (oe.quotation = 'f' OR oe.quotation IS NULL) AND
+          NOT oe.closed AND
+          $oe_owner
+          rl.from_id = oe.id AND
+          rl.from_id = oi.trans_id AND
+          oe.id = oi.trans_id AND
+          rl.from_table = 'oe' AND
+          rl.to_table = 'delivery_orders' AND
+          rl.to_id = doi.delivery_order_id AND
+          oi.parts_id = doi.parts_id
+      ) tuples GROUP BY parts_id, trans_id, qty
+    ) partials
+    LEFT JOIN orderitems oi ON partials.parts_id = oi.parts_id AND partials.trans_id = oi.trans_id
+    WHERE oi.qty > doi_qty
+
+    UNION ALL
+
+    -- 4. since the join over record_links fails for sales_orders wihtout any delivery order
+    --    retrieve those without record_links at all
+    SELECT oi.id FROM orderitems oi, oe
+    WHERE
+      oe.id = oi.trans_id AND
+      oe.customer_id IS NOT NULL AND
+      (oe.quotation = 'f' OR oe.quotation IS NULL) AND
+      NOT oe.closed AND
+      $oe_owner
+      oi.trans_id NOT IN (
+        SELECT from_id
+        FROM record_links rl
+        WHERE
+          rl.from_table ='oe' AND
+          rl.to_table = 'delivery_orders'
+      )
+
+    UNION ALL
+
+    -- 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
+
+          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
+      coalesce(sum, 0) < oi.qty AND
+      oe.customer_id IS NOT NULL AND
+      $oe_owner
+      (oe.quotation = 'f' OR oe.quotation IS NULL) AND NOT oe.closed
+  " ],
+  ]
+}
+
 sub init_models {
   my ($self) = @_;
 
@@ -207,11 +235,15 @@ sub init_models {
       },
       %sort_columns,
     },
-    query        => $delivery_plan_query,
+    query        => $self->delivery_plan_query,
     with_objects => [ 'order', 'order.customer', 'part' ],
   );
 }
 
+sub init_all_edit_right {
+  $::auth->assert('sales_all_edit', 1)
+}
+
 sub link_to {
   my ($self, $object, %params) = @_;