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