From: Mark Wells <mark@freeside.biz>
Date: Tue, 14 Jan 2014 02:55:29 +0000 (-0800)
Subject: query optimization for #25459
X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=0a160304352bc27dde67f55e03e0069f7716ad95

query optimization for #25459
---

diff --git a/FS/FS/part_pkg_report_option.pm b/FS/FS/part_pkg_report_option.pm
index 16a4c9864..372b11930 100644
--- a/FS/FS/part_pkg_report_option.pm
+++ b/FS/FS/part_pkg_report_option.pm
@@ -2,7 +2,7 @@ package FS::part_pkg_report_option;
 
 use strict;
 use base qw( FS::Record );
-use FS::Record qw( qsearch qsearchs );
+use FS::Record qw( qsearch qsearchs dbh );
 
 =head1 NAME
 
@@ -111,6 +111,48 @@ sub check {
 
 =back
 
+=head1 CLASS METHODS
+
+=over 4
+
+=item subsets OPTIONNUM, ...
+
+Given a list of report_option numbers, determines all combinations of those
+numbers that exist on actual package definitions.  For each such combination,
+returns an arrayref of report_option numbers, followed by an arrayref of
+corresponding report class names.  This is used for a search optimization.
+
+=cut
+
+# probably doesn't belong here, but there's not a better place for it
+# and optimizations are, by nature, hackish
+
+sub subsets {
+  my ($self, @nums) = @_;
+  my @optionnames = map { "'report_option_$_'" } @nums;
+  my $where = "WHERE optionname IN(".join(',',@optionnames).")"
+    if @nums;
+  my $subselect =
+    "SELECT pkgpart, replace(optionname, 'report_option_', '')::int AS num ".
+    "FROM part_pkg_option $where ".
+    "ORDER BY pkgpart, num";
+  my $select =
+    "SELECT DISTINCT array_to_string(array_agg(num), ','), ".
+    "array_to_string(array_agg(name), ',') ".
+    "FROM ($subselect) AS x JOIN part_pkg_report_option USING (num) ".
+    "GROUP BY pkgpart";
+  my $dbh = dbh;
+  my $sth = $dbh->prepare($select)
+    or die $dbh->errstr; # seriously, this should never happen
+  $sth->execute
+    or die $sth->errstr;
+  # return the first (only) column
+  map { [ split(',',$_->[0]) ],
+        [ split(',',$_->[1]) ] } @{ $sth->fetchall_arrayref };
+}
+
+=back
+
 =head1 BUGS
 
 Overlaps somewhat with pkg_class and pkg_category
diff --git a/httemplate/graph/cust_bill_pkg.cgi b/httemplate/graph/cust_bill_pkg.cgi
index 1b31955c4..44498c85e 100644
--- a/httemplate/graph/cust_bill_pkg.cgi
+++ b/httemplate/graph/cust_bill_pkg.cgi
@@ -103,7 +103,7 @@ if ( $cgi->param('class_mode') eq 'report' ) {
   $value_col = 'classnum';
 }
 
-my @classnums = grep /^\d+$/, $cgi->param($class_param);
+my @classnums = sort {$a <=> $b} grep /^\d+$/, $cgi->param($class_param);
 my @classnames = map { if ( $_ ) {
                          my $class = qsearchs($class_table, {$value_col=>$_} );
                          $class->$name_col;
@@ -128,22 +128,24 @@ if ( $cgi->param('class_agg_break') eq 'aggregate' or
   if ( $cgi->param('class_mode') eq 'report' ) {
     # The new way:
     # Actually break down all subsets of the (selected) report classes.
-    my $powerset = sub {
-      my @set = [];
-      foreach my $x (@_) {
-        @set = map { $_, [ @$_, $x ] } @set;
+    my @subsets = FS::part_pkg_report_option->subsets(@classnums);
+    warn "SUBSETS:\n".Dumper(\@subsets)."\n\n";
+    my @classnum_space = @classnums;
+    @classnums = @classnames = ();
+    while(@subsets) {
+      my $these = shift @subsets;
+      # applied topology!
+      my $not_these = [ @classnum_space ];
+      my $i = 0;
+      foreach (@$these) {
+        $i++ until $not_these->[$i] == $_;
+        splice($not_these, $i, 1);
       }
-      @set;
-    };
-    @classnums = $powerset->(@classnums);
-    @classnames = $powerset->(@classnames);
-    # this is pairwise complementary to @classnums, because math
-    @not_classnums = reverse(@classnums);
-warn Dumper(\@classnums, \@classnames, \@not_classnums);
-    # remove the null set
-    shift @classnums;
-    shift @classnames;
-    shift @not_classnums;
+      push @classnums, $these;
+      push @not_classnums, $not_these;
+      push @classnames, shift @subsets;
+    } #while subsets
+    warn "COMPLEMENTS:\n".Dumper(\@not_classnums)."\n\n";
   }
   # else it's 'pkg', i.e. part_pkg.classnum, which is singular on pkgpart
   # and much simpler