From b213d89c9bb929cbebda13388284bd442f6f8aa2 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Sven=20Sch=C3=B6ling?= Date: Tue, 24 Apr 2012 16:45:46 +0200 Subject: [PATCH] DeliveryPlan MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Erste Version squashed (überspringt 10 Commits, in denen das Query unbrauchbar langsam ist) --- SL/Controller/DeliveryPlan.pm | 270 ++++++++++++++++++ SL/DB/OrderItem.pm | 5 +- SL/InstallationCheck.pm | 1 + locale/de/all | 9 + menu.ini | 4 + templates/webpages/delivery_plan/_filter.html | 58 ++++ templates/webpages/delivery_plan/_list.html | 44 +++ templates/webpages/delivery_plan/list.html | 7 + .../webpages/delivery_plan/report_bottom.html | 4 + .../webpages/delivery_plan/report_top.html | 3 + 10 files changed, 403 insertions(+), 2 deletions(-) create mode 100644 SL/Controller/DeliveryPlan.pm create mode 100644 templates/webpages/delivery_plan/_filter.html create mode 100644 templates/webpages/delivery_plan/_list.html create mode 100644 templates/webpages/delivery_plan/list.html create mode 100644 templates/webpages/delivery_plan/report_bottom.html create mode 100644 templates/webpages/delivery_plan/report_top.html diff --git a/SL/Controller/DeliveryPlan.pm b/SL/Controller/DeliveryPlan.pm new file mode 100644 index 000000000..a7ed85e10 --- /dev/null +++ b/SL/Controller/DeliveryPlan.pm @@ -0,0 +1,270 @@ +package SL::Controller::DeliveryPlan; + +use strict; +use parent qw(SL::Controller::Base); + +use Clone qw(clone); +use SL::DB::OrderItem; +use SL::Controller::Helper::ParseFilter; +use SL::Controller::Helper::ReportGenerator; + +__PACKAGE__->run_before(sub { $::auth->assert('sales_order_edit'); }); + +sub action_list { + my ($self) = @_; + my %list_params = ( + sort_by => $::form->{sort_by} || 'reqdate', + sort_dir => $::form->{sort_dir}, + filter => $::form->{filter}, + page => $::form->{page}, + ); + + my $db_args = $self->setup_for_list(%list_params); + $self->{pages} = SL::DB::Manager::OrderItem->paginate(%list_params, args => $db_args); + $self->{flat_filter} = { map { $_->{key} => $_->{value} } $::form->flatten_variables('filter') }; + + my $top = $::form->parse_html_template('delivery_plan/report_top', { FORM => $::form, SELF => $self }); + my $bottom = $::form->parse_html_template('delivery_plan/report_bottom', { SELF => $self }); + + $self->prepare_report( + report_generator_options => { + raw_top_info_text => $top, + raw_bottom_info_text => $bottom, + controller_class => 'DeliveryPlan', + }, + report_generator_export_options => [ + 'list', qw(filter sort_by sort_dir), + ], + db_args => $db_args, + ); + + $self->{orderitems} = SL::DB::Manager::OrderItem->get_all(%$db_args); + + $self->list_objects; +} + +# private functions + +sub setup_for_list { + my ($self, %params) = @_; + $self->{filter} = {}; + my %args = ( + parse_filter( + $self->_pre_parse_filter($::form->{filter}, $self->{filter}), + with_objects => [ 'order', 'order.customer', 'part' ], + launder_to => $self->{filter}, + ), + sort_by => $self->set_sort_params(%params), + page => $params{page}, + ); + + $args{query} = [ @{ $args{query} || [] }, + ( + 'order.customer_id' => { gt => 0 }, + 'order.closed' => 0, + or => [ 'order.quotation' => 0, 'order.quotation' => undef ], + + # filter by shipped_qty < qty, read from innermost to outermost + 'id' => [ \" + -- 3. resolve the desired information about those + SELECT oi.id FROM ( + -- 2. slice only part, orderitem and both quantities from it + SELECT parts_id, trans_id, qty, SUM(doi_qty) AS doi_qty FROM ( + -- 1. join orderitems and deliverorder items via record_links. + -- also add customer data to filter for sales_orders + SELECT oi.parts_id, oi.trans_id, oi.id, oi.qty, doi.qty AS doi_qty + FROM orderitems oi, oe, record_links rl, delivery_order_items doi + WHERE + oe.id = oi.trans_id AND + oe.customer_id IS NOT NULL AND + (oe.quotation = 'f' OR oe.quotation IS NULL) AND + NOT oe.closed AND + rl.from_id = oe.id AND + rl.from_id = oi.trans_id AND + oe.id = oi.trans_id AND + rl.from_table = 'oe' AND + rl.to_table = 'delivery_orders' AND + rl.to_id = doi.delivery_order_id AND + oi.parts_id = doi.parts_id + ) tuples GROUP BY parts_id, trans_id, qty + ) partials + LEFT JOIN orderitems oi ON partials.parts_id = oi.parts_id AND partials.trans_id = oi.trans_id + WHERE oi.qty > doi_qty + + UNION ALL + + -- 4. since the join over record_links fails for sales_orders wihtout any delivery order + -- retrieve those without record_links at all + SELECT oi.id FROM orderitems oi, oe + WHERE + oe.id = oi.trans_id AND + oe.customer_id IS NOT NULL AND + (oe.quotation = 'f' OR oe.quotation IS NULL) AND + NOT oe.closed AND + oi.trans_id NOT IN ( + SELECT from_id + FROM record_links rl + WHERE + rl.from_table ='oe' AND + rl.to_table = 'delivery_orders' + ) + " ], + ) + ]; + + return \%args; +} + +sub set_sort_params { + my ($self, %params) = @_; + my $sort_str; + ($self->{sort_by}, $self->{sort_dir}, $sort_str) = + SL::DB::Manager::OrderItem->make_sort_string(%params); + return $sort_str; +} + +sub prepare_report { + my ($self, %params) = @_; + + my $objects = $params{objects} || []; + my $report = SL::ReportGenerator->new(\%::myconfig, $::form); + $self->{report} = $report; + + my @columns = qw(reqdate customer ordnumber partnumber description qty shipped_qty); + my @visible = qw(reqdate partnumber description qty shipped_qty ordnumber customer); + my @sortable = qw(reqdate partnumber description ordnumber customer); + + my %column_defs = ( + reqdate => { text => $::locale->text('Reqdate'), + sub => sub { $_[0]->reqdate_as_date || $_[0]->order->reqdate_as_date }}, + description => { text => $::locale->text('Description'), + sub => sub { $_[0]->description }, + obj_link => sub { $self->link_to($_[0]->part) }}, + partnumber => { text => $::locale->text('Part Number'), + sub => sub { $_[0]->part->partnumber }, + obj_link => sub { $self->link_to($_[0]->part) }}, + qty => { text => $::locale->text('Qty'), + sub => sub { $_[0]->qty_as_number . ' ' . $_[0]->unit }}, + shipped_qty => { text => $::locale->text('shipped'), + sub => sub { $::form->format_amount(\%::myconfig, $_[0]->shipped_qty, 2) . ' ' . $_[0]->unit }}, + ordnumber => { text => $::locale->text('Order'), + sub => sub { $_[0]->order->ordnumber }, + obj_link => sub { $self->link_to($_[0]->order) }}, + customer => { text => $::locale->text('Customer'), + sub => sub { $_[0]->order->customer->name }, + obj_link => sub { $self->link_to($_[0]->order->customer) }}, + ); + + + for my $col (@sortable) { + $column_defs{$col}{link} = $self->url_for( + action => 'list', + sort_by => $col, + sort_dir => ($self->{sort_by} eq $col ? 1 - $self->{sort_dir} : $self->{sort_dir}), + page => $self->{pages}{cur}, + %{ $self->{flat_filter} }, + ); + } + + map { $column_defs{$_}->{visible} = 1 } @visible; + + $report->set_columns(%column_defs); + $report->set_column_order(@columns); + $report->set_options(allow_pdf_export => 1, allow_csv_export => 1); + $report->set_sort_indicator(%params); + $report->set_export_options(@{ $params{report_generator_export_options} || [] }); + $report->set_options( + %{ $params{report_generator_options} || {} }, + output_format => 'HTML', + top_info_text => $::locale->text('Delivery Plan for currently outstanding sales orders'), + title => $::locale->text('Delivery Plan'), + ); + $report->set_options_from_form; + + SL::DB::Manager::OrderItem->disable_paginating(args => $params{db_args}) if $report->{options}{output_format} =~ /^(pdf|csv)$/i; + + $self->{report_data} = { + column_defs => \%column_defs, + columns => \@columns, + visible => \@visible, + sortable => \@sortable, + }; +} + +sub list_objects { + my ($self) = @_; + my $column_defs = $self->{report_data}{column_defs}; + for my $obj (@{ $self->{orderitems} || [] }) { + $self->{report}->add_data({ + map { + $_ => { + data => $column_defs->{$_}{sub} ? $column_defs->{$_}{sub}->($obj) + : $obj->can($_) ? $obj->$_ + : $obj->{$_}, + link => $column_defs->{$_}{obj_link} ? $column_defs->{$_}{obj_link}->($obj) : '', + }, + } @{ $self->{report_data}{columns} || {} } + }); + } + + return $self->{report}->generate_with_headers; +} + +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 "ct.pl?action=$action&id=$id&db=customer"; + } +} + +# unfortunately ParseFilter can't handle compount filters. +# so we clone the original filter (still need that for serializing) +# rip out the options we know an replace them with the compound options. +# ParseFilter will take care of the prefixing then. +sub _pre_parse_filter { + my ($self, $orig_filter, $launder_to) = @_; + + return undef unless $orig_filter; + + my $filter = clone($orig_filter); + if ($filter->{part} && $filter->{part}{type}) { + $launder_to->{part}{type} = delete $filter->{part}{type}; + my @part_filters = grep $_, map { + $launder_to->{part}{type}{$_} ? SL::DB::Manager::Part->type_filter($_) : () + } qw(part service assembly); + + push @{ $filter->{and} }, or => [ @part_filters ] if @part_filters; + } + + if ($filter->{'reqdate:date::le'}) { + $launder_to->{'reqdate_date__le'} = delete $filter->{'reqdate:date::le'}; + my $parsed_date = DateTime->from_lxoffice($launder_to->{'reqdate_date__le'}); + push @{ $filter->{and} }, or => [ + 'reqdate' => { le => $parsed_date }, + and => [ + 'reqdate' => undef, + 'order.reqdate' => { le => $parsed_date }, + ] + ] if $parsed_date; + } + + return $filter; +} + +1; diff --git a/SL/DB/OrderItem.pm b/SL/DB/OrderItem.pm index d193393bb..f24abcaf1 100644 --- a/SL/DB/OrderItem.pm +++ b/SL/DB/OrderItem.pm @@ -68,11 +68,12 @@ sub _sort_spec { qty => [ 'qty' ], ordnumber => [ 'order.ordnumber' ], customer => [ 'lower(customer.name)', ], - position => [ 'trans_id' ], - reqdate => [ 'COALESCE(orderitems.reqdate, order.transdate)' ], + position => [ 'trans_id', 'runningnumber' ], + reqdate => [ 'COALESCE(orderitems.reqdate, order.reqdate)' ], orddate => [ 'order.orddate' ], sellprice => [ 'sellprice' ], discount => [ 'discount' ], + transdate => [ 'transdate::date', 'order.reqdate' ], }, default => [ 'position', 1 ], nulls => { } diff --git a/SL/InstallationCheck.pm b/SL/InstallationCheck.pm index 758707d21..935d131cd 100644 --- a/SL/InstallationCheck.pm +++ b/SL/InstallationCheck.pm @@ -11,6 +11,7 @@ BEGIN { @required_modules = ( { name => "parent", url => "http://search.cpan.org/~corion/", debian => 'libparent-perl' }, { name => "Archive::Zip", version => '1.16', url => "http://search.cpan.org/~adamk/", debian => 'libarchive-zip-perl' }, + { name => "Clone", url => "http://search.cpan.org/~rdf/", debian => 'libclone-perl' }, { name => "Config::Std", url => "http://search.cpan.org/~dconway/", debian => 'libconfig-std-perl' }, { name => "DateTime", url => "http://search.cpan.org/~drolsky/", debian => 'libdatetime-perl' }, { name => "DBI", version => '1.50', url => "http://search.cpan.org/~timb/", debian => 'libdbi-perl' }, diff --git a/locale/de/all b/locale/de/all index bf755e25a..7b16fc0f0 100644 --- a/locale/de/all +++ b/locale/de/all @@ -208,6 +208,7 @@ $self->{texts} = { 'Article type (see below)' => 'Artikeltyp (siehe unten)', 'As a result, the saved onhand values of the present goods can be stored into a warehouse designated by you, or will be reset for a proper warehouse tracking' => 'Als Konsequenz können die gespeicherten Mengen entweder in ein Lager überführt werden, oder für eine frische Lagerverwaltung resettet werden.', 'Assemblies' => 'Erzeugnisse', + 'Assembly' => 'Erzeugnis', 'Assembly Description' => 'Erzeugnis-Beschreibung', 'Assembly Number' => 'Erzeugnis-Nummer', 'Assembly Number missing!' => 'Erzeugnisnummer fehlt!', @@ -592,6 +593,11 @@ $self->{texts} = { 'Delivery Order created' => 'Lieferschein erstellt', 'Delivery Order deleted!' => 'Lieferschein gelöscht!', 'Delivery Orders' => 'Lieferscheine', + 'Delivery Orders for this document' => 'Lieferscheine für dieses Dokument', + 'Delivery Plan' => 'Lieferplan', + 'Delivery Plan for currently outstanding sales orders' => 'Lieferplan für offene Verkaufsaufträge', + 'Delivery information deleted.' => 'Lieferinformation gelöscht.', + 'Delivery information saved.' => 'Lieferinformation gespeichert.', 'Department' => 'Abteilung', 'Department 1' => 'Abteilung (1)', 'Department 2' => 'Abteilung (2)', @@ -903,6 +909,7 @@ $self->{texts} = { 'Help Template Variables' => 'Hilfe zu Dokumenten-Variablen', 'Help on column names' => 'Hilfe zu Spaltennamen', 'Here\'s an example command line:' => 'Hier ist eine Kommandozeile, die als Beispiel dient:', + 'Hide Filter' => 'Filter verbergen', 'Hide by default' => 'Standardmäßig verstecken', 'Hide help text' => 'Hilfetext verbergen', 'History' => 'Historie', @@ -1518,6 +1525,7 @@ $self->{texts} = { 'Requested execution date from' => 'Gewünschtes Ausführungsdatum von', 'Requested execution date to' => 'Gewünschtes Ausführungsdatum bis', 'Required by' => 'Lieferdatum', + 'Reset' => 'Zurücksetzen', 'Restore Dataset' => 'Datenbank wiederherstellen', 'Revenue' => 'Erlöskonto', 'Revenue Account' => 'Erlöskonto', @@ -1635,6 +1643,7 @@ $self->{texts} = { 'Shopartikel' => 'Shopartikel', 'Short' => 'Knapp', 'Show' => 'Zeigen', + 'Show Filter' => 'Filter zeigen', 'Show Salesman' => 'Verkäufer anzeigen', 'Show TODO list' => 'Aufgabenliste anzeigen', 'Show by default' => 'Standardmäßig anzeigen', diff --git a/menu.ini b/menu.ini index da5450664..ac8f9b9b1 100644 --- a/menu.ini +++ b/menu.ini @@ -166,6 +166,10 @@ ACCESS=dunning_edit module=dn.pl action=search +[AR--Reports--Delivery Plan] +ACCESS=sales_order_edit +module=controller.pl +action=DeliveryPlan/list [AP] diff --git a/templates/webpages/delivery_plan/_filter.html b/templates/webpages/delivery_plan/_filter.html new file mode 100644 index 000000000..eefa03edd --- /dev/null +++ b/templates/webpages/delivery_plan/_filter.html @@ -0,0 +1,58 @@ +[%- USE T8 %] +[%- USE L %] +[%- USE LxERP %] +[%- USE HTML %] +
+
+[% 'Show Filter' | $T8 %] + [% SELF.filter_summary %] +
+ + +
diff --git a/templates/webpages/delivery_plan/_list.html b/templates/webpages/delivery_plan/_list.html new file mode 100644 index 000000000..f0bb2772b --- /dev/null +++ b/templates/webpages/delivery_plan/_list.html @@ -0,0 +1,44 @@ +[% USE HTML %][% USE T8 %][% USE L %][% USE LxERP %] + +[% BLOCK header %] + [% SET new_sort_dir = SELF.sort_by == sort_by ? 1 - SELF.sort_dir : SELF.sort_dir %] + + + [%- title %] + [%- IF SELF.sort_by == sort_by %] + + [%- END %] + + +[% END %] + +
+[%- IF !SELF.orderitems.size %] +

[%- 'There are no outstanding deliveries at the moment.' | $T8 %]

+[%- ELSE %] + + + + [% PROCESS header title=LxERP.t8('Date') sort_by='transdate', size=15 %] + [% PROCESS header title=LxERP.t8('Description') sort_by='description', size=15 %] + [% PROCESS header title=LxERP.t8('Part Number') sort_by='partnumber', size=15 %] + [% PROCESS header title=LxERP.t8('Qty') sort_by='qty', size=10 %] + [% PROCESS header title=LxERP.t8('Order') sort_by='ordnumber', size=10 %] + [% PROCESS header title=LxERP.t8('Customer') sort_by='customer', size=10 %] + + + [%- FOREACH row = SELF.orderitems %] + + + + + + + + + [%- END %] +
[% row.transdate ? row.transdate : row.order.reqdate_as_date %][% row.part.partnumber | html %][% row.description | html %][% LxERP.format_amount(row.qty, 2) | html %][% row.order.ordnumber | html %][% row.order.customer.name | html %]
+

[% PROCESS 'common/paginate.html' pages=SELF.pages, base_url=SELF.url_for(action='list', sort_dir=SELF.sort_dir, sort_by=SELF.sort_by) %]

+ +[%- END %] +
diff --git a/templates/webpages/delivery_plan/list.html b/templates/webpages/delivery_plan/list.html new file mode 100644 index 000000000..16ec65632 --- /dev/null +++ b/templates/webpages/delivery_plan/list.html @@ -0,0 +1,7 @@ +[%- USE T8 %] + +

[% 'Delivery Plan' | $T8 %]

+ +[%- PROCESS 'delivery_plan/_filter.html' filter=FORM.filter %] +
+[%- PROCESS 'delivery_plan/_list.html' %] diff --git a/templates/webpages/delivery_plan/report_bottom.html b/templates/webpages/delivery_plan/report_bottom.html new file mode 100644 index 000000000..3193961d9 --- /dev/null +++ b/templates/webpages/delivery_plan/report_bottom.html @@ -0,0 +1,4 @@ +[% SET report_bottom_url_args = {} %] +[% report_bottom_url_args.import(SELF.flat_filter) %] +[% report_bottom_url_args.import({action='list', sort_dir=SELF.sort_dir, sort_by=SELF.sort_by}) %] +

[% PROCESS 'common/paginate.html' pages=SELF.pages, base_url=SELF.url_for(report_bottom_url_args) %]

diff --git a/templates/webpages/delivery_plan/report_top.html b/templates/webpages/delivery_plan/report_top.html new file mode 100644 index 000000000..cc35146be --- /dev/null +++ b/templates/webpages/delivery_plan/report_top.html @@ -0,0 +1,3 @@ +[%- USE L %] +[%- PROCESS 'delivery_plan/_filter.html' filter=SELF.filter %] +
-- 2.20.1