start of FCC 477 report rewrite, #24047 and #28020

7 files changed, 398 insertions, 5 deletions

diff --git a/FS/FS/Mason.pm b/FS/FS/Mason.pm
index ede7259fb..5476fd83e 100644
--- a/FS/FS/Mason.pm
+++ b/FS/FS/Mason.pm
@@ -381,6 +381,7 @@ if ( -e $addl_handler_use_file ) {
   use FS::sched_avail;
   use FS::export_batch;
   use FS::export_batch_item;
+  use FS::part_pkg_fcc_option;
   # Sammath Naur
 
   if ( $FS::Mason::addl_handler_use ) {
diff --git a/FS/FS/Report/FCC_477.pm b/FS/FS/Report/FCC_477.pm
index fd088148b..9c038420b 100644
--- a/FS/FS/Report/FCC_477.pm
+++ b/FS/FS/Report/FCC_477.pm
@@ -4,9 +4,12 @@ use base qw( FS::Report );
 use strict;
 use vars qw( @upload @download @technology @part2aoption @part2boption
              %states
+             $DEBUG
            );
 use FS::Record qw( dbh );
 
+$DEBUG = 1;
+
 =head1 NAME
 
 FS::Report::FCC_477 - Routines for FCC Form 477 reports
@@ -201,10 +204,142 @@ sub statenum2state {
   $states{$num};
 }
 
-#sub statenum2abbr {
-#  my $num = shift;
-#  $states{$num} =~ /\((\w\w)\)$/ or return '';
-#  $1;
-#}
+sub join_optionnames {
+  join(' ', map { join_optionname($_) } @_);
+}
+
+sub join_optionname {
+  # Returns a FROM phrase to join a specific option into the query (via 
+  # part_pkg).  The option value will appear as a field with the same name
+  # as the option.
+  my $name = shift;
+  "LEFT JOIN (SELECT pkgpart, optionvalue AS $name FROM part_pkg_fcc_option".
+    " WHERE fccoptionname = '$name') AS t_$name".
+    " ON (part_pkg.pkgpart = t_$name.pkgpart)";
+}
+
+sub active_on {
+  # Returns a condition to limit packages to those that were setup before a 
+  # certain date, and not canceled before that date.
+  #
+  # (Strictly speaking this should also exclude suspended packages but 
+  # "suspended as of some past date" is a complicated query.)
+  my $date = shift;
+  "cust_pkg.setup <= $date AND ".
+  "(cust_pkg.cancel IS NULL OR cust_pkg.cancel > $date)";
+}
+
+sub is_fixed_broadband {
+  "is_broadband = '1' AND technology::integer IN(".join(',',
+    10, 11, 12, 20, 30, 40, 41, 42, 50, 60, 70, 90, 0
+  ).")";
+}
+
+=item part6 OPTIONS
+
+Returns Part 6 of the 2014 FCC 477 data, as an arrayref of arrayrefs.
+OPTIONS may contain "date" => a timestamp to run the report as of that
+date.
+
+=cut
+
+sub part6 {
+  my $class = shift;
+  my %opt = shift;
+  my $date = $opt{date} || time;
+
+  my @select = (
+    'cust_location.censustract',
+    'technology',
+    'broadband_downstream',
+    'broadband_upstream',
+    'COUNT(*)',
+    'COUNT(is_consumer)',
+  );
+  my $from =
+    'cust_pkg
+      JOIN cust_location USING (locationnum)
+      JOIN part_pkg USING (pkgpart) '.
+      join_optionnames(qw(
+        is_broadband technology 
+        broadband_downstream broadband_upstream
+        is_consumer
+        ))
+  ;
+  my @where = (
+    active_on($date),
+    is_fixed_broadband()
+  );
+  my $group_by = 'cust_location.censustract, technology, '.
+                   'broadband_downstream, broadband_upstream ';
+  my $order_by = $group_by;
+
+  my $statement = "SELECT ".join(', ', @select) . "
+  FROM $from
+  WHERE ".join(' AND ', @where)."
+  GROUP BY $group_by
+  ORDER BY $order_by
+  ";
+
+  warn $statement if $DEBUG;
+  dbh->selectall_arrayref($statement);
+}
+
+=item part9 OPTIONS
+
+Returns Part 9 of the 2014 FCC 477 data, as above.
+
+=cut
+
+sub part9 {
+  my $class = shift;
+  my %opt = shift;
+  my $date = $opt{date} || time;
+
+  my @select = (
+    "cust_location.state",
+    "SUM(COALESCE(phone_vges::int,0))",
+    "SUM(COALESCE(phone_circuits::int,0))",
+    "SUM(COALESCE(phone_lines::int,0))",
+    "SUM(CASE WHEN is_broadband = '1' THEN phone_lines::int ELSE 0 END)",
+    "SUM(CASE WHEN is_consumer = '1' AND is_longdistance IS NULL THEN phone_lines::int ELSE 0 END)",
+    "SUM(CASE WHEN is_consumer = '1' AND is_longdistance = '1' THEN phone_lines::int ELSE 0 END)",
+    "SUM(CASE WHEN is_consumer IS NULL AND is_longdistance IS NULL THEN phone_lines::int ELSE 0 END)",
+    "SUM(CASE WHEN is_consumer IS NULL AND is_longdistance = '1' THEN phone_lines::int ELSE 0 END)",
+    "SUM(CASE WHEN phone_localloop = 'owned' THEN phone_lines::int ELSE 0 END)",
+    "SUM(CASE WHEN phone_localloop = 'leased' THEN phone_lines::int ELSE 0 END)",
+    "SUM(CASE WHEN phone_localloop = 'resale' THEN phone_lines::int ELSE 0 END)",
+    "SUM(CASE WHEN media = 'Fiber' THEN phone_lines::int ELSE 0 END)",
+    "SUM(CASE WHEN media = 'Cable Modem' THEN phone_lines::int ELSE 0 END)",
+    "SUM(CASE WHEN media = 'Fixed Wireless' THEN phone_lines::int ELSE 0 END)",
+  );
+  my $from =
+    'cust_pkg
+      JOIN cust_location USING (locationnum)
+      JOIN part_pkg USING (pkgpart) '.
+      join_optionnames(qw(
+        is_phone is_broadband media
+        phone_vges phone_circuits phone_lines
+        is_consumer is_longdistance phone_localloop 
+        ))
+  ;
+  my @where = (
+    active_on($date),
+    "is_phone::int = 1",
+  );
+  my $group_by = 'cust_location.state';
+  my $order_by = $group_by;
+
+  my $statement = "SELECT ".join(', ', @select) . "
+  FROM $from
+  WHERE ".join(' AND ', @where)."
+  GROUP BY $group_by
+  ORDER BY $order_by
+  ";
+
+  warn $statement if $DEBUG;
+  dbh->selectall_arrayref($statement);
+}
+
 
 1;
diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm
index b0980e8ef..1b76a23e9 100644
--- a/FS/FS/Schema.pm
+++ b/FS/FS/Schema.pm
@@ -4755,6 +4755,18 @@ sub tables_hashref {
                         ],
     },
 
+    'part_pkg_fcc_option' => {
+      'columns' => [
+        'num',        'serial', '', '', '', '',
+        'fccoptionname', 'varchar', '', $char_d, '', '',
+        'pkgpart',       'int', '', '', '', '',
+        'optionvalue',   'varchar', 'NULL', $char_d, '', '',
+      ],
+      'primary_key' => 'num',
+      'unique'      => [ [ 'fccoptionname', 'pkgpart' ] ],
+      'index'       => [],
+    },
+
     'rate' => {
       'columns' => [
         'ratenum',   'serial',     '',      '', '', '', 
diff --git a/FS/FS/part_pkg.pm b/FS/FS/part_pkg.pm
index ffbeba2d8..2f0646740 100644
--- a/FS/FS/part_pkg.pm
+++ b/FS/FS/part_pkg.pm
@@ -17,6 +17,9 @@ use FS::cust_pkg;
 use FS::agent_type;
 use FS::type_pkgs;
 use FS::part_pkg_option;
+use FS::part_pkg_fcc_option;
+use FS::pkg_class;
+use FS::agent;
 use FS::part_pkg_msgcat;
 use FS::part_pkg_taxrate;
 use FS::part_pkg_taxoverride;
@@ -331,6 +334,11 @@ sub insert {
       }
   }
 
+  if ( $options{fcc_options} ) {
+    warn "  updating fcc options " if $DEBUG;
+    $self->process_fcc_options( $options{fcc_options} );
+  }
+
   warn "  committing transaction" if $DEBUG and $oldAutoCommit;
   $dbh->commit or die $dbh->errstr if $oldAutoCommit;
 
@@ -612,6 +620,11 @@ sub replace {
     }
   }
 
+  if ( $options->{fcc_options} ) {
+    warn "  updating fcc options " if $DEBUG;
+    $new->process_fcc_options( $options->{fcc_options} );
+  }
+
   warn "  committing transaction" if $DEBUG and $oldAutoCommit;
   $dbh->commit or die $dbh->errstr if $oldAutoCommit;
   '';
@@ -772,6 +785,43 @@ sub propagate {
   join("\n", @error);
 }
 
+=item process_fcc_options HASHREF
+
+Sets the FCC options on this package definition to the values specified
+in HASHREF.  Names are as in L<FS::part_pkg_fcc_option/info>.
+
+=cut
+
+sub process_fcc_options {
+  my $self = shift;
+  my $pkgpart = $self->pkgpart;
+  my $options;
+  if (ref $_[0]) {
+    $options = shift;
+  } else {
+    $options = { @_ };
+  }
+
+  my %existing_num = map { $_->fccoptionname => $_->num }
+                     qsearch('part_pkg_fcc_option', { pkgpart => $pkgpart });
+
+  # set up params for process_o2m
+  my $i = 0;
+  my $params = {};
+  foreach my $name (keys %$options ) {
+    $params->{ "num$i" } = $existing_num{$name} || '';
+    $params->{ "num$i".'_fccoptionname' } = $name;
+    $params->{ "num$i".'_optionvalue'   } = $options->{$name};
+    $i++;
+  }
+
+  $self->process_o2m(
+    table   => 'part_pkg_fcc_option',
+    fields  => [qw( fccoptionname optionvalue )],
+    params  => $params,
+  );
+}
+
 =item pkg_locale LOCALE
 
 Returns a customer-viewable string representing this package for the given
@@ -1301,6 +1351,35 @@ sub part_pkg_currency_option {
   $part_pkg_currency->optionvalue;
 }
 
+=item fcc_option OPTIONNAME
+
+Returns the FCC 477 report option value for the given name, or the empty 
+string.
+
+=cut
+
+sub fcc_option {
+  my ($self, $name) = @_;
+  my $part_pkg_fcc_option =
+    qsearchs('part_pkg_fcc_option', {
+        pkgpart => $self->pkgpart,
+        fccoptionname => $name,
+    });
+  $part_pkg_fcc_option ? $part_pkg_fcc_option->optionvalue : '';
+}
+
+=item fcc_options
+
+Returns all FCC 477 report options for this package, as a hash-like list.
+
+=cut
+
+sub fcc_options {
+  my $self = shift;
+  map { $_->fccoptionname => $_->optionvalue }
+    qsearch('part_pkg_fcc_option', { pkgpart => $self->pkgpart });
+}
+
 =item bill_part_pkg_link
 
 Returns the associated part_pkg_link records (see L<FS::part_pkg_link>).
diff --git a/FS/FS/part_pkg_fcc_option.pm b/FS/FS/part_pkg_fcc_option.pm
new file mode 100644
index 000000000..0a288def5
--- /dev/null
+++ b/FS/FS/part_pkg_fcc_option.pm
@@ -0,0 +1,159 @@
+package FS::part_pkg_fcc_option;
+
+use strict;
+use base qw( FS::Record );
+use FS::Record qw( qsearch qsearchs );
+use Storable qw(dclone);
+use Tie::IxHash;
+
+sub table { 'part_pkg_fcc_option'; }
+
+=head1 NAME
+
+FS::part_pkg_fcc_option - Object methods for part_pkg_fcc_option records
+
+=head1 SYNOPSIS
+
+  use FS::part_pkg_fcc_option;
+
+  $record = new FS::part_pkg_fcc_option \%hash;
+  $record = new FS::part_pkg_fcc_option { 'column' => 'value' };
+
+  $error = $record->insert;
+
+  $error = $new_record->replace($old_record);
+
+  $error = $record->delete;
+
+  $error = $record->check;
+
+=head1 DESCRIPTION
+
+An FS::part_pkg_fcc_option object represents an option that classifies a
+package definition on the FCC Form 477 report.  FS::part_pkg_fcc_option 
+inherits from FS::Record.  The following fields are currently supported:
+
+=over 4
+
+=item num
+
+primary key
+
+=item fccoptionname
+
+A string identifying a report option, as an element of a static data
+structure found within this module.  See the C<part> method.
+
+=item pkgpart
+
+L<FS::part_pkg> foreign key.
+
+=item optionvalue
+
+The value of the report option, as an integer.  Boolean options use 1 
+and NULL.  Most other options have some kind of lookup table.
+
+=back
+
+=head1 METHODS
+
+=over 4
+
+=item check
+
+Checks all fields to make sure this is a valid FCC option.  If there is
+an error, returns the error, otherwise returns false.  Called by the insert
+and replace methods.
+
+=cut
+
+sub check {
+  my $self = shift;
+
+  my $error = 
+    $self->ut_numbern('num')
+    || $self->ut_alpha('fccoptionname')
+    || $self->ut_number('pkgpart')
+    || $self->ut_foreign_key('pkgpart', 'part_pkg', 'pkgpart')
+    || $self->ut_textn('optionvalue')
+  ;
+  return $error if $error;
+
+  $self->SUPER::check;
+}
+
+=back
+
+=head1 CLASS METHODS
+
+=over 4
+
+=item media_types
+
+Returns a Tie::IxHash hashref of the media type strings (which are not 
+part of the report definition, per se) to arrayrefs of the technology 
+codes included in each one.
+
+=item technology_labels
+
+Returns a hashref relating each technology code to a label.  Unlike the 
+media type strings, the technology codes are part of the formal report
+definition.
+
+=cut
+
+tie our %media_types, 'Tie::IxHash', (
+  'Copper'          => [ 11, 12, 10, 20, 30 ],
+  'Cable Modem'     => [ 41, 42, 40 ],
+  'Fiber'           => [ 50 ],
+  'Satellite'       => [ 60 ],
+  'Fixed Wireless'  => [ 70 ],
+  'Mobile Wireless' => [ 80, 81, 82, 83, 84, 85, 86, 87, 88 ],
+  'Other'           => [ 90, 0 ],
+);
+
+our %technology_labels = (
+      10 => 'Other ADSL',
+      11 => 'ADSL2',
+      12 => 'VDSL',
+      20 => 'SDSL',
+      30 => 'Other Copper Wireline',
+      40 => 'Other Cable Modem',
+      41 => 'Cable - DOCSIS 1, 1.1, 2.0',
+      42 => 'Cable - DOCSIS 3.0',
+      50 => 'Fiber',
+      60 => 'Satellite',
+      70 => 'Terrestrial Fixed Wireless',
+      # mobile wireless
+      80 => 'Mobile - WCDMA/UMTS/HSPA',
+      81 => 'Mobile - HSPA+',
+      82 => 'Mobile - EVDO/EVDO Rev A',
+      83 => 'Mobile - LTE',
+      84 => 'Mobile - WiMAX',
+      85 => 'Mobile - CDMA',
+      86 => 'Mobile - GSM',
+      87 => 'Mobile - Analog',
+      88 => 'Other Mobile',
+
+      90 => 'Electric Power Line',
+      0  => 'Other'
+);
+
+sub media_types {
+  Storable::dclone(\%media_types);
+}
+
+sub technology_labels {
+  +{ %technology_labels };
+}
+
+=head1 BUGS
+
+=head1 SEE ALSO
+
+L<FS::Record>, schema.html from the base documentation.
+
+=cut
+
+1;
+
diff --git a/FS/MANIFEST b/FS/MANIFEST
index 2116442d2..ed8fd9b24 100644
--- a/FS/MANIFEST
+++ b/FS/MANIFEST
@@ -804,3 +804,5 @@ FS/export_batch.pm
 t/export_batch.t
 FS/export_batch_item.pm
 t/export_batch_item.t
+FS/part_pkg_fcc_option.pm
+t/part_pkg_fcc_option.t
diff --git a/FS/t/part_pkg_fcc_option.t b/FS/t/part_pkg_fcc_option.t
new file mode 100644
index 000000000..8f781c866
--- /dev/null
+++ b/FS/t/part_pkg_fcc_option.t
@@ -0,0 +1,5 @@
+BEGIN { $| = 1; print "1..1\n" }
+END {print "not ok 1\n" unless $loaded;}
+use FS::part_pkg_fcc_option;
+$loaded=1;
+print "ok 1\n";