X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FDB%2FPart.pm;h=89261af01541a506132d75a431cdde9aace7029f;hb=3782a90c336bc6c506f572e607c8526cb5e79ea3;hp=6dde98f4d388238458b10e36643b2d48e5e890e4;hpb=78d813b90a293eafa7e17f711edeef5551f937d3;p=kivitendo-erp.git

diff --git a/SL/DB/Part.pm b/SL/DB/Part.pm
index 6dde98f4d..89261af01 100644
--- a/SL/DB/Part.pm
+++ b/SL/DB/Part.pm
@@ -3,26 +3,36 @@ 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;
 use SL::DB::Chart;
 use SL::DB::Helper::AttrHTML;
+use SL::DB::Helper::AttrSorted;
 use SL::DB::Helper::TransNumberGenerator;
 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         => {
@@ -37,6 +47,11 @@ __PACKAGE__->meta->add_relationships(
     manager_args => { sort_by => 'sortorder' },
     column_map   => { id => 'parts_id' },
   },
+  customerprices => {
+    type         => 'one to many',
+    class        => 'SL::DB::PartCustomerPrice',
+    column_map   => { id => 'parts_id' },
+  },
   translations   => {
     type         => 'one to many',
     class        => 'SL::DB::Translation',
@@ -46,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',
@@ -60,13 +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 => '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) = @_;
@@ -75,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) = @_;
 
@@ -104,6 +139,13 @@ sub validate {
   push @errors, $::locale->text('The unit is missing.')           unless $self->unit;
   push @errors, $::locale->text('The buchungsgruppe is missing.') unless $self->buchungsgruppen_id or $self->buchungsgruppe;
 
+  if ( $::instance_conf->get_partsgroup_required
+       && ( !$self->partsgroup_id or ( $self->id && !$self->partsgroup_id && $self->partsgroup ) ) ) {
+    # when unsetting an existing partsgroup in the interface, $self->partsgroup_id will be undef but $self->partsgroup will still have a value
+    # this needs to be checked, as partsgroup dropdown has an empty value
+    push @errors, $::locale->text('The partsgroup is missing.');
+  }
+
   unless ( $self->id ) {
     push @errors, $::locale->text('The partnumber already exists.') if SL::DB::Manager::Part->get_all_count(where => [ partnumber => $self->partnumber ]);
   };
@@ -336,8 +378,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 {
@@ -413,6 +537,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__
@@ -555,6 +687,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.