- 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 )";