X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=httemplate%2Fsearch%2F477.html;h=b485161376c2ff03b56d026218b41cf7f9e72d30;hb=1509cd837ea7e4e147a088d939b4198ee2456ef8;hp=9102c208319977f3c2d9a2c01be415817223ca4d;hpb=1a8d05e7a4345862e9ffb71d00bb07dd178017f0;p=freeside.git
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 ( ('.