1 package FS::Report::FCC_477;
2 use base qw( FS::Report );
5 use vars qw( @upload @download @technology @part2aoption @part2boption
8 use FS::Record qw( dbh );
17 FS::Report::FCC_477 - Routines for FCC Form 477 reports
59 'Terrestrial Fixed Wireless',
60 'Terrestrial Mobile Wireless',
61 'Electric Power Line',
68 'unswitched UNE loops',
85 #from the select at http://www.ffiec.gov/census/default.aspx
86 #though this is now in the database, also
88 '01' => 'ALABAMA (AL)',
89 '02' => 'ALASKA (AK)',
90 '04' => 'ARIZONA (AZ)',
91 '05' => 'ARKANSAS (AR)',
92 '06' => 'CALIFORNIA (CA)',
93 '08' => 'COLORADO (CO)',
95 '09' => 'CONNECTICUT (CT)',
96 '10' => 'DELAWARE (DE)',
97 '11' => 'DISTRICT OF COLUMBIA (DC)',
98 '12' => 'FLORIDA (FL)',
99 '13' => 'GEORGIA (GA)',
100 '15' => 'HAWAII (HI)',
102 '16' => 'IDAHO (ID)',
103 '17' => 'ILLINOIS (IL)',
104 '18' => 'INDIANA (IN)',
106 '20' => 'KANSAS (KS)',
107 '21' => 'KENTUCKY (KY)',
109 '22' => 'LOUISIANA (LA)',
110 '23' => 'MAINE (ME)',
111 '24' => 'MARYLAND (MD)',
112 '25' => 'MASSACHUSETTS (MA)',
113 '26' => 'MICHIGAN (MI)',
114 '27' => 'MINNESOTA (MN)',
116 '28' => 'MISSISSIPPI (MS)',
117 '29' => 'MISSOURI (MO)',
118 '30' => 'MONTANA (MT)',
119 '31' => 'NEBRASKA (NE)',
120 '32' => 'NEVADA (NV)',
121 '33' => 'NEW HAMPSHIRE (NH)',
123 '34' => 'NEW JERSEY (NJ)',
124 '35' => 'NEW MEXICO (NM)',
125 '36' => 'NEW YORK (NY)',
126 '37' => 'NORTH CAROLINA (NC)',
127 '38' => 'NORTH DAKOTA (ND)',
130 '40' => 'OKLAHOMA (OK)',
131 '41' => 'OREGON (OR)',
132 '42' => 'PENNSYLVANIA (PA)',
133 '44' => 'RHODE ISLAND (RI)',
134 '45' => 'SOUTH CAROLINA (SC)',
135 '46' => 'SOUTH DAKOTA (SD)',
137 '47' => 'TENNESSEE (TN)',
138 '48' => 'TEXAS (TX)',
140 '50' => 'VERMONT (VT)',
141 '51' => 'VIRGINIA (VA)',
142 '53' => 'WASHINGTON (WA)',
144 '54' => 'WEST VIRGINIA (WV)',
145 '55' => 'WISCONSIN (WI)',
146 '56' => 'WYOMING (WY)',
147 '72' => 'PUERTO RICO (PR)',
150 sub restore_fcc477map {
152 FS::Record::scalar_sql('',"select formvalue from fcc477map where formkey = ?",$key);
159 local $SIG{HUP} = 'IGNORE';
160 local $SIG{INT} = 'IGNORE';
161 local $SIG{QUIT} = 'IGNORE';
162 local $SIG{TERM} = 'IGNORE';
163 local $SIG{TSTP} = 'IGNORE';
164 local $SIG{PIPE} = 'IGNORE';
166 my $oldAutoCommit = $FS::UID::AutoCommit;
167 local $FS::UID::AutoCommit = 0;
170 my $sql = "delete from fcc477map where formkey = ?";
171 my $sth = dbh->prepare($sql) or die dbh->errstr;
172 $sth->execute($key) or do {
173 warn "WARNING: Error removing FCC 477 form defaults: " . $sth->errstr;
174 $dbh->rollback if $oldAutoCommit;
177 $sql = "insert into fcc477map (formkey,formvalue) values (?,?)";
178 $sth = dbh->prepare($sql) or die dbh->errstr;
179 $sth->execute($key,$value) or do {
180 warn "WARNING: Error setting FCC 477 form defaults: " . $sth->errstr;
181 $dbh->rollback if $oldAutoCommit;
184 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
189 sub parse_technology_option {
194 for (my $i = 0; $i < scalar(@technology); $i++) {
195 my $value = $cgi->param("part1_technology_option_$i"); #lame
196 save_fcc477map("part1_technology_option_$i",$value)
197 if $save && $value =~ /^\d+$/;
198 push @result, $value =~ /^\d+$/ ? $value : 0;
207 ### everything above this point is unmaintained ###
210 =head1 THE "NEW" REPORT (October 2014 and later)
218 # functions for internal use
220 sub join_optionnames {
221 join(' ', map { join_optionname($_) } @_);
224 sub join_optionnames_int {
225 join(' ', map { join_optionname_int($_) } @_);
228 sub join_optionname {
229 # Returns a FROM phrase to join a specific option into the query (via
230 # part_pkg). The option value will appear as a field with the same name
233 "LEFT JOIN (SELECT pkgpart, optionvalue AS $name FROM part_pkg_fcc_option".
234 " WHERE fccoptionname = '$name') AS t_$name".
235 " ON (part_pkg.pkgpart = t_$name.pkgpart)";
238 sub join_optionname_int {
239 # Returns a FROM phrase to join a specific option into the query (via
240 # part_pkg) and cast it to integer.. Note this does not convert nulls
243 "LEFT JOIN (SELECT pkgpart, CAST(optionvalue AS int) AS $name
244 FROM part_pkg_fcc_option".
245 " WHERE fccoptionname = '$name') AS t_$name".
246 " ON (part_pkg.pkgpart = t_$name.pkgpart)";
250 # Returns an sql expression for the DBA name
251 "COALESCE( deploy_zone.dbaname,
252 (SELECT value FROM conf WHERE conf.name = 'company_name'
253 AND (conf.agentnum = deploy_zone.agentnum
254 OR conf.agentnum IS NULL)
255 ORDER BY conf.agentnum IS NOT NULL DESC
261 # Returns a condition to limit packages to those that were setup before a
262 # certain date, and not canceled before that date.
264 # (Strictly speaking this should also exclude suspended packages but
265 # "suspended as of some past date" is a complicated query.)
267 "cust_pkg.setup <= $date AND ".
268 "(cust_pkg.cancel IS NULL OR cust_pkg.cancel > $date) AND ".
269 "(cust_pkg.change_date IS NULL OR cust_pkg.change_date <= $date)"
272 sub is_fixed_broadband {
273 "is_broadband::int = 1 AND technology::int IN( 10, 11, 12, 20, 30, 40, 41, 42, 50, 60, 70, 90, 0 )"
276 sub is_mobile_broadband {
277 "is_broadband::int = 1 AND technology::int IN( 80, 81, 82, 83, 84, 85, 86, 87, 88)"
281 =item report SECTION, OPTIONS
283 Returns the report section SECTION (see the C<parts> method for section
284 name strings). OPTIONS may contain the following:
286 - date: a timestamp value. Packages that were active on that date will be
289 - agentnum: limit to packages with this agent.
291 - ignore_quantity: if true, package quantities will be ignored (only distinct
292 packages will be counted).
294 The result will be a hashref containing three parallel arrayrefs:
295 - "data", the columns required by the FCC.
296 - "detail", a list of the package numbers included in each row's aggregation
297 - "error", a hashref containing any error status strings in that row. Keys
298 are error identifiers, values are the messages to show the user.
299 as well as an informational item:
300 - "num_errors", the number of rows that contain errors
302 =item report_data SECTION, OPTIONS
304 Returns only the data, not the detail or error columns. This is the part that
305 will be submitted to the FCC.
315 # add the error column
316 my $data = $class->report_data($section, %opt);
319 my $check_method = $section.'_check';
321 foreach my $row (@$data) {
322 if ( $class->can($check_method) ) { # they don't all have these
323 my $eh = $class->$check_method( $row );
324 $num_errors++ if keys(%$eh);
327 push @$detail, pop @$row; # this comes from the query
334 num_errors => $num_errors,
343 my $method = $section.'_sql';
344 die "Report section '$section' is not implemented\n"
345 unless $class->can($method);
346 my $statement = $class->$method(%opt);
348 warn $statement if $DEBUG;
349 my $sth = dbh->prepare($statement);
350 $sth->execute or die $sth->errstr;
351 return $sth->fetchall_arrayref;
357 my $date = $opt{date} || time;
358 my $agentnum = $opt{agentnum};
364 'CASE WHEN is_consumer IS NOT NULL THEN 1 ELSE 0 END',
367 'CASE WHEN is_business IS NOT NULL THEN 1 ELSE 0 END',
369 push @select, 'cir_speed_down', 'cir_speed_up'
370 if $opt{date} < 1569826800; #9/30/2019, halfway between the two filing
371 # "as of" dates when it changed
372 push @select, 'blocknum'
375 my $from = 'deploy_zone_block
376 JOIN deploy_zone USING (zonenum)
377 JOIN agent USING (agentnum)';
380 "active_date < $date",
381 "(expire_date > $date OR expire_date IS NULL)",
383 push @where, "agentnum = $agentnum" if $agentnum;
385 my $order_by = 'censusblock, agentnum, technology, is_consumer, is_business';
387 "SELECT DISTINCT ".join(', ', @select) . "
389 WHERE ".join(' AND ', @where)."
397 my $date = $opt{date} || time;
398 my $agentnum = $opt{agentnum};
399 my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
401 my $censustract = "replace(cust_location.censustract, '.', '')";
404 "$censustract AS censustract",
405 '(technology - technology % 10) AS media_type',
406 # media types are multiples of 10
407 'broadband_downstream',
408 'broadband_upstream',
410 "SUM(COALESCE(is_consumer,0) * $q)",
412 push @select, "array_to_string(array_agg(pkgnum), ',')" if $opt{detail};
416 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
417 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
418 JOIN part_pkg USING (pkgpart) '.
419 join_optionnames_int(qw(
420 is_broadband technology
423 join_optionnames(qw(broadband_downstream broadband_upstream))
429 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
430 my $group_by = "$censustract, technology, broadband_downstream, broadband_upstream ";
431 my $order_by = $group_by;
433 "SELECT ".join(', ', @select) . "
435 WHERE ".join(' AND ', @where)."
447 if ( length($row->[0]) == 0 ) {
448 $e{'censustract_null'} = 'The package location has no census tract.';
449 } elsif ($row->[0] !~ /^\d{11}$/) {
450 $e{'censustract_bad'} = 'The census tract must be exactly 11 digits.';
454 if ( length($row->[1]) == 0 ) {
455 $e{'technology_null'} = 'The package has no technology type.';
459 if ( length($row->[2]) == 0 or length($row->[3]) == 0 ) {
460 $e{'speed_null'} = 'The package is missing downstream or upstream speeds.';
461 } elsif ( $row->[2] !~ /^\d*(\.\d+)?$/ or $row->[3] !~ /^\d*(\.\d+)?$/ ) {
462 $e{'speed_bad'} = 'The downstream and upstream speeds must be decimal numbers in Mbps.';
463 } elsif ( $row->[2] == 0 or $row->[3] == 0 ) {
464 $e{'speed_zero'} = 'The downstream and upstream speeds cannot be zero.';
473 my $date = $opt{date} || time;
474 my $agentnum = $opt{agentnum};
475 my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
476 my $censustract = "replace(cust_location.censustract, '.', '')";
479 "$censustract AS censustract",
480 # VoIP indicator (0 for non-VoIP, 1 for VoIP)
481 'COALESCE(is_voip, 0)',
482 # number of lines/subscriptions
483 "SUM($q * (CASE WHEN is_voip = 1 THEN 1 ELSE phone_lines END))",
484 # consumer grade lines/subscriptions
485 "SUM($q * COALESCE(is_consumer,0) * (CASE WHEN is_voip = 1 THEN voip_sessions ELSE phone_lines END))",
487 push @select, "array_to_string(array_agg(pkgnum), ',')" if $opt{detail};
490 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
491 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
492 JOIN part_pkg USING (pkgpart) '.
493 join_optionnames_int(qw(
494 is_phone is_voip is_consumer phone_lines voip_sessions
500 "(is_voip = 1 OR is_phone = 1)",
502 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
503 my $group_by = "$censustract, COALESCE(is_voip, 0)";
504 my $order_by = $group_by;
506 "SELECT ".join(', ', @select) . "
508 WHERE ".join(' AND ', @where)."
520 if ( length($row->[0]) == 0 ) {
521 $e{'censustract_null'} = 'The package location has no census tract.';
522 } elsif ($row->[0] !~ /^\d{11}$/) {
523 $e{'censustract_bad'} = 'The census tract must be exactly 11 digits.';
531 my $date = $opt{date} || time;
532 my $agentnum = $opt{agentnum};
533 my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
537 "SUM($q * phone_vges)",
538 "SUM($q * phone_circuits)",
539 "SUM($q * phone_lines)",
540 "SUM($q * (CASE WHEN is_broadband = 1 THEN phone_lines ELSE 0 END))",
541 "SUM($q * (CASE WHEN is_consumer = 1 AND phone_longdistance IS NULL THEN phone_lines ELSE 0 END))",
542 "SUM($q * (CASE WHEN is_consumer = 1 AND phone_longdistance = 1 THEN phone_lines ELSE 0 END))",
543 "SUM($q * (CASE WHEN is_consumer IS NULL AND phone_longdistance IS NULL THEN phone_lines ELSE 0 END))",
544 "SUM($q * (CASE WHEN is_consumer IS NULL AND phone_longdistance = 1 THEN phone_lines ELSE 0 END))",
545 "SUM($q * (CASE WHEN phone_localloop = 'owned' THEN phone_lines ELSE 0 END))",
546 "SUM($q * (CASE WHEN phone_localloop = 'leased' THEN phone_lines ELSE 0 END))",
547 "SUM($q * (CASE WHEN phone_localloop = 'resale' THEN phone_lines ELSE 0 END))",
548 "SUM($q * (CASE WHEN media = 'Fiber' THEN phone_lines ELSE 0 END))",
549 "SUM($q * (CASE WHEN media = 'Cable Modem' THEN phone_lines ELSE 0 END))",
550 "SUM($q * (CASE WHEN media = 'Fixed Wireless' THEN phone_lines ELSE 0 END))",
552 push @select, "array_to_string(array_agg(pkgnum),',')" if $opt{detail};
556 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
557 JOIN state USING (country, state)
558 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
559 JOIN part_pkg USING (pkgpart) '.
560 join_optionnames_int(qw(
561 is_phone is_broadband
562 phone_vges phone_circuits phone_lines
563 is_consumer phone_longdistance
565 join_optionnames('media', 'phone_localloop')
571 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
572 my $group_by = 'state.fips';
573 my $order_by = $group_by;
575 "SELECT ".join(', ', @select) . "
577 WHERE ".join(' AND ', @where)."
583 # voip_sql has a special case: the fifth column, "Voice with Internet",
584 # must test whether there are _any_ broadband packages at the same location,
585 # not just whether this package is both VoIP and broadband.
590 my $date = $opt{date} || time;
591 my $agentnum = $opt{agentnum};
592 my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
594 # subquery to test whether there's an is_broadband package at this location
596 "SELECT 1 FROM cust_pkg AS broadband_pkg
597 WHERE broadband_pkg.locationnum = cust_pkg.locationnum
598 AND EXISTS(SELECT 1 FROM part_pkg_fcc_option
599 WHERE fccoptionname = 'is_broadband'
600 AND part_pkg_fcc_option.pkgpart = broadband_pkg.pkgpart
601 AND optionvalue = '1')
602 AND ". active_on( $date );
604 my $has_broadband = "EXISTS($broadband_pkg)";
608 # OTT, OTT + consumer
609 "SUM($q * (CASE WHEN (voip_lastmile IS NULL) THEN 1 ELSE 0 END))",
610 "SUM($q * (CASE WHEN (voip_lastmile IS NULL AND is_consumer = 1) THEN 1 ELSE 0 END))",
611 # non-OTT: total, consumer, broadband bundle, media types
612 "SUM($q * (CASE WHEN (voip_lastmile = 1) THEN 1 ELSE 0 END))",
613 "SUM($q * (CASE WHEN (voip_lastmile = 1 AND is_consumer = 1) THEN 1 ELSE 0 END))",
614 "SUM($q * (CASE WHEN (voip_lastmile = 1 AND $has_broadband) THEN 1 ELSE 0 END))",
615 "SUM($q * (CASE WHEN (voip_lastmile = 1 AND media = 'Copper') THEN 1 ELSE 0 END))",
616 "SUM($q * (CASE WHEN (voip_lastmile = 1 AND media = 'Fiber') THEN 1 ELSE 0 END))",
617 "SUM($q * (CASE WHEN (voip_lastmile = 1 AND media = 'Cable Modem') THEN 1 ELSE 0 END))",
618 "SUM($q * (CASE WHEN (voip_lastmile = 1 AND media = 'Fixed Wireless') THEN 1 ELSE 0 END))",
619 "SUM($q * (CASE WHEN (voip_lastmile = 1 AND media NOT IN('Copper', 'Fiber', 'Cable Modem', 'Fixed Wireless') ) THEN 1 ELSE 0 END))",
621 push @select, "array_to_string(array_agg(pkgnum),',')" if $opt{detail};
625 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
626 JOIN state USING (country, state)
627 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
628 JOIN part_pkg USING (pkgpart) '.
629 join_optionnames_int(
630 qw( is_voip is_consumer voip_lastmile)
632 join_optionnames('media')
638 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
639 my $group_by = 'state.fips';
640 my $order_by = $group_by;
642 "SELECT ".join(', ', @select) . "
644 WHERE ".join(' AND ', @where)."
653 my $date = $opt{date} || time;
654 my $agentnum = $opt{agentnum};
655 my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
659 'broadband_downstream',
660 'broadband_upstream',
662 "SUM(COALESCE(is_consumer, 0) * $q)",
664 push @select, "array_to_string(array_agg(pkgnum),',')" if $opt{detail};
668 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
669 JOIN state USING (country, state)
670 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
671 JOIN part_pkg USING (pkgpart) '.
672 join_optionnames_int(qw(
673 is_broadband technology
676 join_optionnames(qw(broadband_downstream broadband_upstream))
680 is_mobile_broadband()
682 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
683 my $group_by = 'state.fips, broadband_downstream, broadband_upstream ';
684 my $order_by = $group_by;
686 "SELECT ".join(', ', @select) . "
688 WHERE ".join(' AND ', @where)."
697 my $date = $opt{date} || time;
698 my $agentnum = $opt{agentnum};
699 my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
704 "SUM($q * COALESCE(mobile_direct,0))",
706 push @select, "array_to_string(array_agg(pkgnum),',')" if $opt{detail};
710 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
711 JOIN state USING (country, state)
712 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
713 JOIN part_pkg USING (pkgpart) '.
714 join_optionnames_int(qw( is_mobile mobile_direct) )
720 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
721 my $group_by = 'state.fips';
722 my $order_by = $group_by;
724 "SELECT ".join(', ', @select) . "
726 WHERE ".join(' AND ', @where)."
734 Returns a Tie::IxHash reference of the internal short names used for the
735 report sections ('fbd', 'mbs', etc.) to the full names.
739 tie our %parts, 'Tie::IxHash', (
740 fbd => 'Fixed Broadband Deployment',
741 fbs => 'Fixed Broadband Subscription',
742 fvs => 'Fixed Voice Subscription',
743 lts => 'Local Exchange Telephone Subscription',
744 voip => 'Interconnected VoIP Subscription',
745 mbd => 'Mobile Broadband Deployment',
746 mbsa => 'Mobile Broadband Service Availability',
747 mbs => 'Mobile Broadband Subscription',
748 mvd => 'Mobile Voice Deployment',
749 mvs => 'Mobile Voice Subscription',
753 Storable::dclone(\%parts);
756 =item part_table SECTION
758 Returns the name of the primary table that's aggregated in the report section
759 SECTION. The last column of the report returned by the L</report> method is
760 a comma-separated list of record numbers, in this table, that are included in
761 the report line item.
766 my ($class, $part) = @_;
767 if ($part eq 'fbd') {
768 return 'deploy_zone_block';
771 } # add other cases as we add more of the deployment/availability reports