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 {
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 = (
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(
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,
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
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
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
" ],
]
}
projects / kivitendo-erp.git / blobdiff