sqlradius data volume report improvements, #18823

3 files changed, 38 insertions, 17 deletions

diff --git a/FS/FS/svc_acct.pm b/FS/FS/svc_acct.pm
index e67db43c6..7ce79ae01 100644
--- a/FS/FS/svc_acct.pm
+++ b/FS/FS/svc_acct.pm
@@ -2808,6 +2808,13 @@ Arrayref of additional WHERE clauses, will be ANDed together.
 sub search {
   my ($class, $params) = @_;
 
+  my @from = (
+    ' LEFT JOIN cust_svc  USING ( svcnum  ) ',
+    ' LEFT JOIN part_svc  USING ( svcpart ) ',
+    ' LEFT JOIN cust_pkg  USING ( pkgnum  ) ',
+    ' LEFT JOIN cust_main USING ( custnum ) ',
+  );
+
   my @where = ();
 
   # domain
@@ -2852,9 +2859,17 @@ sub search {
     push @where, "svcpart = $1";
   }
 
+  if ( $params->{'exportnum'} =~ /^(\d+)$/ ) {
+    push @from, ' LEFT JOIN export_svc USING ( svcpart )';
+    push @where, "exportnum = $1";
+  }
+
   # sector and tower
   my @where_sector = $class->tower_sector_sql($params);
-  push @where, @where_sector if @where_sector;
+  if ( @where_sector ) {
+    push @where, @where_sector;
+    push @from, ' LEFT JOIN tower_sector USING ( sectornum )';
+  }
 
   # here is the agent virtualization
   #if ($params->{CurrentUser}) {
@@ -2875,16 +2890,9 @@ sub search {
 
   push @where, @{ $params->{'where'} } if $params->{'where'};
 
+  my $addl_from = join(' ', @from);
   my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : '';
 
-  my $addl_from = ' LEFT JOIN cust_svc  USING ( svcnum  ) '.
-                  ' LEFT JOIN part_svc  USING ( svcpart ) '.
-                  ' LEFT JOIN cust_pkg  USING ( pkgnum  ) '.
-                  ' LEFT JOIN cust_main USING ( custnum ) ';
-
-  $addl_from .= ' LEFT JOIN tower_sector USING ( sectornum )'
-    if @where_sector;
-
   my $count_query = "SELECT COUNT(*) FROM svc_acct $addl_from $extra_sql";
   #if ( keys %svc_acct ) {
   #  $count_query .= ' WHERE '.
diff --git a/FS/FS/svc_broadband.pm b/FS/FS/svc_broadband.pm
index cc960dd99..c791dc4a4 100755
--- a/FS/FS/svc_broadband.pm
+++ b/FS/FS/svc_broadband.pm
@@ -246,6 +246,12 @@ sub search {
     push @where, "svcpart = $1";
   }
 
+  #exportnum
+  if ( $params->{'exportnum'} =~ /^(\d+)$/ ) {
+    push @from, 'LEFT JOIN export_svc USING ( svcpart )';
+    push @where, "exportnum = $1";
+  }
+
   #ip_addr
   if ( $params->{'ip_addr'} =~ /^(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})$/ ) {
     push @where, "ip_addr = '$1'";
diff --git a/httemplate/search/sqlradius_usage.html b/httemplate/search/sqlradius_usage.html
index dd06c10fa..29ef4c0e8 100755
--- a/httemplate/search/sqlradius_usage.html
+++ b/httemplate/search/sqlradius_usage.html
@@ -27,9 +27,9 @@
                      'Customer',
                      'Package',
                      @svc_header,
-                     'Upload',
-                     'Download',
-                     'Total',
+                     'Upload (GB)',
+                     'Download (GB)',
+                     'Total (GB)',
                    ],
   'footer'      => \@footer,
   'fields'      => [ #\&FS::UI::Web::cust_fields,
@@ -139,7 +139,8 @@ my @svc_usage = map {
     return '' if !exists($usage_by_username{$username});
     my $value = $usage_by_username{ $username }->[$i];
     $total_usage[$i] += $value;
-    FS::UI::bytecount::display_bytecount($value);
+    # for now, always show in GB, rounded to 3 digits
+    bytes_to_gb($value);
   }
 } (0,1,2);
 
@@ -167,7 +168,8 @@ my @svc_header = @{ $svc_header{$svcdb} };
 my @svc_fields = @{ $svc_fields{$svcdb} };
 
 # svc_x search params
-my %search_hash = ( 'agentnum' => $agentnum );
+my %search_hash = ( 'agentnum' => $agentnum,
+                    'exportnum' => $export->exportnum );
 
 my $sql_query = $class->search(\%search_hash);
 $sql_query->{'select'}    .= ', part_pkg.pkg';
@@ -183,12 +185,17 @@ my $skip_cols = 1 + scalar(@svc_header);
 my @footer = (
   '',
   FS::Record->scalar_sql($sql_query->{count_query}) . ' services',
-  (' ') x $skip_cols,
-  map { 
+  ('') x $skip_cols,
+  map {
+    my $i = $_;
     sub { # defer this until the rows have been processed
-        FS::UI::bytecount::display_bytecount($total_usage[$_])
+      bytes_to_gb($total_usage[$i])
     }
   } (0,1,2)
 );
 
+sub bytes_to_gb {
+  $_[0] ?  sprintf('%.3f', $_[0] / (1024*1024*1024.0)) : '';
+}
+
 </%init>