From 5f3a89734813a7a2da10e517de92e9c92a22830c Mon Sep 17 00:00:00 2001 From: ivan Date: Wed, 22 Oct 2008 04:39:54 +0000 Subject: [PATCH] fix error on open-ended RADIUS search with Pg, add options for open session search and search on start time, RT#4051 --- FS/FS/part_export/sqlradius.pm | 88 +++++++++++++++++++++++++++++++++------- httemplate/search/sqlradius.cgi | 39 ++++++++++++++---- httemplate/search/sqlradius.html | 66 +++++++++++++++++++++++++++++- 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. Also see L and L 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 @@ % 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 @@ % } + + Show: + + Completed sessions
+ Open sessions + + + + + Session start + + + <% include( '/elements/tr-input-beginning_ending.html', + 'prefix' => 'starttime', + 'input_time' => 1, + ) + %> + + + + + Session end + -<% include( '/elements/tr-input-beginning_ending.html', 'input_time'=>1 ) %> + <% include( '/elements/tr-input-beginning_ending.html', + 'prefix' => 'stoptime', + 'input_time' => 1, + ) + %>
-- 2.11.0