From 47c1e96b4ffee65de3c2e0558a54f7734ca63e35 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Jan=20B=C3=BCren?= Date: Mon, 28 Sep 2015 14:53:33 +0200 Subject: [PATCH] Lieferwertbericht auf eigenen Controller umgestellt und erweitert MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Erweiterungen aus einem Kundenprojekt übernommen. Ferner performanter gemacht, in Anlehnung an calc_qts aus DeliveryPlan. Zusätzlich das model einfacher umgesetzt und die Auswertung basiert jetzt auf der Verknüpfung von orderitems(id) -> delivery_order_items(id) und nicht mehr über die Näherung über die verknüpften Belege. --- SL/Controller/DeliveryValueReport.pm | 354 ++++++++++++++++++ locale/de/all | 8 +- menus/user/00-erp.yaml | 6 +- .../delivery_value_report/_filter.html | 103 +++++ .../delivery_value_report/report_bottom.html | 2 + .../delivery_value_report/report_top.html | 3 + 6 files changed, 471 insertions(+), 5 deletions(-) create mode 100644 SL/Controller/DeliveryValueReport.pm create mode 100644 templates/webpages/delivery_value_report/_filter.html create mode 100644 templates/webpages/delivery_value_report/report_bottom.html create mode 100644 templates/webpages/delivery_value_report/report_top.html diff --git a/SL/Controller/DeliveryValueReport.pm b/SL/Controller/DeliveryValueReport.pm new file mode 100644 index 000000000..1270f8d0c --- /dev/null +++ b/SL/Controller/DeliveryValueReport.pm @@ -0,0 +1,354 @@ +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 = <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 + +L diff --git a/locale/de/all b/locale/de/all index ac3f2a791..d4bac8359 100755 --- a/locale/de/all +++ b/locale/de/all @@ -1644,8 +1644,12 @@ $self->{texts} = { 'Negative reductions are possible to model price increases.' => 'Negative Abschläge sind möglich um Aufschläge zu modellieren.', 'Neither sections nor function blocks have been created yet.' => 'Es wurden bisher weder Abschnitte noch Funktionsblöcke angelegt.', 'Net Income Statement' => 'Einnahmenüberschußrechnung', + 'Net Value in delivery orders' => 'Netto mit Lieferschein', 'Net amount' => 'Nettobetrag', 'Net amount (for verification)' => 'Nettobetrag (zur Überprüfung)', + 'Net value in Order' => 'Netto Auftrag', + 'Net value transferred in / out' => 'Netto ein- /ausgelagert', + 'Net value without delivery orders' => 'Netto ohne Lieferschein', 'Netto Terms' => 'Zahlungsziel netto', 'New Password' => 'Neues Passwort', 'New Purchase Price Rule' => 'Neue Einkaufspreisregel', @@ -2092,7 +2096,10 @@ $self->{texts} = { 'Qty equal or less than #1' => 'Menge gleich oder kleiner als #1', 'Qty equal or more than #1' => 'Menge gleich oder größer als #1', 'Qty equals #1' => 'Menge ist #1', + 'Qty in Order' => 'Menge Auftrag', 'Qty in Selected Records' => 'Menge in gewählten Belegen', + 'Qty in closed delivery orders' => 'Menge in geschlossenen Lieferscheinen', + 'Qty in delivery orders' => 'Menge mit Lieferschein', 'Qty in stock' => 'Lagerbestand', 'Qty less than #1' => 'Menge weniger als #1', 'Qty more than #1' => 'Menge mehr als #1', @@ -2350,7 +2357,6 @@ $self->{texts} = { 'Sellprice for price group \'#1\'' => 'Verkaufspreis für Preisgruppe \'#1\'', 'Sellprice significant places' => 'Verkaufspreis: Nachkommastellen', 'Semicolon' => 'Semikolon', - 'Send PDF to support contract\'s contact person' => 'PDFs an Ansprechpersonen der Wartungsverträge schicken', 'Sender' => 'AbsenderIn', 'Sent on' => 'Verschickt am', 'Sep' => 'Sep', diff --git a/menus/user/00-erp.yaml b/menus/user/00-erp.yaml index 9f025e1e4..e28239230 100644 --- a/menus/user/00-erp.yaml +++ b/menus/user/00-erp.yaml @@ -363,8 +363,7 @@ order: 900 access: delivery_value_report params: - action: DeliveryPlan/list - mode: delivery_value_report + action: DeliveryValueReport/list vc: customer - parent: ar_reports id: ar_reports_financial_controlling @@ -481,9 +480,8 @@ order: 600 access: delivery_value_report params: - action: DeliveryPlan/list + action: DeliveryValueReport/list vc: vendor - mode: delivery_value_report - id: warehouse name: Warehouse icon: warehouse diff --git a/templates/webpages/delivery_value_report/_filter.html b/templates/webpages/delivery_value_report/_filter.html new file mode 100644 index 000000000..e50b6a7cc --- /dev/null +++ b/templates/webpages/delivery_value_report/_filter.html @@ -0,0 +1,103 @@ +[%- USE T8 %] +[%- USE L %] +[%- USE LxERP %] +[%- USE HTML %] +
+
+[% 'Show Filter' | $T8 %] + [% SELF.filter_summary | html %] +
+ + +
diff --git a/templates/webpages/delivery_value_report/report_bottom.html b/templates/webpages/delivery_value_report/report_bottom.html new file mode 100644 index 000000000..da08e0758 --- /dev/null +++ b/templates/webpages/delivery_value_report/report_bottom.html @@ -0,0 +1,2 @@ +[% USE L %] +[%- L.paginate_controls(models=models) %] diff --git a/templates/webpages/delivery_value_report/report_top.html b/templates/webpages/delivery_value_report/report_top.html new file mode 100644 index 000000000..903c0c50c --- /dev/null +++ b/templates/webpages/delivery_value_report/report_top.html @@ -0,0 +1,3 @@ +[%- USE L %] +[%- PROCESS 'delivery_value_report/_filter.html' filter=SELF.models.filtered.laundered %] +
-- 2.20.1