X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;ds=sidebyside;f=FS%2FFS%2Fcust_main%2FSearch.pm;h=37b8ec8a44687b38672cd6d33630b5ca337e3b30;hb=816b40df3a9fbc155060842999ff8edc155e91d7;hp=7216fe604227433588b6ef4125b58988d0d5ada4;hpb=b1d26754480ee77604521236d9cf9952df2d3e12;p=freeside.git

diff --git a/FS/FS/cust_main/Search.pm b/FS/FS/cust_main/Search.pm
index 7216fe604..37b8ec8a4 100644
--- a/FS/FS/cust_main/Search.pm
+++ b/FS/FS/cust_main/Search.pm
@@ -618,7 +618,7 @@ sub search {
   my $dbh = dbh;
 
   my @where = ();
-  my $orderby = "ORDER BY cust_main.custnum";
+  my $orderby;
 
   # initialize these to prevent warnings
   $params = {
@@ -789,6 +789,35 @@ sub search {
     )";
   }
 
+  ##
+  # no_censustract
+  ##
+  if ( $params->{'no_censustract'} ) {
+    push @where, "EXISTS(
+      SELECT 1 FROM cust_location
+      WHERE locationnum = cust_main.ship_locationnum
+        AND cust_location.country = 'US'
+        AND (    cust_location.censusyear IS NULL
+              OR cust_location.censusyear != '2020'
+            )
+    )";
+  }
+
+  ##
+  # phones
+  ##
+
+  foreach my $phonet (qw(daytime night mobile fax)) {
+    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
   ###
@@ -937,7 +966,7 @@ sub search {
       }
     }
 
-    $orderby .= ", cust_main.$field";
+    $orderby ||= "ORDER BY cust_main.$field";
 
   }
 
@@ -1090,25 +1119,24 @@ sub search {
               ) ";
     }
 
-    if ($contact_params->{'contacts_homephone'} || $contact_params->{'contacts_workphone'} || $contact_params->{'contacts_mobilephone'}) {
-      foreach my $phone (qw( contacts_homephone contacts_workphone contacts_mobilephone )) {
+    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};
       }
-      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 cust_contact USING (contactnum)
                 WHERE cust_contact.custnum = cust_main.custnum
-                $home_query $work_query $mobile_query
+                $phone_query
               ) ";
     }
-}
+  }
 
 
   ##
@@ -1213,8 +1241,7 @@ sub search {
 
   }
 
-  # set select a customer only once. some joins create multiple customer records.
-  my $select = 'DISTINCT ON (cust_main.custnum) ' . join(', ', @select);
+  my $select = join(', ', @select);
 
   my $sql_query = {
     'table'             => 'cust_main',