X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=httemplate%2Fsearch%2F477.html;h=9a412612119dad052ec2e5ff7eb5e355a2e70efb;hp=0f1502f2a9f5ae3ad541fe413f249a73a0ee91d5;hb=HEAD;hpb=63a268637b2d51a8766412617724b9436439deb6 diff --git a/httemplate/search/477.html b/httemplate/search/477.html old mode 100755 new mode 100644 index 0f1502f2a..9a4126121 --- a/httemplate/search/477.html +++ b/httemplate/search/477.html @@ -1,182 +1,313 @@ -<% 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 ], - ], - ) -%> -<%init> - -my $curuser = $FS::CurrentUser::CurrentUser; +<& /elements/header.html, $title &> + +% foreach my $partname (@partnames) { +% my $this_part = $parts{$partname}; +% $cgi->param('parts', $partname); +% $cgi->param('type', 'csv'); + + +% my $header = ".header_$partname"; +% $header .= '_old' if $partname eq 'fbd' && $date < 1569826800; #9/30/2019 +% # ( halfway between the two filing "as of" dates when it changed +% my $data = $this_part->{data}; +% my $error = $this_part->{error}; + + <& $header &> + +% my $rownum = 0; +% foreach my $row (@$data) { +% my %eh; # error hash +% if ( $error->[$rownum] ) { +% %eh = %{ $error->[$rownum] }; +% } + > +% my $first = 1; +% foreach my $item (@$row) { + +% } #foreach $item +% # display errors +% if ( keys %eh ) { + +% } # if there are errors + +% $rownum++; +% } #foreach $row +
+ <% $part_titles->{$partname} %> +% if ( $this_part->{num_errors} > 0 ) { +% # show error heading while it contains errors + + <% emt('This section contains [quant,_1,error].', $this_part->{num_errors}) %> + +% } + Download +
+% if ($first and $part_link{$partname}) { + "><% $item || '(empty)' %> +% $first = 0; +% } else { + <% $item %> +% } +
    +% foreach my $key (sort keys %eh) { +
  • <% $eh{$key} %>
    • +% } +
+% } # foreach $partname +<& /elements/footer.html &> +<%init> die "access denied" - unless $curuser->access_right('List packages'); + unless $FS::CurrentUser::CurrentUser->access_right('List packages'); -my %search_hash = (); -my @sql_query = (); - -for ( qw(agentnum magic classnum) ) { - $search_hash{$_} = $cgi->param($_) if $cgi->param($_); +my %parts; +my %part_link; +# load from cache if possible +my $session; +if ( $cgi->param('session') =~ /^(\d+)$/ ) { + $session = $1; + %parts = %{ $m->cache->get($session) }; +} else { + $session = sprintf('%010d%06d', time, int(rand(1000000))); + $cgi->param('session', $session); } -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"; +my $agentnum; +if ($cgi->param('agentnum') =~ /^(\d+)$/ ) { + $agentnum = $1; } -$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( - { %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++; - } +my $date = parse_datetime($cgi->param('date')) || time; +my @partnames = grep /^\w+$/, $cgi->param('parts'); +my $ignore_quantity = ($cgi->param('ignore_quantity') ? 1 : 0); - $summary .= ""; - $rowcount++; +foreach my $partname (@partnames) { + $parts{$partname} ||= FS::Report::FCC_477->report( $partname, + date => $date, + agentnum => $agentnum, + ignore_quantity => $ignore_quantity, + ); # includes error, detail, and data parts + my $detail_table = FS::Report::FCC_477->part_table($partname); + if ($detail_table eq 'cust_pkg') { + my $link = popurl(1).'477_cust_pkg.html?477part='.$partname.";date=$date;"; + if ($agentnum) { + $link .= "agentnum=$agentnum;"; + } + $part_link{$partname} = $link; + } # don't include detail links to deploy_blocks, that's pointless } +$m->cache->set($session, \%parts, '1h'); + +my $title = 'FCC Form 477 Data - ' . time2str('%b %o, %Y', $date); + +if ( $cgi->param('type') eq 'csv' ) { + my $partname = $partnames[0]; # ignore any beyond the first + my $data = $parts{$partname}->{data}; + my $csv = Text::CSV_XS->new({ eol => "\r\n" }); # i think + + my $filename = time2str('%Y-%m-%d', $date) . '-'. $partname . '.csv'; + http_header('Content-Type' => 'text/csv'); + http_header('Content-Disposition' => qq(attachment;filename="$filename")); + + $m->clear_buffer; + + foreach my $row (@$data) { + $csv->combine(@$row); + $m->print($csv->string); + } + $m->abort; +} + +my $part_titles = FS::Report::FCC_477->parts; -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; - }; +<%def .header_fbd_old> + + Census Block + DBA Name + Technology + Consumer? + Advertised Speed (Mbps) + Business? + Contractual Speed (Mbps) + + + Down + Up + Down + Up + + +<%def .header_fbd> + + Census Block + DBA Name + Technology + Consumer? + Advertised Speed (Mbps) + Business? + + + Down + Up + + +<%def .header_fbs> + + Census Tract + Technology + Speed (Mbps) + Subscriptions + + + Down + Up + Total + Consumer + + +<%def .header_fvs> + + Census Tract + VoIP? + Lines/Subscriptions + + + Total + Consumer + + +<%def .header_lts> + + State + Wholesale + End User Lines + + + VGEs + UNE-Ls + + Total + With Broadband + Consumer + Business + + Local Loop + + Special Media + + + + + +LD + + +LD + + Owned + UNE-L + Resale + + Fiber + Coaxial + Wireless + + +<%def .header_voip> + + State + VoIP OTT + VoIP Non-OTT + + + Total + Consumer + + Total + Consumer + Bundled + Media Type + + + Copper + Fiber + Coaxial + Wireless + Other + + +<%def .header_mbs> +%# unimplemented + + State + Speed (Mbps) + Subscriptions + + + Down + Up + Total + Consumer + + +<%def .header_mvs> +%# unimplemented + + State + Subscriptions + + + Total + Direct + + +