unapplied payments report, RT#4861

4 files changed, 38 insertions, 29 deletions

diff --git a/FS/FS/cust_main.pm b/FS/FS/cust_main.pm
index a1b891226..286d3332a 100644
--- a/FS/FS/cust_main.pm
+++ b/FS/FS/cust_main.pm
@@ -7519,6 +7519,32 @@ sub balance_date_sql {
 
 }
 
+=item unapplied_payments_date_sql START_TIME [ END_TIME ]
+
+Returns an SQL fragment to retreive the total unapplied payments for this
+customer, only considering invoices with date earlier than START_TIME, and
+optionally not later than END_TIME.
+
+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:
+
+=cut
+
+sub unapplied_payments_date_sql {
+  my( $class, $start, $end, ) = @_;
+
+  my $unapp_pay    = FS::cust_pay->unapplied_sql;
+
+  my $pay_where = $class->_money_table_where( 'cust_pay', $start, $end,
+                                                          'unapplied_date'=>1 );
+
+  " ( SELECT COALESCE(SUM($unapp_pay), 0) FROM cust_pay $pay_where ) ";
+}
+
 =item _money_table_where TABLE START_TIME [ END_TIME [ OPTION => VALUE ... ] ]
 
 Helper method for balance_date_sql; name (and usage) subject to change
diff --git a/httemplate/elements/menu.html b/httemplate/elements/menu.html
index 38546ca5d..48b7c498e 100644
--- a/httemplate/elements/menu.html
+++ b/httemplate/elements/menu.html
@@ -219,6 +219,7 @@ if($curuser->access_right('Financial reports')) {
     if $curuser->access_right('View customer pending payments');
   $report_financial{'Payment Batch Report'} = [ $fsurl.'search/pay_batch.html', 'Payment batches (by status and/or date range)' ]
     if $conf->exists('batch-enable') || $conf->config('batch-enable_payby');
+  $report_financial{'Unapplied payment Aging'} = [ $fsurl.'search/report_unapplied_cust_pay.html', 'Unapplied payment aging report' ];
   $report_financial{'A/R Aging'} = [ $fsurl.'search/report_receivables.html', 'Accounts Receivable Aging report' ];
   $report_financial{'Prepaid Income'} = [ $fsurl.'search/report_prepaid_income.html', 'Prepaid income (unearned revenue)  report' ];
   $report_financial{'Sales Tax Liability'} = [ $fsurl.'search/report_tax.html', 'Sales tax liability report (internal taxclass system)' ];
diff --git a/httemplate/search/report_unapplied_cust_pay.html b/httemplate/search/report_unapplied_cust_pay.html
index 3e5922109..eff86f4a8 100755
--- a/httemplate/search/report_unapplied_cust_pay.html
+++ b/httemplate/search/report_unapplied_cust_pay.html
@@ -21,8 +21,8 @@
   <TR>
     <TD ALIGN="right">Customers</TD>
     <TD>
-      <INPUT TYPE="radio" NAME="all_customers" VALUE="1" onClick="if (this.checked) { document.OneTrueForm.days.disabled=true; document.OneTrueForm.days.style.backgroundColor = '#dddddd'; } else { document.OneTrueForm.days.disabled=false; document.OneTrueForm.days.style.backgroundColor = '#ffffff'; }">All customers (even those without an outstanding balance)<BR>
-      <INPUT TYPE="radio" NAME="all_customers" VALUE="0" CHECKED onClick="if ( ! this.checked ) { document.OneTrueForm.days.disabled=true; document.OneTrueForm.days.style.backgroundColor = '#dddddd'; } else { document.OneTrueForm.days.disabled=false; document.OneTrueForm.days.style.backgroundColor = '#ffffff'; }">Customers with a balance over <INPUT NAME="days" TYPE="text" SIZE=4 MAXLENGTH=3 VALUE="0"> days old
+      <INPUT TYPE="radio" NAME="all_customers" VALUE="1" onClick="if (this.checked) { document.OneTrueForm.days.disabled=true; document.OneTrueForm.days.style.backgroundColor = '#dddddd'; } else { document.OneTrueForm.days.disabled=false; document.OneTrueForm.days.style.backgroundColor = '#ffffff'; }">All customers (even those without unapplied payments)<BR>
+      <INPUT TYPE="radio" NAME="all_customers" VALUE="0" CHECKED onClick="if ( ! this.checked ) { document.OneTrueForm.days.disabled=true; document.OneTrueForm.days.style.backgroundColor = '#dddddd'; } else { document.OneTrueForm.days.disabled=false; document.OneTrueForm.days.style.backgroundColor = '#ffffff'; }">Customers with unapplied payments <INPUT NAME="days" TYPE="text" SIZE=4 MAXLENGTH=3 VALUE="0"> days old
     </TD>
   </TR>
 
diff --git a/httemplate/search/unapplied_cust_pay.html b/httemplate/search/unapplied_cust_pay.html
index 35abd6bbc..8d064d174 100755
--- a/httemplate/search/unapplied_cust_pay.html
+++ b/httemplate/search/unapplied_cust_pay.html
@@ -2,7 +2,8 @@
                  #'title'       => 'Prepaid Balance Aging Summary', #???
                  'title'       => 'Unapplied Payments Aging Summary',
                  'range_sub'   => \&unapplied_payments,
-          );
+          )
+%>
 <%init>
 
 die "access denied"
@@ -11,35 +12,16 @@ die "access denied"
 </%init>
 <%once>
 
-#Example:
-#
-# my $balance = 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")
-#   'sum'    => 1, #set to true to get a SUM() of the values, for totals
-#
-#   #obsolete? options for totals (passed to cust_main::balance_date_sql)
-#   'total'  => 1, #set to true to remove all customer comparison clauses
-#   'join'   => $join,   #JOIN clause
-#   'where'  => \@where, #WHERE clause hashref (elements "AND"ed together)
-# )
-
-#sub balance {
 sub unapplied_payments {
   my($start, $end, %opt) = @_;
 
-#XXX fill-in
-#  #handle start and end ranges (86400 = 24h * 60m * 60s)
-#  my $str2time = str2time_sql;
-#  my $closing = str2time_sql_closing;
-#  $start = $start ? "( $str2time now() $closing - ".($start * 86400). ' )' : '';
-#  $end   = $end   ? "( $str2time now() $closing - ".($end   * 86400). ' )' : '';
-#
-#  #$opt{'unapplied_date'} = 1;
-#
-#  FS::cust_main->balance_date_sql( $start, $end, %opt ).
+  #handle start and end ranges (86400 = 24h * 60m * 60s)
+  my $str2time = str2time_sql;
+  my $closing = str2time_sql_closing;
+  $start = $start ? "( $str2time now() $closing - ".($start * 86400). ' )' : '';
+  $end   = $end   ? "( $str2time now() $closing - ".($end   * 86400). ' )' : '';
+
+  FS::cust_main->unapplied_payments_date_sql( $start, $end );
 
 }