X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=FS%2FFS%2Fcust_main%2FSearch.pm;h=bfaf6217ced916e872566e1b10c826fa3f771160;hp=2ec87cd14dfa207af99262737167fccefc960bb0;hb=adbca1f8b7f96efe7915ecc8c101ad1cb8c5f6f2;hpb=90430c5c67581b41b3c4ad48f70b9e443092977b diff --git a/FS/FS/cust_main/Search.pm b/FS/FS/cust_main/Search.pm index 2ec87cd14..bfaf6217c 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); @@ -95,8 +96,11 @@ sub smart_search { #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'", @@ -105,15 +109,14 @@ sub smart_search { " OR phonenum = '$phonenum' ". ' ) '. " AND $agentnums_sql", #agent virtualization - 'addl_from' => ' left join cust_contact using (custnum) left join contact_phone using (contactnum) ', } ); unless ( @cust_main || $phonen =~ /x\d+$/ ) { #no exact match #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\%'", @@ -131,8 +134,12 @@ sub smart_search { if ( $search =~ /@/ ) { #email address from cust_main_invoice and contact_email push @cust_main, qsearch( { - 'table' => 'cust_main', - 'hashref' => { %options }, + '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'", @@ -140,7 +147,6 @@ sub smart_search { ). ' ) '. " AND $agentnums_sql", #agent virtualization - 'addl_from' => ' left join cust_main_invoice using (custnum) left join cust_contact using (custnum) left join contact_email using (contactnum) ', } ); # custnum search (also try agent_custid), with some tweaking options if your @@ -205,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, }, @@ -225,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)), @@ -235,7 +242,7 @@ sub smart_search { " LOWER(company) = ". dbh->quote(lc($company)), $agentnums_sql, ), - } ), + } ); #contacts? # probably not necessary for the "something a browser remembered" case @@ -281,11 +288,12 @@ sub smart_search { #cust_main and contacts push @cust_main, qsearch( { + 'select' => 'cust_main.*', 'table' => 'cust_main', - 'select' => 'cust_main.*, cust_contact.*, contact.contactnum, contact.last as contact_last, contact.first as contact_first, contact.title', + 'addl_from' => ' left join cust_contact using (custnum) '. + ' left join contact using (contactnum) ', 'hashref' => { %options }, 'extra_sql' => "$sql AND $agentnums_sql", #agent virtualization - 'addl_from' => ' left join cust_contact on cust_main.custnum = cust_contact.custnum left join contact using (contactnum) ', } ); # or it just be something that was typed in... (try that in a sec) @@ -313,11 +321,12 @@ sub smart_search { if $conf->exists('address1-search'); push @cust_main, qsearch( { + 'select' => 'cust_main.*', 'table' => 'cust_main', - 'select' => 'cust_main.*, cust_contact.*, contact.contactnum, contact.last as contact_last, contact.first as contact_first, contact.title', + 'addl_from' => ' left join cust_contact using (custnum) '. + ' left join contact using (contactnum) ', 'hashref' => { %options }, 'extra_sql' => "$sql AND $agentnums_sql", #agent virtualization - 'addl_from' => 'left join cust_contact on cust_main.custnum = cust_contact.custnum left join contact using (contactnum) ', } ); #no exact match, trying substring/fuzzy @@ -374,6 +383,7 @@ sub smart_search { if ( $conf->exists('address1-search') && length($value) >= $min_len ) { push @cust_main, qsearch( { + select => 'cust_main.*', table => 'cust_main', addl_from => 'JOIN cust_location USING (custnum)', extra_sql => 'WHERE '. @@ -455,6 +465,7 @@ 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' => {}, @@ -804,15 +815,51 @@ sub search { unless $params->{'cancelled_pkgs'}; ## - # "with email address(es)" checkbox + # "with email address(es)" checkbox, + # also optionally: with_email_dest and with_contact_type ## - push @where, - 'EXISTS ( SELECT 1 FROM contact_email + 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 - )' - if $params->{'with_email'}; + $email_dest_sql + $contact_type_sql + ) "; + } ## # "with postal mail invoices" checkbox @@ -1390,4 +1437,3 @@ L, L =cut 1; -