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 = 1;
  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(".join(',',
 263     10, 11, 12, 20, 30, 40, 41, 42, 50, 60, 70, 90, 0
 264   ).")";
 265 }
 266
 267 =item report SECTION, OPTIONS
 268
 269 Returns the report section SECTION (see the C<parts> method for section
 270 name strings) as an arrayref of arrayrefs.  OPTIONS may contain "date"
 271 (a timestamp value to run the report as of this date) and "agentnum"
 272 (to limit to a single agent).
 273
 274 =cut
 275
 276 sub report {
 277   my $class = shift;
 278   my $section = shift;
 279   my %opt = @_;
 280
 281   my $method = $section.'_sql';
 282   die "Report section '$section' is not implemented\n"
 283     unless $class->can($method);
 284   my $statement = $class->$method(%opt);
 285
 286   my $sth = dbh->prepare($statement);
 287   $sth->execute or die $sth->errstr;
 288   $sth->fetchall_arrayref;
 289 }
 290
 291 sub fbd_sql {
 292   my $class = shift;
 293   my %opt = shift;
 294   my $date = $opt{date} || time;
 295   warn $date;
 296   my $agentnum = $opt{agentnum};
 297
 298   my @select = (
 299     'censusblock',
 300     'COALESCE(dbaname, agent.agent)',
 301     'technology',
 302     'CASE WHEN is_consumer IS NOT NULL THEN 1 ELSE 0 END',
 303     'adv_speed_down',
 304     'adv_speed_up',
 305     'CASE WHEN is_business IS NOT NULL THEN 1 ELSE 0 END',
 306     'cir_speed_down',
 307     'cir_speed_up',
 308   );
 309   my $from =
 310     'deploy_zone_block
 311     JOIN deploy_zone USING (zonenum)
 312     JOIN agent USING (agentnum)';
 313   my @where = (
 314     "zonetype = 'B'",
 315     "active_date  < $date",
 316     "(expire_date > $date OR expire_date IS NULL)",
 317   );
 318   push @where, "agentnum = $agentnum" if $agentnum;
 319
 320   my $order_by = 'censusblock, dbaname, technology, is_consumer, is_business';
 321
 322   "SELECT ".join(', ', @select) . "
 323   FROM $from
 324   WHERE ".join(' AND ', @where)."
 325   ORDER BY $order_by
 326   ";
 327 }
 328
 329 sub fbs_sql {
 330   my $class = shift;
 331   my %opt = shift;
 332   my $date = $opt{date} || time;
 333   my $agentnum = $opt{agentnum};
 334
 335   my @select = (
 336     'cust_location.censustract',
 337     'technology',
 338     'broadband_downstream',
 339     'broadband_upstream',
 340     'COUNT(*)',
 341     'COUNT(is_consumer)',
 342   );
 343   my $from =
 344     'cust_pkg
 345       JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
 346       JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
 347       JOIN part_pkg USING (pkgpart) '.
 348       join_optionnames_int(qw(
 349         is_broadband technology
 350         is_consumer
 351         )).
 352       join_optionnames(qw(broadband_downstream broadband_upstream))
 353   ;
 354   my @where = (
 355     active_on($date),
 356     is_fixed_broadband()
 357   );
 358   push @where, "cust_main.agentnum = $agentnum" if $agentnum;
 359   my $group_by = 'cust_location.censustract, technology, '.
 360                    'broadband_downstream, broadband_upstream ';
 361   my $order_by = $group_by;
 362
 363   "SELECT ".join(', ', @select) . "
 364   FROM $from
 365   WHERE ".join(' AND ', @where)."
 366   GROUP BY $group_by
 367   ORDER BY $order_by
 368   ";
 369
 370 }
 371
 372 sub fvs_sql {
 373   my $class = shift;
 374   my %opt = shift;
 375   my $date = $opt{date} || time;
 376   my $agentnum = $opt{agentnum};
 377
 378   my @select = (
 379     'cust_location.censustract',
 380     # VoIP indicator (0 for non-VoIP, 1 for VoIP)
 381     'COALESCE(is_voip, 0)',
 382     # number of lines/subscriptions
 383     'SUM(CASE WHEN is_voip = 1 THEN 1 ELSE phone_lines END)',
 384     # consumer grade lines/subscriptions
 385     'SUM(CASE WHEN is_consumer = 1 THEN ( CASE WHEN is_voip = 1 THEN voip_sessions ELSE phone_lines END) ELSE 0 END)'
 386   );
 387
 388   my $from = 'cust_pkg
 389     JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
 390     JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
 391     JOIN part_pkg USING (pkgpart) '.
 392     join_optionnames_int(qw(
 393       is_phone is_voip is_consumer phone_lines voip_sessions
 394       ))
 395   ;
 396
 397   my @where = (
 398     active_on($date),
 399     "(is_voip = 1 OR is_phone = 1)",
 400   );
 401   push @where, "cust_main.agentnum = $agentnum" if $agentnum;
 402   my $group_by = 'cust_location.censustract, COALESCE(is_voip, 0)';
 403   my $order_by = $group_by;
 404
 405   "SELECT ".join(', ', @select) . "
 406   FROM $from
 407   WHERE ".join(' AND ', @where)."
 408   GROUP BY $group_by
 409   ORDER BY $order_by
 410   ";
 411
 412 }
 413
 414 sub lts_sql {
 415   my $class = shift;
 416   my %opt = shift;
 417   my $date = $opt{date} || time;
 418   my $agentnum = $opt{agentnum};
 419
 420   my @select = (
 421     "state.fips",
 422     "SUM(phone_vges)",
 423     "SUM(phone_circuits)",
 424     "SUM(phone_lines)",
 425     "SUM(CASE WHEN is_broadband = 1 THEN phone_lines ELSE 0 END)",
 426     "SUM(CASE WHEN is_consumer = 1 AND phone_longdistance IS NULL THEN phone_lines ELSE 0 END)",
 427     "SUM(CASE WHEN is_consumer = 1 AND phone_longdistance = 1 THEN phone_lines ELSE 0 END)",
 428     "SUM(CASE WHEN is_consumer IS NULL AND phone_longdistance IS NULL THEN phone_lines ELSE 0 END)",
 429     "SUM(CASE WHEN is_consumer IS NULL AND phone_longdistance = 1 THEN phone_lines ELSE 0 END)",
 430     "SUM(CASE WHEN phone_localloop = 'owned' THEN phone_lines ELSE 0 END)",
 431     "SUM(CASE WHEN phone_localloop = 'leased' THEN phone_lines ELSE 0 END)",
 432     "SUM(CASE WHEN phone_localloop = 'resale' THEN phone_lines ELSE 0 END)",
 433     "SUM(CASE WHEN media = 'Fiber' THEN phone_lines ELSE 0 END)",
 434     "SUM(CASE WHEN media = 'Cable Modem' THEN phone_lines ELSE 0 END)",
 435     "SUM(CASE WHEN media = 'Fixed Wireless' THEN phone_lines ELSE 0 END)",
 436   );
 437   my $from =
 438     'cust_pkg
 439       JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
 440       JOIN state USING (country, state)
 441       JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
 442       JOIN part_pkg USING (pkgpart) '.
 443       join_optionnames_int(qw(
 444         is_phone is_broadband
 445         phone_vges phone_circuits phone_lines
 446         is_consumer phone_longdistance
 447         )).
 448       join_optionnames('media', 'phone_localloop')
 449   ;
 450   my @where = (
 451     active_on($date),
 452     "is_phone = 1",
 453   );
 454   push @where, "cust_main.agentnum = $agentnum" if $agentnum;
 455   my $group_by = 'state.fips';
 456   my $order_by = $group_by;
 457
 458   "SELECT ".join(', ', @select) . "
 459   FROM $from
 460   WHERE ".join(' AND ', @where)."
 461   GROUP BY $group_by
 462   ORDER BY $order_by
 463   ";
 464 }
 465
 466 sub voip_sql {
 467   my $class = shift;
 468   my %opt = shift;
 469   my $date = $opt{date} || time;
 470   my $agentnum = $opt{agentnum};
 471
 472   my @select = (
 473     "state.fips",
 474     # OTT, OTT + consumer
 475     "SUM(CASE WHEN (voip_lastmile IS NULL) THEN 1 ELSE 0 END)",
 476     "SUM(CASE WHEN (voip_lastmile IS NULL AND is_consumer = 1) THEN 1 ELSE 0 END)",
 477     # non-OTT: total, consumer, broadband bundle, media types
 478     "SUM(CASE WHEN (voip_lastmile = 1) THEN 1 ELSE 0 END)",
 479     "SUM(CASE WHEN (voip_lastmile = 1 AND is_consumer = 1) THEN 1 ELSE 0 END)",
 480     "SUM(CASE WHEN (voip_lastmile = 1 AND is_broadband = 1) THEN 1 ELSE 0 END)",
 481     "SUM(CASE WHEN (voip_lastmile = 1 AND media = 'Copper') THEN 1 ELSE 0 END)",
 482     "SUM(CASE WHEN (voip_lastmile = 1 AND media = 'Cable Modem') THEN 1 ELSE 0 END)",
 483     "SUM(CASE WHEN (voip_lastmile = 1 AND media = 'Fiber') THEN 1 ELSE 0 END)",
 484     "SUM(CASE WHEN (voip_lastmile = 1 AND media = 'Fixed Wireless') THEN 1 ELSE 0 END)",
 485     "SUM(CASE WHEN (voip_lastmile = 1 AND media NOT IN('Copper', 'Fiber', 'Cable Modem', 'Fixed Wireless') ) THEN 1 ELSE 0 END)",
 486   );
 487
 488   my $from =
 489     'cust_pkg
 490       JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
 491       JOIN state USING (country, state)
 492       JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
 493       JOIN part_pkg USING (pkgpart) '.
 494       join_optionnames_int(
 495         qw( is_voip is_broadband is_consumer voip_lastmile)
 496       ).
 497       join_optionnames('media')
 498   ;
 499   my @where = (
 500     active_on($date),
 501     "is_voip = 1",
 502   );
 503   push @where, "cust_main.agentnum = $agentnum" if $agentnum;
 504   my $group_by = 'state.fips';
 505   my $order_by = $group_by;
 506
 507   "SELECT ".join(', ', @select) . "
 508   FROM $from
 509   WHERE ".join(' AND ', @where)."
 510   GROUP BY $group_by
 511   ORDER BY $order_by
 512   ";
 513
 514 }
 515
 516 =item parts
 517
 518 Returns a Tie::IxHash reference of the internal short names used for the
 519 report sections ('fbd', 'mbs', etc.) to the full names.
 520
 521 =cut
 522
 523 tie our %parts, 'Tie::IxHash', (
 524   fbd   => 'Fixed Broadband Deployment',
 525   fbs   => 'Fixed Broadband Subscription',
 526   fvs   => 'Fixed Voice Subscription',
 527   lts   => 'Local Exchange Telephone Subscription',
 528   voip  => 'Interconnected VoIP Subscription',
 529   mbd   => 'Mobile Broadband Deployment',
 530   mbsa  => 'Mobile Broadband Service Availability',
 531   mbs   => 'Mobile Broadband Subscription',
 532   mvd   => 'Mobile Voice Deployment',
 533   mvs   => 'Mobile Voice Subscription',
 534 );
 535
 536 sub parts {
 537   Storable::dclone(\%parts);
 538 }
 539
 540 1;