/*
 * $Id: VKCDynaAttributeSerializer.java,v 1.15 2005/02/09 10:36:06 jonyp Exp $
 *
 * Copyright (c) Siemens AG 2008 
 *           (c) Universität Paderborn 2008 
 * Licensed under EUPL 1.1. 
 *
 * $Log: VKCDynaAttributeSerializer.java,v $
 * Revision 1.15  2005/02/09 10:36:06  jonyp
 * bug fix: allow dynamical attributes to be stored without having a value
 *
 * Revision 1.14  2005/01/04 13:54:18  jonyp
 * rollbacked last fix!!! dynattributes => dynaentries in saveDynaAttributes
 *
 * Revision 1.13  2005/01/04 10:13:09  jonyp
 * bug fix: use correct table dynattributes in method saveDynaAttributes
 *
 * Revision 1.12  2004/12/23 16:19:07  koppy
 * + Collection retrieveDynAttributes(Integer dynAttrsId)
 *
 * Revision 1.11  2004/12/17 01:18:34  marbobo
 * + Weitere Umstellungen und Optimierungen bzgl. PreparedStatements
 *
 * Revision 1.10  2004/12/02 20:29:00  marbobo
 * + Statement auf PreparedStatement umgestellt
 *
 * Revision 1.9  2004/11/24 09:25:53  lawern
 * Hibernate ist VKC-Geschichte!
 *
 * Revision 1.8  2004/11/18 16:00:59  jonyp
 * changed order of close-stmt to resultset/connect/session  in finally-blocks
 *
 * Revision 1.7  2004/09/30 14:08:54  marbobo
 * + Generator für Dyna. Einträge hinzugefügt
 *
 * Revision 1.6  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.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;

import java.sql.Connection;
import vkc.db.entities.VKCCategory;
import vkc.db.entities.VKCDocument;
import vkc.db.entities.VKCDynaAttribute;
import vkc.db.entities.VKCEntity;
import vkc.db.helper.DBHelper;
import vkc.exception.DBException;
import vkc.view.WebShare;

public class VKCDynaAttributeSerializer {
	private VKCDynaAttributeSerializer() {
	}

	public static final VKCDynaAttribute retrieveDynaAttribute( Integer id ) throws DBException {
		Connection conn = null;

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

	public static final VKCDynaAttribute retrieveDynaAttribute( Integer id, Connection conn ) throws DBException {
		if ( id == null ) {
			return null;
		}

		PreparedStatement pStmt = null;
		ResultSet rs = null;

		try {
			pStmt = conn.prepareStatement( "SELECT * FROM dynattributes where daentyrId = ?" );
			pStmt.setInt( 1, id.intValue() );
			rs = pStmt.executeQuery();

			if ( rs.next() ) {
				VKCDynaAttribute attr = new VKCDynaAttribute();
				setFields( attr, rs);
				
				return attr;
			} 

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

	private static final void setFields( VKCDynaAttribute attr, ResultSet rs ) throws SQLException {
		attr.setDynaEntryId( new Integer( rs.getInt( "daentyrId" ) ) );
		if ( rs.wasNull() ) attr.setDynaEntryId( null );

		attr.setDynaAttrId( new Integer( rs.getInt( "DYNATTRID" ) ) );
		if ( rs.wasNull() ) attr.setDynaAttrId( null );

		attr.setName( rs.getString( "name" ) );
		attr.setValue( rs.getString( "valuestring" ) );
	}
	
	private static final void saveDynaAttribute( VKCDynaAttribute attr, Connection conn ) throws DBException {
		if ( attr == null ) {
			return;
		}

		PreparedStatement pStmt = null;

		try {
			if ( DBHelper.existsEntry( conn, "dynattributes", "daentyrId", attr.getDynaEntryId() ) ) {
				String str = "UPDATE dynattributes SET dynAttrId = ?, name = ?, valuestring = ?, daentyrId = ?";
				pStmt = conn.prepareStatement( str );				
			} else {
				attr.setDynaEntryId( DBHelper.getNewId( conn, "dynaentries", "daentyrId" ) );
				
				String str = "INSERT INTO dynattributes ( dynAttrId, name, valuestring, daentyrId ) values ( ?, ?, ?, ? )";
				pStmt = conn.prepareStatement( str );
			}

			pStmt.setInt( 1, attr.getDynaAttrId().intValue() );
			pStmt.setString( 2, attr.getName().trim() );
			
			if ( attr.getValue() == null ) {
				pStmt.setNull( 3, Types.VARCHAR );
			} else {
				pStmt.setString( 3, attr.getValue().trim() );
			}

			pStmt.setInt( 4, attr.getDynaEntryId().intValue() );
			pStmt.executeUpdate();
		} catch ( Exception ex ) {
			throw new DBException( ex );
		} finally {
			DBHelper.close( pStmt );
		}
	}

	/**
	 * Speichert die dynamischen Attribute einer VKC Kategorie
	 *
	 * @param conn in dieser Connection
	 * @param entity von dieser VKCCategory
	 *
	 * @throws HibernateException
	 * @throws SQLException
	 */
	public static void saveDynaAttributes( Connection conn, VKCDocument entity ) throws DBException {
		PreparedStatement pStmt = null;

		try {
			Collection attrs = entity.getDynaAttributes();
	
			if ( attrs == null ) {
				return;
			}
	
			if ( attrs.isEmpty() ) {
				pStmt = conn.prepareStatement( "UPDATE Documents SET dynAttrsId = NULL WHERE documentId = ?" );
				pStmt.setInt( 1, entity.getDocumentId().intValue() );
				pStmt.execute();
				
				return;
			}
	
			if ( entity.getDynaAttrsId() == null ) {
				entity.setDynaAttrsId( DBHelper.getNewId( conn, "DynAttributes", "dynAttrId" ) );
				pStmt = conn.prepareStatement( "UPDATE Documents SET dynAttrsId = ? WHERE documentId = ?" );
				pStmt.setInt( 1, entity.getDynaAttrsId().intValue() );
				pStmt.setInt( 2, entity.getDocumentId().intValue() );
				pStmt.execute();
			} else {
				DBHelper.deleteEntry( conn, "DynAttributes", "dynAttrId", entity.getDynaAttrsId() );
			}
	
			for ( Iterator i = attrs.iterator(); i.hasNext(); ) {
				VKCDynaAttribute attr = (VKCDynaAttribute) i.next();

				if ( attr.getName().trim().length() < 1 ) {
					continue;
				}
				
				attr.setDynaEntryId( DBHelper.getNewId( conn, "dynaentries", "daEntyrId" ) );
				attr.setDynaAttrId( entity.getDynaAttrsId() );
				saveDynaAttribute( attr, conn );
			}
		} catch( Exception ex ) {
			throw new DBException( ex );
		} finally {
			DBHelper.close( pStmt );
		}
	}

	/**
	 * Speichert die dynamischen Attribute einer VKC Kategorie
	 *
	 * @param conn in dieser Connection
	 * @param entity von dieser VKCCategory
	 *
	 * @throws HibernateException
	 * @throws SQLException
	 */
	public static void saveDynaAttributes( Connection conn, VKCCategory entity ) throws DBException {
		PreparedStatement pStmt = null;

		try {
		    Collection attrs = entity.getDynaAttributes();
	
		    if ( attrs == null ) {
		        return;
		    }
	
		    if ( attrs.isEmpty() ) {
				pStmt = conn.prepareStatement( "UPDATE Categories SET dynAttrsId = NULL WHERE categoryId = ?" );
				pStmt.setInt( 1, entity.getCategoryId().intValue() );
				pStmt.execute();
		        
		        return;
		    }
	
		    if ( entity.getDynaAttrsId() == null ) {
		        entity.setDynaAttrsId( DBHelper.getNewId( conn, "DynAttributes", "dynAttrId" ) );
				pStmt = conn.prepareStatement( "UPDATE Categories SET dynAttrsId = ? WHERE categoryId = ?" );
				pStmt.setInt( 1, entity.getDynaAttrsId().intValue() );
				pStmt.setInt( 2, entity.getCategoryId().intValue() );
				pStmt.execute();
		    } else {
		        DBHelper.deleteEntry( conn, "DynAttributes", "dynAttrId", entity.getDynaAttrsId() );
		    }
	
		    for ( Iterator i = attrs.iterator(); i.hasNext(); ) {
		        VKCDynaAttribute attr = (VKCDynaAttribute) i.next();

		        if ( attr.getName().trim().length() < 1 ) {
		            continue;
		        }
		        
		        attr.setDynaEntryId( DBHelper.getNewId( conn, "dynaentries", "daEntyrId" ) );
		        attr.setDynaAttrId( entity.getDynaAttrsId() );
				saveDynaAttribute( attr, conn );
		    }
		} catch( Exception ex ) {
			throw new DBException( ex );
		} finally {
			DBHelper.close( pStmt );
		}
	}

	/**
	 * Laden von dynamischen Attributen und Hinzufügen zu einer VKC Entität
	 * (VKCCategory, VKCDocument, VKCUser)
	 *
	 * @param entity
	 *
	 * @throws HibernateException
	 * @throws SQLException
	 */
	public static void retrieveDynaAttributes( VKCEntity entity ) throws DBException {
		Connection conn = null;

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

	/**
	 * Laden von dynamischen Attributen und Hinzufügen zu einer VKC Entität
	 * (VKCCategory, VKCDocument, VKCUser)
	 *
	 * @param conn
	 * @param entity
	 *
	 * @throws HibernateException
	 * @throws SQLException
	 */
	public static void retrieveDynaAttributes( Connection conn, VKCEntity entity ) throws DBException { 
		
	    Collection attrs = VKCDynaAttributeSerializer.retrieveDynaAttributes(
                conn, entity.getDynaAttrsId());
	    
        if (attrs == null) {
            return;
        }

        entity.getDynaAttributes().clear();
        entity.getDynaAttributes().addAll(attrs);
	}
	
	
	/**
	 * Laden von dynamischen Attributen
	 *
	 * @param dynaAttrsId
	 *
	 * @throws DBException
	 */
	public static Collection retrieveDynaAttributes( Integer dynaAttrsId ) throws DBException {
		Connection conn = null;

		try {
			conn = WebShare.database.getConnection();
			return retrieveDynaAttributes( conn, dynaAttrsId);
		} catch ( Exception e ) {
			throw new DBException( e );
		} finally {
			DBHelper.close( conn );
		}
	}
	
	
	/**
	 * Laden von dynamischen Attributen
	 *
	 * @param conn
	 * @param dynaAttrsId
	 *
	 * @throws HibernateException
	 * @throws SQLException
	 */
	public static Collection retrieveDynaAttributes( Connection conn, Integer dynAttrsId) throws DBException {
	    if (dynAttrsId == null || dynAttrsId.intValue() <= 0 ) {
	        return null; // keine Dynamischen Attribute vorhanden
	    } 
	    
	    Collection attrs = new ArrayList();
		
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		
		try {
			pStmt = conn.prepareStatement( "SELECT * FROM DYNATTRIBUTES where DYNATTRID = ?" );
			pStmt.setInt( 1, dynAttrsId.intValue() );
			rs = pStmt.executeQuery();	

			while ( rs.next() ) {
				VKCDynaAttribute attr = new VKCDynaAttribute();
				setFields( attr, rs );
				attrs.add( attr );
			}
		} catch( Exception ex ) {
			throw new DBException( ex );
		} finally {
			DBHelper.close( rs );
			DBHelper.close( pStmt );
		}
		
		return attrs;
	}
}