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            );
   8 use FS::Record qw( dbh );
   9
  10 use Tie::IxHash;
  11 use Storable;
  12
  13 our $DEBUG = 0;
  14
  15 =head1 NAME
  16
  17 FS::Report::FCC_477 - Routines for FCC Form 477 reports
  18
  19 =head1 SYNOPSIS
  20
  21 =head1 BUGS
  22
  23 Documentation.
  24
  25 =head1 SEE ALSO
  26
  27 =cut
  28
  29 @upload = qw(
  30  <200kbps
  31  200-768kbps
  32  768kbps-1.5mbps
  33  1.5-3mpbs
  34  3-6mbps
  35  6-10mbps
  36  10-25mbps
  37  25-100mbps
  38  >100mbps
  39 );
  40
  41 @download = qw(
  42  200-768kbps
  43  768kbps-1.5mbps
  44  1.5-3mbps
  45  3-6mbps
  46  6-10mbps
  47  10-25mbps
  48  25-100mbps
  49  >100mbps
  50 );
  51
  52 @technology = (
  53   'Asymmetric xDSL',
  54   'Symmetric xDSL',
  55   'Other Wireline',
  56   'Cable Modem',
  57   'Optical Carrier',
  58   'Satellite',
  59   'Terrestrial Fixed Wireless',
  60   'Terrestrial Mobile Wireless',
  61   'Electric Power Line',
  62   'Other Technology',
  63 );
  64
  65 @part2aoption = (
  66  'LD carrier',
  67  'owned loops',
  68  'unswitched UNE loops',
  69  'UNE-P',
  70  'UNE-P replacement',
  71  'FTTP',
  72  'coax',
  73  'wireless',
  74 );
  75
  76 @part2boption = (
  77  'nomadic',
  78  'copper',
  79  'FTTP',
  80  'coax',
  81  'wireless',
  82  'other broadband',
  83 );
  84
  85 #from the select at http://www.ffiec.gov/census/default.aspx
  86 #though this is now in the database, also
  87 %states = (
  88   '01' => 'ALABAMA (AL)',
  89   '02' => 'ALASKA (AK)',
  90   '04' => 'ARIZONA (AZ)',
  91   '05' => 'ARKANSAS (AR)',
  92   '06' => 'CALIFORNIA (CA)',
  93   '08' => 'COLORADO (CO)',
  94
  95   '09' => 'CONNECTICUT (CT)',
  96   '10' => 'DELAWARE (DE)',
  97   '11' => 'DISTRICT OF COLUMBIA (DC)',
  98   '12' => 'FLORIDA (FL)',
  99   '13' => 'GEORGIA (GA)',
 100   '15' => 'HAWAII (HI)',
 101
 102   '16' => 'IDAHO (ID)',
 103   '17' => 'ILLINOIS (IL)',
 104   '18' => 'INDIANA (IN)',
 105   '19' => 'IOWA (IA)',
 106   '20' => 'KANSAS (KS)',
 107   '21' => 'KENTUCKY (KY)',
 108
 109   '22' => 'LOUISIANA (LA)',
 110   '23' => 'MAINE (ME)',
 111   '24' => 'MARYLAND (MD)',
 112   '25' => 'MASSACHUSETTS (MA)',
 113   '26' => 'MICHIGAN (MI)',
 114   '27' => 'MINNESOTA (MN)',
 115
 116   '28' => 'MISSISSIPPI (MS)',
 117   '29' => 'MISSOURI (MO)',
 118   '30' => 'MONTANA (MT)',
 119   '31' => 'NEBRASKA (NE)',
 120   '32' => 'NEVADA (NV)',
 121   '33' => 'NEW HAMPSHIRE (NH)',
 122
 123   '34' => 'NEW JERSEY (NJ)',
 124   '35' => 'NEW MEXICO (NM)',
 125   '36' => 'NEW YORK (NY)',
 126   '37' => 'NORTH CAROLINA (NC)',
 127   '38' => 'NORTH DAKOTA (ND)',
 128   '39' => 'OHIO (OH)',
 129
 130   '40' => 'OKLAHOMA (OK)',
 131   '41' => 'OREGON (OR)',
 132   '42' => 'PENNSYLVANIA (PA)',
 133   '44' => 'RHODE ISLAND (RI)',
 134   '45' => 'SOUTH CAROLINA (SC)',
 135   '46' => 'SOUTH DAKOTA (SD)',
 136
 137   '47' => 'TENNESSEE (TN)',
 138   '48' => 'TEXAS (TX)',
 139   '49' => 'UTAH (UT)',
 140   '50' => 'VERMONT (VT)',
 141   '51' => 'VIRGINIA (VA)',
 142   '53' => 'WASHINGTON (WA)',
 143
 144   '54' => 'WEST VIRGINIA (WV)',
 145   '55' => 'WISCONSIN (WI)',
 146   '56' => 'WYOMING (WY)',
 147   '72' => 'PUERTO RICO (PR)',
 148 );
 149
 150 sub restore_fcc477map {
 151   my $key = shift;
 152   FS::Record::scalar_sql('',"select formvalue from fcc477map where formkey = ?",$key);
 153 }
 154
 155 sub save_fcc477map {
 156   my $key = shift;
 157   my $value = shift;
 158
 159   local $SIG{HUP} = 'IGNORE';
 160   local $SIG{INT} = 'IGNORE';
 161   local $SIG{QUIT} = 'IGNORE';
 162   local $SIG{TERM} = 'IGNORE';
 163   local $SIG{TSTP} = 'IGNORE';
 164   local $SIG{PIPE} = 'IGNORE';
 165
 166   my $oldAutoCommit = $FS::UID::AutoCommit;
 167   local $FS::UID::AutoCommit = 0;
 168   my $dbh = dbh;
 169
 170   my $sql = "delete from fcc477map where formkey = ?";
 171   my $sth = dbh->prepare($sql) or die dbh->errstr;
 172   $sth->execute($key) or do {
 173     warn "WARNING: Error removing FCC 477 form defaults: " . $sth->errstr;
 174     $dbh->rollback if $oldAutoCommit;
 175   };
 176
 177   $sql = "insert into fcc477map (formkey,formvalue) values (?,?)";
 178   $sth = dbh->prepare($sql) or die dbh->errstr;
 179   $sth->execute($key,$value) or do {
 180     warn "WARNING: Error setting FCC 477 form defaults: " . $sth->errstr;
 181     $dbh->rollback if $oldAutoCommit;
 182   };
 183
 184   $dbh->commit or die $dbh->errstr if $oldAutoCommit;
 185
 186   '';
 187 }
 188
 189 sub parse_technology_option {
 190   my $cgi = shift;
 191   my $save = shift;
 192   my @result = ();
 193   my $i = 0;
 194   for (my $i = 0; $i < scalar(@technology); $i++) {
 195     my $value = $cgi->param("part1_technology_option_$i"); #lame
 196     save_fcc477map("part1_technology_option_$i",$value)
 197         if $save && $value =~ /^\d+$/;
 198     push @result, $value =~ /^\d+$/ ? $value : 0;
 199   }
 200   return (@result);
 201 }
 202
 203 sub statenum2state {
 204   my $num = shift;
 205   $states{$num};
 206 }
 207 ### everything above this point is unmaintained ###
 208
 209
 210 =head1 THE "NEW" REPORT (October 2014 and later)
 211
 212 =head2 METHODS
 213
 214 =over 4
 215
 216 =cut
 217
 218 # functions for internal use
 219
 220 sub join_optionnames {
 221   join(' ', map { join_optionname($_) } @_);
 222 }
 223
 224 sub join_optionnames_int {
 225   join(' ', map { join_optionname_int($_) } @_);
 226 }
 227
 228 sub join_optionname {
 229   # Returns a FROM phrase to join a specific option into the query (via
 230   # part_pkg).  The option value will appear as a field with the same name
 231   # as the option.
 232   my $name = shift;
 233   "LEFT JOIN (SELECT pkgpart, optionvalue AS $name FROM part_pkg_fcc_option".
 234     " WHERE fccoptionname = '$name') AS t_$name".
 235     " ON (part_pkg.pkgpart = t_$name.pkgpart)";
 236 }
 237
 238 sub join_optionname_int {
 239   # Returns a FROM phrase to join a specific option into the query (via
 240   # part_pkg) and cast it to integer..  Note this does not convert nulls
 241   # to zero.
 242   my $name = shift;
 243   "LEFT JOIN (SELECT pkgpart, CAST(optionvalue AS int) AS $name
 244    FROM part_pkg_fcc_option".
 245     " WHERE fccoptionname = '$name') AS t_$name".
 246     " ON (part_pkg.pkgpart = t_$name.pkgpart)";
 247 }
 248
 249 sub dbaname {
 250   # Returns an sql expression for the DBA name
 251   "COALESCE( deploy_zone.dbaname,
 252      (SELECT value FROM conf WHERE conf.name = 'company_name'
 253                              AND (conf.agentnum = deploy_zone.agentnum
 254                                   OR conf.agentnum IS NULL)
 255                              ORDER BY conf.agentnum IS NOT NULL DESC
 256                              LIMIT 1)
 257      ) AS dbaname"
 258 }
 259
 260 sub active_on {
 261   # Returns a condition to limit packages to those that were setup before a
 262   # certain date, and not canceled before that date.
 263   #
 264   # (Strictly speaking this should also exclude suspended packages but
 265   # "suspended as of some past date" is a complicated query.)
 266   my $date = shift;
 267   "cust_pkg.setup <= $date AND ".
 268   "(cust_pkg.cancel IS NULL OR cust_pkg.cancel > $date) AND ".
 269   "(cust_pkg.change_date IS NULL OR cust_pkg.change_date <= $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
 281 =item report SECTION, OPTIONS
 282
 283 Returns the report section SECTION (see the C<parts> method for section
 284 name strings).  OPTIONS may contain the following:
 285
 286 - date: a timestamp value. Packages that were active on that date will be
 287 counted.
 288
 289 - agentnum: limit to packages with this agent.
 290
 291 - ignore_quantity: if true, package quantities will be ignored (only distinct
 292 packages will be counted).
 293
 294 The result will be a hashref containing three parallel arrayrefs:
 295 - "data", the columns required by the FCC.
 296 - "detail", a list of the package numbers included in each row's aggregation
 297 - "error", a hashref containing any error status strings in that row. Keys
 298 are error identifiers, values are the messages to show the user.
 299 as well as an informational item:
 300 - "num_errors", the number of rows that contain errors
 301
 302 =item report_data SECTION, OPTIONS
 303
 304 Returns only the data, not the detail or error columns.  This is the part that
 305 will be submitted to the FCC.
 306
 307 =cut
 308
 309 sub report {
 310   my $class = shift;
 311   my $section = shift;
 312   my %opt = @_;
 313   $opt{detail} = 1;
 314
 315   # add the error column
 316   my $data = $class->report_data($section, %opt);
 317   my $error = [];
 318   my $detail = [];
 319   my $check_method = $section.'_check';
 320   my $num_errors = 0;
 321   foreach my $row (@$data) {
 322     if ( $class->can($check_method) ) { # they don't all have these
 323       my $eh = $class->$check_method( $row, 'date'=>$opt{'date'} );
 324       $num_errors++ if keys(%$eh);
 325       push @$error, $eh
 326     }
 327     push @$detail, pop @$row; # this comes from the query
 328   }
 329
 330   return +{
 331     data => $data,
 332     error => $error,
 333     detail => $detail,
 334     num_errors => $num_errors,
 335   };
 336 }
 337
 338 sub report_data {
 339   my $class = shift;
 340   my $section = shift;
 341   my %opt = @_;
 342
 343   my $method = $section.'_sql';
 344   die "Report section '$section' is not implemented\n"
 345     unless $class->can($method);
 346   my $statement = $class->$method(%opt);
 347
 348   warn $statement if $DEBUG;
 349   my $sth = dbh->prepare($statement);
 350   $sth->execute or die $sth->errstr;
 351   return $sth->fetchall_arrayref;
 352 }
 353
 354 sub fbd_sql {
 355   my $class = shift;
 356   my %opt = @_;
 357   my $date = $opt{date} || time;
 358   my $agentnum = $opt{agentnum};
 359
 360   my @select = (
 361     'censusblock',
 362     dbaname(),
 363     'technology',
 364     'CASE WHEN is_consumer IS NOT NULL THEN 1 ELSE 0 END',
 365     'adv_speed_down',
 366     'adv_speed_up',
 367     'CASE WHEN is_business IS NOT NULL THEN 1 ELSE 0 END',
 368   );
 369   push @select, 'cir_speed_down', 'cir_speed_up'
 370     if $opt{date} < 1569826800; #9/30/2019, halfway between the two filing
 371                                 # "as of" dates when it changed
 372   push @select, 'blocknum'
 373     if $opt{detail};
 374
 375   my $from = 'deploy_zone_block
 376     JOIN deploy_zone USING (zonenum)
 377     JOIN agent USING (agentnum)';
 378   my @where = (
 379     "zonetype = 'B'",
 380     "active_date  < $date",
 381     "(expire_date > $date OR expire_date IS NULL)",
 382   );
 383   push @where, "agentnum = $agentnum" if $agentnum;
 384
 385   my $order_by = 'censusblock, agentnum, technology, is_consumer, is_business';
 386
 387   "SELECT DISTINCT ".join(', ', @select) . "
 388   FROM $from
 389   WHERE ".join(' AND ', @where)."
 390   ORDER BY $order_by
 391   ";
 392 }
 393
 394 sub fbs_sql {
 395   my $class = shift;
 396   my %opt = @_;
 397   my $date = $opt{date} || time;
 398   my $agentnum = $opt{agentnum};
 399   my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
 400
 401   my $censustract = "replace(cust_location.censustract, '.', '')";
 402
 403   my @select = (
 404     "$censustract AS censustract",
 405     '(technology - technology % 10) AS media_type',
 406       # media types are multiples of 10
 407     'broadband_downstream',
 408     'broadband_upstream',
 409     "SUM($q)",
 410     "SUM(COALESCE(is_consumer,0) * $q)",
 411   );
 412   push @select, "array_to_string(array_agg(pkgnum), ',')" if $opt{detail};
 413
 414   my $from =
 415     'cust_pkg
 416       JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
 417       JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
 418       JOIN part_pkg USING (pkgpart) '.
 419       join_optionnames_int(qw(
 420         is_broadband technology
 421         is_consumer
 422         )).
 423       join_optionnames(qw(broadband_downstream broadband_upstream))
 424   ;
 425   my @where = (
 426     active_on($date),
 427     is_fixed_broadband()
 428   );
 429   push @where, "cust_main.agentnum = $agentnum" if $agentnum;
 430   my $group_by = "$censustract, technology, broadband_downstream, broadband_upstream ";
 431   my $order_by = $group_by;
 432
 433   "SELECT ".join(', ', @select) . "
 434   FROM $from
 435   WHERE ".join(' AND ', @where)."
 436   GROUP BY $group_by
 437   ORDER BY $order_by
 438   ";
 439
 440 }
 441
 442 sub fbs_check {
 443   my $class = shift;
 444   my $row = shift;
 445
 446   my %opt = @_;
 447   my $date = $opt{date} || time;
 448   my $census_digits = ($date < 1601449200) ? 11 : 15; # 9/30/2020, halfway
 449                                                       # between the two filing
 450                                                       # dates when it changed
 451
 452   my %e;
 453   #censustract
 454   if ( length($row->[0]) == 0 ) {
 455     $e{'censustract_null'} = 'The package location has no census tract.';
 456   } elsif ($row->[0] !~ /^\d{$census_digits}$/) {
 457     $e{'censustract_bad'} = "The census tract must be exactly $census_digits digits.";
 458   }
 459
 460   #technology
 461   if ( length($row->[1]) == 0 ) {
 462     $e{'technology_null'} = 'The package has no technology type.';
 463   }
 464
 465   #speeds
 466   if ( length($row->[2]) == 0 or length($row->[3]) == 0 ) {
 467     $e{'speed_null'} = 'The package is missing downstream or upstream speeds.';
 468   } elsif ( $row->[2] !~ /^\d*(\.\d+)?$/ or $row->[3] !~ /^\d*(\.\d+)?$/ ) {
 469     $e{'speed_bad'} = 'The downstream and upstream speeds must be decimal numbers in Mbps.';
 470   } elsif ( $row->[2] == 0 or $row->[3] == 0 ) {
 471     $e{'speed_zero'} = 'The downstream and upstream speeds cannot be zero.';
 472   }
 473
 474   return \%e;
 475 }
 476
 477 sub fvs_sql {
 478   my $class = shift;
 479   my %opt = @_;
 480   my $date = $opt{date} || time;
 481   my $agentnum = $opt{agentnum};
 482   my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
 483   my $censustract = "replace(cust_location.censustract, '.', '')";
 484
 485   my @select = (
 486     "$censustract AS censustract",
 487     # VoIP indicator (0 for non-VoIP, 1 for VoIP)
 488     'COALESCE(is_voip, 0)',
 489     # number of lines/subscriptions
 490     "SUM($q * (CASE WHEN is_voip = 1 THEN 1 ELSE phone_lines END))",
 491     # consumer grade lines/subscriptions
 492     "SUM($q * COALESCE(is_consumer,0) * (CASE WHEN is_voip = 1 THEN voip_sessions ELSE phone_lines END))",
 493   );
 494   push @select, "array_to_string(array_agg(pkgnum), ',')" if $opt{detail};
 495
 496   my $from = 'cust_pkg
 497     JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
 498     JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
 499     JOIN part_pkg USING (pkgpart) '.
 500     join_optionnames_int(qw(
 501       is_phone is_voip is_consumer phone_lines voip_sessions
 502       ))
 503   ;
 504
 505   my @where = (
 506     active_on($date),
 507     "(is_voip = 1 OR is_phone = 1)",
 508   );
 509   push @where, "cust_main.agentnum = $agentnum" if $agentnum;
 510   my $group_by = "$censustract, COALESCE(is_voip, 0)";
 511   my $order_by = $group_by;
 512
 513   "SELECT ".join(', ', @select) . "
 514   FROM $from
 515   WHERE ".join(' AND ', @where)."
 516   GROUP BY $group_by
 517   ORDER BY $order_by
 518   ";
 519
 520 }
 521
 522 sub fvs_check {
 523   my $class = shift;
 524   my $row = shift;
 525   my %opt = @_;
 526   my $date = $opt{date} || time;
 527   my $census_digits = ($date < 1601449200) ? 11 : 15; # 9/30/2020, halfway
 528                                                       # between the two filing
 529                                                       # dates when it changed
 530
 531   my %e;
 532   #censustract
 533   if ( length($row->[0]) == 0 ) {
 534     $e{'censustract_null'} = 'The package location has no census tract.';
 535   } elsif ($row->[0] !~ /^\d{$census_digits}$/) {
 536     $e{'censustract_bad'} = "The census tract must be exactly $census_digits digits.";
 537   }
 538   return \%e;
 539 }
 540
 541 sub lts_sql {
 542   my $class = shift;
 543   my %opt = @_;
 544   my $date = $opt{date} || time;
 545   my $agentnum = $opt{agentnum};
 546   my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
 547
 548   my @select = (
 549     "state.fips",
 550     "SUM($q * phone_vges)",
 551     "SUM($q * phone_circuits)",
 552     "SUM($q * phone_lines)",
 553     "SUM($q * (CASE WHEN is_broadband = 1 THEN phone_lines ELSE 0 END))",
 554     "SUM($q * (CASE WHEN is_consumer = 1 AND phone_longdistance IS NULL THEN phone_lines ELSE 0 END))",
 555     "SUM($q * (CASE WHEN is_consumer = 1 AND phone_longdistance = 1 THEN phone_lines ELSE 0 END))",
 556     "SUM($q * (CASE WHEN is_consumer IS NULL AND phone_longdistance IS NULL THEN phone_lines ELSE 0 END))",
 557     "SUM($q * (CASE WHEN is_consumer IS NULL AND phone_longdistance = 1 THEN phone_lines ELSE 0 END))",
 558     "SUM($q * (CASE WHEN phone_localloop = 'owned' THEN phone_lines ELSE 0 END))",
 559     "SUM($q * (CASE WHEN phone_localloop = 'leased' THEN phone_lines ELSE 0 END))",
 560     "SUM($q * (CASE WHEN phone_localloop = 'resale' THEN phone_lines ELSE 0 END))",
 561     "SUM($q * (CASE WHEN media = 'Fiber' THEN phone_lines ELSE 0 END))",
 562     "SUM($q * (CASE WHEN media = 'Cable Modem' THEN phone_lines ELSE 0 END))",
 563     "SUM($q * (CASE WHEN media = 'Fixed Wireless' THEN phone_lines ELSE 0 END))",
 564   );
 565   push @select, "array_to_string(array_agg(pkgnum),',')" if $opt{detail};
 566
 567   my $from =
 568     'cust_pkg
 569       JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
 570       JOIN state USING (country, state)
 571       JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
 572       JOIN part_pkg USING (pkgpart) '.
 573       join_optionnames_int(qw(
 574         is_phone is_broadband
 575         phone_vges phone_circuits phone_lines
 576         is_consumer phone_longdistance
 577         )).
 578       join_optionnames('media', 'phone_localloop')
 579   ;
 580   my @where = (
 581     active_on($date),
 582     "is_phone = 1",
 583   );
 584   push @where, "cust_main.agentnum = $agentnum" if $agentnum;
 585   my $group_by = 'state.fips';
 586   my $order_by = $group_by;
 587
 588   "SELECT ".join(', ', @select) . "
 589   FROM $from
 590   WHERE ".join(' AND ', @where)."
 591   GROUP BY $group_by
 592   ORDER BY $order_by
 593   ";
 594 }
 595
 596 # voip_sql has a special case: the fifth column, "Voice with Internet",
 597 # must test whether there are _any_ broadband packages at the same location,
 598 # not just whether this package is both VoIP and broadband.
 599
 600 sub voip_sql {
 601   my $class = shift;
 602   my %opt = @_;
 603   my $date = $opt{date} || time;
 604   my $agentnum = $opt{agentnum};
 605   my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
 606
 607   # subquery to test whether there's an is_broadband package at this location
 608   my $broadband_pkg =
 609     "SELECT 1 FROM cust_pkg AS broadband_pkg
 610     WHERE broadband_pkg.locationnum = cust_pkg.locationnum
 611     AND EXISTS(SELECT 1 FROM part_pkg_fcc_option
 612       WHERE fccoptionname = 'is_broadband'
 613       AND part_pkg_fcc_option.pkgpart = broadband_pkg.pkgpart
 614       AND optionvalue = '1')
 615     AND ".  active_on( $date );
 616
 617   my $has_broadband = "EXISTS($broadband_pkg)";
 618
 619   my @select = (
 620     "state.fips",
 621     # OTT, OTT + consumer
 622     "SUM($q * (CASE WHEN (voip_lastmile IS NULL) THEN 1 ELSE 0 END))",
 623     "SUM($q * (CASE WHEN (voip_lastmile IS NULL AND is_consumer = 1) THEN 1 ELSE 0 END))",
 624     # non-OTT: total, consumer, broadband bundle, media types
 625     "SUM($q * (CASE WHEN (voip_lastmile = 1) THEN 1 ELSE 0 END))",
 626     "SUM($q * (CASE WHEN (voip_lastmile = 1 AND is_consumer = 1) THEN 1 ELSE 0 END))",
 627     "SUM($q * (CASE WHEN (voip_lastmile = 1 AND $has_broadband) THEN 1 ELSE 0 END))",
 628     "SUM($q * (CASE WHEN (voip_lastmile = 1 AND media = 'Copper') THEN 1 ELSE 0 END))",
 629     "SUM($q * (CASE WHEN (voip_lastmile = 1 AND media = 'Fiber') THEN 1 ELSE 0 END))",
 630     "SUM($q * (CASE WHEN (voip_lastmile = 1 AND media = 'Cable Modem') THEN 1 ELSE 0 END))",
 631     "SUM($q * (CASE WHEN (voip_lastmile = 1 AND media = 'Fixed Wireless') THEN 1 ELSE 0 END))",
 632     "SUM($q * (CASE WHEN (voip_lastmile = 1 AND media NOT IN('Copper', 'Fiber', 'Cable Modem', 'Fixed Wireless') ) THEN 1 ELSE 0 END))",
 633   );
 634   push @select, "array_to_string(array_agg(pkgnum),',')" if $opt{detail};
 635
 636   my $from =
 637     'cust_pkg
 638       JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
 639       JOIN state USING (country, state)
 640       JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
 641       JOIN part_pkg USING (pkgpart) '.
 642       join_optionnames_int(
 643         qw( is_voip is_consumer voip_lastmile)
 644       ).
 645       join_optionnames('media')
 646   ;
 647   my @where = (
 648     active_on($date),
 649     "is_voip = 1",
 650   );
 651   push @where, "cust_main.agentnum = $agentnum" if $agentnum;
 652   my $group_by = 'state.fips';
 653   my $order_by = $group_by;
 654
 655   "SELECT ".join(', ', @select) . "
 656   FROM $from
 657   WHERE ".join(' AND ', @where)."
 658   GROUP BY $group_by
 659   ORDER BY $order_by
 660   ";
 661 }
 662
 663 sub mbs_sql {
 664   my $class = shift;
 665   my %opt = @_;
 666   my $date = $opt{date} || time;
 667   my $agentnum = $opt{agentnum};
 668   my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
 669
 670   my @select = (
 671     'state.fips',
 672     'broadband_downstream',
 673     'broadband_upstream',
 674     "SUM($q)",
 675     "SUM(COALESCE(is_consumer, 0) * $q)",
 676   );
 677   push @select, "array_to_string(array_agg(pkgnum),',')" if $opt{detail};
 678
 679   my $from =
 680     'cust_pkg
 681       JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
 682       JOIN state USING (country, state)
 683       JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
 684       JOIN part_pkg USING (pkgpart) '.
 685       join_optionnames_int(qw(
 686         is_broadband technology
 687         is_consumer
 688         )).
 689       join_optionnames(qw(broadband_downstream broadband_upstream))
 690   ;
 691   my @where = (
 692     active_on($date),
 693     is_mobile_broadband()
 694   );
 695   push @where, "cust_main.agentnum = $agentnum" if $agentnum;
 696   my $group_by = 'state.fips, broadband_downstream, broadband_upstream ';
 697   my $order_by = $group_by;
 698
 699   "SELECT ".join(', ', @select) . "
 700   FROM $from
 701   WHERE ".join(' AND ', @where)."
 702   GROUP BY $group_by
 703   ORDER BY $order_by
 704   ";
 705 }
 706
 707 sub mvs_sql {
 708   my $class = shift;
 709   my %opt = @_;
 710   my $date = $opt{date} || time;
 711   my $agentnum = $opt{agentnum};
 712   my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
 713
 714   my @select = (
 715     'state.fips',
 716     "SUM($q)",
 717     "SUM($q * COALESCE(mobile_direct,0))",
 718   );
 719   push @select, "array_to_string(array_agg(pkgnum),',')" if $opt{detail};
 720
 721   my $from =
 722     'cust_pkg
 723       JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
 724       JOIN state USING (country, state)
 725       JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
 726       JOIN part_pkg USING (pkgpart) '.
 727       join_optionnames_int(qw( is_mobile mobile_direct) )
 728   ;
 729   my @where = (
 730     active_on($date),
 731     'is_mobile = 1'
 732   );
 733   push @where, "cust_main.agentnum = $agentnum" if $agentnum;
 734   my $group_by = 'state.fips';
 735   my $order_by = $group_by;
 736
 737   "SELECT ".join(', ', @select) . "
 738   FROM $from
 739   WHERE ".join(' AND ', @where)."
 740   GROUP BY $group_by
 741   ORDER BY $order_by
 742   ";
 743 }
 744
 745 =item parts
 746
 747 Returns a Tie::IxHash reference of the internal short names used for the
 748 report sections ('fbd', 'mbs', etc.) to the full names.
 749
 750 =cut
 751
 752 tie our %parts, 'Tie::IxHash', (
 753   fbd   => 'Fixed Broadband Deployment',
 754   fbs   => 'Fixed Broadband Subscription',
 755   fvs   => 'Fixed Voice Subscription',
 756   lts   => 'Local Exchange Telephone Subscription',
 757   voip  => 'Interconnected VoIP Subscription',
 758   mbd   => 'Mobile Broadband Deployment',
 759   mbsa  => 'Mobile Broadband Service Availability',
 760   mbs   => 'Mobile Broadband Subscription',
 761   mvd   => 'Mobile Voice Deployment',
 762   mvs   => 'Mobile Voice Subscription',
 763 );
 764
 765 sub parts {
 766   Storable::dclone(\%parts);
 767 }
 768
 769 =item part_table SECTION
 770
 771 Returns the name of the primary table that's aggregated in the report section
 772 SECTION. The last column of the report returned by the L</report> method is
 773 a comma-separated list of record numbers, in this table, that are included in
 774 the report line item.
 775
 776 =cut
 777
 778 sub part_table {
 779   my ($class, $part) = @_;
 780   if ($part eq 'fbd') {
 781     return 'deploy_zone_block';
 782   } else {
 783     return 'cust_pkg';
 784   } # add other cases as we add more of the deployment/availability reports
 785 }
 786
 787 1;