From 8dba5cb96454bf8ec81333ebac381180731396f1 Mon Sep 17 00:00:00 2001
From: levinse <levinse>
Date: Fri, 22 Jul 2011 17:55:47 +0000
Subject: [PATCH] FCC form 477 part II and V issues, RT13057

---
 FS/FS/cust_pkg.pm                 | 3 ++-
 httemplate/search/477partIIA.html | 4 ++--
 httemplate/search/477partIIB.html | 4 ++--
 3 files changed, 6 insertions(+), 5 deletions(-)

diff --git a/FS/FS/cust_pkg.pm b/FS/FS/cust_pkg.pm
index 07b71dc11..fba2de1e0 100644
--- a/FS/FS/cust_pkg.pm
+++ b/FS/FS/cust_pkg.pm
@@ -3005,7 +3005,8 @@ sub search {
   # parse fcc_line
   ###
 
-  push @where,  "part_pkg.fcc_ds0s > 0" if $params->{fcc_line};
+  push @where,  "(part_pkg.fcc_ds0s > 0 OR pkg_class.fcc_ds0s > 0)" 
+                                                        if $params->{fcc_line};
 
   ###
   # parse censustract
diff --git a/httemplate/search/477partIIA.html b/httemplate/search/477partIIA.html
index 80ad39368..9b363ad5e 100755
--- a/httemplate/search/477partIIA.html
+++ b/httemplate/search/477partIIA.html
@@ -51,10 +51,10 @@ my $column_value = sub {
 
   my $count_sql = delete($sql_query->{'count_query'});
   if ( $row == 2 || $row == 4 ) {
-    $count_sql =~ s/COUNT\(\*\) FROM/COALESCE( sum(CASE WHEN cust_main.company IS NULL OR cust_main.company = '' THEN fcc_ds0s END), 0 ) FROM/
+    $count_sql =~ s/COUNT\(\*\) FROM/sum(COALESCE(CASE WHEN cust_main.company IS NULL OR cust_main.company = '' THEN   CASE WHEN part_pkg.fcc_ds0s IS NOT NULL AND part_pkg.fcc_ds0s > 0 THEN part_pkg.fcc_ds0s WHEN pkg_class.fcc_ds0s IS NOT NULL AND pkg_class.fcc_ds0s > 0 THEN pkg_class.fcc_ds0s ELSE 0 END   ELSE 0 END, 0) ) FROM/
       or die "couldn't parse count_sql";
   } else {
-    $count_sql =~ s/COUNT\(\*\) FROM/COALESCE( sum(fcc_ds0s), 0 ) FROM/
+    $count_sql =~ s/COUNT\(\*\) FROM/sum(COALESCE(CASE WHEN part_pkg.fcc_ds0s IS NOT NULL AND part_pkg.fcc_ds0s > 0 THEN part_pkg.fcc_ds0s WHEN pkg_class.fcc_ds0s IS NOT NULL AND pkg_class.fcc_ds0s > 0 THEN pkg_class.fcc_ds0s ELSE 0 END, 0)) FROM/
       or die "couldn't parse count_sql";
   }
 
diff --git a/httemplate/search/477partIIB.html b/httemplate/search/477partIIB.html
index 86f75f3c9..94aa818fb 100755
--- a/httemplate/search/477partIIB.html
+++ b/httemplate/search/477partIIB.html
@@ -47,10 +47,10 @@ my $column_value = sub {
 
   my $count_sql = delete($sql_query->{'count_query'});
   if ( $row == 2 ) {
-    $count_sql =~ s/COUNT\(\*\) FROM/COALESCE( sum(CASE WHEN cust_main.company IS NULL OR cust_main.company = '' THEN fcc_ds0s END), 0 ) FROM/
+    $count_sql =~ s/COUNT\(\*\) FROM/sum(COALESCE(CASE WHEN cust_main.company IS NULL OR cust_main.company = '' THEN   CASE WHEN part_pkg.fcc_ds0s IS NOT NULL AND part_pkg.fcc_ds0s > 0 THEN part_pkg.fcc_ds0s WHEN pkg_class.fcc_ds0s IS NOT NULL AND pkg_class.fcc_ds0s > 0 THEN pkg_class.fcc_ds0s ELSE 0 END   ELSE 0 END, 0) ) FROM/
       or die "couldn't parse count_sql";
   } else {
-    $count_sql =~ s/COUNT\(\*\) FROM/COALESCE( sum(fcc_ds0s), 0 ) FROM/
+    $count_sql =~ s/COUNT\(\*\) FROM/sum(COALESCE(CASE WHEN part_pkg.fcc_ds0s IS NOT NULL AND part_pkg.fcc_ds0s > 0 THEN part_pkg.fcc_ds0s WHEN pkg_class.fcc_ds0s IS NOT NULL AND pkg_class.fcc_ds0s > 0 THEN pkg_class.fcc_ds0s ELSE 0 END, 0)) FROM/
       or die "couldn't parse count_sql";
   }
 
-- 
2.11.0