package churn report filtering by advertising source, tower, and zip code, #26999

2 files changed, 165 insertions, 26 deletions

diff --git a/httemplate/graph/cust_pkg.cgi b/httemplate/graph/cust_pkg.cgi
index 21ce07d21..cdd95e10a 100644
--- a/httemplate/graph/cust_pkg.cgi
+++ b/httemplate/graph/cust_pkg.cgi
@@ -1,20 +1,22 @@
-<% include('elements/monthly.html',
-                'title'         => $agentname. 'Package Churn',
-                'items'         => \@items,
-                'labels'        => \%label,
-                'graph_labels'  => \%graph_label,
-                'colors'        => \%color,
-                'links'         => \%link,
-                'agentnum'      => $agentnum,
-                'sprintf'       => '%u',
-                'disable_money' => 1,
-             )
-%>
+<& elements/monthly.html,
+  'title'         => $agentname. 'Package Churn',
+  'items'         => \@items,
+  'labels'        => \@labels,
+  'graph_labels'  => \@labels,
+  'colors'        => \@colors,
+  'links'         => \@links,
+  'params'        => \@params,
+  'agentnum'      => $agentnum,
+  'sprintf'       => '%u',
+  'disable_money' => 1,
+  'remove_empty'  => (scalar(@group_keys) > 1 ? 1 : 0),
+&>
 <%init>
 
 #XXX use a different ACL for package churn?
+my $curuser = $FS::CurrentUser::CurrentUser;
 die "access denied"
-  unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
+  unless $curuser->access_right('Financial reports');
 
 #false laziness w/money_time.cgi, cust_bill_pkg.cgi
 
@@ -28,24 +30,23 @@ if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
 
 my $agentname = $agent ? $agent->agent.' ' : '';
 
-my @items = qw( setup_pkg susp_pkg cancel_pkg );
+my @base_items = qw( setup_pkg susp_pkg cancel_pkg );
 
-my %label = (
+my %base_labels = (
   'setup_pkg'  => 'New orders',
   'susp_pkg'   => 'Suspensions',
 #  'unsusp' => 'Unsuspensions',
   'cancel_pkg' => 'Cancellations',
 );
-my %graph_label = %label;
 
-my %color = (
+my %base_colors = (
   'setup_pkg'   => '00cc00', #green
   'susp_pkg'    => 'ff9900', #yellow
   #'unsusp'  => '', #light green?
   'cancel_pkg'  => 'cc0000', #red ? 'ff0000'
 );
 
-my %link = (
+my %base_links = (
   'setup_pkg'  => { 'link' => "${p}search/cust_pkg.cgi?agentnum=$agentnum;",
                     'fromparam' => 'setup_begin',
                     'toparam'   => 'setup_end',
@@ -60,4 +61,101 @@ my %link = (
                   },
 );
 
+my %filter_params = (
+  # not agentnum, that's elsewhere
+  'refnum'      => [ $cgi->param('refnum') ],
+  'classnum'    => [ $cgi->param('classnum') ],
+  'towernum'    => [ $cgi->param('towernum') ],
+);
+if ( $cgi->param('zip') =~ /^(\w+)/ ) {
+  $filter_params{zip} = $1;
+}
+foreach my $link (values %base_links) {
+  foreach my $key (keys(%filter_params)) {
+    my $value = $filter_params{$key};
+    if (ref($value)) {
+      $value = join(',', @$value);
+    }
+    $link->{'link'} .= "$key=$value;" if length($value);
+  }
+}
+
+
+# In order to keep this from being the same trainwreck as cust_bill_pkg.cgi,
+# we allow ONE breakdown axis, besides the setup/susp/cancel inherent in 
+# the report.
+
+my $breakdown = $cgi->param('breakdown_by');
+my ($name_col, $table);
+if ($breakdown eq 'classnum') {
+  $table = 'pkg_class';
+  $name_col = 'classname';
+} elsif ($breakdown eq 'refnum') {
+  $table = 'part_referral';
+  $name_col = 'referral';
+} elsif ($breakdown eq 'towernum') {
+  $table = 'tower';
+  $name_col = 'towername';
+} elsif ($breakdown) {
+  die "unknown breakdown column '$breakdown'\n";
+}
+
+my @group_keys;
+my @group_labels;
+if ( $table ) {
+  my @groups;
+  if ( $cgi->param($breakdown) ) {
+    foreach my $key ($cgi->param($breakdown)) {
+      next if $key =~ /\D/;
+      push @groups, qsearch( $table, { $breakdown => $key });
+    }
+  } else {
+    @groups = qsearch( $table );
+  }
+  foreach (@groups) {
+    push @group_keys, $_->get($breakdown);
+    push @group_labels, $_->get($name_col);
+  }
+}
+
+my (@items, @labels, @colors, @links, @params);
+if (scalar(@group_keys) > 1) {
+  my $hue = 180;
+  foreach my $key (@group_keys) {
+    # this gives a decent level of contrast as long as there aren't too many
+    # result sets
+    my $scheme = Color::Scheme->new
+      ->scheme('triade')
+      ->from_hue($hue)
+      ->distance(0.5);
+    my $label = shift @group_labels;
+    my $i = 0; # item index
+    foreach (@base_items) {
+      # append the item
+      push @items, $_;
+      # and its parameters
+      push @params, [
+        %filter_params,
+        $breakdown => $key
+      ];
+      # and a label prefixed with the group label
+      push @labels, "$label - $base_labels{$_}";
+      # and colors (?!)
+      push @colors, $scheme->colorset->[$i]->[1];
+      # and links...
+      my %this_link = %{ $base_links{$_} };
+      $this_link{link} .= "$breakdown=$key;";
+      push @links, \%this_link;
+      $i++;
+    } #foreach (@base_items
+    $hue += 35;
+  } # foreach @group_keys
+} else {
+  @items = @base_items;
+  @labels = @base_labels{@base_items};
+  @colors = @base_colors{@base_items};
+  @links = @base_links{@base_items};
+  @params = map { [ %filter_params ] } @base_items;
+}
+
 </%init>
diff --git a/httemplate/graph/report_cust_pkg.html b/httemplate/graph/report_cust_pkg.html
index 22ccd5def..1425ff089 100644
--- a/httemplate/graph/report_cust_pkg.html
+++ b/httemplate/graph/report_cust_pkg.html
@@ -2,16 +2,57 @@
 
 <FORM ACTION="cust_pkg.cgi" METHOD="GET">
 
-<TABLE>
+<TABLE BGCOLOR="#cccccc" CELLSPACING=0>
 
-<% include('/elements/tr-select-from_to.html' ) %>
+<& /elements/tr-select-from_to.html &>
 
-<% include('/elements/tr-select-agent.html',
-             'curr_value'    => scalar($cgi->param('agentnum')),
-             'label'         => 'For agent: ',
-             'disable_empty' => 0,
-          )
-%>
+<& /elements/tr-select-agent.html,
+  'curr_value'    => scalar($cgi->param('agentnum')),
+  'label'         => 'For agent: ',
+  'disable_empty' => 0,
+&>
+
+<& /elements/tr-select-pkg_class.html,
+  'multiple'      => 1,
+  'pre_options'   => [ '0' => '(empty class)' ],
+  'disable_empty' => 1,
+&>
+
+<& /elements/tr-select-part_referral.html,
+  'multiple'      => 1,
+  'disable_empty' => 1,
+&>
+
+<& /elements/tr-select-table.html,
+  'label'         => 'Tower',
+  'table'         => 'tower',
+  'field'         => 'towernum',
+  'name_col'      => 'towername',
+  'multiple'      => 1,
+  'pre_options'   => [ 0 => '(none)' ],
+  'size'          => 8,
+  'hashref'       => { disabled => '' },
+&>
+
+<& /elements/tr-input-text.html,
+  'field'         => 'zip',
+  'label'         => 'Zip',
+&>
+
+<& /elements/tablebreak-tr-title.html,
+  'value' => mt('Display options')
+&>
+
+<& /elements/tr-select.html,
+  'field'         => 'breakdown_by',
+  'label'         => 'Breakdown by: ',
+  'options'       => [ '', 'classnum', 'refnum', 'towernum' ],
+  'labels'        => { ''         => '(none)',
+                       'classnum' => 'Package class',
+                       'refnum'   => 'Advertising source',
+                       'towernum' => 'Tower',
+                     },
+&>
 
 </TABLE>