Tabelle parts: Fremdschlüssel für Konten - bessere Namen
[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::AM;
13 use SL::DBUtils ();
14 use Carp;
15 use Data::Dumper;
16
17 use Rose::Object::MakeMethods::Generic (
18   scalar => [ qw(db_args flat_filter) ],
19   'scalar --get_set_init' => [ qw(models vc all_employees all_businesses) ],
20 );
21
22 __PACKAGE__->run_before(sub { $::auth->assert('delivery_value_report'); });
23
24 my %sort_columns = (
25   reqdate                 => t8('Reqdate'),
26   customer                => t8('Customer'),
27   vendor                  => t8('Vendor'),
28   ordnumber               => t8('Order'),
29   partnumber              => t8('Part Number'),
30   description             => t8('Description'),
31   qty                     => t8('Qty in Order'),
32   unit                    => t8('Unit'),
33   netto_qty               => t8('Net value in Order'),
34   not_shipped_qty         => t8('not shipped'),
35   netto_not_shipped_qty   => t8('Net value without delivery orders'),
36   shipped_qty             => t8('Qty in delivery orders'),
37   netto_shipped_qty       => t8('Net Value in delivery orders'),
38   delivered_qty           => t8('transferred in / out'),
39   netto_delivered_qty     => t8('Net value transferred in / out'),
40   do_closed_qty           => t8('Qty in closed delivery orders'),
41   netto_do_closed_qty     => t8('Net value in closed delivery orders')
42 );
43
44
45
46
47 #
48 # action
49 #
50
51 sub action_list {
52   my ($self) = @_;
53   $self->make_filter_summary;
54   $self->prepare_report;
55
56   my $orderitems = $self->models->get;
57   $self->calc_qtys_price($orderitems);
58   $self->report_generator_list_objects(report => $self->{report}, objects => $orderitems);
59 }
60
61 sub prepare_report {
62   my ($self)      = @_;
63
64   my $vc          = $self->vc;
65   my $report      = SL::ReportGenerator->new(\%::myconfig, $::form);
66   my $csv_option  = $::form->{report_generator_output_format};
67   $self->{report} = $report;
68
69   my @columns     = qw(reqdate customer vendor ordnumber partnumber description unit qty netto_qty
70                        not_shipped_qty netto_not_shipped_qty shipped_qty netto_shipped_qty delivered_qty
71                        netto_delivered_qty do_closed_qty netto_do_closed_qty);
72
73
74   my @sortable    = qw(reqdate customer vendor ordnumber partnumber description);
75
76   # if csv report export no units
77   my $rp_csv_mod  = ($csv_option eq 'CSV') ? 1 : '';
78
79   my %column_defs = (
80     reqdate           => {      sub => sub { $_[0]->reqdate_as_date || $_[0]->order->reqdate_as_date          } },
81     description       => {      sub => sub { $_[0]->description                                               },
82                            obj_link => sub { $self->link_to($_[0]->part)                                      } },
83     partnumber        => {      sub => sub { $_[0]->part->partnumber                                          },
84                            obj_link => sub { $self->link_to($_[0]->part)                                      } },
85     qty               => {      sub => sub { $_[0]->qty_as_number .
86                                              ($rp_csv_mod ? '' : ' ' .  $_[0]->unit)                           } },
87     netto_qty         => {      sub => sub { $::form->format_amount(\%::myconfig,
88                                               ($_[0]->qty * $_[0]->sellprice * (1 - $_[0]->discount) /
89                                                                          ($_[0]->price_factor || 1), 2))       },},
90     unit              => {      sub => sub {  $_[0]->unit                                                      },
91                             visible => $rp_csv_mod                                                             },
92     shipped_qty       => {      sub => sub { $::form->format_amount(\%::myconfig, $_[0]{shipped_qty}, 2) .
93                                              ($rp_csv_mod ? '' : ' ' .  $_[0]->unit)                           } },
94     netto_shipped_qty => {      sub => sub { $::form->format_amount(\%::myconfig, $_[0]{netto_shipped_qty}, 2) },},
95     not_shipped_qty   => {      sub => sub { $::form->format_amount(\%::myconfig, $_[0]->qty - $_[0]{shipped_qty}
96                                                - $_[0]{delivered_qty} - $_[0]{do_closed_qty}, 2) .
97                                              ($rp_csv_mod ? '' : ' ' .  $_[0]->unit)                           } },
98     delivered_qty     => {      sub => sub { $::form->format_amount(\%::myconfig, $_[0]{delivered_qty}, 2) .
99                                              ($rp_csv_mod ? '' : ' ' .  $_[0]->unit)                           } },
100     netto_delivered_qty => {    sub => sub { $::form->format_amount(\%::myconfig, $_[0]{netto_delivered_qty}, 2) },},
101     netto_not_shipped_qty => {  sub => sub { $::form->format_amount(\%::myconfig,(($_[0]->qty -
102                                              $_[0]{shipped_qty} - $_[0]{delivered_qty} - $_[0]{do_closed_qty})
103                                              * ($_[0]->sellprice * (1 - $_[0]->discount) /
104                                                                              ($_[0]->price_factor || 1)), 2))  },},
105     do_closed_qty     => {      sub => sub { $::form->format_amount(\%::myconfig, $_[0]{do_closed_qty}, 2) .
106                                              ($rp_csv_mod ? '' : ' ' .  $_[0]->unit)                           },},
107     netto_do_closed_qty => {    sub => sub { $::form->format_amount(\%::myconfig, $_[0]{netto_do_closed_qty}, 2) },},
108     ordnumber         => {      sub => sub { $_[0]->order->ordnumber                                           },
109                            obj_link => sub { $self->link_to($_[0]->order)                                      } },
110     vendor            => {      sub => sub { $_[0]->order->vendor->name                                        },
111                             visible => $vc eq 'vendor',
112                            obj_link => sub { $self->link_to($_[0]->order->vendor)                              } },
113     customer          => {      sub => sub { $_[0]->order->customer->name                                      },
114                             visible => $vc eq 'customer',
115                            obj_link => sub { $self->link_to($_[0]->order->customer)                            } },
116   );
117
118   $column_defs{$_}->{text} = $sort_columns{$_} for keys %column_defs;
119
120   $report->set_options(
121     std_column_visibility => 1,
122     controller_class      => 'DeliveryValueReport',
123     output_format         => 'HTML',
124     top_info_text         => ($vc eq 'customer') ? t8('Delivery Value Report for currently open sales orders') :
125                                                    t8('Delivery Value Report for currently outstanding purchase orders'),
126     title                 => $::locale->text('Delivery Value Report'),
127     allow_pdf_export      => 1,
128     allow_csv_export      => 1,
129   );
130   $report->set_columns(%column_defs);
131   $report->set_column_order(@columns);
132   $report->set_export_options(qw(list filter vc));
133   $report->set_options_from_form;
134   $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
135   $self->models->finalize; # for filter laundering
136   $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
137   $report->set_options(
138     raw_top_info_text     => $self->render('delivery_value_report/report_top',    { output => 0 }),
139     raw_bottom_info_text  => $self->render('delivery_value_report/report_bottom', { output => 0 }, models => $self->models),
140   );
141 }
142
143
144
145
146 #
147 # filter
148 #
149
150 sub make_filter_summary {
151   my ($self) = @_;
152   my $vc     = $self->vc;
153   my ($business, $employee);
154
155   my $filter = $::form->{filter} || {};
156   my @filter_strings;
157
158   $business = SL::DB::Business->new(id => $filter->{order}{customer}{"business_id"})->load->description if $filter->{order}{customer}{"business_id"};
159   $employee = SL::DB::Employee->new(id => $filter->{order}{employee_id})->load->name if $filter->{order}{employee_id};
160
161   my @filters = (
162     [ $filter->{order}{"ordnumber:substr::ilike"},                    $::locale->text('Number')                                             ],
163     [ $filter->{order}{globalproject}{"projectnumber:substr::ilike"}, $::locale->text('Document Project Number')                            ],
164     [ $filter->{part}{"partnumber:substr::ilike"},                    $::locale->text('Part Number')                                        ],
165     [ $filter->{"description:substr::ilike"},                         $::locale->text('Part Description')                                   ],
166     [ $filter->{"reqdate:date::ge"},                                  $::locale->text('Delivery Date') . " " . $::locale->text('From Date') ],
167     [ $filter->{"reqdate:date::le"},                                  $::locale->text('Delivery Date') . " " . $::locale->text('To Date')   ],
168     [ $filter->{"qty:number"},                                        $::locale->text('Quantity')                                           ],
169     [ $filter->{order}{vendor}{"name:substr::ilike"},                 $::locale->text('Vendor')                                             ],
170     [ $filter->{order}{vendor}{"vendornumber:substr::ilike"},         $::locale->text('Vendor Number')                                      ],
171     [ $filter->{order}{customer}{"name:substr::ilike"},               $::locale->text('Customer')                                           ],
172     [ $filter->{order}{customer}{"customernumber:substr::ilike"},     $::locale->text('Customer Number')                                    ],
173     [ $business,                                                      $::locale->text('Customer type')                                      ],
174     [ $employee,                                                      $::locale->text('Employee')                                           ],
175   );
176
177   # flags for with_object 'part'
178   my %flags = (
179     part     => $::locale->text('Parts'),
180     service  => $::locale->text('Services'),
181     assembly => $::locale->text('Assemblies'),
182   );
183   my @flags = map { $flags{$_} } @{ $filter->{part}{type} || [] };
184
185   for (@flags) {
186     push @filter_strings, $_ if $_;
187   }
188   for (@filters) {
189     push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
190   }
191   $self->{filter_summary} = join ', ', @filter_strings;
192 }
193
194
195
196 #
197 # helpers
198 #
199 sub init_models {
200   my ($self) = @_;
201   my $vc     = $self->vc;
202   SL::Controller::Helper::GetModels->new(
203     controller            => $self,
204     model                 => 'OrderItem',
205     sorted                => {
206       _default              => {
207         by                    => 'reqdate',
208         dir                   => 1,
209       },
210       %sort_columns,
211     },
212     # show only open (sales|purchase) orders
213     query                 => [ 'order.closed' => '0',  "order.${vc}_id" => { gt => 0 },
214                                'order.quotation' => 0                                  ],
215     with_objects          => [ 'order', "order.$vc", 'part' ],
216     additional_url_params => { vc => $vc},
217   )
218 }
219
220 sub init_vc {
221   return $::form->{vc} if ($::form->{vc} eq 'customer' || $::form->{vc} eq 'vendor') || croak "self (DeliveryValueReport) has no vc defined";
222 }
223 sub init_all_employees {
224   return SL::DB::Manager::Employee->get_all_sorted;
225 }
226 sub init_all_businesses {
227   return SL::DB::Manager::Business->get_all_sorted;
228 }
229
230
231 sub link_to {
232   my ($self, $object, %params) = @_;
233
234   return unless $object;
235   my $action = $params{action} || 'edit';
236
237   if ($object->isa('SL::DB::Order')) {
238     my $type   = $object->type;
239     my $vc     = $object->is_sales ? 'customer' : 'vendor';
240     my $id     = $object->id;
241
242     return "oe.pl?action=$action&type=$type&vc=$vc&id=$id";
243   }
244   if ($object->isa('SL::DB::Part')) {
245     my $id     = $object->id;
246     return "ic.pl?action=$action&id=$id";
247   }
248   if ($object->isa('SL::DB::Customer')) {
249     my $id     = $object->id;
250     return "controller.pl?action=CustomerVendor/$action&id=$id&db=customer";
251   }
252 }
253
254
255 sub calc_qtys_price {
256   my ($self, $orderitems) = @_;
257   # using $orderitem->shipped_qty 40 times is far too slow. need to do it manually
258   # also for calc net values
259
260   return unless scalar @$orderitems;
261
262   my %orderitems_by_id = map { $_->id => $_ } @$orderitems;
263
264   my $query = <<SQL;
265     SELECT oi.id, doi.qty, doi.unit, doe.delivered, doe.closed,
266            oi.sellprice, oi.discount, oi.price_factor
267     FROM record_links rl
268     INNER JOIN delivery_order_items doi ON (doi.id = rl.to_id)
269     INNER JOIN orderitems oi            ON (oi.id  = rl.from_id)
270     INNER JOIN delivery_orders doe      ON (doe.id = doi.delivery_order_id)
271     WHERE rl.from_table = 'orderitems'
272       AND rl.to_table   = 'delivery_order_items'
273       AND oi.id IN (@{[ join ', ', ("?")x @$orderitems ]})
274 SQL
275
276   my $result = SL::DBUtils::selectall_hashref_query($::form, $::form->get_standard_dbh, $query, map { $_->id } @$orderitems);
277
278   for my $row (@$result) {
279     my $item = $orderitems_by_id{ $row->{id} };
280     $item->{shipped_qty}   ||= 0;
281     $item->{delivered_qty} ||= 0;
282     $item->{do_closed_qty} ||= 0;
283     $item->{shipped_qty}    += AM->convert_unit($row->{unit} => $item->unit) * $row->{qty}  unless ($row->{delivered} || $row->{closed});
284     $item->{delivered_qty}  += AM->convert_unit($row->{unit} => $item->unit) * $row->{qty}  if ($row->{delivered} && !$row->{closed});
285     $item->{do_closed_qty}  += AM->convert_unit($row->{unit} => $item->unit) * $row->{qty}  if ($row->{closed});
286     $item->{not_shipped_qty} += AM->convert_unit($row->{unit} => $item->unit) * $row->{qty} unless ($row->{delivered});
287
288     my $price_factor = $row->{price_factor} || 1;
289     $item->{netto_shipped_qty}   = $item->{shipped_qty} * $row->{sellprice} * (1 - $row->{discount} ) / $price_factor;
290     $item->{netto_delivered_qty} = $item->{delivered_qty} * $row->{sellprice} * (1 - $row->{discount} ) / $price_factor;
291     $item->{netto_do_closed_qty} = $item->{do_closed_qty} * $row->{sellprice} * (1 - $row->{discount} ) / $price_factor;
292
293   }
294 }
295
296
297
298
299 1;
300
301
302 __END__
303
304 =pod
305
306 =encoding utf8
307
308 =head1 NAME
309
310 SL::Controller::DeliveryValueReport - Controller for Delivery Value Report
311
312 =head2 OVERVIEW
313
314 Controller class for Delivery Value Report
315
316 The goal of the report is to determine which goods and at what costs are already delivered, transfered in
317 relation to open orders, orders in process.
318
319
320 Inherited from the base controller class, this controller implements the Delivery Value Report.
321 Historically the idea derived from a customer extension by thinking: Ah, we just need the Delivery Plan
322 put some more columns in it and then we have a pseudo "Production, Planing, Report" with an additional
323 emphasis on expected future cashflow.
324 Some problems exists with the current report: The definition of not fully delivered sales / purchase order
325 is very (customer) special, in general a simple check on order is open should be a sensible workflow value.
326 Secondly a major database flaw (no persistent ids in order_items) made it impossible to determine the origin
327 of items in terms of linked records. One assumption build in the original DeliveryPlan was that the part_ids
328 are equal. This breaks if the document has the same item on different positions. The next idea was to check
329 for individual item reqdates.
330 After some arguing we decided to implement persistent ids for all items and link them directly via record_links.
331 This linking has been secrectly active since version 3.2, therefore this redesign is possible.
332 Currently the report even works correctly even if the same part has been manually put in another position, renamed or some
333 other metadata for the position has been altered. This is due to the fact that a hidden converted_from_previous_document is
334 used in the position.
335
336 The main intelligence is this query (qty_stocked as comments):
337
338     SELECT oi.id,and more metadata , -- dois.qty as qty_stocked,
339     FROM record_links rl
340     INNER JOIN delivery_order_items doi ON (doi.id = rl.to_id)
341     INNER JOIN orderitems oi            ON (oi.id  = rl.from_id)
342     INNER JOIN delivery_orders doe      ON (doe.id = doi.delivery_order_id)
343     --INNER JOIN delivery_order_items_stock dois ON (doi.id = dois.delivery_order_item_id)
344     WHERE rl.from_table = 'orderitems'
345       AND rl.to_table   = 'delivery_order_items'
346
347 Get all entries which were converted from orderitems to delivery_order_items (WHERE).
348 The persistent id are in rl, therefore we can fetch orderitems and delivery_order_items.
349 The join on delivery_orders (doe) is only needed for the current state of the delivery order (closed, delivered).
350
351 =head1 FUNCTIONS
352
353 =over 2
354
355 =item C<action_list>
356
357 =item C<prepare_report>
358
359 =item C<make_filter_summary>
360
361 =item C<calc_qtys_price>
362
363 =item C<link_to>
364
365 =item C<init_models>
366
367 =item C<init_vc>
368
369 =item C<init_all_employees>
370
371 =item C<init_all_businesses>
372
373 =back
374
375 =head1 TODOS
376
377 Currently no foreign currencies and OrderItems with taxincluded are calculated / supported. The report can be easily extended
378 for the real stocked qty. The report is really easy to implement and customise if your model is focussed straight.
379 For long term maintaineance it would be wise to add more testcases for the conversion from orders to delivery_orders.
380 Right now record_links are tested only from document to document and the convert_invoice method (via task server) has a
381 test case with record_links items included. Furhtermore I personally dislike the calcs in the %columns_def, but for a quick report
382 this is ok, though if we redesign this further, the taxincluded / currency cases should be implemented as well.
383
384
385 =head1 AUTHOR
386
387 Jan Büren E<lt>jan@kivitendo-premium.deE<gt> (based on DeliveryPlan.pm by Sven)
388
389 =cut