477 report: detect errors and fix them more easily, #32499, from #24047

7 files changed, 478 insertions, 13 deletions

diff --git a/FS/FS/Report/FCC_477.pm b/FS/FS/Report/FCC_477.pm
index 20d402d..af45b2d 100644
--- a/FS/FS/Report/FCC_477.pm
+++ b/FS/FS/Report/FCC_477.pm
@@ -265,7 +265,8 @@ sub active_on {
   # "suspended as of some past date" is a complicated query.)
   my $date = shift;
   "cust_pkg.setup <= $date AND ".
-  "(cust_pkg.cancel IS NULL OR cust_pkg.cancel > $date)";
+  "(cust_pkg.cancel IS NULL OR cust_pkg.cancel > $date) AND ".
+  "(cust_pkg.change_date IS NULL OR cust_pkg.change_date <= $date)"
 }
 
 sub is_fixed_broadband {
@@ -276,28 +277,68 @@ sub is_mobile_broadband {
   "is_broadband::int = 1 AND technology::int IN( 80, 81, 82, 83, 84, 85, 86, 87, 88)"
 }
 
+
 =item report SECTION, OPTIONS
 
 Returns the report section SECTION (see the C<parts> method for section 
-name strings) as an arrayref of arrayrefs.  OPTIONS may contain the following:
+name strings).  OPTIONS may contain the following:
 
 - date: a timestamp value. Packages that were active on that date will be 
 counted.
 
 - agentnum: limit to packages with this agent.
 
-- detail: if true, the report will contain an additional column which contains
-the keys of all objects aggregated in the row.
-
 - ignore_quantity: if true, package quantities will be ignored (only distinct
 packages will be counted).
 
+The result will be a hashref containing three parallel arrayrefs:
+- "data", the columns required by the FCC.
+- "detail", a list of the package numbers included in each row's aggregation
+- "error", a hashref containing any error status strings in that row. Keys
+are error identifiers, values are the messages to show the user.
+as well as an informational item:
+- "num_errors", the number of rows that contain errors
+
+=item report_data SECTION, OPTIONS
+
+Returns only the data, not the detail or error columns.  This is the part that
+will be submitted to the FCC.
+
 =cut
 
 sub report {
   my $class = shift;
   my $section = shift;
   my %opt = @_;
+  $opt{detail} = 1;
+
+  # add the error column
+  my $data = $class->report_data($section, %opt);
+  my $error = [];
+  my $detail = [];
+  my $check_method = $section.'_check';
+  my $num_errors = 0;
+  foreach my $row (@$data) {
+    if ( $class->can($check_method) ) { # they don't all have these
+      my $eh = $class->$check_method( $row );
+      $num_errors++ if keys(%$eh);
+      push $error, $eh
+    }
+    push @$detail, pop @$row; # this comes from the query
+  }
+  
+  return +{
+    data => $data,
+    error => $error,
+    detail => $detail,
+    num_errors => $num_errors,
+  };
+}
+
+sub report_data {
+  my $class = shift;
+  my $section = shift;
+  my %opt = @_;
 
   my $method = $section.'_sql';
   die "Report section '$section' is not implemented\n"
@@ -307,7 +348,7 @@ sub report {
   warn $statement if $DEBUG;
   my $sth = dbh->prepare($statement);
   $sth->execute or die $sth->errstr;
-  $sth->fetchall_arrayref;
+  return $sth->fetchall_arrayref;
 }
 
 sub fbd_sql {
@@ -395,6 +436,34 @@ sub fbs_sql {
 
 }
 
+sub fbs_check {
+  my $class = shift;
+  my $row = shift;
+  my %e;
+  #censustract
+  if ( length($row->[0]) == 0 ) {
+    $e{'censustract_null'} = 'The package location has no census tract.';
+  } elsif ($row->[0] !~ /^\d{11}$/) {
+    $e{'censustract_bad'} = 'The census tract must be exactly 11 digits.';
+  }
+
+  #technology
+  if ( length($row->[1]) == 0 ) {
+    $e{'technology_null'} = 'The package has no technology type.';
+  }
+
+  #speeds
+  if ( length($row->[2]) == 0 or length($row->[3]) == 0 ) {
+    $e{'speed_null'} = 'The package is missing downstream or upstream speeds.';
+  } elsif ( $row->[2] !~ /^\d*(\.\d+)?$/ or $row->[3] !~ /^\d*(\.\d+)?$/ ) {
+    $e{'speed_bad'} = 'The downstream and upstream speeds must be decimal numbers in Mbps.';
+  } elsif ( $row->[2] == 0 or $row->[3] == 0 ) {
+    $e{'speed_zero'} = 'The downstream and upstream speeds cannot be zero.';
+  }
+
+  return \%e;
+}
+
 sub fvs_sql {
   my $class = shift;
   my %opt = @_;
@@ -440,6 +509,19 @@ sub fvs_sql {
 
 }
 
+sub fvs_check {
+  my $class = shift;
+  my $row = shift;
+  my %e;
+  #censustract
+  if ( length($row->[0]) == 0 ) {
+    $e{'censustract_null'} = 'The package location has no census tract.';
+  } elsif ($row->[0] !~ /^\d{11}$/) {
+    $e{'censustract_bad'} = 'The census tract must be exactly 11 digits.';
+  }
+  return \%e;
+}
+
 sub lts_sql {
   my $class = shift;
   my %opt = @_;
diff --git a/FS/FS/cust_pkg/Search.pm b/FS/FS/cust_pkg/Search.pm
index aacd387..89809de 100644
--- a/FS/FS/cust_pkg/Search.pm
+++ b/FS/FS/cust_pkg/Search.pm
@@ -585,9 +585,8 @@ sub search {
         'agentnum'  => $agentnum,
         'detail'    => 1
       );
-      my $row = $report->[$rownum]
+      my $pkgnums = $report->{detail}->[$rownum]
         or die "row $rownum is past the end of the report";
-      my $pkgnums = $row->[-1] || '0';
         # '0' so that if there are no pkgnums (empty string) it will create
         # a valid query that returns nothing
       warn "PKGNUMS:\n$pkgnums\n\n"; # XXX debug
diff --git a/httemplate/edit/cust_location-censustract.html b/httemplate/edit/cust_location-censustract.html
new file mode 100644
index 0000000..bdb9823
--- /dev/null
+++ b/httemplate/edit/cust_location-censustract.html
@@ -0,0 +1,66 @@
+<% include('/elements/header-popup.html', "Edit Census Tract") %>
+
+<% include('/elements/error.html') %>
+
+<FORM NAME="EditLocationForm" 
+ACTION="<% $p %>edit/process/cust_location-censustract.html" METHOD=POST>
+<INPUT TYPE="hidden" NAME="locationnum" VALUE="<% $locationnum %>">
+
+<% ntable('#cccccc') %>
+<& /elements/location.html,
+  'object'              => $cust_location,
+  'no_asterisks'        => 1,
+  'enable_censustract'  => 1,
+  'disabled'            => 'DISABLED',
+&>
+<& /elements/standardize_locations.html,
+            'form'          => 'EditLocationForm',
+            'callback'      => 'document.EditLocationForm.submit();',
+            'with_census'   => 1,
+            'with_census_functions' => 1,
+&>
+</TABLE>
+
+<BR>
+<SCRIPT TYPE="text/javascript">
+<&| /elements/onload.js &>
+  document.getElementById('enter_censustract').disabled = false;
+</&>
+function go() {
+  confirm_censustract();
+}
+
+function submit_abort() {
+  nd(1);
+}
+</SCRIPT>
+<INPUT TYPE="button" NAME="submitButton" VALUE="Submit" onclick="go()">
+</FORM>
+</BODY>
+</HTML>
+
+<%init>
+
+my $conf = new FS::Conf;
+
+my $curuser = $FS::CurrentUser::CurrentUser;
+
+# it's the same access right you'd need to do this by editing packages
+die "access denied"
+  unless $curuser->access_right('Change customer package');
+
+my $locationnum = scalar($cgi->param('locationnum'));
+my $cust_location = qsearchs({
+    'select'    => 'cust_location.*',
+    'table'     => 'cust_location',
+    'addl_from' => 'LEFT JOIN cust_main USING ( custnum )',
+    'hashref'   => { 'locationnum' => $locationnum },
+    'extra_sql' => ' AND '. $curuser->agentnums_sql,
+  }) or die "unknown locationnum $locationnum";
+
+# unlike the regular one, this allows editing disabled locations
+
+my $cust_main = qsearchs('cust_main', { 'custnum' => $cust_location->custnum })
+  or die "can't get cust_main record for custnum ". $cust_location->custnum;
+
+</%init>
diff --git a/httemplate/edit/process/bulk-477_cust_pkg.html b/httemplate/edit/process/bulk-477_cust_pkg.html
new file mode 100644
index 0000000..064f73b
--- /dev/null
+++ b/httemplate/edit/process/bulk-477_cust_pkg.html
@@ -0,0 +1,20 @@
+<% $cgi->redirect($fsurl.'search/477_cust_pkg.html?redirect='.$session) %>
+<%init>
+my $curuser = $FS::CurrentUser::CurrentUser;
+my $edit_acl = $curuser->access_right('Edit FCC report configuration');
+my $global_edit_acl = $curuser->access_right('Edit FCC report configuration for all agents');
+die "access denied" unless $edit_acl or $global_edit_acl;
+
+my %error;
+foreach my $param ($cgi->param) {
+  $param =~ /^pkgnum(\d+)pkgpart(\d+)$/ or next;
+  my $pkgpart = $2;
+  my $part_pkg = FS::part_pkg->by_key($pkgpart);
+  my $hashref = decode_json( $cgi->param($param) );
+  my $error = $part_pkg->set_fcc_options($hashref);
+  $error{$pkgpart} = $error if $error; # XXX report this somehow
+}
+
+my $session = $cgi->param('redirect');
+
+</%init>
diff --git a/httemplate/edit/process/cust_location-censustract.html b/httemplate/edit/process/cust_location-censustract.html
new file mode 100644
index 0000000..bc9cd4f
--- /dev/null
+++ b/httemplate/edit/process/cust_location-censustract.html
@@ -0,0 +1,34 @@
+% if ($error) {
+%   $cgi->param('error', $error);
+%   $cgi->redirect(popurl(3). 'edit/cust_location-censustract.html?'. $cgi->query_string );
+% } else {
+
+    <% header("Census tract changed") %>
+      <SCRIPT TYPE="text/javascript">
+        window.top.location.reload();
+      </SCRIPT>
+    </BODY>
+    </HTML>
+
+% }
+<%init>
+
+my $curuser = $FS::CurrentUser::CurrentUser;
+
+die "access denied"
+  unless $curuser->access_right('Change customer package');
+
+my $locationnum = $cgi->param('locationnum');
+my $cust_location = qsearchs({
+  'select'    => 'cust_location.*',
+  'table'     => 'cust_location',
+  'addl_from' => 'LEFT JOIN cust_main USING ( custnum )',
+  'hashref'   => { 'locationnum' => $locationnum },
+  'extra_sql' => ' AND '. $curuser->agentnums_sql,
+});
+die "unknown locationnum $locationnum" unless $cust_location;
+
+$cust_location->set('censustract', $cgi->param('censustract'));
+my $error = $cust_location->replace;
+
+</%init>
diff --git a/httemplate/search/477.html b/httemplate/search/477.html
index ff2ac86..2e9f942 100644
--- a/httemplate/search/477.html
+++ b/httemplate/search/477.html
@@ -32,26 +32,54 @@ table.fcc477part thead tr.subhead {
   font-size: large;
   float: left;
 }
+.errortitle {
+  font-weight: bold;
+  color: #ff0000;
+}
+tr.error td {
+  background-color: #ffdddd;
+}
+tr.error td.error {
+  text-align: left;
+  border: none;
+}
+tr.error ul {
+  margin: 0px;
+  list-style-image: url("<% $fsurl %>images/cross.png");
+}
 a.download {
   float: right;
 }
 </STYLE>
 % foreach my $partname (@partnames) {
+%   my $this_part = $parts{$partname};
 %   $cgi->param('parts', $partname);
 %   $cgi->param('type', 'csv');
 <table class="fcc477part">
   <caption>
     <span class="parttitle"><% $part_titles->{$partname} %></span>
+%   if ( $this_part->{num_errors} > 0 ) {
+%     # disable downloading while it contains errors
+    <span class="errortitle">
+      <% emt('This section contains [quant,_1,error].', $this_part->{num_errors}) %>
+    </span>
+%   } else {
     <a class="download" href="<% $cgi->self_url %>">Download</a>
+%   }
   </caption>
 %   my $header = ".header_$partname";
-%   my $data = $parts{$partname};
+%   my $data = $this_part->{data};
+%   my $error = $this_part->{error};
   <thead>
     <& $header &>
   </thead>
 %   my $rownum = 0;
 %   foreach my $row (@$data) {
-  <tr>
+%     my %eh; # error hash
+%     if ( $error->[$rownum] ) {
+%       %eh = %{ $error->[$rownum] };
+%     }
+  <tr<% keys(%eh) ? ' class="error"' : ''%>>
 %     my $first = 1;
 %     foreach my $item (@$row) {
     <td>
@@ -63,6 +91,14 @@ a.download {
 %     }
     </td>
 %   } #foreach $item
+%   # display errors
+%   if ( keys %eh ) {
+    <td class="error"><ul>
+%     foreach my $key (sort keys %eh) {
+      <li><% $eh{$key} %></li>
+%     }
+    </ul></td>
+%   } # if there are errors
   </tr>
 %   $rownum++;
 %   } #foreach $row
@@ -98,10 +134,10 @@ foreach my $partname (@partnames) {
     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).'cust_pkg.cgi?477part='.$partname.";date=$date;";
+    my $link = popurl(1).'477_cust_pkg.html?477part='.$partname.";date=$date;";
     if ($agentnum) {
       $link .= "agentnum=$agentnum;";
     }
@@ -114,7 +150,7 @@ 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};
+  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';
diff --git a/httemplate/search/477_cust_pkg.html b/httemplate/search/477_cust_pkg.html
new file mode 100644
index 0000000..b8df9fd
--- /dev/null
+++ b/httemplate/search/477_cust_pkg.html
@@ -0,0 +1,228 @@
+<& elements/search.html,
+                  'html_init'   => $html_init,
+                  'html_form'   => $html_form,
+                  'html_foot'   => '</FORM>',
+                  'title'       => emt('Package Search Results'), 
+                  'name'        => 'packages',
+                  'query'       => $query,
+                  'count_query' => $count_query,
+                  'header'      => [ emt('#'),
+                                     emt('Quan.'),
+                                     emt('Package'),
+                                     emt('Class'),
+                                     emt('Status'),
+                                     emt('Freq.'),
+                                     emt('Setup'),
+                                     emt('Next bill'),
+                                     emt('Susp.'),
+                                     emt('Changed'),
+                                     emt('Cancel'),
+                                     FS::UI::Web::cust_header(),
+                                     emt('Census tract'),
+                                     emt('Package options'),
+                                   ],
+                  'fields'      => [
+                    'pkgnum',
+                    'quantity',
+                    sub { $_[0]->pkg; },
+                    'classname',
+                    sub { ucfirst(shift->status); },
+                    sub { FS::part_pkg::freq_pretty(shift); },
+
+                    ( map { time_or_blank($_) }
+                      qw( setup bill susp change_date cancel ) ),
+
+                    \&FS::UI::Web::cust_fields,
+
+                    sub { # census tract
+                      my $cust_pkg = shift;
+                      my $cust_location = $cust_pkg->cust_location;
+                      ($cust_location->censustract || '<b>unknown</b>').
+                        '<font size="-1"> (edit)</font>';
+                    },
+
+                    # a hidden input in each row with the pkgnum, so that
+                    # we can refresh back to this list of pkgnums
+                    sub {
+                      my $cust_pkg = shift;
+                      my $part_pkg = $cust_pkg->part_pkg;
+                      my %hash = $part_pkg->fcc_options;
+                      '<INPUT NAME="pkgnum" TYPE="hidden" VALUE="' .
+                        $cust_pkg->pkgnum . '">' .
+                      include('/elements/input-fcc_options.html',
+                        id          => 'pkgnum'.$cust_pkg->pkgnum.
+                                       'pkgpart'.$part_pkg->pkgpart,
+                        curr_value  => encode_json(\%hash),
+                        html_only   => 1
+                      )
+                    },
+                  ],
+                  'color' => [
+                    '',
+                    '',
+                    '',
+                    '',
+                    sub { shift->statuscolor; },
+                    '', '', '', '', '', '',
+                    FS::UI::Web::cust_colors(),
+                    '',
+                    '',
+                  ],
+                  'style' => [ '', '', '', '', 'b', 
+                               '', '', '', '', '', '',
+                               FS::UI::Web::cust_styles() ],
+                  'size'  => [ '', '', '', '', '-1' ],
+                  'align' => 'rrlcccrrrrr'. FS::UI::Web::cust_aligns(). 'cl',
+                  'links' => [
+                    $link,
+                    $link,
+                    $link,
+                    '', '', '', '', '', '', '', '',
+                    ( map { $_ ne 'Cust. Status' ? $clink : '' }
+                          FS::UI::Web::cust_header()
+                    ),
+                    '',
+                    '',
+                  ],
+                  'link_onclicks' => [
+                    (('') x 11),
+                    (map { '' } FS::UI::Web::cust_header()),
+                    $pkg_edit_location_link,
+                    '',
+                  ],
+
+&>
+<%init>
+
+my $curuser = $FS::CurrentUser::CurrentUser;
+my $edit        = 'Edit FCC report configuration';
+my $edit_global = 'Edit FCC report configuration for all agents';
+my $acl_edit        = $curuser->access_right($edit);
+my $acl_edit_global = $curuser->access_right($edit_global);
+
+die "access denied"
+  unless $acl_edit || $acl_edit_global;
+
+my $conf = new FS::Conf;
+
+my $session;
+
+my ($query, $count_query);
+
+if ( $cgi->param('redirect') ) { # then restore the pkgnum list
+  $session = $cgi->param('redirect');
+  my $pref = $curuser->option("redirect$session"); # contains a list of pkgnums
+  die "unknown redirect session $session\n" unless length($pref);
+  my @pkgnums = grep /^\d+$/, split(',', $pref);
+  
+  $query = FS::cust_pkg->search({});
+  $count_query = delete($query->{count_query});
+
+  my $where = "cust_pkg.pkgnum IN (".join(',', @pkgnums).")";
+  if ( $count_query =~ /WHERE/i ) {
+    $where = " AND ($where) ";
+  } else {
+    $where = " WHERE ($where) ";
+  }
+  $query->{extra_sql} .= $where;
+  $count_query .= $where;
+} else {
+  # build and run the query right now, and then cache the pkgnums it returned
+  my %search_hash = ();
+    
+  #scalars
+  for (qw( agentnum 477part 477rownum date )) {
+    $search_hash{$_} = $cgi->param($_) if length($cgi->param($_));
+  }
+
+  $query = FS::cust_pkg->search(\%search_hash);
+  $count_query = delete($query->{'count_query'});
+
+  my @cust_pkg = qsearch($query);
+
+  my $pkgnums = join(',', map { $_->pkgnum } @cust_pkg);
+  $session = int(rand(4294967296)); #XXX
+  my $pref = new FS::access_user_pref({
+    'usernum'    => $FS::CurrentUser::CurrentUser->usernum,
+    'prefname'   => "redirect$session",
+    'prefvalue'  => $pkgnums,
+    'expiration' => time + 3600, #1h?  1m?
+  });
+  my $pref_error = $pref->insert;
+  if ($pref_error) {
+    die "couldn't even set redirect cookie: $pref_error\n";
+  }
+
+  # and then bail out and reload using the redirect cookie
+  $cgi->delete_all();
+  $cgi->param("redirect", $session);
+  $m->clear_buffer;
+  $m->print( $cgi->redirect($cgi->self_url) );
+  $m->abort;
+}
+
+my $show = $curuser->default_customer_view =~ /^(jumbo|packages)$/
+             ? ''
+             : ';show=packages';
+
+my $link = sub {
+  my $self = shift;
+  my $frag = 'cust_pkg'. $self->pkgnum; #hack for IE ignoring real #fragment
+  [ "${p}view/cust_main.cgi?custnum=".$self->custnum.
+                           "$show;fragment=$frag#cust_pkg",
+    'pkgnum'
+  ];
+};
+
+my $html_init =
+  include('/elements/init_overlib.html') .
+  include('/elements/input-fcc_options.html', js_only => 1) .
+  include('.style') .
+  include('.script');
+
+my $clink = sub {
+  my $cust_pkg = shift;
+  $cust_pkg->cust_main_custnum
+    ? [ "${p}view/cust_main.cgi?", 'custnum' ] 
+    : '';
+};
+
+my $html_form = qq!
+  <FORM ACTION="${p}edit/process/bulk-477_cust_pkg.html" METHOD="POST" NAME="477_cust_pkg">
+  <INPUT NAME="redirect" TYPE="hidden" VALUE="$session">
+!;
+
+my $pkg_edit_location_link = sub {
+  my $cust_pkg = shift;
+  my $locationnum = $cust_pkg->locationnum;
+  include('/elements/popup_link_onclick.html',
+    'action'      => $p. "edit/cust_location-censustract.html?locationnum=$locationnum",
+    'actionlabel' => emt('Edit census tract'),
+    'width'       => 700,
+    'height'      => 355,
+  );
+};
+
+sub time_or_blank {
+   my $column = shift;
+   return sub {
+     my $record = shift;
+     my $value = $record->get($column); #mmm closures
+     $value ? time2str('%b %d %Y', $value ) : '';
+   };
+}
+
+</%init>
+<%def .style>
+<style>
+  button.edit_fcc_button { float: right; }
+</style>
+</%def>
+<%def .script>
+<script type="text/javascript">
+  function finish_edit_fcc(id) {
+    cClick();
+    document.forms['477_cust_pkg'].submit(); //immediately save/refresh
+  }
+</script>
+</%def>