X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=httemplate%2Fsearch%2F477partIIB.html;h=ce1ac03f040d9594b3f3c5e7c8e0d0eedc1bcdfb;hp=94aa818fb94a124b2fd79627f78788ebff650fb3;hb=e9e0cf0989259b94d9758eceff448666a2e5a5cc;hpb=fb4ab1073f0d15d660c6cdc4e07afebf68ef3924 diff --git a/httemplate/search/477partIIB.html b/httemplate/search/477partIIB.html index 94aa818fb..ce1ac03f0 100755 --- a/httemplate/search/477partIIB.html +++ b/httemplate/search/477partIIB.html @@ -1,17 +1,44 @@ -<% 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]) ) { +<% $cols[$col] %>>\ +<% $data[$col][$row] %>\ +<% $cols[$col] %>> +% } +% } #for $col +% } #for $row +% } else { # HTML mode +% # fake up the search-html.html header +

Part IIB

+ + + +

+ + +% foreach (@headers) { + +% } + +% my $row = 0; +% foreach my $rowhead (@rows) { + + +% for my $col (0..2) { + +% } # for $col + +% $row++; +% } #for $rowhead +
<% $_ %>
<% $rowhead %> +% if ( exists($data[$col][$row]) ) { + <% $data[$col][$row] %> +% } +
+
+% } #XML/HTML <%init> my $curuser = $FS::CurrentUser::CurrentUser; @@ -19,67 +46,90 @@ my $curuser = $FS::CurrentUser::CurrentUser; die "access denied" unless $curuser->access_right('List packages'); -my $html_init = '

Part IIB

'; +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/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/ - or die "couldn't parse count_sql"; - } else { - $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/ - 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', @@ -92,12 +142,11 @@ my @rows = ( '% 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';