X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=FS%2FFS%2Fcust_main%2FSearch.pm;h=479ebf5c4acf829c949d1bc953a2086524623f0c;hp=27559d73dabaf7c6aa5ae7e618df8478a10d6649;hb=6b5dda03831aef0cb5689cf2acf3fac47e4b12cb;hpb=5e489861dba968c011f7cd539d806ab45acc210a diff --git a/FS/FS/cust_main/Search.pm b/FS/FS/cust_main/Search.pm index 27559d73d..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); @@ -93,35 +94,29 @@ sub smart_search { my $phonenum = "$1$2$3"; #my $extension = $4; - #cust_main phone numbers + #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 } ); - #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 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\%'", @@ -136,28 +131,23 @@ sub smart_search { } - if ( $search =~ /@/ ) { #email address - - # invoicing email address - push @cust_main, - grep $agentnums_href->{$_->agentnum}, #agent virt - map $_->cust_main, - qsearch( { - 'table' => 'cust_main_invoice', - 'hashref' => { 'dest' => $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 }, - } - ); + 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 @@ -221,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, }, @@ -241,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)), @@ -251,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,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'}); } @@ -292,28 +283,19 @@ 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 + #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 - 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) } @@ -326,7 +308,9 @@ sub smart_search { 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 ( @@ -336,19 +320,12 @@ sub smart_search { )" if $conf->exists('address1-search'); - #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( { + '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 } ); @@ -406,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 '. @@ -487,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' => {}, @@ -810,6 +789,21 @@ sub search { )"; } + ## + # 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 ### @@ -836,15 +830,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 @@ -1001,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 = '. $_ . ')'; } @@ -1047,10 +1078,58 @@ sub search { } ## + # 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, @@ -1070,8 +1149,6 @@ sub search { # (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', @@ -1081,8 +1158,11 @@ sub search { 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'}) { @@ -1127,6 +1207,7 @@ sub search { my $p = $a[!.--$headercount. q!]; $p; };!; + unshift @extra_sort_fields, ''; } } @@ -1142,21 +1223,23 @@ sub search { 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, }; $sql_query; @@ -1379,4 +1462,3 @@ L, L =cut 1; -