diff options
Diffstat (limited to 'FS')
| -rw-r--r-- | FS/FS/cust_bill.pm | 27 | ||||
| -rw-r--r-- | FS/FS/h_Common.pm | 45 |
2 files changed, 54 insertions, 18 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", '', |