my @where = ( $agentnums_sql );
+if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
+ push @where, "cust_main.agentnum = $1";
+}
+
my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
if ( $cgi->param('status') =~ /^([a-z]+)$/ ) {
push @where, "cust_bill._date >= $beginning",
"cust_bill._date <= $ending";
-if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
- push @where, "cust_main.agentnum = $1";
-}
-
-if ( $cgi->param('cust_classnum') ) {
- my @classnums = grep /^\d+$/, $cgi->param('cust_classnum');
- push @where, 'cust_main.classnum IN('.join(',',@classnums).')'
+# cust_classnum (false laziness w/ elements/cust_main_dayranges.html, elements/cust_pay_or_refund.html, prepaid_income.html, cust_bill_pay.html, cust_bill_pkg.html, cust_bill_pkg_referral.html, cust_credit.html, cust_credit_refund.html, cust_main::Search::search_sql)
+if ( grep { $_ eq 'cust_classnum' } $cgi->param ) {
+ my @classnums = grep /^\d*$/, $cgi->param('cust_classnum');
+ push @where, 'COALESCE( cust_main.classnum, 0) IN ( '.
+ join(',', map { $_ || '0' } @classnums ).
+ ' )'
if @classnums;
}
# last payment/credit date
my %t = (pay => 'cust_bill_pay', credit => 'cust_credit_bill');
foreach my $x (qw(pay credit)) {
- my $table = $t{$x};
- my $link = $table.'_pkg';
- my $pkey = dbdef->table($table)->primary_key;
- my $last_date_sql = "SELECT MAX(_date)
- FROM $table JOIN $link USING ($pkey)
- WHERE $link.billpkgnum = cust_bill_pkg.billpkgnum
+ my $table = "cust_$x";
+ my $link_bill = $t{$x};
+ my $link_pkg = $link_bill.'_pkg';
+ my %pkey = map { $_ => dbdef->table($_)->primary_key }
+ ( $table, $link_bill );
+
+ my $last_date_sql = "SELECT MAX($table._date)
+ FROM $table JOIN $link_bill USING ($pkey{$table})
+ JOIN $link_pkg USING ($pkey{$link_bill})
+ WHERE $link_pkg.billpkgnum = cust_bill_pkg.billpkgnum
AND $table._date <= $unearned";
push @select, "($last_date_sql) AS last_$x";
}