X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2Fcust_main%2FStatus.pm;h=b5e8986ef86913890f905f200da435861309376e;hb=395160f885a698dd557fd48d316c431789f4c47d;hp=f84ff0f0edde3c3ae13c0aa37083cddbcc6f1040;hpb=63973c641c4be00765fa27e55c57cc5b9aa4da19;p=freeside.git

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