ARPU option on sales report, #30911

1 files changed, 53 insertions, 28 deletions

diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm
index 8c45ae1..924fd05 100644
--- a/FS/FS/Report/Table.pm
+++ b/FS/FS/Report/Table.pm
@@ -408,6 +408,8 @@ unspecified, defaults to all three.
 'use_override': for line items generated by an add-on package, use the class
 of the add-on rather than the base package.
 
+'average_per_cust_pkg': divide the result by the number of distinct packages.
+
 'distribute': for non-monthly recurring charges, ignore the invoice 
 date.  Instead, consider the line item's starting/ending dates.  Determine 
 the fraction of the line item duration that falls within the specified 
@@ -428,6 +430,12 @@ sub cust_bill_pkg {
   $sum += $self->cust_bill_pkg_setup(@_) if $charges{S};
   $sum += $self->cust_bill_pkg_recur(@_) if $charges{R};
   $sum += $self->cust_bill_pkg_detail(@_) if $charges{U};
+
+  if ($opt{'average_per_cust_pkg'}) {
+    my $count = $self->cust_bill_pkg_count_pkgnum(@_);
+    return '' if $count == 0;
+    $sum = sprintf('%.2f', $sum / $count);
+  }
   $sum;
 }
 
@@ -469,7 +477,9 @@ sub cust_bill_pkg_setup {
   $self->scalar_sql($total_sql);
 }
 
-sub cust_bill_pkg_recur {
+sub _cust_bill_pkg_recurring {
+  # returns the FROM/WHERE part of the statement to query all recurring 
+  # line items in the period
   my $self = shift;
   my ($speriod, $eperiod, $agentnum, %opt) = @_;
 
@@ -486,18 +496,6 @@ sub cust_bill_pkg_recur {
 
   push @where, $self->with_cust_classnum(%opt);
 
-  # subtract all usage from the line item regardless of date
-  my $item_usage;
-  if ( $opt{'project'} ) {
-    $item_usage = 'usage'; #already calculated
-  }
-  else {
-    $item_usage = '( SELECT COALESCE(SUM(cust_bill_pkg_detail.amount),0)
-      FROM cust_bill_pkg_detail
-      WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum )';
-  }
-  my $recur_fraction = '';
-
   if ( $opt{'distribute'} ) {
     $where[0] = 'pkgnum != 0'; # specifically exclude fees
     push @where, "cust_main.agentnum = $agentnum" if $agentnum;
@@ -505,11 +503,6 @@ sub cust_bill_pkg_recur {
       "$cust_bill_pkg.sdate <  $eperiod",
       "$cust_bill_pkg.edate >= $speriod",
     ;
-    # the fraction of edate - sdate that's within [speriod, eperiod]
-    $recur_fraction = " * 
-      CAST(LEAST($eperiod, $cust_bill_pkg.edate) - 
-       GREATEST($speriod, $cust_bill_pkg.sdate) AS DECIMAL) / 
-      ($cust_bill_pkg.edate - $cust_bill_pkg.sdate)";
   }
   else {
     # we don't want to have to create v_cust_bill
@@ -518,12 +511,52 @@ sub cust_bill_pkg_recur {
       $self->in_time_period_and_agent($speriod, $eperiod, $agentnum, $_date);
   }
 
-  my $total_sql = 'SELECT '.
-  "COALESCE(SUM(($cust_bill_pkg.recur - $item_usage) $recur_fraction),0)
+  return "
   FROM $cust_bill_pkg 
   $cust_bill_pkg_join
   WHERE ".join(' AND ', grep $_, @where);
 
+}
+
+sub cust_bill_pkg_recur {
+  my $self = shift;
+  my ($speriod, $eperiod, $agentnum, %opt) = @_;
+
+  # subtract all usage from the line item regardless of date
+  my $item_usage;
+  if ( $opt{'project'} ) {
+    $item_usage = 'usage'; #already calculated
+  }
+  else {
+    $item_usage = '( SELECT COALESCE(SUM(cust_bill_pkg_detail.amount),0)
+      FROM cust_bill_pkg_detail
+      WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum )';
+  }
+  
+  my $cust_bill_pkg = $opt{'project'} ? 'v_cust_bill_pkg' : 'cust_bill_pkg';
+
+  my $recur_fraction = '';
+  if ($opt{'distribute'}) {
+    # the fraction of edate - sdate that's within [speriod, eperiod]
+    $recur_fraction = " * 
+      CAST(LEAST($eperiod, $cust_bill_pkg.edate) - 
+       GREATEST($speriod, $cust_bill_pkg.sdate) AS DECIMAL) / 
+      ($cust_bill_pkg.edate - $cust_bill_pkg.sdate)";
+  }
+
+  my $total_sql = 
+    "SELECT COALESCE(SUM(($cust_bill_pkg.recur - $item_usage) $recur_fraction),0)" .
+    $self->_cust_bill_pkg_recurring(@_);
+
+  $self->scalar_sql($total_sql);
+}
+
+sub cust_bill_pkg_count_pkgnum {
+  # for ARPU calculation
+  my $self = shift;
+  my $total_sql = 'SELECT COUNT(DISTINCT pkgnum) '.
+    $self->_cust_bill_pkg_recurring(@_);
+
   $self->scalar_sql($total_sql);
 }
 
@@ -570,10 +603,6 @@ sub cust_bill_pkg_detail {
   my $total_sql = " SELECT SUM(cust_bill_pkg_detail.amount) ";
 
   $total_sql .=
-    " / CASE COUNT(cust_pkg.*) WHEN 0 THEN 1 ELSE COUNT(cust_pkg.*) END "
-      if $opt{average_per_cust_pkg};
-
-  $total_sql .=
     " FROM cust_bill_pkg_detail
         LEFT JOIN cust_bill_pkg USING ( billpkgnum )
         LEFT JOIN cust_bill ON cust_bill_pkg.invnum = cust_bill.invnum
@@ -616,10 +645,6 @@ sub cust_bill_pkg_discount {
   my $total_sql =
     " SELECT COALESCE( SUM( cust_bill_pkg_discount.amount ), 0 ) ";
 
-  #$total_sql .=
-  #  " / CASE COUNT(cust_pkg.*) WHEN 0 THEN 1 ELSE COUNT(cust_pkg.*) END "
-  #    if $opt{average_per_cust_pkg};
-
   $total_sql .=
     " FROM cust_bill_pkg_discount
         LEFT JOIN cust_bill_pkg USING ( billpkgnum )