/*
 * $Id: VKCAddAttributeSerializer.java,v 1.13 2006/03/27 22:08:15 marbobo Exp $
 *
 * Copyright (c) Siemens AG 2008 
 *           (c) Universität Paderborn 2008 
 * Licensed under EUPL 1.1. 
 *
 * $Log: VKCAddAttributeSerializer.java,v $
 * Revision 1.13  2006/03/27 22:08:15  marbobo
 * + Bugfixes und Refactoring
 *
 * Revision 1.12  2006/03/23 11:39:19  marbobo
 * + Mögliche NPE abgefangen
 *
 * Revision 1.11  2004/12/09 12:39:28  lawern
 * vergessene PreparedStatements geschlossen
 *
 * Revision 1.10  2004/12/01 21:44:15  marbobo
 * + Statement auf PreparedStatement umgestellt
 *
 * Revision 1.9  2004/11/24 13:01:12  lawern
 * Global "conn.setAutoCommit( false );" enfernt, weil überflüssig.
 *
 * Revision 1.8  2004/11/24 09:25:54  lawern
 * Hibernate ist VKC-Geschichte!
 *
 * Revision 1.7  2004/07/20 08:23:21  jonyp
 * added copyright info + cvs log
 *
 *
 */
package vkc.db.serializer;

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

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Enumeration;
import java.util.List;
import java.util.Vector;

public class VKCAddAttributeSerializer {
	private VKCAddAttributeSerializer() {
	}

	/**
	 * Speichert alle verfügbaren Additional Attributes für ein Dokument
	 *
	 * @param conn Die Connection
	 * @param document Das Dokument, für das die Additional Attributes
	 *        gespeichert werden sollen
	 *
	 * @throws HibernateException
	 * @throws SQLException
	 */
	public static final void saveAddDocAttributes(Connection conn, VKCDocument document) {
		saveAddAttributes(conn, document);
	}

	/**
	 * Speichert alle verfügbaren Additional Attributes für eine Kategorie
	 *
	 * @param conn Die Connection
	 * @param category Die Kategorie, für die die Additional Attributes
	 *        gespeichert werden sollen
	 *
	 * @throws HibernateException
	 * @throws SQLException
	 */
	public static final void saveAddCatAttributes(Connection conn, VKCCategory category) {
		saveAddAttributes(conn, category);
	}

	/**
	 * Speichert alle verfügbaren Attribute für eine VKCEntity. Bisher werden
	 * VKCDocument und VKCCategory unterstützt.
	 *
	 * @param conn Die Connection
	 * @param entity Die Entity, für die die Additional Attributes gespeichert
	 *        werden sollen
	 *
	 * @throws HibernateException
	 * @throws SQLException
	 */
	private static final void saveAddAttributes(Connection conn, VKCEntity entity) {
		// Methodenweite Variablen
		PreparedStatement pStmt1 = null;
		PreparedStatement pStmt2 = null;
		String sqlStmt = null;

		// Diese Variablen werden je nach Entity-Typ gesetzt
		String tableValuesName = null;
		String entityIdString = null;
		String entityAttrId = null;
		Integer entityId = null;

		try {
			// feststellen, um welchen Entity-Typ es sich handelt
			if (entity instanceof VKCCategory) {
				tableValuesName = "addcatattrvalues";
				entityIdString = "categoryId";
				entityAttrId = "catAttrId";
				entityId = ((VKCCategory) entity).getCategoryId();
			} else if (entity instanceof VKCDocument) {
				tableValuesName = "adddocattrvalues";
				entityIdString = "documentId";
				entityAttrId = "docAttrId";
				entityId = ((VKCDocument) entity).getDocumentId();
			} else {
				return;
			}

			// Falls keine Additional Attributes für die Entity vorhanden sind, hier schon beenden 
			if (entity.getAddAttributes() == null) {
				return;
			}

			// zunächst alle Einträge löschen ...
			sqlStmt = "DELETE FROM " + tableValuesName + " WHERE " + entityIdString + " = ?";
			pStmt1 = conn.prepareStatement(sqlStmt);
			pStmt1.setInt(1, entityId.intValue());
			pStmt1.executeUpdate();

			// ... dann alle neuen Einträge schreiben
			sqlStmt = "INSERT INTO " + tableValuesName + " (" + entityIdString + ", " + entityAttrId + ", valuestring) "
					+ "VALUES (?, ?, ?)";
			pStmt2 = conn.prepareStatement(sqlStmt);

			for (Enumeration e = entity.getAddAttributes().elements(); e.hasMoreElements();) {
				VKCAddAttribute addAttr = (VKCAddAttribute) e.nextElement();

				pStmt2.setInt(1, entityId.intValue());
				pStmt2.setInt(2, addAttr.getAttrId().intValue());
				pStmt2.setString(3, addAttr.getValuestring());

				pStmt2.executeUpdate();
			}
		} catch (Exception ex) {
			VKCLogManager.errorLog(VKCAddAttributeSerializer.class, "1", "Error occured while save additional attributes!", ex);
		} finally {
			DBHelper.close(pStmt1);
			DBHelper.close(pStmt2);
		}
	}

	/**
	 * Lädt alle verfügbaren Additional Attributes für Kategorien.
	 *
	 * @param conn Die Connection
	 *
	 * @return Vector mit VKCAddAttribute-Objekten
	 *
	 * @throws HibernateException
	 * @throws SQLException
	 */
	public static final Vector retrieveAddCatAttributes(Connection conn) throws SQLException {
		return retrieveAddAttributes(conn, new VKCCategory(), true);
	}

	/**
	 * Lädt alle verfügbaren Additional Attributes einer bestimmten Kategorie
	 * mitsamt ihren Werten. Wird diese Methode mit Parameter category=null
	 * aufruft, bewirkt dies dasselbe wie ein Aufruf von
	 * retrieveAddAttributes(Connection conn).
	 *
	 * @param conn Die Connection
	 * @param category Die Kategorie, für die die Attribute geholt werden
	 *        sollen
	 *
	 * @return Vector mit VKCAddAttribute-Objekten
	 *
	 * @throws HibernateException
	 * @throws SQLException
	 */
	public static final Vector retrieveAddCatAttributes(Connection conn, VKCCategory category) throws SQLException {
		if (category == null) {
			return retrieveAddCatAttributes(conn);
		}

		return retrieveAddAttributes(conn, category, false);
	}

	/**
	 * Lädt alle verfügbaren Attribute einer VKCEntity. Bisher werden
	 * VKCDocument und VKCCategory unterstützt. Ist der Parameter
	 * AttributesOnly auf true gesetzt, werden nur die Attribute selbst, nicht
	 * aber deren Werte zurückgeliefert. Diese private Methode wird von den
	 * entsprechenden public-Methoden retrieveAddCatAttrs(...) und
	 * retrieveAddDocAttrs(...) aufgerufen.
	 *
	 * @param conn Die Connection
	 * @param entity Die VKCEntity, deren additional Attributes geholt werden
	 *        sollen
	 * @param attributesOnly Auf 'true' setzen, falls nur die Attribute selbst
	 *        geholt werden sollen
	 *
	 * @return Vector mit VKCAddAttribute Objekten
	 *
	 * @throws HibernateException
	 * @throws SQLException
	 */
	private static final Vector retrieveAddAttributes(Connection conn, VKCEntity entity, boolean attributesOnly) throws SQLException {
		// Methodenweite Variablen
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		String sqlStatement = null;

		try {
			// Diese Variablen werden je nach VKCEntity-Typ gesetzt
			String entityAttrIdName = null;
			String tableName = null;
			String tableValuesName = null;
			String entityIdString = null;
			Integer entityId = null;

			if (entity instanceof VKCCategory) {
				// es handelt sich um ein VKCCategory-Objekt
				entityAttrIdName = "catAttrId";
				tableName = "addcatattrs";
				tableValuesName = "addcatattrvalues";
				entityIdString = "categoryId";
				entityId = ((VKCCategory) entity).getCategoryId();
			} else if (entity instanceof VKCDocument) {
				// es handelt sich um ein VKCDocument-Objekt
				entityAttrIdName = "docAttrId";
				tableName = "adddocattrs";
				tableValuesName = "adddocattrvalues";
				entityIdString = "documentId";
				entityId = ((VKCDocument) entity).getDocumentId();
			} else {
				return null; // ungültiges übergebenes Objekt
			}

			// SQL-Statement zusammensetzen
			if (attributesOnly) {
				// Erstelle Statement, welches nur die Attribute selbst holt
				sqlStatement = "SELECT " + tableName + "." + entityAttrIdName
						+ ", " + tableName + ".name, " + tableName
						+ ".allowNull, " + tableName + ".defaultvalue, "
						+ tableName + ".description" + "  FROM " + tableName;
				pStmt = conn.prepareStatement(sqlStatement);
			} else {
				// Erstelle Statement, welches die Attribute mitsamt Werten holt
				sqlStatement = "SELECT " + tableName + "." + entityAttrIdName
						+ ", " + tableName + ".name, " + tableName
						+ ".allowNull, " + tableName + ".defaultvalue, "
						+ tableName + ".description, " + tableValuesName
						+ ".valuestring" + "  FROM " + tableName
						+ " LEFT OUTER JOIN " + tableValuesName + "    ON "
						+ tableName + "." + entityAttrIdName + " = "
						+ tableValuesName + "." + entityAttrIdName + "   AND "
						+ tableValuesName + "." + entityIdString + " = ?";
				pStmt = conn.prepareStatement(sqlStatement);
				pStmt.setInt(1, entityId.intValue());
			}

			rs = pStmt.executeQuery();

			Vector addEntityAttrs = new Vector();

			while (rs.next()) {
				VKCAddAttribute attr = new VKCAddAttribute();
				attr.setAttrId(new Integer(rs.getInt(entityAttrIdName)));
				attr.setName(rs.getString("name"));
				attr.setDescription(rs.getString("description"));
				attr.setAllowNull(new Integer(rs.getInt("allowNull")));
				attr.setDefaultValue(rs.getString("defaultvalue"));

				if (!attributesOnly) {
					attr.setValuestring(rs.getString("valuestring"));
				}

				addEntityAttrs.add(attr);
			}

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

	/**
	 * Lädt alle verfügbaren Additional Attributes für ein bestimmtes Dokument
	 * mitsamt ihren Werten Wird diese Methode mit Parameter document=null
	 * aufgerufen, bewirkt dies dasselbe wie ein Aufruf von
	 * retrieveAddDocAttributes(Connection conn);
	 *
	 * @param conn Die Connection
	 * @param document Das Dokument, für das die Attribute geladen werden
	 *        sollen
	 *
	 * @return Vector mit VKCAddAttribute-Objekten
	 *
	 * @throws HibernateException
	 * @throws SQLException
	 */
	public static final Vector retrieveAddDocAttributes(Connection conn, VKCDocument document) throws SQLException {
		if (document == null) {
			return retrieveAddDocAttributes(conn);
		}

		return retrieveAddAttributes(conn, document, false);
	}

	/**
	 * Lädt alle verfügbaren Attribute für Dokumente
	 *
	 * @param conn Die Connection
	 *
	 * @return Vector mit VKCAddAttribute-Objekten
	 *
	 * @throws HibernateException
	 * @throws SQLException
	 */
	public static final Vector retrieveAddDocAttributes(Connection conn) throws SQLException {
		return retrieveAddAttributes(conn, new VKCDocument(), true);
	}

	/**
	 * Liefert alle globalen zusätzlichen Dokumentattribute zurück
	 *
	 * @return Vector von VKCAddAttributes
	 *
	 * @throws DBException
	 */
	public static final Vector getAllAddDocAttributes() throws DBException {
		Connection conn = null;

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

			return retrieveAddDocAttributes(conn, null);
		} catch (Exception e) {
			VKCLogManager.errorLog(VKCAddAttributeSerializer.class, "1", "Error occured while retrieving document additional attributes!", e);
			throw new DBException(e);
		} finally {
			DBHelper.close(conn);
		}
	}

	/**
	 * Gibt alle globalen zusätzlichen CategorieAttribute wieder
	 *
	 * @return Vector von VKCAddAttributes
	 */
	public final static Vector getAllAddCatAttributes() throws DBException {
		Connection conn = null;

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

			return VKCAddAttributeSerializer.retrieveAddCatAttributes(conn, null);
		} catch (Exception e) {
			VKCLogManager.errorLog(VKCAddAttributeSerializer.class, "1", "Error occured while retrieving category additional attributes!", e);
			throw new DBException(e);
		} finally {
			DBHelper.close(conn);
		}
	}

	/**
	 * Löscht alle angegebene globale CategorieAttribute und alle ihre Referenzen
	 * aus der Datenbank
	 *
	 */
	public final static void deleteAddCatAttribute(Integer[] attrIds) throws DBException {
		Connection conn = null;

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

			for (int i = 0; i < attrIds.length; i++) {
				deleteAddCatAttribute(conn, attrIds[i]);
			}

			conn.commit();
		} catch (Exception e) {
			VKCLogManager.errorLog(VKCAddAttributeSerializer.class, "1", "Error occured while deleting category additional attributes!", e);
			DBHelper.rollBack(conn);
			throw new DBException(e);
		} finally {
			DBHelper.close(conn);
		}
	}

	/**
	 * Löscht angegebenen globalen CategorieAttribute und alle seine Referenzen
	 * aus der Datenbank
	 *
	 */
	public final static void deleteAddCatAttribute(Integer attrId) throws DBException {
		Connection conn = null;

		try {
			conn = WebShare.database.getConnection();
			deleteAddCatAttribute(conn, attrId);
			conn.commit();
		} catch (Exception e) {
			VKCLogManager.errorLog(VKCAddAttributeSerializer.class, "1", "Error occured while deleting category additional attribute!", e);
			DBHelper.rollBack(conn);
			throw new DBException(e);
		} finally {
			DBHelper.close(conn);
		}
	}

	/**
	 * Löscht angegebenen globalen CategorieAttribute und alle seine Referenzen
	 * aus der Datenbank
	 *
	 * @param attribute zulöschender Attribute
	 */
	public final static void deleteAddCatAttribute(Connection conn, Integer attrId) throws SQLException {
		DBHelper.deleteEntry(conn, "AddCatAttrValues", "catAttrId", attrId);
		DBHelper.deleteEntry(conn, "AddCatAttrs", "catAttrId", attrId);
	}

	/**
	 * Löscht alle angegebene globale DokumentAttribute und alle ihre Referenzen
	 * aus der Datenbank
	 *
	 */
	public final static void deleteAddDocAttribute(Integer[] attrIds) throws DBException {
		Connection conn = null;

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

			for (int i = 0; i < attrIds.length; i++) {
				deleteAddDocAttribute(conn, attrIds[i]);
			}

			conn.commit();
		} catch (Exception e) {
			VKCLogManager.errorLog(VKCAddAttributeSerializer.class, "1", "Error occured while deleting document additional attributes!", e);
			DBHelper.rollBack(conn);
			throw new DBException(e);
		} finally {
			DBHelper.close(conn);
		}
	}

	/**
	 * Löscht angegebenen globalen DokumentAttribute und alle seine Referenzen
	 * aus der Datenbank
	 *
	 */
	public final static void deleteAddDocAttribute(Integer attrId) throws DBException {
		Connection conn = null;

		try {
			conn = WebShare.database.getConnection();
			deleteAddDocAttribute(conn, attrId);
			conn.commit();
		} catch (Exception e) {
			VKCLogManager.errorLog(VKCAddAttributeSerializer.class, "1", "Error occured while deleting document additional attribute!", e);
			DBHelper.rollBack(conn);
			throw new DBException(e);
		} finally {
			DBHelper.close(conn);
		}
	}

	/**
	 * Löscht angegebenen globalen DokumentAttribute und alle seine Referenzen
	 * aus der Datenbank
	 *
	 * @param attribute zulöschender Attribute
	 */
	public final static void deleteAddDocAttribute(Connection conn, Integer attrId) throws SQLException {
		DBHelper.deleteEntry(conn, "AddDocAttrValues", "docAttrId", attrId);
		DBHelper.deleteEntry(conn, "AddDocAttrs", "docAttrId", attrId);
	}

	/**
	 * Speichert angegebene globale KategorieAttribute 
	 */
	public final static void saveAddCatAttribute(List attributes) throws DBException {
		Connection conn = null;

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

			for (int i = 0; i < attributes.size(); i++) {
				VKCAddAttribute attribute = (VKCAddAttribute) attributes.get(i);
				saveAddCatAttribute(conn, attribute);
			}

			conn.commit();
		} catch (Exception e) {
			VKCLogManager.errorLog(VKCAddAttributeSerializer.class, "1", "Error occured while saving category additional attributes!", e);
			DBHelper.rollBack(conn);
			throw new DBException(e);
		} finally {
			DBHelper.close(conn);
		}
	}

	/**
	 * Speichert angegebenen globalen KategorieAttribute 
	 */
	public final static void saveAddCatAttribute(VKCAddAttribute attribute) throws DBException {
		Connection conn = null;

		try {
			conn = WebShare.database.getConnection();
			saveAddCatAttribute(conn, attribute);
			conn.commit();
		} catch (Exception e) {
			VKCLogManager.errorLog(VKCAddAttributeSerializer.class, "1", "Error occured while saving category additional attribute!", e);
			DBHelper.rollBack(conn);
			throw new DBException(e);
		} finally {
			DBHelper.close(conn);
		}
	}

	/**
	 * Speichert angegebenen globalen KategorieAttribute 
	 */
	public final static void saveAddCatAttribute(Connection conn, VKCAddAttribute attribute) throws DBException {
		if (attribute == null) {
			return;
		}

		PreparedStatement pStmt = null;

		try {
			if (DBHelper.existsEntry(conn, "addCatAttrs", "catAttrId", attribute.getAttrId())) {
				String str = "UPDATE addCatAttrs SET name = ?, description = ?, allowNull = ?, defaultValue = ? WHERE catAttrId = ?";
				pStmt = conn.prepareStatement(str);
				pStmt.setString(1, attribute.getName());
				pStmt.setString(2, attribute.getDescription());

				if (attribute.getAllowNull() != null && attribute.getAllowNull().intValue() == 1) {
					pStmt.setInt(3, attribute.getAllowNull().intValue());
				} else {
					pStmt.setNull(3, Types.INTEGER);
				}

				pStmt.setString(4, attribute.getDefaultValue());
				pStmt.setInt(5, attribute.getAttrId().intValue());
			} else {
				attribute.setAttrId(DBHelper.getNewId(conn, "addCatAttrs", "catAttrId"));

				String str = "INSERT INTO addCatAttrs ( catAttrId, name, description, allowNull, defaultValue ) values ( ?, ?, ?, ?, ? )";
				pStmt = conn.prepareStatement(str);
				pStmt.setInt(1, attribute.getAttrId().intValue());
				pStmt.setString(2, attribute.getName());
				pStmt.setString(3, attribute.getDescription());

				if (attribute.getAllowNull() != null && attribute.getAllowNull().intValue() == 1) {
					pStmt.setInt(4, attribute.getAllowNull().intValue());
				} else {
					pStmt.setNull(4, Types.INTEGER);
				}

				pStmt.setString(5, attribute.getDefaultValue());
			}

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

	/**
	 * Speichert angegebene globale DokumentAttribute 
	 */
	public final static void saveAddDocAttribute(List attributes) throws DBException {
		Connection conn = null;

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

			for (int i = 0; i < attributes.size(); i++) {
				VKCAddAttribute attribute = (VKCAddAttribute) attributes.get(i);
				saveAddDocAttribute(conn, attribute);
			}

			conn.commit();
		} catch (Exception e) {
			VKCLogManager.errorLog(VKCAddAttributeSerializer.class, "1", "Error occured while saving document additional attributes!", e);
			DBHelper.rollBack(conn);
			throw new DBException(e);
		} finally {
			DBHelper.close(conn);
		}
	}

	/**
	 * Speichert angegebenen globalen DokumentAttribute 
	 */
	public final static void saveAddDocAttribute(VKCAddAttribute attribute) throws DBException {
		Connection conn = null;

		try {
			conn = WebShare.database.getConnection();
			saveAddDocAttribute(conn, attribute);
			conn.commit();
		} catch (Exception e) {
			VKCLogManager.errorLog(VKCAddAttributeSerializer.class, "1", "Error occured while saving document additional attribute!", e);
			DBHelper.rollBack(conn);
			throw new DBException(e);
		} finally {
			DBHelper.close(conn);
		}
	}

	/**
	 * Speichert angegebenen globalen DokumentAttribute 
	 */
	public final static void saveAddDocAttribute(Connection conn, VKCAddAttribute attribute) throws DBException {
		if (attribute == null) {
			return;
		}

		PreparedStatement pStmt = null;

		try {

			if (DBHelper.existsEntry(conn, "addDocAttrs", "docAttrId", attribute.getAttrId())) {
				String str = "UPDATE addDocAttrs SET name = ?, description = ?, allowNull = ?, defaultValue = ? WHERE docAttrId = ?";
				pStmt = conn.prepareStatement(str);
				pStmt.setString(1, attribute.getName());
				pStmt.setString(2, attribute.getDescription());

				if (attribute.getAllowNull() != null && attribute.getAllowNull().intValue() == 1) {
					pStmt.setInt(3, attribute.getAllowNull().intValue());
				} else {
					pStmt.setNull(3, Types.INTEGER);
				}

				pStmt.setString(4, attribute.getDefaultValue());
				pStmt.setInt(5, attribute.getAttrId().intValue());
			} else {
				attribute.setAttrId(DBHelper.getNewId(conn, "addDocAttrs", "docAttrId"));

				String str = "INSERT INTO addDocAttrs ( docAttrId, name, description, allowNull, defaultValue ) values ( ?, ?, ?, ?, ? )";
				pStmt = conn.prepareStatement(str);
				pStmt.setInt(1, attribute.getAttrId().intValue());
				pStmt.setString(2, attribute.getName());
				pStmt.setString(3, attribute.getDescription());

				if (attribute.getAllowNull() != null && attribute.getAllowNull().intValue() == 1) {
					pStmt.setInt(4, attribute.getAllowNull().intValue());
				} else {
					pStmt.setNull(4, Types.INTEGER);
				}

				pStmt.setString(5, attribute.getDefaultValue());
			}

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