- my $owed_sql = FS::cust_bill_pkg->owed_sql($unearned, '',
- setuprecur => 'recur', no_usage => 1);
- push @select, "($owed_sql) AS owed"; # also this
+ my $paid_sql = 'GREATEST(' .
+ FS::cust_bill_pkg->paid_sql($unearned, '', setuprecur => 'recur') .
+ " - $usage_sql, 0)";
+
+ push @select, "$paid_sql AS paid_no_usage"; # need this either way
+
+ if ( $unearned_mode eq 'paid' ) {
+ # then use the amount paid, minus usage charges
+ $unearned_base = $paid_sql;
+ }
+ else {
+ # use the amount billed, minus usage charges and credits
+ $unearned_base = "GREATEST( cust_bill_pkg.recur - ".
+ FS::cust_bill_pkg->credited_sql($unearned, '', setuprecur => 'recur') .
+ " - $usage_sql, 0)";
+ # include only rows that have some non-usage, non-credited portion
+ }
+ # whatever we're using as the base, only show rows where it's positive
+ push @where, "$unearned_base > 0";