From d2c621ec6c3d2edbd3d8b44600a92d6773ffa971 Mon Sep 17 00:00:00 2001 From: ivan Date: Wed, 24 Mar 2010 08:37:45 +0000 Subject: [PATCH] better prepaid income reporting, with line item detail, RT#7776 --- FS/FS/Conf.pm | 7 + httemplate/search/cust_bill_pkg.cgi | 92 ++++++++- httemplate/search/report_prepaid_income.cgi | 288 ++++++++++++++++++++-------- 3 files changed, 296 insertions(+), 91 deletions(-) diff --git a/FS/FS/Conf.pm b/FS/FS/Conf.pm index 94ecf0e2d..53c2d1387 100644 --- a/FS/FS/Conf.pm +++ b/FS/FS/Conf.pm @@ -3568,6 +3568,13 @@ worry that config_items is freeside-specific and icky. 'type' => 'checkbox', }, + { + 'key' => 'enable_legacy_prepaid_income', + 'section' => '', + 'description' => "Enable legacy prepaid income reporting. Only useful when you have imported pre-Freeside packages with longer-than-monthly duration, and need to do prepaid income reporting on them before they've been invoiced the first time.", + 'type' => 'checkbox', + }, + { key => "apacheroot", section => "deprecated", description => "DEPRECATED", type => "text" }, { key => "apachemachine", section => "deprecated", description => "DEPRECATED", type => "text" }, { key => "apachemachines", section => "deprecated", description => "DEPRECATED", type => "text" }, diff --git a/httemplate/search/cust_bill_pkg.cgi b/httemplate/search/cust_bill_pkg.cgi index 115597a94..f6ba8acf3 100644 --- a/httemplate/search/cust_bill_pkg.cgi +++ b/httemplate/search/cust_bill_pkg.cgi @@ -3,15 +3,24 @@ 'name' => 'line items', 'query' => $query, 'count_query' => $count_query, - 'count_addl' => [ $money_char. '%.2f total', ], + 'count_addl' => [ $money_char. '%.2f total', + $unearned ? ( $money_char. '%.2f unearned revenue' ) : (), + ], 'header' => [ #'#', 'Description', - 'Setup charge', + ( $unearned + ? 'Unearned' + : 'Setup charge' + ), ( $use_usage eq 'usage' ? 'Usage charge' : 'Recurring charge' ), + ( $unearned + ? ( 'Charge start', 'Charge end' ) + : () + ), 'Invoice', 'Date', FS::UI::Web::cust_header(), @@ -24,7 +33,22 @@ }, #strikethrough or "N/A ($amount)" or something these when # they're not applicable to pkg_tax search - sub { sprintf($money_char.'%.2f', shift->setup ) }, + sub { my $cust_bill_pkg = shift; + if ( $unearned ) { + my $period = + $cust_bill_pkg->edate - $cust_bill_pkg->sdate; + my $elapsed = $unearned - $cust_bill_pkg->sdate; + $elapsed = 0 if $elapsed < 0; + + my $remaining = 1 - $elapsed/$period; + + sprintf($money_char. '%.2f', + $remaining * $cust_bill_pkg->recur ); + + } else { + sprintf($money_char.'%.2f', $cust_bill_pkg->setup ); + } + }, sub { my $row = shift; my $value = 0; if ( $use_usage eq 'recurring' ) { @@ -36,6 +60,12 @@ } sprintf($money_char.'%.2f', $value ); }, + ( $unearned + ? ( sub { time2str('%b %d %Y', shift->sdate ) }, + sub { time2str('%b %d %Y', shift->edate ) }, + ) + : () + ), 'invnum', sub { time2str('%b %d %Y', shift->_date ) }, \&FS::UI::Web::cust_fields, @@ -45,6 +75,7 @@ '', '', '', + ( $unearned ? ( '', '' ) : () ), $ilink, $ilink, ( map { $_ ne 'Cust. Status' ? $clink : '' } @@ -52,12 +83,16 @@ ), ], #'align' => 'rlrrrc'.FS::UI::Web::cust_aligns(), - 'align' => 'lrrrc'.FS::UI::Web::cust_aligns(), + 'align' => 'lrr'. + ( $unearned ? 'cc' : '' ). + 'rc'. + FS::UI::Web::cust_aligns(), 'color' => [ #'', '', '', '', + ( $unearned ? ( '', '' ) : () ), '', '', FS::UI::Web::cust_colors(), @@ -67,6 +102,7 @@ '', '', '', + ( $unearned ? ( '', '' ) : () ), '', '', FS::UI::Web::cust_styles(), @@ -82,6 +118,8 @@ die "access denied" my $conf = new FS::Conf; +my $unearned = ''; + #here is the agent virtualization my $agentnums_sql = $FS::CurrentUser::CurrentUser->agentnums_sql( 'table' => 'cust_main' ); @@ -284,6 +322,19 @@ if ( $cgi->param('out') ) { keys %ph ); +} elsif ( $cgi->param('unearned_now') =~ /^(\d+)$/ ) { + + $unearned = $1; + + push @where, "cust_bill_pkg.sdate < $unearned", + "cust_bill_pkg.edate > $unearned", + "cust_bill_pkg.recur != 0", + "part_pkg.freq != '0'", + "part_pkg.freq != '1'", + "part_pkg.freq NOT LIKE '%h'", + "part_pkg.freq NOT LIKE '%d'", + "part_pkg.freq NOT LIKE '%w'"; + } if ( $cgi->param('itemdesc') ) { @@ -401,10 +452,31 @@ if ( $cgi->param('pkg_tax') ) { $count_query .= "SUM(setup + recur - usage)"; } elsif ( $use_usage eq 'usage' ) { $count_query .= "SUM(usage)"; + } elsif ( $unearned ) { + $count_query .= "SUM(cust_bill_pkg.recur)"; } else { $count_query .= "SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)"; } + if ( $unearned ) { + + #false laziness w/report_prepaid_income.cgi + + my $float = 'REAL'; #'DOUBLE PRECISION'; + + my $period = "CAST(cust_bill_pkg.edate - cust_bill_pkg.sdate AS $float)"; + my $elapsed = "(CASE WHEN cust_bill_pkg.sdate > $unearned + THEN 0 + ELSE ($unearned - cust_bill_pkg.sdate) + END)"; + #my $elapsed = "CAST($unearned - cust_bill_pkg.sdate AS $float)"; + + my $remaining = "(1 - $elapsed/$period)"; + + $count_query .= ", SUM($remaining * cust_bill_pkg.recur)"; + + } + } my $join_cust = ' JOIN cust_bill USING ( invnum ) @@ -473,11 +545,13 @@ if ($use_usage) { $count_query .= " FROM cust_bill_pkg $join_cust $join_pkg $where"; } -my @select = ( - 'cust_bill_pkg.*', - 'cust_bill._date', - ); -push @select, 'part_pkg.pkg' unless $cgi->param('istax'); +my @select = ( 'cust_bill_pkg.*', + 'cust_bill._date', ); + +push @select, 'part_pkg.pkg', + 'part_pkg.freq', + unless $cgi->param('istax'); + push @select, 'cust_main.custnum', FS::UI::Web::cust_sql_fields(); diff --git a/httemplate/search/report_prepaid_income.cgi b/httemplate/search/report_prepaid_income.cgi index ce928b81c..c0e2b807e 100644 --- a/httemplate/search/report_prepaid_income.cgi +++ b/httemplate/search/report_prepaid_income.cgi @@ -1,36 +1,86 @@ <% include("/elements/header.html", 'Prepaid Income (Unearned Revenue) Report') %> -<% table() %> - - Actual Unearned Revenue - Legacy Unearned Revenue - +<% include( '/elements/table-grid.html' ) %> + - $<% $total %> - - <% $now == $time ? "\$$total_legacy" : 'N/A'%> - +% if ( scalar(@agentnums) > 1 ) { + Agent +% } + <% $actual_label %>Unearned Revenue +% if ( $legacy ) { + Legacy Unearned Revenue +% } +% 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; +% } + + + +% if ( scalar(@agentnums) > 1 ) { + <% $agent_name |h %> +% } + + <% $money_char %><% $total{$agentnum} %> + +% if ( $legacy ) { + + <% $now == $time ? $money_char.$total_legacy{$agentnum} : 'N/A'%> + +% } + + + +% } + +
-Actual unearned revenue is the amount of unearned revenue Freeside has -actually invoiced for packages with longer-than monthly terms. -

-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. - - +<% $actual_label %><% $actual_label ? 'u' : 'U' %>nearned revenue +is the amount of unearned revenue +<% $actual_label ? 'Freeside has actually' : '' %> +invoiced for packages with longer-than monthly terms. + +% if ( $legacy ) { +

+ 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'); -#doesn't yet deal with daily/weekly packages +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 ' : ''; -#needs to be re-written in sql for efficiency +#doesn't yet deal with daily/weekly packages my $time = time; @@ -38,74 +88,148 @@ my $now = $cgi->param('date') && str2time($cgi->param('date')) || $time; $now =~ /^(\d+)$/ or die "unparsable date?"; $now = $1; -my @where = (); +my $link = "cust_bill_pkg.cgi?nottax=1;unearned_now=$now"; -if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { - my $agentnum = $1; - push @where, "agentnum = $agentnum"; -} - -#here is the agent virtualization -push @where, $FS::CurrentUser::CurrentUser->agentnums_sql; - -my $where = join(' AND ', @where); -$where = "AND $where" if $where; - -my( $total, $total_legacy ) = ( 0, 0 ); - -my @cust_bill_pkg = - grep { $_->cust_pkg && $_->cust_pkg->part_pkg->freq !~ /^([01]|\d+[dw])$/ } - qsearch({ - 'select' => 'cust_bill_pkg.*', - 'table' => 'cust_bill_pkg', - 'addl_from' => ' LEFT JOIN cust_bill USING ( invnum ) '. - ' LEFT JOIN cust_main USING ( custnum ) ', - 'hashref' => { - 'recur' => { op=>'!=', value=>0 }, - 'edate' => { op=>'>', value=>$now }, - }, - 'extra_sql' => $where, - }); - -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_bill_pkg ( @cust_bill_pkg) { - my $period = $cust_bill_pkg->edate - $cust_bill_pkg->sdate; - - my $elapsed = $now - $cust_bill_pkg->sdate; - $elapsed = 0 if $elapsed < 0; - - my $remaining = 1 - $elapsed/$period; - - my $unearned = $remaining * $cust_bill_pkg->recur; - $total += $unearned; +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; } -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 @where = (); - my $unearned = $remaining * $cust_pkg->part_pkg->recur; #!! only works for flat/legacy - $total_legacy += $unearned; +#here is the agent virtualization +push @where, $curuser->agentnums_sql( 'table'=>'cust_main' ); + +#well, because cust_bill_pkg.cgi has it and without it the numbers don't match.. +push @where , " payby != 'COMP' " + unless $cgi->param('include_comp_cust'); + +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 @cust_bill_pkg = + # grep { $_->cust_pkg && $_->cust_pkg->part_pkg->freq !~ /^([01]|\d+[hdw])$/ } + # qsearch({ + # 'select' => 'cust_bill_pkg.*', + # 'table' => 'cust_bill_pkg', + # 'addl_from' => ' LEFT JOIN cust_bill USING ( invnum ) '. + # ' LEFT JOIN cust_main USING ( custnum ) ', + # 'hashref' => { + # 'recur' => { op=>'!=', value=>0 }, + # 'sdate' => { op=>'<', value=>$now }, + # 'edate' => { op=>'>', value=>$now }, + # }, + # 'extra_sql' => $where, + # }); + # + # foreach my $cust_bill_pkg ( @cust_bill_pkg) { + # my $period = $cust_bill_pkg->edate - $cust_bill_pkg->sdate; + # + # my $elapsed = $now - $cust_bill_pkg->sdate; + # $elapsed = 0 if $elapsed < 0; + # + # my $remaining = 1 - $elapsed/$period; + # + # my $unearned = $remaining * $cust_bill_pkg->recur; + # $total += $unearned; + # + # } + + #re-written in sql: + + #false laziness w/cust_bill_pkg.cgi + + my $float = 'REAL'; #'DOUBLE PRECISION'; + + my $period = "CAST(cust_bill_pkg.edate - cust_bill_pkg.sdate AS $float)"; + my $elapsed = "(CASE WHEN cust_bill_pkg.sdate > $now + THEN 0 + ELSE ($now - cust_bill_pkg.sdate) + END)"; + #my $elapsed = "CAST($unearned - cust_bill_pkg.sdate AS $float)"; + + my $remaining = "(1 - $elapsed/$period)"; + + my $select = "SUM($remaining * cust_bill_pkg.recur)"; + + #[...] + + my $sql = "SELECT $select FROM cust_bill_pkg + LEFT JOIN cust_pkg USING ( pkgnum ) + LEFT JOIN part_pkg USING ( pkgpart ) + LEFT JOIN cust_main USING ( custnum ) + WHERE pkgpart > 0 + AND sdate < $now + AND edate > $now + AND cust_bill_pkg.recur != 0 + AND part_pkg.freq != '0' + 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' + $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 = sprintf('%.2f', $total); -$total_legacy = sprintf('%.2f', $total_legacy); - +$total{0} = sprintf('%.2f', $total{0}); +$total_legacy{0} = sprintf('%.2f', $total_legacy{0}); + -- 2.11.0