From 64a2d7910d7935825f26a3d1512205003cd79e14 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Sven=20Sch=C3=B6ling?= Date: Tue, 7 Jan 2014 17:06:08 +0100 Subject: [PATCH] =?utf8?q?Eigenes=20Recht=20f=C3=BCr=20Lieferplan=20und=20?= =?utf8?q?"sales=5Fall=5Fedit"=20ber=C3=BCcksichtigen?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- SL/Auth.pm | 1 + SL/Controller/DeliveryPlan.pm | 161 ++++++++++--------- locale/de/all | 1 + menus/erp.ini | 2 +- sql/Pg-upgrade2-auth/delivery_plan_rights.pl | 26 +++ 5 files changed, 115 insertions(+), 76 deletions(-) create mode 100644 sql/Pg-upgrade2-auth/delivery_plan_rights.pl diff --git a/SL/Auth.pm b/SL/Auth.pm index 59542d65d..13044e776 100644 --- a/SL/Auth.pm +++ b/SL/Auth.pm @@ -1067,6 +1067,7 @@ sub all_rights_full { ["sales_all_edit", $locale->text("View/edit all employees sales documents")], ["edit_prices", $locale->text("Edit prices and discount (if not used, textfield is ONLY set readonly)")], ["show_ar_transactions", $locale->text("Show AR transactions as part of AR invoice report")], + ["delivery_plan", $locale->text("Show delivery plan")], ["--ap", $locale->text("AP")], ["request_quotation_edit", $locale->text("Create and edit RFQs")], ["purchase_order_edit", $locale->text("Create and edit purchase orders")], diff --git a/SL/Controller/DeliveryPlan.pm b/SL/Controller/DeliveryPlan.pm index ee4a29265..ebf6fa6b2 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,85 @@ 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. 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 + $oe_owner + NOT oe.closed + " ], + ] +} + sub init_models { my ($self) = @_; @@ -207,11 +214,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) = @_; diff --git a/locale/de/all b/locale/de/all index 3fdc2a988..f4f207dc0 100755 --- a/locale/de/all +++ b/locale/de/all @@ -1925,6 +1925,7 @@ $self->{texts} = { 'Show delete button in purchase orders?' => 'Soll der "Löschen"-Knopf bei Lieferantenaufträgen angezeigt werden?', 'Show delete button in sales delivery orders?' => 'Soll der "Löschen"-Knopf bei Verkaufslieferscheinen angezeigt werden?', 'Show delete button in sales orders?' => 'Soll der "Löschen"-Knopf bei Kundenaufträgen angezeigt werden?', + 'Show delivery plan' => 'Lieferplan anzeigen', 'Show details' => 'Detailsanzeige', 'Show details and reports of parts, services, assemblies' => 'Details und Berichte von Waren, Dienstleistungen und Erzeugnissen anzeigen', 'Show fields used for the best before date?' => 'Felder zur Eingabe des Mindesthaltbarkeitsdatums anzeigen?', diff --git a/menus/erp.ini b/menus/erp.ini index a096dc6ef..8de2f0cfd 100644 --- a/menus/erp.ini +++ b/menus/erp.ini @@ -165,7 +165,7 @@ module=dn.pl action=search [AR--Reports--Delivery Plan] -ACCESS=sales_order_edit +ACCESS=delivery_plan module=controller.pl action=DeliveryPlan/list diff --git a/sql/Pg-upgrade2-auth/delivery_plan_rights.pl b/sql/Pg-upgrade2-auth/delivery_plan_rights.pl new file mode 100644 index 000000000..38b31c844 --- /dev/null +++ b/sql/Pg-upgrade2-auth/delivery_plan_rights.pl @@ -0,0 +1,26 @@ +# @tag: delivery_plan_rights +# @description: Setzt das neue Recht den Lieferplan anzuzeigen +# @depends: release_3_0_0 +package SL::DBUpgrade2::delivery_plan_rights; + +use strict; +use utf8; + +use parent qw(SL::DBUpgrade2::Base); + +use SL::DBUtils; + +sub run { + my ($self) = @_; + + my $groups = $main::auth->read_groups(); + + foreach my $group (values %{$groups}) { + $group->{rights}->{delivery_plan_rights} = $group->{rights}->{sales_order_edit}; + $main::auth->save_group($group); + } + + return 1; +} # end run + +1; -- 2.20.1