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 dbaname {
 251   # Returns an sql expression for the DBA name
 252   "COALESCE( deploy_zone.dbaname,
 253      (SELECT value FROM conf WHERE conf.name = 'company_name'
 254                              AND (conf.agentnum = deploy_zone.agentnum
 255                                   OR conf.agentnum IS NULL)
 256                              ORDER BY conf.agentnum IS NOT NULL DESC
 257                              LIMIT 1)
 258      ) AS dbaname"
 259 }
 260
 261 sub active_on {
 262   # Returns a condition to limit packages to those that were setup before a
 263   # certain date, and not canceled before that date.
 264   #
 265   # (Strictly speaking this should also exclude suspended packages but
 266   # "suspended as of some past date" is a complicated query.)
 267   my $date = shift;
 268   "cust_pkg.setup <= $date AND ".
 269   "(cust_pkg.cancel IS NULL OR cust_pkg.cancel > $date)";
 270 }
 271
 272 sub is_fixed_broadband {
 273   "is_broadband::int = 1 AND technology::int IN( 10, 11, 12, 20, 30, 40, 41, 42, 50, 60, 70, 90, 0 )"
 274 }
 275
 276 sub is_mobile_broadband {
 277   "is_broadband::int = 1 AND technology::int IN( 80, 81, 82, 83, 84, 85, 86, 87, 88)"
 278 }
 279
 280 =item report SECTION, OPTIONS
 281
 282 Returns the report section SECTION (see the C<parts> method for section
 283 name strings) as an arrayref of arrayrefs.  OPTIONS may contain "date"
 284 (a timestamp value to run the report as of this date) and "agentnum"
 285 (to limit to a single agent).
 286
 287 =cut
 288
 289 sub report {
 290   my $class = shift;
 291   my $section = shift;
 292   my %opt = @_;
 293
 294   my $method = $section.'_sql';
 295   die "Report section '$section' is not implemented\n"
 296     unless $class->can($method);
 297   my $statement = $class->$method(%opt);
 298
 299   my $sth = dbh->prepare($statement);
 300   $sth->execute or die $sth->errstr;
 301   $sth->fetchall_arrayref;
 302 }
 303
 304 sub fbd_sql {
 305   my $class = shift;
 306   my %opt = @_;
 307   my $date = $opt{date} || time;
 308   warn $date;
 309   my $agentnum = $opt{agentnum};
 310
 311   my @select = (
 312     'censusblock',
 313     dbaname(),
 314     'technology',
 315     'CASE WHEN is_consumer IS NOT NULL THEN 1 ELSE 0 END',
 316     'adv_speed_down',
 317     'adv_speed_up',
 318     'CASE WHEN is_business IS NOT NULL THEN 1 ELSE 0 END',
 319     'cir_speed_down',
 320     'cir_speed_up',
 321   );
 322   my $from =
 323     'deploy_zone_block
 324     JOIN deploy_zone USING (zonenum)
 325     JOIN agent USING (agentnum)';
 326   my @where = (
 327     "zonetype = 'B'",
 328     "active_date  < $date",
 329     "(expire_date > $date OR expire_date IS NULL)",
 330   );
 331   push @where, "agentnum = $agentnum" if $agentnum;
 332
 333   my $order_by = 'censusblock, agentnum, technology, is_consumer, is_business';
 334
 335   "SELECT ".join(', ', @select) . "
 336   FROM $from
 337   WHERE ".join(' AND ', @where)."
 338   ORDER BY $order_by
 339   ";
 340 }
 341
 342 sub fbs_sql {
 343   my $class = shift;
 344   my %opt = @_;
 345   my $date = $opt{date} || time;
 346   my $agentnum = $opt{agentnum};
 347
 348   my @select = (
 349     'cust_location.censustract',
 350     'technology',
 351     'broadband_downstream',
 352     'broadband_upstream',
 353     'COUNT(*)',
 354     'COUNT(is_consumer)',
 355   );
 356   my $from =
 357     'cust_pkg
 358       JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
 359       JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
 360       JOIN part_pkg USING (pkgpart) '.
 361       join_optionnames_int(qw(
 362         is_broadband technology
 363         is_consumer
 364         )).
 365       join_optionnames(qw(broadband_downstream broadband_upstream))
 366   ;
 367   my @where = (
 368     active_on($date),
 369     is_fixed_broadband()
 370   );
 371   push @where, "cust_main.agentnum = $agentnum" if $agentnum;
 372   my $group_by = 'cust_location.censustract, technology, '.
 373                    'broadband_downstream, broadband_upstream ';
 374   my $order_by = $group_by;
 375
 376   "SELECT ".join(', ', @select) . "
 377   FROM $from
 378   WHERE ".join(' AND ', @where)."
 379   GROUP BY $group_by
 380   ORDER BY $order_by
 381   ";
 382
 383 }
 384
 385 sub fvs_sql {
 386   my $class = shift;
 387   my %opt = @_;
 388   my $date = $opt{date} || time;
 389   my $agentnum = $opt{agentnum};
 390
 391   my @select = (
 392     'cust_location.censustract',
 393     # VoIP indicator (0 for non-VoIP, 1 for VoIP)
 394     'COALESCE(is_voip, 0)',
 395     # number of lines/subscriptions
 396     'SUM(CASE WHEN is_voip = 1 THEN 1 ELSE phone_lines END)',
 397     # consumer grade lines/subscriptions
 398     'SUM(CASE WHEN is_consumer = 1 THEN ( CASE WHEN is_voip = 1 THEN voip_sessions ELSE phone_lines END) ELSE 0 END)'
 399   );
 400
 401   my $from = 'cust_pkg
 402     JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
 403     JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
 404     JOIN part_pkg USING (pkgpart) '.
 405     join_optionnames_int(qw(
 406       is_phone is_voip is_consumer phone_lines voip_sessions
 407       ))
 408   ;
 409
 410   my @where = (
 411     active_on($date),
 412     "(is_voip = 1 OR is_phone = 1)",
 413   );
 414   push @where, "cust_main.agentnum = $agentnum" if $agentnum;
 415   my $group_by = 'cust_location.censustract, COALESCE(is_voip, 0)';
 416   my $order_by = $group_by;
 417
 418   "SELECT ".join(', ', @select) . "
 419   FROM $from
 420   WHERE ".join(' AND ', @where)."
 421   GROUP BY $group_by
 422   ORDER BY $order_by
 423   ";
 424
 425 }
 426
 427 sub lts_sql {
 428   my $class = shift;
 429   my %opt = @_;
 430   my $date = $opt{date} || time;
 431   my $agentnum = $opt{agentnum};
 432
 433   my @select = (
 434     "state.fips",
 435     "SUM(phone_vges)",
 436     "SUM(phone_circuits)",
 437     "SUM(phone_lines)",
 438     "SUM(CASE WHEN is_broadband = 1 THEN phone_lines ELSE 0 END)",
 439     "SUM(CASE WHEN is_consumer = 1 AND phone_longdistance IS NULL THEN phone_lines ELSE 0 END)",
 440     "SUM(CASE WHEN is_consumer = 1 AND phone_longdistance = 1 THEN phone_lines ELSE 0 END)",
 441     "SUM(CASE WHEN is_consumer IS NULL AND phone_longdistance IS NULL THEN phone_lines ELSE 0 END)",
 442     "SUM(CASE WHEN is_consumer IS NULL AND phone_longdistance = 1 THEN phone_lines ELSE 0 END)",
 443     "SUM(CASE WHEN phone_localloop = 'owned' THEN phone_lines ELSE 0 END)",
 444     "SUM(CASE WHEN phone_localloop = 'leased' THEN phone_lines ELSE 0 END)",
 445     "SUM(CASE WHEN phone_localloop = 'resale' THEN phone_lines ELSE 0 END)",
 446     "SUM(CASE WHEN media = 'Fiber' THEN phone_lines ELSE 0 END)",
 447     "SUM(CASE WHEN media = 'Cable Modem' THEN phone_lines ELSE 0 END)",
 448     "SUM(CASE WHEN media = 'Fixed Wireless' THEN phone_lines ELSE 0 END)",
 449   );
 450   my $from =
 451     'cust_pkg
 452       JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
 453       JOIN state USING (country, state)
 454       JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
 455       JOIN part_pkg USING (pkgpart) '.
 456       join_optionnames_int(qw(
 457         is_phone is_broadband
 458         phone_vges phone_circuits phone_lines
 459         is_consumer phone_longdistance
 460         )).
 461       join_optionnames('media', 'phone_localloop')
 462   ;
 463   my @where = (
 464     active_on($date),
 465     "is_phone = 1",
 466   );
 467   push @where, "cust_main.agentnum = $agentnum" if $agentnum;
 468   my $group_by = 'state.fips';
 469   my $order_by = $group_by;
 470
 471   "SELECT ".join(', ', @select) . "
 472   FROM $from
 473   WHERE ".join(' AND ', @where)."
 474   GROUP BY $group_by
 475   ORDER BY $order_by
 476   ";
 477 }
 478
 479 sub voip_sql {
 480   my $class = shift;
 481   my %opt = @_;
 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   "SELECT ".join(', ', @select) . "
 521   FROM $from
 522   WHERE ".join(' AND ', @where)."
 523   GROUP BY $group_by
 524   ORDER BY $order_by
 525   ";
 526 }
 527
 528 sub mbs_sql {
 529   my $class = shift;
 530   my %opt = @_;
 531   my $date = $opt{date} || time;
 532   my $agentnum = $opt{agentnum};
 533
 534   my @select = (
 535     'state.fips',
 536     'broadband_downstream',
 537     'broadband_upstream',
 538     'COUNT(*)',
 539     'COUNT(is_consumer)',
 540   );
 541   my $from =
 542     'cust_pkg
 543       JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
 544       JOIN state USING (country, state)
 545       JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
 546       JOIN part_pkg USING (pkgpart) '.
 547       join_optionnames_int(qw(
 548         is_broadband technology
 549         is_consumer
 550         )).
 551       join_optionnames(qw(broadband_downstream broadband_upstream))
 552   ;
 553   my @where = (
 554     active_on($date),
 555     is_mobile_broadband()
 556   );
 557   push @where, "cust_main.agentnum = $agentnum" if $agentnum;
 558   my $group_by = 'state.fips, broadband_downstream, broadband_upstream ';
 559   my $order_by = $group_by;
 560
 561   "SELECT ".join(', ', @select) . "
 562   FROM $from
 563   WHERE ".join(' AND ', @where)."
 564   GROUP BY $group_by
 565   ORDER BY $order_by
 566   ";
 567 }
 568
 569 sub mvs_sql {
 570   my $class = shift;
 571   my %opt = @_;
 572   my $date = $opt{date} || time;
 573   my $agentnum = $opt{agentnum};
 574
 575   my @select = (
 576     'state.fips',
 577     'COUNT(*)',
 578     'COUNT(mobile_direct)',
 579   );
 580   my $from =
 581     'cust_pkg
 582       JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
 583       JOIN state USING (country, state)
 584       JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
 585       JOIN part_pkg USING (pkgpart) '.
 586       join_optionnames_int(qw( is_mobile mobile_direct) )
 587   ;
 588   my @where = (
 589     active_on($date),
 590     'is_mobile = 1'
 591   );
 592   push @where, "cust_main.agentnum = $agentnum" if $agentnum;
 593   my $group_by = 'state.fips';
 594   my $order_by = $group_by;
 595
 596   "SELECT ".join(', ', @select) . "
 597   FROM $from
 598   WHERE ".join(' AND ', @where)."
 599   GROUP BY $group_by
 600   ORDER BY $order_by
 601   ";
 602 }
 603
 604 =item parts
 605
 606 Returns a Tie::IxHash reference of the internal short names used for the
 607 report sections ('fbd', 'mbs', etc.) to the full names.
 608
 609 =cut
 610
 611 tie our %parts, 'Tie::IxHash', (
 612   fbd   => 'Fixed Broadband Deployment',
 613   fbs   => 'Fixed Broadband Subscription',
 614   fvs   => 'Fixed Voice Subscription',
 615   lts   => 'Local Exchange Telephone Subscription',
 616   voip  => 'Interconnected VoIP Subscription',
 617   mbd   => 'Mobile Broadband Deployment',
 618   mbsa  => 'Mobile Broadband Service Availability',
 619   mbs   => 'Mobile Broadband Subscription',
 620   mvd   => 'Mobile Voice Deployment',
 621   mvs   => 'Mobile Voice Subscription',
 622 );
 623
 624 sub parts {
 625   Storable::dclone(\%parts);
 626 }
 627
 628 1;