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 use Tie::IxHash;
  12 use Storable;
  13
  14 $DEBUG = 0;
  15
  16 =head1 NAME
  17
  18 FS::Report::FCC_477 - Routines for FCC Form 477 reports
  19
  20 =head1 SYNOPSIS
  21
  22 =head1 BUGS
  23
  24 Documentation.
  25
  26 =head1 SEE ALSO
  27
  28 =cut
  29
  30 @upload = qw(
  31  <200kbps
  32  200-768kbps
  33  768kbps-1.5mbps
  34  1.5-3mpbs
  35  3-6mbps
  36  6-10mbps
  37  10-25mbps
  38  25-100mbps
  39  >100mbps
  40 );
  41
  42 @download = qw(
  43  200-768kbps
  44  768kbps-1.5mbps
  45  1.5-3mbps
  46  3-6mbps
  47  6-10mbps
  48  10-25mbps
  49  25-100mbps
  50  >100mbps
  51 );
  52
  53 @technology = (
  54   'Asymmetric xDSL',
  55   'Symmetric xDSL',
  56   'Other Wireline',
  57   'Cable Modem',
  58   'Optical Carrier',
  59   'Satellite',
  60   'Terrestrial Fixed Wireless',
  61   'Terrestrial Mobile Wireless',
  62   'Electric Power Line',
  63   'Other Technology',
  64 );
  65
  66 @part2aoption = (
  67  'LD carrier',
  68  'owned loops',
  69  'unswitched UNE loops',
  70  'UNE-P',
  71  'UNE-P replacement',
  72  'FTTP',
  73  'coax',
  74  'wireless',
  75 );
  76
  77 @part2boption = (
  78  'nomadic',
  79  'copper',
  80  'FTTP',
  81  'coax',
  82  'wireless',
  83  'other broadband',
  84 );
  85
  86 #from the select at http://www.ffiec.gov/census/default.aspx
  87 #though this is now in the database, also
  88 %states = (
  89   '01' => 'ALABAMA (AL)',
  90   '02' => 'ALASKA (AK)',
  91   '04' => 'ARIZONA (AZ)',
  92   '05' => 'ARKANSAS (AR)',
  93   '06' => 'CALIFORNIA (CA)',
  94   '08' => 'COLORADO (CO)',
  95
  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)',
 102
 103   '16' => 'IDAHO (ID)',
 104   '17' => 'ILLINOIS (IL)',
 105   '18' => 'INDIANA (IN)',
 106   '19' => 'IOWA (IA)',
 107   '20' => 'KANSAS (KS)',
 108   '21' => 'KENTUCKY (KY)',
 109
 110   '22' => 'LOUISIANA (LA)',
 111   '23' => 'MAINE (ME)',
 112   '24' => 'MARYLAND (MD)',
 113   '25' => 'MASSACHUSETTS (MA)',
 114   '26' => 'MICHIGAN (MI)',
 115   '27' => 'MINNESOTA (MN)',
 116
 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)',
 123
 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)',
 129   '39' => 'OHIO (OH)',
 130
 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)',
 137
 138   '47' => 'TENNESSEE (TN)',
 139   '48' => 'TEXAS (TX)',
 140   '49' => 'UTAH (UT)',
 141   '50' => 'VERMONT (VT)',
 142   '51' => 'VIRGINIA (VA)',
 143   '53' => 'WASHINGTON (WA)',
 144
 145   '54' => 'WEST VIRGINIA (WV)',
 146   '55' => 'WISCONSIN (WI)',
 147   '56' => 'WYOMING (WY)',
 148   '72' => 'PUERTO RICO (PR)',
 149 );
 150
 151 sub restore_fcc477map {
 152   my $key = shift;
 153   FS::Record::scalar_sql('',"select formvalue from fcc477map where formkey = ?",$key);
 154 }
 155
 156 sub save_fcc477map {
 157   my $key = shift;
 158   my $value = shift;
 159
 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';
 166
 167   my $oldAutoCommit = $FS::UID::AutoCommit;
 168   local $FS::UID::AutoCommit = 0;
 169   my $dbh = dbh;
 170
 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;
 176   };
 177
 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;
 183   };
 184
 185   $dbh->commit or die $dbh->errstr if $oldAutoCommit;
 186
 187   '';
 188 }
 189
 190 sub parse_technology_option {
 191   my $cgi = shift;
 192   my $save = shift;
 193   my @result = ();
 194   my $i = 0;
 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;
 200   }
 201   return (@result);
 202 }
 203
 204 sub statenum2state {
 205   my $num = shift;
 206   $states{$num};
 207 }
 208 ### everything above this point is unmaintained ###
 209
 210
 211 =head1 THE "NEW" REPORT (October 2014 and later)
 212
 213 =head2 METHODS
 214
 215 =over 4
 216
 217 =cut
 218
 219 # functions for internal use
 220
 221 sub join_optionnames {
 222   join(' ', map { join_optionname($_) } @_);
 223 }
 224
 225 sub join_optionnames_int {
 226   join(' ', map { join_optionname_int($_) } @_);
 227 }
 228
 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
 232   # as the option.
 233   my $name = shift;
 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)";
 237 }
 238
 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
 242   # to zero.
 243   my $name = shift;
 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)";
 248 }
 249
 250 sub active_on {
 251   # Returns a condition to limit packages to those that were setup before a
 252   # certain date, and not canceled before that date.
 253   #
 254   # (Strictly speaking this should also exclude suspended packages but
 255   # "suspended as of some past date" is a complicated query.)
 256   my $date = shift;
 257   "cust_pkg.setup <= $date AND ".
 258   "(cust_pkg.cancel IS NULL OR cust_pkg.cancel > $date)";
 259 }
 260
 261 sub is_fixed_broadband {
 262   "is_broadband::int = 1 AND technology::int IN( 10, 11, 12, 20, 30, 40, 41, 42, 50, 60, 70, 90, 0 )"
 263 }
 264
 265 sub is_mobile_broadband {
 266   "is_broadband::int = 1 AND technology::int IN( 80, 81, 82, 83, 84, 85, 86, 87, 88)"
 267 }
 268
 269 =item report SECTION, OPTIONS
 270
 271 Returns the report section SECTION (see the C<parts> method for section
 272 name strings) as an arrayref of arrayrefs.  OPTIONS may contain "date"
 273 (a timestamp value to run the report as of this date) and "agentnum"
 274 (to limit to a single agent).
 275
 276 =cut
 277
 278 sub report {
 279   my $class = shift;
 280   my $section = shift;
 281   my %opt = @_;
 282
 283   my $method = $section.'_sql';
 284   die "Report section '$section' is not implemented\n"
 285     unless $class->can($method);
 286   my $statement = $class->$method(%opt);
 287
 288   my $sth = dbh->prepare($statement);
 289   $sth->execute or die $sth->errstr;
 290   $sth->fetchall_arrayref;
 291 }
 292
 293 sub fbd_sql {
 294   my $class = shift;
 295   my %opt = @_;
 296   my $date = $opt{date} || time;
 297   warn $date;
 298   my $agentnum = $opt{agentnum};
 299
 300   my @select = (
 301     'censusblock',
 302     'COALESCE(dbaname, agent.agent)',
 303     'technology',
 304     'CASE WHEN is_consumer IS NOT NULL THEN 1 ELSE 0 END',
 305     'adv_speed_down',
 306     'adv_speed_up',
 307     'CASE WHEN is_business IS NOT NULL THEN 1 ELSE 0 END',
 308     'cir_speed_down',
 309     'cir_speed_up',
 310   );
 311   my $from =
 312     'deploy_zone_block
 313     JOIN deploy_zone USING (zonenum)
 314     JOIN agent USING (agentnum)';
 315   my @where = (
 316     "zonetype = 'B'",
 317     "active_date  < $date",
 318     "(expire_date > $date OR expire_date IS NULL)",
 319   );
 320   push @where, "agentnum = $agentnum" if $agentnum;
 321
 322   my $order_by = 'censusblock, dbaname, technology, is_consumer, is_business';
 323
 324   "SELECT ".join(', ', @select) . "
 325   FROM $from
 326   WHERE ".join(' AND ', @where)."
 327   ORDER BY $order_by
 328   ";
 329 }
 330
 331 sub fbs_sql {
 332   my $class = shift;
 333   my %opt = @_;
 334   my $date = $opt{date} || time;
 335   my $agentnum = $opt{agentnum};
 336
 337   my @select = (
 338     'cust_location.censustract',
 339     'technology',
 340     'broadband_downstream',
 341     'broadband_upstream',
 342     'COUNT(*)',
 343     'COUNT(is_consumer)',
 344   );
 345   my $from =
 346     'cust_pkg
 347       JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
 348       JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
 349       JOIN part_pkg USING (pkgpart) '.
 350       join_optionnames_int(qw(
 351         is_broadband technology
 352         is_consumer
 353         )).
 354       join_optionnames(qw(broadband_downstream broadband_upstream))
 355   ;
 356   my @where = (
 357     active_on($date),
 358     is_fixed_broadband()
 359   );
 360   push @where, "cust_main.agentnum = $agentnum" if $agentnum;
 361   my $group_by = 'cust_location.censustract, technology, '.
 362                    'broadband_downstream, broadband_upstream ';
 363   my $order_by = $group_by;
 364
 365   "SELECT ".join(', ', @select) . "
 366   FROM $from
 367   WHERE ".join(' AND ', @where)."
 368   GROUP BY $group_by
 369   ORDER BY $order_by
 370   ";
 371
 372 }
 373
 374 sub fvs_sql {
 375   my $class = shift;
 376   my %opt = @_;
 377   my $date = $opt{date} || time;
 378   my $agentnum = $opt{agentnum};
 379
 380   my @select = (
 381     'cust_location.censustract',
 382     # VoIP indicator (0 for non-VoIP, 1 for VoIP)
 383     'COALESCE(is_voip, 0)',
 384     # number of lines/subscriptions
 385     'SUM(CASE WHEN is_voip = 1 THEN 1 ELSE phone_lines END)',
 386     # consumer grade lines/subscriptions
 387     'SUM(CASE WHEN is_consumer = 1 THEN ( CASE WHEN is_voip = 1 THEN voip_sessions ELSE phone_lines END) ELSE 0 END)'
 388   );
 389
 390   my $from = 'cust_pkg
 391     JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
 392     JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
 393     JOIN part_pkg USING (pkgpart) '.
 394     join_optionnames_int(qw(
 395       is_phone is_voip is_consumer phone_lines voip_sessions
 396       ))
 397   ;
 398
 399   my @where = (
 400     active_on($date),
 401     "(is_voip = 1 OR is_phone = 1)",
 402   );
 403   push @where, "cust_main.agentnum = $agentnum" if $agentnum;
 404   my $group_by = 'cust_location.censustract, COALESCE(is_voip, 0)';
 405   my $order_by = $group_by;
 406
 407   "SELECT ".join(', ', @select) . "
 408   FROM $from
 409   WHERE ".join(' AND ', @where)."
 410   GROUP BY $group_by
 411   ORDER BY $order_by
 412   ";
 413
 414 }
 415
 416 sub lts_sql {
 417   my $class = shift;
 418   my %opt = @_;
 419   my $date = $opt{date} || time;
 420   my $agentnum = $opt{agentnum};
 421
 422   my @select = (
 423     "state.fips",
 424     "SUM(phone_vges)",
 425     "SUM(phone_circuits)",
 426     "SUM(phone_lines)",
 427     "SUM(CASE WHEN is_broadband = 1 THEN phone_lines ELSE 0 END)",
 428     "SUM(CASE WHEN is_consumer = 1 AND phone_longdistance IS NULL THEN phone_lines ELSE 0 END)",
 429     "SUM(CASE WHEN is_consumer = 1 AND phone_longdistance = 1 THEN phone_lines ELSE 0 END)",
 430     "SUM(CASE WHEN is_consumer IS NULL AND phone_longdistance IS NULL THEN phone_lines ELSE 0 END)",
 431     "SUM(CASE WHEN is_consumer IS NULL AND phone_longdistance = 1 THEN phone_lines ELSE 0 END)",
 432     "SUM(CASE WHEN phone_localloop = 'owned' THEN phone_lines ELSE 0 END)",
 433     "SUM(CASE WHEN phone_localloop = 'leased' THEN phone_lines ELSE 0 END)",
 434     "SUM(CASE WHEN phone_localloop = 'resale' THEN phone_lines ELSE 0 END)",
 435     "SUM(CASE WHEN media = 'Fiber' THEN phone_lines ELSE 0 END)",
 436     "SUM(CASE WHEN media = 'Cable Modem' THEN phone_lines ELSE 0 END)",
 437     "SUM(CASE WHEN media = 'Fixed Wireless' THEN phone_lines ELSE 0 END)",
 438   );
 439   my $from =
 440     'cust_pkg
 441       JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
 442       JOIN state USING (country, state)
 443       JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
 444       JOIN part_pkg USING (pkgpart) '.
 445       join_optionnames_int(qw(
 446         is_phone is_broadband
 447         phone_vges phone_circuits phone_lines
 448         is_consumer phone_longdistance
 449         )).
 450       join_optionnames('media', 'phone_localloop')
 451   ;
 452   my @where = (
 453     active_on($date),
 454     "is_phone = 1",
 455   );
 456   push @where, "cust_main.agentnum = $agentnum" if $agentnum;
 457   my $group_by = 'state.fips';
 458   my $order_by = $group_by;
 459
 460   "SELECT ".join(', ', @select) . "
 461   FROM $from
 462   WHERE ".join(' AND ', @where)."
 463   GROUP BY $group_by
 464   ORDER BY $order_by
 465   ";
 466 }
 467
 468 sub voip_sql {
 469   my $class = shift;
 470   my %opt = @_;
 471   my $date = $opt{date} || time;
 472   my $agentnum = $opt{agentnum};
 473
 474   my @select = (
 475     "state.fips",
 476     # OTT, OTT + consumer
 477     "SUM(CASE WHEN (voip_lastmile IS NULL) THEN 1 ELSE 0 END)",
 478     "SUM(CASE WHEN (voip_lastmile IS NULL AND is_consumer = 1) THEN 1 ELSE 0 END)",
 479     # non-OTT: total, consumer, broadband bundle, media types
 480     "SUM(CASE WHEN (voip_lastmile = 1) THEN 1 ELSE 0 END)",
 481     "SUM(CASE WHEN (voip_lastmile = 1 AND is_consumer = 1) THEN 1 ELSE 0 END)",
 482     "SUM(CASE WHEN (voip_lastmile = 1 AND is_broadband = 1) THEN 1 ELSE 0 END)",
 483     "SUM(CASE WHEN (voip_lastmile = 1 AND media = 'Copper') THEN 1 ELSE 0 END)",
 484     "SUM(CASE WHEN (voip_lastmile = 1 AND media = 'Cable Modem') THEN 1 ELSE 0 END)",
 485     "SUM(CASE WHEN (voip_lastmile = 1 AND media = 'Fiber') THEN 1 ELSE 0 END)",
 486     "SUM(CASE WHEN (voip_lastmile = 1 AND media = 'Fixed Wireless') THEN 1 ELSE 0 END)",
 487     "SUM(CASE WHEN (voip_lastmile = 1 AND media NOT IN('Copper', 'Fiber', 'Cable Modem', 'Fixed Wireless') ) THEN 1 ELSE 0 END)",
 488   );
 489
 490   my $from =
 491     'cust_pkg
 492       JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
 493       JOIN state USING (country, state)
 494       JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
 495       JOIN part_pkg USING (pkgpart) '.
 496       join_optionnames_int(
 497         qw( is_voip is_broadband is_consumer voip_lastmile)
 498       ).
 499       join_optionnames('media')
 500   ;
 501   my @where = (
 502     active_on($date),
 503     "is_voip = 1",
 504   );
 505   push @where, "cust_main.agentnum = $agentnum" if $agentnum;
 506   my $group_by = 'state.fips';
 507   my $order_by = $group_by;
 508
 509   "SELECT ".join(', ', @select) . "
 510   FROM $from
 511   WHERE ".join(' AND ', @where)."
 512   GROUP BY $group_by
 513   ORDER BY $order_by
 514   ";
 515 }
 516
 517 sub mbs_sql {
 518   my $class = shift;
 519   my %opt = @_;
 520   my $date = $opt{date} || time;
 521   my $agentnum = $opt{agentnum};
 522
 523   my @select = (
 524     'state.fips',
 525     'broadband_downstream',
 526     'broadband_upstream',
 527     'COUNT(*)',
 528     'COUNT(is_consumer)',
 529   );
 530   my $from =
 531     'cust_pkg
 532       JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
 533       JOIN state USING (country, state)
 534       JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
 535       JOIN part_pkg USING (pkgpart) '.
 536       join_optionnames_int(qw(
 537         is_broadband technology
 538         is_consumer
 539         )).
 540       join_optionnames(qw(broadband_downstream broadband_upstream))
 541   ;
 542   my @where = (
 543     active_on($date),
 544     is_mobile_broadband()
 545   );
 546   push @where, "cust_main.agentnum = $agentnum" if $agentnum;
 547   my $group_by = 'state.fips, broadband_downstream, broadband_upstream ';
 548   my $order_by = $group_by;
 549
 550   "SELECT ".join(', ', @select) . "
 551   FROM $from
 552   WHERE ".join(' AND ', @where)."
 553   GROUP BY $group_by
 554   ORDER BY $order_by
 555   ";
 556 }
 557
 558 sub mvs_sql {
 559   my $class = shift;
 560   my %opt = @_;
 561   my $date = $opt{date} || time;
 562   my $agentnum = $opt{agentnum};
 563
 564   my @select = (
 565     'state.fips',
 566     'COUNT(*)',
 567     'COUNT(mobile_direct)',
 568   );
 569   my $from =
 570     'cust_pkg
 571       JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
 572       JOIN state USING (country, state)
 573       JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
 574       JOIN part_pkg USING (pkgpart) '.
 575       join_optionnames_int(qw( is_mobile mobile_direct) )
 576   ;
 577   my @where = (
 578     active_on($date),
 579     'is_mobile = 1'
 580   );
 581   push @where, "cust_main.agentnum = $agentnum" if $agentnum;
 582   my $group_by = 'state.fips';
 583   my $order_by = $group_by;
 584
 585   "SELECT ".join(', ', @select) . "
 586   FROM $from
 587   WHERE ".join(' AND ', @where)."
 588   GROUP BY $group_by
 589   ORDER BY $order_by
 590   ";
 591 }
 592
 593 =item parts
 594
 595 Returns a Tie::IxHash reference of the internal short names used for the
 596 report sections ('fbd', 'mbs', etc.) to the full names.
 597
 598 =cut
 599
 600 tie our %parts, 'Tie::IxHash', (
 601   fbd   => 'Fixed Broadband Deployment',
 602   fbs   => 'Fixed Broadband Subscription',
 603   fvs   => 'Fixed Voice Subscription',
 604   lts   => 'Local Exchange Telephone Subscription',
 605   voip  => 'Interconnected VoIP Subscription',
 606   mbd   => 'Mobile Broadband Deployment',
 607   mbsa  => 'Mobile Broadband Service Availability',
 608   mbs   => 'Mobile Broadband Subscription',
 609   mvd   => 'Mobile Voice Deployment',
 610   mvs   => 'Mobile Voice Subscription',
 611 );
 612
 613 sub parts {
 614   Storable::dclone(\%parts);
 615 }
 616
 617 1;