show last Calling-Station-Id on RADIUS summary, RT#29154

3 files changed, 113 insertions, 21 deletions

diff --git a/FS/FS/cust_svc.pm b/FS/FS/cust_svc.pm
index df179f5..f499bef 100644
--- a/FS/FS/cust_svc.pm
+++ b/FS/FS/cust_svc.pm
@@ -7,7 +7,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::part_pkg;
 use FS::part_svc;
 use FS::pkg_svc;
@@ -648,6 +648,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);
 
@@ -667,9 +668,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)
@@ -680,14 +681,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,
@@ -703,14 +704,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,
@@ -731,8 +732,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       )"
@@ -793,6 +794,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);
 
@@ -810,8 +812,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)
@@ -829,6 +831,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<FS::svc_acct/get_session_history>.  Equivalent to
diff --git a/FS/FS/svc_Radius_Mixin.pm b/FS/FS/svc_Radius_Mixin.pm
index 8e53a15..6674162 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<FS::svc_acct>)
-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<perlfunc/"time">.  Also see L<Time::Local> and L<Date::Parse> 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 1d58ef6..08d6d53 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<BR>
@@ -57,6 +62,9 @@
 % if ( $svc->table eq 'svc_acct' ) {
     Last Login: <B><% $svc->last_login_text %></B><BR>
 % }
+% if ( length($last_mac) ) {
+    Last Source or MAC: <B><% $last_mac %></B><BR>
+% }
 % my $href = qq!<A HREF="${p}search/sqlradius.cgi?svcnum=!. $svc->svcnum; 
 
   View session detail: