make package churn report actually show package churn, #7990

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

diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm
index 98f66e9..3a4a169 100644 (file)
--- a/FS/FS/Report/Table.pm
+++ b/FS/FS/Report/Table.pm
@@ -664,37 +664,10 @@ sub cust_bill_pkg_discount {
 
 }
 
 
 }
 

-sub pkg_field_where {
-  my( $self, $field, $speriod, $eperiod, $agentnum, %opt ) = @_;
-  # someday this will use an aggregate query and return all the columns
-  # at once
-  # and I will drive a Tesla and have a live-in sushi chef who is also a 
-  # ninja bodyguard
-  my @where = (
-    $self->in_time_period_and_agent($speriod,
-                                    $eperiod,
-                                    $agentnum,
-                                    "cust_pkg.$field",
-                                   ),
-    $self->with_refnum(%opt),
-    $self->with_towernum(%opt),
-    $self->with_zip(%opt),
-    # can't use with_classnum here...
-  );
-  if ($opt{classnum}) {
-    my $classnum = $opt{classnum};
-    $classnum = [ $classnum ] if !ref($classnum);
-    @$classnum = grep /^\d+$/, @$classnum;
-    my $in = 'IN ('. join(',', @$classnum). ')';
-    push @where, "COALESCE(part_pkg.classnum, 0) $in" if scalar @$classnum;
-  }
+##### churn report #####

 
 

-  ' WHERE ' . join(' AND ', grep $_, @where);
-}
-
-=item setup_pkg: The number of packages with setup dates in the period.
-
-This excludes packages created by package changes. Options:
+=item active_pkg: The number of packages that were active at the start of 
+the period. The end date of the period is ignored. Options:

 
 - refnum: Limit to customers with this advertising source.
 - classnum: Limit to packages with this class.
 
 - refnum: Limit to customers with this advertising source.
 - classnum: Limit to packages with this class.


@@ -704,61 +677,86 @@ This excludes packages created by package changes. Options:
 Except for zip, any of these can be an arrayref to allow multiple values for
 the field.
 
 Except for zip, any of these can be an arrayref to allow multiple values for
 the field.
 

-=item susp_pkg: The number of suspended packages that were last suspended
-in the period. Options are as for setup_pkg.
+=item setup_pkg: The number of packages with setup dates in the period. This 
+excludes packages created by package changes. Options are as for active_pkg.
+
+=item susp_pkg: The number of packages that were suspended in the period
+(and not canceled).  Options are as for active_pkg.
+
+=item unsusp_pkg: The number of packages that were unsuspended in the period.
+Options are as for active_pkg.

 
 =item cancel_pkg: The number of packages with cancel dates in the period.
 Excludes packages that were canceled to be changed to a new package. Options
 
 =item cancel_pkg: The number of packages with cancel dates in the period.
 Excludes packages that were canceled to be changed to a new package. Options

-are as for setup_pkg.
+are as for active_pkg.

 
 =cut
 
 
 =cut
 

+sub active_pkg {
+  my $self = shift;
+  $self->churn_pkg('active', @_);
+}
+

 sub setup_pkg {
   my $self = shift;
 sub setup_pkg {
   my $self = shift;

-  my $sql = 'SELECT COUNT(*) FROM cust_pkg
-              LEFT JOIN part_pkg USING (pkgpart)
-              LEFT JOIN cust_main USING (custnum)'.
-              $self->pkg_field_where('setup', @_) .
-              ' AND change_pkgnum IS NULL';
+  $self->churn_pkg('setup', @_);
+}

 
 

-  $self->scalar_sql($sql);
+sub cancel_pkg {
+  my $self = shift;
+  $self->churn_pkg('cancel', @_);

 }
 
 sub susp_pkg {
 }
 
 sub susp_pkg {

-  # number of currently suspended packages that were suspended in the period

   my $self = shift;
   my $self = shift;

-  my $sql = 'SELECT COUNT(*) FROM cust_pkg
-              LEFT JOIN part_pkg USING (pkgpart)
-              LEFT JOIN cust_main USING (custnum) '.
-              $self->pkg_field_where('susp', @_);
+  $self->churn_pkg('susp', @_);
+}

 
 

-  $self->scalar_sql($sql);
+sub unsusp_pkg {
+  my $self = shift;
+  $self->churn_pkg('unsusp', @_);

 }
 
 }
 

-sub cancel_pkg {
-  # number of packages canceled in the period and not changed to another
-  # package
+sub churn_pkg {

   my $self = shift;
   my $self = shift;

-  my $sql = 'SELECT COUNT(*) FROM cust_pkg
-              LEFT JOIN part_pkg USING (pkgpart)
-              LEFT JOIN cust_main USING (custnum)
-              LEFT JOIN cust_pkg changed_to_pkg ON(
-                cust_pkg.pkgnum = changed_to_pkg.change_pkgnum
-              ) '.
-              $self->pkg_field_where('cancel', @_) .
-              ' AND changed_to_pkg.pkgnum IS NULL';
+  my ( $status, $speriod, $eperiod, $agentnum, %opt ) = @_;
+  my ($from, @where) =
+    FS::h_cust_pkg->churn_fromwhere_sql( $status, $speriod, $eperiod);
+
+  push @where, $self->pkg_where(%opt, 'agentnum' => $agentnum);
+
+  my $sql = "SELECT COUNT(*) FROM $from
+    JOIN part_pkg ON (cust_pkg.pkgpart = part_pkg.pkgpart)
+    JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)";
+  $sql .= ' WHERE '.join(' AND ', @where)
+    if scalar(@where);

 
   $self->scalar_sql($sql);
 }
 
 
   $self->scalar_sql($sql);
 }
 

-#this is going to be harder..
-#sub unsusp_pkg {
-#  my( $self, $speriod, $eperiod, $agentnum ) = @_;
-#  $self->scalar_sql("
-#    SELECT COUNT(*) FROM h_cust_pkg
-#      WHERE 
-#
-#}
+sub pkg_where {
+  my $self = shift;
+  my %opt = @_;
+  my @where = (
+    "part_pkg.freq != '0'",
+    $self->with_refnum(%opt),
+    $self->with_towernum(%opt),
+    $self->with_zip(%opt),
+  );
+  if ($opt{agentnum} =~ /^(\d+)$/) {
+    push @where, "cust_main.agentnum = $1";
+  }
+  if ($opt{classnum}) {
+    my $classnum = $opt{classnum};
+    $classnum = [ $classnum ] if !ref($classnum);
+    @$classnum = grep /^\d+$/, @$classnum;
+    my $in = 'IN ('. join(',', @$classnum). ')';
+    push @where, "COALESCE(part_pkg.classnum, 0) $in" if scalar @$classnum;
+  }
+  @where;
+}
+
+##### end of churn report stuff #####

 
 sub in_time_period_and_agent {
   my( $self, $speriod, $eperiod, $agentnum ) = splice(@_, 0, 4);
 
 sub in_time_period_and_agent {
   my( $self, $speriod, $eperiod, $agentnum ) = splice(@_, 0, 4);