separate all 477 reports by state, #18503

diff --git a/FS/FS/cust_pkg.pm b/FS/FS/cust_pkg.pm
index ea29a2c..c34eb43 100644 (file)
--- 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/httemplate/search/477.html b/httemplate/search/477.html
index 250e718..6f5fcdf 100755 (executable)
--- 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 2eca107..66f3a86 100755 (executable)
--- 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 ecacaef..f5c2bc2 100755 (executable)
--- 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 9b363ad..d2cc8c3 100755 (executable)
--- 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/477partV.html b/httemplate/search/477partV.html
index 1dedf1b..2fd5119 100755 (executable)
--- 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 0789342..8425c4b 100755 (executable)
--- 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 c9d97c5..f593a94 100755 (executable)
--- 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>