my( $count_query, $sql_query );
my $count_addl = '';
-my $distinct = '';
+#my $distinct = '';
my($begin, $end) = ( '', '' );
my $agentnum = '';
my($open, $days) = ( '', '' );
push @where, "cust_bill._date < ". (time-86400*$days) if $days;
}
- #here is the agent virtualization
- push @where, $agentnums_sql;
- my $extra_sql = scalar(@where) ? 'WHERE '. join(' AND ', @where) : '';
-
if ( $cgi->param('newest_percust') ) {
$newest_percust = 1;
- $distinct = 'DISTINCT ON ( cust_bill.custnum )';
- $orderby = 'ORDER BY cust_bill.custnum ASC, cust_bill._date DESC';
+
+ #$distinct = 'DISTINCT ON ( cust_bill.custnum )';
+ #$orderby = 'ORDER BY cust_bill.custnum ASC, cust_bill._date DESC';
#$count_query = "SELECT 'N/A', 'N/A', 'N/A'"; #XXXXXXX fix
+
+ my @newest_where = map { my $x = $_;
+ $x =~ s/\bcust_bill\./newest_cust_bill./g;
+ $x;
+ }
+ grep ! /^cust_main./, @where;
+ my $newest_where = scalar(@newest_where)
+ ? ' AND '. join(' AND ', @newest_where)
+ : '';
+
+ push @where, "cust_bill._date = (
+ SELECT(MAX(newest_cust_bill._date)) FROM cust_bill AS newest_cust_bill
+ WHERE newest_cust_bill.custnum = cust_bill.custnum
+ $newest_where
+ )";
+
+
$count_query = "SELECT COUNT(DISTINCT cust_bill.custnum), 'N/A', 'N/A'";
}
+ #here is the agent virtualization
+ push @where, $agentnums_sql;
+
+ my $extra_sql = scalar(@where) ? 'WHERE '. join(' AND ', @where) : '';
+
unless ( $count_query ) {
$count_query = "SELECT COUNT(*), sum(charged), sum($owed)";
$count_addl = [ '$%.2f total invoiced',
'table' => 'cust_bill',
'addl_from' => $join_cust_main,
'hashref' => {},
- 'select' => "$distinct ". join(', ',
+ #'select' => "$distinct ". join(', ',
+ 'select' => join(', ',
'cust_bill.*',
#( map "cust_main.$_", qw(custnum last first company) ),
'cust_main.custnum as cust_main_custnum',