use FS::cust_main;
use FS::cust_main_invoice;
use FS::svc_acct;
+use FS::payinfo_Mixin;
@EXPORT_OK = qw( smart_search );
$DEBUG = 0;
$me = '[FS::cust_main::Search]';
-@fuzzyfields = ( 'first', 'last', 'company', 'address1' );
+@fuzzyfields = ( 'cust_main.first', 'cust_main.last', 'cust_main.company',
+ 'cust_location.address1' );
install_callback FS::UID sub {
$conf = new FS::Conf;
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 )
+ qw( daytime night mobile 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",
} );
}
my %fuzopts = (
'hashref' => \%options,
'select' => '',
- 'extra_sql' => " AND $agentnums_sql", #agent virtualization
+ 'extra_sql' => "WHERE $agentnums_sql", #agent virtualization
);
if ( $first && $last ) {
}
if ( $conf->exists('address1-search') ) {
push @cust_main,
- FS::cust_main::Search->fuzzy_search( { 'address1' => $value }, %fuzopts );
+ FS::cust_main::Search->fuzzy_search(
+ { 'cust_location.address1' => $value }, %fuzopts );
}
}
}
+ ( my $nospace_search = $search ) =~ s/\s//g;
+ ( my $card_search = $nospace_search ) =~ s/\-//g;
+ $card_search =~ s/[x\*\.\_]/x/gi;
+
+ if ( $nospace_search =~ /^[\dx]{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;
=item address
+=item zip
+
=item refnum
=item cancelled_pkgs
listref of start date, end date
+=item anniversary_date
+
+listref of start date, end date
+
=item payby
listref
'usernum' => '',
'status' => '',
'address' => '',
+ 'zip' => '',
'paydate_year' => '',
'invoice_terms' => '',
'custbatch' => '',
)";
}
+ ##
+ # zipcode
+ ##
+ if ( $params->{'zip'} =~ /\S/ ) {
+ my $zip = dbh->quote($params->{'zip'} . '%');
+ push @where, "EXISTS(
+ SELECT 1 FROM cust_location
+ WHERE cust_location.custnum = cust_main.custnum
+ AND cust_location.zip LIKE $zip
+ )";
+ }
+
###
# 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;
+
}
##
# 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"
+ push @where, "ship_location.geocode is not null"
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'};
+
+ ##
+ # "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
+ ##
+
+ 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 birthdate spouse_birthdate )) {
+ foreach my $field (qw( signupdate birthdate spouse_birthdate anniversary_date )) {
next unless exists($params->{$field});
@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;
+ }
}
}
my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : '';
my $addl_from = '';
+ # always make address fields available in results
+ for my $pre ('bill_', 'ship_') {
+ $addl_from .=
+ 'LEFT JOIN cust_location AS '.$pre.'location '.
+ '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',
+ # there's a good chance that we'll need these
+ 'cust_main.bill_locationnum',
+ 'cust_main.ship_locationnum',
FS::UI::Web::cust_sql_fields($params->{'cust_fields'}),
);
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') {
- push @select, "array_to_string(array(select pkg from cust_pkg left join part_pkg using ( pkgpart ) where cust_main.custnum = cust_pkg.custnum $pkgwhere),'|') as magic";
+ push @select, "
+ ARRAY_TO_STRING(
+ ARRAY(
+ SELECT pkg FROM cust_pkg LEFT JOIN part_pkg USING ( pkgpart )
+ WHERE cust_main.custnum = cust_pkg.custnum $pkgwhere
+ ), '|'
+ ) AS magic
+ ";
} elsif ($dbh->{Driver}->{Name} =~ /^mysql/i) {
push @select, "GROUP_CONCAT(part_pkg.pkg SEPARATOR '|') as magic";
"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";
+ 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
+ ";
my $sth = dbh->prepare($header_query) or die dbh->errstr;
$sth->execute() or die $sth->errstr;
'extra_headers' => \@extra_headers,
'extra_fields' => \@extra_fields,
};
+ warn Data::Dumper::Dumper($sql_query);
+ $sql_query;
}
=cut
sub fuzzy_search {
- my( $self, $fuzzy ) = @_;
+ my $self = shift;
+ my $fuzzy = shift;
# sensible defaults, then merge in any passed options
my %fuzopts = (
'table' => 'cust_main',
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 ) );
-
- my @fcust = ();
- foreach ( keys %match ) {
- if ( $field eq 'address1' ) {
- #because it lives outside the table
- my $addl_from = $fuzopts{addl_from} .
- 'JOIN cust_location USING (custnum)';
- my $extra_sql = $fuzopts{extra_sql} .
- " AND cust_location.address1 = ".dbh->quote($_);
- push @fcust, qsearch({
- %fuzopts,
- 'addl_from' => $addl_from,
- 'extra_sql' => $extra_sql,
- });
- } else {
- my $hash = $fuzopts{hashref};
- $hash->{$field} = $_;
- push @fcust, qsearch({
- %fuzopts,
- 'hashref' => $hash
- });
- }
+ $match{$_}=1 foreach ( amatch( $fuzzy->{$field}, \@fuzzy_mod, @$all ) );
+ next if !keys(%match);
+
+ my $in_matches = 'IN (' .
+ join(',', map { dbh->quote($_) } keys %match) .
+ ')';
+
+ my $extra_sql = $fuzopts{extra_sql};
+ if ($extra_sql =~ /^\s*where /i or keys %{ $fuzopts{hashref} }) {
+ $extra_sql .= ' AND ';
+ } else {
+ $extra_sql .= 'WHERE ';
}
- my %fsaw = ();
- push @cust_main, grep { ! $fsaw{$_->custnum}++ } @fcust;
+ $extra_sql .= "$field $in_matches";
+
+ my $addl_from = $fuzopts{addl_from};
+ if ( $field =~ /^cust_location/ ) {
+ $addl_from .= ' JOIN cust_location USING (custnum)';
+ }
+
+ push @cust_main, qsearch({
+ %fuzopts,
+ 'addl_from' => $addl_from,
+ 'extra_sql' => $extra_sql,
+ });
}
# we want the components of $fuzzy ANDed, not ORed, but still don't want dupes
foreach my $fuzzy ( @fuzzyfields ) {
- open(LOCK,">>$dir/cust_main.$fuzzy")
- or die "can't open $dir/cust_main.$fuzzy: $!";
- flock(LOCK,LOCK_EX)
- or die "can't lock $dir/cust_main.$fuzzy: $!";
+ my ($field, $table) = reverse split('\.', $fuzzy);
+ $table ||= 'cust_main';
- open (CACHE, '>:encoding(UTF-8)', "$dir/cust_main.$fuzzy.tmp")
- or die "can't open $dir/cust_main.$fuzzy.tmp: $!";
+ open(LOCK,">>$dir/$table.$field")
+ or die "can't open $dir/$table.$field: $!";
+ flock(LOCK,LOCK_EX)
+ or die "can't lock $dir/$table.$field: $!";
- foreach my $field ( $fuzzy, "ship_$fuzzy" ) {
- my $sth = dbh->prepare("SELECT $field FROM cust_main".
- " WHERE $field != '' AND $field IS NOT NULL");
- $sth->execute or die $sth->errstr;
+ open (CACHE, '>:encoding(UTF-8)', "$dir/$table.$field.tmp")
+ or die "can't open $dir/$table.$field.tmp: $!";
- while ( my $row = $sth->fetchrow_arrayref ) {
- print CACHE $row->[0]. "\n";
- }
+ my $sth = dbh->prepare(
+ "SELECT $field FROM $table WHERE $field IS NOT NULL AND $field != ''"
+ );
+ $sth->execute or die $sth->errstr;
- }
+ while ( my $row = $sth->fetchrow_arrayref ) {
+ print CACHE $row->[0]. "\n";
+ }
- close CACHE or die "can't close $dir/cust_main.$fuzzy.tmp: $!";
+ close CACHE or die "can't close $dir/$table.$field.tmp: $!";
- rename "$dir/cust_main.$fuzzy.tmp", "$dir/cust_main.$fuzzy";
+ rename "$dir/$table.$field.tmp", "$dir/$table.$field";
close LOCK;
}
my $dir = $FS::UID::conf_dir. "/cache.". $FS::UID::datasrc;
- foreach my $field (@fuzzyfields) {
+ foreach my $fuzzy (@fuzzyfields) {
+
+ my ($field, $table) = reverse split('\.', $fuzzy);
+ $table ||= 'cust_main';
+
my $value = shift;
if ( $value ) {
- open(CACHE, '>>:encoding(UTF-8)', "$dir/cust_main.$field" )
- or die "can't open $dir/cust_main.$field: $!";
+ open(CACHE, '>>:encoding(UTF-8)', "$dir/$table.$field" )
+ or die "can't open $dir/$table.$field: $!";
flock(CACHE,LOCK_EX)
- or die "can't lock $dir/cust_main.$field: $!";
+ or die "can't lock $dir/$table.$field: $!";
print CACHE "$value\n";
flock(CACHE,LOCK_UN)
- or die "can't unlock $dir/cust_main.$field: $!";
+ or die "can't unlock $dir/$table.$field: $!";
close CACHE;
}
=cut
sub all_X {
- my( $self, $field ) = @_;
+ my( $self, $fuzzy ) = @_;
+ my ($field, $table) = reverse split('\.', $fuzzy);
+ $table ||= 'cust_main';
+
my $dir = $FS::UID::conf_dir. "/cache.". $FS::UID::datasrc;
- open(CACHE, '<:encoding(UTF-8)', "$dir/cust_main.$field")
- or die "can't open $dir/cust_main.$field: $!";
+ open(CACHE, '<:encoding(UTF-8)', "$dir/$table.$field")
+ or die "can't open $dir/$table.$field: $!";
my @array = map { chomp; $_; } <CACHE>;
close CACHE;
\@array;
projects / freeside.git / blobdiff