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);
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\%'",
}
- 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
# 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, },
#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)),
" LOWER(company) = ". dbh->quote(lc($company)),
$agentnums_sql,
),
- } ),
+ } );
#contacts?
# probably not necessary for the "something a browser remembered" case
} 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'});
}
#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)
}
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 (
)"
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
} );
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 '.
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' => {},
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
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 = ();
+
+ ## 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 )";
+ }
+
+ }
if ($params->{'flattened_pkgs'}) {
my $p = $a[!.--$headercount. q!];
$p;
};!;
+ unshift @extra_sort_fields, '';
}
}
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;
=cut
1;
-
projects / freeside.git / blobdiff