From 1a8d05e7a4345862e9ffb71d00bb07dd178017f0 Mon Sep 17 00:00:00 2001 From: jeff Date: Tue, 25 Aug 2009 19:08:19 +0000 Subject: [PATCH] improved 477 report #6004 --- httemplate/search/477.html | 139 ++++++++++++++++++++++++++------- httemplate/search/elements/search.html | 29 ++++--- httemplate/search/report_477.html | 18 ++++- 3 files changed, 146 insertions(+), 40 deletions(-) diff --git a/httemplate/search/477.html b/httemplate/search/477.html index 45c92ffe7..9102c2083 100755 --- a/httemplate/search/477.html +++ b/httemplate/search/477.html @@ -2,25 +2,38 @@ 'title' => 'FCC Form 477 Results', 'html_init' => $html_init, 'name' => 'regions', - 'query' => $sql_query, - 'count_query' => $count_query, + 'query' => [ @sql_query ], + 'count_query' => $count_query, + 'order_by' => 'ORDER BY censustract', 'header' => [ - 'State code', 'County code', 'Census tract code', + 'Upload rate', + 'Download rate', + 'Technology code', + 'Technology code other', 'Quantity', + 'Percentage residential', ], 'fields' => [ - sub { my $row = shift; substr($row->censustract, 0, 2) }, sub { my $row = shift; substr($row->censustract, 2, 3) }, sub { my $row = shift; substr($row->censustract, 5) }, + 'upload', + 'download', + sub { 7 }, + sub { '' }, 'quantity', + sub { my $row = shift; sprintf "%.2f", $row->residential }, ], 'links' => [ - [ $link, 'censustract' ], - [ $link, 'censustract' ], - [ $link, 'censustract' ], - [ $link, 'censustract' ], + [ $link, $link_suffix ], + [ $link, $link_suffix ], + [ $link, $link_suffix ], + [ $link, $link_suffix ], + [ $link, $link_suffix ], + [ $link, $link_suffix ], + [ $link, $link_suffix ], + [ $link, $link_suffix ], ], ) %> @@ -32,39 +45,111 @@ die "access denied" unless $curuser->access_right('List packages'); my %search_hash = (); +my @sql_query = (); -$search_hash{'query'} = $cgi->keywords; - -for ( qw(agentnum magic status classnum) ) { +for ( qw(agentnum magic classnum) ) { $search_hash{$_} = $cgi->param($_) if $cgi->param($_); } +my @column_option = $cgi->param('column_option') + if $cgi->param('column_option'); -my @report_option = $cgi->param('report_option') - if $cgi->param('report_option'); -$search_hash{report_option} = join(',', @report_option) if @report_option; +my @row_option = $cgi->param('row_option') + if $cgi->param('row_option'); -my $where = join(' OR ', map { "num = $_" } grep { /^\d+$/ } @report_option ); -my @report_option_name = $where ? - ( map { $_->name } +my $where = join(' OR ', map { "num = $_" } grep { /^\d+$/ } @column_option ); +my %column_option_name = $where ? + ( map { $_->name => $_->num } qsearch({ 'table' => 'part_pkg_report_option', 'hashref' => {}, 'extra_sql' => "WHERE $where", }) ) : - ( 'all packages' ); + ( 'all packages' => '' ); + +$where = join(' OR ', map { "num = $_" } grep { /^\d+$/ } @row_option ); +my %row_option_name = $where ? + ( map { $_->name => $_->num } + qsearch({ 'table' => 'part_pkg_report_option', + 'hashref' => {}, + 'extra_sql' => "WHERE $where", + }) + ) : + ( 'all packages' => '' ); + +@row_option = map { $row_option_name{$_} } sort keys %row_option_name; +@column_option = map { $column_option_name{$_} } sort keys %column_option_name; + +#$search_hash{row_option} = join(',', @row_option) if @row_option; +my $html_init = '

Summary

'. include('/elements/table.html'); + $html_init .= ''; +foreach my $column ( sort keys %column_option_name ) { + $html_init .= "$column"; +} + $html_init .= ""; + +my $rowcount = 1; +foreach my $row ( sort keys %row_option_name ) { -my $html_init = "For
". join(' and
', @report_option_name). "
"; + $html_init .= "$row"; -my $sql_query = FS::cust_pkg->search_sql(\%search_hash); + my $columncount = 2; + foreach my $column ( sort keys %column_option_name ) { + my @report_option = (); + push @report_option, $row_option_name{$row} + if $row_option_name{$row}; + push @report_option, $column_option_name{$column} + if $column_option_name{$column}; + my $report_option = join(',', @report_option) if @report_option; + + my $sql_query = FS::cust_pkg->search_sql( + { %search_hash, + ($report_option ? ( 'report_option' => $report_option ) : () ), + } + ); + my $extracolumns = "$rowcount AS upload, $columncount AS download"; + my $percent = "100-100*cast(count(cust_main.company) as numeric)/cast(count(*) as numeric) AS residential"; + $sql_query->{select} = "count(*) AS quantity, $extracolumns, censustract, $percent"; + $sql_query->{extra_sql} =~ /^(.*)(ORDER BY pkgnum)(.*)$/s + or die "couldn't parse extra_sql"; + $sql_query->{extra_sql} = "$1 GROUP BY censustract $3"; + + my $count_sql = delete($sql_query->{'count_query'}); + + 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]; + + $html_init .= "$count"; + push @sql_query, $sql_query; + $columncount++; + } + + $html_init .= ""; + $rowcount++; +} -$sql_query->{select} = 'count(*) as quantity, censustract'; -$sql_query->{extra_sql} =~ /^(.*)(ORDER BY bill)(.*)$/s or die "couldn't parse extra_sql"; -$sql_query->{extra_sql} = "$1 GROUP BY censustract $3"; +$html_init .= "

Details

"; -my $count_query = delete($sql_query->{'count_query'}); -$count_query = 'SELECT count(*) FROM (SELECT count(*) FROM cust_pkg '. - $sql_query->{addl_from}. ' '. $sql_query->{extra_sql}. ') AS foo'; +my $count_query = 'SELECT count(*) FROM ( ('. + join( ') UNION (', + map { my $extra = $_->{extra_sql}; my $addl = $_->{addl_from}; + "SELECT censustract from cust_pkg $addl $extra"; + } + @sql_query + ). ') ) AS foo'; -my $link = 'cust_pkg.cgi?'. $cgi->query_string. ';censustract='; +my $link = 'cust_pkg.cgi?'. + join(';', map{ "$_=". $search_hash{$_} } keys %search_hash). ';'; +my $link_suffix = sub { my $row = shift; + my $result = 'censustract='. $row->censustract. ';'; + $result .= 'report_option='. @row_option[$row->upload - 1] + if @row_option[$row->upload - 1]; + $result .= 'report_option='. @column_option[$row->download - 1] + if @column_option[$row->download - 1]; + $result; + }; diff --git a/httemplate/search/elements/search.html b/httemplate/search/elements/search.html index a66176d00..4bfe8b091 100644 --- a/httemplate/search/elements/search.html +++ b/httemplate/search/elements/search.html @@ -16,7 +16,8 @@ Example: # (deprecated, will be singularlized # simplisticly) - #literal SQL query string (deprecated?) or qsearch hashref + #literal SQL query string (deprecated?) or qsearch hashref or arrayref + #of qsearch hashrefs for a union of qsearches 'query' => { 'table' => 'tablename', #everything else is optional... @@ -347,6 +348,15 @@ my $header = [ map { ref($_) ? $_->{'label'} : $_ } @{$opt{header}} ]; my $rows; if ( ref($opt{query}) ) { + my @query; + if (ref($opt{query}) eq 'HASH') { + @query = ( $opt{query} ); + } elsif (ref($opt{query}) eq 'ARRAY') { + @query = @{ $opt{query} }; + } else { + die "invalid query reference"; + } + if ( $opt{disableable} && ! $cgi->param('showdisabled') ) { #%search = ( 'disabled' => '' ); $opt{'query'}->{'hashref'}->{'disabled'} = ''; @@ -354,14 +364,15 @@ if ( ref($opt{query}) ) { } #eval "use FS::$opt{'query'};"; - $rows = [ qsearch({ - 'select' => $opt{'query'}->{'select'}, - 'table' => $opt{'query'}->{'table'}, - 'addl_from' => (exists($opt{'query'}->{'addl_from'}) ? $opt{'query'}->{'addl_from'} : ''), - 'hashref' => $opt{'query'}->{'hashref'} || {}, - 'extra_sql' => $opt{'query'}->{'extra_sql'}, - 'order_by' => $opt{'query'}->{'order_by'}. " $limit", - }) ]; + my @param = qw( select table addl_from hashref extra_sql order_by ); + $rows = [ qsearch( [ map { my $query = $_; + ({ map { $_ => $query->{$_} } @param }); + } + @query + ], + 'order_by' => $opt{order_by}. " ". $limit, + ) + ]; } else { my $sth = dbh->prepare("$opt{'query'} $limit") or die "Error preparing $opt{'query'}: ". dbh->errstr; diff --git a/httemplate/search/report_477.html b/httemplate/search/report_477.html index 542824b89..7b85c137c 100755 --- a/httemplate/search/report_477.html +++ b/httemplate/search/report_477.html @@ -1,8 +1,7 @@ <% include('/elements/header.html', 'FCC Form 477 Report' ) %>
- - + @@ -25,13 +24,24 @@ %> % if ( scalar( qsearch( 'part_pkg_report_option', { 'disabled' => '' } ) ) ) { +% # the m2 javascript magic in edit/elements/edit.html would be better here <% include( '/elements/tr-select-table.html', - 'label' => 'Report classes', + 'label' => 'Column report classes', 'table' => 'part_pkg_report_option', 'name_col' => 'name', 'hashref' => { 'disabled' => '' }, - 'element_name' => 'report_option', + 'element_name' => 'column_option', + 'multiple' => 'multiple', + ) + %> + + <% include( '/elements/tr-select-table.html', + 'label' => 'Row report classes', + 'table' => 'part_pkg_report_option', + 'name_col' => 'name', + 'hashref' => { 'disabled' => '' }, + 'element_name' => 'row_option', 'multiple' => 'multiple', ) %> -- 2.11.0