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') && parse_datetime($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' );
112 my %total_legacy = ();
113 foreach my $agentnum (@agentnums) {
115 my $where = join(' AND ', @where, "cust_main.agentnum = $agentnum");
116 $where = "AND $where" if $where;
118 my( $total, $total_legacy ) = ( 0, 0 );
120 # my @cust_bill_pkg =
121 # grep { $_->cust_pkg && $_->cust_pkg->part_pkg->freq !~ /^([01]|\d+[hdw])$/ }
123 # 'select' => 'cust_bill_pkg.*',
124 # 'table' => 'cust_bill_pkg',
125 # 'addl_from' => ' LEFT JOIN cust_bill USING ( invnum ) '.
126 # ' LEFT JOIN cust_main USING ( custnum ) ',
128 # 'recur' => { op=>'!=', value=>0 },
129 # 'sdate' => { op=>'<', value=>$now },
130 # 'edate' => { op=>'>', value=>$now },
132 # 'extra_sql' => $where,
135 # foreach my $cust_bill_pkg ( @cust_bill_pkg) {
136 # my $period = $cust_bill_pkg->edate - $cust_bill_pkg->sdate;
138 # my $elapsed = $now - $cust_bill_pkg->sdate;
139 # $elapsed = 0 if $elapsed < 0;
141 # my $remaining = 1 - $elapsed/$period;
143 # my $unearned = $remaining * $cust_bill_pkg->recur;
144 # $total += $unearned;
150 #false laziness w/cust_bill_pkg.cgi
152 my $float = 'REAL'; #'DOUBLE PRECISION';
154 my $period = "CAST(cust_bill_pkg.edate - cust_bill_pkg.sdate AS $float)";
155 my $elapsed = "(CASE WHEN cust_bill_pkg.sdate > $now
157 ELSE ($now - cust_bill_pkg.sdate)
159 #my $elapsed = "CAST($unearned - cust_bill_pkg.sdate AS $float)";
161 my $remaining = "(1 - $elapsed/$period)";
163 my $select = "SUM($remaining * cust_bill_pkg.recur)";
167 my $sql = "SELECT $select FROM cust_bill_pkg
168 LEFT JOIN cust_pkg USING ( pkgnum )
169 LEFT JOIN part_pkg USING ( pkgpart )
170 LEFT JOIN cust_main USING ( custnum )
174 AND cust_bill_pkg.recur != 0
175 AND part_pkg.freq != '0'
176 AND part_pkg.freq != '1'
177 AND part_pkg.freq NOT LIKE '%h'
178 AND part_pkg.freq NOT LIKE '%d'
179 AND part_pkg.freq NOT LIKE '%w'
183 my $sth = dbh->prepare($sql) or die dbh->errstr;
184 $sth->execute or die $sth->errstr;
185 my $total = $sth->fetchrow_arrayref->[0];
187 $total = sprintf('%.2f', $total);
188 $total{$agentnum} = $total;
193 #not yet rewritten in sql, but now not enabled by default
196 grep { $_->part_pkg->recur != 0
197 && $_->part_pkg->freq !~ /^([01]|\d+[dw])$/
200 'select' => 'cust_pkg.*',
201 'table' => 'cust_pkg',
202 'addl_from' => ' LEFT JOIN cust_main USING ( custnum ) ',
203 'hashref' => { 'bill' => { op=>'>', value=>$now } },
204 'extra_sql' => $where,
207 foreach my $cust_pkg ( @cust_pkg ) {
208 my $period = $cust_pkg->bill - $cust_pkg->last_bill;
210 my $elapsed = $now - $cust_pkg->last_bill;
211 $elapsed = 0 if $elapsed < 0;
213 my $remaining = 1 - $elapsed/$period;
215 my $unearned = $remaining * $cust_pkg->part_pkg->recur; #!! only works for flat/legacy
216 $total_legacy += $unearned;
220 $total_legacy = sprintf('%.2f', $total_legacy);
221 $total_legacy{$agentnum} = $total_legacy;
222 $total_legacy{0} += $total_legacy;
228 $total{0} = sprintf('%.2f', $total{0});
229 $total_legacy{0} = sprintf('%.2f', $total_legacy{0});