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