From: Mark Wells Date: Fri, 27 Apr 2012 19:07:32 +0000 (-0700) Subject: improvements to prepaid income report, #13289 X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=d54109cc3035ce63ab0f6c2ec94317e378887d49 improvements to prepaid income report, #13289 --- diff --git a/httemplate/search/cust_bill_pkg.cgi b/httemplate/search/cust_bill_pkg.cgi index b08024cb0..820b17864 100644 --- a/httemplate/search/cust_bill_pkg.cgi +++ b/httemplate/search/cust_bill_pkg.cgi @@ -9,7 +9,9 @@ 'header' => [ emt('Description'), ( $unearned - ? ( emt('Unearned'), emt('Owed'), emt('Payment date') ) + ? ( emt('Unearned'), + emt('Owed'), # useful in 'paid' mode? + emt('Payment date') ) : ( emt('Setup charge') ) ), ( $use_usage eq 'usage' @@ -39,9 +41,10 @@ $elapsed = 0 if $elapsed < 0; my $remaining = 1 - $elapsed/$period; + my $base = ($unearned_mode eq 'paid' ? 'total_paid' : 'recur'); sprintf($money_char. '%.2f', - $remaining * $cust_bill_pkg->recur ); + $remaining * $cust_bill_pkg->$base ); } else { sprintf($money_char.'%.2f', $cust_bill_pkg->setup ); @@ -137,6 +140,12 @@ die "access denied" my $conf = new FS::Conf; my $unearned = ''; +my $unearned_mode = ''; +my $unearned_base = ''; + +my @select = ( 'cust_bill_pkg.*', + 'cust_bill._date', ); +my ($join_cust, $join_pkg ) = ('', ''); #here is the agent virtualization my $agentnums_sql = @@ -146,14 +155,18 @@ my @where = ( $agentnums_sql ); my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); +if ( $cgi->param('status') =~ /^([a-z]+)$/ ) { + push @where, FS::cust_main->cust_status_sql . " = '$1'"; +} + if ( $cgi->param('distribute') == 1 ) { push @where, "sdate <= $ending", "edate > $beginning", ; } else { - push @where, "_date >= $beginning", - "_date <= $ending"; + push @where, "cust_bill._date >= $beginning", + "cust_bill._date <= $ending"; } if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { @@ -336,16 +349,40 @@ if ( $cgi->param('out') ) { } elsif ( $cgi->param('unearned_now') =~ /^(\d+)$/ ) { $unearned = $1; + $unearned_mode = $cgi->param('mode'); push @where, "cust_bill_pkg.sdate < $unearned", "cust_bill_pkg.edate > $unearned", "cust_bill_pkg.recur != 0", - "part_pkg.freq != '0'", + "part_pkg.freq != '0'"; + + if ( !$cgi->param('include_monthly') ) { + push @where, "part_pkg.freq != '1'", "part_pkg.freq NOT LIKE '%h'", "part_pkg.freq NOT LIKE '%d'", "part_pkg.freq NOT LIKE '%w'"; + } + if ( !$unearned_mode or $unearned_mode eq 'billed' ) { + $unearned_base = 'cust_bill_pkg.recur'; + } + elsif ( $unearned_mode eq 'paid' ) { + $join_pkg .= "JOIN ( + SELECT billpkgnum, SUM(cust_bill_pay_pkg.amount) AS total_paid + FROM cust_bill_pay_pkg + JOIN cust_bill_pay USING (billpaynum) + JOIN cust_pay USING (paynum) + WHERE cust_bill_pay_pkg.setuprecur = 'recur' + AND cust_pay._date <= $unearned + GROUP BY billpkgnum + ) AS cust_bill_pkg_paid USING (billpkgnum)"; + $unearned_base = 'total_paid'; + push @select, 'total_paid'; + } + else { + die "invalid mode '$unearned_mode'"; + } } if ( $cgi->param('itemdesc') ) { @@ -468,7 +505,7 @@ if ( $cgi->param('pkg_tax') ) { } elsif ( $use_usage eq 'usage' ) { $count_query .= "SUM(usage)"; } elsif ( $unearned ) { - $count_query .= "SUM(cust_bill_pkg.recur)"; + $count_query .= "SUM($unearned_base)"; } elsif ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) { $count_query .= "SUM( COALESCE(cust_bill_pkg_tax_rate_location.amount, cust_bill_pkg.setup + cust_bill_pkg.recur))"; } elsif ( $cgi->param('iscredit') eq 'rate') { @@ -492,23 +529,21 @@ if ( $cgi->param('pkg_tax') ) { my $remaining = "(1 - $elapsed/$period)"; - $count_query .= ", SUM($remaining * cust_bill_pkg.recur)"; + $count_query .= ", SUM($remaining * $unearned_base)"; } } -my $join_cust = ' JOIN cust_bill USING ( invnum ) - LEFT JOIN cust_main USING ( custnum ) '; +$join_cust = ' JOIN cust_bill USING ( invnum ) + LEFT JOIN cust_main USING ( custnum ) '; - -my $join_pkg; if ( $cgi->param('nottax') ) { - $join_pkg = ' LEFT JOIN cust_pkg USING ( pkgnum ) - LEFT JOIN part_pkg USING ( pkgpart ) - LEFT JOIN part_pkg AS override - ON pkgpart_override = override.pkgpart '; + $join_pkg .= ' LEFT JOIN cust_pkg USING ( pkgnum ) + LEFT JOIN part_pkg USING ( pkgpart ) + LEFT JOIN part_pkg AS override + ON pkgpart_override = override.pkgpart '; $join_pkg .= ' LEFT JOIN cust_location USING ( locationnum ) ' if $conf->exists('tax-pkg_address'); @@ -567,9 +602,6 @@ 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', 'part_pkg.freq', unless $cgi->param('istax'); @@ -583,7 +615,7 @@ my $query = { 'hashref' => {}, 'select' => join(', ', @select ), 'extra_sql' => $where, - 'order_by' => 'ORDER BY _date, billpkgnum', + 'order_by' => 'ORDER BY cust_bill._date, billpkgnum', }; my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ]; @@ -593,9 +625,8 @@ my $conf = new FS::Conf; my $money_char = $conf->config('money_char') || '$'; my $owed_sub = sub { - $money_char. shift->owed_recur; #_recur :/ + $money_char . shift->owed_recur; }; - my $payment_date_sub = sub { #my $cust_bill_pkg = shift; my @cust_pay = sort { $a->_date <=> $b->_date } diff --git a/httemplate/search/prepaid_income.html b/httemplate/search/prepaid_income.html new file mode 100644 index 000000000..d224af9d7 --- /dev/null +++ b/httemplate/search/prepaid_income.html @@ -0,0 +1,237 @@ +<% include("/elements/header.html", 'Prepaid Income (Unearned Revenue) Report') %> + +<% include( '/elements/table-grid.html' ) %> + + +% 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_label %><% $actual_label ? 'u' : 'U' %>nearned revenue +is the amount of unearned 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 ) { +

+ 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 $link = "cust_bill_pkg.cgi?nottax=1;unearned_now=$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 $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 $base; + my $from = ''; + if ( $mode eq 'billed' ) { + $base = 'cust_bill_pkg.recur'; + $from = 'cust_bill_pkg'; + } + elsif ( $mode eq 'paid' ) { + $base = 'cust_bill_pay_pkg.amount'; + $from = 'cust_bill_pay_pkg + LEFT JOIN cust_bill_pkg USING ( billpkgnum ) + LEFT JOIN cust_bill_pay USING ( billpaynum ) + LEFT JOIN cust_pay USING ( paynum )'; + $where .= ' AND ' if $where; + $where .= "cust_bill_pay_pkg.setuprecur = 'recur' + AND cust_pay._date <= $now"; + } + else { + die "invalid mode '$mode'"; + } + my $select = "SUM($remaining * $base)"; + + 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'"; + } + + my $sql = + "SELECT $select FROM $from + 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}); + + diff --git a/httemplate/search/report_prepaid_income.cgi b/httemplate/search/report_prepaid_income.cgi deleted file mode 100644 index 2fe5b6f10..000000000 --- a/httemplate/search/report_prepaid_income.cgi +++ /dev/null @@ -1,231 +0,0 @@ -<% include("/elements/header.html", 'Prepaid Income (Unearned Revenue) Report') %> - -<% include( '/elements/table-grid.html' ) %> - - -% 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_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'); - -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 $time = time; - -my $now = $cgi->param('date') && parse_datetime($cgi->param('date')) || $time; -$now =~ /^(\d+)$/ or die "unparsable date?"; -$now = $1; - -my $link = "cust_bill_pkg.cgi?nottax=1;unearned_now=$now"; - -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 %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{0} = sprintf('%.2f', $total{0}); -$total_legacy{0} = sprintf('%.2f', $total_legacy{0}); - - diff --git a/httemplate/search/report_prepaid_income.html b/httemplate/search/report_prepaid_income.html index 061b24c68..90b72f60b 100644 --- a/httemplate/search/report_prepaid_income.html +++ b/httemplate/search/report_prepaid_income.html @@ -2,7 +2,7 @@ <% include('/elements/init_calendar.html') %> -
+ @@ -13,7 +13,7 @@ - + <% include( '/elements/tr-select-agent.html', 'disable_empty'=>0 ) %> - + + <& /elements/tr-select-cust_main-status.html, + label => mt('Customer Status') &> + <& /elements/tr-select.html, + label => 'Invoice Status', + field => 'mode', + options => [ qw(billed paid) ] &> + + + +
As of As of @@ -30,7 +30,20 @@
+ + <% emt('Include packages with period <= 1 month') %> +