<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.'
$now =~ /^(\d+)$/ or die "unparsable date?";
$now = $1;
-my $link = "cust_bill_pkg.cgi?nottax=1;unearned_now=$now;mode=$mode";
+my $dt = DateTime->from_epoch(epoch => $now, time_zone => 'local');
+$dt->truncate(to => 'day'); # local midnight on the report day
+$dt->add(days => 1); # the day after that
+$dt->subtract(seconds => 1); # the last second of the report day
+$now = $dt->epoch;
+
+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 );
- my $float = 'REAL'; #'DOUBLE PRECISION';
-
- my $period = "CAST(cust_bill_pkg.edate - cust_bill_pkg.sdate AS $float)";
- my $elapsed = "(CASE WHEN cust_bill_pkg.sdate > $now
- THEN 0
- ELSE ($now - cust_bill_pkg.sdate)
- END)";
- #my $elapsed = "CAST($unearned - cust_bill_pkg.sdate AS $float)";
-
- my $remaining = "(1 - $elapsed/$period)";
-
- my $base;
- my $from = '';
+ my @opt = ($now, '', setuprecur => 'recur', no_usage => 1);
+ # balance owed, recurring only, not including usage charges
+ my $unearned_base;
if ( $mode eq 'billed' ) {
- $base = 'cust_bill_pkg.recur';
- $from = 'cust_bill_pkg';
+ $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);
}
- elsif ( $mode eq 'paid' ) {
- $base = 'cust_bill_pay_pkg.amount';
- $from = 'cust_bill_pay_pkg
- LEFT JOIN cust_bill_pkg USING ( billpkgnum )
- LEFT JOIN cust_bill_pay USING ( billpaynum )
- LEFT JOIN cust_pay USING ( paynum )';
- $where .= ' AND ' if $where;
- $where .= "cust_bill_pay_pkg.setuprecur = 'recur'
- AND cust_pay._date <= $now";
- }
- else {
- die "invalid mode '$mode'";
- }
- my $select = "SUM($remaining * $base)";
+
+ 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 * $fraction, 0 )
+ AS DECIMAL(10,2)
+ )";
+
+ my $select = "SUM( $unearned_sql )";
if ( !$cgi->param('include_monthly') ) {
# all except freq != 0; one-time charges should never be included
AND part_pkg.freq NOT LIKE '%w'";
}
+ # $mode actually doesn't matter here, since unpaid invoices have zero
+ # unearned revenue
+
my $sql =
- "SELECT $select FROM $from
+ "SELECT $select FROM cust_bill_pkg
LEFT JOIN cust_pkg ON (cust_bill_pkg.pkgnum = cust_pkg.pkgnum)
LEFT JOIN part_pkg USING ( pkgpart )
LEFT JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)