$now =~ /^(\d+)$/ or die "unparsable date?";
$now = $1;
+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 = "cust_bill_pkg.cgi?nottax=1;unearned_now=$now;mode=$mode";
if ( $cgi->param('include_monthly') ) {
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)";
-
+ # 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 $base;
- my $from = '';
- if ( $mode eq 'billed' ) {
- $base = 'cust_bill_pkg.recur';
- $from = 'cust_bill_pkg';
- }
- 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 $unearned_sql = "CAST(
+ GREATEST( $unearned_base * $remaining - $owed_sql, 0 )
+ AS DECIMAL(10,2)
+ )";
+
+ my $float = 'REAL'; #'DOUBLE PRECISION';
+
+ 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)
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];