/*
 * $Id: VKCDocumentViewFieldSerializer.java,v 1.11 2006/07/11 23:40:02 marbobo Exp $
 *
 * Copyright (c) Siemens AG 2008 
 *           (c) Universität Paderborn 2008 
 * Licensed under EUPL 1.1. 
 *
 * $Log: VKCDocumentViewFieldSerializer.java,v $
 * Revision 1.11  2006/07/11 23:40:02  marbobo
 * + PostgreSQL ist pingelig und erlaubt kein get/setBoolean auf Int-Spalten
 *
 * Revision 1.10  2004/12/09 21:07:40  marbobo
 * + kleine Optimierungen und Bugfixes
 * +an einigen Stellen Rreihenfolge der close-Anweisungen vereinheitlicht
 *
 * Revision 1.9  2004/12/09 12:39:28  lawern
 * vergessene PreparedStatements geschlossen
 *
 * Revision 1.8  2004/12/02 20:29:00  marbobo
 * + Statement auf PreparedStatement umgestellt
 *
 * Revision 1.7  2004/11/24 13:01:12  lawern
 * Global "conn.setAutoCommit( false );" enfernt, weil überflüssig.
 *
 * Revision 1.6  2004/11/24 09:25:54  lawern
 * Hibernate ist VKC-Geschichte!
 *
 * Revision 1.5  2004/11/18 16:00:01  jonyp
 * changed order of close-stmt to resultset/connect/session  in finally-blocks
 *
 * Revision 1.4  2004/11/10 15:02:44  koppy
 * Projektweite Überarbeitung von Error-Logs, Stacktraces,..
 *
 * Revision 1.3  2004/11/09 14:35:38  marbobo
 * + Umstellung von authorId auf publisherId
 * + Hinzufügen von AuthorName für Dokumente
 * + Nötwendige Änderungen betreffend AuthorName und PublisherId
 *
 * Revision 1.2  2004/07/20 08:23:21  jonyp
 * added copyright info + cvs log
 *
 *
 */

package vkc.db.serializer;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Iterator;

import java.sql.Connection;
import vkc.db.entities.VKCDocumentViewField;
import vkc.db.helper.DBHelper;
import vkc.exception.DBException;
import vkc.misc.VKCLogManager;
import vkc.view.WebShare;
import vkc.view.bean.DocumentViewSettings;

public class VKCDocumentViewFieldSerializer {
	private VKCDocumentViewFieldSerializer() {

	}

	/**
	 * Lädt das DocumentView-Objekt mit der übergebenen ID aus der Datenbank
	 *
	 * @param userId ID des Benutzers
	 */
	public final static DocumentViewSettings retrieveDocumentViewSettings( Integer userId )	throws DBException {
		if ( userId == null ) {
			HashMap map = new HashMap();
			DocumentViewSettings view = new DocumentViewSettings();
			view.setDocViewElements( map );
			
			return view;
		}

		Connection conn = null;

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

	/**
	 * Lädt das DocumentView-Objekt mit der übergebenen ID aus der Datenbank
	 *
	 * @param userId ID des Benutzers
	 */
	public static DocumentViewSettings retrieveDocumentViewSettings( Integer userId, Connection conn ) throws SQLException, DBException {
		if ( userId == null ) {
			HashMap map = new HashMap();
			DocumentViewSettings view = new DocumentViewSettings();
			view.setDocViewElements( map );

			return view;
		}

		PreparedStatement pStmt = null;
		ResultSet rs = null;

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

			HashMap map = new HashMap();
			
			while ( rs.next() ) {
				Integer fieldId = new Integer( rs.getInt( "fieldId" ) );
				VKCDocumentViewField field = retrieveDocumentViewField( fieldId, conn );
				map.put( field.getName(), field );
			}
			
			DocumentViewSettings view = new DocumentViewSettings();
			view.setDocViewElements( view.getDefaultviewDocumentViewSettings().getDocumentViewFields() );
			view.setDocViewElements( map );

			return view;
		} finally {
			DBHelper.close( rs );
			DBHelper.close( pStmt );
		}
	}

	/**
	 * Speichert das DocumentView-Objekt für das angegebene Benutzer
	 *
	 * @param view das DocumentView-Objekt der gespeichert werden soll 
	 * @param userId ID des Benutzerobjekts für welches das DocumentView-Objekt gespeichert werden soll 
	 */
	public final static void saveDocumentViewSettings( DocumentViewSettings view, Integer userId ) throws DBException {
		Connection conn = null;

		try {
			conn = WebShare.database.getConnection();
			saveDocumentViewSettings( view, userId, conn );
			conn.commit();
		} catch ( Exception e ) {
			DBHelper.rollBack( conn );
			VKCLogManager.errorLog( VKCDocumentViewFieldSerializer.class, "", "Documentview settings could not be saved!", e );
			throw new DBException( e );
		} finally {
			DBHelper.close( conn );
		}
	}

	/**
	 * Speichert das DocumentView-Objekt für das angegebene Benutzer
	 *
	 * @param view das DocumentView-Objekt der gespeichert werden soll 
	 * @param userId ID des Benutzerobjekts für welches das DocumentView-Objekt gespeichert werden soll 
	 */
	public static void saveDocumentViewSettings( DocumentViewSettings view, Integer userId, Connection conn ) throws DBException {
		PreparedStatement pStmt = null;

		try {
			// Erstmal evtl. vorhandene Schlüsselbegriffe löschen
			DBHelper.deleteEntry( conn, "USERDOCVIEWFIELDS", "userId", userId );

			pStmt = conn.prepareStatement( "INSERT INTO USERDOCVIEWFIELDS ( entryId, fieldId, userId ) VALUES ( ?, ?, ? )" );
			
			Iterator keys = view.getDocumentViewFields().keySet().iterator();

			while ( keys.hasNext() ) {
				String fieldKey = (String) keys.next();
				VKCDocumentViewField field = (VKCDocumentViewField)view.getDocumentViewFields().get( fieldKey );
				Integer id = DBHelper.getNewId( conn, "USERDOCVIEWFIELDS", "ENTRYID" );
				
				// Assoziation hinzufügen in die Zwischentabelle
				pStmt.setInt( 1, id.intValue() );
				pStmt.setInt( 2, field.getFieldId().intValue() );
				pStmt.setInt( 3, userId.intValue() );
				pStmt.execute();
			}
		} catch ( Exception e ) {
			throw new DBException( e );
		} finally {
			DBHelper.close( pStmt );
		}
	}

	/**
	 * Lädt alle verfügbaren DocumentViewField-Objekte aus der Datenbank
	 *
	 */
	public final static HashMap getAllDefaultDocumentViewFields() throws DBException {
		HashMap fields = getAllDocumentViewFields();
		HashMap map = new HashMap();
		
		Iterator keys = fields.keySet().iterator();
		
		while ( keys.hasNext() ) {
			String key = (String)keys.next();
			VKCDocumentViewField field = (VKCDocumentViewField)fields.get( key );
			
			if ( field.isDefaulField() ) {
				map.put( field.getName(), field );
			}
		}
		
		return map;
	}

	/**
	 * Lädt alle verfügbaren DocumentViewField-Objekte aus der Datenbank
	 *
	 */
	public final static HashMap getAllDocumentViewFields() throws DBException {
		Connection conn = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		HashMap fields = new HashMap();

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

			while ( rs.next() ) {
				Integer fieldId = new Integer( rs.getInt( "fieldId" ) );
				String name = rs.getString( "name" );
				String nameKey = rs.getString( "nameKey" );
				boolean defaultField = rs.getInt( "defaultField" ) != 0;

				VKCDocumentViewField field = new VKCDocumentViewField();
				field.setFieldId( fieldId );
				field.setName( name );
				field.setNameKey( nameKey );
				field.setDefaulField( defaultField );

				fields.put( field.getName(), field );
			}

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

	/**
	 * Lädt das DocumentViewField-Objekt mit der übergebenen ID aus der Datenbank
	 *
	 * @param id ID des Feldes
	 */
	public final static VKCDocumentViewField retrieveDocumentViewField( Integer id ) throws DBException {
		if ( id == null ) {
			return null;
		}

		Connection conn = null;

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

			return retrieveDocumentViewField( id, conn );
		} catch ( Exception e ) {
			throw new DBException( e );
		} finally {
			DBHelper.close( conn );
		}
	}

	/**
	 * Lädt das DocumentViewField-Objekt mit der übergebenen ID aus der Datenbank
	 *
	 * @param id ID des Feldes
	 */
	public static VKCDocumentViewField retrieveDocumentViewField( Integer id, Connection conn ) throws SQLException {
		if ( id == null ) {
			return null;
		}

		PreparedStatement pStmt = null;
		ResultSet rs = null;

		try {
			pStmt = conn.prepareStatement( "SELECT * FROM DOCUMENTVIEWFIELDS WHERE FIELDID = ?" );
			pStmt.setInt( 1, id.intValue() );
			rs = pStmt.executeQuery();

			VKCDocumentViewField field = null;
			
			if ( rs.next() ) {
				String name = rs.getString( "name" );
				String nameKey = rs.getString( "nameKey" );
				boolean defaultField = rs.getInt( "defaultField" ) != 0;

				field = new VKCDocumentViewField();
				field.setFieldId( id );
				field.setName( name );
				field.setNameKey( nameKey );
				field.setDefaulField( defaultField );
			}

			return field;
		} finally {
			DBHelper.close( rs );
			DBHelper.close( pStmt );
		}
	}

	/**
	 * Speichert das DocumentViewField-Objekt
	 *
	 * @param field das DocumentViewField-Objekt der gespeichert werden soll 
	 */
	public final static void saveDocumentViewField( VKCDocumentViewField field ) throws DBException {
		Connection conn = null;

		try {
			conn = WebShare.database.getConnection();
			saveDocumentViewField( field, conn );
			conn.commit();
		} catch ( Exception e ) {
			DBHelper.rollBack( conn );
			VKCLogManager.errorLog( VKCDocumentViewFieldSerializer.class, "", "DocumentViewField could not be saved!", e );
			throw new DBException( e );
		} finally {
			DBHelper.close( conn );
		}
	}

	/**
	 * Speichert das DocumentViewField-Objekt
	 *
	 * @param field das DocumentViewField-Objekt der gespeichert werden soll 
	 */
	public static void saveDocumentViewField( VKCDocumentViewField field, Connection conn ) throws SQLException, DBException {
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		try {
			pStmt = conn.prepareStatement( "SELECT * FROM DOCUMENTVIEWFIELDS WHERE FIELDID = ?" );
			pStmt.setInt( 1, field.getFieldId().intValue() );
			rs = pStmt.executeQuery();

			if ( rs.next() ) {
				Integer defaultField = ( field.isDefaulField() ) ? new Integer( 1 ) : new Integer( 0 );
				DBHelper.close( pStmt );
				pStmt = conn.prepareStatement( "UPDATE DOCUMENTVIEWFIELDS SET name = ?, nameKey = ?, defaultField = ?  WHERE fieldId = ?" );
				pStmt.setString( 1, field.getName() );
				pStmt.setString( 2, field.getNameKey() );
				pStmt.setInt( 3, defaultField.intValue() );
				pStmt.setInt( 4, field.getFieldId().intValue() );
				pStmt.execute();
			} else {
				Integer defaultField = ( field.isDefaulField() ) ? new Integer( 1 ) : new Integer( 0 );
				Integer id = DBHelper.getNewId( conn, "DOCUMENTVIEWFIELDS", "FIELDID" );
				// Assoziation hinzufügen in die Zwischentabelle
				DBHelper.close( pStmt );
				pStmt = conn.prepareStatement( "INSERT INTO DOCUMENTVIEWFIELDS ( fieldId, name, nameKey, defaultField ) VALUES ( ?, ?, ?, ? )" );
				pStmt.setInt( 1, id.intValue() );
				pStmt.setString( 2, field.getName() );
				pStmt.setString( 3, field.getNameKey() );
				pStmt.setInt( 4, defaultField.intValue() );
				pStmt.execute();
			}
		} finally {
			DBHelper.close( rs );
			DBHelper.close( pStmt );
		}
	}
}