1 <& elements/search.html,
2 'html_init' => $html_init,
5 'count_query' => 'SELECT 11',
6 'really_disable_download' => 1,
7 'disable_download' => 1,
10 'header' => [ @headers ],
11 'xml_elements' => [ @xml_elements ],
12 'fields' => [ @fields ],
17 my $curuser = $FS::CurrentUser::CurrentUser;
20 unless $curuser->access_right('List packages');
22 my $html_init = '<H2>Part IIA</H2>';
25 for ( qw(agentnum magic state) ) {
26 $search_hash{$_} = $cgi->param($_) if $cgi->param($_);
28 $search_hash{'country'} = 'US';
29 $search_hash{'classnum'} = [ $cgi->param('classnum') ];
31 my @row_option = grep { /^\d+$/ } $cgi->param('part2a_row_option')
32 if $cgi->param('part2a_row_option');
34 # fudge in two rows of LD carrier
35 unshift @row_option, $row_option[0];
37 # fudge in the first pair of rows
38 unshift @row_option, '';
39 unshift @row_option, '';
41 my $query = 'SELECT '. join(' UNION SELECT ', 1..11);
44 my $column_value = sub {
47 my @report_option = ( $row_option[$row - 1] || '' );
49 my $sql_query = FS::cust_pkg->search(
50 { %search_hash, 'report_option' => join(',', @report_option) }
53 my $count_sql = delete($sql_query->{'count_query'});
54 if ( $row == 2 || $row == 4 ) {
55 $count_sql =~ s/COUNT\(\*\) FROM/sum(COALESCE(CASE WHEN cust_main.company IS NULL OR cust_main.company = '' THEN CASE WHEN part_pkg.fcc_ds0s IS NOT NULL AND part_pkg.fcc_ds0s > 0 THEN part_pkg.fcc_ds0s WHEN pkg_class.fcc_ds0s IS NOT NULL AND pkg_class.fcc_ds0s > 0 THEN pkg_class.fcc_ds0s ELSE 0 END ELSE 0 END, 0) ) FROM/
56 or die "couldn't parse count_sql";
58 $count_sql =~ s/COUNT\(\*\) FROM/sum(COALESCE(CASE WHEN part_pkg.fcc_ds0s IS NOT NULL AND part_pkg.fcc_ds0s > 0 THEN part_pkg.fcc_ds0s WHEN pkg_class.fcc_ds0s IS NOT NULL AND pkg_class.fcc_ds0s > 0 THEN pkg_class.fcc_ds0s ELSE 0 END, 0)) FROM/
59 or die "couldn't parse count_sql";
62 my $count_sth = dbh->prepare($count_sql)
63 or die "Error preparing $count_sql: ". dbh->errstr;
65 or die "Error executing $count_sql: ". $count_sth->errstr;
66 my $count_arrayref = $count_sth->fetchrow_arrayref;
67 my $count = $count_arrayref->[0];
69 $total_count = $count if $row == 1;
70 $count = sprintf('%.2f', $total_count ? 100*$count/$total_count : 0)
85 sub { my $row = shift; my $rownum = $row->[0] + 1; "PartII_${rownum}a" },
86 sub { my $row = shift; my $rownum = $row->[0] + 1; "PartII_${rownum}b" },
87 sub { my $row = shift; my $rownum = $row->[0] + 1; "PartII_${rownum}c" },
88 sub { my $row = shift; my $rownum = $row->[0] + 1; "PartII_${rownum}d" },
96 '% residential and LD carrier',
98 '% obtained unswitched UNE loops',
100 '% UNE-P replacement',
107 sub { my $row = shift; $rows[$row->[0] - 1]; },
108 sub { my $row = shift; &{$column_value}($row->[0]); },
114 shift @fields if $cgi->param('_type') eq 'xml';