477 report rewrite, #28020

8 files changed, 1154 insertions, 0 deletions

diff --git a/httemplate/search/old477/477.html b/httemplate/search/old477/477.html
new file mode 100755
index 000000000..ecf21cfb1
--- /dev/null
+++ b/httemplate/search/old477/477.html
@@ -0,0 +1,135 @@
+% if ( $type eq 'xml' ) {
+% $filename = "fcc_477_$state" . '_' . time2str('%Y%m%d', $date) . '.xml';
+% http_header('Content-Type' => 'application/XML' ); # So saith RFC 4180
+% http_header('Content-Disposition' => 'attachment;filename="'.$filename.'"');
+<?xml version="1.0" encoding="ISO-8859-1"?>
+<Form_477_submission xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="https://specialreports.fcc.gov/wcb/Form477/XMLSchema-instance/form_477_upload_Schema.xsd" >
+% } else { #html
+<& /elements/header.html, "FCC Form 477 Results - $state" &>
+%# XXX when we stop supporting IE8, add this to freeside.css using :nth-child
+%# selectors, and remove it from everywhere else
+<STYLE TYPE="text/css">
+.grid TH { background-color: #cccccc; padding: 0px 3px 2px; text-align: right }
+.row0 TD { background-color: #eeeeee; padding: 0px 3px 2px; text-align: right }
+.row1 TD { background-color: #ffffff; padding: 0px 3px 2px; text-align: right }
+</STYLE>
+
+<TABLE WIDTH="100%">
+  <TR>
+    <TD></TD>
+    <TD ALIGN="right" CLASS="noprint">
+      Download full results<BR>
+%   $cgi->param('_type', 'xml');
+      as <A HREF="<% $cgi->self_url %>">XML file</A><BR>
+
+%   $cgi->param('_type', 'html-print');
+      as <A HREF="<% $cgi->self_url %>">printable copy</A>
+
+    </TD>
+%   $cgi->param('_type', $type );
+  </TR>
+</TABLE>
+% } #html
+% foreach my $part ( @parts ) {
+%   if ( $part{$part} ) {
+%
+%     if ( $part eq 'V' ) {
+%       next unless ( $part{'IIA'} || $part{'IIB'} );
+%     }
+%
+%     if ( $part eq 'VI_census' ) {
+%       next unless $part{'IA'};
+%     }
+%
+%     my @reports = ();
+%     if ( $part eq 'IA' ) {
+%       for ( my $tech = 0; $tech < scalar(@technology_option); $tech++ ) {
+%         next unless $technology_option[$tech];
+%         my $url = &{$url_mangler}($part);
+%         if ( $type eq 'xml' ) {
+<<% 'Part_IA_'. chr(65 + $tech) %>>
+%         }
+<& "477part${part}.html",
+    'tech_code' => $tech,
+    'url' => $url,
+    'type' => $type,
+    'date' => $date,
+&>
+%         if ( $type eq 'xml' ) {
+</<% 'Part_IA_'. chr(65 + $tech) %>>
+%         }
+%       }
+%     } else { # not part IA
+%       if ( $type eq 'xml' ) {
+<<% 'Part_'. $part %>>
+%       }
+%       my $url = &{$url_mangler}($part);
+<& "477part${part}.html",
+    'url' => $url,
+    'date' => $date,
+    'filename' => $filename,
+&>
+%       if ( $type eq 'xml' ) {
+</<% 'Part_'. $part %>>
+%       }
+%     }
+%   }
+% }
+%
+% if ( $type eq 'xml' ) {
+</Form_477_submission>
+% } else {
+<& /elements/footer.html &>
+% }
+<%init>
+
+my $curuser = $FS::CurrentUser::CurrentUser;
+
+die "access denied"
+  unless $curuser->access_right('List packages');
+
+my $date = $cgi->param('date') ? parse_datetime($cgi->param('date'))
+                               : time;
+
+my $state = uc($cgi->param('state'));
+$state =~ /^[A-Z]{2}$/ or die "illegal state: $state";
+
+my %part = map { $_ => 1 } grep { /^\w+$/ } $cgi->param('part');
+my $type = $cgi->param('_type') || 'html';
+my $filename;
+my @technology_option = &FS::Report::FCC_477::parse_technology_option($cgi,1);
+
+# save upload and download mappings
+my @download = $cgi->param('part1_column_option');
+my @upload = $cgi->param('part1_row_option');
+for(my $i=0; $i < scalar(@download); $i++) {
+    &FS::Report::FCC_477::save_fcc477map("part1_column_option_$i",$download[$i]);
+}
+for(my $i=0; $i < scalar(@upload); $i++) {
+    &FS::Report::FCC_477::save_fcc477map("part1_row_option_$i",$upload[$i]);
+}
+
+my @part2a_row_option = $cgi->param('part2a_row_option');
+for(my $i=0; $i < scalar(@part2a_row_option); $i++) {
+    &FS::Report::FCC_477::save_fcc477map("part2a_row_option_$i",$part2a_row_option[$i]);
+}
+
+my @part2b_row_option = $cgi->param('part2b_row_option');
+for(my $i=0; $i < scalar(@part2b_row_option); $i++) {
+    &FS::Report::FCC_477::save_fcc477map("part2b_row_option_$i",$part2b_row_option[$i]);
+}
+
+my $part5_report_option = $cgi->param('part5_report_option');
+if ( $part5_report_option ) {
+  FS::Report::FCC_477::save_fcc477map('part5_report_option', $part5_report_option);
+}
+
+my $url_mangler = sub {
+  my $part = shift;
+  my $url = $cgi->url('-path_info' => 1, '-full' => 1);
+  $url =~ s/477\./477part$part./;
+  $url;
+};
+my @parts = qw( IA IIA IIB IV V VI_census );
+
+</%init>
diff --git a/httemplate/search/old477/477partIA.html b/httemplate/search/old477/477partIA.html
new file mode 100755
index 000000000..55e901bb3
--- /dev/null
+++ b/httemplate/search/old477/477partIA.html
@@ -0,0 +1,178 @@
+% if ( $opt{'type'} eq 'xml' ) {
+%# container element <Part_IA_$tech> is in 477.html
+%   my $col = 'a';
+%   foreach ( @summary_row ) {
+%     my $el = $xml_prefix . $col . '1'; # PartIA_Aa1, PartIA_Ab1, etc.
+  <<% $el %>><% $_ %><<% "/$el" %>>
+%     $col++;
+%   }
+%   foreach my $col_data ( @data ) { 
+%     my $row = 1;
+%     foreach my $cell ( @$col_data ) {
+%       my $el = $xml_prefix . $col . $row; # PartIA_Af1, PartIA_Af2...
+%       if ( $cell->[0] > 0 ) {
+  <<% $el %>><% $cell->[0] %><<% "/$el" %>>
+%         if ( $percentages ) {
+%           $el = $xml_percent . $col . $row; # Part_p_IA_Af1, ...
+  <<% $el %>><% $cell->[1] %><<% "/$el" %>>
+%         }
+%       }
+%       $row++;
+%     } # foreach $cell
+%     $col++;
+%   } # foreach $col_data
+% } else { # not XML
+
+<H2><% $title %> totals</H2>
+<& /elements/table-grid.html &>
+  <TR>
+%   foreach ( 'Total Connections',
+%             '% owned loop',
+%             '% billed to end users',
+%             '% residential',
+%             '% residential > 200 kbps') {
+    <TH WIDTH="20%"><% $_ |h %></TH>
+%   }
+  </TR>
+  <TR CLASS="row0">
+%   foreach ( @summary_row ) {
+    <TD><% $_ %></TD>
+%   }
+  </TR>
+</TABLE>
+<H2><% $title %> breakdown by speed</H2>
+<TABLE CLASS="grid" CELLSPACING=0>
+  <TR>
+    <TH WIDTH="12%"></TH>
+%   for (my $col = 0; $col < scalar(@download_option); $col++) {
+    <TH WIDTH="11%">
+      <% $FS::Report::FCC_477::download[$col] |h %>
+    </TH>
+%   }
+  </TR>
+% for (my $row = 0; $row < scalar(@upload_option); $row++) {
+  <TR CLASS="row<% $row % 2%>">
+    <TD STYLE="text-align: left; font-weight: bold">
+%     if ( $asymmetric ) {
+      <% $FS::Report::FCC_477::upload[$row] |h %>
+%     }
+    </TD>
+%   for (my $col = 0; $col < scalar(@download_option); $col++) {
+    <TD>
+%     if ( $data[$col][$row][0] > 0 ) {
+      <% $data[$col][$row][0] %>
+%       if ( $percentages ) {
+      <BR><% $data[$col][$row][1] %>
+%       }
+%     }
+    </TD>
+%   } # for $col
+  </TR>
+% } # for $row
+</TABLE>
+% }
+<%init>
+
+my $curuser = $FS::CurrentUser::CurrentUser;
+
+die "access denied"
+  unless $curuser->access_right('List packages');
+
+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');
+my @upload_option = $cgi->param('part1_row_option');
+
+my @technology_option = &FS::Report::FCC_477::parse_technology_option($cgi);
+
+my $total_count = 0;
+my $total_residential = 0;
+my $above_200 = 0;
+my $tech_code = $opt{tech_code};
+my $technology = $FS::Report::FCC_477::technology[$tech_code] || 'unknown';
+my $title = "Part IA $technology";
+my $xml_prefix = 'PartIA_'. chr(65 + $tech_code);
+my $xml_percent = 'Part_p_IA_'. chr(65 + $tech_code); # yes, seriously
+
+# whether to show the results as a matrix (upload speeds in rows) or a single
+# row
+my $asymmetric = 1;
+if ( $technology eq 'Symmetric xDSL' or $technology eq 'Other Wireline' ) {
+  $asymmetric = 0;
+  @upload_option = ( undef );
+}
+# whether to show residential percentages in each cell of the matrix
+my $percentages = ($technology eq 'Terrestrial Mobile Wireless');
+
+# as of date
+# FCC 477 instructions: "Only count connections that are in service."
+# So we count packages that were in active status as of the specified date,
+# not over any sort of range.
+$search_hash{'active'} = [ $opt{date}, $opt{date} ];
+
+my $query = FS::cust_pkg->search(\%search_hash);
+my $count_query = $query->{'count_query'};
+
+my $is_residential = " AND COALESCE(cust_main.company, '') = ''";
+my $has_option = sub {
+  my $optionnum = shift;
+  $optionnum =~ /^\d+$/ ?
+  " AND EXISTS(
+    SELECT 1 FROM part_pkg_option
+    WHERE part_pkg_option.pkgpart = part_pkg.pkgpart
+    AND optionname = 'report_option_$optionnum'
+    AND optionvalue = '1'
+  )" : '';
+};
+
+# limit to those that have technology option $tech_code
+$count_query .= $has_option->($technology_option[$tech_code]);
+
+my @data;
+for ( my $row = 0; $row < scalar @upload_option; $row++ ) {
+  for ( my $col = 0; $col < scalar @download_option; $col++ ) {
+
+    my $this_count_query = $count_query .
+                           $has_option->($upload_option[$row]) .
+                           $has_option->($download_option[$col]);
+
+    my $count = FS::Record->scalar_sql($this_count_query);
+    my $residential = FS::Record->scalar_sql($this_count_query . $is_residential);
+
+    my $percent = sprintf('%.3f', $count ? 100 * $residential / $count : 0);
+    $data[$col][$row] = [ $count, $percent ];
+
+    $total_count += $count;
+    $total_residential += $residential;
+    $above_200 += $residential if $row > 0 or !$asymmetric;
+  }
+}
+
+my $total_percentage =
+  sprintf("%.3f", $total_count ? 100*$total_residential/$total_count : 0);
+
+my $above_200_percentage =
+  sprintf("%.3f", $total_count ? 100*$above_200/$total_count : 0);
+
+my @summary_row = (
+  $total_count,
+  100.00, # own local loop--consistent with previous practice, but probably wrong
+  100.00, # billed to end user--also wrong
+  $total_percentage, # residential percentage
+  $above_200_percentage,
+);
+
+</%init>
diff --git a/httemplate/search/old477/477partIIA.html b/httemplate/search/old477/477partIIA.html
new file mode 100755
index 000000000..7ccee6928
--- /dev/null
+++ b/httemplate/search/old477/477partIIA.html
@@ -0,0 +1,132 @@
+% if ( $cgi->param('_type') eq 'xml' ) {
+%   my @cols = qw(a b c d);
+%   for ( my $row = 0; $row < scalar(@rows); $row++ ) {
+%     for my $col (0..3) {
+<PartII_<% $row + 1 %><% $cols[$col] %>>\
+<% $data[$col][$row] || 0 %>\
+</PartII_<% $row + 1 %><% $cols[$col] %>>
+%     } #for $col
+%   } #for $row
+% } else { # HTML mode
+% # fake up the search-html.html header
+<H2>Part IIA</H2>
+<TABLE>
+  <TR><TD VALIGN="bottom"><BR></TD></TR>
+  <TR><TD COLSPAN=2>
+  <TABLE CLASS="grid" CELLSPACING=0>
+    <TR>
+% foreach (@row1_headers) {
+      <TH><% $_ %></TH>
+% }
+    </TR>
+% my $row = 0;
+% foreach my $rowhead (@rows) {
+    <TR CLASS="row<%$row % 2%>"> 
+      <TD STYLE="text-align: left; font-weight: bold"><% $rowhead %></TD>
+%     for my $col (0..3) {
+      <TD>
+%       if ( exists($data[$col][$row]) ) {
+      <% $data[$col][$row] %>
+%       }
+      </TD>
+%     } # for $col
+    </TR>
+%   $row++;
+% } #for $rowhead
+  </TABLE>
+  </TD></TR>
+</TABLE>
+% } #XML/HTML
+<%init>
+my $curuser = $FS::CurrentUser::CurrentUser;
+
+die "access denied"
+  unless $curuser->access_right('List packages');
+
+my %opt = @_;
+my %search_hash = ();
+
+$search_hash{'agentnum'} = $cgi->param('agentnum');
+$search_hash{'state'}    = $cgi->param('state');
+$search_hash{'classnum'} = [ $cgi->param('classnum') ];
+$search_hash{'active'}   = [ $opt{date}, $opt{date} ];
+
+my @row_option;
+foreach ($cgi->param('part2a_row_option')) {
+  push @row_option, (/^\d+$/ ? $_ : undef);
+}
+
+my $is_residential = " AND COALESCE(cust_main.company, '') = ''";
+my $has_report_option = sub {
+  map {
+    defined($row_option[$_]) ?
+    " AND EXISTS(
+      SELECT 1 FROM part_pkg_option 
+      WHERE part_pkg_option.pkgpart = part_pkg.pkgpart
+      AND optionname = 'report_option_" . $row_option[$_]."'
+      AND optionvalue = '1'
+    )" : ' AND FALSE'
+  } @_
+};
+
+# an arrayref for each column
+my @data;
+# get the skeleton of the query
+my $sql_query = FS::cust_pkg->search(\%search_hash);
+my $from_where = $sql_query->{'count_query'};
+$from_where =~ s/^SELECT COUNT\(\*\) //;
+
+# for row 1
+my $query_ds0 = "SELECT SUM(COALESCE(part_pkg.fcc_ds0s, pkg_class.fcc_ds0s, 0))
+  $from_where AND fcc_voip_class = '4'"; # 4 = Local Exchange
+
+my $total_lines = FS::Record->scalar_sql($query_ds0);
+# always return zero for the number of resold lines, until an actual ILEC
+# starts using this report
+
+@data = (
+  [ $total_lines ],
+  [ 0 ],
+  [ 0 ],
+  [ 0 ],
+);
+
+my @row_conds = (
+  $is_residential,
+  $has_report_option->(0), # LD carrier
+  ($has_report_option->(0))[0] . $is_residential,
+  $has_report_option->(1..7),
+);
+
+if ( $total_lines > 0 ) {
+  foreach (@row_conds) {
+    my $sql = $query_ds0 . $_;
+    my $lines = FS::Record->scalar_sql($sql);
+    my $percent = sprintf('%.3f', 100 * $lines / $total_lines);
+    push @{ $data[0] }, $percent;
+  }
+}
+
+my @rows = (
+  'lines',
+  '% residential',
+  '% LD carrier',
+  '% residential and LD',
+  '% owned loops',
+  '% unswitched UNE',
+  '% UNE-P',
+  '% UNE-P replacement',
+  '% FTTP',
+  '% coax',
+  '% wireless',
+);
+
+my @row1_headers = (
+  '',
+  'End user lines',
+  'UNE-P replacement',
+  'unswitched UNE',
+  'UNE-P',
+);
+
+</%init>
diff --git a/httemplate/search/old477/477partIIB.html b/httemplate/search/old477/477partIIB.html
new file mode 100755
index 000000000..bd56ecc60
--- /dev/null
+++ b/httemplate/search/old477/477partIIB.html
@@ -0,0 +1,155 @@
+% if ( $cgi->param('_type') eq 'xml' ) {
+%   my @cols = qw(a b c);
+%   for ( my $row = 0; $row < scalar(@rows); $row++ ) {
+%     for my $col (0..2) {
+%       if ( exists($data[$col][$row]) ) {
+<PartII_<% $row + 1 %><% $cols[$col] %>>\
+<% $data[$col][$row] %>\
+</PartII_<% $row + 1 %><% $cols[$col] %>>
+%       }
+%     } #for $col
+%   } #for $row
+% } else { # HTML mode
+% # fake up the search-html.html header
+<H2>Part IIB</H2>
+<TABLE>
+  <TR><TD VALIGN="bottom"><BR></TD></TR>
+  <TR><TD COLSPAN=2>
+  <TABLE CLASS="grid" CELLSPACING=0>
+    <TR>
+% foreach (@headers) {
+      <TH><% $_ %></TH>
+% }
+    </TR>
+% my $row = 0;
+% foreach my $rowhead (@rows) {
+    <TR CLASS="row<% $row % 2 %>"> 
+      <TD STYLE="text-align: left; font-weight: bold"><% $rowhead %></TD>
+%     for my $col (0..2) {
+      <TD>
+%       if ( exists($data[$col][$row]) ) {
+      <% $data[$col][$row] %>
+%       }
+      </TD>
+%     } # for $col
+    </TR>
+%   $row++;
+% } #for $rowhead
+  </TABLE>
+  </TD></TR>
+</TABLE>
+% } #XML/HTML
+<%init>
+
+my $curuser = $FS::CurrentUser::CurrentUser;
+
+die "access denied"
+  unless $curuser->access_right('List packages');
+
+my %opt = @_;
+my %search_hash = ();
+
+$search_hash{'agentnum'} = $cgi->param('agentnum');
+$search_hash{'state'}    = $cgi->param('state');
+$search_hash{'classnum'} = [ $cgi->param('classnum') ];
+$search_hash{'active'}   = [ $opt{date}, $opt{date} ];
+
+my @row_option;
+foreach ($cgi->param('part2b_row_option')) {
+  push @row_option, (/^\d+$/ ? $_ : undef);
+}
+
+my $is_residential = " AND COALESCE(cust_main.company, '') = ''";
+my $has_report_option = sub {
+  map {
+    defined($row_option[$_]) ?
+    " AND EXISTS(
+      SELECT 1 FROM part_pkg_option 
+      WHERE part_pkg_option.pkgpart = part_pkg.pkgpart
+      AND optionname = 'report_option_" . $row_option[$_]."'
+      AND optionvalue = '1'
+    )" : ' AND FALSE'
+  } @_
+};
+
+# an arrayref for each column
+my @data;
+# get the skeleton of the query
+my $sql_query = FS::cust_pkg->search(\%search_hash);
+my $from_where = $sql_query->{'count_query'};
+$from_where =~ s/^SELECT COUNT\(\*\) //;
+# columns 1 and 2
+my $query_ds0 = "SELECT SUM(COALESCE(part_pkg.fcc_ds0s, pkg_class.fcc_ds0s, 0))
+  $from_where";
+# column 3
+my $query_custnum = "SELECT COUNT(DISTINCT cust_pkg.custnum) $from_where";
+
+my @base_queries = ($query_ds0, $query_ds0, $query_custnum);
+my @col_conds = (
+  # column 1
+  [
+    '',
+    $is_residential,
+    $has_report_option->(0), # nomadic
+  ],
+  # column 2
+  [
+    '',
+    $is_residential,
+    $has_report_option->(0..5),
+  ],
+  # column 3
+  [
+    ''
+  ]
+);
+
+my $col = 0;
+foreach (@col_conds) {
+  my @col_data;
+  my $row = 0;
+  foreach my $cond (@{ $col_conds[$col] }) {
+    # three parts: the select expression, the VoIP class (column selection),
+    # and the row selection
+    my $query = $base_queries[$col] . 
+                " AND part_pkg.fcc_voip_class = '".($col+1)."'
+                $cond";
+    my $count = FS::Record->scalar_sql($query) || 0;
+    if ( $row == 0 ) {
+      $col_data[$row] = $count; # the raw count
+    } else {
+      # the rows that are percentages of the raw count
+      if ( $col_data[0] == 0 ) {
+        # 0 out of 0 is not 0%, but supposedly this works
+        $col_data[$row] = '0.000';
+      } else {
+        $col_data[$row] = sprintf('%.3f', 100 * $count / $col_data[0]);
+      }
+      $col_data[$row] .= '%' unless $cgi->param('_type') eq 'xml';
+    } #if $row == 0
+    $row++;
+  }
+  $data[$col] = \@col_data;
+  $col++;
+}
+
+
+my @rows = (
+  'total number',
+  '% residential',
+  '% nomadic',
+  '% copper',
+  '% FTTP',
+  '% coax',
+  '% wireless',
+  '% other broadband',
+);
+
+my @headers = (
+  '',
+  'without broadband',
+  'with broadband',
+  'wholesale',
+);
+
+</%init>
diff --git a/httemplate/search/old477/477partIV.html b/httemplate/search/old477/477partIV.html
new file mode 100755
index 000000000..269a925dc
--- /dev/null
+++ b/httemplate/search/old477/477partIV.html
@@ -0,0 +1,17 @@
+%if ( $cgi->param('_type') eq 'html' || $cgi->param('_type') eq 'html-print' ) {
+<H2>Part IV</H2>
+%} elsif ( $cgi->param('_type') eq 'xml' ) {
+<notes>
+%}
+<% $cgi->param('notes') |h %>
+%if ( $cgi->param('_type') eq 'xml' ) {
+</notes>
+%}
+<%init>
+
+my $curuser = $FS::CurrentUser::CurrentUser;
+
+die "access denied"
+  unless $curuser->access_right('List packages');
+  
+</%init>
diff --git a/httemplate/search/old477/477partV.html b/httemplate/search/old477/477partV.html
new file mode 100755
index 000000000..80201f9d7
--- /dev/null
+++ b/httemplate/search/old477/477partV.html
@@ -0,0 +1,54 @@
+% if ( $cgi->param('_type') =~ /^xml$/ ) {
+<zip_codes>
+% }
+<& /search/elements/search.html,
+                  'html_init'         => $html_init,
+                  'name'              => 'zip code',
+                  'query'             => $sql_query,
+                  'count_query'       => $count_query,
+                  'nohtmlheader'      => 1,
+                  'disable_total'     => 1,
+                  'header'            => [ 'zip code' ],
+                  'xml_elements'      => [ 'zip codes' ],
+                  'no_field_elements' => 1,
+                  'fields'            => [ 'zip' ],
+                  'url'               => $opt{url} || '',
+                  'really_disable_download'  => 1,
+
+              
+&>
+% if ( $cgi->param('_type') =~ /^xml$/ ) {
+</zip_codes>
+% }
+<%init>
+
+my $curuser = $FS::CurrentUser::CurrentUser;
+
+die "access denied"
+  unless $curuser->access_right('List packages');
+
+my %opt = @_;
+my $html_init = '<H2>Part V</H2>';
+my %search_hash = ();
+my @sql_query = ();
+my @count_query = ();
+  
+for ( qw(agentnum state) ) {
+  $search_hash{$_} = $cgi->param($_) if $cgi->param($_);
+}
+$search_hash{'country'} = 'US';
+$search_hash{'classnum'} = [ $cgi->param('classnum') ];
+$search_hash{report_option} = $cgi->param('part5_report_option')
+  if $cgi->param('part5_report_option');
+$search_hash{'active'}   = [ $opt{date}, $opt{date} ];
+
+my $sql_query = FS::cust_pkg->search( { %search_hash,
+                                        'fcc_line'    => 1,
+                                        'select_zip5' => 1,
+                                      }
+                                    );
+my $count_query = delete($sql_query->{'count_query'});
+$count_query =~ s/COUNT\(\*\)/count(DISTINCT substr(zip,1,5))/;
+$count_query =~ s/ORDER BY [.\w]+//;
+
+</%init>
diff --git a/httemplate/search/old477/477partVI_census.html b/httemplate/search/old477/477partVI_census.html
new file mode 100755
index 000000000..efcf4ef1b
--- /dev/null
+++ b/httemplate/search/old477/477partVI_census.html
@@ -0,0 +1,201 @@
+<& /search/elements/search.html,
+                  'html_init'       => '<H2>Part VI</H2>',
+                  'html_foot'       => $html_foot,
+                  'name'            => 'regions',
+                  'query'           => [ @sql_query ],
+                  'count_query'     =>  $count_query,
+                  'order_by'        => 'ORDER BY censustract',
+                  'avoid_quote'     => 1,
+                  'no_csv_header'   => 1,
+                  'nohtmlheader'    => 1,
+                  'header'          => \@header,
+                  'xml_elements'    => [
+                                         'county_fips',
+                                         'census_tract',
+                                         'upload_rate_code',
+                                         'download_rate_code',
+                                         'technology_code',
+                                         'technology_code_other',
+                                         'value',
+                                         'percentage',
+                                       ],
+                  'fields'          => \@fields,
+                  'links'           => \@links,
+                  'url'             => $opt{url} || '',
+                  'xml_row_element' => 'Datarow',
+                  'really_disable_download' => 1,
+                  'filename'        => $opt{filename},
+&>
+<%init>
+
+my $curuser = $FS::CurrentUser::CurrentUser;
+
+die "access denied"
+  unless $curuser->access_right('List packages');
+
+my %opt = @_;
+
+my %state_hash = ();
+my %state_pkgcount = ();
+
+my @header = ();
+my @fields = ();
+my @links = ();
+my $num = ($cgi->param('offset') =~ /^(\d+)$/) ? $1 : 0;
+unless ( $cgi->param('_type') eq 'xml' ) {
+  push @header, '#';
+  push @fields, sub { ++$num };
+  push @links,  '';
+}
+push @header,
+  'County code',
+  'Census tract code',
+  'Upload rate',
+  'Download rate',
+  'Technology code',
+  'Technology code other',
+  'Quantity',
+  'Percentage residential',
+;
+push @fields,
+  sub { my $row = shift; 
+        my $state = substr($row->censustract, 0, 2);
+        $state_hash{$state}++;
+        substr($row->censustract, 2, 3) || 'None';
+      },
+  sub { my $row = shift;
+        substr($row->censustract, 5) || 'None';
+      },
+  'upload',
+  'download',
+  'technology_code',
+  sub { $cgi->param('_type') eq 'xml' ? '0' : '' },#broken
+  sub { my $row = shift;
+        my $state = substr($row->censustract, 0, 2);
+        $state_pkgcount{$state} += $row->quantity;
+        $row->quantity;
+      },
+  sub { my $row = shift; sprintf "%.3f", $row->residential },
+;
+
+my %search_hash = ();
+my @sql_query = ();
+  
+for ( qw(agentnum state) ) {
+  $search_hash{$_} = $cgi->param($_) if $cgi->param($_);
+}
+
+$search_hash{'active'}  = [ $opt{date}, $opt{date} ];
+$search_hash{'country'} = 'US';
+$search_hash{'classnum'} = [ $cgi->param('classnum') ]
+  if grep { $_ eq 'classnum' } $cgi->param;
+
+my @column_option = grep { /^\d+$/ } $cgi->param('part1_column_option')
+  if $cgi->param('part1_column_option');
+
+my @row_option = grep { /^\d+$/ } $cgi->param('part1_row_option')
+  if $cgi->param('part1_row_option');
+
+my @technology_option = &FS::Report::FCC_477::parse_technology_option($cgi);
+
+my $rowcount = 1;
+foreach my $row ( @row_option ) {
+  my $columncount = 2;
+  foreach my $column ( @column_option ) {
+    my $tech_code = 0;
+    foreach my $technology ( @technology_option ) {
+      $tech_code++;
+      next unless $technology;
+      my @report_option = ();
+      push @report_option, $row if $row;
+      push @report_option, $column if $column;
+      push @report_option, $technology;
+      my $report_option = join(',', @report_option) if @report_option;
+ 
+      my $sql_query = FS::cust_pkg->search(
+        { %search_hash,
+          ($report_option ? ( 'report_option' => $report_option ) : () ),
+        }
+      );
+      my $extracolumns = "$rowcount AS upload, $columncount AS download, $tech_code as technology_code";
+      my $percent = "CASE WHEN count(*) > 0 THEN 100-100*cast(count(cust_main.company) as numeric)/cast(count(*) as numeric) ELSE cast(0 as numeric) END AS residential";
+      $sql_query->{select} = "count(*) AS quantity, $extracolumns, cust_location.censustract, $percent";
+      $sql_query->{order_by} = " GROUP BY cust_location.censustract ";
+      push @sql_query, $sql_query;
+    }
+    $columncount++;
+  }
+  $rowcount++;
+}
+
+my $count_query = 'SELECT count(*) FROM ( ('.
+   join( ') UNION ALL (',
+      map { my $addl_from = $_->{addl_from};
+            my $extra_sql = $_->{extra_sql};
+            my $order_by  = $_->{order_by};
+            "SELECT cust_location.censustract from cust_pkg $addl_from 
+            $extra_sql $order_by";
+          }
+      @sql_query
+   ). ') ) AS foo';
+
+
+my $link = 'cust_pkg.cgi?'. join(';',
+                                      map { my $key = $_;
+                                            my @values = ref($search_hash{$_}) 
+                                              ? @{ $search_hash{$_} }
+                                              : $search_hash{$_};
+                                            map { "$key=$_" } @values;
+                                          }
+                                          keys %search_hash        
+                                ). ';';
+
+my $link_suffix = sub { my $row = shift;
+                        my $result = 'censustract='. $row->censustract. ';';
+                        my @ro = grep $_,
+                          @row_option[$row->upload - 1],
+                          @column_option[$row->download - 2],
+                          @technology_option[$row->technology_code - 1],
+                        ;
+                        $result .= 'report_option='. join(',',@ro) if @ro;
+                        $result;
+                      };
+
+for (1..8) { push @links, [ $link, $link_suffix ]; }
+
+
+my $html_foot = sub {
+  if (scalar(keys %state_hash) > 1) {
+
+    my $roa_r = join(',', grep $_, @row_option);
+    $roa_r = ";report_option_any_r=$roa_r" if $roa_r;
+
+    my $roa_c = join(',', grep $_, @column_option);
+    $roa_c = ";report_option_any_c=$roa_c" if $roa_c;
+
+    my $roa_t = join(',', grep $_, @technology_option);
+    $roa_t = ";report_option_any_t=$roa_t" if $roa_t;
+    
+    '<BR><B>WARNING: multiple states found</B><BR>'.
+    '<TABLE BORDER=0>'. #nicer formatting someday
+    join('', map { '<TR>'.
+                     '<TD>'.
+                       ( &FS::Report::FCC_477::statenum2state($_) || 'None' ).
+                     '</TD>'.
+                     '<TD>'.
+                       qq(<A HREF="${link}censustract2=$_$roa_r$roa_c$roa_t">).
+                       $state_pkgcount{$_}.
+                      ' packages</A> in '.
+                       $state_hash{$_}. ' census tracts'.
+                     '</TD>'.
+                   '</TR>';
+                 }
+                 keys %state_hash
+        ).
+    '</TABLE>';
+  } else { 
+    '';
+  }
+};
+
+</%init>
diff --git a/httemplate/search/old477/report_477.html b/httemplate/search/old477/report_477.html
new file mode 100755
index 000000000..a5dd70b7c
--- /dev/null
+++ b/httemplate/search/old477/report_477.html
@@ -0,0 +1,282 @@
+<% include('/elements/header.html', 'FCC Form 477 Report' ) %>
+
+<FORM ACTION="477.html" METHOD="GET">
+<INPUT TYPE="hidden" NAME="magic" VALUE="active">
+
+  <TABLE BGCOLOR="#cccccc" CELLSPACING=0>
+
+    <TR>
+      <TH CLASS="background" COLSPAN=2 ALIGN="left">
+        <FONT SIZE="+1">Search options</FONT>
+      </TH>
+    </TR>
+
+    <% include( '/elements/tr-select-agent.html',
+                   'curr_value'    => scalar( $cgi->param('agentnum') ),
+                   'disable_empty' => 0,
+               )
+    %>
+
+%   # not tr-select-state, we only want to choose from among those that 
+%   # have customers
+    <& /elements/tr-select-table.html,
+        'label'         => 'State',
+        'field'         => 'state',
+        'table'         => 'cust_location',
+        'name_col'      => 'state',
+        'value_col'     => 'state',
+        'disable_empty' => 1,
+        'records'       => \@states,
+    &>
+
+    <& /elements/tr-input-date-field.html, {
+        'label'         => 'As of date',
+        'name'          => 'date',
+        'value'         => '',
+        'format'        => '%m/%d/%Y'
+    } &>
+
+    <% include( '/elements/tr-select-pkg_class.html',
+                   'multiple'       => 1,
+                   'empty_label' => '(empty class)',
+               )
+    %>
+
+    <SCRIPT type="text/javascript">
+      function partchange(what) {
+        var id = 'part' + what.value;
+        var element = document.getElementById(id);
+        if (what.checked) {
+          element.style.display = '';
+        } else {
+          element.style.display = 'none';
+        }
+      }
+      function toggleV() {
+        document.getElementById('enableV').disabled =
+          ! (document.getElementById('enableIIA').checked ||
+             document.getElementById('enableIIB').checked);
+      }
+      function toggleVI() {
+        document.getElementById('enableVI').disabled =
+          ! document.getElementById('enableIA').checked;
+      }
+    </SCRIPT>
+
+    <% include( '/elements/tr-checkbox.html',
+                   'label' => 'Enable part IA?',
+                   'field' => 'part',
+                   'id'    => 'enableIA',
+                   'value' => 'IA',
+                   'onchange' => 'partchange(this); toggleVI();',
+               )
+    %>
+
+    <TR id='partIA' style="display:none"><TD>Part IA</TD><TD><TABLE>
+      <TR><TD>Download speeds</TD><TD>
+        <TABLE>
+%       my $i = 0;
+%       foreach my $speed ( @FS::Report::FCC_477::download ) {
+          <TR>
+            <TH><% $speed %></TH>
+            <TD>
+            <% include( '/elements/select-table.html',
+                           'table'        => 'part_pkg_report_option',
+                           'name_col'     => 'name',
+                           'hashref'      => { 'disabled' => '' },
+                           'element_name' => 'part1_column_option',
+                           'disable_empty' => 1,
+                           'curr_value'   =>
+                                FS::Report::FCC_477::restore_fcc477map("part1_column_option_$i"),
+                       )
+            %>
+            </TD>
+          </TR>
+%       $i++
+%       }
+        </TABLE></TD>
+      <TD>Upload speeds</TD><TD>
+        <TABLE>
+%       $i = 0;
+%       foreach my $speed ( @FS::Report::FCC_477::upload ) {
+          <TR>
+            <TH><% $speed %></TH>
+            <TD>
+            <% include( '/elements/select-table.html',
+                           'table'        => 'part_pkg_report_option',
+                           'name_col'     => 'name',
+                           'hashref'      => { 'disabled' => '' },
+                           'element_name' => 'part1_row_option',
+                           'disable_empty' => 1,
+                           'curr_value'   =>
+                                FS::Report::FCC_477::restore_fcc477map("part1_row_option_$i"),
+                       )
+            %>
+            </TD>
+          </TR>
+%       $i++
+%       }
+        </TABLE></TD></TR>
+      <TR><TD>Technologies</TD><TD>
+        <TABLE>
+%       $i = 0;
+%       foreach my $tech ( @FS::Report::FCC_477::technology ) {
+          <TR>
+            <TH><% $tech %></TH>
+            <TD>
+            <% include( '/elements/select-table.html',
+                           'table'        => 'part_pkg_report_option',
+                           'name_col'     => 'name',
+                           'hashref'      => { 'disabled' => '' },
+                           'element_name' => "part1_technology_option_$i",
+                           'empty_label'  => '(omit)',
+                           'curr_value'   =>
+                                FS::Report::FCC_477::restore_fcc477map("part1_technology_option_$i"),
+                       )
+            %>
+            </TD>
+          </TR>
+%       $i++
+%       }
+        </TABLE></TD></TR>
+    </TABLE></TD></TR>
+
+    <% include( '/elements/tr-checkbox.html',
+                   'label' => 'Enable part IIA?',
+                   'field' => 'part',
+                   'id'    => 'enableIIA',
+                   'value' => 'IIA',
+                   'onchange' => 'partchange(this); toggleV();',
+               )
+    %>
+
+    <TR id='partIIA' style="display:none"><TD>Part IIA</TD><TD><TABLE>
+%   $i = 0;
+%   foreach my $option ( @FS::Report::FCC_477::part2aoption ) {
+    <TR>
+      <TH><% $option %></TH>
+      <TD>
+      <% include( '/elements/select-table.html',
+                     'table'        => 'part_pkg_report_option',
+                     'name_col'     => 'name',
+                     'hashref'      => { 'disabled' => '' },
+                     'element_name' => 'part2a_row_option',
+                     'curr_value'   =>
+                           FS::Report::FCC_477::restore_fcc477map("part2a_row_option_$i"),
+                 )
+      %>
+      </TD>
+    </TR>
+%    $i++
+%   }
+  </TABLE></TD></TR>
+
+    <% include( '/elements/tr-checkbox.html',
+                   'label' => 'Enable part IIB?',
+                   'field' => 'part',
+                   'id'    => 'enableIIB',
+                   'value' => 'IIB',
+                   'onchange' => 'partchange(this); toggleV();',
+               )
+    %>
+
+    <TR id='partIIB' style="display:none"><TD>Part IIB</TD><TD><TABLE>
+%   $i = 0;
+%   foreach my $option ( @FS::Report::FCC_477::part2boption ) {
+    <TR>
+      <TH><% $option %></TH>
+      <TD>
+      <% include( '/elements/select-table.html',
+                     'table'        => 'part_pkg_report_option',
+                     'name_col'     => 'name',
+                     'hashref'      => { 'disabled' => '' },
+                     'element_name' => 'part2b_row_option',
+                      'curr_value'   =>
+                            FS::Report::FCC_477::restore_fcc477map("part2b_row_option_$i"),
+                 )
+      %>
+      </TD>
+    </TR>
+%    $i++
+%   }
+  </TABLE></TD></TR>
+
+    <% include( '/elements/tr-checkbox.html',
+                   'label' => 'Enable part IV?',
+                   'field' => 'part',
+                   'id'    => 'enableIV', #unused
+                   'value' => 'IV',
+                   'onchange' => 'partchange(this)',
+               )
+    %>
+
+    <TR id='partIV' style="display:none"><TD>Part IV</TD><TD><TABLE>
+    <% include( '/elements/tr-textarea.html',
+                   'label'        => 'Explanatory notes',
+                   'id'           => 'partIV',
+                   'field'         => 'notes',
+                   'rows'         => 15,
+                   'cols'         => 80,
+               )
+    %>
+  </TABLE></TD></TR>
+
+    <% include( '/elements/tr-checkbox.html',
+                   'label' => 'Enable part V?',
+                   'field' => 'part',
+                   'value' => 'V',
+                   'id'    => 'enableV',
+                   'onchange' => 'partchange(this)',
+                   'postfix'  => 
+                    '&nbsp;<FONT SIZE="-1">(requires Part IIA or IIB)</FONT>',
+               )
+    %>
+    <TR id='partV' style="display:none">
+        <TD>Part V</TD>
+        <TD>
+            <% include( '/elements/select-table.html',
+                     'table'        => 'part_pkg_report_option',
+                     'name_col'     => 'name',
+                     'hashref'      => { 'disabled' => '' },
+                     'element_name' => 'part5_report_option',
+                     'curr_value'   =>
+                            FS::Report::FCC_477::restore_fcc477map("part5_report_option"),
+                 )
+            %>
+        </TD>
+    </TR>
+
+
+    <% include( '/elements/tr-checkbox.html',
+                   'label' => 'Enable part VI?',
+                   'field' => 'part',
+                   'id'    => 'enableVI',
+                   'value' => 'VI_census',
+                   'postfix'  =>
+                    '&nbsp;<FONT SIZE="-1">(requires part IA)</FONT>',
+               )
+    %>
+  <SCRIPT TYPE="text/javascript">
+  toggleV();
+  toggleVI();
+  </SCRIPT>
+  </TABLE>
+
+<BR>
+<INPUT TYPE="submit" VALUE="Get Report">
+
+</FORM>
+
+<% include('/elements/footer.html') %>
+<%init>
+
+die "access denied"
+  unless $FS::CurrentUser::CurrentUser->access_right('List packages');
+
+my @states = qsearch({
+  'table'   => 'cust_location',
+  'select'  => 'DISTINCT(state)',
+  'hashref' => { 'country' => 'US' }, # 477 report isn't relevant elsewhere
+});
+
+</%init>