--- /dev/null
+% 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>