make package churn report actually show package churn, #7990

5 files changed, 223 insertions, 37 deletions

diff --git a/httemplate/graph/cust_pkg.cgi b/httemplate/graph/cust_pkg.html
index cdd95e10a..3b6552ba8 100644
--- a/httemplate/graph/cust_pkg.cgi
+++ b/httemplate/graph/cust_pkg.html
@@ -7,9 +7,12 @@
   'links'         => \@links,
   'params'        => \@params,
   'agentnum'      => $agentnum,
-  'sprintf'       => '%u',
+  'sprintf'       => ( $normalize ? '%0.1f%%' : '%u'), 
+  'normalize'     => ( $normalize ? 0 : undef ),
   'disable_money' => 1,
   'remove_empty'  => (scalar(@group_keys) > 1 ? 1 : 0),
+  'nototal'       => 1,
+  'no_graph'      => [ 1, 0, 0, 0, 0 ], # don't graph 'active'
 &>
 <%init>
 
@@ -30,36 +33,29 @@ if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
 
 my $agentname = $agent ? $agent->agent.' ' : '';
 
-my @base_items = qw( setup_pkg susp_pkg cancel_pkg );
+my @base_items = qw( active_pkg setup_pkg susp_pkg unsusp_pkg cancel_pkg );
 
 my %base_labels = (
+  'active_pkg' => 'Active packages',
   'setup_pkg'  => 'New orders',
   'susp_pkg'   => 'Suspensions',
-#  'unsusp' => 'Unsuspensions',
+  'unsusp_pkg' => 'Unsuspensions',
   'cancel_pkg' => 'Cancellations',
 );
 
 my %base_colors = (
+  'active_pkg'  => '000000', #black
   'setup_pkg'   => '00cc00', #green
   'susp_pkg'    => 'ff9900', #yellow
-  #'unsusp'  => '', #light green?
-  'cancel_pkg'  => 'cc0000', #red ? 'ff0000'
+  'unsusp_pkg'  => '44ff44', #light green
+  'cancel_pkg'  => 'cc0000', #red 
 );
 
-my %base_links = (
-  'setup_pkg'  => { 'link' => "${p}search/cust_pkg.cgi?agentnum=$agentnum;",
-                    'fromparam' => 'setup_begin',
-                    'toparam'   => 'setup_end',
-                  },
-  'susp_pkg'   => { 'link' => "${p}search/cust_pkg.cgi?agentnum=$agentnum;",
-                    'fromparam' => 'susp_begin',
-                    'toparam'   => 'susp_end',
-                  },
-  'cancel_pkg' => { 'link' => "${p}search/cust_pkg.cgi?agentnum=$agentnum;",
-                    'fromparam' => 'cancel_begin',
-                    'toparam'   => 'cancel_end',
-                  },
-);
+my %base_links;
+foreach my $status (qw(active setup cancel susp unsusp)) {
+  $base_links{$status.'_pkg'} =
+    "${p}search/cust_pkg_churn.html?agentnum=$agentnum;status=$status;";
+}
 
 my %filter_params = (
   # not agentnum, that's elsewhere
@@ -76,7 +72,7 @@ foreach my $link (values %base_links) {
     if (ref($value)) {
       $value = join(',', @$value);
     }
-    $link->{'link'} .= "$key=$value;" if length($value);
+    $link .= "$key=$value;" if length($value);
   }
 }
 
@@ -143,9 +139,9 @@ if (scalar(@group_keys) > 1) {
       # and colors (?!)
       push @colors, $scheme->colorset->[$i]->[1];
       # and links...
-      my %this_link = %{ $base_links{$_} };
-      $this_link{link} .= "$breakdown=$key;";
-      push @links, \%this_link;
+      my $this_link = $base_links{$_};
+      $this_link .= "$breakdown=$key;";
+      push @links, $this_link;
       $i++;
     } #foreach (@base_items
     $hue += 35;
@@ -158,4 +154,6 @@ if (scalar(@group_keys) > 1) {
   @params = map { [ %filter_params ] } @base_items;
 }
 
+my $normalize = $cgi->param('normalize');
+
 </%init>
diff --git a/httemplate/graph/elements/monthly.html b/httemplate/graph/elements/monthly.html
index 939f18a35..4b988f166 100644
--- a/httemplate/graph/elements/monthly.html
+++ b/httemplate/graph/elements/monthly.html
@@ -125,6 +125,7 @@ my %reportopts = (
       'cust_classnum'=> $opt{'cust_classnum'},
       'remove_empty' => $opt{'remove_empty'},
       'doublemonths' => $opt{'doublemonths'},
+      'normalize'    => $opt{'normalize'},
 );
 
 warn Dumper({ 'REPORTOPTS' => \%reportopts }) if $opt{'debug'};
@@ -147,17 +148,12 @@ $col_labels = $data->{label} if $opt{'daily'};
 my @colors;
 my @graph_labels;
 my @no_graph;
-if ( $opt{'remove_empty'} ) {
+#if ( $opt{'remove_empty'} ) { # no, always do this
   # then filter out per-item things for collapsed rows
-  foreach my $i (@{ $data->{'indices'} }) {
-    push @colors,       $opt{'colors'}[$i];
-    push @graph_labels, $opt{'graph_labels'}[$i];
-    push @no_graph,     $opt{'no_graph'}[$i];
-  }
-} else {
-  @colors       = @{ $opt{'colors'} };
-  @graph_labels = @{ $opt{'graph_labels'} };
-  @no_graph     = @{ $opt{'no_graph'} || [] };
+foreach my $i (@{ $data->{'indices'} }) {
+  push @colors,       $opt{'colors'}[$i];
+  push @graph_labels, $opt{'graph_labels'}[$i];
+  push @no_graph,     $opt{'no_graph'}[$i];
 }
 
 my @links;
diff --git a/httemplate/graph/elements/report.html b/httemplate/graph/elements/report.html
index b3ba9ee22..cffc82816 100644
--- a/httemplate/graph/elements/report.html
+++ b/httemplate/graph/elements/report.html
@@ -108,11 +108,11 @@ any delimiter and linked from the elements in @data.
 %     foreach ( @{ shift( @data ) } ) {
 %       $total += $_;
 %       $bottom_total[$col-1] += $_ unless $opt{no_graph}[$row];
-%       $worksheet->write($row, $col++,  sprintf($sprintf, $_) );
+%       $worksheet->write_number($row, $col++,  sprintf($sprintf, $_) );
 %     }
 %     if ( !$opt{'nototal'} ) {
 %       $bottom_total[$col-1] += $total unless $opt{no_graph}[$row]; 
-%       $worksheet->write($row, $col++,  sprintf($sprintf, $total) );
+%       $worksheet->write_number($row, $col++,  sprintf($sprintf, $total) );
 %     } 
 %   }
 % 
@@ -120,7 +120,7 @@ any delimiter and linked from the elements in @data.
 %   if ( $opt{'bottom_total'} ) {
 %     $row++;
 %     $worksheet->write($row, $col++, 'Total');
-%     $worksheet->write($row, $col++, sprintf($sprintf, $_)) foreach @bottom_total;
+%     $worksheet->write_number($row, $col++, sprintf($sprintf, $_)) foreach @bottom_total;
 %   } 
 %   
 %   $workbook->close();# or die "Error creating .xls file: $!";
diff --git a/httemplate/graph/report_cust_pkg.html b/httemplate/graph/report_cust_pkg.html
index 1425ff089..0da5016a7 100644
--- a/httemplate/graph/report_cust_pkg.html
+++ b/httemplate/graph/report_cust_pkg.html
@@ -1,6 +1,6 @@
 <% include('/elements/header.html', 'Package Churn Summary' ) %>
 
-<FORM ACTION="cust_pkg.cgi" METHOD="GET">
+<FORM ACTION="cust_pkg.html" METHOD="GET">
 
 <TABLE BGCOLOR="#cccccc" CELLSPACING=0>
 
@@ -54,6 +54,12 @@
                      },
 &>
 
+<& /elements/tr-checkbox.html,
+  'field'         => 'normalize',
+  'value'         => 1,
+  'label'         => 'Show percentages'
+&>
+
 </TABLE>
 
 <BR><INPUT TYPE="submit" VALUE="Display">
diff --git a/httemplate/search/cust_pkg_churn.html b/httemplate/search/cust_pkg_churn.html
new file mode 100644
index 000000000..0ab99aa97
--- /dev/null
+++ b/httemplate/search/cust_pkg_churn.html
@@ -0,0 +1,186 @@
+<& elements/search.html,
+                  'title'       => $title,
+                  'name'        => 'packages',
+                  'query'       => $sql_query,
+                  'count_query' => $count_query,
+                  'header'      => [ emt('#'),
+                                     emt('Quantity'),
+                                     emt('Package'),
+                                     emt('Class'),
+                                     emt('Sales Person'),
+                                     emt('Ordered by'),
+                                     emt('Setup Fee'),
+                                     emt('Base Recur'),
+                                     emt('Freq.'),
+                                     emt('Setup'),
+                                     emt('Last bill'),
+                                     emt('Next bill'),
+                                     emt('Susp.'),
+                                     emt('Changed'),
+                                     emt('Cancel'),
+                                     #emt('Reason'), # hard to do this right
+                                     FS::UI::Web::cust_header(
+                                       $cgi->param('cust_fields')
+                                     ),
+                                     #emt('Services'), # even harder
+                                   ],
+                  'fields'      => [
+                    'pkgnum',
+                    'quantity',
+                    'pkg',
+                    'classname',
+                    'salesperson',
+                    'otaker',
+                    sub { sprintf( $money_char.'%.2f',
+                                   shift->part_pkg->option('setup_fee'),
+                                 );
+                        },
+                    sub { my $c = shift;
+                          sprintf( $money_char.'%.2f',
+                                   $c->part_pkg->base_recur($c)
+                                 );
+                        },
+                    sub { FS::part_pkg::freq_pretty(shift); },
+
+                    ( map { time_or_blank($_) }
+                      qw( setup last_bill bill susp change_date cancel ) ),
+
+                    \&FS::UI::Web::cust_fields,
+                  ],
+                  'sort_fields' => [
+                    'cust_pkg.pkgnum',
+                    ('') x 5, # can use as-is
+                    ('') x 3, # can't use at all
+                    # use the plain SQL column names
+                    qw( setup last_bill bill susp change_date cancel ),
+                    # cust_fields can take care of themselves
+                  ],
+                  'color' => [
+                    ('') x 15,
+                    FS::UI::Web::cust_colors(),
+                  ],
+                  'style' => [ ('') x 15,
+                               FS::UI::Web::cust_styles() ],
+                  'size'  => [ '', '', '', '', '-1' ],
+                  'align' => 'rrlcccrrlrrrrrr'. FS::UI::Web::cust_aligns(). 'r',
+                  'links' => [
+                    $link,
+                    $link,
+                    $link,
+                    ('') x 12,
+                    ( map { $_ ne 'Cust. Status' ? $clink : '' }
+                          FS::UI::Web::cust_header(
+                                                    $cgi->param('cust_fields')
+                                                  )
+                    ),
+                  ],
+&>
+<%once>
+my %title = (
+  'active' => 'Active packages as of ',
+  'setup'  => 'Packages started between ',
+  'cancel' => 'Packages canceled between ',
+  'susp'   => 'Packages suspended between ',
+  'unsusp' => 'Packages unsuspended between ',
+);
+</%once>
+<%init>
+
+my $curuser = $FS::CurrentUser::CurrentUser;
+
+die "access denied"
+  unless $curuser->access_right('List packages');
+
+my $conf = new FS::Conf;
+my $money_char = $conf->config('money_char') || '$';
+
+my %search_hash = ();
+
+# pass a very limited set of parameters through
+#scalars
+for (qw( agentnum zip )) 
+{
+  $search_hash{$_} = $cgi->param($_) if length($cgi->param($_));
+}
+
+#arrays / comma-separated lists
+for my $param (qw( pkgpart classnum refnum towernum )) {
+  my @values = map { split(',') } $cgi->param($param);
+  $search_hash{$param} = \@values if scalar(@values);
+}
+
+###
+# do not pass dates to FS::cust_pkg->search; use the special churn_fromwhere
+# logic.
+###
+
+my $pkg_query = FS::cust_pkg->search(\%search_hash);
+#warn Dumper $pkg_query;
+
+my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
+my $status = $cgi->param('status');
+
+my $title = emt($title{$status}) .
+            time2str('%b %o %Y', $beginning);
+if ($status ne 'active') {
+  $title .= emt(' to ') . time2str('%b %o %Y', $ending);
+}
+
+my ($from, @where) = FS::h_cust_pkg->churn_fromwhere_sql($status, $beginning, $ending);
+
+push @where, "freq != '0'";
+
+# split off the primary table name
+$from =~ s/^(\w+)(.*)$/$2/s;
+my $table = $1;
+
+# merge with $pkg_query
+$from .= ' ' . $pkg_query->{addl_from};
+
+my $extra_sql;
+if ($pkg_query->{extra_sql}) {
+  $extra_sql = $pkg_query->{extra_sql} . ' AND ';
+} else {
+  $extra_sql = 'WHERE ';
+}
+$extra_sql .= join(' AND ', @where);
+
+my $sql_query = {
+  'table'     => $table,
+  'addl_from' => $from,
+  'extra_sql' => $extra_sql,
+};
+warn (Dumper $sql_query) if $cgi->param('debug');
+
+my $count_query = "SELECT COUNT(*) FROM $table $from $extra_sql";
+
+my $show = $curuser->default_customer_view =~ /^(jumbo|packages)$/
+             ? ''
+             : ';show=packages';
+
+my $link = sub {
+  my $self = shift;
+  my $frag = 'cust_pkg'. $self->pkgnum; #hack for IE ignoring real #fragment
+  [ "${p}view/cust_main.cgi?custnum=".$self->custnum.
+                           "$show;fragment=$frag#cust_pkg",
+    'pkgnum'
+  ];
+};
+
+my $clink = sub {
+  my $cust_pkg = shift;
+  $cust_pkg->cust_main_custnum
+    ? [ "${p}view/cust_main.cgi?", 'custnum' ] 
+    : '';
+};
+
+sub time_or_blank {
+   my $column = shift;
+   return sub {
+     my $record = shift;
+     my $value = $record->get($column); #mmm closures
+     $value ? time2str('%b %d %Y', $value ) : '';
+   };
+}
+
+</%init>