X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2Fcust_main%2FSearch.pm;h=091be9956acf0637bab595ad2a64eff3c541b199;hb=1370d9061f191fa7e62e89e128263f7c128ee8f1;hp=58bdd8835bd6dede0b3f1d68c5ef32cb24a8c35a;hpb=513d1c3cbbcb536acda260ffe121610fc9c1ed81;p=freeside.git

diff --git a/FS/FS/cust_main/Search.pm b/FS/FS/cust_main/Search.pm
index 58bdd8835..091be9956 100644
--- a/FS/FS/cust_main/Search.pm
+++ b/FS/FS/cust_main/Search.pm
@@ -93,35 +93,29 @@ sub smart_search {
     my $phonenum = "$1$2$3";
     #my $extension = $4;
 
-    #cust_main phone numbers
+    #cust_main phone numbers and contact phone number
     push @cust_main, qsearch( {
-      'table'   => 'cust_main',
-      'hashref' => { %options },
+      'select'    => 'cust_main.*',
+      'table'     => 'cust_main',
+      'addl_from' => ' left join contact  using (custnum) '.
+                     ' left join contact_phone using (contactnum) ',
+      'hashref'   => { %options },
       'extra_sql' => ( scalar(keys %options) ? ' AND ' : ' WHERE ' ).
                      ' ( '.
                          join(' OR ', map "$_ = '$phonen'",
                                           qw( daytime night mobile fax )
                              ).
+                          " OR phonenum = '$phonenum' ".
                      ' ) '.
                      " AND $agentnums_sql", #agent virtualization
     } );
 
-    #contact phone numbers
-    push @cust_main,
-      grep $agentnums_href->{$_->agentnum}, #agent virt
-        grep $_, #skip contacts that don't have cust_main records
-          map $_->contact->cust_main,
-            qsearch({
-                      'table'   => 'contact_phone',
-                      'hashref' => { 'phonenum' => $phonenum },
-                   });
-
     unless ( @cust_main || $phonen =~ /x\d+$/ ) { #no exact match
       #try looking for matches with extensions unless one was specified
 
       push @cust_main, qsearch( {
-        'table'   => 'cust_main',
-        'hashref' => { %options },
+        'table'     => 'cust_main',
+        'hashref'   => { %options },
         'extra_sql' => ( scalar(keys %options) ? ' AND ' : ' WHERE ' ).
                        ' ( '.
                            join(' OR ', map "$_ LIKE '$phonen\%'",
@@ -136,28 +130,23 @@ sub smart_search {
   } 
   
   
-  if ( $search =~ /@/ ) { #email address
-
-      # invoicing email address
-      push @cust_main,
-        grep $agentnums_href->{$_->agentnum}, #agent virt
-	  map $_->cust_main,
-	      qsearch( {
-			 'table'     => 'cust_main_invoice',
-			 'hashref'   => { 'dest' => $search },
-		       }
-		     );
-
-      # contact email address
-      push @cust_main,
-        grep $agentnums_href->{$_->agentnum}, #agent virt
-          grep $_, #skip contacts that don't have cust_main records
-	    map $_->contact->cust_main,
-	      qsearch( {
-			 'table'     => 'contact_email',
-			 'hashref'   => { 'emailaddress' => $search },
-		       }
-		     );
+  if ( $search =~ /@/ ) { #email address from cust_main_invoice and contact_email
+
+    push @cust_main, qsearch( {
+      'select'    => 'cust_main.*',
+      'table'     => 'cust_main',
+      'addl_from' => ' left join cust_main_invoice using (custnum) '.
+                     ' left join contact      using (custnum) '.
+                     ' left join contact_email     using (contactnum) ',
+      'hashref'   => { %options },
+      'extra_sql' => ( scalar(keys %options) ? ' AND ' : ' WHERE ' ).
+                     ' ( '.
+                         join(' OR ', map "$_ = '$search'",
+                                          qw( dest emailaddress )
+                             ).
+                     ' ) '.
+                     " AND $agentnums_sql", #agent virtualization
+    } );
 
   # custnum search (also try agent_custid), with some tweaking options if your
   # legacy cust "numbers" have letters
@@ -282,8 +271,8 @@ sub smart_search {
     } elsif ( ! $NameParse->parse($value) ) {
 
       my %name = $NameParse->components;
-      $first = $name{'given_name_1'} || $name{'initials_1'}; #wtf NameParse, Ed?
-      $last  = $name{'surname_1'};
+      $first = lc($name{'given_name_1'}) || $name{'initials_1'}; #wtf NameParse, Ed?
+      $last  = lc($name{'surname_1'});
 
     }
 
@@ -293,28 +282,18 @@ sub smart_search {
 
       #exact
       my $sql = scalar(keys %options) ? ' AND ' : ' WHERE ';
-      $sql .= "( LOWER(cust_main.last) = $q_last AND LOWER(cust_main.first) = $q_first )";
+      $sql .= "( (LOWER(cust_main.last) = $q_last AND LOWER(cust_main.first) = $q_first)
+                 OR (LOWER(contact.last) = $q_last AND LOWER(contact.first) = $q_first) )";
 
-      #cust_main
+      #cust_main and contacts
       push @cust_main, qsearch( {
+        'select'    => 'cust_main.*',
         'table'     => 'cust_main',
-        'hashref'   => \%options,
+        'addl_from' => ' left join contact using (custnum) ',
+        'hashref'   => { %options },
         'extra_sql' => "$sql AND $agentnums_sql", #agent virtualization
       } );
 
-      #contacts
-      push @cust_main,
-        grep $agentnums_href->{$_->agentnum}, #agent virt
-          grep $_, #skip contacts that don't have cust_main records
-	    map $_->cust_main,
-	      qsearch( {
-			 'table'     => 'contact',
-			 'hashref'   => { 'first' => $first,
-                                          'last'  => $last,
-                                        }, 
-		       }
-		     );
-
       # or it just be something that was typed in... (try that in a sec)
 
     }
@@ -327,7 +306,9 @@ sub smart_search {
                 OR LOWER(cust_main.last)          = $q_value
                 OR LOWER(cust_main.company)       = $q_value
                 OR LOWER(cust_main.ship_company)  = $q_value
-            ";
+                OR LOWER(contact.first)           = $q_value
+                OR LOWER(contact.last)            = $q_value
+            )";
 
     #address1 (yes, it's a kludge)
     $sql .= "   OR EXISTS ( 
@@ -337,19 +318,11 @@ sub smart_search {
                           )"
       if $conf->exists('address1-search');
 
-    #contacts (look, another kludge)
-    $sql .= "   OR EXISTS ( SELECT 1 FROM contact
-                              WHERE (    LOWER(contact.first) = $q_value
-                                      OR LOWER(contact.last)  = $q_value
-                                    )
-                                AND contact.custnum IS NOT NULL
-                                AND contact.custnum = cust_main.custnum
-                          )
-              ) ";
-
     push @cust_main, qsearch( {
+      'select'    => 'cust_main.*',
       'table'     => 'cust_main',
-      'hashref'   => \%options,
+      'addl_from' => ' left join contact using (custnum) ',
+      'hashref'   => { %options },
       'extra_sql' => "$sql AND $agentnums_sql", #agent virtualization
     } );
 
@@ -646,7 +619,7 @@ sub search {
   my $dbh = dbh;
 
   my @where = ();
-  my $orderby;
+  my $orderby = "ORDER BY cust_main.custnum";
 
   # initialize these to prevent warnings
   $params = {
@@ -936,7 +909,7 @@ sub search {
       }
     }
 
-    $orderby ||= "ORDER BY cust_main.$field";
+    $orderby .= ", cust_main.$field";
 
   }
 
@@ -1096,10 +1069,58 @@ sub search {
   }
 
   ##
+  # contacts
+  ##
+  if (keys %{ $params->{'contacts'} }) {
+    my $contact_params = $params->{'contacts'};
+
+    if ($contact_params->{'contacts_firstname'} || $contact_params->{'contacts_lastname'}) {
+      my $first_query = " AND contact.first = '" . $contact_params->{'contacts_firstname'} . "'"
+        unless !$contact_params->{'contacts_firstname'};
+      my $last_query = " AND contact.last = '" . $contact_params->{'contacts_lastname'} . "'"
+        unless !$contact_params->{'contacts_lastname'};
+      push @where,
+      "EXISTS ( SELECT 1 FROM contact
+                WHERE contact.custnum = cust_main.custnum
+                $first_query $last_query
+              ) ";
+    }
+
+    if ($contact_params->{'contacts_email'}) {
+      push @where,
+      "EXISTS ( SELECT 1 FROM contact_email
+                JOIN contact USING (contactnum)
+                WHERE contact.custnum = cust_main.custnum
+                AND contact_email.emailaddress = '" . $contact_params->{'contacts_email'} . "'
+              ) ";
+    }
+
+    if ($contact_params->{'contacts_homephone'} || $contact_params->{'contacts_workphone'} || $contact_params->{'contacts_mobilephone'}) {
+      foreach my $phone (qw( contacts_homephone contacts_workphone contacts_mobilephone )) {
+        (my $num = $contact_params->{$phone}) =~ s/\W//g;
+        if ( $num =~ /^1?(\d{3})(\d{3})(\d{4})(\d*)$/ ) { $contact_params->{$phone} = "$1$2$3"; }
+      }
+      my $home_query = " AND ( contact_phone.phonetypenum = '2' AND contact_phone.phonenum = '" . $contact_params->{'contacts_homephone'} . "' )"
+        unless !$contact_params->{'contacts_homephone'};
+      my $work_query = " AND ( contact_phone.phonetypenum = '1' AND contact_phone.phonenum = '" . $contact_params->{'contacts_workphone'} . "' )"
+        unless !$contact_params->{'contacts_workphone'};
+      my $mobile_query = " AND ( contact_phone.phonetypenum = '3' AND contact_phone.phonenum = '" . $contact_params->{'contacts_mobilephone'} . "' )"
+        unless !$contact_params->{'contacts_mobilephone'};
+      push @where,
+      "EXISTS ( SELECT 1 FROM contact_phone
+                JOIN contact USING (contactnum)
+                WHERE contact.custnum = cust_main.custnum
+                $home_query $work_query $mobile_query
+              ) ";
+    }
+}
+
+
+  ##
   # setup queries, subs, etc. for the search
   ##
 
-  $orderby ||= 'ORDER BY custnum';
+  $orderby ||= 'ORDER BY cust_main.custnum';
 
   # here is the agent virtualization
   push @where,
@@ -1119,8 +1140,6 @@ sub search {
   #   (maybe we should be using FS::UI::Web::join_cust_main instead?)
   $addl_from .= ' LEFT JOIN (select refnum, referral from part_referral) AS part_referral_x ON (cust_main.refnum = part_referral_x.refnum) ';
 
-  my $count_query = "SELECT COUNT(*) FROM cust_main $addl_from $extra_sql";
-
   my @select = (
                  'cust_main.custnum',
                  'cust_main.salesnum',
@@ -1134,6 +1153,8 @@ sub search {
   my @extra_fields      = ();
   my @extra_sort_fields = ();
 
+  my $count_query = "SELECT COUNT(DISTINCT cust_main.custnum) FROM cust_main $addl_from $extra_sql";
+
   if ($params->{'flattened_pkgs'}) {
 
     #my $pkg_join = '';
@@ -1197,7 +1218,8 @@ sub search {
 
   }
 
-  my $select = join(', ', @select);
+  # set select a customer only once. some joins create multiple customer records.
+  my $select = 'DISTINCT ON (cust_main.custnum) ' . join(', ', @select);
 
   my $sql_query = {
     'table'             => 'cust_main',