/*
 * $Id: VKCDocumentFavoritesSerializer.java,v 1.13 2006/03/27 22:08:16 marbobo Exp $
 * 
 * Copyright (c) Siemens AG 2008 
 *           (c) Universität Paderborn 2008 
 * Licensed under EUPL 1.1. 
 * 
 * $Log: VKCDocumentFavoritesSerializer.java,v $
 * Revision 1.13  2006/03/27 22:08:16  marbobo
 * + Bugfixes und Refactoring
 *
 * Revision 1.12  2006/03/27 15:24:16  marbobo
 * + Bugfix
 *
 * Revision 1.11  2006/03/23 11:39:20  marbobo
 * + Mögliche NPE abgefangen
 *
 * Revision 1.10  2004/12/09 12:39:29  lawern
 * vergessene PreparedStatements geschlossen
 *
 * Revision 1.9  2004/12/02 14:20:19  marbobo
 * + Statement auf PreparedStatement umgestellt
 *
 * Revision 1.8  2004/11/24 13:01:12  lawern
 * Global "conn.setAutoCommit( false );" enfernt, weil überflüssig.
 *
 * Revision 1.7  2004/11/24 09:25:54  lawern
 * Hibernate ist VKC-Geschichte!
 *
 * Revision 1.6  2004/11/18 15:56:54  jonyp
 * changed order of close-stmt to resultset/connect/session  in finally-blocks
 *
 * Revision 1.5  2004/10/21 23:49:18  marbobo
 * + CURRENT_TIMESTAMP ersetzt mit java-Anweisung
 *
 * Revision 1.4  2004/06/14 21:12:41  marbobo
 * - Überflüssige session.flush() und session.connection().commit() entfernt
 *
 * Revision 1.3  2004/05/11 15:53:24  jonyp
 * added functions deleteDocumentFavorite
 *
 * Revision 1.2  2004/05/07 10:15:44  jonyp
 * added countDoc/Cat function + ask for maxCount during saveFavorites
 *
 * Revision 1.1  2004/05/06 15:08:48  jonyp
 * first strike
 *
 */ 
package vkc.db.serializer;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.ArrayList;
import vkc.db.entities.VKCUserDocFavorite;
import vkc.db.helper.DBHelper;
import vkc.exception.DBException;
import vkc.view.WebShare;

/**
 * DOCUMENT ME!!!
 * 
 * @author vchris
 */
public class VKCDocumentFavoritesSerializer {
	public VKCDocumentFavoritesSerializer() {
		
	}
	
	public final static ArrayList retrieveDocumentFavorites(Integer userId) throws DBException {
		if (userId == null) {
			return null;
		}

		Connection conn = null;

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

	public final static ArrayList retrieveDocumentFavorites(Integer userId, Connection conn) throws DBException {
		if (userId == null || userId.intValue() <= 0) {
			return null;
		}

		PreparedStatement pStmt = null;
		ResultSet rs = null;
		ArrayList favoriteList = new ArrayList();

		try {
			pStmt = conn.prepareStatement("SELECT userId, documentId, created FROM userDocFavorites WHERE userid = ? ORDER BY created DESC");
			pStmt.setInt(1, userId.intValue());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				VKCUserDocFavorite udf = new VKCUserDocFavorite();
			
				udf.setUserId(new Integer(rs.getInt("userId")));
				udf.setDocumentId(new Integer(rs.getInt("documentId")));
				udf.setCreated(rs.getTimestamp("created"));
			
				favoriteList.add(udf);
			}
			
			return favoriteList;
		} catch (Exception e) {
			throw new DBException(e);
		} finally {
			DBHelper.close(rs);
			DBHelper.close(pStmt);
		}
	}

	/**
	 * Löscht die in der ArrayListe docs übergebenen Dokument-IDs aus der Tabelle
	 * USERDOCFAVORITES für den übergebenen User.
	 * 
	 * @param userId User, für den die Favoriten gelöscht werden sollen
	 * @param docs ArrayList mit Integer-Objekten der zu löschenen Favoriten
	 * @throws DBException
	 */
	public final static void deleteDocumentFavorites(Integer userId, ArrayList cats) throws DBException {
		Connection conn = null;

		try {
			conn = WebShare.database.getConnection();
			deleteDocumentFavorites(userId, cats, conn);
			conn.commit();
		} catch (Exception e) {
			DBHelper.rollBack(conn);
			throw new DBException(e);
		} finally {
			DBHelper.close(conn);
		}
	}
	
	/**
	 * Löscht Einträge aus der Tabelle USERDOCFAVORITES<br>
	 * <ul>
	 * <li>Werden die Parameter userId und docs angegeben, werden nur die Einträge die in docs übergeben
	 *     wurden für den übergebenen user gelöscht
	 * <li>Wurde nur der Parameter userId übergeben (docs == null), werden alle Favoriten des Users gelöscht
	 * <li>Wurde nur der Parameter docs übergeben (userId == null), werden alle Referenzen hierauf bei allen usern gelöscht
	 * 
	 * @param userId
	 * @param cats
	 * @param conn
	 * @throws DBException
	 */
	public final static void deleteDocumentFavorites(Integer userId, ArrayList docs, Connection conn) throws DBException {
		if ((userId == null || userId.intValue() <= 0) && (docs == null || docs.isEmpty())) {
			return;
		}
		
		PreparedStatement pStmt = null;
		
		try {
			// Es wurden zu löschende Kategorien übergeben
			if (docs != null && !docs.isEmpty()) {
				if (userId != null && userId.intValue() > 0) {
					pStmt = conn.prepareStatement("DELETE FROM userdocfavorites WHERE userid = ? AND documentid = ?");

					// Alle Referenzen auf die zu in der Arraylist übergebenen Dokumente sollen gelöscht werden
					for (int i = 0; i < docs.size(); i++) {
						Integer documentId = (Integer)docs.get(i);
					    
					    pStmt.setInt(1, userId.intValue());
						pStmt.setInt(2, documentId.intValue());
						pStmt.execute();
					}
				} else {
					pStmt = conn.prepareStatement("DELETE FROM userdocfavorites WHERE documentid = ?");
					
					// Alle Referenzen auf die zu in der Arraylist übergebenen Dokumente sollen gelöscht werden
					for (int i = 0; i < docs.size(); i++) {
						Integer documentId = (Integer)docs.get(i);
					    
						pStmt.setInt(1, documentId.intValue());
						pStmt.execute();
					}
				}
			} else {
				// Es wurde ein zu löschender User übergeben
				pStmt = conn.prepareStatement("DELETE FROM userdocfavorites WHERE userid = ?");
				pStmt.setInt(1, userId.intValue());
			    pStmt.execute();
			}
		} catch (Exception e) {	
			throw new DBException(e);
		} finally {	
			DBHelper.close(pStmt);
		}
	}
	
	public final static void deleteDocumentFavorite(Integer userId, Integer documentId) throws DBException {
		if (userId == null && documentId == null) {
			return;
		}

		Connection conn = null;

		try {
			conn = WebShare.database.getConnection();
			deleteDocumentFavorite(userId, documentId, conn);
			conn.commit();
		} catch (Exception e) {
			DBHelper.rollBack(conn);
			throw new DBException(e);
		} finally {
			DBHelper.close(conn);
		}
	}

	public final static void deleteDocumentFavorite(Integer userId, Integer documentId, Connection conn) throws DBException {
		if ((userId == null || userId.intValue() <= 0) && (documentId == null || documentId.intValue() <= 0)) {
			return;
		}
	
		PreparedStatement pStmt = null;
		ResultSet rs = null;
	
		try {
            if (userId != null && documentId == null) {
    			pStmt = conn.prepareStatement("DELETE FROM userdocfavorites WHERE userid = ?");
    			pStmt.setInt(1, userId.intValue());
				pStmt.execute();	
            } else if (userId == null && documentId != null) {
    			pStmt = conn.prepareStatement("DELETE FROM userdocfavorites WHERE documentId = ?");
    			pStmt.setInt(1, documentId.intValue());
				pStmt.execute();	
			} else if (userId != null && documentId != null) {
    			pStmt = conn.prepareStatement("DELETE FROM userdocfavorites WHERE documentId = ? AND userId = ?");
    			pStmt.setInt(1, documentId.intValue());
    			pStmt.setInt(2, userId.intValue());
				pStmt.execute();	
			}
		} catch (Exception e) {	
			throw new DBException(e);
		} finally {	
			DBHelper.close(rs);
			DBHelper.close(pStmt);
		}
	}
	
	public final static void addDocumentFavorites(Integer userId, Integer docId) throws DBException {
		if (userId == null || docId == null) {
			return;
		}
		
		Connection conn = null;

		try {
			conn = WebShare.database.getConnection();
			addDocumentFavorites(userId, docId, conn);
			conn.commit();
		} catch (Exception e) {
			DBHelper.rollBack(conn);
			throw new DBException(e);
		} finally {
			DBHelper.close(conn);
		}
	}
	
	public final static void addDocumentFavorites(Integer userId, Integer docId, Connection conn) throws DBException {
		if ((userId == null || userId.intValue() <= 0) || (docId == null || docId.intValue() <= 0)) {
			return;
		}
		
		PreparedStatement pStmt = null;
		
		try {
			pStmt = conn.prepareStatement("DELETE FROM userdocfavorites WHERE userid = ? AND documentid = ?");
			pStmt.setInt(1, userId.intValue());
			pStmt.setInt(2, docId.intValue());
			pStmt.execute();
			DBHelper.close(pStmt);


			pStmt = conn.prepareStatement("INSERT INTO userdocfavorites ( userId, documentId, created ) VALUES ( ?, ?, ? )");
			pStmt.setInt(1, userId.intValue());
			pStmt.setInt(2, docId.intValue());
			pStmt.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
			pStmt.execute();
		} catch (Exception e) {	
			throw new DBException(e);
		} finally {	
			DBHelper.close(pStmt);
		}
	}
}