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