add summarized RADIUS report feature, RT11776

3 files changed, 61 insertions, 5 deletions

diff --git a/FS/FS/part_export/sqlradius.pm b/FS/FS/part_export/sqlradius.pm
index 15aa98620..d1663dcf5 100644
--- a/FS/FS/part_export/sqlradius.pm
+++ b/FS/FS/part_export/sqlradius.pm
@@ -610,6 +610,7 @@ sub usage_sessions {
 
   my $opt = {};
   my($start, $end, $svc_acct, $ip, $prefix) = ( '', '', '', '', '');
+  my $summarize = 0;
   if ( ref($_[0]) ) {
     $opt = shift;
     $start    = $opt->{stoptime_start};
@@ -617,6 +618,7 @@ sub usage_sessions {
     $svc_acct = $opt->{svc_acct};
     $ip       = $opt->{ip};
     $prefix   = $opt->{prefix};
+    $summarize   = $opt->{summarize};
   } else {
     ( $start, $end ) = splice(@_, 0, 2);
     $svc_acct = @_ ? shift : '';
@@ -644,6 +646,10 @@ sub usage_sessions {
                  "$str2time acctstoptime ) as acctstoptime",
                );
 
+  @fields = ( 'username', 'sum(acctsessiontime) as acctsessiontime', 'sum(acctinputoctets) as acctinputoctets',
+              'sum(acctoutputoctets) as acctoutputoctets',
+            ) if $summarize;
+
   my @param = ();
   my @where = ();
 
@@ -696,11 +702,15 @@ sub usage_sessions {
   my $where = join(' AND ', @where);
   $where = "WHERE $where" if $where;
 
+  my $groupby = '';
+  $groupby = 'GROUP BY username' if $summarize;
+
+  my $orderby = 'ORDER BY AcctStartTime DESC';
+  $orderby = '' if $summarize;
+
   my $sth = $dbh->prepare('SELECT '. join(', ', @fields).
-                          "  FROM radacct
-                             $where
-                             ORDER BY AcctStartTime DESC
-  ") or die $dbh->errstr;                                 
+                          "  FROM radacct $where $groupby $orderby
+                        ") or die $dbh->errstr;                                 
   $sth->execute(@param) or die $sth->errstr;
 
   [ map { { %$_ } } @{ $sth->fetchall_arrayref({}) } ];
diff --git a/httemplate/search/sqlradius.cgi b/httemplate/search/sqlradius.cgi
index cca121179..5363944e4 100644
--- a/httemplate/search/sqlradius.cgi
+++ b/httemplate/search/sqlradius.cgi
@@ -57,6 +57,7 @@
 %            'svc_acct'        => $cgi_svc_acct,
 %            'ip'              => $ip,
 %            'prefix'          => $prefix, 
+%            'summarize'       => $summarize,
 %          } )
 %       }
 %   ) {
@@ -96,6 +97,9 @@ die "access denied"
 # parse cgi params
 ###
 
+my $summarize = 0;
+$summarize = 1 if $cgi->param('summarize') eq 'Y';
+
 #sort of false laziness w/cust_pay.cgi
 my( $beginning, $ending ) = ( '', '' );
 if ( $cgi->param('stoptime_beginning')
@@ -265,7 +269,42 @@ my $octets_format = sub {
 # the fields
 ###
 
-tie my %fields, 'Tie::IxHash', 
+my %fields;
+if ( $summarize ) {
+tie %fields, 'Tie::IxHash', 
+  'username'          => {
+                           name    => 'User',
+                           attrib  => 'UserName',
+                           fmt     => $user_format,
+                           align   => 'left',
+                         },
+  'dummy'             => {
+                           name    => 'Customer',
+                           attrib  => '',
+                           fmt     => $customer_format,
+                           align   => 'left',
+                         },
+  'acctsessiontime'   => {
+                           name    => 'Duration',
+                           attrib  => 'Acct-Session-Time',
+                           fmt     => $duration_format,
+                           align   => 'right',
+                         },
+  'acctinputoctets'   => {
+                           name    => 'Upload', # (from user)',
+                           attrib  => 'Acct-Input-Octets',
+                           fmt     => $octets_format,
+                           align   => 'right',
+                         },
+  'acctoutputoctets'  => {
+                           name    => 'Download', # (to user)',
+                           attrib  => 'Acct-Output-Octets',
+                           fmt     => $octets_format,
+                           align   => 'right',
+                         },
+;
+} else {
+tie %fields, 'Tie::IxHash',
   'username'          => {
                            name    => 'User',
                            attrib  => 'UserName',
@@ -322,6 +361,7 @@ tie my %fields, 'Tie::IxHash',
                            align   => 'right',
                          },
 ;
+}
 $fields{$_}->{fmt} ||= sub { length($_[0]) ? shift : '&nbsp'; }
   foreach keys %fields;
 
diff --git a/httemplate/search/sqlradius.html b/httemplate/search/sqlradius.html
index 8c405982f..7b9fce310 100644
--- a/httemplate/search/sqlradius.html
+++ b/httemplate/search/sqlradius.html
@@ -8,6 +8,12 @@
   <TD ALIGN="right">Username: </TD>
   <TD><INPUT TYPE="text" NAME="username"></TD>
 </TR>
+<% include( '/elements/tr-checkbox.html',
+            'field' => 'summarize',
+            'label' => 'Summarize',
+            'value' => 'Y',
+          )
+%>
 <TR>
   <TD></TD>
   <TD><FONT SIZE="-1"><I>(leave blank to show all users)</I></FONT></TD>