per-customer RADIUS data usage report, #42310

[freeside.git] / httemplate / search / sqlradius_usage.html

diff --git a/httemplate/search/sqlradius_usage.html b/httemplate/search/sqlradius_usage.html
index 29ef4c0..b5551ad 100644 (file)
--- a/httemplate/search/sqlradius_usage.html
+++ b/httemplate/search/sqlradius_usage.html
@@ -59,8 +59,8 @@
 
 my %opt = @_;
 
-die "access denied" unless
-  $FS::CurrentUser::CurrentUser->access_right('List services');
+my $curuser = $FS::CurrentUser::CurrentUser;
+die "access denied" unless $curuser->access_right('List services');
 
 my $title = 'Data Usage Report - '; 
 my $agentnum;
@@ -92,6 +92,40 @@ if ( $ending == 4294967295 ) {
   $title .= time2str('%h %o %Y', $ending);
 }
 
+# can also show a specific customer / service. the main query will handle
+# agent restrictions, but we need a list of the services to ask the export
+# for usage data.
+my ($cust_main, @svc_x);
+if ( $cgi->param('custnum') =~ /^(\d+)$/ ) {
+  $cust_main = qsearchs( {
+    'table'     => 'cust_main',
+    'hashref'   => { 'custnum' => $1 },
+    'extra_sql' => ' AND '. $curuser->agentnums_sql,
+  });
+  die "Customer not found!" unless $cust_main;
+  # then only report on this agent
+  $agentnum = $cust_main->agentnum;
+  @include_agents = ();
+  # and announce that we're doing it
+  $title .= ' - ' . $cust_main->name_short;
+
+  # yes, we'll query the database once for each service the customer has,
+  # even non-radacct'd services. probably less bad than a single query that
+  # pulls records for every service for every customer.
+  foreach my $cust_pkg ($cust_main->all_pkgs) {
+    foreach my $cust_svc ($cust_pkg->cust_svc) {
+      push @svc_x, $cust_svc->svc_x;
+    }
+  }
+}
+foreach ($cgi->param('svcnum')) {
+  if (/^(\d+)$/) {
+    my $cust_svc = FS::cust_svc->by_key($1)
+      or die "service #$1 not found."; # or continue?
+    push @svc_x, $cust_svc->svc_x;
+  }
+}
+
 my $export;
 my %usage_by_username;
 if ( exists($opt{usage_by_username}) ) {
@@ -109,16 +143,28 @@ if ( exists($opt{usage_by_username}) ) {
     or die "exportnum ".$export->exportnum." is type ".$export->exporttype.
            ", not sqlradius";
 
-  my $usage = $export->usage_sessions( {
+  my %usage_param = (
       stoptime_start  => $beginning,
       stoptime_end    => $ending,
       summarize       => 1
-  } );
-  # arrayref of hashrefs of
+  );
+  # usage_sessions() returns an arrayref of hashrefs of
   # (username, acctsessiontime, acctinputoctets, acctoutputoctets)
   # (XXX needs to include 'realm' for sqlradius_withdomain)
-  # rearrange to be indexed by username.
+  my $usage;
+  if ( @svc_x ) {
+    # then query once per service
+    $usage = [];
+    foreach my $svc ( @svc_x ) {
+      $usage_param{'svc'} = $svc;
+      push @$usage, @{ $export->usage_sessions(\%usage_param) };
+    }
+  } else {
+    # one query, get everyone's data
+    my $usage = $export->usage_sessions(\%usage_param);
+  }
 
+  # rearrange to be indexed by username.
   foreach (@$usage) {
     my $username = $_->{'username'};
     my @row = (
@@ -171,10 +217,22 @@ my @svc_fields = @{ $svc_fields{$svcdb} };
 my %search_hash = ( 'agentnum' => $agentnum,
                     'exportnum' => $export->exportnum );
 
+if ($cust_main) {
+  $search_hash{'custnum'} = $cust_main->custnum;
+}
+if (@svc_x) {
+  $search_hash{'svcnum'} = [ map { $_->get('svcnum') } @svc_x ];
+}
+
 my $sql_query = $class->search(\%search_hash);
 $sql_query->{'select'}    .= ', part_pkg.pkg';
 $sql_query->{'addl_from'} .= ' LEFT JOIN part_pkg USING (pkgpart)';
 
+if ( @svc_x ) {
+  my $svcnums = join(',', map { $_->get('svcnum') } @svc_x);
+  $sql_query->{'extra_sql'} .= ' AND svcnum IN('.$svcnums.')';
+}
+
 my $link_svc = [ $p.'view/cust_svc.cgi?', 'svcnum' ];
 
 my $link_cust = [ $p.'view/cust_main.cgi?', 'custnum' ];
@@ -182,9 +240,10 @@ my $link_cust = [ $p.'view/cust_main.cgi?', 'custnum' ];
 # columns between the customer name and the usage fields
 my $skip_cols = 1 + scalar(@svc_header);
 
+my $num_rows = FS::Record->scalar_sql($sql_query->{count_query});
 my @footer = (
   '',
-  FS::Record->scalar_sql($sql_query->{count_query}) . ' services',
+  emt('[quant,_1,service]', $num_rows), 
   ('') x $skip_cols,
   map {
     my $i = $_;
@@ -198,4 +257,7 @@ sub bytes_to_gb {
   $_[0] ?  sprintf('%.3f', $_[0] / (1024*1024*1024.0)) : '';
 }
 
+warn Dumper \%usage_by_username;
+
+
 </%init>