last login reporting (#2952)

diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm
index 3319768..5001c97 100644 (file)
--- a/FS/FS/Schema.pm
+++ b/FS/FS/Schema.pm
@@ -1057,6 +1057,8 @@ sub tables_hashref {
         'totalbytes','bigint', 'NULL',   '', '', '',
         'totalbytes_threshold',   'bigint', 'NULL',   '', '', '',
         'domsvc',    'int', '',   '', '', '', 
+        'last_login',  @date_type, '', '', 
+        'last_logout', @date_type, '', '', 
       ],
       'primary_key' => 'svcnum',
       #'unique' => [ [ 'username', 'domsvc' ] ],

diff --git a/FS/FS/part_export/sqlradius.pm b/FS/FS/part_export/sqlradius.pm
index 4190b03..5e63e10 100644 (file)
--- a/FS/FS/part_export/sqlradius.pm
+++ b/FS/FS/part_export/sqlradius.pm
@@ -619,9 +619,11 @@ sub update_svc_acct {
 
   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 = ();
@@ -629,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
@@ -637,8 +640,8 @@ 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;
@@ -653,6 +656,9 @@ sub update_svc_acct {
                           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, } )
@@ -672,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; 
@@ -690,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;
+
   }
 
 }
@@ -707,7 +715,7 @@ sub _try_decrement {
   } else {
     warn "  no existing $column value for svc_acct - skipping\n" if $DEBUG;
   }
-  return '';
+  return 'skipped';
 }
 
 1;

diff --git a/FS/FS/svc_acct.pm b/FS/FS/svc_acct.pm
index 259d093..f26b210 100644 (file)
--- a/FS/FS/svc_acct.pm
+++ b/FS/FS/svc_acct.pm
@@ -204,7 +204,7 @@ sub table_info {
   {
     'name'   => 'Account',
     'longname_plural' => 'Access accounts and mailboxes',
-    'sorts' => [ 'username', 'uid', 'seconds' ],
+    'sorts' => [ 'username', 'uid', 'seconds', 'last_login' ],
     'display_weight' => 10,
     'cancel_weight'  => 50, 
     'fields' => {
@@ -322,6 +322,14 @@ sub table_info {
                                    'format' => \&FS::UI::bytecount::display_bytecount,
                                    'parse' => \&FS::UI::bytecount::parse_bytecount,
                                  },
+        'last_login'=>           {
+                                   label     => 'Last login',
+                                   type      => 'disabled',
+                                 },
+        'last_logout'=>          {
+                                   label     => 'Last logout',
+                                   type      => 'disabled',
+                                 },
     },
   };
 }
@@ -344,6 +352,54 @@ sub _fieldhandlers {
   };
 }
 
+sub last_login {
+  shift->_lastlog('in', @_);
+}
+
+sub last_logout {
+  shift->_lastlog('out', @_);
+}
+
+sub _lastlog {
+  my( $self, $op, $time ) = @_;
+
+  if ( defined($time) ) {
+    warn "$me last_log$op called on svcnum ". $self->svcnum.
+         ' ('. $self->email. "): $time\n"
+      if $DEBUG;
+
+    local $SIG{HUP} = 'IGNORE';
+    local $SIG{INT} = 'IGNORE';
+    local $SIG{QUIT} = 'IGNORE';
+    local $SIG{TERM} = 'IGNORE';
+    local $SIG{TSTP} = 'IGNORE';
+    local $SIG{PIPE} = 'IGNORE';
+
+    my $oldAutoCommit = $FS::UID::AutoCommit;
+    local $FS::UID::AutoCommit = 0;
+    my $dbh = dbh;
+
+    my $sql = "UPDATE svc_acct SET last_log$op = ? WHERE svcnum = ?";
+    warn "$me $sql\n"
+      if $DEBUG;
+
+    my $sth = $dbh->prepare( $sql )
+      or die "Error preparing $sql: ". $dbh->errstr;
+    my $rv = $sth->execute($time, $self->svcnum);
+    die "Error executing $sql: ". $sth->errstr
+      unless defined($rv);
+    die "Can't update last_log$op for svcnum". $self->svcnum
+      if $rv == 0;
+
+    warn "$me update successful; committing\n"
+      if $DEBUG;
+    $dbh->commit or die $dbh->errstr if $oldAutoCommit;
+    $self->{'Hash'}->{"last_log$op"} = $time;
+  }else{
+    $self->getfield("last_log$op");
+  }
+}
+
 =item search_sql STRING
 
 Class method which returns an SQL fragment to search for the given string.
@@ -1918,6 +1974,17 @@ sub get_session_history {
   $self->cust_svc->get_session_history(@_);
 }
 
+=item last_login_text 
+
+Returns text describing the time of last login.
+
+=cut
+
+sub last_login_text {
+  my $self = shift;
+  $self->last_login ? ctime($self->last_login) : 'unknown';
+}
+
 =item get_cdrs TIMESTAMP_START TIMESTAMP_END [ 'OPTION' => 'VALUE ... ]
 
 =cut

diff --git a/httemplate/search/svc_acct.cgi b/httemplate/search/svc_acct.cgi
index 2c42ef0..d2389ba 100755 (executable)
--- a/httemplate/search/svc_acct.cgi
+++ b/httemplate/search/svc_acct.cgi
@@ -77,12 +77,12 @@ my $link_cust = sub {
 
 my @extra_sql = ();
 
-my @header = ( '#', 'Service', 'Account', 'UID' );
-my @fields = ( 'svcnum', 'svc', 'email', 'uid' );
-my @links = ( $link, $link, $link, $link );
-my $align = 'rlll';
-my @color = ( '', '', '', '' );
-my @style = ( '', '', '', '' );
+my @header = ( '#', 'Service', 'Account', 'UID', 'Last Login' );
+my @fields = ( 'svcnum', 'svc', 'email', 'uid', 'last_login_text' );
+my @links = ( $link, $link, $link, $link, $link );
+my $align = 'rlllr';
+my @color = ( '', '', '', '', '' );
+my @style = ( '', '', '', '', '' );
 
 if ( $cgi->param('domain') ) { 
   my $svc_domain =
@@ -110,7 +110,7 @@ if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) {
     $sortby = "LOWER($sortby)"
       if $sortby eq 'username';
     push @extra_sql, "$sortby IS NOT NULL"
-      if $sortby eq 'uid' || $sortby eq 'seconds';
+      if $sortby eq 'uid' || $sortby eq 'seconds' || $sortby eq 'last_login';
     $orderby = "ORDER BY $sortby";
   }
 

diff --git a/httemplate/view/svc_acct.cgi b/httemplate/view/svc_acct.cgi
index 8326818..d764afe 100755 (executable)
--- a/httemplate/view/svc_acct.cgi
+++ b/httemplate/view/svc_acct.cgi
@@ -79,6 +79,7 @@
 
   Upload: <B><% sprintf("%.3f", $input) %></B> megabytes<BR>
   Download: <B><% sprintf("%.3f", $output) %></B> megabytes<BR>
+  Last Login: <B><% $svc_acct->last_login_text %></B><BR>
 % my $href = qq!<A HREF="${p}search/sqlradius.cgi?svcnum=$svcnum!; 
 
   View session detail: