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