1 <& elements/search.html,
2 'title' => emt("Unearned revenue - ".ucfirst($unearned_mode)) . ' (' .
3 time2str('%b %d %Y', $unearned) . ')',
4 'name' => emt('line items'),
6 'count_query' => $count_query,
7 'count_addl' => [ $money_char. '%.2f total',
8 $money_char. '%.2f unearned revenue'
10 'header' => [ map( {emt $_}
12 'Unearned', # depends on mode
13 'Recurring charge', #recur - usage
14 'Owed', #recur - usage - credits - payments
16 'Payment date', #of last payment
17 'Credit date', #of last credit
23 FS::UI::Web::cust_header(),
27 sub { $_[0]->pkgnum > 0
28 ? $_[0]->get('pkg') # possibly use override.pkg
29 : $_[0]->get('itemdesc') # but i think this correct
32 money_sub('unearned_revenue'),
34 money_sub('recur_no_usage'),
36 money_sub('owed_no_usage'),
38 money_sub('paid_no_usage'),
42 date_sub('last_credit'),
45 #Charge end, minus most of a day
46 date_sub('before_edate'),
51 \&FS::UI::Web::cust_fields,
55 # SQL expressions work as sort keys...
71 ( map { $_ ne 'Cust. Status' ? $clink : '' }
72 FS::UI::Web::cust_header()
75 'align' => 'lrrcrccrc'. FS::UI::Web::cust_aligns(),
78 FS::UI::Web::cust_colors(),
82 FS::UI::Web::cust_styles(),
87 # Separated from cust_bill_pkg.cgi to simplify things.
90 unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
92 my $conf = new FS::Conf;
95 my $unearned_mode = '';
96 my $unearned_base = '';
97 my $unearned_sql = '';
99 my @select = ( 'cust_bill_pkg.*', 'cust_bill._date' );
100 my ($join_cust, $join_pkg ) = ('', '');
102 #here is the agent virtualization
104 $FS::CurrentUser::CurrentUser->agentnums_sql( 'table' => 'cust_main' );
106 my @where = ( $agentnums_sql );
108 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
110 if ( $cgi->param('status') =~ /^([a-z]+)$/ ) {
111 push @where, FS::cust_main->cust_status_sql . " = '$1'";
114 push @where, "cust_bill._date >= $beginning",
115 "cust_bill._date <= $ending";
117 # 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)
118 if ( grep { $_ eq 'cust_classnum' } $cgi->param ) {
119 my @classnums = grep /^\d*$/, $cgi->param('cust_classnum');
120 push @where, 'COALESCE( cust_main.classnum, 0) IN ( '.
121 join(',', map { $_ || '0' } @classnums ).
126 # no pkgclass, no taxclass, no tax location...
128 # unearned revenue mode
129 $cgi->param('date') =~ /^(\d+)$/
130 or die "date required";
133 $unearned_mode = $cgi->param('mode');
135 push @where, "cust_bill_pkg.sdate < $unearned",
136 "cust_bill_pkg.edate > $unearned",
137 "cust_bill_pkg.recur != 0",
138 "part_pkg.freq != '0'";
140 if ( !$cgi->param('include_monthly') ) {
142 "part_pkg.freq != '1'",
143 "part_pkg.freq NOT LIKE '%h'",
144 "part_pkg.freq NOT LIKE '%d'",
145 "part_pkg.freq NOT LIKE '%w'";
149 $unearned, #before this date
151 setuprecur => 'recur',
155 my $charged = FS::cust_bill_pkg->charged_sql(@opt);
156 push @select, "($charged) AS recur_no_usage";
158 my $owed_sql = FS::cust_bill_pkg->owed_sql(@opt);
159 push @select, "($owed_sql) AS owed_no_usage";
161 my $paid_sql = FS::cust_bill_pkg->paid_sql(@opt);
162 push @select, "$paid_sql AS paid_no_usage";
164 if ( $unearned_mode eq 'paid' ) {
165 # then use the amount paid, minus usage charges
166 $unearned_base = $paid_sql;
169 # use the amount billed, minus usage charges and credits
170 $unearned_base = "( $charged - " .
171 FS::cust_bill_pkg->credited_sql(@opt) . ' )';
173 # whatever we're using as the base, only show rows where it's positive
174 push @where, "$unearned_base > 0";
176 my $edate_zero = midnight_sql('edate');
177 my $sdate_zero = midnight_sql('sdate');
178 # $unearned is one second before midnight on the date requested for the report.
180 # suppress partial days for more accounting-like behavior
181 my $period = "CAST( ($edate_zero - $sdate_zero) / 86400.0 AS DECIMAL(10,0) )";
183 my $remaining = "GREATEST(
184 CAST( ($edate_zero - $unearned) / 86400.0 AS DECIMAL(10,0) ),
186 my $fraction = "$remaining / $period";
188 $unearned_sql = "CAST( $unearned_base * $fraction AS DECIMAL(10,2) )";
189 push @select, "$unearned_sql AS unearned_revenue";
191 # last payment/credit date
192 my %t = (pay => 'cust_bill_pay', credit => 'cust_credit_bill');
193 foreach my $x (qw(pay credit)) {
195 my $link = $table.'_pkg';
196 my $pkey = dbdef->table($table)->primary_key;
197 my $last_date_sql = "SELECT MAX(_date)
198 FROM $table JOIN $link USING ($pkey)
199 WHERE $link.billpkgnum = cust_bill_pkg.billpkgnum
200 AND $table._date <= $unearned";
201 push @select, "($last_date_sql) AS last_$x";
204 push @select, '(edate - 82799) AS before_edate';
207 #no tax report group kludge
209 #usage always excluded
211 # always 'nottax', not 'istax'
212 $join_cust = ' JOIN cust_bill USING ( invnum )
213 LEFT JOIN cust_main USING ( custnum ) ';
215 $join_pkg .= ' LEFT JOIN cust_pkg USING ( pkgnum )
216 LEFT JOIN part_pkg USING ( pkgpart )
217 LEFT JOIN part_pkg AS override
218 ON pkgpart_override = override.pkgpart ';
220 my $where = ' WHERE '. join(' AND ', @where);
222 my $count_query = "SELECT COUNT(DISTINCT billpkgnum),
223 SUM( $unearned_base ), SUM( $unearned_sql )
224 FROM cust_bill_pkg $join_cust $join_pkg $where";
226 push @select, 'part_pkg.pkg',
229 FS::UI::Web::cust_sql_fields();
232 'table' => 'cust_bill_pkg',
233 'addl_from' => "$join_cust $join_pkg",
235 'select' => join(",\n", @select ),
236 'extra_sql' => $where,
237 'order_by' => 'ORDER BY cust_bill._date, billpkgnum',
240 my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ];
241 my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
243 my $conf = new FS::Conf;
244 my $money_char = $conf->config('money_char') || '$';
247 $conf ||= new FS::Conf;
248 $money_char ||= $conf->config('money_char') || '$';
251 $money_char . sprintf('%.2f', $_[0]->get($field));
258 my $value = $_[0]->get($field);
259 $value ? time2str('%b %d %Y', $value) : '';