From caad814a67620dad4aa97f0c5be8adb324956cc1 Mon Sep 17 00:00:00 2001 From: Ivan Kohler Date: Sat, 2 Nov 2013 13:47:50 -0700 Subject: [PATCH] contact search, RT#25687 (also possibly #25583 and #22991) --- FS/FS/contact.pm | 65 +++++++++++++++- FS/FS/contact_email.pm | 55 +++++++------- FS/FS/contact_phone.pm | 36 ++++----- FS/FS/cust_main.pm | 16 +++- FS/FS/cust_main/Search.pm | 184 +++++++++++++++++++++++++++++++++++----------- 5 files changed, 262 insertions(+), 94 deletions(-) diff --git a/FS/FS/contact.pm b/FS/FS/contact.pm index 8fcd724a0..da6f2eb99 100644 --- a/FS/FS/contact.pm +++ b/FS/FS/contact.pm @@ -1,7 +1,7 @@ package FS::contact; +use base qw( FS::Record ); use strict; -use base qw( FS::Record ); use FS::Record qw( qsearch qsearchs dbh ); use FS::prospect_main; use FS::cust_main; @@ -153,6 +153,16 @@ sub insert { } + #unless ( $import || $skip_fuzzyfiles ) { + #warn " queueing fuzzyfiles update\n" + # if $DEBUG > 1; + $error = $self->queue_fuzzyfiles_update; + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return "updating fuzzy search cache: $error"; + } + #} + $dbh->commit or die $dbh->errstr if $oldAutoCommit; ''; @@ -277,6 +287,16 @@ sub replace { } + #unless ( $import || $skip_fuzzyfiles ) { + #warn " queueing fuzzyfiles update\n" + # if $DEBUG > 1; + $error = $self->queue_fuzzyfiles_update; + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return "updating fuzzy search cache: $error"; + } + #} + $dbh->commit or die $dbh->errstr if $oldAutoCommit; ''; @@ -306,6 +326,44 @@ sub _parse_phonestring { ); } +=item queue_fuzzyfiles_update + +Used by insert & replace to update the fuzzy search cache + +=cut + +use FS::cust_main::Search; +sub queue_fuzzyfiles_update { + my $self = shift; + + local $SIG{HUP} = 'IGNORE'; + local $SIG{INT} = 'IGNORE'; + local $SIG{QUIT} = 'IGNORE'; + local $SIG{TERM} = 'IGNORE'; + local $SIG{TSTP} = 'IGNORE'; + local $SIG{PIPE} = 'IGNORE'; + + my $oldAutoCommit = $FS::UID::AutoCommit; + local $FS::UID::AutoCommit = 0; + my $dbh = dbh; + + foreach my $field ( 'first', 'last' ) { + my $queue = new FS::queue { + 'job' => 'FS::cust_main::Search::append_fuzzyfiles_fuzzyfield' + }; + my @args = "contact.$field", $self->get($field); + my $error = $queue->insert( @args ); + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return "queueing job (transaction rolled back): $error"; + } + } + + $dbh->commit or die $dbh->errstr if $oldAutoCommit; + ''; + +} + =item check Checks all fields to make sure this is a valid example. If there is @@ -381,6 +439,11 @@ sub contact_email { qsearch('contact_email', { 'contactnum' => $self->contactnum } ); } +sub cust_main { + my $self = shift; + qsearchs('cust_main', { 'custnum' => $self->custnum } ); +} + =back =head1 BUGS diff --git a/FS/FS/contact_email.pm b/FS/FS/contact_email.pm index 1276d8d68..4f787358b 100644 --- a/FS/FS/contact_email.pm +++ b/FS/FS/contact_email.pm @@ -1,8 +1,9 @@ package FS::contact_email; +use base qw( FS::Record ); use strict; -use base qw( FS::Record ); use FS::Record qw( qsearch qsearchs ); +use FS::contact; =head1 NAME @@ -25,8 +26,9 @@ FS::contact_email - Object methods for contact_email records =head1 DESCRIPTION -An FS::contact_email object represents an example. FS::contact_email inherits from -FS::Record. The following fields are currently supported: +An FS::contact_email object represents a contact's email address. +FS::contact_email inherits from FS::Record. The following fields are currently +supported: =over 4 @@ -51,15 +53,14 @@ emailaddress =item new HASHREF -Creates a new example. To add the example to the database, see L<"insert">. +Creates a new contact email address. To add the email address to the database, +see L<"insert">. Note that this stores the hash reference, not a distinct copy of the hash it points to. You can ask the object for a copy with the I method. =cut -# the new method can be inherited from FS::Record, if a table method is defined - sub table { 'contact_email'; } =item insert @@ -67,60 +68,62 @@ sub table { 'contact_email'; } Adds this record to the database. If there is an error, returns the error, otherwise returns false. -=cut - -# the insert method can be inherited from FS::Record - =item delete Delete this record from the database. -=cut - -# the delete method can be inherited from FS::Record - =item replace OLD_RECORD Replaces the OLD_RECORD with this one in the database. If there is an error, returns the error, otherwise returns false. -=cut - -# the replace method can be inherited from FS::Record - =item check -Checks all fields to make sure this is a valid example. If there is +Checks all fields to make sure this is a valid email address. If there is an error, returns the error, otherwise returns false. Called by the insert and replace methods. =cut -# the check method should currently be supplied - FS::Record contains some -# data checking routines - sub check { my $self = shift; my $error = $self->ut_numbern('contactemailnum') || $self->ut_number('contactnum') - || $self->ut_text('emailaddress') ; return $error if $error; + #technically \w and also ! # $ % & ' * + - / = ? ^ _ ` { | } ~ + # and even more technically need to deal with i18n addreesses soon + # (maybe the UI can convert them for us ala punycode.js) + # but for now in practice have not encountered anything outside \w . - & + ' + # and even & and ' are super rare and probably have scarier "pass to shell" + # implications than worth being pedantic about accepting + # (we always String::ShellQuote quote them, but once passed...) + # SO: \w . - + + if ( $self->emailaddress =~ /^\s*([\w\.\-\+]+)\@(([\w\.\-]+\.)+\w+)\s*$/ ) { + my($user, $domain) = ($1, $2); + $self->emailaddress("$1\@$2"); + } else { + return gettext("illegal_email_invoice_address"). ': '. $self->emailaddress; + } + $self->SUPER::check; } +sub contact { + my $self = shift; + qsearchs( 'contact', { 'contactnum' => $self->contactnum } ); +} + =back =head1 BUGS -The author forgot to customize this manpage. - =head1 SEE ALSO -L, schema.html from the base documentation. +L, L =cut diff --git a/FS/FS/contact_phone.pm b/FS/FS/contact_phone.pm index 7ba85234a..0eb216668 100644 --- a/FS/FS/contact_phone.pm +++ b/FS/FS/contact_phone.pm @@ -1,8 +1,9 @@ package FS::contact_phone; +use base qw( FS::Record ); use strict; -use base qw( FS::Record ); use FS::Record qw( qsearch qsearchs ); +use FS::contact; =head1 NAME @@ -25,8 +26,8 @@ FS::contact_phone - Object methods for contact_phone records =head1 DESCRIPTION -An FS::contact_phone object represents an example. FS::contact_phone inherits from -FS::Record. The following fields are currently supported: +An FS::contact_phone object represents a contatct's phone number. +FS::contact_phone inherits from FS::Record. The following fields are currently supported: =over 4 @@ -63,15 +64,14 @@ extension =item new HASHREF -Creates a new example. To add the example to the database, see L<"insert">. +Creates a new phone number. To add the phone number to the database, see +L<"insert">. Note that this stores the hash reference, not a distinct copy of the hash it points to. You can ask the object for a copy with the I method. =cut -# the new method can be inherited from FS::Record, if a table method is defined - sub table { 'contact_phone'; } =item insert @@ -79,38 +79,23 @@ sub table { 'contact_phone'; } Adds this record to the database. If there is an error, returns the error, otherwise returns false. -=cut - -# the insert method can be inherited from FS::Record - =item delete Delete this record from the database. -=cut - -# the delete method can be inherited from FS::Record - =item replace OLD_RECORD Replaces the OLD_RECORD with this one in the database. If there is an error, returns the error, otherwise returns false. -=cut - -# the replace method can be inherited from FS::Record - =item check -Checks all fields to make sure this is a valid example. If there is +Checks all fields to make sure this is a valid phone number. If there is an error, returns the error, otherwise returns false. Called by the insert and replace methods. =cut -# the check method should currently be supplied - FS::Record contains some -# data checking routines - sub check { my $self = shift; @@ -154,13 +139,18 @@ sub phonenum_pretty { } +sub contact { + my $self = shift; + qsearchs( 'contact', { 'contactnum' => $self->contactnum } ); +} + =back =head1 BUGS =head1 SEE ALSO -L, schema.html from the base documentation. +L, L =cut diff --git a/FS/FS/cust_main.pm b/FS/FS/cust_main.pm index 3e36c6049..d768f8406 100644 --- a/FS/FS/cust_main.pm +++ b/FS/FS/cust_main.pm @@ -1662,13 +1662,25 @@ sub queue_fuzzyfiles_update { local $FS::UID::AutoCommit = 0; my $dbh = dbh; + foreach my $field ( 'first', 'last', 'company' ) { + my $queue = new FS::queue { + 'job' => 'FS::cust_main::Search::append_fuzzyfiles_fuzzyfield' + }; + my @args = "cust_main.$field", $self->get($field); + my $error = $queue->insert( @args ); + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return "queueing job (transaction rolled back): $error"; + } + } + my @locations = $self->bill_location; push @locations, $self->ship_location if $self->has_ship_address; foreach my $location (@locations) { my $queue = new FS::queue { - 'job' => 'FS::cust_main::Search::append_fuzzyfiles' + 'job' => 'FS::cust_main::Search::append_fuzzyfiles_fuzzyfield' }; - my @args = map $location->get($_), @FS::cust_main::Search::fuzzyfields; + my @args = 'cust_location.address1', $location->address1; my $error = $queue->insert( @args ); if ( $error ) { $dbh->rollback if $oldAutoCommit; diff --git a/FS/FS/cust_main/Search.pm b/FS/FS/cust_main/Search.pm index 70d12c97d..362a6aa1c 100644 --- a/FS/FS/cust_main/Search.pm +++ b/FS/FS/cust_main/Search.pm @@ -19,8 +19,11 @@ 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_location.address1', + 'contact.first', 'contact.last', +); install_callback FS::UID sub { $conf = new FS::Conf; @@ -72,6 +75,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 +89,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 +105,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 +135,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 +147,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 +191,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 +248,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 +290,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 +318,28 @@ 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 "; - #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', @@ -304,7 +361,6 @@ sub smart_search { ); if ( $first && $last ) { - #contacts? ship_first/ship_last are gone push @hashrefs, { 'first' => { op=>'ILIKE', value=>"%$first%" }, @@ -315,6 +371,7 @@ sub smart_search { } else { push @hashrefs, + { 'first' => { op=>'ILIKE', value=>"%$value%" }, }, { 'last' => { op=>'ILIKE', value=>"%$value%" }, }, ; } @@ -334,14 +391,35 @@ 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 + + shift @hashrefs; #no company column in contact table + + 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, @@ -356,7 +434,7 @@ sub smart_search { %fuzopts ); } - foreach my $field ( 'last', 'company' ) { + foreach my $field ( 'first', 'last', 'company' ) { push @cust_main, FS::cust_main::Search->fuzzy_search( { $field => $value }, %fuzopts ); } @@ -1004,8 +1082,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({ @@ -1035,7 +1115,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 @@ -1088,34 +1175,47 @@ 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', + ) { - 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 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 -- 2.11.0