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 06a4522..6c79315 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 );
 


@@ -18,7 +19,8 @@ use FS::svc_acct;
 $DEBUG = 0;
 $me = '[FS::cust_main::Search]';
 
 $DEBUG = 0;
 $me = '[FS::cust_main::Search]';
 

-@fuzzyfields = @FS::cust_main::fuzzyfields;
+@fuzzyfields = ( 'cust_main.first', 'cust_main.last', 'cust_main.company', 
+  'cust_location.address1' );

 
 install_callback FS::UID sub { 
   $conf = new FS::Conf;
 
 install_callback FS::UID sub { 
   $conf = new FS::Conf;


@@ -49,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


@@ -64,7 +70,8 @@ sub smart_search {
   my %options = @_;
 
   #here is the agent virtualization
   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 @cust_main = ();
 
 
   my @cust_main = ();
 


@@ -84,8 +91,7 @@ sub smart_search {
       'extra_sql' => ( scalar(keys %options) ? ' AND ' : ' WHERE ' ).
                      ' ( '.
                          join(' OR ', map "$_ = '$phonen'",
       '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
                              ).
                      ' ) '.
                      " AND $agentnums_sql", #agent virtualization


@@ -100,8 +106,7 @@ sub smart_search {
         'extra_sql' => ( scalar(keys %options) ? ' AND ' : ' WHERE ' ).
                        ' ( '.
                            join(' OR ', map "$_ LIKE '$phonen\%'",
         '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
                                ).
                        ' ) '.
                        " AND $agentnums_sql", #agent virtualization


@@ -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,10 +125,19 @@ 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*$/
             )

+         || ( $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
             )
          || ( $conf->exists('address1-search' )
               && $search =~ /^\s*(\d+\-?\w*)\s*$/ #i.e. 1234A or 9432-D
             )


@@ -135,32 +147,52 @@ sub smart_search {
     my $num = $1;
 
     if ( $num =~ /^(\d+)$/ && $num <= 2147483647 ) { #need a bigint custnum? wow
     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 },
       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 ( $FS::CurrentUser::CurrentUser->agentnums ) {
+      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( {
     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);
     } );
 
     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*$/ ) {
     }
 
   } elsif ( $search =~ /^\s*(\S.*\S)\s+\((.+), ([^,]+)\)\s*$/ ) {


@@ -172,20 +204,19 @@ sub smart_search {
     #so just do an exact search (but case-insensitive, so USPS standardization
     #doesn't throw a wrench in the works)
 
     #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' => 
         '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?

 
   } elsif ( $search =~ /^\s*(\S.*\S)\s*$/ ) { # value search
                                               # try (ship_){last,company}
 
   } elsif ( $search =~ /^\s*(\S.*\S)\s*$/ ) { # value search
                                               # try (ship_){last,company}


@@ -223,16 +254,14 @@ sub smart_search {
 
       #exact
       my $sql = scalar(keys %options) ? ' AND ' : ' WHERE ';
 
       #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 )";

 
       push @cust_main, qsearch( {
         'table'     => 'cust_main',
         'hashref'   => \%options,
         'extra_sql' => "$sql AND $agentnums_sql", #agent virtualization
       } );
 
       push @cust_main, qsearch( {
         'table'     => 'cust_main',
         'hashref'   => \%options,
         'extra_sql' => "$sql AND $agentnums_sql", #agent virtualization
       } );

+      #contacts?

 
       # or it just be something that was typed in... (try that in a sec)
 
 
       # or it just be something that was typed in... (try that in a sec)
 


@@ -244,11 +273,13 @@ sub smart_search {
     my $sql = scalar(keys %options) ? ' AND ' : ' WHERE ';
     $sql .= " (    LOWER(last)          = $q_value
                 OR LOWER(company)       = $q_value
     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 .= "   OR LOWER(address1)      = $q_value
-                OR LOWER(ship_address1) = $q_value
+    #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 .= " )";
             "
       if $conf->exists('address1-search');
     $sql .= " )";


@@ -270,32 +301,21 @@ sub smart_search {
 
       my @hashrefs = (
         { 'company'      => { op=>'ILIKE', value=>"%$value%" }, },
 
       my @hashrefs = (
         { 'company'      => { op=>'ILIKE', value=>"%$value%" }, },

-        { 'ship_company' => { op=>'ILIKE', value=>"%$value%" }, },

       );
 
       if ( $first && $last ) {
       );
 
       if ( $first && $last ) {

+        #contacts? ship_first/ship_last are gone

 
         push @hashrefs,
           { 'first'        => { op=>'ILIKE', value=>"%$first%" },
             'last'         => { op=>'ILIKE', value=>"%$last%" },
           },
 
         push @hashrefs,
           { 'first'        => { op=>'ILIKE', value=>"%$first%" },
             'last'         => { op=>'ILIKE', value=>"%$last%" },
           },

-          { 'ship_first'   => { op=>'ILIKE', value=>"%$first%" },
-            'ship_last'    => { op=>'ILIKE', value=>"%$last%" },
-          },

         ;
 
       } else {
 
         push @hashrefs,
           { 'last'         => { op=>'ILIKE', value=>"%$value%" }, },
         ;
 
       } else {
 
         push @hashrefs,
           { '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%" }, },

         ;
       }
 
         ;
       }
 


@@ -311,33 +331,67 @@ sub smart_search {
 
       }
 
 
       }
 

+      if ( $conf->exists('address1-search') ) {
+
+        push @cust_main, qsearch( {
+          'table'     => 'cust_main',
+          'addl_from' => 'JOIN cust_location USING (custnum)',
+          'extra_sql' => 'WHERE cust_location.address1 ILIKE '.
+                          dbh->quote("%$value%"),
+        } );
+
+      }
+

       #fuzzy
       #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 }, #
       );
 
       if ( $first && $last ) {
         push @cust_main, FS::cust_main::Search->fuzzy_search(
           { 'last'   => $last,    #fuzzy hashref
             'first'  => $first }, #

-          @fuzopts
+          %fuzopts

         );
       }
       foreach my $field ( 'last', 'company' ) {
         push @cust_main,
         );
       }
       foreach my $field ( 'last', 'company' ) {
         push @cust_main,

-          FS::cust_main::Search->fuzzy_search( { $field => $value }, @fuzopts );
+          FS::cust_main::Search->fuzzy_search( { $field => $value }, %fuzopts );

       }
       if ( $conf->exists('address1-search') ) {
         push @cust_main,
       }
       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 ( $nospace_search =~ /^[\dx]{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;


@@ -433,6 +487,10 @@ HASHREF.  Valid parameters are
 
 =item address
 
 
 =item address
 

+=item zip
+
+=item refnum
+

 =item cancelled_pkgs
 
 bool
 =item cancelled_pkgs
 
 bool


@@ -441,6 +499,18 @@ bool
 
 listref of start date, end date
 
 
 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
 =item payby
 
 listref


@@ -471,6 +541,34 @@ sub search {
   my @where = ();
   my $orderby;
 
   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
   ##
   ##
   # parse agent
   ##


@@ -505,11 +603,40 @@ sub search {
   ##
   if ( $params->{'address'} =~ /\S/ ) {
     my $address = dbh->quote('%'. lc($params->{'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)
-                          ).
-                 ')';
+    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)
+    )";
+  }
+
+  ##
+  # 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
+    )";
+  }
+
+  ###
+  # 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;
+

   }
 
   ##
   }
 
   ##


@@ -525,21 +652,52 @@ 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"
+  push @where, "ship_location.geocode is not null"

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

+  # "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,
+    "NOT EXISTS ( SELECT 1 FROM cust_main_invoice
+                    WHERE cust_main_invoice.custnum = cust_main.custnum
+                      AND dest = 'POST' )"
+    if $params->{'no_POST'};
+
+  ##

   # dates
   ##
 
   # dates
   ##
 

-  foreach my $field (qw( signupdate )) {
+  foreach my $field (qw( signupdate birthdate spouse_birthdate anniversary_date )) {

 
     next unless exists($params->{$field});
 
 
     next unless exists($params->{$field});
 


@@ -550,7 +708,7 @@ sub search {
       "cust_main.$field >= $beginning",
       "cust_main.$field <= $ending";
 
       "cust_main.$field >= $beginning",
       "cust_main.$field <= $ending";
 

-    if(defined $hour) {
+    if($field eq 'signupdate' && defined $hour) {

       if ($dbh->{Driver}->{Name} =~ /Pg/i) {
         push @where, "extract(hour from to_timestamp(cust_main.$field)) = $hour";
       }
       if ($dbh->{Driver}->{Name} =~ /Pg/i) {
         push @where, "extract(hour from to_timestamp(cust_main.$field)) = $hour";
       }


@@ -674,11 +832,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;

+      }

     }
   }
 
     }
   }
 


@@ -690,16 +856,26 @@ sub search {
   $orderby ||= 'ORDER BY custnum';
 
   # here is the agent virtualization
   $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 $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) ';
+  }

 
 

-  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',

+                 # 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'}),
                );
 
                  FS::UI::Web::cust_sql_fields($params->{'cust_fields'}),
                );
 


@@ -708,19 +884,35 @@ sub search {
 
   if ($params->{'flattened_pkgs'}) {
 
 
   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 ) ';

 
 

-      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";
+    if ($dbh->{Driver}->{Name} eq 'Pg') {

 
 

-    }elsif ($dbh->{Driver}->{Name} =~ /^mysql/i) {
-      push @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}. 
       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;
 
     my $sth = dbh->prepare($header_query) or die dbh->errstr;
     $sth->execute() or die $sth->errstr;


@@ -755,6 +947,7 @@ sub search {
   my $sql_query = {
     'table'         => 'cust_main',
     'select'        => $select,
   my $sql_query = {
     'table'         => 'cust_main',
     'select'        => $select,

+    'addl_from'     => $addl_from,

     'hashref'       => {},
     'extra_sql'     => $extra_sql,
     'order_by'      => $orderby,
     'hashref'       => {},
     'extra_sql'     => $extra_sql,
     'order_by'      => $orderby,


@@ -762,25 +955,40 @@ 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;

 
 }
 
 
 }
 

-=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
 
 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 {
 
 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 @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 ) {
 


@@ -788,15 +996,31 @@ 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 (' .
+                     join(',', map { dbh->quote($_) } keys %match) .
+                     ')';
+
+    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 @fcust = ();
-    foreach ( keys %match ) {
-      push @fcust, qsearch('cust_main', { %$hash, $field=>$_}, @opt);
-      push @fcust, qsearch('cust_main', { %$hash, "ship_$field"=>$_}, @opt);
+    my $addl_from = $fuzopts{addl_from};
+    if ( $field =~ /^cust_location/ ) {
+      $addl_from .= ' JOIN cust_location 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
   }
 
   # we want the components of $fuzzy ANDed, not ORed, but still don't want dupes


@@ -819,7 +1043,7 @@ sub fuzzy_search {
 
 sub check_and_rebuild_fuzzyfiles {
   my $dir = $FS::UID::conf_dir. "/cache.". $FS::UID::datasrc;
 
 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/cust_main.$_" } @fuzzyfields;

 }
 
 =item rebuild_fuzzyfiles
 }
 
 =item rebuild_fuzzyfiles


@@ -835,42 +1059,85 @@ sub rebuild_fuzzyfiles {
 
   foreach my $fuzzy ( @fuzzyfields ) {
 
 
   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;
   }
 
 }
 
     close LOCK;
   }
 
 }
 

+=item append_fuzzyfiles FIRSTNAME LASTNAME COMPANY ADDRESS1
+
+=cut
+
+sub append_fuzzyfiles {
+  #my( $first, $last, $company ) = @_;
+
+  check_and_rebuild_fuzzyfiles();
+
+  use Fcntl qw(:flock);
+
+  my $dir = $FS::UID::conf_dir. "/cache.". $FS::UID::datasrc;
+
+  foreach my $fuzzy (@fuzzyfields) {
+
+    my ($field, $table) = reverse split('\.', $fuzzy);
+    $table ||= 'cust_main';
+
+    my $value = shift;
+
+    if ( $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;
+    }
+
+  }
+
+  1;
+}
+

 =item all_X
 
 =cut
 
 sub all_X {
 =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;
   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;
   my @array = map { chomp; $_; } <CACHE>;
   close CACHE;
   \@array;