From 4402718cbb1f0647bd734440b6fa2a1cd85a5635 Mon Sep 17 00:00:00 2001 From: Mark Wells Date: Tue, 9 Apr 2013 20:01:01 -0700 Subject: [PATCH] improvements to 477 report, #21703 --- httemplate/elements/tr-select-voip_class.html | 3 +- httemplate/search/477.html | 15 ++- httemplate/search/477partIA.html | 165 +++++++++++++++++++++++ httemplate/search/477partIA_detail.html | 129 ------------------ httemplate/search/477partIA_summary.html | 89 ------------- httemplate/search/477partIIA.html | 185 ++++++++++++++------------ httemplate/search/477partIIB.html | 15 +-- httemplate/search/477partV.html | 6 + httemplate/search/elements/search.html | 2 +- 9 files changed, 296 insertions(+), 313 deletions(-) create mode 100755 httemplate/search/477partIA.html delete mode 100755 httemplate/search/477partIA_detail.html delete mode 100755 httemplate/search/477partIA_summary.html diff --git a/httemplate/elements/tr-select-voip_class.html b/httemplate/elements/tr-select-voip_class.html index dcc1487cc..afd3e1f8a 100644 --- a/httemplate/elements/tr-select-voip_class.html +++ b/httemplate/elements/tr-select-voip_class.html @@ -18,7 +18,8 @@ my @options = ( '' => '', 1 => 'VoIP without Broadband', 2 => 'VoIP with Broadband', - 3 => 'Wholesale VoIP' + 3 => 'Wholesale VoIP', + 4 => 'Local Exchange (non-VoIP)', ); diff --git a/httemplate/search/477.html b/httemplate/search/477.html index 04764c1da..eed3df946 100755 --- a/httemplate/search/477.html +++ b/httemplate/search/477.html @@ -3,6 +3,14 @@ % } else { #html <& /elements/header.html, "FCC Form 477 Results - $state" &> +%# XXX when we stop supporting IE8, add this to freeside.css using :nth-child +%# selectors, and remove it from everywhere else + + @@ -38,8 +46,11 @@ % if ( $type eq 'xml' ) { <<% 'Part_IA_'. chr(65 + $tech) %>> % } -<& "477part${part}_summary.html", 'tech_code' => $tech, 'url' => $url &> -<& "477part${part}_detail.html", 'tech_code' => $tech, 'url' => $url &> +<& "477part${part}.html", + 'tech_code' => $tech, + 'url' => $url, + 'type' => $type +&> % if ( $type eq 'xml' ) { > % } diff --git a/httemplate/search/477partIA.html b/httemplate/search/477partIA.html new file mode 100755 index 000000000..1cd0b70e0 --- /dev/null +++ b/httemplate/search/477partIA.html @@ -0,0 +1,165 @@ +% if ( $opt{'type'} eq 'xml' ) { +%# container element is in 477.html +% my $col = 'a'; +% foreach ( @summary_row ) { +% my $el = $xml_prefix . $col . '1'; # PartIA_Aa1, PartIA_Ab1, etc. + <<% $el %>><% $_ %><<% "/$el" %>> +% $col++; +% } +% foreach my $col_data ( @data ) { +% my $row = 1; +% foreach my $cell ( @$col_data ) { +% my $el = $xml_prefix . $col . $row; # PartIA_Af1, PartIA_Af2... + <<% $el %>><% $cell->[0] %><<% "/$el" %>> +% if ( $percentages ) { +% $el = $xml_percent . $col . $row; # Part_p_IA_Af1, ... + <<% $el %>><% $cell->[1] %><<% "/$el" %>> +% } +% $row++; +% } # foreach $cell +% $col++; +% } # foreach $col_data +% } else { # not XML + +

<% $title %> totals

+<& /elements/table-grid.html &> + +% foreach ( 'Total Connections', +% '% owned loop', +% '% billed to end users', +% '% residential', +% '% residential > 200 kbps') { + +% } + + +% foreach ( @summary_row ) { + +% } + +
<% $_ |h %>
<% $_ %>
+

<% $title %> breakdown by speed

+ + + +% for (my $col = 0; $col < scalar(@download_option); $col++) { + +% } + +% for (my $row = 0; $row < scalar(@upload_option); $row++) { + + +% for (my $col = 0; $col < scalar(@download_option); $col++) { + +% } # for $col + +% } # for $row +
+ <% $FS::Report::FCC_477::download[$col] |h %> +
+% if ( $asymmetric ) { + <% $FS::Report::FCC_477::upload[$row] |h %> +% } + + <% $data[$col][$row][0] %> +% if ( $percentages ) { +
<% $data[$col][$row][1] %> +% } +
+% } +<%init> + +my $curuser = $FS::CurrentUser::CurrentUser; + +die "access denied" + unless $curuser->access_right('List packages'); + +my %opt = @_; +my %search_hash; + +for ( qw(agentnum state) ) { + $search_hash{$_} = $cgi->param($_) if $cgi->param($_); +} +$search_hash{'status'} = 'active'; +$search_hash{'country'} = 'US'; +$search_hash{'classnum'} = [ $cgi->param('classnum') ]; + +# arrays of report_option_ numbers, running parallel to +# the download and upload speed arrays +my @download_option = $cgi->param('part1_column_option'); +my @upload_option = $cgi->param('part1_row_option'); + +my @technology_option = &FS::Report::FCC_477::parse_technology_option($cgi); + +my $total_count = 0; +my $total_residential = 0; +my $above_200 = 0; +my $tech_code = $opt{tech_code}; +my $technology = $FS::Report::FCC_477::technology[$tech_code] || 'unknown'; +my $title = "Part IA $technology"; +my $xml_prefix = 'PartIA_'. chr(65 + $tech_code); +my $xml_percent = 'Part_p_IA_'. chr(65 + $tech_code); # yes, seriously + +# whether to show the results as a matrix (upload speeds in rows) or a single +# row +my $asymmetric = 1; +if ( $technology eq 'Symmetric xDSL' or $technology eq 'Other Wireline' ) { + $asymmetric = 0; + @upload_option = ( undef ); +} +# whether to show residential percentages in each cell of the matrix +my $percentages = ($technology eq 'Terrestrial Mobile Wireless'); + +my $query = FS::cust_pkg->search(\%search_hash); +my $count_query = $query->{'count_query'}; + +my $is_residential = " AND COALESCE(cust_main.company, '') = ''"; +my $has_option = sub { + my $optionnum = shift; + $optionnum =~ /^\d+$/ ? + " AND EXISTS( + SELECT 1 FROM part_pkg_option + WHERE part_pkg_option.pkgpart = part_pkg.pkgpart + AND optionname = 'report_option_$optionnum' + AND optionvalue = '1' + )" : ''; +}; + +# limit to those that have technology option $tech_code +$count_query .= $has_option->($technology_option[$tech_code]); + +my @data; +for ( my $row = 0; $row < scalar @upload_option; $row++ ) { + for ( my $col = 0; $col < scalar @download_option; $col++ ) { + + my $this_count_query = $count_query . + $has_option->($upload_option[$row]) . + $has_option->($download_option[$col]); + + my $count = FS::Record->scalar_sql($this_count_query); + my $residential = FS::Record->scalar_sql($this_count_query . $is_residential); + + my $percent = sprintf('%.2f', $count ? 100 * $residential / $count : 0); + $data[$col][$row] = [ $count, $percent ]; + + $total_count += $count; + $total_residential += $residential; + $above_200 += $residential if $row > 0 or !$asymmetric; + } +} + +my $total_percentage = + sprintf("%.2f", $total_count ? 100*$total_residential/$total_count : 0); + +my $above_200_percentage = + sprintf("%.2f", $total_count ? 100*$above_200/$total_count : 0); + +my @summary_row = ( + $total_count, + 100.00, # own local loop--consistent with previous practice, but probably wrong + 100.00, # billed to end user--also wrong + $total_percentage, # residential percentage + $above_200_percentage, +); + + diff --git a/httemplate/search/477partIA_detail.html b/httemplate/search/477partIA_detail.html deleted file mode 100755 index 666032d0c..000000000 --- a/httemplate/search/477partIA_detail.html +++ /dev/null @@ -1,129 +0,0 @@ -<& elements/search.html, - 'html_init' => $html_init, - 'name' => 'lines', - 'query' => $query, - 'count_query' => $count_query, - 'really_disable_download' => 1, - 'disable_download' => 1, - 'nohtmlheader' => 1, - 'disable_total' => 1, - 'header' => [ '', @column_option_name ], - 'xml_elements' => [ @xml_elements ], - 'xml_omit_empty' => 1, - 'fields' => [ @fields ], - -&> -<%init> - -my $curuser = $FS::CurrentUser::CurrentUser; - -die "access denied" - unless $curuser->access_right('List packages'); - -my %opt = @_; -my %search_hash = (); - -for ( qw(agentnum magic state) ) { - $search_hash{$_} = $cgi->param($_) if $cgi->param($_); -} -$search_hash{'country'} = 'US'; - -$search_hash{'classnum'} = [ $cgi->param('classnum') ]; - -my @column_option = grep { /^\d+/ } $cgi->param('part1_column_option') - if $cgi->param('part1_column_option'); - -my @row_option = grep { /^\d+/ } $cgi->param('part1_row_option') - if $cgi->param('part1_row_option'); - -my @technology_option = &FS::Report::FCC_477::parse_technology_option($cgi); - -my @column_option_name = scalar(@column_option) - ? ( map { my $part_pkg_report_option = - qsearchs({ 'table' => 'part_pkg_report_option', - 'hashref' => { num => $_ }, - }); - $part_pkg_report_option ? $part_pkg_report_option->name - : 'no such report option'; - } @column_option - ) - : ( 'all packages' ); - -my $where = join(' OR ', map { "num = $_" } @row_option ); -my %row_option_name = $where ? - ( map { $_->num => $_->name } - qsearch({ 'table' => 'part_pkg_report_option', - 'hashref' => {}, - 'extra_sql' => "WHERE $where", - }) - ) : - (); - -my $tech_code = $opt{tech_code}; -my $technology = $FS::Report::FCC_477::technology[$tech_code] || 'unknown'; -my $html_init = "

Part IA $technology breakdown by speeds

"; -my $xml_prefix = 'PartIA_'. chr(65 + $tech_code); - -if ($cgi->param('_type') eq 'xml') { - #rotate data pi/2 - my @temp = @column_option; - @column_option = @row_option; - @row_option = @temp; -} - -my $query = 'SELECT '. join(' UNION ALL SELECT ',@row_option); -my $count_query = 'SELECT '. scalar(@row_option); - -my $xml_element = 'OOPS, I was never set'; -my $rowchar = 101; # 'e' -- rows are columns! (pi/2) - -my $value = sub { - my ($rowref, $column) = (shift, shift); - my $row = $rowref->[0]; - - if ($column eq 'name') { - return $row_option_name{$row} || 'no such report option'; - } elsif ( $column =~ /^(\d+)$/ ) { - my @report_option = ( $row || '', - $column_option[$column] || '', - $technology_option[$tech_code] || '', - ); - - my ( $count, $residential ) = FS::cust_pkg->fcc_477_count( - { %search_hash, 'report_option' => join(',', @report_option) } - ); - - my $percentage = sprintf('%.2f', $count ? 100 * $residential / $count : 0); - my $return = $count; - - if ($cgi->param('_type') eq 'xml') { - $rowchar++ if $column == 0; - $xml_element = $xml_prefix. chr($rowchar). ($column+1); - $return = '' if $count == 0 and $cgi->param('_type') eq 'xml'; - } else { - $return .= "
$percentage% residential"; - } - - return $return; - } else { - return 'Bad call to column_value'; - } -}; - -my @fields = map { my $ci = $_; sub { &{$value}(shift, $ci); } } - ( 'name', (0 .. $#column_option) ); -shift @fields if $cgi->param('_type') eq 'xml'; - -my @xml_elements = ( # -- columns are rows! (pi/2) - sub { return $xml_element; }, - sub { return $xml_element; }, - sub { return $xml_element; }, - sub { return $xml_element; }, - sub { return $xml_element; }, - sub { return $xml_element; }, - sub { return $xml_element; }, - sub { return $xml_element; }, - sub { return $xml_element; }, -); - - diff --git a/httemplate/search/477partIA_summary.html b/httemplate/search/477partIA_summary.html deleted file mode 100755 index ebf081c71..000000000 --- a/httemplate/search/477partIA_summary.html +++ /dev/null @@ -1,89 +0,0 @@ -<& elements/search.html, - 'html_init' => $html_init, - 'name' => 'lines', - 'query' => 'SELECT 1', - 'count_query' => 'SELECT 1', - 'really_disable_download' => 1, - 'disable_download' => 1, - 'nohtmlheader' => 1, - 'disable_total' => 1, - 'header' => [ - 'Total Connections', - '% owned loop', - '% billed to end users', - '% residential', - '% residential > 200kbps', - ], - 'xml_elements' => [ - $xml_prefix. 'a1', - $xml_prefix. 'b1', - $xml_prefix. 'c1', - $xml_prefix. 'd1', - $xml_prefix. 'e1', - ], - 'fields' => [ - sub { $total_count }, - sub { '100.00' }, - sub { '100.00' }, - sub { $total_percentage }, - sub { $above_200_percentage }, - ], - -&> -<%init> - -my $curuser = $FS::CurrentUser::CurrentUser; - -die "access denied" - unless $curuser->access_right('List packages'); - -my %opt = @_; -my %search_hash = (); - -for ( qw(agentnum magic state) ) { - $search_hash{$_} = $cgi->param($_) if $cgi->param($_); -} -$search_hash{'country'} = 'US'; -$search_hash{'classnum'} = [ $cgi->param('classnum') ]; - -my @column_option = grep { /^\d+$/ } $cgi->param('part1_column_option') - if $cgi->param('part1_column_option'); - -my @row_option = grep { /^\d+$/ } $cgi->param('part1_row_option') - if $cgi->param('part1_row_option'); - -my @technology_option = &FS::Report::FCC_477::parse_technology_option($cgi); - -my $total_count = 0; -my $total_residential = 0; -my $above_200 = 0; -my $tech_code = $opt{tech_code}; -my $technology = $FS::Report::FCC_477::technology[$tech_code] || 'unknown'; -my $html_init = "

Part IA $technology totals

"; -my $xml_prefix = 'PartIA_'. chr(65 + $tech_code); - -my $not_first_row = 0; # ugh; -foreach my $row ( @row_option ) { - foreach my $column ( @column_option ) { - - my @report_option = ( $row || '-1', $column || '-1', $technology_option[$tech_code] ); - - my ( $count, $residential ) = FS::cust_pkg->fcc_477_count( - { %search_hash, 'report_option' => join(',', @report_option) } - ); - - $total_count += $count; - $total_residential += $residential; - $above_200 += $residential if $not_first_row; - } - $not_first_row++; -} - -my $total_percentage = - sprintf("%.2f", $total_count ? 100*$total_residential/$total_count : 0); - -my $above_200_percentage = - sprintf("%.2f", $total_count ? 100*$above_200/$total_count : 0); - - - diff --git a/httemplate/search/477partIIA.html b/httemplate/search/477partIIA.html index 6a532299b..95c00a3e0 100755 --- a/httemplate/search/477partIIA.html +++ b/httemplate/search/477partIIA.html @@ -1,17 +1,44 @@ -<& 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 d); +% for ( my $row = 0; $row < scalar(@rows); $row++ ) { +% for my $col (0..3) { +% if ( exists($data[$col][$row]) and $data[$col][$row] > 0 ) { +<% $cols[$col] %>>\ +<% $data[$col][$row] %>\ +<% $cols[$col] %>> +% } +% } #for $col +% } #for $row +% } else { # HTML mode +% # fake up the search-html.html header +

Part IIA

+ + + +

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

Part IIA

'; my %search_hash = (); - -for ( qw(agentnum magic state) ) { - $search_hash{$_} = $cgi->param($_) if $cgi->param($_); -} -$search_hash{'country'} = 'US'; -$search_hash{'classnum'} = [ $cgi->param('classnum') ]; - -my @row_option = grep { /^\d+$/ } $cgi->param('part2a_row_option') - if $cgi->param('part2a_row_option'); - -# fudge in two rows of LD carrier -unshift @row_option, $row_option[0]; - -# fudge in the first pair of rows -unshift @row_option, ''; -unshift @row_option, ''; - -my $query = 'SELECT '. join(' UNION SELECT ', 1..11); -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 || $row == 4 ) { - $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{'status'} = 'active'; - $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('part2a_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 = ( - '', - 'End user lines', - 'UNE-P replacement', - 'UNE (unswitched)', - 'UNE-P', +# 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\(\*\) //; + +# for row 1 +my $query_ds0 = "SELECT SUM(COALESCE(part_pkg.fcc_ds0s, pkg_class.fcc_ds0s, 0)) + $from_where AND fcc_voip_class = '4'"; # 4 = Local Exchange + +my $total_lines = FS::Record->scalar_sql($query_ds0); +# always return zero for the number of resold lines, until an actual ILEC +# starts using this report + +@data = ( + [ $total_lines ], + [ 0 ], + [ 0 ], + [ 0 ], ); -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" }, - sub { my $row = shift; my $rownum = $row->[0] + 1; "PartII_${rownum}d" }, +my @row_conds = ( + $is_residential, + $has_report_option->(0), # LD carrier + ($has_report_option->(0))[0] . $is_residential, + $has_report_option->(1..7), ); +if ( $total_lines > 0 ) { + foreach (@row_conds) { + my $sql = $query_ds0 . $_; + my $lines = FS::Record->scalar_sql($sql); + my $percent = sprintf('%.2f', 100 * $lines / $total_lines); + push @{ $data[0] }, $percent; + } +} my @rows = ( 'lines', '% residential', '% LD carrier', - '% residential and LD carrier', - '% own loops', - '% obtained unswitched UNE loops', + '% residential and LD', + '% owned loops', + '% unswitched UNE', '% UNE-P', '% UNE-P replacement', '% FTTP', @@ -103,13 +123,12 @@ my @rows = ( '% wireless', ); -my @fields = ( - sub { my $row = shift; $rows[$row->[0] - 1]; }, - sub { my $row = shift; &{$column_value}($row->[0]); }, - sub { 0; }, - sub { 0; }, - sub { 0; }, +my @row1_headers = ( + '', + 'End user lines', + 'UNE-P replacement', + 'unswitched UNE', + 'UNE-P', ); -shift @fields if $cgi->param('_type') eq 'xml'; diff --git a/httemplate/search/477partIIB.html b/httemplate/search/477partIIB.html index 714f15aca..5b9b30769 100755 --- a/httemplate/search/477partIIB.html +++ b/httemplate/search/477partIIB.html @@ -3,9 +3,9 @@ % for ( my $row = 0; $row < scalar(@rows); $row++ ) { % for my $col (0..2) { % if ( exists($data[$col][$row]) ) { -<% $cols[$col] %>>\ +<% $cols[$col] %>>\ <% $data[$col][$row] %>\ -<% $cols[$col] %>> +<% $cols[$col] %>> % } % } #for $col % } #for $row @@ -15,19 +15,18 @@

- +
% foreach (@headers) { - + % } -% my @bgcolor = ('eeeeee','ffffff'); % my $row = 0; % foreach my $rowhead (@rows) { - - + + % for my $col (0..2) { -
<% $_ %><% $_ %>
<% $rowhead %>
<% $rowhead %> + % if ( exists($data[$col][$row]) ) { <% $data[$col][$row] %> % } diff --git a/httemplate/search/477partV.html b/httemplate/search/477partV.html index 2106a44d6..b2dd9ca95 100755 --- a/httemplate/search/477partV.html +++ b/httemplate/search/477partV.html @@ -1,3 +1,6 @@ +% if ( $cgi->param('_type') =~ /^xml$/ ) { + +% } <& elements/search.html, 'html_init' => $html_init, 'name' => 'zip code', @@ -14,6 +17,9 @@ &> +% if ( $cgi->param('_type') =~ /^xml$/ ) { + +% } <%init> my $curuser = $FS::CurrentUser::CurrentUser; diff --git a/httemplate/search/elements/search.html b/httemplate/search/elements/search.html index 68c488837..d44b45465 100644 --- a/httemplate/search/elements/search.html +++ b/httemplate/search/elements/search.html @@ -353,7 +353,7 @@ if ( $opt{'disableable'} ) { my $limit = ''; my($confmax, $maxrecords, $offset ); -unless ( $type =~ /^(csv|\w*.xls)$/) { +unless ( $type =~ /^(csv|xml|\w*.xls)$/) { # html mode unless (exists($opt{count_query}) && length($opt{count_query})) { ( $opt{count_query} = $opt{query} ) =~ -- 2.11.0