diff options
author | mark <mark> | 2010-05-20 01:33:08 +0000 |
---|---|---|
committer | mark <mark> | 2010-05-20 01:33:08 +0000 |
commit | 08b36523ebbf6e2995878f26bfac988f32f7a218 (patch) | |
tree | a9352c8bed56d918a89ebff37a33fb0a6acb8431 /FS | |
parent | 07c8992ccbb2d179939af013e9c90774a093f820 (diff) |
RT#7266: aging report "as of" date now limits applied payments
Diffstat (limited to 'FS')
-rw-r--r-- | FS/FS/cust_bill.pm | 20 | ||||
-rw-r--r-- | FS/FS/cust_credit.pm | 13 | ||||
-rw-r--r-- | FS/FS/cust_main.pm | 16 | ||||
-rw-r--r-- | FS/FS/cust_pay.pm | 12 | ||||
-rw-r--r-- | FS/FS/cust_refund.pm | 12 |
5 files changed, 54 insertions, 19 deletions
diff --git a/FS/FS/cust_bill.pm b/FS/FS/cust_bill.pm index 8cc84e5f0..f10a5d0c4 100644 --- a/FS/FS/cust_bill.pm +++ b/FS/FS/cust_bill.pm @@ -4342,7 +4342,10 @@ Returns an SQL fragment to retreive the amount owed (charged minus credited and sub owed_sql { my $class = shift; - 'charged - '. $class->paid_sql. ' - '. $class->credited_sql; + my ($start, $end) = @_; + 'charged - '. + $class->paid_sql($start, $end). ' - '. + $class->credited_sql($start, $end); } =item net_sql @@ -4353,7 +4356,8 @@ Returns an SQL fragment to retreive the net amount (charged minus credited). sub net_sql { my $class = shift; - 'charged - '. $class->credited_sql; + my ($start, $end) = @_; + 'charged - '. $class->credited_sql($start, $end); } =item paid_sql @@ -4363,9 +4367,11 @@ Returns an SQL fragment to retreive the amount paid against this invoice. =cut sub paid_sql { - #my $class = shift; + my ($class, $start, $end) = @_; + $start &&= "AND cust_bill_pay._date <= $start"; + $end &&= "AND cust_bill_pay._date > $end"; "( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay - WHERE cust_bill.invnum = cust_bill_pay.invnum )"; + WHERE cust_bill.invnum = cust_bill_pay.invnum $start $end )"; } =item credited_sql @@ -4375,9 +4381,11 @@ Returns an SQL fragment to retreive the amount credited against this invoice. =cut sub credited_sql { - #my $class = shift; + my ($class, $start, $end) = shift; + $start &&= "AND cust_credit_bill._date <= $start"; + $end &&= "AND cust_credit_bill._date > $end"; "( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill - WHERE cust_bill.invnum = cust_credit_bill.invnum )"; + WHERE cust_bill.invnum = cust_credit_bill.invnum $start $end )"; } =item search_sql_where HASHREF diff --git a/FS/FS/cust_credit.pm b/FS/FS/cust_credit.pm index b87b0dbc3..674bc1047 100644 --- a/FS/FS/cust_credit.pm +++ b/FS/FS/cust_credit.pm @@ -565,17 +565,24 @@ Returns an SQL fragment to retreive the unapplied amount. =cut sub unapplied_sql { - #my $class = shift; + my ($class, $start, $end) = @_; + + my $bill_start = $start ? "AND cust_credit_bill._date <= $start" : ''; + my $bill_end = $end ? "AND cust_credit_bill._date > $end" : ''; + my $refund_start = $start ? "AND cust_credit_refund._date <= $start" : ''; + my $refund_end = $end ? "AND cust_credit_refund._date > $end" : ''; "amount - COALESCE( ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_credit.crednum = cust_credit_refund.crednum ) + WHERE cust_credit.crednum = cust_credit_refund.crednum + $refund_start $refund_end ) ,0 ) - COALESCE( ( SELECT SUM(amount) FROM cust_credit_bill - WHERE cust_credit.crednum = cust_credit_bill.crednum ) + WHERE cust_credit.crednum = cust_credit_bill.crednum + $bill_start $bill_end ) ,0 ) "; diff --git a/FS/FS/cust_main.pm b/FS/FS/cust_main.pm index 684031c1d..c1a8aafde 100644 --- a/FS/FS/cust_main.pm +++ b/FS/FS/cust_main.pm @@ -7392,6 +7392,12 @@ WHERE clause hashref (elements "AND"ed together) (typically used with the total (unused. obsolete?) JOIN clause (typically used with the total option) +=item cutoff + +An absolute cutoff time. Payments, credits, and refunds I<applied> after this +time will be ignored. Note that START_TIME and END_TIME only limit the date +range for invoices and I<unapplied> payments, credits, and refunds. + =back =cut @@ -7399,10 +7405,12 @@ JOIN clause (typically used with the total option) sub balance_date_sql { my( $class, $start, $end, %opt ) = @_; - my $owed = FS::cust_bill->owed_sql; - my $unapp_refund = FS::cust_refund->unapplied_sql; - my $unapp_credit = FS::cust_credit->unapplied_sql; - my $unapp_pay = FS::cust_pay->unapplied_sql; + my $cutoff = $opt{'cutoff'}; + + my $owed = FS::cust_bill->owed_sql($cutoff); + my $unapp_refund = FS::cust_refund->unapplied_sql($cutoff); + my $unapp_credit = FS::cust_credit->unapplied_sql($cutoff); + my $unapp_pay = FS::cust_pay->unapplied_sql($cutoff); my $j = $opt{'join'} || ''; diff --git a/FS/FS/cust_pay.pm b/FS/FS/cust_pay.pm index 175dbe890..ff8226c8d 100644 --- a/FS/FS/cust_pay.pm +++ b/FS/FS/cust_pay.pm @@ -659,17 +659,23 @@ Returns an SQL fragment to retreive the unapplied amount. =cut sub unapplied_sql { - #my $class = shift; + my ($class, $start, $end) = shift; + my $bill_start = $start ? "AND cust_bill_pay._date <= $start" : ''; + my $bill_end = $end ? "AND cust_bill_pay._date > $end" : ''; + my $refund_start = $start ? "AND cust_pay_refund._date <= $start" : ''; + my $refund_end = $end ? "AND cust_pay_refund._date > $end" : ''; "paid - COALESCE( ( SELECT SUM(amount) FROM cust_bill_pay - WHERE cust_pay.paynum = cust_bill_pay.paynum ) + WHERE cust_pay.paynum = cust_bill_pay.paynum + $bill_start $bill_end ) ,0 ) - COALESCE( ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_pay.paynum = cust_pay_refund.paynum ) + WHERE cust_pay.paynum = cust_pay_refund.paynum + $refund_start $refund_end ) ,0 ) "; diff --git a/FS/FS/cust_refund.pm b/FS/FS/cust_refund.pm index e7c5a8215..6d08c8621 100644 --- a/FS/FS/cust_refund.pm +++ b/FS/FS/cust_refund.pm @@ -343,17 +343,23 @@ Returns an SQL fragment to retreive the unapplied amount. =cut sub unapplied_sql { - #my $class = shift; + my ($class, $start, $end) = shift; + my $credit_start = $start ? "AND cust_credit_refund._date <= $start" : ''; + my $credit_end = $end ? "AND cust_credit_refund._date > $end" : ''; + my $pay_start = $start ? "AND cust_pay_refund._date <= $start" : ''; + my $pay_end = $end ? "AND cust_pay_refund._date > $end" : ''; "refund - COALESCE( ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) + WHERE cust_refund.refundnum = cust_credit_refund.refundnum + $credit_start $credit_end ) ,0 ) - COALESCE( ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) + WHERE cust_refund.refundnum = cust_pay_refund.refundnum + $pay_start $pay_end ) ,0 ) "; |