diff options
author | Mark Wells <mark@freeside.biz> | 2012-05-12 22:31:03 -0700 |
---|---|---|
committer | Mark Wells <mark@freeside.biz> | 2012-05-12 22:31:03 -0700 |
commit | 4cc0b69a94138d85b1ff8b67966127af3c72288d (patch) | |
tree | 0c1eaf50f431754a1b98d733ec0fe769c4558163 /FS/FS/cust_bill_pkg.pm | |
parent | 053116d325050963ada4455c83b7bd1c154ce59f (diff) |
change calculation method for prepaid income report, #13289
Diffstat (limited to 'FS/FS/cust_bill_pkg.pm')
-rw-r--r-- | FS/FS/cust_bill_pkg.pm | 80 |
1 files changed, 80 insertions, 0 deletions
diff --git a/FS/FS/cust_bill_pkg.pm b/FS/FS/cust_bill_pkg.pm index 1ee5c09..f196a0a 100644 --- a/FS/FS/cust_bill_pkg.pm +++ b/FS/FS/cust_bill_pkg.pm @@ -1067,6 +1067,86 @@ sub _X_show_zero { =back +=head1 CLASS METHODS + +=over 4 + +=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 owed_sql { + my ($class, $start, $end, %opt) = @_; + my $charged = + $opt{setuprecur} =~ /^s/ ? 'setup' : + $opt{setuprecur} =~ /^r/ ? 'recur' : + 'setup + recur'; + + if ( $opt{no_usage} ) { + $charged .= ' - ' . $class->usage_sql; + } + + '(' . $charged . + ' - ' . $class->paid_sql($start, $end, %opt) . + ' - ' . $class->credited_sql($start, $end, %opt) . ')' +} + +=item usage_sql + +Returns an SQL expression for the total usage charges in details on +an item. + +=cut + +sub usage_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)" +} + +=item paid_sql [ BEFORE, AFTER, OPTIONS ] + +Returns an SQL expression for the sum of payments applied to this item. + +=cut + +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 $setuprecur = + $opt{setuprecur} =~ /^s/ ? 'setup' : + $opt{setuprecur} =~ /^r/ ? 'recur' : + ''; + $setuprecur &&= "AND setuprecur = '$setuprecur'"; + "( 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 )"; +} + +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 $setuprecur = + $opt{setuprecur} =~ /^s/ ? 'setup' : + $opt{setuprecur} =~ /^r/ ? 'recur' : + ''; + $setuprecur &&= "AND setuprecur = '$setuprecur'"; + "( 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 )"; +} + +=back + =head1 BUGS setup and recur shouldn't be separate fields. There should be one "amount" |