per-customer RADIUS data usage report, #42310

4 files changed, 147 insertions, 9 deletions

diff --git a/FS/FS/svc_Common.pm b/FS/FS/svc_Common.pm
index 1dd9ffb63..f2456a56f 100644
--- a/FS/FS/svc_Common.pm
+++ b/FS/FS/svc_Common.pm
@@ -1481,8 +1481,12 @@ sub search {
   }
 
   #svcnum
-  if ( $params->{'svcnum'} =~ /^(\d+)$/ ) {
-    push @where, "svcnum = $1";
+  if ( $params->{'svcnum'} ) {
+    my @svcnum = ref( $params->{'svcnum'} )
+                 ? @{ $params->{'svcnum'} }
+                 : $params->{'svcnum'};
+    @svcnum = grep /^\d+$/, @svcnum;
+    push @where, 'svcnum IN ('. join(',', @svcnum) . ')' if @svcnum;
   }
 
   # svcpart
diff --git a/httemplate/search/report_sqlradius_usage-custnum.html b/httemplate/search/report_sqlradius_usage-custnum.html
new file mode 100644
index 000000000..a71012dd4
--- /dev/null
+++ b/httemplate/search/report_sqlradius_usage-custnum.html
@@ -0,0 +1,71 @@
+<& /elements/header-popup.html, mt($title) &>
+
+<FORM ACTION="sqlradius_usage.html" METHOD="GET" TARGET="_top">
+
+<& /elements/hidden.html,
+  'field' => 'custnum',
+  'value' => $custnum,
+&>
+<TABLE BGCOLOR="#cccccc" CELLSPACING=0>
+
+% if ( scalar(@exports) == 1 ) {
+<tr><td>
+<& /elements/hidden.html,
+  'field'         => 'exportnum',
+  'value'         => $exports[0]->exportnum,
+&>
+</td></tr>
+% } else {
+<& /elements/tr-select-table.html,
+  'label'         => 'Export', # kind of non-indicative...
+  'table'         => 'part_export',
+  'name_col'      => 'label',
+  'value_col'     => 'exportnum',
+  'records'       => \@exports,
+  'disable_empty' => 1,
+&>
+% }
+<& /elements/tr-input-beginning_ending.html &>
+
+</TABLE>
+
+<BR>
+<INPUT TYPE="submit" VALUE="<% mt('Get Report') |h %>">
+
+</FORM>
+
+<& /elements/footer.html &>
+<%init>
+
+my $curuser = $FS::CurrentUser::CurrentUser;
+die "access denied"
+  unless $curuser->access_right('Usage: RADIUS sessions');
+  # yes?
+
+my $title = 'Data Usage Report';
+my $custnum;
+if ($cgi->keywords) {
+  ($custnum) = $cgi->keywords;
+} else {
+  $custnum = $cgi->param('custnum');
+}
+$custnum =~ /^(\d+)$/
+  or die "illegal custnum $custnum";
+my $cust_main = qsearchs( {
+  'table'     => 'cust_main',
+  'hashref'   => { 'custnum' => $custnum },
+  'extra_sql' => ' AND '. $curuser->agentnums_sql,
+});
+# get all exports that apply to this customer's services--should be fast, as
+# everything here is indexed
+my @exports = qsearch({
+  'table'     => 'part_export',
+  'select'    => 'DISTINCT part_export.*',
+  'addl_from' => ' JOIN export_svc USING (exportnum)
+                   JOIN cust_svc USING (svcpart)
+                   JOIN cust_pkg USING (pkgnum) ',
+  'extra_sql' => ' WHERE cust_pkg.custnum = '.$custnum,
+});
+@exports = grep { $_->can('usage_sessions') } @exports;
+
+</%init>
diff --git a/httemplate/search/report_sqlradius_usage.html b/httemplate/search/report_sqlradius_usage.html
index e818fb57d..89b60847b 100644
--- a/httemplate/search/report_sqlradius_usage.html
+++ b/httemplate/search/report_sqlradius_usage.html
@@ -1,3 +1,4 @@
+%# some overlap with report_sqlradius_usage_custnum.html
 <& /elements/header.html, mt($title) &>
 
 <FORM ACTION="sqlradius_usage.html" METHOD="GET">
diff --git a/httemplate/search/sqlradius_usage.html b/httemplate/search/sqlradius_usage.html
index 29ef4c0e8..b5551ade6 100644
--- 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>