}
-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
+##### 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:
+
+- refnum: Limit to customers with this advertising source.
+- classnum: Limit to packages with this class.
+- towernum: Limit to packages that have a broadband service with this tower.
+- zip: Limit to packages with this service location zip code.
+
+Except for zip, any of these can be an arrayref to allow multiple values for
+the field.
+
+=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
+are as for active_pkg.
+
+=cut
+
+sub active_pkg {
+ my $self = shift;
+ $self->churn_pkg('active', @_);
+}
+
+sub setup_pkg {
+ my $self = shift;
+ $self->churn_pkg('setup', @_);
+}
+
+sub cancel_pkg {
+ my $self = shift;
+ $self->churn_pkg('cancel', @_);
+}
+
+sub susp_pkg {
+ my $self = shift;
+ $self->churn_pkg('susp', @_);
+}
+
+sub unsusp_pkg {
+ my $self = shift;
+ $self->churn_pkg('unsusp', @_);
+}
+
+sub churn_pkg {
+ my $self = shift;
+ 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);
+}
+
+sub pkg_where {
+ my $self = shift;
+ my %opt = @_;
my @where = (
- $self->in_time_period_and_agent($speriod,
- $eperiod,
- $agentnum,
- "cust_pkg.$field",
- ),
+ "part_pkg.freq != '0'",
$self->with_refnum(%opt),
$self->with_towernum(%opt),
$self->with_zip(%opt),
- # can't use with_classnum here...
);
+ if ($opt{agentnum} =~ /^(\d+)$/) {
+ push @where, "cust_main.agentnum = $1";
+ }
if ($opt{classnum}) {
my $classnum = $opt{classnum};
$classnum = [ $classnum ] if !ref($classnum);
my $in = 'IN ('. join(',', @$classnum). ')';
push @where, "COALESCE(part_pkg.classnum, 0) $in" if scalar @$classnum;
}
-
- ' WHERE ' . join(' AND ', grep $_, @where);
+ @where;
}
-=item setup_pkg: The number of packages with setup dates in the period.
+##### end of package churn report stuff #####
-This excludes packages created by package changes. Options:
+##### customer churn report #####
-- refnum: Limit to customers with this advertising source.
-- classnum: Limit to packages with this class.
-- towernum: Limit to packages that have a broadband service with this tower.
-- zip: Limit to packages with this service location zip code.
+=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.
-Except for zip, any of these can be an arrayref to allow multiple values for
-the field.
+=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 susp_pkg: The number of suspended packages that were last suspended
-in the period. Options are as for setup_pkg.
+=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 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.
+=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 setup_pkg {
+sub active_cust {
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->scalar_sql($sql);
+ $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 susp_pkg {
- # number of currently suspended packages that were suspended in the period
+sub churn_cust {
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', @_);
+ my ( $speriod ) = @_;
- $self->scalar_sql($sql);
+ # run one query for each interval
+ return $self->{_interval}{$speriod} ||= $self->calculate_churn_cust(@_);
}
-sub cancel_pkg {
- # number of packages canceled in the period and not changed to another
- # package
+sub calculate_churn_cust {
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 ($speriod, $eperiod, $agentnum, %opt) = @_;
- $self->scalar_sql($sql);
+ 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;
}
-#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 in_time_period_and_agent {
my( $self, $speriod, $eperiod, $agentnum ) = splice(@_, 0, 4);
my $col = @_ ? shift() : '_date';
projects / freeside.git / blobdiff