use FS::cust_main;
use FS::cust_main_invoice;
use FS::svc_acct;
+use FS::payinfo_Mixin;
@EXPORT_OK = qw( smart_search );
Accepts the following options: I<search>, the string to search for. The string
will be searched for as a customer number, phone number, name or company name,
-as an exact, or, in some cases, a substring or fuzzy match (see the source code
-for the exact heuristics used); I<no_fuzzy_on_exact>, causes smart_search to
+address (if address1-search is on), invoicing email address, or credit card
+number.
+
+Searches match as an exact, or, in some cases, a substring or fuzzy match (see
+the source code for the exact heuristics used); I<no_fuzzy_on_exact>, causes
+smart_search to
skip fuzzy matching when an exact match is found.
Any additional options are treated as an additional qualifier on the search
}
- # custnum search (also try agent_custid), with some tweaking options if your
- # legacy cust "numbers" have letters
}
- if ( $search =~ /@/ ) {
+ if ( $search =~ /@/ ) { #invoicing email address
push @cust_main,
map $_->cust_main,
qsearch( {
'hashref' => { 'dest' => $search },
}
);
+
+ # custnum search (also try agent_custid), with some tweaking options if your
+ # legacy cust "numbers" have letters
} elsif ( $search =~ /^\s*(\d+)\s*$/
|| ( $conf->config('cust_main-agent_custid-format') eq 'ww?d+'
&& $search =~ /^\s*(\w\w?\d+)\s*$/
}
+ ( my $nospace_search = $search ) =~ s/\s//g;
+ ( my $card_search = $nospace_search ) =~ s/\-//g;
+ $card_search =~ s/[x\*\.\_]/x/gi;
+
+ if ( $card_search =~ /^[\dx]{15,16}$/i ) { #credit card search
+
+ ( my $like_search = $card_search ) =~ s/x/_/g;
+ my $mask_search = FS::payinfo_Mixin->mask_payinfo('CARD', $card_search);
+
+ push @cust_main, qsearch({
+ 'table' => 'cust_main',
+ 'hashref' => {},
+ 'extra_sql' => " WHERE ( payinfo LIKE '$like_search'
+ OR paymask = '$mask_search'
+ ) ".
+ " AND payby IN ('CARD','DCRD') ".
+ " AND $agentnums_sql", #agent virtulization
+ });
+
+ }
+
+
#eliminate duplicates
my %saw = ();
@cust_main = grep { !$saw{$_->custnum}++ } @cust_main;
}
##
- # do the same for user
+ # parse sales person
+ ##
+
+ if ( $params->{'salesnum'} =~ /^(\d+)$/ ) {
+ push @where, ($1 > 0 ) ? "cust_main.salesnum = $1"
+ : 'cust_main.salesnum IS NULL';
+ }
+
+ ##
+ # parse usernum
##
if ( $params->{'usernum'} =~ /^(\d+)$/ and $1 ) {
##
# address
##
- if ( $params->{'address'} =~ /\S/ ) {
- my $address = dbh->quote('%'. lc($params->{'address'}). '%');
- push @where, "EXISTS(
- SELECT 1 FROM cust_location
- WHERE cust_location.custnum = cust_main.custnum
- AND (LOWER(cust_location.address1) LIKE $address OR
- LOWER(cust_location.address2) LIKE $address)
- )";
+ if ( $params->{'address'} ) {
+ # allow this to be an arrayref
+ my @values = ($params->{'address'});
+ @values = @{$values[0]} if ref($values[0]);
+ my @orwhere;
+ foreach (grep /\S/, @values) {
+ my $address = dbh->quote('%'. lc($_). '%');
+ push @orwhere,
+ "LOWER(cust_location.address1) LIKE $address",
+ "LOWER(cust_location.address2) LIKE $address";
+ }
+ if (@orwhere) {
+ push @where, "EXISTS(
+ SELECT 1 FROM cust_location
+ WHERE cust_location.custnum = cust_main.custnum
+ AND (".join(' OR ',@orwhere).")
+ )";
+ }
}
##
# parse without census tract checkbox
##
- push @where, "(censustract = '' or censustract is null)"
+ push @where, "(ship_location.censustract = '' or ship_location.censustract is null)"
if $params->{'no_censustract'};
##
# parse with hardcoded tax location checkbox
##
- push @where, "geocode is not null"
+ my $tax_prefix = FS::Conf->new->exists('tax-ship_location') ? 'ship_'
+ : 'bill_';
+ push @where, "${tax_prefix}location.geocode is not null"
if $params->{'with_geocode'};
##
)' # AND dest LIKE '%@%'
if $params->{'with_email'};
+ ##
+ # "with postal mail invoices" checkbox
+ ##
+
+ push @where,
+ "EXISTS ( SELECT 1 FROM cust_main_invoice
+ WHERE cust_main_invoice.custnum = cust_main.custnum
+ AND dest = 'POST' )"
+ if $params->{'POST'};
+
##
# "without postal mail invoices" checkbox
##
@tagnums = grep /^(\d+)$/, @tagnums;
if ( @tagnums ) {
+ if ( $params->{'all_tags'} ) {
+ foreach ( @tagnums ) {
+ push @where, 'exists(select 1 from cust_tag where '.
+ 'cust_tag.custnum = cust_main.custnum and tagnum = '.
+ $_ . ')';
+ }
+ } else { # matching any tag, not all
my $tags_where = "0 < (select count(1) from cust_tag where "
. " cust_tag.custnum = cust_main.custnum and tagnum in ("
. join(',', @tagnums) . "))";
push @where, $tags_where;
+ }
}
}
'ON (cust_main.'.$pre.'locationnum = '.$pre.'location.locationnum) ';
}
- my $count_query = "SELECT COUNT(*) FROM cust_main $extra_sql";
+ my $count_query = "SELECT COUNT(*) FROM cust_main $addl_from $extra_sql";
my @select = (
'cust_main.custnum',
if ($params->{'flattened_pkgs'}) {
#my $pkg_join = '';
- $addl_from .= ' LEFT JOIN cust_pkg USING ( custnum ) ';
+ $addl_from .=
+ ' LEFT JOIN cust_pkg ON ( cust_main.custnum = cust_pkg.custnum ) ';
if ($dbh->{Driver}->{Name} eq 'Pg') {
'extra_headers' => \@extra_headers,
'extra_fields' => \@extra_fields,
};
+ warn Data::Dumper::Dumper($sql_query);
+ $sql_query;
}
my @cust_main = ();
+ my @fuzzy_mod = 'i';
+ my $conf = new FS::Conf;
+ my $fuzziness = $conf->config('fuzzy-fuzziness');
+ push @fuzzy_mod, $fuzziness if $fuzziness;
+
check_and_rebuild_fuzzyfiles();
foreach my $field ( keys %$fuzzy ) {
next unless scalar(@$all);
my %match = ();
- $match{$_}=1 foreach ( amatch( $fuzzy->{$field}, ['i'], @$all ) );
+ $match{$_}=1 foreach ( amatch( $fuzzy->{$field}, \@fuzzy_mod, @$all ) );
next if !keys(%match);
my $in_matches = 'IN (' .