-<% 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 d);
+% for ( my $row = 0; $row < scalar(@rows); $row++ ) {
+% for my $col (0..3) {
+% if ( exists($data[$col][$row]) and $data[$col][$row] > 0 ) {
+<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 IIA</H2>
+<TABLE>
+ <TR><TD VALIGN="bottom"><BR></TD></TR>
+ <TR><TD COLSPAN=2>
+ <TABLE CLASS="grid" CELLSPACING=0>
+ <TR>
+% foreach (@row1_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..3) {
+ <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 IIA</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('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{'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('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('%.3f', 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',
'% 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';
</%init>
projects / freeside.git / blobdiff