push @where, "cust_bill._date < ". (time-86400*$param{'days'})
if $param{'days'};
- my $extra_sql = scalar(@where) ? 'WHERE '. join(' AND ', @where) : '';
+ if ( $param{'newest_percust'} ) {
- my $addl_from = 'left join cust_main using ( custnum )';
+ #$distinct = 'DISTINCT ON ( cust_bill.custnum )';
+ #$orderby = 'ORDER BY cust_bill.custnum ASC, cust_bill._date DESC';
+
+ my @newest_where = map { s/\bcust_bill\./newest_cust_bill./g; }
+ 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
+ )";
- if ( $param{'newest_percust'} ) {
- $distinct = 'DISTINCT ON ( cust_bill.custnum )';
- $orderby = 'ORDER BY cust_bill.custnum ASC, cust_bill._date DESC';
}
+
+ my $extra_sql = scalar(@where) ? 'WHERE '. join(' AND ', @where) : '';
+
+ my $addl_from = 'left join cust_main using ( custnum )';
my @cust_bill = qsearch( 'cust_bill',
{},
- "$distinct cust_bill.*",
+ #"$distinct cust_bill.*",
+ "cust_bill.*",
$extra_sql,
'',
$addl_from
=item sql_h_search END_TIMESTAMP [ START_TIMESTAMP ]
-Returns an a list consisting of the "SELECT" and "EXTRA_SQL" SQL fragments to
-search for the appropriate history records created before END_TIMESTAMP
-and (optionally) not cancelled before START_TIMESTAMP.
+Returns an a list consisting of the "SELECT", "EXTRA_SQL", SQL fragments, a
+placeholder for "CACHE_OBJ" and an "AS" SQL fragment, to search for the
+appropriate history records created before END_TIMESTAMP and (optionally) not
+cancelled before START_TIMESTAMP.
=cut
confess 'Called sql_h_search without END_TIMESTAMP';
}
- my $notcancelled = '';
+ my( $notcancelled, $notcancelled_mr ) = ( '', '' );
if ( scalar(@_) && $_[0] ) {
- $notcancelled = "AND 0 = ( SELECT COUNT(*) FROM $table as notdel
- WHERE notdel.$pkey = maintable.$pkey
- AND notdel.history_action = 'delete'
- AND notdel.history_date > maintable.history_date
- AND notdel.history_date <= $_[0]
- )";
+ $notcancelled =
+ "AND 0 = ( SELECT COUNT(*) FROM $table as notdel
+ WHERE notdel.$pkey = maintable.$pkey
+ AND notdel.history_action = 'delete'
+ AND notdel.history_date > maintable.history_date
+ AND notdel.history_date <= $_[0]
+ )";
+ $notcancelled_mr =
+ "AND 0 = ( SELECT COUNT(*) FROM $table as notdel_mr
+ WHERE notdel_mr.$pkey = mostrecent.$pkey
+ AND notdel_mr.history_action = 'delete'
+ AND notdel_mr.history_date > mostrecent.history_date
+ AND notdel_mr.history_date <= $_[0]
+ )";
}
(
- "DISTINCT ON ( $pkey ) *",
+ #"DISTINCT ON ( $pkey ) *",
+ "*",
"AND history_date <= $end
AND ( history_action = 'insert'
OR history_action = 'replace_new'
)
$notcancelled
- ORDER BY $pkey ASC, history_date DESC",
+ AND history_date = ( SELECT MAX(mostrecent.history_date)
+ FROM $table AS mostrecent
+ WHERE mostrecent.$pkey = maintable.$pkey
+ AND mostrecent.history_date <= $end
+ AND ( mostrecent.history_action = 'insert'
+ OR mostrecent.history_action = 'replace_new'
+ )
+ $notcancelled_mr
+ )
+
+ ORDER BY $pkey ASC",
+ #ORDER BY $pkey ASC, history_date DESC",
'',
<TH><FONT SIZE=-1>Granularity</FONT></TH>
</TR>
% foreach my $rate_region (
-% sort { lc($a->regionname) cmp lc($b->regionname) }
+% #sort { lc($a->regionname) cmp lc($b->regionname) }
% qsearch({
-% 'select' => 'DISTINCT ON ( regionnum ) rate_region.*',
% 'table' => 'rate_region',
% 'hashref' => {},
+% 'order_by' => 'ORDER BY lc(regionname)',
+%
+% #'select' => 'DISTINCT ON ( regionnum ) rate_region.*',
+% #...
% #'addl_from' => 'INNER JOIN rate_prefix USING ( regionnum )',
% #'extra_sql' => "WHERE countrycode != '1'",
%
% # 'ORDER BY regionname'
% # ERROR: SELECT DISTINCT ON expressions must
% # match initial ORDER BY expressions
+% # also, DISTINCT ON is a Pg-ism
% })
% ) {
% my $n = $rate_region->regionnum;
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 { s/\bcust_bill\./newest_cust_bill./g; }
+ 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',
foreach my $r (
qsearch( 'cust_main_county',
{},
- "DISTINCT ON ( country, state, county, CASE WHEN taxname IS NULL THEN '' ELSE taxname END ) *",
+ "DISTINCT
+ country,
+ state,
+ county,
+ CASE WHEN taxname IS NULL THEN '' ELSE taxname END AS taxname,".
+
+ #a little bit unsure of this part... test?
+ #ah, it looks like it winds up being irrelevant as ->{'tax'}
+ # from $regions is not displayed when show_taxclasses is on
+ ( $cgi->param('show_taxclasses')
+ ? " CASE WHEN taxclass IS NULL THEN '' ELSE taxclass END "
+ : " '' "
+ )." AS taxclass"
+ ,
$gotcust
)
) {
projects / freeside.git / commitdiff