projekt kivitendo

package SL::Controller::DeliveryValueReport;

use strict;

use parent qw(SL::Controller::Base);

use Clone qw(clone);

use SL::DB::OrderItem;

use SL::DB::Business;

use SL::Controller::Helper::GetModels;

use SL::Controller::Helper::ReportGenerator;

use SL::Locale::String;

use SL::AM;

use SL::DBUtils ();

use Carp;

use Data::Dumper;

use Rose::Object::MakeMethods::Generic (

  scalar => [ qw(db_args flat_filter) ],

  'scalar --get_set_init' => [ qw(models vc all_employees all_businesses) ],

);

my %sort_columns = (

  reqdate                 => t8('Reqdate'),

  customer                => t8('Customer'),

  vendor                  => t8('Vendor'),

  ordnumber               => t8('Order'),

  partnumber              => t8('Part Number'),

  description             => t8('Description'),

  qty                     => t8('Qty in Order'),

  unit                    => t8('Unit'),

  netto_qty               => t8('Net value in Order'),

  not_shipped_qty         => t8('not shipped'),

  netto_not_shipped_qty   => t8('Net value without delivery orders'),

  shipped_qty             => t8('Qty in delivery orders'),

  netto_shipped_qty       => t8('Net Value in delivery orders'),

  delivered_qty           => t8('transferred in / out'),

  netto_delivered_qty     => t8('Net value transferred in / out'),

  do_closed_qty           => t8('Qty in closed delivery orders'),

  netto_do_closed_qty     => t8('Qty in closed delivery orders')

);

#

# action

#

sub action_list {

  my ($self) = @_;

  $self->make_filter_summary;

  $self->prepare_report;

  my $orderitems = $self->models->get;

  $self->calc_qtys_price($orderitems);

  $self->report_generator_list_objects(report => $self->{report}, objects => $orderitems);

}

sub prepare_report {

  my ($self)      = @_;

  my $vc          = $self->vc;

  my $mode        = 'delivery_value_report';

  my $report      = SL::ReportGenerator->new(\%::myconfig, $::form);

  my $csv_option  = $::form->{report_generator_output_format};

  $self->{report} = $report;

  my @columns     = qw(reqdate customer vendor ordnumber partnumber description unit qty netto_qty

                       not_shipped_qty netto_not_shipped_qty shipped_qty netto_shipped_qty delivered_qty

                       netto_delivered_qty do_closed_qty netto_do_closed_qty);

  my @sortable    = qw(reqdate customer vendor ordnumber partnumber description);

  # if csv report export no units (better calculation in

  my $rp_csv_mod  = ($csv_option eq 'CSV') ? 1 : '';

  my %column_defs = (

    reqdate           => {      sub => sub { $_[0]->reqdate_as_date || $_[0]->order->reqdate_as_date     } },

    description       => {      sub => sub { $_[0]->description                                          },

                           obj_link => sub { $self->link_to($_[0]->part)                                 } },

    partnumber        => {      sub => sub { $_[0]->part->partnumber                                     },

                           obj_link => sub { $self->link_to($_[0]->part)                                 } },

    qty               => {      sub => sub { $_[0]->qty_as_number .

                                             ($rp_csv_mod ? '' : ' ' .  $_[0]->unit)                     } },

    netto_qty         => {      sub => sub { $::form->format_amount(\%::myconfig,

                                              ($_[0]->qty * $_[0]->sellprice * (1 - $_[0]->discount) /

                                                                         ($_[0]->price_factor || 1), 2)) },},

    unit              => {      sub => sub {  $_[0]->unit                                                                    },

                            visible => $rp_csv_mod                                                                           },

    shipped_qty       => {      sub => sub { $::form->format_amount(\%::myconfig, $_[0]{shipped_qty}, 2) .

                                             ($rp_csv_mod ? '' : ' ' .  $_[0]->unit)                                         } },

    netto_shipped_qty => {      sub => sub { $::form->format_amount(\%::myconfig, $_[0]{netto_shipped_qty}, 2)             },},

    not_shipped_qty   => {      sub => sub { $::form->format_amount(\%::myconfig, $_[0]->qty - $_[0]{shipped_qty} - $_[0]{delivered_qty} - $_[0]{do_closed_qty}, 2) .

                                             ($rp_csv_mod ? '' : ' ' .  $_[0]->unit)                                         } },

    delivered_qty     => {      sub => sub { $::form->format_amount(\%::myconfig, $_[0]{delivered_qty}, 2) .

                                             ($rp_csv_mod ? '' : ' ' .  $_[0]->unit)                                         } },

    netto_delivered_qty => {      sub => sub { $::form->format_amount(\%::myconfig, $_[0]{netto_delivered_qty}, 2)          },},

    netto_not_shipped_qty => {      sub => sub { $::form->format_amount(\%::myconfig,(

                                                    ($_[0]->qty - $_[0]{shipped_qty} - $_[0]{delivered_qty} - $_[0]{do_closed_qty})

                                                  * ($_[0]->sellprice * (1 - $_[0]->discount) / ($_[0]->price_factor || 1)), 2))  },},

    do_closed_qty     => {      sub => sub { $::form->format_amount(\%::myconfig, $_[0]{do_closed_qty}, 2) .

                                             ($rp_csv_mod ? '' : ' ' .  $_[0]->unit)                                        },},

    netto_do_closed_qty => {      sub => sub { $::form->format_amount(\%::myconfig, $_[0]{netto_do_closed_qty}, 2)            },

                            visible => $mode eq 'delivery_value_report'                                                     },

    ordnumber         => {      sub => sub { $_[0]->order->ordnumber                                                         },

                           obj_link => sub { $self->link_to($_[0]->order)                                                    } },

    vendor            => {      sub => sub { $_[0]->order->vendor->name                                                      },

                            visible => $vc eq 'vendor',

                           obj_link => sub { $self->link_to($_[0]->order->vendor)                                            } },

    customer          => {      sub => sub { $_[0]->order->customer->name                                                    },

                            visible => $vc eq 'customer',

                           obj_link => sub { $self->link_to($_[0]->order->customer)                                          } },

  );

  $column_defs{$_}->{text} = $sort_columns{$_} for keys %column_defs;

  $report->set_options(

    std_column_visibility => 1,

    controller_class      => 'DeliveryValueReport',

    output_format         => 'HTML',

    top_info_text         => ($vc eq 'customer') ?  (($mode eq 'delivery_plan') ? $::locale->text('Delivery Plan for currently outstanding sales orders') :

                                                   $::locale->text('Delivery Value Report for currently open sales orders')) :

                                                   ($mode eq 'delivery_value_report') ? $::locale->text('Delivery Value Report for currently outstanding purchase orders') :

                                                   $::locale->text('Delivery Plan for currently outstanding purchase orders'),

    title                 => ($mode eq 'delivery_plan') ? $::locale->text('Delivery Plan') : $::locale->text('Delivery Value Report'),

    allow_pdf_export      => 1,

    allow_csv_export      => 1,

  );

  $report->set_columns(%column_defs);

  $report->set_column_order(@columns);

  $report->set_export_options(qw(list filter vc));

  $report->set_options_from_form;

  $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;

  $self->models->finalize; # for filter laundering

  $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);

  $report->set_options(

    raw_top_info_text     => $self->render('delivery_value_report/report_top',    { output => 0 }),

    raw_bottom_info_text  => $self->render('delivery_value_report/report_bottom', { output => 0 }, models => $self->models),

  );

}

#

# filter

#

sub make_filter_summary {

  my ($self) = @_;

  my $vc     = $self->vc;

  my $mode   = 'delivery_value_report';

  my ($business, $employee);

  my $filter = $::form->{filter} || {};

  my @filter_strings;

  $business = SL::DB::Business->new(id => $filter->{order}{customer}{"business_id"})->load->description if $filter->{order}{customer}{"business_id"};

  $employee = SL::DB::Employee->new(id => $filter->{order}{employee_id})->load->name if $filter->{order}{employee_id};

  my @filters = (

    [ $filter->{order}{"ordnumber:substr::ilike"},                    $::locale->text('Number')                                             ],

    [ $filter->{order}{globalproject}{"projectnumber:substr::ilike"}, $::locale->text('Document Project Number')                            ],

    [ $filter->{part}{"partnumber:substr::ilike"},                    $::locale->text('Part Number')                                        ],

    [ $filter->{"description:substr::ilike"},                         $::locale->text('Part Description')                                   ],

    [ $filter->{"reqdate:date::ge"},                                  $::locale->text('Delivery Date') . " " . $::locale->text('From Date') ],

    [ $filter->{"reqdate:date::le"},                                  $::locale->text('Delivery Date') . " " . $::locale->text('To Date')   ],

    [ $filter->{"qty:number"},                                        $::locale->text('Quantity')                                           ],

    [ $filter->{order}{vendor}{"name:substr::ilike"},                 $::locale->text('Vendor')                                             ],

    [ $filter->{order}{vendor}{"vendornumber:substr::ilike"},         $::locale->text('Vendor Number')                                      ],

    [ $filter->{order}{customer}{"name:substr::ilike"},               $::locale->text('Customer')                                           ],

    [ $filter->{order}{customer}{"customernumber:substr::ilike"},     $::locale->text('Customer Number')                                    ],

    [ $business,                                                      $::locale->text('Customer type')                                      ],

    [ $employee,                                                      $::locale->text('Employee')                                           ],

  );

  my %flags = (

    part     => $::locale->text('Parts'),

    service  => $::locale->text('Services'),

    assembly => $::locale->text('Assemblies'),

  );

  my @flags = map { $flags{$_} } @{ $filter->{part}{type} || [] };

  for (@flags) {

    push @filter_strings, $_ if $_;

  }

  for (@filters) {

    push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];

  }

  $self->{filter_summary} = join ', ', @filter_strings;

}

#

# helpers

#

sub init_models {

  my ($self) = @_;

  my $vc     = $self->vc;

  $main::lxdebug->message(0, 'vc hier:' . $vc);

  SL::Controller::Helper::GetModels->new(

    controller            => $self,

    model                 => 'OrderItem',

    sorted                => {

      _default              => {

        by                    => 'reqdate',

        dir                   => 1,

      },

      %sort_columns,

    },

    # show only open orders

    query                 => [ 'order.closed' => '0',  "order.${vc}_id" => { gt => 0 } ],

    with_objects          => [ 'order', "order.$vc" ],

    additional_url_params => { vc => $vc},

  );

}

sub init_vc {

  return $::form->{vc} if ($::form->{vc} eq 'customer' || $::form->{vc} eq 'vendor') || croak "self (DeliveryPlan) has no vc defined";

}

sub init_all_employees {

  return SL::DB::Manager::Employee->get_all_sorted;

}

sub init_all_businesses {

  return SL::DB::Manager::Business->get_all_sorted;

}

sub link_to {

  my ($self, $object, %params) = @_;

  return unless $object;

  my $action = $params{action} || 'edit';

  if ($object->isa('SL::DB::Order')) {

    my $type   = $object->type;

    my $vc     = $object->is_sales ? 'customer' : 'vendor';

    my $id     = $object->id;

    return "oe.pl?action=$action&type=$type&vc=$vc&id=$id";

  }

  if ($object->isa('SL::DB::Part')) {

    my $id     = $object->id;

    return "ic.pl?action=$action&id=$id";

  }

  if ($object->isa('SL::DB::Customer')) {

    my $id     = $object->id;

    return "controller.pl?action=CustomerVendor/$action&id=$id&db=customer";

  }

}

sub calc_qtys_price {

  my ($self, $orderitems) = @_;

  # using $orderitem->shipped_qty 40 times is far too slow. need to do it manually

  #

  return unless scalar @$orderitems;

  my %orderitems_by_id = map { $_->id => $_ } @$orderitems;

  my $query = <<SQL;

    SELECT oi.id, doi.qty, doi.qty, doi.unit, doe.delivered, doe.closed, -- dois.qty as qty_stocked,

           oi.sellprice, oi.discount, oi.price_factor

    FROM record_links rl

    INNER JOIN delivery_order_items doi ON (doi.id = rl.to_id)

    INNER JOIN orderitems oi            ON (oi.id  = rl.from_id)

    INNER JOIN delivery_orders doe      ON (doe.id = doi.delivery_order_id)

    --INNER JOIN delivery_order_items_stock dois ON (doi.id = dois.delivery_order_item_id)

    WHERE rl.from_table = 'orderitems'

      AND rl.to_table   = 'delivery_order_items'

      AND oi.id IN (@{[ join ', ', ("?")x @$orderitems ]})

SQL

  my $result = SL::DBUtils::selectall_hashref_query($::form, $::form->get_standard_dbh, $query, map { $_->id } @$orderitems);

  for my $row (@$result) {

    my $item = $orderitems_by_id{ $row->{id} };

    $item->{shipped_qty}   ||= 0;

    $item->{delivered_qty} ||= 0;

    $item->{do_closed_qty} ||= 0;

    $item->{shipped_qty}    += AM->convert_unit($row->{unit} => $item->unit) * $row->{qty} unless ($row->{delivered} || $row->{closed});

    $item->{delivered_qty}  += AM->convert_unit($row->{unit} => $item->unit) * $row->{qty} if ($row->{delivered} && !$row->{closed});

    $item->{do_closed_qty}  += AM->convert_unit($row->{unit} => $item->unit) * $row->{qty} if ($row->{closed});

    #$item->{do_closed_qty}  += AM->convert_unit($row->{unit} => $item->unit) * $row->{qty_stocked} if ($row->{closed});

    $item->{not_shipped_qty} += AM->convert_unit($row->{unit} => $item->unit) * $row->{qty} unless ($row->{delivered});

    #  my $price_factor = $self->price_factor || 1;

    #$self->_delivered_qty;

    #$item->{netto_qty}              += $row->{qty} * $row->{sellprice} * (1 - $row->{discount} ) / ($row->{price_factor} || 1);

    # no sum

    $item->{netto_shipped_qty}      = $item->{shipped_qty} * $row->{sellprice} * (1 - $row->{discount} ) / ($row->{price_factor} || 1);

#    $item->{netto_not_shipped_qty}  += $item->{shipped_qty} * $row->{sellprice} * (1 - $row->{discount} ) / ($row->{price_factor} || 1);

    $item->{netto_delivered_qty}    = $item->{delivered_qty} * $row->{sellprice} * (1 - $row->{discount} ) / ($row->{price_factor} || 1);

    $item->{netto_do_closed_qty}    = $item->{do_closed_qty} * $row->{sellprice} * (1 - $row->{discount} ) / ($row->{price_factor} || 1);

  }

}

1;

__END__

=pod

=encoding utf8

=head1 NAME

SL::Controller::DeliveryValueReport - Controller for Delivery Value Report

=head2 OVERVIEW

Controller class for Delivery Value Report

The goal of the record is to determine which goods and at what costs are already delivered, transfered in

relation to open orders, orders in process.

Inherited from the base controller class, this controller implements the Delivery Value Report.

Historically the idea derived from a customer extension by thinking: Ah, we just need the Delivery Plan

put some more columns in it and then we have a pseudo "Production, Planing, Report" with an additional

emphasis on expected future cashflow.

Some problems exists with the current report: The definition of not fully delivered sales / purchase order

is very (customer) special, in general a simple check on order is open should be a sensible workflow value.

Secondly a major database flaw (no persistent ids in order_items) made it impossible to determine the origin

of items in terms of linked records. One assumption build in the original DeliveryPlan was that the part_ids

are equal. This breaks if the document has the same item on different positions. The next idea was to check

for individual item reqdates.

After some arguing we decided to implement persistent ids for all items and link them directly via record_links.

This linking has been secrectly active since version 3.2, therefore this redesign is possible.

Currently the report even works correctly even if the same part has been manually put in another position, renamed or some

 other metadata for the position has been altered. This is due to the fact that a hidden converted_from_previous_document is

used in the position.

  from_table |   id    | to_table             |   id

  orderitems |    7    | delivery_order_items |   11

=head1 FUNCTIONS

=over 2

=item C<action_list_sales_delivery_orders>

L

  'Net Value in delivery orders' => 'Netto mit Lieferschein',

  'Net value in Order'          => 'Netto Auftrag',

  'Net value transferred in / out' => 'Netto ein- /ausgelagert',

  'Net value without delivery orders' => 'Netto ohne Lieferschein',

  'Qty in Order'                => 'Menge Auftrag',

  'Qty in closed delivery orders' => 'Menge in geschlossenen Lieferscheinen',

  'Qty in delivery orders'      => 'Menge mit Lieferschein',

    action: DeliveryValueReport/list

    action: DeliveryValueReport/list

[%- USE T8 %]

[%- USE L %]

[%- USE LxERP %]

[%- USE HTML %]

<form action='controller.pl' method='post'>

<div class='filter_toggle'>

<a href='#' onClick='javascript:$(".filter_toggle").toggle()'>[% 'Show Filter' | $T8 %]</a>

  [% SELF.filter_summary | html %]

</div>

<div class='filter_toggle' style='display:none'>

<a href='#' onClick='javascript:$(".filter_toggle").toggle()'>[% 'Hide Filter' | $T8 %]</a>

 <table id='filter_table'>

  <tr>

   <th align="right">[% 'Number' | $T8 %]</th>

   <td>[% L.input_tag('filter.order.ordnumber:substr::ilike', filter.order.ordnumber_substr__ilike, size = 20) %]</td>

  </tr>

  <tr>

   <th align="right">[% 'Document Project Number' | $T8 %]</th>

   <td>[% L.input_tag('filter.order.globalproject.projectnumber:substr::ilike', filter.order.globalproject.projectnumber_substr__ilike, size = 20) %]</td>

  </tr>

  <tr>

   <th align="right">[% 'Part Number' | $T8 %]</th>

   <td>[% L.input_tag('filter.part.partnumber:substr::ilike', filter.part.partnumber_substr__ilike, size = 20) %]</td>

  </tr>

  <tr>

   <th align="right">[% 'Part Description' | $T8 %]</th>

   <td>[% L.input_tag('filter.description:substr::ilike', filter.description_substr__ilike, size = 20) %]</td>

  </tr>

  <tr>

   <th align="right">[% 'Delivery Date' | $T8 %] [% 'From Date' | $T8 %]</th>

   <td>[% L.date_tag('filter.reqdate:date::ge', filter.reqdate_date__ge) %]</td>

  </tr>

  <tr>

   <th align="right">[% 'Delivery Date' | $T8 %] [% 'To Date' | $T8 %]</th>

   <td>[% L.date_tag('filter.reqdate:date::le', filter.reqdate_date__le) %]</td>

  </tr>

  <tr>

   <th align="right">[% 'Quantity' | $T8 %]</th>

   <td>[% L.input_tag('filter.qty:number', filter.qty_number, size = 20) %]</td>

  </tr>

[% IF SELF.vc == 'customer' %]

    <tr>

     <th align="right">[% 'Customer' | $T8 %]</th>

     <td>[% L.input_tag('filter.order.customer.name:substr::ilike', filter.order.customer.name_substr__ilike, size = 20) %]</td>

   </tr>

   <tr>

    <th align="right">[% 'Customer Number' | $T8 %]</th>

    <td>[% L.input_tag('filter.order.customer.customernumber:substr::ilike', filter.order.customer.customernumber_substr__ilike, size = 20) %]</td>

   </tr>

[% ELSE %]  <tr>

    <th align="right">[% 'Vendor' | $T8 %]</th>

    <td>[% L.input_tag('filter.order.vendor.name:substr::ilike', filter.order.vendor.name_substr__ilike, size = 20) %]</td>

   </tr>

   <tr>

    <th align="right">[% 'Vendor Number' | $T8 %]</th>

    <td>[% L.input_tag('filter.order.vendor.vendornumber:substr::ilike', filter.order.vendor.vendornumber_substr__ilike, size = 20) %]</td>

   </tr>

[% END %]

  <tr>

   <th align="right">[% 'Customer type' | $T8 %]</th>

   <td>

     [% L.select_tag('filter.order.customer.business_id', SELF.all_businesses,

                     default    => filter.order.customer.business_id,

                     title_key  => 'description',

                     value_key  => 'id',

                     with_empty => 1,

                     style      => 'width: 200px') %]

   </td>

  </tr>

  <tr>

   <th align="right">[% 'Employee' | $T8 %]</th>

   <td>

     [% L.select_tag('filter.order.employee_id', SELF.all_employees,

                     default    => filter.order.employee_id,

                     title_key  => 'name',

                     value_key  => 'id',

                     with_empty => 1,

                     style      => 'width: 200px') %]

   </td>

  </tr>

  <!-- tr>

   <th align="right">[% 'Type' | $T8 %]</th>

   <td>

     [% L.checkbox_tag('filter.part.type[]', checked=filter.part.type_.part,     value='part',     label=LxERP.t8('Part')) %]

     [% L.checkbox_tag('filter.part.type[]', checked=filter.part.type_.service,  value='service',  label=LxERP.t8('Service')) %]

     [% L.checkbox_tag('filter.part.type[]', checked=filter.part.type_.assembly, value='assembly', label=LxERP.t8('Assembly')) %]

   </td>

  </tr -->

 </table>

[% L.hidden_tag('action', 'DeliveryValueReport/dispatch') %]

[% L.hidden_tag('sort_by', FORM.sort_by) %]

[% L.hidden_tag('sort_dir', FORM.sort_dir) %]

[% L.hidden_tag('page', FORM.page) %]

[% L.hidden_tag('vc', SELF.vc) %]

[% L.input_tag('action_list', LxERP.t8('Continue'), type = 'submit', class='submit')%]

<a href='#' onClick='javascript:$("#filter_table input").val("");$("#filter_table input[type=checkbox]").prop("checked", 0);$("#filter_table select").prop("selectedIndex", 0);'>[% 'Reset' | $T8 %]</a>

</div>

</form>

[% USE L %]

[%- L.paginate_controls(models=models) %]

[%- USE L %]

[%- PROCESS 'delivery_value_report/_filter.html' filter=SELF.models.filtered.laundered %]

 <hr>