X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=FS%2FFS%2Fcust_main%2FSearch.pm;h=32806401cdea6b4fb4050cb6a9ba4de816c023ab;hp=182527ff9bd2fd10a0386530b32428274c9b9bfe;hb=af167dc766fad944b32ca7dc431b2549943b94dd;hpb=913b38d717fc7f2fae32722e9f30462fc9344fb3 diff --git a/FS/FS/cust_main/Search.pm b/FS/FS/cust_main/Search.pm index 182527ff9..32806401c 100644 --- a/FS/FS/cust_main/Search.pm +++ b/FS/FS/cust_main/Search.pm @@ -19,8 +19,12 @@ use FS::payinfo_Mixin; $DEBUG = 0; $me = '[FS::cust_main::Search]'; -@fuzzyfields = ( 'cust_main.first', 'cust_main.last', 'cust_main.company', - 'cust_location.address1' ); +@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; @@ -72,6 +76,7 @@ sub smart_search { #here is the agent virtualization my $agentnums_sql = $FS::CurrentUser::CurrentUser->agentnums_sql(table => 'cust_main'); + my $agentnums_href = $FS::CurrentUser::CurrentUser->agentnums_href; my @cust_main = (); @@ -85,6 +90,10 @@ 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 }, @@ -97,6 +106,16 @@ sub smart_search { " 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 @@ -117,8 +136,11 @@ sub smart_search { } - if ( $search =~ /@/ ) { #invoicing email address + if ( $search =~ /@/ ) { #email address + + # invoicing email address push @cust_main, + grep $agentnums_href->{$_->agentnum}, #agent virt map $_->cust_main, qsearch( { 'table' => 'cust_main_invoice', @@ -126,6 +148,17 @@ sub smart_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 }, + } + ); + # custnum search (also try agent_custid), with some tweaking options if your # legacy cust "numbers" have letters } elsif ( $search =~ /^\s*(\d+)\s*$/ @@ -159,7 +192,7 @@ sub smart_search { # 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 ) { + 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)) ) { @@ -216,10 +249,12 @@ sub smart_search { $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); @@ -256,12 +291,25 @@ sub smart_search { my $sql = scalar(keys %options) ? ' AND ' : ' WHERE '; $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? + + #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) @@ -271,18 +319,29 @@ sub smart_search { #exact my $sql = scalar(keys %options) ? ' AND ' : ' WHERE '; - $sql .= " ( LOWER(last) = $q_value - OR LOWER(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 "; - #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 - ) - " + + #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', @@ -299,27 +358,30 @@ sub smart_search { #substring - my @hashrefs = ( + my @company_hashrefs = ( { 'company' => { op=>'ILIKE', value=>"%$value%" }, }, + { 'ship_company' => { op=>'ILIKE', value=>"%$value%" }, }, ); + my @hashrefs = (); + if ( $first && $last ) { - #contacts? ship_first/ship_last are gone - push @hashrefs, + @hashrefs = ( { 'first' => { op=>'ILIKE', value=>"%$first%" }, 'last' => { op=>'ILIKE', value=>"%$last%" }, }, - ; + ); } else { - push @hashrefs, + @hashrefs = ( + { 'first' => { op=>'ILIKE', value=>"%$value%" }, }, { 'last' => { op=>'ILIKE', value=>"%$value%" }, }, - ; + ); } - foreach my $hashref ( @hashrefs ) { + foreach my $hashref ( @company_hashrefs, @hashrefs ) { push @cust_main, qsearch( { 'table' => 'cust_main', @@ -334,14 +396,33 @@ 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%"), + 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 = ( 'hashref' => \%options, @@ -355,15 +436,30 @@ sub smart_search { 'first' => $first }, # %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( - { 'cust_location.address1' => $value }, %fuzopts ); + { 'cust_location.address1' => $value }, + %fuzopts + ); } } @@ -668,22 +764,6 @@ sub search { unless $params->{'cancelled_pkgs'}; ## - # parse without census tract checkbox - ## - - push @where, "(ship_location.censustract = '' or ship_location.censustract is null)" - if $params->{'no_censustract'}; - - ## - # parse with hardcoded tax location checkbox - ## - - 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'}; - - ## # "with email address(es)" checkbox ## @@ -869,6 +949,35 @@ sub search { } } + # 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 @@ -950,19 +1059,6 @@ 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 - }; - push @select, 'geocode'; - push @select, 'zip' unless grep { $_ eq 'zip' } @select; - push @select, 'ship_zip' unless grep { $_ eq 'ship_zip' } @select; - } - my $select = join(', ', @select); my $sql_query = { @@ -976,7 +1072,7 @@ sub search { 'extra_headers' => \@extra_headers, 'extra_fields' => \@extra_fields, }; - warn Data::Dumper::Dumper($sql_query); + #warn Data::Dumper::Dumper($sql_query); $sql_query; } @@ -1033,8 +1129,10 @@ sub fuzzy_search { $extra_sql .= "$field $in_matches"; my $addl_from = $fuzopts{addl_from}; - if ( $field =~ /^cust_location/ ) { + if ( $field =~ /^cust_location\./ ) { $addl_from .= ' JOIN cust_location USING (custnum)'; + } elsif ( $field =~ /^contact\./ ) { + $addl_from .= ' JOIN contact USING (custnum)'; } push @cust_main, qsearch({ @@ -1064,7 +1162,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 @@ -1117,34 +1222,48 @@ sub append_fuzzyfiles { check_and_rebuild_fuzzyfiles(); - use Fcntl qw(:flock); + #foreach my $fuzzy (@fuzzyfields) { + foreach my $fuzzy ( 'cust_main.first', 'cust_main.last', 'cust_main.company', + 'cust_location.address1', + 'cust_main.ship_company', + ) { - my $dir = $FS::UID::conf_dir. "/cache.". $FS::UID::datasrc; + append_fuzzyfiles_fuzzyfield($fuzzy, shift); - foreach my $fuzzy (@fuzzyfields) { + } - my ($field, $table) = reverse split('\.', $fuzzy); - $table ||= 'cust_main'; + 1; +} - my $value = shift; +=item append_fuzzyfiles_fuzzyfield COLUMN VALUE - if ( $value ) { +=item append_fuzzyfiles_fuzzyfield TABLE.COLUMN 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: $!"; +=cut - print CACHE "$value\n"; +use Fcntl qw(:flock); +sub append_fuzzyfiles_fuzzyfield { + my( $fuzzyfield, $value ) = @_; - flock(CACHE,LOCK_UN) - or die "can't unlock $dir/$table.$field: $!"; - close CACHE; - } + my $dir = $FS::UID::conf_dir. "/cache.". $FS::UID::datasrc; - } - 1; + my ($field, $table) = reverse split('\.', $fuzzyfield); + $table ||= 'cust_main'; + + return unless defined($value) && 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