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

13 files changed, 779 insertions, 19 deletions

diff --git a/FS/FS/Mason.pm b/FS/FS/Mason.pm
index ede7259..5476fd8 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 fd08814..9c03842 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 b0980e8..1b76a23 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 ffbeba2..2f06467 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 0000000..0a288de
--- /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 2116442..ed8fd9b 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 0000000..8f781c8
--- /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";
diff --git a/bin/convert-477-options b/bin/convert-477-options
new file mode 100755
index 0000000..a52c56c
--- /dev/null
+++ b/bin/convert-477-options
@@ -0,0 +1,125 @@
+#!/usr/bin/perl
+
+my $user = shift;
+use FS::UID 'adminsuidsetup';
+use FS::Record qw(qsearch qsearchs dbh);
+use FS::part_pkg_report_option;
+use Text::CSV;
+
+adminsuidsetup($user) or die "invalid user '$user'";
+$FS::UID::AutoCommit = 1;
+$FS::Record::nowarn_classload = 1;
+
+print "This script will convert your per-package FCC 477 report options
+from the classic style (part IA, IB, IIA...) to the 2014 style.
+This is an approximate conversion, and you should review the 
+resulting package settings for accuracy.  In particular:
+  - Broadband speeds will be set to the lowest speed in their 
+    tier.
+  - Broadband technologies for ADSL and cable modem will be set
+    to 'other ADSL' and 'other cable modem'.  You should set 
+    them to the specific ADSL or cable encapsulation in use.
+  - The 'consumer grade' vs. 'business grade' classification 
+    was introduced in 2014 and will not be set.
+
+Configuring packages...
+";
+
+my @min_download_speed = ( 0.2, 0.768, 1.5, 3, 6, 10, 25, 100 );
+my @min_upload_speed = ( 0.1, @min_download_speed );
+my @media_type = ( 'Copper', 'Copper', 'Copper', 'Cable Modem',
+                   'Fiber', 'Satellite', 'Fixed Wireless', 'Mobile Wireless',
+                   'Other', 'Other' );
+my @technology = ( 10, 20, 30, 40,
+                   50, 60, 70, 80,
+                   90, 0 );
+
+my @phone_option = (
+  'phone_longdistance:1',   # LD carrier
+  'phone_localloop:owned',  # owned loops
+  'phone_localloop:leased', # unswitched UNE loops
+  'phone_localloop:resale', # UNE-P (is pretty much extinct...)
+  'phone_localloop:resale', # UNE-P replacement
+  'media:Fiber',
+  'media:Cable Modem',
+  'media:Fixed Wireless',
+);
+
+my @voip_option = (
+  '', #nomadic; no longer reported
+  'media:Copper',
+  'media:Fiber',
+  'media:Cable Modem',
+  'media:Fixed Wireless',
+  'media:Other'
+);
+
+my %num_to_formkey = map { $_->formvalue => $_->formkey }
+                      qsearch('fcc477map', {});
+
+sub report_option_to_fcc_option {
+  my $report_option_num = shift;
+  my $formkey = $num_to_formkey{$report_option_num}
+    or return;
+  if      ($formkey =~ /^part1_column_option_(\d+)/) {
+    #download speed
+    return (broadband_downstream => $min_download_speed[$1]);
+  } elsif ($formkey =~ /^part1_row_option_(\d+)/) {
+    #upload speed
+    return (broadband_upstream   => $min_upload_speed[$1]);
+  } elsif ($formkey =~ /^part1_technology_option_(\d+)/) {
+    #broadband tech
+    return (is_broadband  => 1,
+            media         => $media_type[$1],
+            technology    => $technology[$1]);
+  } elsif ($formkey =~ /^part2a_row_option_(\d+)/) {
+    #local phone options
+    return (split(':', $phone_option[$1]));
+  } elsif ($formkey =~ /^part2b_row_option_(\d+)/) {
+    #VoIP options (are all media types)
+    return (split(':', $voip_option[$1]));
+  } else {
+    warn "can't parse option with formkey '$formkey'\n";
+    return;
+  }
+}
+
+for my $part_pkg (qsearch('part_pkg', { freq => {op => '!=', value => '0'}})) {
+  my $pkgpart = $part_pkg->pkgpart;
+  print "#$pkgpart\n";
+  my %report_opts = $part_pkg->options;
+  my @fcc_opts;
+  foreach my $optionname (keys(%report_opts)) {
+    $optionname =~ /^report_option_(\d+)$/ or next;
+    my $num = $1;
+    push @fcc_opts, report_option_to_fcc_option($num);
+  }
+  # other special stuff:
+  # FCC voice class (VoIP OTT, VoIP + broadband)
+  if ($part_pkg->fcc_voip_class == 1) {
+    push @fcc_opts, 'is_voip' => 1;
+  } elsif ( $part_pkg->fcc_voip_class == 2) {
+    push @fcc_opts, 'is_voip' => 1, 'is_broadband' => 1;
+  }
+  # DS0 equivalent lines
+  if ( $part_pkg->fcc_ds0s ) {
+    if ($part_pkg->fcc_voip_class) {
+      # there's no such thing as a VoIP DS0 equivalent, but this is
+      # what we used the field for
+      push @fcc_opts, 'voip_lines' => $part_pkg->fcc_ds0s;
+    } else {
+      push @fcc_opts, 'phone_lines' => $part_pkg->fcc_ds0s, 'is_phone' => 1;
+    }
+  }
+
+  my %fcc_opts = @fcc_opts;
+  print map {"\t$_\t".$fcc_opts{$_}."\n"} keys %fcc_opts;
+  my $error = $part_pkg->process_fcc_options(\%fcc_opts);
+  if ( $error ) {
+    die "$error\n";
+  }
+  print "\n";
+}
+
+print "Finished.\n";
+
diff --git a/httemplate/edit/part_pkg.cgi b/httemplate/edit/part_pkg.cgi
index 61994f9..9e55d9f 100755
--- a/httemplate/edit/part_pkg.cgi
+++ b/httemplate/edit/part_pkg.cgi
@@ -232,18 +232,23 @@
                        },
                      },
 
-                     ( $conf->exists('cust_pkg-show_fcc_voice_grade_equivalent')
-                       ? ( 
-                           { type  => 'tablebreak-tr-title',
-                             value => 'FCC Form 477 information',
-                           },
-                           { field=>'fcc_voip_class',
-                             type=>'select-voip_class',
-                           },
-                           { field=>'fcc_ds0s', type=>'text', size=>6 },
-                         )
-                        : ()
-                     ),
+                     { type  => 'tablebreak-tr-title',
+                       value => 'FCC Form 477 information',
+                     },
+                     { field => 'fcc_options_string',
+                       type  => 'input-fcc_options',
+                       curr_value_callback => sub {
+                         my ($cgi, $part_pkg, $fref) = @_;
+                         if ( $cgi->param('fcc_options_string') ) {
+                           # error redirect
+                           return $cgi->param('fcc_options_string');
+                         }
+                         my %hash;
+                         %hash = $part_pkg->fcc_options 
+                           if ($part_pkg->pkgpart);
+                         return encode_json(\%hash);
+                       },
+                     },
 
                      { type  => 'tablebreak-tr-title',
                        value => 'External Links', #better name?
diff --git a/httemplate/edit/process/part_pkg.cgi b/httemplate/edit/process/part_pkg.cgi
index 8ea39e1..d27ddb0 100755
--- a/httemplate/edit/process/part_pkg.cgi
+++ b/httemplate/edit/process/part_pkg.cgi
@@ -127,6 +127,14 @@ my $args_callback = sub {
   push @args, 'part_pkg_currency' => \%part_pkg_currency;
 
   ###
+  # fcc options
+  ###
+  my $fcc_options_string = $cgi->param('fcc_options_string');
+  if ($fcc_options_string) {
+    push @args, 'fcc_options' => decode_json($fcc_options_string);
+  }
+
+  ###
   #pkg_svc
   ###
 
diff --git a/httemplate/elements/tr-input-fcc_options.html b/httemplate/elements/tr-input-fcc_options.html
new file mode 100644
index 0000000..bd50830
--- /dev/null
+++ b/httemplate/elements/tr-input-fcc_options.html
@@ -0,0 +1,25 @@
+<TR>
+  <TH COLSPAN=2>
+    <& hidden.html, 'id' => $id, @_ &>
+%#    <& input-text.html, 'id' => $id, @_ &>
+%# XXX debugging
+    <FONT SIZE="+1"><BUTTON TYPE="button" onclick="show_fcc_options()">
+      FCC Form 477 information
+    </BUTTON></FONT>
+% # show some kind of useful summary of the FCC options here
+  </TH>
+</TR>
+<SCRIPT TYPE="text/javascript">
+function show_fcc_options() {
+  <& popup_link_onclick.html,
+  'action'      => $fsurl.'misc/part_pkg_fcc_options.html?id=' . $id,
+  'actionlabel' => 'FCC Form 477 options',
+  'width'       => 760,
+  'height'      => 600,
+  &>
+}
+</SCRIPT>
+<%init>
+my %opt = @_;
+my $id = $opt{id} || $opt{field};
+</%init>
diff --git a/httemplate/misc/part_pkg_fcc_options.html b/httemplate/misc/part_pkg_fcc_options.html
new file mode 100644
index 0000000..1f5d4a8
--- /dev/null
+++ b/httemplate/misc/part_pkg_fcc_options.html
@@ -0,0 +1,200 @@
+<& /elements/header-popup.html &>
+
+<STYLE>
+  fieldset {
+    border: 1px solid #7e0079;
+    border-radius: 8px;
+    background-color: #fff;
+  }
+  fieldset.inv {
+    border: none;
+  }
+  fieldset:disabled {
+    display: none;
+  } 
+</STYLE>
+
+<%def .checkbox>
+% my $field = shift;
+% my $extra = shift || '';
+<INPUT TYPE="checkbox" NAME="<% $field %>" ID="<% $field %>" VALUE="1" <%$extra%>>
+</%def>
+
+<FORM NAME="fcc_option_form">
+% # The option structure is hardcoded.  The FCC rules changed enough from 
+% # the original 477 report to the 2013 revision that any data-driven 
+% # mechanism for expressing the original rules would likely fail to 
+% # accommodate the new ones.  Therefore, we assume that whatever the FCC
+% # does NEXT will also require a rewrite of this form, and we'll deal with
+% # that when it arrives.
+  <P>
+    <LABEL FOR="media">Media type</LABEL>
+    <SELECT NAME="media" ID="media">
+      <OPTION VALUE=""></OPTION>
+% foreach (keys(%$media_types)) {
+      <OPTION VALUE="<% $_ %>"><% $_ %></OPTION>
+% }
+    </SELECT>
+  </P>
+  <P>
+    <& .checkbox, 'is_consumer' &>
+    <LABEL FOR="is_consumer">This is a consumer-grade package</LABEL>
+  </P>
+  <P>
+    <& .checkbox, 'is_broadband' &>
+    <LABEL FOR="is_broadband">This package provides broadband service</LABEL>
+    <FIELDSET ID="broadband">
+      <LABEL FOR="technology">Technology of transmission</LABEL>
+      <SELECT NAME="technology" ID="technology"> </SELECT>
+      <BR>
+      <LABEL FOR="broadband_downstream">Downstream speed (Mbps)</LABEL>
+      <INPUT NAME="broadband_downstream" ID="broadband_downstream">
+      <BR>
+      <LABEL FOR="broadband_upstream">Upstream speed (Mbps)</LABEL>
+      <INPUT NAME="broadband_upstream" ID="broadband_upstream">
+    </FIELDSET>
+  </P>
+  <P>
+    <& .checkbox, 'is_phone' &>
+    <LABEL FOR="is_phone">This package provides local telephone service</LABEL>
+    <FIELDSET ID="phone">
+      <LABEL FOR="phone_wholesale">Marketed as</LABEL>
+      <SELECT NAME="phone_wholesale" ID="phone_wholesale">
+        <OPTION VALUE="">end user</OPTION>
+        <OPTION VALUE="1">wholesale</OPTION>
+      </SELECT>
+      <FIELDSET CLASS="inv" ID="phone0" DISABLED="1">
+        <LABEL FOR="phone_lines">Number of voice-grade equivalents</LABEL>
+        <INPUT NAME="phone_lines" ID="phone_lines">
+        <BR>
+        <LABEL FOR="phone_longdistance">Are you the presubscribed long-distance carrier?</LABEL>
+        <& .checkbox, 'phone_longdistance' &>
+        <BR>
+        <LABEL FOR="phone_localloop">Local loop arrangement</LABEL>
+        <SELECT NAME="phone_localloop" ID="phone_localloop">
+          <OPTION VALUE="owned">You own the local loop</OPTION>
+          <OPTION VALUE="leased">You lease UNE-L from another carrier</OPTION>
+          <OPTION VALUE="resale">You resell another carrier's service</OPTION>
+        </SELECT>
+      </FIELDSET>
+      <FIELDSET CLASS="inv" ID="phone1" DISABLED="1">
+        <LABEL FOR="phone_vges">Number of voice-grade equivalents (if any)</LABEL>
+        <INPUT NAME="phone_vges" ID="phone_vges">
+        <BR>
+        <LABEL FOR="phone_circuits">Number of unswitched circuits (if any)</LABEL>
+        <INPUT NAME="phone_circuits" ID="phone_circuits">
+      </FIELDSET>
+    </FIELDSET>
+  </P>
+  <P>
+    <& .checkbox, 'is_voip' &>
+    <LABEL FOR="is_voip">This package provides VoIP telephone service</LABEL>
+    <FIELDSET ID="voip">
+      <LABEL FOR="voip_ott">Do you also provide last-mile connectivity?</LABEL>
+      <& .checkbox, 'voip_ott' &>
+    </FIELDSET>
+  </P>
+  <DIV WIDTH="100%" STYLE="text-align:center">
+    <INPUT TYPE="submit" VALUE="Save changes">
+  </DIV>
+</FORM>
+
+<SCRIPT TYPE="text/javascript">
+// this form is invoked as a popup; the current values of the parent 
+// object are in the form field ID passed as the 'id' param
+
+var parent_id = window.parent.document.getElementById('<% $parent_id %>');
+var curr_values = JSON.parse(window.parent_id.value);
+var form = document.forms['fcc_option_form'];
+var media_types = <% encode_json($media_types) %>
+var technology_labels = <% encode_json($technology_labels) %>
+
+function set_tech_options() {
+  var form = document.forms['fcc_option_form'];
+  var curr_type = form.elements['media'].value;
+  var technology_obj = form.elements['technology'];
+  technology_obj.options.length = 0;
+  if (media_types[curr_type]) {
+    for( var i = 0; i < media_types[curr_type].length; i++ ) {
+      var value = media_types[curr_type][i];
+      var o = document.createElement('OPTION');
+      o.text = technology_labels[value]
+      o.value = value;
+      technology_obj.add(o);
+    }
+  }
+}
+  
+function save_changes() {
+  var form = document.forms['fcc_option_form'];
+  var data = {};
+  for (var i = 0; i < form.elements.length; i++) {
+    // quick and dirty test for whether the element is displayed
+    if (form.elements[i].clientHeight > 0) {
+      if (form.elements[i].type == 'checkbox') {
+        if (form.elements[i].checked) {
+          data[ form.elements[i].name ] = 1;
+        }
+      } else {
+        data[ form.elements[i].name ] = form.elements[i].value;
+      }
+    }
+  }
+  parent_id.value = JSON.stringify(data);
+  parent.cClick(); //overlib
+}
+
+function enable_fieldset(fieldset_id) {
+  var fieldset = document.getElementById(fieldset_id);
+  return function () { fieldset.disabled = !this.checked; };
+}
+
+<&| /elements/onload.js &>
+  function addEventListener(target, action, listener) {
+    if (target.addEventListener) {
+      target.addEventListener(action, listener);
+    } else if (target.attachEvent) { // IE 8 fails at everything
+      target.attachEvent('on'+action, listener);
+    }
+  }
+
+  // set up all event handlers
+  addEventListener(form, 'submit', save_changes);
+
+  var sections = [ 'broadband', 'phone', 'voip' ];
+  for(var i = 0; i < sections.length; i++) {
+    var toggle = form.elements['is_'+sections[i]];
+    addEventListener(toggle, 'change', enable_fieldset(sections[i]));
+  }
+
+  addEventListener(form.elements['media'], 'change', set_tech_options);
+  addEventListener(form.elements['phone_wholesale'], 'change',
+    function () {
+      form.elements['phone0'].disabled = (this.value == 1);
+      form.elements['phone1'].disabled = (this.value == '');
+    }
+  );
+
+  // load data from the parent form and trigger handlers
+  for(var i = 0; i < form.elements.length; i++) {
+    var input_obj = form.elements[i];
+    if (input_obj.type == 'submit') {
+      //nothing
+    } else if (input_obj.type == 'checkbox') {
+      input_obj.checked = (curr_values[input_obj.name] > 0);
+    } else {
+      input_obj.value = curr_values[input_obj.name] || '';
+    }
+    input_obj.dispatchEvent( new Event('change') );
+  }
+
+</&>
+
+</SCRIPT>
+<& /elements/footer.html &>
+<%init>
+my $media_types = FS::part_pkg_fcc_option->media_types;
+my $technology_labels = FS::part_pkg_fcc_option->technology_labels;
+
+my $parent_id = $cgi->param('id');
+</%init>
diff --git a/httemplate/search/477partIA.html b/httemplate/search/477partIA.html
index 97f8ac0..55e901b 100755
--- a/httemplate/search/477partIA.html
+++ b/httemplate/search/477partIA.html
@@ -80,13 +80,17 @@ die "access denied"
 
 my %opt = @_;
 my %search_hash;
-  
+
 for ( qw(agentnum state) ) {
   $search_hash{$_} = $cgi->param($_) if $cgi->param($_);
-}
+} # note that separation by state is no longer required after July 2014
 $search_hash{'country'} = 'US';
 $search_hash{'classnum'} = [ $cgi->param('classnum') ];
 
+my $info = FS::part_pkg_fcc_option->info;
+
+
+
 # arrays of report_option_ numbers, running parallel to 
 # the download and upload speed arrays
 my @download_option = $cgi->param('part1_column_option');