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