From d0fcbc3d04250ec54cb5dea7abcc58d1f45d78b1 Mon Sep 17 00:00:00 2001 From: Mark Wells Date: Tue, 16 Jul 2013 16:24:12 -0700 Subject: [PATCH] sales report: filter/breakdown by package report class, #24002 --- FS/FS/Report/Table.pm | 38 ++++++++++++ httemplate/graph/cust_bill_pkg.cgi | 92 +++++++++++++++++++----------- httemplate/graph/report_cust_bill_pkg.html | 62 +++++++++++++++++--- httemplate/search/cust_bill_pkg.cgi | 12 ++++ 4 files changed, 165 insertions(+), 39 deletions(-) diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm index 2e202e5d9..c5a6503c3 100644 --- a/FS/FS/Report/Table.pm +++ b/FS/FS/Report/Table.pm @@ -443,6 +443,7 @@ sub cust_bill_pkg_setup { my @where = ( 'pkgnum != 0', $self->with_classnum($opt{'classnum'}, $opt{'use_override'}), + $self->with_report_option($opt{'report_optionnum'}, $opt{'use_override'}), $self->in_time_period_and_agent($speriod, $eperiod, $agentnum), ); @@ -474,6 +475,7 @@ sub cust_bill_pkg_recur { my @where = ( 'pkgnum != 0', $self->with_classnum($opt{'classnum'}, $opt{'use_override'}), + $self->with_report_option($opt{'report_optionnum'}, $opt{'use_override'}), ); push @where, 'cust_main.refnum = '. $opt{'refnum'} if $opt{'refnum'}; @@ -552,6 +554,7 @@ sub cust_bill_pkg_detail { push @where, $self->with_classnum($opt{'classnum'}, $opt{'use_override'}), $self->with_usageclass($opt{'usageclass'}), + $self->with_report_option($opt{'report_optionnum'}, $opt{'use_override'}), ; if ( $opt{'distribute'} ) { @@ -733,6 +736,41 @@ sub with_usageclass { return "cust_bill_pkg_detail.classnum $comparison"; } +sub with_report_option { + my $self = shift; + # $num can be a single number, or a comma-delimited list of numbers, + # or '0' to match only the empty set. + # + # or the word 'multiple' for all packages with more than one report class + my ($num, $use_override) = @_; + return '' if !defined($num); + + # stringify the set of report options for each pkgpart + my $table = $use_override ? 'override' : 'part_pkg'; + my $subselect = " + SELECT replace(optionname, 'report_option_', '') AS num + FROM part_pkg_option + WHERE optionname like 'report_option_%' + AND part_pkg_option.pkgpart = $table.pkgpart + ORDER BY num"; + + my $comparison; + if ( $num eq 'multiple' ) { + $comparison = "(SELECT COUNT(*) FROM ($subselect) AS x) > 1"; + } elsif ( $num eq '0' ) { + $comparison = "NOT EXISTS ($subselect)"; + } else { + $comparison = "(SELECT COALESCE(string_agg(num, ','), '') FROM ( + $subselect + ) AS x) = '$num'"; + } + if ( $use_override ) { + # then also allow the non-override package to match + $comparison = "( $comparison OR " . $self->with_report_option($num) . ")"; + } + $comparison; +} + sub scalar_sql { my( $self, $sql ) = ( shift, shift ); my $sth = dbh->prepare($sql) or die dbh->errstr; diff --git a/httemplate/graph/cust_bill_pkg.cgi b/httemplate/graph/cust_bill_pkg.cgi index 91bedf3fe..96404a438 100644 --- a/httemplate/graph/cust_bill_pkg.cgi +++ b/httemplate/graph/cust_bill_pkg.cgi @@ -83,35 +83,67 @@ $bottom_link .= "cust_classnum=$_;" foreach @cust_classnums; #false lazinessish w/FS::cust_pkg::search_sql (previously search/cust_pkg.cgi) my $classnum = 0; -my @pkg_class = (); +my (@classnums, @classnames); my $all_class = ''; -if ( $cgi->param('classnum') eq 'all' ) { - $all_class = 'ALL'; - @pkg_class = (''); + +my ($class_table, $name_col, $value_col, $class_param); + +if ( $cgi->param('mode') eq 'report' ) { + $class_param = 'report_optionnum'; # CGI param name, also used in the report engine + $class_table = 'part_pkg_report_option'; # table containing classes + $name_col = 'name'; # the column of that table containing the label + $value_col = 'num'; # the column containing the class number +} else { + $class_param = 'classnum'; + $class_table = 'pkg_class'; + $name_col = 'classname'; + $value_col = 'classnum'; } -elsif ( $cgi->param('classnum') =~ /^(\d*)$/ ) { + +if ( $cgi->param($class_param) eq 'all' ) { # all, aggregated + $all_class = 'ALL'; + @classnums = (''); + @classnames = (''); +} elsif ( $cgi->param($class_param) =~ /^(\d*)$/ ) { + $classnum = $1; if ( $classnum ) { #a specific class + my $class = qsearchs($class_table, { $value_col => $classnum }) + or die "$class_table #$classnum not found"; - @pkg_class = ( qsearchs('pkg_class', { 'classnum' => $classnum } ) ); - die "classnum $classnum not found!" unless $pkg_class[0]; - $title .= ' '.$pkg_class[0]->classname.' '; - $bottom_link .= "classnum=$classnum;"; + $title .= ' '.$class->get($name_col); + $bottom_link .= "$class_param=$classnum;"; - } elsif ( $classnum eq '' ) { #the empty class + @classnums = ($classnum); + @classnames = ($class->get($name_col)); - $title .= 'Empty class '; - @pkg_class = ( '(empty class)' ); - $bottom_link .= "classnum=0;"; + } elsif ( $classnum eq '0' ) { #the empty class - } elsif ( $classnum eq '0' ) { #all classes + $title .= ' Empty class '; + @classnums = ( '' ); + @classnames = ( '(empty class)' ); + $bottom_link .= "$class_param=0;"; - @pkg_class = qsearch('pkg_class', {} ); # { 'disabled' => '' } ); - push @pkg_class, '(empty class)'; + } elsif ( $classnum eq '' ) { #all, breakdown + my @classes = qsearch($class_table, {}); + @classnames = map { $_->get($name_col) } @classes; + @classnums = map { $_->get($value_col) } @classes; + + push @classnames, '(empty class)'; + push @classnums, '0'; + + if ( $cgi->param('mode') eq 'report' ) { + # In theory, a package can belong to any subset of the report classes, + # so the report groups should be all the _subsets_, but for now we're + # handling the simple case where each package belongs to one report + # class. Packages with multiple classes will go into one bin at the + # end. + push @classnames, '(multiple classes)'; + push @classnums, 'multiple'; + } } -} -#eslaf +} #eslaf my $hue = 0; #my $hue_increment = 170; @@ -163,7 +195,9 @@ foreach my $agent ( $all_agent || $sel_agent || qsearch('agent', { 'disabled' => qsearch('part_referral', { 'disabled' => '' } ) ) { - foreach my $pkg_class ( @pkg_class ) { + for (my $i = 0; $i < scalar @classnums; $i++) { + my $row_classnum = $classnums[$i]; + my $row_classname = $classnames[$i]; foreach my $component ( @components ) { push @items, 'cust_bill_pkg'; @@ -171,16 +205,11 @@ foreach my $agent ( $all_agent || $sel_agent || qsearch('agent', { 'disabled' => push @labels, ( $all_agent || $sel_agent ? '' : $agent->agent.' ' ). ( $all_part_referral || $sel_part_referral ? '' : $part_referral->referral.' ' ). - ( $classnum eq '0' - ? ( ref($pkg_class) ? $pkg_class->classname : $pkg_class ) - : '' - ). - ' '.$charge_labels{$component}; + $row_classname . ' ' . $charge_labels{$component}; - my $row_classnum = ref($pkg_class) ? $pkg_class->classnum : 0; my $row_agentnum = $all_agent || $agent->agentnum; my $row_refnum = $all_part_referral || $part_referral->refnum; - push @params, [ ($all_class ? () : ('classnum' => $row_classnum) ), + push @params, [ ($all_class ? () : ($class_param => $row_classnum) ), ($all_agent ? () : ('agentnum' => $row_agentnum) ), ($all_part_referral ? () : ('refnum' => $row_refnum) ), 'use_override' => $use_override, @@ -193,7 +222,7 @@ foreach my $agent ( $all_agent || $sel_agent || qsearch('agent', { 'disabled' => ($all_agent ? '' : "agentnum=$row_agentnum;"). ($all_part_referral ? '' : "refnum=$row_refnum;"). (join('',map {"cust_classnum=$_;"} @cust_classnums)). - ($all_class ? '' : "classnum=$row_classnum;"). + ($all_class ? '' : "$class_param=$row_classnum;"). "distribute=$distribute;". "use_override=$use_override;charges=$component;"; @@ -205,7 +234,7 @@ foreach my $agent ( $all_agent || $sel_agent || qsearch('agent', { 'disabled' => push @no_graph, 0; } #foreach $component - } #foreach $pkg_class + } #foreach $row_classnum } #foreach $part_referral if ( $cgi->param('agent_totals') and !$all_agent ) { @@ -226,11 +255,10 @@ foreach my $agent ( $all_agent || $sel_agent || qsearch('agent', { 'disabled' => "charges=$component"; # Also apply any refnum/classnum filters - if ( !$all_class and scalar(@pkg_class) == 1 ) { + if ( !$all_class and scalar(@classnums) == 1 ) { # then a specific class has been chosen, but it may be the empty class - my $row_classnum = ref($pkg_class[0]) ? $pkg_class[0]->classnum : 0; - push @row_params, 'classnum' => $row_classnum; - $row_link .= ";classnum=$row_classnum"; + push @row_params, $class_param => $classnums[0]; + $row_link .= ";$class_param=".$classnums[0]; } if ( $sel_part_referral ) { push @row_params, 'refnum' => $sel_part_referral->refnum; diff --git a/httemplate/graph/report_cust_bill_pkg.html b/httemplate/graph/report_cust_bill_pkg.html index 251e7d36e..d3d8e664d 100644 --- a/httemplate/graph/report_cust_bill_pkg.html +++ b/httemplate/graph/report_cust_bill_pkg.html @@ -23,6 +23,27 @@ function enable_agent_totals(obj) { ) ); } + +function mode_changed() { + var options = document.getElementsByName('mode'); + var mode; + for(var i=0; i < options.length; i++) { + if (options[i].checked) { + mode = options[i].value; + } + } + + var div_pkg = document.getElementById('pkg_class'); + var div_report = document.getElementById('report_class'); + if (mode == 'pkg') { + div_pkg.style.display = ''; + div_report.style.display = 'none'; + } else if (mode == 'report') { + div_pkg.style.display = 'none'; + div_report.style.display = ''; + } +} +window.onload = mode_changed; <& /elements/tr-select-agent.html, @@ -49,13 +70,40 @@ function enable_agent_totals(obj) { 'onchange' => 'enable_agent_totals' &> -<& /elements/tr-select-pkg_class.html, - 'field' => 'classnum', - 'pre_options' => [ 'all' => 'all (aggregate)', - '0' => 'all (breakdown)' ], - 'empty_label' => '(empty class)', - 'onchange' => 'enable_agent_totals', -&> + + + + <% emt('Package class') %> +
+ + <% emt('Report class') %> + + +
+ <& /elements/select-pkg_class.html, + 'field' => 'classnum', + 'pre_options' => [ 'all' => 'all (aggregate)', + '' => 'all (breakdown)', + '0' => '(empty class)' ], + 'disable_empty' => 1, + 'onchange' => 'enable_agent_totals', + &> +
+ + +