$DEBUG = 0;
$me = '[FS::cust_main::Search]';
-@fuzzyfields = @FS::cust_main::fuzzyfields;
+@fuzzyfields = ( 'first', 'last', 'company', 'address1' );
install_callback FS::UID sub {
$conf = new FS::Conf;
my %options = @_;
#here is the agent virtualization
- my $agentnums_sql = $FS::CurrentUser::CurrentUser->agentnums_sql;
+ my $agentnums_sql =
+ $FS::CurrentUser::CurrentUser->agentnums_sql(table => 'cust_main');
my @cust_main = ();
'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
|| ( $conf->config('cust_main-agent_custid-format') eq 'ww?d+'
&& $search =~ /^\s*(\w\w?\d+)\s*$/
)
+ || ( $conf->config('cust_main-custnum-display_special')
+ # it's not currently possible for special prefixes to contain
+ # digits, so just strip off any alphabetic prefix and match
+ # the rest to custnum
+ && $search =~ /^\s*[[:alpha:]]*(\d+)\s*$/
+ )
|| ( $conf->exists('address1-search' )
&& $search =~ /^\s*(\d+\-?\w*)\s*$/ #i.e. 1234A or 9432-D
)
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",
} );
}
+ # for all agents this user can see, if any of them have custnum prefixes
+ # that match the search string, include customers that match the rest
+ # of the custnum and belong to that agent
+ foreach my $agentnum ( $FS::CurrentUser::CurrentUser->agentnums ) {
+ my $p = $conf->config('cust_main-custnum-display_prefix', $agentnum);
+ next if !$p;
+ if ( $p eq substr($num, 0, length($p)) ) {
+ push @cust_main, qsearch( {
+ 'table' => 'cust_main',
+ 'hashref' => { 'custnum' => 0 + substr($num, length($p)),
+ 'agentnum' => $agentnum,
+ %options,
+ },
+ } );
+ }
+ }
+
push @cust_main, qsearch( {
- 'table' => 'cust_main',
- 'hashref' => { 'agent_custid' => $num, %options },
- 'extra_sql' => " AND $agentnums_sql", #agent virtualization
+ 'table' => 'cust_main',
+ 'hashref' => { 'agent_custid' => $num, %options },
+ 'extra_sql' => " AND $agentnums_sql", #agent virtualization
} );
if ( $conf->exists('address1-search') ) {
=item address
+=item refnum
+
=item cancelled_pkgs
bool
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
my @where = ();
my $orderby;
+ # initialize these to prevent warnings
+ $params = {
+ 'custnum' => '',
+ 'agentnum' => '',
+ 'usernum' => '',
+ 'status' => '',
+ 'address' => '',
+ 'paydate_year' => '',
+ 'invoice_terms' => '',
+ 'custbatch' => '',
+ %$params
+ };
+
+ ##
+ # explicit custnum(s)
+ ##
+
+ if ( $params->{'custnum'} ) {
+ my @custnums = ref($params->{'custnum'}) ?
+ @{ $params->{'custnum'} } :
+ $params->{'custnum'};
+ push @where,
+ 'cust_main.custnum IN (' .
+ join(',', map { $_ =~ /^(\d+)$/ ? $1 : () } @custnums ) .
+ ')' if scalar(@custnums) > 0;
+ }
+
##
# parse agent
##
')';
}
+ ###
+ # refnum
+ ###
+ 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 cancelled package checkbox
##
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";
}
$orderby ||= 'ORDER BY custnum';
# here is the agent virtualization
- push @where, $FS::CurrentUser::CurrentUser->agentnums_sql;
+ push @where,
+ $FS::CurrentUser::CurrentUser->agentnums_sql(table => 'cust_main');
my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : '';
- my $addl_from = 'LEFT JOIN cust_pkg USING ( custnum ) ';
+ my $addl_from = '';
my $count_query = "SELECT COUNT(*) FROM cust_main $extra_sql";
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') {
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(pkg SEPARATOR '|') as magic";
- $addl_from .= " LEFT JOIN part_pkg using ( pkgpart )";
- }else{
+ } elsif ($dbh->{Driver}->{Name} =~ /^mysql/i) {
+ push @select, "GROUP_CONCAT(part_pkg.pkg SEPARATOR '|') as magic";
+ $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";
my $sql_query = {
'table' => 'cust_main',
'select' => $select,
+ 'addl_from' => $addl_from,
'hashref' => {},
'extra_sql' => $extra_sql,
'order_by' => $orderby,
sub check_and_rebuild_fuzzyfiles {
my $dir = $FS::UID::conf_dir. "/cache.". $FS::UID::datasrc;
- rebuild_fuzzyfiles() if grep { ! -e "$dir/cust_main.$_" } @fuzzyfields
+ rebuild_fuzzyfiles() if grep { ! -e "$dir/cust_main.$_" } @fuzzyfields;
}
=item rebuild_fuzzyfiles
flock(LOCK,LOCK_EX)
or die "can't lock $dir/cust_main.$fuzzy: $!";
- open (CACHE,">$dir/cust_main.$fuzzy.tmp")
+ open (CACHE, '>:encoding(UTF-8)', "$dir/cust_main.$fuzzy.tmp")
or die "can't open $dir/cust_main.$fuzzy.tmp: $!";
foreach my $field ( $fuzzy, "ship_$fuzzy" ) {
}
+=item append_fuzzyfiles FIRSTNAME LASTNAME COMPANY ADDRESS1
+
+=cut
+
+sub append_fuzzyfiles {
+ #my( $first, $last, $company ) = @_;
+
+ check_and_rebuild_fuzzyfiles();
+
+ use Fcntl qw(:flock);
+
+ my $dir = $FS::UID::conf_dir. "/cache.". $FS::UID::datasrc;
+
+ foreach my $field (@fuzzyfields) {
+ my $value = shift;
+
+ if ( $value ) {
+
+ open(CACHE, '>>:encoding(UTF-8)', "$dir/cust_main.$field" )
+ or die "can't open $dir/cust_main.$field: $!";
+ flock(CACHE,LOCK_EX)
+ or die "can't lock $dir/cust_main.$field: $!";
+
+ print CACHE "$value\n";
+
+ flock(CACHE,LOCK_UN)
+ or die "can't unlock $dir/cust_main.$field: $!";
+ close CACHE;
+ }
+
+ }
+
+ 1;
+}
+
=item all_X
=cut
sub all_X {
my( $self, $field ) = @_;
my $dir = $FS::UID::conf_dir. "/cache.". $FS::UID::datasrc;
- open(CACHE,"<$dir/cust_main.$field")
+ open(CACHE, '<:encoding(UTF-8)', "$dir/cust_main.$field")
or die "can't open $dir/cust_main.$field: $!";
my @array = map { chomp; $_; } <CACHE>;
close CACHE;
projects / freeside.git / blobdiff