marei: cleanup scrlayers
[kivitendo-erp.git] / SL / Controller / DeliveryValueReport.pm
1 package SL::Controller::DeliveryValueReport;
2
3 use strict;
4 use parent qw(SL::Controller::Base);
5
6 use Clone qw(clone);
7 use SL::DB::OrderItem;
8 use SL::DB::Business;
9 use SL::Controller::Helper::GetModels;
10 use SL::Controller::Helper::ReportGenerator;
11 use SL::Locale::String;
12 use SL::Helper::ShippedQty;
13 use SL::AM;
14 use SL::DBUtils qw(selectall_as_map);
15 use List::MoreUtils qw(uniq);
16 use Carp;
17 use Data::Dumper;
18
19 use Rose::Object::MakeMethods::Generic (
20   'scalar --get_set_init' => [ qw(models vc all_employees all_businesses) ],
21 );
22
23 __PACKAGE__->run_before(sub { $::auth->assert('delivery_value_report'); });
24
25 my %sort_columns = (
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'),
33   unit                    => t8('Unit'),
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'),
43 );
44
45
46
47
48 #
49 # action
50 #
51
52 sub action_list {
53   my ($self) = @_;
54   $self->make_filter_summary;
55   $self->prepare_report;
56
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);
61 }
62
63 sub prepare_report {
64   my ($self)      = @_;
65
66   my $vc          = $self->vc;
67   my $report      = SL::ReportGenerator->new(\%::myconfig, $::form);
68   my $csv_option  = $::form->{report_generator_output_format};
69   $self->{report} = $report;
70
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);
74
75
76   my @sortable    = qw(reqdate customer vendor ordnumber partnumber description);
77
78   # if csv report export no units
79   my $rp_csv_mod  = ($csv_option eq 'CSV') ? 1 : '';
80
81   my %column_defs = (
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)                            } },
107   );
108
109   $column_defs{$_}->{text} = $sort_columns{$_} for keys %column_defs;
110
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,
120   );
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),
131   );
132 }
133
134
135
136
137 #
138 # filter
139 #
140
141 sub make_filter_summary {
142   my ($self) = @_;
143   my $vc     = $self->vc;
144   my ($business, $employee);
145
146   my $filter = $::form->{filter} || {};
147   my @filter_strings;
148
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
152   my @filters = (
153     [ $filter->{order}{"ordnumber:substr::ilike"},                    $::locale->text('Number')                                             ],
154     [ $filter->{order}{globalproject}{"projectnumber:substr::ilike"}, $::locale->text('Document Project Number')                            ],
155     [ $filter->{part}{"partnumber:substr::ilike"},                    $::locale->text('Part Number')                                        ],
156     [ $filter->{"description:substr::ilike"},                         $::locale->text('Part Description')                                   ],
157     [ $filter->{"reqdate:date::ge"},                                  $::locale->text('Delivery Date') . " " . $::locale->text('From Date') ],
158     [ $filter->{"reqdate:date::le"},                                  $::locale->text('Delivery Date') . " " . $::locale->text('To Date')   ],
159     [ $filter->{"qty:number"},                                        $::locale->text('Quantity')                                           ],
160     [ $filter->{order}{vendor}{"name:substr::ilike"},                 $::locale->text('Vendor')                                             ],
161     [ $filter->{order}{vendor}{"vendornumber:substr::ilike"},         $::locale->text('Vendor Number')                                      ],
162     [ $filter->{order}{customer}{"name:substr::ilike"},               $::locale->text('Customer')                                           ],
163     [ $filter->{order}{customer}{"customernumber:substr::ilike"},     $::locale->text('Customer Number')                                    ],
164     [ $business,                                                      $::locale->text('Customer type')                                      ],
165     [ $employee,                                                      $::locale->text('Employee')                                           ],
166   );
167
168   # flags for with_object 'part'
169   my %flags = (
170     part     => $::locale->text('Parts'),
171     service  => $::locale->text('Services'),
172     assembly => $::locale->text('Assemblies'),
173   );
174   my @flags = map { $flags{$_} } @{ $filter->{part}{type} || [] };
175
176   for (@flags) {
177     push @filter_strings, $_ if $_;
178   }
179   for (@filters) {
180     push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
181   }
182   $self->{filter_summary} = join ', ', @filter_strings;
183 }
184
185
186
187 #
188 # helpers
189 #
190 sub init_models {
191   my ($self) = @_;
192   my $vc     = $self->vc;
193   SL::Controller::Helper::GetModels->new(
194     controller            => $self,
195     model                 => 'OrderItem',
196     sorted                => {
197       _default              => {
198         by                    => 'reqdate',
199         dir                   => 1,
200       },
201       %sort_columns,
202     },
203     # show only open (sales|purchase) orders
204     query                 => [ 'order.closed' => '0',  "order.${vc}_id" => { gt => 0 },
205                                'order.quotation' => 0                                  ],
206     with_objects          => [ 'order', "order.$vc", 'part' ],
207     additional_url_params => { vc => $vc},
208   )
209 }
210
211 sub init_vc {
212   return $::form->{vc} if ($::form->{vc} eq 'customer' || $::form->{vc} eq 'vendor') || croak "self (DeliveryValueReport) has no vc defined";
213 }
214 sub init_all_employees {
215   return SL::DB::Manager::Employee->get_all_sorted;
216 }
217 sub init_all_businesses {
218   return SL::DB::Manager::Business->get_all_sorted;
219 }
220
221
222 sub link_to {
223   my ($self, $object, %params) = @_;
224
225   return unless $object;
226   my $action = $params{action} || 'edit';
227
228   if ($object->isa('SL::DB::Order')) {
229     my $type   = $object->type;
230     my $vc     = $object->is_sales ? 'customer' : 'vendor';
231     my $id     = $object->id;
232
233     if ($::instance_conf->get_feature_experimental_order) {
234       return "controller.pl?action=Order/$action&type=$type&id=$id";
235     } else {
236       return "oe.pl?action=$action&type=$type&vc=$vc&id=$id";
237     }
238   }
239   if ($object->isa('SL::DB::Part')) {
240     my $id     = $object->id;
241     return "controller.pl?action=Part/$action&part.id=$id";
242   }
243   if ($object->isa('SL::DB::Customer')) {
244     my $id     = $object->id;
245     return "controller.pl?action=CustomerVendor/$action&id=$id&db=customer";
246   }
247 }
248
249 sub _format_qty {
250   my ($item, $col, $csv_mod) = @_;
251
252   $::form->format_amount(\%::myconfig, $item->{$col}, 2) .  ($csv_mod ? '' : ' ' .  $item->unit)
253 }
254
255 sub _format_val {
256   my ($item, $col) = @_;
257
258   $::form->format_amount(\%::myconfig, $item->{$col} * $item->sellprice * (1 - $item->discount) / ($item->price_factor || 1), 2)
259 }
260
261
262 sub calc_qtys_price {
263   my ($self, $orderitems) = @_;
264
265   return unless scalar @$orderitems;
266
267   SL::Helper::ShippedQty
268     ->new(require_stock_out => 1)
269     ->calculate($orderitems)
270     ->write_to_objects;
271
272   $_->{delivered_qty} = delete $_->{shipped_qty} for @$orderitems;
273
274   my $helper = SL::Helper::ShippedQty
275     ->new(require_stock_out => 0, keep_matches => 1)
276     ->calculate($orderitems)
277     ->write_to_objects;
278
279   for my $item (@$orderitems) {
280     $item->{not_shipped_qty} = $item->qty - $item->{shipped_qty};
281     $item->{do_closed_qty}   = 0;
282
283     my $price_factor = $item->price_factor || 1;
284   }
285
286   if (my @all_doi_ids = uniq map { $_->[1] } @{ $helper->matches }) {
287     my %oi_by_id = map { $_->id => $_ } @$orderitems;
288     my $query    = sprintf <<'', join ', ', ("?")x@all_doi_ids;
289       SELECT DISTINCT doi.id, closed FROM delivery_orders
290       LEFT JOIN delivery_order_items doi ON (doi.delivery_order_id = delivery_orders.id)
291       WHERE doi.id IN (%s)
292
293     my %doi_is_closed = selectall_as_map($::form, SL::DB->client->dbh, $query, (id => 'closed'), @all_doi_ids);
294
295     for my $match (@{ $helper->matches }) {
296       next unless $doi_is_closed{$match->[1]};
297       $oi_by_id{$match->[0]}->{do_closed_qty} += $match->[2];
298     }
299   }
300 }
301
302 sub setup_list_action_bar {
303   my ($self, %params) = @_;
304
305   for my $bar ($::request->layout->get('actionbar')) {
306     $bar->add(
307       action => [
308         t8('Update'),
309         submit    => [ '#filter_form', { action => 'DeliveryValueReport/list' } ],
310         accesskey => 'enter',
311       ],
312     );
313   }
314 }
315
316 1;
317
318
319 __END__
320
321 =pod
322
323 =encoding utf8
324
325 =head1 NAME
326
327 SL::Controller::DeliveryValueReport - Controller for Delivery Value Report
328
329 =head2 OVERVIEW
330
331 Controller class for Delivery Value Report
332
333 The goal of the report is to determine which goods and at what costs are already delivered, transfered in
334 relation to open orders, orders in process.
335
336
337 Inherited from the base controller class, this controller implements the Delivery Value Report.
338 Historically the idea derived from a customer extension by thinking: Ah, we just need the Delivery Plan
339 put some more columns in it and then we have a pseudo "Production, Planing, Report" with an additional
340 emphasis on expected future cashflow.
341 Some problems exists with the current report: The definition of not fully delivered sales / purchase order
342 is very (customer) special, in general a simple check on order is open should be a sensible workflow value.
343 Secondly a major database flaw (no persistent ids in order_items) made it impossible to determine the origin
344 of items in terms of linked records. One assumption build in the original DeliveryPlan was that the part_ids
345 are equal. This breaks if the document has the same item on different positions. The next idea was to check
346 for individual item reqdates.
347 After some arguing we decided to implement persistent ids for all items and link them directly via record_links.
348 This linking has been secrectly active since version 3.2, therefore this redesign is possible.
349 Currently the report even works correctly even if the same part has been manually put in another position, renamed or some
350 other metadata for the position has been altered. This is due to the fact that a hidden converted_from_previous_document is
351 used in the position.
352
353 The main intelligence is this query (qty_stocked as comments):
354
355     SELECT oi.id,and more metadata , -- dois.qty as qty_stocked,
356     FROM record_links rl
357     INNER JOIN delivery_order_items doi ON (doi.id = rl.to_id)
358     INNER JOIN orderitems oi            ON (oi.id  = rl.from_id)
359     INNER JOIN delivery_orders doe      ON (doe.id = doi.delivery_order_id)
360     --INNER JOIN delivery_order_items_stock dois ON (doi.id = dois.delivery_order_item_id)
361     WHERE rl.from_table = 'orderitems'
362       AND rl.to_table   = 'delivery_order_items'
363
364 Get all entries which were converted from orderitems to delivery_order_items (WHERE).
365 The persistent id are in rl, therefore we can fetch orderitems and delivery_order_items.
366 The join on delivery_orders (doe) is only needed for the current state of the delivery order (closed, delivered).
367
368 =head1 FUNCTIONS
369
370 =over 2
371
372 =item C<action_list>
373
374 =item C<prepare_report>
375
376 =item C<make_filter_summary>
377
378 =item C<calc_qtys_price>
379
380 =item C<link_to>
381
382 =item C<init_models>
383
384 =item C<init_vc>
385
386 =item C<init_all_employees>
387
388 =item C<init_all_businesses>
389
390 =back
391
392 =head1 TODOS
393
394 Currently no foreign currencies and OrderItems with taxincluded are calculated / supported. The report can be easily extended
395 for the real stocked qty. The report is really easy to implement and customise if your model is focussed straight.
396 For long term maintaineance it would be wise to add more testcases for the conversion from orders to delivery_orders.
397 Right now record_links are tested only from document to document and the convert_invoice method (via task server) has a
398 test case with record_links items included. Furhtermore I personally dislike the calcs in the %columns_def, but for a quick report
399 this is ok, though if we redesign this further, the taxincluded / currency cases should be implemented as well.
400
401
402 =head1 AUTHOR
403
404 Jan Büren E<lt>jan@kivitendo-premium.deE<gt> (based on DeliveryPlan.pm by Sven)
405
406 =cut