diff options
Diffstat (limited to 'httemplate/search')
| -rwxr-xr-x | httemplate/search/cust_bill.html | 33 | ||||
| -rwxr-xr-x | httemplate/search/report_tax.cgi | 15 |
2 files changed, 39 insertions, 9 deletions
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 ) ) { |