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