X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2Fcust_main%2FSearch.pm;h=ef106b560bdd4e4cc6778e11f90d7cccb0cfa996;hb=2c04f1df05a1b14d9539bbae97275c1d3a0995e2;hp=a7f876fcca2602fd0a4daa51bd299b5a37c27e01;hpb=5ca9ba1f1e2831b1f22bbaeb414e68202093515a;p=freeside.git

diff --git a/FS/FS/cust_main/Search.pm b/FS/FS/cust_main/Search.pm
index a7f876fcc..ef106b560 100644
--- 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::payinfo_Mixin;
 
 @EXPORT_OK = qw( smart_search );
 
@@ -18,7 +19,12 @@ use FS::svc_acct;
 $DEBUG = 0;
 $me = '[FS::cust_main::Search]';
 
-@fuzzyfields = @FS::cust_main::fuzzyfields;
+@fuzzyfields = (
+  'cust_main.first', 'cust_main.last', 'cust_main.company', 
+  'cust_main.ship_company', # if you're using it
+  'cust_location.address1',
+  'contact.first',   'contact.last',
+);
 
 install_callback FS::UID sub { 
   $conf = new FS::Conf;
@@ -49,8 +55,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,
-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
@@ -64,7 +74,9 @@ sub smart_search {
   my %options = @_;
 
   #here is the agent virtualization
-  my $agentnums_sql = $FS::CurrentUser::CurrentUser->agentnums_sql;
+  my $agentnums_sql = 
+    $FS::CurrentUser::CurrentUser->agentnums_sql(table => 'cust_main');
+  my $agentnums_href = $FS::CurrentUser::CurrentUser->agentnums_href;
 
   my @cust_main = ();
 
@@ -78,19 +90,32 @@ sub smart_search {
     my $phonen = "$1-$2-$3";
     $phonen .= " x$4" if $4;
 
+    my $phonenum = "$1$2$3";
+    #my $extension = $4;
+
+    #cust_main phone numbers
     push @cust_main, qsearch( {
       'table'   => 'cust_main',
       'hashref' => { %options },
       'extra_sql' => ( scalar(keys %options) ? ' AND ' : ' WHERE ' ).
                      ' ( '.
                          join(' OR ', map "$_ = '$phonen'",
-                                          qw( daytime night fax
-                                              ship_daytime ship_night ship_fax )
+                                          qw( daytime night mobile fax )
                              ).
                      ' ) '.
                      " 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
 
@@ -100,8 +125,7 @@ sub smart_search {
         'extra_sql' => ( scalar(keys %options) ? ' AND ' : ' WHERE ' ).
                        ' ( '.
                            join(' OR ', map "$_ LIKE '$phonen\%'",
-                                            qw( daytime night
-                                                ship_daytime ship_night )
+                                            qw( daytime night )
                                ).
                        ' ) '.
                        " AND $agentnums_sql", #agent virtualization
@@ -109,14 +133,44 @@ sub smart_search {
 
     }
 
-  # custnum search (also try agent_custid), with some tweaking options if your
-  # legacy cust "numbers" have letters
   } 
+  
+  
+  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 =~ /^\s*(\d+)\s*$/
+  # 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*$/
             )
+         || ( $conf->config('cust_main-custnum-display_special')
+           # it's not currently possible for special prefixes to contain
+           # digits, so just strip off any alphabetic prefix and match 
+           # the rest to custnum
+              && $search =~ /^\s*[[:alpha:]]*(\d+)\s*$/
+            )
          || ( $conf->exists('address1-search' )
               && $search =~ /^\s*(\d+\-?\w*)\s*$/ #i.e. 1234A or 9432-D
             )
@@ -126,32 +180,52 @@ sub smart_search {
     my $num = $1;
 
     if ( $num =~ /^(\d+)$/ && $num <= 2147483647 ) { #need a bigint custnum? wow
+      my $agent_custid_null = $conf->exists('cust_main-default_agent_custid')
+                                ? ' AND agent_custid IS NULL ' : '';
       push @cust_main, qsearch( {
         'table'     => 'cust_main',
         'hashref'   => { 'custnum' => $num, %options },
-        'extra_sql' => " AND $agentnums_sql", #agent virtualization
+        'extra_sql' => " AND $agentnums_sql $agent_custid_null",
       } );
     }
 
+    # for all agents this user can see, if any of them have custnum prefixes 
+    # that match the search string, include customers that match the rest 
+    # of the custnum and belong to that agent
+    foreach my $agentnum ( keys %$agentnums_href ) {
+      my $p = $conf->config('cust_main-custnum-display_prefix', $agentnum);
+      next if !$p;
+      if ( $p eq substr($num, 0, length($p)) ) {
+        push @cust_main, qsearch( {
+          'table'   => 'cust_main',
+          'hashref' => { 'custnum' => 0 + substr($num, length($p)),
+                         'agentnum' => $agentnum,
+                          %options,
+                       },
+        } );
+      }
+    }
+
     push @cust_main, qsearch( {
-      'table'     => 'cust_main',
-      'hashref'   => { 'agent_custid' => $num, %options },
-      'extra_sql' => " AND $agentnums_sql", #agent virtualization
+        'table'     => 'cust_main',
+        'hashref'   => { 'agent_custid' => $num, %options },
+        'extra_sql' => " AND $agentnums_sql", #agent virtualization
     } );
 
     if ( $conf->exists('address1-search') ) {
       my $len = length($num);
       $num = lc($num);
-      foreach my $prefix ( '', 'ship_' ) {
-        push @cust_main, qsearch( {
-          'table'     => 'cust_main',
-          'hashref'   => { %options, },
-          'extra_sql' => 
-            ( keys(%options) ? ' AND ' : ' WHERE ' ).
-            " LOWER(SUBSTRING(${prefix}address1 FROM 1 FOR $len)) = '$num' ".
-            " AND $agentnums_sql",
-        } );
-      }
+      # probably the Right Thing: return customers that have any associated
+      # locations matching the string, not just bill/ship location
+      push @cust_main, qsearch( {
+        'table'     => 'cust_main',
+        'addl_from' => ' JOIN cust_location USING (custnum) ',
+        'hashref'   => { %options, },
+        'extra_sql' => 
+          ( keys(%options) ? ' AND ' : ' WHERE ' ).
+          " LOWER(SUBSTRING(cust_location.address1 FROM 1 FOR $len)) = '$num' ".
+          " AND $agentnums_sql",
+      } );
     }
 
   } elsif ( $search =~ /^\s*(\S.*\S)\s+\((.+), ([^,]+)\)\s*$/ ) {
@@ -163,23 +237,24 @@ sub smart_search {
     #so just do an exact search (but case-insensitive, so USPS standardization
     #doesn't throw a wrench in the works)
 
-    foreach my $prefix ( '', 'ship_' ) {
-      push @cust_main, qsearch( {
+    push @cust_main, qsearch( {
         'table'     => 'cust_main',
         'hashref'   => { %options },
         'extra_sql' => 
-          ( keys(%options) ? ' AND ' : ' WHERE ' ).
-          join(' AND ',
-            " LOWER(${prefix}first)   = ". dbh->quote(lc($first)),
-            " LOWER(${prefix}last)    = ". dbh->quote(lc($last)),
-            " LOWER(${prefix}company) = ". dbh->quote(lc($company)),
-            $agentnums_sql,
-          ),
-      } );
-    }
+        ( keys(%options) ? ' AND ' : ' WHERE ' ).
+        join(' AND ',
+          " LOWER(first)   = ". dbh->quote(lc($first)),
+          " LOWER(last)    = ". dbh->quote(lc($last)),
+          " LOWER(company) = ". dbh->quote(lc($company)),
+          $agentnums_sql,
+        ),
+      } ),
+
+    #contacts?
+    # probably not necessary for the "something a browser remembered" case
 
   } elsif ( $search =~ /^\s*(\S.*\S)\s*$/ ) { # value search
-                                              # try (ship_){last,company}
+                                              # try {first,last,company}
 
     my $value = lc($1);
 
@@ -214,17 +289,28 @@ sub smart_search {
 
       #exact
       my $sql = scalar(keys %options) ? ' AND ' : ' WHERE ';
-      $sql .= "
-        (     ( LOWER(last) = $q_last AND LOWER(first) = $q_first )
-           OR ( LOWER(ship_last) = $q_last AND LOWER(ship_first) = $q_first )
-        )";
+      $sql .= "( LOWER(cust_main.last) = $q_last AND LOWER(cust_main.first) = $q_first )";
 
+      #cust_main
       push @cust_main, qsearch( {
         'table'     => 'cust_main',
         '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)
 
     }
@@ -233,16 +319,29 @@ sub smart_search {
 
     #exact
     my $sql = scalar(keys %options) ? ' AND ' : ' WHERE ';
-    $sql .= " (    LOWER(last)          = $q_value
-                OR LOWER(company)       = $q_value
-                OR LOWER(ship_last)     = $q_value
-                OR LOWER(ship_company)  = $q_value
+    $sql .= " (    LOWER(cust_main.first)         = $q_value
+                OR LOWER(cust_main.last)          = $q_value
+                OR LOWER(cust_main.company)       = $q_value
+                OR LOWER(cust_main.ship_company)  = $q_value
             ";
-    $sql .= "   OR LOWER(address1)      = $q_value
-                OR LOWER(ship_address1) = $q_value
-            "
+
+    #address1 (yes, it's a kludge)
+    $sql .= "   OR EXISTS ( 
+                            SELECT 1 FROM cust_location 
+                              WHERE LOWER(cust_location.address1) = $q_value
+                                AND cust_location.custnum = cust_main.custnum
+                          )"
       if $conf->exists('address1-search');
-    $sql .= " )";
+
+    #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( {
       'table'     => 'cust_main',
@@ -259,38 +358,33 @@ sub smart_search {
 
       #substring
 
-      my @hashrefs = (
-        { 'company'      => { op=>'ILIKE', value=>"%$value%" }, },
-        { 'ship_company' => { op=>'ILIKE', value=>"%$value%" }, },
-      );
+      my @company_hashrefs = ();
+      if ( length($value) >= 3 ) {
+        @company_hashrefs = (
+          { 'company'      => { op=>'ILIKE', value=>"%$value%" }, },
+          { 'ship_company' => { op=>'ILIKE', value=>"%$value%" }, },
+        );
+      }
 
+      my @hashrefs = ();
       if ( $first && $last ) {
 
-        push @hashrefs,
+        @hashrefs = (
           { 'first'        => { op=>'ILIKE', value=>"%$first%" },
             'last'         => { op=>'ILIKE', value=>"%$last%" },
           },
-          { 'ship_first'   => { op=>'ILIKE', value=>"%$first%" },
-            'ship_last'    => { op=>'ILIKE', value=>"%$last%" },
-          },
-        ;
+        );
 
-      } else {
+      } elsif ( length($value) >= 3 ) {
 
-        push @hashrefs,
+        @hashrefs = (
+          { 'first'        => { op=>'ILIKE', value=>"%$value%" }, },
           { 'last'         => { op=>'ILIKE', value=>"%$value%" }, },
-          { 'ship_last'    => { op=>'ILIKE', value=>"%$value%" }, },
-        ;
-      }
+        );
 
-      if ( $conf->exists('address1-search') ) {
-        push @hashrefs,
-          { 'address1'      => { op=>'ILIKE', value=>"%$value%" }, },
-          { 'ship_address1' => { op=>'ILIKE', value=>"%$value%" }, },
-        ;
       }
 
-      foreach my $hashref ( @hashrefs ) {
+      foreach my $hashref ( @company_hashrefs, @hashrefs ) {
 
         push @cust_main, qsearch( {
           'table'     => 'cust_main',
@@ -302,33 +396,101 @@ sub smart_search {
 
       }
 
+      if ( $conf->exists('address1-search') && length($value) >= 3 ) {
+
+        push @cust_main, qsearch( {
+          table     => 'cust_main',
+          addl_from => 'JOIN cust_location USING (custnum)',
+          extra_sql => 'WHERE '.
+                        ' cust_location.address1 ILIKE '.dbh->quote("%$value%").
+                        " AND $agentnums_sql", #agent virtualizaiton
+        } );
+
+      }
+
+      #contact substring
+
+      foreach my $hashref ( @hashrefs ) {
+
+        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'   => { %$hashref,
+                                           #%options,
+                                         },
+                          #'extra_sql' => " AND $agentnums_sql", #agent virt
+                       });
+
+      }
+
       #fuzzy
-      my @fuzopts = (
-        \%options,                #hashref
-        '',                       #select
-        " AND $agentnums_sql",    #extra_sql  #agent virtualization
+      my %fuzopts = (
+        'hashref'   => \%options,
+        'select'    => '',
+        'extra_sql' => "WHERE $agentnums_sql",    #agent virtualization
       );
 
       if ( $first && $last ) {
         push @cust_main, FS::cust_main::Search->fuzzy_search(
           { 'last'   => $last,    #fuzzy hashref
             'first'  => $first }, #
-          @fuzopts
+          %fuzopts
+        );
+        push @cust_main, FS::cust_main::Search->fuzzy_search(
+          { 'contact.last'   => $last,    #fuzzy hashref
+            'contact.first'  => $first }, #
+          %fuzopts
+        );
+     }
+      foreach my $field ( 'first', 'last', 'company', 'ship_company' ) {
+        push @cust_main, FS::cust_main::Search->fuzzy_search(
+          { $field => $value },
+          %fuzopts
         );
       }
-      foreach my $field ( 'last', 'company' ) {
-        push @cust_main,
-          FS::cust_main::Search->fuzzy_search( { $field => $value }, @fuzopts );
+      foreach my $field ( 'first', 'last' ) {
+        push @cust_main, FS::cust_main::Search->fuzzy_search(
+          { "contact.$field" => $value },
+          %fuzopts
+        );
       }
       if ( $conf->exists('address1-search') ) {
         push @cust_main,
-          FS::cust_main::Search->fuzzy_search( { 'address1' => $value }, @fuzopts );
+          FS::cust_main::Search->fuzzy_search(
+            { 'cust_location.address1' => $value },
+            %fuzopts
+        );
       }
 
     }
 
   }
 
+  ( 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;
@@ -354,11 +516,9 @@ none or one).
 sub email_search {
   my %options = @_;
 
-  local($DEBUG) = 1;
-
   my $email = delete $options{'email'};
 
-  #we're only being used by RT at the moment... no agent virtualization yet
+  #no agent virtualization yet
   #my $agentnums_sql = $FS::CurrentUser::CurrentUser->agentnums_sql;
 
   my @cust_main = ();
@@ -424,6 +584,10 @@ HASHREF.  Valid parameters are
 
 =item address
 
+=item zip
+
+=item refnum
+
 =item cancelled_pkgs
 
 bool
@@ -432,6 +596,18 @@ bool
 
 listref of start date, end date
 
+=item birthdate
+
+listref of start date, end date
+
+=item spouse_birthdate
+
+listref of start date, end date
+
+=item anniversary_date
+
+listref of start date, end date
+
 =item payby
 
 listref
@@ -462,6 +638,34 @@ sub search {
   my @where = ();
   my $orderby;
 
+  # initialize these to prevent warnings
+  $params = {
+    'custnum'       => '',
+    'agentnum'      => '',
+    'usernum'       => '',
+    'status'        => '',
+    'address'       => '',
+    'zip'           => '',
+    'paydate_year'  => '',
+    'invoice_terms' => '',
+    'custbatch'     => '',
+    %$params
+  };
+
+  ##
+  # explicit custnum(s)
+  ##
+
+  if ( $params->{'custnum'} ) {
+    my @custnums = ref($params->{'custnum'}) ? 
+                      @{ $params->{'custnum'} } : 
+                      $params->{'custnum'};
+    push @where, 
+      'cust_main.custnum IN (' . 
+      join(',', map { $_ =~ /^(\d+)$/ ? $1 : () } @custnums ) .
+      ')' if scalar(@custnums) > 0;
+  }
+
   ##
   # parse agent
   ##
@@ -472,7 +676,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 ) {
@@ -491,16 +704,124 @@ sub search {
     push @where, FS::cust_main->$method();
   }
 
+  my $current = '';
+  unless ( $params->{location_history} ) {
+    $current = '
+      AND (    cust_location.locationnum IN ( cust_main.bill_locationnum,
+                                              cust_main.ship_locationnum
+                                            )
+            OR cust_location.locationnum IN (
+                 SELECT locationnum FROM cust_pkg
+                  WHERE cust_pkg.custnum = cust_main.custnum
+                    AND locationnum IS NOT NULL
+                    AND '. FS::cust_pkg->ncancelled_recurring_sql.'
+               )
+          )';
+  }
+
   ##
   # address
   ##
-  if ( $params->{'address'} =~ /\S/ ) {
-    my $address = dbh->quote('%'. lc($params->{'address'}). '%');
-    push @where, '('. join(' OR ',
-                             map "LOWER($_) LIKE $address",
-                               qw(address1 address2 ship_address1 ship_address2)
-                          ).
-                 ')';
+  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).")
+          $current
+        )";
+    }
+  }
+
+  ##
+  # city
+  ##
+  if ( $params->{'city'} =~ /\S/ ) {
+    my $city = dbh->quote($params->{'city'});
+    push @where, "EXISTS(
+      SELECT 1 FROM cust_location
+      WHERE cust_location.custnum = cust_main.custnum
+        AND cust_location.city = $city
+        $current
+    )";
+  }
+
+  ##
+  # county
+  ##
+  if ( $params->{'county'} =~ /\S/ ) {
+    my $county = dbh->quote($params->{'county'});
+    push @where, "EXISTS(
+      SELECT 1 FROM cust_location
+      WHERE cust_location.custnum = cust_main.custnum
+        AND cust_location.county = $county
+        $current
+    )";
+  }
+
+  ##
+  # state
+  ##
+  if ( $params->{'state'} =~ /\S/ ) {
+    my $state = dbh->quote($params->{'state'});
+    push @where, "EXISTS(
+      SELECT 1 FROM cust_location
+      WHERE cust_location.custnum = cust_main.custnum
+        AND cust_location.state = $state
+        $current
+    )";
+  }
+
+  ##
+  # zipcode
+  ##
+  if ( $params->{'zip'} =~ /\S/ ) {
+    my $zip = dbh->quote($params->{'zip'} . '%');
+    push @where, "EXISTS(
+      SELECT 1 FROM cust_location
+      WHERE cust_location.custnum = cust_main.custnum
+        AND cust_location.zip LIKE $zip
+        $current
+    )";
+  }
+
+  ##
+  # country
+  ##
+  if ( $params->{'country'} =~ /^(\w\w)$/ ) {
+    my $country = uc($1);
+    push @where, "EXISTS(
+      SELECT 1 FROM cust_location
+      WHERE cust_location.custnum = cust_main.custnum
+        AND cust_location.country = '$country'
+        $current
+    )";
+  }
+
+  ###
+  # refnum
+  ###
+  if ( $params->{'refnum'}  ) {
+
+    my @refnum = ref( $params->{'refnum'} )
+                   ? @{ $params->{'refnum'} }
+                   :  ( $params->{'refnum'} );
+
+    @refnum = grep /^(\d*)$/, @refnum;
+
+    push @where, '( '. join(' OR ', map "cust_main.refnum = $_", @refnum ). ' )'
+      if @refnum;
+
   }
 
   ##
@@ -513,24 +834,53 @@ sub search {
     unless $params->{'cancelled_pkgs'};
 
   ##
-  # parse without census tract checkbox
+  # "with email address(es)" checkbox
+  ##
+
+  push @where,
+    'EXISTS ( SELECT 1 FROM cust_main_invoice
+                WHERE cust_main_invoice.custnum = cust_main.custnum
+                  AND length(dest) > 5
+            )'  # AND dest LIKE '%@%'
+    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
   ##
 
-  push @where, "(censustract = '' or censustract is null)"
-    if $params->{'no_censustract'};
+  push @where,
+    "NOT EXISTS ( SELECT 1 FROM cust_main_invoice
+                    WHERE cust_main_invoice.custnum = cust_main.custnum
+                      AND dest = 'POST' )"
+    if $params->{'no_POST'};
 
   ##
-  # parse with hardcoded tax location checkbox
+  # "tax exempt" checkbox
   ##
+  push @where, "cust_main.tax = 'Y'"
+    if $params->{'tax'};
 
-  push @where, "geocode is not null"
-    if $params->{'with_geocode'};
+  ##
+  # "not tax exempt" checkbox
+  ##
+  push @where, "(cust_main.tax = '' OR cust_main.tax IS NULL )"
+    if $params->{'no_tax'};
 
   ##
   # dates
   ##
 
-  foreach my $field (qw( signupdate )) {
+  foreach my $field (qw( signupdate birthdate spouse_birthdate anniversary_date )) {
 
     next unless exists($params->{$field});
 
@@ -541,11 +891,13 @@ sub search {
       "cust_main.$field >= $beginning",
       "cust_main.$field <= $ending";
 
-    # XXX: do this for mysql and/or pull it out of here
-    if(defined $hour) {
-      if ($dbh->{Driver}->{Name} eq 'Pg') {
+    if($field eq 'signupdate' && defined $hour) {
+      if ($dbh->{Driver}->{Name} =~ /Pg/i) {
         push @where, "extract(hour from to_timestamp(cust_main.$field)) = $hour";
       }
+      elsif( $dbh->{Driver}->{Name} =~ /mysql/i) {
+        push @where, "hour(from_unixtime(cust_main.$field)) = $hour"
+      }
       else {
         warn "search by time of day not supported on ".$dbh->{Driver}->{Name}." databases";
       }
@@ -656,6 +1008,58 @@ sub search {
     push @where,
       "cust_main.custbatch = '$1'";
   }
+  
+  if ( $params->{'tagnum'} ) {
+    my @tagnums = ref( $params->{'tagnum'} ) ? @{ $params->{'tagnum'} } : ( $params->{'tagnum'} );
+
+    @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;
+      }
+    }
+  }
+
+  # pkg_classnum
+  #   all_pkg_classnums
+  #   any_pkg_status
+  if ( $params->{'pkg_classnum'} ) {
+    my @pkg_classnums = ref( $params->{'pkg_classnum'} ) ?
+                          @{ $params->{'pkg_classnum'} } :
+                             $params->{'pkg_classnum'};
+    @pkg_classnums = grep /^(\d+)$/, @pkg_classnums;
+
+    if ( @pkg_classnums ) {
+
+      my @pkg_where;
+      if ( $params->{'all_pkg_classnums'} ) {
+        push @pkg_where, "part_pkg.classnum = $_" foreach @pkg_classnums;
+      } else {
+        push @pkg_where,
+          'part_pkg.classnum IN('. join(',', @pkg_classnums).')';
+      }
+      foreach (@pkg_where) {
+        my $select_pkg = 
+          "SELECT 1 FROM cust_pkg JOIN part_pkg USING (pkgpart) WHERE ".
+          "cust_pkg.custnum = cust_main.custnum AND $_ ";
+        if ( not $params->{'any_pkg_status'} ) {
+          $select_pkg .= 'AND '.FS::cust_pkg->active_sql;
+        }
+        push @where, "EXISTS($select_pkg)";
+      }
+    }
+  }
 
   ##
   # setup queries, subs, etc. for the search
@@ -664,16 +1068,31 @@ sub search {
   $orderby ||= 'ORDER BY custnum';
 
   # here is the agent virtualization
-  push @where, $FS::CurrentUser::CurrentUser->agentnums_sql;
+  push @where,
+    $FS::CurrentUser::CurrentUser->agentnums_sql(table => 'cust_main');
 
   my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : '';
 
-  my $addl_from = 'LEFT JOIN cust_pkg USING ( custnum  ) ';
+  my $addl_from = '';
+  # always make address fields available in results
+  for my $pre ('bill_', 'ship_') {
+    $addl_from .= 
+      'LEFT JOIN cust_location AS '.$pre.'location '.
+      'ON (cust_main.'.$pre.'locationnum = '.$pre.'location.locationnum) ';
+  }
+
+  # always make referral available in results
+  #   (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 $extra_sql";
+  my $count_query = "SELECT COUNT(*) FROM cust_main $addl_from $extra_sql";
 
-  my $select = join(', ', 
+  my @select = (
                  'cust_main.custnum',
+                 'cust_main.salesnum',
+                 # there's a good chance that we'll need these
+                 'cust_main.bill_locationnum',
+                 'cust_main.ship_locationnum',
                  FS::UI::Web::cust_sql_fields($params->{'cust_fields'}),
                );
 
@@ -682,19 +1101,35 @@ sub search {
 
   if ($params->{'flattened_pkgs'}) {
 
-    if ($dbh->{Driver}->{Name} eq 'Pg') {
+    #my $pkg_join = '';
+    $addl_from .=
+      ' LEFT JOIN cust_pkg ON ( cust_main.custnum = cust_pkg.custnum ) ';
 
-      $select .= ", array_to_string(array(select pkg from cust_pkg left join part_pkg using ( pkgpart ) where cust_main.custnum = cust_pkg.custnum $pkgwhere),'|') as magic";
+    if ($dbh->{Driver}->{Name} eq 'Pg') {
 
-    }elsif ($dbh->{Driver}->{Name} =~ /^mysql/i) {
-      $select .= ", GROUP_CONCAT(pkg SEPARATOR '|') as magic";
-      $addl_from .= " LEFT JOIN part_pkg using ( pkgpart )";
-    }else{
+      push @select, "
+        ARRAY_TO_STRING(
+          ARRAY(
+            SELECT pkg FROM cust_pkg LEFT JOIN part_pkg USING ( pkgpart )
+              WHERE cust_main.custnum = cust_pkg.custnum $pkgwhere
+          ), '|'
+        ) AS magic
+      ";
+
+    } elsif ($dbh->{Driver}->{Name} =~ /^mysql/i) {
+      push @select, "GROUP_CONCAT(part_pkg.pkg SEPARATOR '|') as magic";
+      $addl_from .= ' LEFT JOIN part_pkg USING ( pkgpart ) ';
+      #$pkg_join  .= ' LEFT JOIN part_pkg USING ( pkgpart ) ';
+    } else {
       warn "warning: unknown database type ". $dbh->{Driver}->{Name}. 
-           "omitting packing information from report.";
+           "omitting package information from report.";
     }
 
-    my $header_query = "SELECT COUNT(cust_pkg.custnum = cust_main.custnum) AS count FROM cust_main $addl_from $extra_sql $pkgwhere group by cust_main.custnum order by count desc limit 1";
+    my $header_query = "
+      SELECT COUNT(cust_pkg.custnum = cust_main.custnum) AS count
+        FROM cust_main $addl_from $extra_sql $pkgwhere
+          GROUP BY cust_main.custnum ORDER BY count DESC LIMIT 1
+    ";
 
     my $sth = dbh->prepare($header_query) or die dbh->errstr;
     $sth->execute() or die $sth->errstr;
@@ -711,20 +1146,12 @@ sub search {
 
   }
 
-  if ( $params->{'with_geocode'} ) {
-
-    unshift @extra_headers, 'Tax location override', 'Calculated tax location';
-    unshift @extra_fields, sub { my $c = shift; $c->get('geocode'); },
-                           sub { my $c = shift;
-                                 $c->set('geocode', '');
-                                 $c->geocode('cch'); #XXX only cch right now
-                               };
-
-  }
+  my $select = join(', ', @select);
 
   my $sql_query = {
     'table'         => 'cust_main',
     'select'        => $select,
+    'addl_from'     => $addl_from,
     'hashref'       => {},
     'extra_sql'     => $extra_sql,
     'order_by'      => $orderby,
@@ -732,25 +1159,40 @@ sub search {
     'extra_headers' => \@extra_headers,
     'extra_fields'  => \@extra_fields,
   };
+  #warn Data::Dumper::Dumper($sql_query);
+  $sql_query;
 
 }
 
-=item fuzzy_search FUZZY_HASHREF [ HASHREF, SELECT, EXTRA_SQL, CACHE_OBJ ]
+=item fuzzy_search FUZZY_HASHREF [ OPTS ]
 
 Performs a fuzzy (approximate) search and returns the matching FS::cust_main
 records.  Currently, I<first>, I<last>, I<company> and/or I<address1> may be
-specified (the appropriate ship_ field is also searched).
+specified.
 
 Additional options are the same as FS::Record::qsearch
 
 =cut
 
 sub fuzzy_search {
-  my( $self, $fuzzy, $hash, @opt) = @_;
-  #$self
-  $hash ||= {};
+  my $self = shift;
+  my $fuzzy = shift;
+  # sensible defaults, then merge in any passed options
+  my %fuzopts = (
+    'table'     => 'cust_main',
+    'addl_from' => '',
+    'extra_sql' => '',
+    'hashref'   => {},
+    @_
+  );
+
   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 ) {
 
@@ -758,15 +1200,33 @@ sub fuzzy_search {
     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 (' .
+                     join(',', map { dbh->quote($_) } keys %match) .
+                     ')';
 
-    my @fcust = ();
-    foreach ( keys %match ) {
-      push @fcust, qsearch('cust_main', { %$hash, $field=>$_}, @opt);
-      push @fcust, qsearch('cust_main', { %$hash, "ship_$field"=>$_}, @opt);
+    my $extra_sql = $fuzopts{extra_sql};
+    if ($extra_sql =~ /^\s*where /i or keys %{ $fuzopts{hashref} }) {
+      $extra_sql .= ' AND ';
+    } else {
+      $extra_sql .= 'WHERE ';
+    }
+    $extra_sql .= "$field $in_matches";
+
+    my $addl_from = $fuzopts{addl_from};
+    if ( $field =~ /^cust_location\./ ) {
+      $addl_from .= ' JOIN cust_location USING (custnum)';
+    } elsif ( $field =~ /^contact\./ ) {
+      $addl_from .= ' JOIN contact USING (custnum)';
     }
-    my %fsaw = ();
-    push @cust_main, grep { ! $fsaw{$_->custnum}++ } @fcust;
+
+    push @cust_main, qsearch({
+      %fuzopts,
+      'addl_from' => $addl_from,
+      'extra_sql' => $extra_sql,
+    });
   }
 
   # we want the components of $fuzzy ANDed, not ORed, but still don't want dupes
@@ -789,7 +1249,14 @@ sub fuzzy_search {
 
 sub check_and_rebuild_fuzzyfiles {
   my $dir = $FS::UID::conf_dir. "/cache.". $FS::UID::datasrc;
-  rebuild_fuzzyfiles() if grep { ! -e "$dir/cust_main.$_" } @fuzzyfields
+  rebuild_fuzzyfiles()
+    if grep { ! -e "$dir/$_" }
+         map {
+               my ($field, $table) = reverse split('\.', $_);
+               $table ||= 'cust_main';
+               "$table.$field"
+             }
+           @fuzzyfields;
 }
 
 =item rebuild_fuzzyfiles
@@ -805,42 +1272,99 @@ sub rebuild_fuzzyfiles {
 
   foreach my $fuzzy ( @fuzzyfields ) {
 
-    open(LOCK,">>$dir/cust_main.$fuzzy")
-      or die "can't open $dir/cust_main.$fuzzy: $!";
-    flock(LOCK,LOCK_EX)
-      or die "can't lock $dir/cust_main.$fuzzy: $!";
+    my ($field, $table) = reverse split('\.', $fuzzy);
+    $table ||= 'cust_main';
 
-    open (CACHE,">$dir/cust_main.$fuzzy.tmp")
-      or die "can't open $dir/cust_main.$fuzzy.tmp: $!";
+    open(LOCK,">>$dir/$table.$field")
+      or die "can't open $dir/$table.$field: $!";
+    flock(LOCK,LOCK_EX)
+      or die "can't lock $dir/$table.$field: $!";
 
-    foreach my $field ( $fuzzy, "ship_$fuzzy" ) {
-      my $sth = dbh->prepare("SELECT $field FROM cust_main".
-                             " WHERE $field != '' AND $field IS NOT NULL");
-      $sth->execute or die $sth->errstr;
+    open (CACHE, '>:encoding(UTF-8)', "$dir/$table.$field.tmp")
+      or die "can't open $dir/$table.$field.tmp: $!";
 
-      while ( my $row = $sth->fetchrow_arrayref ) {
-        print CACHE $row->[0]. "\n";
-      }
+    my $sth = dbh->prepare(
+      "SELECT $field FROM $table WHERE $field IS NOT NULL AND $field != ''"
+    );
+    $sth->execute or die $sth->errstr;
 
-    } 
+    while ( my $row = $sth->fetchrow_arrayref ) {
+      print CACHE $row->[0]. "\n";
+    }
 
-    close CACHE or die "can't close $dir/cust_main.$fuzzy.tmp: $!";
+    close CACHE or die "can't close $dir/$table.$field.tmp: $!";
   
-    rename "$dir/cust_main.$fuzzy.tmp", "$dir/cust_main.$fuzzy";
+    rename "$dir/$table.$field.tmp", "$dir/$table.$field";
     close LOCK;
   }
 
 }
 
+=item append_fuzzyfiles FIRSTNAME LASTNAME COMPANY ADDRESS1
+
+=cut
+
+sub append_fuzzyfiles {
+  #my( $first, $last, $company ) = @_;
+
+  check_and_rebuild_fuzzyfiles();
+
+  #foreach my $fuzzy (@fuzzyfields) {
+  foreach my $fuzzy ( 'cust_main.first', 'cust_main.last', 'cust_main.company', 
+                      'cust_location.address1',
+                      'cust_main.ship_company',
+                    ) {
+
+    append_fuzzyfiles_fuzzyfield($fuzzy, shift);
+
+  }
+
+  1;
+}
+
+=item append_fuzzyfiles_fuzzyfield COLUMN VALUE
+
+=item append_fuzzyfiles_fuzzyfield TABLE.COLUMN VALUE
+
+=cut
+
+use Fcntl qw(:flock);
+sub append_fuzzyfiles_fuzzyfield {
+  my( $fuzzyfield, $value ) = @_;
+
+  my $dir = $FS::UID::conf_dir. "/cache.". $FS::UID::datasrc;
+
+
+  my ($field, $table) = reverse split('\.', $fuzzyfield);
+  $table ||= 'cust_main';
+
+  return unless length($value);
+
+  open(CACHE, '>>:encoding(UTF-8)', "$dir/$table.$field" )
+    or die "can't open $dir/$table.$field: $!";
+  flock(CACHE,LOCK_EX)
+    or die "can't lock $dir/$table.$field: $!";
+
+  print CACHE "$value\n";
+
+  flock(CACHE,LOCK_UN)
+    or die "can't unlock $dir/$table.$field: $!";
+  close CACHE;
+
+}
+
 =item all_X
 
 =cut
 
 sub all_X {
-  my( $self, $field ) = @_;
+  my( $self, $fuzzy ) = @_;
+  my ($field, $table) = reverse split('\.', $fuzzy);
+  $table ||= 'cust_main';
+
   my $dir = $FS::UID::conf_dir. "/cache.". $FS::UID::datasrc;
-  open(CACHE,"<$dir/cust_main.$field")
-    or die "can't open $dir/cust_main.$field: $!";
+  open(CACHE, '<:encoding(UTF-8)', "$dir/$table.$field")
+    or die "can't open $dir/$table.$field: $!";
   my @array = map { chomp; $_; } <CACHE>;
   close CACHE;
   \@array;