average per cust_pkg option for peter

3 files changed, 36 insertions, 22 deletions

diff --git a/FS/FS/Report/Table/Monthly.pm b/FS/FS/Report/Table/Monthly.pm
index 845ab150b..fa9949d49 100644
--- a/FS/FS/Report/Table/Monthly.pm
+++ b/FS/FS/Report/Table/Monthly.pm
@@ -331,11 +331,15 @@ sub cust_bill_pkg {
 
   $agentnum ||= $opt{'agentnum'};
 
-  my $usage = cust_bill_pkg_detail(@_);
+  my $total_sql =
+    " SELECT COALESCE( SUM(cust_bill_pkg.setup + cust_bill_pkg.recur), 0 ) ";
 
-  my $total = $self->scalar_sql("
-    SELECT SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)
-      FROM cust_bill_pkg
+  $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
         LEFT JOIN cust_bill USING ( invnum )
         LEFT JOIN cust_main USING ( custnum )
         LEFT JOIN cust_pkg USING ( pkgnum )
@@ -343,15 +347,16 @@ sub cust_bill_pkg {
         LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart
       WHERE pkgnum != 0
         AND $where
-        AND ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum)
-  );
+        AND ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum);
   
   if ($opt{use_usage} && $opt{use_usage} eq 'recurring') {
+    my $total = $self->scalar_sql($total_sql);
+    my $usage = cust_bill_pkg_detail(@_); #$speriod, $eperiod, $agentnum, %opt 
     return $total-$usage;
   } elsif ($opt{use_usage} && $opt{use_usage} eq 'usage') {
-    return $usage;
+    return cust_bill_pkg_detail(@_); #$speriod, $eperiod, $agentnum, %opt 
   } else {
-    return $total;
+    return $self->scalar_sql($total_sql);
   }
 }
 
@@ -391,9 +396,14 @@ sub cust_bill_pkg_detail {
 
   my $where = join( ' AND ', @where );
 
-  $self->scalar_sql("
-    SELECT SUM(amount)
-      FROM cust_bill_pkg_detail
+  my $total_sql = " SELECT SUM(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
         LEFT JOIN cust_main USING ( custnum )
@@ -401,8 +411,9 @@ sub cust_bill_pkg_detail {
         LEFT JOIN part_pkg USING ( pkgpart )
         LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart
       WHERE $where
-        AND ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum)
-  );
+        AND ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum);
+
+  $self->scalar_sql($total_sql);
   
 }
 
diff --git a/httemplate/graph/cust_bill_pkg.cgi b/httemplate/graph/cust_bill_pkg.cgi
index 832660ffd..2346d9416 100644
--- a/httemplate/graph/cust_bill_pkg.cgi
+++ b/httemplate/graph/cust_bill_pkg.cgi
@@ -68,11 +68,9 @@ if ( $cgi->param('classnum') =~ /^(\d*)$/ ) {
 }
 #eslaf
 
-my $use_override = 0;
-$use_override = 1 if ( $cgi->param('use_override') );
-
-my $use_usage = 0;
-$use_usage = 1 if ( $cgi->param('use_usage') );
+my $use_override         = $cgi->param('use_override')         ? 1 : 0;
+my $use_usage            = $cgi->param('use_usage')            ? 1 : 0;
+my $average_per_cust_pkg = $cgi->param('average_per_cust_pkg') ? 1 : 0;
 
 my $hue = 0;
 #my $hue_increment = 170;
@@ -113,10 +111,11 @@ foreach my $agent ( $sel_agent || qsearch('agent', { 'disabled' => '' } ) ) {
 
       my $row_classnum = ref($pkg_class) ? $pkg_class->classnum : 0;
       my $row_agentnum = $agent->agentnum;
-      push @params, [ 'classnum'     => $row_classnum,
-                      'agentnum'     => $row_agentnum,
-                      'use_override' => $use_override,
-                      'use_usage'    => $component,
+      push @params, [ 'classnum'             => $row_classnum,
+                      'agentnum'             => $row_agentnum,
+                      'use_override'         => $use_override,
+                      'use_usage'            => $component,
+                      'average_per_cust_pkg' => $average_per_cust_pkg,
                     ];
 
       push @links, "$link;agentnum=$row_agentnum;classnum=$row_classnum;".
diff --git a/httemplate/graph/report_cust_bill_pkg.html b/httemplate/graph/report_cust_bill_pkg.html
index 51655a925..4a6433272 100644
--- a/httemplate/graph/report_cust_bill_pkg.html
+++ b/httemplate/graph/report_cust_bill_pkg.html
@@ -35,6 +35,10 @@
   <TD>Separate rated usage from recurring fees</TD>
 </TR>
 
+<TR>
+  <TD ALIGN="right"><INPUT TYPE="checkbox" NAME="average_per_cust_pkg" VALUE="1"></TD>
+  <TD>Average per customer package</TD>
+</TR>
 
 </TABLE>