From: Mark Wells <mark@freeside.biz>
Date: Thu, 17 Apr 2014 17:06:56 +0000 (-0700)
Subject: customer signup report, filter by customer and package class, #28025
X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=af167dc766fad944b32ca7dc431b2549943b94dd

customer signup report, filter by customer and package class, #28025
---

diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm
index 17b12ae23..8c45ae145 100644
--- a/FS/FS/Report/Table.pm
+++ b/FS/FS/Report/Table.pm
@@ -32,9 +32,14 @@ options in %opt.
 
 =over 4
 
-=item signups: The number of customers signed up.  Options are "refnum" 
-(limit by advertising source) and "indirect" (boolean, tells us to limit 
-to customers that have a referral_custnum that matches the advertising source).
+=item signups: The number of customers signed up.  Options are:
+
+- cust_classnum: limit to this customer class
+- pkg_classnum: limit to customers with a package of this class.  If this is
+  an arrayref, it's an ANY match.
+- refnum: limit to this advertising source
+- indirect: boolean; limit to customers that have a referral_custnum that
+  matches the advertising source
 
 =cut
 
@@ -57,6 +62,19 @@ sub signups {
   }
 
   push @where, $self->with_cust_classnum(%opt);
+  if ( $opt{'pkg_classnum'} ) {
+    my $classnum = $opt{'pkg_classnum'};
+    $classnum = [ $classnum ] unless ref $classnum;
+    @$classnum = grep /^\d+$/, @$classnum;
+    if (@$classnum) {
+      my $in = 'IN ('. join(',', @$classnum). ')';
+      push @where,
+        "EXISTS(SELECT 1 FROM cust_pkg JOIN part_pkg USING (pkgpart) ".
+               "WHERE cust_pkg.custnum = cust_main.custnum ".
+               "AND part_pkg.classnum $in".
+               ")";
+    }
+  }
 
   $self->scalar_sql(
     "SELECT COUNT(*) FROM cust_main $join WHERE ".join(' AND ', @where)
diff --git a/FS/FS/cust_main/Search.pm b/FS/FS/cust_main/Search.pm
index 76d141035..32806401c 100644
--- a/FS/FS/cust_main/Search.pm
+++ b/FS/FS/cust_main/Search.pm
@@ -950,6 +950,8 @@ sub search {
   }
 
   # pkg_classnum
+  #   all_pkg_classnums
+  #   any_pkg_status
   if ( $params->{'pkg_classnum'} ) {
     my @pkg_classnums = ref( $params->{'pkg_classnum'} ) ?
                           @{ $params->{'pkg_classnum'} } :
@@ -966,11 +968,13 @@ sub search {
           'part_pkg.classnum IN('. join(',', @pkg_classnums).')';
       }
       foreach (@pkg_where) {
-        push @where, "EXISTS(".
+        my $select_pkg = 
           "SELECT 1 FROM cust_pkg JOIN part_pkg USING (pkgpart) WHERE ".
-          "cust_pkg.custnum = cust_main.custnum AND ".
-          $_ . ' AND ' . FS::cust_pkg->active_sql .
-        ')';
+          "cust_pkg.custnum = cust_main.custnum AND $_ ";
+        if ( not $params->{'any_pkg_status'} ) {
+          $select_pkg .= 'AND '.FS::cust_pkg->active_sql;
+        }
+        push @where, "EXISTS($select_pkg)";
       }
     }
   }
diff --git a/httemplate/graph/cust_signup.html b/httemplate/graph/cust_signup.html
index a3eb702f2..28466da02 100644
--- a/httemplate/graph/cust_signup.html
+++ b/httemplate/graph/cust_signup.html
@@ -38,6 +38,24 @@ $title = "$agentname $title" if $agentname;
 my $link = $p.'search/cust_main.html?';
 $link .= "agentnum=$agentnum;" if $agentnum;
 
+my %global_params;
+# cust_classnum
+my @classnums = grep /^\d+$/, $cgi->param('cust_classnum');
+if (@classnums) {
+  $global_params{'cust_classnum'} = [ @classnums ];
+  # classnum here, not cust_classnum, for cust_main.html
+  $link .= "classnum=$_;" foreach @classnums;
+}
+# pkg_classnum
+@classnums = grep /^\d+$/, $cgi->param('pkg_classnum');
+if (@classnums) {
+  $global_params{'pkg_classnum'} = [ @classnums ];
+  $link .= "pkg_classnum=$_;" foreach @classnums;
+  $link .= 'any_pkg_status=1;';
+  # because we report any customer that had the package at any time, not
+  # only those for whom it's still active
+}
+
 my $bottom_link = $link;
 
 my @referral;
@@ -68,7 +86,8 @@ my $hue_increment = 75;
 my @signup_colors;
 
 foreach my $referral (@referral) {
-  my %params = ('refnum' => $referral->refnum) unless $all_referral;
+  my %params = %global_params;
+  $params{'refnum'} = $referral->refnum unless $all_referral;
 
   push @items, 'signups';
   push @labels, ( $all_referral ? 'Signups' : $referral->referral );
diff --git a/httemplate/graph/report_cust_signup.html b/httemplate/graph/report_cust_signup.html
index d4ea8c215..bfe788188 100644
--- a/httemplate/graph/report_cust_signup.html
+++ b/httemplate/graph/report_cust_signup.html
@@ -11,17 +11,29 @@
 <& /elements/tr-select-from_to.html &>
 
 <& /elements/tr-select-agent.html,
-             'curr_value'    => scalar($cgi->param('agentnum')),
-             'label'         => 'For agent: ',
-             'disable_empty' => 0,
+  'curr_value'    => scalar($cgi->param('agentnum')),
+  'label'         => 'For agent: ',
+  'disable_empty' => 0,
 &>
 
 <& /elements/tr-select-part_referral.html,
-             'curr_value'    => scalar($cgi->param('refnum')),
-             'label'         => 'Advertising source: ',
-             'disable_empty' => 0,
-             'pre_options'   => [ 'all' => 'all (aggregate)' ],
-             'empty_label'   => 'all (breakdown)',
+  'curr_value'    => scalar($cgi->param('refnum')),
+  'label'         => 'Advertising source: ',
+  'disable_empty' => 0,
+  'pre_options'   => [ 'all' => 'all (aggregate)' ],
+  'empty_label'   => 'all (breakdown)',
+&>
+
+<& /elements/tr-select-cust_class.html,
+  'field'         => 'cust_classnum',
+  'label'         => 'Customer class ',
+  'multiple'      => 1,
+&>
+
+<& /elements/tr-select-pkg_class.html,
+  'field'         => 'pkg_classnum',
+  'label'         => 'With package of class ',
+  'multiple'      => 1,
 &>
 
 <TR>
diff --git a/httemplate/search/cust_main.html b/httemplate/search/cust_main.html
index b97a4ef60..c0292aab2 100755
--- a/httemplate/search/cust_main.html
+++ b/httemplate/search/cust_main.html
@@ -48,6 +48,7 @@ my @scalars = qw (
   cust_fields flattened_pkgs
   all_tags
   all_pkg_classnums
+  any_pkg_status
 );
 
 for my $param ( @scalars ) {