'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)
my $order_by = 'censusblock, agentnum, technology, is_consumer, is_business';
- "SELECT ".join(', ', @select) . "
+ "SELECT DISTINCT ".join(', ', @select) . "
FROM $from
WHERE ".join(' AND ', @where)."
ORDER BY $order_by
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)",
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",
";
}
+# 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 = @_;
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
# 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))",
);
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')
;