X-Git-Url: http://wagnertech.de/gitweb/gitweb.cgi/mfinanz.git/blobdiff_plain/7dfa1a54b755208936400e4dc9a890cadc983c1e..4ecb8321edcee5e012028a23db3c8d0f760c2178:/SL/Controller/DeliveryPlan.pm diff --git a/SL/Controller/DeliveryPlan.pm b/SL/Controller/DeliveryPlan.pm index da114d84a..b740e26c4 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'), @@ -22,83 +22,13 @@ my %sort_columns = ( partnumber => t8('Part Number'), qty => t8('Qty'), shipped_qty => t8('shipped'), + delivered_qty => t8('transferred out'), not_shipped_qty => t8('not shipped'), ordnumber => t8('Order'), customer => t8('Customer'), + value_of_goods => t8('Value of goods'), ); -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) = @_; @@ -118,7 +48,7 @@ sub prepare_report { my $report = SL::ReportGenerator->new(\%::myconfig, $::form); $self->{report} = $report; - my @columns = qw(reqdate customer ordnumber partnumber description qty shipped_qty not_shipped_qty); + my @columns = qw(reqdate customer ordnumber partnumber description qty shipped_qty not_shipped_qty delivered_qty value_of_goods); my @sortable = qw(reqdate customer ordnumber partnumber description); my %column_defs = ( @@ -130,12 +60,17 @@ sub prepare_report { qty => { sub => sub { $_[0]->qty_as_number . ' ' . $_[0]->unit } }, shipped_qty => { sub => sub { $::form->format_amount(\%::myconfig, $_[0]->shipped_qty, 2) . ' ' . $_[0]->unit } }, not_shipped_qty => { sub => sub { $::form->format_amount(\%::myconfig, $_[0]->qty - $_[0]->shipped_qty, 2) . ' ' . $_[0]->unit } }, + delivered_qty => { sub => sub { $::form->format_amount(\%::myconfig, $_[0]->delivered_qty, 2) . ' ' . $_[0]->unit } }, ordnumber => { sub => sub { $_[0]->order->ordnumber }, obj_link => sub { $self->link_to($_[0]->order) } }, - customer => { sub => sub { return ''; $_[0]->order->customer->name }, + customer => { sub => sub { $_[0]->order->customer->name }, obj_link => sub { $self->link_to($_[0]->order->customer) } }, ); + # add value of goods in report + if ($main::auth->assert('sales_order_edit') && $::instance_conf->get_delivery_plan_show_value_of_goods) { + $column_defs{value_of_goods} = { sub => sub { $::form->format_amount(\%::myconfig, $_[0]->value_of_goods, 2) . ' ' . $_[0]->taxincluded } }; + } $column_defs{$_}->{text} = $sort_columns{$_} for keys %column_defs; $report->set_options( @@ -194,6 +129,112 @@ 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"; + # check delivered state for delivery_orders (transferred out) if enabled + my $filter_delivered = $::instance_conf->get_delivery_plan_calculate_transferred_do ? + "AND (SELECT delivered from delivery_orders where id = doi.delivery_order_id)" : ''; + [ + '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 + $filter_delivered + ) 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 without 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) + WHERE 1 = 1 + $filter_delivered + 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 +248,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) = @_;