marei Druckvorlagen: Tote Datei entfernt

[mfinanz.git] / SL / DB / Part.pm

diff --git a/SL/DB/Part.pm b/SL/DB/Part.pm
index 9bd9addcaf7ee2bed9ae749f8c046adbbbed2cda..1ee4f26eef1408698dd04a7d512eaa3e19c04053 100644 (file)
--- a/SL/DB/Part.pm
+++ b/SL/DB/Part.pm
@@ -3,7 +3,8 @@ package SL::DB::Part;
 use strict;
 
 use Carp;
 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 Rose::DB::Object::Helpers qw(as_tree);
 
 use SL::Locale::String qw(t8);


@@ -26,13 +27,12 @@ use SL::DB::Helper::DisplayableNamePreferences (
                {name => 'ean',         title => t8('EAN')            }, ],
 );
 
                {name => 'ean',         title => t8('EAN')            }, ],
 );
 

-use List::Util qw(sum);

 
 __PACKAGE__->meta->add_relationships(
   assemblies                     => {
     type         => 'one to many',
     class        => 'SL::DB::Assembly',
 
 __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         => {
     column_map   => { id => 'id' },
   },
   prices         => {


@@ -61,6 +61,7 @@ __PACKAGE__->meta->add_relationships(
     type         => 'one to many',
     class        => 'SL::DB::AssortmentItem',
     column_map   => { id => 'assortment_id' },
     type         => 'one to many',
     class        => 'SL::DB::AssortmentItem',
     column_map   => { id => 'assortment_id' },

+    manager_args => { sort_by => 'position' },

   },
   history_entries   => {
     type            => 'one to many',
   },
   history_entries   => {
     type            => 'one to many',


@@ -75,14 +76,22 @@ __PACKAGE__->meta->add_relationships(
     column_map   => { id => 'part_id' },
     manager_args => { with_objects => [ 'shop' ] },
   },
     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__->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_partnumber');

+__PACKAGE__->before_save('_before_save_set_assembly_weight');

 
 sub _before_save_set_partnumber {
   my ($self) = @_;
 
 sub _before_save_set_partnumber {
   my ($self) = @_;


@@ -91,6 +100,16 @@ sub _before_save_set_partnumber {
   return 1;
 }
 
   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) = @_;
 
 sub items {
   my ($self) = @_;
 


@@ -363,27 +382,81 @@ sub get_simple_stock_sql {
             SUM(i.qty)                            AS qty,
             SUM(i.qty * p.lastcost)               AS stock_value,
             p.unit                                AS unit,
             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
             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 = ?
       WHERE parts_id = ?

-   GROUP BY w.description, b.description, p.unit, i.parts_id
+   GROUP BY w.description, w.sortkey, b.description, p.unit, i.parts_id

      HAVING SUM(qty) != 0
      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;
 }
 
 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 {
   my ($self) = @_;
 
 sub clone_and_reset_deep {
   my ($self) = @_;
 


@@ -457,6 +530,14 @@ sub items_lastcost_sum {
   sum map { $_->linetotal_lastcost } @{$self->items};
 };
 
   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__
 1;
 
 __END__


@@ -612,8 +693,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.
 
 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>
 
 
 =item C<items_lastcost_sum>