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