aliased subqueries müssen mit ihrem alias im group by stehen.

[kivitendo-erp.git] / SL / IC.pm

diff --git a/SL/IC.pm b/SL/IC.pm
index f9315f42acb23a5b310d08b369c9eb414b779338..f691a354f2e32e6a810a5ab4f9f5c6cbe09e588a 100644 (file)
--- a/SL/IC.pm
+++ b/SL/IC.pm
@@ -793,6 +793,10 @@ sub all_parts {
   my @join_order = qw(partsgroup makemodel invoice_oi apoe cv pfac);
   my %joins_needed;
 
   my @join_order = qw(partsgroup makemodel invoice_oi apoe cv pfac);
   my %joins_needed;
 

+  if (($form->{searchitems} eq 'assembly') && $form->{l_lastcost}) {
+    @simple_l_switches = grep { $_ ne 'lastcost' } @simple_l_switches;
+  }
+

   #===== switches and simple filters ========#
 
   my @select_tokens = qw(id factor);
   #===== switches and simple filters ========#
 
   my @select_tokens = qw(id factor);


@@ -845,6 +849,12 @@ sub all_parts {
     push @where_tokens, 'p.onhand < p.rop',            if /short/;
   }
 
     push @where_tokens, 'p.onhand < p.rop',            if /short/;
   }
 

+  my $q_assembly_lastcost =
+    qq|(SELECT SUM(a_lc.qty * p_lc.lastcost / COALESCE(pfac_lc.factor, 1))
+        FROM assembly a_lc
+        LEFT JOIN parts p_lc            ON (a_lc.parts_id        = p_lc.id)
+        LEFT JOIN price_factors pfac_lc ON (p_lc.price_factor_id = pfac_lc.id)
+        WHERE (a_lc.id = p.id)) AS lastcost|;

 
   my @sort_cols = (@simple_filters, qw(id bin priceupdate onhand invnumber ordnumber quonumber name serialnumber soldtotal deliverydate));
   $form->{sort} = 'id' unless grep { $form->{"l_$_"} } grep { $form->{sort} eq $_ } @sort_cols;
 
   my @sort_cols = (@simple_filters, qw(id bin priceupdate onhand invnumber ordnumber quonumber name serialnumber soldtotal deliverydate));
   $form->{sort} = 'id' unless grep { $form->{"l_$_"} } grep { $form->{sort} eq $_ } @sort_cols;


@@ -871,6 +881,7 @@ sub all_parts {
   my @bsooqr;
   push @select_tokens, @qsooqr_flags                                          if $bsooqr;
   push @select_tokens, @deliverydate_flags                                    if $bsooqr && $form->{l_deliverydate};
   my @bsooqr;
   push @select_tokens, @qsooqr_flags                                          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};
   push @bsooqr_tokens, q|module = 'is' AND NOT ioi.assemblyitem|              if $form->{sold};
   push @bsooqr_tokens, q|module = 'oe' AND NOT quotation AND cv = 'customer'| if $form->{ordered};
   push @bsooqr_tokens, q|module = 'ir' AND NOT ioi.assemblyitem|              if $form->{bought};
   push @bsooqr_tokens, q|module = 'is' AND NOT ioi.assemblyitem|              if $form->{sold};
   push @bsooqr_tokens, q|module = 'oe' AND NOT quotation AND cv = 'customer'| if $form->{ordered};


@@ -903,6 +914,7 @@ sub all_parts {
   if ($form->{l_soldtotal}) {
     push @where_tokens, 'ioi.qty >= 0';
     push @group_tokens, @select_tokens;
   if ($form->{l_soldtotal}) {
     push @where_tokens, 'ioi.qty >= 0';
     push @group_tokens, @select_tokens;

+     map { s/.*\sAS\s+//si } @group_tokens;

     push @select_tokens, 'SUM(ioi.qty)';
   }
 
     push @select_tokens, 'SUM(ioi.qty)';
   }
 


@@ -916,10 +928,13 @@ sub all_parts {
      ordnumber    => 'apoe.', make         => 'mm.',
      quonumber    => 'apoe.', model        => 'mm.',
      invnumber    => 'apoe.', partsgroup   => 'pg.',
      ordnumber    => 'apoe.', make         => 'mm.',
      quonumber    => 'apoe.', model        => 'mm.',
      invnumber    => 'apoe.', partsgroup   => 'pg.',

+     lastcost     => ' ',

      factor       => 'pfac.',
      'SUM(ioi.qty)' => ' ',
   );
 
      factor       => 'pfac.',
      'SUM(ioi.qty)' => ' ',
   );
 

+  $table_prefix{$q_assembly_lastcost} = ' ';
+

   my %renamed_columns = (
     'factor'       => 'price_factor',
     'SUM(ioi.qty)' => 'soldtotal',
   my %renamed_columns = (
     'factor'       => 'price_factor',
     'SUM(ioi.qty)' => 'soldtotal',