From: Ivan Kohler Date: Sun, 17 Aug 2014 22:37:30 +0000 (-0700) Subject: show last Calling-Station-Id on RADIUS summary, RT#29154 X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=788275e860cfa8126fd6919c11fce8a46b1062a9 show last Calling-Station-Id on RADIUS summary, RT#29154 --- diff --git a/FS/FS/cust_svc.pm b/FS/FS/cust_svc.pm index 4c38aae24..cbd26a1df 100644 --- a/FS/FS/cust_svc.pm +++ b/FS/FS/cust_svc.pm @@ -6,7 +6,7 @@ use Carp; #use Scalar::Util qw( blessed ); use List::Util qw( max ); use FS::Conf; -use FS::Record qw( qsearch qsearchs dbh str2time_sql ); +use FS::Record qw( qsearch qsearchs dbh str2time_sql str2time_sql_closing ); use FS::cust_pkg; use FS::part_pkg; use FS::part_svc; @@ -656,6 +656,7 @@ sub seconds_since_sqlradacct { #select a unix time conversion function based on database type my $str2time = str2time_sql( $dbh->{Driver}->{Name} ); + my $closing = str2time_sql_closing( $dbh->{Driver}->{Name} ); my $username = $part_export->export_username($svc_x); @@ -675,9 +676,9 @@ sub seconds_since_sqlradacct { FROM radacct WHERE UserName = ? $realm - AND $str2time AcctStartTime) >= ? - AND $str2time AcctStopTime ) < ? - AND $str2time AcctStopTime ) > 0 + AND $str2time AcctStartTime $closing >= ? + AND $str2time AcctStopTime $closing < ? + AND $str2time AcctStopTime $closing > 0 AND AcctStopTime IS NOT NULL" ) or die $dbh->errstr; $sth->execute($username, ($realm ? $realmparam : ()), $start, $end) @@ -688,14 +689,14 @@ sub seconds_since_sqlradacct { if $DEBUG; # count session start->range end - $query = "SELECT SUM( ? - $str2time AcctStartTime ) ) + $query = "SELECT SUM( ? - $str2time AcctStartTime $closing ) FROM radacct WHERE UserName = ? $realm - AND $str2time AcctStartTime ) >= ? - AND $str2time AcctStartTime ) < ? - AND ( ? - $str2time AcctStartTime ) ) < 86400 - AND ( $str2time AcctStopTime ) = 0 + AND $str2time AcctStartTime $closing >= ? + AND $str2time AcctStartTime $closing < ? + AND ( ? - $str2time AcctStartTime $closing ) < 86400 + AND ( $str2time AcctStopTime $closing = 0 OR AcctStopTime IS NULL )"; $sth = $dbh->prepare($query) or die $dbh->errstr; $sth->execute( $end, @@ -711,14 +712,14 @@ sub seconds_since_sqlradacct { if $DEBUG; #count range start->session end - $sth = $dbh->prepare("SELECT SUM( $str2time AcctStopTime ) - ? ) + $sth = $dbh->prepare("SELECT SUM( $str2time AcctStopTime $closing - ? ) FROM radacct WHERE UserName = ? $realm - AND $str2time AcctStartTime ) < ? - AND $str2time AcctStopTime ) >= ? - AND $str2time AcctStopTime ) < ? - AND $str2time AcctStopTime ) > 0 + AND $str2time AcctStartTime $closing < ? + AND $str2time AcctStopTime $closing >= ? + AND $str2time AcctStopTime $closing < ? + AND $str2time AcctStopTime $closing > 0 AND AcctStopTime IS NOT NULL" ) or die $dbh->errstr; $sth->execute( $start, @@ -739,8 +740,8 @@ sub seconds_since_sqlradacct { FROM radacct WHERE UserName = ? $realm - AND $str2time AcctStartTime ) < ? - AND ( $str2time AcctStopTime ) >= ? + AND $str2time AcctStartTime $closing < ? + AND ( $str2time AcctStopTime $closing >= ? )" # OR AcctStopTime = 0 # OR AcctStopTime IS NULL )" @@ -801,6 +802,7 @@ sub attribute_since_sqlradacct { #select a unix time conversion function based on database type my $str2time = str2time_sql( $dbh->{Driver}->{Name} ); + my $closing = str2time_sql_closing( $dbh->{Driver}->{Name} ); my $username = $part_export->export_username($svc_x); @@ -818,8 +820,8 @@ sub attribute_since_sqlradacct { FROM radacct WHERE UserName = ? $realm - AND $str2time AcctStopTime ) >= ? - AND $str2time AcctStopTime ) < ? + AND $str2time AcctStopTime $closing >= ? + AND $str2time AcctStopTime $closing < ? AND AcctStopTime IS NOT NULL" ) or die $dbh->errstr; $sth->execute($username, ($realm ? $realmparam : ()), $start, $end) @@ -837,6 +839,78 @@ sub attribute_since_sqlradacct { } +#note: implementation here, POD in FS::svc_acct +# false laziness w/above +sub attribute_last_sqlradacct { + my($self, $attrib) = @_; + + my $mes = "$me attribute_last_sqlradacct:"; + + my $svc_x = $self->svc_x; + + my @part_export = $self->part_svc->part_export_usage; + die "no accounting-capable exports are enabled for ". $self->part_svc->svc. + " service definition" + unless @part_export; + #or return undef; + + my $value = ''; + my $AcctStartTime = 0; + + foreach my $part_export ( @part_export ) { + + next if $part_export->option('ignore_accounting'); + + warn "$mes connecting to sqlradius database\n" + if $DEBUG; + + my $dbh = DBI->connect( map { $part_export->option($_) } + qw(datasrc username password) ) + or die "can't connect to sqlradius database: ". $DBI::errstr; + + warn "$mes connected to sqlradius database\n" + if $DEBUG; + + #select a unix time conversion function based on database type + my $str2time = str2time_sql( $dbh->{Driver}->{Name} ); + my $closing = str2time_sql_closing( $dbh->{Driver}->{Name} ); + + my $username = $part_export->export_username($svc_x); + + warn "$mes finding most-recent $attrib\n" + if $DEBUG; + + my $realm = ''; + my $realmparam = ''; + if ($part_export->option('process_single_realm')) { + $realm = 'AND Realm = ?'; + $realmparam = $part_export->option('realm'); + } + + my $sth = $dbh->prepare("SELECT $attrib, $str2time AcctStartTime $closing + FROM radacct + WHERE UserName = ? + $realm + ORDER BY AcctStartTime DESC LIMIT 1 + ") or die $dbh->errstr; + $sth->execute($username, ($realm ? $realmparam : ()) ) + or die $sth->errstr; + + my $row = $sth->fetchrow_arrayref; + if ( defined($row->[0]) && $row->[1] > $AcctStartTime ) { + $value = $row->[0]; + $AcctStartTime = $row->[1]; + } + + warn "$mes done\n" + if $DEBUG; + + } + + $value; + +} + =item get_session_history TIMESTAMP_START TIMESTAMP_END See L. Equivalent to diff --git a/FS/FS/svc_Radius_Mixin.pm b/FS/FS/svc_Radius_Mixin.pm index 969f74952..ef11ac6c3 100644 --- a/FS/FS/svc_Radius_Mixin.pm +++ b/FS/FS/svc_Radius_Mixin.pm @@ -185,9 +185,8 @@ sub seconds_since_sqlradacct { =item attribute_since_sqlradacct TIMESTAMP_START TIMESTAMP_END ATTRIBUTE -Returns the sum of the given attribute for all accounts (see L) -in this package for sessions ending between TIMESTAMP_START (inclusive) and -TIMESTAMP_END (exclusive). +For this service, returns the sum of the given attribute for sessions ending +between TIMESTAMP_START (inclusive) and TIMESTAMP_END (exclusive). TIMESTAMP_START and TIMESTAMP_END are specified as UNIX timestamps; see L. Also see L and L for conversion @@ -201,6 +200,17 @@ sub attribute_since_sqlradacct { $self->cust_svc->attribute_since_sqlradacct(@_); } +=item attribute_last_sqlradacct ATTRIBUTE + +For this service, returns the most recent value of the given attribute. + +=cut + +#note: POD here, implementation in FS::cust_svc +sub attribute_last_sqlradacct { + my $self = shift; + $self->cust_svc->attribute_last_sqlradacct(@_); +} =item get_session_history TIMESTAMP_START TIMESTAMP_END Returns an array of hash references of this customers login history for the diff --git a/httemplate/view/elements/svc_radius_usage.html b/httemplate/view/elements/svc_radius_usage.html index 1d58ef660..08d6d53d3 100644 --- a/httemplate/view/elements/svc_radius_usage.html +++ b/httemplate/view/elements/svc_radius_usage.html @@ -25,6 +25,11 @@ % my $output = $svc->attribute_since_sqlradacct( % $last_bill, time, 'AcctOutputOctets' % ) / 1048576; +% +% my $last_mac = $svc->attribute_last_sqlradacct( 'CallingStationId' ); +% if ( $last_mac =~ /^\s*(([\dA-F]{2}[\-:]){5}[\dA-F]{2})/i ) { +% $last_mac .= ' ('. (Net::MAC::Vendor::lookup($1))->[0]. ')'; +% } RADIUS session information
@@ -57,6 +62,9 @@ % if ( $svc->table eq 'svc_acct' ) { Last Login: <% $svc->last_login_text %>
% } +% if ( length($last_mac) ) { + Last Source or MAC: <% $last_mac %>
+% } % my $href = qq!