X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2FReport%2FFCC_477.pm;h=75ddee0d7c5c6cb12a72f132c30c710f4f7e4345;hb=121510b6337b7f75f25d583730a65f3fc63bbfbe;hp=93c8dd38ebe19fe859041c35f132ca03d1c31046;hpb=a63883fa4d6944e534c2cfbea1b32ec907d53ffe;p=freeside.git

diff --git a/FS/FS/Report/FCC_477.pm b/FS/FS/Report/FCC_477.pm
index 93c8dd38e..75ddee0d7 100644
--- a/FS/FS/Report/FCC_477.pm
+++ b/FS/FS/Report/FCC_477.pm
@@ -400,7 +400,8 @@ sub fbs_sql {
 
   my @select = (
     "$censustract AS censustract",
-    'technology',
+    '(technology - technology % 10) AS media_type',
+      # media types are multiples of 10
     'broadband_downstream',
     'broadband_upstream',
     "SUM($q)",
@@ -577,6 +578,10 @@ sub lts_sql {
   ";
 }
 
+# voip_sql has a special case: the fifth column, "Voice with Internet",
+# must test whether there are _any_ broadband packages at the same location,
+# not just whether this package is both VoIP and broadband.
+
 sub voip_sql {
   my $class = shift;
   my %opt = @_;
@@ -584,6 +589,18 @@ sub voip_sql {
   my $agentnum = $opt{agentnum};
   my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
 
+  # subquery to test whether there's an is_broadband package at this location
+  my $broadband_pkg =
+    "SELECT 1 FROM cust_pkg AS broadband_pkg
+    WHERE broadband_pkg.locationnum = cust_pkg.locationnum 
+    AND EXISTS(SELECT 1 FROM part_pkg_fcc_option
+      WHERE fccoptionname = 'is_broadband'
+      AND part_pkg_fcc_option.pkgpart = broadband_pkg.pkgpart
+      AND optionvalue = '1')
+    AND ".  active_on( $date );
+
+  my $has_broadband = "EXISTS($broadband_pkg)";
+
   my @select = (
     "state.fips",
     # OTT, OTT + consumer
@@ -592,10 +609,10 @@ sub voip_sql {
     # non-OTT: total, consumer, broadband bundle, media types
     "SUM($q * (CASE WHEN (voip_lastmile = 1) THEN 1 ELSE 0 END))",
     "SUM($q * (CASE WHEN (voip_lastmile = 1 AND is_consumer = 1) THEN 1 ELSE 0 END))",
-    "SUM($q * (CASE WHEN (voip_lastmile = 1 AND is_broadband = 1) THEN 1 ELSE 0 END))",
+    "SUM($q * (CASE WHEN (voip_lastmile = 1 AND $has_broadband) THEN 1 ELSE 0 END))",
     "SUM($q * (CASE WHEN (voip_lastmile = 1 AND media = 'Copper') THEN 1 ELSE 0 END))",
-    "SUM($q * (CASE WHEN (voip_lastmile = 1 AND media = 'Cable Modem') THEN 1 ELSE 0 END))",
     "SUM($q * (CASE WHEN (voip_lastmile = 1 AND media = 'Fiber') THEN 1 ELSE 0 END))",
+    "SUM($q * (CASE WHEN (voip_lastmile = 1 AND media = 'Cable Modem') THEN 1 ELSE 0 END))",
     "SUM($q * (CASE WHEN (voip_lastmile = 1 AND media = 'Fixed Wireless') THEN 1 ELSE 0 END))",
     "SUM($q * (CASE WHEN (voip_lastmile = 1 AND media NOT IN('Copper', 'Fiber', 'Cable Modem', 'Fixed Wireless') ) THEN 1 ELSE 0 END))",
   );
@@ -608,7 +625,7 @@ sub voip_sql {
       JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
       JOIN part_pkg USING (pkgpart) '.
       join_optionnames_int(
-        qw( is_voip is_broadband is_consumer voip_lastmile)
+        qw( is_voip is_consumer voip_lastmile)
       ).
       join_optionnames('media')
   ;