From f8c8b9782ff5400790c2fb6dae017ce01790e56e Mon Sep 17 00:00:00 2001 From: Mark Wells Date: Thu, 6 Sep 2012 11:47:02 -0700 Subject: [PATCH] separate all 477 reports by state, #18503 --- FS/FS/cust_pkg.pm | 37 ++++++++++++++-------- httemplate/search/477.html | 54 ++++++++++++++------------------ httemplate/search/477partIA_detail.html | 3 +- httemplate/search/477partIA_summary.html | 3 +- httemplate/search/477partIIA.html | 3 +- httemplate/search/477partV.html | 3 +- httemplate/search/477partVI_census.html | 10 +++--- httemplate/search/report_477.html | 18 +++++++++++ 8 files changed, 80 insertions(+), 51 deletions(-) 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/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' ) { -%} -% if ( $type eq 'html' || $type eq 'html-print' ) { +% } else { #html +<& /elements/header.html, "FCC Form 477 Results - $state" &> - -%}elsif ( $type eq 'xml' ) { -%} -% unless ( $type eq 'html-print' || $type eq 'xml' ) { + + + -% $cgi->param('_type', $type ); -% } -% if ( $type eq 'html' || $type eq 'html-print' ) { + +% $cgi->param('_type', $type );
+ Download full results
+% $cgi->param('_type', 'xml'); + as XML file
- 		+% $cgi->param('_type', 'html-print'); + as printable copy - Download full results
-% $cgi->param('_type', 'xml'); - as XML file
- -% $cgi->param('_type', 'html-print'); - as printable copy - -
-%}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' ) { > % } @@ -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' ) { > % } @@ -66,11 +57,11 @@ % } % } % -% if ( $type eq 'html' || $type eq 'html-print' ) { -<% include( '/elements/footer.html') %> -%}elsif ( $type eq 'xml' ) { +% if ( $type eq 'xml' ) { -%} +% } 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 = '

Part IIA

'; 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 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 +}); + -- 2.11.0