-<% include( 'elements/search.html',
- 'html_init' => $html_init,
- 'name' => 'lines',
- 'query' => $query,
- 'count_query' => 'SELECT 11',
- 'really_disable_download' => 1,
- 'disable_download' => 1,
- 'nohtmlheader' => 1,
- 'disable_total' => 1,
- 'header' => [ @headers ],
- 'xml_elements' => [ @xml_elements ],
- 'fields' => [ @fields ],
- )
-%>
+% 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 $html_init = '<H2>Part IIB</H2>';
+my %opt = @_;
my %search_hash = ();
-
-for ( qw(agentnum magic) ) {
- $search_hash{$_} = $cgi->param($_) if $cgi->param($_);
-}
-$search_hash{'classnum'} = [ $cgi->param('classnum') ];
-
-my @row_option = grep { /^\d+$/ } $cgi->param('part2b_row_option')
- if $cgi->param('part2b_row_option');
-
-# fudge in 2nd row
-unshift @row_option, $row_option[0];
-
-my $query = 'SELECT '. join(' UNION SELECT ', 1..8);
-
-my $total_count = 0;
-my $column_value = sub {
- my $row = shift;
-
- my @report_option = ( $row_option[$row - 1] || '' );
- my $sql_query = FS::cust_pkg->search(
- { %search_hash, 'report_option' => join(',', @report_option) }
- );
-
- my $count_sql = delete($sql_query->{'count_query'});
- if ( $row == 2 ) {
- $count_sql =~ s/COUNT\(\*\) FROM/COALESCE( sum(CASE WHEN cust_main.company IS NULL OR cust_main.company = '' THEN fcc_ds0s END), 0 ) FROM/
- or die "couldn't parse count_sql";
- } else {
- $count_sql =~ s/COUNT\(\*\) FROM/COALESCE( sum(fcc_ds0s), 0 ) FROM/
- or die "couldn't parse count_sql";
- }
-
- my $count_sth = dbh->prepare($count_sql)
- or die "Error preparing $count_sql: ". dbh->errstr;
- $count_sth->execute
- or die "Error executing $count_sql: ". $count_sth->errstr;
- my $count_arrayref = $count_sth->fetchrow_arrayref;
- my $count = $count_arrayref->[0];
+$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} ];
- $total_count = $count if $row == 1;
- $count = sprintf('%.2f', $total_count ? 100*$count/$total_count : 0)
- if $row != 1;
+my @row_option;
+foreach ($cgi->param('part2b_row_option')) {
+ push @row_option, (/^\d+$/ ? $_ : undef);
+}
- return "$count";
+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'
+ } @_
};
-my @headers = (
- '',
- 'without broadband',
- 'with broadband',
- 'wholesale',
+# 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 @xml_elements = (
- sub { my $row = shift; my $rownum = $row->[0] + 1; "PartII_${rownum}a" },
- sub { my $row = shift; my $rownum = $row->[0] + 1; "PartII_${rownum}b" },
- sub { my $row = shift; my $rownum = $row->[0] + 1; "PartII_${rownum}c" },
-);
+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 {
+ if ( $col_data[0] == 0 ) {
+ $col_data[$row] = ''; # show nothing in this row, then
+ } else {
+ $col_data[$row] = sprintf('%.3f', 100 * $count / $col_data[0]) . '%';
+ }
+ } #if $row == 0
+ $row++;
+ }
+ $data[$col] = \@col_data;
+ $col++;
+}
+
my @rows = (
'total number',
'% other broadband',
);
-my @fields = (
- sub { my $row = shift; $rows[$row->[0] - 1]; },
- sub { 0; },
- sub { my $row = shift; &{$column_value}($row->[0]); },
- sub { 0; },
+my @headers = (
+ '',
+ 'without broadband',
+ 'with broadband',
+ 'wholesale',
);
-shift @fields if $cgi->param('_type') eq 'xml';
</%init>
projects / freeside.git / blobdiff