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 my $sql = "delete from fcc477map where formkey = ?";
169 my $sth = dbh->prepare($sql) or die dbh->errstr;
170 $sth->execute($key) or do {
171 warn "WARNING: Error removing FCC 477 form defaults: " . $sth->errstr;
172 $dbh->rollback if $oldAutoCommit;
175 $sql = "insert into fcc477map (formkey,formvalue) values (?,?)";
176 $sth = dbh->prepare($sql) or die dbh->errstr;
177 $sth->execute($key,$value) or do {
178 warn "WARNING: Error setting FCC 477 form defaults: " . $sth->errstr;
179 $dbh->rollback if $oldAutoCommit;
182 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
187 sub parse_technology_option {
192 for (my $i = 0; $i < scalar(@technology); $i++) {
193 my $value = $cgi->param("part1_technology_option_$i"); #lame
194 save_fcc477map("part1_technology_option_$i",$value)
195 if $save && $value =~ /^\d+$/;
196 push @result, $value =~ /^\d+$/ ? $value : 0;
205 ### everything above this point is unmaintained ###
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
262 =item report_fixed_broadband OPTIONS
264 Returns the Fixed Broadband Subscription report (section 5.4), as an arrayref
265 of an arrayrefs. OPTIONS may contain:
266 - date: a timestamp value to count active packages as of that date
267 - agentnum: limit to customers of that agent
269 Columns of this report are:
274 (the above columns form a key)
275 - number of subscriptions
276 - number of consumer-grade subscriptions
280 sub report_fixed_broadband {
283 my $date = $opt{date} || time;
284 my $agentnum = $opt{agentnum};
287 'cust_location.censustract',
289 'broadband_downstream',
290 'broadband_upstream',
292 'COUNT(is_consumer)',
296 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
297 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
298 JOIN part_pkg USING (pkgpart) '.
299 join_optionnames_int(qw(
300 is_broadband technology
303 join_optionnames(qw(broadband_downstream broadband_upstream))
309 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
310 my $group_by = 'cust_location.censustract, technology, '.
311 'broadband_downstream, broadband_upstream ';
312 my $order_by = $group_by;
314 my $statement = "SELECT ".join(', ', @select) . "
316 WHERE ".join(' AND ', @where)."
321 warn $statement if $DEBUG;
322 dbh->selectall_arrayref($statement);
325 =item report_fixed_voice OPTIONS
327 Returns the Fixed Voice Subscription Detail report (section 5.5). OPTIONS
328 are as above. Columns are:
331 - service type (0 for non-VoIP, 1 for VoIP)
332 (the above columns form a key)
333 - VGE lines/VoIP subscriptions in service
334 - consumer grade VGE lines/VoIP subscriptions
338 sub report_fixed_voice {
341 my $date = $opt{date} || time;
342 my $agentnum = $opt{agentnum};
345 'cust_location.censustract',
346 # VoIP indicator (0 for non-VoIP, 1 for VoIP)
347 'COALESCE(is_voip, 0)',
348 # number of lines/subscriptions
349 'SUM(CASE WHEN is_voip = 1 THEN 1 ELSE phone_lines END)',
350 # consumer grade lines/subscriptions
351 'SUM(CASE WHEN is_consumer = 1 THEN ( CASE WHEN is_voip = 1 THEN voip_sessions ELSE phone_lines END) ELSE 0 END)'
355 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
356 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
357 JOIN part_pkg USING (pkgpart) '.
358 join_optionnames_int(qw(
359 is_phone is_voip is_consumer phone_lines voip_sessions
365 "(is_voip = 1 OR is_phone = 1)",
367 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
368 my $group_by = 'cust_location.censustract, COALESCE(is_voip, 0)';
369 my $order_by = $group_by;
371 my $statement = "SELECT ".join(', ', @select) . "
373 WHERE ".join(' AND ', @where)."
378 warn $statement if $DEBUG;
379 dbh->selectall_arrayref($statement);
382 =item report_local_phone OPTIONS
384 Returns the Local Exchange Telephone Subscription report (section 5.6).
385 OPTIONS are as above. Each row is data for one state. Columns are:
387 - state FIPS code (key)
388 - wholesale switched voice lines
389 - wholesale unswitched local loops
390 - end-user total lines
391 - end-user lines sold in a package with broadband
392 - consumer-grade lines where you are not the long-distance carrier
393 - consumer-grade lines where the carrier IS the long-distance carrier
394 - business-grade lines where you are not the long-distance carrier
395 - business-grade lines where the carrier IS the long-distance carrier
396 - end-user lines where you own the local loop facility
397 - end-user lines where you lease an unswitched local loop from a LEC
398 - end-user lines resold from another carrier
399 - end-user lines provided over fiber to the premises
400 - end-user lines provided over coaxial
401 - end-user lines provided over fixed wireless
405 sub report_local_phone {
408 my $date = $opt{date} || time;
409 my $agentnum = $opt{agentnum};
414 "SUM(phone_circuits)",
416 "SUM(CASE WHEN is_broadband = 1 THEN phone_lines ELSE 0 END)",
417 "SUM(CASE WHEN is_consumer = 1 AND phone_longdistance IS NULL THEN phone_lines ELSE 0 END)",
418 "SUM(CASE WHEN is_consumer = 1 AND phone_longdistance = 1 THEN phone_lines ELSE 0 END)",
419 "SUM(CASE WHEN is_consumer IS NULL AND phone_longdistance IS NULL THEN phone_lines ELSE 0 END)",
420 "SUM(CASE WHEN is_consumer IS NULL AND phone_longdistance = 1 THEN phone_lines ELSE 0 END)",
421 "SUM(CASE WHEN phone_localloop = 'owned' THEN phone_lines ELSE 0 END)",
422 "SUM(CASE WHEN phone_localloop = 'leased' THEN phone_lines ELSE 0 END)",
423 "SUM(CASE WHEN phone_localloop = 'resale' THEN phone_lines ELSE 0 END)",
424 "SUM(CASE WHEN media = 'Fiber' THEN phone_lines ELSE 0 END)",
425 "SUM(CASE WHEN media = 'Cable Modem' THEN phone_lines ELSE 0 END)",
426 "SUM(CASE WHEN media = 'Fixed Wireless' THEN phone_lines ELSE 0 END)",
430 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
431 JOIN state USING (country, state)
432 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
433 JOIN part_pkg USING (pkgpart) '.
434 join_optionnames_int(qw(
435 is_phone is_broadband
436 phone_vges phone_circuits phone_lines
437 is_consumer phone_longdistance
439 join_optionnames('media', 'phone_localloop')
445 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
446 my $group_by = 'state.fips';
447 my $order_by = $group_by;
449 my $statement = "SELECT ".join(', ', @select) . "
451 WHERE ".join(' AND ', @where)."
456 warn $statement if $DEBUG;
457 dbh->selectall_arrayref($statement);
460 =item report_voip OPTIONS
462 Returns the Interconnected VoIP Subscription report (section 5.7).
463 OPTIONS are as above. Columns are:
465 - state FIPS code (key)
466 - OTT subscriptions (non-last-mile)
467 - OTT subscriptions sold to consumers
468 - last-mile subscriptions
469 - last-mile subscriptions sold to consumers
470 - last-mile subscriptions bundled with broadband Internet
471 - last-mile subscriptions over copper pairs
472 - last-mile subscriptions over coaxial
473 - last-mile subscriptions over fiber
474 - last-mile subscriptions over fixed wireless
475 - last-mile subscriptions over other media
482 my $date = $opt{date} || time;
483 my $agentnum = $opt{agentnum};
487 # OTT, OTT + consumer
488 "SUM(CASE WHEN (voip_lastmile IS NULL) THEN 1 ELSE 0 END)",
489 "SUM(CASE WHEN (voip_lastmile IS NULL AND is_consumer = 1) THEN 1 ELSE 0 END)",
490 # non-OTT: total, consumer, broadband bundle, media types
491 "SUM(CASE WHEN (voip_lastmile = 1) THEN 1 ELSE 0 END)",
492 "SUM(CASE WHEN (voip_lastmile = 1 AND is_consumer = 1) THEN 1 ELSE 0 END)",
493 "SUM(CASE WHEN (voip_lastmile = 1 AND is_broadband = 1) THEN 1 ELSE 0 END)",
494 "SUM(CASE WHEN (voip_lastmile = 1 AND media = 'Copper') THEN 1 ELSE 0 END)",
495 "SUM(CASE WHEN (voip_lastmile = 1 AND media = 'Cable Modem') THEN 1 ELSE 0 END)",
496 "SUM(CASE WHEN (voip_lastmile = 1 AND media = 'Fiber') THEN 1 ELSE 0 END)",
497 "SUM(CASE WHEN (voip_lastmile = 1 AND media = 'Fixed Wireless') THEN 1 ELSE 0 END)",
498 "SUM(CASE WHEN (voip_lastmile = 1 AND media NOT IN('Copper', 'Fiber', 'Cable Modem', 'Fixed Wireless') ) THEN 1 ELSE 0 END)",
503 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
504 JOIN state USING (country, state)
505 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
506 JOIN part_pkg USING (pkgpart) '.
507 join_optionnames_int(
508 qw( is_voip is_broadband is_consumer voip_lastmile)
510 join_optionnames('media')
516 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
517 my $group_by = 'state.fips';
518 my $order_by = $group_by;
520 my $statement = "SELECT ".join(', ', @select) . "
522 WHERE ".join(' AND ', @where)."
527 warn $statement if $DEBUG;
528 dbh->selectall_arrayref($statement);