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",
404 'broadband_downstream',
405 'broadband_upstream',
407 "SUM(COALESCE(is_consumer,0) * $q)",
409 push @select, "array_to_string(array_agg(pkgnum), ',')" if $opt{detail};
413 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
414 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
415 JOIN part_pkg USING (pkgpart) '.
416 join_optionnames_int(qw(
417 is_broadband technology
420 join_optionnames(qw(broadband_downstream broadband_upstream))
426 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
427 my $group_by = "$censustract, technology, broadband_downstream, broadband_upstream ";
428 my $order_by = $group_by;
430 "SELECT ".join(', ', @select) . "
432 WHERE ".join(' AND ', @where)."
444 if ( length($row->[0]) == 0 ) {
445 $e{'censustract_null'} = 'The package location has no census tract.';
446 } elsif ($row->[0] !~ /^\d{11}$/) {
447 $e{'censustract_bad'} = 'The census tract must be exactly 11 digits.';
451 if ( length($row->[1]) == 0 ) {
452 $e{'technology_null'} = 'The package has no technology type.';
456 if ( length($row->[2]) == 0 or length($row->[3]) == 0 ) {
457 $e{'speed_null'} = 'The package is missing downstream or upstream speeds.';
458 } elsif ( $row->[2] !~ /^\d*(\.\d+)?$/ or $row->[3] !~ /^\d*(\.\d+)?$/ ) {
459 $e{'speed_bad'} = 'The downstream and upstream speeds must be decimal numbers in Mbps.';
460 } elsif ( $row->[2] == 0 or $row->[3] == 0 ) {
461 $e{'speed_zero'} = 'The downstream and upstream speeds cannot be zero.';
470 my $date = $opt{date} || time;
471 my $agentnum = $opt{agentnum};
472 my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
473 my $censustract = "replace(cust_location.censustract, '.', '')";
476 "$censustract AS censustract",
477 # VoIP indicator (0 for non-VoIP, 1 for VoIP)
478 'COALESCE(is_voip, 0)',
479 # number of lines/subscriptions
480 "SUM($q * (CASE WHEN is_voip = 1 THEN 1 ELSE phone_lines END))",
481 # consumer grade lines/subscriptions
482 "SUM($q * COALESCE(is_consumer,0) * (CASE WHEN is_voip = 1 THEN voip_sessions ELSE phone_lines END))",
484 push @select, "array_to_string(array_agg(pkgnum), ',')" if $opt{detail};
487 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
488 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
489 JOIN part_pkg USING (pkgpart) '.
490 join_optionnames_int(qw(
491 is_phone is_voip is_consumer phone_lines voip_sessions
497 "(is_voip = 1 OR is_phone = 1)",
499 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
500 my $group_by = "$censustract, COALESCE(is_voip, 0)";
501 my $order_by = $group_by;
503 "SELECT ".join(', ', @select) . "
505 WHERE ".join(' AND ', @where)."
517 if ( length($row->[0]) == 0 ) {
518 $e{'censustract_null'} = 'The package location has no census tract.';
519 } elsif ($row->[0] !~ /^\d{11}$/) {
520 $e{'censustract_bad'} = 'The census tract must be exactly 11 digits.';
528 my $date = $opt{date} || time;
529 my $agentnum = $opt{agentnum};
530 my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
534 "SUM($q * phone_vges)",
535 "SUM($q * phone_circuits)",
536 "SUM($q * phone_lines)",
537 "SUM($q * (CASE WHEN is_broadband = 1 THEN phone_lines ELSE 0 END))",
538 "SUM($q * (CASE WHEN is_consumer = 1 AND phone_longdistance IS NULL THEN phone_lines ELSE 0 END))",
539 "SUM($q * (CASE WHEN is_consumer = 1 AND phone_longdistance = 1 THEN phone_lines ELSE 0 END))",
540 "SUM($q * (CASE WHEN is_consumer IS NULL AND phone_longdistance IS NULL THEN phone_lines ELSE 0 END))",
541 "SUM($q * (CASE WHEN is_consumer IS NULL AND phone_longdistance = 1 THEN phone_lines ELSE 0 END))",
542 "SUM($q * (CASE WHEN phone_localloop = 'owned' THEN phone_lines ELSE 0 END))",
543 "SUM($q * (CASE WHEN phone_localloop = 'leased' THEN phone_lines ELSE 0 END))",
544 "SUM($q * (CASE WHEN phone_localloop = 'resale' THEN phone_lines ELSE 0 END))",
545 "SUM($q * (CASE WHEN media = 'Fiber' THEN phone_lines ELSE 0 END))",
546 "SUM($q * (CASE WHEN media = 'Cable Modem' THEN phone_lines ELSE 0 END))",
547 "SUM($q * (CASE WHEN media = 'Fixed Wireless' THEN phone_lines ELSE 0 END))",
549 push @select, "array_to_string(array_agg(pkgnum),',')" if $opt{detail};
553 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
554 JOIN state USING (country, state)
555 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
556 JOIN part_pkg USING (pkgpart) '.
557 join_optionnames_int(qw(
558 is_phone is_broadband
559 phone_vges phone_circuits phone_lines
560 is_consumer phone_longdistance
562 join_optionnames('media', 'phone_localloop')
568 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
569 my $group_by = 'state.fips';
570 my $order_by = $group_by;
572 "SELECT ".join(', ', @select) . "
574 WHERE ".join(' AND ', @where)."
583 my $date = $opt{date} || time;
584 my $agentnum = $opt{agentnum};
585 my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
589 # OTT, OTT + consumer
590 "SUM($q * (CASE WHEN (voip_lastmile IS NULL) THEN 1 ELSE 0 END))",
591 "SUM($q * (CASE WHEN (voip_lastmile IS NULL AND is_consumer = 1) THEN 1 ELSE 0 END))",
592 # non-OTT: total, consumer, broadband bundle, media types
593 "SUM($q * (CASE WHEN (voip_lastmile = 1) THEN 1 ELSE 0 END))",
594 "SUM($q * (CASE WHEN (voip_lastmile = 1 AND is_consumer = 1) THEN 1 ELSE 0 END))",
595 "SUM($q * (CASE WHEN (voip_lastmile = 1 AND is_broadband = 1) THEN 1 ELSE 0 END))",
596 "SUM($q * (CASE WHEN (voip_lastmile = 1 AND media = 'Copper') THEN 1 ELSE 0 END))",
597 "SUM($q * (CASE WHEN (voip_lastmile = 1 AND media = 'Cable Modem') THEN 1 ELSE 0 END))",
598 "SUM($q * (CASE WHEN (voip_lastmile = 1 AND media = 'Fiber') THEN 1 ELSE 0 END))",
599 "SUM($q * (CASE WHEN (voip_lastmile = 1 AND media = 'Fixed Wireless') THEN 1 ELSE 0 END))",
600 "SUM($q * (CASE WHEN (voip_lastmile = 1 AND media NOT IN('Copper', 'Fiber', 'Cable Modem', 'Fixed Wireless') ) THEN 1 ELSE 0 END))",
602 push @select, "array_to_string(array_agg(pkgnum),',')" if $opt{detail};
606 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
607 JOIN state USING (country, state)
608 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
609 JOIN part_pkg USING (pkgpart) '.
610 join_optionnames_int(
611 qw( is_voip is_broadband is_consumer voip_lastmile)
613 join_optionnames('media')
619 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
620 my $group_by = 'state.fips';
621 my $order_by = $group_by;
623 "SELECT ".join(', ', @select) . "
625 WHERE ".join(' AND ', @where)."
634 my $date = $opt{date} || time;
635 my $agentnum = $opt{agentnum};
636 my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
640 'broadband_downstream',
641 'broadband_upstream',
643 "SUM(COALESCE(is_consumer, 0) * $q)",
645 push @select, "array_to_string(array_agg(pkgnum),',')" if $opt{detail};
649 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
650 JOIN state USING (country, state)
651 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
652 JOIN part_pkg USING (pkgpart) '.
653 join_optionnames_int(qw(
654 is_broadband technology
657 join_optionnames(qw(broadband_downstream broadband_upstream))
661 is_mobile_broadband()
663 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
664 my $group_by = 'state.fips, broadband_downstream, broadband_upstream ';
665 my $order_by = $group_by;
667 "SELECT ".join(', ', @select) . "
669 WHERE ".join(' AND ', @where)."
678 my $date = $opt{date} || time;
679 my $agentnum = $opt{agentnum};
680 my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
685 "SUM($q * COALESCE(mobile_direct,0))",
687 push @select, "array_to_string(array_agg(pkgnum),',')" if $opt{detail};
691 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
692 JOIN state USING (country, state)
693 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
694 JOIN part_pkg USING (pkgpart) '.
695 join_optionnames_int(qw( is_mobile mobile_direct) )
701 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
702 my $group_by = 'state.fips';
703 my $order_by = $group_by;
705 "SELECT ".join(', ', @select) . "
707 WHERE ".join(' AND ', @where)."
715 Returns a Tie::IxHash reference of the internal short names used for the
716 report sections ('fbd', 'mbs', etc.) to the full names.
720 tie our %parts, 'Tie::IxHash', (
721 fbd => 'Fixed Broadband Deployment',
722 fbs => 'Fixed Broadband Subscription',
723 fvs => 'Fixed Voice Subscription',
724 lts => 'Local Exchange Telephone Subscription',
725 voip => 'Interconnected VoIP Subscription',
726 mbd => 'Mobile Broadband Deployment',
727 mbsa => 'Mobile Broadband Service Availability',
728 mbs => 'Mobile Broadband Subscription',
729 mvd => 'Mobile Voice Deployment',
730 mvs => 'Mobile Voice Subscription',
734 Storable::dclone(\%parts);
737 =item part_table SECTION
739 Returns the name of the primary table that's aggregated in the report section
740 SECTION. The last column of the report returned by the L</report> method is
741 a comma-separated list of record numbers, in this table, that are included in
742 the report line item.
747 my ($class, $part) = @_;
748 if ($part eq 'fbd') {
749 return 'deploy_zone_block';
752 } # add other cases as we add more of the deployment/availability reports