From 81ffc8da62da1cc5a01ad92c7c4489e37b35f088 Mon Sep 17 00:00:00 2001 From: mark Date: Thu, 20 May 2010 01:34:20 +0000 Subject: [PATCH] RT#7266: aging report "as of" date now limits applied payments --- FS/FS/cust_bill.pm | 20 ++++++++++++++------ FS/FS/cust_credit.pm | 13 ++++++++++--- FS/FS/cust_main.pm | 16 ++++++++++++---- FS/FS/cust_pay.pm | 12 +++++++++--- FS/FS/cust_refund.pm | 12 +++++++++--- httemplate/search/report_receivables.cgi | 5 +++-- 6 files changed, 57 insertions(+), 21 deletions(-) diff --git a/FS/FS/cust_bill.pm b/FS/FS/cust_bill.pm index 9ba2e6eb1..2c678954f 100644 --- a/FS/FS/cust_bill.pm +++ b/FS/FS/cust_bill.pm @@ -4321,7 +4321,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 @@ -4332,7 +4335,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 @@ -4342,9 +4346,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 @@ -4354,9 +4360,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 d0aa3a4b4..a4e335e3c 100644 --- a/FS/FS/cust_credit.pm +++ b/FS/FS/cust_credit.pm @@ -557,17 +557,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 88aceb935..267c0d2f0 100644 --- a/FS/FS/cust_main.pm +++ b/FS/FS/cust_main.pm @@ -7392,6 +7392,12 @@ sub credit { } =item charge HASHREF || AMOUNT [ PKG [ COMMENT [ TAXCLASS ] ] ] +=item cutoff + +An absolute cutoff time. Payments, credits, and refunds I after this +time will be ignored. Note that START_TIME and END_TIME only limit the date +range for invoices and I payments, credits, and refunds. + Creates a one-time charge for this customer. If there is an error, returns the error, otherwise returns false. @@ -8257,10 +8263,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 69bcd8717..f9185cc4b 100644 --- a/FS/FS/cust_pay.pm +++ b/FS/FS/cust_pay.pm @@ -657,17 +657,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 abc131e44..894d19086 100644 --- a/FS/FS/cust_refund.pm +++ b/FS/FS/cust_refund.pm @@ -321,17 +321,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 ) "; diff --git a/httemplate/search/report_receivables.cgi b/httemplate/search/report_receivables.cgi index 73ebee49d..3696ed40d 100755 --- a/httemplate/search/report_receivables.cgi +++ b/httemplate/search/report_receivables.cgi @@ -16,7 +16,7 @@ die "access denied" #Example: # # my $balance = balance( -# $start, $end, +# $start, $end, $offset, # 'no_as' => 1, #set to true when using in a WHERE clause (supress AS clause) # #or 0 / omit when using in a SELECT clause as a column # # ("AS balance_$start_$end") @@ -44,7 +44,8 @@ sub balance { #$opt{'unapplied_date'} = 1; - FS::cust_main->balance_date_sql( $start, $end, 'unapplied_date'=>1,); + FS::cust_main->balance_date_sql( $start, $end, 'unapplied_date'=>1, + 'cutoff' => "( $str2time now() $closing - ".$offset * 86400 . ')' ); } -- 2.11.0