/*
 * $Id: VKCUserSerializer.java,v 1.82 2008/04/17 10:01:00 marbobo Exp $
 *
 * Copyright (c) Siemens AG 2008 
 *           (c) Universität Paderborn 2008 
 * Licensed under EUPL 1.1. 
 *
 * $Log: VKCUserSerializer.java,v $
 * Revision 1.82  2008/04/17 10:01:00  marbobo
 * + Velocity-Templates angepasst
 *
 * Revision 1.81  2008/02/13 07:45:00  marbobo
 * + Bugfix
 *
 * Revision 1.80  2008/02/12 09:39:49  korowa
 * + Eine Möglichkeit für den Benutzer hinzugefügt die Emailsart auszuwählen (HTML oder Plaintext)
 * + Betreff für den Messenger der als Emailsubject bei Benachrichtigung übers erscheinen von VKC Dokumenten verschickt wird.
 *
 * Revision 1.79  2007/11/05 11:33:39  korowa
 * Rückkehr zu der früheren Version
 *
 * Revision 1.77  2007/10/25 11:11:09  korowa
 * Das Löschen des Benutzers vervollständigt
 *
 * Revision 1.76  2007/10/15 06:29:45  marbobo
 * + Ded. Admin-Rechte kopieren umgeschrieben
 *
 * Revision 1.75  2007/08/28 12:15:54  korowa
 * Export- und Importfunktionen hinzugefügt
 *
 * Revision 1.74  2007/08/08 12:14:41  marbobo
 * + License-Funktionalität komplett umgeschrieben
 * + Bugfix bzgl. Beschreibungsfelder von Entitäten
 * + Bugfix mailing
 *
 * Revision 1.73  2007/07/27 11:14:34  marbobo
 * + Ungenutzte Anweisungen entfernt
 *
 * Revision 1.72  2007/03/14 10:07:47  jw
 * Method to transfer group members added (used for copy admin rights).
 *
 * Revision 1.71  2007/01/12 12:58:24  korowa
 * bugfixing: Die leere Liste mit dynamischen attributen wird jetzt auch gespeichert
 *
 * Revision 1.70  2006/11/21 12:22:01  korowa
 * bugfixing
 *
 * Revision 1.69  2006/11/21 09:27:51  korowa
 * bugfixing
 *
 * Revision 1.68  2006/11/06 10:45:28  korowa
 * Zugriffsfunktionen für akzeptierte Lizenz und Zeitpunkt des Akzeptierens hinzugefügt
 *
 * Revision 1.67  2006/09/21 00:40:43  marbobo
 * + Bugfix (Rechte die über "Jeder" erworben werden, werden jetzt auch berücksichtigt)
 *
 * Revision 1.66  2006/08/31 12:23:47  korowa
 * Dynamische Attribute für Users eingefügt
 *
 * Revision 1.65  2006/07/11 14:21:20  lawern
 * PostgreSQL ist pingelig und erlaubt kein get/setBoolean auf Int-Spalten.
 *
 * Revision 1.64  2006/05/28 18:10:53  marbobo
 * + Benutzer- und Gruppensuche nach Umlauten nochmals verbessert
 *
 * Revision 1.63  2006/05/12 14:17:37  marbobo
 * + Bugfix
 *
 * Revision 1.62  2006/05/12 08:09:07  korowa
 * +showTree zum Userprofil hinzugefügt. Dieser parameter regelt die Anzeige des Explorerfensters beim VKC-Anmeldung. Lässt sich im Userprofil umschalten.
 *
 * Revision 1.61  2006/04/18 16:09:09  marbobo
 * + Bugfix (Benutzer konnte nicht gelöscht werden wenn Dokumentansicht angepasst wurde)
 *
 * Revision 1.60  2006/03/23 11:39:18  marbobo
 * + Mögliche NPE abgefangen
 *
 * Revision 1.59  2006/02/28 16:04:58  marbobo
 * + Spaltennamen in der Datenbank angepasst (Oracle-Einschränkung)
 *
 * Revision 1.58  2006/02/27 19:39:29  marbobo
 * + Bugfix
 *
 * Revision 1.57  2006/02/27 16:15:41  marbobo
 * + Systemsichten hinzugefügt
 * + SysLinks angepasst
 * + Benutzer können falls gewünscht Dokumentqueries anlegen
 *
 * Revision 1.56  2005/11/24 01:26:03  marbobo
 * + Begrenzung von Fehlversuchen bei Passworteingabe und Sperrung von Benutzer eingebaut (beide sind Konfigurierbar)
 *
 * Revision 1.55  2005/10/06 08:05:30  jonyp
 * bugfix: delete entries from userdocmails if user is to be deleted (deleteUser method)
 *
 * Revision 1.54  2005/09/01 09:56:38  jonyp
 * retrieve userinfos with order by lastname,firstname,username
 *
 * Revision 1.53  2005/08/09 19:07:00  jonyp
 * added changeCreatorId method
 *
 * Revision 1.52  2005/07/06 13:32:12  jonyp
 * getValidNewPublishers: retrieve only valid users, which are not external
 *
 * Revision 1.51  2005/07/05 10:09:43  jonyp
 * change method "retrieveUserInfos" from private to public scope
 *
 * Revision 1.50  2005/05/23 14:31:35  marbobo
 * + Bugfix: Nun werden auch Stellvertreter-Einträge gelöscht
 *
 * Revision 1.49  2005/05/11 19:30:33  marbobo
 * + Namensänderung in der Datenbank bei Sortiereinstellungen (wegen Oracle Probleme)
 *
 * Revision 1.48  2005/04/28 15:32:22  korowa
 * Die Sortierungs Methoden werden jetzt im Profil in Datenbank gespeichert
 *
 * Revision 1.47  2005/01/19 12:46:27  jonyp
 * deleted userregisteredkeywords statements
 *
 * Revision 1.46  2005/01/18 16:53:58  jonyp
 * deleted logic for userregsiteredkeywords
 *
 * Revision 1.45  2004/12/16 14:13:36  marbobo
 * + Weitere Umstellungen und Optimierungen bzgl. PreparedStatements
 *
 * Revision 1.44  2004/12/16 13:18:04  jonyp
 * + saveMostRecentLogout
 *
 * Revision 1.43  2004/12/11 18:33:58  marbobo
 * + Restliche Statements auf PreparedStatement umgestellt
 *
 * Revision 1.42  2004/12/09 21:07:39  marbobo
 * + kleine Optimierungen und Bugfixes
 * +an einigen Stellen Rreihenfolge der close-Anweisungen vereinheitlicht
 *
 * Revision 1.41  2004/12/09 12:39:27  lawern
 * vergessene PreparedStatements geschlossen
 *
 * Revision 1.40  2004/12/07 22:04:53  jonyp
 * added TODO in retrieveUser for VKCLdapUser
 *
 * Revision 1.39  2004/12/06 14:08:00  marbobo
 * + Statement auf PreparedStatement umgestellt
 *
 * Revision 1.38  2004/12/01 16:43:46  jonyp
 * added comment
 *
 * Revision 1.37  2004/11/24 13:48:36  koppy
 * bugfix: 2xdatabase.getConnection(), aber nur 1x geschlossen
 *
 * Revision 1.36  2004/11/24 13:01:12  lawern
 * Global "conn.setAutoCommit( false );" enfernt, weil überflüssig.
 *
 * Revision 1.35  2004/11/24 09:25:53  lawern
 * Hibernate ist VKC-Geschichte!
 *
 * Revision 1.34  2004/11/22 14:41:31  vchris
 * MessagingTimePeriod mit Standardwert besetzt
 *
 * Revision 1.33  2004/11/18 19:38:23  marbobo
 * + changed order of close-stmt to resultset/connect/session  in finally-blocks
 *
 * Revision 1.32  2004/11/18 16:05:25  lawern
 * changed order of close-stmt to resultset/connect/session  in finally-blocks
 *
 * Revision 1.31  2004/11/13 20:43:59  marbobo
 * + Bugfix bei der Benutzersuche ( Betrifft die Literal Behandlung )
 *
 * Revision 1.30  2004/11/12 00:25:17  marbobo
 * + Einige neue Methoden implementiert
 *
 * Revision 1.29  2004/11/11 14:14:04  marbobo
 * + retrieveUserInfosByQuery()
 *
 * Revision 1.28  2004/11/10 15:29:43  jonyp
 * added info field for externalServerId
 *
 * Revision 1.27  2004/11/10 15:02:44  koppy
 * Projektweite Überarbeitung von Error-Logs, Stacktraces,..
 *
 * Revision 1.26  2004/11/09 14:35:37  marbobo
 * + Umstellung von authorId auf publisherId
 * + Hinzufügen von AuthorName für Dokumente
 * + Nötwendige Änderungen betreffend AuthorName und PublisherId
 *
 * Revision 1.25  2004/11/05 01:55:09  marbobo
 * + Einige Select-Statements korrigiert
 *
 * Revision 1.24  2004/11/03 11:24:21  jonyp
 * do not display user SYSTEM-USER
 *
 * Revision 1.23  2004/10/22 15:37:20  jonyp
 * added support for ldap user
 *
 * Revision 1.22  2004/10/21 14:45:25  marbobo
 * + CURRENT_TIME ersetzt mit java-Anweisung
 *
 * Revision 1.21  2004/08/20 15:41:36  jonyp
 * added deletion of userregistereredserachqueires when a user is deleted
 *
 * Revision 1.20  2004/08/20 12:41:59  koppy
 * +getAllUserIds(), kleine Änderungen
 *
 * Revision 1.19  2004/08/19 14:09:46  marbobo
 * + Zusätzliche Felder für UserInfo-Objekte
 *
 * Revision 1.18  2004/07/22 22:10:14  marbobo
 * + Speicher und Holen von "mostrecentnews"-Feld für VKCUser freigeschaltet
 *
 * Revision 1.17  2004/07/21 14:16:45  jonyp
 * added column for user: mostrecentnews
 *
 * Revision 1.16  2004/06/15 20:23:15  marbobo
 * Hibernate für VKCUser rausgenommen
 *
 * Revision 1.15  2004/06/14 21:12:40  marbobo
 * - Überflüssige session.flush() und session.connection().commit() entfernt
 *
 * Revision 1.14  2004/06/03 16:48:41  jonyp
 * added saveMostRecentLogin-method
 *
 * Revision 1.13  2004/05/15 21:13:43  marbobo
 * retrieveUserInfo-Methoden angepasst
 *
 * Revision 1.12  2004/05/14 14:45:30  jonyp
 * set UserInfo.created to timestamp datatype
 *
 * Revision 1.11  2004/05/14 10:30:32  marbobo
 * + Ersteller und Erstellungsdatum anzeigen
 *
 * Revision 1.10  2004/05/11 15:51:40  jonyp
 * added deletion of doc/catFavorites when user is deleted
 *
 * Revision 1.9  2004/05/07 13:54:00  jonyp
 * added header for cvs comments
 *
 */
package vkc.db.serializer;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

import java.sql.Connection;
import vkc.db.entities.VKCAclEntry;
import vkc.db.entities.VKCAclType;
import vkc.db.entities.VKCCategory;
import vkc.db.entities.VKCUser;
import vkc.db.helper.DBHelper;
import vkc.exception.DBException;
import vkc.ldap.VKCLdapUser;
import vkc.misc.VKCLogManager;
import vkc.security.VKCAcl;
import vkc.view.WebShare;
import vkc.view.bean.UserInfo;

public class VKCUserSerializer {
    /**
     * Speichert einen VKCUser in der Datenbank ab
     *
     * @param user das zu speichernde VKCUser Objekt
     */
    public static final void saveUser(VKCUser user) throws DBException {
        if (user == null) {
        	return;
        }
        
        Connection conn = null;

        try {
            conn = WebShare.database.getConnection();
			saveUser(user, conn);
            conn.commit();
        } catch (Exception e) {
            DBHelper.rollBack(conn);
            VKCLogManager.errorLog(VKCUserSerializer.class, "1", "Error saving user: " + user, e);
            throw new DBException(e);
        } finally {
            DBHelper.close(conn);
        }
    }

	/**
	 * Speichert einen VKCUser in der Datenbank ab
	 *
	 * @param user das zu speichernde VKCUser Objekt
	 */
	public static final void saveUser(VKCUser user, Connection conn) throws DBException {
		if (user == null) {
			return;
		}
	
		try {
			if (user.getUserId() != null && DBHelper.existsEntry(conn, "Users", "userId", user.getUserId())) {
				// alter USER --> UPDATE
				String stmt = "UPDATE USERS SET ";
				stmt += "ACCOUNTVALID = ?, ";
				stmt += "ADMINPRIVILEGES = ?, ";
				stmt += "MANAGEOWNGROUPS = ?, ";
				stmt += "MANAGEOWNUSERS = ?, ";
				stmt += "MANAGEOWNUSERS_S_V = ?, ";
				stmt += "MANAGEOWNPROXIES = ?, ";
				stmt += "MANAGEVIRTUALDOC_Q = ?, ";
				stmt += "ENABLEMESSAGING = ?, ";
				stmt += "PASSWDCHANGED = ?, ";
				stmt += "SHOWEMAIL = ?, ";
				stmt += "SHOWTREE = ?, ";
				stmt += "CREATORID = ?, ";
				stmt += "DYNATTRSID = ?, ";
				stmt += "STARTCATEGORYID = ?, ";
				stmt += "LANGUAGEID = ?, ";
				stmt += "LASTCHANGERID = ?, ";
				stmt += "MESSAGINGTIMEPERIOD = ?, ";
				stmt += "QUOTA = ?, ";				
				stmt += "SESSIONTIMEOUT = ?, ";
				stmt += "TEMPLATEID = ?, ";
				stmt += "CITY = ?, ";
				stmt += "COMPANY = ?, ";
				stmt += "CONTACTURL = ?, ";				
				stmt += "DEPARTMENT = ?, ";
				stmt += "EMAIL = ?, ";
				stmt += "FAX = ?, ";
				stmt += "FIRSTNAME = ?, ";
				stmt += "HOMEPAGEURL = ?, ";				
				stmt += "LASTNAME = ?, ";
				stmt += "MOBILE = ?, ";
				stmt += "PASSWD = ?, ";
				stmt += "PHONE = ?, ";
				stmt += "TITLE = ?, ";				
				stmt += "STREET = ?, ";
				stmt += "USERNAME = ?, ";
				stmt += "ZIP = ?, ";
				stmt += "CREATED = ?, ";
				stmt += "LASTCHANGED = ?, ";
				stmt += "MOSTRECENTLOGIN = ?, ";
				stmt += "MOSTRECENTLOGOUT = ?, ";
				stmt += "MOSTRECENTNEWS = ?, ";
				stmt += "PASSWDCHANGEDTS = ?, ";
				stmt += "VALIDFROM = ?, ";
				stmt += "VALIDUNTIL = ?, ";
				stmt += "EXTERNALSERVERID = ?, ";
				stmt += "EXTERNALSERVERTYPE = ?, ";
				stmt += "EXTERNALUSERID = ?, ";
				stmt += "S_DOCSORTMODE = ?, ";
				stmt += "B_DOCSORTMODE = ?, ";
				stmt += "L_DOCSORTMODE = ?, ";
				stmt += "C_DOCSORTMODE = ?, ";
				stmt += "PWDMISMATCHCOUNT = ?, ";
				stmt += "ALLOWMANAGE_S_V_T = ?, ";
				stmt += "SYSTEMVIEWTYPEID = ?, ";               
                stmt += "ENABLEHTMLNOTIFICATION = ? ";
				stmt += "WHERE USERID = ?";
				
				executeUserStatement(stmt, user, conn);
			} else {
				// neuer User
				user.setUserId(DBHelper.getNewId(conn, "Users", "userId"));

				String stmt = "INSERT INTO USERS ( ";
				stmt += "ACCOUNTVALID, ";
				stmt += "ADMINPRIVILEGES, ";
				stmt += "MANAGEOWNGROUPS, ";
				stmt += "MANAGEOWNUSERS, ";
				stmt += "MANAGEOWNUSERS_S_V, ";
				stmt += "MANAGEOWNPROXIES, ";
				stmt += "MANAGEVIRTUALDOC_Q, ";
				stmt += "ENABLEMESSAGING, ";
				stmt += "PASSWDCHANGED, ";
				stmt += "SHOWEMAIL, ";
				stmt += "SHOWTREE, ";
				stmt += "CREATORID, ";
				stmt += "DYNATTRSID, ";
				stmt += "STARTCATEGORYID, ";
				stmt += "LANGUAGEID, ";
				stmt += "LASTCHANGERID, ";
				stmt += "MESSAGINGTIMEPERIOD, ";
				stmt += "QUOTA, ";				
				stmt += "SESSIONTIMEOUT, ";
				stmt += "TEMPLATEID, ";
				stmt += "CITY, ";
				stmt += "COMPANY, ";
				stmt += "CONTACTURL, ";				
				stmt += "DEPARTMENT, ";
				stmt += "EMAIL, ";
				stmt += "FAX, ";
				stmt += "FIRSTNAME, ";
				stmt += "HOMEPAGEURL, ";				
				stmt += "LASTNAME, ";
				stmt += "MOBILE, ";
				stmt += "PASSWD, ";
				stmt += "PHONE, ";
				stmt += "TITLE, ";		
				stmt += "STREET, ";
				stmt += "USERNAME, ";
				stmt += "ZIP, ";
				stmt += "CREATED, ";				
				stmt += "LASTCHANGED, ";
				stmt += "MOSTRECENTLOGIN, ";
				stmt += "MOSTRECENTLOGOUT, ";
				stmt += "MOSTRECENTNEWS, ";
				stmt += "PASSWDCHANGEDTS, ";
				stmt += "VALIDFROM, ";
				stmt += "VALIDUNTIL, ";
				stmt += "EXTERNALSERVERID, ";
				stmt += "EXTERNALSERVERTYPE, ";
				stmt += "EXTERNALUSERID, ";
				stmt += "S_DOCSORTMODE, ";
				stmt += "B_DOCSORTMODE, ";
				stmt += "L_DOCSORTMODE, ";
				stmt += "C_DOCSORTMODE, ";
				stmt += "PWDMISMATCHCOUNT, ";
				stmt += "ALLOWMANAGE_S_V_T, ";
				stmt += "SYSTEMVIEWTYPEID, ";
                stmt += "ENABLEHTMLNOTIFICATION, ";
				stmt += "USERID ) ";				
				stmt += "VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?," +
								" ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?," +
								" ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) ";
				
				executeUserStatement(stmt, user, conn);
			}
			
            VKCDynUserAttributeSerializer.saveDynUserAttributes(conn,(List)user.getDynaAttributes(), user.getUserId());
			// Assozierte Gruppen speichern
			VKCGroupSerializer.saveUserGroups(user, conn);
		} catch (Exception e) {
			VKCLogManager.errorLog(VKCUserSerializer.class, "1", "Error saving user: " + user, e);
			throw new DBException(e);
		}
	}

    /**
     * Löscht einen VKCUser aus der Datenbank
     *
     * @param userId ID des zu löschenden Users
     */
    public static final void deleteUser(Integer userId) throws DBException {
        Connection conn = null;
        PreparedStatement pStmt = null;

        try {
            conn = WebShare.database.getConnection();            

            pStmt = conn.prepareStatement("DELETE FROM ProxyUsers WHERE userId = ?");
            pStmt.setInt(1, userId.intValue());
            pStmt.execute();
            DBHelper.close(pStmt);
            
            pStmt = conn.prepareStatement("DELETE FROM ProxyUsers WHERE proxyUserId = ?");
            pStmt.setInt(1, userId.intValue());
            pStmt.execute();
            DBHelper.close(pStmt);
            
            pStmt = conn.prepareStatement("DELETE FROM UserRegisteredCategories WHERE userId = ?");
            pStmt.setInt(1, userId.intValue());
            pStmt.execute();
            DBHelper.close(pStmt);
            
            pStmt = conn.prepareStatement("DELETE FROM UserRegisteredDocuments WHERE userId = ?");
            pStmt.setInt(1, userId.intValue());
            pStmt.execute();
            DBHelper.close(pStmt);
            
            pStmt = conn.prepareStatement("DELETE FROM CategoryAcls WHERE AclTypeId = " + VKCAclType.USER_TYPE + " AND AccessorId = ?");
            pStmt.setInt(1, userId.intValue());
            pStmt.execute();
            DBHelper.close(pStmt);
            
            pStmt = conn.prepareStatement("DELETE FROM UserGroups WHERE userId = ?");
            pStmt.setInt(1, userId.intValue());
            pStmt.execute();
            DBHelper.close(pStmt);
            
            pStmt = conn.prepareStatement("DELETE FROM UserDocMails WHERE userId = ?");
            pStmt.setInt(1, userId.intValue());
            pStmt.execute();
            DBHelper.close(pStmt);
            
            pStmt = conn.prepareStatement("DELETE FROM DefaultCatAcls WHERE AclTypeId = " + VKCAclType.USER_TYPE + " AND AccessorId = ?");
            pStmt.setInt(1, userId.intValue());
            pStmt.execute();
            DBHelper.close(pStmt);  

			DBHelper.deleteEntry(conn, "USERDOCVIEWFIELDS", "userId", userId);
			
			VKCCategoryFavoritesSerializer.deleteCategoryFavorites(userId, null, conn);
			VKCDocumentFavoritesSerializer.deleteDocumentFavorites(userId, null, conn);
			VKCUserDocHistorySerializer.deleteDocumentHistory(userId, null, conn);			
            VKCUserRegSearchQuerySerializer.deleteSearchQuery(userId, null, conn);
            
            
            pStmt = conn.prepareStatement("DELETE FROM DYNUSERATTRVALUES WHERE userId = ?");
            pStmt.setInt(1, userId.intValue());
            pStmt.execute();
            DBHelper.close(pStmt);
            
            pStmt = conn.prepareStatement("DELETE FROM USERPROJECTHISTORIES WHERE userId = ?");
            pStmt.setInt(1, userId.intValue());
            pStmt.execute();
            DBHelper.close(pStmt);
            
            pStmt = conn.prepareStatement("DELETE FROM USERFUNCTIONALHISTORIES WHERE userId = ?");
            pStmt.setInt(1, userId.intValue());
            pStmt.execute();
            DBHelper.close(pStmt);
            
            pStmt = conn.prepareStatement("DELETE FROM USERLICENSE WHERE userId = ?");
            pStmt.setInt(1, userId.intValue());
            pStmt.execute();
            DBHelper.close(pStmt);
            
            pStmt = conn.prepareStatement("update categories set creatorid = ?  where creatorid = ? ");
            pStmt.setInt(1, VKCUser.SYSTEM_USER_ID.intValue());
            pStmt.setInt(2, userId.intValue());
            pStmt.execute();
            DBHelper.close(pStmt);
            
            pStmt = conn.prepareStatement("update categories  set lastchangerid = ? where lastchangerid = ? ");
            pStmt.setInt(1, VKCUser.SYSTEM_USER_ID.intValue());
            pStmt.setInt(2, userId.intValue());
            pStmt.execute();
            DBHelper.close(pStmt);
            
            pStmt = conn.prepareStatement("update categories  set lastaccessorid = ? where lastaccessorid = ? ");
            pStmt.setInt(1, VKCUser.SYSTEM_USER_ID.intValue());
            pStmt.setInt(2, userId.intValue());
            pStmt.execute();  
            DBHelper.close(pStmt);
            
            pStmt = conn.prepareStatement("update categoryacls set creatorid = ? where creatorid = ? ");
            pStmt.setInt(1, VKCUser.SYSTEM_USER_ID.intValue());
            pStmt.setInt(2, userId.intValue());
            pStmt.execute();
            DBHelper.close(pStmt);
            
            
            pStmt = conn.prepareStatement("update defaultcatacls set creatorid = ? where creatorid = ? ");
            pStmt.setInt(1, VKCUser.SYSTEM_USER_ID.intValue());
            pStmt.setInt(2, userId.intValue());
            pStmt.execute();
            DBHelper.close(pStmt);            
           
            pStmt = conn.prepareStatement("update categorydocuments set creatorid = ?  where creatorid = ? ");
            pStmt.setInt(1, VKCUser.SYSTEM_USER_ID.intValue());
            pStmt.setInt(2, userId.intValue());
            pStmt.execute();
            DBHelper.close(pStmt);
            
            pStmt = conn.prepareStatement("update catprojectsteps set checkerid = ? where checkerid = ? ");
            pStmt.setInt(1, VKCUser.SYSTEM_USER_ID.intValue());
            pStmt.setInt(2, userId.intValue());
            pStmt.execute();
            DBHelper.close(pStmt);
            
            pStmt = conn.prepareStatement("update catprojectsteps set processorid = ? where processorid = ? ");
            pStmt.setInt(1, VKCUser.SYSTEM_USER_ID.intValue());
            pStmt.setInt(2, userId.intValue());
            pStmt.execute();
            DBHelper.close(pStmt);
            
            pStmt = conn.prepareStatement("update groups set creatorid = ? where creatorid = ? ");
            pStmt.setInt(1, VKCUser.SYSTEM_USER_ID.intValue());
            pStmt.setInt(2, userId.intValue());
            pStmt.execute();
            DBHelper.close(pStmt);
            
            pStmt = conn.prepareStatement("update groups set lastchangerid = ? where lastchangerid = ? ");
            pStmt.setInt(1, VKCUser.SYSTEM_USER_ID.intValue());
            pStmt.setInt(2, userId.intValue());
            pStmt.execute();
            DBHelper.close(pStmt);
            
            pStmt = conn.prepareStatement("update license set creatorid = ? where creatorid = ? ");
            pStmt.setInt(1, VKCUser.SYSTEM_USER_ID.intValue());
            pStmt.setInt(2, userId.intValue());
            pStmt.execute();
            DBHelper.close(pStmt);
            
            pStmt = conn.prepareStatement("update news set creatorid = ? where creatorid = ? ");
            pStmt.setInt(1, VKCUser.SYSTEM_USER_ID.intValue());
            pStmt.setInt(2, userId.intValue());
            pStmt.execute();
            DBHelper.close(pStmt);
            
            pStmt = conn.prepareStatement("update projectmessages set creatorid = ? where creatorid = ? ");
            pStmt.setInt(1, VKCUser.SYSTEM_USER_ID.intValue());
            pStmt.setInt(2, userId.intValue());
            pStmt.execute();
            DBHelper.close(pStmt);
                       
            pStmt = conn.prepareStatement("DELETE FROM Users WHERE userId = ?");
            pStmt.setInt(1, userId.intValue());
            pStmt.execute();
            
            
            conn.commit();
        } catch (Exception e) {
            DBHelper.rollBack(conn);
            VKCLogManager.errorLog(VKCUserSerializer.class, "1", "Error occured while deleting user ID=" + userId, e);
            throw new DBException(e);
        } finally {
            DBHelper.close(pStmt);
            DBHelper.close(conn);
        }
    }

    public static final Set getAllUserIds() throws DBException {
        Connection conn = null;
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        Set userIds = new HashSet();
        
        try {
            conn = WebShare.database.getConnection();
            pStmt = conn.prepareStatement("SELECT USERID FROM USERS WHERE USERID > 0");
            rs = pStmt.executeQuery();
            
            while(rs.next()) {
                userIds.add(new Integer(rs.getInt("USERID")));
            }
        } catch (Exception e) {
            VKCLogManager.errorLog(VKCUserSerializer.class, "", e.getMessage(), e);
            throw new DBException(e);
        } finally {
            DBHelper.close(rs);
            DBHelper.close(pStmt);
            DBHelper.close(conn);
        }
        
        return userIds;
    }
    
    /**
     * Lädt alle verfügbaren User aus der Datenbank und gibt sie als HashMap
     * zurück
     *
     * @return HashMap von VKCUser Objekten mit dem Usernamen als Schlüssel
     *
     * @throws DBException DOCUMENT ME!
     */
    public static final HashMap getAllUsers() throws DBException {
		Connection conn = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		HashMap users = new HashMap();

		try {
			conn = WebShare.database.getConnection();
			pStmt = conn.prepareStatement("SELECT * FROM users WHERE userId > 0");
			rs = pStmt.executeQuery();

			while (rs.next()) {
				VKCUser user = new VKCUser();
				setUserFields(user, rs);
				// Userinterface-Sprache bestimmen
				user.setLanguage(VKCLanguageSerializer.getLanguage(user.getLanguageId()));
				// get groups for user
				user.setGroups(VKCGroupSerializer.retrieveUserGroups(user.getUserId(), conn));
				// Dynamische Attribute anbinden
				VKCDynaAttributeSerializer.retrieveDynaAttributes(conn, user);

				users.put(user.getUser().trim(), user);
			}

			return users;
		} catch (Exception e) {
			VKCLogManager.errorLog(VKCUserSerializer.class, "1", "Error occured while retrieving users!", e);
			throw new DBException(e);
		} finally {
			DBHelper.close(rs);
			DBHelper.close(pStmt);
			DBHelper.close(conn);
		}
    }

	/**
	 * Lädt einen VKCUser aus der Datenbank
	 *
	 * @param key ID oder Benutzername des zu ladenden VKCUsers
	 *
	 * @return das gewünschte VKCUser Objekt
	 */
	public static final VKCUser retrieveUser( Object key ) throws DBException {
		if ( key == null ) {
			return null;
		}
		
		Connection conn = null;

		try {
			conn = WebShare.database.getConnection();
			return retrieveUser(key, conn);
		} catch (Exception e) {
			throw new DBException(e);
		} finally {
			DBHelper.close(conn);
		}
	}

    /**
     * Lädt einen VKCUser aus der Datenbank
     *
	 * @param key ID oder Benutzername des zu ladenden VKCUsers
     *
     * @return das gewünschte VKCUser Objekt
     *
     * @throws DBException DOCUMENT ME!
     */
    public static final VKCUser retrieveUser(Object key, Connection conn) throws DBException {
		if (key == null) {
			return null;
		}
		
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		VKCUser user = new VKCUser();
		Integer userId = null;
		String username = null;

        try {
			if (key instanceof Integer) {
				userId = (Integer)key;
				pStmt = conn.prepareStatement("SELECT * FROM USERS WHERE USERID = ?");
				pStmt.setInt(1, userId.intValue());
				rs = pStmt.executeQuery();
			} else if (key instanceof String) {
				username = (String)key;

				if ("".equals(key)) {
					return null;
				}

				pStmt = conn.prepareStatement("SELECT * FROM USERS WHERE USERNAME = ?");
				pStmt.setString(1, username);
				rs = pStmt.executeQuery();
			} else {
				return null;
			}
			
            if (!rs.next()) {
                return null;
            }
			
			// prüfen, ob der Benutzer ein VKCLdapUser ist
			if (rs.getInt("EXTERNALSERVERID") != 0) {
				if (rs.getString("EXTERNALSERVERTYPE").equals("ldap")) {
					user = new VKCLdapUser();
					// TODO: load an ldap-server and context
				}
			}
            
            setUserFields(user, rs);

            // Userinterface-Sprache bestimmen
            user.setLanguage(VKCLanguageSerializer.getLanguage(user.getLanguageId()));
            
            // get groups for user
            user.setGroups(VKCGroupSerializer.retrieveUserGroups(user.getUserId(), conn));
            
            // Dynamische Attribute anbinden
            user.setDynaAttributes(VKCDynUserAttributeSerializer.retrieveDynUserAttributes( user.getUserId()));

            return user;
        } catch (Exception e) {
            if (username != null) {
                VKCLogManager.errorLog(VKCUserSerializer.class, "1", "Error while retrieving user with name '" + username + "'", e);
            } else {
                VKCLogManager.errorLog(VKCUserSerializer.class, "1", "Error while retrieving user with id = " + key, e);
            }
            
            throw new DBException(e);
        } finally {
			DBHelper.close(rs);
			DBHelper.close(pStmt);
        }
    }

    public final static UserInfo retrieveUserInfo(Object userId, Connection conn) throws Exception {
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        UserInfo userInfo = null;

        if (userId == null) 
        	return null;
        
        try {
            if (userId instanceof Integer) {
                pStmt = conn.prepareStatement("SELECT * FROM USERS WHERE USERID = ?");
				pStmt.setInt(1, ((Integer)userId).intValue());
            } else {
                pStmt = conn.prepareStatement("SELECT * FROM USERS WHERE USERNAME = ?");
				pStmt.setString(1, (String)userId);
            }
            rs = pStmt.executeQuery();
            if (rs.next()) {
                userInfo = new UserInfo();
                setUserInfoFields(userInfo, rs);
            }
            
            return userInfo;
    	} finally {
            DBHelper.close(rs);
            DBHelper.close(pStmt);
        }
    }
    
    /**
     * Lädt eine UserInfo-Bean aus der Datenbank
     *
     * @param userId ID des zu ladenden UserInfo-Beans
     * @param username Username des zu ladenden UserInfo-Beans
     *
     * @return das gewünschte UserInfo-Bean Objekt
     */
    public final static UserInfo retrieveUserInfo(Integer userId) throws DBException {
        if (userId == null) {
            return null;
        }

        Connection conn = null;
        
        try {
            conn = WebShare.database.getConnection();
            return retrieveUserInfo(userId, conn);
       } catch (Exception e) {
            VKCLogManager.errorLog(VKCUserSerializer.class, "1", "Error occured while retrieving UserInfo!", e);
            throw new DBException(e);
        } finally {
            DBHelper.close(conn);
        }
    }

    public static final List retrieveUserInfos(String whereClause, Object[] params) throws DBException {
        Connection conn = null;
        PreparedStatement pStmt = null;
        ResultSet rs = null;

        try {
            conn = WebShare.database.getConnection();
            pStmt = conn.prepareStatement("SELECT * FROM USERS " + whereClause);
            DBHelper.setElements(pStmt, params);
            rs = pStmt.executeQuery();

            List list = new ArrayList();

            while (rs.next()) {
                UserInfo info = new UserInfo();
                setUserInfoFields(info, rs);
                list.add(info);
            }

            return list;
        } catch (Exception e) {
            VKCLogManager.errorLog(VKCUserSerializer.class, "1", "Error occured while retrieving UserInfos!", e);
            throw new DBException(e);
        } finally {
            DBHelper.close(rs);
            DBHelper.close(pStmt);
            DBHelper.close(conn);
        }
    }

    public static final int getAllUsersCount() throws DBException {
        return getUsersCount("WHERE userId > 0", null);
    }
    
    public static final int getAllUsersCountByCreator(Integer creatorId) throws DBException {
        if (creatorId == null) {
            return 0;
        }

        return getUsersCount("WHERE userId > 0 AND creatorId = ?", new Object[]{creatorId});
    }
    
    private static final int getUsersCount(String whereClause, Object[] params) throws DBException {
        Connection conn = null;
        PreparedStatement pStmt = null;
        ResultSet rs = null;

        try {
            conn = WebShare.database.getConnection();
            pStmt = conn.prepareStatement("SELECT count(*) AS userscount FROM USERS " + whereClause);
            DBHelper.setElements(pStmt, params);
            rs = pStmt.executeQuery();

			if (rs.next()) {
				return rs.getInt("userscount");
			} 

			return 0;
        } catch (Exception e) {
            VKCLogManager.errorLog(VKCUserSerializer.class, "1", "Error occured while retrieving UserInfos!", e);
            throw new DBException(e);
        } finally {
            DBHelper.close(rs);
            DBHelper.close(pStmt);
            DBHelper.close(conn);
        }
    }

    /**
     * Lädt alle verfügbaren UserInfo aus der Datenbank und gibt sie als
     * List zurück
     *
     * @return List von UserInfo-Objekten
     */
    public static final List retrieveUserInfos() throws DBException {
        return retrieveUserInfos("WHERE userId > 0 ORDER BY lastname ASC, firstname ASC, username ASC", null);
    }

    /**
     * Lädt alle verfügbaren UserInfo aus der Datenbank die der Benutzer angelegt hat 
     * und gibt sie als List zurück
     *
     * @return List von UserInfo-Objekten
     */
    public static final List retrieveUserInfosByCreator(Integer creatorId) throws DBException {
        if (creatorId == null) {
            return new ArrayList();
        }
        
        return retrieveUserInfos("WHERE userId > 0 AND creatorId = ?", new Object[]{creatorId});
    }

    /**
     * Lädt alle verfügbaren UserInfo aus der Datenbank die mit den 
     * angegebenen Suchanfragen übereinstimmen.
     *
     * @return List von UserInfo-Objekten
     */
    public static final List retrieveUserInfosByQueries(String[] queries) throws DBException {
        if (queries == null) {
            return new ArrayList();
        }
        
        String condition = "( ";
        Object[] params = new Object[6 * queries.length];
        
        for (int i = 0; i < queries.length; i++) {
        	condition += "( UPPER(username) LIKE UPPER(?) ESCAPE '\\' OR UPPER(lastname) LIKE UPPER(?) ESCAPE '\\' OR UPPER(firstname) LIKE UPPER(?) ESCAPE '\\' )";
    		condition += " OR ";
        	condition += "( UPPER(username) LIKE UPPER(?) ESCAPE '\\' OR UPPER(lastname) LIKE UPPER(?) ESCAPE '\\' OR UPPER(firstname) LIKE UPPER(?) ESCAPE '\\' )";

        	if (i < queries.length - 1) {
        		condition += " OR ";
			}
        	
        	String upper = queries[i].toUpperCase();
        	String lower = queries[i].toLowerCase();
        	int offset = 6 * i;

        	params[offset] = upper;
        	params[offset + 1] = upper;
        	params[offset + 2] = upper;
        	params[offset + 3] = lower;
        	params[offset + 4] = lower;
        	params[offset + 5] = lower;
		}
        
        condition += " )";
        
        return retrieveUserInfos("WHERE userId > 0 AND " + condition, params);
    }

    /**
     * Lädt alle verfügbaren UserInfo aus der Datenbank die der Benutzer angelegt hat
     * und die mit den angegebenen Suchanfragen übereinstimmen.
     *
     * @return List von UserInfo-Objekten
     */
    public static final List retrieveUserInfosByCreatorAndQueries(Integer creatorId, String[] queries) throws DBException {
        if (creatorId == null || queries == null) {
            return new ArrayList();
        }
        
        String condition = "( ";
        Object[] params = new Object[6 * queries.length + 1];
        params[0] = creatorId;
        
        for (int i = 0; i < queries.length; i++) {
        	condition += "( UPPER(username) LIKE UPPER(?) ESCAPE '\\' OR UPPER(lastname) LIKE UPPER(?) ESCAPE '\\' OR UPPER(firstname) LIKE UPPER(?) ESCAPE '\\' )";
    		condition += " OR ";
        	condition += "( UPPER(username) LIKE UPPER(?) ESCAPE '\\' OR UPPER(lastname) LIKE UPPER(?) ESCAPE '\\' OR UPPER(firstname) LIKE UPPER(?) ESCAPE '\\' )";

        	if (i < queries.length - 1) {
        		condition += " OR ";
			}
        	
        	String upper = queries[i].toUpperCase();
        	String lower = queries[i].toLowerCase();
        	int offset = 6 * i;

        	params[offset + 1] = upper;
        	params[offset + 2] = upper;
        	params[offset + 3] = upper;
        	params[offset + 4] = lower;
        	params[offset + 5] = lower;
        	params[offset + 6] = lower;
		}
        
        condition += " )";

        return retrieveUserInfos("WHERE userId > 0 AND creatorId = ? AND " + condition, params);
    }

    /**
     * Lädt alle verfügbaren Userinfo, deren Status 'valid' ist und gibt
     * sie als HashMap zurück
     * 
     * @return HashMap von UserInfo-Objekten
     */
    public static final List retrieveValidUserInfos() throws DBException {
		Timestamp time = new Timestamp(System.currentTimeMillis());
        
		return retrieveUserInfos("WHERE userId > 0 AND accountvalid = 1 AND ( validFrom <= ? OR validFrom IS NULL ) AND ( validUntil >= ? OR validUntil IS NULL ) ORDER BY lastname ASC, firstname ASC, username ASC", new Object[]{time, time});
    }

    /**
     * Lädt alle verfügbaren Userinfo ungleich bisheriger Publisher, deren Status 'valid' ist 
     * und gibt sie als HashMap zurück
     * 
     * @return HashMap von UserInfo-Objekten
     */
    public static final List retrieveValidNewPublisherInfos(Integer oldPublisherId) throws DBException {
		Timestamp time = new Timestamp(System.currentTimeMillis());
        
		return retrieveUserInfos("WHERE userId > 0 AND accountvalid = 1 AND externalserverid IS NULL AND ( validFrom <= ? OR validFrom IS NULL ) AND ( validUntil >= ? OR validUntil IS NULL) AND userId <> ? ORDER BY lastname ASC, firstname ASC, username ASC", new Object[]{time, time, oldPublisherId});
    }
    
	/**
	 * Method creates a userInfo from the current cursor in a sql result set
	 */
	private final static void setUserFields(VKCUser user, ResultSet rs) throws SQLException {
		user.setAccountValid(rs.getInt("ACCOUNTVALID") != 0);
		user.setAdminPrivileges(rs.getInt("ADMINPRIVILEGES") != 0);
		user.setManageOwnGroups(rs.getInt("MANAGEOWNGROUPS") != 0);
		user.setManageOwnUsers(rs.getInt("MANAGEOWNUSERS") != 0);
		user.setManageOwnUsersSystemViews(rs.getInt("MANAGEOWNUSERS_S_V") != 0);
		user.setManageOwnProxies(rs.getInt("MANAGEOWNPROXIES") != 0);
		user.setManageVirtualDocumentQueries(rs.getInt("MANAGEVIRTUALDOC_Q") != 0);
		user.setMessagingEnabled(rs.getInt("ENABLEMESSAGING") != 0);
		user.setPasswordChanged(rs.getInt("PASSWDCHANGED") != 0);
		user.setShowEmail(rs.getInt("SHOWEMAIL") != 0);
		user.setShowTree(rs.getInt("SHOWTREE") != 0);

		user.setCreatorId(new Integer(rs.getInt("CREATORID")));
		if (rs.wasNull()) user.setCreatorId(null);
		user.setDynaAttrsId(new Integer(rs.getInt("DYNATTRSID")));
		if (rs.wasNull()) user.setDynaAttrsId(null);
		user.setHomeCategoryId(new Integer(rs.getInt("STARTCATEGORYID")));
		if (rs.wasNull()) user.setHomeCategoryId(null);
		user.setLanguageId(new Integer(rs.getInt("LANGUAGEID")));
		if (rs.wasNull()) user.setLanguageId(null);
		user.setLastChangerId(new Integer(rs.getInt("LASTCHANGERID")));
		if (rs.wasNull()) user.setLastChangerId(null);
		user.setMessagingTimePeriod(new Integer(rs.getInt("MESSAGINGTIMEPERIOD")));
		if (rs.wasNull()) user.setMessagingTimePeriod(null);
		user.setQuota(new Integer(rs.getInt("QUOTA")));
		if (rs.wasNull()) user.setQuota(null);
		user.setSessionTimeout(new Integer(rs.getInt("SESSIONTIMEOUT")));
		if (rs.wasNull()) user.setSessionTimeout(null);
		user.setTemplateId(new Integer(rs.getInt("TEMPLATEID")));
		if (rs.wasNull()) user.setTemplateId(null);
		user.setUserId(new Integer(rs.getInt("USERID")));
		if (rs.wasNull()) user.setUserId(null);

		user.setCity(rs.getString("CITY"));
		user.setCompany(rs.getString("COMPANY"));
		user.setContactURL(rs.getString("CONTACTURL"));
		user.setDepartment(rs.getString("DEPARTMENT"));
		user.setEmail(rs.getString("EMAIL"));
		user.setFax(rs.getString("FAX"));
		user.setFirstname(rs.getString("FIRSTNAME"));
		user.setHomepageURL(rs.getString("HOMEPAGEURL"));
		user.setLastname(rs.getString("LASTNAME"));
		user.setMobile(rs.getString("MOBILE"));
		user.setPassword(rs.getString("PASSWD"));
		user.setPhone(rs.getString("PHONE"));
		user.setTitle(rs.getString("TITLE"));
		user.setStreet(rs.getString("STREET"));
		user.setUser(rs.getString("USERNAME"));
		user.setZip(rs.getString("ZIP"));

		user.setCreated(rs.getTimestamp("CREATED"));
		if (rs.wasNull()) user.setCreated(null);
		user.setLastChanged(rs.getTimestamp("LASTCHANGED"));
		if (rs.wasNull()) user.setLastChanged(null);
		user.setMostRecentLogin(rs.getTimestamp("MOSTRECENTLOGIN"));
		if (rs.wasNull()) user.setMostRecentLogin(null);
		user.setMostRecentLogout(rs.getTimestamp("MOSTRECENTLOGOUT"));
		if (rs.wasNull()) user.setMostRecentLogout(null);
		user.setMostRecentNews(rs.getTimestamp("MOSTRECENTNEWS"));
		if (rs.wasNull()) user.setMostRecentNews(null);

		user.setPasswordChangedTs(rs.getTimestamp("PASSWDCHANGEDTS"));
		if (rs.wasNull()) user.setPasswordChangedTs(null);
		user.setValidFrom(rs.getTimestamp("VALIDFROM"));
		if (rs.wasNull()) user.setValidFrom(null);
		user.setValidUntil(rs.getTimestamp("VALIDUNTIL"));
		if (rs.wasNull()) user.setValidUntil(null);
		
		user.setExternalServerId(new Integer(rs.getInt("EXTERNALSERVERID")));
		if (rs.wasNull()) user.setExternalServerId(null);
		
		user.setExternalServerType(rs.getString("EXTERNALSERVERTYPE"));
		user.setExternalUserId(rs.getString("EXTERNALUSERID"));

		user.setSearchDocumentSortMode(new Integer(rs.getInt("S_DOCSORTMODE")));
		if (rs.wasNull()) user.setSearchDocumentSortMode(null);
		user.setBestsellerDocumentSortMode(new Integer(rs.getInt("B_DOCSORTMODE")));
		if (rs.wasNull()) user.setBestsellerDocumentSortMode(null);
		user.setLatestAdditionsDocumentSortMode(new Integer(rs.getInt("L_DOCSORTMODE")));
		if (rs.wasNull()) user.setLatestAdditionsDocumentSortMode(null);
		user.setCategoryViewDocumentSortMode(new Integer(rs.getInt("C_DOCSORTMODE")));
		if (rs.wasNull()) user.setCategoryViewDocumentSortMode(null);
		user.setPwdMismatchCount(new Integer(rs.getInt("PWDMISMATCHCOUNT")));
		if (rs.wasNull()) user.setPwdMismatchCount(null);

		user.setAllowManageSystemViewType(rs.getInt("ALLOWMANAGE_S_V_T") != 0);
        
        user.setEnableHtmlNotification(rs.getInt("ENABLEHTMLNOTIFICATION") != 0);

		user.setSystemViewTypeId(new Integer(rs.getInt("SYSTEMVIEWTYPEID")));
	}

    /**
     * Method creates a userInfo from the current cursor in a sql result set
     */
    private final static void setUserInfoFields(UserInfo userInfo, ResultSet rs) throws SQLException {
        userInfo.setCreatorId(new Integer(rs.getInt("CREATORID")));
		if (rs.wasNull()) userInfo.setCreatorId(null);
		userInfo.setCreated(rs.getTimestamp("CREATED"));
		if (rs.wasNull()) userInfo.setCreated(null);
        userInfo.setEmail(rs.getString("EMAIL"));
        userInfo.setUsername(rs.getString("USERNAME"));
        userInfo.setFirstname(rs.getString("FIRSTNAME"));
        userInfo.setLastname(rs.getString("LASTNAME"));
        userInfo.setUserId(new Integer(rs.getInt("USERID")));
        userInfo.setQuota(new Integer(rs.getInt("QUOTA")));
        userInfo.setMessagingInterval(new Integer(rs.getInt("MESSAGINGTIMEPERIOD")));
        userInfo.setPwdChanged(rs.getInt("PASSWDCHANGED") != 0);
        userInfo.setAccountValid(rs.getInt("ACCOUNTVALID") != 0);
		userInfo.setAdminPrivileges(rs.getInt("ADMINPRIVILEGES") != 0);
		userInfo.setManageOwnGroups(rs.getInt("MANAGEOWNGROUPS") != 0);
		userInfo.setManageOwnUsers(rs.getInt("MANAGEOWNUSERS") != 0);
		userInfo.setManageOwnProxies(rs.getInt("MANAGEOWNPROXIES") != 0);
        userInfo.setMessagingEnabled(rs.getInt("ENABLEMESSAGING") != 0);
		userInfo.setMostRecentLogin(rs.getTimestamp("MOSTRECENTLOGIN"));
		if (rs.wasNull()) userInfo.setMostRecentLogin(null);
		userInfo.setMostRecentNews(rs.getTimestamp("MOSTRECENTNEWS"));
		if (rs.wasNull()) userInfo.setMostRecentNews(null);
		userInfo.setValidFrom(rs.getTimestamp("VALIDFROM"));
		if (rs.wasNull()) userInfo.setValidFrom(null);
		userInfo.setValidUntil(rs.getTimestamp("VALIDUNTIL"));
		if (rs.wasNull()) userInfo.setValidUntil(null);
		userInfo.setExternalServerId(new Integer(rs.getInt("EXTERNALSERVERID")));
		if (rs.wasNull()) userInfo.setExternalServerId(null);
		userInfo.setPwdMismatchCount(new Integer(rs.getInt("PWDMISMATCHCOUNT")));
		if (rs.wasNull()) userInfo.setPwdMismatchCount(null);
    }

    /**
     * Method returns a list of matching UserInfo object for a given Category
     * and a String denoting permission tokens
     */
    public static final List retrieveUserInfos(VKCCategory category, String permTokens) throws DBException {
        HashSet userIds = new HashSet();
        VKCAcl acl = category.getAcl();

        Connection conn = null;

        try {
            conn = WebShare.database.getConnection();

            for (Enumeration e = acl.getAclEntries().elements(); e.hasMoreElements();) {
                VKCAclEntry aclEntry = (VKCAclEntry)e.nextElement();
                
                if (aclEntry.getAclType().getAclTypeId().intValue() == VKCAclType.GROUP_TYPE) {
                    if (permTokens.indexOf(aclEntry.getPermission().getToken()) >= 0) {
                        userIds.addAll(VKCGroupSerializer.getGroupUserIds(aclEntry.getAccessorId(), conn));
                    }
                } else if (aclEntry.getAclType().getAclTypeId().intValue() == VKCAclType.USER_TYPE) {
                    if (permTokens.indexOf(aclEntry.getPermission().getToken()) >= 0) {
                        userIds.add(aclEntry.getAccessorId());
                    }
                } else if (aclEntry.getAclType().getAclTypeId().intValue() == VKCAclType.ANYOTHER_TYPE) {
                    if (permTokens.indexOf(aclEntry.getPermission().getToken()) >= 0) {
                        userIds.addAll(VKCUserSerializer.getAllUserIds());
                    }
                }
            }

            List userInfos = new ArrayList();
            
            for (Iterator iter = userIds.iterator(); iter.hasNext();) {
                Integer userId = (Integer)iter.next();
                UserInfo info =  retrieveUserInfo(userId, conn);
                
                if (info != null) {
                    userInfos.add(info);
                }
            }
            
            return userInfos;
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(conn);
        }
    }

    /**
     * Prüft ob ein Benutzerkonto bereits existiert
     *
     * @param username Benutzername
     * @return true falls Benutzer bereits existiert, false sonst
     */

    public static final boolean isUserExistent(String username) throws DBException {
        Connection conn = null;
        PreparedStatement pStmt = null;
        ResultSet rs = null;

        try {
            conn = WebShare.database.getConnection();
            pStmt = conn.prepareStatement("SELECT * FROM users WHERE username = ?");
            pStmt.setString(1, username);
            rs = pStmt.executeQuery();
            
            return rs.next();            
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(rs);
            DBHelper.close(pStmt);
            DBHelper.close(conn);
        }
    }
    
	public static final void saveMostRecentLogin(Integer userId) throws DBException {
		Connection conn = null;
		PreparedStatement pStmt = null;

		try {
			conn = WebShare.database.getConnection();
			pStmt = conn.prepareStatement("UPDATE users SET mostRecentLogin = ? WHERE userid = ?");
			pStmt.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
			pStmt.setInt(2, userId.intValue());
			pStmt.execute();
            conn.commit();
		} catch (Exception e) {
			DBHelper.rollBack(conn);
			throw new DBException(e);
		} finally {
			DBHelper.close(pStmt);
			DBHelper.close(conn);
		}
	}

    
	public static final void saveMostRecentLogout(Integer userId) throws DBException {
		Connection conn = null;
		PreparedStatement pStmt = null;

		try {
			conn = WebShare.database.getConnection();
			pStmt = conn.prepareStatement("UPDATE users SET mostRecentLogout = ? WHERE userid = ?");
			pStmt.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
			pStmt.setInt(2, userId.intValue());
			pStmt.execute();
            conn.commit();
		} catch (Exception e) {
			DBHelper.rollBack(conn);
			throw new DBException(e);
		} finally {
			DBHelper.close(pStmt);
			DBHelper.close(conn);
		}
	}

    public static final void changeUsersOwner(Integer oldOwnerId, Integer newOwnerId, Integer changerId) throws DBException {
        Connection conn = null;

        try {
            conn = WebShare.database.getConnection();
            changeUsersOwner(oldOwnerId, newOwnerId, changerId, conn);
            conn.commit();
        } catch (Exception e) {
            DBHelper.rollBack(conn);
            throw new DBException(e);
        } finally {
            DBHelper.close(conn);
        }
    }
    
    public static final void changeUsersOwner(Integer oldOwnerId, Integer newOwnerId, Integer changerId, Connection conn) throws DBException {
        PreparedStatement pStmt = null;

        try {
            pStmt = conn.prepareStatement("UPDATE users SET creatorid = ?, lastchanged = ?, lastchangerid = ? WHERE creatorid = ?");
            pStmt.setInt(1, newOwnerId.intValue());  // new user owner
            pStmt.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
            pStmt.setInt(3, changerId.intValue());
            pStmt.setInt(4, oldOwnerId.intValue());  // old user owner
            pStmt.execute();
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(pStmt);
        }
    }
    
	private final static void executeUserStatement(String stmt, VKCUser user, Connection conn) throws SQLException {
		PreparedStatement pStmt = null; 

		try {
			pStmt = conn.prepareStatement(stmt);
            int index = 1;

			pStmt.setInt(index++, user.getAccountValid() ? 1 : 0);
			pStmt.setInt(index++, user.getAdminPrivileges() ? 1 : 0);
			pStmt.setInt(index++, user.getManageOwnGroups() ? 1 : 0);
			pStmt.setInt(index++, user.getManageOwnUsers() ? 1 : 0);
			pStmt.setInt(index++, user.isManageOwnUsersSystemViews() ? 1 : 0);
			pStmt.setInt(index++, user.getManageOwnProxies() ? 1 : 0);
			pStmt.setInt(index++, user.isManageVirtualDocumentQueries() ? 1 : 0);
			pStmt.setInt(index++, user.getMessagingEnabled() ? 1 : 0);
			pStmt.setInt(index++, user.isPasswordChanged() ? 1 : 0);
			pStmt.setInt(index++, user.getShowEmail() ? 1 : 0);
			pStmt.setInt(index++, user.getShowTree() ? 1 : 0);
                    
			if (user.getCreatorId() == null) {
				pStmt.setNull(index++, Types.INTEGER);
			} else {
				pStmt.setInt(index++, user.getCreatorId().intValue());
			}
    
			if (user.getDynaAttrsId() == null) {
				pStmt.setNull(index++, Types.INTEGER);
			} else {
				pStmt.setInt(index++, user.getDynaAttrsId().intValue());
			}
    
			if (user.getHomeCategoryId() == null) {
				pStmt.setNull(index++, Types.INTEGER);
			} else {
				pStmt.setInt(index++, user.getHomeCategoryId().intValue());
			}
    
			if (user.getLanguageId() == null) {
				pStmt.setNull(index++, Types.INTEGER);
			} else {
				pStmt.setInt(index++, user.getLanguageId().intValue());
			}
    
			if (user.getLastChangerId() == null) {
				pStmt.setNull(index++, Types.INTEGER);
			} else {
				pStmt.setInt(index++, user.getLastChangerId().intValue());
			}
    
			if (user.getMessagingTimePeriod() == null) {
				pStmt.setNull(index++, Types.INTEGER);
			} else {
				pStmt.setInt(index++, user.getMessagingTimePeriod().intValue());
			}
    
			if (user.getQuota() == null) {
				pStmt.setNull(index++, Types.INTEGER);
			} else {
				pStmt.setInt(index++, user.getQuota().intValue());
			}
    
			if (user.getSessionTimeout() == null) {
				pStmt.setNull(index++, Types.INTEGER);
			} else {
				pStmt.setInt(index++, user.getSessionTimeout().intValue());
			}
    
			if (user.getTemplateId() == null) {
				pStmt.setNull(index++, Types.INTEGER);
			} else {
				pStmt.setInt(index++, user.getTemplateId().intValue());
			}
    
			pStmt.setString(index++, user.getCity());
			pStmt.setString(index++, user.getCompany());
			pStmt.setString(index++, user.getContactURL());
			pStmt.setString(index++, user.getDepartment());
			pStmt.setString(index++, user.getEmail());
			pStmt.setString(index++, user.getFax());
			pStmt.setString(index++, user.getFirstname());
			pStmt.setString(index++, user.getHomepageURL());
			pStmt.setString(index++, user.getLastname());
			pStmt.setString(index++, user.getMobile());
			pStmt.setString(index++, user.getPassword());
			pStmt.setString(index++, user.getPhone());
			pStmt.setString(index++, user.getTitle());
			pStmt.setString(index++, user.getStreet());
			pStmt.setString(index++, user.getUser());
			pStmt.setString(index++, user.getZip());

			if (user.getCreated() == null) {
				pStmt.setNull(index++, Types.TIMESTAMP);
			} else {
				pStmt.setTimestamp(index++, user.getCreated());
			}
    
			if (user.getLastChanged() == null) {
				pStmt.setNull(index++, Types.TIMESTAMP);
			} else {
				pStmt.setTimestamp(index++, user.getLastChanged());
			}
    
			if (user.getMostRecentLogin() == null) {
				pStmt.setNull(index++, Types.TIMESTAMP);
			} else {
				pStmt.setTimestamp(index++, user.getMostRecentLogin());
			}

			if (user.getMostRecentLogout() == null) {
				pStmt.setNull(index++, Types.TIMESTAMP);
			} else {
				pStmt.setTimestamp(index++, user.getMostRecentLogout());
			}
			
			if (user.getMostRecentNews() == null) {
				pStmt.setNull(index++, Types.TIMESTAMP);
			} else {
				pStmt.setTimestamp(index++, user.getMostRecentNews());
			}

			if (user.getPasswordChangedTs() == null) {
				pStmt.setNull(index++, Types.TIMESTAMP);
			} else {
				pStmt.setTimestamp(index++, user.getPasswordChangedTs());
			}
    
			if (user.getValidFrom() == null) {
				pStmt.setNull(index++, Types.TIMESTAMP);
			} else {
				pStmt.setTimestamp(index++, user.getValidFrom());
			}
    
			if (user.getValidUntil() == null) {
				pStmt.setNull(index++, Types.TIMESTAMP);
			} else {
				pStmt.setTimestamp(index++, user.getValidUntil());
			}

			if (user.getExternalServerId() == null) {
				pStmt.setNull(index++, Types.INTEGER);
			} else {
				pStmt.setInt(index++, user.getExternalServerId().intValue());
			}

			pStmt.setString(index++, user.getExternalServerType());
			pStmt.setString(index++, user.getExternalUserId());
			
			if (user.getSearchDocumentSortMode() == null) {
				pStmt.setNull(index++, Types.INTEGER);
			} else {
				pStmt.setInt(index++, user.getSearchDocumentSortMode().intValue());
			}

			if (user.getBestsellerDocumentSortMode() == null) {
				pStmt.setNull(index++, Types.INTEGER);
			} else {
				pStmt.setInt(index++, user.getBestsellerDocumentSortMode().intValue());
			}

			if (user.getLatestAdditionsDocumentSortMode() == null) {
				pStmt.setNull(index++, Types.INTEGER);
			} else {
				pStmt.setInt(index++, user.getLatestAdditionsDocumentSortMode().intValue());
			}

			if (user.getCategoryViewDocumentSortMode() == null) {
				pStmt.setNull(index++, Types.INTEGER);
			} else {
				pStmt.setInt(index++, user.getCategoryViewDocumentSortMode().intValue());
			}

			if (user.getPwdMismatchCount() == null) {
				pStmt.setNull(index++, Types.INTEGER);
			} else {
				pStmt.setInt(index++, user.getPwdMismatchCount().intValue());
			}

			pStmt.setInt(index++, user.isAllowManageSystemViewType() ? 1 : 0);

			if (user.getSystemViewTypeId() == null) {
				pStmt.setNull(index++, Types.INTEGER);
			} else {
				pStmt.setInt(index++, user.getSystemViewTypeId().intValue());
			}			
  
            pStmt.setInt(index++, user.isEnableHtmlNotification() ? 1 : 0);
            
			pStmt.setInt(index++, user.getUserId().intValue());

			pStmt.execute();
		} finally {
			DBHelper.close(pStmt);
		}
	}
	
	public static final Integer retrieveUserIdFromExternalUserId(Integer externalServerId, String externalUserId) throws DBException {
		Connection conn = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		Integer userId = null;

		if (externalServerId == null || externalUserId == null) {
		    return null;
		}
		
		try {
			conn = WebShare.database.getConnection();
			pStmt = conn.prepareStatement("SELECT userId FROM users WHERE externalServerId = ? AND externalUserId = ?");
			pStmt.setInt(1, externalServerId.intValue());
			pStmt.setString(2, externalUserId);
		    rs = pStmt.executeQuery();

			if (rs.next()) {
				userId = new Integer(rs.getInt("userId")); 
			}

			return userId;
		} catch (Exception e) {
			throw new DBException(e);
		} finally {
			DBHelper.close(rs);
			DBHelper.close(pStmt);
			DBHelper.close(conn);
		}
	}

	public static final void disableExternalUsers(Integer externalServerId) throws DBException {
		Connection conn = null;
		PreparedStatement pStmt = null;	

		if (externalServerId == null) {
		    return;
		}
		
		try {
			conn = WebShare.database.getConnection();
			pStmt = conn.prepareStatement("UPDATE users SET accountValid = 0 WHERE externalServerId = ?");
			pStmt.setInt(1, externalServerId.intValue());
			pStmt.execute();
			conn.commit();
		} catch (Exception e) {
			DBHelper.rollBack(conn);
			throw new DBException(e);
		} finally {
			DBHelper.close(pStmt);
			DBHelper.close(conn);
		}
	}
	
	public static final void enableExternalUsers(Integer externalServerId) throws DBException {
		Connection conn = null;
		PreparedStatement pStmt = null;	

		if (externalServerId == null) {
		    return;
		}
		
		try {
			conn = WebShare.database.getConnection();
			pStmt = conn.prepareStatement("UPDATE users SET accountValid = 1 WHERE externalServerId = ?");
			pStmt.setInt(1, externalServerId.intValue());
			pStmt.execute();
			conn.commit();
		} catch (Exception e) {
			DBHelper.rollBack(conn);
			throw new DBException(e);
		} finally {
			DBHelper.close(pStmt);
			DBHelper.close(conn);
		}
	}

	public static final void disableUser(Integer userId) throws DBException {
		Connection conn = null;
		PreparedStatement pStmt = null;	

		if (userId == null) {
		    return;
		}
		
		try {
			conn = WebShare.database.getConnection();
			pStmt = conn.prepareStatement("UPDATE users SET accountValid = 0 WHERE userId = ?");
			pStmt.setInt(1, userId.intValue());
			pStmt.execute();
			conn.commit();
		} catch (Exception e) {
			DBHelper.rollBack(conn);
			throw new DBException(e);
		} finally {
			DBHelper.close(pStmt);
			DBHelper.close(conn);
		}
	}

	public static final void setUserValidFrom(Integer userId, Timestamp validFrom) throws DBException {
		Connection conn = null;
		PreparedStatement pStmt = null;	

		if (userId == null) {
		    return;
		}
		
		try {
			conn = WebShare.database.getConnection();
			pStmt = conn.prepareStatement("UPDATE users SET validFrom = ? WHERE userId = ?");
			
			if (validFrom != null) {
				pStmt.setTimestamp(1, validFrom);
			} else {
				pStmt.setNull(1, Types.TIMESTAMP);
			}

			pStmt.setInt(2, userId.intValue());
			pStmt.execute();
			conn.commit();
		} catch (Exception e) {
			DBHelper.rollBack(conn);
			throw new DBException(e);
		} finally {
			DBHelper.close(pStmt);
			DBHelper.close(conn);
		}
	}

	public static final void setUserPwdMismatchCount(Integer userId, Integer count) throws DBException {
		Connection conn = null;
		PreparedStatement pStmt = null;	

		if (userId == null || count == null || count.intValue() < 0) {
		    return;
		}
		
		try {
			conn = WebShare.database.getConnection();
			pStmt = conn.prepareStatement("UPDATE users SET pwdMismatchCount = ? WHERE userId = ?");
			
			pStmt.setInt(1, count.intValue());
			pStmt.setInt(2, userId.intValue());
			pStmt.execute();
			conn.commit();
		} catch (Exception e) {
			DBHelper.rollBack(conn);
			throw new DBException(e);
		} finally {
			DBHelper.close(pStmt);
			DBHelper.close(conn);
		}
	}
	
	/**
     * Speichert einen VKCUser in der Datenbank ab
     *
     * @param users das zu speichernde VKCUser Objekt
     */
    public static final void importUser(List users) throws DBException {
        if (users == null) {
        	return;
        }
        
        Connection conn = null;

        try {
            conn = WebShare.database.getConnection();
			importUser(users, conn);
            conn.commit();
        } catch (Exception e) {
            DBHelper.rollBack(conn);
            VKCLogManager.errorLog(VKCUserSerializer.class, "1", "Error saving user: " + users, e);
            throw new DBException(e);
        } finally {
            DBHelper.close(conn);
        }
    }

	/**
	 * Speichert einen VKCUser in der Datenbank ab
	 *
	 * @param users das zu speichernde VKCUser Objekt
	 */
	public static final void importUser(List users, Connection conn) throws DBException {
		if (users == null) {
			return;
		}
	
		try {
			String stmt = "INSERT INTO USERS ( ";
			stmt += "ACCOUNTVALID, ";
			stmt += "ADMINPRIVILEGES, ";
			stmt += "MANAGEOWNGROUPS, ";
			stmt += "MANAGEOWNUSERS, ";
			stmt += "MANAGEOWNUSERS_S_V, ";
			stmt += "MANAGEOWNPROXIES, ";
			stmt += "MANAGEVIRTUALDOC_Q, ";
			stmt += "ENABLEMESSAGING, ";
			stmt += "PASSWDCHANGED, ";
			stmt += "SHOWEMAIL, ";
			stmt += "SHOWTREE, ";
			stmt += "CREATORID, ";
			stmt += "DYNATTRSID, ";
			stmt += "STARTCATEGORYID, ";
			stmt += "LANGUAGEID, ";
			stmt += "LASTCHANGERID, ";
			stmt += "MESSAGINGTIMEPERIOD, ";
			stmt += "QUOTA, ";				
			stmt += "SESSIONTIMEOUT, ";
			stmt += "TEMPLATEID, ";
			stmt += "CITY, ";
			stmt += "COMPANY, ";
			stmt += "CONTACTURL, ";				
			stmt += "DEPARTMENT, ";
			stmt += "EMAIL, ";
			stmt += "FAX, ";
			stmt += "FIRSTNAME, ";
			stmt += "HOMEPAGEURL, ";				
			stmt += "LASTNAME, ";
			stmt += "MOBILE, ";
			stmt += "PASSWD, ";
			stmt += "PHONE, ";
			stmt += "TITLE, ";		
			stmt += "STREET, ";
			stmt += "USERNAME, ";
			stmt += "ZIP, ";
			stmt += "CREATED, ";				
			stmt += "LASTCHANGED, ";
			stmt += "MOSTRECENTLOGIN, ";
			stmt += "MOSTRECENTLOGOUT, ";
			stmt += "MOSTRECENTNEWS, ";
			stmt += "PASSWDCHANGEDTS, ";
			stmt += "VALIDFROM, ";
			stmt += "VALIDUNTIL, ";
			stmt += "EXTERNALSERVERID, ";
			stmt += "EXTERNALSERVERTYPE, ";
			stmt += "EXTERNALUSERID, ";
			stmt += "S_DOCSORTMODE, ";
			stmt += "B_DOCSORTMODE, ";
			stmt += "L_DOCSORTMODE, ";
			stmt += "C_DOCSORTMODE, ";
			stmt += "PWDMISMATCHCOUNT, ";
			stmt += "ALLOWMANAGE_S_V_T, ";
			stmt += "SYSTEMVIEWTYPEID, ";
            stmt += "ENABLEHTMLNOTIFICATION, ";
			stmt += "USERID ) ";				
			stmt += "VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?," +
							" ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?," +
							" ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) ";
			Iterator iterUsers = users.iterator();
			while(iterUsers.hasNext()){
					VKCUser user = (VKCUser) iterUsers.next();
				if(isUserExistent(user.getUser())){
					saveUser(user, conn);
				}else{
					executeUserStatement(stmt, user, conn);			
					VKCDynUserAttributeSerializer.saveDynUserAttributes(conn,(List)user.getDynaAttributes(), user.getUserId());
					// Assozierte Gruppen speichern
					VKCGroupSerializer.saveUserGroups(user, conn);
				}
			}
		} catch (Exception e) {
			VKCLogManager.errorLog(VKCUserSerializer.class, "1", "Error saving user: " + users, e);
			throw new DBException(e);
		}
	}
}