From 4fd5994039666584fa14b7b2afbb4cd0d6481b21 Mon Sep 17 00:00:00 2001 From: jeff Date: Fri, 28 Dec 2007 01:41:23 +0000 Subject: [PATCH] last login reporting (#2952) --- FS/FS/Schema.pm | 2 ++ FS/FS/part_export/sqlradius.pm | 32 ++++++++++++-------- FS/FS/svc_acct.pm | 69 +++++++++++++++++++++++++++++++++++++++++- httemplate/search/svc_acct.cgi | 14 ++++----- httemplate/view/svc_acct.cgi | 1 + 5 files changed, 98 insertions(+), 20 deletions(-) diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm index 331976807..5001c97bf 100644 --- 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 4190b034d..5e63e1004 100644 --- 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 259d093c6..f26b2109f 100644 --- 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 2c42ef039..d2389baf0 100755 --- 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 8326818d6..d764afee6 100755 --- a/httemplate/view/svc_acct.cgi +++ b/httemplate/view/svc_acct.cgi @@ -79,6 +79,7 @@ Upload: <% sprintf("%.3f", $input) %> megabytes
Download: <% sprintf("%.3f", $output) %> megabytes
+ Last Login: <% $svc_acct->last_login_text %>
% my $href = qq!