X-Git-Url: http://wagnertech.de/gitweb/gitweb.cgi/mfinanz.git/blobdiff_plain/ff159a4d47b9a2d10744dcfc23da2c63605c8a32..eeb5375ee7727c956cc357cc8f90b19d1bfe80b9:/SL/WH.pm

diff --git a/SL/WH.pm b/SL/WH.pm
index 52e174ff0..44c40d6d6 100644
--- a/SL/WH.pm
+++ b/SL/WH.pm
@@ -36,6 +36,7 @@
 package WH;
 
 use Carp qw(croak);
+use List::MoreUtils qw(any);
 
 use SL::AM;
 use SL::DBUtils;
@@ -269,7 +270,7 @@ sub get_warehouse_journal {
 
   # if of a property number or description is requested,
   # automatically check the matching id too.
-  map { $form->{"l_${_}id"} = "Y" if ($form->{"l_${_}description"} || $form->{"l_${_}number"}); } qw(warehouse bin);
+  map { $form->{"l_${_}id"} = "Y" if ($form->{"l_${_}"} || $form->{"l_${_}number"}); } qw(warehouse bin);
 
   # customize shown entry for not available fields.
   $filter{na} = '-' unless $filter{na};
@@ -319,32 +320,32 @@ sub get_warehouse_journal {
   }
 
   $select_tokens{'trans'} = {
-     "parts_id"             => "i1.parts_id",
-     "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",
-     "warehousedescription" => "w.description",
-     "partunit"             => "p.unit",
-     "bin_from"             => "b1.description",
-     "bin_to"               => "b2.description",
-     "warehouse_from"       => "w1.description",
-     "warehouse_to"         => "w2.description",
-     "comment"              => "i1.comment",
-     "trans_type"           => "tt.description",
-     "trans_id"             => "i1.trans_id",
-     "id"                   => "i1.id",
-     "oe_id"                => "COALESCE(i1.oe_id, i2.oe_id)",
-     "invoice_id"           => "COALESCE(i1.invoice_id, i2.invoice_id)",
-     "date"                 => "i1.shippingdate",
-     "itime"                => "i1.itime",
-     "shippingdate"         => "i1.shippingdate",
-     "employee"             => "e.name",
-     "projectnumber"        => "COALESCE(pr.projectnumber, '$filter{na}')",
+     "parts_id"          => "i1.parts_id",
+     "qty"               => "ABS(SUM(i1.qty))",
+     "partnumber"        => "p.partnumber",
+     "partdescription"   => "p.description",
+     "classification_id" => "p.classification_id",
+     "part_type"         => "p.part_type",
+     "bin"               => "b.description",
+     "chargenumber"      => "i1.chargenumber",
+     "bestbefore"        => "i1.bestbefore",
+     "warehouse"         => "w.description",
+     "partunit"          => "p.unit",
+     "bin_from"          => "b1.description",
+     "bin_to"            => "b2.description",
+     "warehouse_from"    => "w1.description",
+     "warehouse_to"      => "w2.description",
+     "comment"           => "i1.comment",
+     "trans_type"        => "tt.description",
+     "trans_id"          => "i1.trans_id",
+     "id"                => "i1.id",
+     "oe_id"             => "COALESCE(i1.oe_id, i2.oe_id)",
+     "invoice_id"        => "COALESCE(i1.invoice_id, i2.invoice_id)",
+     "date"              => "i1.shippingdate",
+     "itime"             => "i1.itime",
+     "shippingdate"      => "i1.shippingdate",
+     "employee"          => "e.name",
+     "projectnumber"     => "COALESCE(pr.projectnumber, '$filter{na}')",
      };
 
   $select_tokens{'out'} = {
@@ -427,10 +428,7 @@ sub get_warehouse_journal {
   if ($form->{l_oe_id}) {
     $q_oe_id = <<SQL;
       SELECT dord.id AS id, dord.donumber AS number,
-        CASE
-          WHEN dord.customer_id IS NULL THEN 'purchase_delivery_order'
-          ELSE                               'sales_delivery_order'
-        END AS type
+      dord.record_type::text AS type
       FROM delivery_orders dord
       WHERE dord.id = ?
 
@@ -519,7 +517,7 @@ sub get_warehouse_report {
   # filters
   my (@filter_ary, @filter_vars, @wh_bin_filter_ary, @wh_bin_filter_vars);
 
-  delete $form->{include_empty_bins} unless ($form->{l_warehousedescription} || $form->{l_bindescription});
+  delete $form->{include_empty_bins} unless ($form->{l_warehouse} || $form->{l_bin});
 
   if ($filter{warehouse_id}) {
     push @wh_bin_filter_ary,  "w.id = ?";
@@ -600,7 +598,7 @@ sub get_warehouse_report {
 
   # if of a property number or description is requested,
   # automatically check the matching id too.
-  map { $form->{"l_${_}id"} = "Y" if ($form->{"l_${_}description"} || $form->{"l_${_}number"}); } qw(warehouse bin);
+  map { $form->{"l_${_}id"} = "Y" if ($form->{"l_${_}"} || $form->{"l_${_}number"}); } qw(warehouse bin);
 
   # make order, search in $filter and $form
   my $sort_col    =  $form->{sort};
@@ -628,20 +626,22 @@ sub get_warehouse_report {
      "partdescription"      => "p.description",
      "classification_id"    => "p.classification_id",
      "part_type"            => "p.part_type",
-     "bindescription"       => "b.description",
+     "bin"                  => "b.description",
      "binid"                => "b.id",
      "chargenumber"         => "i.chargenumber",
      "bestbefore"           => "i.bestbefore",
      "ean"                  => "p.ean",
      "chargeid"             => "c.id",
-     "warehousedescription" => "w.description",
+     "warehouse"            => "w.description",
      "partunit"             => "p.unit",
      "stock_value"          => ($form->{stock_value_basis} // '') eq 'list_price' ? "p.listprice / COALESCE(pfac.factor, 1)" : "p.lastcost / COALESCE(pfac.factor, 1)",
      "purchase_price"       => "p.lastcost",
      "list_price"           => "p.listprice",
+     "price_factor"         => ($form->{l_purchase_price} || $form->{l_list_price}) ? "pfac.description" : undef,
   );
   $form->{l_classification_id}  = 'Y';
   $form->{l_part_type}          = 'Y';
+  $form->{l_price_factor}       = 'Y' if $form->{l_purchase_price} || $form->{l_list_price};
 
   my $select_clause = join ', ', map { +/^l_/; "$select_tokens{$'} AS $'" }
         ( grep( { !/qty/ and !/^l_cvar/ and /^l_/ and $form->{$_} eq 'Y' } keys %$form),
@@ -651,14 +651,45 @@ sub get_warehouse_report {
         ( grep( { !/qty/ and !/^l_cvar/ and /^l_/ and $form->{$_} eq 'Y' } keys %$form),
           qw(l_parts_id l_partunit) );
 
+  my @join_values = ();
   my %join_tokens = (
-    "stock_value" => "LEFT JOIN price_factors pfac ON (p.price_factor_id = pfac.id)",
-    );
+    "stock_value"  => "LEFT JOIN price_factors pfac ON (p.price_factor_id = pfac.id)",
+  );
+  $join_tokens{price_factor} = "LEFT JOIN price_factors pfac ON (p.price_factor_id = pfac.id)" if !$form->{l_stock_value};
 
   my $joins = join ' ', grep { $_ } map { +/^l_/; $join_tokens{"$'"} }
         ( grep( { !/qty/ and !/^l_cvar/ and /^l_/ and $form->{$_} eq 'Y' } keys %$form),
           qw(l_parts_id l_qty l_partunit) );
 
+  # add cvar for sorting
+  if (($form->{sort} // '') =~ /^cvar_/) {
+    my $sort_name = $form->{sort};
+    my $cvar_name = $sort_name;
+    $cvar_name =~ s/^cvar_//;
+    my $cvar_configs = CVar->get_configs('module' => 'IC');
+    my @allowed_cvar_names =
+      map {$_->{name}}
+      grep {$_->{type} =~ m/text|textfield|htmlfield/}
+      @$cvar_configs;
+    unless (any {$sort_name eq 'cvar_' . $_} @allowed_cvar_names) {
+      die "unsupported sort on cvar field";
+    }
+
+    $select_clause .= ", cvar_fields.$sort_name";
+    $group_clause  .= ", cvar_fields.$sort_name";
+    $joins .= qq|
+      LEFT JOIN (
+        SELECT text_value as $sort_name, trans_id
+        FROM custom_variable_configs cvar_cfg
+        LEFT JOIN custom_variables cvar
+        ON (cvar_cfg.module = 'IC' AND cvar_cfg.name = ?
+            AND cvar_cfg.id = cvar.config_id)
+      ) cvar_fields ON (cvar_fields.trans_id = p.id)
+      |;
+    push @join_values, $cvar_name
+  }
+  @filter_vars = (@join_values, @filter_vars);
+
   my ($cvar_where, @cvar_values) = CVar->build_filter_query(
     module         => 'IC',
     trans_id_field => 'p.id',
@@ -724,8 +755,8 @@ sub get_warehouse_report {
   if ($form->{include_empty_bins}) {
     $query =
       qq|SELECT
-           w.id AS warehouseid, w.description AS warehousedescription,
-           b.id AS binid, b.description AS bindescription
+           w.id AS warehouseid, w.description AS warehouse,
+           b.id AS binid, b.description AS bin
          FROM bin b
          LEFT JOIN warehouse w ON (b.warehouse_id = w.id)|;
 
@@ -748,7 +779,7 @@ sub get_warehouse_report {
     }
     $sth->finish();
 
-    if (grep { $orderby eq $_ } qw(bindescription warehousedescription)) {
+    if (grep { $orderby eq $_ } qw(bin warehouse)) {
       @contents = sort { ($a->{$orderby} cmp $b->{$orderby}) * (($form->{order}) ? 1 : -1) } @contents;
     }
   }