From 0a5b2467d57cf94a2786bb76a106b14596e30916 Mon Sep 17 00:00:00 2001 From: Mark Wells Date: Wed, 12 Nov 2014 17:18:18 -0800 Subject: [PATCH] customer churn report, #30132 --- httemplate/elements/menu.html | 1 + httemplate/graph/cust_churn.html | 70 +++++++++++++++++++ httemplate/graph/report_cust_churn.html | 32 +++++++++ httemplate/search/cust_main_churn.html | 120 ++++++++++++++++++++++++++++++++ 4 files changed, 223 insertions(+) create mode 100644 httemplate/graph/cust_churn.html create mode 100644 httemplate/graph/report_cust_churn.html create mode 100644 httemplate/search/cust_main_churn.html diff --git a/httemplate/elements/menu.html b/httemplate/elements/menu.html index d34904592..f26882b1f 100644 --- 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 index 000000000..07193c776 --- /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'); + + diff --git a/httemplate/graph/report_cust_churn.html b/httemplate/graph/report_cust_churn.html new file mode 100644 index 000000000..3f942777f --- /dev/null +++ b/httemplate/graph/report_cust_churn.html @@ -0,0 +1,32 @@ +<% include('/elements/header.html', 'Customer Churn Summary' ) %> + +
+ + + +<& /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' +&> + +
+ +
+
+ +<% include('/elements/footer.html') %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('List customers'); + + diff --git a/httemplate/search/cust_main_churn.html b/httemplate/search/cust_main_churn.html new file mode 100644 index 000000000..59f92cb6f --- /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' ]; + + -- 2.11.0