<BR>
<% $actual_label %><% $actual_label ? 'u' : 'U' %>nearned revenue
-is the amount of unearned revenue
+is the as-yet-unearned portion of revenue
<% $actual_label ? 'Freeside has actually' : '' %>
invoiced for packages with
<% $cgi->param('include_monthly') ? 'terms extending into the future.'
$dt->subtract(seconds => 1); # the last second of the report day
$now = $dt->epoch;
-my $link = "cust_bill_pkg.cgi?nottax=1;unearned_now=$now;mode=$mode";
+my $link = "unearned_detail.html?date=$now;mode=$mode";
if ( $cgi->param('include_monthly') ) {
$link .= ';include_monthly=1';
push @where, FS::cust_main->cust_status_sql . " = '$status'";
}
+# cust_classnum (false laziness w/ elements/cust_main_dayranges.html, elements/cust_pay_or_refund.html, cust_bill_pay.html, cust_bill_pkg.html, cust_bill_pkg_referral.html, unearned_detail.html, cust_credit.html, cust_credit_refund.html, cust_main::Search::search_sql)
+if ( grep { $_ eq 'cust_classnum' } $cgi->param ) {
+ my @classnums = grep /^\d*$/, $cgi->param('cust_classnum');
+ $link .= ";cust_classnum=$_" foreach @classnums;
+ push @where, 'COALESCE( cust_main.classnum, 0) IN ( '.
+ join(',', map { $_ || '0' } @classnums ).
+ ' )'
+ if @classnums;
+}
+
my %total = ();
my %total_legacy = ();
foreach my $agentnum (@agentnums) {
my( $total, $total_legacy ) = ( 0, 0 );
- # false laziness
- my $usage_sql = FS::cust_bill_pkg->usage_sql;
- my $owed_sql = FS::cust_bill_pkg->owed_sql($now, '',
- setuprecur => 'recur', no_usage => 1);
- my $unearned_base = "(cust_bill_pkg.recur - $usage_sql)";
- my $period = "CAST(cust_bill_pkg.edate - cust_bill_pkg.sdate AS REAL)";
- my $elapsed = "GREATEST( $now - cust_bill_pkg.sdate, 0 )";
- my $remaining = "(1 - $elapsed/$period)";
-
+ my @opt = ($now, '', setuprecur => 'recur', no_usage => 1);
+ # balance owed, recurring only, not including usage charges
+ my $unearned_base;
+ if ( $mode eq 'billed' ) {
+ $unearned_base = '( ' .
+ FS::cust_bill_pkg->charged_sql(@opt) . ' - ' .
+ FS::cust_bill_pkg->credited_sql(@opt) . ' )';
+ } elsif ( $mode eq 'paid' ) {
+ $unearned_base = FS::cust_bill_pkg->paid_sql(@opt);
+ }
+
+ my $edate_zero = midnight_sql('edate');
+ my $sdate_zero = midnight_sql('sdate');
+ my $period = "CAST( ($edate_zero - $sdate_zero) / 86400.0 AS DECIMAL(10,0) )";
+ my $remaining = "GREATEST(
+ CAST( ($edate_zero - $now) / 86400.0 AS DECIMAL(10,0) ),
+ 0)";
+ my $fraction = "$remaining / $period";
+
my $unearned_sql = "CAST(
- GREATEST( $unearned_base * $remaining - $owed_sql, 0 )
+ GREATEST( $unearned_base * $fraction, 0 )
AS DECIMAL(10,2)
)";
- my $float = 'REAL'; #'DOUBLE PRECISION';
-
my $select = "SUM( $unearned_sql )";
if ( !$cgi->param('include_monthly') ) {
AND part_pkg.freq != '0'
$where
";
+
my $sth = dbh->prepare($sql) or die dbh->errstr;
$sth->execute or die $sth->errstr;
my $total = $sth->fetchrow_arrayref->[0];