X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2FReport%2FFCC_477.pm;h=3749bfebe2a35fcd9166fe9ff159f5a2ee25cea8;hb=HEAD;hp=af45b2daca966f8a32f6ec6ab309649f2d20c583;hpb=0eedfd553057f9fd8d69197675f33dbc893e6c51;p=freeside.git

diff --git a/FS/FS/Report/FCC_477.pm b/FS/FS/Report/FCC_477.pm
index af45b2dac..3749bfebe 100644
--- a/FS/FS/Report/FCC_477.pm
+++ b/FS/FS/Report/FCC_477.pm
@@ -322,7 +322,7 @@ sub report {
     if ( $class->can($check_method) ) { # they don't all have these
       my $eh = $class->$check_method( $row );
       $num_errors++ if keys(%$eh);
-      push $error, $eh
+      push @$error, $eh
     }
     push @$detail, pop @$row; # this comes from the query
   }
@@ -365,10 +365,12 @@ sub fbd_sql {
     'adv_speed_down',
     'adv_speed_up',
     'CASE WHEN is_business IS NOT NULL THEN 1 ELSE 0 END',
-    'cir_speed_down',
-    'cir_speed_up',
   );
-  push @select, 'blocknum' if $opt{detail};
+  push @select, 'cir_speed_down', 'cir_speed_up'
+    if $opt{date} < 1569826800; #9/30/2019, halfway between the two filing
+                                # "as of" dates when it changed
+  push @select, 'blocknum'
+    if $opt{detail};
 
   my $from = 'deploy_zone_block
     JOIN deploy_zone USING (zonenum)
@@ -380,9 +382,10 @@ sub fbd_sql {
   );
   push @where, "agentnum = $agentnum" if $agentnum;
 
-  my $order_by = 'censusblock, agentnum, technology, is_consumer, is_business';
+  #my $order_by = 'censusblock, agentnum, technology, is_consumer, is_business';
+  my $order_by = 'censusblock, technology';
 
-  "SELECT ".join(', ', @select) . "
+  "SELECT DISTINCT ".join(', ', @select) . "
   FROM $from
   WHERE ".join(' AND ', @where)."
   ORDER BY $order_by
@@ -396,11 +399,12 @@ sub fbs_sql {
   my $agentnum = $opt{agentnum};
   my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
 
-  my $censustract = "replace(cust_location.censustract, '.', '')";
+  my $censustract = "substr( replace(cust_location.censustract, '.', ''), 1, 11)";
 
   my @select = (
     "$censustract AS censustract",
-    'technology',
+    '(technology - technology % 10) AS media_type',
+      # media types are multiples of 10
     'broadband_downstream',
     'broadband_upstream',
     "SUM($q)",
@@ -470,7 +474,7 @@ sub fvs_sql {
   my $date = $opt{date} || time;
   my $agentnum = $opt{agentnum};
   my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
-  my $censustract = "replace(cust_location.censustract, '.', '')";
+  my $censustract = "substr( replace(cust_location.censustract, '.', ''), 1, 11)";
 
   my @select = (
     "$censustract AS censustract",
@@ -577,6 +581,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 +592,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 +612,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 +628,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')
   ;