httemplate/search/477.html

   1 <% include( 'elements/search.html',
   2                   'title'         => 'FCC Form 477 Results',
   3                   'html_init'     => $html_init,
   4                   'name'          => 'regions',
   5                   'query'         => [ @sql_query ],
   6                   'count_query'   =>  $count_query,
   7                   'order_by'      => 'ORDER BY censustract',
   8                   'avoid_quote'   => 1,
   9                   'no_csv_header' => 1,
  10                   'header'        => [
  11                                        'County code',
  12                                        'Census tract code',
  13                                        'Upload rate',
  14                                        'Download rate',
  15                                        'Technology code',
  16                                        'Technology code other',
  17                                        'Quantity',
  18                                        'Percentage residential',
  19                                      ],
  20                   'fields'        => [
  21                     sub { my $row = shift; substr($row->censustract, 2, 3) },
  22                     sub { my $row = shift; substr($row->censustract, 5) },
  23                     'upload',
  24                     'download',
  25                     sub { 7 },
  26                     sub { '' },
  27                     'quantity',
  28                     sub { my $row = shift; sprintf "%.2f", $row->residential },
  29                   ],
  30                   'links'        => [
  31                     [ $link, $link_suffix ],
  32                     [ $link, $link_suffix ],
  33                     [ $link, $link_suffix ],
  34                     [ $link, $link_suffix ],
  35                     [ $link, $link_suffix ],
  36                     [ $link, $link_suffix ],
  37                     [ $link, $link_suffix ],
  38                     [ $link, $link_suffix ],
  39                   ],
  40               )
  41 %>
  42 <%init>
  43
  44 my $curuser = $FS::CurrentUser::CurrentUser;
  45
  46 die "access denied"
  47   unless $curuser->access_right('List packages');
  48
  49 my %search_hash = ();
  50 my @sql_query = ();
  51
  52 for ( qw(agentnum magic classnum) ) {
  53   $search_hash{$_} = $cgi->param($_) if $cgi->param($_);
  54 }
  55
  56 my @column_option = $cgi->param('column_option')
  57   if $cgi->param('column_option');
  58
  59 my @row_option = $cgi->param('row_option')
  60   if $cgi->param('row_option');
  61
  62 my $where = join(' OR ', map { "num = $_" } grep { /^\d+$/ } @column_option );
  63 my %column_option_name = $where ?
  64                            ( map { $_->name => $_->num }
  65                              qsearch({ 'table' => 'part_pkg_report_option',
  66                                        'hashref' => {},
  67                                        'extra_sql' => "WHERE $where",
  68                                     })
  69                            ) :
  70                            ( 'all packages' => '' );
  71
  72 $where = join(' OR ', map { "num = $_" } grep { /^\d+$/ } @row_option );
  73 my %row_option_name = $where ?
  74                         ( map { $_->name => $_->num }
  75                           qsearch({ 'table' => 'part_pkg_report_option',
  76                                     'hashref' => {},
  77                                     'extra_sql' => "WHERE $where",
  78                                  })
  79                         ) :
  80                         ( 'all packages' => '' );
  81
  82 @row_option = map { $row_option_name{$_} } sort keys %row_option_name;
  83 @column_option = map { $column_option_name{$_} } sort keys %column_option_name;
  84
  85 #$search_hash{row_option} = join(',', @row_option) if @row_option;
  86 my $summary .= '<H2>Summary</H2>'. include('/elements/table.html');
  87 $summary .= '<TR><TH></TH>';
  88 foreach my $column ( sort keys %column_option_name ) {
  89   $summary .= "<TH>$column</TH>";
  90 }
  91 $summary .= "</TR>";
  92
  93 my $total_count = 0;
  94 my $total_residential = 0;
  95 my $rowcount = 1;
  96 foreach my $row ( sort keys %row_option_name ) {
  97
  98   $summary .= "<TR><TH>$row</TH>";
  99
 100   my $columncount = 2;
 101   foreach my $column ( sort keys %column_option_name ) {
 102     my @report_option = ();
 103     push @report_option, $row_option_name{$row}
 104       if $row_option_name{$row};
 105     push @report_option, $column_option_name{$column}
 106       if $column_option_name{$column};
 107     my $report_option = join(',', @report_option) if @report_option;
 108
 109     my $sql_query = FS::cust_pkg->search(
 110       { %search_hash,
 111         ($report_option ? ( 'report_option' => $report_option ) : () ),
 112       }
 113     );
 114     my $extracolumns = "$rowcount AS upload, $columncount AS download";
 115     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";
 116     $sql_query->{select} = "count(*) AS quantity, $extracolumns, censustract, $percent";
 117     $sql_query->{extra_sql} =~ /^(.*)(ORDER BY pkgnum)(.*)$/s
 118       or die "couldn't parse extra_sql";
 119     $sql_query->{extra_sql} = "$1 GROUP BY censustract $3";
 120
 121     my $count_sql = delete($sql_query->{'count_query'});
 122     $count_sql =~ s/ FROM/,count(cust_main.company) FROM/
 123       or die "couldn't parse count_sql";
 124
 125     my $count_sth = dbh->prepare($count_sql)
 126       or die "Error preparing $count_sql: ". dbh->errstr;
 127     $count_sth->execute
 128       or die "Error executing $count_sql: ". $count_sth->errstr;
 129     my $count_arrayref = $count_sth->fetchrow_arrayref;
 130     my $count = $count_arrayref->[0];
 131     $total_count += $count;
 132     my $residential = $count_arrayref->[1];
 133     $total_residential += $residential;
 134     my $percentage = sprintf('%.2f', $count ? 100-100*$residential/$count : 0);
 135
 136     $summary .= "<TD>$count<BR>$percentage% residential</TD>";
 137     push @sql_query, $sql_query;
 138     $columncount++;
 139   }
 140
 141   $summary .= "</TR>";
 142   $rowcount++;
 143 }
 144
 145 my $total_percentage =
 146   sprintf("%.2f", $total_count ? 100-100*$total_residential/$total_count : 0);
 147
 148 my $html_init = '<H2>Totals</H2>'. include('/elements/table.html'). "<TR>";
 149 $html_init .= "<TH>Total Connections</TH>";
 150 $html_init .= "<TH>% owned loop</TH>";
 151 $html_init .= "<TH>% billed to end users</TH>";
 152 $html_init .= "<TH>% residential</TH>";
 153 $html_init .= "<TH>% residential &gt; 200kbps</TH>";
 154 $html_init .= "</TR><TR>";
 155 $html_init .= "<TD>$total_count</TD>";
 156 $html_init .= "<TD>100.00</TD>";
 157 $html_init .= "<TD>100.00</TD>";
 158 $html_init .= "<TD>$total_percentage</TD>";
 159 $html_init .= "<TD>$total_percentage</TD>";
 160 $html_init .= "</TR></TABLE><BR>";
 161 $html_init .= $summary;
 162 $html_init .= "</TABLE><BR><H2>Details</H2>";
 163
 164 my $count_query = 'SELECT count(*) FROM ( ('.
 165    join( ') UNION (',
 166           map { my $extra = $_->{extra_sql};  my $addl = $_->{addl_from};
 167                 "SELECT censustract from cust_pkg $addl $extra";
 168               }
 169           @sql_query
 170        ). ') ) AS foo';
 171
 172 my $link = 'cust_pkg.cgi?'.
 173            join(';', map{ "$_=". $search_hash{$_} } keys %search_hash). ';';
 174 my $link_suffix = sub { my $row = shift;
 175                         my $result = 'censustract='. $row->censustract. ';';
 176                         $result .= 'report_option='. @row_option[$row->upload - 1]
 177                           if @row_option[$row->upload - 1];
 178                         $result .= 'report_option='. @column_option[$row->download - 1]
 179                           if @column_option[$row->download - 1];
 180                         $result;
 181                       };
 182 </%init>