=over 4
-=item owed_sql [ BEFORE, AFTER, OPTIONS ]
+=item usage_sql
-Returns an SQL expression for the amount owed. BEFORE and AFTER specify
-a date window. OPTIONS may include 'no_usage' (excludes usage charges)
-and 'setuprecur' (set to "setup" or "recur" to limit to one or the other).
+Returns an SQL expression for the total usage charges in details on
+an item.
=cut
-sub owed_sql {
+my $usage_sql =
+ '(SELECT COALESCE(SUM(cust_bill_pkg_detail.amount),0)
+ FROM cust_bill_pkg_detail
+ WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum)';
+
+sub usage_sql { $usage_sql }
+
+# this makes owed_sql, etc. much more concise
+sub charged_sql {
my ($class, $start, $end, %opt) = @_;
my $charged =
$opt{setuprecur} =~ /^s/ ? 'cust_bill_pkg.setup' :
$opt{setuprecur} =~ /^r/ ? 'cust_bill_pkg.recur' :
'cust_bill_pkg.setup + cust_bill_pkg.recur';
- if ( $opt{no_usage} ) {
- $charged .= ' - ' . $class->usage_sql;
+ if ($opt{no_usage} and $charged =~ /recur/) {
+ $charged = "$charged - $usage_sql"
}
- '(' . $charged .
- ' - ' . $class->paid_sql($start, $end, %opt) .
- ' - ' . $class->credited_sql($start, $end, %opt) . ')'
+ $charged;
}
-=item usage_sql
-Returns an SQL expression for the total usage charges in details on
-an item.
+=item owed_sql [ BEFORE, AFTER, OPTIONS ]
+
+Returns an SQL expression for the amount owed. BEFORE and AFTER specify
+a date window. OPTIONS may include 'no_usage' (excludes usage charges)
+and 'setuprecur' (set to "setup" or "recur" to limit to one or the other).
=cut
-sub usage_sql {
+sub owed_sql {
my $class = shift;
- "(SELECT COALESCE(SUM(cust_bill_pkg_detail.amount),0)
- FROM cust_bill_pkg_detail
- WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum)"
+ '(' . $class->charged_sql(@_) .
+ ' - ' . $class->paid_sql(@_) .
+ ' - ' . $class->credited_sql(@_) . ')'
}
=item paid_sql [ BEFORE, AFTER, OPTIONS ]
sub paid_sql {
my ($class, $start, $end, %opt) = @_;
- $start = $start ? "AND cust_bill_pay._date <= $start" : '';
- $end = $end ? "AND cust_bill_pay._date > $end" : '';
+ my $s = $start ? "AND cust_bill_pay._date <= $start" : '';
+ my $e = $end ? "AND cust_bill_pay._date > $end" : '';
my $setuprecur =
$opt{setuprecur} =~ /^s/ ? 'setup' :
$opt{setuprecur} =~ /^r/ ? 'recur' :
'';
$setuprecur &&= "AND setuprecur = '$setuprecur'";
- "( SELECT COALESCE(SUM(cust_bill_pay_pkg.amount),0)
+
+ my $paid = "( SELECT COALESCE(SUM(cust_bill_pay_pkg.amount),0)
FROM cust_bill_pay_pkg JOIN cust_bill_pay USING (billpaynum)
WHERE cust_bill_pay_pkg.billpkgnum = cust_bill_pkg.billpkgnum
- $start $end $setuprecur )";
+ $s $e$setuprecur )";
+
+ if ( $opt{no_usage} ) {
+ # cap the amount paid at the sum of non-usage charges,
+ # minus the amount credited against non-usage charges
+ "LEAST($paid, ".
+ $class->charged_sql($start, $end, %opt) . ' - ' .
+ $class->credited_sql($start, $end, %opt).')';
+ }
+ else {
+ $paid;
+ }
+
}
sub credited_sql {
my ($class, $start, $end, %opt) = @_;
- $start = $start ? "AND cust_credit_bill._date <= $start" : '';
- $end = $end ? "AND cust_credit_bill._date > $end" : '';
+ my $s = $start ? "AND cust_credit_bill._date <= $start" : '';
+ my $e = $end ? "AND cust_credit_bill._date > $end" : '';
my $setuprecur =
$opt{setuprecur} =~ /^s/ ? 'setup' :
$opt{setuprecur} =~ /^r/ ? 'recur' :
'';
$setuprecur &&= "AND setuprecur = '$setuprecur'";
- "( SELECT COALESCE(SUM(cust_credit_bill_pkg.amount),0)
+
+ my $credited = "( SELECT COALESCE(SUM(cust_credit_bill_pkg.amount),0)
FROM cust_credit_bill_pkg JOIN cust_credit_bill USING (creditbillnum)
WHERE cust_credit_bill_pkg.billpkgnum = cust_bill_pkg.billpkgnum
- $start $end $setuprecur )";
+ $s $e $setuprecur )";
+
+ if ( $opt{no_usage} ) {
+ # cap the amount credited at the sum of non-usage charges
+ "LEAST($credited, ". $class->charged_sql($start, $end, %opt).')';
+ }
+ else {
+ $credited;
+ }
+
}
=back
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
+ my $paid_sql = 'GREATEST(' .
+ FS::cust_bill_pkg->paid_sql($unearned, '', setuprecur => 'recur') .
+ " - $usage_sql, 0)";
+
+ push @select, "$paid_sql AS paid_no_usage"; # need this either way
+
+ if ( $unearned_mode eq 'paid' ) {
+ # then use the amount paid, minus usage charges
+ $unearned_base = $paid_sql;
+ }
+ else {
+ # use the amount billed, minus usage charges and credits
+ $unearned_base = "GREATEST( cust_bill_pkg.recur - ".
+ FS::cust_bill_pkg->credited_sql($unearned, '', setuprecur => 'recur') .
+ " - $usage_sql, 0)";
+ # include only rows that have some non-usage, non-credited portion
+ }
+ # whatever we're using as the base, only show rows where it's positive
+ push @where, "$unearned_base > 0";
- $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)
- )";
+ $unearned_sql = "CAST( $unearned_base * $remaining 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";
+ # last payment/credit date
+ my %t = (pay => 'cust_bill_pay', credit => 'cust_credit_bill');
+ foreach my $x (qw(pay credit)) {
+ my $table = $t{$x};
+ my $link = $table.'_pkg';
+ my $pkey = dbdef->table($table)->primary_key;
+ my $last_date_sql = "SELECT MAX(_date)
+ FROM $table JOIN $link USING ($pkey)
+ WHERE $link.billpkgnum = cust_bill_pkg.billpkgnum
+ AND $table._date <= $unearned";
+ push @select, "($last_date_sql) AS last_$x";
}
}
$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';
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 @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 $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 $unearned_sql = "CAST(
- GREATEST( $unearned_base * $remaining - $owed_sql, 0 )
+ GREATEST( $unearned_base * $remaining, 0 )
AS DECIMAL(10,2)
)";
AND part_pkg.freq != '0'
$where
";
+
+ warn $sql;
my $sth = dbh->prepare($sql) or die dbh->errstr;
$sth->execute or die $sth->errstr;
my $total = $sth->fetchrow_arrayref->[0];
projects / freeside.git / commitdiff