X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FWH.pm;h=78a1442e56f34febec278459986a8235c138a039;hb=3c938e03dbb186f9c163e19473e9572f7fa16aea;hp=6f693d2fa7d2bbed587a9e2b8525849cd44afff2;hpb=1b9a64fa292f375c82b4af788d0606354bc4e8ff;p=kivitendo-erp.git

diff --git a/SL/WH.pm b/SL/WH.pm
index 6f693d2fa..78a1442e5 100644
--- a/SL/WH.pm
+++ b/SL/WH.pm
@@ -369,6 +369,11 @@ sub get_warehouse_journal {
     push @filter_vars, like($filter{description});
   }
 
+  if ($filter{classification_id}) {
+    push @filter_ary, "p.classification_id = ?";
+    push @filter_vars, $filter{classification_id};
+  }
+
   if ($filter{chargenumber}) {
     push @filter_ary, "i1.chargenumber ILIKE ?";
     push @filter_vars, like($filter{chargenumber});
@@ -415,10 +420,29 @@ sub get_warehouse_journal {
   my $sort_order = $form->{order};
 
   $sort_col      = $filter{sort}         unless $sort_col;
+  $sort_col      = 'shippingdate'        if     $sort_col eq 'date';
   $sort_order    = ($sort_col = 'shippingdate') unless $sort_col;
-  $sort_col      = 'shippingdate'               if     $sort_col eq 'date';
-  $sort_order    = $filter{order}        unless $sort_order;
-  my $sort_spec  = "${sort_col} " . ($sort_order ? " DESC" : " ASC");
+
+  my %orderspecs = (
+    'shippingdate'   => ['shippingdate', 'r_itime', 'r_parts_id'],
+    'bin_to'         => ['bin_to', 'r_itime', 'r_parts_id'],
+    'bin_from'       => ['bin_from', 'r_itime', 'r_parts_id'],
+    'warehouse_to'   => ['warehouse_to, r_itime, r_parts_id'],
+    'warehouse_from' => ['warehouse_from, r_itime, r_parts_id'],
+    'partnumber'     => ['partnumber'],
+    'partdescription'=> ['partdescription'],
+    'partunit'       => ['partunit, r_itime, r_parts_id'],
+    'qty'            => ['qty, r_itime, r_parts_id'],
+    'oe_id'          => ['oe_id'],
+    'comment'        => ['comment'],
+    'trans_type'     => ['trans_type'],
+    'employee'       => ['employee'],
+    'projectnumber'  => ['projectnumber'],
+  );
+
+  $sort_order    = $filter{order}  unless $sort_order;
+  my $ASC = ($sort_order ? " DESC" : " ASC");
+  my $sort_spec  = join("$ASC , ", @{$orderspecs{$sort_col}}). " $ASC";
 
   my $where_clause = @filter_ary ? join(" AND ", @filter_ary) . " AND " : '';
 
@@ -427,6 +451,8 @@ sub get_warehouse_journal {
      "qty"                  => "ABS(SUM(i1.qty))",
      "partnumber"           => "p.partnumber",
      "partdescription"      => "p.description",
+     "classification_id"    => "p.classification_id",
+     "part_type"            => "p.part_type",
      "bindescription"       => "b.description",
      "chargenumber"         => "i1.chargenumber",
      "bestbefore"           => "i1.bestbefore",
@@ -458,6 +484,9 @@ sub get_warehouse_journal {
      "warehouse_from"       => "'$filter{na}'",
      };
 
+  $form->{l_classification_id}  = 'Y';
+  $form->{l_part_type}          = 'Y';
+  $form->{l_itime}              = 'Y';
   $form->{l_invoice_id} = $form->{l_oe_id} if $form->{l_oe_id};
 
   # build the select clauses.
@@ -468,12 +497,12 @@ sub get_warehouse_journal {
   }
 
   my $group_clause = join ", ", map { +/^l_/; "r_$'" }
-        ( grep( { !/qty$/ and /^l_/ and $form->{$_} eq 'Y' } keys %$form), qw(l_parts_id l_partunit l_shippingdate) );
+        ( grep( { !/qty$/ and /^l_/ and $form->{$_} eq 'Y' } keys %$form), qw(l_parts_id l_partunit l_shippingdate l_itime) );
 
   $where_clause = defined($where_clause) ? $where_clause : '';
 
   my $query =
-  qq|SELECT DISTINCT $select{trans}
+  qq|SELECT * FROM (SELECT DISTINCT $select{trans}
     FROM inventory i1
     LEFT JOIN inventory i2 ON i1.trans_id = i2.trans_id
     LEFT JOIN parts p ON i1.parts_id = p.id
@@ -521,7 +550,10 @@ sub get_warehouse_journal {
     WHERE $where_clause i1.qty > 0 AND
           i1.trans_id IN ( SELECT i.trans_id FROM inventory i GROUP BY i.trans_id HAVING COUNT(i.trans_id) = 1 )
     GROUP BY $group_clause
-    ORDER BY r_${sort_spec}|;
+    ORDER BY r_${sort_spec}) AS lines WHERE r_qty>0|;
+
+  $query .= " LIMIT $filter{limit}"    if $filter{limit} ;
+  $query .= " OFFSET $filter{offset}"  if $filter{offset} ;
 
   my $sth = prepare_execute_query($form, $dbh, $query, @filter_vars, @filter_vars, @filter_vars);
 
@@ -615,6 +647,7 @@ SQL
 #  - warehouse_id - will return matches with this warehouse_id only
 #  - partnumber   - will return only matches where the given string is a substring of the partnumber
 #  - partsid      - will return matches with this parts_id only
+#  - classification_id - will return matches with this parts with this classification only
 #  - description  - will return only matches where the given string is a substring of the description
 #  - chargenumber - will return only matches where the given string is a substring of the chargenumber
 #  - bestbefore   - will return only matches with this bestbefore date
@@ -666,6 +699,11 @@ sub get_warehouse_report {
     push @filter_vars, like($filter{partnumber});
   }
 
+  if ($filter{classification_id}) {
+    push @filter_ary, "p.classification_id = ?";
+    push @filter_vars, $filter{classification_id};
+  }
+
   if ($filter{description}) {
     push @filter_ary,  "p.description ILIKE ?";
     push @filter_vars, like($filter{description});
@@ -686,6 +724,11 @@ sub get_warehouse_report {
     push @filter_vars, trim($form->{bestbefore});
   }
 
+  if ($filter{classification_id}) {
+    push @filter_ary, "p.classification_id = ?";
+    push @filter_vars, $filter{classification_id};
+  }
+
   if ($filter{ean}) {
     push @filter_ary,  "p.ean ILIKE ?";
     push @filter_vars, like($filter{ean});
@@ -738,6 +781,8 @@ sub get_warehouse_report {
      "warehouseid"          => "i.warehouse_id",
      "partnumber"           => "p.partnumber",
      "partdescription"      => "p.description",
+     "classification_id"    => "p.classification_id",
+     "part_type"            => "p.part_type",
      "bindescription"       => "b.description",
      "binid"                => "b.id",
      "chargenumber"         => "i.chargenumber",
@@ -747,7 +792,11 @@ sub get_warehouse_report {
      "warehousedescription" => "w.description",
      "partunit"             => "p.unit",
      "stock_value"          => "p.lastcost / COALESCE(pfac.factor, 1)",
+     "purchase_price"       => "p.lastcost",
   );
+  $form->{l_classification_id}  = 'Y';
+  $form->{l_part_type}          = 'Y';
+
   my $select_clause = join ', ', map { +/^l_/; "$select_tokens{$'} AS $'" }
         ( grep( { !/qty/ and /^l_/ and $form->{$_} eq 'Y' } keys %$form),
           qw(l_parts_id l_qty l_partunit) );
@@ -765,7 +814,7 @@ sub get_warehouse_report {
           qw(l_parts_id l_qty l_partunit) );
 
   my $query =
-    qq|SELECT $select_clause
+    qq|SELECT * FROM ( SELECT $select_clause
       FROM inventory i
       LEFT JOIN parts     p ON i.parts_id     = p.id
       LEFT JOIN bin       b ON i.bin_id       = b.id
@@ -773,7 +822,10 @@ sub get_warehouse_report {
       $joins
       WHERE $where_clause
       GROUP BY $group_clause
-      ORDER BY $sort_spec|;
+      ORDER BY $sort_spec ) AS lines WHERE qty<>0|;
+
+  $query .= " LIMIT $filter{limit}"    if $filter{limit} ;
+  $query .= " OFFSET $filter{offset}"  if $filter{offset} ;
 
   my $sth = prepare_execute_query($form, $dbh, $query, @filter_vars);