Merge branch 'master' of git.freeside.biz:/home/git/freeside

16 files changed, 254 insertions, 128 deletions

diff --git a/FS/FS/Conf.pm b/FS/FS/Conf.pm
index 321ba0b89..b72cf0313 100644
--- a/FS/FS/Conf.pm
+++ b/FS/FS/Conf.pm
@@ -3303,7 +3303,7 @@ and customer address. Include units.',
   {
     'key'         => 'cust_pkg-show_fcc_voice_grade_equivalent',
     'section'     => 'UI',
-    'description' => "Show a field on package definitions for assigning a DS0 equivalency number suitable for use on FCC form 477.",
+    'description' => "Show fields on package definitions for FCC Form 477 classification",
     'type'        => 'checkbox',
   },
 
diff --git a/FS/FS/Mason.pm b/FS/FS/Mason.pm
index b3c2d4a5b..39c7dfdc6 100644
--- a/FS/FS/Mason.pm
+++ b/FS/FS/Mason.pm
@@ -93,7 +93,7 @@ if ( -e $addl_handler_use_file ) {
   use Spreadsheet::WriteExcel::Utility;
   use OLE::Storage_Lite;
   use Excel::Writer::XLSX;
-  use Excel::Writer::XLSX::Utility;
+  #use Excel::Writer::XLSX::Utility; #redundant with above
 
   use Business::CreditCard 0.30; #for mask-aware cardtype()
   use NetAddr::IP;
diff --git a/FS/FS/Report/FCC_477.pm b/FS/FS/Report/FCC_477.pm
index 4c94fff2e..49bb8a852 100644
--- a/FS/FS/Report/FCC_477.pm
+++ b/FS/FS/Report/FCC_477.pm
@@ -45,8 +45,8 @@ Documentation.
 );
 
 @technology = (
-  'Asymetric xDSL',
-  'Symetric xDSL',
+  'Asymmetric xDSL',
+  'Symmetric xDSL',
   'Other Wireline',
   'Cable Modem',
   'Optical Carrier',
diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm
index b1e7a9c5d..37bba68cb 100644
--- a/FS/FS/Schema.pm
+++ b/FS/FS/Schema.pm
@@ -1912,6 +1912,7 @@ sub tables_hashref {
         'credit_weight', 'real',    'NULL', '', '', '',
         'agentnum',      'int',     'NULL', '', '', '', 
         'fcc_ds0s',      'int',     'NULL', '', '', '', 
+        'fcc_voip_class','char',    'NULL',  1, '', '',
         'no_auto',          'char', 'NULL',  1, '', '', 
         'recur_show_zero',  'char', 'NULL',  1, '', '',
         'setup_show_zero',  'char', 'NULL',  1, '', '',
diff --git a/FS/FS/cust_pkg.pm b/FS/FS/cust_pkg.pm
index ea29a2c68..c34eb43b5 100644
--- a/FS/FS/cust_pkg.pm
+++ b/FS/FS/cust_pkg.pm
@@ -3317,7 +3317,12 @@ specifies the user for agent virtualization
 
 =item fcc_line
 
- boolean selects packages containing fcc form 477 telco lines
+boolean; if true, returns only packages with more than 0 FCC phone lines.
+
+=item state, country
+
+Limit to packages with a service location in the specified state and country.
+For FCC 477 reporting, mostly.
 
 =back
 
@@ -3491,8 +3496,8 @@ sub search {
 
   if ( exists($params->{'censustract'}) ) {
     $params->{'censustract'} =~ /^([.\d]*)$/;
-    my $censustract = "cust_main.censustract = '$1'";
-    $censustract .= ' OR cust_main.censustract is NULL' unless $1;
+    my $censustract = "cust_location.censustract = '$1'";
+    $censustract .= ' OR cust_location.censustract is NULL' unless $1;
     push @where,  "( $censustract )";
   }
 
@@ -3504,10 +3509,22 @@ sub search {
      )
   {
     if ($1) {
-      push @where, "cust_main.censustract LIKE '$1%'";
+      push @where, "cust_location.censustract LIKE '$1%'";
     } else {
       push @where,
-        "( cust_main.censustract = '' OR cust_main.censustract IS NULL )";
+        "( cust_location.censustract = '' OR cust_location.censustract IS NULL )";
+    }
+  }
+
+  ###
+  # parse country/state
+  ###
+  for (qw(state country)) { # parsing rules are the same for these
+  if ( exists($params->{$_}) 
+    && uc($params->{$_}) =~ /^([A-Z]{2})$/ )
+    {
+      # XXX post-2.3 only--before that, state/country may be in cust_main
+      push @where, "cust_location.$_ = '$1'";
     }
   }
 
@@ -3635,7 +3652,8 @@ sub search {
 
   my $addl_from = 'LEFT JOIN cust_main USING ( custnum  ) '.
                   'LEFT JOIN part_pkg  USING ( pkgpart  ) '.
-                  'LEFT JOIN pkg_class ON ( part_pkg.classnum = pkg_class.classnum ) ';
+                  'LEFT JOIN pkg_class ON ( part_pkg.classnum = pkg_class.classnum ) '.
+                  'LEFT JOIN cust_location USING ( locationnum ) ';
 
   my $select;
   my $count_query;
@@ -3644,13 +3662,6 @@ sub search {
 
     $select = "DISTINCT substr($zip,1,5) as zip";
     $orderby = "ORDER BY substr($zip,1,5)";
-    $addl_from .= 'LEFT JOIN cust_location ON (
-                     cust_location.locationnum = COALESCE(
-                                                   cust_pkg.locationnum,
-                                                   cust_main.ship_locationnum,
-                                                   cust_main.bill_locationnum
-                                                 )
-                                              )';
     $count_query = "SELECT COUNT( DISTINCT substr($zip,1,5) )";
   } else {
     $select = join(', ',
diff --git a/FS/FS/part_pkg.pm b/FS/FS/part_pkg.pm
index 061001bdc..91bcdc5b5 100644
--- a/FS/FS/part_pkg.pm
+++ b/FS/FS/part_pkg.pm
@@ -103,6 +103,9 @@ inherits from FS::Record.  The following fields are currently supported:
 
 =item fcc_ds0s - Optional DS0 equivalency number for FCC form 477
 
+=item fcc_voip_class - Which column of FCC form 477 part II.B this package 
+belongs in.
+
 =item successor - Foreign key for the part_pkg that replaced this record.
 If this record is not obsolete, will be null.
 
@@ -622,6 +625,7 @@ sub check {
            : $self->ut_agentnum_acl('agentnum', \@null_agentnum_right)
        )
     || $self->ut_numbern('fcc_ds0s')
+    || $self->ut_numbern('fcc_voip_class')
     || $self->ut_foreign_keyn('successor', 'part_pkg', 'pkgpart')
     || $self->ut_foreign_keyn('family_pkgpart', 'part_pkg', 'pkgpart')
     || $self->SUPER::check
@@ -1592,6 +1596,22 @@ sub _upgrade_data { # class method
         }
   }
 
+  # set any package with FCC voice lines to the "VoIP with broadband" category
+  # for backward compatibility
+  my $journal = 'part_pkg_fcc_voip_class';
+  if (!FS::upgrade_journal->is_done($journal)) {
+    @part_pkg = qsearch('part_pkg', { 
+        fcc_ds0s        => { op => '>', value => 0 },
+        fcc_voip_class  => ''
+    });
+    foreach my $part_pkg (@part_pkg) {
+      $part_pkg->set(fcc_voip_class => 2);
+      my $error = $part_pkg->replace;
+      die $error if $error;
+    }
+    FS::upgrade_journal->set_done($journal);
+  }
+
 }
 
 =item curuser_pkgs_sql
diff --git a/httemplate/edit/part_pkg.cgi b/httemplate/edit/part_pkg.cgi
index cd0731370..f3ad8f52d 100755
--- a/httemplate/edit/part_pkg.cgi
+++ b/httemplate/edit/part_pkg.cgi
@@ -55,6 +55,7 @@
                             'svc_dst_pkgpart'  => 'Include services of package',
                             'report_option'    => 'Report classes',
                             'fcc_ds0s'         => 'Voice-grade equivalents',
+                            'fcc_voip_class'   => 'Category',
                           },
 
               'fields' => [
@@ -196,6 +197,9 @@
                                     { type  => 'tablebreak-tr-title',
                                       value => 'FCC Form 477 information',
                                     },
+                                    { field=>'fcc_voip_class',
+                                      type=>'select-voip_class',
+                                    },
                                     { field=>'fcc_ds0s', type=>'text', size=>6 },
                                   )
                                  : ()
diff --git a/httemplate/elements/tr-select-voip_class.html b/httemplate/elements/tr-select-voip_class.html
new file mode 100644
index 000000000..dcc1487cc
--- /dev/null
+++ b/httemplate/elements/tr-select-voip_class.html
@@ -0,0 +1,24 @@
+<& tr-td-label.html, label => 'Category', @_ &>
+<TD>
+<SELECT NAME="<% $opt{'field'} %>">
+% while(@options) {
+%   my $value = shift @options;
+%   my $selected = ($value eq $opt{'curr_value'}) ? 'SELECTED' : '';
+  <OPTION VALUE="<% $value %>" <% $selected %>><% shift @options %></OPTION>
+% }
+</SELECT>
+</TD></TR>
+<%init>
+my %opt = (
+  field => 'fcc_voip_class',
+  label => 'Category',
+  @_
+);
+my @options = (
+  '' => '',
+   1 => 'VoIP without Broadband',
+   2 => 'VoIP with Broadband',
+   3 => 'Wholesale VoIP'
+);
+
+</%init>
diff --git a/httemplate/search/477.html b/httemplate/search/477.html
index 250e71811..6f5fcdf3b 100755
--- a/httemplate/search/477.html
+++ b/httemplate/search/477.html
@@ -1,33 +1,24 @@
-% unless ( $type eq 'xml' ) {
-<% include( '/elements/header.html', 'FCC Form 477 Results') %>
-%}else{
+% if ( $type eq 'xml' ) {
 <?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" >
-%}
-% if ( $type eq 'html' || $type eq 'html-print' ) {
+% } else { #html
+<& /elements/header.html, "FCC Form 477 Results - $state" &>
 <TABLE WIDTH="100%">
-  <TR><TD></TD>
-%}elsif ( $type eq 'xml' ) {
-%}
-%           unless ( $type eq 'html-print' || $type eq 'xml' ) {
+  <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>
 
-              <TD ALIGN="right">
+%   $cgi->param('_type', 'html-print');
+      as <A HREF="<% $cgi->self_url %>">printable copy</A>
 
-                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 );
-%           }
-% if ( $type eq 'html' || $type eq 'html-print' ) {
+    </TD>
+%   $cgi->param('_type', $type );
   </TR>
 </TABLE>
-%}elsif ( $type eq 'xml' ) {
-%}
+% } #html
 % foreach my $part ( @parts ) {
 %   if ( $part{$part} ) {
 %
@@ -47,8 +38,8 @@
 %         if ( $type eq 'xml' ) {
 <<% 'Part_IA_'. chr(65 + $tech) %>>
 %         }
-<% include( "477part${part}_summary.html", 'tech_code' => $tech, 'url' => $url ) %>
-<% include( "477part${part}_detail.html", 'tech_code' => $tech, 'url' => $url ) %>
+<& "477part${part}_summary.html", 'tech_code' => $tech, 'url' => $url &>
+<& "477part${part}_detail.html", 'tech_code' => $tech, 'url' => $url &>
 %         if ( $type eq 'xml' ) {
 </<% 'Part_IA_'. chr(65 + $tech) %>>
 %         }
@@ -58,7 +49,7 @@
 <<% 'Part_'. $part %>>
 %       }
 %       my $url = &{$url_mangler}($part);
-<% include( "477part${part}.html", 'url' => $url ) %>
+<& "477part${part}.html", 'url' => $url &>
 %       if ( $type eq 'xml' ) {
 </<% 'Part_'. $part %>>
 %       }
@@ -66,11 +57,11 @@
 %   }
 % }
 %
-% if ( $type eq 'html' || $type eq 'html-print' ) {
-<% include( '/elements/footer.html') %>
-%}elsif ( $type eq 'xml' ) {
+% if ( $type eq 'xml' ) {
 </Form_477_submission>
-%}
+% } else {
+<& /elements/footer.html &>
+% }
 <%init>
 
 my $curuser = $FS::CurrentUser::CurrentUser;
@@ -78,6 +69,9 @@ my $curuser = $FS::CurrentUser::CurrentUser;
 die "access denied"
   unless $curuser->access_right('List packages');
 
+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 $xlsname = '477report';
diff --git a/httemplate/search/477partIA_detail.html b/httemplate/search/477partIA_detail.html
index 2eca1072b..66f3a8651 100755
--- a/httemplate/search/477partIA_detail.html
+++ b/httemplate/search/477partIA_detail.html
@@ -23,9 +23,10 @@ die "access denied"
 my %opt = @_;
 my %search_hash = ();
   
-for ( qw(agentnum magic) ) {
+for ( qw(agentnum magic state) ) {
   $search_hash{$_} = $cgi->param($_) if $cgi->param($_);
 }
+$search_hash{'country'} = 'US';
 
 $search_hash{'classnum'} = [ $cgi->param('classnum') ];
 
diff --git a/httemplate/search/477partIA_summary.html b/httemplate/search/477partIA_summary.html
index ecacaefad..f5c2bc251 100755
--- a/httemplate/search/477partIA_summary.html
+++ b/httemplate/search/477partIA_summary.html
@@ -40,9 +40,10 @@ die "access denied"
 my %opt = @_;
 my %search_hash = ();
   
-for ( qw(agentnum magic) ) {
+for ( qw(agentnum magic state) ) {
   $search_hash{$_} = $cgi->param($_) if $cgi->param($_);
 }
+$search_hash{'country'} = 'US';
 $search_hash{'classnum'} = [ $cgi->param('classnum') ];
 
 my @column_option = grep { /^\d+$/ } $cgi->param('part1_column_option')
diff --git a/httemplate/search/477partIIA.html b/httemplate/search/477partIIA.html
index 9b363ad5e..d2cc8c3e9 100755
--- a/httemplate/search/477partIIA.html
+++ b/httemplate/search/477partIIA.html
@@ -22,9 +22,10 @@ die "access denied"
 my $html_init = '<H2>Part IIA</H2>';
 my %search_hash = ();
   
-for ( qw(agentnum magic) ) {
+for ( qw(agentnum magic state) ) {
   $search_hash{$_} = $cgi->param($_) if $cgi->param($_);
 }
+$search_hash{'country'} = 'US';
 $search_hash{'classnum'} = [ $cgi->param('classnum') ];
 
 my @row_option = grep { /^\d+$/ } $cgi->param('part2a_row_option')
diff --git a/httemplate/search/477partIIB.html b/httemplate/search/477partIIB.html
index 94aa818fb..c58310d36 100755
--- a/httemplate/search/477partIIB.html
+++ b/httemplate/search/477partIIB.html
@@ -1,17 +1,44 @@
-<% include( 'elements/search.html',
-                  'html_init'        => $html_init,
-                  'name'             => 'lines',
-                  'query'            => $query,
-                  'count_query'      => 'SELECT 11',
-                  'really_disable_download' => 1,
-                  'disable_download' => 1,
-                  'nohtmlheader'     => 1,
-                  'disable_total'    => 1,
-                  'header'           => [ @headers ],
-                  'xml_elements'     => [ @xml_elements ],
-                  'fields'           => [ @fields ],
-              )
-%>
+% 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 %><% $cols[$col] %>>
+%       }
+</PartII_<% $row %><% $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 STYLE="border: 1px solid #cccccc;" BGCOLOR="#cccccc">
+    <TR>
+% foreach (@headers) {
+      <TH class="grid"><% $_ %></TH>
+% }
+    </TR>
+% my @bgcolor = ('eeeeee','ffffff');
+% my $row = 0;
+% foreach my $rowhead (@rows) {
+    <TR> 
+      <TD CLASS="grid" BGCOLOR="#<% $bgcolor[$row % 2] %>"><% $rowhead %></TD>
+%     for my $col (0..2) {
+      <TD CLASS="grid" BGCOLOR="#<% $bgcolor[$row % 2] %>">
+%       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;
@@ -19,67 +46,89 @@ my $curuser = $FS::CurrentUser::CurrentUser;
 die "access denied"
   unless $curuser->access_right('List packages');
 
-my $html_init = '<H2>Part IIB</H2>';
 my %search_hash = ();
-  
-for ( qw(agentnum magic) ) {
-  $search_hash{$_} = $cgi->param($_) if $cgi->param($_);
-}
-$search_hash{'classnum'} = [ $cgi->param('classnum') ];
-
-my @row_option = grep { /^\d+$/ } $cgi->param('part2b_row_option')
-  if $cgi->param('part2b_row_option');
-
-# fudge in 2nd row
-unshift @row_option, $row_option[0];
-
-my $query = 'SELECT '. join(' UNION SELECT ', 1..8);
-
-my $total_count = 0;
-my $column_value = sub {
-  my $row = shift;
-
-  my @report_option = ( $row_option[$row - 1] || '' );
 
-  my $sql_query = FS::cust_pkg->search(
-    { %search_hash, 'report_option' => join(',', @report_option) }
-  );
-
-  my $count_sql = delete($sql_query->{'count_query'});
-  if ( $row == 2 ) {
-    $count_sql =~ s/COUNT\(\*\) FROM/sum(COALESCE(CASE WHEN cust_main.company IS NULL OR cust_main.company = '' THEN   CASE WHEN part_pkg.fcc_ds0s IS NOT NULL AND part_pkg.fcc_ds0s > 0 THEN part_pkg.fcc_ds0s WHEN pkg_class.fcc_ds0s IS NOT NULL AND pkg_class.fcc_ds0s > 0 THEN pkg_class.fcc_ds0s ELSE 0 END   ELSE 0 END, 0) ) FROM/
-      or die "couldn't parse count_sql";
-  } else {
-    $count_sql =~ s/COUNT\(\*\) FROM/sum(COALESCE(CASE WHEN part_pkg.fcc_ds0s IS NOT NULL AND part_pkg.fcc_ds0s > 0 THEN part_pkg.fcc_ds0s WHEN pkg_class.fcc_ds0s IS NOT NULL AND pkg_class.fcc_ds0s > 0 THEN pkg_class.fcc_ds0s ELSE 0 END, 0)) FROM/
-      or die "couldn't parse count_sql";
-  }
-
-  my $count_sth = dbh->prepare($count_sql)
-    or die "Error preparing $count_sql: ". dbh->errstr;
-  $count_sth->execute
-    or die "Error executing $count_sql: ". $count_sth->errstr;
-  my $count_arrayref = $count_sth->fetchrow_arrayref;
-  my $count = $count_arrayref->[0];
+$search_hash{'agentnum'} = $cgi->param('agentnum');
+$search_hash{'state'}    = $cgi->param('state');
+$search_hash{'classnum'} = [ $cgi->param('classnum') ];
+$search_hash{'status'}   = 'active';
 
-  $total_count = $count if $row == 1;
-  $count = sprintf('%.2f', $total_count ? 100*$count/$total_count : 0)
-    if $row != 1;
+my @row_option;
+foreach ($cgi->param('part2b_row_option')) {
+  push @row_option, (/^\d+$/ ? $_ : undef);
+}
 
-  return "$count";
+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'
+  } @_
 };
 
-my @headers = (
-  '',
-  'without broadband',
-  'with broadband',
-  'wholesale',
+# 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 @xml_elements = (
-  sub { my $row = shift; my $rownum = $row->[0] + 1; "PartII_${rownum}a" },
-  sub { my $row = shift; my $rownum = $row->[0] + 1; "PartII_${rownum}b" },
-  sub { my $row = shift; my $rownum = $row->[0] + 1; "PartII_${rownum}c" },
-);
+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 {
+      if ( $col_data[0] == 0 ) {
+        $col_data[$row] = ''; # show nothing in this row, then
+      } else {
+        $col_data[$row] = sprintf('%.2f', 100 * $count / $col_data[0]) . '%';
+      }
+    } #if $row == 0
+    $row++;
+  }
+  $data[$col] = \@col_data;
+  $col++;
+}
+
 
 my @rows = (
   'total number',
@@ -92,12 +141,11 @@ my @rows = (
   '% other broadband',
 );
 
-my @fields = (
-  sub { my $row = shift; $rows[$row->[0] - 1]; },
-  sub { 0; },
-  sub { my $row = shift; &{$column_value}($row->[0]); },
-  sub { 0; },
+my @headers = (
+  '',
+  'without broadband',
+  'with broadband',
+  'wholesale',
 );
 
-shift @fields if $cgi->param('_type') eq 'xml';
 </%init>
diff --git a/httemplate/search/477partV.html b/httemplate/search/477partV.html
index 1dedf1b76..2fd5119d1 100755
--- a/httemplate/search/477partV.html
+++ b/httemplate/search/477partV.html
@@ -27,9 +27,10 @@ my %search_hash = ();
 my @sql_query = ();
 my @count_query = ();
   
-for ( qw(agentnum magic) ) {
+for ( qw(agentnum magic state) ) {
   $search_hash{$_} = $cgi->param($_) if $cgi->param($_);
 }
+$search_hash{'country'} = 'US';
 $search_hash{'classnum'} = [ $cgi->param('classnum') ];
 $search_hash{report_option} = $cgi->param('partv_report_option')
   if $cgi->param('partv_report_option');
diff --git a/httemplate/search/477partVI_census.html b/httemplate/search/477partVI_census.html
index 078934284..8425c4b48 100755
--- a/httemplate/search/477partVI_census.html
+++ b/httemplate/search/477partVI_census.html
@@ -81,9 +81,10 @@ push @fields,
 my %search_hash = ();
 my @sql_query = ();
   
-for ( qw(agentnum magic) ) {
+for ( qw(agentnum magic state) ) {
   $search_hash{$_} = $cgi->param($_) if $cgi->param($_);
 }
+$search_hash{'country'} = 'US';
 $search_hash{'classnum'} = [ $cgi->param('classnum') ]
   if grep { $_ eq 'classnum' } $cgi->param;
 
@@ -116,10 +117,10 @@ foreach my $row ( @row_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, censustract, $percent";
+      $sql_query->{select} = "count(*) AS quantity, $extracolumns, cust_location.censustract, $percent";
       $sql_query->{order_by} =~ /^(.*)(ORDER BY pkgnum)(.*)$/s
         or die "couldn't parse order_by";
-      $sql_query->{order_by} = "$1 GROUP BY censustract $3";
+      $sql_query->{order_by} = "$1 GROUP BY cust_location.censustract $3";
       push @sql_query, $sql_query;
     }
     $columncount++;
@@ -132,7 +133,8 @@ my $count_query = 'SELECT count(*) FROM ( ('.
       map { my $addl_from = $_->{addl_from};
             my $extra_sql = $_->{extra_sql};
             my $order_by  = $_->{order_by};
-            "SELECT censustract from cust_pkg $addl_from $extra_sql $order_by";
+            "SELECT cust_location.censustract from cust_pkg $addl_from 
+            $extra_sql $order_by";
           }
       @sql_query
    ). ') ) AS foo';
diff --git a/httemplate/search/report_477.html b/httemplate/search/report_477.html
index c9d97c5eb..f593a94d8 100755
--- a/httemplate/search/report_477.html
+++ b/httemplate/search/report_477.html
@@ -17,6 +17,18 @@
                )
     %>
 
+%   # 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,
+    &>
+
     <% include( '/elements/tr-select-pkg_class.html',
                    'multiple'       => 1,
                    'empty_label' => '(empty class)',
@@ -252,4 +264,10 @@
 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>