From: mark <mark>
Date: Thu, 20 May 2010 01:33:08 +0000 (+0000)
Subject: RT#7266: aging report "as of" date now limits applied payments
X-Git-Tag: root_of_svc_elec_features~263
X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=08b36523ebbf6e2995878f26bfac988f32f7a218

RT#7266: aging report "as of" date now limits applied payments
---

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
               )
   ";
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 . ')' );
 
 }