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
  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('Qty 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 $mode        = 'delivery_value_report';
  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 (better calculation in
  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} - $_[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
 102     netto_not_shipped_qty => {      sub => sub { $::form->format_amount(\%::myconfig,(
 103                                                     ($_[0]->qty - $_[0]{shipped_qty} - $_[0]{delivered_qty} - $_[0]{do_closed_qty})
 104                                                   * ($_[0]->sellprice * (1 - $_[0]->discount) / ($_[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
 108     netto_do_closed_qty => {      sub => sub { $::form->format_amount(\%::myconfig, $_[0]{netto_do_closed_qty}, 2)            },
 109                             visible => $mode eq 'delivery_value_report'                                                     },
 110     ordnumber         => {      sub => sub { $_[0]->order->ordnumber                                                         },
 111                            obj_link => sub { $self->link_to($_[0]->order)                                                    } },
 112     vendor            => {      sub => sub { $_[0]->order->vendor->name                                                      },
 113                             visible => $vc eq 'vendor',
 114                            obj_link => sub { $self->link_to($_[0]->order->vendor)                                            } },
 115     customer          => {      sub => sub { $_[0]->order->customer->name                                                    },
 116                             visible => $vc eq 'customer',
 117                            obj_link => sub { $self->link_to($_[0]->order->customer)                                          } },
 118   );
 119
 120   $column_defs{$_}->{text} = $sort_columns{$_} for keys %column_defs;
 121
 122   $report->set_options(
 123     std_column_visibility => 1,
 124     controller_class      => 'DeliveryValueReport',
 125     output_format         => 'HTML',
 126     top_info_text         => ($vc eq 'customer') ?  (($mode eq 'delivery_plan') ? $::locale->text('Delivery Plan for currently outstanding sales orders') :
 127                                                    $::locale->text('Delivery Value Report for currently open sales orders')) :
 128                                                    ($mode eq 'delivery_value_report') ? $::locale->text('Delivery Value Report for currently outstanding purchase orders') :
 129                                                    $::locale->text('Delivery Plan for currently outstanding purchase orders'),
 130     title                 => ($mode eq 'delivery_plan') ? $::locale->text('Delivery Plan') : $::locale->text('Delivery Value Report'),
 131     allow_pdf_export      => 1,
 132     allow_csv_export      => 1,
 133   );
 134   $report->set_columns(%column_defs);
 135   $report->set_column_order(@columns);
 136   $report->set_export_options(qw(list filter vc));
 137   $report->set_options_from_form;
 138   $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
 139   $self->models->finalize; # for filter laundering
 140   $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
 141   $report->set_options(
 142     raw_top_info_text     => $self->render('delivery_value_report/report_top',    { output => 0 }),
 143     raw_bottom_info_text  => $self->render('delivery_value_report/report_bottom', { output => 0 }, models => $self->models),
 144   );
 145 }
 146
 147
 148
 149
 150 #
 151 # filter
 152 #
 153
 154 sub make_filter_summary {
 155   my ($self) = @_;
 156   my $vc     = $self->vc;
 157   my $mode   = 'delivery_value_report';
 158   my ($business, $employee);
 159
 160   my $filter = $::form->{filter} || {};
 161   my @filter_strings;
 162
 163   $business = SL::DB::Business->new(id => $filter->{order}{customer}{"business_id"})->load->description if $filter->{order}{customer}{"business_id"};
 164   $employee = SL::DB::Employee->new(id => $filter->{order}{employee_id})->load->name if $filter->{order}{employee_id};
 165
 166   my @filters = (
 167     [ $filter->{order}{"ordnumber:substr::ilike"},                    $::locale->text('Number')                                             ],
 168     [ $filter->{order}{globalproject}{"projectnumber:substr::ilike"}, $::locale->text('Document Project Number')                            ],
 169     [ $filter->{part}{"partnumber:substr::ilike"},                    $::locale->text('Part Number')                                        ],
 170     [ $filter->{"description:substr::ilike"},                         $::locale->text('Part Description')                                   ],
 171     [ $filter->{"reqdate:date::ge"},                                  $::locale->text('Delivery Date') . " " . $::locale->text('From Date') ],
 172     [ $filter->{"reqdate:date::le"},                                  $::locale->text('Delivery Date') . " " . $::locale->text('To Date')   ],
 173     [ $filter->{"qty:number"},                                        $::locale->text('Quantity')                                           ],
 174     [ $filter->{order}{vendor}{"name:substr::ilike"},                 $::locale->text('Vendor')                                             ],
 175     [ $filter->{order}{vendor}{"vendornumber:substr::ilike"},         $::locale->text('Vendor Number')                                      ],
 176     [ $filter->{order}{customer}{"name:substr::ilike"},               $::locale->text('Customer')                                           ],
 177     [ $filter->{order}{customer}{"customernumber:substr::ilike"},     $::locale->text('Customer Number')                                    ],
 178     [ $business,                                                      $::locale->text('Customer type')                                      ],
 179     [ $employee,                                                      $::locale->text('Employee')                                           ],
 180   );
 181
 182   my %flags = (
 183     part     => $::locale->text('Parts'),
 184     service  => $::locale->text('Services'),
 185     assembly => $::locale->text('Assemblies'),
 186   );
 187   my @flags = map { $flags{$_} } @{ $filter->{part}{type} || [] };
 188
 189   for (@flags) {
 190     push @filter_strings, $_ if $_;
 191   }
 192   for (@filters) {
 193     push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
 194   }
 195   $self->{filter_summary} = join ', ', @filter_strings;
 196 }
 197
 198
 199
 200 #
 201 # helpers
 202 #
 203 sub init_models {
 204   my ($self) = @_;
 205   my $vc     = $self->vc;
 206   $main::lxdebug->message(0, 'vc hier:' . $vc);
 207   SL::Controller::Helper::GetModels->new(
 208     controller            => $self,
 209     model                 => 'OrderItem',
 210     sorted                => {
 211       _default              => {
 212         by                    => 'reqdate',
 213         dir                   => 1,
 214       },
 215       %sort_columns,
 216     },
 217     # show only open orders
 218     query                 => [ 'order.closed' => '0',  "order.${vc}_id" => { gt => 0 } ],
 219     with_objects          => [ 'order', "order.$vc" ],
 220     additional_url_params => { vc => $vc},
 221   );
 222 }
 223
 224 sub init_vc {
 225   return $::form->{vc} if ($::form->{vc} eq 'customer' || $::form->{vc} eq 'vendor') || croak "self (DeliveryPlan) has no vc defined";
 226 }
 227 sub init_all_employees {
 228   return SL::DB::Manager::Employee->get_all_sorted;
 229 }
 230 sub init_all_businesses {
 231   return SL::DB::Manager::Business->get_all_sorted;
 232 }
 233
 234
 235 sub link_to {
 236   my ($self, $object, %params) = @_;
 237
 238   return unless $object;
 239   my $action = $params{action} || 'edit';
 240
 241   if ($object->isa('SL::DB::Order')) {
 242     my $type   = $object->type;
 243     my $vc     = $object->is_sales ? 'customer' : 'vendor';
 244     my $id     = $object->id;
 245
 246     return "oe.pl?action=$action&type=$type&vc=$vc&id=$id";
 247   }
 248   if ($object->isa('SL::DB::Part')) {
 249     my $id     = $object->id;
 250     return "ic.pl?action=$action&id=$id";
 251   }
 252   if ($object->isa('SL::DB::Customer')) {
 253     my $id     = $object->id;
 254     return "controller.pl?action=CustomerVendor/$action&id=$id&db=customer";
 255   }
 256 }
 257
 258
 259 sub calc_qtys_price {
 260   my ($self, $orderitems) = @_;
 261   # using $orderitem->shipped_qty 40 times is far too slow. need to do it manually
 262   #
 263
 264   return unless scalar @$orderitems;
 265
 266   my %orderitems_by_id = map { $_->id => $_ } @$orderitems;
 267
 268   my $query = <<SQL;
 269     SELECT oi.id, doi.qty, doi.qty, doi.unit, doe.delivered, doe.closed, -- dois.qty as qty_stocked,
 270            oi.sellprice, oi.discount, oi.price_factor
 271     FROM record_links rl
 272     INNER JOIN delivery_order_items doi ON (doi.id = rl.to_id)
 273     INNER JOIN orderitems oi            ON (oi.id  = rl.from_id)
 274     INNER JOIN delivery_orders doe      ON (doe.id = doi.delivery_order_id)
 275     --INNER JOIN delivery_order_items_stock dois ON (doi.id = dois.delivery_order_item_id)
 276     WHERE rl.from_table = 'orderitems'
 277       AND rl.to_table   = 'delivery_order_items'
 278       AND oi.id IN (@{[ join ', ', ("?")x @$orderitems ]})
 279 SQL
 280
 281   my $result = SL::DBUtils::selectall_hashref_query($::form, $::form->get_standard_dbh, $query, map { $_->id } @$orderitems);
 282
 283   for my $row (@$result) {
 284     my $item = $orderitems_by_id{ $row->{id} };
 285     $item->{shipped_qty}   ||= 0;
 286     $item->{delivered_qty} ||= 0;
 287     $item->{do_closed_qty} ||= 0;
 288     $item->{shipped_qty}    += AM->convert_unit($row->{unit} => $item->unit) * $row->{qty} unless ($row->{delivered} || $row->{closed});
 289     $item->{delivered_qty}  += AM->convert_unit($row->{unit} => $item->unit) * $row->{qty} if ($row->{delivered} && !$row->{closed});
 290     $item->{do_closed_qty}  += AM->convert_unit($row->{unit} => $item->unit) * $row->{qty} if ($row->{closed});
 291     #$item->{do_closed_qty}  += AM->convert_unit($row->{unit} => $item->unit) * $row->{qty_stocked} if ($row->{closed});
 292     $item->{not_shipped_qty} += AM->convert_unit($row->{unit} => $item->unit) * $row->{qty} unless ($row->{delivered});
 293     #  my $price_factor = $self->price_factor || 1;
 294     #$self->_delivered_qty;
 295     #$item->{netto_qty}              += $row->{qty} * $row->{sellprice} * (1 - $row->{discount} ) / ($row->{price_factor} || 1);
 296     # no sum
 297     $item->{netto_shipped_qty}      = $item->{shipped_qty} * $row->{sellprice} * (1 - $row->{discount} ) / ($row->{price_factor} || 1);
 298 #    $item->{netto_not_shipped_qty}  += $item->{shipped_qty} * $row->{sellprice} * (1 - $row->{discount} ) / ($row->{price_factor} || 1);
 299     $item->{netto_delivered_qty}    = $item->{delivered_qty} * $row->{sellprice} * (1 - $row->{discount} ) / ($row->{price_factor} || 1);
 300     $item->{netto_do_closed_qty}    = $item->{do_closed_qty} * $row->{sellprice} * (1 - $row->{discount} ) / ($row->{price_factor} || 1);
 301
 302   }
 303 }
 304
 305
 306
 307
 308 1;
 309
 310
 311 __END__
 312
 313 =pod
 314
 315 =encoding utf8
 316
 317 =head1 NAME
 318
 319 SL::Controller::DeliveryValueReport - Controller for Delivery Value Report
 320
 321 =head2 OVERVIEW
 322
 323 Controller class for Delivery Value Report
 324
 325 The goal of the record is to determine which goods and at what costs are already delivered, transfered in
 326 relation to open orders, orders in process.
 327
 328
 329 Inherited from the base controller class, this controller implements the Delivery Value Report.
 330 Historically the idea derived from a customer extension by thinking: Ah, we just need the Delivery Plan
 331 put some more columns in it and then we have a pseudo "Production, Planing, Report" with an additional
 332 emphasis on expected future cashflow.
 333 Some problems exists with the current report: The definition of not fully delivered sales / purchase order
 334 is very (customer) special, in general a simple check on order is open should be a sensible workflow value.
 335 Secondly a major database flaw (no persistent ids in order_items) made it impossible to determine the origin
 336 of items in terms of linked records. One assumption build in the original DeliveryPlan was that the part_ids
 337 are equal. This breaks if the document has the same item on different positions. The next idea was to check
 338 for individual item reqdates.
 339 After some arguing we decided to implement persistent ids for all items and link them directly via record_links.
 340 This linking has been secrectly active since version 3.2, therefore this redesign is possible.
 341 Currently the report even works correctly even if the same part has been manually put in another position, renamed or some
 342  other metadata for the position has been altered. This is due to the fact that a hidden converted_from_previous_document is
 343 used in the position.
 344
 345   from_table |   id    | to_table             |   id
 346   orderitems |    7    | delivery_order_items |   11
 347
 348 =head1 FUNCTIONS
 349
 350 =over 2
 351
 352 =item C<action_list_sales_delivery_orders>
 353
 354 L