fix interaction of percentage discount and quantity, #19354, etc.
[freeside.git] / 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   'table'     => $table,
150   'addl_from' => $from,
151   'extra_sql' => $extra_sql,
152 };
153 warn (Dumper $sql_query) if $cgi->param('debug');
154
155 my $count_query = "SELECT COUNT(*) FROM $table $from $extra_sql";
156
157 my $show = $curuser->default_customer_view =~ /^(jumbo|packages)$/
158              ? ''
159              : ';show=packages';
160
161 my $link = sub {
162   my $self = shift;
163   my $frag = 'cust_pkg'. $self->pkgnum; #hack for IE ignoring real #fragment
164   [ "${p}view/cust_main.cgi?custnum=".$self->custnum.
165                            "$show;fragment=$frag#cust_pkg",
166     'pkgnum'
167   ];
168 };
169
170 my $clink = sub {
171   my $cust_pkg = shift;
172   $cust_pkg->cust_main_custnum
173     ? [ "${p}view/cust_main.cgi?", 'custnum' ] 
174     : '';
175 };
176
177 sub time_or_blank {
178    my $column = shift;
179    return sub {
180      my $record = shift;
181      my $value = $record->get($column); #mmm closures
182      $value ? time2str('%b %d %Y', $value ) : '';
183    };
184 }
185
186 </%init>