1 <% include("/elements/header.html", 'Prepaid Income (Unearned Revenue) Report') %>
3 <% include( '/elements/table-grid.html' ) %>
6 % if ( scalar(@agentnums) > 1 ) {
7 <TH CLASS="grid" BGCOLOR="#cccccc">Agent</TH>
9 <TH CLASS="grid" BGCOLOR="#cccccc"><% $actual_label %>Unearned Revenue</TH>
11 <TH CLASS="grid" BGCOLOR="#cccccc">Legacy Unearned Revenue</TH>
15 % my $bgcolor1 = '#eeeeee';
16 % my $bgcolor2 = '#ffffff';
19 % push @agentnums, 0 unless scalar(@agentnums) < 2;
20 % foreach my $agentnum (@agentnums) {
22 % if ( $bgcolor eq $bgcolor1 ) {
23 % $bgcolor = $bgcolor2;
25 % $bgcolor = $bgcolor1;
28 % my $alink = $agentnum ? "$link;agentnum=$agentnum" : $link;
30 % my $agent_name = 'Total';
32 % my $agent = qsearchs('agent', { 'agentnum' => $agentnum })
33 % or die "unknown agentnum $agentnum";
34 % $agent_name = $agent->agent;
39 % if ( scalar(@agentnums) > 1 ) {
40 <TD CLASS="grid" BGCOLOR="<% $bgcolor %>"><% $agent_name |h %></TD>
43 <TD ALIGN="right" CLASS="grid" BGCOLOR="<% $bgcolor %>"><A HREF="<% $alink %>"><% $money_char %><% $total{$agentnum} %></A></TD>
46 <TD ALIGN="right" CLASS="grid" BGCOLOR="<% $bgcolor %>">
47 <% $now == $time ? $money_char.$total_legacy{$agentnum} : '<i>N/A</i>'%>
58 <% $actual_label %><% $actual_label ? 'u' : 'U' %>nearned revenue
59 is the amount of unearned revenue
60 <% $actual_label ? 'Freeside has actually' : '' %>
61 invoiced for packages with longer-than monthly terms.
65 Legacy unearned revenue is the amount of unearned revenue represented by
66 customer packages. This number may be larger than actual unearned
67 revenue if you have imported longer-than monthly customer packages from
68 a previous billing system.
71 <% include('/elements/footer.html') %>
75 unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
77 my $conf = new FS::Conf;
78 my $money_char = $conf->config('money_char') || '$';
80 my $legacy = $conf->exists('enable_legacy_prepaid_income');
81 my $actual_label = $legacy ? 'Actual ' : '';
83 #doesn't yet deal with daily/weekly packages
87 my $now = $cgi->param('date') && str2time($cgi->param('date')) || $time;
88 $now =~ /^(\d+)$/ or die "unparsable date?";
91 my $link = "cust_bill_pkg.cgi?nottax=1;unearned_now=$now";
93 my $curuser = $FS::CurrentUser::CurrentUser;
97 $agentnum ? ($agentnum) : $curuser->agentnums;
98 if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
100 #XXX#push @where, "agentnum = $agentnum";
101 #XXX#$link .= ";agentnum=$agentnum";
103 @agentnums = $curuser->agentnums;
108 #here is the agent virtualization
109 push @where, $curuser->agentnums_sql( 'table'=>'cust_main' );
111 #well, because cust_bill_pkg.cgi has it and without it the numbers don't match..
112 push @where , " payby != 'COMP' "
113 unless $cgi->param('include_comp_cust');
116 my %total_legacy = ();
117 foreach my $agentnum (@agentnums) {
119 my $where = join(' AND ', @where, "cust_main.agentnum = $agentnum");
120 $where = "AND $where" if $where;
122 my( $total, $total_legacy ) = ( 0, 0 );
124 # my @cust_bill_pkg =
125 # grep { $_->cust_pkg && $_->cust_pkg->part_pkg->freq !~ /^([01]|\d+[hdw])$/ }
127 # 'select' => 'cust_bill_pkg.*',
128 # 'table' => 'cust_bill_pkg',
129 # 'addl_from' => ' LEFT JOIN cust_bill USING ( invnum ) '.
130 # ' LEFT JOIN cust_main USING ( custnum ) ',
132 # 'recur' => { op=>'!=', value=>0 },
133 # 'sdate' => { op=>'<', value=>$now },
134 # 'edate' => { op=>'>', value=>$now },
136 # 'extra_sql' => $where,
139 # foreach my $cust_bill_pkg ( @cust_bill_pkg) {
140 # my $period = $cust_bill_pkg->edate - $cust_bill_pkg->sdate;
142 # my $elapsed = $now - $cust_bill_pkg->sdate;
143 # $elapsed = 0 if $elapsed < 0;
145 # my $remaining = 1 - $elapsed/$period;
147 # my $unearned = $remaining * $cust_bill_pkg->recur;
148 # $total += $unearned;
154 #false laziness w/cust_bill_pkg.cgi
156 my $float = 'REAL'; #'DOUBLE PRECISION';
158 my $period = "CAST(cust_bill_pkg.edate - cust_bill_pkg.sdate AS $float)";
159 my $elapsed = "(CASE WHEN cust_bill_pkg.sdate > $now
161 ELSE ($now - cust_bill_pkg.sdate)
163 #my $elapsed = "CAST($unearned - cust_bill_pkg.sdate AS $float)";
165 my $remaining = "(1 - $elapsed/$period)";
167 my $select = "SUM($remaining * cust_bill_pkg.recur)";
171 my $sql = "SELECT $select FROM cust_bill_pkg
172 LEFT JOIN cust_pkg USING ( pkgnum )
173 LEFT JOIN part_pkg USING ( pkgpart )
174 LEFT JOIN cust_main USING ( custnum )
178 AND cust_bill_pkg.recur != 0
179 AND part_pkg.freq != '0'
180 AND part_pkg.freq != '1'
181 AND part_pkg.freq NOT LIKE '%h'
182 AND part_pkg.freq NOT LIKE '%d'
183 AND part_pkg.freq NOT LIKE '%w'
187 my $sth = dbh->prepare($sql) or die dbh->errstr;
188 $sth->execute or die $sth->errstr;
189 my $total = $sth->fetchrow_arrayref->[0];
191 $total = sprintf('%.2f', $total);
192 $total{$agentnum} = $total;
197 #not yet rewritten in sql, but now not enabled by default
200 grep { $_->part_pkg->recur != 0
201 && $_->part_pkg->freq !~ /^([01]|\d+[dw])$/
204 'select' => 'cust_pkg.*',
205 'table' => 'cust_pkg',
206 'addl_from' => ' LEFT JOIN cust_main USING ( custnum ) ',
207 'hashref' => { 'bill' => { op=>'>', value=>$now } },
208 'extra_sql' => $where,
211 foreach my $cust_pkg ( @cust_pkg ) {
212 my $period = $cust_pkg->bill - $cust_pkg->last_bill;
214 my $elapsed = $now - $cust_pkg->last_bill;
215 $elapsed = 0 if $elapsed < 0;
217 my $remaining = 1 - $elapsed/$period;
219 my $unearned = $remaining * $cust_pkg->part_pkg->recur; #!! only works for flat/legacy
220 $total_legacy += $unearned;
224 $total_legacy = sprintf('%.2f', $total_legacy);
225 $total_legacy{$agentnum} = $total_legacy;
226 $total_legacy{0} += $total_legacy;
232 $total{0} = sprintf('%.2f', $total{0});
233 $total_legacy{0} = sprintf('%.2f', $total_legacy{0});