diff options
| -rw-r--r-- | FS/FS/Report/Table/Monthly.pm | 37 | ||||
| -rw-r--r-- | httemplate/graph/cust_bill_pkg.cgi | 17 | ||||
| -rw-r--r-- | httemplate/graph/report_cust_bill_pkg.html | 4 |
3 files changed, 36 insertions, 22 deletions
diff --git a/FS/FS/Report/Table/Monthly.pm b/FS/FS/Report/Table/Monthly.pm index 845ab150b..fa9949d49 100644 --- a/FS/FS/Report/Table/Monthly.pm +++ b/FS/FS/Report/Table/Monthly.pm @@ -331,11 +331,15 @@ sub cust_bill_pkg { $agentnum ||= $opt{'agentnum'}; - my $usage = cust_bill_pkg_detail(@_); + my $total_sql = + " SELECT COALESCE( SUM(cust_bill_pkg.setup + cust_bill_pkg.recur), 0 ) "; - my $total = $self->scalar_sql(" - SELECT SUM(cust_bill_pkg.setup + cust_bill_pkg.recur) - FROM cust_bill_pkg + $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 LEFT JOIN cust_bill USING ( invnum ) LEFT JOIN cust_main USING ( custnum ) LEFT JOIN cust_pkg USING ( pkgnum ) @@ -343,15 +347,16 @@ sub cust_bill_pkg { LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart WHERE pkgnum != 0 AND $where - AND ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum) - ); + AND ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum); if ($opt{use_usage} && $opt{use_usage} eq 'recurring') { + my $total = $self->scalar_sql($total_sql); + my $usage = cust_bill_pkg_detail(@_); #$speriod, $eperiod, $agentnum, %opt return $total-$usage; } elsif ($opt{use_usage} && $opt{use_usage} eq 'usage') { - return $usage; + return cust_bill_pkg_detail(@_); #$speriod, $eperiod, $agentnum, %opt } else { - return $total; + return $self->scalar_sql($total_sql); } } @@ -391,9 +396,14 @@ sub cust_bill_pkg_detail { my $where = join( ' AND ', @where ); - $self->scalar_sql(" - SELECT SUM(amount) - FROM cust_bill_pkg_detail + my $total_sql = " SELECT SUM(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 LEFT JOIN cust_main USING ( custnum ) @@ -401,8 +411,9 @@ sub cust_bill_pkg_detail { LEFT JOIN part_pkg USING ( pkgpart ) LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart WHERE $where - AND ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum) - ); + AND ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum); + + $self->scalar_sql($total_sql); } diff --git a/httemplate/graph/cust_bill_pkg.cgi b/httemplate/graph/cust_bill_pkg.cgi index 832660ffd..2346d9416 100644 --- a/httemplate/graph/cust_bill_pkg.cgi +++ b/httemplate/graph/cust_bill_pkg.cgi @@ -68,11 +68,9 @@ if ( $cgi->param('classnum') =~ /^(\d*)$/ ) { } #eslaf -my $use_override = 0; -$use_override = 1 if ( $cgi->param('use_override') ); - -my $use_usage = 0; -$use_usage = 1 if ( $cgi->param('use_usage') ); +my $use_override = $cgi->param('use_override') ? 1 : 0; +my $use_usage = $cgi->param('use_usage') ? 1 : 0; +my $average_per_cust_pkg = $cgi->param('average_per_cust_pkg') ? 1 : 0; my $hue = 0; #my $hue_increment = 170; @@ -113,10 +111,11 @@ foreach my $agent ( $sel_agent || qsearch('agent', { 'disabled' => '' } ) ) { my $row_classnum = ref($pkg_class) ? $pkg_class->classnum : 0; my $row_agentnum = $agent->agentnum; - push @params, [ 'classnum' => $row_classnum, - 'agentnum' => $row_agentnum, - 'use_override' => $use_override, - 'use_usage' => $component, + push @params, [ 'classnum' => $row_classnum, + 'agentnum' => $row_agentnum, + 'use_override' => $use_override, + 'use_usage' => $component, + 'average_per_cust_pkg' => $average_per_cust_pkg, ]; push @links, "$link;agentnum=$row_agentnum;classnum=$row_classnum;". diff --git a/httemplate/graph/report_cust_bill_pkg.html b/httemplate/graph/report_cust_bill_pkg.html index 51655a925..4a6433272 100644 --- a/httemplate/graph/report_cust_bill_pkg.html +++ b/httemplate/graph/report_cust_bill_pkg.html @@ -35,6 +35,10 @@ <TD>Separate rated usage from recurring fees</TD> </TR> +<TR> + <TD ALIGN="right"><INPUT TYPE="checkbox" NAME="average_per_cust_pkg" VALUE="1"></TD> + <TD>Average per customer package</TD> +</TR> </TABLE> |