X-Git-Url: http://wagnertech.de/gitweb/gitweb.cgi/mfinanz.git/blobdiff_plain/64a2d7910d7935825f26a3d1512205003cd79e14..4ecb8321edcee5e012028a23db3c8d0f760c2178:/SL/Controller/DeliveryPlan.pm diff --git a/SL/Controller/DeliveryPlan.pm b/SL/Controller/DeliveryPlan.pm index ebf6fa6b2..b740e26c4 100644 --- a/SL/Controller/DeliveryPlan.pm +++ b/SL/Controller/DeliveryPlan.pm @@ -22,9 +22,11 @@ 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'), ); sub action_list { @@ -46,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 = ( @@ -58,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 { $_[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( @@ -125,6 +132,9 @@ sub make_filter_summary { 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, @@ -153,6 +163,7 @@ sub delivery_plan_query { 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 @@ -160,7 +171,7 @@ sub delivery_plan_query { UNION ALL - -- 4. since the join over record_links fails for sales_orders wihtout any delivery order + -- 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 @@ -179,24 +190,47 @@ 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) + 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 - - 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 " ], ] }