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).
294 my $method = $section.'_sql';
295 die "Report section '$section' is not implemented\n"
296 unless $class->can($method);
297 my $statement = $class->$method(%opt);
299 my $sth = dbh->prepare($statement);
300 $sth->execute or die $sth->errstr;
301 $sth->fetchall_arrayref;
307 my $date = $opt{date} || time;
309 my $agentnum = $opt{agentnum};
315 'CASE WHEN is_consumer IS NOT NULL THEN 1 ELSE 0 END',
318 'CASE WHEN is_business IS NOT NULL THEN 1 ELSE 0 END',
324 JOIN deploy_zone USING (zonenum)
325 JOIN agent USING (agentnum)';
328 "active_date < $date",
329 "(expire_date > $date OR expire_date IS NULL)",
331 push @where, "agentnum = $agentnum" if $agentnum;
333 my $order_by = 'censusblock, agentnum, technology, is_consumer, is_business';
335 "SELECT ".join(', ', @select) . "
337 WHERE ".join(' AND ', @where)."
345 my $date = $opt{date} || time;
346 my $agentnum = $opt{agentnum};
349 'cust_location.censustract',
351 'broadband_downstream',
352 'broadband_upstream',
354 'COUNT(is_consumer)',
358 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
359 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
360 JOIN part_pkg USING (pkgpart) '.
361 join_optionnames_int(qw(
362 is_broadband technology
365 join_optionnames(qw(broadband_downstream broadband_upstream))
371 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
372 my $group_by = 'cust_location.censustract, technology, '.
373 'broadband_downstream, broadband_upstream ';
374 my $order_by = $group_by;
376 "SELECT ".join(', ', @select) . "
378 WHERE ".join(' AND ', @where)."
388 my $date = $opt{date} || time;
389 my $agentnum = $opt{agentnum};
392 'cust_location.censustract',
393 # VoIP indicator (0 for non-VoIP, 1 for VoIP)
394 'COALESCE(is_voip, 0)',
395 # number of lines/subscriptions
396 'SUM(CASE WHEN is_voip = 1 THEN 1 ELSE phone_lines END)',
397 # consumer grade lines/subscriptions
398 'SUM(CASE WHEN is_consumer = 1 THEN ( CASE WHEN is_voip = 1 THEN voip_sessions ELSE phone_lines END) ELSE 0 END)'
402 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
403 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
404 JOIN part_pkg USING (pkgpart) '.
405 join_optionnames_int(qw(
406 is_phone is_voip is_consumer phone_lines voip_sessions
412 "(is_voip = 1 OR is_phone = 1)",
414 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
415 my $group_by = 'cust_location.censustract, COALESCE(is_voip, 0)';
416 my $order_by = $group_by;
418 "SELECT ".join(', ', @select) . "
420 WHERE ".join(' AND ', @where)."
430 my $date = $opt{date} || time;
431 my $agentnum = $opt{agentnum};
436 "SUM(phone_circuits)",
438 "SUM(CASE WHEN is_broadband = 1 THEN phone_lines ELSE 0 END)",
439 "SUM(CASE WHEN is_consumer = 1 AND phone_longdistance IS NULL THEN phone_lines ELSE 0 END)",
440 "SUM(CASE WHEN is_consumer = 1 AND phone_longdistance = 1 THEN phone_lines ELSE 0 END)",
441 "SUM(CASE WHEN is_consumer IS NULL AND phone_longdistance IS NULL THEN phone_lines ELSE 0 END)",
442 "SUM(CASE WHEN is_consumer IS NULL AND phone_longdistance = 1 THEN phone_lines ELSE 0 END)",
443 "SUM(CASE WHEN phone_localloop = 'owned' THEN phone_lines ELSE 0 END)",
444 "SUM(CASE WHEN phone_localloop = 'leased' THEN phone_lines ELSE 0 END)",
445 "SUM(CASE WHEN phone_localloop = 'resale' THEN phone_lines ELSE 0 END)",
446 "SUM(CASE WHEN media = 'Fiber' THEN phone_lines ELSE 0 END)",
447 "SUM(CASE WHEN media = 'Cable Modem' THEN phone_lines ELSE 0 END)",
448 "SUM(CASE WHEN media = 'Fixed Wireless' THEN phone_lines ELSE 0 END)",
452 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
453 JOIN state USING (country, state)
454 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
455 JOIN part_pkg USING (pkgpart) '.
456 join_optionnames_int(qw(
457 is_phone is_broadband
458 phone_vges phone_circuits phone_lines
459 is_consumer phone_longdistance
461 join_optionnames('media', 'phone_localloop')
467 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
468 my $group_by = 'state.fips';
469 my $order_by = $group_by;
471 "SELECT ".join(', ', @select) . "
473 WHERE ".join(' AND ', @where)."
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 "SELECT ".join(', ', @select) . "
522 WHERE ".join(' AND ', @where)."
531 my $date = $opt{date} || time;
532 my $agentnum = $opt{agentnum};
536 'broadband_downstream',
537 'broadband_upstream',
539 'COUNT(is_consumer)',
543 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
544 JOIN state USING (country, state)
545 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
546 JOIN part_pkg USING (pkgpart) '.
547 join_optionnames_int(qw(
548 is_broadband technology
551 join_optionnames(qw(broadband_downstream broadband_upstream))
555 is_mobile_broadband()
557 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
558 my $group_by = 'state.fips, broadband_downstream, broadband_upstream ';
559 my $order_by = $group_by;
561 "SELECT ".join(', ', @select) . "
563 WHERE ".join(' AND ', @where)."
572 my $date = $opt{date} || time;
573 my $agentnum = $opt{agentnum};
578 'COUNT(mobile_direct)',
582 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
583 JOIN state USING (country, state)
584 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
585 JOIN part_pkg USING (pkgpart) '.
586 join_optionnames_int(qw( is_mobile mobile_direct) )
592 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
593 my $group_by = 'state.fips';
594 my $order_by = $group_by;
596 "SELECT ".join(', ', @select) . "
598 WHERE ".join(' AND ', @where)."
606 Returns a Tie::IxHash reference of the internal short names used for the
607 report sections ('fbd', 'mbs', etc.) to the full names.
611 tie our %parts, 'Tie::IxHash', (
612 fbd => 'Fixed Broadband Deployment',
613 fbs => 'Fixed Broadband Subscription',
614 fvs => 'Fixed Voice Subscription',
615 lts => 'Local Exchange Telephone Subscription',
616 voip => 'Interconnected VoIP Subscription',
617 mbd => 'Mobile Broadband Deployment',
618 mbsa => 'Mobile Broadband Service Availability',
619 mbs => 'Mobile Broadband Subscription',
620 mvd => 'Mobile Voice Deployment',
621 mvs => 'Mobile Voice Subscription',
625 Storable::dclone(\%parts);