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 );
15 FS::Report::FCC_477 - Routines for FCC Form 477 reports
57 'Terrestrial Fixed Wireless',
58 'Terrestrial Mobile Wireless',
59 'Electric Power Line',
66 'unswitched UNE loops',
83 #from the select at http://www.ffiec.gov/census/default.aspx
84 #though this is now in the database, also
86 '01' => 'ALABAMA (AL)',
87 '02' => 'ALASKA (AK)',
88 '04' => 'ARIZONA (AZ)',
89 '05' => 'ARKANSAS (AR)',
90 '06' => 'CALIFORNIA (CA)',
91 '08' => 'COLORADO (CO)',
93 '09' => 'CONNECTICUT (CT)',
94 '10' => 'DELAWARE (DE)',
95 '11' => 'DISTRICT OF COLUMBIA (DC)',
96 '12' => 'FLORIDA (FL)',
97 '13' => 'GEORGIA (GA)',
98 '15' => 'HAWAII (HI)',
100 '16' => 'IDAHO (ID)',
101 '17' => 'ILLINOIS (IL)',
102 '18' => 'INDIANA (IN)',
104 '20' => 'KANSAS (KS)',
105 '21' => 'KENTUCKY (KY)',
107 '22' => 'LOUISIANA (LA)',
108 '23' => 'MAINE (ME)',
109 '24' => 'MARYLAND (MD)',
110 '25' => 'MASSACHUSETTS (MA)',
111 '26' => 'MICHIGAN (MI)',
112 '27' => 'MINNESOTA (MN)',
114 '28' => 'MISSISSIPPI (MS)',
115 '29' => 'MISSOURI (MO)',
116 '30' => 'MONTANA (MT)',
117 '31' => 'NEBRASKA (NE)',
118 '32' => 'NEVADA (NV)',
119 '33' => 'NEW HAMPSHIRE (NH)',
121 '34' => 'NEW JERSEY (NJ)',
122 '35' => 'NEW MEXICO (NM)',
123 '36' => 'NEW YORK (NY)',
124 '37' => 'NORTH CAROLINA (NC)',
125 '38' => 'NORTH DAKOTA (ND)',
128 '40' => 'OKLAHOMA (OK)',
129 '41' => 'OREGON (OR)',
130 '42' => 'PENNSYLVANIA (PA)',
131 '44' => 'RHODE ISLAND (RI)',
132 '45' => 'SOUTH CAROLINA (SC)',
133 '46' => 'SOUTH DAKOTA (SD)',
135 '47' => 'TENNESSEE (TN)',
136 '48' => 'TEXAS (TX)',
138 '50' => 'VERMONT (VT)',
139 '51' => 'VIRGINIA (VA)',
140 '53' => 'WASHINGTON (WA)',
142 '54' => 'WEST VIRGINIA (WV)',
143 '55' => 'WISCONSIN (WI)',
144 '56' => 'WYOMING (WY)',
145 '72' => 'PUERTO RICO (PR)',
148 sub restore_fcc477map {
150 FS::Record::scalar_sql('',"select formvalue from fcc477map where formkey = ?",$key);
157 local $SIG{HUP} = 'IGNORE';
158 local $SIG{INT} = 'IGNORE';
159 local $SIG{QUIT} = 'IGNORE';
160 local $SIG{TERM} = 'IGNORE';
161 local $SIG{TSTP} = 'IGNORE';
162 local $SIG{PIPE} = 'IGNORE';
164 my $oldAutoCommit = $FS::UID::AutoCommit;
165 local $FS::UID::AutoCommit = 0;
168 # lame (should be normal FS::Record access)
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;
208 =head1 THE "NEW" REPORT (October 2014 and later)
216 sub join_optionnames {
217 join(' ', map { join_optionname($_) } @_);
220 sub join_optionnames_int {
221 join(' ', map { join_optionname_int($_) } @_);
224 sub join_optionname {
225 # Returns a FROM phrase to join a specific option into the query (via
226 # part_pkg). The option value will appear as a field with the same name
229 "LEFT JOIN (SELECT pkgpart, optionvalue AS $name FROM part_pkg_fcc_option".
230 " WHERE fccoptionname = '$name') AS t_$name".
231 " ON (part_pkg.pkgpart = t_$name.pkgpart)";
234 sub join_optionname_int {
235 # Returns a FROM phrase to join a specific option into the query (via
236 # part_pkg) and cast it to integer.. Note this does not convert nulls
239 "LEFT JOIN (SELECT pkgpart, CAST(optionvalue AS int) AS $name
240 FROM part_pkg_fcc_option".
241 " WHERE fccoptionname = '$name') AS t_$name".
242 " ON (part_pkg.pkgpart = t_$name.pkgpart)";
246 # Returns a condition to limit packages to those that were setup before a
247 # certain date, and not canceled before that date.
249 # (Strictly speaking this should also exclude suspended packages but
250 # "suspended as of some past date" is a complicated query.)
252 "cust_pkg.setup <= $date AND ".
253 "(cust_pkg.cancel IS NULL OR cust_pkg.cancel > $date)";
256 sub is_fixed_broadband {
257 "is_broadband::int = 1 AND technology::int IN(".join(',',
258 10, 11, 12, 20, 30, 40, 41, 42, 50, 60, 70, 90, 0
264 Returns Part 6 of the 2014 FCC 477 data, as an arrayref of arrayrefs.
266 - date: a timestamp value to count active packages as of that date
267 - agentnum: limit to customers of that agent
269 Part 6 is the broadband subscription detail report. Columns of the
275 (the above columns form a key)
276 - number of subscriptions
277 - number of consumer-grade subscriptions
284 my $date = $opt{date} || time;
285 my $agentnum = $opt{agentnum};
288 'cust_location.censustract',
290 'broadband_downstream',
291 'broadband_upstream',
293 'COUNT(is_consumer)',
297 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
298 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
299 JOIN part_pkg USING (pkgpart) '.
300 join_optionnames_int(qw(
301 is_broadband technology
304 join_optionnames(qw(broadband_downstream broadband_upstream))
310 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
311 my $group_by = 'cust_location.censustract, technology, '.
312 'broadband_downstream, broadband_upstream ';
313 my $order_by = $group_by;
315 my $statement = "SELECT ".join(', ', @select) . "
317 WHERE ".join(' AND ', @where)."
322 warn $statement if $DEBUG;
323 dbh->selectall_arrayref($statement);
328 Returns Part 9 of the 2014 FCC 477 data. Part 9 is the Local Exchange
329 Telephone Subscription report. Columns are:
331 - state FIPS code (key)
332 - wholesale switched voice lines
333 - wholesale unswitched local loops
334 - end-user total lines
335 - end-user lines sold in a package with broadband
336 - consumer-grade lines where you are not the long-distance carrier
337 - consumer-grade lines where the carrier IS the long-distance carrier
338 - business-grade lines where you are not the long-distance carrier
339 - business-grade lines where the carrier IS the long-distance carrier
340 - end-user lines where you own the local loop facility
341 - end-user lines where you lease an unswitched local loop from a LEC
342 - end-user lines resold from another carrier
343 - end-user lines provided over fiber to the premises
344 - end-user lines provided over coaxial
345 - end-user lines provided over fixed wireless
352 my $date = $opt{date} || time;
353 my $agentnum = $opt{agentnum};
358 "SUM(phone_circuits)",
360 "SUM(CASE WHEN is_broadband = 1 THEN phone_lines ELSE 0 END)",
361 "SUM(CASE WHEN is_consumer = 1 AND is_longdistance IS NULL THEN phone_lines ELSE 0 END)",
362 "SUM(CASE WHEN is_consumer = 1 AND is_longdistance = 1 THEN phone_lines ELSE 0 END)",
363 "SUM(CASE WHEN is_consumer IS NULL AND is_longdistance IS NULL THEN phone_lines ELSE 0 END)",
364 "SUM(CASE WHEN is_consumer IS NULL AND is_longdistance = 1 THEN phone_lines ELSE 0 END)",
365 "SUM(CASE WHEN phone_localloop = 'owned' THEN phone_lines ELSE 0 END)",
366 "SUM(CASE WHEN phone_localloop = 'leased' THEN phone_lines ELSE 0 END)",
367 "SUM(CASE WHEN phone_localloop = 'resale' THEN phone_lines ELSE 0 END)",
368 "SUM(CASE WHEN media = 'Fiber' THEN phone_lines ELSE 0 END)",
369 "SUM(CASE WHEN media = 'Cable Modem' THEN phone_lines ELSE 0 END)",
370 "SUM(CASE WHEN media = 'Fixed Wireless' THEN phone_lines ELSE 0 END)",
374 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
375 JOIN state USING (country, state)
376 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
377 JOIN part_pkg USING (pkgpart) '.
378 join_optionnames_int(qw(
379 is_phone is_broadband
380 phone_vges phone_circuits phone_lines
381 is_consumer is_longdistance
383 join_optionnames('media', 'phone_localloop')
389 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
390 my $group_by = 'state.fips';
391 my $order_by = $group_by;
393 my $statement = "SELECT ".join(', ', @select) . "
395 WHERE ".join(' AND ', @where)."
400 warn $statement if $DEBUG;
401 dbh->selectall_arrayref($statement);
407 my $date = $opt{date} || time;
408 my $agentnum = $opt{agentnum};
412 # OTT, OTT + consumer
413 "SUM(CASE WHEN (voip_lastmile IS NULL) THEN 1 ELSE 0 END)",
414 "SUM(CASE WHEN (voip_lastmile IS NULL AND is_consumer = 1) THEN 1 ELSE 0 END)",
415 # non-OTT: total, consumer, broadband bundle, media types
416 "SUM(CASE WHEN (voip_lastmile = 1) THEN 1 ELSE 0 END)",
417 "SUM(CASE WHEN (voip_lastmile = 1 AND is_consumer = 1) THEN 1 ELSE 0 END)",
418 "SUM(CASE WHEN (voip_lastmile = 1 AND is_broadband = 1) THEN 1 ELSE 0 END)",
419 "SUM(CASE WHEN (voip_lastmile = 1 AND media = 'Copper') THEN 1 ELSE 0 END)",
420 "SUM(CASE WHEN (voip_lastmile = 1 AND media = 'Cable Modem') THEN 1 ELSE 0 END)",
421 "SUM(CASE WHEN (voip_lastmile = 1 AND media = 'Fiber') THEN 1 ELSE 0 END)",
422 "SUM(CASE WHEN (voip_lastmile = 1 AND media = 'Fixed Wireless') THEN 1 ELSE 0 END)",
423 "SUM(CASE WHEN (voip_lastmile = 1 AND media NOT IN('Copper', 'Fiber', 'Cable Modem', 'Fixed Wireless') ) THEN 1 ELSE 0 END)",
428 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
429 JOIN state USING (country, state)
430 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
431 JOIN part_pkg USING (pkgpart) '.
432 join_optionnames_int(
433 qw( is_voip is_broadband is_consumer voip_lastmile)
435 join_optionnames('media')
441 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
442 my $group_by = 'state.fips';
443 my $order_by = $group_by;
445 my $statement = "SELECT ".join(', ', @select) . "
447 WHERE ".join(' AND ', @where)."
452 warn $statement if $DEBUG;
453 dbh->selectall_arrayref($statement);
458 Returns part 11 (voice subscription detail), as above.
465 my $date = $opt{date} || time;
466 my $agentnum = $opt{agentnum};
469 'cust_location.censustract',
470 # VoIP indicator (0 for non-VoIP, 1 for VoIP)
471 'COALESCE(is_voip, 0)',
472 # number of lines/subscriptions
473 'SUM(CASE WHEN is_voip = 1 THEN 1 ELSE phone_lines END)',
474 # consumer grade lines/subscriptions
475 'SUM(CASE WHEN is_consumer = 1 THEN ( CASE WHEN is_voip = 1 THEN 1 ELSE phone_lines END) ELSE 0 END)'
479 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
480 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
481 JOIN part_pkg USING (pkgpart) '.
482 join_optionnames_int(qw(
483 is_phone is_voip is_consumer phone_lines
489 "(is_voip = 1 OR is_phone = 1)",
491 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
492 my $group_by = 'cust_location.censustract, COALESCE(is_voip, 0)';
493 my $order_by = $group_by;
495 my $statement = "SELECT ".join(', ', @select) . "
497 WHERE ".join(' AND ', @where)."
502 warn $statement if $DEBUG;
503 dbh->selectall_arrayref($statement);