average per cust_pkg option for peter

[freeside.git] / FS / FS / Report / Table / Monthly.pm

diff --git a/FS/FS/Report/Table/Monthly.pm b/FS/FS/Report/Table/Monthly.pm
index d75f0be..fa9949d 100644 (file)
--- a/FS/FS/Report/Table/Monthly.pm
+++ b/FS/FS/Report/Table/Monthly.pm
@@ -311,27 +311,109 @@ sub cust_bill_pkg {
   my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
 
   my $where = '';
+  my $comparison = '';
   if ( $opt{'classnum'} =~ /^(\d+)$/ ) {
     if ( $1 == 0 ) {
-      $where = "classnum IS NULL";
+      $comparison = "IS NULL";
     } else {
-      $where = "classnum = $1";
+      $comparison = "= $1";
+    }
+
+    if ( $opt{'use_override'} ) {
+      $where = "(
+        part_pkg.classnum $comparison AND pkgpart_override IS NULL OR
+        override.classnum $comparison AND pkgpart_override IS NOT NULL
+      )";
+    } else {
+      $where = "part_pkg.classnum $comparison";
     }
   }
 
   $agentnum ||= $opt{'agentnum'};
 
-  $self->scalar_sql("
-    SELECT SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)
-      FROM cust_bill_pkg
+  my $total_sql =
+    " SELECT COALESCE( SUM(cust_bill_pkg.setup + cust_bill_pkg.recur), 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
         LEFT JOIN cust_bill USING ( invnum )
         LEFT JOIN cust_main USING ( custnum )
         LEFT JOIN cust_pkg USING ( pkgnum )
         LEFT JOIN part_pkg USING ( pkgpart )
+        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 cust_bill_pkg_detail(@_); #$speriod, $eperiod, $agentnum, %opt 
+  } else {
+    return $self->scalar_sql($total_sql);
+  }
+}
+
+sub cust_bill_pkg_detail {
+  my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
+
+  my @where = ( "cust_bill_pkg.pkgnum != 0" );
+  my $comparison = '';
+  if ( $opt{'classnum'} =~ /^(\d+)$/ ) {
+    if ( $1 == 0 ) {
+      $comparison = "IS NULL";
+    } else {
+      $comparison = "= $1";
+    }
+
+    if ( $opt{'use_override'} ) {
+      push @where, "(
+        part_pkg.classnum $comparison AND pkgpart_override IS NULL OR
+        override.classnum $comparison AND pkgpart_override IS NOT NULL
+      )";
+    } else {
+      push @where, "part_pkg.classnum $comparison";
+    }
+  }
+
+  if ( $opt{'usageclass'} =~ /^(\d+)$/ ) {
+    if ( $1 == 0 ) {
+      $comparison = "IS NULL";
+    } else {
+      $comparison = "= $1";
+    }
+
+    push @where, "cust_bill_pkg_detail.classnum $comparison";
+  }
+
+  $agentnum ||= $opt{'agentnum'};
+
+  my $where = join( ' AND ', @where );
+
+  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 )
+        LEFT JOIN cust_pkg ON cust_bill_pkg.pkgnum = cust_pkg.pkgnum
+        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);
+
+  $self->scalar_sql($total_sql);
   
 }