make package churn report actually show package churn, #7990

1 files changed, 186 insertions, 0 deletions

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>