package churn report filtering by advertising source, tower, and zip code, #26999

[freeside.git] / FS / FS / cust_pkg / Search.pm

diff --git a/FS/FS/cust_pkg/Search.pm b/FS/FS/cust_pkg/Search.pm
index 543ef1a..5091236 100644 (file)
--- a/FS/FS/cust_pkg/Search.pm
+++ b/FS/FS/cust_pkg/Search.pm
@@ -17,13 +17,15 @@ Valid parameters are
 
 =item agentnum
 
-=item magic
+=item status
 
-on hold, active, inactive (or one-time charge), suspended, cancel (or cancelled)
+on hold, active, inactive (or one-time charge), suspended, canceled (or cancelled)
 
-=item status
+=item magic
 
-on hold, active, inactive (or one-time charge), suspended, cancel (or cancelled)
+Equivalent to "status", except that "canceled"/"cancelled" will exclude 
+packages that were changed into a new package with the same pkgpart (i.e.
+location or quantity changes).
 
 =item custom
 
@@ -110,6 +112,12 @@ Limit to packages whose locations have geocodes.
 
 Limit to packages whose locations do not have geocodes.
 
+=item towernum
+
+Limit to packages associated with a svc_broadband, associated with a sector,
+associated with this towernum (or any of these, if it's an arrayref) (or NO
+towernum, if it's zero). This is an extreme niche case.
+
 =back
 
 =cut
@@ -208,6 +216,19 @@ sub search {
     push @where, FS::cust_pkg->cancelled_sql();
 
   }
+  
+  ### special case: "magic" is used in detail links from browse/part_pkg,
+  # where "cancelled" has the restriction "and not replaced with a package
+  # of the same pkgpart".  Be consistent with that.
+  ###
+
+  if ( $params->{'magic'} =~ /^cancell?ed$/ ) {
+    my $new_pkgpart = "SELECT pkgpart FROM cust_pkg AS cust_pkg_next ".
+                      "WHERE cust_pkg_next.change_pkgnum = cust_pkg.pkgnum";
+    # ...may not exist, if this was just canceled and not changed; in that
+    # case give it a "new pkgpart" that never equals the old pkgpart
+    push @where, "COALESCE(($new_pkgpart), 0) != cust_pkg.pkgpart";
+  }
 
   ###
   # parse package class
@@ -336,7 +357,7 @@ sub search {
   }
 
   ###
-  # parse country/state
+  # parse country/state/zip
   ###
   for (qw(state country)) { # parsing rules are the same for these
   if ( exists($params->{$_}) 
@@ -346,6 +367,9 @@ sub search {
       push @where, "cust_location.$_ = '$1'";
     }
   }
+  if ( exists($params->{zip}) ) {
+    push @where, "cust_location.zip = " . dbh->quote($params->{zip});
+  }
 
   ###
   # location_* flags
@@ -418,6 +442,9 @@ sub search {
       "NOT (".FS::cust_pkg->onetime_sql . ")";
   }
   else {
+    my $exclude_change_from = 0;
+    my $exclude_change_to = 0;
+
     foreach my $field (qw( setup last_bill bill adjourn susp expire contract_end change_date cancel )) {
 
       next unless exists($params->{$field});
@@ -433,6 +460,27 @@ sub search {
 
       $orderby ||= "ORDER BY cust_pkg.$field";
 
+      if ( $field eq 'setup' ) {
+        $exclude_change_from = 1;
+      } elsif ( $field eq 'cancel' ) {
+        $exclude_change_to = 1;
+      } elsif ( $field eq 'change_date' ) {
+        # if we are given setup and change_date ranges, and the setup date
+        # falls in _both_ ranges, then include the package whether it was 
+        # a change or not
+        $exclude_change_from = 0;
+      }
+    }
+
+    if ($exclude_change_from) {
+      push @where, "change_pkgnum IS NULL";
+    }
+    if ($exclude_change_to) {
+      # a join might be more efficient here
+      push @where, "NOT EXISTS(
+        SELECT 1 FROM cust_pkg AS changed_to_pkg
+        WHERE cust_pkg.pkgnum = changed_to_pkg.change_pkgnum
+      )";
     }
   }
 
@@ -472,6 +520,26 @@ sub search {
   }
 
   ##
+  # parse the extremely weird 'towernum' param
+  ##
+
+  if ($params->{towernum}) {
+    my $towernum = $params->{towernum};
+    $towernum = [ $towernum ] if !ref($towernum);
+    my $in = join(',', grep /^\d+$/, @$towernum);
+    if (length $in) {
+      # inefficient, but this is an obscure feature
+      eval "use FS::Report::Table";
+      FS::Report::Table->_init_tower_pkg_cache; # probably does nothing
+      push @where, "EXISTS(
+      SELECT 1 FROM tower_pkg_cache
+      WHERE tower_pkg_cache.pkgnum = cust_pkg.pkgnum
+        AND tower_pkg_cache.towernum IN ($in)
+      )"
+    }
+  }
+
+  ##
   # setup queries, links, subs, etc. for the search
   ##