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 8cc84e5..f10a5d0 100644 (file)
--- 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;
 
 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
 }
 
 =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;
 
 sub net_sql {
   my $class = shift;

-  'charged - '. $class->credited_sql;
+  my ($start, $end) = @_;
+  'charged - '. $class->credited_sql($start, $end);

 }
 
 =item paid_sql
 }
 
 =item paid_sql


@@ -4363,9 +4367,11 @@ Returns an SQL fragment to retreive the amount paid against this invoice.
 =cut
 
 sub paid_sql {
 =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
   "( 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
 }
 
 =item credited_sql


@@ -4375,9 +4381,11 @@ Returns an SQL fragment to retreive the amount credited against this invoice.
 =cut
 
 sub credited_sql {
 =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
   "( 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
 }
 
 =item search_sql_where HASHREF

diff --git a/FS/FS/cust_credit.pm b/FS/FS/cust_credit.pm
index b87b0db..674bc10 100644 (file)
--- 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 {
 =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
 
   "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
                     ,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
                   )
   ";
                     ,0
                   )
   ";

diff --git a/FS/FS/cust_main.pm b/FS/FS/cust_main.pm
index 684031c..c1a8aaf 100644 (file)
--- 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)
 
 (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
 =back
 
 =cut


@@ -7399,10 +7405,12 @@ JOIN clause (typically used with the total option)
 sub balance_date_sql {
   my( $class, $start, $end, %opt ) = @_;
 
 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'} || '';
 
 
   my $j = $opt{'join'} || '';
 

diff --git a/FS/FS/cust_pay.pm b/FS/FS/cust_pay.pm
index 175dbe8..ff8226c 100644 (file)
--- 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 {
 =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
 
   "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
                     ,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
                   )
   ";
                     ,0
                   )
   ";

diff --git a/FS/FS/cust_refund.pm b/FS/FS/cust_refund.pm
index e7c5a82..6d08c86 100644 (file)
--- 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 {
 =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
 
   "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
                 ,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
               )
   ";
                 ,0
               )
   ";

diff --git a/httemplate/search/report_receivables.cgi b/httemplate/search/report_receivables.cgi
index 73ebee4..3696ed4 100755 (executable)
--- a/httemplate/search/report_receivables.cgi
+++ b/httemplate/search/report_receivables.cgi
@@ -16,7 +16,7 @@ die "access denied"
 #Example:
 #
 # my $balance = balance(
 #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")
 #   '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;
 
 
   #$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 . ')' );

 
 }
 
 
 }