From 4109791a9b80df968edaf2e71da8ebcfb8882be3 Mon Sep 17 00:00:00 2001
From: Mark Wells <mark@freeside.biz>
Date: Thu, 8 Aug 2013 18:43:07 -0700
Subject: [PATCH] unearned revenue report: consider primary object dates, not
 application dates, #13289

---
 FS/FS/cust_bill_pkg.pm                 | 10 ++++++----
 httemplate/search/unearned_detail.html | 20 ++++++++++++++------
 2 files changed, 20 insertions(+), 10 deletions(-)

diff --git a/FS/FS/cust_bill_pkg.pm b/FS/FS/cust_bill_pkg.pm
index 572fe7973..f94bf9b19 100644
--- a/FS/FS/cust_bill_pkg.pm
+++ b/FS/FS/cust_bill_pkg.pm
@@ -955,8 +955,8 @@ Returns an SQL expression for the sum of payments applied to this item.
 
 sub paid_sql {
   my ($class, $start, $end, %opt) = @_;
-  my $s = $start ? "AND cust_bill_pay._date <= $start" : '';
-  my $e = $end   ? "AND cust_bill_pay._date >  $end"   : '';
+  my $s = $start ? "AND cust_pay._date <= $start" : '';
+  my $e = $end   ? "AND cust_pay._date >  $end"   : '';
   my $setuprecur = 
     $opt{setuprecur} =~ /^s/ ? 'setup' :
     $opt{setuprecur} =~ /^r/ ? 'recur' :
@@ -965,6 +965,7 @@ sub paid_sql {
 
   my $paid = "( SELECT COALESCE(SUM(cust_bill_pay_pkg.amount),0)
      FROM cust_bill_pay_pkg JOIN cust_bill_pay USING (billpaynum)
+                            JOIN cust_pay      USING (paynum)
      WHERE cust_bill_pay_pkg.billpkgnum = cust_bill_pkg.billpkgnum
            $s $e $setuprecur )";
 
@@ -983,8 +984,8 @@ sub paid_sql {
 
 sub credited_sql {
   my ($class, $start, $end, %opt) = @_;
-  my $s = $start ? "AND cust_credit_bill._date <= $start" : '';
-  my $e = $end   ? "AND cust_credit_bill._date >  $end"   : '';
+  my $s = $start ? "AND cust_credit._date <= $start" : '';
+  my $e = $end   ? "AND cust_credit._date >  $end"   : '';
   my $setuprecur = 
     $opt{setuprecur} =~ /^s/ ? 'setup' :
     $opt{setuprecur} =~ /^r/ ? 'recur' :
@@ -993,6 +994,7 @@ sub credited_sql {
 
   my $credited = "( SELECT COALESCE(SUM(cust_credit_bill_pkg.amount),0)
      FROM cust_credit_bill_pkg JOIN cust_credit_bill USING (creditbillnum)
+                               JOIN cust_credit      USING (crednum)
      WHERE cust_credit_bill_pkg.billpkgnum = cust_bill_pkg.billpkgnum
            $s $e $setuprecur )";
 
diff --git a/httemplate/search/unearned_detail.html b/httemplate/search/unearned_detail.html
index 285fb50a7..b87a7477e 100644
--- a/httemplate/search/unearned_detail.html
+++ b/httemplate/search/unearned_detail.html
@@ -105,6 +105,10 @@ my $agentnums_sql =
 
 my @where = ( $agentnums_sql );
 
+if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
+  push @where, "cust_main.agentnum = $1";
+}
+
 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
 
 if ( $cgi->param('status') =~ /^([a-z]+)$/ ) {
@@ -191,12 +195,16 @@ push @select, "$unearned_sql AS unearned_revenue";
 # last payment/credit date
 my %t = (pay => 'cust_bill_pay', credit => 'cust_credit_bill');
 foreach my $x (qw(pay credit)) {
-  my $table = $t{$x};
-  my $link = $table.'_pkg';
-  my $pkey = dbdef->table($table)->primary_key;
-  my $last_date_sql = "SELECT MAX(_date) 
-  FROM $table JOIN $link USING ($pkey)
-  WHERE $link.billpkgnum = cust_bill_pkg.billpkgnum 
+  my $table     = "cust_$x";
+  my $link_bill = $t{$x};
+  my $link_pkg  = $link_bill.'_pkg';
+  my %pkey = map { $_ => dbdef->table($_)->primary_key }
+    ( $table, $link_bill );
+
+  my $last_date_sql = "SELECT MAX($table._date) 
+  FROM $table JOIN $link_bill USING ($pkey{$table})
+              JOIN $link_pkg  USING ($pkey{$link_bill})
+  WHERE $link_pkg.billpkgnum = cust_bill_pkg.billpkgnum 
   AND $table._date <= $unearned";
   push @select, "($last_date_sql) AS last_$x";
 }
-- 
2.11.0