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                   'header'      => [
   8                                      'State code',
   9                                      'County code',
  10                                      'Census tract code',
  11                                      'Quantity',
  12                                    ],
  13                   'fields'      => [
  14                     sub { my $row = shift; substr($row->censustract, 0, 2) },
  15                     sub { my $row = shift; substr($row->censustract, 2, 3) },
  16                     sub { my $row = shift; substr($row->censustract, 5) },
  17                     'quantity',
  18                   ],
  19                   'links'      => [
  20                     [ $link, 'censustract' ],
  21                     [ $link, 'censustract' ],
  22                     [ $link, 'censustract' ],
  23                     [ $link, 'censustract' ],
  24                   ],
  25               )
  26 %>
  27 <%init>
  28
  29 my $curuser = $FS::CurrentUser::CurrentUser;
  30
  31 die "access denied"
  32   unless $curuser->access_right('List packages');
  33
  34 my %search_hash = ();
  35
  36 $search_hash{'query'} = $cgi->keywords;
  37
  38 for ( qw(agentnum magic status classnum) ) {
  39   $search_hash{$_} = $cgi->param($_) if $cgi->param($_);
  40 }
  41
  42
  43 my @report_option = $cgi->param('report_option')
  44   if $cgi->param('report_option');
  45 $search_hash{report_option} = join(',', @report_option) if @report_option;
  46
  47 my $where = join(' OR ', map { "num = $_" } grep { /^\d+$/ } @report_option );
  48 my @report_option_name = $where ?
  49                            ( map { $_->name }
  50                              qsearch({ 'table' => 'part_pkg_report_option',
  51                                        'hashref' => {},
  52                                        'extra_sql' => "WHERE $where",
  53                                     })
  54                            ) :
  55                            ( 'all packages' );
  56
  57 my $html_init = "For <BR>". join(' and <BR>', @report_option_name). "<BR>";
  58
  59 my $sql_query = FS::cust_pkg->search_sql(\%search_hash);
  60
  61 $sql_query->{select} = 'count(*) as quantity, censustract';
  62 $sql_query->{extra_sql} =~ /^(.*)(ORDER BY bill)(.*)$/s or die "couldn't parse extra_sql";
  63 $sql_query->{extra_sql} = "$1 GROUP BY censustract $3";
  64
  65 my $count_query = delete($sql_query->{'count_query'});
  66 $count_query = 'SELECT count(*) FROM (SELECT count(*) FROM cust_pkg '.
  67   $sql_query->{addl_from}. ' '. $sql_query->{extra_sql}. ') AS foo';
  68
  69 my $link = 'cust_pkg.cgi?'. $cgi->query_string. ';censustract=';
  70 </%init>