fix error on open-ended RADIUS search with Pg, add options for open session search and search on start time, RT#4051

3 files changed, 169 insertions, 24 deletions

diff --git a/FS/FS/part_export/sqlradius.pm b/FS/FS/part_export/sqlradius.pm
index 6b068a93d..a89d4d764 100644
--- a/FS/FS/part_export/sqlradius.pm
+++ b/FS/FS/part_export/sqlradius.pm
@@ -503,8 +503,34 @@ sub sqlreplace_usergroups {
 
 #--
 
+=item usage_sessions HASHREF
+
 =item usage_sessions TIMESTAMP_START TIMESTAMP_END [ SVC_ACCT [ IP [ PREFIX [ SQL_SELECT ] ] ] ]
 
+New-style: pass a hashref with the following keys:
+
+=over 4
+
+=item stoptime_start - Lower bound for AcctStopTime, as a UNIX timestamp
+
+=item stoptime_end - Upper bound for AcctStopTime, as a UNIX timestamp
+
+=item open_sessions - Only show records with no AcctStopTime (typically used without stoptime_* options and with starttime_* options instead)
+
+=item starttime_start - Lower bound for AcctStartTime, as a UNIX timestamp
+
+=item starttime_end - Upper bound for AcctStartTime, as a UNIX timestamp
+
+=item svc_acct
+
+=item ip
+
+=item prefix
+
+=back
+
+Old-style: 
+
 TIMESTAMP_START and TIMESTAMP_END are specified as UNIX timestamps; see
 L<perlfunc/"time">.  Also see L<Time::Local> and L<Date::Parse> for conversion
 functions.
@@ -546,11 +572,24 @@ Returns an arrayref of hashrefs with the following fields:
 #some false laziness w/cust_svc::seconds_since_sqlradacct
 
 sub usage_sessions {
-  my( $self, $start, $end ) = splice(@_, 0, 3);
-  my $svc_acct = @_ ? shift : '';
-  my $ip = @_ ? shift : '';
-  my $prefix = @_ ? shift : '';
-  #my $select = @_ ? shift : '*';
+  my( $self ) = shift;
+
+  my $opt = {};
+  my($start, $end, $svc_acct, $ip, $prefix) = ( '', '', '', '', '');
+  if ( ref($_[0]) ) {
+    my $opt = shift;
+    $start    = $opt->{stoptime_start};
+    $end      = $opt->{stoptime_end};
+    $svc_acct = $opt->{svc_acct};
+    $ip       = $opt->{ip};
+    $prefix   = $opt->{prefix};
+  } else {
+    ( $start, $end ) = splice(@_, 0, 2);
+    $svc_acct = @_ ? shift : '';
+    $ip = @_ ? shift : '';
+    $prefix = @_ ? shift : '';
+    #my $select = @_ ? shift : '*';
+  }
 
   $end ||= 2147483647;
 
@@ -572,37 +611,56 @@ sub usage_sessions {
                );
 
   my @param = ();
-  my $where = '';
+  my @where = '';
 
   if ( $svc_acct ) {
     my $username = $self->export_username($svc_acct);
     if ( $svc_acct =~ /^([^@]+)\@([^@]+)$/ ) {
-      $where = '( UserName = ? OR ( UserName = ? AND Realm = ? ) ) AND';
+      push @where, '( UserName = ? OR ( UserName = ? AND Realm = ? ) )';
       push @param, $username, $1, $2;
     } else {
-      $where = 'UserName = ? AND';
+      push @where, 'UserName = ?';
       push @param, $username;
     }
   }
 
   if ( length($ip) ) {
-    $where .= ' FramedIPAddress = ? AND';
+    push @where, ' FramedIPAddress = ?';
     push @param, $ip;
   }
 
   if ( length($prefix) ) {
     #assume sip: for now, else things get ugly trying to match /^\w+:$prefix/
-    $where .= " CalledStationID LIKE 'sip:$prefix\%' AND";
+    push @where, " CalledStationID LIKE 'sip:$prefix\%'";
+  }
+
+  if ( $start ) {
+    push @where, "$str2time AcctStopTime ) >= ?";
+    push @param, $start;
+  }
+  if ( $end ) {
+    push @where, "$str2time AcctStopTime ) <= ?";
+    push @param, $end;
+  }
+  if ( $opt->{open_sessions} ) {
+    push @where, 'AcctStopTime IS NULL';
+  }
+  if ( $opt->{starttime_start} ) {
+    push @where, "$str2time AcctStartTime ) >= ?";
+    push @param, $opt->{starttime_start};
+  }
+  if ( $opt->{starttime_end} ) {
+    push @where, "$str2time AcctStartTime ) <= ?";
+    push @param, $opt->{starttime_end};
   }
 
-  push @param, $start, $end;
+  my $where = join(' AND ', @where);
+  $where = "WHERE $where" if $where;
 
   my $sth = $dbh->prepare('SELECT '. join(', ', @fields).
                           "  FROM radacct
-                             WHERE $where
-                                   $str2time AcctStopTime ) >= ?
-                               AND $str2time AcctStopTime ) <=  ?
-                               ORDER BY AcctStartTime DESC
+                             $where
+                             ORDER BY AcctStartTime DESC
   ") or die $dbh->errstr;                                 
   $sth->execute(@param) or die $sth->errstr;
 
diff --git a/httemplate/search/sqlradius.cgi b/httemplate/search/sqlradius.cgi
index 24b17d3b3..29f360298 100644
--- a/httemplate/search/sqlradius.cgi
+++ b/httemplate/search/sqlradius.cgi
@@ -48,8 +48,17 @@
   </TR>
 
 %   foreach my $session (
-%       @{ $part_export->usage_sessions(
-%            $beginning, $ending, $cgi_svc_acct, $ip, $prefix, ) }
+%       @{ $part_export->usage_sessions( {
+%            'stoptime_start'  => $beginning,
+%            'stoptime_end'    => $ending,
+%            'open_sessions'   => $open_sessions,
+%            'starttime_start' => $starttime_beginning,
+%            'starttime_end'   => $starttime_ending,
+%            'svc_acct'        => $cgi_svc_acct,
+%            'ip'              => $ip,
+%            'prefix'          => $prefix, 
+%          } )
+%       }
 %   ) {
 %     if ( $bgcolor eq $bgcolor1 ) {
 %       $bgcolor = $bgcolor2;
@@ -88,14 +97,13 @@ die "access denied"
 ###
 
 #sort of false laziness w/cust_pay.cgi
-my $beginning = '';
-my $ending = '';
-if ( $cgi->param('beginning')
-     && $cgi->param('beginning') =~ /^([ 0-9\-\/\:\w]{0,54})$/ ) {
+my( $beginning, $ending ) = ( '', '' );
+if ( $cgi->param('stoptime_beginning')
+     && $cgi->param('stoptime_beginning') =~ /^([ 0-9\-\/\:\w]{0,54})$/ ) {
   $beginning = str2time($1);
 }
-if ( $cgi->param('ending')
-     && $cgi->param('ending') =~ /^([ 0-9\-\/\:\w]{0,54})$/ ) {
+if ( $cgi->param('stoptime_ending')
+     && $cgi->param('stoptime_ending') =~ /^([ 0-9\-\/\:\w]{0,54})$/ ) {
   $ending = str2time($1); # + 86399;
 }
 if ( $cgi->param('begin') && $cgi->param('begin') =~ /^(\d+)$/ ) {
@@ -105,6 +113,21 @@ if ( $cgi->param('end') && $cgi->param('end') =~ /^(\d+)$/ ) {
   $ending = $1;
 }
 
+my $open_sessions = '';
+if ( $cgi->param('open_sessions') =~ /^(\d*)$/ ) {
+  $open_sessions = $1;
+}
+
+my( $starttime_beginning, $starttime_ending ) = ( '', '' );
+if ( $cgi->param('starttime_beginning')
+     && $cgi->param('starttime_beginning') =~ /^([ 0-9\-\/\:\w]{0,54})$/ ) {
+  $starttime_beginning = str2time($1);
+}
+if ( $cgi->param('starttime_ending')
+     && $cgi->param('starttime_ending') =~ /^([ 0-9\-\/\:\w]{0,54})$/ ) {
+  $starttime_ending = str2time($1); # + 86399;
+}
+
 my $cgi_svc_acct = '';
 if ( $cgi->param('svcnum') =~ /^(\d+)$/ ) {
   $cgi_svc_acct = qsearchs( 'svc_acct', { 'svcnum' => $1 } );
diff --git a/httemplate/search/sqlradius.html b/httemplate/search/sqlradius.html
index 660a54f3c..8c405982f 100644
--- a/httemplate/search/sqlradius.html
+++ b/httemplate/search/sqlradius.html
@@ -43,8 +43,72 @@
   </TR>
 % } 
 
+  <TR>
+    <TD>Show:</TD>
+    <TD>
+      <INPUT TYPE="radio" NAME="open_sessions" VALUE="0" onClick="open_changed(this);" CHECKED>Completed sessions<BR>
+      <INPUT TYPE="radio" NAME="open_sessions" VALUE="1" onClick="open_changed(this);">Open sessions
+    </TD>
+  </TR>
+
+  <TR>
+    <TH COLSPAN=2>Session start</TD>
+  </TR>
+
+  <% include( '/elements/tr-input-beginning_ending.html',
+                'prefix'     => 'starttime',
+                'input_time' => 1,
+            )
+  %>
+
+  <SCRIPT TYPE="text/javascript">
+
+    function open_changed(what) {
+
+      var value=get_open_value(what); 
+      if ( value == '1' ) {
+        what.form.stoptime_beginning_text.disabled = true;
+        what.form.stoptime_ending_text.disabled = true;
+        what.form.stoptime_beginning_text.style.backgroundColor = '#dddddd';
+        what.form.stoptime_ending_text.style.backgroundColor = '#dddddd';
+        what.form.stoptime_beginning_button.style.display = 'none';
+        what.form.stoptime_ending_button.style.display = 'none';
+        what.form.stoptime_beginning_disabled.style.display = '';
+        what.form.stoptime_ending_disabled.style.display = '';
+      } else if ( value == '0' ) {
+        what.form.stoptime_beginning_text.disabled = false;
+        what.form.stoptime_ending_text.disabled = false;
+        what.form.stoptime_beginning_text.style.backgroundColor = '#ffffff';
+        what.form.stoptime_ending_text.style.backgroundColor = '#ffffff';
+        what.form.stoptime_beginning_button.style.display = '';
+        what.form.stoptime_ending_button.style.display = '';
+        what.form.stoptime_beginning_disabled.style.display = 'none';
+        what.form.stoptime_ending_disabled.style.display = 'none';
+      }
+
+    }
+
+    function get_open_value(what) {
+      var rad_val = '';
+      for (var i=0; i < what.form.open_sessions.length; i++) {
+        if (what.form.open_sessions[i].checked) {
+          var rad_val = what.form.open_sessions[i].value;
+        }
+     }
+     return rad_val;
+   }
+
+  </SCRIPT>
+
+  <TR>
+    <TH COLSPAN=2>Session end</TD>
+  </TR>
 
-<% include( '/elements/tr-input-beginning_ending.html', 'input_time'=>1 ) %>
+  <% include( '/elements/tr-input-beginning_ending.html',
+                'prefix'     => 'stoptime',
+                'input_time' => 1,
+            )
+  %>
 
 </TABLE>
 <BR><INPUT TYPE="submit" VALUE="View sessions">