X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2Fcust_main%2FSearch.pm;h=7216fe604227433588b6ef4125b58988d0d5ada4;hb=9f2da34557def5325781121ba4f6ac1eb101cb77;hp=353542df04c4d3df2d8e7bb1ffa8c8803e4e1fd6;hpb=d4df360c7618d45cc60647bb31520f757117992f;p=freeside.git diff --git a/FS/FS/cust_main/Search.pm b/FS/FS/cust_main/Search.pm index 353542df0..7216fe604 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); @@ -617,7 +618,7 @@ sub search { my $dbh = dbh; my @where = (); - my $orderby; + my $orderby = "ORDER BY cust_main.custnum"; # initialize these to prevent warnings $params = { @@ -814,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 @@ -900,7 +937,7 @@ sub search { } } - $orderby ||= "ORDER BY cust_main.$field"; + $orderby .= ", cust_main.$field"; } @@ -1026,10 +1063,59 @@ 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 ($contact_params->{'contacts_homephone'} || $contact_params->{'contacts_workphone'} || $contact_params->{'contacts_mobilephone'}) { + foreach my $phone (qw( contacts_homephone contacts_workphone contacts_mobilephone )) { + (my $num = $contact_params->{$phone}) =~ s/\W//g; + if ( $num =~ /^1?(\d{3})(\d{3})(\d{4})(\d*)$/ ) { $contact_params->{$phone} = "$1$2$3"; } + } + my $home_query = " AND ( contact_phone.phonetypenum = '2' AND contact_phone.phonenum = '" . $contact_params->{'contacts_homephone'} . "' )" + unless !$contact_params->{'contacts_homephone'}; + my $work_query = " AND ( contact_phone.phonetypenum = '1' AND contact_phone.phonenum = '" . $contact_params->{'contacts_workphone'} . "' )" + unless !$contact_params->{'contacts_workphone'}; + my $mobile_query = " AND ( contact_phone.phonetypenum = '3' AND contact_phone.phonenum = '" . $contact_params->{'contacts_mobilephone'} . "' )" + unless !$contact_params->{'contacts_mobilephone'}; + push @where, + "EXISTS ( SELECT 1 FROM contact_phone + JOIN cust_contact USING (contactnum) + WHERE cust_contact.custnum = cust_main.custnum + $home_query $work_query $mobile_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, @@ -1049,8 +1135,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', @@ -1064,44 +1148,7 @@ sub search { my @extra_fields = (); my @extra_sort_fields = (); - ## search contacts - if ($params->{'contacts'}) { - my $contact_params = $params->{'contacts'}; - - $addl_from .= - ' LEFT JOIN cust_contact ON ( cust_main.custnum = cust_contact.custnum ) '; - - if ($contact_params->{'contacts_firstname'} || $contact_params->{'contacts_lastname'}) { - $addl_from .= ' LEFT JOIN contact ON ( cust_contact.contactnum = contact.contactnum ) '; - 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'}; - $extra_sql .= " AND ( '1' $first_query $last_query )"; - } - - if ($contact_params->{'contacts_email'}) { - $addl_from .= ' LEFT JOIN contact_email ON ( cust_contact.contactnum = contact_email.contactnum ) '; - $extra_sql .= " AND ( contact_email.emailaddress = '" . $contact_params->{'contacts_email'} . "' )"; - } - - if ($contact_params->{'contacts_homephone'} || $contact_params->{'contacts_workphone'} || $contact_params->{'contacts_mobilephone'}) { - $addl_from .= ' LEFT JOIN contact_phone ON ( cust_contact.contactnum = contact_phone.contactnum ) '; - my $contacts_mobilephone; - foreach my $phone (qw( contacts_homephone contacts_workphone contacts_mobilephone )) { - (my $num = $contact_params->{$phone}) =~ s/\W//g; - if ( $num =~ /^1?(\d{3})(\d{3})(\d{4})(\d*)$/ ) { $contact_params->{$phone} = "$1$2$3"; } - } - my $home_query = " AND ( contact_phone.phonetypenum = '2' AND contact_phone.phonenum = '" . $contact_params->{'contacts_homephone'} . "' )" - unless !$contact_params->{'contacts_homephone'}; - my $work_query = " AND ( contact_phone.phonetypenum = '1' AND contact_phone.phonenum = '" . $contact_params->{'contacts_workphone'} . "' )" - unless !$contact_params->{'contacts_workphone'}; - my $mobile_query = " AND ( contact_phone.phonetypenum = '3' AND contact_phone.phonenum = '" . $contact_params->{'contacts_mobilephone'} . "' )" - unless !$contact_params->{'contacts_mobilephone'}; - $extra_sql .= " AND ( '1' $home_query $work_query $mobile_query )"; - } - - } + my $count_query = "SELECT COUNT(DISTINCT cust_main.custnum) FROM cust_main $addl_from $extra_sql"; if ($params->{'flattened_pkgs'}) { @@ -1166,7 +1213,8 @@ sub search { } - my $select = join(', ', @select); + # set select a customer only once. some joins create multiple customer records. + my $select = 'DISTINCT ON (cust_main.custnum) ' . join(', ', @select); my $sql_query = { 'table' => 'cust_main', @@ -1401,4 +1449,3 @@ L, L =cut 1; -