last login reporting (#2952)

[freeside.git] / FS / FS / part_export / sqlradius.pm

diff --git a/FS/FS/part_export/sqlradius.pm b/FS/FS/part_export/sqlradius.pm
index 139f400..5e63e10 100644 (file)
--- a/FS/FS/part_export/sqlradius.pm
+++ b/FS/FS/part_export/sqlradius.pm
@@ -2,10 +2,11 @@ package FS::part_export::sqlradius;
 
 use vars qw(@ISA $DEBUG %info %options $notes1 $notes2);
 use Tie::IxHash;
-use FS::Record qw( dbh qsearch qsearchs );
+use FS::Record qw( dbh qsearch qsearchs str2time_sql );
 use FS::part_export;
 use FS::svc_acct;
 use FS::export_svc;
+use Carp qw( cluck );
 
 @ISA = qw(FS::part_export);
 
@@ -89,7 +90,7 @@ sub rebless { shift; }
 
 sub export_username {
   my($self, $svc_acct) = (shift, shift);
-  warn "export_username called on $self with arg $svc_acct" if $DEBUG;
+  warn "export_username called on $self with arg $svc_acct" if $DEBUG > 1;
   $svc_acct->username;
 }
 
@@ -106,6 +107,9 @@ sub _export_insert {
   }
   my @groups = $svc_acct->radius_groups;
   if ( @groups ) {
+    cluck localtime(). ": queuing usergroup_insert for ". $svc_acct->svcnum.
+          " (". $self->export_username($svc_acct). " with ". join(", ", @groups)
+      if $DEBUG;
     my $err_or_queue = $self->sqlradius_queue(
       $svc_acct->svcnum, 'usergroup_insert',
       $self->export_username($svc_acct), @groups );
@@ -374,10 +378,22 @@ sub sqlradius_usergroup_insert { #subroutine, not method
   my $dbh = sqlradius_connect(shift, shift, shift);
   my( $username, @groups ) = @_;
 
+  my $s_sth = $dbh->prepare(
+    "SELECT COUNT(*) FROM usergroup WHERE UserName = ? AND GroupName = ?"
+  ) or die $dbh->errstr;
+
   my $sth = $dbh->prepare( 
     "INSERT INTO usergroup ( UserName, GroupName ) VALUES ( ?, ? )"
   ) or die $dbh->errstr;
+
   foreach my $group ( @groups ) {
+    $s_sth->execute( $username, $group ) or die $s_sth->errstr;
+    if ($s_sth->fetchrow_arrayref->[0]) {
+      warn localtime() . ": sqlradius_usergroup_insert attempted to reinsert " .
+           "$group for $username\n"
+        if $DEBUG;
+      next;
+    }
     $sth->execute( $username, $group )
       or die "can't insert into groupname table: ". $sth->errstr;
   }
@@ -469,6 +485,9 @@ sub sqlreplace_usergroups {
   }
 
   if ( @newgroups ) {
+    cluck localtime(). ": queuing usergroup_insert for $svcnum ($username) ".
+          "with ".  join(", ", @newgroups)
+      if $DEBUG;
     my $err_or_queue = $self->sqlradius_queue( $svcnum, 'usergroup_insert',
       $username, @newgroups );
     return $err_or_queue
@@ -541,16 +560,7 @@ sub usage_sessions {
                                    qw( datasrc username password ) );
 
   #select a unix time conversion function based on database type
-  my $str2time;
-  if ( $dbh->{Driver}->{Name} =~ /^mysql(PP)?$/ ) {
-    $str2time = 'UNIX_TIMESTAMP(';
-  } elsif ( $dbh->{Driver}->{Name} eq 'Pg' ) {
-    $str2time = 'EXTRACT( EPOCH FROM ';
-  } else {
-    warn "warning: unknown database type ". $dbh->{Driver}->{Name}.
-         "; guessing how to convert to UNIX timestamps";
-    $str2time = 'extract(epoch from ';
-  }
+  my $str2time = str2time_sql( $dbh->{Driver}->{Name} );
 
   my @fields = (
                  qw( username realm framedipaddress
@@ -607,9 +617,13 @@ sub usage_sessions {
 sub update_svc_acct {
   my $self = shift;
 
+  my $conf = new FS::Conf;
+
+  my $fdbh = dbh;
   my $dbh = sqlradius_connect( map $self->option($_),
                                    qw( datasrc username password ) );
 
+  my $str2time = str2time_sql( $dbh->{Driver}->{Name} );
   my @fields = qw( radacctid username realm acctsessiontime );
 
   my @param = ();
@@ -617,6 +631,7 @@ sub update_svc_acct {
 
   my $sth = $dbh->prepare("
     SELECT RadAcctId, UserName, Realm, AcctSessionTime,
+           $str2time AcctStartTime),  $str2time AcctStopTime), 
            AcctInputOctets, AcctOutputOctets
       FROM radacct
       WHERE FreesideStatus IS NULL
@@ -625,19 +640,25 @@ sub update_svc_acct {
   $sth->execute() or die $sth->errstr;
 
   while ( my $row = $sth->fetchrow_arrayref ) {
-    my($RadAcctId, $UserName, $Realm, $AcctSessionTime,
-       $AcctInputOctets, $AcctOutputOctets) = @$row;
+    my($RadAcctId, $UserName, $Realm, $AcctSessionTime, $AcctStartTime,
+       $AcctStopTime, $AcctInputOctets, $AcctOutputOctets) = @$row;
     warn "processing record: ".
          "$RadAcctId ($UserName\@$Realm for ${AcctSessionTime}s"
       if $DEBUG;
 
+    $UserName = lc($UserName) unless $conf->exists('username-uppercase');
+
     my %search = ( 'username' => $UserName );
+
     my $extra_sql = '';
     if ( ref($self) =~ /withdomain/ ) { #well...
       $extra_sql = " AND '$Realm' = ( SELECT domain FROM svc_domain
                           WHERE svc_domain.svcnum = svc_acct.domsvc ) ";
     }
 
+    my $oldAutoCommit = $FS::UID::AutoCommit; # can't undo side effects, but at
+    local $FS::UID::AutoCommit = 0;           # least we can avoid over counting
+
     my @svc_acct =
       grep { qsearch( 'export_svc', { 'exportnum' => $self->exportnum,
                                       'svcpart'   => $_->cust_svc->svcpart, } )
@@ -657,15 +678,15 @@ sub update_svc_acct {
       warn "WARNING: multiple svc_acct records found $errinfo - skipping\n";
     } else {
       warn "found svc_acct ". $svc_acct[0]->svcnum. " $errinfo\n" if $DEBUG;
-      _try_decrement($svc_acct[0], 'seconds', $AcctSessionTime) 
-        and $status='done';
-      _try_decrement($svc_acct[0], 'upbytes', $AcctInputOctets)
-        and $status='done';
-      _try_decrement($svc_acct[0], 'downbytes', $AcctOutputOctets)
-        and $status='done';
-      _try_decrement($svc_acct[0], 'totalbytes', $AcctInputOctets + 
-                     $AcctOutputOctets)
-        and $status='done';
+      $svc_acct[0]->last_login($AcctStartTime);
+      $svc_acct[0]->last_logout($AcctStopTime);
+      my @stati;
+      push @stati, _try_decrement($svc_acct[0], 'seconds', $AcctSessionTime);
+      push @stati, _try_decrement($svc_acct[0], 'upbytes', $AcctInputOctets);
+      push @stati, _try_decrement($svc_acct[0], 'downbytes', $AcctOutputOctets);
+      push @stati, _try_decrement($svc_acct[0], 'totalbytes', $AcctInputOctets + 
+                     $AcctOutputOctets);
+      $status=join(' ', @stati);
     }
 
     warn "setting FreesideStatus to $status $errinfo\n" if $DEBUG; 
@@ -675,6 +696,8 @@ sub update_svc_acct {
     ) or die $dbh->errstr;
     $psth->execute($status, $RadAcctId) or die $psth->errstr;
 
+    $fdbh->commit or die $fdbh->errstr if $oldAutoCommit;
+
   }
 
 }
@@ -692,7 +715,7 @@ sub _try_decrement {
   } else {
     warn "  no existing $column value for svc_acct - skipping\n" if $DEBUG;
   }
-  return '';
+  return 'skipped';
 }
 
 1;