diff options
Diffstat (limited to 'httemplate/search/prepaid_income.html')
| -rw-r--r-- | httemplate/search/prepaid_income.html | 240 |
1 files changed, 240 insertions, 0 deletions
diff --git a/httemplate/search/prepaid_income.html b/httemplate/search/prepaid_income.html new file mode 100644 index 000000000..ebac5a2a9 --- /dev/null +++ b/httemplate/search/prepaid_income.html @@ -0,0 +1,240 @@ +<% include("/elements/header.html", 'Prepaid Income (Unearned Revenue) Report') %> + +<% include( '/elements/table-grid.html' ) %> + + <TR> +% if ( scalar(@agentnums) > 1 ) { + <TH CLASS="grid" BGCOLOR="#cccccc">Agent</TH> +% } + <TH CLASS="grid" BGCOLOR="#cccccc"><% $actual_label %>Unearned Revenue</TH> +% if ( $legacy ) { + <TH CLASS="grid" BGCOLOR="#cccccc">Legacy Unearned Revenue</TH> +% } + </TR> + +% my $bgcolor1 = '#eeeeee'; +% my $bgcolor2 = '#ffffff'; +% my $bgcolor; +% +% push @agentnums, 0 unless scalar(@agentnums) < 2; +% foreach my $agentnum (@agentnums) { +% +% if ( $bgcolor eq $bgcolor1 ) { +% $bgcolor = $bgcolor2; +% } else { +% $bgcolor = $bgcolor1; +% } +% +% my $alink = $agentnum ? "$link;agentnum=$agentnum" : $link; +% +% my $agent_name = 'Total'; +% if ( $agentnum ) { +% my $agent = qsearchs('agent', { 'agentnum' => $agentnum }) +% or die "unknown agentnum $agentnum"; +% $agent_name = $agent->agent; +% } + + <TR> + +% if ( scalar(@agentnums) > 1 ) { + <TD CLASS="grid" BGCOLOR="<% $bgcolor %>"><% $agent_name |h %></TD> +% } + + <TD ALIGN="right" CLASS="grid" BGCOLOR="<% $bgcolor %>"><A HREF="<% $alink %>"><% $money_char %><% $total{$agentnum} %></A></TD> + +% if ( $legacy ) { + <TD ALIGN="right" CLASS="grid" BGCOLOR="<% $bgcolor %>"> + <% $now == $time ? $money_char.$total_legacy{$agentnum} : '<i>N/A</i>'%> + </TD> +% } + + </TR> + +% } + +</TABLE> + +<BR> +<% $actual_label %><% $actual_label ? 'u' : 'U' %>nearned revenue +is the as-yet-unearned portion of revenue +<% $actual_label ? 'Freeside has actually' : '' %> +invoiced for packages with +<% $cgi->param('include_monthly') ? 'terms extending into the future.' + : 'longer-than monthly terms.' %> + +% if ( $legacy ) { + <BR><BR> + Legacy unearned revenue is the amount of unearned revenue represented by + customer packages. This number may be larger than actual unearned + revenue if you have imported longer-than monthly customer packages from + a previous billing system. +% } + +<% include('/elements/footer.html') %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + +my $conf = new FS::Conf; +my $money_char = $conf->config('money_char') || '$'; + +my $legacy = $conf->exists('enable_legacy_prepaid_income'); +my $actual_label = $legacy ? 'Actual ' : ''; + +#doesn't yet deal with daily/weekly packages + +my $mode = $cgi->param('mode'); + +my $time = time; + +my $now = $cgi->param('date') && parse_datetime($cgi->param('date')) || $time; +$now =~ /^(\d+)$/ or die "unparsable date?"; +$now = $1; + +my $dt = DateTime->from_epoch(epoch => $now, time_zone => 'local'); +$dt->truncate(to => 'day'); # local midnight on the report day +$dt->add(days => 1); # the day after that +$dt->subtract(seconds => 1); # the last second of the report day +$now = $dt->epoch; + +my $link = "unearned_detail.html?date=$now;mode=$mode"; + +if ( $cgi->param('include_monthly') ) { + $link .= ';include_monthly=1'; +} + +my $curuser = $FS::CurrentUser::CurrentUser; + +my $agentnum = ''; +my @agentnums = (); +$agentnum ? ($agentnum) : $curuser->agentnums; +if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { + @agentnums = ($1); + #XXX#push @where, "agentnum = $agentnum"; + #XXX#$link .= ";agentnum=$agentnum"; +} else { + @agentnums = $curuser->agentnums; +} + +my @where = (); + +#here is the agent virtualization +push @where, $curuser->agentnums_sql( 'table'=>'cust_main' ); + +my $status = ''; +if ( $cgi->param('status') =~ /^([a-z]+)$/ ) { + $status = $1; + $link .= ";status=$status"; + push @where, FS::cust_main->cust_status_sql . " = '$status'"; +} + +my %total = (); +my %total_legacy = (); +foreach my $agentnum (@agentnums) { + + my $where = join(' AND ', @where, "cust_main.agentnum = $agentnum"); + $where = "AND $where" if $where; + + my( $total, $total_legacy ) = ( 0, 0 ); + + my @opt = ($now, '', setuprecur => 'recur', no_usage => 1); + # balance owed, recurring only, not including usage charges + my $unearned_base; + if ( $mode eq 'billed' ) { + $unearned_base = '( ' . + FS::cust_bill_pkg->charged_sql(@opt) . ' - ' . + FS::cust_bill_pkg->credited_sql(@opt) . ' )'; + } elsif ( $mode eq 'paid' ) { + $unearned_base = FS::cust_bill_pkg->paid_sql(@opt); + } + + my $edate_zero = midnight_sql('edate'); + my $sdate_zero = midnight_sql('sdate'); + my $period = "CAST( ($edate_zero - $sdate_zero) / 86400.0 AS DECIMAL(10,0) )"; + my $remaining = "GREATEST( + CAST( ($edate_zero - $now) / 86400.0 AS DECIMAL(10,0) ), + 0)"; + my $fraction = "$remaining / $period"; + + my $unearned_sql = "CAST( + GREATEST( $unearned_base * $fraction, 0 ) + AS DECIMAL(10,2) + )"; + + my $select = "SUM( $unearned_sql )"; + + if ( !$cgi->param('include_monthly') ) { + # all except freq != 0; one-time charges should never be included + $where .= " + AND part_pkg.freq != '1' + AND part_pkg.freq NOT LIKE '%h' + AND part_pkg.freq NOT LIKE '%d' + AND part_pkg.freq NOT LIKE '%w'"; + } + + # $mode actually doesn't matter here, since unpaid invoices have zero + # unearned revenue + + my $sql = + "SELECT $select FROM cust_bill_pkg + LEFT JOIN cust_pkg ON (cust_bill_pkg.pkgnum = cust_pkg.pkgnum) + LEFT JOIN part_pkg USING ( pkgpart ) + LEFT JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum) + WHERE pkgpart > 0 + AND cust_bill_pkg.sdate < $now + AND cust_bill_pkg.edate > $now + AND cust_bill_pkg.recur != 0 + AND part_pkg.freq != '0' + $where + "; + + my $sth = dbh->prepare($sql) or die dbh->errstr; + $sth->execute or die $sth->errstr; + my $total = $sth->fetchrow_arrayref->[0]; + + $total = sprintf('%.2f', $total); + $total{$agentnum} = $total; + $total{0} += $total; + + if ( $legacy ) { + + #not yet rewritten in sql, but now not enabled by default + + my @cust_pkg = + grep { $_->part_pkg->recur != 0 + && $_->part_pkg->freq !~ /^([01]|\d+[dw])$/ + } + qsearch({ + 'select' => 'cust_pkg.*', + 'table' => 'cust_pkg', + 'addl_from' => ' LEFT JOIN cust_main USING ( custnum ) ', + 'hashref' => { 'bill' => { op=>'>', value=>$now } }, + 'extra_sql' => $where, + }); + + foreach my $cust_pkg ( @cust_pkg ) { + my $period = $cust_pkg->bill - $cust_pkg->last_bill; + + my $elapsed = $now - $cust_pkg->last_bill; + $elapsed = 0 if $elapsed < 0; + + my $remaining = 1 - $elapsed/$period; + + my $unearned = $remaining * $cust_pkg->part_pkg->recur; #!! only works for flat/legacy + $total_legacy += $unearned; + + } + + $total_legacy = sprintf('%.2f', $total_legacy); + $total_legacy{$agentnum} = $total_legacy; + $total_legacy{0} += $total_legacy; + + } + +} + +$total{0} = sprintf('%.2f', $total{0}); +$total_legacy{0} = sprintf('%.2f', $total_legacy{0}); + +</%init> |