httemplate/search/cust_pkg_churn.html

   1 <& elements/search.html,
   2                   'title'       => $title,
   3                   'name'        => 'packages',
   4                   'query'       => $sql_query,
   5                   'count_query' => $count_query,
   6                   'header'      => [ emt('#'),
   7                                      emt('Quantity'),
   8                                      emt('Package'),
   9                                      emt('Class'),
  10                                      emt('Sales Person'),
  11                                      emt('Ordered by'),
  12                                      emt('Setup Fee'),
  13                                      emt('Base Recur'),
  14                                      emt('Freq.'),
  15                                      emt('Setup'),
  16                                      emt('Last bill'),
  17                                      emt('Next bill'),
  18                                      emt('Susp.'),
  19                                      emt('Changed'),
  20                                      emt('Cancel'),
  21                                      #emt('Reason'), # hard to do this right
  22                                      FS::UI::Web::cust_header(
  23                                        $cgi->param('cust_fields')
  24                                      ),
  25                                      #emt('Services'), # even harder
  26                                    ],
  27                   'fields'      => [
  28                     'pkgnum',
  29                     'quantity',
  30                     'pkg',
  31                     'classname',
  32                     'salesperson',
  33                     'otaker',
  34                     sub { sprintf( $money_char.'%.2f',
  35                                    shift->part_pkg->option('setup_fee'),
  36                                  );
  37                         },
  38                     sub { my $c = shift;
  39                           sprintf( $money_char.'%.2f',
  40                                    $c->part_pkg->base_recur($c)
  41                                  );
  42                         },
  43                     sub { FS::part_pkg::freq_pretty(shift); },
  44
  45                     ( map { time_or_blank($_) }
  46                       qw( setup last_bill bill susp change_date cancel ) ),
  47
  48                     \&FS::UI::Web::cust_fields,
  49                   ],
  50                   'sort_fields' => [
  51                     'cust_pkg.pkgnum',
  52                     ('') x 5, # can use as-is
  53                     ('') x 3, # can't use at all
  54                     # use the plain SQL column names
  55                     qw( setup last_bill bill susp change_date cancel ),
  56                     # cust_fields can take care of themselves
  57                   ],
  58                   'color' => [
  59                     ('') x 15,
  60                     FS::UI::Web::cust_colors(),
  61                   ],
  62                   'style' => [ ('') x 15,
  63                                FS::UI::Web::cust_styles() ],
  64                   'size'  => [ '', '', '', '', '-1' ],
  65                   'align' => 'rrlcccrrlrrrrrr'. FS::UI::Web::cust_aligns(). 'r',
  66                   'links' => [
  67                     $link,
  68                     $link,
  69                     $link,
  70                     ('') x 12,
  71                     ( map { $_ ne 'Cust. Status' ? $clink : '' }
  72                           FS::UI::Web::cust_header(
  73                                                     $cgi->param('cust_fields')
  74                                                   )
  75                     ),
  76                   ],
  77 &>
  78 <%once>
  79 my %title = (
  80   'active' => 'Active packages as of ',
  81   'setup'  => 'Packages started between ',
  82   'cancel' => 'Packages canceled between ',
  83   'susp'   => 'Packages suspended between ',
  84   'unsusp' => 'Packages unsuspended between ',
  85 );
  86 </%once>
  87 <%init>
  88
  89 my $curuser = $FS::CurrentUser::CurrentUser;
  90
  91 die "access denied"
  92   unless $curuser->access_right('List packages');
  93
  94 my $conf = new FS::Conf;
  95 my $money_char = $conf->config('money_char') || '$';
  96
  97 my %search_hash = ();
  98
  99 # pass a very limited set of parameters through
 100 #scalars
 101 for (qw( agentnum zip ))
 102 {
 103   $search_hash{$_} = $cgi->param($_) if length($cgi->param($_));
 104 }
 105
 106 #arrays / comma-separated lists
 107 for my $param (qw( pkgpart classnum refnum towernum )) {
 108   my @values = map { split(',') } $cgi->param($param);
 109   $search_hash{$param} = \@values if scalar(@values);
 110 }
 111
 112 ###
 113 # do not pass dates to FS::cust_pkg->search; use the special churn_fromwhere
 114 # logic.
 115 ###
 116
 117 my $pkg_query = FS::cust_pkg->search(\%search_hash);
 118 #warn Dumper $pkg_query;
 119
 120 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
 121 my $status = $cgi->param('status');
 122
 123 my $title = emt($title{$status}) .
 124             time2str('%b %o %Y', $beginning);
 125 if ($status ne 'active') {
 126   $title .= emt(' to ') . time2str('%b %o %Y', $ending);
 127 }
 128
 129 my ($from, @where) = FS::h_cust_pkg->churn_fromwhere_sql($status, $beginning, $ending);
 130
 131 push @where, "freq != '0'";
 132
 133 # split off the primary table name
 134 $from =~ s/^(\w+)(.*)$/$2/s;
 135 my $table = $1;
 136
 137 # merge with $pkg_query
 138 $from .= ' ' . $pkg_query->{addl_from};
 139
 140 my $extra_sql;
 141 if ($pkg_query->{extra_sql}) {
 142   $extra_sql = $pkg_query->{extra_sql} . ' AND ';
 143 } else {
 144   $extra_sql = 'WHERE ';
 145 }
 146 $extra_sql .= join(' AND ', @where);
 147
 148 my $sql_query = {
 149   'select'    => $pkg_query->{select},
 150   'table'     => $table,
 151   'addl_from' => $from,
 152   'extra_sql' => $extra_sql,
 153 };
 154 warn (Dumper $sql_query) if $cgi->param('debug');
 155
 156 my $count_query = "SELECT COUNT(*) FROM $table $from $extra_sql";
 157
 158 my $show = $curuser->default_customer_view =~ /^(jumbo|packages)$/
 159              ? ''
 160              : ';show=packages';
 161
 162 my $link = sub {
 163   my $self = shift;
 164   my $frag = 'cust_pkg'. $self->pkgnum; #hack for IE ignoring real #fragment
 165   [ "${p}view/cust_main.cgi?custnum=".$self->custnum.
 166                            "$show;fragment=$frag#cust_pkg",
 167     'pkgnum'
 168   ];
 169 };
 170
 171 my $clink = sub {
 172   my $cust_pkg = shift;
 173   $cust_pkg->cust_main_custnum
 174     ? [ "${p}view/cust_main.cgi?", 'custnum' ]
 175     : '';
 176 };
 177
 178 sub time_or_blank {
 179    my $column = shift;
 180    return sub {
 181      my $record = shift;
 182      my $value = $record->get($column); #mmm closures
 183      $value ? time2str('%b %d %Y', $value ) : '';
 184    };
 185 }
 186
 187 </%init>