X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=FS%2FFS%2Fcust_main%2FSearch.pm;h=e0211401653cf6460dc152688c8c0431aa4f0ee4;hp=16db71271ab211bcc4c9250f75d4517bc219727a;hb=eae5bf77222d04df4727ebb3e5a61fafec033c2c;hpb=cf4e690746ef76895e50d057b9e3f0e660f8e21e diff --git a/FS/FS/cust_main/Search.pm b/FS/FS/cust_main/Search.pm index 16db71271..e02114016 100644 --- a/FS/FS/cust_main/Search.pm +++ b/FS/FS/cust_main/Search.pm @@ -21,6 +21,7 @@ $me = '[FS::cust_main::Search]'; @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', ); @@ -321,6 +322,7 @@ sub smart_search { $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 "; #address1 (yes, it's a kludge) @@ -356,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 ) { - 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', @@ -402,8 +407,6 @@ sub smart_search { #contact substring - shift @hashrefs; #no company column in contact table - foreach my $hashref ( @hashrefs ) { push @cust_main, @@ -439,7 +442,7 @@ sub smart_search { %fuzopts ); } - foreach my $field ( 'first', 'last', 'company' ) { + foreach my $field ( 'first', 'last', 'company', 'ship_company' ) { push @cust_main, FS::cust_main::Search->fuzzy_search( { $field => $value }, %fuzopts @@ -474,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 }); @@ -510,11 +514,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 = (); @@ -527,10 +529,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 }, } ); @@ -604,14 +608,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')) @@ -642,7 +638,6 @@ sub search { 'status' => '', 'address' => '', 'zip' => '', - 'paydate_year' => '', 'invoice_terms' => '', 'custbatch' => '', %$params @@ -724,6 +719,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/ ) { @@ -735,6 +766,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 ### @@ -765,33 +808,39 @@ sub search { ## push @where, - 'EXISTS ( SELECT 1 FROM cust_main_invoice - WHERE cust_main_invoice.custnum = cust_main.custnum - AND length(dest) > 5 - )' # AND dest LIKE '%@%' + 'EXISTS ( SELECT 1 FROM contact_email + JOIN cust_contact USING (contactnum) + WHERE cust_contact.custnum = cust_main.custnum + )' 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' )" + push @where, "cust_main.postal_invoice = 'Y'" 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' )" + push @where, "cust_main.postal_invoice IS NULL" 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 ## @@ -847,40 +896,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 ### @@ -946,6 +961,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 @@ -967,10 +1011,15 @@ sub search { 'ON (cust_main.'.$pre.'locationnum = '.$pre.'location.locationnum) '; } + # 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 $count_query = "SELECT COUNT(*) FROM cust_main $addl_from $extra_sql"; 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', @@ -1040,7 +1089,6 @@ sub search { 'extra_headers' => \@extra_headers, 'extra_fields' => \@extra_fields, }; - #warn Data::Dumper::Dumper($sql_query); $sql_query; } @@ -1193,6 +1241,7 @@ sub append_fuzzyfiles { #foreach my $fuzzy (@fuzzyfields) { foreach my $fuzzy ( 'cust_main.first', 'cust_main.last', 'cust_main.company', 'cust_location.address1', + 'cust_main.ship_company', ) { append_fuzzyfiles_fuzzyfield($fuzzy, shift); @@ -1218,7 +1267,7 @@ sub append_fuzzyfiles_fuzzyfield { my ($field, $table) = reverse split('\.', $fuzzyfield); $table ||= 'cust_main'; - return unless length($value); + return unless defined($value) && length($value); open(CACHE, '>>:encoding(UTF-8)', "$dir/$table.$field" ) or die "can't open $dir/$table.$field: $!";