1 package SL::Controller::DeliveryValueReport;
4 use parent qw(SL::Controller::Base);
9 use SL::Controller::Helper::GetModels;
10 use SL::Controller::Helper::ReportGenerator;
11 use SL::Locale::String;
12 use SL::Helper::ShippedQty;
14 use SL::DBUtils qw(selectall_as_map);
15 use List::MoreUtils qw(uniq);
19 use Rose::Object::MakeMethods::Generic (
20 'scalar --get_set_init' => [ qw(models vc all_employees all_businesses all_partsgroups) ],
23 __PACKAGE__->run_before(sub { $::auth->assert('delivery_value_report'); });
26 reqdate => t8('Reqdate'),
27 customer => t8('Customer'),
28 vendor => t8('Vendor'),
29 ordnumber => t8('Order'),
30 partnumber => t8('Part Number'),
31 description => t8('Description'),
32 qty => t8('Qty in Order'),
34 netto_qty => t8('Net value in Order'),
35 not_shipped_qty => t8('not shipped'),
36 netto_not_shipped_qty => t8('Net value without delivery orders'),
37 shipped_qty => t8('Qty in delivery orders'),
38 netto_shipped_qty => t8('Net Value in delivery orders'),
39 delivered_qty => t8('transferred in / out'),
40 netto_delivered_qty => t8('Net value transferred in / out'),
41 do_closed_qty => t8('Qty in closed delivery orders'),
42 netto_do_closed_qty => t8('Net value in closed delivery orders'),
54 $self->make_filter_summary;
55 $self->prepare_report;
57 my $orderitems = $self->models->get;
58 $self->calc_qtys_price($orderitems);
59 $self->setup_list_action_bar;
60 $self->report_generator_list_objects(report => $self->{report}, objects => $orderitems);
67 my $report = SL::ReportGenerator->new(\%::myconfig, $::form);
68 my $csv_option = $::form->{report_generator_output_format};
69 $self->{report} = $report;
71 my @columns = qw(reqdate customer vendor ordnumber partnumber description unit qty netto_qty
72 not_shipped_qty netto_not_shipped_qty shipped_qty netto_shipped_qty delivered_qty
73 netto_delivered_qty do_closed_qty netto_do_closed_qty);
76 my @sortable = qw(reqdate customer vendor ordnumber partnumber description);
78 # if csv report export no units
79 my $rp_csv_mod = ($csv_option eq 'CSV') ? 1 : '';
82 reqdate => { sub => sub { $_[0]->reqdate_as_date || $_[0]->order->reqdate_as_date } },
83 description => { sub => sub { $_[0]->description },
84 obj_link => sub { $self->link_to($_[0]->part) } },
85 partnumber => { sub => sub { $_[0]->part->partnumber },
86 obj_link => sub { $self->link_to($_[0]->part) } },
87 qty => { sub => sub { _format_qty($_[0], 'qty', $rp_csv_mod) } },
88 netto_qty => { sub => sub { _format_val($_[0], 'qty') },},
89 unit => { sub => sub { $_[0]->unit },
90 visible => $rp_csv_mod },
91 shipped_qty => { sub => sub { _format_qty($_[0], 'shipped_qty', $rp_csv_mod) } },
92 netto_shipped_qty => { sub => sub { _format_val($_[0], 'shipped_qty') },},
93 not_shipped_qty => { sub => sub { _format_qty($_[0], 'not_shipped_qty', $rp_csv_mod) } },
94 netto_not_shipped_qty => { sub => sub { _format_val($_[0], 'not_shipped_qty') },},
95 delivered_qty => { sub => sub { _format_qty($_[0], 'delivered_qty', $rp_csv_mod) } },
96 netto_delivered_qty => { sub => sub { _format_val($_[0], 'delivered_qty') },},
97 do_closed_qty => { sub => sub { _format_qty($_[0], 'do_closed_qty', $rp_csv_mod) },},
98 netto_do_closed_qty => { sub => sub { _format_val($_[0], 'do_closed_qty') },},
99 ordnumber => { sub => sub { $_[0]->order->ordnumber },
100 obj_link => sub { $self->link_to($_[0]->order) } },
101 vendor => { sub => sub { $_[0]->order->vendor->name },
102 visible => $vc eq 'vendor',
103 obj_link => sub { $self->link_to($_[0]->order->vendor) } },
104 customer => { sub => sub { $_[0]->order->customer->name },
105 visible => $vc eq 'customer',
106 obj_link => sub { $self->link_to($_[0]->order->customer) } },
109 $column_defs{$_}->{text} = $sort_columns{$_} for keys %column_defs;
111 $report->set_options(
112 std_column_visibility => 1,
113 controller_class => 'DeliveryValueReport',
114 output_format => 'HTML',
115 top_info_text => ($vc eq 'customer') ? t8('Delivery Value Report for currently open sales orders') :
116 t8('Delivery Value Report for currently outstanding purchase orders'),
117 title => $::locale->text('Delivery Value Report'),
118 allow_pdf_export => 1,
119 allow_csv_export => 1,
121 $report->set_columns(%column_defs);
122 $report->set_column_order(@columns);
123 $report->set_export_options(qw(list filter vc));
124 $report->set_options_from_form;
125 $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
126 $self->models->finalize; # for filter laundering
127 $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
128 $report->set_options(
129 raw_top_info_text => $self->render('delivery_value_report/report_top', { output => 0 }),
130 raw_bottom_info_text => $self->render('delivery_value_report/report_bottom', { output => 0 }, models => $self->models),
141 sub make_filter_summary {
144 my ($business, $employee, $partsgroup);
146 my $filter = $::form->{filter} || {};
149 $business = SL::DB::Business->new(id => $filter->{order}{customer}{"business_id"})->load->description if $filter->{order}{customer}{"business_id"};
150 $employee = SL::DB::Employee->new(id => $filter->{order}{employee_id})->load->name if $filter->{order}{employee_id};
151 $partsgroup = SL::DB::PartsGroup->new(id => $filter->{part}{partsgroup_id})->load->partsgroup if $filter->{part}{partsgroup_id};
154 [ $filter->{order}{"ordnumber:substr::ilike"}, $::locale->text('Number') ],
155 [ $filter->{order}{globalproject}{"projectnumber:substr::ilike"}, $::locale->text('Document Project Number') ],
156 [ $filter->{part}{"partnumber:substr::ilike"}, $::locale->text('Part Number') ],
157 [ $filter->{"description:substr::ilike"}, $::locale->text('Part Description') ],
158 [ $filter->{"reqdate:date::ge"}, $::locale->text('Delivery Date') . " " . $::locale->text('From Date') ],
159 [ $filter->{"reqdate:date::le"}, $::locale->text('Delivery Date') . " " . $::locale->text('To Date') ],
160 [ $filter->{"qty:number"}, $::locale->text('Quantity') ],
161 [ $filter->{order}{vendor}{"name:substr::ilike"}, $::locale->text('Vendor') ],
162 [ $filter->{order}{vendor}{"vendornumber:substr::ilike"}, $::locale->text('Vendor Number') ],
163 [ $filter->{order}{customer}{"name:substr::ilike"}, $::locale->text('Customer') ],
164 [ $filter->{order}{customer}{"customernumber:substr::ilike"}, $::locale->text('Customer Number') ],
165 [ $business, $::locale->text('Customer type') ],
166 [ $employee, $::locale->text('Employee') ],
167 [ $partsgroup, $::locale->text('Partsgroup') ],
170 # flags for with_object 'part'
172 part => $::locale->text('Parts'),
173 service => $::locale->text('Services'),
174 assembly => $::locale->text('Assemblies'),
176 my @flags = map { $flags{$_} } @{ $filter->{part}{type} || [] };
179 push @filter_strings, $_ if $_;
182 push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
184 $self->{filter_summary} = join ', ', @filter_strings;
195 SL::Controller::Helper::GetModels->new(
197 model => 'OrderItem',
205 # show only open (sales|purchase) orders
206 query => [ 'order.closed' => '0', "order.${vc}_id" => { gt => 0 },
207 'order.quotation' => 0 ],
208 with_objects => [ 'order', "order.$vc", 'part' ],
209 additional_url_params => { vc => $vc},
214 return $::form->{vc} if ($::form->{vc} eq 'customer' || $::form->{vc} eq 'vendor') || croak "self (DeliveryValueReport) has no vc defined";
216 sub init_all_employees {
217 return SL::DB::Manager::Employee->get_all_sorted;
219 sub init_all_businesses {
220 return SL::DB::Manager::Business->get_all_sorted;
222 sub init_all_partsgroups {
223 return SL::DB::Manager::PartsGroup->get_all_sorted;
228 my ($self, $object, %params) = @_;
230 return unless $object;
231 my $action = $params{action} || 'edit';
233 if ($object->isa('SL::DB::Order')) {
234 my $type = $object->type;
235 my $vc = $object->is_sales ? 'customer' : 'vendor';
236 my $id = $object->id;
238 if ($::instance_conf->get_feature_experimental_order) {
239 return "controller.pl?action=Order/$action&type=$type&id=$id";
241 return "oe.pl?action=$action&type=$type&vc=$vc&id=$id";
244 if ($object->isa('SL::DB::Part')) {
245 my $id = $object->id;
246 return "controller.pl?action=Part/$action&part.id=$id";
248 if ($object->isa('SL::DB::Customer')) {
249 my $id = $object->id;
250 return "controller.pl?action=CustomerVendor/$action&id=$id&db=customer";
255 my ($item, $col, $csv_mod) = @_;
257 $::form->format_amount(\%::myconfig, $item->{$col}, 2) . ($csv_mod ? '' : ' ' . $item->unit)
261 my ($item, $col) = @_;
263 $::form->format_amount(\%::myconfig, $item->{$col} * $item->sellprice * (1 - $item->discount) / ($item->price_factor || 1), 2)
267 sub calc_qtys_price {
268 my ($self, $orderitems) = @_;
270 return unless scalar @$orderitems;
272 SL::Helper::ShippedQty
273 ->new(require_stock_out => 1)
274 ->calculate($orderitems)
277 $_->{delivered_qty} = delete $_->{shipped_qty} for @$orderitems;
279 my $helper = SL::Helper::ShippedQty
280 ->new(require_stock_out => 0, keep_matches => 1)
281 ->calculate($orderitems)
284 for my $item (@$orderitems) {
285 $item->{not_shipped_qty} = $item->qty - $item->{shipped_qty};
286 $item->{do_closed_qty} = 0;
288 my $price_factor = $item->price_factor || 1;
291 if (my @all_doi_ids = uniq map { $_->[1] } @{ $helper->matches }) {
292 my %oi_by_id = map { $_->id => $_ } @$orderitems;
293 my $query = sprintf <<'', join ', ', ("?")x@all_doi_ids;
294 SELECT DISTINCT doi.id, closed FROM delivery_orders
295 LEFT JOIN delivery_order_items doi ON (doi.delivery_order_id = delivery_orders.id)
298 my %doi_is_closed = selectall_as_map($::form, SL::DB->client->dbh, $query, (id => 'closed'), @all_doi_ids);
300 for my $match (@{ $helper->matches }) {
301 next unless $doi_is_closed{$match->[1]};
302 $oi_by_id{$match->[0]}->{do_closed_qty} += $match->[2];
307 sub setup_list_action_bar {
308 my ($self, %params) = @_;
310 for my $bar ($::request->layout->get('actionbar')) {
314 submit => [ '#filter_form', { action => 'DeliveryValueReport/list' } ],
315 accesskey => 'enter',
332 SL::Controller::DeliveryValueReport - Controller for Delivery Value Report
336 Controller class for Delivery Value Report
338 The goal of the report is to determine which goods and at what costs are already delivered, transfered in
339 relation to open orders, orders in process.
342 Inherited from the base controller class, this controller implements the Delivery Value Report.
343 Historically the idea derived from a customer extension by thinking: Ah, we just need the Delivery Plan
344 put some more columns in it and then we have a pseudo "Production, Planing, Report" with an additional
345 emphasis on expected future cashflow.
346 Some problems exists with the current report: The definition of not fully delivered sales / purchase order
347 is very (customer) special, in general a simple check on order is open should be a sensible workflow value.
348 Secondly a major database flaw (no persistent ids in order_items) made it impossible to determine the origin
349 of items in terms of linked records. One assumption build in the original DeliveryPlan was that the part_ids
350 are equal. This breaks if the document has the same item on different positions. The next idea was to check
351 for individual item reqdates.
352 After some arguing we decided to implement persistent ids for all items and link them directly via record_links.
353 This linking has been secrectly active since version 3.2, therefore this redesign is possible.
354 Currently the report even works correctly even if the same part has been manually put in another position, renamed or some
355 other metadata for the position has been altered. This is due to the fact that a hidden converted_from_previous_document is
356 used in the position.
358 The main intelligence is this query (qty_stocked as comments):
360 SELECT oi.id,and more metadata , -- dois.qty as qty_stocked,
362 INNER JOIN delivery_order_items doi ON (doi.id = rl.to_id)
363 INNER JOIN orderitems oi ON (oi.id = rl.from_id)
364 INNER JOIN delivery_orders doe ON (doe.id = doi.delivery_order_id)
365 --INNER JOIN delivery_order_items_stock dois ON (doi.id = dois.delivery_order_item_id)
366 WHERE rl.from_table = 'orderitems'
367 AND rl.to_table = 'delivery_order_items'
369 Get all entries which were converted from orderitems to delivery_order_items (WHERE).
370 The persistent id are in rl, therefore we can fetch orderitems and delivery_order_items.
371 The join on delivery_orders (doe) is only needed for the current state of the delivery order (closed, delivered).
379 =item C<prepare_report>
381 =item C<make_filter_summary>
383 =item C<calc_qtys_price>
391 =item C<init_all_employees>
393 =item C<init_all_businesses>
399 Currently no foreign currencies and OrderItems with taxincluded are calculated / supported. The report can be easily extended
400 for the real stocked qty. The report is really easy to implement and customise if your model is focussed straight.
401 For long term maintaineance it would be wise to add more testcases for the conversion from orders to delivery_orders.
402 Right now record_links are tested only from document to document and the convert_invoice method (via task server) has a
403 test case with record_links items included. Furhtermore I personally dislike the calcs in the %columns_def, but for a quick report
404 this is ok, though if we redesign this further, the taxincluded / currency cases should be implemented as well.
409 Jan Büren E<lt>jan@kivitendo-premium.deE<gt> (based on DeliveryPlan.pm by Sven)