X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=FS%2FFS%2Fcust_main%2FSearch.pm;h=479ebf5c4acf829c949d1bc953a2086524623f0c;hp=f14f897ea42a9cdf12a1595fdcde1563783cb969;hb=6b5dda03831aef0cb5689cf2acf3fac47e4b12cb;hpb=60724f233fbeb9388f4b2cbb68a4e6297c2b2118 diff --git a/FS/FS/cust_main/Search.pm b/FS/FS/cust_main/Search.pm index f14f897ea..479ebf5c4 100644 --- a/FS/FS/cust_main/Search.pm +++ b/FS/FS/cust_main/Search.pm @@ -1,6 +1,7 @@ package FS::cust_main::Search; use strict; +use Carp qw( croak ); use base qw( Exporter ); use vars qw( @EXPORT_OK $DEBUG $me $conf @fuzzyfields ); use String::Approx qw(amatch); @@ -19,8 +20,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 +77,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,14 +91,22 @@ 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 and contact phone number push @cust_main, qsearch( { - 'table' => 'cust_main', - 'hashref' => { %options }, + 'select' => 'cust_main.*', + 'table' => 'cust_main', + 'addl_from' => ' left join cust_contact using (custnum) '. + ' left join contact_phone using (contactnum) ', + 'hashref' => { %options }, 'extra_sql' => ( scalar(keys %options) ? ' AND ' : ' WHERE ' ). ' ( '. join(' OR ', map "$_ = '$phonen'", qw( daytime night mobile fax ) ). + " OR phonenum = '$phonenum' ". ' ) '. " AND $agentnums_sql", #agent virtualization } ); @@ -101,8 +115,8 @@ sub smart_search { #try looking for matches with extensions unless one was specified push @cust_main, qsearch( { - 'table' => 'cust_main', - 'hashref' => { %options }, + 'table' => 'cust_main', + 'hashref' => { %options }, 'extra_sql' => ( scalar(keys %options) ? ' AND ' : ' WHERE ' ). ' ( '. join(' OR ', map "$_ LIKE '$phonen\%'", @@ -117,31 +131,43 @@ sub smart_search { } - if ( $search =~ /@/ ) { #invoicing email address - push @cust_main, - map $_->cust_main, - qsearch( { - 'table' => 'cust_main_invoice', - 'hashref' => { 'dest' => $search }, - } - ); + if ( $search =~ /@/ ) { #email address from cust_main_invoice and contact_email + + push @cust_main, qsearch( { + 'select' => 'cust_main.*', + 'table' => 'cust_main', + 'addl_from' => ' left join cust_main_invoice using (custnum) '. + ' left join cust_contact using (custnum) '. + ' left join contact_email using (contactnum) ', + 'hashref' => { %options }, + 'extra_sql' => ( scalar(keys %options) ? ' AND ' : ' WHERE ' ). + ' ( '. + join(' OR ', map "$_ = '$search'", + qw( dest emailaddress ) + ). + ' ) '. + " AND $agentnums_sql", #agent virtualization + } ); # 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 - ) - ) + } elsif ( $search =~ /^\s*(\d+)\s*$/ + or ( $conf->config('cust_main-agent_custid-format') eq 'ww?d+' + && $search =~ /^\s*(\w\w?\d+)\s*$/ + ) + or ( $conf->config('cust_main-agent_custid-format') eq 'd+-w' + && $search =~ /^\s*(\d+-\w)\s*$/ + ) + or ( $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*$/ + ) + or ( $conf->exists('address1-search' ) + && $search =~ /^\s*(\d+\-?\w*)\s*$/ #i.e. 1234A or 9432-D + ) + ) { my $num = $1; @@ -159,7 +185,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)) ) { @@ -185,6 +211,7 @@ sub smart_search { # probably the Right Thing: return customers that have any associated # locations matching the string, not just bill/ship location push @cust_main, qsearch( { + 'select' => 'cust_main.*', 'table' => 'cust_main', 'addl_from' => ' JOIN cust_location USING (custnum) ', 'hashref' => { %options, }, @@ -205,9 +232,9 @@ sub smart_search { #doesn't throw a wrench in the works) 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(first) = ". dbh->quote(lc($first)), @@ -215,11 +242,13 @@ sub smart_search { " 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); @@ -243,8 +272,8 @@ sub smart_search { } elsif ( ! $NameParse->parse($value) ) { my %name = $NameParse->components; - $first = $name{'given_name_1'} || $name{'initials_1'}; #wtf NameParse, Ed? - $last = $name{'surname_1'}; + $first = lc($name{'given_name_1'}) || $name{'initials_1'}; #wtf NameParse, Ed? + $last = lc($name{'surname_1'}); } @@ -254,14 +283,18 @@ sub smart_search { #exact my $sql = scalar(keys %options) ? ' AND ' : ' WHERE '; - $sql .= "( LOWER(cust_main.last) = $q_last AND LOWER(cust_main.first) = $q_first )"; + $sql .= "( (LOWER(cust_main.last) = $q_last AND LOWER(cust_main.first) = $q_first) + OR (LOWER(contact.last) = $q_last AND LOWER(contact.first) = $q_first) )"; + #cust_main and contacts push @cust_main, qsearch( { + 'select' => 'cust_main.*', 'table' => 'cust_main', - 'hashref' => \%options, + 'addl_from' => ' left join cust_contact using (custnum) '. + ' left join contact using (contactnum) ', + '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) @@ -271,22 +304,28 @@ sub smart_search { #exact my $sql = scalar(keys %options) ? ' AND ' : ' WHERE '; - $sql .= " ( LOWER(last) = $q_value - OR LOWER(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 - ) - " + $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 + OR LOWER(contact.first) = $q_value + OR LOWER(contact.last) = $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 .= " )"; push @cust_main, qsearch( { + 'select' => 'cust_main.*', 'table' => 'cust_main', - 'hashref' => \%options, + 'addl_from' => ' left join cust_contact using (custnum) '. + ' left join contact using (contactnum) ', + 'hashref' => { %options }, 'extra_sql' => "$sql AND $agentnums_sql", #agent virtualization } ); @@ -297,29 +336,39 @@ sub smart_search { #still some false laziness w/search (was search/cust_main.cgi) + my $min_len = + $FS::CurrentUser::CurrentUser->access_right('List all customers') + ? 3 : 4; + #substring - my @hashrefs = ( - { 'company' => { op=>'ILIKE', value=>"%$value%" }, }, - ); + my @company_hashrefs = (); + if ( length($value) >= $min_len ) { + @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 { + } elsif ( length($value) >= $min_len ) { - 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', @@ -331,17 +380,37 @@ sub smart_search { } - if ( $conf->exists('address1-search') ) { + if ( $conf->exists('address1-search') && length($value) >= $min_len ) { push @cust_main, qsearch( { - 'table' => 'cust_main', - 'addl_from' => 'JOIN cust_location USING (custnum)', - 'extra_sql' => 'WHERE cust_location.address1 ILIKE '. - dbh->quote("%$value%"), + select => 'cust_main.*', + 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 +424,31 @@ 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 ( 'last', 'company' ) { - push @cust_main, - FS::cust_main::Search->fuzzy_search( { $field => $value }, %fuzopts ); + + foreach my $field ( 'first', 'last', 'company', 'ship_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 + ); } } @@ -380,12 +465,14 @@ sub smart_search { my $mask_search = FS::payinfo_Mixin->mask_payinfo('CARD', $card_search); push @cust_main, qsearch({ + 'select' => 'cust_main.*', 'table' => 'cust_main', + 'addl_from' => ' JOIN cust_payby USING (custnum)', 'hashref' => {}, - 'extra_sql' => " WHERE ( payinfo LIKE '$like_search' - OR paymask = '$mask_search' + 'extra_sql' => " WHERE ( cust_payby.payinfo LIKE '$like_search' + OR cust_payby.paymask = '$mask_search' ) ". - " AND payby IN ('CARD','DCRD') ". + " AND cust_payby.payby IN ('CARD','DCRD') ". " AND $agentnums_sql", #agent virtulization }); @@ -417,11 +504,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 = (); @@ -434,10 +519,12 @@ sub email_search { if $DEBUG; push @cust_main, - map $_->cust_main, + map { $_->cust_main } + map { $_->cust_contact } + map { $_->contact } qsearch( { - 'table' => 'cust_main_invoice', - 'hashref' => { 'dest' => $email }, + 'table' => 'contact_email', + 'hashref' => { 'emailaddress' => $email }, } ); @@ -511,14 +598,6 @@ listref of start date, end date listref of start date, end date -=item payby - -listref - -=item paydate_year - -=item paydate_month - =item current_balance listref (list returned by FS::UI::Web::parse_lt_gt($cgi, 'current_balance')) @@ -549,7 +628,6 @@ sub search { 'status' => '', 'address' => '', 'zip' => '', - 'paydate_year' => '', 'invoice_terms' => '', 'custbatch' => '', %$params @@ -607,16 +685,81 @@ 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'}). '%'); + 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 + 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) + AND cust_location.state = $state + $current )"; } @@ -629,9 +772,38 @@ sub search { 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 + )"; + } + + ## + # phones + ## + + foreach my $phonet (qw(daytime night mobile)) { + if ($params->{$phonet}) { + $params->{$phonet} =~ s/\D//g; + $params->{$phonet} =~ /^(\d{3})(\d{3})(\d{4})(\d*)$/ + or next; + my $phonen = "$1-$2-$3"; + if ($4) { push @where, "cust_main.".$phonet." = '".$phonen." x$4'"; } + else { push @where, "cust_main.".$phonet." like '".$phonen."%'"; } + } + } + ### # refnum ### @@ -658,51 +830,100 @@ sub search { unless $params->{'cancelled_pkgs'}; ## - # parse without census tract checkbox + # "with email address(es)" checkbox, + # also optionally: with_email_dest and with_contact_type ## - push @where, "(ship_location.censustract = '' or ship_location.censustract is null)" - if $params->{'no_censustract'}; + if ($params->{with_email}) { + my @email_dest; + my $email_dest_sql; + my $contact_type_sql; + + if ($params->{with_email_dest}) { + croak unless ref $params->{with_email_dest} eq 'ARRAY'; + + @email_dest = @{$params->{with_email_dest}}; + $email_dest_sql = + " AND ( ". + join(' OR ',map(" cust_contact.${_}_dest IS NOT NULL ", @email_dest)). + " ) "; + # Can't use message_dist = 'Y' because single quotes are escaped later + } + if ($params->{with_contact_type}) { + croak unless ref $params->{with_contact_type} eq 'ARRAY'; + + my @contact_type = grep {/^\d+$/ && $_ > 0} @{$params->{with_contact_type}}; + my $has_null_type = 0; + $has_null_type = 1 if grep { $_ eq 0 } @{$params->{with_contact_type}}; + my $hnt_sql; + if ($has_null_type) { + $hnt_sql = ' OR ' if @contact_type; + $hnt_sql .= ' cust_contact.classnum IS NULL '; + } + + $contact_type_sql = + " AND ( ". + join(' OR ', map(" cust_contact.classnum = $_ ", @contact_type)). + $hnt_sql. + " ) "; + } + push @where, + "EXISTS ( SELECT 1 FROM contact_email + JOIN cust_contact USING (contactnum) + WHERE cust_contact.custnum = cust_main.custnum + $email_dest_sql + $contact_type_sql + ) "; + } ## - # parse with hardcoded tax location checkbox + # "with postal mail invoices" 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'}; + push @where, "cust_main.postal_invoice = 'Y'" + if $params->{'POST'}; ## - # "with email address(es)" checkbox + # "without postal mail invoices" 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'}; + push @where, "cust_main.postal_invoice IS NULL" + if $params->{'no_POST'}; ## - # "with postal mail invoices" checkbox + # "tax exempt" checkbox ## + push @where, "cust_main.tax = 'Y'" + if $params->{'tax'}; - push @where, - "EXISTS ( SELECT 1 FROM cust_main_invoice - WHERE cust_main_invoice.custnum = cust_main.custnum - AND dest = 'POST' )" - if $params->{'POST'}; + ## + # "not tax exempt" checkbox + ## + push @where, "(cust_main.tax = '' OR cust_main.tax IS NULL )" + if $params->{'no_tax'}; ## - # "without postal mail invoices" checkbox + # with referrals ## + if ( $params->{with_referrals} =~ /^\s*(\d+)\s*$/ ) { - 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'}; + my $n = $1; + + # referral status + my $and_status = ''; + if ( grep { $params->{referral_status} eq $_ } FS::cust_main->statuses() ) { + my $method = $params->{referral_status}. '_sql'; + $and_status = ' AND '. FS::cust_main->$method(); + $and_status =~ s/ cust_main\./ referred_cust_main./g; + } + + push @where, + " $n <= ( SELECT COUNT(*) FROM cust_main AS referred_cust_main + WHERE cust_main.custnum = referred_cust_main.referral_custnum + $and_status + )"; + + } ## # dates @@ -760,40 +981,6 @@ sub search { } ### - # payby - ### - - if ( $params->{'payby'} ) { - - my @payby = ref( $params->{'payby'} ) - ? @{ $params->{'payby'} } - : ( $params->{'payby'} ); - - @payby = grep /^([A-Z]{4})$/, @payby; - - push @where, '( '. join(' OR ', map "cust_main.payby = '$_'", @payby). ' )' - if @payby; - - } - - ### - # paydate_year / paydate_month - ### - - if ( $params->{'paydate_year'} =~ /^(\d{4})$/ ) { - my $year = $1; - $params->{'paydate_month'} =~ /^(\d\d?)$/ - or die "paydate_year without paydate_month?"; - my $month = $1; - - push @where, - 'paydate IS NOT NULL', - "paydate != ''", - "CAST(paydate AS timestamp) < CAST('$year-$month-01' AS timestamp )" -; - } - - ### # invoice terms ### @@ -844,8 +1031,9 @@ sub search { if ( @tagnums ) { if ( $params->{'all_tags'} ) { + my $exists = $params->{'all_tags'} eq 'all' ? 'exists' : 'not exists'; foreach ( @tagnums ) { - push @where, 'exists(select 1 from cust_tag where '. + push @where, $exists.'(select 1 from cust_tag where '. 'cust_tag.custnum = cust_main.custnum and tagnum = '. $_ . ')'; } @@ -859,12 +1047,89 @@ 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)"; + } + } + } + + ## + # contacts + ## + if (keys %{ $params->{'contacts'} }) { + my $contact_params = $params->{'contacts'}; + + if ($contact_params->{'contacts_firstname'} || $contact_params->{'contacts_lastname'}) { + my $first_query = " AND contact.first = '" . $contact_params->{'contacts_firstname'} . "'" + unless !$contact_params->{'contacts_firstname'}; + my $last_query = " AND contact.last = '" . $contact_params->{'contacts_lastname'} . "'" + unless !$contact_params->{'contacts_lastname'}; + push @where, + "EXISTS ( SELECT 1 FROM contact + JOIN cust_contact USING (contactnum) + WHERE cust_contact.custnum = cust_main.custnum + $first_query $last_query + ) "; + } + + if ($contact_params->{'contacts_email'}) { + push @where, + "EXISTS ( SELECT 1 FROM contact_email + JOIN cust_contact USING (contactnum) + WHERE cust_contact.custnum = cust_main.custnum + AND contact_email.emailaddress = '" . $contact_params->{'contacts_email'} . "' + ) "; + } + + if ( grep { /^contacts_phonetypenum(\d+)$/ } keys %{ $contact_params } ) { + my $phone_query; + foreach my $phone ( grep { /^contacts_phonetypenum(\d+)$/ } keys %{ $contact_params } ) { + $phone =~ /^contacts_phonetypenum(\d+)$/ or die "No phone type num $1 from $phone"; + my $phonetypenum = $1; + (my $num = $contact_params->{$phone}) =~ s/\W//g; + if ( $num =~ /^1?(\d{3})(\d{3})(\d{4})(\d*)$/ ) { $contact_params->{$phone} = "$1$2$3"; } + $phone_query .= " AND ( contact_phone.phonetypenum = '".$phonetypenum."' AND contact_phone.phonenum = '" . $contact_params->{$phone} . "' )" + unless !$contact_params->{$phone}; + } + push @where, + "EXISTS ( SELECT 1 FROM contact_phone + JOIN cust_contact USING (contactnum) + WHERE cust_contact.custnum = cust_main.custnum + $phone_query + ) "; + } + } + ## # setup queries, subs, etc. for the search ## - $orderby ||= 'ORDER BY custnum'; + $orderby ||= 'ORDER BY cust_main.custnum'; # here is the agent virtualization push @where, @@ -880,18 +1145,24 @@ sub search { 'ON (cust_main.'.$pre.'locationnum = '.$pre.'location.locationnum) '; } - my $count_query = "SELECT COUNT(*) FROM cust_main $addl_from $extra_sql"; + # 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 @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'}), ); - my(@extra_headers) = (); - my(@extra_fields) = (); + my @extra_headers = (); + my @extra_fields = (); + my @extra_sort_fields = (); + + my $count_query = "SELECT COUNT(DISTINCT cust_main.custnum) FROM cust_main $addl_from $extra_sql"; if ($params->{'flattened_pkgs'}) { @@ -936,37 +1207,40 @@ sub search { my $p = $a[!.--$headercount. q!]; $p; };!; + unshift @extra_sort_fields, ''; } } - 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; + if ( $params->{'with_referrals'} ) { + + #XXX next: num for each customer status + + push @select, + '( SELECT COUNT(*) FROM cust_main AS referred_cust_main + WHERE cust_main.custnum = referred_cust_main.referral_custnum + ) AS num_referrals'; + + unshift @extra_headers, 'Referrals'; + unshift @extra_fields, 'num_referrals'; + unshift @extra_sort_fields, 'num_referrals'; + } my $select = join(', ', @select); my $sql_query = { - 'table' => 'cust_main', - 'select' => $select, - 'addl_from' => $addl_from, - 'hashref' => {}, - 'extra_sql' => $extra_sql, - 'order_by' => $orderby, - 'count_query' => $count_query, - 'extra_headers' => \@extra_headers, - 'extra_fields' => \@extra_fields, + 'table' => 'cust_main', + 'select' => $select, + 'addl_from' => $addl_from, + 'hashref' => {}, + 'extra_sql' => $extra_sql, + 'order_by' => $orderby, + 'count_query' => $count_query, + 'extra_headers' => \@extra_headers, + 'extra_fields' => \@extra_fields, + 'extra_sort_fields' => \@extra_sort_fields, }; - warn Data::Dumper::Dumper($sql_query); $sql_query; } @@ -1023,8 +1297,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({ @@ -1054,7 +1330,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 @@ -1107,34 +1390,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 @@ -1165,4 +1462,3 @@ L, L =cut 1; -