diff options
author | Mark Wells <mark@freeside.biz> | 2014-11-13 13:10:59 -0800 |
---|---|---|
committer | Mark Wells <mark@freeside.biz> | 2014-11-13 13:12:00 -0800 |
commit | 395160f885a698dd557fd48d316c431789f4c47d (patch) | |
tree | b30c152f9028ce6b2fa03400f6af5833ee9d5ff2 /FS/FS/cust_main/Status.pm | |
parent | 57d7d006398f31bfc6672a7adeed7993dc8ee7ad (diff) |
rest of customer churn report, #30132
Diffstat (limited to 'FS/FS/cust_main/Status.pm')
-rw-r--r-- | FS/FS/cust_main/Status.pm | 36 |
1 files changed, 36 insertions, 0 deletions
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 |