# 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
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' => {},
)";
}
+ ##
+ # no_censustract
+ ##
+ if ( $params->{'no_censustract'} ) {
+ push @where, "EXISTS(
+ SELECT 1 FROM cust_location
+ WHERE locationnum = cust_main.ship_locationnum
+ AND cust_location.country = 'US'
+ AND ( cust_location.censusyear IS NULL
+ OR cust_location.censusyear != '2020'
+ )
+ )";
+ }
+
+ ##
+ # phones
+ ##
+
+ foreach my $phonet (qw(daytime night mobile fax)) {
+ 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
###
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 = '.
$_ . ')';
}
}
}
+ ##
+ # 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,
# (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',
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'}) {