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(".join(',',
263 10, 11, 12, 20, 30, 40, 41, 42, 50, 60, 70, 90, 0
267 =item report SECTION, OPTIONS
269 Returns the report section SECTION (see the C<parts> method for section
270 name strings) as an arrayref of arrayrefs. OPTIONS may contain "date"
271 (a timestamp value to run the report as of this date) and "agentnum"
272 (to limit to a single agent).
281 my $method = $section.'_sql';
282 die "Report section '$section' is not implemented\n"
283 unless $class->can($method);
284 my $statement = $class->$method(%opt);
286 my $sth = dbh->prepare($statement);
287 $sth->execute or die $sth->errstr;
288 $sth->fetchall_arrayref;
294 my $date = $opt{date} || time;
296 my $agentnum = $opt{agentnum};
300 'COALESCE(dbaname, agent.agent)',
302 'CASE WHEN is_consumer IS NOT NULL THEN 1 ELSE 0 END',
305 'CASE WHEN is_business IS NOT NULL THEN 1 ELSE 0 END',
311 JOIN deploy_zone USING (zonenum)
312 JOIN agent USING (agentnum)';
315 "active_date < $date",
316 "(expire_date > $date OR expire_date IS NULL)",
318 push @where, "agentnum = $agentnum" if $agentnum;
320 my $order_by = 'censusblock, dbaname, technology, is_consumer, is_business';
322 "SELECT ".join(', ', @select) . "
324 WHERE ".join(' AND ', @where)."
332 my $date = $opt{date} || time;
333 my $agentnum = $opt{agentnum};
336 'cust_location.censustract',
338 'broadband_downstream',
339 'broadband_upstream',
341 'COUNT(is_consumer)',
345 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
346 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
347 JOIN part_pkg USING (pkgpart) '.
348 join_optionnames_int(qw(
349 is_broadband technology
352 join_optionnames(qw(broadband_downstream broadband_upstream))
358 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
359 my $group_by = 'cust_location.censustract, technology, '.
360 'broadband_downstream, broadband_upstream ';
361 my $order_by = $group_by;
363 "SELECT ".join(', ', @select) . "
365 WHERE ".join(' AND ', @where)."
375 my $date = $opt{date} || time;
376 my $agentnum = $opt{agentnum};
379 'cust_location.censustract',
380 # VoIP indicator (0 for non-VoIP, 1 for VoIP)
381 'COALESCE(is_voip, 0)',
382 # number of lines/subscriptions
383 'SUM(CASE WHEN is_voip = 1 THEN 1 ELSE phone_lines END)',
384 # consumer grade lines/subscriptions
385 'SUM(CASE WHEN is_consumer = 1 THEN ( CASE WHEN is_voip = 1 THEN voip_sessions ELSE phone_lines END) ELSE 0 END)'
389 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
390 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
391 JOIN part_pkg USING (pkgpart) '.
392 join_optionnames_int(qw(
393 is_phone is_voip is_consumer phone_lines voip_sessions
399 "(is_voip = 1 OR is_phone = 1)",
401 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
402 my $group_by = 'cust_location.censustract, COALESCE(is_voip, 0)';
403 my $order_by = $group_by;
405 "SELECT ".join(', ', @select) . "
407 WHERE ".join(' AND ', @where)."
417 my $date = $opt{date} || time;
418 my $agentnum = $opt{agentnum};
423 "SUM(phone_circuits)",
425 "SUM(CASE WHEN is_broadband = 1 THEN phone_lines ELSE 0 END)",
426 "SUM(CASE WHEN is_consumer = 1 AND phone_longdistance IS NULL THEN phone_lines ELSE 0 END)",
427 "SUM(CASE WHEN is_consumer = 1 AND phone_longdistance = 1 THEN phone_lines ELSE 0 END)",
428 "SUM(CASE WHEN is_consumer IS NULL AND phone_longdistance IS NULL THEN phone_lines ELSE 0 END)",
429 "SUM(CASE WHEN is_consumer IS NULL AND phone_longdistance = 1 THEN phone_lines ELSE 0 END)",
430 "SUM(CASE WHEN phone_localloop = 'owned' THEN phone_lines ELSE 0 END)",
431 "SUM(CASE WHEN phone_localloop = 'leased' THEN phone_lines ELSE 0 END)",
432 "SUM(CASE WHEN phone_localloop = 'resale' THEN phone_lines ELSE 0 END)",
433 "SUM(CASE WHEN media = 'Fiber' THEN phone_lines ELSE 0 END)",
434 "SUM(CASE WHEN media = 'Cable Modem' THEN phone_lines ELSE 0 END)",
435 "SUM(CASE WHEN media = 'Fixed Wireless' THEN phone_lines ELSE 0 END)",
439 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
440 JOIN state USING (country, state)
441 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
442 JOIN part_pkg USING (pkgpart) '.
443 join_optionnames_int(qw(
444 is_phone is_broadband
445 phone_vges phone_circuits phone_lines
446 is_consumer phone_longdistance
448 join_optionnames('media', 'phone_localloop')
454 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
455 my $group_by = 'state.fips';
456 my $order_by = $group_by;
458 "SELECT ".join(', ', @select) . "
460 WHERE ".join(' AND ', @where)."
469 my $date = $opt{date} || time;
470 my $agentnum = $opt{agentnum};
474 # OTT, OTT + consumer
475 "SUM(CASE WHEN (voip_lastmile IS NULL) THEN 1 ELSE 0 END)",
476 "SUM(CASE WHEN (voip_lastmile IS NULL AND is_consumer = 1) THEN 1 ELSE 0 END)",
477 # non-OTT: total, consumer, broadband bundle, media types
478 "SUM(CASE WHEN (voip_lastmile = 1) THEN 1 ELSE 0 END)",
479 "SUM(CASE WHEN (voip_lastmile = 1 AND is_consumer = 1) THEN 1 ELSE 0 END)",
480 "SUM(CASE WHEN (voip_lastmile = 1 AND is_broadband = 1) THEN 1 ELSE 0 END)",
481 "SUM(CASE WHEN (voip_lastmile = 1 AND media = 'Copper') THEN 1 ELSE 0 END)",
482 "SUM(CASE WHEN (voip_lastmile = 1 AND media = 'Cable Modem') THEN 1 ELSE 0 END)",
483 "SUM(CASE WHEN (voip_lastmile = 1 AND media = 'Fiber') THEN 1 ELSE 0 END)",
484 "SUM(CASE WHEN (voip_lastmile = 1 AND media = 'Fixed Wireless') THEN 1 ELSE 0 END)",
485 "SUM(CASE WHEN (voip_lastmile = 1 AND media NOT IN('Copper', 'Fiber', 'Cable Modem', 'Fixed Wireless') ) THEN 1 ELSE 0 END)",
490 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
491 JOIN state USING (country, state)
492 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
493 JOIN part_pkg USING (pkgpart) '.
494 join_optionnames_int(
495 qw( is_voip is_broadband is_consumer voip_lastmile)
497 join_optionnames('media')
503 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
504 my $group_by = 'state.fips';
505 my $order_by = $group_by;
507 "SELECT ".join(', ', @select) . "
509 WHERE ".join(' AND ', @where)."
518 Returns a Tie::IxHash reference of the internal short names used for the
519 report sections ('fbd', 'mbs', etc.) to the full names.
523 tie our %parts, 'Tie::IxHash', (
524 fbd => 'Fixed Broadband Deployment',
525 fbs => 'Fixed Broadband Subscription',
526 fvs => 'Fixed Voice Subscription',
527 lts => 'Local Exchange Telephone Subscription',
528 voip => 'Interconnected VoIP Subscription',
529 mbd => 'Mobile Broadband Deployment',
530 mbsa => 'Mobile Broadband Service Availability',
531 mbs => 'Mobile Broadband Subscription',
532 mvd => 'Mobile Voice Deployment',
533 mvs => 'Mobile Voice Subscription',
537 Storable::dclone(\%parts);