From 395160f885a698dd557fd48d316c431789f4c47d Mon Sep 17 00:00:00 2001 From: Mark Wells Date: Thu, 13 Nov 2014 13:10:59 -0800 Subject: [PATCH] rest of customer churn report, #30132 --- FS/FS/Report/Table.pm | 88 +++++++++++++++++++++++++++++++++++++++++++++-- FS/FS/cust_main/Status.pm | 36 +++++++++++++++++++ FS/FS/h_cust_pkg.pm | 50 ++++++++++++++++++++++++++- 3 files changed, 171 insertions(+), 3 deletions(-) diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm index 3a4a1695d..934287a15 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 f84ff0f0e..b5e8986ef 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 0c3db107f..423b44250 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 -- 2.11.0