[vhffs-dev] [973] add a date field to know the last time the user logged in to the panel, use it to know if a user should be removed or not, user cleanup is now set for users after 30 days instead of 15 days of inactivity

[subversion@xxxxxxxxxxxxx]

Revision: 973
Author:   gradator
Date:     2007-10-09 21:09:00 +0000 (Tue, 09 Oct 2007)

Log Message:
-----------
add a date field to know the last time the user logged in to the panel, use it to know if a user should be removed or not, user cleanup is now set for users after 30 days instead of 15 days of inactivity

Modified Paths:
--------------
    trunk/vhffs-api/src/Vhffs/User.pm
    trunk/vhffs-backend/src/pgsql/initdb.sql.in
    trunk/vhffs-compat/4.0.sql.in
    trunk/vhffs-panel/auth.pl
    trunk/vhffs-robots/src/user_cleanup.pl


Modified: trunk/vhffs-api/src/Vhffs/User.pm
===================================================================
--- trunk/vhffs-api/src/Vhffs/User.pm	2007-10-08 21:48:27 UTC (rev 972)
+++ trunk/vhffs-api/src/Vhffs/User.pm	2007-10-09 21:09:00 UTC (rev 973)
@@ -88,7 +88,7 @@
 
 
 sub _new {
-    my ($class, $main, $uid, $gid, $oid, $username, $passwd, $homedir, $shell, $admin, $firstname, $lastname, $address, $zipcode, $city, $country, $mail, $gpg_key, $note, $language, $theme, $date_creation, $description, $state) = @_;
+    my ($class, $main, $uid, $gid, $oid, $username, $passwd, $homedir, $shell, $admin, $firstname, $lastname, $address, $zipcode, $city, $country, $mail, $gpg_key, $note, $language, $theme, $lastloginpanel, $date_creation, $description, $state) = @_;
     my $self = $class->SUPER::_new($main, $oid, $uid, $gid, $date_creation, $description, '', $state, Vhffs::Constants::TYPE_USER);
     return undef unless(defined $self);
     $self->{uid} = $uid;
@@ -109,6 +109,7 @@
     $self->{note} = $note;
     $self->{language} = $language;
     $self->{theme} = $theme;
+    $self->{lastloginpanel} = $lastloginpanel;
     return $self;
 }
 
@@ -214,7 +215,7 @@
         $admin = 0 unless (defined $admin);
         my $homedir = Vhffs::Functions::hash_homename( $username  , $main );
 
-        my $sth = $dbh->prepare('INSERT INTO vhffs_users (uid, gid, username, shell, passwd, homedir, admin, firstname, lastname, address, zipcode, city, country, mail, gpg_key, note, language, theme, object_id) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, ?, NULL, ?)');
+        my $sth = $dbh->prepare('INSERT INTO vhffs_users (uid, gid, username, shell, passwd, homedir, admin, firstname, lastname, address, zipcode, city, country, mail, gpg_key, note, language, theme, lastloginpanel, object_id) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, ?, NULL, NULL, ?)');
         $sth->execute($uid, $gid, $username, $userconf->{'default_shell'}, Vhffs::Functions::password_encrypt($password), $homedir, $admin, $firstname, $lastname, $address, $zipcode, $city, $country, $mail, $gpg_key, Vhffs::Constants::DEFAULT_LANG, $parent->get_oid);
         
         my $group = Vhffs::Group::create($main, $username, undef, $uid, $gid);
@@ -282,11 +283,11 @@
     
     return -1 if( $self->SUPER::commit < 0 );
     
-    my $sql = 'UPDATE vhffs_users SET shell = ?, passwd = ?, admin = ?, firstname = ?, lastname = ?, address = ?, zipcode = ?, country = ?, mail = ?, city = ?, gpg_key = ? , note = ? WHERE uid = ?';
+    my $sql = 'UPDATE vhffs_users SET shell = ?, passwd = ?, admin = ?, firstname = ?, lastname = ?, address = ?, zipcode = ?, country = ?, mail = ?, city = ?, gpg_key = ? , note = ? , lastloginpanel = ? WHERE uid = ?';
     my $sth = $self->{db}->prepare($sql) or return -1;
     $sth->execute($self->{'shell'}, $self->{'passwd'}, $self->{'admin'}, 
         $self->{'firstname'}, $self->{'lastname'}, $self->{'address'}, $self->{'zipcode'},
-        $self->{'country'}, $self->{'mail'}, $self->{'city'}, $self->{'gpg_key'}, $self->{'note'}, $self->{'uid'}) or return -3;
+        $self->{'country'}, $self->{'mail'}, $self->{'city'}, $self->{'gpg_key'}, $self->{'note'}, $self->{'lastloginpanel'}, $self->{'uid'}) or return -3;
 
     return 1;
 }
@@ -543,6 +544,12 @@
     return( $self->{'note'} );
 }
 
+sub get_lastloginpanel
+{
+    my $self = shift;
+    return( $self->{'lastloginpanel'} );
+}
+
 sub set_shell
 {
     my $self = shift;
@@ -632,6 +639,12 @@
     $self->{'passwd'} = Vhffs::Functions::password_encrypt( $value );
 }
 
+sub update_lastloginpanel
+{
+	my $self = shift;
+	$self->{'lastloginpanel'} = time();
+}
+
 sub set_admin
 {
     my ( $self , $value ) = @_;
@@ -737,10 +750,12 @@
 	my @users;
 	return unless defined $vhffs;
 
-	my $query = 'SELECT u.uid FROM vhffs_users u INNER JOIN vhffs_object o ON o.object_id=u.object_id WHERE u.admin=? AND o.state=? AND o.date_creation<? AND u.uid NOT IN (SELECT u.uid FROM vhffs_users u INNER JOIN vhffs_user_group ug ON u.uid=ug.uid)';
+	my $ts = time() - $age;
 
+	my $query = 'SELECT u.uid FROM vhffs_users u INNER JOIN vhffs_object o ON o.object_id=u.object_id WHERE u.admin=? AND o.state=? AND o.date_creation<? AND ( u.lastloginpanel IS NULL OR u.lastloginpanel<? ) AND u.uid NOT IN (SELECT u.uid FROM vhffs_users u INNER JOIN vhffs_user_group ug ON u.uid=ug.uid)';
+
 	my $request = $vhffs->{'db'}->prepare( $query );
-	$request->execute( Vhffs::Constants::USER_NORMAL , Vhffs::Constants::ACTIVATED , time() - $age );
+	$request->execute( Vhffs::Constants::USER_NORMAL , Vhffs::Constants::ACTIVATED , $ts , $ts );
 	while( my $uid = $request->fetchrow_array )
 	{   
 		my $user = Vhffs::User::get_by_uid( $vhffs , $uid );
@@ -851,7 +866,7 @@
 
 sub get_by_uid {
     my ($main, $uid) = @_;
-    my $query = 'SELECT u.uid, u.gid, u.object_id, u.username, u.passwd, u.homedir, u.shell, u.admin, u.firstname, u.lastname, u.address, u.zipcode, u.city, u.country, u.mail, u.gpg_key, u.note, u.language, u.theme, o.date_creation, o.description, o.state FROM vhffs_users u INNER JOIN vhffs_object o ON o.object_id = u.object_id WHERE u.uid = ?';
+    my $query = 'SELECT u.uid, u.gid, u.object_id, u.username, u.passwd, u.homedir, u.shell, u.admin, u.firstname, u.lastname, u.address, u.zipcode, u.city, u.country, u.mail, u.gpg_key, u.note, u.language, u.theme, u.lastloginpanel, o.date_creation, o.description, o.state FROM vhffs_users u INNER JOIN vhffs_object o ON o.object_id = u.object_id WHERE u.uid = ?';
     my $dbh = $main->get_db_object;
     my @params = $dbh->selectrow_array($query, undef, $uid);
     return undef unless(@params);
@@ -873,7 +888,7 @@
 
 sub get_by_username {
     my ($main, $username) = @_;
-    my $query = 'SELECT u.uid, u.gid, u.object_id, u.username, u.passwd, u.homedir, u.shell, u.admin, u.firstname, u.lastname, u.address, u.zipcode, u.city, u.country, u.mail, u.gpg_key, u.note, u.language, u.theme, o.date_creation, o.description, o.state FROM vhffs_users u INNER JOIN vhffs_object o ON o.object_id = u.object_id WHERE u.username = ?';
+    my $query = 'SELECT u.uid, u.gid, u.object_id, u.username, u.passwd, u.homedir, u.shell, u.admin, u.firstname, u.lastname, u.address, u.zipcode, u.city, u.country, u.mail, u.gpg_key, u.note, u.language, u.theme, u.lastloginpanel, o.date_creation, o.description, o.state FROM vhffs_users u INNER JOIN vhffs_object o ON o.object_id = u.object_id WHERE u.username = ?';
     my $dbh = $main->get_db_object;
     my @params = $dbh->selectrow_array($query, undef, $username);
     return undef unless(@params);

Modified: trunk/vhffs-backend/src/pgsql/initdb.sql.in
===================================================================
--- trunk/vhffs-backend/src/pgsql/initdb.sql.in	2007-10-08 21:48:27 UTC (rev 972)
+++ trunk/vhffs-backend/src/pgsql/initdb.sql.in	2007-10-09 21:09:00 UTC (rev 973)
@@ -87,6 +87,7 @@
 	note int4 DEFAULT 0,
 	language varchar(16),
 	theme varchar(250),
+	lastloginpanel int8,
 	CONSTRAINT vhffs_users_pkey PRIMARY KEY (uid)
 ) WITH OIDS;
 

Modified: trunk/vhffs-compat/4.0.sql.in
===================================================================
--- trunk/vhffs-compat/4.0.sql.in	2007-10-08 21:48:27 UTC (rev 972)
+++ trunk/vhffs-compat/4.0.sql.in	2007-10-09 21:09:00 UTC (rev 973)
@@ -358,3 +358,6 @@
 -- add realname field to vhffs_groups
 ALTER TABLE vhffs_groups ADD COLUMN realname varchar(100);
 UPDATE vhffs_groups SET realname=groupname;
+
+-- add lastloginpanel to vhffs_users
+ALTER TABLE vhffs_users ADD COLUMN lastloginpanel int8;

Modified: trunk/vhffs-panel/auth.pl
===================================================================
--- trunk/vhffs-panel/auth.pl	2007-10-08 21:48:27 UTC (rev 972)
+++ trunk/vhffs-panel/auth.pl	2007-10-09 21:09:00 UTC (rev 973)
@@ -85,6 +85,10 @@
         my $themecookie = new CGI::Cookie( -name=> 'theme', -value=>$user->get_theme, -expires=>'+10y' );
         my $langcookie = new CGI::Cookie( -name=>'language', -value=>$user->get_lang, -expires=>'+10y' );
 
+	# Set last login panel to current time
+	$user->update_lastloginpanel;
+	$user->commit;
+
         $panel->redirect('/panel.pl', [$sessioncookie, $themecookie, $langcookie]);
     }
 }

Modified: trunk/vhffs-robots/src/user_cleanup.pl
===================================================================
--- trunk/vhffs-robots/src/user_cleanup.pl	2007-10-08 21:48:27 UTC (rev 972)
+++ trunk/vhffs-robots/src/user_cleanup.pl	2007-10-09 21:09:00 UTC (rev 973)
@@ -41,7 +41,7 @@
 
 Vhffs::Robots::lock( $vhffs , 'user' );
 
-my $users = Vhffs::User::get_unused_accounts( $vhffs , 1296000 );   # 15 days
+my $users = Vhffs::User::get_unused_accounts( $vhffs , 2592000 );   # 30 days
 foreach my $user ( @{$users} )
 {
 	Vhffs::Robots::vhffs_log( sprintf( 'deleted user %s because it was unused' , $user->get_username ) , $vhffs );