show negative balances on A/R report, closes: RT#2983

2 files changed, 112 insertions, 44 deletions

diff --git a/FS/FS/cust_credit.pm b/FS/FS/cust_credit.pm
index 938686fea..70d78749c 100644
--- a/FS/FS/cust_credit.pm
+++ b/FS/FS/cust_credit.pm
@@ -331,15 +331,15 @@ sub cust_credit_bill {
   ;
 }
 
-=item credited
+=item unapplied
 
-Returns the amount of this credit that is still outstanding; which is
+Returns the amount of this credit that is still unapplied/outstanding; 
 amount minus all refund applications (see L<FS::cust_credit_refund>) and
 applications to invoices (see L<FS::cust_credit_bill>).
 
 =cut
 
-sub credited {
+sub unapplied {
   my $self = shift;
   my $amount = $self->amount;
   $amount -= $_->amount foreach ( $self->cust_credit_refund );
@@ -347,6 +347,18 @@ sub credited {
   sprintf( "%.2f", $amount );
 }
 
+=item credited
+
+Deprecated name for the unapplied method.
+
+=cut
+
+sub credited {
+  my $self = shift;
+  #carp "cust_credit->credited deprecated; use ->unapplied";
+  $self->unapplied(@_);
+}
+
 =item cust_main
 
 Returns the customer (see L<FS::cust_main>) for this credit.
@@ -527,13 +539,13 @@ sub _upgrade_data {  # class method
 
 =over 4
 
-=item credited_sql
+=item unapplied_sql
 
 Returns an SQL fragment to retreive the unapplied amount.
 
 =cut
 
-sub credited_sql {
+sub unapplied_sql {
   #my $class = shift;
 
   "amount
@@ -551,14 +563,29 @@ sub credited_sql {
 
 }
 
+=item credited_sql
+
+Deprecated name for the unapplied_sql method.
+
+=cut
+
+sub credited_sql {
+  #my $class = shift;
+
+  #carp "cust_credit->credited_sql deprecated; use ->unapplied_sql";
+
+  #$class->unapplied_sql(@_);
+  unapplied_sql();
+}
+
 =back
 
 =head1 BUGS
 
 The delete method.  The replace method.
 
-B<credited> and B<credited_sql> should probably be called B<unapplied> and
-B<unapplied_sql>.
+B<credited> and B<credited_sql> are now called B<unapplied> and
+B<unapplied_sql>.  The old method names should start to give warnings.
 
 =head1 SEE ALSO
 
diff --git a/FS/FS/cust_main.pm b/FS/FS/cust_main.pm
index 952aa73c2..de4d38de9 100644
--- a/FS/FS/cust_main.pm
+++ b/FS/FS/cust_main.pm
@@ -5108,58 +5108,99 @@ Returns an SQL fragment to retreive the balance.
 =cut
 
 sub balance_sql { "
-    COALESCE( ( SELECT SUM(charged) FROM cust_bill
-                  WHERE cust_bill.custnum   = cust_main.custnum ), 0)
-  - COALESCE( ( SELECT SUM(paid)    FROM cust_pay
-                  WHERE cust_pay.custnum    = cust_main.custnum ), 0)
-  - COALESCE( ( SELECT SUM(amount)  FROM cust_credit
-                  WHERE cust_credit.custnum = cust_main.custnum ), 0)
-  + COALESCE( ( SELECT SUM(refund)  FROM cust_refund
-                   WHERE cust_refund.custnum = cust_main.custnum ), 0)
+    ( SELECT COALESCE( SUM(charged), 0 ) FROM cust_bill
+        WHERE cust_bill.custnum   = cust_main.custnum     )
+  - ( SELECT COALESCE( SUM(paid),    0 ) FROM cust_pay
+        WHERE cust_pay.custnum    = cust_main.custnum     )
+  - ( SELECT COALESCE( SUM(amount),  0 ) FROM cust_credit
+        WHERE cust_credit.custnum = cust_main.custnum     )
+  + ( SELECT COALESCE( SUM(refund),  0 ) FROM cust_refund
+        WHERE cust_refund.custnum = cust_main.custnum     )
 "; }
 
-=item balance_date_sql TIME
+=item balance_date_sql START_TIME [ END_TIME [ OPTION => VALUE ... ] ]
 
 Returns an SQL fragment to retreive the balance for this customer, only
-considering invoices with date earlier than TIME. (total_owed_date minus total_credited minus
-total_unapplied_payments).  TIME is specified as an SQL fragment or a numeric
-UNIX timestamp; see L<perlfunc/"time">).  Also see L<Time::Local> and
-L<Date::Parse> for conversion functions.
+considering invoices with date earlier than START_TIME, and optionally not
+later than END_TIME (total_owed_date minus total_credited minus
+total_unapplied_payments).
+
+Times are specified as SQL fragments or numeric
+UNIX timestamps; see L<perlfunc/"time">).  Also see L<Time::Local> and
+L<Date::Parse> for conversion functions.  The empty string can be passed
+to disable that time constraint completely.
+
+Available options are:
+
+=over 4
+
+=item unapplied_date - set to true to disregard unapplied credits, payments and refunds outside the specified time period - by default the time period restriction only applies to invoices (useful for reporting, probably a bad idea for event triggering)
+
+=item total - set to true to remove all customer comparison clauses, for totals
+
+=item where - WHERE clause hashref (elements "AND"ed together) (typically used with the total option)
+
+=item join - JOIN clause (typically used with the total option)
+
+=item 
+
+=back
 
 =cut
 
 sub balance_date_sql {
-  my( $class, $time ) = @_;
+  my( $class, $start, $end, %opt ) = @_;
 
-  my $owed_sql         = FS::cust_bill->owed_sql;
-  my $unapp_refund_sql = FS::cust_refund->unapplied_sql;
-  #my $unapp_credit_sql = FS::cust_credit->unapplied_sql;
-  my $unapp_credit_sql = FS::cust_credit->credited_sql;
-  my $unapp_pay_sql    = FS::cust_pay->unapplied_sql;
+  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;
 
-  "
-      COALESCE( ( SELECT SUM($owed_sql) FROM cust_bill
-                    WHERE cust_bill.custnum   = cust_main.custnum
-                      AND cust_bill._date    <= $time             )
-                ,0
-              )
-    + COALESCE( ( SELECT SUM($unapp_refund_sql) FROM cust_refund
-                    WHERE cust_refund.custnum = cust_main.custnum )
-                ,0
-              )
-    - COALESCE( ( SELECT SUM($unapp_credit_sql) FROM cust_credit
-                    WHERE cust_credit.custnum = cust_main.custnum )
-                ,0
-              )
-    - COALESCE( ( SELECT SUM($unapp_pay_sql) FROM cust_pay
-                    WHERE cust_pay.custnum = cust_main.custnum )
-                ,0
-              )
+  my $j = $opt{'join'} || '';
 
+  my $owed_wh   = $class->_money_table_where( 'cust_bill',   $start,$end,%opt );
+  my $refund_wh = $class->_money_table_where( 'cust_refund', $start,$end,%opt );
+  my $credit_wh = $class->_money_table_where( 'cust_credit', $start,$end,%opt );
+  my $pay_wh    = $class->_money_table_where( 'cust_pay',    $start,$end,%opt );
+
+  "   ( SELECT COALESCE(SUM($owed),         0) FROM cust_bill   $j $owed_wh   )
+    + ( SELECT COALESCE(SUM($unapp_refund), 0) FROM cust_refund $j $refund_wh )
+    - ( SELECT COALESCE(SUM($unapp_credit), 0) FROM cust_credit $j $credit_wh )
+    - ( SELECT COALESCE(SUM($unapp_pay),    0) FROM cust_pay    $j $pay_wh    )
   ";
 
 }
 
+=item _money_table_where TABLE START_TIME [ END_TIME [ OPTION => VALUE ... ] ]
+
+Helper method for balance_date_sql; name (and usage) subject to change
+(suggestions welcome).
+
+Returns a WHERE clause for the specified monetary TABLE (cust_bill,
+cust_refund, cust_credit or cust_pay).
+
+If TABLE is "cust_bill" or the unapplied_date option is true, only
+considers records with date earlier than START_TIME, and optionally not
+later than END_TIME .
+
+=cut
+
+sub _money_table_where {
+  my( $class, $table, $start, $end, %opt ) = @_;
+
+  my @where = ();
+  push @where, "cust_main.custnum = $table.custnum" unless $opt{'total'};
+  if ( $table eq 'cust_bill' || $opt{'unapplied_date'} ) {
+    push @where, "$table._date <= $start" if length($start);
+    push @where, "$table._date >  $end"   if length($end);
+  }
+  push @where, @{$opt{'where'}} if $opt{'where'};
+  my $where = scalar(@where) ? 'WHERE '. join(' AND ', @where ) : '';
+
+  $where;
+
+}
+
 =item fuzzy_search FUZZY_HASHREF [ HASHREF, SELECT, EXTRA_SQL, CACHE_OBJ ]
 
 Performs a fuzzy (approximate) search and returns the matching FS::cust_main