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 the following:
285 - date: a timestamp value. Packages that were active on that date will be
288 - agentnum: limit to packages with this agent.
290 - detail: if true, the report will contain an additional column which contains
291 the keys of all objects aggregated in the row.
293 - ignore_quantity: if true, package quantities will be ignored (only distinct
294 packages will be counted).
303 my $method = $section.'_sql';
304 die "Report section '$section' is not implemented\n"
305 unless $class->can($method);
306 my $statement = $class->$method(%opt);
308 my $sth = dbh->prepare($statement);
309 $sth->execute or die $sth->errstr;
310 $sth->fetchall_arrayref;
316 my $date = $opt{date} || time;
317 my $agentnum = $opt{agentnum};
323 'CASE WHEN is_consumer IS NOT NULL THEN 1 ELSE 0 END',
326 'CASE WHEN is_business IS NOT NULL THEN 1 ELSE 0 END',
330 push @select, 'blocknum' if $opt{detail};
332 my $from = 'deploy_zone_block
333 JOIN deploy_zone USING (zonenum)
334 JOIN agent USING (agentnum)';
337 "active_date < $date",
338 "(expire_date > $date OR expire_date IS NULL)",
340 push @where, "agentnum = $agentnum" if $agentnum;
342 my $order_by = 'censusblock, agentnum, technology, is_consumer, is_business';
344 "SELECT ".join(', ', @select) . "
346 WHERE ".join(' AND ', @where)."
354 my $date = $opt{date} || time;
355 my $agentnum = $opt{agentnum};
356 my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
359 'cust_location.censustract',
361 'broadband_downstream',
362 'broadband_upstream',
364 "SUM(COALESCE(is_consumer,0) * $q)",
366 push @select, "array_to_string(array_agg(pkgnum), ',')" if $opt{detail};
370 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
371 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
372 JOIN part_pkg USING (pkgpart) '.
373 join_optionnames_int(qw(
374 is_broadband technology
377 join_optionnames(qw(broadband_downstream broadband_upstream))
383 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
384 my $group_by = 'cust_location.censustract, technology, '.
385 'broadband_downstream, broadband_upstream ';
386 my $order_by = $group_by;
388 "SELECT ".join(', ', @select) . "
390 WHERE ".join(' AND ', @where)."
400 my $date = $opt{date} || time;
401 my $agentnum = $opt{agentnum};
402 my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
405 'cust_location.censustract',
406 # VoIP indicator (0 for non-VoIP, 1 for VoIP)
407 'COALESCE(is_voip, 0)',
408 # number of lines/subscriptions
409 "SUM($q * (CASE WHEN is_voip = 1 THEN 1 ELSE phone_lines END))",
410 # consumer grade lines/subscriptions
411 "SUM($q * COALESCE(is_consumer,0) * (CASE WHEN is_voip = 1 THEN voip_sessions ELSE phone_lines END))",
413 push @select, "array_to_string(array_agg(pkgnum), ',')" if $opt{detail};
416 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
417 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
418 JOIN part_pkg USING (pkgpart) '.
419 join_optionnames_int(qw(
420 is_phone is_voip is_consumer phone_lines voip_sessions
426 "(is_voip = 1 OR is_phone = 1)",
428 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
429 my $group_by = 'cust_location.censustract, COALESCE(is_voip, 0)';
430 my $order_by = $group_by;
432 "SELECT ".join(', ', @select) . "
434 WHERE ".join(' AND ', @where)."
444 my $date = $opt{date} || time;
445 my $agentnum = $opt{agentnum};
446 my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
450 "SUM($q * phone_vges)",
451 "SUM($q * phone_circuits)",
452 "SUM($q * phone_lines)",
453 "SUM($q * (CASE WHEN is_broadband = 1 THEN phone_lines ELSE 0 END))",
454 "SUM($q * (CASE WHEN is_consumer = 1 AND phone_longdistance IS NULL THEN phone_lines ELSE 0 END))",
455 "SUM($q * (CASE WHEN is_consumer = 1 AND phone_longdistance = 1 THEN phone_lines ELSE 0 END))",
456 "SUM($q * (CASE WHEN is_consumer IS NULL AND phone_longdistance IS NULL THEN phone_lines ELSE 0 END))",
457 "SUM($q * (CASE WHEN is_consumer IS NULL AND phone_longdistance = 1 THEN phone_lines ELSE 0 END))",
458 "SUM($q * (CASE WHEN phone_localloop = 'owned' THEN phone_lines ELSE 0 END))",
459 "SUM($q * (CASE WHEN phone_localloop = 'leased' THEN phone_lines ELSE 0 END))",
460 "SUM($q * (CASE WHEN phone_localloop = 'resale' THEN phone_lines ELSE 0 END))",
461 "SUM($q * (CASE WHEN media = 'Fiber' THEN phone_lines ELSE 0 END))",
462 "SUM($q * (CASE WHEN media = 'Cable Modem' THEN phone_lines ELSE 0 END))",
463 "SUM($q * (CASE WHEN media = 'Fixed Wireless' THEN phone_lines ELSE 0 END))",
465 push @select, "array_to_string(array_agg(pkgnum),',')" if $opt{detail};
469 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
470 JOIN state USING (country, state)
471 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
472 JOIN part_pkg USING (pkgpart) '.
473 join_optionnames_int(qw(
474 is_phone is_broadband
475 phone_vges phone_circuits phone_lines
476 is_consumer phone_longdistance
478 join_optionnames('media', 'phone_localloop')
484 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
485 my $group_by = 'state.fips';
486 my $order_by = $group_by;
488 "SELECT ".join(', ', @select) . "
490 WHERE ".join(' AND ', @where)."
499 my $date = $opt{date} || time;
500 my $agentnum = $opt{agentnum};
501 my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
505 # OTT, OTT + consumer
506 "SUM($q * (CASE WHEN (voip_lastmile IS NULL) THEN 1 ELSE 0 END))",
507 "SUM($q * (CASE WHEN (voip_lastmile IS NULL AND is_consumer = 1) THEN 1 ELSE 0 END))",
508 # non-OTT: total, consumer, broadband bundle, media types
509 "SUM($q * (CASE WHEN (voip_lastmile = 1) THEN 1 ELSE 0 END))",
510 "SUM($q * (CASE WHEN (voip_lastmile = 1 AND is_consumer = 1) THEN 1 ELSE 0 END))",
511 "SUM($q * (CASE WHEN (voip_lastmile = 1 AND is_broadband = 1) THEN 1 ELSE 0 END))",
512 "SUM($q * (CASE WHEN (voip_lastmile = 1 AND media = 'Copper') THEN 1 ELSE 0 END))",
513 "SUM($q * (CASE WHEN (voip_lastmile = 1 AND media = 'Cable Modem') THEN 1 ELSE 0 END))",
514 "SUM($q * (CASE WHEN (voip_lastmile = 1 AND media = 'Fiber') THEN 1 ELSE 0 END))",
515 "SUM($q * (CASE WHEN (voip_lastmile = 1 AND media = 'Fixed Wireless') THEN 1 ELSE 0 END))",
516 "SUM($q * (CASE WHEN (voip_lastmile = 1 AND media NOT IN('Copper', 'Fiber', 'Cable Modem', 'Fixed Wireless') ) THEN 1 ELSE 0 END))",
518 push @select, "array_to_string(array_agg(pkgnum),',')" if $opt{detail};
522 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
523 JOIN state USING (country, state)
524 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
525 JOIN part_pkg USING (pkgpart) '.
526 join_optionnames_int(
527 qw( is_voip is_broadband is_consumer voip_lastmile)
529 join_optionnames('media')
535 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
536 my $group_by = 'state.fips';
537 my $order_by = $group_by;
539 "SELECT ".join(', ', @select) . "
541 WHERE ".join(' AND ', @where)."
550 my $date = $opt{date} || time;
551 my $agentnum = $opt{agentnum};
552 my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
556 'broadband_downstream',
557 'broadband_upstream',
559 "SUM(COALESCE(is_consumer, 0) * $q)",
561 push @select, "array_to_string(array_agg(pkgnum),',')" if $opt{detail};
565 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
566 JOIN state USING (country, state)
567 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
568 JOIN part_pkg USING (pkgpart) '.
569 join_optionnames_int(qw(
570 is_broadband technology
573 join_optionnames(qw(broadband_downstream broadband_upstream))
577 is_mobile_broadband()
579 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
580 my $group_by = 'state.fips, broadband_downstream, broadband_upstream ';
581 my $order_by = $group_by;
583 "SELECT ".join(', ', @select) . "
585 WHERE ".join(' AND ', @where)."
594 my $date = $opt{date} || time;
595 my $agentnum = $opt{agentnum};
596 my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
601 "SUM($q * COALESCE(mobile_direct,0))",
603 push @select, "array_to_string(array_agg(pkgnum),',')" if $opt{detail};
607 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
608 JOIN state USING (country, state)
609 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
610 JOIN part_pkg USING (pkgpart) '.
611 join_optionnames_int(qw( is_mobile mobile_direct) )
617 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
618 my $group_by = 'state.fips';
619 my $order_by = $group_by;
621 "SELECT ".join(', ', @select) . "
623 WHERE ".join(' AND ', @where)."
631 Returns a Tie::IxHash reference of the internal short names used for the
632 report sections ('fbd', 'mbs', etc.) to the full names.
636 tie our %parts, 'Tie::IxHash', (
637 fbd => 'Fixed Broadband Deployment',
638 fbs => 'Fixed Broadband Subscription',
639 fvs => 'Fixed Voice Subscription',
640 lts => 'Local Exchange Telephone Subscription',
641 voip => 'Interconnected VoIP Subscription',
642 mbd => 'Mobile Broadband Deployment',
643 mbsa => 'Mobile Broadband Service Availability',
644 mbs => 'Mobile Broadband Subscription',
645 mvd => 'Mobile Voice Deployment',
646 mvs => 'Mobile Voice Subscription',
650 Storable::dclone(\%parts);
653 =item part_table SECTION
655 Returns the name of the primary table that's aggregated in the report section
656 SECTION. The last column of the report returned by the L</report> method is
657 a comma-separated list of record numbers, in this table, that are included in
658 the report line item.
663 my ($class, $part) = @_;
664 if ($part eq 'fbd') {
665 return 'deploy_zone_block';
668 } # add other cases as we add more of the deployment/availability reports