X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FIC.pm;h=0256e3d9c546256bc6e8fd3d6e6a0a8e8d85c10f;hb=ee61dd2b559ab9d6bf741086c86fa9076efba022;hp=c9511b4db52a461718adf4aa95af673948b2874d;hpb=0ade2438400535b4b5d969244ff1b23daddaf3fb;p=kivitendo-erp.git

diff --git a/SL/IC.pm b/SL/IC.pm
index c9511b4db..0256e3d9c 100644
--- a/SL/IC.pm
+++ b/SL/IC.pm
@@ -35,12 +35,14 @@
 package IC;
 
 use Data::Dumper;
-use List::MoreUtils qw(all any);
+use List::MoreUtils qw(all any uniq);
 use YAML;
 
 use SL::CVar;
 use SL::DBUtils;
 
+use strict;
+
 sub get_part {
   $main::lxdebug->enter_sub();
 
@@ -412,6 +414,7 @@ sub save {
          ve = ?,
          gv = ?,
          ean = ?,
+         has_sernumber = ?,
          not_discountable = ?,
          microfiche = ?,
          partsgroup_id = ?,
@@ -441,6 +444,7 @@ sub save {
              conv_i($form->{ve}),
              conv_i($form->{gv}),
              $form->{ean},
+             $form->{has_sernumber} ? 't' : 'f',
              $form->{not_discountable} ? 't' : 'f',
              $form->{microfiche},
              conv_i($partsgroup_id),
@@ -493,8 +497,8 @@ sub save {
       if (($form->{"make_$i"}) || ($form->{"model_$i"})) {
 
         $query = qq|INSERT INTO makemodel (parts_id, make, model) | .
-		             qq|VALUES (?, ?, ?)|;
-		    @values = (conv_i($form->{id}), conv_i($form->{"make_$i"}), $form->{"model_$i"});
+                 qq|VALUES (?, ?, ?)|;
+        @values = (conv_i($form->{id}), conv_i($form->{"make_$i"}), $form->{"model_$i"});
 
         do_query($form, $dbh, $query, @values);
       }
@@ -507,7 +511,7 @@ sub save {
       $query =
         qq|INSERT INTO partstax (parts_id, chart_id)
            VALUES (?, (SELECT id FROM chart WHERE accno = ?))|;
-			@values = (conv_i($form->{id}), $item);
+      @values = (conv_i($form->{id}), $item);
       do_query($form, $dbh, $query, @values);
     }
   }
@@ -521,8 +525,8 @@ sub save {
       if ($form->{"qty_$i"} != 0) {
         $form->{"bom_$i"} *= 1;
         $query = qq|INSERT INTO assembly (id, parts_id, qty, bom) | .
-		             qq|VALUES (?, ?, ?, ?)|;
-		    @values = (conv_i($form->{id}), conv_i($form->{"id_$i"}), conv_i($form->{"qty_$i"}), $form->{"bom_$i"} ? 't' : 'f');
+                 qq|VALUES (?, ?, ?, ?)|;
+        @values = (conv_i($form->{id}), conv_i($form->{"id_$i"}), conv_i($form->{"qty_$i"}), $form->{"bom_$i"} ? 't' : 'f');
         do_query($form, $dbh, $query, @values);
       }
     }
@@ -790,14 +794,14 @@ sub all_parts {
     pfac       => 'LEFT JOIN price_factors pfac ON (pfac.id     = p.price_factor_id)',
     invoice_oi =>
       q|LEFT JOIN (
-         SELECT parts_id, description, serialnumber, trans_id, unit, sellprice, qty,          assemblyitem,         deliverydate, 'invoice'    AS ioi FROM invoice UNION
-         SELECT parts_id, description, serialnumber, trans_id, unit, sellprice, qty, FALSE AS assemblyitem, NULL AS deliverydate, 'orderitems' AS ioi FROM orderitems
+         SELECT parts_id, description, serialnumber, trans_id, unit, sellprice, qty,          assemblyitem,         deliverydate, 'invoice'    AS ioi, id FROM invoice UNION
+         SELECT parts_id, description, serialnumber, trans_id, unit, sellprice, qty, FALSE AS assemblyitem, NULL AS deliverydate, 'orderitems' AS ioi, id FROM orderitems
        ) AS ioi ON ioi.parts_id = p.id|,
     apoe       =>
       q|LEFT JOIN (
-         SELECT id, transdate, 'ir' AS module, ordnumber, quonumber,         invnumber, FALSE AS quotation, NULL AS customer_id,         vendor_id, NULL AS deliverydate, 'invoice'    AS ioi FROM ap UNION
-         SELECT id, transdate, 'is' AS module, ordnumber, quonumber,         invnumber, FALSE AS quotation,         customer_id, NULL AS vendor_id,         deliverydate, 'invoice'    AS ioi FROM ar UNION
-         SELECT id, transdate, 'oe' AS module, ordnumber, quonumber, NULL AS invnumber,          quotation,         customer_id,         vendor_id, NULL AS deliverydate, 'orderitems' AS ioi FROM oe
+         SELECT id, transdate, 'ir' AS module, ordnumber, quonumber,         invnumber, FALSE AS quotation, NULL AS customer_id,         vendor_id,    NULL AS deliverydate, 'invoice'    AS ioi FROM ap UNION
+         SELECT id, transdate, 'is' AS module, ordnumber, quonumber,         invnumber, FALSE AS quotation,         customer_id, NULL AS vendor_id,            deliverydate, 'invoice'    AS ioi FROM ar UNION
+         SELECT id, transdate, 'oe' AS module, ordnumber, quonumber, NULL AS invnumber,          quotation,         customer_id,         vendor_id, reqdate AS deliverydate, 'orderitems' AS ioi FROM oe
        ) AS apoe ON ((ioi.trans_id = apoe.id) AND (ioi.ioi = apoe.ioi))|,
     cv         =>
       q|LEFT JOIN (
@@ -814,12 +818,16 @@ sub all_parts {
      ordnumber    => 'apoe.', make         => 'mm.',
      quonumber    => 'apoe.', model        => 'mm.',
      invnumber    => 'apoe.', partsgroup   => 'pg.',
-     lastcost     => ' ',
+     lastcost     => ' ',   , soldtotal    => ' ',
      factor       => 'pfac.',
      'SUM(ioi.qty)' => ' ',
      description  => 'p.',
      qty          => 'ioi.',
      serialnumber => 'ioi.',
+     quotation    => 'apoe.',
+     cv           => 'cv.',
+     "ioi.id"     => ' ',
+     "ioi.ioi"    => ' ',
   );
 
   # if the join condition in these blocks are met, the column
@@ -829,14 +837,17 @@ sub all_parts {
     #  column name,   prefix,  joins_needed
     [ 'description',  'ioi.',  'invoice_oi'  ],
     [ 'deliverydate', 'ioi.',  'invoice_oi'  ],
-    [ 'transdate' ,   'apoe.', 'apoe'  ],
-    [ 'unit' ,        'ioi.',  'invoice_oi'  ],
+    [ 'transdate',    'apoe.', 'apoe'        ],
+    [ 'unit',         'ioi.',  'invoice_oi'  ],
+    [ 'sellprice',    'ioi.',  'invoice_oi'  ],
   );
 
   # careful with renames. these are HARD, and any filters done on the original column will break
   my %renamed_columns = (
     'factor'       => 'price_factor',
     'SUM(ioi.qty)' => 'soldtotal',
+    'ioi.id'       => 'ioi_id',
+    'ioi.ioi'      => 'ioi',
   );
 
   if (($form->{searchitems} eq 'assembly') && $form->{l_lastcost}) {
@@ -846,18 +857,18 @@ sub all_parts {
   my $make_token_builder = sub {
     my $joins_needed = shift;
     sub {
-      my ($col, $group) = @_;
+      my ($col, $alias) = @_;
       my @coalesce_tokens =
         map  { ($_->[1] || 'p.') . $_->[0] }
         grep { !$_->[2] || $joins_needed->{$_->[2]} }
-        grep { $_->[0] eq $col }
+        grep {  $_->[0] eq $col }
         @column_override, [ $col, $table_prefix{$col} ];
 
       my $coalesce = scalar @coalesce_tokens > 1;
       return ($coalesce
         ? sprintf 'COALESCE(%s)', join ', ', @coalesce_tokens
         : shift                              @coalesce_tokens)
-        . ($group && ($coalesce || $renamed_columns{$col})
+        . ($alias && ($coalesce || $renamed_columns{$col})
         ?  " AS " . ($renamed_columns{$col} || $col)
         : '');
     }
@@ -926,14 +937,16 @@ sub all_parts {
 
   #my $order_clause = " ORDER BY $form->{sort} $sort_order";
 
-  my $limit_clause = " LIMIT 100" if $form->{top100};
+  my $limit_clause;
+  $limit_clause = " LIMIT 100"                   if $form->{top100};
+  $limit_clause = " LIMIT " . $form->{limit} * 1 if $form->{limit} * 1;
 
   #=== joins and complicated filters ========#
 
   my $bsooqr        = any { $form->{$_} } @oe_flags;
   my @bsooqr_tokens = ();
 
-  push @select_tokens, @qsooqr_flags                                          if $bsooqr;
+  push @select_tokens, @qsooqr_flags, 'quotation', 'cv', 'ioi.id', 'ioi.ioi'  if $bsooqr;
   push @select_tokens, @deliverydate_flags                                    if $bsooqr && $form->{l_deliverydate};
   push @select_tokens, $q_assembly_lastcost                                   if ($form->{searchitems} eq 'assembly') && $form->{l_lastcost};
   push @bsooqr_tokens, q|module = 'ir' AND NOT ioi.assemblyitem|              if $form->{bought};
@@ -944,9 +957,6 @@ sub all_parts {
   push @bsooqr_tokens, q|module = 'oe' AND     quotation AND cv = 'vendor'|   if $form->{rfq};
   push @where_tokens, join ' OR ', map { "($_)" } @bsooqr_tokens              if $bsooqr;
 
-  $renamed_columns{onhand} = 'onhand_before_bsooqr';
-  $renamed_columns{qty}    = 'onhand';
-
   $joins_needed{partsgroup}  = 1;
   $joins_needed{pfac}        = 1;
   $joins_needed{makemodel}   = 1 if grep { $form->{$_} || $form->{"l_$_"} } @makemodel_filters;
@@ -954,6 +964,12 @@ sub all_parts {
   $joins_needed{apoe}        = 1 if $joins_needed{cv}   || grep { $form->{$_} || $form->{"l_$_"} } @apoe_filters;
   $joins_needed{invoice_oi}  = 1 if $joins_needed{apoe} || grep { $form->{$_} || $form->{"l_$_"} } @invoice_oi_filters;
 
+  # in bsoorq, use qtys instead of onhand
+  if ($joins_needed{invoice_oi}) {
+    $renamed_columns{onhand} = 'onhand_before_bsooqr';
+    $renamed_columns{qty}    = 'onhand';
+  }
+
   # special case for description search.
   # up in the simple filter section the description filter got interpreted as something like: WHERE description ILIKE '%$form->{description}%'
   # now we'd like to search also for the masked description entered in orderitems and invoice, so...
@@ -969,7 +985,7 @@ sub all_parts {
 
   # now the master trick: soldtotal.
   if ($form->{l_soldtotal}) {
-    push @where_tokens, 'ioi.qty >= 0';
+    push @where_tokens, 'NOT ioi.qty = 0';
     push @group_tokens, @select_tokens;
      map { s/.*\sAS\s+//si } @group_tokens;
     push @select_tokens, 'SUM(ioi.qty)';
@@ -989,16 +1005,36 @@ sub all_parts {
   my $where_clause  = join ' AND ', map { "($_)" } @where_tokens;
   my $group_clause  = ' GROUP BY ' . join ', ',    map { $token_builder->($_) } @group_tokens if scalar @group_tokens;
 
-  my ($cvar_where, @cvar_values) = CVar->build_filter_query('module'         => 'IC',
-                                                            'trans_id_field' => 'p.id',
-                                                            'filter'         => $form);
+  my %oe_flag_to_cvar = (
+    bought   => 'invoice',
+    sold     => 'invoice',
+    onorder  => 'orderitems',
+    ordered  => 'orderitems',
+    rfq      => 'orderitems',
+    quoted   => 'orderitems',
+  );
+
+  my ($cvar_where, @cvar_values) = CVar->build_filter_query(
+    module         => 'IC',
+    trans_id_field => $bsooqr ? 'ioi.id': 'p.id',
+    filter         => $form,
+    sub_module     => $bsooqr ? [ uniq grep { $oe_flag_to_cvar{$form->{$_}} } @oe_flags ] : undef,
+  );
 
   if ($cvar_where) {
     $where_clause .= qq| AND ($cvar_where)|;
     push @bind_vars, @cvar_values;
   }
 
-  my $query = qq|SELECT DISTINCT $select_clause FROM parts p $join_clause WHERE $where_clause $group_clause $order_clause $limit_clause|;
+  my $query = <<"  SQL";
+    SELECT DISTINCT $select_clause
+    FROM parts p
+    $join_clause
+    WHERE $where_clause
+    $group_clause
+    $order_clause
+    $limit_clause
+  SQL
 
   $form->{parts} = selectall_hashref_query($form, $dbh, $query, @bind_vars);
 
@@ -1038,6 +1074,8 @@ sub all_parts {
   }
 
   $main::lxdebug->leave_sub();
+
+  return wantarray ? @{ $form->{parts} } : $form->{parts};
 }
 
 sub _create_filter_for_priceupdate {
@@ -1420,7 +1458,7 @@ sub follow_account_chain {
     qq|  cnew.accno | .
     qq|FROM chart c | .
     qq|LEFT JOIN chart cnew ON c.new_chart_id = cnew.id | .
-    qq|WHERE (c.id = ?) AND NOT c.new_chart_id ISNULL AND (c.new_chart_id > 0)|;
+    qq|WHERE (c.id = ?) AND NOT c.new_chart_id IS NULL AND (c.new_chart_id > 0)|;
   $sth = prepare_query($form, $dbh, $query);
 
   while (1) {
@@ -1556,7 +1594,7 @@ sub get_basic_part_info {
 
   my $dbh      = $form->get_standard_dbh($myconfig);
 
-  my $query    = qq|SELECT id, partnumber, description, unit FROM parts WHERE id IN (| . join(', ', ('?') x scalar(@ids)) . qq|)|;
+  my $query    = qq|SELECT * FROM parts WHERE id IN (| . join(', ', ('?') x scalar(@ids)) . qq|)|;
 
   my $info     = selectall_hashref_query($form, $dbh, $query, map { conv_i($_) } @ids);