/*
 * $Id: VKCUserRegSearchQuerySerializer.java,v 1.13 2008/03/03 15:59:02 korowa Exp $
 *
 * Copyright (c) Siemens AG 2008 
 *           (c) Universität Paderborn 2008 
 * Licensed under EUPL 1.1. 
 * 
 * Created on 17.08.2004
 *
 * $Log: VKCUserRegSearchQuerySerializer.java,v $
 * Revision 1.13  2008/03/03 15:59:02  korowa
 * +bugfixing
 *
 * Revision 1.12  2008/03/03 14:15:18  korowa
 * Anpassung des Serializer an Änderungen in VKCQuery(+Attribute: "subjectForNotification")
 *
 * Revision 1.11  2008/02/12 09:39:48  korowa
 * + Eine Möglichkeit für den Benutzer hinzugefügt die Emailsart auszuwählen (HTML oder Plaintext)
 * + Betreff für den Messenger der als Emailsubject bei Benachrichtigung übers erscheinen von VKC Dokumenten verschickt wird.
 *
 * Revision 1.10  2006/03/23 11:39:19  marbobo
 * + Mögliche NPE abgefangen
 *
 * Revision 1.9  2005/09/05 13:54:26  marbobo
 * +Suche nach Metadaten-Attribute (Erstellungsdatum) für Dokumente implementiert
 *
 * Revision 1.8  2004/12/09 21:07:40  marbobo
 * + kleine Optimierungen und Bugfixes
 * +an einigen Stellen Rreihenfolge der close-Anweisungen vereinheitlicht
 *
 * Revision 1.7  2004/12/05 22:57:47  marbobo
 * + Statement auf PreparedStatement umgestellt
 *
 * Revision 1.6  2004/11/24 13:01:12  lawern
 * Global "conn.setAutoCommit( false );" enfernt, weil überflüssig.
 *
 * Revision 1.5  2004/11/24 09:25:53  lawern
 * Hibernate ist VKC-Geschichte!
 *
 * Revision 1.4  2004/11/18 19:38:23  marbobo
 * + changed order of close-stmt to resultset/connect/session  in finally-blocks
 *
 * Revision 1.3  2004/11/10 15:02:44  koppy
 * Projektweite Überarbeitung von Error-Logs, Stacktraces,..
 *
 * Revision 1.2  2004/10/13 09:34:24  marbobo
 * change queries for userregisteredsearchqueries to userregsearchqueries due to renaming of this table
 *
 * Revision 1.1  2004/08/20 15:40:41  jonyp
 * renamed from originally VKCSearchQuerySerializer
 *
 * Revision 1.1  2004/08/20 12:40:08  koppy
 * Serializer für VKCSearchQuery
 *
 */
package vkc.db.serializer;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

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

/**
 * This class represents
 * @author Andreas Koop
 */
public class VKCUserRegSearchQuerySerializer {
    /**
     * Retrieves all registered search queries
     * with queryId as key
     * @return
     * @throws DBException
     */
    public static final Map retrieveAllRegisteredSearchQueries() throws DBException {
        Connection conn = null;
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        
        try {
            conn = WebShare.database.getConnection();
            pStmt = conn.prepareStatement("SELECT QUERYID, USERID, QUERYFIELD, SUBJECTFORNOTIFICATION FROM USERREGSEARCHQUERIES");
            rs = pStmt.executeQuery();
    
    		HashMap queryMaps = new HashMap();
    		
    		while (rs.next()) {
    		    Integer queryId = new Integer(rs.getInt("QUERYID"));
                String subject = rs.getString("SUBJECTFORNOTIFICATION");
    		    
    		    Map map = (Map)queryMaps.get(queryId);

    		    if (map == null) {
    		        map = new UserHashMap(new Integer(rs.getInt("USERID")));
    		        queryMaps.put(queryId, map);
    		    }
    			
    		    String value = rs.getString("QUERYFIELD");
    		    int normalIndex = value.indexOf(":(");
    		    int rangeIndex = value.indexOf(":[");
    		    String key = "";
    		    
    		    if (normalIndex != -1 && rangeIndex != -1) {
    		    	if (normalIndex < rangeIndex) {
    	    			key = value.substring(0, value.indexOf("(") + 1);
    		    	} else {
    	    			key = value.substring(0, value.indexOf("[") + 1);
    		    	}
    		    } else if (normalIndex != -1 && rangeIndex == -1) {
	    			key = value.substring(0, value.indexOf("(") + 1);
    		    } else if (normalIndex == -1 && rangeIndex != -1) {
	    			key = value.substring(0, value.indexOf("[") + 1);
    		    }
    		    
    			map.put(key, value);
                map.put("subjectForNotification",subject);
    		}
    		
    		HashMap queries = new HashMap();
    		
    		for (Iterator it = queryMaps.keySet().iterator(); it.hasNext();) {
    		    Integer queryId = (Integer)it.next();
    		    
    		    UserHashMap elements = (UserHashMap)queryMaps.get(queryId);
    		    
    		    VKCSearchQuery query = new VKCSearchQuery();
                query.setSubjectForNotification((String) elements.get("subjectForNotification"));
                elements.remove("subjectForNotification");
    		    query.setQueryId(queryId);
    		    query.setUserId(elements.getUserId());
    			query.setQueryElements(elements);
                
    			
    			queries.put(queryId, query);
    		}
    		
    		queryMaps = null;
    		
    		return queries;
        } catch (Exception e) {
            VKCLogManager.errorLog(VKCUserRegSearchQuerySerializer.class, "", e.getMessage(), e);
            throw new DBException(e);
        } finally {
            DBHelper.close(rs);
            DBHelper.close(pStmt);
            DBHelper.close(conn);
        }
    }
    
    /**
     * Retrieve the searchquery for the given queryId
     * 
     * @return
     * @throws DBException
     */
    public static final VKCSearchQuery retrieveSearchQuery(Connection conn, Integer queryId) throws DBException {
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        
        try {
            pStmt = conn.prepareStatement("SELECT USERID, QUERYFIELD, SUBJECTFORNOTIFICATION FROM USERREGSEARCHQUERIES WHERE QUERYID = ?");
            pStmt.setInt(1, queryId.intValue());
            rs = pStmt.executeQuery();

    		HashMap map = new HashMap();
    		
    		int userId = -1;
            String subject = "";
    		while (rs.next()) {
    		    if (userId == -1) {
    		        userId = rs.getInt("USERID");
    		    }
    		    subject = rs.getString("SUBJECTFORNOTIFICATION");
    			String value = rs.getString("QUERYFIELD");
    		    int normalIndex = value.indexOf(":(");
    		    int rangeIndex = value.indexOf(":[");
    		    String key = "";
    		    
    		    if (normalIndex != -1 && rangeIndex != -1) {
    		    	if (normalIndex < rangeIndex) {
    	    			key = value.substring(0, value.indexOf("(") + 1);
    		    	} else {
    	    			key = value.substring(0, value.indexOf("[") + 1);
    		    	}
    		    } else if (normalIndex != -1 && rangeIndex == -1) {
	    			key = value.substring(0, value.indexOf("(") + 1);
    		    } else if (normalIndex == -1 && rangeIndex != -1) {
	    			key = value.substring(0, value.indexOf("[") + 1);
    		    }
    		    
    			map.put(key, value);
    		}
    		    
    	    VKCSearchQuery query = new VKCSearchQuery();
    	    query.setQueryId(queryId);
    	    query.setUserId(new Integer(userId));
    		query.setQueryElements(map);
            query.setSubjectForNotification(subject);
    		
    		return query;
        } catch (Exception e) {
            VKCLogManager.errorLog(VKCUserRegSearchQuerySerializer.class, "", e.getMessage(), e);
            throw new DBException(e);
        } finally {
            DBHelper.close(rs);
            DBHelper.close(pStmt);
        }
    }
    
    /**
     * Retrieve the searchquery for the given queryId
     * 
     * @return
     * @throws DBException
     */
    public static final VKCSearchQuery retrieveSearchQuery(Integer queryId) throws DBException {
        Connection conn = null;
        
        try {
            conn = WebShare.database.getConnection();
            
            return retrieveSearchQuery(conn,queryId);
        } catch (Exception e) {
            VKCLogManager.errorLog(VKCUserRegSearchQuerySerializer.class, "", e.getMessage(), e);
            throw new DBException(e);
        } finally {
            DBHelper.close(conn);
        }
    }
    
    /**
     * Speichert eine Query für den angegebenen Benutzer
     *
     * @param query VKCSearchQuery-Objekt 
     * @param userId ID des VKC-Benutzers 
     */
    public final static void saveSearchQuery(VKCSearchQuery query, Integer userId) throws DBException {
    	Connection conn = null;
    	
    	try {
    		conn = WebShare.database.getConnection();
    		
    		
    		// Erstmal evtl. vorhandene Searchquery löschen
    		if (query.getQueryId() != null) {
    		    DBHelper.deleteEntry(conn, "USERREGSEARCHQUERIES", "QUERYID", query.getQueryId());
    		}
    
    		VKCUserRegSearchQuerySerializer.saveSearchQuery(query, userId, conn);
    		conn.commit();
    	} catch (Exception e) {
    		DBHelper.rollBack(conn);
    		VKCLogManager.errorLog(VKCUserRegSearchQuerySerializer.class, "", e.getMessage(), e);
    		throw new DBException(e);
    	} finally {
    		DBHelper.close(conn);
    	}
    }

    /**
     * Speichert eine Query für den angegebenen Benutzer
     *
     * @param conn DB-Connection
     * @param query VKCSearchQuery-Objekt 
     * @param userId ID des VKC-Benutzers 
     * @throws SQLException
     * @throws HibernateException
     */
    public final static void saveSearchQuery(VKCSearchQuery query, Integer userId, Connection conn) throws DBException {
        if (query == null || userId == null || "".equals(query.getSearchQuery())) {
    	    return;
        }
    	
        PreparedStatement pStmt = null;
        
        try {
            pStmt = conn.prepareStatement("INSERT INTO USERREGSEARCHQUERIES (URSQID, QUERYID, USERID, QUERYFIELD, subjectForNotification) VALUES (?,?,?,?,?)");
    	    
    		Integer queryId = DBHelper.getNewId(conn, WebShare.database.getDbUrl(), "dummy", "USERREGSEARCHQUERIES", "QUERYID");
    		query.setQueryId(queryId);
    		
    		Iterator it = query.getQueryElements().values().iterator();
    
    		while (it.hasNext()) {
    			Integer URSQID = DBHelper.getNewId(conn, "USERREGSEARCHQUERIES", "URSQID");
    			
    			pStmt.setInt(1, URSQID.intValue());
    			pStmt.setInt(2, queryId.intValue());
    			pStmt.setInt(3, userId.intValue());
    			pStmt.setString(4, (String) it.next());
                pStmt.setString(5, query.getSubjectForNotification());
    			
    			pStmt.execute();
    		}
        } catch (Exception e) {
    		VKCLogManager.errorLog(VKCUserRegSearchQuerySerializer.class, "", e.getMessage(), e);
    		throw new DBException(e);
    	} finally {
            DBHelper.close(pStmt);
        }
    }

    /**
     * Speichert Queries für den angegebenen Benutzer
     *
     * @param queries Sammlung von VKCSearchQuery-Objekten 
     * @param userId ID des VKC-Benutzers 
     */
    public final static void saveSearchQueries(Collection queries, Integer userId) throws DBException {
    	Connection conn = null;

    	try {
    		conn = WebShare.database.getConnection();
    		
    		
    		// Erstmal evtl. vorhandene Searchqueries löschen
    		DBHelper.deleteEntry(conn, "USERREGSEARCHQUERIES", "USERID", userId);
    
    		for (Iterator iter = queries.iterator(); iter.hasNext();) {
                VKCSearchQuery query = (VKCSearchQuery) iter.next();
                
                saveSearchQuery(query, userId, conn);
    		}
    		
    		conn.commit();
    	} catch (Exception e) {
    		DBHelper.rollBack(conn);
    		VKCLogManager.errorLog(VKCUserRegSearchQuerySerializer.class, "", e.getMessage(), e);
    		throw new DBException(e);
    	} finally {
    		DBHelper.close(conn);
    	}
    }

    /**
     * Lädt das alle registrierten Searchqueries eines Benutzers
     * als Map mit dem Schlüssel "queryId"
     *
     * @param userId ID des VKC-Benutzers
     */
    public static final HashMap retrieveSearchQueries(Integer userId) throws DBException {
    	if (userId == null) {
    		return new HashMap();
    	}
    	
    	Connection conn = null;
    	PreparedStatement pStmt = null;
    	ResultSet rs = null;
    
    	try {
    	    conn = WebShare.database.getConnection();
    		pStmt = conn.prepareStatement("SELECT * FROM USERREGSEARCHQUERIES WHERE USERID = ?");
    		pStmt.setInt(1, userId.intValue());
    		rs = pStmt.executeQuery();
    
    		HashMap queryMaps = new HashMap();
    		
    		while (rs.next()) {
    		    Integer queryId = new Integer(rs.getInt("QUERYID"));
    		    
    		    Map map = (Map)queryMaps.get(queryId);
    		    
    		    if (map == null) {
    		        map = new HashMap();
    		        queryMaps.put(queryId, map);
    		    }
    			String subject = rs.getString("SUBJECTFORNOTIFICATION");
    		    String value = rs.getString("QUERYFIELD");
    		    int normalIndex = value.indexOf(":(");
    		    int rangeIndex = value.indexOf(":[");
    		    String key = "";
    		    
    		    if (normalIndex != -1 && rangeIndex != -1) {
    		    	if (normalIndex < rangeIndex) {
    	    			key = value.substring(0, value.indexOf("(") + 1);
    		    	} else {
    	    			key = value.substring(0, value.indexOf("[") + 1);
    		    	}
    		    } else if (normalIndex != -1 && rangeIndex == -1) {
	    			key = value.substring(0, value.indexOf("(") + 1);
    		    } else if (normalIndex == -1 && rangeIndex != -1) {
	    			key = value.substring(0, value.indexOf("[") + 1);
    		    }
    		    
    			map.put(key, value);
                map.put("subjectForNotification", subject);
    		}
    		
    		HashMap queries = new HashMap();
    		
    		for (Iterator it = queryMaps.keySet().iterator(); it.hasNext();) {
    		    Integer queryId = (Integer)it.next();
    		    
    		    VKCSearchQuery query = new VKCSearchQuery();
                HashMap elements = (HashMap)queryMaps.get(queryId);
                query.setSubjectForNotification((String) (elements.get("subjectForNotification")));
                elements.remove("subjectForNotification");
    		    query.setQueryId(queryId);
    			query.setQueryElements(elements);
    			queries.put(queryId, query);
    		}
    		
    		queryMaps = null;
    		
    		return queries;
    	} catch (Exception e) {
    	    VKCLogManager.errorLog(VKCUserRegSearchQuerySerializer.class, "", e.getMessage(), e);
    	    throw new DBException(e);
    	} finally {
    		DBHelper.close(rs);
    		DBHelper.close(pStmt);
    		DBHelper.close(conn);
    	}
    }

	public final static void deleteSearchQuery(Integer userId, Integer queryId) throws DBException {
		if (userId == null && queryId == null) {
			return;
		}

		Connection conn = null;

		try {
			conn = WebShare.database.getConnection();
			deleteSearchQuery(userId, queryId, conn);
			conn.commit();
		} catch (Exception e) {
			DBHelper.rollBack(conn);
			throw new DBException(e);
		} finally {
			DBHelper.close(conn);
		}
	}
	
	public final static void deleteSearchQuery(Integer userId, Integer queryId, Connection conn) throws DBException {
		if (userId == null && queryId == null) {
			return;
		}
		
		PreparedStatement pStmt = null;
	
		try {
			if (userId != null && queryId == null) {
				pStmt = conn.prepareStatement("DELETE FROM userRegSearchQueries WHERE userId = ?");
				pStmt.setInt(1, userId.intValue());
				pStmt.execute(); 
			} else if (userId == null && queryId != null) {
				pStmt = conn.prepareStatement("DELETE FROM userRegSearchQueries WHERE queryId = ?");
				pStmt.setInt(1, queryId.intValue());
				pStmt.execute(); 
			} else if (userId != null && queryId != null) {
				pStmt = conn.prepareStatement("DELETE FROM userRegSearchQueries WHERE userId = ? AND queryId = ?");
				pStmt.setInt(1, userId.intValue());
				pStmt.setInt(2, queryId.intValue());
				pStmt.execute(); 
			}
		} catch (Exception e) {
			throw new DBException(e);
		} finally {
		    DBHelper.close(pStmt);
		}
	}

    private static class UserHashMap extends HashMap{
	    private Integer userId;
	    
	    public UserHashMap(Integer userId){
	        this.userId = userId;
	    }
        
	    public Integer getUserId() {
            return userId;
        }
        
	    public void setUserId(Integer userId) {
            this.userId = userId;
        }
	}
}