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',
371 push @select, 'blocknum' if $opt{detail};
373 my $from = 'deploy_zone_block
374 JOIN deploy_zone USING (zonenum)
375 JOIN agent USING (agentnum)';
378 "active_date < $date",
379 "(expire_date > $date OR expire_date IS NULL)",
381 push @where, "agentnum = $agentnum" if $agentnum;
383 my $order_by = 'censusblock, agentnum, technology, is_consumer, is_business';
385 "SELECT ".join(', ', @select) . "
387 WHERE ".join(' AND ', @where)."
395 my $date = $opt{date} || time;
396 my $agentnum = $opt{agentnum};
397 my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
399 my $censustract = "replace(cust_location.censustract, '.', '')";
402 "$censustract AS censustract",
403 '(technology - technology % 10) AS media_type',
404 # media types are multiples of 10
405 'broadband_downstream',
406 'broadband_upstream',
408 "SUM(COALESCE(is_consumer,0) * $q)",
410 push @select, "array_to_string(array_agg(pkgnum), ',')" if $opt{detail};
414 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
415 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
416 JOIN part_pkg USING (pkgpart) '.
417 join_optionnames_int(qw(
418 is_broadband technology
421 join_optionnames(qw(broadband_downstream broadband_upstream))
427 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
428 my $group_by = "$censustract, technology, broadband_downstream, broadband_upstream ";
429 my $order_by = $group_by;
431 "SELECT ".join(', ', @select) . "
433 WHERE ".join(' AND ', @where)."
445 if ( length($row->[0]) == 0 ) {
446 $e{'censustract_null'} = 'The package location has no census tract.';
447 } elsif ($row->[0] !~ /^\d{11}$/) {
448 $e{'censustract_bad'} = 'The census tract must be exactly 11 digits.';
452 if ( length($row->[1]) == 0 ) {
453 $e{'technology_null'} = 'The package has no technology type.';
457 if ( length($row->[2]) == 0 or length($row->[3]) == 0 ) {
458 $e{'speed_null'} = 'The package is missing downstream or upstream speeds.';
459 } elsif ( $row->[2] !~ /^\d*(\.\d+)?$/ or $row->[3] !~ /^\d*(\.\d+)?$/ ) {
460 $e{'speed_bad'} = 'The downstream and upstream speeds must be decimal numbers in Mbps.';
461 } elsif ( $row->[2] == 0 or $row->[3] == 0 ) {
462 $e{'speed_zero'} = 'The downstream and upstream speeds cannot be zero.';
471 my $date = $opt{date} || time;
472 my $agentnum = $opt{agentnum};
473 my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
474 my $censustract = "replace(cust_location.censustract, '.', '')";
477 "$censustract AS censustract",
478 # VoIP indicator (0 for non-VoIP, 1 for VoIP)
479 'COALESCE(is_voip, 0)',
480 # number of lines/subscriptions
481 "SUM($q * (CASE WHEN is_voip = 1 THEN 1 ELSE phone_lines END))",
482 # consumer grade lines/subscriptions
483 "SUM($q * COALESCE(is_consumer,0) * (CASE WHEN is_voip = 1 THEN voip_sessions ELSE phone_lines END))",
485 push @select, "array_to_string(array_agg(pkgnum), ',')" if $opt{detail};
488 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
489 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
490 JOIN part_pkg USING (pkgpart) '.
491 join_optionnames_int(qw(
492 is_phone is_voip is_consumer phone_lines voip_sessions
498 "(is_voip = 1 OR is_phone = 1)",
500 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
501 my $group_by = "$censustract, COALESCE(is_voip, 0)";
502 my $order_by = $group_by;
504 "SELECT ".join(', ', @select) . "
506 WHERE ".join(' AND ', @where)."
518 if ( length($row->[0]) == 0 ) {
519 $e{'censustract_null'} = 'The package location has no census tract.';
520 } elsif ($row->[0] !~ /^\d{11}$/) {
521 $e{'censustract_bad'} = 'The census tract must be exactly 11 digits.';
529 my $date = $opt{date} || time;
530 my $agentnum = $opt{agentnum};
531 my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
535 "SUM($q * phone_vges)",
536 "SUM($q * phone_circuits)",
537 "SUM($q * phone_lines)",
538 "SUM($q * (CASE WHEN is_broadband = 1 THEN phone_lines ELSE 0 END))",
539 "SUM($q * (CASE WHEN is_consumer = 1 AND phone_longdistance IS NULL THEN phone_lines ELSE 0 END))",
540 "SUM($q * (CASE WHEN is_consumer = 1 AND phone_longdistance = 1 THEN phone_lines ELSE 0 END))",
541 "SUM($q * (CASE WHEN is_consumer IS NULL AND phone_longdistance IS NULL THEN phone_lines ELSE 0 END))",
542 "SUM($q * (CASE WHEN is_consumer IS NULL AND phone_longdistance = 1 THEN phone_lines ELSE 0 END))",
543 "SUM($q * (CASE WHEN phone_localloop = 'owned' THEN phone_lines ELSE 0 END))",
544 "SUM($q * (CASE WHEN phone_localloop = 'leased' THEN phone_lines ELSE 0 END))",
545 "SUM($q * (CASE WHEN phone_localloop = 'resale' THEN phone_lines ELSE 0 END))",
546 "SUM($q * (CASE WHEN media = 'Fiber' THEN phone_lines ELSE 0 END))",
547 "SUM($q * (CASE WHEN media = 'Cable Modem' THEN phone_lines ELSE 0 END))",
548 "SUM($q * (CASE WHEN media = 'Fixed Wireless' THEN phone_lines ELSE 0 END))",
550 push @select, "array_to_string(array_agg(pkgnum),',')" if $opt{detail};
554 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
555 JOIN state USING (country, state)
556 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
557 JOIN part_pkg USING (pkgpart) '.
558 join_optionnames_int(qw(
559 is_phone is_broadband
560 phone_vges phone_circuits phone_lines
561 is_consumer phone_longdistance
563 join_optionnames('media', 'phone_localloop')
569 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
570 my $group_by = 'state.fips';
571 my $order_by = $group_by;
573 "SELECT ".join(', ', @select) . "
575 WHERE ".join(' AND ', @where)."
581 # voip_sql has a special case: the fifth column, "Voice with Internet",
582 # must test whether there are _any_ broadband packages at the same location,
583 # not just whether this package is both VoIP and broadband.
588 my $date = $opt{date} || time;
589 my $agentnum = $opt{agentnum};
590 my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
592 # subquery to test whether there's an is_broadband package at this location
594 "SELECT 1 FROM cust_pkg AS broadband_pkg
595 WHERE broadband_pkg.locationnum = cust_pkg.locationnum
596 AND EXISTS(SELECT 1 FROM part_pkg_fcc_option
597 WHERE fccoptionname = 'is_broadband'
598 AND part_pkg_fcc_option.pkgpart = broadband_pkg.pkgpart
599 AND optionvalue = '1')
600 AND ". active_on( $date );
602 my $has_broadband = "EXISTS($broadband_pkg)";
606 # OTT, OTT + consumer
607 "SUM($q * (CASE WHEN (voip_lastmile IS NULL) THEN 1 ELSE 0 END))",
608 "SUM($q * (CASE WHEN (voip_lastmile IS NULL AND is_consumer = 1) THEN 1 ELSE 0 END))",
609 # non-OTT: total, consumer, broadband bundle, media types
610 "SUM($q * (CASE WHEN (voip_lastmile = 1) THEN 1 ELSE 0 END))",
611 "SUM($q * (CASE WHEN (voip_lastmile = 1 AND is_consumer = 1) THEN 1 ELSE 0 END))",
612 "SUM($q * (CASE WHEN (voip_lastmile = 1 AND $has_broadband) THEN 1 ELSE 0 END))",
613 "SUM($q * (CASE WHEN (voip_lastmile = 1 AND media = 'Copper') THEN 1 ELSE 0 END))",
614 "SUM($q * (CASE WHEN (voip_lastmile = 1 AND media = 'Fiber') THEN 1 ELSE 0 END))",
615 "SUM($q * (CASE WHEN (voip_lastmile = 1 AND media = 'Cable Modem') THEN 1 ELSE 0 END))",
616 "SUM($q * (CASE WHEN (voip_lastmile = 1 AND media = 'Fixed Wireless') THEN 1 ELSE 0 END))",
617 "SUM($q * (CASE WHEN (voip_lastmile = 1 AND media NOT IN('Copper', 'Fiber', 'Cable Modem', 'Fixed Wireless') ) THEN 1 ELSE 0 END))",
619 push @select, "array_to_string(array_agg(pkgnum),',')" if $opt{detail};
623 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
624 JOIN state USING (country, state)
625 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
626 JOIN part_pkg USING (pkgpart) '.
627 join_optionnames_int(
628 qw( is_voip is_consumer voip_lastmile)
630 join_optionnames('media')
636 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
637 my $group_by = 'state.fips';
638 my $order_by = $group_by;
640 "SELECT ".join(', ', @select) . "
642 WHERE ".join(' AND ', @where)."
651 my $date = $opt{date} || time;
652 my $agentnum = $opt{agentnum};
653 my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
657 'broadband_downstream',
658 'broadband_upstream',
660 "SUM(COALESCE(is_consumer, 0) * $q)",
662 push @select, "array_to_string(array_agg(pkgnum),',')" if $opt{detail};
666 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
667 JOIN state USING (country, state)
668 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
669 JOIN part_pkg USING (pkgpart) '.
670 join_optionnames_int(qw(
671 is_broadband technology
674 join_optionnames(qw(broadband_downstream broadband_upstream))
678 is_mobile_broadband()
680 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
681 my $group_by = 'state.fips, broadband_downstream, broadband_upstream ';
682 my $order_by = $group_by;
684 "SELECT ".join(', ', @select) . "
686 WHERE ".join(' AND ', @where)."
695 my $date = $opt{date} || time;
696 my $agentnum = $opt{agentnum};
697 my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
702 "SUM($q * COALESCE(mobile_direct,0))",
704 push @select, "array_to_string(array_agg(pkgnum),',')" if $opt{detail};
708 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
709 JOIN state USING (country, state)
710 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
711 JOIN part_pkg USING (pkgpart) '.
712 join_optionnames_int(qw( is_mobile mobile_direct) )
718 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
719 my $group_by = 'state.fips';
720 my $order_by = $group_by;
722 "SELECT ".join(', ', @select) . "
724 WHERE ".join(' AND ', @where)."
732 Returns a Tie::IxHash reference of the internal short names used for the
733 report sections ('fbd', 'mbs', etc.) to the full names.
737 tie our %parts, 'Tie::IxHash', (
738 fbd => 'Fixed Broadband Deployment',
739 fbs => 'Fixed Broadband Subscription',
740 fvs => 'Fixed Voice Subscription',
741 lts => 'Local Exchange Telephone Subscription',
742 voip => 'Interconnected VoIP Subscription',
743 mbd => 'Mobile Broadband Deployment',
744 mbsa => 'Mobile Broadband Service Availability',
745 mbs => 'Mobile Broadband Subscription',
746 mvd => 'Mobile Voice Deployment',
747 mvs => 'Mobile Voice Subscription',
751 Storable::dclone(\%parts);
754 =item part_table SECTION
756 Returns the name of the primary table that's aggregated in the report section
757 SECTION. The last column of the report returned by the L</report> method is
758 a comma-separated list of record numbers, in this table, that are included in
759 the report line item.
764 my ($class, $part) = @_;
765 if ($part eq 'fbd') {
766 return 'deploy_zone_block';
769 } # add other cases as we add more of the deployment/availability reports