1 package FS::Report::FCC_477;
2 use base qw( FS::Report );
5 use vars qw( @upload @download @technology @part2aoption @part2boption
9 use FS::Record qw( dbh );
18 FS::Report::FCC_477 - Routines for FCC Form 477 reports
60 'Terrestrial Fixed Wireless',
61 'Terrestrial Mobile Wireless',
62 'Electric Power Line',
69 'unswitched UNE loops',
86 #from the select at http://www.ffiec.gov/census/default.aspx
87 #though this is now in the database, also
89 '01' => 'ALABAMA (AL)',
90 '02' => 'ALASKA (AK)',
91 '04' => 'ARIZONA (AZ)',
92 '05' => 'ARKANSAS (AR)',
93 '06' => 'CALIFORNIA (CA)',
94 '08' => 'COLORADO (CO)',
96 '09' => 'CONNECTICUT (CT)',
97 '10' => 'DELAWARE (DE)',
98 '11' => 'DISTRICT OF COLUMBIA (DC)',
99 '12' => 'FLORIDA (FL)',
100 '13' => 'GEORGIA (GA)',
101 '15' => 'HAWAII (HI)',
103 '16' => 'IDAHO (ID)',
104 '17' => 'ILLINOIS (IL)',
105 '18' => 'INDIANA (IN)',
107 '20' => 'KANSAS (KS)',
108 '21' => 'KENTUCKY (KY)',
110 '22' => 'LOUISIANA (LA)',
111 '23' => 'MAINE (ME)',
112 '24' => 'MARYLAND (MD)',
113 '25' => 'MASSACHUSETTS (MA)',
114 '26' => 'MICHIGAN (MI)',
115 '27' => 'MINNESOTA (MN)',
117 '28' => 'MISSISSIPPI (MS)',
118 '29' => 'MISSOURI (MO)',
119 '30' => 'MONTANA (MT)',
120 '31' => 'NEBRASKA (NE)',
121 '32' => 'NEVADA (NV)',
122 '33' => 'NEW HAMPSHIRE (NH)',
124 '34' => 'NEW JERSEY (NJ)',
125 '35' => 'NEW MEXICO (NM)',
126 '36' => 'NEW YORK (NY)',
127 '37' => 'NORTH CAROLINA (NC)',
128 '38' => 'NORTH DAKOTA (ND)',
131 '40' => 'OKLAHOMA (OK)',
132 '41' => 'OREGON (OR)',
133 '42' => 'PENNSYLVANIA (PA)',
134 '44' => 'RHODE ISLAND (RI)',
135 '45' => 'SOUTH CAROLINA (SC)',
136 '46' => 'SOUTH DAKOTA (SD)',
138 '47' => 'TENNESSEE (TN)',
139 '48' => 'TEXAS (TX)',
141 '50' => 'VERMONT (VT)',
142 '51' => 'VIRGINIA (VA)',
143 '53' => 'WASHINGTON (WA)',
145 '54' => 'WEST VIRGINIA (WV)',
146 '55' => 'WISCONSIN (WI)',
147 '56' => 'WYOMING (WY)',
148 '72' => 'PUERTO RICO (PR)',
151 sub restore_fcc477map {
153 FS::Record::scalar_sql('',"select formvalue from fcc477map where formkey = ?",$key);
160 local $SIG{HUP} = 'IGNORE';
161 local $SIG{INT} = 'IGNORE';
162 local $SIG{QUIT} = 'IGNORE';
163 local $SIG{TERM} = 'IGNORE';
164 local $SIG{TSTP} = 'IGNORE';
165 local $SIG{PIPE} = 'IGNORE';
167 my $oldAutoCommit = $FS::UID::AutoCommit;
168 local $FS::UID::AutoCommit = 0;
171 my $sql = "delete from fcc477map where formkey = ?";
172 my $sth = dbh->prepare($sql) or die dbh->errstr;
173 $sth->execute($key) or do {
174 warn "WARNING: Error removing FCC 477 form defaults: " . $sth->errstr;
175 $dbh->rollback if $oldAutoCommit;
178 $sql = "insert into fcc477map (formkey,formvalue) values (?,?)";
179 $sth = dbh->prepare($sql) or die dbh->errstr;
180 $sth->execute($key,$value) or do {
181 warn "WARNING: Error setting FCC 477 form defaults: " . $sth->errstr;
182 $dbh->rollback if $oldAutoCommit;
185 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
190 sub parse_technology_option {
195 for (my $i = 0; $i < scalar(@technology); $i++) {
196 my $value = $cgi->param("part1_technology_option_$i"); #lame
197 save_fcc477map("part1_technology_option_$i",$value)
198 if $save && $value =~ /^\d+$/;
199 push @result, $value =~ /^\d+$/ ? $value : 0;
208 ### everything above this point is unmaintained ###
211 =head1 THE "NEW" REPORT (October 2014 and later)
219 # functions for internal use
221 sub join_optionnames {
222 join(' ', map { join_optionname($_) } @_);
225 sub join_optionnames_int {
226 join(' ', map { join_optionname_int($_) } @_);
229 sub join_optionname {
230 # Returns a FROM phrase to join a specific option into the query (via
231 # part_pkg). The option value will appear as a field with the same name
234 "LEFT JOIN (SELECT pkgpart, optionvalue AS $name FROM part_pkg_fcc_option".
235 " WHERE fccoptionname = '$name') AS t_$name".
236 " ON (part_pkg.pkgpart = t_$name.pkgpart)";
239 sub join_optionname_int {
240 # Returns a FROM phrase to join a specific option into the query (via
241 # part_pkg) and cast it to integer.. Note this does not convert nulls
244 "LEFT JOIN (SELECT pkgpart, CAST(optionvalue AS int) AS $name
245 FROM part_pkg_fcc_option".
246 " WHERE fccoptionname = '$name') AS t_$name".
247 " ON (part_pkg.pkgpart = t_$name.pkgpart)";
251 # Returns an sql expression for the DBA name
252 "COALESCE( deploy_zone.dbaname,
253 (SELECT value FROM conf WHERE conf.name = 'company_name'
254 AND (conf.agentnum = deploy_zone.agentnum
255 OR conf.agentnum IS NULL)
256 ORDER BY conf.agentnum IS NOT NULL DESC
262 # Returns a condition to limit packages to those that were setup before a
263 # certain date, and not canceled before that date.
265 # (Strictly speaking this should also exclude suspended packages but
266 # "suspended as of some past date" is a complicated query.)
268 "cust_pkg.setup <= $date AND ".
269 "(cust_pkg.cancel IS NULL OR cust_pkg.cancel > $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)"
280 =item report SECTION, OPTIONS
282 Returns the report section SECTION (see the C<parts> method for section
283 name strings) as an arrayref of arrayrefs. OPTIONS may contain "date"
284 (a timestamp value to run the report as of this date) and "agentnum"
285 (to limit to a single agent).
287 OPTIONS may also contain "detail", a flag that tells the report to return
288 a comma-separated list of the detail records included in each row count.
297 my $method = $section.'_sql';
298 die "Report section '$section' is not implemented\n"
299 unless $class->can($method);
300 my $statement = $class->$method(%opt);
302 my $sth = dbh->prepare($statement);
303 $sth->execute or die $sth->errstr;
304 $sth->fetchall_arrayref;
310 my $date = $opt{date} || time;
312 my $agentnum = $opt{agentnum};
318 'CASE WHEN is_consumer IS NOT NULL THEN 1 ELSE 0 END',
321 'CASE WHEN is_business IS NOT NULL THEN 1 ELSE 0 END',
325 push @select, 'blocknum' if $opt{detail};
327 my $from = 'deploy_zone_block
328 JOIN deploy_zone USING (zonenum)
329 JOIN agent USING (agentnum)';
332 "active_date < $date",
333 "(expire_date > $date OR expire_date IS NULL)",
335 push @where, "agentnum = $agentnum" if $agentnum;
337 my $order_by = 'censusblock, agentnum, technology, is_consumer, is_business';
339 "SELECT ".join(', ', @select) . "
341 WHERE ".join(' AND ', @where)."
349 my $date = $opt{date} || time;
350 my $agentnum = $opt{agentnum};
353 'cust_location.censustract',
355 'broadband_downstream',
356 'broadband_upstream',
358 'COUNT(is_consumer)',
360 push @select, "array_to_string(array_agg(pkgnum), ',')" if $opt{detail};
364 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
365 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
366 JOIN part_pkg USING (pkgpart) '.
367 join_optionnames_int(qw(
368 is_broadband technology
371 join_optionnames(qw(broadband_downstream broadband_upstream))
377 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
378 my $group_by = 'cust_location.censustract, technology, '.
379 'broadband_downstream, broadband_upstream ';
380 my $order_by = $group_by;
382 "SELECT ".join(', ', @select) . "
384 WHERE ".join(' AND ', @where)."
394 my $date = $opt{date} || time;
395 my $agentnum = $opt{agentnum};
398 'cust_location.censustract',
399 # VoIP indicator (0 for non-VoIP, 1 for VoIP)
400 'COALESCE(is_voip, 0)',
401 # number of lines/subscriptions
402 'SUM(CASE WHEN is_voip = 1 THEN 1 ELSE phone_lines END)',
403 # consumer grade lines/subscriptions
404 'SUM(CASE WHEN is_consumer = 1 THEN ( CASE WHEN is_voip = 1 THEN voip_sessions ELSE phone_lines END) ELSE 0 END)',
406 push @select, "array_to_string(array_agg(pkgnum), ',')" if $opt{detail};
409 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
410 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
411 JOIN part_pkg USING (pkgpart) '.
412 join_optionnames_int(qw(
413 is_phone is_voip is_consumer phone_lines voip_sessions
419 "(is_voip = 1 OR is_phone = 1)",
421 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
422 my $group_by = 'cust_location.censustract, COALESCE(is_voip, 0)';
423 my $order_by = $group_by;
425 "SELECT ".join(', ', @select) . "
427 WHERE ".join(' AND ', @where)."
437 my $date = $opt{date} || time;
438 my $agentnum = $opt{agentnum};
443 "SUM(phone_circuits)",
445 "SUM(CASE WHEN is_broadband = 1 THEN phone_lines ELSE 0 END)",
446 "SUM(CASE WHEN is_consumer = 1 AND phone_longdistance IS NULL THEN phone_lines ELSE 0 END)",
447 "SUM(CASE WHEN is_consumer = 1 AND phone_longdistance = 1 THEN phone_lines ELSE 0 END)",
448 "SUM(CASE WHEN is_consumer IS NULL AND phone_longdistance IS NULL THEN phone_lines ELSE 0 END)",
449 "SUM(CASE WHEN is_consumer IS NULL AND phone_longdistance = 1 THEN phone_lines ELSE 0 END)",
450 "SUM(CASE WHEN phone_localloop = 'owned' THEN phone_lines ELSE 0 END)",
451 "SUM(CASE WHEN phone_localloop = 'leased' THEN phone_lines ELSE 0 END)",
452 "SUM(CASE WHEN phone_localloop = 'resale' THEN phone_lines ELSE 0 END)",
453 "SUM(CASE WHEN media = 'Fiber' THEN phone_lines ELSE 0 END)",
454 "SUM(CASE WHEN media = 'Cable Modem' THEN phone_lines ELSE 0 END)",
455 "SUM(CASE WHEN media = 'Fixed Wireless' THEN phone_lines ELSE 0 END)",
457 push @select, "array_to_string(array_agg(pkgnum),',')" if $opt{detail};
461 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
462 JOIN state USING (country, state)
463 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
464 JOIN part_pkg USING (pkgpart) '.
465 join_optionnames_int(qw(
466 is_phone is_broadband
467 phone_vges phone_circuits phone_lines
468 is_consumer phone_longdistance
470 join_optionnames('media', 'phone_localloop')
476 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
477 my $group_by = 'state.fips';
478 my $order_by = $group_by;
480 "SELECT ".join(', ', @select) . "
482 WHERE ".join(' AND ', @where)."
491 my $date = $opt{date} || time;
492 my $agentnum = $opt{agentnum};
496 # OTT, OTT + consumer
497 "SUM(CASE WHEN (voip_lastmile IS NULL) THEN 1 ELSE 0 END)",
498 "SUM(CASE WHEN (voip_lastmile IS NULL AND is_consumer = 1) THEN 1 ELSE 0 END)",
499 # non-OTT: total, consumer, broadband bundle, media types
500 "SUM(CASE WHEN (voip_lastmile = 1) THEN 1 ELSE 0 END)",
501 "SUM(CASE WHEN (voip_lastmile = 1 AND is_consumer = 1) THEN 1 ELSE 0 END)",
502 "SUM(CASE WHEN (voip_lastmile = 1 AND is_broadband = 1) THEN 1 ELSE 0 END)",
503 "SUM(CASE WHEN (voip_lastmile = 1 AND media = 'Copper') THEN 1 ELSE 0 END)",
504 "SUM(CASE WHEN (voip_lastmile = 1 AND media = 'Cable Modem') THEN 1 ELSE 0 END)",
505 "SUM(CASE WHEN (voip_lastmile = 1 AND media = 'Fiber') THEN 1 ELSE 0 END)",
506 "SUM(CASE WHEN (voip_lastmile = 1 AND media = 'Fixed Wireless') THEN 1 ELSE 0 END)",
507 "SUM(CASE WHEN (voip_lastmile = 1 AND media NOT IN('Copper', 'Fiber', 'Cable Modem', 'Fixed Wireless') ) THEN 1 ELSE 0 END)",
509 push @select, "array_to_string(array_agg(pkgnum),',')" if $opt{detail};
513 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
514 JOIN state USING (country, state)
515 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
516 JOIN part_pkg USING (pkgpart) '.
517 join_optionnames_int(
518 qw( is_voip is_broadband is_consumer voip_lastmile)
520 join_optionnames('media')
526 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
527 my $group_by = 'state.fips';
528 my $order_by = $group_by;
530 "SELECT ".join(', ', @select) . "
532 WHERE ".join(' AND ', @where)."
541 my $date = $opt{date} || time;
542 my $agentnum = $opt{agentnum};
546 'broadband_downstream',
547 'broadband_upstream',
549 'COUNT(is_consumer)',
551 push @select, "array_to_string(array_agg(pkgnum),',')" if $opt{detail};
555 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
556 JOIN state USING (country, state)
557 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
558 JOIN part_pkg USING (pkgpart) '.
559 join_optionnames_int(qw(
560 is_broadband technology
563 join_optionnames(qw(broadband_downstream broadband_upstream))
567 is_mobile_broadband()
569 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
570 my $group_by = 'state.fips, broadband_downstream, broadband_upstream ';
571 my $order_by = $group_by;
573 "SELECT ".join(', ', @select) . "
575 WHERE ".join(' AND ', @where)."
584 my $date = $opt{date} || time;
585 my $agentnum = $opt{agentnum};
590 'COUNT(mobile_direct)',
592 push @select, "array_to_string(array_agg(pkgnum),',')" if $opt{detail};
596 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
597 JOIN state USING (country, state)
598 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
599 JOIN part_pkg USING (pkgpart) '.
600 join_optionnames_int(qw( is_mobile mobile_direct) )
606 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
607 my $group_by = 'state.fips';
608 my $order_by = $group_by;
610 "SELECT ".join(', ', @select) . "
612 WHERE ".join(' AND ', @where)."
620 Returns a Tie::IxHash reference of the internal short names used for the
621 report sections ('fbd', 'mbs', etc.) to the full names.
625 tie our %parts, 'Tie::IxHash', (
626 fbd => 'Fixed Broadband Deployment',
627 fbs => 'Fixed Broadband Subscription',
628 fvs => 'Fixed Voice Subscription',
629 lts => 'Local Exchange Telephone Subscription',
630 voip => 'Interconnected VoIP Subscription',
631 mbd => 'Mobile Broadband Deployment',
632 mbsa => 'Mobile Broadband Service Availability',
633 mbs => 'Mobile Broadband Subscription',
634 mvd => 'Mobile Voice Deployment',
635 mvs => 'Mobile Voice Subscription',
639 Storable::dclone(\%parts);
642 =item part_table SECTION
644 Returns the name of the primary table that's aggregated in the report section
645 SECTION. The last column of the report returned by the L</report> method is
646 a comma-separated list of record numbers, in this table, that are included in
647 the report line item.
652 my ($class, $part) = @_;
653 if ($part eq 'fbd') {
654 return 'deploy_zone_block';
657 } # add other cases as we add more of the deployment/availability reports