# 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*$/
- )
- || ( $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
- )
- )
+ } elsif ( $search =~ /^\s*(\d+)\s*$/
+ or ( $conf->config('cust_main-agent_custid-format') eq 'ww?d+'
+ && $search =~ /^\s*(\w\w?\d+)\s*$/
+ )
+ or ( $conf->config('cust_main-agent_custid-format') eq 'd+-w'
+ && $search =~ /^\s*(\d+-\w)\s*$/
+ )
+ or ( $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*$/
+ )
+ or ( $conf->exists('address1-search' )
+ && $search =~ /^\s*(\d+\-?\w*)\s*$/ #i.e. 1234A or 9432-D
+ )
+ )
{
my $num = $1;
# 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, },
#still some false laziness w/search (was search/cust_main.cgi)
+ my $min_len =
+ $FS::CurrentUser::CurrentUser->access_right('List all customers')
+ ? 3 : 4;
+
#substring
my @company_hashrefs = ();
- if ( length($value) >= 4 ) {
+ if ( length($value) >= $min_len ) {
@company_hashrefs = (
{ 'company' => { op=>'ILIKE', value=>"%$value%" }, },
{ 'ship_company' => { op=>'ILIKE', value=>"%$value%" }, },
},
);
- } elsif ( length($value) >= 4 ) {
+ } elsif ( length($value) >= $min_len ) {
@hashrefs = (
{ 'first' => { op=>'ILIKE', value=>"%$value%" }, },
}
- if ( $conf->exists('address1-search') && length($value) >= 4 ) {
+ 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 '.
if $params->{'no_tax'};
##
+ # with referrals
+ ##
+ if ( $params->{with_referrals} =~ /^\s*(\d+)\s*$/ ) {
+
+ my $n = $1;
+
+ # referral status
+ my $and_status = '';
+ if ( grep { $params->{referral_status} eq $_ } FS::cust_main->statuses() ) {
+ my $method = $params->{referral_status}. '_sql';
+ $and_status = ' AND '. FS::cust_main->$method();
+ $and_status =~ s/ cust_main\./ referred_cust_main./g;
+ }
+
+ push @where,
+ " $n <= ( SELECT COUNT(*) FROM cust_main AS referred_cust_main
+ WHERE cust_main.custnum = referred_cust_main.referral_custnum
+ $and_status
+ )";
+
+ }
+
+ ##
# dates
##
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 = '.
$_ . ')';
}
FS::UI::Web::cust_sql_fields($params->{'cust_fields'}),
);
- my(@extra_headers) = ();
- my(@extra_fields) = ();
+ my @extra_headers = ();
+ my @extra_fields = ();
+ my @extra_sort_fields = ();
if ($params->{'flattened_pkgs'}) {
my $p = $a[!.--$headercount. q!];
$p;
};!;
+ unshift @extra_sort_fields, '';
}
}
+ if ( $params->{'with_referrals'} ) {
+
+ #XXX next: num for each customer status
+
+ push @select,
+ '( SELECT COUNT(*) FROM cust_main AS referred_cust_main
+ WHERE cust_main.custnum = referred_cust_main.referral_custnum
+ ) AS num_referrals';
+
+ unshift @extra_headers, 'Referrals';
+ unshift @extra_fields, 'num_referrals';
+ unshift @extra_sort_fields, 'num_referrals';
+
+ }
+
my $select = join(', ', @select);
my $sql_query = {
- 'table' => 'cust_main',
- 'select' => $select,
- 'addl_from' => $addl_from,
- 'hashref' => {},
- 'extra_sql' => $extra_sql,
- 'order_by' => $orderby,
- 'count_query' => $count_query,
- 'extra_headers' => \@extra_headers,
- 'extra_fields' => \@extra_fields,
+ 'table' => 'cust_main',
+ 'select' => $select,
+ 'addl_from' => $addl_from,
+ 'hashref' => {},
+ 'extra_sql' => $extra_sql,
+ 'order_by' => $orderby,
+ 'count_query' => $count_query,
+ 'extra_headers' => \@extra_headers,
+ 'extra_fields' => \@extra_fields,
+ 'extra_sort_fields' => \@extra_sort_fields,
};
#warn Data::Dumper::Dumper($sql_query);
$sql_query;