X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=httemplate%2Fsearch%2F477.html;h=d586406af7120469cd0a3c527b81db992960b9c4;hb=636dd1f25af52d35efe7a323a5765ff5adeadf83;hp=b485161376c2ff03b56d026218b41cf7f9e72d30;hpb=1509cd837ea7e4e147a088d939b4198ee2456ef8;p=freeside.git diff --git a/httemplate/search/477.html b/httemplate/search/477.html index b48516137..d586406af 100755 --- a/httemplate/search/477.html +++ b/httemplate/search/477.html @@ -1,44 +1,76 @@ -<% include( 'elements/search.html', - 'title' => 'FCC Form 477 Results', - 'html_init' => $html_init, - 'name' => 'regions', - 'query' => [ @sql_query ], - 'count_query' => $count_query, - 'order_by' => 'ORDER BY censustract', - 'avoid_quote' => 1, - 'no_csv_header' => 1, - 'header' => [ - 'County code', - 'Census tract code', - 'Upload rate', - 'Download rate', - 'Technology code', - 'Technology code other', - 'Quantity', - 'Percentage residential', - ], - 'fields' => [ - sub { my $row = shift; substr($row->censustract, 2, 3) }, - sub { my $row = shift; substr($row->censustract, 5) }, - 'upload', - 'download', - sub { 7 }, - sub { '' }, - 'quantity', - sub { my $row = shift; sprintf "%.2f", $row->residential }, - ], - 'links' => [ - [ $link, $link_suffix ], - [ $link, $link_suffix ], - [ $link, $link_suffix ], - [ $link, $link_suffix ], - [ $link, $link_suffix ], - [ $link, $link_suffix ], - [ $link, $link_suffix ], - [ $link, $link_suffix ], - ], - ) -%> +% unless ( $type eq 'xml' ) { +<% include( '/elements/header.html', 'FCC Form 477 Results') %> +%}else{ + + +%} +% if ( $type eq 'html' || $type eq 'html-print' ) { + + +%}elsif ( $type eq 'xml' ) { +%} +% unless ( $type eq 'html-print' || $type eq 'xml' ) { + + +% $cgi->param('_type', $type ); +% } +% if ( $type eq 'html' || $type eq 'html-print' ) { + +
+ + Download full results
+% $cgi->param('_type', 'xml'); + as XML file
+ +% $cgi->param('_type', 'html-print'); + as printable copy + +
+%}elsif ( $type eq 'xml' ) { +%} +% foreach my $part ( @parts ) { +% if ( $part{$part} ) { +% +% if ( $part eq 'V' ) { +% next unless ( $part{'IIA'} || $part{'IIB'} ); +% } +% +% if ( $part eq 'VI_census' ) { +% next unless $part{'IA'}; +% } +% +% my @reports = (); +% if ( $part eq 'IA' ) { +% for ( my $tech = 0; $tech < scalar(@technology_option); $tech++ ) { +% next unless $technology_option[$tech]; +% my $url = &{$url_mangler}($part); +% if ( $type eq 'xml' ) { +<<% 'Part_IA_'. chr(65 + $tech) %>> +% } +<% include( "477part${part}_summary.html", 'tech_code' => $tech, 'url' => $url ) %> +<% include( "477part${part}_detail.html", 'tech_code' => $tech, 'url' => $url ) %> +% if ( $type eq 'xml' ) { +> +% } +% } +% } else { +% if ( $type eq 'xml' ) { +<<% 'Part_'. $part %>> +% } +% my $url = &{$url_mangler}($part); +<% include( "477part${part}.html", 'url' => $url ) %> +% if ( $type eq 'xml' ) { +> +% } +% } +% } +% } +% +% if ( $type eq 'html' || $type eq 'html-print' ) { +<% include( '/elements/footer.html') %> +%}elsif ( $type eq 'xml' ) { + +%} <%init> my $curuser = $FS::CurrentUser::CurrentUser; @@ -46,137 +78,16 @@ my $curuser = $FS::CurrentUser::CurrentUser; die "access denied" unless $curuser->access_right('List packages'); -my %search_hash = (); -my @sql_query = (); - -for ( qw(agentnum magic classnum) ) { - $search_hash{$_} = $cgi->param($_) if $cgi->param($_); -} +my %part = map { $_ => 1 } grep { /^\w+$/ } $cgi->param('part'); +my $type = $cgi->param('_type') || 'html'; +my $xlsname = '477report'; +my @technology_option = &FS::Report::FCC_477::parse_technology_option($cgi); +my $url_mangler = sub { + my $part = shift; + my $url = $cgi->url('-path_info' => 1, '-full' => 1); + $url =~ s/477\./477part$part./; + $url; +}; +my @parts = qw( IA IIA IIB IV V VI_census ); -my @column_option = $cgi->param('column_option') - if $cgi->param('column_option'); - -my @row_option = $cgi->param('row_option') - if $cgi->param('row_option'); - -my $where = join(' OR ', map { "num = $_" } grep { /^\d+$/ } @column_option ); -my %column_option_name = $where ? - ( map { $_->name => $_->num } - qsearch({ 'table' => 'part_pkg_report_option', - 'hashref' => {}, - 'extra_sql' => "WHERE $where", - }) - ) : - ( 'all packages' => '' ); - -$where = join(' OR ', map { "num = $_" } grep { /^\d+$/ } @row_option ); -my %row_option_name = $where ? - ( map { $_->name => $_->num } - qsearch({ 'table' => 'part_pkg_report_option', - 'hashref' => {}, - 'extra_sql' => "WHERE $where", - }) - ) : - ( 'all packages' => '' ); - -@row_option = map { $row_option_name{$_} } sort keys %row_option_name; -@column_option = map { $column_option_name{$_} } sort keys %column_option_name; - -#$search_hash{row_option} = join(',', @row_option) if @row_option; -my $summary .= '

Summary

'. include('/elements/table.html'); -$summary .= ''; -foreach my $column ( sort keys %column_option_name ) { - $summary .= "$column"; -} -$summary .= ""; - -my $total_count = 0; -my $total_residential = 0; -my $rowcount = 1; -foreach my $row ( sort keys %row_option_name ) { - - $summary .= "$row"; - - my $columncount = 2; - foreach my $column ( sort keys %column_option_name ) { - my @report_option = (); - push @report_option, $row_option_name{$row} - if $row_option_name{$row}; - push @report_option, $column_option_name{$column} - if $column_option_name{$column}; - my $report_option = join(',', @report_option) if @report_option; - - my $sql_query = FS::cust_pkg->search_sql( - { %search_hash, - ($report_option ? ( 'report_option' => $report_option ) : () ), - } - ); - my $extracolumns = "$rowcount AS upload, $columncount AS download"; - my $percent = "CASE WHEN count(*) > 0 THEN 100-100*cast(count(cust_main.company) as numeric)/cast(count(*) as numeric) ELSE cast(0 as numeric) END AS residential"; - $sql_query->{select} = "count(*) AS quantity, $extracolumns, censustract, $percent"; - $sql_query->{extra_sql} =~ /^(.*)(ORDER BY pkgnum)(.*)$/s - or die "couldn't parse extra_sql"; - $sql_query->{extra_sql} = "$1 GROUP BY censustract $3"; - - my $count_sql = delete($sql_query->{'count_query'}); - $count_sql =~ s/ FROM/,count(cust_main.company) 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]; - $total_count += $count; - my $residential = $count_arrayref->[1]; - $total_residential += $residential; - my $percentage = sprintf('%.2f', $count ? 100-100*$residential/$count : 0); - - $summary .= "$count
$percentage% residential"; - push @sql_query, $sql_query; - $columncount++; - } - - $summary .= ""; - $rowcount++; -} - -my $total_percentage = - sprintf("%.2f", $total_count ? 100-100*$total_residential/$total_count : 0); - -my $html_init = '

Totals

'. include('/elements/table.html'). ""; -$html_init .= "Total Connections"; -$html_init .= "% owned loop"; -$html_init .= "% billed to end users"; -$html_init .= "% residential"; -$html_init .= "% residential > 200kbps"; -$html_init .= ""; -$html_init .= "$total_count"; -$html_init .= "100.00"; -$html_init .= "100.00"; -$html_init .= "$total_percentage"; -$html_init .= "$total_percentage"; -$html_init .= "
"; -$html_init .= $summary; -$html_init .= "

Details

"; - -my $count_query = 'SELECT count(*) FROM ( ('. - join( ') UNION (', - map { my $extra = $_->{extra_sql}; my $addl = $_->{addl_from}; - "SELECT censustract from cust_pkg $addl $extra"; - } - @sql_query - ). ') ) AS foo'; - -my $link = 'cust_pkg.cgi?'. - join(';', map{ "$_=". $search_hash{$_} } keys %search_hash). ';'; -my $link_suffix = sub { my $row = shift; - my $result = 'censustract='. $row->censustract. ';'; - $result .= 'report_option='. @row_option[$row->upload - 1] - if @row_option[$row->upload - 1]; - $result .= 'report_option='. @column_option[$row->download - 1] - if @column_option[$row->download - 1]; - $result; - };