rest of customer churn report, #30132

3 files changed, 171 insertions, 3 deletions

diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm
index 3a4a169..934287a 100644
--- a/FS/FS/Report/Table.pm
+++ b/FS/FS/Report/Table.pm
@@ -664,7 +664,7 @@ sub cust_bill_pkg_discount {
 
 }
 
-##### churn report #####
+##### package churn report #####
 
 =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:
@@ -756,7 +756,91 @@ sub pkg_where {
   @where;
 }
 
-##### end of churn report stuff #####
+##### end of package churn report stuff #####
+
+##### customer churn report #####
+
+=item active_cust: The number of customers who had any active recurring 
+packages at the start of the period. The end date is ignored, agentnum is 
+mandatory, and no other parameters are accepted.
+
+=item started_cust: The number of customers who had no active packages at 
+the start of the period, but had active packages at the end. Like
+active_cust, agentnum is mandatory and no other parameters are accepted.
+
+=item suspended_cust: The number of customers who had active packages at
+the start of the period, and at the end had no active packages but some
+suspended packages. Note that this does not necessarily mean that their 
+packages were suspended during the period.
+
+=item resumed_cust: The inverse of suspended_cust: the number of customers
+who had suspended packages and no active packages at the start of the 
+period, and active packages at the end.
+
+=item cancelled_cust: The number of customers who had active packages
+at the start of the period, and only cancelled packages at the end.
+
+=cut
+
+sub active_cust {
+  my $self = shift;
+  $self->churn_cust(@_)->{active};
+}
+sub started_cust {
+  my $self = shift;
+  $self->churn_cust(@_)->{started};
+}
+sub suspended_cust {
+  my $self = shift;
+  $self->churn_cust(@_)->{suspended};
+}
+sub resumed_cust {
+  my $self = shift;
+  $self->churn_cust(@_)->{resumed};
+}
+sub cancelled_cust {
+  my $self = shift;
+  $self->churn_cust(@_)->{cancelled};
+}
+
+sub churn_cust {
+  my $self = shift;
+  my ( $speriod ) = @_;
+
+  # run one query for each interval
+  return $self->{_interval}{$speriod} ||= $self->calculate_churn_cust(@_);
+}
+
+sub calculate_churn_cust {
+  my $self = shift;
+  my ($speriod, $eperiod, $agentnum, %opt) = @_;
+
+  my $churn_sql = FS::cust_main::Status->churn_sql($speriod, $eperiod);
+  my $where = '';
+  $where = " WHERE cust_main.agentnum = $agentnum " if $agentnum;
+  my $cust_sql =
+    "SELECT churn.* ".
+    "FROM cust_main JOIN ($churn_sql) AS churn USING (custnum)".
+    $where;
+
+  # query to count the ones with certain status combinations
+  my $total_sql = "
+    SELECT SUM((s_active > 0)::int)                   as active,
+           SUM((s_active = 0 and e_active > 0)::int)  as started,
+           SUM((s_active > 0 and e_active = 0 and e_suspended > 0)::int)
+                                                      as suspended,
+           SUM((s_active = 0 and s_suspended > 0 and e_active > 0)::int)
+                                                      as resumed,
+           SUM((s_active > 0 and e_active = 0 and e_suspended = 0)::int)
+                                                      as cancelled
+    FROM ($cust_sql) AS x
+  ";
+
+  my $sth = dbh->prepare($total_sql);
+  $sth->execute or die "failed to execute churn query: " . $sth->errstr;
+
+  $self->{_interval}{$speriod} = $sth->fetchrow_hashref;
+}
 
 sub in_time_period_and_agent {
   my( $self, $speriod, $eperiod, $agentnum ) = splice(@_, 0, 4);
diff --git a/FS/FS/cust_main/Status.pm b/FS/FS/cust_main/Status.pm
index f84ff0f..b5e8986 100644
--- a/FS/FS/cust_main/Status.pm
+++ b/FS/FS/cust_main/Status.pm
@@ -103,6 +103,42 @@ sub cancelled_sql {
 
 =back
 
+=head1 CLASS METHODS
+
+=over 4
+
+=item churn_sql START, END
+
+Returns an SQL statement for the customer churn status query.  The columns
+returned are the custnum and the number of active, suspended, and cancelled
+packages (excluding one-time packages) at the start date ("s_active",
+"s_suspended", and "s_cancelled") and the end date ("e_active", etc.).
+
+=cut
+
+# not sure this belongs here...FS::cust_main::Packages?
+
+sub churn_sql {
+  my $self = shift;
+  my ($speriod, $eperiod) = @_;
+
+  my $s_sql = FS::h_cust_pkg->status_as_of_sql($speriod);
+  my $e_sql = FS::h_cust_pkg->status_as_of_sql($eperiod);
+
+  my @select = (
+    'custnum',
+    'COALESCE(SUM(s.is_active::int),0)     as s_active',
+    'COALESCE(SUM(s.is_suspended::int),0)  as s_suspended',
+    'COALESCE(SUM(s.is_cancelled::int),0)  as s_cancelled',
+    'COALESCE(SUM(e.is_active::int),0)     as e_active',
+    'COALESCE(SUM(e.is_suspended::int),0)  as e_suspended',
+    'COALESCE(SUM(e.is_cancelled::int),0)  as e_cancelled',
+  );
+  my $from = "($s_sql) AS s FULL JOIN ($e_sql) AS e USING (custnum)";
+
+  return "SELECT ".join(',', @select)." FROM $from GROUP BY custnum";
+}
+
 =head1 BUGS
 
 =head1 SEE ALSO
diff --git a/FS/FS/h_cust_pkg.pm b/FS/FS/h_cust_pkg.pm
index 0c3db10..423b442 100644
--- a/FS/FS/h_cust_pkg.pm
+++ b/FS/FS/h_cust_pkg.pm
@@ -177,9 +177,57 @@ sub churn_fromwhere_sql {
   return ($from, @where);
 }
 
+=head1 as_of_sql DATE
+
+Returns a qsearch hash for the instantaneous state of the cust_pkg table 
+on DATE.
+
+Currently accepts no restrictions; use it in a subquery if you want to 
+limit or sort the output. (Restricting within the query is problematic.)
+
+=cut
+
+sub as_of_sql {
+  my $class = shift;
+  my $date = shift;
+  "SELECT DISTINCT ON (pkgnum) *
+    FROM h_cust_pkg
+    WHERE history_date < $date
+      AND history_action IN('insert', 'replace_new')
+    ORDER BY pkgnum ASC, history_date DESC"
+}
+
+=item status_query DATE
+
+Returns a statement for determining the status of packages on a particular 
+past date.
+
+=cut
+
+sub status_as_of_sql {
+  my $class = shift;
+  my $date = shift;
+
+  my @select = (
+    'h_cust_pkg.*',
+    FS::cust_pkg->active_sql() . ' AS is_active',
+    FS::cust_pkg->suspended_sql() . ' AS is_suspended',
+    FS::cust_pkg->cancelled_sql() . ' AS is_cancelled',
+  );
+  # foo_sql queries reference 'cust_pkg' in field names
+  foreach(@select) {
+    s/\bcust_pkg\b/h_cust_pkg/g;
+  }
+
+  return "SELECT DISTINCT ON(pkgnum) ".join(',', @select).
+         " FROM h_cust_pkg".
+         " WHERE history_date < $date AND history_action IN('insert','replace_new')".
+         " ORDER BY pkgnum ASC, history_date DESC";
+}
+
 =head1 BUGS
 
-churn_fromwhere_sql fails on MySQL.
+churn_fromwhere_sql and as_of_sql fail on MySQL.
 
 =head1 SEE ALSO