ARPU option on sales report, #30911

diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm
index 8c45ae1..924fd05 100644 (file)
--- 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 )

diff --git a/httemplate/graph/cust_bill_pkg.cgi b/httemplate/graph/cust_bill_pkg.cgi
index 151b295..ea7fee9 100644 (file)
--- a/httemplate/graph/cust_bill_pkg.cgi
+++ b/httemplate/graph/cust_bill_pkg.cgi
@@ -1,7 +1,7 @@
 <% include('elements/monthly.html',
    #Dumper(
                 'title'        => $title,
-                'graph_type'   => 'Mountain',
+                'graph_type'   => $graph_type,
                 'items'        => \@items,
                 'params'       => \@params,
                 'labels'       => \@labels,
@@ -10,7 +10,8 @@
                 'links'        => \@links,
                 'no_graph'     => \@no_graph,
                 'remove_empty' => 1,
-                'bottom_total' => 1,
+                'bottom_total' => $show_total,
+                'nototal'      => !$show_total,
                 'bottom_link'  => $bottom_link,
                 'agentnum'     => $agentnum,
                 'cust_classnum'=> \@cust_classnums,
@@ -30,6 +31,15 @@ my $use_override         = $cgi->param('use_override')         ? 1 : 0;
 my $average_per_cust_pkg = $cgi->param('average_per_cust_pkg') ? 1 : 0;
 my $distribute           = $cgi->param('distribute')           ? 1 : 0;
 
+my $show_total = 1;
+my $graph_type = 'Mountain';
+
+if ( $average_per_cust_pkg ) {
+  # then the rows are not additive
+  $show_total = 0;
+  $graph_type = 'LinesPoints';
+}
+
 my %charge_labels = (
   'SR' => 'setup + recurring',
   'RU' => 'recurring',
@@ -356,6 +366,17 @@ foreach my $agent ( $all_agent || $sel_agent || $FS::CurrentUser::CurrentUser->a
 
 }
 
+# may be useful at some point...
+#if ( $average_per_cust_pkg ) {
+#  @items = map { ('cust_bill_pkg', 'cust_bill_pkg_count_pkgnum') } @items;
+#  @labels = map { $_, "Packages" } @labels;
+#  @params = map { $_, $_ } @params;
+#  @links = map { $_, $_ } @links;
+#  @colors = map { $_, $_ } @colors;
+#  @no_graph = map { $_, 1 } @no_graph;
+#}
+#
+
 #use Data::Dumper;
 if ( $cgi->param('debug') == 1 ) {
   $FS::Report::Table::DEBUG = 1;