From 0956674f4acf255ea9f73c52048b727b81c4e555 Mon Sep 17 00:00:00 2001 From: Mark Wells Date: Thu, 6 Sep 2012 11:43:20 -0700 Subject: [PATCH] separate all 477 reports by state, #18503 --- FS/FS/cust_pkg.pm | 27 ++++++++++++++-- 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 | 6 ++-- httemplate/search/report_477.html | 32 +++++++++++++++++++ 8 files changed, 92 insertions(+), 39 deletions(-) diff --git a/FS/FS/cust_pkg.pm b/FS/FS/cust_pkg.pm index 586abcb94..bab2039eb 100644 --- a/FS/FS/cust_pkg.pm +++ b/FS/FS/cust_pkg.pm @@ -3206,7 +3206,14 @@ 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 whose customer is located in the specified state and +country. For FCC 477 reporting. This will use the customer's service +address if there is one, but isn't yet smart enough to use the package +address. =back @@ -3401,6 +3408,20 @@ sub search { } ### + # parse country/state + ### + + for (qw(state country)) { + if ( exists($params->{$_}) + && uc($params->{$_}) =~ /^([A-Z]{2})$/ ) + { + push @where, + "COALESCE(cust_location.$_, cust_main.ship_$_, cust_main.$_) = '$1'"; + } + } + + + ### # parse part_pkg ### @@ -3524,7 +3545,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; @@ -3533,7 +3555,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 ( 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 a7dc2eeb2..2b20bc790 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..cb8cf23a6 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; @@ -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 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..24fee0527 100755 --- a/httemplate/search/report_477.html +++ b/httemplate/search/report_477.html @@ -17,6 +17,15 @@ ) %> + <& /elements/tr-td-label.html, 'label' => 'State' &> + + + + <% include( '/elements/tr-select-pkg_class.html', 'multiple' => 1, 'empty_label' => '(empty class)', @@ -252,4 +261,27 @@ die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('List packages'); +my @states = ( + qsearch({ + 'table' => 'cust_main', + 'select' => 'DISTINCT(state)', + 'hashref' => { 'country' => 'US' }, # 477 report isn't relevant elsewhere + }), + qsearch({ + 'table' => 'cust_main', + 'select' => 'DISTINCT(ship_state) AS state', + 'hashref' => { 'country' => 'US' }, + }), + qsearch({ + 'table' => 'cust_location', + 'select' => 'DISTINCT(state)', + 'hashref' => { 'country' => 'US' }, + }), +); + +{ + my %seen = map {$_->get('state') => 1} @states; + @states = grep $_, sort keys(%seen); +} + -- 2.11.0