Merge branch 'master' of git.freeside.biz:/home/git/freeside

[freeside.git] / FS / FS / cust_main / Search.pm

diff --git a/FS/FS/cust_main/Search.pm b/FS/FS/cust_main/Search.pm
index 2c7c046..182527f 100644 (file)
--- a/FS/FS/cust_main/Search.pm
+++ b/FS/FS/cust_main/Search.pm
@@ -9,6 +9,7 @@ use FS::Record qw( qsearch );
 use FS::cust_main;
 use FS::cust_main_invoice;
 use FS::svc_acct;
 use FS::cust_main;
 use FS::cust_main_invoice;
 use FS::svc_acct;

+use FS::payinfo_Mixin;

 
 @EXPORT_OK = qw( smart_search );
 
 
 @EXPORT_OK = qw( smart_search );
 


@@ -50,8 +51,12 @@ FS::cust_main::Search - Customer searching
 
 Accepts the following options: I<search>, the string to search for.  The string
 will be searched for as a customer number, phone number, name or company name,
 
 Accepts the following options: I<search>, the string to search for.  The string
 will be searched for as a customer number, phone number, name or company name,

-as an exact, or, in some cases, a substring or fuzzy match (see the source code
-for the exact heuristics used); I<no_fuzzy_on_exact>, causes smart_search to
+address (if address1-search is on), invoicing email address, or credit card
+number.
+
+Searches match as an exact, or, in some cases, a substring or fuzzy match (see
+the source code for the exact heuristics used); I<no_fuzzy_on_exact>, causes
+smart_search to

 skip fuzzy matching when an exact match is found.
 
 Any additional options are treated as an additional qualifier on the search
 skip fuzzy matching when an exact match is found.
 
 Any additional options are treated as an additional qualifier on the search


@@ -109,12 +114,10 @@ sub smart_search {
 
     }
 
 
     }
 

-  # custnum search (also try agent_custid), with some tweaking options if your
-  # legacy cust "numbers" have letters

   } 
   
   
   } 
   
   

-  if ( $search =~ /@/ ) {
+  if ( $search =~ /@/ ) { #invoicing email address

       push @cust_main,
          map $_->cust_main,
              qsearch( {
       push @cust_main,
          map $_->cust_main,
              qsearch( {


@@ -122,6 +125,9 @@ sub smart_search {
                         'hashref'   => { 'dest' => $search },
                       }
                     );
                         'hashref'   => { 'dest' => $search },
                       }
                     );

+
+  # custnum search (also try agent_custid), with some tweaking options if your
+  # legacy cust "numbers" have letters

   } elsif ( $search =~ /^\s*(\d+)\s*$/
          || ( $conf->config('cust_main-agent_custid-format') eq 'ww?d+'
               && $search =~ /^\s*(\w\w?\d+)\s*$/
   } elsif ( $search =~ /^\s*(\d+)\s*$/
          || ( $conf->config('cust_main-agent_custid-format') eq 'ww?d+'
               && $search =~ /^\s*(\w\w?\d+)\s*$/


@@ -364,6 +370,28 @@ sub smart_search {
 
   }
 
 
   }
 

+  ( my $nospace_search = $search ) =~ s/\s//g;
+  ( my $card_search = $nospace_search ) =~ s/\-//g;
+  $card_search =~ s/[x\*\.\_]/x/gi;
+  
+  if ( $card_search =~ /^[\dx]{15,16}$/i ) { #credit card search
+
+    ( my $like_search = $card_search ) =~ s/x/_/g;
+    my $mask_search = FS::payinfo_Mixin->mask_payinfo('CARD', $card_search);
+
+    push @cust_main, qsearch({
+      'table'     => 'cust_main',
+      'hashref'   => {},
+      'extra_sql' => " WHERE (    payinfo LIKE '$like_search'
+                               OR paymask =    '$mask_search'
+                             ) ".
+                     " AND payby IN ('CARD','DCRD') ".
+                     " AND $agentnums_sql", #agent virtulization
+    });
+
+  }
+  
+

   #eliminate duplicates
   my %saw = ();
   @cust_main = grep { !$saw{$_->custnum}++ } @cust_main;
   #eliminate duplicates
   my %saw = ();
   @cust_main = grep { !$saw{$_->custnum}++ } @cust_main;


@@ -551,7 +579,16 @@ sub search {
   }
 
   ##
   }
 
   ##

-  # do the same for user
+  # parse sales person
+  ##
+
+  if ( $params->{'salesnum'} =~ /^(\d+)$/ ) {
+    push @where, ($1 > 0 ) ? "cust_main.salesnum = $1"
+                           : 'cust_main.salesnum IS NULL';
+  }
+
+  ##
+  # parse usernum

   ##
 
   if ( $params->{'usernum'} =~ /^(\d+)$/ and $1 ) {
   ##
 
   if ( $params->{'usernum'} =~ /^(\d+)$/ and $1 ) {


@@ -573,14 +610,24 @@ sub search {
   ##
   # address
   ##
   ##
   # address
   ##

-  if ( $params->{'address'} =~ /\S/ ) {
-    my $address = dbh->quote('%'. lc($params->{'address'}). '%');
-    push @where, "EXISTS(
-      SELECT 1 FROM cust_location 
-      WHERE cust_location.custnum = cust_main.custnum
-        AND (LOWER(cust_location.address1) LIKE $address OR
-             LOWER(cust_location.address2) LIKE $address)
-    )";
+  if ( $params->{'address'} ) {
+    # allow this to be an arrayref
+    my @values = ($params->{'address'});
+    @values = @{$values[0]} if ref($values[0]);
+    my @orwhere;
+    foreach (grep /\S/, @values) {
+      my $address = dbh->quote('%'. lc($_). '%');
+      push @orwhere,
+        "LOWER(cust_location.address1) LIKE $address",
+        "LOWER(cust_location.address2) LIKE $address";
+    }
+    if (@orwhere) {
+      push @where, "EXISTS(
+        SELECT 1 FROM cust_location 
+        WHERE cust_location.custnum = cust_main.custnum
+          AND (".join(' OR ',@orwhere).")
+        )";
+    }

   }
 
   ##
   }
 
   ##


@@ -624,14 +671,16 @@ sub search {
   # parse without census tract checkbox
   ##
 
   # parse without census tract checkbox
   ##
 

-  push @where, "(censustract = '' or censustract is null)"
+  push @where, "(ship_location.censustract = '' or ship_location.censustract is null)"

     if $params->{'no_censustract'};
 
   ##
   # parse with hardcoded tax location checkbox
   ##
 
     if $params->{'no_censustract'};
 
   ##
   # parse with hardcoded tax location checkbox
   ##
 

-  push @where, "geocode is not null"
+  my $tax_prefix = FS::Conf->new->exists('tax-ship_location') ? 'ship_' 
+                                                              : 'bill_';
+  push @where, "${tax_prefix}location.geocode is not null"

     if $params->{'with_geocode'};
 
   ##
     if $params->{'with_geocode'};
 
   ##


@@ -646,6 +695,16 @@ sub search {
     if $params->{'with_email'};
 
   ##
     if $params->{'with_email'};
 
   ##

+  # "with postal mail invoices" checkbox
+  ##
+
+  push @where,
+    "EXISTS ( SELECT 1 FROM cust_main_invoice
+                WHERE cust_main_invoice.custnum = cust_main.custnum
+                  AND dest = 'POST' )"
+    if $params->{'POST'};
+
+  ##

   # "without postal mail invoices" checkbox
   ##
 
   # "without postal mail invoices" checkbox
   ##
 


@@ -794,11 +853,19 @@ sub search {
     @tagnums = grep /^(\d+)$/, @tagnums;
 
     if ( @tagnums ) {
     @tagnums = grep /^(\d+)$/, @tagnums;
 
     if ( @tagnums ) {

+      if ( $params->{'all_tags'} ) {
+        foreach ( @tagnums ) {
+          push @where, 'exists(select 1 from cust_tag where '.
+                       'cust_tag.custnum = cust_main.custnum and tagnum = '.
+                       $_ . ')';
+        }
+      } else { # matching any tag, not all

        my $tags_where = "0 < (select count(1) from cust_tag where " 
                . " cust_tag.custnum = cust_main.custnum and tagnum in ("
                . join(',', @tagnums) . "))";
 
        push @where, $tags_where;
        my $tags_where = "0 < (select count(1) from cust_tag where " 
                . " cust_tag.custnum = cust_main.custnum and tagnum in ("
                . join(',', @tagnums) . "))";
 
        push @where, $tags_where;

+      }

     }
   }
 
     }
   }
 


@@ -823,7 +890,7 @@ sub search {
       'ON (cust_main.'.$pre.'locationnum = '.$pre.'location.locationnum) ';
   }
 
       'ON (cust_main.'.$pre.'locationnum = '.$pre.'location.locationnum) ';
   }
 

-  my $count_query = "SELECT COUNT(*) FROM cust_main $extra_sql";
+  my $count_query = "SELECT COUNT(*) FROM cust_main $addl_from $extra_sql";

 
   my @select = (
                  'cust_main.custnum',
 
   my @select = (
                  'cust_main.custnum',


@@ -839,7 +906,8 @@ sub search {
   if ($params->{'flattened_pkgs'}) {
 
     #my $pkg_join = '';
   if ($params->{'flattened_pkgs'}) {
 
     #my $pkg_join = '';

-    $addl_from .= ' LEFT JOIN cust_pkg USING ( custnum ) ';
+    $addl_from .=
+      ' LEFT JOIN cust_pkg ON ( cust_main.custnum = cust_pkg.custnum ) ';

 
     if ($dbh->{Driver}->{Name} eq 'Pg') {
 
 
     if ($dbh->{Driver}->{Name} eq 'Pg') {
 


@@ -908,6 +976,8 @@ sub search {
     'extra_headers' => \@extra_headers,
     'extra_fields'  => \@extra_fields,
   };
     'extra_headers' => \@extra_headers,
     'extra_fields'  => \@extra_fields,
   };

+  warn Data::Dumper::Dumper($sql_query);
+  $sql_query;

 
 }
 
 
 }
 


@@ -935,6 +1005,11 @@ sub fuzzy_search {
 
   my @cust_main = ();
 
 
   my @cust_main = ();
 

+  my @fuzzy_mod = 'i';
+  my $conf = new FS::Conf;
+  my $fuzziness = $conf->config('fuzzy-fuzziness');
+  push @fuzzy_mod, $fuzziness if $fuzziness;
+

   check_and_rebuild_fuzzyfiles();
   foreach my $field ( keys %$fuzzy ) {
 
   check_and_rebuild_fuzzyfiles();
   foreach my $field ( keys %$fuzzy ) {
 


@@ -942,7 +1017,7 @@ sub fuzzy_search {
     next unless scalar(@$all);
 
     my %match = ();
     next unless scalar(@$all);
 
     my %match = ();

-    $match{$_}=1 foreach ( amatch( $fuzzy->{$field}, ['i'], @$all ) );
+    $match{$_}=1 foreach ( amatch( $fuzzy->{$field}, \@fuzzy_mod, @$all ) );

     next if !keys(%match);
 
     my $in_matches = 'IN (' .
     next if !keys(%match);
 
     my $in_matches = 'IN (' .