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 a condition to limit packages to those that were setup before a
252 # certain date, and not canceled before that date.
254 # (Strictly speaking this should also exclude suspended packages but
255 # "suspended as of some past date" is a complicated query.)
257 "cust_pkg.setup <= $date AND ".
258 "(cust_pkg.cancel IS NULL OR cust_pkg.cancel > $date)";
261 sub is_fixed_broadband {
262 "is_broadband::int = 1 AND technology::int IN( 10, 11, 12, 20, 30, 40, 41, 42, 50, 60, 70, 90, 0 )"
265 sub is_mobile_broadband {
266 "is_broadband::int = 1 AND technology::int IN( 80, 81, 82, 83, 84, 85, 86, 87, 88)"
269 =item report SECTION, OPTIONS
271 Returns the report section SECTION (see the C<parts> method for section
272 name strings) as an arrayref of arrayrefs. OPTIONS may contain "date"
273 (a timestamp value to run the report as of this date) and "agentnum"
274 (to limit to a single agent).
283 my $method = $section.'_sql';
284 die "Report section '$section' is not implemented\n"
285 unless $class->can($method);
286 my $statement = $class->$method(%opt);
288 my $sth = dbh->prepare($statement);
289 $sth->execute or die $sth->errstr;
290 $sth->fetchall_arrayref;
296 my $date = $opt{date} || time;
298 my $agentnum = $opt{agentnum};
302 'COALESCE(dbaname, agent.agent)',
304 'CASE WHEN is_consumer IS NOT NULL THEN 1 ELSE 0 END',
307 'CASE WHEN is_business IS NOT NULL THEN 1 ELSE 0 END',
313 JOIN deploy_zone USING (zonenum)
314 JOIN agent USING (agentnum)';
317 "active_date < $date",
318 "(expire_date > $date OR expire_date IS NULL)",
320 push @where, "agentnum = $agentnum" if $agentnum;
322 my $order_by = 'censusblock, dbaname, technology, is_consumer, is_business';
324 "SELECT ".join(', ', @select) . "
326 WHERE ".join(' AND ', @where)."
334 my $date = $opt{date} || time;
335 my $agentnum = $opt{agentnum};
338 'cust_location.censustract',
340 'broadband_downstream',
341 'broadband_upstream',
343 'COUNT(is_consumer)',
347 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
348 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
349 JOIN part_pkg USING (pkgpart) '.
350 join_optionnames_int(qw(
351 is_broadband technology
354 join_optionnames(qw(broadband_downstream broadband_upstream))
360 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
361 my $group_by = 'cust_location.censustract, technology, '.
362 'broadband_downstream, broadband_upstream ';
363 my $order_by = $group_by;
365 "SELECT ".join(', ', @select) . "
367 WHERE ".join(' AND ', @where)."
377 my $date = $opt{date} || time;
378 my $agentnum = $opt{agentnum};
381 'cust_location.censustract',
382 # VoIP indicator (0 for non-VoIP, 1 for VoIP)
383 'COALESCE(is_voip, 0)',
384 # number of lines/subscriptions
385 'SUM(CASE WHEN is_voip = 1 THEN 1 ELSE phone_lines END)',
386 # consumer grade lines/subscriptions
387 'SUM(CASE WHEN is_consumer = 1 THEN ( CASE WHEN is_voip = 1 THEN voip_sessions ELSE phone_lines END) ELSE 0 END)'
391 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
392 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
393 JOIN part_pkg USING (pkgpart) '.
394 join_optionnames_int(qw(
395 is_phone is_voip is_consumer phone_lines voip_sessions
401 "(is_voip = 1 OR is_phone = 1)",
403 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
404 my $group_by = 'cust_location.censustract, COALESCE(is_voip, 0)';
405 my $order_by = $group_by;
407 "SELECT ".join(', ', @select) . "
409 WHERE ".join(' AND ', @where)."
419 my $date = $opt{date} || time;
420 my $agentnum = $opt{agentnum};
425 "SUM(phone_circuits)",
427 "SUM(CASE WHEN is_broadband = 1 THEN phone_lines ELSE 0 END)",
428 "SUM(CASE WHEN is_consumer = 1 AND phone_longdistance IS NULL THEN phone_lines ELSE 0 END)",
429 "SUM(CASE WHEN is_consumer = 1 AND phone_longdistance = 1 THEN phone_lines ELSE 0 END)",
430 "SUM(CASE WHEN is_consumer IS NULL AND phone_longdistance IS NULL THEN phone_lines ELSE 0 END)",
431 "SUM(CASE WHEN is_consumer IS NULL AND phone_longdistance = 1 THEN phone_lines ELSE 0 END)",
432 "SUM(CASE WHEN phone_localloop = 'owned' THEN phone_lines ELSE 0 END)",
433 "SUM(CASE WHEN phone_localloop = 'leased' THEN phone_lines ELSE 0 END)",
434 "SUM(CASE WHEN phone_localloop = 'resale' THEN phone_lines ELSE 0 END)",
435 "SUM(CASE WHEN media = 'Fiber' THEN phone_lines ELSE 0 END)",
436 "SUM(CASE WHEN media = 'Cable Modem' THEN phone_lines ELSE 0 END)",
437 "SUM(CASE WHEN media = 'Fixed Wireless' THEN phone_lines ELSE 0 END)",
441 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
442 JOIN state USING (country, state)
443 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
444 JOIN part_pkg USING (pkgpart) '.
445 join_optionnames_int(qw(
446 is_phone is_broadband
447 phone_vges phone_circuits phone_lines
448 is_consumer phone_longdistance
450 join_optionnames('media', 'phone_localloop')
456 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
457 my $group_by = 'state.fips';
458 my $order_by = $group_by;
460 "SELECT ".join(', ', @select) . "
462 WHERE ".join(' AND ', @where)."
471 my $date = $opt{date} || time;
472 my $agentnum = $opt{agentnum};
476 # OTT, OTT + consumer
477 "SUM(CASE WHEN (voip_lastmile IS NULL) THEN 1 ELSE 0 END)",
478 "SUM(CASE WHEN (voip_lastmile IS NULL AND is_consumer = 1) THEN 1 ELSE 0 END)",
479 # non-OTT: total, consumer, broadband bundle, media types
480 "SUM(CASE WHEN (voip_lastmile = 1) THEN 1 ELSE 0 END)",
481 "SUM(CASE WHEN (voip_lastmile = 1 AND is_consumer = 1) THEN 1 ELSE 0 END)",
482 "SUM(CASE WHEN (voip_lastmile = 1 AND is_broadband = 1) THEN 1 ELSE 0 END)",
483 "SUM(CASE WHEN (voip_lastmile = 1 AND media = 'Copper') THEN 1 ELSE 0 END)",
484 "SUM(CASE WHEN (voip_lastmile = 1 AND media = 'Cable Modem') THEN 1 ELSE 0 END)",
485 "SUM(CASE WHEN (voip_lastmile = 1 AND media = 'Fiber') THEN 1 ELSE 0 END)",
486 "SUM(CASE WHEN (voip_lastmile = 1 AND media = 'Fixed Wireless') THEN 1 ELSE 0 END)",
487 "SUM(CASE WHEN (voip_lastmile = 1 AND media NOT IN('Copper', 'Fiber', 'Cable Modem', 'Fixed Wireless') ) THEN 1 ELSE 0 END)",
492 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
493 JOIN state USING (country, state)
494 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
495 JOIN part_pkg USING (pkgpart) '.
496 join_optionnames_int(
497 qw( is_voip is_broadband is_consumer voip_lastmile)
499 join_optionnames('media')
505 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
506 my $group_by = 'state.fips';
507 my $order_by = $group_by;
509 "SELECT ".join(', ', @select) . "
511 WHERE ".join(' AND ', @where)."
520 my $date = $opt{date} || time;
521 my $agentnum = $opt{agentnum};
525 'broadband_downstream',
526 'broadband_upstream',
528 'COUNT(is_consumer)',
532 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
533 JOIN state USING (country, state)
534 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
535 JOIN part_pkg USING (pkgpart) '.
536 join_optionnames_int(qw(
537 is_broadband technology
540 join_optionnames(qw(broadband_downstream broadband_upstream))
544 is_mobile_broadband()
546 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
547 my $group_by = 'state.fips, broadband_downstream, broadband_upstream ';
548 my $order_by = $group_by;
550 "SELECT ".join(', ', @select) . "
552 WHERE ".join(' AND ', @where)."
561 my $date = $opt{date} || time;
562 my $agentnum = $opt{agentnum};
567 'COUNT(mobile_direct)',
571 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
572 JOIN state USING (country, state)
573 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
574 JOIN part_pkg USING (pkgpart) '.
575 join_optionnames_int(qw( is_mobile mobile_direct) )
581 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
582 my $group_by = 'state.fips';
583 my $order_by = $group_by;
585 "SELECT ".join(', ', @select) . "
587 WHERE ".join(' AND ', @where)."
595 Returns a Tie::IxHash reference of the internal short names used for the
596 report sections ('fbd', 'mbs', etc.) to the full names.
600 tie our %parts, 'Tie::IxHash', (
601 fbd => 'Fixed Broadband Deployment',
602 fbs => 'Fixed Broadband Subscription',
603 fvs => 'Fixed Voice Subscription',
604 lts => 'Local Exchange Telephone Subscription',
605 voip => 'Interconnected VoIP Subscription',
606 mbd => 'Mobile Broadband Deployment',
607 mbsa => 'Mobile Broadband Service Availability',
608 mbs => 'Mobile Broadband Subscription',
609 mvd => 'Mobile Voice Deployment',
610 mvs => 'Mobile Voice Subscription',
614 Storable::dclone(\%parts);