projects
/
freeside.git
/ blobdiff
commit
grep
author
committer
pickaxe
?
search:
re
summary
|
shortlog
|
log
|
commit
|
commitdiff
|
tree
raw
|
inline
| side by side
477 reporting
[freeside.git]
/
FS
/
FS
/
Report
/
FCC_477.pm
diff --git
a/FS/FS/Report/FCC_477.pm
b/FS/FS/Report/FCC_477.pm
index
c93c919
..
0f5d4ce
100644
(file)
--- a/
FS/FS/Report/FCC_477.pm
+++ b/
FS/FS/Report/FCC_477.pm
@@
-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',
'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 $from = 'deploy_zone_block
JOIN deploy_zone USING (zonenum)
@@
-382,7
+384,7
@@
sub fbd_sql {
my $order_by = 'censusblock, agentnum, technology, is_consumer, is_business';
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
FROM $from
WHERE ".join(' AND ', @where)."
ORDER BY $order_by
@@
-396,11
+398,12
@@
sub fbs_sql {
my $agentnum = $opt{agentnum};
my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
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",
my @select = (
"$censustract AS censustract",
- 'technology',
+ '(technology - technology % 10) AS media_type',
+ # media types are multiples of 10
'broadband_downstream',
'broadband_upstream',
"SUM($q)",
'broadband_downstream',
'broadband_upstream',
"SUM($q)",
@@
-470,7
+473,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 $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",
my @select = (
"$censustract AS censustract",