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