From e58351f6f09eb25ab48d0567b31a2eea25188de0 Mon Sep 17 00:00:00 2001 From: Mark Wells Date: Thu, 18 Sep 2014 13:45:57 -0700 Subject: [PATCH] ARPU option on sales report, #30911 --- FS/FS/Report/Table.pm | 81 +++++++++++++++++++++++++------------- httemplate/graph/cust_bill_pkg.cgi | 25 +++++++++++- 2 files changed, 76 insertions(+), 30 deletions(-) diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm index 8c45ae145..924fd0506 100644 --- a/FS/FS/Report/Table.pm +++ b/FS/FS/Report/Table.pm @@ -408,6 +408,8 @@ unspecified, defaults to all three. 'use_override': for line items generated by an add-on package, use the class of the add-on rather than the base package. +'average_per_cust_pkg': divide the result by the number of distinct packages. + 'distribute': for non-monthly recurring charges, ignore the invoice date. Instead, consider the line item's starting/ending dates. Determine the fraction of the line item duration that falls within the specified @@ -428,6 +430,12 @@ sub cust_bill_pkg { $sum += $self->cust_bill_pkg_setup(@_) if $charges{S}; $sum += $self->cust_bill_pkg_recur(@_) if $charges{R}; $sum += $self->cust_bill_pkg_detail(@_) if $charges{U}; + + if ($opt{'average_per_cust_pkg'}) { + my $count = $self->cust_bill_pkg_count_pkgnum(@_); + return '' if $count == 0; + $sum = sprintf('%.2f', $sum / $count); + } $sum; } @@ -469,7 +477,9 @@ sub cust_bill_pkg_setup { $self->scalar_sql($total_sql); } -sub cust_bill_pkg_recur { +sub _cust_bill_pkg_recurring { + # returns the FROM/WHERE part of the statement to query all recurring + # line items in the period my $self = shift; my ($speriod, $eperiod, $agentnum, %opt) = @_; @@ -486,18 +496,6 @@ sub cust_bill_pkg_recur { push @where, $self->with_cust_classnum(%opt); - # subtract all usage from the line item regardless of date - my $item_usage; - if ( $opt{'project'} ) { - $item_usage = 'usage'; #already calculated - } - else { - $item_usage = '( SELECT COALESCE(SUM(cust_bill_pkg_detail.amount),0) - FROM cust_bill_pkg_detail - WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum )'; - } - my $recur_fraction = ''; - if ( $opt{'distribute'} ) { $where[0] = 'pkgnum != 0'; # specifically exclude fees push @where, "cust_main.agentnum = $agentnum" if $agentnum; @@ -505,11 +503,6 @@ sub cust_bill_pkg_recur { "$cust_bill_pkg.sdate < $eperiod", "$cust_bill_pkg.edate >= $speriod", ; - # the fraction of edate - sdate that's within [speriod, eperiod] - $recur_fraction = " * - CAST(LEAST($eperiod, $cust_bill_pkg.edate) - - GREATEST($speriod, $cust_bill_pkg.sdate) AS DECIMAL) / - ($cust_bill_pkg.edate - $cust_bill_pkg.sdate)"; } else { # we don't want to have to create v_cust_bill @@ -518,12 +511,52 @@ sub cust_bill_pkg_recur { $self->in_time_period_and_agent($speriod, $eperiod, $agentnum, $_date); } - my $total_sql = 'SELECT '. - "COALESCE(SUM(($cust_bill_pkg.recur - $item_usage) $recur_fraction),0) + return " FROM $cust_bill_pkg $cust_bill_pkg_join WHERE ".join(' AND ', grep $_, @where); +} + +sub cust_bill_pkg_recur { + my $self = shift; + my ($speriod, $eperiod, $agentnum, %opt) = @_; + + # subtract all usage from the line item regardless of date + my $item_usage; + if ( $opt{'project'} ) { + $item_usage = 'usage'; #already calculated + } + else { + $item_usage = '( SELECT COALESCE(SUM(cust_bill_pkg_detail.amount),0) + FROM cust_bill_pkg_detail + WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum )'; + } + + my $cust_bill_pkg = $opt{'project'} ? 'v_cust_bill_pkg' : 'cust_bill_pkg'; + + my $recur_fraction = ''; + if ($opt{'distribute'}) { + # the fraction of edate - sdate that's within [speriod, eperiod] + $recur_fraction = " * + CAST(LEAST($eperiod, $cust_bill_pkg.edate) - + GREATEST($speriod, $cust_bill_pkg.sdate) AS DECIMAL) / + ($cust_bill_pkg.edate - $cust_bill_pkg.sdate)"; + } + + my $total_sql = + "SELECT COALESCE(SUM(($cust_bill_pkg.recur - $item_usage) $recur_fraction),0)" . + $self->_cust_bill_pkg_recurring(@_); + + $self->scalar_sql($total_sql); +} + +sub cust_bill_pkg_count_pkgnum { + # for ARPU calculation + my $self = shift; + my $total_sql = 'SELECT COUNT(DISTINCT pkgnum) '. + $self->_cust_bill_pkg_recurring(@_); + $self->scalar_sql($total_sql); } @@ -570,10 +603,6 @@ sub cust_bill_pkg_detail { my $total_sql = " SELECT SUM(cust_bill_pkg_detail.amount) "; $total_sql .= - " / CASE COUNT(cust_pkg.*) WHEN 0 THEN 1 ELSE COUNT(cust_pkg.*) END " - if $opt{average_per_cust_pkg}; - - $total_sql .= " FROM cust_bill_pkg_detail LEFT JOIN cust_bill_pkg USING ( billpkgnum ) LEFT JOIN cust_bill ON cust_bill_pkg.invnum = cust_bill.invnum @@ -616,10 +645,6 @@ sub cust_bill_pkg_discount { my $total_sql = " SELECT COALESCE( SUM( cust_bill_pkg_discount.amount ), 0 ) "; - #$total_sql .= - # " / CASE COUNT(cust_pkg.*) WHEN 0 THEN 1 ELSE COUNT(cust_pkg.*) END " - # if $opt{average_per_cust_pkg}; - $total_sql .= " FROM cust_bill_pkg_discount LEFT JOIN cust_bill_pkg USING ( billpkgnum ) diff --git a/httemplate/graph/cust_bill_pkg.cgi b/httemplate/graph/cust_bill_pkg.cgi index 151b29504..ea7fee932 100644 --- a/httemplate/graph/cust_bill_pkg.cgi +++ b/httemplate/graph/cust_bill_pkg.cgi @@ -1,7 +1,7 @@ <% include('elements/monthly.html', #Dumper( 'title' => $title, - 'graph_type' => 'Mountain', + 'graph_type' => $graph_type, 'items' => \@items, 'params' => \@params, 'labels' => \@labels, @@ -10,7 +10,8 @@ 'links' => \@links, 'no_graph' => \@no_graph, 'remove_empty' => 1, - 'bottom_total' => 1, + 'bottom_total' => $show_total, + 'nototal' => !$show_total, 'bottom_link' => $bottom_link, 'agentnum' => $agentnum, 'cust_classnum'=> \@cust_classnums, @@ -30,6 +31,15 @@ my $use_override = $cgi->param('use_override') ? 1 : 0; my $average_per_cust_pkg = $cgi->param('average_per_cust_pkg') ? 1 : 0; my $distribute = $cgi->param('distribute') ? 1 : 0; +my $show_total = 1; +my $graph_type = 'Mountain'; + +if ( $average_per_cust_pkg ) { + # then the rows are not additive + $show_total = 0; + $graph_type = 'LinesPoints'; +} + my %charge_labels = ( 'SR' => 'setup + recurring', 'RU' => 'recurring', @@ -356,6 +366,17 @@ foreach my $agent ( $all_agent || $sel_agent || $FS::CurrentUser::CurrentUser->a } +# may be useful at some point... +#if ( $average_per_cust_pkg ) { +# @items = map { ('cust_bill_pkg', 'cust_bill_pkg_count_pkgnum') } @items; +# @labels = map { $_, "Packages" } @labels; +# @params = map { $_, $_ } @params; +# @links = map { $_, $_ } @links; +# @colors = map { $_, $_ } @colors; +# @no_graph = map { $_, 1 } @no_graph; +#} +# + #use Data::Dumper; if ( $cgi->param('debug') == 1 ) { $FS::Report::Table::DEBUG = 1; -- 2.11.0