projekt kivitendo

  'scalar'                => [ qw(warehouse bin stock_amounts journal) ],

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 });

};

  } elsif ( $::form->{id} ) {

    return SL::DB::Part->new(id => $::form->{id})->load; # used by inventory tab

use List::MoreUtils qw(any uniq);

            LEAD(w.description)           OVER pt AS wh_lead,            -- to detect warehouse changes for subtotals in template

            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)

     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

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;

}

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.

- Part Controller - neuer Tab mit Lagerinformationen - was ist wo gelagert

  'Stock levels'                => 'Lagerbestände',

[%- 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>

[%- 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 %]

    [%- 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 AUTH.assert('warehouse_contents', 1) AND SELF.part.id AND NOT SELF.part.is_service %]

   <div id="inventory">

    [% PROCESS 'part/_inventory.html' %]

   </div>

   [%- END %]