From 20004cac8f656f67e86e63fdb85f6dc083cf83d7 Mon Sep 17 00:00:00 2001 From: "G. Richardson" Date: Sat, 10 Aug 2019 17:00:10 +0200 Subject: [PATCH] Part Controller - neuer Tab mit Lagerinformationen MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit * Übersicht über alle Lagerbestände, wo der Artikel überall gelagert ist (Derzeit gibt es im Template Variabeln um Zwischensummen und Nachkommastellen zu kontrollieren) * Mini-Journal mit den letzten 10 Lagertransaktionen des Artikels Diese Daten werden nur bei Bedarf geladen, also wenn der Benutzer auf den neuen Tab "Lagerbewegungen/-bestände" klickt. Außerdem gibt es Links zu diversen Lageraktionen (Einlagern, Umlagern, Entnahme), wo der Artikel dann schon vorausgewählt ist. --- SL/Controller/Part.pm | 15 +++- SL/DB/Part.pm | 75 +++++++++++++--- doc/changelog | 1 + locale/de/all | 1 + templates/webpages/part/_inventory.html | 29 ++++++ templates/webpages/part/_inventory_data.html | 93 ++++++++++++++++++++ templates/webpages/part/form.html | 10 +++ 7 files changed, 213 insertions(+), 11 deletions(-) create mode 100644 templates/webpages/part/_inventory.html create mode 100644 templates/webpages/part/_inventory_data.html diff --git a/SL/Controller/Part.pm b/SL/Controller/Part.pm index 6092f2962..ce09affa4 100644 --- a/SL/Controller/Part.pm +++ b/SL/Controller/Part.pm @@ -33,7 +33,7 @@ use Rose::Object::MakeMethods::Generic ( all_buchungsgruppen all_payment_terms all_warehouses parts_classification_filter all_languages all_units all_price_factors) ], - 'scalar' => [ qw(warehouse bin) ], + 'scalar' => [ qw(warehouse bin stock_amounts journal) ], ); # safety @@ -263,6 +263,17 @@ sub action_history { history_entries => $history_entries); } +sub action_inventory { + my ($self) = @_; + + $::auth->assert('warehouse_contents'); + + $self->stock_amounts($self->part->get_simple_stock_sql); + $self->journal($self->part->get_mini_journal); + + $_[0]->render('part/_inventory_data', { layout => 0 }); +}; + sub action_update_item_totals { my ($self) = @_; @@ -903,6 +914,8 @@ sub init_part { if ( $::form->{part}{id} ) { return SL::DB::Part->new(id => $::form->{part}{id})->load(with => [ qw(makemodels customerprices prices translations partsgroup shop_parts shop_parts.shop) ]); + } elsif ( $::form->{id} ) { + return SL::DB::Part->new(id => $::form->{id})->load; # used by inventory tab } else { die "part_type missing" unless $::form->{part}{part_type}; return SL::DB::Part->new(part_type => $::form->{part}{part_type}); diff --git a/SL/DB/Part.pm b/SL/DB/Part.pm index 9bd9addca..9eb76df6a 100644 --- a/SL/DB/Part.pm +++ b/SL/DB/Part.pm @@ -3,7 +3,7 @@ package SL::DB::Part; use strict; use Carp; -use List::MoreUtils qw(any); +use List::MoreUtils qw(any uniq); use Rose::DB::Object::Helpers qw(as_tree); use SL::Locale::String qw(t8); @@ -363,27 +363,81 @@ sub get_simple_stock_sql { SUM(i.qty) AS qty, SUM(i.qty * p.lastcost) AS stock_value, p.unit AS unit, - LEAD(w.description) OVER pt AS wh_lead, -- to detect warehouse changes for subtotals + LEAD(w.description) OVER pt AS wh_lead, -- to detect warehouse changes for subtotals in template SUM( SUM(i.qty) ) OVER pt AS run_qty, -- running total of total qty SUM( SUM(i.qty) ) OVER wh AS wh_run_qty, -- running total of warehouse qty SUM( SUM(i.qty * p.lastcost)) OVER pt AS run_stock_value, -- running total of total stock_value SUM( SUM(i.qty * p.lastcost)) OVER wh AS wh_run_stock_value -- running total of warehouse stock_value FROM inventory i - LEFT JOIN parts p ON (p.id = i.parts_id) - LEFT JOIN warehouse w ON (i.warehouse_id = w.id) - LEFT JOIN bin b ON (i.bin_id = b.id) + LEFT JOIN parts p ON (p.id = i.parts_id) + LEFT JOIN warehouse w ON (i.warehouse_id = w.id) + LEFT JOIN bin b ON (i.bin_id = b.id) WHERE parts_id = ? GROUP BY w.description, b.description, p.unit, i.parts_id HAVING SUM(qty) != 0 - WINDOW pt AS (PARTITION BY i.parts_id ORDER BY w.description, b.description, p.unit), - wh AS (PARTITION by w.description ORDER BY w.description, b.description, p.unit) - ORDER BY w.description, b.description + WINDOW pt AS (PARTITION BY i.parts_id ORDER BY w.sortkey, b.description, p.unit), + wh AS (PARTITION by w.description ORDER BY w.sortkey, b.description, p.unit) + ORDER BY w.sortkey, b.description, p.unit SQL my $stock_info = selectall_hashref_query($::form, $self->db->dbh, $query, $self->id); return $stock_info; } +sub get_mini_journal { + my ($self) = @_; + + # inventory ids of the most recent 10 inventory trans_ids + + # duplicate code copied from SL::Controller::Inventory mini_journal, except + # for the added filter on parts_id + + my $parts_id = $self->id; + my $query = <<"SQL"; +with last_inventories as ( + select id, + trans_id, + itime + from inventory + where parts_id = $parts_id + order by itime desc + limit 20 +), +grouped_ids as ( + select trans_id, + array_agg(id) as ids + from last_inventories + group by trans_id + order by max(itime) + desc limit 10 +) +select unnest(ids) + from grouped_ids + limit 20 -- so the planner knows how many ids to expect, the cte is an optimisation fence +SQL + + my $objs = SL::DB::Manager::Inventory->get_all( + query => [ id => [ \"$query" ] ], + with_objects => [ 'parts', 'trans_type', 'bin', 'bin.warehouse' ], # prevent lazy loading in template + sort_by => 'itime DESC', + ); + # remember order of trans_ids from query, for ordering hash later + my @sorted_trans_ids = uniq map { $_->trans_id } @$objs; + + # at most 2 of them belong to a transaction and the qty determines in or out. + my %transactions; + for (@$objs) { + $transactions{ $_->trans_id }{ $_->qty > 0 ? 'in' : 'out' } = $_; + $transactions{ $_->trans_id }{base} = $_; + } + + # because the inventory transactions were built in a hash, we need to sort the + # hash by using the original sort order of the trans_ids + my @sorted = map { $transactions{$_} } @sorted_trans_ids; + + return \@sorted; +} + sub clone_and_reset_deep { my ($self) = @_; @@ -612,8 +666,9 @@ Using the LEAD(w.description) the template can check if the warehouse description is about to change, i.e. the next line will contain numbers from a different warehouse, so that a subtotal line can be added. -The last line will contain the qty total and the total stock value over all -warehouses/bins and can be used to add a line for the grand totals. +The last row will contain the running qty total (run_qty) and the running total +stock value (run_stock_value) over all warehouses/bins and can be used to add a +line for the grand totals. =item C diff --git a/doc/changelog b/doc/changelog index 02dfe497f..2762eaadc 100644 --- a/doc/changelog +++ b/doc/changelog @@ -12,6 +12,7 @@ Mittelgroße neue Features: suchen soll. Ist dieses Feature eingeschaltet, so werden auch die Kunden- bzw. Lieferanten-Artikelnummern als Spalte in den Positionen angezeigt. +- Part Controller - neuer Tab mit Lagerinformationen - was ist wo gelagert 2019-08-07 - Release 3.5.4 diff --git a/locale/de/all b/locale/de/all index 54972ae0d..470757567 100755 --- a/locale/de/all +++ b/locale/de/all @@ -3016,6 +3016,7 @@ $self->{texts} = { 'Stock Local/Shop' => 'Bestand Lokal/Online', 'Stock Qty for Date' => 'Lagerbestand am', 'Stock for part #1' => 'Bestand für Artikel #1', + 'Stock levels' => 'Lagerbestände', 'Stock value' => 'Bestandswert', 'Stocked Qty' => 'Lagermenge', 'Stocktaking' => 'Inventur', diff --git a/templates/webpages/part/_inventory.html b/templates/webpages/part/_inventory.html new file mode 100644 index 000000000..8b48b18ba --- /dev/null +++ b/templates/webpages/part/_inventory.html @@ -0,0 +1,29 @@ +[%- USE HTML %][%- USE L -%][%- USE P -%][%- USE LxERP -%][%- USE T8 -%] + +[%- IF AUTH.assert('warehouse_management', 1) -%] +

+[% 'Actions' | $T8 %]: + [% 'Stock' | $T8 %] + [% 'Transfer' | $T8 %] + [% 'Removal' | $T8 %] +

+[%- END -%] + +
+
+ + diff --git a/templates/webpages/part/_inventory_data.html b/templates/webpages/part/_inventory_data.html new file mode 100644 index 000000000..d04edd03e --- /dev/null +++ b/templates/webpages/part/_inventory_data.html @@ -0,0 +1,93 @@ +[%- USE HTML %][%- USE L -%][%- USE P -%][%- USE LxERP -%][%- USE T8 -%] + +[%- SET dec = 2 %] +[%- SET show_warehouse_subtotals = 1 %] + +
+ +

[% 'Stock levels' | $T8 %]

+ +[%- IF SELF.stock_amounts.size %] +[% 'Stock levels' | $T8 %]: + + + + + + + + + + + + [% FOREACH stock = SELF.stock_amounts %] + + + + + + + + [% IF show_warehouse_subtotals AND stock.wh_lead != stock.warehouse_description %] + + + + + + + + [% END %] + [% IF loop.last %] + + + + + + + + [% END %] + [% END %] + +
[% 'Warehouse' | $T8 %][% 'Bin' | $T8 %][% 'Qty' | $T8 %][% 'Unit' | $T8 %][% 'Stock value' | $T8 %]
[% HTML.escape(stock.warehouse_description) %][% IF stock.order_link %][% END %] + [% HTML.escape(stock.bin_description) %] + [% IF stock.order_link %][% END %] + [% LxERP.format_amount(stock.qty, dec) %][% HTML.escape(stock.unit) %][% LxERP.format_amount(stock.stock_value, 2) %]
[% HTML.escape(stock.warehouse_description) %][% LxERP.format_amount(stock.wh_run_qty, dec) %][% LxERP.format_amount(stock.wh_run_stock_value, dec) %]
[% 'Total' | $T8 %][% LxERP.format_amount(stock.run_qty, dec) %][% LxERP.format_amount(stock.run_stock_value, dec) %]
+[% ELSE %] +

[% 'No transactions yet.' | $T8 %]

+[% END %] +
+ +[% IF AUTH.assert('warehouse_management', 1) %] +
+

[% 'Journal of Last 10 Transfers' | $T8 %]

+[% 'WHJournal' | $T8 %]: +[%- IF SELF.journal.size %] + + + + + + + + + + + +[% FOREACH row = SELF.journal %] + + + + + + + + + + +[% END %] +
[% 'Date' | $T8 %][% 'Trans Type' | $T8 %][% 'Warehouse From' | $T8 %][% 'Qty' | $T8 %][% 'Unit' | $T8 %][% 'Warehouse To' | $T8 %][% 'Charge Number' | $T8 %][% 'Comment' | $T8 %]
[% row.base.itime_as_date %][% row.base.trans_type.description | $T8 %][% row.out ? row.out.bin.full_description : '-' | html %][% row.in ? row.in.qty_as_number : LxERP.format_amount(-1 * row.out.qty, 2) %][% row.base.part.unit | html %][% row.in ? row.in.bin.full_description : '-' | html %][% row.base.chargenumber | html %][% row.base.comment | html %]
+[%- ELSE %] +

[% 'No transactions yet.' | $T8 %]

+[%- END %] +
+[% END # assert warehouse_management %] diff --git a/templates/webpages/part/form.html b/templates/webpages/part/form.html index 733fb13bc..05e73f34c 100644 --- a/templates/webpages/part/form.html +++ b/templates/webpages/part/form.html @@ -38,6 +38,9 @@ [%- IF SELF.part.id %]
  • [% 'Price Rules' | $T8 %]
    • [% END %] + [%- IF (AUTH.assert('warehouse_contents', 1) AND SELF.part.id AND NOT SELF.part.is_service) %] +
  • [% 'Inventories' | $T8 %]
    • + [%- END %] [%- IF CUSTOM_VARIABLES.size %]
  • [% 'Custom Variables' | $T8 %]
    • [%- END %] @@ -75,6 +78,13 @@ [% PROCESS 'part/_shop.html' %] [%- END %] + + [%- IF AUTH.assert('warehouse_contents', 1) AND SELF.part.id AND NOT SELF.part.is_service %] +
    + [% PROCESS 'part/_inventory.html' %] +
    + [%- END %] + [%- END %] [%- IF CUSTOM_VARIABLES.size %] -- 2.20.1