-# unearned revenue mode
-if ( $cgi->param('unearned_now') =~ /^(\d+)$/ ) {
-
- $unearned = $1;
- $unearned_mode = $cgi->param('mode');
-
- push @where, "cust_bill_pkg.sdate < $unearned",
- "cust_bill_pkg.edate > $unearned",
- "cust_bill_pkg.recur != 0",
- "part_pkg.freq != '0'";
-
- if ( !$cgi->param('include_monthly') ) {
- push @where,
- "part_pkg.freq != '1'",
- "part_pkg.freq NOT LIKE '%h'",
- "part_pkg.freq NOT LIKE '%d'",
- "part_pkg.freq NOT LIKE '%w'";
- }
-
- my $usage_sql = FS::cust_bill_pkg->usage_sql;
- push @select, "($usage_sql) AS usage"; # we need 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";
-
- my $period = "CAST(cust_bill_pkg.edate - cust_bill_pkg.sdate AS REAL)";
- my $elapsed = "GREATEST( $unearned - cust_bill_pkg.sdate, 0 )";
- my $remaining = "(1 - $elapsed/$period)";
-
- $unearned_sql = "CAST( $unearned_base * $remaining AS DECIMAL(10,2) )";
- push @select, "$unearned_sql AS unearned_revenue";
-
- # last payment/credit date
- my %t = (pay => 'cust_bill_pay', credit => 'cust_credit_bill');
- foreach my $x (qw(pay credit)) {
- my $table = $t{$x};
- my $link = $table.'_pkg';
- my $pkey = dbdef->table($table)->primary_key;
- my $last_date_sql = "SELECT MAX(_date)
- FROM $table JOIN $link USING ($pkey)
- WHERE $link.billpkgnum = cust_bill_pkg.billpkgnum
- AND $table._date <= $unearned";
- push @select, "($last_date_sql) AS last_$x";
- }
-
-}
-