Revision 20004cac
Von G. Richardson vor mehr als 5 Jahren hinzugefügt
| SL/Controller/Part.pm | ||
|---|---|---|
|
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
|
||
| ... | ... | |
|
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) = @_;
|
||
|
|
||
| ... | ... | |
|
|
||
|
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});
|
||
| SL/DB/Part.pm | ||
|---|---|---|
|
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);
|
||
| ... | ... | |
|
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) = @_;
|
||
|
|
||
| ... | ... | |
|
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<items_lastcost_sum>
|
||
|
|
||
| doc/changelog | ||
|---|---|---|
|
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
|
||
|
|
||
| locale/de/all | ||
|---|---|---|
|
'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',
|
||
| templates/webpages/part/_inventory.html | ||
|---|---|---|
|
[%- USE HTML %][%- USE L -%][%- USE P -%][%- USE LxERP -%][%- USE T8 -%]
|
||
|
|
||
|
[%- IF AUTH.assert('warehouse_management', 1) -%]
|
||
|
<p>
|
||
|
[% 'Actions' | $T8 %]:
|
||
|
<span><a href="controller.pl?action=Inventory/stock_in&part_id=[% HTML.escape(SELF.part.id)%]&select_default_bin=1">[% 'Stock' | $T8 %]</a></span>
|
||
|
<span><a href="wh.pl?trans_type=transfer&action=transfer_warehouse_selection&parts_id=[% HTML.escape(SELF.part.id) %]">[% 'Transfer' | $T8 %]</a></span>
|
||
|
<span><a href="wh.pl?action=transfer_warehouse_selection&trans_type=removal&parts_id=[% HTML.escape(SELF.part.id) %]">[% 'Removal' | $T8 %]</a></span>
|
||
|
</p>
|
||
|
[%- END -%]
|
||
|
|
||
|
<div id="inventory_data">
|
||
|
</div>
|
||
|
|
||
|
<script type='text/javascript'>
|
||
|
$(function() {
|
||
|
$('.tabwidget').on('tabsbeforeactivate', function(event, ui){
|
||
|
if (ui.newPanel.attr('id') == 'inventory') {
|
||
|
$.ajax({
|
||
|
url: 'controller.pl?action=Part/inventory&id=[% SELF.part.id %]',
|
||
|
success: function (html) {
|
||
|
$("#inventory_data").html(html);
|
||
|
},
|
||
|
});
|
||
|
}
|
||
|
return 1;
|
||
|
});
|
||
|
});
|
||
|
</script>
|
||
| templates/webpages/part/_inventory_data.html | ||
|---|---|---|
|
[%- USE HTML %][%- USE L -%][%- USE P -%][%- USE LxERP -%][%- USE T8 -%]
|
||
|
|
||
|
[%- SET dec = 2 %]
|
||
|
[%- SET show_warehouse_subtotals = 1 %]
|
||
|
|
||
|
<div id="stock_levels">
|
||
|
|
||
|
<h3>[% 'Stock levels' | $T8 %]</h3>
|
||
|
|
||
|
[%- IF SELF.stock_amounts.size %]
|
||
|
<a href="wh.pl?action=report&partnumber=[% HTML.escape(SELF.part.partnumber) %]">[% 'Stock levels' | $T8 %]</a>:
|
||
|
<table>
|
||
|
<thead>
|
||
|
<tr class='listheading'>
|
||
|
<th>[% 'Warehouse' | $T8 %]</th>
|
||
|
<th>[% 'Bin' | $T8 %]</th>
|
||
|
<th>[% 'Qty' | $T8 %]</th>
|
||
|
<th>[% 'Unit' | $T8 %]</th>
|
||
|
<th>[% 'Stock value' | $T8 %]</th>
|
||
|
</tr>
|
||
|
</thead>
|
||
|
<tbody>
|
||
|
[% FOREACH stock = SELF.stock_amounts %]
|
||
|
<tr class='listrow'>
|
||
|
<td >[% HTML.escape(stock.warehouse_description) %]</td>
|
||
|
<td >[% IF stock.order_link %]<a target="_blank" href="[% stock.order_link %]">[% END %]
|
||
|
[% HTML.escape(stock.bin_description) %]
|
||
|
[% IF stock.order_link %]</a>[% END %]
|
||
|
</td>
|
||
|
<td class='numeric'>[% LxERP.format_amount(stock.qty, dec) %]</td>
|
||
|
<td >[% HTML.escape(stock.unit) %]</td>
|
||
|
<td class='numeric'>[% LxERP.format_amount(stock.stock_value, 2) %]</td>
|
||
|
</tr>
|
||
|
[% IF show_warehouse_subtotals AND stock.wh_lead != stock.warehouse_description %]
|
||
|
<tr class='listheading'>
|
||
|
<th >[% HTML.escape(stock.warehouse_description) %]</th>
|
||
|
<td></td>
|
||
|
<td class='numeric bold'>[% LxERP.format_amount(stock.wh_run_qty, dec) %]</td>
|
||
|
<td></td>
|
||
|
<td class='numeric bold'>[% LxERP.format_amount(stock.wh_run_stock_value, dec) %]</td>
|
||
|
</tr>
|
||
|
[% END %]
|
||
|
[% IF loop.last %]
|
||
|
<tr class='listheading'>
|
||
|
<th>[% 'Total' | $T8 %]</th>
|
||
|
<td></td>
|
||
|
<td class='numeric bold'>[% LxERP.format_amount(stock.run_qty, dec) %]</td>
|
||
|
<td></td>
|
||
|
<td class='numeric bold'>[% LxERP.format_amount(stock.run_stock_value, dec) %]</td>
|
||
|
</tr>
|
||
|
[% END %]
|
||
|
[% END %]
|
||
|
</tbody>
|
||
|
</table>
|
||
|
[% ELSE %]
|
||
|
<p>[% 'No transactions yet.' | $T8 %]</p>
|
||
|
[% END %]
|
||
|
</div>
|
||
|
|
||
|
[% IF AUTH.assert('warehouse_management', 1) %]
|
||
|
<div>
|
||
|
<h3>[% 'Journal of Last 10 Transfers' | $T8 %]</h3>
|
||
|
<a href="wh.pl?action=journal&partnumber=[% HTML.escape(SELF.part.partnumber) %]">[% 'WHJournal' | $T8 %]</a>:
|
||
|
[%- IF SELF.journal.size %]
|
||
|
<table>
|
||
|
<tr class='listheading'>
|
||
|
<th>[% 'Date' | $T8 %]</th>
|
||
|
<th>[% 'Trans Type' | $T8 %]</th>
|
||
|
<th>[% 'Warehouse From' | $T8 %]</th>
|
||
|
<th>[% 'Qty' | $T8 %]</th>
|
||
|
<th>[% 'Unit' | $T8 %]</th>
|
||
|
<th>[% 'Warehouse To' | $T8 %]</th>
|
||
|
<th>[% 'Charge Number' | $T8 %]</th>
|
||
|
<th>[% 'Comment' | $T8 %]</th>
|
||
|
</tr>
|
||
|
[% FOREACH row = SELF.journal %]
|
||
|
<tr class='listrow'>
|
||
|
<td>[% row.base.itime_as_date %]</td>
|
||
|
<td>[% row.base.trans_type.description | $T8 %]</td>
|
||
|
<td>[% row.out ? row.out.bin.full_description : '-' | html %]</td>
|
||
|
<td class='numeric'>[% row.in ? row.in.qty_as_number : LxERP.format_amount(-1 * row.out.qty, 2) %]</td>
|
||
|
<td>[% row.base.part.unit | html %]</td>
|
||
|
<td>[% row.in ? row.in.bin.full_description : '-' | html %]</td>
|
||
|
<td>[% row.base.chargenumber | html %]</td>
|
||
|
<td>[% row.base.comment | html %]</td>
|
||
|
</tr>
|
||
|
[% END %]
|
||
|
</table>
|
||
|
[%- ELSE %]
|
||
|
<p>[% 'No transactions yet.' | $T8 %]</p>
|
||
|
[%- END %]
|
||
|
</div>
|
||
|
[% END # assert warehouse_management %]
|
||
| templates/webpages/part/form.html | ||
|---|---|---|
|
[%- IF SELF.part.id %]
|
||
|
<li><a href="#price_rules">[% 'Price Rules' | $T8 %]</a></li>
|
||
|
[% END %]
|
||
|
[%- IF (AUTH.assert('warehouse_contents', 1) AND SELF.part.id AND NOT SELF.part.is_service) %]
|
||
|
<li><a href="#inventory">[% 'Inventories' | $T8 %]</a></li>
|
||
|
[%- END %]
|
||
|
[%- IF CUSTOM_VARIABLES.size %]
|
||
|
<li><a href="#custom_variables">[% 'Custom Variables' | $T8 %]</a></li>
|
||
|
[%- END %]
|
||
| ... | ... | |
|
[% PROCESS 'part/_shop.html' %]
|
||
|
</div>
|
||
|
[%- END %]
|
||
|
|
||
|
[%- IF AUTH.assert('warehouse_contents', 1) AND SELF.part.id AND NOT SELF.part.is_service %]
|
||
|
<div id="inventory">
|
||
|
[% PROCESS 'part/_inventory.html' %]
|
||
|
</div>
|
||
|
[%- END %]
|
||
|
|
||
|
[%- END %]
|
||
|
|
||
|
[%- IF CUSTOM_VARIABLES.size %]
|
||
Auch abrufbar als: Unified diff
Part Controller - neuer Tab mit Lagerinformationen
(Derzeit gibt es im Template Variabeln um Zwischensummen und
Nachkommastellen zu kontrollieren)
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.