/*
 * $Id: VKCGroupSerializer.java,v 1.38 2007/10/15 06:29:44 marbobo Exp $
 *
 * Copyright (c) Siemens AG 2008 
 *           (c) Universität Paderborn 2008 
 * Licensed under EUPL 1.1. 
 *
 * $Log: VKCGroupSerializer.java,v $
 * Revision 1.38  2007/10/15 06:29:44  marbobo
 * + Ded. Admin-Rechte kopieren umgeschrieben
 *
 * Revision 1.37  2007/08/28 12:15:51  korowa
 * Export- und Importfunktionen hinzugefügt
 *
 * Revision 1.36  2006/07/11 23:40:03  marbobo
 * + PostgreSQL ist pingelig und erlaubt kein get/setBoolean auf Int-Spalten
 *
 * Revision 1.35  2006/05/28 18:10:53  marbobo
 * + Benutzer- und Gruppensuche nach Umlauten nochmals verbessert
 *
 * Revision 1.34  2006/02/27 16:15:42  marbobo
 * + Systemsichten hinzugefügt
 * + SysLinks angepasst
 * + Benutzer können falls gewünscht Dokumentqueries anlegen
 *
 * Revision 1.33  2005/12/01 12:55:04  marbobo
 * + kleine Anpassungen
 *
 * Revision 1.32  2005/02/20 17:56:13  marbobo
 * + Bugfix (falls keine Gruppen Einträge dann alle alte löschen)
 *
 * Revision 1.31  2004/12/16 14:13:36  marbobo
 * + Weitere Umstellungen und Optimierungen bzgl. PreparedStatements
 *
 * Revision 1.30  2004/12/11 18:33:58  marbobo
 * + Restliche Statements auf PreparedStatement umgestellt
 *
 * Revision 1.29  2004/12/09 21:07:39  marbobo
 * + kleine Optimierungen und Bugfixes
 * +an einigen Stellen Rreihenfolge der close-Anweisungen vereinheitlicht
 *
 * Revision 1.28  2004/12/09 12:39:27  lawern
 * vergessene PreparedStatements geschlossen
 *
 * Revision 1.27  2004/12/05 22:57:46  marbobo
 * + Statement auf PreparedStatement umgestellt
 *
 * Revision 1.26  2004/11/24 13:01:11  lawern
 * Global "conn.setAutoCommit( false );" enfernt, weil überflüssig.
 *
 * Revision 1.25  2004/11/24 09:25:56  lawern
 * Hibernate ist VKC-Geschichte!
 *
 * Revision 1.24  2004/11/18 19:38:23  marbobo
 * + changed order of close-stmt to resultset/connect/session  in finally-blocks
 *
 * Revision 1.23  2004/11/18 16:05:24  lawern
 * changed order of close-stmt to resultset/connect/session  in finally-blocks
 *
 * Revision 1.22  2004/11/13 20:44:22  marbobo
 * + Bugfix bei der Gruppensuche ( Betrifft die Literal Behandlung )
 *
 * Revision 1.21  2004/11/12 00:25:28  marbobo
 * + Einige neue Methoden implementiert
 *
 * Revision 1.20  2004/10/24 18:18:58  marbobo
 * + getAllValidGroupIds()
 *
 * Revision 1.19  2004/10/22 15:40:06  jonyp
 * *** empty log message ***
 *
 * Revision 1.18  2004/10/22 11:03:58  marbobo
 * + Parametername bei saveUserGroups() korrigiert
 *
 * Revision 1.17  2004/10/22 07:50:20  jonyp
 * added code to prevent saving groups when there are none
 *
 * Revision 1.16  2004/10/21 23:49:17  marbobo
 * + CURRENT_TIMESTAMP ersetzt mit java-Anweisung
 *
 * Revision 1.15  2004/08/20 12:43:28  koppy
 * retrieveGroup(..) kann entweder mit "namen" oder "gruppenid"
 * aufgerufen werden
 *
 * Revision 1.14  2004/07/30 11:16:36  marbobo
 * - Nicht verwendete Variablen entfernt
 *
 * Revision 1.13  2004/07/26 11:33:37  jonyp
 * added method getCountMembers()
 *
 * Revision 1.12  2004/07/20 08:23:20  jonyp
 * added copyright info + cvs log
 *
 *
 */

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.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.VKCAclType;
import vkc.db.entities.VKCGroup;
import vkc.db.entities.VKCUser;
import vkc.db.helper.DBHelper;
import vkc.exception.DBException;
import vkc.misc.VKCLogManager;
import vkc.view.WebShare;
import vkc.view.bean.GroupInfo;

public class VKCGroupSerializer {
    private VKCGroupSerializer() {
    }

    public static final int getAllGroupsCount() throws DBException {
	    return getGroupsCount("", null);
    }
    
    public static final int getAllGroupsCountByCreator(Integer creatorId) throws DBException {
        if (creatorId == null) {
            return 0;
        }

	    return getGroupsCount("WHERE creatorId = ?", new Object[]{creatorId});
    }
    
    private static final int getGroupsCount(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 groupscount FROM GROUPS " + whereClause);
            DBHelper.setElements(pStmt, params);
            rs = pStmt.executeQuery();

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

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

	public final static VKCGroup retrieveGroup(Object objectId, Connection conn) throws DBException {
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		
		try {
			if (objectId == null) {
				return null;
			}
			
			if (objectId instanceof Integer) {
			    if (((Integer)objectId).intValue() <= 0) {
			        return null;
			    }
			    
				pStmt = conn.prepareStatement("SELECT * FROM GROUPS WHERE GROUPID = ?");
				pStmt.setInt(1, ((Integer)objectId).intValue());
			} else {
				pStmt = conn.prepareStatement("SELECT * FROM GROUPS WHERE NAME = ?");
				pStmt.setString(1, (String)objectId);
			}
			
			rs = pStmt.executeQuery();
			
			if (rs.next()) {
				VKCGroup group = new VKCGroup();
				setGroupFields(group, rs);
				
				return group;
			} 

			return null;
		} catch (Exception ex) {
			throw new DBException(ex);
		} finally {
			DBHelper.close(rs);
			DBHelper.close(pStmt);
		}
	}
	
	public final static VKCGroup retrieveGroup(Object objectId) throws DBException {
		Connection conn = null;

		try {
			conn = WebShare.database.getConnection();
			
			return retrieveGroup(objectId, conn);
		} catch (Exception ex) {
			throw new DBException(ex);
		} finally {
			DBHelper.close(conn);
		}
	}

    /**
     * Lädt alle verfügbaren GroupInfo aus der Datenbank und gibt sie als
     * List zurück
     *
     * <p></p>
     *
     * @return List von GroupInfo-Objekten
     */
    public final static List retrieveGroupInfos() throws DBException {
        return retrieveGroupInfos("", null);
    }

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

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

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

        return retrieveGroupInfos("WHERE " + condition, params);
    }

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

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

        return retrieveGroupInfos("WHERE creatorId = ? AND " + condition, params);
    }

    private static final List retrieveGroupInfos(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 groups " + whereClause);
            DBHelper.setElements(pStmt, params);
            rs = pStmt.executeQuery();

            List list = new ArrayList();

            while (rs.next()) {
                GroupInfo info = new GroupInfo();
                setGroupInfoFields(info, rs);
				info.setCountMembers(VKCGroupSerializer.retrieveCountMembers(info.getGroupId(), null, conn));
                list.add(info);
            }

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

	private final static void setGroupFields(VKCGroup group, ResultSet rs) throws SQLException {
		group.setGroupId(new Integer(rs.getInt("groupid")));
		group.setName(rs.getString("name"));
		group.setDescription(rs.getString("description"));
		group.setValidFrom(rs.getTimestamp("validFrom"));
		group.setValidUntil(rs.getTimestamp("validUntil"));
		group.setLastChanged( rs.getTimestamp("lastChanged"));
		group.setCreated(rs.getTimestamp("created"));

		group.setAccountValid(new Boolean(rs.getInt("accountValid") != 0));
		
		if (rs.wasNull()) {
			group.setAccountValid(null);
		}

		group.setAdminPrivileges(new Boolean(rs.getInt("adminPrivileges") != 0));
		
		if (rs.wasNull()) {
			group.setAdminPrivileges(null);
		}

		group.setLastChangerId(new Integer(rs.getInt("lastChangerId")));
		
		if (rs.wasNull()) {
			group.setLastChangerId(null);
		}
		
		group.setCreatorId(new Integer(rs.getInt("creatorId")));
		
		if (rs.wasNull()) {
			group.setCreatorId(null);
		}
	}

	private final static void setGroupInfoFields(GroupInfo groupInfo, ResultSet rs) throws SQLException {
        groupInfo.setGroupId(new Integer(rs.getInt("groupid")));
        groupInfo.setName(rs.getString("name"));
        groupInfo.setDescription(rs.getString("description"));
        groupInfo.setAdminPrivileges(rs.getInt("adminprivileges") != 0);
        groupInfo.setAccountValid(rs.getInt("accountvalid") != 0);
        groupInfo.setValidFrom(rs.getTimestamp("validFrom"));
        groupInfo.setValidUntil(rs.getTimestamp("validUntil"));
        groupInfo.setCreatorId(new Integer(rs.getInt("creatorid")));
        groupInfo.setCreated(rs.getTimestamp("created"));
	}

	/**
	 * Speichert eine Gruppe in der DB ab Falls schon vorhanden, wird sie
	 * aktualisiert, sonst neu inseriert
	 *
	 * @param conn
	 * @param group die Gruppe
	 */
	public static void saveGroup(VKCGroup group) throws DBException {
		Connection conn = null;

		try {
			conn = WebShare.database.getConnection();
			saveGroup(group, conn);
			conn.commit();
		} catch (Exception ex) {
			DBHelper.rollBack(conn);
			throw new DBException(ex);
		} finally {
			DBHelper.close(conn);
		}
	}

	/**
	 * Speichert eine Gruppe in der DB ab Falls schon vorhanden, wird sie
	 * aktualisiert, sonst neu inseriert
	 *
	 * @param conn
	 * @param group die Gruppe
	 */
	public static void saveGroup(VKCGroup group, Connection conn) throws DBException {
		try {
			String stmt = null;

			if (group.getGroupId() == null || group.getGroupId().intValue() <= 0) {
				group.setGroupId(DBHelper.getNewId(conn, "Groups", "groupId"));
	
				stmt  = "INSERT INTO GROUPS ( ";
				stmt += "NAME, ";
				stmt += "DESCRIPTION, ";
				stmt += "ACCOUNTVALID, ";
				stmt += "ADMINPRIVILEGES, ";
				stmt += "VALIDFROM, ";
				stmt += "VALIDUNTIL, ";
				stmt += "CREATED, ";
				stmt += "CREATORID, ";
				stmt += "LASTCHANGED, ";
				stmt += "LASTCHANGERID, ";
				stmt += "GROUPID )";
				stmt += " values ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) ";
			} else {
				stmt  = "UPDATE GROUPS SET ";
				stmt += "NAME = ?, ";
				stmt += "DESCRIPTION = ?, ";
				stmt += "ACCOUNTVALID = ?, ";
				stmt += "ADMINPRIVILEGES = ?, ";
				stmt += "VALIDFROM = ?, ";
				stmt += "VALIDUNTIL = ?, ";
				stmt += "CREATED = ?, ";
				stmt += "CREATORID = ?, ";
				stmt += "LASTCHANGED = ?, ";
				stmt += "LASTCHANGERID = ? ";
				stmt += "WHERE GROUPID = ?";
			}

			executeGroupStatement(stmt, group, conn);
		} catch (Exception ex) {
			throw new DBException(ex);
		}
	}

	private final static void executeGroupStatement(String stmt, VKCGroup group, Connection conn) throws SQLException {
		PreparedStatement pStmt = null; 

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

			pStmt.setString(index++, group.getName());
			pStmt.setString(index++, group.getDescription());
			pStmt.setInt(index++, group.getAccountValid().booleanValue() ? 1 : 0);
			pStmt.setInt(index++, group.getAdminPrivileges().booleanValue() ? 1 : 0);
			
			if (group.getValidFrom() == null) {
				pStmt.setNull(index++, Types.TIMESTAMP);
			} else {
				pStmt.setTimestamp(index++, new Timestamp(group.getValidFrom().getTime()));
			}

			if (group.getValidUntil() == null) {
				pStmt.setNull(index++, Types.TIMESTAMP);
			} else {
				pStmt.setTimestamp(index++, new Timestamp(group.getValidUntil().getTime()));
			}

			if (group.getCreated() == null) {
				pStmt.setNull(index++, Types.TIMESTAMP);
			} else {
				pStmt.setTimestamp(index++, new Timestamp(group.getCreated().getTime()));
			}

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

			if (group.getLastChanged() == null) {
				pStmt.setNull(index++, Types.TIMESTAMP);
			} else {
				pStmt.setTimestamp(index++, new Timestamp(group.getLastChanged().getTime()));
			}

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

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

			pStmt.execute();
		} finally {
			DBHelper.close(pStmt);
		}
	}

	/**
	 * Speichert die Gruppen eines Users
	 *
	 * @param conn
	 * @param user der User, dessen Gruppen gespeichert werden sollen
	 */
	public static void saveUserGroups(VKCUser user, Connection conn) throws DBException {
		PreparedStatement pStmt = null;
		
		if (user == null) {
			return;
		}

	    if (user.getGroups() == null || user.getGroups().size() == 0) { 
			try {
			    pStmt = conn.prepareStatement("DELETE FROM UserGroups WHERE userId = ?");
			    pStmt.setInt(1, user.getUserId().intValue());
				pStmt.execute();
	
				return;
			} catch(Exception ex) {
				throw new DBException(ex);
			} finally {
				DBHelper.close(pStmt);
			}
	    }
	    
		try {
		    // Erstmal evtl. vorhandene Gruppen aus der Assoziationstabelle löschen
		    pStmt = conn.prepareStatement("DELETE FROM UserGroups WHERE userId = ?");
		    pStmt.setInt(1, user.getUserId().intValue());
			pStmt.execute();
			
			DBHelper.close(pStmt);
			pStmt = conn.prepareStatement("INSERT INTO UserGroups ( groupId, userId, created, creatorId ) VALUES ( ?, ?, ?, ? )");
	        
			for (Iterator it = user.getGroups().iterator(); it.hasNext();) {
	            VKCGroup group = (VKCGroup)it.next();

			    pStmt.setInt(1, group.getGroupId().intValue());
			    pStmt.setInt(2, user.getUserId().intValue());
			    pStmt.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
			    pStmt.setInt(4, user.getLastChangerId().intValue());
				pStmt.execute();
		    }
		} catch(Exception ex) {
			throw new DBException(ex);
		} finally {
			DBHelper.close(pStmt);
		}
	}

	/**
	 * Speichert dei Gruppen eines Users
	 *
	 * @param conn
	 * @param user der User, dessen Gruppen gespeichert werden sollen
	 */
	public static Set retrieveUserGroups(Integer userId, Connection conn) throws DBException {
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		HashSet groups = new HashSet();
		
		try {
			pStmt = conn.prepareStatement("SELECT * FROM UserGroups WHERE userId = ?");
			pStmt.setInt(1, userId.intValue());
			rs = pStmt.executeQuery();
			
			while(rs.next()) {
				Integer groupId = new Integer(rs.getInt("GROUPID"));
				VKCGroup group = retrieveGroup(groupId, conn);
				
				if (group != null) {
					groups.add(group);
				}
			}
			
			return groups;
		} catch(Exception ex) {
			throw new DBException(ex);
		} finally {
			DBHelper.close(rs);
			DBHelper.close(pStmt);
		}
	}

	public static HashSet retrieveUserGroupInfos(Integer userId) throws DBException {
		Connection conn = null;

		try {
			conn = WebShare.database.getConnection();
			
			return retrieveUserGroupInfos(userId, conn);
		} catch (Exception e) {
			VKCLogManager.errorLog(VKCGroupSerializer.class, "1", "Error occured while retrieving user groupInfos!", e);
			throw new DBException(e);
		} finally {
			DBHelper.close(conn);
		}
	}

	public static HashSet retrieveUserGroupInfos(Integer userId, Connection conn) throws DBException {
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		HashSet groupInfos = new HashSet();

		try {
			pStmt = conn.prepareStatement("SELECT * FROM USERGROUPS WHERE userid = ?");
			pStmt.setInt(1, userId.intValue());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				Integer id = new Integer(rs.getInt("groupid"));
				GroupInfo g = retrieveGroupInfo(id, null, conn);
				
				if (g != null) {
					groupInfos.add(g);
				}
			}

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

	/**
	 * Lädt alle verfügbaren Gruppen aus der Datenbank und gibt sie als HashMap
	 * zurück
	 *
	 * @return HashMap von VKCGroup Objekten mit dem Gruppennamen als Schlüssel
	 *
	 * @throws DBException DOCUMENT ME!
	 */
	public final static HashMap getAllGroups() throws DBException {
		Connection conn = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		HashMap groups = new HashMap();

		try {
			conn = WebShare.database.getConnection();
			pStmt = conn.prepareStatement("SELECT * FROM groups");
			rs = pStmt.executeQuery();

			while (rs.next()) {
				VKCGroup group = new VKCGroup();
				setGroupFields(group, rs);
				groups.put(group.getName().trim(), group);
			}

			return groups;
		} catch (Exception e) {
			throw new DBException(e);
		} finally {
			DBHelper.close(rs);
			DBHelper.close(pStmt);
			DBHelper.close(conn);
		}
	}
	
	/**
	 * Lädt alle verfügbaren gültigen Gruppen-Ids aus der Datenbank und gibt sie als Set
	 * zurück
	 *
	 * @return Set ids aller gültigen Gruppen
	 */
	public final static Set getAllValidGroupIds() throws DBException {
		Connection conn = null;

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

			return getAllValidGroupIds(conn);
		} catch (Exception e) {
			throw new DBException(e);
		} finally {
			DBHelper.close(conn);
		}
	}
	
	/**
	 * Lädt alle verfügbaren gültigen Gruppen-Ids aus der Datenbank und gibt sie als Set
	 * zurück
	 *
	 * @return Set ids aller gültigen Gruppen
	 */
	public final static Set getAllValidGroupIds(Connection conn) throws DBException {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Set groupIds = new HashSet();

		try {
			Timestamp time = new Timestamp(System.currentTimeMillis());
		    String stmt = "SELECT groupId FROM GROUPS WHERE accountValid = ?";
		    stmt += " AND ( validFrom <= ? OR validFrom IS NULL )";
		    stmt += " AND ( validUntil >= ? OR validUntil IS NULL )";
			pstmt = conn.prepareStatement(stmt);
			pstmt.setInt(1, 1);
			pstmt.setTimestamp(2, time);
			pstmt.setTimestamp(3, time);
			rs = pstmt.executeQuery();
			
			while (rs.next()) {
			    groupIds.add(new Integer(rs.getInt("groupId")));
			}

			return groupIds;
		} catch (Exception e) {
			throw new DBException(e);
		} finally {
			DBHelper.close(rs);
			DBHelper.close(pstmt);
		}
	}
	
	/**
	 * Löscht eine VKCGroup aus der Datenbank
	 *
	 * @param groupId ID der zu löschenden Gruppe
	 *
	 * @throws DBException DOCUMENT ME!
	 */
	public final static void deleteGroup(Integer groupId) throws DBException {
		Connection conn = null;
		PreparedStatement pStmt = null;

		try {
			conn = WebShare.database.getConnection();
			pStmt = conn.prepareStatement("DELETE FROM CategoryAcls WHERE AclTypeId = " + VKCAclType.GROUP_TYPE + " AND AccessorId = ?");
			pStmt.setInt(1, groupId.intValue());
			pStmt.execute();
			DBHelper.close(pStmt);
			pStmt = conn.prepareStatement("DELETE FROM UserGroups WHERE groupId = ?");
			pStmt.setInt(1, groupId.intValue());
			pStmt.execute();
			DBHelper.close(pStmt);
			pStmt = conn.prepareStatement("DELETE FROM Groups WHERE groupId = ?");
			pStmt.setInt(1, groupId.intValue());
			pStmt.execute();
			conn.commit();
		} catch (Exception e) {
			DBHelper.rollBack(conn);
			throw new DBException(e);
		} finally {
			DBHelper.close(pStmt);
			DBHelper.close(conn);
		}
	}

	/**
	 * Löscht eine Menge von Benutzern aus einer VKCGroup aus der Datenbank
	 *
	 * @param groupId ID der Gruppe
	 * @param userIds IDs der Benutzer die aus dieser Gruppe ausgetragen werden
	 *        sollen
	 */
	public final static void deleteGroupUsers(Integer groupId, Integer[] userIds)	throws DBException {
		Connection conn = null;
		PreparedStatement pStmt = null;

		try {
			conn = WebShare.database.getConnection();
			pStmt = conn.prepareStatement("DELETE FROM UserGroups WHERE groupId = ? AND userId = ?");

			for (int i = 0; i < userIds.length; i++) {
			    pStmt.setInt(1, groupId.intValue());
			    pStmt.setInt(2, userIds[i].intValue());
				pStmt.execute();
			}

			conn.commit();
		} catch (Exception e) {
			DBHelper.rollBack(conn);
			throw new DBException(e);
		} finally {
			DBHelper.close(pStmt);
			DBHelper.close(conn);
		}
	}

	/**
	 * Speichert eine Menge von Benutzer unter einer VKCGroup in der Datenbank
	 * ab
	 *
	 * @param groupId ID der Gruppe
	 * @param userIds IDs der Benutzer die in dieser Gruppe eingetragen werden
	 *        sollen
	 * @param creatorId ID des Benutzers der diese Operation ausführt
	 */
	public final static void saveGroupUsers(Integer groupId, 
											Integer[] userIds,
											Integer creatorId) 
	throws DBException {
		Connection conn = null;
		PreparedStatement pStmt = null;
		PreparedStatement pStmt2 = null;
		ResultSet rs = null;

		try {
			conn = WebShare.database.getConnection();
			
			pStmt = conn.prepareStatement("SELECT * FROM UserGroups WHERE groupId = ? AND userId = ?");
			pStmt2 = conn.prepareStatement("INSERT INTO UserGroups ( groupId, userId, created, creatorId ) VALUES ( ?, ?, ?, ? )");

			for (int i = 0; i < userIds.length; i++ ) {
				pStmt.setInt(1, groupId.intValue());
				pStmt.setInt(2, userIds[i].intValue());
				DBHelper.close(rs);
				rs = pStmt.executeQuery();

				if (!rs.next()) {
					pStmt2.setInt(1, groupId.intValue());
					pStmt2.setInt(2, userIds[i].intValue());
					pStmt2.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
					pStmt2.setInt(4, creatorId.intValue());
				    pStmt2.execute();
				}
			}

			conn.commit();
		} catch (Exception e) {
			DBHelper.rollBack(conn);
			throw new DBException(e);
		} finally {
			DBHelper.close(rs);
			DBHelper.close(pStmt);
			DBHelper.close(pStmt2);
			DBHelper.close(conn);
		}
	}

	/**
	 * Gibt eine Menge(HashSet) aller vorhandenen IDs von Benutzern, die zu
	 * einer VKCGroup gehören
	 *
	 * @param groupId ID der Guppe
	 *
	 * @return Menge aller vorhandenen Benutzer-Ids einer Gruppe
	 */
	public final static Set getGroupUserIds(Integer groupId) throws DBException {
		if (groupId == null) {
		    return new HashSet();
		}
	    
		Connection conn = null;

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

	/**
	 * Gibt eine Menge(HashSet) aller vorhandenen IDs von Benutzern, die zu
	 * einer VKCGroup gehören
	 *
	 * @param groupId ID der Guppe
	 *
	 * @return Menge aller vorhandenen Benutzer-Ids einer Gruppe
	 */
	public final static Set getGroupUserIds(Integer groupId, Connection conn) throws DBException {
		if (groupId == null) {
		    return new HashSet();
		}
	    
	    PreparedStatement pStmt = null;
		ResultSet rs = null;
		Set userIds = new HashSet();

		try {
			pStmt = conn.prepareStatement("SELECT userId FROM UserGroups WHERE groupId = ?");
			pStmt.setInt(1, groupId.intValue());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				userIds.add(new Integer(rs.getInt("userId")));
			}

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

	/**
	 * Prüft ob die Gruppe bereits existiert
	 *
	 * @param groupname Gruppenname
	 * @return true falls Benutzer bereits existiert, false sonst
	 */
	public final static boolean isGroupExistent(String groupname) throws DBException {
		Connection conn = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		try {
			conn = WebShare.database.getConnection();
			pStmt = conn.prepareStatement("SELECT * FROM groups WHERE name = ?");
			pStmt.setString(1, groupname);
			rs = pStmt.executeQuery();
			
			return rs.next();
		} catch (Exception e) {
			throw new DBException(e);
		} finally {
			DBHelper.close(rs);
			DBHelper.close(pStmt);
			DBHelper.close(conn);
		}
	}

    /**
     * Lädt eine GroupInfo-Bean aus der Datenbank
     *
     * @param groupId ID des zu ladenden GroupInfo-Beans
     *
     * @return das gewünschte GroupInfo-Bean Objekt
     */
    public static final GroupInfo retrieveGroupInfo(Integer groupId) throws DBException {
        if (groupId == null) {
            return null;
        }

		Connection conn = null;

		try {
			conn = WebShare.database.getConnection();
			
			return retrieveGroupInfo(groupId, null, conn);
		} catch (Exception e) {
			VKCLogManager.errorLog(VKCGroupSerializer.class, "1", "Error occured while retrieving user groupInfos!", e);
			throw new DBException(e);
		} finally {
			DBHelper.close(conn);
		}
    }

    /**
     * Lädt eine GroupInfo-Bean aus der Datenbank
     *
     * @param groupname Name des zu ladenden GroupInfo-Beans
     *
     * @return das gewünschte GroupInfo-Bean Objekt
     */
    public static final GroupInfo retrieveGroupInfo(String groupname) throws DBException {
        if (groupname == null || groupname.equals("")) {
            return null;
        }

		Connection conn = null;

		try {
			conn = WebShare.database.getConnection();
			
			return retrieveGroupInfo(null, groupname, conn);
		} catch (Exception e) {
			VKCLogManager.errorLog(VKCGroupSerializer.class, "1", "Error occured while retrieving user groupInfos!", e);
			throw new DBException(e);
		} finally {
			DBHelper.close(conn);
		}
    }

    /**
     * Lädt eine GroupInfo-Bean aus der Datenbank
     *
     * @param groupId ID des zu ladenden GroupInfo-Beans
     * @param groupname Name des zu ladenden GroupInfo-Beans
     *
     * @return das gewünschte GroupInfo-Bean Objekt
     */
    private static final GroupInfo retrieveGroupInfo(Integer groupId, String groupname, Connection conn) throws DBException {
        PreparedStatement pStmt = null;
        ResultSet rs = null;

        try {
            if (groupId != null) {
                pStmt = conn.prepareStatement("SELECT * FROM GROUPS WHERE groupId = ?");
                pStmt.setInt(1, groupId.intValue());
            } else if (groupname != null) {
                pStmt = conn.prepareStatement("SELECT * FROM GROUPS WHERE name = ?");
                pStmt.setString(1, groupname);
            } else {
                return null;
            }

            rs = pStmt.executeQuery();

            if (!rs.next()) {
                return null;
            }

            GroupInfo group = new GroupInfo();
            group.setGroupId(new Integer(rs.getInt("groupid")));
            group.setName(rs.getString("name"));
            group.setDescription(rs.getString("description"));
			group.setAdminPrivileges(rs.getInt("adminprivileges") != 0);
            group.setAccountValid(rs.getInt("accountvalid") != 0);
            group.setValidFrom(rs.getTimestamp("validFrom"));
            group.setValidUntil(rs.getTimestamp("validUntil"));
            group.setCreatorId(new Integer(rs.getInt("creatorid")));
			group.setCreated(rs.getTimestamp("created"));

            // retrieve count members
            group.setCountMembers(VKCGroupSerializer.retrieveCountMembers(groupId, groupname, conn));

            return group;
        } catch (Exception e) {
            VKCLogManager.errorLog(VKCGroupSerializer.class, "1", "Error occured while retrieving GroupInfo!", e);
            throw new DBException(e);
        } finally {
        	DBHelper.close(rs);
            DBHelper.close(pStmt);
        }
    }
    
	public static final int retrieveCountMembers(Integer groupId, String groupname) throws DBException {
		Connection conn = null;

		try {
			conn = WebShare.database.getConnection();
			
			return retrieveCountMembers(groupId, groupname, conn);
		} catch (Exception e) {
			VKCLogManager.errorLog(VKCGroupSerializer.class, "1", "Error occured while retrieving user groupInfos!", e);
			throw new DBException(e);
		} finally {
			DBHelper.close(conn);
		}
	}

	private static final int retrieveCountMembers(Integer groupId, String groupname, Connection conn) throws DBException {
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		try {
			// retrieve count members
			if (groupId != null) {
				pStmt = conn.prepareStatement("SELECT COUNT(*) as count_members FROM usergroups WHERE groupId = ?");
				pStmt.setInt(1, groupId.intValue());
				rs = pStmt.executeQuery();
			} else if (groupname != null) {
				pStmt = conn.prepareStatement("SELECT COUNT(*) AS count_members FROM usergroups WHERE groupId = ( SELECT groupId FROM groups WHERE name = ? )");
				pStmt.setString(1, groupname);
				rs = pStmt.executeQuery();
			} else {
			    return 0;
			}

			rs.next();

			return rs.getInt("count_members");
		} catch (Exception e) {
			VKCLogManager.errorLog(VKCGroupSerializer.class, "1", "Error occured while retrieving count members!", e);
			throw new DBException(e);
		} finally {
			DBHelper.close(rs);
			DBHelper.close(pStmt);
		}
	}
	

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

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

        try {
            pStmt = conn.prepareStatement("UPDATE groups SET creatorid = ?, lastchanged = ?, lastchangerid = ? WHERE creatorid = ?");
            pStmt.setInt(1, newOwnerId.intValue());  // new group owner
            pStmt.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
            pStmt.setInt(3, changerId.intValue());
            pStmt.setInt(4, oldOwnerId.intValue());  // old group owner
            pStmt.execute();
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(pStmt);
        }
    }
	
	/**
	 * Speichert eine Gruppe in der DB ab Falls schon vorhanden, wird sie
	 * aktualisiert, sonst neu inseriert
	 *
	 * @param conn
	 * @param set die Gruppe
	 */
	public static void importGroup(List groups) throws DBException {
		Connection conn = null;

		try {
			conn = WebShare.database.getConnection();
			importGroup(groups,conn);
			conn.commit();
		} catch (Exception ex) {
			DBHelper.rollBack(conn);
			throw new DBException(ex);
		} finally {
			DBHelper.close(conn);
		}
	}

	/**
	 * Speichert eine Gruppe in der DB ab Falls schon vorhanden, wird sie
	 * aktualisiert, sonst neu inseriert
	 *
	 * @param conn
	 * @param group die Gruppe
	 */
	public static void importGroup(List groups, Connection conn) throws DBException {
		try {
			Iterator iterGroups = groups.iterator();
			String stmt = null;
			stmt  = "INSERT INTO GROUPS ( ";
			stmt += "NAME, ";
			stmt += "DESCRIPTION, ";
			stmt += "ACCOUNTVALID, ";
			stmt += "ADMINPRIVILEGES, ";
			stmt += "VALIDFROM, ";
			stmt += "VALIDUNTIL, ";
			stmt += "CREATED, ";
			stmt += "CREATORID, ";
			stmt += "LASTCHANGED, ";
			stmt += "LASTCHANGERID, ";
			stmt += "GROUPID )";
			stmt += " values ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) ";
			while(iterGroups.hasNext()){
				VKCGroup group = (VKCGroup) iterGroups.next();
				if(isGroupExistent(group.getName())){//falls Gruppe übernommen wurde
					saveGroup(group,conn);
				}else executeGroupStatement(stmt, group, conn);
			}
		} catch (Exception ex) {
			throw new DBException(ex);
		}
	}
}