+ $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 $owed_sql = FS::cust_bill_pkg->owed_sql($unearned, '',
+ setuprecur => 'recur', no_usage => 1);
+ push @select, "($owed_sql) AS owed"; # also this
+
+ $unearned_base = "(cust_bill_pkg.recur - $usage_sql)";
+ 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(
+ GREATEST( ( $unearned_base * $remaining ) - $owed_sql, 0 )
+ AS DECIMAL(10,2)
+ )";
+ push @select, "$unearned_sql AS unearned_revenue";
+
+ if ( $unearned_mode eq 'billed' ) {
+ # include only rows that have some unearned portion
+ push @where, "$unearned_base > 0";
+ }
+ elsif ( $unearned_mode eq 'paid' ) {
+ # include only those that have some _paid_ unearned portion
+ push @where, "$unearned_sql > 0";
+ }