X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FDB%2FPart.pm;h=1ee4f26eef1408698dd04a7d512eaa3e19c04053;hb=47577d1daa5da455e3883ed60c70875a6845233a;hp=5b5b4a2f51b31a11a3e2861f5e3377e155cf9867;hpb=dcc967a6df2daa4360c1652934186fc05e37bec5;p=kivitendo-erp.git

diff --git a/SL/DB/Part.pm b/SL/DB/Part.pm
index 5b5b4a2f5..1ee4f26ee 100644
--- a/SL/DB/Part.pm
+++ b/SL/DB/Part.pm
@@ -3,9 +3,11 @@ package SL::DB::Part;
 use strict;
 
 use Carp;
-use List::MoreUtils qw(any);
+use List::MoreUtils qw(any uniq);
+use List::Util qw(sum);
 use Rose::DB::Object::Helpers qw(as_tree);
 
+use SL::Locale::String qw(t8);
 use SL::DBUtils;
 use SL::DB::MetaSetup::Part;
 use SL::DB::Manager::Part;
@@ -17,13 +19,20 @@ use SL::DB::Helper::CustomVariables (
   module      => 'IC',
   cvars_alias => 1,
 );
-use List::Util qw(sum);
+use SL::DB::Helper::DisplayableNamePreferences (
+  title   => t8('Article'),
+  options => [ {name => 'partnumber',  title => t8('Part Number')     },
+               {name => 'description', title => t8('Description')    },
+               {name => 'notes',       title => t8('Notes')},
+               {name => 'ean',         title => t8('EAN')            }, ],
+);
+
 
 __PACKAGE__->meta->add_relationships(
   assemblies                     => {
     type         => 'one to many',
     class        => 'SL::DB::Assembly',
-    manager_args => { sort_by => 'position, oid' },
+    manager_args => { sort_by => 'position' },
     column_map   => { id => 'id' },
   },
   prices         => {
@@ -52,6 +61,7 @@ __PACKAGE__->meta->add_relationships(
     type         => 'one to many',
     class        => 'SL::DB::AssortmentItem',
     column_map   => { id => 'assortment_id' },
+    manager_args => { sort_by => 'position' },
   },
   history_entries   => {
     type            => 'one to many',
@@ -66,14 +76,22 @@ __PACKAGE__->meta->add_relationships(
     column_map   => { id => 'part_id' },
     manager_args => { with_objects => [ 'shop' ] },
   },
+  last_price_update => {
+    type         => 'one to one',
+    class        => 'SL::DB::PartsPriceHistory',
+    column_map   => { id => 'part_id' },
+    manager_args => { sort_by => 'valid_from DESC', limit => 1 },
+  },
 );
 
 __PACKAGE__->meta->initialize;
 
 __PACKAGE__->attr_html('notes');
-__PACKAGE__->attr_sorted({ unsorted => 'makemodels', position => 'sortorder' });
+__PACKAGE__->attr_sorted({ unsorted => 'makemodels',     position => 'sortorder' });
+__PACKAGE__->attr_sorted({ unsorted => 'customerprices', position => 'sortorder' });
 
 __PACKAGE__->before_save('_before_save_set_partnumber');
+__PACKAGE__->before_save('_before_save_set_assembly_weight');
 
 sub _before_save_set_partnumber {
   my ($self) = @_;
@@ -82,6 +100,16 @@ sub _before_save_set_partnumber {
   return 1;
 }
 
+sub _before_save_set_assembly_weight {
+  my ($self) = @_;
+
+  if ( $self->part_type eq 'assembly' ) {
+    my $weight_sum = $self->items_weight_sum;
+    $self->weight($self->items_weight_sum) if $weight_sum;
+  }
+  return 1;
+}
+
 sub items {
   my ($self) = @_;
 
@@ -343,8 +371,90 @@ sub get_simple_stock {
   sub bin       { require SL::DB::Bin;       SL::DB::Manager::Bin      ->find_by_or_create(id => $_[0]->{bin_id}) }
 }
 
-sub displayable_name {
-  join ' ', grep $_, map $_[0]->$_, qw(partnumber description);
+sub get_simple_stock_sql {
+  my ($self, %params) = @_;
+
+  return [] unless $self->id;
+
+  my $query = <<SQL;
+     SELECT w.description                         AS warehouse_description,
+            b.description                         AS bin_description,
+            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 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)
+      WHERE parts_id = ?
+   GROUP BY w.description, w.sortkey, b.description, p.unit, i.parts_id
+     HAVING SUM(qty) != 0
+     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" ] ],                           # make emacs happy "
+    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 {
@@ -420,6 +530,14 @@ sub items_lastcost_sum {
   sum map { $_->linetotal_lastcost } @{$self->items};
 };
 
+sub items_weight_sum {
+  my ($self) = @_;
+
+  return unless $self->is_assembly;
+  return unless $self->items;
+  sum map { $_->linetotal_weight} @{$self->items};
+};
+
 1;
 
 __END__
@@ -562,6 +680,23 @@ Used to set the accounting information from a L<SL:DB::Buchungsgruppe> object.
 Please note, that this is a write only accessor, the original Buchungsgruppe can
 not be retrieved from an article once set.
 
+=item C<get_simple_stock_sql>
+
+Fetches the qty and the stock value for the current part for each bin and
+warehouse where the part is in stock (or rather different from 0, might be
+negative).
+
+Runs some additional window functions to add the running totals (total running
+total and total per warehouse) for qty and stock value to each line.
+
+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 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>
 
 Non-recursive lastcost sum of all the items in an assembly or assortment.