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 => [ qw(db_args flat_filter) ],
21 'scalar --get_set_init' => [ qw(models vc all_employees all_businesses) ],
24 __PACKAGE__->run_before(sub { $::auth->assert('delivery_value_report'); });
27 reqdate => t8('Reqdate'),
28 customer => t8('Customer'),
29 vendor => t8('Vendor'),
30 ordnumber => t8('Order'),
31 partnumber => t8('Part Number'),
32 description => t8('Description'),
33 qty => t8('Qty in Order'),
35 netto_qty => t8('Net value in Order'),
36 not_shipped_qty => t8('not shipped'),
37 netto_not_shipped_qty => t8('Net value without delivery orders'),
38 shipped_qty => t8('Qty in delivery orders'),
39 netto_shipped_qty => t8('Net Value in delivery orders'),
40 delivered_qty => t8('transferred in / out'),
41 netto_delivered_qty => t8('Net value transferred in / out'),
42 do_closed_qty => t8('Qty in closed delivery orders'),
43 netto_do_closed_qty => t8('Net value in closed delivery orders'),
55 $self->make_filter_summary;
56 $self->prepare_report;
58 my $orderitems = $self->models->get;
59 $self->calc_qtys_price($orderitems);
60 $self->setup_list_action_bar;
61 $self->report_generator_list_objects(report => $self->{report}, objects => $orderitems);
68 my $report = SL::ReportGenerator->new(\%::myconfig, $::form);
69 my $csv_option = $::form->{report_generator_output_format};
70 $self->{report} = $report;
72 my @columns = qw(reqdate customer vendor ordnumber partnumber description unit qty netto_qty
73 not_shipped_qty netto_not_shipped_qty shipped_qty netto_shipped_qty delivered_qty
74 netto_delivered_qty do_closed_qty netto_do_closed_qty);
77 my @sortable = qw(reqdate customer vendor ordnumber partnumber description);
79 # if csv report export no units
80 my $rp_csv_mod = ($csv_option eq 'CSV') ? 1 : '';
83 reqdate => { sub => sub { $_[0]->reqdate_as_date || $_[0]->order->reqdate_as_date } },
84 description => { sub => sub { $_[0]->description },
85 obj_link => sub { $self->link_to($_[0]->part) } },
86 partnumber => { sub => sub { $_[0]->part->partnumber },
87 obj_link => sub { $self->link_to($_[0]->part) } },
88 qty => { sub => sub { _format_qty($_[0], 'qty', $rp_csv_mod) } },
89 netto_qty => { sub => sub { _format_val($_[0], 'qty') },},
90 unit => { sub => sub { $_[0]->unit },
91 visible => $rp_csv_mod },
92 shipped_qty => { sub => sub { _format_qty($_[0], 'shipped_qty', $rp_csv_mod) } },
93 netto_shipped_qty => { sub => sub { _format_val($_[0], 'shipped_qty') },},
94 not_shipped_qty => { sub => sub { _format_qty($_[0], 'not_shipped_qty', $rp_csv_mod) } },
95 netto_not_shipped_qty => { sub => sub { _format_val($_[0], 'not_shipped_qty') },},
96 delivered_qty => { sub => sub { _format_qty($_[0], 'delivered_qty', $rp_csv_mod) } },
97 netto_delivered_qty => { sub => sub { _format_val($_[0], 'delivered_qty') },},
98 do_closed_qty => { sub => sub { _format_qty($_[0], 'do_closed_qty', $rp_csv_mod) },},
99 netto_do_closed_qty => { sub => sub { _format_val($_[0], 'do_closed_qty') },},
100 ordnumber => { sub => sub { $_[0]->order->ordnumber },
101 obj_link => sub { $self->link_to($_[0]->order) } },
102 vendor => { sub => sub { $_[0]->order->vendor->name },
103 visible => $vc eq 'vendor',
104 obj_link => sub { $self->link_to($_[0]->order->vendor) } },
105 customer => { sub => sub { $_[0]->order->customer->name },
106 visible => $vc eq 'customer',
107 obj_link => sub { $self->link_to($_[0]->order->customer) } },
110 $column_defs{$_}->{text} = $sort_columns{$_} for keys %column_defs;
112 $report->set_options(
113 std_column_visibility => 1,
114 controller_class => 'DeliveryValueReport',
115 output_format => 'HTML',
116 top_info_text => ($vc eq 'customer') ? t8('Delivery Value Report for currently open sales orders') :
117 t8('Delivery Value Report for currently outstanding purchase orders'),
118 title => $::locale->text('Delivery Value Report'),
119 allow_pdf_export => 1,
120 allow_csv_export => 1,
122 $report->set_columns(%column_defs);
123 $report->set_column_order(@columns);
124 $report->set_export_options(qw(list filter vc));
125 $report->set_options_from_form;
126 $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
127 $self->models->finalize; # for filter laundering
128 $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
129 $report->set_options(
130 raw_top_info_text => $self->render('delivery_value_report/report_top', { output => 0 }),
131 raw_bottom_info_text => $self->render('delivery_value_report/report_bottom', { output => 0 }, models => $self->models),
142 sub make_filter_summary {
145 my ($business, $employee);
147 my $filter = $::form->{filter} || {};
150 $business = SL::DB::Business->new(id => $filter->{order}{customer}{"business_id"})->load->description if $filter->{order}{customer}{"business_id"};
151 $employee = SL::DB::Employee->new(id => $filter->{order}{employee_id})->load->name if $filter->{order}{employee_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') ],
169 # flags for with_object 'part'
171 part => $::locale->text('Parts'),
172 service => $::locale->text('Services'),
173 assembly => $::locale->text('Assemblies'),
175 my @flags = map { $flags{$_} } @{ $filter->{part}{type} || [] };
178 push @filter_strings, $_ if $_;
181 push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
183 $self->{filter_summary} = join ', ', @filter_strings;
194 SL::Controller::Helper::GetModels->new(
196 model => 'OrderItem',
204 # show only open (sales|purchase) orders
205 query => [ 'order.closed' => '0', "order.${vc}_id" => { gt => 0 },
206 'order.quotation' => 0 ],
207 with_objects => [ 'order', "order.$vc", 'part' ],
208 additional_url_params => { vc => $vc},
213 return $::form->{vc} if ($::form->{vc} eq 'customer' || $::form->{vc} eq 'vendor') || croak "self (DeliveryValueReport) has no vc defined";
215 sub init_all_employees {
216 return SL::DB::Manager::Employee->get_all_sorted;
218 sub init_all_businesses {
219 return SL::DB::Manager::Business->get_all_sorted;
224 my ($self, $object, %params) = @_;
226 return unless $object;
227 my $action = $params{action} || 'edit';
229 if ($object->isa('SL::DB::Order')) {
230 my $type = $object->type;
231 my $vc = $object->is_sales ? 'customer' : 'vendor';
232 my $id = $object->id;
234 if ($::instance_conf->get_feature_experimental) {
235 return "controller.pl?action=Order/$action&type=$type&id=$id";
237 return "oe.pl?action=$action&type=$type&vc=$vc&id=$id";
240 if ($object->isa('SL::DB::Part')) {
241 my $id = $object->id;
242 return "controller.pl?action=Part/$action&part.id=$id";
244 if ($object->isa('SL::DB::Customer')) {
245 my $id = $object->id;
246 return "controller.pl?action=CustomerVendor/$action&id=$id&db=customer";
251 my ($item, $col, $csv_mod) = @_;
253 $::form->format_amount(\%::myconfig, $item->{$col}, 2) . ($csv_mod ? '' : ' ' . $item->unit)
257 my ($item, $col) = @_;
259 $::form->format_amount(\%::myconfig, $item->{$col} * $item->sellprice * (1 - $item->discount) / ($item->price_factor || 1), 2)
263 sub calc_qtys_price {
264 my ($self, $orderitems) = @_;
266 return unless scalar @$orderitems;
268 SL::Helper::ShippedQty
269 ->new(require_stock_out => 1)
270 ->calculate($orderitems)
273 $_->{delivered_qty} = delete $_->{shipped_qty} for @$orderitems;
275 my $helper = SL::Helper::ShippedQty
276 ->new(require_stock_out => 0, keep_matches => 1)
277 ->calculate($orderitems)
280 for my $item (@$orderitems) {
281 $item->{not_shipped_qty} = $item->qty - $item->{shipped_qty};
282 $item->{do_closed_qty} = 0;
284 my $price_factor = $item->price_factor || 1;
287 if (my @all_doi_ids = uniq map { $_->[1] } @{ $helper->matches }) {
288 my %oi_by_id = map { $_->id => $_ } @$orderitems;
289 my $query = sprintf <<'', join ', ', ("?")x@all_doi_ids;
290 SELECT DISTINCT doi.id, closed FROM delivery_orders
291 LEFT JOIN delivery_order_items doi ON (doi.delivery_order_id = delivery_orders.id)
294 my %doi_is_closed = selectall_as_map($::form, SL::DB->client->dbh, $query, (id => 'closed'), @all_doi_ids);
296 for my $match (@{ $helper->matches }) {
297 next unless $doi_is_closed{$match->[1]};
298 $oi_by_id{$match->[0]}->{do_closed_qty} += $match->[2];
303 sub setup_list_action_bar {
304 my ($self, %params) = @_;
306 for my $bar ($::request->layout->get('actionbar')) {
310 submit => [ '#filter_form', { action => 'DeliveryValueReport/list' } ],
311 accesskey => 'enter',
328 SL::Controller::DeliveryValueReport - Controller for Delivery Value Report
332 Controller class for Delivery Value Report
334 The goal of the report is to determine which goods and at what costs are already delivered, transfered in
335 relation to open orders, orders in process.
338 Inherited from the base controller class, this controller implements the Delivery Value Report.
339 Historically the idea derived from a customer extension by thinking: Ah, we just need the Delivery Plan
340 put some more columns in it and then we have a pseudo "Production, Planing, Report" with an additional
341 emphasis on expected future cashflow.
342 Some problems exists with the current report: The definition of not fully delivered sales / purchase order
343 is very (customer) special, in general a simple check on order is open should be a sensible workflow value.
344 Secondly a major database flaw (no persistent ids in order_items) made it impossible to determine the origin
345 of items in terms of linked records. One assumption build in the original DeliveryPlan was that the part_ids
346 are equal. This breaks if the document has the same item on different positions. The next idea was to check
347 for individual item reqdates.
348 After some arguing we decided to implement persistent ids for all items and link them directly via record_links.
349 This linking has been secrectly active since version 3.2, therefore this redesign is possible.
350 Currently the report even works correctly even if the same part has been manually put in another position, renamed or some
351 other metadata for the position has been altered. This is due to the fact that a hidden converted_from_previous_document is
352 used in the position.
354 The main intelligence is this query (qty_stocked as comments):
356 SELECT oi.id,and more metadata , -- dois.qty as qty_stocked,
358 INNER JOIN delivery_order_items doi ON (doi.id = rl.to_id)
359 INNER JOIN orderitems oi ON (oi.id = rl.from_id)
360 INNER JOIN delivery_orders doe ON (doe.id = doi.delivery_order_id)
361 --INNER JOIN delivery_order_items_stock dois ON (doi.id = dois.delivery_order_item_id)
362 WHERE rl.from_table = 'orderitems'
363 AND rl.to_table = 'delivery_order_items'
365 Get all entries which were converted from orderitems to delivery_order_items (WHERE).
366 The persistent id are in rl, therefore we can fetch orderitems and delivery_order_items.
367 The join on delivery_orders (doe) is only needed for the current state of the delivery order (closed, delivered).
375 =item C<prepare_report>
377 =item C<make_filter_summary>
379 =item C<calc_qtys_price>
387 =item C<init_all_employees>
389 =item C<init_all_businesses>
395 Currently no foreign currencies and OrderItems with taxincluded are calculated / supported. The report can be easily extended
396 for the real stocked qty. The report is really easy to implement and customise if your model is focussed straight.
397 For long term maintaineance it would be wise to add more testcases for the conversion from orders to delivery_orders.
398 Right now record_links are tested only from document to document and the convert_invoice method (via task server) has a
399 test case with record_links items included. Furhtermore I personally dislike the calcs in the %columns_def, but for a quick report
400 this is ok, though if we redesign this further, the taxincluded / currency cases should be implemented as well.
405 Jan Büren E<lt>jan@kivitendo-premium.deE<gt> (based on DeliveryPlan.pm by Sven)