From ecd1baba392d971b49a50f133349f85aaec548a0 Mon Sep 17 00:00:00 2001 From: Ivan Kohler Date: Sat, 7 Sep 2013 02:05:53 -0700 Subject: [PATCH] select multiple package classes (or report classes) on sales report, RT#24776 --- FS/FS/Report/Table.pm | 85 +++++++-------- httemplate/elements/select-table.html | 6 +- httemplate/graph/cust_bill_pkg.cgi | 169 ++++++++++++++++++----------- httemplate/graph/report_cust_bill_pkg.html | 98 +++++++++++------ httemplate/search/cust_bill_pkg.cgi | 23 ++-- 5 files changed, 233 insertions(+), 148 deletions(-) diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm index ffd2ac490..03ee27391 100644 --- a/FS/FS/Report/Table.pm +++ b/FS/FS/Report/Table.pm @@ -325,16 +325,15 @@ sub _subtract_11mo { 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"; + + if ( $opt{'classnum'} ne '' ) { + my $classnums = $opt{'classnum'}; + $classnums = [ $classnums ] if !ref($classnums); + @$classnums = grep /^\d+$/, @$classnums; + $where .= ' AND COALESCE(part_pkg.classnum,0) IN ('. join(',', @$classnums). + ')'; } + $agentnum ||= $opt{'agentnum'}; my $total_sql = " SELECT SUM(part_pkg.setup_cost) "; @@ -357,16 +356,15 @@ sub cust_pkg_setup_cost { 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"; + + if ( $opt{'classnum'} ne '' ) { + my $classnums = $opt{'classnum'}; + $classnums = [ $classnums ] if !ref($classnums); + @$classnums = grep /^\d+$/, @$classnums; + $where .= ' AND COALESCE(part_pkg.classnum,0) IN ('. join(',', @$classnums). + ')'; } + $agentnum ||= $opt{'agentnum'}; # duplication of in_time_period_and_agent # because we do it a little differently here @@ -592,6 +590,7 @@ sub cust_bill_pkg_detail { sub cust_bill_pkg_discount { my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_; + #need to do this the new multi-classnum way if it gets re-enabled #my $where = ''; #my $comparison = ''; #if ( $opt{'classnum'} =~ /^(\d+)$/ ) { @@ -701,24 +700,20 @@ sub for_opts { } sub with_classnum { - my $self = shift; - my ($classnum, $use_override) = @_; - return '' unless $classnum =~ /^\d+$/; - my $comparison; - if ( $classnum == 0 ) { - $comparison = 'IS NULL'; - } - else { - $comparison = "= $classnum"; - } + my ($self, $classnum, $use_override) = @_; + return '' if $classnum eq ''; + + $classnum = [ $classnum ] if !ref($classnum); + @$classnum = grep /^\d+$/, @$classnum; + my $in = 'IN ('. join(',', @$classnum). ')'; + if ( $use_override ) { - return "( - part_pkg.classnum $comparison AND pkgpart_override IS NULL OR - override.classnum $comparison AND pkgpart_override IS NOT NULL + "( + ( COALESCE(part_pkg.classnum, 0) $in AND pkgpart_override IS NULL) + OR ( COALESCE(override.classnum, 0) $in AND pkgpart_override IS NOT NULL ) )"; - } - else { - return "part_pkg.classnum $comparison"; + } else { + "COALESCE(part_pkg.classnum, 0) $in"; } } @@ -737,14 +732,14 @@ sub with_usageclass { } sub with_report_option { - my $self = shift; + my ($self, $num, $use_override) = @_; # $num can be a single number, or a comma-delimited list of numbers, - # or '0' to match only the empty set. - # + # or an arrayref. 0 matches the empty set # or the word 'multiple' for all packages with more than one report class - my ($num, $use_override) = @_; return '' if !defined($num); + $num = join(',', @$num) if ref($num); + # stringify the set of report options for each pkgpart my $table = $use_override ? 'override' : 'part_pkg'; my $subselect = " @@ -757,14 +752,18 @@ sub with_report_option { my $comparison; if ( $num eq 'multiple' ) { $comparison = "(SELECT COUNT(*) FROM ($subselect) AS x) > 1"; - } elsif ( $num eq '0' ) { - $comparison = "NOT EXISTS ($subselect)"; } else { + + my @num = split(/\s*,\s*/, $num); + #$comparison = "(SELECT COALESCE(string_agg(num, ','), '') FROM ( #Pg 9-ism $comparison = "(SELECT COALESCE(array_to_string(array_agg(num), ','), '') - FROM ( - $subselect - ) AS x) = '$num'"; + FROM ($subselect) AS x + ) = '". join(',', grep $_, @num). "'"; + + $comparison = "( $comparison OR NOT EXISTS ($subselect) )" + if grep !$_, @num; + } if ( $use_override ) { # then also allow the non-override package to match diff --git a/httemplate/elements/select-table.html b/httemplate/elements/select-table.html index b6c1573d1..9f26a3591 100644 --- a/httemplate/elements/select-table.html +++ b/httemplate/elements/select-table.html @@ -37,6 +37,7 @@ Example: 'post_options' => [ 'value' => 'option' ], #after normal options 'empty_label' => '', #better specify it though, the default might change 'multiple' => 0, # bool + 'all_selected' => 0, # useful with multiple 'disable_empty' => 0, # bool (implied by multiple) 'label_showkey' => 0, # bool 'label_callback' => sub { my $record = shift; return "label"; }, @@ -75,8 +76,9 @@ Example: % while ( @pre_options ) { % my $pre_opt = shift(@pre_options); % my $pre_label = shift(@pre_options); -% my $selected = ( ref($value) && $value->{$pre_opt} ) -% || ( $value eq $pre_opt ); +% my $selected = $opt{'all_selected'} +% || ( ref($value) && $value->{$pre_opt} ) +% || ( $value eq $pre_opt );