From f0097d06a2a96a49340fc2420f1713aa692d45a8 Mon Sep 17 00:00:00 2001 From: mark Date: Fri, 21 May 2010 19:09:18 +0000 Subject: [PATCH] RT#8504: option to aggregate agents on sales report --- FS/FS/Report/Table/Monthly.pm | 68 ++++++++++++++++++++++++++++-- httemplate/graph/cust_bill_pkg.cgi | 34 +++++++++------ httemplate/graph/report_cust_bill_pkg.html | 5 ++- 3 files changed, 89 insertions(+), 18 deletions(-) diff --git a/FS/FS/Report/Table/Monthly.pm b/FS/FS/Report/Table/Monthly.pm index fa9949d49..19c0b216d 100644 --- a/FS/FS/Report/Table/Monthly.pm +++ b/FS/FS/Report/Table/Monthly.pm @@ -1,13 +1,14 @@ package FS::Report::Table::Monthly; use strict; -use vars qw( @ISA ); +use vars qw( @ISA $DEBUG ); use Time::Local; use FS::UID qw( dbh ); use FS::Report::Table; use FS::CurrentUser; @ISA = qw( FS::Report::Table ); +$DEBUG = 0; # turning this on will trace all SQL statements, VERY noisy =head1 NAME @@ -307,6 +308,64 @@ sub _subtract_11mo { timelocal($sec,$min,$hour,$mday,$mon,$year); } +sub cust_pkg_setup_cost { + my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_; + my $where = ''; + my $comparison = ''; + if ( $opt{'classnum'} =~ /^(\d+)$/ ) { + if ( $1 == 0 ) { + $comparison = 'IS NULL'; + } + else { + $comparison = "= $1"; + } + $where = "AND part_pkg.classnum $comparison"; + } + $agentnum ||= $opt{'agentnum'}; + + my $total_sql = " SELECT SUM(part_pkg.setup_cost) "; + $total_sql .= " FROM cust_pkg + LEFT JOIN cust_main USING ( custnum ) + LEFT JOIN part_pkg USING ( pkgpart ) + WHERE pkgnum != 0 + $where + AND ".$self->in_time_period_and_agent( + $speriod, $eperiod, $agentnum, 'cust_pkg.setup'); + return $self->scalar_sql($total_sql); +} + +sub cust_pkg_recur_cost { + my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_; + my $where = ''; + my $comparison = ''; + if ( $opt{'classnum'} =~ /^(\d+)$/ ) { + if ( $1 == 0 ) { + $comparison = 'IS NULL'; + } + else { + $comparison = "= $1"; + } + $where = " AND part_pkg.classnum $comparison"; + } + $agentnum ||= $opt{'agentnum'}; + # duplication of in_time_period_and_agent + # because we do it a little differently here + $where .= " AND cust_main.agentnum = $agentnum" if $agentnum; + $where .= " AND ". + $FS::CurrentUser::CurrentUser->agentnums_sql('table' => 'cust_main'); + + my $total_sql = " SELECT SUM(part_pkg.recur_cost) "; + $total_sql .= " FROM cust_pkg + LEFT JOIN cust_main USING ( custnum ) + LEFT JOIN part_pkg USING ( pkgpart ) + WHERE pkgnum != 0 + $where + AND cust_pkg.setup < $eperiod + AND (cust_pkg.cancel > $speriod OR cust_pkg.cancel IS NULL) + "; + return $self->scalar_sql($total_sql); +} + sub cust_bill_pkg { my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_; @@ -320,12 +379,12 @@ sub cust_bill_pkg { } if ( $opt{'use_override'} ) { - $where = "( + $where = "AND ( part_pkg.classnum $comparison AND pkgpart_override IS NULL OR override.classnum $comparison AND pkgpart_override IS NOT NULL )"; } else { - $where = "part_pkg.classnum $comparison"; + $where = "AND part_pkg.classnum $comparison"; } } @@ -346,7 +405,7 @@ sub cust_bill_pkg { LEFT JOIN part_pkg USING ( pkgpart ) LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart WHERE pkgnum != 0 - AND $where + $where AND ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum); if ($opt{use_usage} && $opt{use_usage} eq 'recurring') { @@ -463,6 +522,7 @@ sub in_time_period_and_agent { sub scalar_sql { my( $self, $sql ) = ( shift, shift ); + warn "FS::Report::Table::Monthly\n$sql\n" if $DEBUG; my $sth = dbh->prepare($sql) or die dbh->errstr; $sth->execute or die "Unexpected error executing statement $sql: ". $sth->errstr; diff --git a/httemplate/graph/cust_bill_pkg.cgi b/httemplate/graph/cust_bill_pkg.cgi index ccd41b8a9..224fbbb42 100644 --- a/httemplate/graph/cust_bill_pkg.cgi +++ b/httemplate/graph/cust_bill_pkg.cgi @@ -1,4 +1,5 @@ <% include('elements/monthly.html', + #Dumper( 'title' => $title, 'graph_type' => 'Mountain', 'items' => \@items, @@ -26,8 +27,12 @@ my $use_usage = $cgi->param('use_usage') ? 1 : 0; my $average_per_cust_pkg = $cgi->param('average_per_cust_pkg') ? 1 : 0; #XXX or virtual -my( $agentnum, $sel_agent ) = ('', ''); -if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { +my( $agentnum, $sel_agent, $all_agent ) = ('', '', ''); +if ( $cgi->param('agentnum') eq 'all' ) { + $agentnum = 0; + $all_agent = 'ALL'; +} +elsif ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { $agentnum = $1; $bottom_link .= "agentnum=$agentnum;"; $sel_agent = qsearchs('agent', { 'agentnum' => $agentnum } ); @@ -45,18 +50,21 @@ $title .= ', average per customer package' if $average_per_cust_pkg; # not specified: all classes # 0: empty class # N: classnum - #false lazinessish w/FS::cust_pkg::search_sql (previously search/cust_pkg.cgi) my $classnum = 0; my @pkg_class = (); -if ( $cgi->param('classnum') =~ /^(\d*)$/ ) { +my $all_class = ''; +if ( $cgi->param('classnum') eq 'all' ) { + $all_class = 'ALL'; + @pkg_class = (''); +} +elsif ( $cgi->param('classnum') =~ /^(\d*)$/ ) { $classnum = $1; - if ( $classnum ) { #a specific class @pkg_class = ( qsearchs('pkg_class', { 'classnum' => $classnum } ) ); die "classnum $classnum not found!" unless $pkg_class[0]; - $title .= $pkg_class[0]->classname.' '; + $title .= ' '.$pkg_class[0]->classname.' '; $bottom_link .= "classnum=$classnum;"; } elsif ( $classnum eq '' ) { #the empty class @@ -85,7 +93,7 @@ my @labels = (); my @colors = (); my @links = (); -foreach my $agent ( $sel_agent || qsearch('agent', { 'disabled' => '' } ) ) { +foreach my $agent ( $all_agent || $sel_agent || qsearch('agent', { 'disabled' => '' } ) ) { my $col_scheme = Color::Scheme->new ->from_hue($hue) #->from_hex($agent->color) @@ -104,7 +112,7 @@ foreach my $agent ( $sel_agent || qsearch('agent', { 'disabled' => '' } ) ) { push @items, 'cust_bill_pkg'; push @labels, - ( $sel_agent ? '' : $agent->agent.' ' ). + ( $all_agent || $sel_agent ? '' : $agent->agent.' ' ). ( $classnum eq '0' ? ( ref($pkg_class) ? $pkg_class->classname : $pkg_class ) : '' @@ -112,15 +120,16 @@ foreach my $agent ( $sel_agent || qsearch('agent', { 'disabled' => '' } ) ) { " $component"; my $row_classnum = ref($pkg_class) ? $pkg_class->classnum : 0; - my $row_agentnum = $agent->agentnum; - push @params, [ 'classnum' => $row_classnum, - 'agentnum' => $row_agentnum, + my $row_agentnum = $all_agent || $agent->agentnum; + push @params, [ ($all_class ? () : ('classnum' => $row_classnum) ), + ($all_agent ? () : ('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;". + push @links, "$link;".($all_agent ? '' : "agentnum=$row_agentnum;"). + ($all_class ? '' : "classnum=$row_classnum;"). "use_override=$use_override;use_usage=$component;"; @recur_colors = ($col_scheme->colors)[0,4,8,1,5,9] @@ -137,6 +146,5 @@ foreach my $agent ( $sel_agent || qsearch('agent', { 'disabled' => '' } ) ) { } #use Data::Dumper; -#warn Dumper(\@items); diff --git a/httemplate/graph/report_cust_bill_pkg.html b/httemplate/graph/report_cust_bill_pkg.html index 4a6433272..348746514 100644 --- a/httemplate/graph/report_cust_bill_pkg.html +++ b/httemplate/graph/report_cust_bill_pkg.html @@ -9,11 +9,14 @@ <% include('/elements/tr-select-agent.html', 'label' => 'For agent: ', 'disable_empty' => 0, + 'pre_options' => [ 'all' => 'all (aggregate)' ], + 'empty_label' => 'all (breakdown)', ) %> <% include('/elements/tr-select-pkg_class.html', - 'pre_options' => [ '0' => 'all' ], + 'pre_options' => [ 'all' => 'all (aggregate)', + '0' => 'all (breakdown)' ], 'empty_label' => '(empty class)', ) %> -- 2.11.0