From 09306b9f2258b2414d89cc19567c2d7d0af29bfb Mon Sep 17 00:00:00 2001
From: =?utf8?q?Sven=20Sch=C3=B6ling?= <s.schoeling@linet-services.de>
Date: Wed, 7 Nov 2012 17:47:27 +0100
Subject: [PATCH] joins in Kundensuche umgeschrieben
MIME-Version: 1.0
Content-Type: text/plain; charset=utf8
Content-Transfer-Encoding: 8bit

behebt #1894

...wenn auch nicht sonderlich schön, auf Dauer müssen die ganzen aggreg
Funktionen in ein Berichtswesen ausgelagert werden.
---
 SL/CT.pm | 30 ++++++++++++------------------
 1 file changed, 12 insertions(+), 18 deletions(-)

diff --git a/SL/CT.pm b/SL/CT.pm
index de10623f2..93c3fcea2 100644
--- a/SL/CT.pm
+++ b/SL/CT.pm
@@ -598,6 +598,7 @@ sub search {
   my $dbh = $form->dbconnect($myconfig);
 
   my $cv = $form->{db} eq "customer" ? "customer" : "vendor";
+  my $join_records = $form->{l_invnumber} || $form->{l_ordnumber} || $form->{l_quonumber};
 
   my $where = "1 = 1";
   my @values;
@@ -611,7 +612,7 @@ sub search {
   $form->{sort} = $sortorder;
   my $sortdir   = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
 
-  if ($sortorder !~ /(business|id)/ && 1 >= scalar grep { $form->{$_} } qw(l_ordnumber l_quonumber l_invnumber )) {
+  if ($sortorder !~ /(business|id)/ && !$join_records) {
     $sortorder  = "lower($sortorder) ${sortdir}";
   } else {
     $sortorder .= " ${sortdir}";
@@ -705,21 +706,22 @@ sub search {
 
   my $query =
     qq|SELECT ct.*, b.description AS business | .
+    (qq|, NULL AS invnumber, NULL AS ordnumber, NULL AS quonumber, NULL AS invid, NULL AS module, NULL AS formtype, NULL AS closed | x!! $join_records) .
     qq|FROM $cv ct | .
     qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
     qq|WHERE $where|;
 
   my @saved_values = @values;
   # redo for invoices, orders and quotations
-  if ($form->{l_invnumber} || $form->{l_ordnumber} || $form->{l_quonumber}) {
-    my ($ar, $union, $module);
-    $query = "";
+  if ($join_records) {
+    my $union = "UNION";
 
     if ($form->{l_invnumber}) {
       my $ar = $cv eq 'customer' ? 'ar' : 'ap';
       my $module = $ar eq 'ar' ? 'is' : 'ir';
-
-      $query =
+      push(@values, @saved_values);
+      $query .=
+        qq| UNION | .
         qq|SELECT ct.*, b.description AS business, | .
         qq|  a.invnumber, a.ordnumber, a.quonumber, a.id AS invid, | .
         qq|  '$module' AS module, 'invoice' AS formtype, | .
@@ -728,16 +730,12 @@ sub search {
         qq|JOIN $ar a ON (a.${cv}_id = ct.id) | .
         qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
         qq|WHERE $where AND (a.invoice = '1')|;
-
-      $union = qq|UNION|;
     }
 
     if ( $form->{l_ordnumber} ) {
-      if ($union eq "UNION") {
-        push(@values, @saved_values);
-      }
+      push(@values, @saved_values);
       $query .=
-        qq| $union | .
+        qq| UNION | .
         qq|SELECT ct.*, b.description AS business,| .
         qq|  ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
         qq|  'oe' AS module, 'order' AS formtype, o.closed | .
@@ -745,16 +743,12 @@ sub search {
         qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
         qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
         qq|WHERE $where AND (o.quotation = '0')|;
-
-      $union = qq|UNION|;
     }
 
     if ( $form->{l_quonumber} ) {
-      if ($union eq "UNION") {
-        push(@values, @saved_values);
-      }
+      push(@values, @saved_values);
       $query .=
-        qq| $union | .
+        qq| UNION | .
         qq|SELECT ct.*, b.description AS business, | .
         qq|  ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
         qq|  'oe' AS module, 'quotation' AS formtype, o.closed | .
-- 
2.20.1