customer churn report, #30132

diff --git a/httemplate/elements/menu.html b/httemplate/elements/menu.html
index d349045..f26882b 100644 (file)
--- a/httemplate/elements/menu.html
+++ b/httemplate/elements/menu.html
@@ -114,6 +114,7 @@ $report_customers{'List customers'} = [ \%report_customers_lists, 'List customer
   if $curuser->access_right('List all customers');
 $report_customers{'Zip code distribution'}     = [ $fsurl. 'search/report_cust_main-zip.html', 'Zip codes by number of customers' ];
 $report_customers{'Customer signup report'}    = [ $fsurl. 'graph/report_cust_signup.html',    'New customer signups by date' ];
+$report_customers{'Customer churn report'} =  [ $fsurl.'graph/report_cust_churn.html', 'New customers, suspensions, and cancellations summary' ];
 $report_customers{'Signup date report'}        = [ $fsurl. 'graph/report_signupdate.html',     'Signup date report (by date of signup)' ];
 $report_customers{'Advanced customer reports'} = [ $fsurl. 'search/report_cust_main.html',     'by status, signup date, agent, etc.' ]
   if $curuser->access_right('Advanced customer search');

diff --git a/httemplate/graph/cust_churn.html b/httemplate/graph/cust_churn.html
new file mode 100644 (file)
index 0000000..07193c7
--- /dev/null
+++ b/httemplate/graph/cust_churn.html
@@ -0,0 +1,70 @@
+<& elements/monthly.html,
+  'title'         => $agentname. 'Customer Churn',
+  'items'         => \@items,
+  'labels'        => \@labels,
+  'graph_labels'  => \@labels,
+  'colors'        => \@colors,
+  'links'         => \@links,
+  #'params'        => \@params,
+  'agentnum'      => $agentnum,
+  'sprintf'       => ( $normalize ? '%0.1f%%' : '%u'), 
+  'normalize'     => ( $normalize ? 0 : undef ),
+  'disable_money' => 1,
+  'remove_empty'  => 0,
+  'nototal'       => 1,
+  'no_graph'      => [ 1, 0, 0, 0, 0 ], # don't graph 'active'
+&>
+<%init>
+
+my $curuser = $FS::CurrentUser::CurrentUser;
+die "access denied"
+  unless $curuser->access_right('List customers');
+
+my( $agentnum, $agent ) = ('', '');
+if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
+  $agentnum = $1;
+  $agent = qsearchs('agent', { 'agentnum' => $agentnum } );
+} else {
+  die "agentnum required"; # UI prevents this
+}
+
+my $agentname = $agent ? $agent->agent.' ' : '';
+
+my @base_items = qw(active_cust    
+                    started_cust   
+                    suspended_cust 
+                    resumed_cust   
+                    cancelled_cust  );
+
+my %base_labels = (
+  active_cust    => 'Active customers',
+  started_cust   => 'New',
+  suspended_cust => 'Suspended',
+  resumed_cust   => 'Resumed',
+  cancelled_cust => 'Cancelled',
+);
+
+my %base_colors = (
+  active_cust     => '000000', #black
+  started_cust    => '00cc00', #green
+  suspended_cust  => 'ff9900', #yellow
+  resumed_cust    => '4444ff', #light blue for some reason
+  cancelled_cust  => 'cc0000', #red 
+);
+
+my %base_links;
+foreach my $status (qw(active started suspended resumed cancelled)) {
+  $base_links{$status.'_cust'} =
+    "${p}search/cust_main_churn.html?agentnum=$agentnum;status=$status;";
+}
+
+# indirection in case at some point we need to add breakdown options
+my (@items, @labels, @colors, @links, @params);
+@items = @base_items;
+@labels = @base_labels{@base_items};
+@colors = @base_colors{@base_items};
+@links = @base_links{@base_items};
+
+my $normalize = $cgi->param('normalize');
+
+</%init>

diff --git a/httemplate/graph/report_cust_churn.html b/httemplate/graph/report_cust_churn.html
new file mode 100644 (file)
index 0000000..3f94277
--- /dev/null
+++ b/httemplate/graph/report_cust_churn.html
@@ -0,0 +1,32 @@
+<% include('/elements/header.html', 'Customer Churn Summary' ) %>
+
+<FORM ACTION="cust_churn.html" METHOD="GET">
+
+<TABLE BGCOLOR="#cccccc" CELLSPACING=0>
+
+<& /elements/tr-select-from_to.html &>
+
+<& /elements/tr-select-agent.html,
+  'curr_value'    => scalar($cgi->param('agentnum')),
+  'label'         => 'For agent: ',
+  'disable_empty' => 1,
+&>
+
+<& /elements/tr-checkbox.html,
+  'field'         => 'normalize',
+  'value'         => 1,
+  'label'         => 'Show percentages'
+&>
+
+</TABLE>
+
+<BR><INPUT TYPE="submit" VALUE="Display">
+</FORM>
+
+<% include('/elements/footer.html') %>
+<%init>
+
+die "access denied"
+  unless $FS::CurrentUser::CurrentUser->access_right('List customers');
+
+</%init>

diff --git a/httemplate/search/cust_main_churn.html b/httemplate/search/cust_main_churn.html
new file mode 100644 (file)
index 0000000..59f92cb
--- /dev/null
+++ b/httemplate/search/cust_main_churn.html
@@ -0,0 +1,120 @@
+<& elements/search.html,
+  'title'       => $title,
+  'name'        => 'customers',
+  'query'       => $query,
+  'count_query' => $count_query,
+  'header'      => [ emt('#'),
+                     FS::UI::Web::cust_header(
+                       $cgi->param('cust_fields')
+                     ),
+                     { label => "Packages on $start_date", colspan => 3 },
+                     '',
+                     '',
+                     { label => "Packages on $end_date", colspan => 3 },
+                     '',
+                     '',
+                   ],
+  'header2'     => [ '',
+                     map({ '' } (FS::UI::Web::cust_header())),
+                     'Active',
+                     'Suspended',
+                     'Cancelled',
+                     'Active',
+                     'Suspended',
+                     'Cancelled',
+                   ],
+  'fields'      => [
+    'custnum',
+    \&FS::UI::Web::cust_fields,
+    's_active',
+    's_suspended',
+    's_cancelled',
+    'e_active',
+    'e_suspended',
+    'e_cancelled',
+  ],
+  'color' => [
+    '',
+    FS::UI::Web::cust_colors(),
+    # package colors here
+    '00CC00',
+    'FF9900',
+    'FF0000',
+
+    '00CC00',
+    'FF9900',
+    'FF0000',
+  ],
+  'style' => [ '',
+               FS::UI::Web::cust_styles(),
+               '' ],
+  'align' => 'r'. FS::UI::Web::cust_aligns(). 'rrrrrr',
+  'links' => [
+    '',
+    ( map { $_ ne 'Cust. Status' ? $clink : '' }
+          FS::UI::Web::cust_header(
+                                    $cgi->param('cust_fields')
+                                  )
+    ),
+    '',
+  ],
+&>
+<%init>
+
+my $curuser = $FS::CurrentUser::CurrentUser;
+
+die "access denied"
+  unless $curuser->access_right('List customers');
+
+my($speriod, $eperiod) = FS::UI::Web::parse_beginning_ending($cgi);
+my $start_date = time2str('%b %o, %Y', $speriod);
+my $end_date = time2str('%b %o, %Y', $eperiod);
+
+my $agentnum;
+if ($cgi->param('agentnum') =~ /^(\d+)$/) {
+  $agentnum = $1;
+}
+
+# can't use this directly as it doesn't have any cust_main fields.
+my $churn = FS::cust_main::Status->churn_sql($speriod, $eperiod);
+
+my $query = {
+  'table'     => 'cust_main',
+  'select'    => 'cust_main.*, churn.*',
+  'addl_from' => " JOIN ($churn) AS churn USING (custnum) ",
+};
+my $count_query = "SELECT COUNT(*) FROM cust_main JOIN ($churn) AS churn USING (custnum)";
+my @where;
+
+my $status = $cgi->param('status');
+my $title;
+if ( $status eq 'active' ) {
+  $title = "Customers active on $start_date";
+  push @where, "s_active > 0";
+} elsif ( $status eq 'started' ) {
+  $title = "Customers starting service, $start_date - $end_date";
+  push @where, "s_active = 0 and e_active > 0";
+} elsif ( $status eq 'suspended' ) {
+  $title = "Customers suspended, $start_date - $end_date";
+  push @where, "s_active > 0 and e_active = 0 and e_suspended > 0";
+} elsif ( $status eq 'resumed' ) {
+  $title = "Customers resuming service, $start_date - $end_date";
+  push @where, "s_active = 0 and s_suspended > 0 and e_active > 0";
+} elsif ( $status eq 'cancelled' ) {
+  $title = "Customers cancelled, $start_date - $end_date";
+  push @where, "s_active > 0 and e_active = 0 and e_suspended = 0";
+}
+
+if ($agentnum) {
+  push @where, "agentnum = $agentnum";
+}
+
+if ( @where ) {
+  my $where = " WHERE ". join(' AND ', @where);
+  $query->{extra_sql} = $where;
+  $count_query .= $where;
+}
+
+my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
+
+</%init>