1 package FS::Report::FCC_477;
2 use base qw( FS::Report );
5 use vars qw( @upload @download @technology @part2aoption @part2boption
8 use FS::Record qw( dbh );
17 FS::Report::FCC_477 - Routines for FCC Form 477 reports
59 'Terrestrial Fixed Wireless',
60 'Terrestrial Mobile Wireless',
61 'Electric Power Line',
68 'unswitched UNE loops',
85 #from the select at http://www.ffiec.gov/census/default.aspx
86 #though this is now in the database, also
88 '01' => 'ALABAMA (AL)',
89 '02' => 'ALASKA (AK)',
90 '04' => 'ARIZONA (AZ)',
91 '05' => 'ARKANSAS (AR)',
92 '06' => 'CALIFORNIA (CA)',
93 '08' => 'COLORADO (CO)',
95 '09' => 'CONNECTICUT (CT)',
96 '10' => 'DELAWARE (DE)',
97 '11' => 'DISTRICT OF COLUMBIA (DC)',
98 '12' => 'FLORIDA (FL)',
99 '13' => 'GEORGIA (GA)',
100 '15' => 'HAWAII (HI)',
102 '16' => 'IDAHO (ID)',
103 '17' => 'ILLINOIS (IL)',
104 '18' => 'INDIANA (IN)',
106 '20' => 'KANSAS (KS)',
107 '21' => 'KENTUCKY (KY)',
109 '22' => 'LOUISIANA (LA)',
110 '23' => 'MAINE (ME)',
111 '24' => 'MARYLAND (MD)',
112 '25' => 'MASSACHUSETTS (MA)',
113 '26' => 'MICHIGAN (MI)',
114 '27' => 'MINNESOTA (MN)',
116 '28' => 'MISSISSIPPI (MS)',
117 '29' => 'MISSOURI (MO)',
118 '30' => 'MONTANA (MT)',
119 '31' => 'NEBRASKA (NE)',
120 '32' => 'NEVADA (NV)',
121 '33' => 'NEW HAMPSHIRE (NH)',
123 '34' => 'NEW JERSEY (NJ)',
124 '35' => 'NEW MEXICO (NM)',
125 '36' => 'NEW YORK (NY)',
126 '37' => 'NORTH CAROLINA (NC)',
127 '38' => 'NORTH DAKOTA (ND)',
130 '40' => 'OKLAHOMA (OK)',
131 '41' => 'OREGON (OR)',
132 '42' => 'PENNSYLVANIA (PA)',
133 '44' => 'RHODE ISLAND (RI)',
134 '45' => 'SOUTH CAROLINA (SC)',
135 '46' => 'SOUTH DAKOTA (SD)',
137 '47' => 'TENNESSEE (TN)',
138 '48' => 'TEXAS (TX)',
140 '50' => 'VERMONT (VT)',
141 '51' => 'VIRGINIA (VA)',
142 '53' => 'WASHINGTON (WA)',
144 '54' => 'WEST VIRGINIA (WV)',
145 '55' => 'WISCONSIN (WI)',
146 '56' => 'WYOMING (WY)',
147 '72' => 'PUERTO RICO (PR)',
150 sub restore_fcc477map {
152 FS::Record::scalar_sql('',"select formvalue from fcc477map where formkey = ?",$key);
159 local $SIG{HUP} = 'IGNORE';
160 local $SIG{INT} = 'IGNORE';
161 local $SIG{QUIT} = 'IGNORE';
162 local $SIG{TERM} = 'IGNORE';
163 local $SIG{TSTP} = 'IGNORE';
164 local $SIG{PIPE} = 'IGNORE';
166 my $oldAutoCommit = $FS::UID::AutoCommit;
167 local $FS::UID::AutoCommit = 0;
170 my $sql = "delete from fcc477map where formkey = ?";
171 my $sth = dbh->prepare($sql) or die dbh->errstr;
172 $sth->execute($key) or do {
173 warn "WARNING: Error removing FCC 477 form defaults: " . $sth->errstr;
174 $dbh->rollback if $oldAutoCommit;
177 $sql = "insert into fcc477map (formkey,formvalue) values (?,?)";
178 $sth = dbh->prepare($sql) or die dbh->errstr;
179 $sth->execute($key,$value) or do {
180 warn "WARNING: Error setting FCC 477 form defaults: " . $sth->errstr;
181 $dbh->rollback if $oldAutoCommit;
184 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
189 sub parse_technology_option {
194 for (my $i = 0; $i < scalar(@technology); $i++) {
195 my $value = $cgi->param("part1_technology_option_$i"); #lame
196 save_fcc477map("part1_technology_option_$i",$value)
197 if $save && $value =~ /^\d+$/;
198 push @result, $value =~ /^\d+$/ ? $value : 0;
207 ### everything above this point is unmaintained ###
210 =head1 THE "NEW" REPORT (October 2014 and later)
218 # functions for internal use
220 sub join_optionnames {
221 join(' ', map { join_optionname($_) } @_);
224 sub join_optionnames_int {
225 join(' ', map { join_optionname_int($_) } @_);
228 sub join_optionname {
229 # Returns a FROM phrase to join a specific option into the query (via
230 # part_pkg). The option value will appear as a field with the same name
233 "LEFT JOIN (SELECT pkgpart, optionvalue AS $name FROM part_pkg_fcc_option".
234 " WHERE fccoptionname = '$name') AS t_$name".
235 " ON (part_pkg.pkgpart = t_$name.pkgpart)";
238 sub join_optionname_int {
239 # Returns a FROM phrase to join a specific option into the query (via
240 # part_pkg) and cast it to integer.. Note this does not convert nulls
243 "LEFT JOIN (SELECT pkgpart, CAST(optionvalue AS int) AS $name
244 FROM part_pkg_fcc_option".
245 " WHERE fccoptionname = '$name') AS t_$name".
246 " ON (part_pkg.pkgpart = t_$name.pkgpart)";
250 # Returns an sql expression for the DBA name
251 "COALESCE( deploy_zone.dbaname,
252 (SELECT value FROM conf WHERE conf.name = 'company_name'
253 AND (conf.agentnum = deploy_zone.agentnum
254 OR conf.agentnum IS NULL)
255 ORDER BY conf.agentnum IS NOT NULL DESC
261 # Returns a condition to limit packages to those that were setup before a
262 # certain date, and not canceled before that date.
264 # (Strictly speaking this should also exclude suspended packages but
265 # "suspended as of some past date" is a complicated query.)
267 "cust_pkg.setup <= $date AND ".
268 "(cust_pkg.cancel IS NULL OR cust_pkg.cancel > $date)";
271 sub is_fixed_broadband {
272 "is_broadband::int = 1 AND technology::int IN( 10, 11, 12, 20, 30, 40, 41, 42, 50, 60, 70, 90, 0 )"
275 sub is_mobile_broadband {
276 "is_broadband::int = 1 AND technology::int IN( 80, 81, 82, 83, 84, 85, 86, 87, 88)"
279 =item report SECTION, OPTIONS
281 Returns the report section SECTION (see the C<parts> method for section
282 name strings) as an arrayref of arrayrefs. OPTIONS may contain the following:
284 - date: a timestamp value. Packages that were active on that date will be
287 - agentnum: limit to packages with this agent.
289 - detail: if true, the report will contain an additional column which contains
290 the keys of all objects aggregated in the row.
292 - ignore_quantity: if true, package quantities will be ignored (only distinct
293 packages will be counted).
302 my $method = $section.'_sql';
303 die "Report section '$section' is not implemented\n"
304 unless $class->can($method);
305 my $statement = $class->$method(%opt);
307 warn $statement if $DEBUG;
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)';
358 my $censustract = "replace(cust_location.censustract, '.', '')";
361 "$censustract AS censustract",
363 'broadband_downstream',
364 'broadband_upstream',
366 "SUM(COALESCE(is_consumer,0) * $q)",
368 push @select, "array_to_string(array_agg(pkgnum), ',')" if $opt{detail};
372 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
373 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
374 JOIN part_pkg USING (pkgpart) '.
375 join_optionnames_int(qw(
376 is_broadband technology
379 join_optionnames(qw(broadband_downstream broadband_upstream))
385 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
386 my $group_by = "$censustract, technology, broadband_downstream, broadband_upstream ";
387 my $order_by = $group_by;
389 "SELECT ".join(', ', @select) . "
391 WHERE ".join(' AND ', @where)."
401 my $date = $opt{date} || time;
402 my $agentnum = $opt{agentnum};
403 my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
404 my $censustract = "replace(cust_location.censustract, '.', '')";
407 "$censustract AS censustract",
408 # VoIP indicator (0 for non-VoIP, 1 for VoIP)
409 'COALESCE(is_voip, 0)',
410 # number of lines/subscriptions
411 "SUM($q * (CASE WHEN is_voip = 1 THEN 1 ELSE phone_lines END))",
412 # consumer grade lines/subscriptions
413 "SUM($q * COALESCE(is_consumer,0) * (CASE WHEN is_voip = 1 THEN voip_sessions ELSE phone_lines END))",
415 push @select, "array_to_string(array_agg(pkgnum), ',')" if $opt{detail};
418 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
419 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
420 JOIN part_pkg USING (pkgpart) '.
421 join_optionnames_int(qw(
422 is_phone is_voip is_consumer phone_lines voip_sessions
428 "(is_voip = 1 OR is_phone = 1)",
430 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
431 my $group_by = "$censustract, COALESCE(is_voip, 0)";
432 my $order_by = $group_by;
434 "SELECT ".join(', ', @select) . "
436 WHERE ".join(' AND ', @where)."
446 my $date = $opt{date} || time;
447 my $agentnum = $opt{agentnum};
448 my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
452 "SUM($q * phone_vges)",
453 "SUM($q * phone_circuits)",
454 "SUM($q * phone_lines)",
455 "SUM($q * (CASE WHEN is_broadband = 1 THEN phone_lines ELSE 0 END))",
456 "SUM($q * (CASE WHEN is_consumer = 1 AND phone_longdistance IS NULL THEN phone_lines ELSE 0 END))",
457 "SUM($q * (CASE WHEN is_consumer = 1 AND phone_longdistance = 1 THEN phone_lines ELSE 0 END))",
458 "SUM($q * (CASE WHEN is_consumer IS NULL AND phone_longdistance IS NULL THEN phone_lines ELSE 0 END))",
459 "SUM($q * (CASE WHEN is_consumer IS NULL AND phone_longdistance = 1 THEN phone_lines ELSE 0 END))",
460 "SUM($q * (CASE WHEN phone_localloop = 'owned' THEN phone_lines ELSE 0 END))",
461 "SUM($q * (CASE WHEN phone_localloop = 'leased' THEN phone_lines ELSE 0 END))",
462 "SUM($q * (CASE WHEN phone_localloop = 'resale' THEN phone_lines ELSE 0 END))",
463 "SUM($q * (CASE WHEN media = 'Fiber' THEN phone_lines ELSE 0 END))",
464 "SUM($q * (CASE WHEN media = 'Cable Modem' THEN phone_lines ELSE 0 END))",
465 "SUM($q * (CASE WHEN media = 'Fixed Wireless' THEN phone_lines ELSE 0 END))",
467 push @select, "array_to_string(array_agg(pkgnum),',')" if $opt{detail};
471 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
472 JOIN state USING (country, state)
473 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
474 JOIN part_pkg USING (pkgpart) '.
475 join_optionnames_int(qw(
476 is_phone is_broadband
477 phone_vges phone_circuits phone_lines
478 is_consumer phone_longdistance
480 join_optionnames('media', 'phone_localloop')
486 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
487 my $group_by = 'state.fips';
488 my $order_by = $group_by;
490 "SELECT ".join(', ', @select) . "
492 WHERE ".join(' AND ', @where)."
501 my $date = $opt{date} || time;
502 my $agentnum = $opt{agentnum};
503 my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
507 # OTT, OTT + consumer
508 "SUM($q * (CASE WHEN (voip_lastmile IS NULL) THEN 1 ELSE 0 END))",
509 "SUM($q * (CASE WHEN (voip_lastmile IS NULL AND is_consumer = 1) THEN 1 ELSE 0 END))",
510 # non-OTT: total, consumer, broadband bundle, media types
511 "SUM($q * (CASE WHEN (voip_lastmile = 1) THEN 1 ELSE 0 END))",
512 "SUM($q * (CASE WHEN (voip_lastmile = 1 AND is_consumer = 1) THEN 1 ELSE 0 END))",
513 "SUM($q * (CASE WHEN (voip_lastmile = 1 AND is_broadband = 1) THEN 1 ELSE 0 END))",
514 "SUM($q * (CASE WHEN (voip_lastmile = 1 AND media = 'Copper') THEN 1 ELSE 0 END))",
515 "SUM($q * (CASE WHEN (voip_lastmile = 1 AND media = 'Cable Modem') THEN 1 ELSE 0 END))",
516 "SUM($q * (CASE WHEN (voip_lastmile = 1 AND media = 'Fiber') THEN 1 ELSE 0 END))",
517 "SUM($q * (CASE WHEN (voip_lastmile = 1 AND media = 'Fixed Wireless') THEN 1 ELSE 0 END))",
518 "SUM($q * (CASE WHEN (voip_lastmile = 1 AND media NOT IN('Copper', 'Fiber', 'Cable Modem', 'Fixed Wireless') ) THEN 1 ELSE 0 END))",
520 push @select, "array_to_string(array_agg(pkgnum),',')" if $opt{detail};
524 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
525 JOIN state USING (country, state)
526 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
527 JOIN part_pkg USING (pkgpart) '.
528 join_optionnames_int(
529 qw( is_voip is_broadband is_consumer voip_lastmile)
531 join_optionnames('media')
537 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
538 my $group_by = 'state.fips';
539 my $order_by = $group_by;
541 "SELECT ".join(', ', @select) . "
543 WHERE ".join(' AND ', @where)."
552 my $date = $opt{date} || time;
553 my $agentnum = $opt{agentnum};
554 my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
558 'broadband_downstream',
559 'broadband_upstream',
561 "SUM(COALESCE(is_consumer, 0) * $q)",
563 push @select, "array_to_string(array_agg(pkgnum),',')" if $opt{detail};
567 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
568 JOIN state USING (country, state)
569 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
570 JOIN part_pkg USING (pkgpart) '.
571 join_optionnames_int(qw(
572 is_broadband technology
575 join_optionnames(qw(broadband_downstream broadband_upstream))
579 is_mobile_broadband()
581 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
582 my $group_by = 'state.fips, broadband_downstream, broadband_upstream ';
583 my $order_by = $group_by;
585 "SELECT ".join(', ', @select) . "
587 WHERE ".join(' AND ', @where)."
596 my $date = $opt{date} || time;
597 my $agentnum = $opt{agentnum};
598 my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
603 "SUM($q * COALESCE(mobile_direct,0))",
605 push @select, "array_to_string(array_agg(pkgnum),',')" if $opt{detail};
609 JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
610 JOIN state USING (country, state)
611 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
612 JOIN part_pkg USING (pkgpart) '.
613 join_optionnames_int(qw( is_mobile mobile_direct) )
619 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
620 my $group_by = 'state.fips';
621 my $order_by = $group_by;
623 "SELECT ".join(', ', @select) . "
625 WHERE ".join(' AND ', @where)."
633 Returns a Tie::IxHash reference of the internal short names used for the
634 report sections ('fbd', 'mbs', etc.) to the full names.
638 tie our %parts, 'Tie::IxHash', (
639 fbd => 'Fixed Broadband Deployment',
640 fbs => 'Fixed Broadband Subscription',
641 fvs => 'Fixed Voice Subscription',
642 lts => 'Local Exchange Telephone Subscription',
643 voip => 'Interconnected VoIP Subscription',
644 mbd => 'Mobile Broadband Deployment',
645 mbsa => 'Mobile Broadband Service Availability',
646 mbs => 'Mobile Broadband Subscription',
647 mvd => 'Mobile Voice Deployment',
648 mvs => 'Mobile Voice Subscription',
652 Storable::dclone(\%parts);
655 =item part_table SECTION
657 Returns the name of the primary table that's aggregated in the report section
658 SECTION. The last column of the report returned by the L</report> method is
659 a comma-separated list of record numbers, in this table, that are included in
660 the report line item.
665 my ($class, $part) = @_;
666 if ($part eq 'fbd') {
667 return 'deploy_zone_block';
670 } # add other cases as we add more of the deployment/availability reports