X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FWH.pm;h=e596abc9445880cdb87cb2d972f1b140c7088e68;hb=703a7cf8dd55983761032658a0ac8de65fbda144;hp=89a937f9ac932d3f58e2c3f4736e5fa03dcfef20;hpb=98b64fe1e380c232428d63cea0eb5f44b1d1a2c3;p=kivitendo-erp.git

diff --git a/SL/WH.pm b/SL/WH.pm
index 89a937f9a..e596abc94 100644
--- a/SL/WH.pm
+++ b/SL/WH.pm
@@ -25,7 +25,8 @@
 # GNU General Public License for more details.
 # You should have received a copy of the GNU General Public License
 # along with this program; if not, write to the Free Software
-# Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
+# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,
+# MA 02110-1335, USA.
 #======================================================================
 #
 #  Warehouse module
@@ -311,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,
@@ -368,6 +367,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});
@@ -414,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 " : '';
 
@@ -426,6 +450,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",
@@ -457,6 +483,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.
@@ -467,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
@@ -520,26 +549,24 @@ 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}) {
     $q_oe_id = <<SQL;
-      SELECT oe.id AS id,
-        CASE WHEN oe.quotation THEN oe.quonumber ELSE oe.ordnumber END AS number,
-        CASE
-          WHEN oe.customer_id IS NOT NULL AND     COALESCE(oe.quotation, FALSE) THEN 'sales_quotation'
-          WHEN oe.customer_id IS NOT NULL AND NOT COALESCE(oe.quotation, FALSE) THEN 'sales_order'
-          WHEN oe.customer_id IS     NULL AND     COALESCE(oe.quotation, FALSE) THEN 'request_quotation'
-          ELSE                                                                       'purchase_order'
-        END AS type
-      FROM oe
-      WHERE oe.id = ?
-
-      UNION
-
       SELECT dord.id AS id, dord.donumber AS number,
         CASE
           WHEN dord.customer_id IS NULL THEN 'purchase_delivery_order'
@@ -550,18 +577,6 @@ sub get_warehouse_journal {
 
       UNION
 
-      SELECT ar.id AS id, ar.invnumber AS number, 'sales_invoice' AS type
-      FROM ar
-      WHERE ar.id = ?
-
-      UNION
-
-      SELECT ap.id AS id, ap.invnumber AS number, 'purchase_invoice' AS type
-      FROM ap
-      WHERE ap.id = ?
-
-      UNION
-
       SELECT ar.id AS id, ar.invnumber AS number, 'sales_invoice' AS type
       FROM ar
       WHERE ar.id = (SELECT trans_id FROM invoice WHERE id = ?)
@@ -592,8 +607,7 @@ SQL
     }
 
     if ($h_oe_id && ($ref->{oe_id} || $ref->{invoice_id})) {
-      my $id = $ref->{oe_id} ? $ref->{oe_id} : $ref->{invoice_id};
-      do_statement($form, $h_oe_id, $q_oe_id, ($id) x 6);
+      do_statement($form, $h_oe_id, $q_oe_id, $ref->{oe_id}, ($ref->{invoice_id}) x 2);
       $ref->{oe_id_info} = $h_oe_id->fetchrow_hashref() || {};
     }
 
@@ -614,6 +628,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
@@ -665,6 +680,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});
@@ -685,6 +705,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});
@@ -737,6 +762,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",
@@ -746,7 +773,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) );
@@ -764,7 +795,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
@@ -772,9 +803,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);