From e5770f1c578ba47f730488df7fb5a03307ac71ab Mon Sep 17 00:00:00 2001
From: Mark Wells <mark@freeside.biz>
Date: Wed, 13 Aug 2014 19:19:18 -0700
Subject: [PATCH] avoid counting location-changed packages in total canceled
 packages

---
 FS/FS/cust_pkg/Search.pm       | 23 +++++++++++++++++++----
 httemplate/browse/part_pkg.cgi | 17 +++++++++++++++--
 2 files changed, 34 insertions(+), 6 deletions(-)

diff --git a/FS/FS/cust_pkg/Search.pm b/FS/FS/cust_pkg/Search.pm
index 543ef1a61..1a9132df6 100644
--- 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
 
@@ -208,6 +210,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
diff --git a/httemplate/browse/part_pkg.cgi b/httemplate/browse/part_pkg.cgi
index 63822c7c9..1eb55c88a 100755
--- a/httemplate/browse/part_pkg.cgi
+++ b/httemplate/browse/part_pkg.cgi
@@ -108,6 +108,14 @@ my $count_cust_pkg = "
     WHERE cust_pkg.pkgpart = part_pkg.pkgpart
       AND $agentnums_sql
 ";
+my $count_cust_pkg_cancel = "
+  SELECT COUNT(*) FROM cust_pkg LEFT JOIN cust_main USING ( custnum )
+    LEFT JOIN cust_pkg AS cust_pkg_next
+      ON (cust_pkg.pkgnum = cust_pkg_next.change_pkgnum)
+    WHERE cust_pkg.pkgpart = part_pkg.pkgpart
+      AND $agentnums_sql
+      AND cust_pkg.cancel IS NOT NULL AND cust_pkg.cancel != 0
+";
 
 $select = "
 
@@ -137,11 +145,16 @@ $select = "
       AND ( setup IS NULL OR setup = 0 )
   ) AS num_on_hold,
 
-  ( $count_cust_pkg
-      AND cancel IS NOT NULL AND cancel != 0
+  ( $count_cust_pkg_cancel
+      AND (cust_pkg_next.pkgnum IS NULL
+           OR cust_pkg_next.pkgpart != cust_pkg.pkgpart)
   ) AS num_cancelled
 
 ";
+# About the num_cancelled expression: packages that were changed, but 
+# kept the same pkgpart, are considered "moved", not "canceled" (because
+# this is the part_pkg UI).  We could show the count of those but it's 
+# probably not interesting.
 
 my $html_init = qq!
     One or more service definitions are grouped together into a package 
-- 
2.11.0