1 <% include("/elements/header.html", 'Prepaid Income (Unearned Revenue) Report') %>
5 <TH>Actual Unearned Revenue</TH>
6 <TH>Legacy Unearned Revenue</TH>
9 <TD ALIGN="right">$<% $total %>
11 <% $now == $time ? "\$$total_legacy" : '<i>N/A</i>'%>
17 Actual unearned revenue is the amount of unearned revenue Freeside has
18 actually invoiced for packages with longer-than monthly terms.
20 Legacy unearned revenue is the amount of unearned revenue represented by
21 customer packages. This number may be larger than actual unearned
22 revenue if you have imported longer-than monthly customer packages from
23 a previous billing system.
29 unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
31 #doesn't yet deal with daily/weekly packages
33 #needs to be re-written in sql for efficiency
37 my $now = $cgi->param('date') && str2time($cgi->param('date')) || $time;
38 $now =~ /^(\d+)$/ or die "unparsable date?";
43 if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
45 push @where, "agentnum = $agentnum";
48 #here is the agent virtualization
49 push @where, $FS::CurrentUser::CurrentUser->agentnums_sql;
51 my $where = join(' AND ', @where);
52 $where = "AND $where" if $where;
54 my( $total, $total_legacy ) = ( 0, 0 );
57 grep { $_->cust_pkg && $_->cust_pkg->part_pkg->freq !~ /^([01]|\d+[dw])$/ }
59 'select' => 'cust_bill_pkg.*',
60 'table' => 'cust_bill_pkg',
61 'addl_from' => ' LEFT JOIN cust_bill USING ( invnum ) '.
62 ' LEFT JOIN cust_main USING ( custnum ) ',
64 'recur' => { op=>'!=', value=>0 },
65 'edate' => { op=>'>', value=>$now },
67 'extra_sql' => $where,
71 grep { $_->part_pkg->recur != 0
72 && $_->part_pkg->freq !~ /^([01]|\d+[dw])$/
75 'select' => 'cust_pkg.*',
76 'table' => 'cust_pkg',
77 'addl_from' => ' LEFT JOIN cust_main USING ( custnum ) ',
78 'hashref' => { 'bill' => { op=>'>', value=>$now } },
79 'extra_sql' => $where,
82 foreach my $cust_bill_pkg ( @cust_bill_pkg) {
83 my $period = $cust_bill_pkg->edate - $cust_bill_pkg->sdate;
85 my $elapsed = $now - $cust_bill_pkg->sdate;
86 $elapsed = 0 if $elapsed < 0;
88 my $remaining = 1 - $elapsed/$period;
90 my $unearned = $remaining * $cust_bill_pkg->recur;
95 foreach my $cust_pkg ( @cust_pkg ) {
96 my $period = $cust_pkg->bill - $cust_pkg->last_bill;
98 my $elapsed = $now - $cust_pkg->last_bill;
99 $elapsed = 0 if $elapsed < 0;
101 my $remaining = 1 - $elapsed/$period;
103 my $unearned = $remaining * $cust_pkg->part_pkg->recur; #!! only works for flat/legacy
104 $total_legacy += $unearned;
108 $total = sprintf('%.2f', $total);
109 $total_legacy = sprintf('%.2f', $total_legacy);