477 report as of a specific date, #24980

diff --git a/FS/FS/cust_pkg.pm b/FS/FS/cust_pkg.pm
index 1430aef..5779d5c 100644 (file)
--- a/FS/FS/cust_pkg.pm
+++ b/FS/FS/cust_pkg.pm
@@ -4617,12 +4617,18 @@ sub search {
   );
 
   if( exists($params->{'active'} ) ) {
-    # This overrides all the other date-related fields
+    # This overrides all the other date-related fields, and includes packages
+    # that were active at some time during the interval.  It excludes:
+    # - packages that were set up after the end of the interval
+    # - packages that were canceled before the start of the interval
+    # - packages that were suspended before the start of the interval
+    #   and are still suspended now
     my($beginning, $ending) = @{$params->{'active'}};
     push @where,
       "cust_pkg.setup IS NOT NULL",
       "cust_pkg.setup <= $ending",
       "(cust_pkg.cancel IS NULL OR cust_pkg.cancel >= $beginning )",
+      "(cust_pkg.susp   IS NULL OR cust_pkg.susp   >= $beginning )",
       "NOT (".FS::cust_pkg->onetime_sql . ")";
   }
   else {

diff --git a/httemplate/search/477.html b/httemplate/search/477.html
index eed3df9..3e7888c 100755 (executable)
--- a/httemplate/search/477.html
+++ b/httemplate/search/477.html
@@ -49,18 +49,22 @@
 <& "477part${part}.html",
     'tech_code' => $tech,
     'url' => $url,
-    'type' => $type
+    'type' => $type,
+    'date' => $date,
 &>
 %         if ( $type eq 'xml' ) {
 </<% 'Part_IA_'. chr(65 + $tech) %>>
 %         }
 %       }
-%     } else {
+%     } else { # not part IA
 %       if ( $type eq 'xml' ) {
 <<% 'Part_'. $part %>>
 %       }
 %       my $url = &{$url_mangler}($part);
-<& "477part${part}.html", 'url' => $url &>
+<& "477part${part}.html",
+    'url' => $url,
+    'date' => $date,
+&>
 %       if ( $type eq 'xml' ) {
 </<% 'Part_'. $part %>>
 %       }
@@ -80,6 +84,9 @@ my $curuser = $FS::CurrentUser::CurrentUser;
 die "access denied"
   unless $curuser->access_right('List packages');
 
+my $date = $cgi->param('date') ? parse_datetime($cgi->param('date'))
+                               : time;
+
 my $state = uc($cgi->param('state'));
 $state =~ /^[A-Z]{2}$/ or die "illegal state: $state";
 

diff --git a/httemplate/search/477partIA.html b/httemplate/search/477partIA.html
index 6b4bffd..aa73811 100755 (executable)
--- a/httemplate/search/477partIA.html
+++ b/httemplate/search/477partIA.html
@@ -84,7 +84,6 @@ 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') ];
 
@@ -114,6 +113,13 @@ if ( $technology eq 'Symmetric xDSL' or $technology eq 'Other Wireline' ) {
 # whether to show residential percentages in each cell of the matrix
 my $percentages = ($technology eq 'Terrestrial Mobile Wireless');
 
+# as of date
+# FCC 477 instructions: "Only count connections that are in service."
+# So we count packages that were in active status as of the specified date,
+# not over any sort of range.
+$search_hash{'active'} = [ $opt{date}, $opt{date} ];
+warn Dumper \%search_hash;
+
 my $query = FS::cust_pkg->search(\%search_hash);
 my $count_query = $query->{'count_query'};
 

diff --git a/httemplate/search/477partIIA.html b/httemplate/search/477partIIA.html
index 907a176..467b19c 100755 (executable)
--- a/httemplate/search/477partIIA.html
+++ b/httemplate/search/477partIIA.html
@@ -40,25 +40,25 @@
 </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{'status'}   = 'active';
+$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 $is_residential = " AND COALESCE(cust_main.company, '') = ''";
 my $has_report_option = sub {
   map {
     defined($row_option[$_]) ?

diff --git a/httemplate/search/477partIIB.html b/httemplate/search/477partIIB.html
index cb181f4..ce1ac03 100755 (executable)
--- a/httemplate/search/477partIIB.html
+++ b/httemplate/search/477partIIB.html
@@ -46,28 +46,29 @@ 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{'status'}   = 'active';
+$search_hash{'active'}   = [ $opt{date}, $opt{date} ];
 
 my @row_option;
 foreach ($cgi->param('part2b_row_option')) {
   push @row_option, (/^\d+$/ ? $_ : undef);
 }
 
-my $is_residential = "AND COALESCE(cust_main.company, '') = ''";
+my $is_residential = " AND COALESCE(cust_main.company, '') = ''";
 my $has_report_option = sub {
   map {
     defined($row_option[$_]) ?
-    "AND EXISTS(
+    " 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'
+    )" : ' AND FALSE'
   } @_
 };
 

diff --git a/httemplate/search/477partV.html b/httemplate/search/477partV.html
index b2dd9ca..5f77081 100755 (executable)
--- a/httemplate/search/477partV.html
+++ b/httemplate/search/477partV.html
@@ -33,13 +33,14 @@ my %search_hash = ();
 my @sql_query = ();
 my @count_query = ();
   
-for ( qw(agentnum magic state) ) {
+for ( qw(agentnum state) ) {
   $search_hash{$_} = $cgi->param($_) if $cgi->param($_);
 }
 $search_hash{'country'} = 'US';
 $search_hash{'classnum'} = [ $cgi->param('classnum') ];
 $search_hash{report_option} = $cgi->param('part5_report_option')
   if $cgi->param('part5_report_option');
+$search_hash{'active'}   = [ $opt{date}, $opt{date} ];
 
 my $sql_query = FS::cust_pkg->search( { %search_hash,
                                         'fcc_line'    => 1,

diff --git a/httemplate/search/477partVI_census.html b/httemplate/search/477partVI_census.html
index 0dafc6b..b4f6ddc 100755 (executable)
--- a/httemplate/search/477partVI_census.html
+++ b/httemplate/search/477partVI_census.html
@@ -81,9 +81,11 @@ push @fields,
 my %search_hash = ();
 my @sql_query = ();
   
-for ( qw(agentnum magic state) ) {
+for ( qw(agentnum state) ) {
   $search_hash{$_} = $cgi->param($_) if $cgi->param($_);
 }
+
+$search_hash{'active'}  = [ $opt{date}, $opt{date} ];
 $search_hash{'country'} = 'US';
 $search_hash{'classnum'} = [ $cgi->param('classnum') ]
   if grep { $_ eq 'classnum' } $cgi->param;
@@ -115,12 +117,11 @@ foreach my $row ( @row_option ) {
           ($report_option ? ( 'report_option' => $report_option ) : () ),
         }
       );
+warn Dumper($sql_query) if $rowcount==1 and $columncount==3;
       my $extracolumns = "$rowcount AS upload, $columncount AS download, $tech_code as technology_code";
       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, cust_location.censustract, $percent";
-      $sql_query->{order_by} =~ /^(.*)(ORDER BY pkgnum)(.*)$/s
-        or die "couldn't parse order_by";
-      $sql_query->{order_by} = "$1 GROUP BY cust_location.censustract $3";
+      $sql_query->{order_by} = " GROUP BY cust_location.censustract ";
       push @sql_query, $sql_query;
     }
     $columncount++;

diff --git a/httemplate/search/report_477.html b/httemplate/search/report_477.html
index b842b1f..a5dd70b 100755 (executable)
--- a/httemplate/search/report_477.html
+++ b/httemplate/search/report_477.html
@@ -29,6 +29,13 @@
         'records'       => \@states,
     &>
 
+    <& /elements/tr-input-date-field.html, {
+        'label'         => 'As of date',
+        'name'          => 'date',
+        'value'         => '',
+        'format'        => '%m/%d/%Y'
+    } &>
+
     <% include( '/elements/tr-select-pkg_class.html',
                    'multiple'       => 1,
                    'empty_label' => '(empty class)',