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
'extra_sql' => ( scalar(keys %options) ? ' AND ' : ' WHERE ' ).
' ( '.
join(' OR ', map "$_ = '$phonen'",
- qw( daytime night fax
- ship_daytime ship_night ship_fax )
+ qw( daytime night mobile fax
+ ship_daytime ship_night ship_mobile ship_fax )
).
' ) '.
" AND $agentnums_sql", #agent virtualization
}
- # 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 $num = $1;
if ( $num =~ /^(\d+)$/ && $num <= 2147483647 ) { #need a bigint custnum? wow
+ my $agent_custid_null = $conf->exists('cust_main-default_agent_custid')
+ ? ' AND agent_custid IS NULL ' : '';
push @cust_main, qsearch( {
'table' => 'cust_main',
'hashref' => { 'custnum' => $num, %options },
- 'extra_sql' => " AND $agentnums_sql", #agent virtualization
+ 'extra_sql' => " AND $agentnums_sql $agent_custid_null",
} );
}
}
}
+ push @cust_main, qsearch( {
+ 'table' => 'cust_main',
+ 'hashref' => { 'agent_custid' => $num, %options },
+ 'extra_sql' => " AND $agentnums_sql", #agent virtualization
+ } );
+
if ( $conf->exists('address1-search') ) {
my $len = length($num);
$num = lc($num);
}
+ ( my $nospace_search = $search ) =~ s/\s//g;
+ ( my $card_search = $nospace_search ) =~ s/\-//g;
+ $card_search =~ s/[x\*\.\_]/x/gi;
+
+ if ( $nospace_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;
listref of start date, end date
+=item birthdate
+
+listref of start date, end date
+
+=item spouse_birthdate
+
+listref of start date, end date
+
+=item anniversary_date
+
+listref of start date, end date
+
=item payby
listref
###
# refnum
###
- if ( $params->{'refnum'} =~ /^(\d+)$/ ) {
- push @where, "refnum = $1";
+ if ( $params->{'refnum'} ) {
+
+ my @refnum = ref( $params->{'refnum'} )
+ ? @{ $params->{'refnum'} }
+ : ( $params->{'refnum'} );
+
+ @refnum = grep /^(\d*)$/, @refnum;
+
+ push @where, '( '. join(' OR ', map "cust_main.refnum = $_", @refnum ). ' )'
+ if @refnum;
+
}
##
if $params->{'with_geocode'};
##
+ # "with email address(es)" checkbox
+ ##
+
+ push @where,
+ 'EXISTS ( SELECT 1 FROM cust_main_invoice
+ WHERE cust_main_invoice.custnum = cust_main.custnum
+ AND length(dest) > 5
+ )' # AND dest LIKE '%@%'
+ if $params->{'with_email'};
+
+ ##
+ # "without postal mail invoices" checkbox
+ ##
+
+ push @where,
+ "NOT EXISTS ( SELECT 1 FROM cust_main_invoice
+ WHERE cust_main_invoice.custnum = cust_main.custnum
+ AND dest = 'POST' )"
+ if $params->{'no_POST'};
+
+ ##
# dates
##
- foreach my $field (qw( signupdate )) {
+ foreach my $field (qw( signupdate birthdate spouse_birthdate anniversary_date )) {
next unless exists($params->{$field});
"cust_main.$field >= $beginning",
"cust_main.$field <= $ending";
- if(defined $hour) {
+ if($field eq 'signupdate' && defined $hour) {
if ($dbh->{Driver}->{Name} =~ /Pg/i) {
push @where, "extract(hour from to_timestamp(cust_main.$field)) = $hour";
}
if ($params->{'flattened_pkgs'}) {
#my $pkg_join = '';
+ $addl_from .=
+ ' LEFT JOIN cust_pkg ON ( cust_main.custnum = cust_pkg.custnum ) ';
if ($dbh->{Driver}->{Name} eq 'Pg') {
} elsif ($dbh->{Driver}->{Name} =~ /^mysql/i) {
push @select, "GROUP_CONCAT(part_pkg.pkg SEPARATOR '|') as magic";
- $addl_from .= ' LEFT JOIN cust_pkg USING ( custnum ) '; #Pg too w/flatpkg?
$addl_from .= ' LEFT JOIN part_pkg USING ( pkgpart ) ';
#$pkg_join .= ' LEFT JOIN part_pkg USING ( pkgpart ) ';
} else {
warn "warning: unknown database type ". $dbh->{Driver}->{Name}.
- "omitting packing information from report.";
+ "omitting package information from report.";
}
my $header_query = "SELECT COUNT(cust_pkg.custnum = cust_main.custnum) AS count FROM cust_main $addl_from $extra_sql $pkgwhere group by cust_main.custnum order by count desc limit 1";