X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=FS%2FFS%2Fcust_main%2FSearch.pm;h=479ebf5c4acf829c949d1bc953a2086524623f0c;hp=2460250a6b231cf761d272b2337271237ff60be0;hb=6b5dda03831aef0cb5689cf2acf3fac47e4b12cb;hpb=922f9f7ee1ff081a4098d49449c63043d551897f

diff --git a/FS/FS/cust_main/Search.pm b/FS/FS/cust_main/Search.pm
index 2460250a6..479ebf5c4 100644
--- a/FS/FS/cust_main/Search.pm
+++ b/FS/FS/cust_main/Search.pm
@@ -383,6 +383,7 @@ sub smart_search {
       if ( $conf->exists('address1-search') && length($value) >= $min_len ) {
 
         push @cust_main, qsearch( {
+          select    => 'cust_main.*',
           table     => 'cust_main',
           addl_from => 'JOIN cust_location USING (custnum)',
           extra_sql => 'WHERE '.
@@ -464,6 +465,7 @@ sub smart_search {
     my $mask_search = FS::payinfo_Mixin->mask_payinfo('CARD', $card_search);
 
     push @cust_main, qsearch({
+      'select'    => 'cust_main.*',
       'table'     => 'cust_main',
       'addl_from' => ' JOIN cust_payby USING (custnum)',
       'hashref'   => {},
@@ -787,6 +789,21 @@ sub search {
     )";
   }
 
+  ##
+  # phones
+  ##
+
+  foreach my $phonet (qw(daytime night mobile)) {
+    if ($params->{$phonet}) {
+      $params->{$phonet} =~ s/\D//g;
+      $params->{$phonet} =~ /^(\d{3})(\d{3})(\d{4})(\d*)$/
+        or next;
+      my $phonen = "$1-$2-$3";
+      if ($4) { push @where, "cust_main.".$phonet." = '".$phonen." x$4'"; }
+      else { push @where, "cust_main.".$phonet." like '".$phonen."%'"; }
+    }
+  }
+
   ###
   # refnum
   ###
@@ -1014,8 +1031,9 @@ sub search {
 
     if ( @tagnums ) {
       if ( $params->{'all_tags'} ) {
+        my $exists = $params->{'all_tags'} eq 'all' ? 'exists' : 'not exists';
         foreach ( @tagnums ) {
-          push @where, 'exists(select 1 from cust_tag where '.
+          push @where, $exists.'(select 1 from cust_tag where '.
                        'cust_tag.custnum = cust_main.custnum and tagnum = '.
                        $_ . ')';
         }
@@ -1060,10 +1078,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
+                JOIN cust_contact USING (contactnum)
+                WHERE cust_contact.custnum = cust_main.custnum
+                $first_query $last_query
+              ) ";
+    }
+
+    if ($contact_params->{'contacts_email'}) {
+      push @where,
+      "EXISTS ( SELECT 1 FROM contact_email
+                JOIN cust_contact USING (contactnum)
+                WHERE cust_contact.custnum = cust_main.custnum
+                AND contact_email.emailaddress = '" . $contact_params->{'contacts_email'} . "'
+              ) ";
+    }
+
+    if ( grep { /^contacts_phonetypenum(\d+)$/ } keys %{ $contact_params } ) {
+      my $phone_query;
+      foreach my $phone ( grep { /^contacts_phonetypenum(\d+)$/ } keys %{ $contact_params } ) {
+        $phone =~ /^contacts_phonetypenum(\d+)$/ or die "No phone type num $1 from $phone";
+        my $phonetypenum = $1;
+        (my $num = $contact_params->{$phone}) =~ s/\W//g;
+        if ( $num =~ /^1?(\d{3})(\d{3})(\d{4})(\d*)$/ ) { $contact_params->{$phone} = "$1$2$3"; }
+        $phone_query .= " AND ( contact_phone.phonetypenum = '".$phonetypenum."' AND contact_phone.phonenum = '" . $contact_params->{$phone} . "' )"
+        unless !$contact_params->{$phone};
+      }
+      push @where,
+      "EXISTS ( SELECT 1 FROM contact_phone
+                JOIN cust_contact USING (contactnum)
+                WHERE cust_contact.custnum = cust_main.custnum
+                $phone_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,
@@ -1083,8 +1149,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',
@@ -1098,44 +1162,7 @@ sub search {
   my @extra_fields      = ();
   my @extra_sort_fields = ();
 
-  ## search contacts
-  if ($params->{'contacts'}) {
-    my $contact_params = $params->{'contacts'};
-
-    $addl_from .=
-      ' LEFT JOIN cust_contact ON ( cust_main.custnum = cust_contact.custnum ) ';
-
-    if ($contact_params->{'contacts_firstname'} || $contact_params->{'contacts_lastname'}) {
-      $addl_from .= ' LEFT JOIN contact ON ( cust_contact.contactnum = contact.contactnum ) ';
-      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'};
-      $extra_sql .= " AND ( '1' $first_query $last_query )";
-    }
-
-    if ($contact_params->{'contacts_email'}) {
-      $addl_from .= ' LEFT JOIN contact_email ON ( cust_contact.contactnum = contact_email.contactnum ) ';
-      $extra_sql .= " AND ( contact_email.emailaddress = '" . $contact_params->{'contacts_email'} . "' )";
-    }
-
-    if ($contact_params->{'contacts_homephone'} || $contact_params->{'contacts_workphone'} || $contact_params->{'contacts_mobilephone'}) {
-      $addl_from .= ' LEFT JOIN contact_phone ON ( cust_contact.contactnum = contact_phone.contactnum ) ';
-      my $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'};
-      $extra_sql .= " AND ( '1' $home_query $work_query $mobile_query )";
-    }
-
-  }
+  my $count_query = "SELECT COUNT(DISTINCT cust_main.custnum) FROM cust_main $addl_from $extra_sql";
 
   if ($params->{'flattened_pkgs'}) {