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   my $sql = "delete from fcc477map where formkey = ?";
 169   my $sth = dbh->prepare($sql) or die dbh->errstr;
 170   $sth->execute($key) or do {
 171     warn "WARNING: Error removing FCC 477 form defaults: " . $sth->errstr;
 172     $dbh->rollback if $oldAutoCommit;
 173   };
 174
 175   $sql = "insert into fcc477map (formkey,formvalue) values (?,?)";
 176   $sth = dbh->prepare($sql) or die dbh->errstr;
 177   $sth->execute($key,$value) or do {
 178     warn "WARNING: Error setting FCC 477 form defaults: " . $sth->errstr;
 179     $dbh->rollback if $oldAutoCommit;
 180   };
 181
 182   $dbh->commit or die $dbh->errstr if $oldAutoCommit;
 183
 184   '';
 185 }
 186
 187 sub parse_technology_option {
 188   my $cgi = shift;
 189   my $save = shift;
 190   my @result = ();
 191   my $i = 0;
 192   for (my $i = 0; $i < scalar(@technology); $i++) {
 193     my $value = $cgi->param("part1_technology_option_$i"); #lame
 194     save_fcc477map("part1_technology_option_$i",$value)
 195         if $save && $value =~ /^\d+$/;
 196     push @result, $value =~ /^\d+$/ ? $value : 0;
 197   }
 198   return (@result);
 199 }
 200
 201 sub statenum2state {
 202   my $num = shift;
 203   $states{$num};
 204 }
 205 ### everything above this point is unmaintained ###
 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 report_fixed_broadband OPTIONS
 263
 264 Returns the Fixed Broadband Subscription report (section 5.4), as an arrayref
 265 of an arrayrefs.  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 Columns of this report are:
 270 - census tract
 271 - technology code
 272 - downstream speed
 273 - upstream speed
 274 (the above columns form a key)
 275 - number of subscriptions
 276 - number of consumer-grade subscriptions
 277
 278 =cut
 279
 280 sub report_fixed_broadband {
 281   my $class = shift;
 282   my %opt = shift;
 283   my $date = $opt{date} || time;
 284   my $agentnum = $opt{agentnum};
 285
 286   my @select = (
 287     'cust_location.censustract',
 288     'technology',
 289     'broadband_downstream',
 290     'broadband_upstream',
 291     'COUNT(*)',
 292     'COUNT(is_consumer)',
 293   );
 294   my $from =
 295     'cust_pkg
 296       JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
 297       JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
 298       JOIN part_pkg USING (pkgpart) '.
 299       join_optionnames_int(qw(
 300         is_broadband technology
 301         is_consumer
 302         )).
 303       join_optionnames(qw(broadband_downstream broadband_upstream))
 304   ;
 305   my @where = (
 306     active_on($date),
 307     is_fixed_broadband()
 308   );
 309   push @where, "cust_main.agentnum = $agentnum" if $agentnum;
 310   my $group_by = 'cust_location.censustract, technology, '.
 311                    'broadband_downstream, broadband_upstream ';
 312   my $order_by = $group_by;
 313
 314   my $statement = "SELECT ".join(', ', @select) . "
 315   FROM $from
 316   WHERE ".join(' AND ', @where)."
 317   GROUP BY $group_by
 318   ORDER BY $order_by
 319   ";
 320
 321   warn $statement if $DEBUG;
 322   dbh->selectall_arrayref($statement);
 323 }
 324
 325 =item report_fixed_voice OPTIONS
 326
 327 Returns the Fixed Voice Subscription Detail report (section 5.5).  OPTIONS
 328 are as above.  Columns are:
 329
 330 - census tract
 331 - service type (0 for non-VoIP, 1 for VoIP)
 332 (the above columns form a key)
 333 - VGE lines/VoIP subscriptions in service
 334 - consumer grade VGE lines/VoIP subscriptions
 335
 336 =cut
 337
 338 sub report_fixed_voice {
 339   my $class = shift;
 340   my %opt = shift;
 341   my $date = $opt{date} || time;
 342   my $agentnum = $opt{agentnum};
 343
 344   my @select = (
 345     'cust_location.censustract',
 346     # VoIP indicator (0 for non-VoIP, 1 for VoIP)
 347     'COALESCE(is_voip, 0)',
 348     # number of lines/subscriptions
 349     'SUM(CASE WHEN is_voip = 1 THEN 1 ELSE phone_lines END)',
 350     # consumer grade lines/subscriptions
 351     'SUM(CASE WHEN is_consumer = 1 THEN ( CASE WHEN is_voip = 1 THEN voip_sessions ELSE phone_lines END) ELSE 0 END)'
 352   );
 353
 354   my $from = 'cust_pkg
 355     JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
 356     JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
 357     JOIN part_pkg USING (pkgpart) '.
 358     join_optionnames_int(qw(
 359       is_phone is_voip is_consumer phone_lines voip_sessions
 360       ))
 361   ;
 362
 363   my @where = (
 364     active_on($date),
 365     "(is_voip = 1 OR is_phone = 1)",
 366   );
 367   push @where, "cust_main.agentnum = $agentnum" if $agentnum;
 368   my $group_by = 'cust_location.censustract, COALESCE(is_voip, 0)';
 369   my $order_by = $group_by;
 370
 371   my $statement = "SELECT ".join(', ', @select) . "
 372   FROM $from
 373   WHERE ".join(' AND ', @where)."
 374   GROUP BY $group_by
 375   ORDER BY $order_by
 376   ";
 377
 378   warn $statement if $DEBUG;
 379   dbh->selectall_arrayref($statement);
 380 }
 381
 382 =item report_local_phone OPTIONS
 383
 384 Returns the Local Exchange Telephone Subscription report (section 5.6).
 385 OPTIONS are as above.  Each row is data for one state.  Columns are:
 386
 387 - state FIPS code (key)
 388 - wholesale switched voice lines
 389 - wholesale unswitched local loops
 390 - end-user total lines
 391 - end-user lines sold in a package with broadband
 392 - consumer-grade lines where you are not the long-distance carrier
 393 - consumer-grade lines where the carrier IS the long-distance carrier
 394 - business-grade lines where you are not the long-distance carrier
 395 - business-grade lines where the carrier IS the long-distance carrier
 396 - end-user lines where you own the local loop facility
 397 - end-user lines where you lease an unswitched local loop from a LEC
 398 - end-user lines resold from another carrier
 399 - end-user lines provided over fiber to the premises
 400 - end-user lines provided over coaxial
 401 - end-user lines provided over fixed wireless
 402
 403 =cut
 404
 405 sub report_local_phone {
 406   my $class = shift;
 407   my %opt = shift;
 408   my $date = $opt{date} || time;
 409   my $agentnum = $opt{agentnum};
 410
 411   my @select = (
 412     "state.fips",
 413     "SUM(phone_vges)",
 414     "SUM(phone_circuits)",
 415     "SUM(phone_lines)",
 416     "SUM(CASE WHEN is_broadband = 1 THEN phone_lines ELSE 0 END)",
 417     "SUM(CASE WHEN is_consumer = 1 AND phone_longdistance IS NULL THEN phone_lines ELSE 0 END)",
 418     "SUM(CASE WHEN is_consumer = 1 AND phone_longdistance = 1 THEN phone_lines ELSE 0 END)",
 419     "SUM(CASE WHEN is_consumer IS NULL AND phone_longdistance IS NULL THEN phone_lines ELSE 0 END)",
 420     "SUM(CASE WHEN is_consumer IS NULL AND phone_longdistance = 1 THEN phone_lines ELSE 0 END)",
 421     "SUM(CASE WHEN phone_localloop = 'owned' THEN phone_lines ELSE 0 END)",
 422     "SUM(CASE WHEN phone_localloop = 'leased' THEN phone_lines ELSE 0 END)",
 423     "SUM(CASE WHEN phone_localloop = 'resale' THEN phone_lines ELSE 0 END)",
 424     "SUM(CASE WHEN media = 'Fiber' THEN phone_lines ELSE 0 END)",
 425     "SUM(CASE WHEN media = 'Cable Modem' THEN phone_lines ELSE 0 END)",
 426     "SUM(CASE WHEN media = 'Fixed Wireless' THEN phone_lines ELSE 0 END)",
 427   );
 428   my $from =
 429     'cust_pkg
 430       JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
 431       JOIN state USING (country, state)
 432       JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
 433       JOIN part_pkg USING (pkgpart) '.
 434       join_optionnames_int(qw(
 435         is_phone is_broadband
 436         phone_vges phone_circuits phone_lines
 437         is_consumer phone_longdistance
 438         )).
 439       join_optionnames('media', 'phone_localloop')
 440   ;
 441   my @where = (
 442     active_on($date),
 443     "is_phone = 1",
 444   );
 445   push @where, "cust_main.agentnum = $agentnum" if $agentnum;
 446   my $group_by = 'state.fips';
 447   my $order_by = $group_by;
 448
 449   my $statement = "SELECT ".join(', ', @select) . "
 450   FROM $from
 451   WHERE ".join(' AND ', @where)."
 452   GROUP BY $group_by
 453   ORDER BY $order_by
 454   ";
 455
 456   warn $statement if $DEBUG;
 457   dbh->selectall_arrayref($statement);
 458 }
 459
 460 =item report_voip OPTIONS
 461
 462 Returns the Interconnected VoIP Subscription report (section 5.7).
 463 OPTIONS are as above.  Columns are:
 464
 465 - state FIPS code (key)
 466 - OTT subscriptions (non-last-mile)
 467 - OTT subscriptions sold to consumers
 468 - last-mile subscriptions
 469 - last-mile subscriptions sold to consumers
 470 - last-mile subscriptions bundled with broadband Internet
 471 - last-mile subscriptions over copper pairs
 472 - last-mile subscriptions over coaxial
 473 - last-mile subscriptions over fiber
 474 - last-mile subscriptions over fixed wireless
 475 - last-mile subscriptions over other media
 476
 477 =cut
 478
 479 sub report_voip {
 480   my $class = shift;
 481   my %opt = shift;
 482   my $date = $opt{date} || time;
 483   my $agentnum = $opt{agentnum};
 484
 485   my @select = (
 486     "state.fips",
 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)",
 499   );
 500
 501   my $from =
 502     'cust_pkg
 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)
 509       ).
 510       join_optionnames('media')
 511   ;
 512   my @where = (
 513     active_on($date),
 514     "is_voip = 1",
 515   );
 516   push @where, "cust_main.agentnum = $agentnum" if $agentnum;
 517   my $group_by = 'state.fips';
 518   my $order_by = $group_by;
 519
 520   my $statement = "SELECT ".join(', ', @select) . "
 521   FROM $from
 522   WHERE ".join(' AND ', @where)."
 523   GROUP BY $group_by
 524   ORDER BY $order_by
 525   ";
 526
 527   warn $statement if $DEBUG;
 528   dbh->selectall_arrayref($statement);
 529 }
 530
 531
 532 1;