+ 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(
+ { %search_hash,
+ ($report_option ? ( 'report_option' => $report_option ) : () ),
+ }
+ );
+ my $extracolumns = "$rowcount AS upload, $columncount AS download";
+ 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;
+ $count_sth->execute
+ 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);
+
+ $summary .= "<TD>$count<BR>$percentage% residential</TD>";
+ push @sql_query, $sql_query;
+ $columncount++;
+ }
+
+ $summary .= "</TR>";
+ $rowcount++;
+}