diff options
| -rw-r--r-- | FS/FS/cust_bill.pm | 27 | ||||
| -rw-r--r-- | FS/FS/h_Common.pm | 45 | ||||
| -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 |
5 files changed, 99 insertions, 29 deletions
diff --git a/FS/FS/cust_bill.pm b/FS/FS/cust_bill.pm index 0512d3bf4..bd1dbe02c 100644 --- a/FS/FS/cust_bill.pm +++ b/FS/FS/cust_bill.pm @@ -2623,18 +2623,33 @@ sub re_X { 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 diff --git a/FS/FS/h_Common.pm b/FS/FS/h_Common.pm index d55da8cfb..d9846eec4 100644 --- a/FS/FS/h_Common.pm +++ b/FS/FS/h_Common.pm @@ -30,9 +30,10 @@ inherit 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 @@ -48,25 +49,45 @@ sub sql_h_search { 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", '', 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 1a5a3c6d0..7e76cf61f 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 e97480540..7185b6dd5 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 ) ) { |