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