FS/FS/Report/FCC_477.pm

   1 package FS::Report::FCC_477;
   2 use base qw( FS::Report );
   3
   4 use strict;
   5 use vars qw( @upload @download @technology @part2aoption @part2boption
   6              %states
   7              $DEBUG
   8            );
   9 use FS::Record qw( dbh );
  10
  11 $DEBUG = 1;
  12
  13 =head1 NAME
  14
  15 FS::Report::FCC_477 - Routines for FCC Form 477 reports
  16
  17 =head1 SYNOPSIS
  18
  19 =head1 BUGS
  20
  21 Documentation.
  22
  23 =head1 SEE ALSO
  24
  25 =cut
  26
  27 @upload = qw(
  28  <200kbps
  29  200-768kbps
  30  768kbps-1.5mbps
  31  1.5-3mpbs
  32  3-6mbps
  33  6-10mbps
  34  10-25mbps
  35  25-100mbps
  36  >100mbps
  37 );
  38
  39 @download = qw(
  40  200-768kbps
  41  768kbps-1.5mbps
  42  1.5-3mbps
  43  3-6mbps
  44  6-10mbps
  45  10-25mbps
  46  25-100mbps
  47  >100mbps
  48 );
  49
  50 @technology = (
  51   'Asymmetric xDSL',
  52   'Symmetric xDSL',
  53   'Other Wireline',
  54   'Cable Modem',
  55   'Optical Carrier',
  56   'Satellite',
  57   'Terrestrial Fixed Wireless',
  58   'Terrestrial Mobile Wireless',
  59   'Electric Power Line',
  60   'Other Technology',
  61 );
  62
  63 @part2aoption = (
  64  'LD carrier',
  65  'owned loops',
  66  'unswitched UNE loops',
  67  'UNE-P',
  68  'UNE-P replacement',
  69  'FTTP',
  70  'coax',
  71  'wireless',
  72 );
  73
  74 @part2boption = (
  75  'nomadic',
  76  'copper',
  77  'FTTP',
  78  'coax',
  79  'wireless',
  80  'other broadband',
  81 );
  82
  83 #from the select at http://www.ffiec.gov/census/default.aspx
  84 #though this is now in the database, also
  85 %states = (
  86   '01' => 'ALABAMA (AL)',
  87   '02' => 'ALASKA (AK)',
  88   '04' => 'ARIZONA (AZ)',
  89   '05' => 'ARKANSAS (AR)',
  90   '06' => 'CALIFORNIA (CA)',
  91   '08' => 'COLORADO (CO)',
  92
  93   '09' => 'CONNECTICUT (CT)',
  94   '10' => 'DELAWARE (DE)',
  95   '11' => 'DISTRICT OF COLUMBIA (DC)',
  96   '12' => 'FLORIDA (FL)',
  97   '13' => 'GEORGIA (GA)',
  98   '15' => 'HAWAII (HI)',
  99
 100   '16' => 'IDAHO (ID)',
 101   '17' => 'ILLINOIS (IL)',
 102   '18' => 'INDIANA (IN)',
 103   '19' => 'IOWA (IA)',
 104   '20' => 'KANSAS (KS)',
 105   '21' => 'KENTUCKY (KY)',
 106
 107   '22' => 'LOUISIANA (LA)',
 108   '23' => 'MAINE (ME)',
 109   '24' => 'MARYLAND (MD)',
 110   '25' => 'MASSACHUSETTS (MA)',
 111   '26' => 'MICHIGAN (MI)',
 112   '27' => 'MINNESOTA (MN)',
 113
 114   '28' => 'MISSISSIPPI (MS)',
 115   '29' => 'MISSOURI (MO)',
 116   '30' => 'MONTANA (MT)',
 117   '31' => 'NEBRASKA (NE)',
 118   '32' => 'NEVADA (NV)',
 119   '33' => 'NEW HAMPSHIRE (NH)',
 120
 121   '34' => 'NEW JERSEY (NJ)',
 122   '35' => 'NEW MEXICO (NM)',
 123   '36' => 'NEW YORK (NY)',
 124   '37' => 'NORTH CAROLINA (NC)',
 125   '38' => 'NORTH DAKOTA (ND)',
 126   '39' => 'OHIO (OH)',
 127
 128   '40' => 'OKLAHOMA (OK)',
 129   '41' => 'OREGON (OR)',
 130   '42' => 'PENNSYLVANIA (PA)',
 131   '44' => 'RHODE ISLAND (RI)',
 132   '45' => 'SOUTH CAROLINA (SC)',
 133   '46' => 'SOUTH DAKOTA (SD)',
 134
 135   '47' => 'TENNESSEE (TN)',
 136   '48' => 'TEXAS (TX)',
 137   '49' => 'UTAH (UT)',
 138   '50' => 'VERMONT (VT)',
 139   '51' => 'VIRGINIA (VA)',
 140   '53' => 'WASHINGTON (WA)',
 141
 142   '54' => 'WEST VIRGINIA (WV)',
 143   '55' => 'WISCONSIN (WI)',
 144   '56' => 'WYOMING (WY)',
 145   '72' => 'PUERTO RICO (PR)',
 146 );
 147
 148 sub restore_fcc477map {
 149   my $key = shift;
 150   FS::Record::scalar_sql('',"select formvalue from fcc477map where formkey = ?",$key);
 151 }
 152
 153 sub save_fcc477map {
 154   my $key = shift;
 155   my $value = shift;
 156
 157   local $SIG{HUP} = 'IGNORE';
 158   local $SIG{INT} = 'IGNORE';
 159   local $SIG{QUIT} = 'IGNORE';
 160   local $SIG{TERM} = 'IGNORE';
 161   local $SIG{TSTP} = 'IGNORE';
 162   local $SIG{PIPE} = 'IGNORE';
 163
 164   my $oldAutoCommit = $FS::UID::AutoCommit;
 165   local $FS::UID::AutoCommit = 0;
 166   my $dbh = dbh;
 167
 168   # lame (should be normal FS::Record access)
 169
 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;
 175   };
 176
 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;
 182   };
 183
 184   $dbh->commit or die $dbh->errstr if $oldAutoCommit;
 185
 186   '';
 187 }
 188
 189 sub parse_technology_option {
 190   my $cgi = shift;
 191   my $save = shift;
 192   my @result = ();
 193   my $i = 0;
 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;
 199   }
 200   return (@result);
 201 }
 202
 203 sub statenum2state {
 204   my $num = shift;
 205   $states{$num};
 206 }
 207
 208 =head1 THE "NEW" REPORT (October 2014 and later)
 209
 210 =head2 METHODS
 211
 212 =over 4
 213
 214 =cut
 215
 216 sub join_optionnames {
 217   join(' ', map { join_optionname($_) } @_);
 218 }
 219
 220 sub join_optionnames_int {
 221   join(' ', map { join_optionname_int($_) } @_);
 222 }
 223
 224 sub join_optionname {
 225   # Returns a FROM phrase to join a specific option into the query (via
 226   # part_pkg).  The option value will appear as a field with the same name
 227   # as the option.
 228   my $name = shift;
 229   "LEFT JOIN (SELECT pkgpart, optionvalue AS $name FROM part_pkg_fcc_option".
 230     " WHERE fccoptionname = '$name') AS t_$name".
 231     " ON (part_pkg.pkgpart = t_$name.pkgpart)";
 232 }
 233
 234 sub join_optionname_int {
 235   # Returns a FROM phrase to join a specific option into the query (via
 236   # part_pkg) and cast it to integer..  Note this does not convert nulls
 237   # to zero.
 238   my $name = shift;
 239   "LEFT JOIN (SELECT pkgpart, CAST(optionvalue AS int) AS $name
 240    FROM part_pkg_fcc_option".
 241     " WHERE fccoptionname = '$name') AS t_$name".
 242     " ON (part_pkg.pkgpart = t_$name.pkgpart)";
 243 }
 244
 245 sub active_on {
 246   # Returns a condition to limit packages to those that were setup before a
 247   # certain date, and not canceled before that date.
 248   #
 249   # (Strictly speaking this should also exclude suspended packages but
 250   # "suspended as of some past date" is a complicated query.)
 251   my $date = shift;
 252   "cust_pkg.setup <= $date AND ".
 253   "(cust_pkg.cancel IS NULL OR cust_pkg.cancel > $date)";
 254 }
 255
 256 sub is_fixed_broadband {
 257   "is_broadband::int = 1 AND technology::int IN(".join(',',
 258     10, 11, 12, 20, 30, 40, 41, 42, 50, 60, 70, 90, 0
 259   ).")";
 260 }
 261
 262 =item part6 OPTIONS
 263
 264 Returns Part 6 of the 2014 FCC 477 data, as an arrayref of arrayrefs.
 265 OPTIONS may contain:
 266 - date: a timestamp value to count active packages as of that date
 267 - agentnum: limit to customers of that agent
 268
 269 Part 6 is the broadband subscription detail report.  Columns of the
 270 report are:
 271 - census tract
 272 - technology code
 273 - downstream speed
 274 - upstream speed
 275 (the above columns form a key)
 276 - number of subscriptions
 277 - number of consumer-grade subscriptions
 278
 279 =cut
 280
 281 sub part6 {
 282   my $class = shift;
 283   my %opt = shift;
 284   my $date = $opt{date} || time;
 285   my $agentnum = $opt{agentnum};
 286
 287   my @select = (
 288     'cust_location.censustract',
 289     'technology',
 290     'broadband_downstream',
 291     'broadband_upstream',
 292     'COUNT(*)',
 293     'COUNT(is_consumer)',
 294   );
 295   my $from =
 296     'cust_pkg
 297       JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
 298       JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
 299       JOIN part_pkg USING (pkgpart) '.
 300       join_optionnames_int(qw(
 301         is_broadband technology
 302         is_consumer
 303         )).
 304       join_optionnames(qw(broadband_downstream broadband_upstream))
 305   ;
 306   my @where = (
 307     active_on($date),
 308     is_fixed_broadband()
 309   );
 310   push @where, "cust_main.agentnum = $agentnum" if $agentnum;
 311   my $group_by = 'cust_location.censustract, technology, '.
 312                    'broadband_downstream, broadband_upstream ';
 313   my $order_by = $group_by;
 314
 315   my $statement = "SELECT ".join(', ', @select) . "
 316   FROM $from
 317   WHERE ".join(' AND ', @where)."
 318   GROUP BY $group_by
 319   ORDER BY $order_by
 320   ";
 321
 322   warn $statement if $DEBUG;
 323   dbh->selectall_arrayref($statement);
 324 }
 325
 326 =item part9 OPTIONS
 327
 328 Returns Part 9 of the 2014 FCC 477 data.  Part 9 is the Local Exchange
 329 Telephone Subscription report.  Columns are:
 330
 331 - state FIPS code (key)
 332 - wholesale switched voice lines
 333 - wholesale unswitched local loops
 334 - end-user total lines
 335 - end-user lines sold in a package with broadband
 336 - consumer-grade lines where you are not the long-distance carrier
 337 - consumer-grade lines where the carrier IS the long-distance carrier
 338 - business-grade lines where you are not the long-distance carrier
 339 - business-grade lines where the carrier IS the long-distance carrier
 340 - end-user lines where you own the local loop facility
 341 - end-user lines where you lease an unswitched local loop from a LEC
 342 - end-user lines resold from another carrier
 343 - end-user lines provided over fiber to the premises
 344 - end-user lines provided over coaxial
 345 - end-user lines provided over fixed wireless
 346
 347 =cut
 348
 349 sub part9 {
 350   my $class = shift;
 351   my %opt = shift;
 352   my $date = $opt{date} || time;
 353   my $agentnum = $opt{agentnum};
 354
 355   my @select = (
 356     "state.fips",
 357     "SUM(phone_vges)",
 358     "SUM(phone_circuits)",
 359     "SUM(phone_lines)",
 360     "SUM(CASE WHEN is_broadband = 1 THEN phone_lines ELSE 0 END)",
 361     "SUM(CASE WHEN is_consumer = 1 AND is_longdistance IS NULL THEN phone_lines ELSE 0 END)",
 362     "SUM(CASE WHEN is_consumer = 1 AND is_longdistance = 1 THEN phone_lines ELSE 0 END)",
 363     "SUM(CASE WHEN is_consumer IS NULL AND is_longdistance IS NULL THEN phone_lines ELSE 0 END)",
 364     "SUM(CASE WHEN is_consumer IS NULL AND is_longdistance = 1 THEN phone_lines ELSE 0 END)",
 365     "SUM(CASE WHEN phone_localloop = 'owned' THEN phone_lines ELSE 0 END)",
 366     "SUM(CASE WHEN phone_localloop = 'leased' THEN phone_lines ELSE 0 END)",
 367     "SUM(CASE WHEN phone_localloop = 'resale' THEN phone_lines ELSE 0 END)",
 368     "SUM(CASE WHEN media = 'Fiber' THEN phone_lines ELSE 0 END)",
 369     "SUM(CASE WHEN media = 'Cable Modem' THEN phone_lines ELSE 0 END)",
 370     "SUM(CASE WHEN media = 'Fixed Wireless' THEN phone_lines ELSE 0 END)",
 371   );
 372   my $from =
 373     'cust_pkg
 374       JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
 375       JOIN state USING (country, state)
 376       JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
 377       JOIN part_pkg USING (pkgpart) '.
 378       join_optionnames_int(qw(
 379         is_phone is_broadband
 380         phone_vges phone_circuits phone_lines
 381         is_consumer is_longdistance
 382         )).
 383       join_optionnames('media', 'phone_localloop')
 384   ;
 385   my @where = (
 386     active_on($date),
 387     "is_phone = 1",
 388   );
 389   push @where, "cust_main.agentnum = $agentnum" if $agentnum;
 390   my $group_by = 'state.fips';
 391   my $order_by = $group_by;
 392
 393   my $statement = "SELECT ".join(', ', @select) . "
 394   FROM $from
 395   WHERE ".join(' AND ', @where)."
 396   GROUP BY $group_by
 397   ORDER BY $order_by
 398   ";
 399
 400   warn $statement if $DEBUG;
 401   dbh->selectall_arrayref($statement);
 402 }
 403
 404 sub part10 {
 405   my $class = shift;
 406   my %opt = shift;
 407   my $date = $opt{date} || time;
 408   my $agentnum = $opt{agentnum};
 409
 410   my @select = (
 411     "state.fips",
 412     # OTT, OTT + consumer
 413     "SUM(CASE WHEN (voip_lastmile IS NULL) THEN 1 ELSE 0 END)",
 414     "SUM(CASE WHEN (voip_lastmile IS NULL AND is_consumer = 1) THEN 1 ELSE 0 END)",
 415     # non-OTT: total, consumer, broadband bundle, media types
 416     "SUM(CASE WHEN (voip_lastmile = 1) THEN 1 ELSE 0 END)",
 417     "SUM(CASE WHEN (voip_lastmile = 1 AND is_consumer = 1) THEN 1 ELSE 0 END)",
 418     "SUM(CASE WHEN (voip_lastmile = 1 AND is_broadband = 1) THEN 1 ELSE 0 END)",
 419     "SUM(CASE WHEN (voip_lastmile = 1 AND media = 'Copper') THEN 1 ELSE 0 END)",
 420     "SUM(CASE WHEN (voip_lastmile = 1 AND media = 'Cable Modem') THEN 1 ELSE 0 END)",
 421     "SUM(CASE WHEN (voip_lastmile = 1 AND media = 'Fiber') THEN 1 ELSE 0 END)",
 422     "SUM(CASE WHEN (voip_lastmile = 1 AND media = 'Fixed Wireless') THEN 1 ELSE 0 END)",
 423     "SUM(CASE WHEN (voip_lastmile = 1 AND media NOT IN('Copper', 'Fiber', 'Cable Modem', 'Fixed Wireless') ) THEN 1 ELSE 0 END)",
 424   );
 425
 426   my $from =
 427     'cust_pkg
 428       JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
 429       JOIN state USING (country, state)
 430       JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
 431       JOIN part_pkg USING (pkgpart) '.
 432       join_optionnames_int(
 433         qw( is_voip is_broadband is_consumer voip_lastmile)
 434       ).
 435       join_optionnames('media')
 436   ;
 437   my @where = (
 438     active_on($date),
 439     "is_voip = 1",
 440   );
 441   push @where, "cust_main.agentnum = $agentnum" if $agentnum;
 442   my $group_by = 'state.fips';
 443   my $order_by = $group_by;
 444
 445   my $statement = "SELECT ".join(', ', @select) . "
 446   FROM $from
 447   WHERE ".join(' AND ', @where)."
 448   GROUP BY $group_by
 449   ORDER BY $order_by
 450   ";
 451
 452   warn $statement if $DEBUG;
 453   dbh->selectall_arrayref($statement);
 454 }
 455
 456 =item part11 OPTIONS
 457
 458 Returns part 11 (voice subscription detail), as above.
 459
 460 =cut
 461
 462 sub part11 {
 463   my $class = shift;
 464   my %opt = shift;
 465   my $date = $opt{date} || time;
 466   my $agentnum = $opt{agentnum};
 467
 468   my @select = (
 469     'cust_location.censustract',
 470     # VoIP indicator (0 for non-VoIP, 1 for VoIP)
 471     'COALESCE(is_voip, 0)',
 472     # number of lines/subscriptions
 473     'SUM(CASE WHEN is_voip = 1 THEN 1 ELSE phone_lines END)',
 474     # consumer grade lines/subscriptions
 475     'SUM(CASE WHEN is_consumer = 1 THEN ( CASE WHEN is_voip = 1 THEN 1 ELSE phone_lines END) ELSE 0 END)'
 476   );
 477
 478   my $from = 'cust_pkg
 479     JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
 480     JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
 481     JOIN part_pkg USING (pkgpart) '.
 482     join_optionnames_int(qw(
 483       is_phone is_voip is_consumer phone_lines
 484       ))
 485   ;
 486
 487   my @where = (
 488     active_on($date),
 489     "(is_voip = 1 OR is_phone = 1)",
 490   );
 491   push @where, "cust_main.agentnum = $agentnum" if $agentnum;
 492   my $group_by = 'cust_location.censustract, COALESCE(is_voip, 0)';
 493   my $order_by = $group_by;
 494
 495   my $statement = "SELECT ".join(', ', @select) . "
 496   FROM $from
 497   WHERE ".join(' AND ', @where)."
 498   GROUP BY $group_by
 499   ORDER BY $order_by
 500   ";
 501
 502   warn $statement if $DEBUG;
 503   dbh->selectall_arrayref($statement);
 504 }
 505
 506 1;