From: jeff Date: Sun, 1 Nov 2009 22:12:56 +0000 (+0000) Subject: form 477 improvements #6499 X-Git-Tag: root_of_svc_elec_features~715 X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=1509cd837ea7e4e147a088d939b4198ee2456ef8 form 477 improvements #6499 --- diff --git a/httemplate/search/477.html b/httemplate/search/477.html index 9102c2083..b48516137 100755 --- a/httemplate/search/477.html +++ b/httemplate/search/477.html @@ -1,21 +1,23 @@ <% include( 'elements/search.html', - 'title' => 'FCC Form 477 Results', - 'html_init' => $html_init, - 'name' => 'regions', - 'query' => [ @sql_query ], - 'count_query' => $count_query, - 'order_by' => 'ORDER BY censustract', - 'header' => [ - 'County code', - 'Census tract code', - 'Upload rate', - 'Download rate', - 'Technology code', - 'Technology code other', - 'Quantity', - 'Percentage residential', - ], - 'fields' => [ + 'title' => 'FCC Form 477 Results', + 'html_init' => $html_init, + 'name' => 'regions', + 'query' => [ @sql_query ], + 'count_query' => $count_query, + 'order_by' => 'ORDER BY censustract', + 'avoid_quote' => 1, + 'no_csv_header' => 1, + 'header' => [ + '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, 2, 3) }, sub { my $row = shift; substr($row->censustract, 5) }, 'upload', @@ -25,7 +27,7 @@ 'quantity', sub { my $row = shift; sprintf "%.2f", $row->residential }, ], - 'links' => [ + 'links' => [ [ $link, $link_suffix ], [ $link, $link_suffix ], [ $link, $link_suffix ], @@ -81,17 +83,19 @@ my %row_option_name = $where ? @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 .= ''; +my $summary .= '

Summary

'. include('/elements/table.html'); +$summary .= ''; foreach my $column ( sort keys %column_option_name ) { - $html_init .= "$column"; + $summary .= "$column"; } - $html_init .= ""; +$summary .= ""; +my $total_count = 0; +my $total_residential = 0; my $rowcount = 1; foreach my $row ( sort keys %row_option_name ) { - $html_init .= "$row"; + $summary .= "$row"; my $columncount = 2; foreach my $column ( sort keys %column_option_name ) { @@ -108,13 +112,15 @@ foreach my $row ( sort keys %row_option_name ) { } ); 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"; + 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->{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'}); + $count_sql =~ s/ FROM/,count(cust_main.company) FROM/ + or die "couldn't parse count_sql"; my $count_sth = dbh->prepare($count_sql) or die "Error preparing $count_sql: ". dbh->errstr; @@ -122,16 +128,37 @@ foreach my $row ( sort keys %row_option_name ) { or die "Error executing $count_sql: ". $count_sth->errstr; my $count_arrayref = $count_sth->fetchrow_arrayref; my $count = $count_arrayref->[0]; + $total_count += $count; + my $residential = $count_arrayref->[1]; + $total_residential += $residential; + my $percentage = sprintf('%.2f', $count ? 100-100*$residential/$count : 0); - $html_init .= "$count"; + $summary .= "$count
$percentage% residential"; push @sql_query, $sql_query; $columncount++; } - $html_init .= ""; + $summary .= ""; $rowcount++; } +my $total_percentage = + sprintf("%.2f", $total_count ? 100-100*$total_residential/$total_count : 0); + +my $html_init = '

Totals

'. include('/elements/table.html'). ""; +$html_init .= "Total Connections"; +$html_init .= "% owned loop"; +$html_init .= "% billed to end users"; +$html_init .= "% residential"; +$html_init .= "% residential > 200kbps"; +$html_init .= ""; +$html_init .= "$total_count"; +$html_init .= "100.00"; +$html_init .= "100.00"; +$html_init .= "$total_percentage"; +$html_init .= "$total_percentage"; +$html_init .= "
"; +$html_init .= $summary; $html_init .= "

Details

"; my $count_query = 'SELECT count(*) FROM ( ('. diff --git a/httemplate/search/elements/search-csv.html b/httemplate/search/elements/search-csv.html index cd4ea63f5..21822700e 100644 --- a/httemplate/search/elements/search-csv.html +++ b/httemplate/search/elements/search-csv.html @@ -1,6 +1,6 @@ % $csv->combine(@$header); #or die $csv->status; % -<% $csv->string %>\ +<% $opt{no_csv_header} ? '' : $csv->string %>\ % % foreach my $row ( @$rows ) { % @@ -41,7 +41,10 @@ my %opt = %{ $args{'opt'} }; #http_header('Content-Type' => 'text/comma-separated-values' ); #IE chokes http_header('Content-Type' => 'text/plain' ); -my $csv = new Text::CSV_XS { 'always_quote' => 1, +my $quote_char = '"'; +$quote_char = $opt{csv_quote} if exists($opt{csv_quote}); + +my $csv = new Text::CSV_XS { 'always_quote' => $opt{avoid_quote} ? 0 : 1, 'eol' => "\n", #"\015\012", #"\012" };