/*
 * $Id: VKCLdapGroupSerializer.java,v 1.10 2004/12/09 12:45:40 lawern Exp $
 *
 * Copyright (c) Siemens AG 2008 
 *           (c) Universität Paderborn 2008 
 * Licensed under EUPL 1.1. 
 *
 * $Log: VKCLdapGroupSerializer.java,v $
 * Revision 1.10  2004/12/09 12:45:40  lawern
 * vergessene PreparedStatements geschlossen
 *
 * Revision 1.9  2004/12/07 15:02:10  marbobo
 * + Statement auf PreparedStatement umgestellt
 *
 * Revision 1.8  2004/11/24 13:01:16  lawern
 * Global "conn.setAutoCommit( false );" enfernt, weil überflüssig.
 *
 * Revision 1.7  2004/11/24 09:25:58  lawern
 * Hibernate ist VKC-Geschichte!
 *
 * Revision 1.6  2004/11/18 19:38:24  marbobo
 * + changed order of close-stmt to resultset/connect/session  in finally-blocks
 *
 * Revision 1.5  2004/11/15 12:35:39  vchris
 * + Cleanup-Job für obsolete LDAP-Group Einträge
 *
 * Revision 1.4  2004/11/15 09:51:36  jonyp
 * changed saveGroup ==> saveLdapGroup
 *
 * Revision 1.3  2004/10/25 14:20:45  vchris
 * *** empty log message ***
 *
 * Revision 1.2  2004/10/08 08:27:31  jonyp
 * added method getLdapUserURL
 *
 * Revision 1.1  2004/10/07 14:41:12  jonyp
 * first strike
 *
 */

package vkc.ldap.db.serializer;

import java.sql.PreparedStatement;
import java.sql.ResultSet;

import java.sql.Connection;

import vkc.db.entities.VKCAclType;
import vkc.db.helper.DBHelper;
import vkc.exception.DBException;
import vkc.view.WebShare;
import vkc.ldap.db.entities.VKCLdapGroup;

public class VKCLdapGroupSerializer {
    private VKCLdapGroupSerializer() {
    }

	public final static VKCLdapGroup retrieveLdapGroup( Integer ldapGroupId ) throws DBException {
		Connection conn = null;

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

			return retrieveLdapGroup( ldapGroupId, conn );
		} catch ( Exception ex ) {
			throw new DBException( ex );
		} finally {
			DBHelper.close( conn );
		}
	}
    
	public final static VKCLdapGroup retrieveLdapGroup( Integer ldapGroupId, Connection conn ) throws DBException {
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		
		try {
			if ( ldapGroupId == null ) {
				return null;
			}
			
			pStmt = conn.prepareStatement( "SELECT * FROM ldapGroups WHERE ldapGroupId = ?" );
			pStmt.setInt( 1, ldapGroupId.intValue() );
			rs = pStmt.executeQuery();
			
			if ( rs.next() ) {
				VKCLdapGroup ldapGroup = new VKCLdapGroup();
				ldapGroup.setLdapGroupId( new Integer( rs.getInt( "ldapGroupId" ) ) );
				ldapGroup.setLdapServerId( new Integer( rs.getInt( "ldapServerId" ) ) );
				ldapGroup.setLdapGroupRDN( rs.getString( "ldapGroupRDN" ) );
				
				return ldapGroup;
			} 

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

	/**
	 * 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 saveLdapGroup( VKCLdapGroup ldapGroup ) throws DBException {
		Connection conn = null;

		try {
			conn = WebShare.database.getConnection();
			saveLdapGroup( ldapGroup, 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 saveLdapGroup( VKCLdapGroup ldapGroup, Connection conn  ) throws DBException {
		PreparedStatement pStmt = null;

		try {
			String stmt = null;

			if ( ldapGroup.getLdapGroupId() == null || ldapGroup.getLdapGroupId().intValue() <= 0 ) {
				ldapGroup.setLdapGroupId( DBHelper.getNewId( conn, "ldapGroups", "ldapGroupId" ) );
	
				stmt  = "INSERT INTO ldapGroups ( ";
				stmt += "ldapServerId, ";
				stmt += "ldapGroupRDN, ";
				stmt += "ldapGroupId ) ";
				stmt += " values ( ?, ?, ? ) ";
			} else {
				stmt  = "UPDATE ldapGroups SET ";
				stmt += "ldapServerId = ?, ";
				stmt += "ldapGroupRDN = ?, ";
				stmt += "WHERE ldapGroupId = ?";
			}

			pStmt = conn.prepareStatement( stmt );
			pStmt.setInt( 1, ldapGroup.getLdapServerId().intValue() );
			pStmt.setString( 2, ldapGroup.getLdapGroupRDN() );
			pStmt.setInt( 3, ldapGroup.getLdapGroupId().intValue() );

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

		try {
			conn = WebShare.database.getConnection();
			
			pStmt = conn.prepareStatement( "DELETE FROM CategoryAcls WHERE AclTypeId = " + VKCAclType.LDAP_GROUP_TYPE + " AND AccessorId = ?" );
			pStmt.setInt( 1, ldapGroupId.intValue() );
			pStmt.execute();
			DBHelper.close(pStmt);
			pStmt = conn.prepareStatement( "DELETE FROM ldapGroups WHERE ldapGroupId = ?" );
			pStmt.setInt( 1, ldapGroupId.intValue() );
			pStmt.execute();

			conn.commit();
		} catch ( Exception e ) {
			DBHelper.rollBack( conn );
			throw new DBException( e );
		} finally {
			DBHelper.close( pStmt );
			DBHelper.close( conn );
		}
	} 
	
	/**
	 * Löscht obsolete Einträge aus der Datenbanktabelle LDAPGROUPS, die dadurch entstehen können, 
	 * dass der Benutzer die Erstellung einer ACL abbricht.
	 * 
	 * @throws DBException
	 */
	public static void deleteObsoleteEntries() throws DBException {
		Connection conn = null;

		try {
			conn = WebShare.database.getConnection();
			deleteObsoleteEntries( conn );
			conn.commit();
		} catch ( Exception ex ) {
			DBHelper.rollBack( conn );
			throw new DBException( ex );
		} finally {
			DBHelper.close( conn );
		}
	}
	
	/**
	 * Löscht obsolete Einträge aus der Datenbanktabelle LDAPGROUPS, die dadurch entstehen können, 
	 * dass der Benutzer die Erstellung einer ACL abbricht.
	 * 
	 * @param conn
	 * @throws DBException
	 */
	public static final void deleteObsoleteEntries( Connection conn ) throws DBException {
		PreparedStatement pStmt = null;
		
		try {
			String query = 
			    "DELETE FROM ldapgroups " +
				"WHERE ldapgroupid NOT IN ( " +
					"SELECT accessorid " +
					"FROM categoryacls, ldapgroups " +
					"WHERE acltypeid = " + VKCAclType.LDAP_GROUP_TYPE + " " +
					"AND categoryacls.accessorid = ldapgroups.ldapgroupid )";
			pStmt = conn.prepareStatement( query );
			pStmt.execute();
		} catch ( Exception e ) {
			throw new DBException( e );
		} finally {
			DBHelper.close( pStmt );
		}
	}
}