X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FWH.pm;h=bcdffa5118abc6cc6c30b2882d91ede7246d3acb;hb=2b31bcbfb48ad1ef8da19703e1a2d1fa832026c6;hp=df5bad90935c0d8451a5a65489d17a615f6c3687;hpb=65d2537d658b99b005a18c6663bc1293b41a1d83;p=kivitendo-erp.git

diff --git a/SL/WH.pm b/SL/WH.pm
index df5bad909..bcdffa511 100644
--- a/SL/WH.pm
+++ b/SL/WH.pm
@@ -312,9 +312,7 @@ sub transfer_assembly {
    }
     # gibt die Fehlermeldung zurück. A.) Keine Teile definiert
     #                                B.) Artikel und Anzahl der fehlenden Teile/Dienstleistungen
-    if ($kannNichtFertigen) {
-      return 0;
-    }
+    die "<br><br>" . $kannNichtFertigen if ($kannNichtFertigen);
 
     # soweit alles gut. Jetzt noch die wirkliche Lagerbewegung für das Erzeugnis ausführen ...
     my $transferAssemblySQL = qq|INSERT INTO inventory (parts_id, warehouse_id, bin_id, chargenumber, bestbefore,
@@ -420,10 +418,30 @@ 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'],
+    'chargenumber'   => ['chargenumber'],
+  );
+
+  $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 " : '';
 
@@ -466,6 +484,8 @@ sub get_warehouse_journal {
      };
 
   $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.
@@ -476,12 +496,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
@@ -529,9 +549,20 @@ 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|;
+
+  my @all_vars = (@filter_vars,@filter_vars,@filter_vars);
+
+  if ($filter{limit}) {
+    $query .= " LIMIT ?";
+    push @all_vars,$filter{limit};
+  }
+  if ($filter{offset}) {
+    $query .= " OFFSET ?";
+    push @all_vars, $filter{offset};
+  }
 
-  my $sth = prepare_execute_query($form, $dbh, $query, @filter_vars, @filter_vars, @filter_vars);
+  my $sth = prepare_execute_query($form, $dbh, $query, @all_vars);
 
   my ($h_oe_id, $q_oe_id);
   if ($form->{l_oe_id}) {
@@ -700,6 +731,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});
@@ -763,6 +799,7 @@ 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';
@@ -784,7 +821,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
@@ -792,9 +829,17 @@ 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|;
 
-  my $sth = prepare_execute_query($form, $dbh, $query, @filter_vars);
+  if ($filter{limit}) {
+    $query .= " LIMIT ?";
+    push @filter_vars,$filter{limit};
+  }
+  if ($filter{offset}) {
+    $query .= " OFFSET ?";
+    push @filter_vars, $filter{offset};
+  }
+  my $sth = prepare_execute_query($form, $dbh, $query, @filter_vars );
 
   my (%non_empty_bins, @all_fields, @contents);