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 %states = (
  85   '01' => 'ALABAMA (AL)',
  86   '02' => 'ALASKA (AK)',
  87   '04' => 'ARIZONA (AZ)',
  88   '05' => 'ARKANSAS (AR)',
  89   '06' => 'CALIFORNIA (CA)',
  90   '08' => 'COLORADO (CO)',
  91
  92   '09' => 'CONNECTICUT (CT)',
  93   '10' => 'DELAWARE (DE)',
  94   '11' => 'DISTRICT OF COLUMBIA (DC)',
  95   '12' => 'FLORIDA (FL)',
  96   '13' => 'GEORGIA (GA)',
  97   '15' => 'HAWAII (HI)',
  98
  99   '16' => 'IDAHO (ID)',
 100   '17' => 'ILLINOIS (IL)',
 101   '18' => 'INDIANA (IN)',
 102   '19' => 'IOWA (IA)',
 103   '20' => 'KANSAS (KS)',
 104   '21' => 'KENTUCKY (KY)',
 105
 106   '22' => 'LOUISIANA (LA)',
 107   '23' => 'MAINE (ME)',
 108   '24' => 'MARYLAND (MD)',
 109   '25' => 'MASSACHUSETTS (MA)',
 110   '26' => 'MICHIGAN (MI)',
 111   '27' => 'MINNESOTA (MN)',
 112
 113   '28' => 'MISSISSIPPI (MS)',
 114   '29' => 'MISSOURI (MO)',
 115   '30' => 'MONTANA (MT)',
 116   '31' => 'NEBRASKA (NE)',
 117   '32' => 'NEVADA (NV)',
 118   '33' => 'NEW HAMPSHIRE (NH)',
 119
 120   '34' => 'NEW JERSEY (NJ)',
 121   '35' => 'NEW MEXICO (NM)',
 122   '36' => 'NEW YORK (NY)',
 123   '37' => 'NORTH CAROLINA (NC)',
 124   '38' => 'NORTH DAKOTA (ND)',
 125   '39' => 'OHIO (OH)',
 126
 127   '40' => 'OKLAHOMA (OK)',
 128   '41' => 'OREGON (OR)',
 129   '42' => 'PENNSYLVANIA (PA)',
 130   '44' => 'RHODE ISLAND (RI)',
 131   '45' => 'SOUTH CAROLINA (SC)',
 132   '46' => 'SOUTH DAKOTA (SD)',
 133
 134   '47' => 'TENNESSEE (TN)',
 135   '48' => 'TEXAS (TX)',
 136   '49' => 'UTAH (UT)',
 137   '50' => 'VERMONT (VT)',
 138   '51' => 'VIRGINIA (VA)',
 139   '53' => 'WASHINGTON (WA)',
 140
 141   '54' => 'WEST VIRGINIA (WV)',
 142   '55' => 'WISCONSIN (WI)',
 143   '56' => 'WYOMING (WY)',
 144   '72' => 'PUERTO RICO (PR)',
 145 );
 146
 147 sub restore_fcc477map {
 148   my $key = shift;
 149   FS::Record::scalar_sql('',"select formvalue from fcc477map where formkey = ?",$key);
 150 }
 151
 152 sub save_fcc477map {
 153   my $key = shift;
 154   my $value = shift;
 155
 156   local $SIG{HUP} = 'IGNORE';
 157   local $SIG{INT} = 'IGNORE';
 158   local $SIG{QUIT} = 'IGNORE';
 159   local $SIG{TERM} = 'IGNORE';
 160   local $SIG{TSTP} = 'IGNORE';
 161   local $SIG{PIPE} = 'IGNORE';
 162
 163   my $oldAutoCommit = $FS::UID::AutoCommit;
 164   local $FS::UID::AutoCommit = 0;
 165   my $dbh = dbh;
 166
 167   # lame (should be normal FS::Record access)
 168
 169   my $sql = "delete from fcc477map where formkey = ?";
 170   my $sth = dbh->prepare($sql) or die dbh->errstr;
 171   $sth->execute($key) or do {
 172     warn "WARNING: Error removing FCC 477 form defaults: " . $sth->errstr;
 173     $dbh->rollback if $oldAutoCommit;
 174   };
 175
 176   $sql = "insert into fcc477map (formkey,formvalue) values (?,?)";
 177   $sth = dbh->prepare($sql) or die dbh->errstr;
 178   $sth->execute($key,$value) or do {
 179     warn "WARNING: Error setting FCC 477 form defaults: " . $sth->errstr;
 180     $dbh->rollback if $oldAutoCommit;
 181   };
 182
 183   $dbh->commit or die $dbh->errstr if $oldAutoCommit;
 184
 185   '';
 186 }
 187
 188 sub parse_technology_option {
 189   my $cgi = shift;
 190   my $save = shift;
 191   my @result = ();
 192   my $i = 0;
 193   for (my $i = 0; $i < scalar(@technology); $i++) {
 194     my $value = $cgi->param("part1_technology_option_$i"); #lame
 195     save_fcc477map("part1_technology_option_$i",$value)
 196         if $save && $value =~ /^\d+$/;
 197     push @result, $value =~ /^\d+$/ ? $value : 0;
 198   }
 199   return (@result);
 200 }
 201
 202 sub statenum2state {
 203   my $num = shift;
 204   $states{$num};
 205 }
 206
 207 sub join_optionnames {
 208   join(' ', map { join_optionname($_) } @_);
 209 }
 210
 211 sub join_optionname {
 212   # Returns a FROM phrase to join a specific option into the query (via
 213   # part_pkg).  The option value will appear as a field with the same name
 214   # as the option.
 215   my $name = shift;
 216   "LEFT JOIN (SELECT pkgpart, optionvalue AS $name FROM part_pkg_fcc_option".
 217     " WHERE fccoptionname = '$name') AS t_$name".
 218     " ON (part_pkg.pkgpart = t_$name.pkgpart)";
 219 }
 220
 221 sub active_on {
 222   # Returns a condition to limit packages to those that were setup before a
 223   # certain date, and not canceled before that date.
 224   #
 225   # (Strictly speaking this should also exclude suspended packages but
 226   # "suspended as of some past date" is a complicated query.)
 227   my $date = shift;
 228   "cust_pkg.setup <= $date AND ".
 229   "(cust_pkg.cancel IS NULL OR cust_pkg.cancel > $date)";
 230 }
 231
 232 sub is_fixed_broadband {
 233   "is_broadband = '1' AND technology::integer IN(".join(',',
 234     10, 11, 12, 20, 30, 40, 41, 42, 50, 60, 70, 90, 0
 235   ).")";
 236 }
 237
 238 =item part6 OPTIONS
 239
 240 Returns Part 6 of the 2014 FCC 477 data, as an arrayref of arrayrefs.
 241 OPTIONS may contain "date" => a timestamp to run the report as of that
 242 date.
 243
 244 =cut
 245
 246 sub part6 {
 247   my $class = shift;
 248   my %opt = shift;
 249   my $date = $opt{date} || time;
 250
 251   my @select = (
 252     'cust_location.censustract',
 253     'technology',
 254     'broadband_downstream',
 255     'broadband_upstream',
 256     'COUNT(*)',
 257     'COUNT(is_consumer)',
 258   );
 259   my $from =
 260     'cust_pkg
 261       JOIN cust_location USING (locationnum)
 262       JOIN part_pkg USING (pkgpart) '.
 263       join_optionnames(qw(
 264         is_broadband technology
 265         broadband_downstream broadband_upstream
 266         is_consumer
 267         ))
 268   ;
 269   my @where = (
 270     active_on($date),
 271     is_fixed_broadband()
 272   );
 273   my $group_by = 'cust_location.censustract, technology, '.
 274                    'broadband_downstream, broadband_upstream ';
 275   my $order_by = $group_by;
 276
 277   my $statement = "SELECT ".join(', ', @select) . "
 278   FROM $from
 279   WHERE ".join(' AND ', @where)."
 280   GROUP BY $group_by
 281   ORDER BY $order_by
 282   ";
 283
 284   warn $statement if $DEBUG;
 285   dbh->selectall_arrayref($statement);
 286 }
 287
 288 =item part9 OPTIONS
 289
 290 Returns Part 9 of the 2014 FCC 477 data, as above.
 291
 292 =cut
 293
 294 sub part9 {
 295   my $class = shift;
 296   my %opt = shift;
 297   my $date = $opt{date} || time;
 298
 299   my @select = (
 300     "cust_location.state",
 301     "SUM(COALESCE(phone_vges::int,0))",
 302     "SUM(COALESCE(phone_circuits::int,0))",
 303     "SUM(COALESCE(phone_lines::int,0))",
 304     "SUM(CASE WHEN is_broadband = '1' THEN phone_lines::int ELSE 0 END)",
 305     "SUM(CASE WHEN is_consumer = '1' AND is_longdistance IS NULL THEN phone_lines::int ELSE 0 END)",
 306     "SUM(CASE WHEN is_consumer = '1' AND is_longdistance = '1' THEN phone_lines::int ELSE 0 END)",
 307     "SUM(CASE WHEN is_consumer IS NULL AND is_longdistance IS NULL THEN phone_lines::int ELSE 0 END)",
 308     "SUM(CASE WHEN is_consumer IS NULL AND is_longdistance = '1' THEN phone_lines::int ELSE 0 END)",
 309     "SUM(CASE WHEN phone_localloop = 'owned' THEN phone_lines::int ELSE 0 END)",
 310     "SUM(CASE WHEN phone_localloop = 'leased' THEN phone_lines::int ELSE 0 END)",
 311     "SUM(CASE WHEN phone_localloop = 'resale' THEN phone_lines::int ELSE 0 END)",
 312     "SUM(CASE WHEN media = 'Fiber' THEN phone_lines::int ELSE 0 END)",
 313     "SUM(CASE WHEN media = 'Cable Modem' THEN phone_lines::int ELSE 0 END)",
 314     "SUM(CASE WHEN media = 'Fixed Wireless' THEN phone_lines::int ELSE 0 END)",
 315   );
 316   my $from =
 317     'cust_pkg
 318       JOIN cust_location USING (locationnum)
 319       JOIN part_pkg USING (pkgpart) '.
 320       join_optionnames(qw(
 321         is_phone is_broadband media
 322         phone_vges phone_circuits phone_lines
 323         is_consumer is_longdistance phone_localloop
 324         ))
 325   ;
 326   my @where = (
 327     active_on($date),
 328     "is_phone::int = 1",
 329   );
 330   my $group_by = 'cust_location.state';
 331   my $order_by = $group_by;
 332
 333   my $statement = "SELECT ".join(', ', @select) . "
 334   FROM $from
 335   WHERE ".join(' AND ', @where)."
 336   GROUP BY $group_by
 337   ORDER BY $order_by
 338   ";
 339
 340   warn $statement if $DEBUG;
 341   dbh->selectall_arrayref($statement);
 342 }
 343
 344
 345 1;