% if ( $cgi->param('_type') eq 'xml' ) {
%   my @cols = qw(a b c);
%   for ( my $row = 0; $row < scalar(@rows); $row++ ) {
%     for my $col (0..2) {
%       if ( exists($data[$col][$row]) ) {
<PartII_<% $row + 1 %><% $cols[$col] %>>\
<% $data[$col][$row] %>\
</PartII_<% $row + 1 %><% $cols[$col] %>>
%       }
%     } #for $col
%   } #for $row
% } else { # HTML mode
% # fake up the search-html.html header
<H2>Part IIB</H2>
<TABLE>
  <TR><TD VALIGN="bottom"><BR></TD></TR>
  <TR><TD COLSPAN=2>
  <TABLE CLASS="grid" CELLSPACING=0>
    <TR>
% foreach (@headers) {
      <TH><% $_ %></TH>
% }
    </TR>
% my $row = 0;
% foreach my $rowhead (@rows) {
    <TR CLASS="row<% $row % 2 %>"> 
      <TD STYLE="text-align: left; font-weight: bold"><% $rowhead %></TD>
%     for my $col (0..2) {
      <TD>
%       if ( exists($data[$col][$row]) ) {
      <% $data[$col][$row] %>
%       }
      </TD>
%     } # for $col
    </TR>
%   $row++;
% } #for $rowhead
  </TABLE>
  </TD></TR>
</TABLE>
% } #XML/HTML
<%init>

my $curuser = $FS::CurrentUser::CurrentUser;

die "access denied"
  unless $curuser->access_right('List packages');

my %opt = @_;
my %search_hash = ();

$search_hash{'agentnum'} = $cgi->param('agentnum');
$search_hash{'state'}    = $cgi->param('state');
$search_hash{'classnum'} = [ $cgi->param('classnum') ];
$search_hash{'active'}   = [ $opt{date}, $opt{date} ];

my @row_option;
foreach ($cgi->param('part2b_row_option')) {
  push @row_option, (/^\d+$/ ? $_ : undef);
}

my $is_residential = " AND COALESCE(cust_main.company, '') = ''";
my $has_report_option = sub {
  map {
    defined($row_option[$_]) ?
    " AND EXISTS(
      SELECT 1 FROM part_pkg_option 
      WHERE part_pkg_option.pkgpart = part_pkg.pkgpart
      AND optionname = 'report_option_" . $row_option[$_]."'
      AND optionvalue = '1'
    )" : ' AND FALSE'
  } @_
};

# an arrayref for each column
my @data;
# get the skeleton of the query
my $sql_query = FS::cust_pkg->search(\%search_hash);
my $from_where = $sql_query->{'count_query'};
$from_where =~ s/^SELECT COUNT\(\*\) //;
# columns 1 and 2
my $query_ds0 = "SELECT SUM(COALESCE(part_pkg.fcc_ds0s, pkg_class.fcc_ds0s, 0))
  $from_where";
# column 3
my $query_custnum = "SELECT COUNT(DISTINCT cust_pkg.custnum) $from_where";

my @base_queries = ($query_ds0, $query_ds0, $query_custnum);
my @col_conds = (
  # column 1
  [
    '',
    $is_residential,
    $has_report_option->(0), # nomadic
  ],
  # column 2
  [
    '',
    $is_residential,
    $has_report_option->(0..5),
  ],
  # column 3
  [
    ''
  ]
);

my $col = 0;
foreach (@col_conds) {
  my @col_data;
  my $row = 0;
  foreach my $cond (@{ $col_conds[$col] }) {
    # three parts: the select expression, the VoIP class (column selection),
    # and the row selection
    my $query = $base_queries[$col] . 
                " AND part_pkg.fcc_voip_class = '".($col+1)."'
                $cond";
    my $count = FS::Record->scalar_sql($query) || 0;
    if ( $row == 0 ) {
      $col_data[$row] = $count; # the raw count
    } else {
      # the rows that are percentages of the raw count
      if ( $col_data[0] == 0 ) {
        # 0 out of 0 is not 0%, but supposedly this works
        $col_data[$row] = '0.000';
      } else {
        $col_data[$row] = sprintf('%.3f', 100 * $count / $col_data[0]);
      }
      $col_data[$row] .= '%' unless $cgi->param('_type') eq 'xml';
    } #if $row == 0
    $row++;
  }
  $data[$col] = \@col_data;
  $col++;
}


my @rows = (
  'total number',
  '% residential',
  '% nomadic',
  '% copper',
  '% FTTP',
  '% coax',
  '% wireless',
  '% other broadband',
);

my @headers = (
  '',
  'without broadband',
  'with broadband',
  'wholesale',
);

</%init>