detail links on new 477 report, #30360

diff --git a/FS/FS/Report/FCC_477.pm b/FS/FS/Report/FCC_477.pm
index 599b9e0..d541551 100644 (file)
--- a/FS/FS/Report/FCC_477.pm
+++ b/FS/FS/Report/FCC_477.pm
@@ -284,6 +284,9 @@ name strings) as an arrayref of arrayrefs.  OPTIONS may contain "date"
 (a timestamp value to run the report as of this date) and "agentnum"
 (to limit to a single agent).
 
 (a timestamp value to run the report as of this date) and "agentnum"
 (to limit to a single agent).
 

+OPTIONS may also contain "detail", a flag that tells the report to return
+a comma-separated list of the detail records included in each row count.
+

 =cut
 
 sub report {
 =cut
 
 sub report {


@@ -319,8 +322,9 @@ sub fbd_sql {
     'cir_speed_down',
     'cir_speed_up',
   );
     'cir_speed_down',
     'cir_speed_up',
   );

-  my $from =
-    'deploy_zone_block
+  push @select, 'blocknum' if $opt{detail};
+
+  my $from = 'deploy_zone_block

     JOIN deploy_zone USING (zonenum)
     JOIN agent USING (agentnum)';
   my @where = (
     JOIN deploy_zone USING (zonenum)
     JOIN agent USING (agentnum)';
   my @where = (


@@ -353,6 +357,8 @@ sub fbs_sql {
     'COUNT(*)',
     'COUNT(is_consumer)',
   );
     'COUNT(*)',
     'COUNT(is_consumer)',
   );

+  push @select, "array_to_string(array_agg(pkgnum), ',')" if $opt{detail};
+

   my $from =
     'cust_pkg
       JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
   my $from =
     'cust_pkg
       JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)


@@ -395,8 +401,9 @@ sub fvs_sql {
     # number of lines/subscriptions
     'SUM(CASE WHEN is_voip = 1 THEN 1 ELSE phone_lines END)',
     # consumer grade lines/subscriptions
     # number of lines/subscriptions
     'SUM(CASE WHEN is_voip = 1 THEN 1 ELSE phone_lines END)',
     # consumer grade lines/subscriptions

-    'SUM(CASE WHEN is_consumer = 1 THEN ( CASE WHEN is_voip = 1 THEN voip_sessions ELSE phone_lines END) ELSE 0 END)'
+    'SUM(CASE WHEN is_consumer = 1 THEN ( CASE WHEN is_voip = 1 THEN voip_sessions ELSE phone_lines END) ELSE 0 END)',

   );
   );

+  push @select, "array_to_string(array_agg(pkgnum), ',')" if $opt{detail};

 
   my $from = 'cust_pkg
     JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
 
   my $from = 'cust_pkg
     JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)


@@ -447,6 +454,8 @@ sub lts_sql {
     "SUM(CASE WHEN media = 'Cable Modem' THEN phone_lines ELSE 0 END)",
     "SUM(CASE WHEN media = 'Fixed Wireless' THEN phone_lines ELSE 0 END)",
   );
     "SUM(CASE WHEN media = 'Cable Modem' THEN phone_lines ELSE 0 END)",
     "SUM(CASE WHEN media = 'Fixed Wireless' THEN phone_lines ELSE 0 END)",
   );

+  push @select, "array_to_string(array_agg(pkgnum),',')" if $opt{detail};
+

   my $from =
     'cust_pkg
       JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
   my $from =
     'cust_pkg
       JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)


@@ -497,6 +506,7 @@ sub voip_sql {
     "SUM(CASE WHEN (voip_lastmile = 1 AND media = 'Fixed Wireless') THEN 1 ELSE 0 END)",
     "SUM(CASE WHEN (voip_lastmile = 1 AND media NOT IN('Copper', 'Fiber', 'Cable Modem', 'Fixed Wireless') ) THEN 1 ELSE 0 END)",
   );
     "SUM(CASE WHEN (voip_lastmile = 1 AND media = 'Fixed Wireless') THEN 1 ELSE 0 END)",
     "SUM(CASE WHEN (voip_lastmile = 1 AND media NOT IN('Copper', 'Fiber', 'Cable Modem', 'Fixed Wireless') ) THEN 1 ELSE 0 END)",
   );

+  push @select, "array_to_string(array_agg(pkgnum),',')" if $opt{detail};

 
   my $from =
     'cust_pkg
 
   my $from =
     'cust_pkg


@@ -538,6 +548,8 @@ sub mbs_sql {
     'COUNT(*)',
     'COUNT(is_consumer)',
   );
     'COUNT(*)',
     'COUNT(is_consumer)',
   );

+  push @select, "array_to_string(array_agg(pkgnum),',')" if $opt{detail};
+

   my $from =
     'cust_pkg
       JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
   my $from =
     'cust_pkg
       JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)


@@ -577,6 +589,8 @@ sub mvs_sql {
     'COUNT(*)',
     'COUNT(mobile_direct)',
   );
     'COUNT(*)',
     'COUNT(mobile_direct)',
   );

+  push @select, "array_to_string(array_agg(pkgnum),',')" if $opt{detail};
+

   my $from =
     'cust_pkg
       JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
   my $from =
     'cust_pkg
       JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)


@@ -625,4 +639,22 @@ sub parts {
   Storable::dclone(\%parts);
 }
 
   Storable::dclone(\%parts);
 }
 

+=item part_table SECTION
+
+Returns the name of the primary table that's aggregated in the report section 
+SECTION. The last column of the report returned by the L</report> method is 
+a comma-separated list of record numbers, in this table, that are included in
+the report line item.
+
+=cut
+
+sub part_table {
+  my ($class, $part) = @_;
+  if ($part eq 'fbd') {
+    return 'deploy_zone_block';
+  } else {
+    return 'cust_pkg';
+  } # add other cases as we add more of the deployment/availability reports
+}
+

 1;
 1;

diff --git a/FS/FS/cust_pkg.pm b/FS/FS/cust_pkg.pm
index ad530f7..9a953ff 100644 (file)
--- a/FS/FS/cust_pkg.pm
+++ b/FS/FS/cust_pkg.pm
@@ -4479,6 +4479,15 @@ Limit to packages associated with a svc_broadband, associated with a sector,
 associated with this towernum (or any of these, if it's an arrayref) (or NO
 towernum, if it's zero). This is an extreme niche case.
 
 associated with this towernum (or any of these, if it's an arrayref) (or NO
 towernum, if it's zero). This is an extreme niche case.
 

+=item 477part, 477rownum, date
+
+Limit to packages included in a specific row of one of the FCC 477 reports.
+'477part' is the section name (see L<FS::Report::FCC_477> methods), 'date'
+is the report as-of date (completely unrelated to the package setup/bill/
+other date fields), and '477rownum' is the row number of the report starting
+with zero. Row numbers have no inherent meaning, so this is useful only 
+for explaining a 477 report you've already run.
+

 =back
 
 =cut
 =back
 
 =cut


@@ -4901,6 +4910,40 @@ sub search {
   }
 
   ##
   }
 
   ##

+  # parse the 477 report drill-down options
+  ##
+
+  if ($params->{'477part'} =~ /^([a-z]+)$/) {
+    my $section = $1;
+    my ($date, $rownum, $agentnum);
+    if ($params->{'date'} =~ /^(\d+)$/) {
+      $date = $1;
+    }
+    if ($params->{'477rownum'} =~ /^(\d+)$/) {
+      $rownum = $1;
+    }
+    if ($params->{'agentnum'} =~ /^(\d+)$/) {
+      $agentnum = $1;
+    }
+    if ($date and defined($rownum)) {
+      my $report = FS::Report::FCC_477->report($section,
+        'date'      => $date,
+        'agentnum'  => $agentnum,
+        'detail'    => 1
+      );
+      my $row = $report->[$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
+
+      # and this overrides everything
+      @where = ( "cust_pkg.pkgnum IN($pkgnums)" );
+    } # else we're missing some params, ignore the whole business
+  }
+
+  ##

   # setup queries, links, subs, etc. for the search
   ##
 
   # setup queries, links, subs, etc. for the search
   ##
 

diff --git a/httemplate/search/477.html b/httemplate/search/477.html
index fb85f1e..cc865e0 100644 (file)
--- a/httemplate/search/477.html
+++ b/httemplate/search/477.html
@@ -49,13 +49,23 @@ a.download {
   <thead>
     <& $header &>
   </thead>
   <thead>
     <& $header &>
   </thead>

+%   my $rownum = 0;

 %   foreach my $row (@$data) {
   <tr>
 %   foreach my $row (@$data) {
   <tr>

+%     my $first = 1;

 %     foreach my $item (@$row) {
 %     foreach my $item (@$row) {

-    <td><% $item %></td>
+    <td>
+%     if ($first and $part_link{$partname}) {
+      <a href="<% $part_link{$partname} . "477rownum=$rownum" %>"><% $item || '(empty)' %></a>
+%       $first = 0;
+%     } else {
+      <% $item %>

 %     }
 %     }

+    </td>
+%   } #foreach $item

   </tr>
   </tr>

-%   }
+%   $rownum++;
+%   } #foreach $row

 </table>
 % } # foreach $partname
 <& /elements/footer.html &>
 </table>
 % } # foreach $partname
 <& /elements/footer.html &>


@@ -64,6 +74,7 @@ die "access denied"
   unless $FS::CurrentUser::CurrentUser->access_right('List packages');
 
 my %parts;
   unless $FS::CurrentUser::CurrentUser->access_right('List packages');
 
 my %parts;

+my %part_link;

 # load from cache if possible
 my $session;
 if ( $cgi->param('session') =~ /^(\d+)$/ ) {
 # load from cache if possible
 my $session;
 if ( $cgi->param('session') =~ /^(\d+)$/ ) {


@@ -83,8 +94,16 @@ my @partnames = grep /^\w+$/, $cgi->param('parts');
 foreach my $partname (@partnames) {
   $parts{$partname} ||= FS::Report::FCC_477->report( $partname,
     date      => $date,
 foreach my $partname (@partnames) {
   $parts{$partname} ||= FS::Report::FCC_477->report( $partname,
     date      => $date,

-    agentnum  => $agentnum
+    agentnum  => $agentnum,

   );
   );

+  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;";
+    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');
 
 }
 $m->cache->set($session, \%parts, '1h');
 

diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi
index c388487..c88b3a1 100755 (executable)
--- a/httemplate/search/cust_pkg.cgi
+++ b/httemplate/search/cust_pkg.cgi
@@ -158,6 +158,7 @@ $search_hash{'query'} = $cgi->keywords;
 #scalars
 for (qw( agentnum cust_status cust_main_salesnum salesnum custnum magic status
          custom cust_fields pkgbatch zip
 #scalars
 for (qw( agentnum cust_status cust_main_salesnum salesnum custnum magic status
          custom cust_fields pkgbatch zip

+         477part 477rownum date 

     )) 
 {
   $search_hash{$_} = $cgi->param($_) if length($cgi->param($_));
     )) 
 {
   $search_hash{$_} = $cgi->param($_) if length($cgi->param($_));