diff options
Diffstat (limited to 'httemplate')
-rw-r--r-- | httemplate/edit/rate.cgi | 8 | ||||
-rwxr-xr-x | httemplate/search/cust_bill.html | 33 | ||||
-rwxr-xr-x | httemplate/search/report_tax.cgi | 15 |
3 files changed, 45 insertions, 11 deletions
diff --git a/httemplate/edit/rate.cgi b/httemplate/edit/rate.cgi index 72a04c339..55fe319cf 100644 --- a/httemplate/edit/rate.cgi +++ b/httemplate/edit/rate.cgi @@ -58,17 +58,21 @@ Rate plan <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; diff --git a/httemplate/search/cust_bill.html b/httemplate/search/cust_bill.html index aa4b2f108..be7406ec6 100755 --- a/httemplate/search/cust_bill.html +++ b/httemplate/search/cust_bill.html @@ -59,7 +59,7 @@ my $agentnums_sql = $FS::CurrentUser::CurrentUser->agentnums_sql; my( $count_query, $sql_query ); my $count_addl = ''; -my $distinct = ''; +#my $distinct = ''; my($begin, $end) = ( '', '' ); my $agentnum = ''; my($open, $days) = ( '', '' ); @@ -141,18 +141,34 @@ if ( $cgi->param('invnum') =~ /^\s*(FS-)?(\d+)\s*$/ ) { 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', @@ -165,7 +181,8 @@ if ( $cgi->param('invnum') =~ /^\s*(FS-)?(\d+)\s*$/ ) { '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', diff --git a/httemplate/search/report_tax.cgi b/httemplate/search/report_tax.cgi index bb9c8f433..eddb3da00 100755 --- a/httemplate/search/report_tax.cgi +++ b/httemplate/search/report_tax.cgi @@ -444,7 +444,20 @@ my %base_regions = (); 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 ) ) { |