X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2Fcust_main%2FSearch.pm;h=f0a7d4137102912ef1ace32cfef38cb5d1c63276;hb=ceeaad3d99869f159bf2fee490aafa4c95d03365;hp=046e959939e81fed9656bfeefd0e989983b977c7;hpb=7f9a709406959210d4177c6e76a5e074319abf18;p=freeside.git diff --git a/FS/FS/cust_main/Search.pm b/FS/FS/cust_main/Search.pm index 046e95993..f0a7d4137 100644 --- a/FS/FS/cust_main/Search.pm +++ b/FS/FS/cust_main/Search.pm @@ -477,11 +477,12 @@ sub smart_search { push @cust_main, qsearch({ '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 }); @@ -727,6 +728,42 @@ sub search { } ## + # 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 + )"; + } + + ## + # 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 + )"; + } + + ## + # state + ## + if ( $params->{'state'} =~ /\S/ ) { + my $state = dbh->quote($params->{'state'}); + push @where, "EXISTS( + SELECT 1 FROM cust_location + WHERE cust_location.custnum = cust_main.custnum + AND cust_location.state = $state + )"; + } + + ## # zipcode ## if ( $params->{'zip'} =~ /\S/ ) { @@ -738,6 +775,18 @@ sub search { )"; } + ## + # 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' + )"; + } + ### # refnum ### @@ -795,6 +844,18 @@ sub search { if $params->{'no_POST'}; ## + # "tax exempt" checkbox + ## + push @where, "cust_main.tax = 'Y'" + if $params->{'tax'}; + + ## + # "not tax exempt" checkbox + ## + push @where, "(cust_main.tax = '' OR cust_main.tax IS NULL )" + if $params->{'no_tax'}; + + ## # dates ## @@ -860,10 +921,10 @@ sub search { : ( $params->{'payby'} ); @payby = grep /^([A-Z]{4})$/, @payby; - - push @where, '( '. join(' OR ', map "cust_main.payby = '$_'", @payby). ' )' + my $in_payby = 'IN(' . join(',', map {"'$_'"} @payby) . ')'; + push @where, "EXISTS( SELECT 1 FROM cust_payby WHERE payby $in_payby ". + "AND cust_payby.custnum = cust_main.custnum)" if @payby; - } ###