X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FWH.pm;h=895db95e5e2745a4c01766107cdf050a7fa52403;hb=60b170eb823d19d211db6412d2137129113e5c84;hp=a548bda77daf9ffb3910920ffc2d5f7e363d2a7b;hpb=aa0c7e72dc143e3f29e9bfdbda962ae98c503274;p=kivitendo-erp.git

diff --git a/SL/WH.pm b/SL/WH.pm
index a548bda77..895db95e5 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
@@ -191,7 +192,7 @@ sub transfer_assembly {
 
     my $query = qq|SELECT assembly.parts_id, assembly.qty, parts.warehouse_id
                    FROM assembly INNER JOIN parts ON assembly.parts_id = parts.id
-                   WHERE assembly.id = ? AND (inventory_accno_id IS NOT NULL OR parts.assembly = TRUE)|;
+                   WHERE assembly.id = ? AND parts.part_type != 'service'|;
 
     my $sth_part_qty_assembly = prepare_execute_query($form, $dbh, $query, $params{assembly_id});
 
@@ -368,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});
@@ -414,10 +420,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 +452,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 +485,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 +498,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,9 +551,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}) {
@@ -614,6 +656,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 +708,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 +733,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 +790,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 +801,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 +823,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 +831,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);