optimize Reports->Customers->List Customers, RT#20173
[freeside.git] / httemplate / search / unearned_detail.html
1 <& elements/search.html,
2   'title'       => emt("Unearned revenue - ".ucfirst($unearned_mode)) . ' (' .
3                    time2str('%b %d %Y', $unearned) . ')',
4   'name'        => emt('line items'),
5   'query'       => $query,
6   'count_query' => $count_query,
7   'count_addl'  => [ $money_char. '%.2f total',
8                      $money_char. '%.2f unearned revenue' 
9                    ],
10   'header'      => [ map( {emt $_} 
11     'Description',
12     'Unearned', # depends on mode
13     'Recurring charge', #recur - usage
14     'Owed', #recur - usage - credits - payments
15     'Paid', #payments
16     'Payment date', #of last payment
17     'Credit date', #of last credit
18     'Charge start',
19     'Charge end',
20     'Invoice',
21     'Date'
22     ),
23     FS::UI::Web::cust_header(),
24   ],
25   'fields'      => [
26     #Description
27     sub { $_[0]->pkgnum > 0
28       ? $_[0]->get('pkg')      # possibly use override.pkg
29       : $_[0]->get('itemdesc') # but i think this correct
30     },
31     #Unearned
32     money_sub('unearned_revenue'),
33     #Recurring charge
34     money_sub('recur_no_usage'),
35     #Owed
36     money_sub('owed_no_usage'),
37     #Paid
38     money_sub('paid_no_usage'),
39     #Payment date
40     date_sub('last_pay'),
41     #Credit date
42     date_sub('last_credit'),
43     #Charge start
44     date_sub('sdate'),
45     #Charge end, minus most of a day
46     date_sub('before_edate'),
47     #Invoice
48     'invnum',
49     #Invoice date
50     date_sub('_date'),
51     \&FS::UI::Web::cust_fields,
52   ],
53   'sort_fields' => [
54     'pkg',
55     # SQL expressions work as sort keys...
56     'unearned_revenue',
57     'recur_no_usage',
58     'owed_no_usage',
59     'paid_no_usage',
60     'last_pay',
61     'last_credit',
62     'sdate',
63     'edate',
64     'invnum',
65     '_date',
66   ],
67   'links'       => [
68     ('' x 9),
69     $ilink,
70     $ilink,
71     ( map { $_ ne 'Cust. Status' ? $clink : '' }
72       FS::UI::Web::cust_header()
73     ),
74   ],
75   'align' => 'lrrcrccrc'.  FS::UI::Web::cust_aligns(),
76   'color' => [ 
77     ('' x 11),
78     FS::UI::Web::cust_colors(),
79   ],
80   'style' => [ 
81     ('' x 11),
82     FS::UI::Web::cust_styles(),
83   ],
84 &>
85 <%init>
86
87 # Separated from cust_bill_pkg.cgi to simplify things.
88
89 die "access denied"
90   unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
91
92 my $conf = new FS::Conf;
93
94 my $unearned = '';
95 my $unearned_mode = '';
96 my $unearned_base = '';
97 my $unearned_sql = '';
98
99 my @select = ( 'cust_bill_pkg.*', 'cust_bill._date' );
100 my ($join_cust, $join_pkg ) = ('', '');
101
102 #here is the agent virtualization
103 my $agentnums_sql =
104   $FS::CurrentUser::CurrentUser->agentnums_sql( 'table' => 'cust_main' );
105
106 my @where = ( $agentnums_sql );
107
108 if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
109   push @where, "cust_main.agentnum = $1";
110 }
111
112 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
113
114 if ( $cgi->param('status') =~ /^([a-z]+)$/ ) {
115   push @where, FS::cust_main->cust_status_sql . " = '$1'";
116 }
117
118 push @where, "cust_bill._date >= $beginning",
119              "cust_bill._date <= $ending";
120
121 # cust_classnum (false laziness w/ elements/cust_main_dayranges.html, elements/cust_pay_or_refund.html, prepaid_income.html, cust_bill_pay.html, cust_bill_pkg.html, cust_bill_pkg_referral.html, cust_credit.html, cust_credit_refund.html, cust_main::Search::search_sql)
122 if ( grep { $_ eq 'cust_classnum' } $cgi->param ) {
123   my @classnums = grep /^\d*$/, $cgi->param('cust_classnum');
124   push @where, 'COALESCE( cust_main.classnum, 0) IN ( '.
125                    join(',', map { $_ || '0' } @classnums ).
126                ' )'
127     if @classnums;
128 }
129
130 # no pkgclass, no taxclass, no tax location...
131
132 # unearned revenue mode
133 $cgi->param('date') =~ /^(\d+)$/
134   or die "date required";
135
136 $unearned = $1;
137 $unearned_mode = $cgi->param('mode');
138
139 push @where, "cust_bill_pkg.sdate < $unearned",
140              "cust_bill_pkg.edate > $unearned",
141              "cust_bill_pkg.recur != 0",
142              "part_pkg.freq != '0'";
143
144 if ( !$cgi->param('include_monthly') ) {
145   push @where,
146              "part_pkg.freq != '1'",
147              "part_pkg.freq NOT LIKE '%h'",
148              "part_pkg.freq NOT LIKE '%d'",
149              "part_pkg.freq NOT LIKE '%w'";
150 }
151
152 my @opt = (
153   $unearned, #before this date
154   '',        #after this date
155   setuprecur => 'recur',
156   no_usage => 1
157 );
158
159 my $charged = FS::cust_bill_pkg->charged_sql(@opt);
160 push @select, "($charged) AS recur_no_usage";
161
162 my $owed_sql = FS::cust_bill_pkg->owed_sql(@opt);
163 push @select, "($owed_sql) AS owed_no_usage";
164
165 my $paid_sql = FS::cust_bill_pkg->paid_sql(@opt);
166 push @select, "$paid_sql AS paid_no_usage";
167
168 if ( $unearned_mode eq 'paid' ) {
169   # then use the amount paid, minus usage charges
170   $unearned_base = $paid_sql;
171 }
172 else {
173   # use the amount billed, minus usage charges and credits
174   $unearned_base = "( $charged - " . 
175                     FS::cust_bill_pkg->credited_sql(@opt) . ' )';
176 }
177 # whatever we're using as the base, only show rows where it's positive
178 push @where, "$unearned_base > 0";
179
180 my $edate_zero = midnight_sql('edate');
181 my $sdate_zero = midnight_sql('sdate');
182 # $unearned is one second before midnight on the date requested for the report.
183
184 # suppress partial days for more accounting-like behavior
185 my $period = "CAST( ($edate_zero - $sdate_zero) / 86400.0 AS DECIMAL(10,0) )";
186
187 my $remaining = "GREATEST( 
188   CAST( ($edate_zero - $unearned) / 86400.0 AS DECIMAL(10,0) ),
189   0)";
190 my $fraction = "$remaining / $period";
191
192 $unearned_sql = "CAST( $unearned_base * $fraction AS DECIMAL(10,2) )";
193 push @select, "$unearned_sql AS unearned_revenue";
194
195 # last payment/credit date
196 my %t = (pay => 'cust_bill_pay', credit => 'cust_credit_bill');
197 foreach my $x (qw(pay credit)) {
198   my $table     = "cust_$x";
199   my $link_bill = $t{$x};
200   my $link_pkg  = $link_bill.'_pkg';
201   my %pkey = map { $_ => dbdef->table($_)->primary_key }
202     ( $table, $link_bill );
203
204   my $last_date_sql = "SELECT MAX($table._date) 
205   FROM $table JOIN $link_bill USING ($pkey{$table})
206               JOIN $link_pkg  USING ($pkey{$link_bill})
207   WHERE $link_pkg.billpkgnum = cust_bill_pkg.billpkgnum 
208   AND $table._date <= $unearned";
209   push @select, "($last_date_sql) AS last_$x";
210 }
211
212 push @select, '(edate - 82799) AS before_edate';
213
214 #no itemdesc
215 #no tax report group kludge
216 #no tax exemption
217 #usage always excluded
218
219 # always 'nottax', not 'istax'
220 $join_cust =  '        JOIN cust_bill USING ( invnum ) '.
221                   FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg');
222
223 $join_pkg .=  ' LEFT JOIN cust_pkg USING ( pkgnum )
224                 LEFT JOIN part_pkg USING ( pkgpart )
225                 LEFT JOIN part_pkg AS override
226                   ON pkgpart_override = override.pkgpart ';
227
228 my $where = ' WHERE '. join(' AND ', @where);
229
230 my $count_query = "SELECT COUNT(DISTINCT billpkgnum), 
231   SUM( $unearned_base ), SUM( $unearned_sql )
232   FROM cust_bill_pkg $join_pkg $join_cust $where";
233
234 push @select, 'part_pkg.pkg',
235               'part_pkg.freq',
236               'cust_main.custnum',
237               FS::UI::Web::cust_sql_fields();
238
239 my $query = {
240   'table'     => 'cust_bill_pkg',
241   'addl_from' => "$join_pkg $join_cust",
242   'hashref'   => {},
243   'select'    => join(",\n", @select ),
244   'extra_sql' => $where,
245   'order_by'  => 'ORDER BY cust_bill._date, billpkgnum',
246 };
247
248 my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ];
249 my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
250
251 my $conf = new FS::Conf;
252 my $money_char = $conf->config('money_char') || '$';
253
254 sub money_sub {
255   $conf ||= new FS::Conf;
256   $money_char ||= $conf->config('money_char') || '$';
257   my $field = shift;
258   sub {
259     $money_char . sprintf('%.2f', $_[0]->get($field));
260   };
261 }
262
263 sub date_sub {
264   my $field = shift;
265   sub {
266     my $value = $_[0]->get($field);
267     $value ? time2str('%b %d %Y', $value) : '';
268   };
269 };
270
271 </%init>