X-Git-Url: http://wagnertech.de/gitweb/gitweb.cgi/mfinanz.git/blobdiff_plain/6858b66bb7d401245459f5148c2c1ea28f71bfca..f217d072d76183bc07723dcc29503b732bd2022d:/SL/DB/Manager/Part.pm

diff --git a/SL/DB/Manager/Part.pm b/SL/DB/Manager/Part.pm
index 6c781a9fe..b101d1a14 100644
--- a/SL/DB/Manager/Part.pm
+++ b/SL/DB/Manager/Part.pm
@@ -3,6 +3,9 @@ package SL::DB::Manager::Part;
 use strict;
 
 use SL::DB::Helper::Manager;
+use SL::DB::Helper::Sorted;
+use SL::DB::Helper::Paginated;
+use SL::DB::Helper::Filtered;
 use base qw(SL::DB::Helper::Manager);
 
 use Carp;
@@ -12,31 +15,55 @@ use SL::MoreCommon qw(listify);
 sub object_class { 'SL::DB::Part' }
 
 __PACKAGE__->make_manager_methods;
+__PACKAGE__->add_filter_specs(
+  part_type => sub {
+    my ($key, $value, $prefix) = @_;
+    return __PACKAGE__->type_filter($value, $prefix);
+  },
+  all => sub {
+    my ($key, $value, $prefix) = @_;
+    return or => [ map { $prefix . $_ => $value } qw(partnumber description ean) ]
+  },
+  all_with_makemodel => sub {
+    my ($key, $value, $prefix) = @_;
+    return or => [ map { $prefix . $_ => $value } qw(partnumber description ean makemodels.model) ],
+      $prefix . 'makemodels';
+  },
+  all_with_customer_partnumber => sub {
+    my ($key, $value, $prefix) = @_;
+    return or => [ map { $prefix . $_ => $value } qw(partnumber description ean customerprices.customer_partnumber) ],
+      $prefix . 'customerprices';
+  },
+);
 
 sub type_filter {
-  my ($class, $type) = @_;
+  my ($class, $type, $prefix) = @_;
 
   return () unless $type;
 
-  my @types = listify($type);
+  $prefix //= '';
+
+  # this is to make selections like part_type => { part => 1, service => 1 } work
+  if ('HASH' eq ref $type) {
+    $type = [ grep { $type->{$_} } keys %$type ];
+  }
+
+  my @types = grep { $_ } listify($type);
   my @filter;
 
   for my $type (@types) {
     if ($type =~ m/^part/) {
-      push @filter, (and => [ or                    => [ assembly => 0, assembly => undef ],
-                       '!inventory_accno_id' => 0,
-                       '!inventory_accno_id' => undef,
-                     ]);
+      push @filter, ($prefix . part_type => 'part');
     } elsif ($type =~ m/^service/) {
-      push @filter, (and => [ or => [ assembly           => 0, assembly           => undef ],
-                       or => [ inventory_accno_id => 0, inventory_accno_id => undef ],
-                     ]);
-    } elsif ($type =~ m/^assembl/) {
-      push @filter, (assembly => 1);
+      push @filter, ($prefix . part_type => 'service');
+    } elsif ($type =~ m/^assembly/) {
+      push @filter, ($prefix . part_type => 'assembly');
+    } elsif ($type =~ m/^assortment/) {
+      push @filter, ($prefix . part_type => 'assortment');
     }
   }
 
-  return @filter ? (or => \@filter) : ();
+  return @filter > 2 ? (or => \@filter) : @filter;
 }
 
 sub get_ordered_qty {
@@ -51,10 +78,9 @@ sub get_ordered_qty {
     FROM orderitems oi
     LEFT JOIN oe ON (oi.trans_id = oe.id)
     WHERE (oi.parts_id IN ($placeholders))
-      AND (NOT COALESCE(oe.quotation, FALSE))
+      AND oe.record_type = 'purchase_order'
       AND (NOT COALESCE(oe.closed,    FALSE))
       AND (NOT COALESCE(oe.delivered, FALSE))
-      AND (COALESCE(oe.vendor_id, 0) <> 0)
     GROUP BY oi.parts_id
 SQL
 
@@ -64,6 +90,85 @@ SQL
   return %qty_by_id;
 }
 
+sub get_open_ordered_qty {
+  my $class    = shift;
+  my $part_id  = shift;
+  return () unless $part_id;
+
+  my $query = <<SQL;
+WITH
+open_qty AS (
+  SELECT parts_id, sum(oi.qty) as sum
+  FROM orderitems oi
+  LEFT OUTER JOIN oe o ON (oi.trans_id = o.id)
+  WHERE
+    oi.parts_id = ?
+    AND (o.record_type = 'purchase_order')
+    AND (NOT COALESCE(o.closed,    FALSE))
+    AND (NOT COALESCE(o.delivered, FALSE))
+    AND (COALESCE(o.vendor_id, 0) <> 0)
+  GROUP BY oi.parts_id
+),
+
+open_orderitems_ids AS (
+  SELECT oi.id, parts_id
+  FROM orderitems oi
+  LEFT OUTER JOIN oe o ON (oi.trans_id = o.id)
+  WHERE
+    oi.parts_id = ?
+    AND (o.record_type = 'purchase_order')
+    AND (NOT COALESCE(o.closed,    FALSE))
+    AND (NOT COALESCE(o.delivered, FALSE))
+    AND (o.vendor_id is not null)
+),
+
+delivered_qty AS (
+  SELECT parts_id, sum(qty) AS sum
+  FROM delivery_order_items
+  WHERE id IN (
+    SELECT to_id from record_links
+    WHERE
+      from_id IN ( SELECT id FROM open_orderitems_ids)
+      AND from_table = 'orderitems'
+      AND to_table = 'delivery_order_items'
+  ) AND parts_id = ?
+  GROUP BY parts_id
+),
+
+open_ordered_qty AS (
+  SELECT
+    oq.parts_id,
+    oq.sum AS ordered_sum,
+    COALESCE(dq.sum,0.00) AS sum,
+    sum(COALESCE(oq.sum,0.00) - COALESCE(dq.sum,0.00)) AS open_qty
+  FROM open_qty oq
+  LEFT JOIN delivered_qty dq ON dq.parts_id = oq.parts_id
+  GROUP BY oq.parts_id, oq.sum, dq.sum
+)
+
+SELECT open_qty FROM open_ordered_qty
+
+SQL
+
+  my ($open_qty) = selectfirst_array_query(
+    $::form, $class->object_class->init_db->dbh,
+    $query, $part_id, $part_id, $part_id
+  );
+
+  $open_qty ||= 0;
+  return $open_qty
+}
+
+sub _sort_spec {
+  (
+    default  => [ 'partnumber', 1 ],
+    columns  => {
+      SIMPLE => 'ALL',
+    },
+    nulls    => {},
+  );
+}
+
 1;
 __END__