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