477 report rewrite, #28020

[freeside.git] / httemplate / search / old477 / 477partIIA.html

diff --git a/httemplate/search/old477/477partIIA.html b/httemplate/search/old477/477partIIA.html
new file mode 100755 (executable)
index 0000000..7ccee69
--- /dev/null
+++ b/httemplate/search/old477/477partIIA.html
@@ -0,0 +1,132 @@
+% 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) {
+<PartII_<% $row + 1 %><% $cols[$col] %>>\
+<% $data[$col][$row] || 0 %>\
+</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 %opt = @_;
+my %search_hash = ();
+
+$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} ];
+
+my @row_option;
+foreach ($cgi->param('part2a_row_option')) {
+  push @row_option, (/^\d+$/ ? $_ : undef);
+}
+
+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'
+  } @_
+};
+
+# 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 @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',
+  '% owned loops',
+  '% unswitched UNE',
+  '% UNE-P',
+  '% UNE-P replacement',
+  '% FTTP',
+  '% coax',
+  '% wireless',
+);
+
+my @row1_headers = (
+  '',
+  'End user lines',
+  'UNE-P replacement',
+  'unswitched UNE',
+  'UNE-P',
+);
+
+</%init>