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);
#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'",
" 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\%'",
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'",
).
' ) '.
" 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
# 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
#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)
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
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 $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