/*
 * $Id: MailManager.java,v 1.43 2008/03/05 08:34:05 korowa Exp $
 * 
 * Copyright (c) Siemens AG 2008 
 *           (c) Universität Paderborn 2008 
 * Licensed under EUPL 1.1. 
 *
 * $Log: MailManager.java,v $
 * Revision 1.43  2008/03/05 08:34:05  korowa
 * bugfixing:der Userobjekt wird für die Rechteüberprüfung des Suchsvorgang benötigt
 *
 * Revision 1.42  2008/02/12 09:39:59  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.41  2007/08/08 12:14:39  marbobo
 * + License-Funktionalität komplett umgeschrieben
 * + Bugfix bzgl. Beschreibungsfelder von Entitäten
 * + Bugfix mailing
 *
 * Revision 1.40  2007/07/27 11:14:30  marbobo
 * + Ungenutzte Anweisungen entfernt
 *
 * Revision 1.39  2007/06/29 13:40:35  marbobo
 * + Bugfix (Vermeiden von doppel Benachrichtigungen)
 *
 * Revision 1.38  2006/12/22 10:35:23  lawern
 * Bei der Suche wird aus Performanz-Gründen nicht mehr das User-Objekt übergeben.
 *
 * Revision 1.37  2006/12/21 14:41:46  lawern
 * Paralleler Aufruf von populateUserDocMails sperrt den IndexSearcher zu lange.
 *
 * Revision 1.36  2006/12/21 13:02:54  lawern
 * WhatsRelated auch noch umgestellt.
 *
 * Revision 1.35  2006/12/21 12:08:41  lawern
 * Benachrichtigung zu registrierten Suchanfragen überarbeitet.
 *
 * Revision 1.34  2006/12/05 15:21:29  korowa
 * anpassung an JDK 1.4
 *
 * Revision 1.33  2006/12/05 14:15:43  korowa
 * retrieveUserForMailing() erweitert so, daß die Benachrichtigung auch für die Benutzer verschickt wird, die eine der früheren Version deises Dokuments registriert haben
 *
 * Revision 1.32  2006/07/12 12:44:10  marbobo
 * + Zur Überlaufvermeidung von Benachrichtigungsintervall Long-Wert verwendet
 *
 * Revision 1.31  2006/07/10 14:12:18  marbobo
 * + Für gelöschte Dokumente im Papierkorb keine Benachrichtigung verschicken (korrigiert)
 *
 * Revision 1.30  2006/06/13 01:53:19  marbobo
 * + Für Dokumente im Papierkorb keine Benachrichtigung senden
 *
 * Revision 1.29  2006/05/13 19:06:05  marbobo
 * + ProCoBa-Only-Benutzer werden nur noch bzgl. Dokumente und Kategorien benachrichtigt die auch nur unter ProCoBa liegen
 *
 * Revision 1.28  2006/03/23 11:40:04  marbobo
 * + Mögliche NPE abgefangen
 *
 * Revision 1.27  2005/09/13 00:44:19  marbobo
 * + prevent notification of documents which are member categories in trash (vervollständigt)
 *
 * Revision 1.26  2005/09/12 15:18:15  jonyp
 * prevent notification of documents which are member categories in trash
 *
 * Revision 1.25  2005/07/04 18:25:54  marbobo
 * + Bugfix - Falsches Casting
 *
 * Revision 1.24  2005/01/16 17:56:36  jonyp
 * deleted statements of userregisteredcategories
 *
 * Revision 1.23  2004/12/07 21:37:04  marbobo
 * + Statement auf PreparedStatement umgestellt
 *
 * Revision 1.22  2004/11/24 13:01:15  lawern
 * Global "conn.setAutoCommit( false );" enfernt, weil überflüssig.
 *
 * Revision 1.21  2004/11/24 09:25:57  lawern
 * Hibernate ist VKC-Geschichte!
 *
 * Revision 1.20  2004/11/18 19:38:23  marbobo
 * + changed order of close-stmt to resultset/connect/session  in finally-blocks
 *
 * Revision 1.19  2004/11/10 15:02:48  koppy
 * Projektweite Überarbeitung von Error-Logs, Stacktraces,..
 *
 * Revision 1.18  2004/11/09 14:35:36  marbobo
 * + Umstellung von authorId auf publisherId
 * + Hinzufügen von AuthorName für Dokumente
 * + Nötwendige Änderungen betreffend AuthorName und PublisherId
 *
 * Revision 1.17  2004/10/21 23:49:18  marbobo
 * + CURRENT_TIMESTAMP ersetzt mit java-Anweisung
 *
 * Revision 1.16  2004/10/18 18:08:28  marbobo
 * + Für inaktive (invalid) Benutzer keine Mails verschicken
 *
 * Revision 1.15  2004/09/03 00:42:43  marbobo
 * + Kategorieauswahl über ein Explorer für die Suche eingebaut
 *
 * Revision 1.14  2004/08/20 15:43:03  jonyp
 * refactoring due to change of VKCSearchQuerySerializer to VKCUserRegSearchQuerySerializer
 *
 * Revision 1.13  2004/08/20 12:48:27  koppy
 * Etwas überarbeitet + Behandlung von registrierten Queries
 *
 * Revision 1.12  2004/07/20 08:28:18  jonyp
 * added copyright info + cvs log
 *
 * Revision 1.11  2004/06/20 21:41:40  marbobo
 * - Unnötige flush-Anweisungen entfernt
 * + Bugfixes
 *
 * Revision 1.10  2004/04/17 21:00:46  marbobo
 * + Suche im beliebigem Zeitraum
 *
 * Revision 1.9  2004/03/30 13:34:18  teng
 * DBHelper.close(rs) in der Zeile 136 ergänzt.
 *
 * Revision 1.8  2004/03/25 01:52:48  marbobo
 * Suche nach Bestimmten Dokumenttypen gefixt (nochmal)
 *
 * Revision 1.7  2004/03/24 19:07:15  marbobo
 * Suche nach Bestimmten Dokumenttypen gefixt
 *
 * Revision 1.6  2004/03/16 16:58:39  teng
 * *** empty log message ***
 *
 * Revision 1.5  2004/03/16 16:19:59  teng
 * *** empty log message ***
 *
 * Revision 1.4  2004/03/15 13:40:03  bigmac
 * Anpassungen an PermissionManager und weitere Refactorings
 *
 * Revision 1.3  2004/03/12 12:56:58  bigmac
 * Anpassungen an VKCKeywordSerializer und VKCDynaAttributeSerializer
 * und generelle Refactorings durchgeführt
 *
 * Revision 1.2  2004/03/11 20:17:14  bigmac
 * Anpassungen an VKCUserSerializer
 *
 * Revision 1.1  2004/03/11 17:35:19  teng
 * *** empty log message ***
 *
 */
package vkc.messenger.mail;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.Map;
import java.util.Vector;

import vkc.db.entities.VKCCategory;
import vkc.db.entities.VKCDocument;
import vkc.db.entities.VKCSearchQuery;
import vkc.db.entities.VKCUser;
import vkc.db.entities.VKCUserDocMail;
import vkc.db.helper.DBHelper;
import vkc.db.serializer.VKCAclSerializer;
import vkc.db.serializer.VKCCategorySerializer;
import vkc.db.serializer.VKCDocumentSerializer;
import vkc.db.serializer.VKCUserRegSearchQuerySerializer;
import vkc.db.serializer.VKCSysParameterSerializer;
import vkc.db.serializer.VKCUserSerializer;
import vkc.exception.DBException;
import vkc.misc.VKCLogManager;
import vkc.search.Search;
import vkc.security.PermissionManager;
import vkc.security.VKCAcl;
import vkc.util.CategoryUtils;
import vkc.util.DateUtils;
import vkc.view.PageId;
import vkc.view.WebShare;
import vkc.view.bean.UserInfo;
import java.sql.Connection;

import org.apache.lucene.search.Hits;
import org.apache.lucene.search.Sort;

/**
 * @author teng
 *
 */
public class MailManager {
	private static MailManager manager = null;

	/**
	 * Return the only instance of MailManager in the system
	 * @return an instance of MailManager
	 */
	public static synchronized MailManager getMailManager() {
		if (manager == null) {
			manager = new MailManager();
		}
		
		return manager;
	}

	/**
	 * write <code>VKCUserDocMail</code>s in the database for mailing the users
	 * @param doc
	 * @return
	 */
	public synchronized int populateUserDocMails(VKCDocument doc) throws DBException {
	    Connection conn = null;

        try {
            conn = WebShare.database.getConnection();
            int count = populateUserDocMails(conn, doc);
			conn.commit();
			
			return count;
        } catch (Exception e) {
            DBHelper.rollBack(conn);
            VKCLogManager.errorLog(this, "", e.getMessage(), e);
            throw new DBException(e);
        } finally {
            DBHelper.close(conn);
        }
	}
	
	/**
	 * write <code>VKCUserDocMail</code>s in the database for mailing the users
	 * @param conn
	 * @param doc
	 * @return
	 */
	private int populateUserDocMails(Connection conn, VKCDocument doc) throws Exception {
	    if (doc.getCategoryId() == null) {
			return 0;
		}
	    
	    
		HashMap userId_userDocMailInfo = retrieveUserForMailing(conn, doc);

		if (userId_userDocMailInfo == null) {
		    return 0;
		}
		    
		PreparedStatement pStmt = null;
		PreparedStatement pStmt2 = null;
		PreparedStatement pStmt3 = null;
		Integer userId = null;
	    int userDocMailCount = 0;

        try {
	   	    pStmt = conn.prepareStatement("INSERT INTO USERDOCMAILS (UDMID, USERID, DOCUMENTID, SENDON, PROCESSED, RELDOCID, QUERYID, SUBJECTFORNOTIFICATION) " +
		            					  "VALUES (?,?,?,?,?,?,?,?)");
	   	    pStmt2 = conn.prepareStatement("SELECT userId FROM userdocmails WHERE userId = ? AND documentId = ?");
	   	    pStmt3 = conn.prepareStatement("SELECT userId, sendon, processed, wassendat FROM userdocmails WHERE userId = ? AND processed < " + VKCUserDocMail.PROCESSED2 + " ORDER BY sendon DESC");

	   	    for (Iterator kitr = userId_userDocMailInfo.keySet().iterator(); kitr.hasNext();) {
				userId = (Integer) kitr.next();
	
				//exclusive the userdocmail with the same userId and docId
				pStmt2.setInt(1, userId.intValue());
				pStmt2.setInt(2, doc.getDocumentId().intValue());
				ResultSet rs = pStmt2.executeQuery();
				
				if (rs.next()) {
					DBHelper.close(rs);
					continue;
				}
				
				DBHelper.close(rs);
	
				//####### compute the sendon time for this user #######
				//Check if there are mails in the table userdocmails for this user (exclusive the invalid userdocmails)
				pStmt3.setInt(1, userId.intValue());
				rs = pStmt3.executeQuery();
	
				Timestamp sendOn;
	
				if (!rs.next()) {
					//this means no mail was sent at the last period 
					sendOn = new Timestamp(System.currentTimeMillis());
				} else {
					if (rs.getInt("processed") == VKCUserDocMail.PROCESSED) {
						UserInfo user = VKCUserSerializer.retrieveUserInfo(userId, conn);

						//mail was sent, use the wassendat to compute the new sendon time 
						sendOn = new Timestamp(rs.getTimestamp("wassendat").getTime() + (60L * 1000L * user.getMessagingInterval().longValue()));
					} else { 
                        //mail isnot sent, use this sendon time
						sendOn = new Timestamp(rs.getTimestamp("sendon").getTime());
					}
				}
				
				DBHelper.close(rs);
	
				UserDocMailInfo udmi = (UserDocMailInfo) userId_userDocMailInfo.get(userId);
	
				pStmt.setInt(1, DBHelper.getNewId( conn, "USERDOCMAILS", "UDMID" ).intValue());
				pStmt.setInt(2, userId.intValue());
				pStmt.setInt(3, doc.getDocumentId().intValue());
				pStmt.setTimestamp(4, sendOn);
				pStmt.setInt(5, VKCUserDocMail.NOT_PROCESSED);
				
				if (udmi.relDocId != null) {
				    pStmt.setInt(6, udmi.relDocId.intValue());
				} else {
				    pStmt.setNull(6, Types.INTEGER);
				}
				
				if (udmi.relQueryId != null) {
				    pStmt.setInt(7, udmi.relQueryId.intValue());
				} else {
				    pStmt.setNull(7, Types.INTEGER);
				}
                pStmt.setString(8, udmi.subject);
				
				pStmt.execute();
				userDocMailCount++;
			}
			
			return userDocMailCount;
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(pStmt);
            DBHelper.close(pStmt2);
            DBHelper.close(pStmt3);
        }
	}

	/**
	 * retrieve the users for sending document notification, who are registed for
	 * 1. keywords of this document
	 * 2. category or it's subcategories where this document published 
	 * @param conn
	 * @param doc
	 * @return HashMap contains key:Integer userId, value:Integer Id of the similar documents (it's null if no similar document found)
	 * @throws Exception
	 */
	private HashMap retrieveUserForMailing(Connection conn, VKCDocument doc) throws Exception {
		PreparedStatement pStmt = null;
		PreparedStatement pStmt2 = null;
		PreparedStatement pStmt4 = null;
		ResultSet rs = null;
		ResultSet ucrs = null;
		ResultSet rs1 = null;
		ResultSet ukrs = null;
		HashMap userIdDocMailInfos = new HashMap();
		Integer userId;
		VKCUser user;
		int publisherId = doc.getPublisherId().intValue();
		
		if (WebShare.isTrashEnabled()) {
			HashSet trashCategoryIds = new HashSet(CategoryUtils.getAllSubCategoryIdsRecursiv(WebShare.getTrashCategory().getCategoryId()));
			trashCategoryIds.add(WebShare.getTrashCategory().getCategoryId());
			
			if (trashCategoryIds.contains(doc.getCategoryId())) {
				return userIdDocMailInfos;
			}
		}

		try {
			VKCAcl acl = VKCAclSerializer.getAclByCategoryId(doc.getCategoryId(), conn);
			VKCCategory docCategory = VKCCategorySerializer.retrieveCategory(doc.getCategoryId(), conn);
			Integer rootProjectId = new Integer(VKCSysParameterSerializer.getProjectsRootCategoryId());
			Integer rootFunctionalId = new Integer(VKCSysParameterSerializer.getFunctionalViewsRootCategoryId());
			HashSet projectFunctionalIds = new HashSet(CategoryUtils.getAllSubCategoryIdsRecursiv(rootProjectId));
			projectFunctionalIds.addAll(CategoryUtils.getAllSubCategoryIdsRecursiv(rootFunctionalId));

 //Reihenfolge gibt Priorität an => DokumentenBenachrichtigung>Kategoriebenachrichtigung>Suchanfragenbenachrichtigung          
//          +++++++++ prüfen ob frühere Versionen von diesem Dokument von jemandem registriert sind
             
            pStmt2 = conn.prepareStatement("SELECT userId, subjectForNotification FROM userregistereddocuments WHERE documentId = ?");
            Collection allVersions = doc.getAllVersions();
            Iterator iterOfVersions = allVersions.iterator();
            
            while (iterOfVersions.hasNext()) {
                VKCDocument preDoc = (VKCDocument)iterOfVersions.next();
                pStmt2.setInt(1, preDoc.getDocumentId().intValue());
                rs1 = pStmt2.executeQuery();

                while (rs1.next()) {
                    userId = new Integer(rs1.getInt("userId"));
                    String subject = rs1.getString("subjectForNotification");
                    if(subject==null||subject.toLowerCase().equals("null")){
                        subject="";
                    }

                    if (userId.intValue() == publisherId || userIdDocMailInfos.containsKey(userId)) {
                        continue;
                    }

                    user = VKCUserSerializer.retrieveUser(userId, conn);

                    //check permission for this user
                    if (!userIdDocMailInfos.containsKey(userId)
                       && PermissionManager.hasPermission(acl, user, 'r')
                       && user.isUserValid()
                       && user.getMessagingEnabled()) {
                        if (!user.isAllowManageSystemViewType() 
                           && (PageId.PROJECT_VIEW.equals(user.getSystemViewTypeId()) || PageId.FUNCTIONAL_VIEW.equals(user.getSystemViewTypeId())) 
                           && VKCSysParameterSerializer.isProjectManagementEnabled()
                           && !projectFunctionalIds.contains(doc.getCategoryId())) {
                            continue;
                        } 

                        userIdDocMailInfos.put(userId, new UserDocMailInfo(subject));
                    }
                }               
                
                DBHelper.close(rs1);
                rs1 = null;
            }

            
            
            
            
            
			//	find out, which users have been registered for the given category
			pStmt = conn.prepareStatement("SELECT userId, subjectForNotification FROM userregisteredcategories WHERE categoryId = ?");
			pStmt.setInt(1, doc.getCategoryId().intValue());
			ucrs = pStmt.executeQuery();

			while (ucrs.next()) {
				userId = new Integer(ucrs.getInt("userId"));
				user = VKCUserSerializer.retrieveUser(userId, conn);
                String subject = ucrs.getString("subjectForNotification");
                if(subject==null||subject.toLowerCase().equals("null")){
                    subject="";
                }
                if ( userIdDocMailInfos.containsKey(userId)) {
                    continue;
                }
				if (PermissionManager.hasPermission(acl, user, 'r') //check permission for this user
				   && user.getMessagingEnabled()
				   && user.isUserValid()
				   && userId.intValue() != publisherId) { //exclusion author
					if (!user.isAllowManageSystemViewType() 
					   && (PageId.PROJECT_VIEW.equals(user.getSystemViewTypeId()) || PageId.FUNCTIONAL_VIEW.equals(user.getSystemViewTypeId())) 
					   && VKCSysParameterSerializer.isProjectManagementEnabled()
					   && !projectFunctionalIds.contains(doc.getCategoryId())) {
						continue;
					} 

					userIdDocMailInfos.put(userId, new UserDocMailInfo(subject));
				}
			}

			DBHelper.close(ucrs);
			ucrs = null;

			//+++++++++ find out all parent categories
 
			pStmt2 = conn.prepareStatement("SELECT userId, subjectForNotification FROM userregisteredcategories WHERE categoryId = ? AND inclsubdirs = 1");

			while (docCategory.getParentCategoryId() != null) {
				pStmt2.setInt(1, docCategory.getParentCategoryId().intValue());
			    rs1 = pStmt2.executeQuery();
               

				while (rs1.next()) {
					userId = new Integer(rs1.getInt("userId"));
					String subject = rs1.getString("subjectForNotification");
					if(subject==null||subject.toLowerCase().equals("null")){
	                    subject="";
	                }
					if (userId.intValue() == publisherId || userIdDocMailInfos.containsKey(userId)) {
						continue;
					}

					user = VKCUserSerializer.retrieveUser(userId, conn);

					//check permission for this user
					if (!userIdDocMailInfos.containsKey(userId)
				       && PermissionManager.hasPermission(acl, user, 'r')
				       && user.isUserValid()
				       && user.getMessagingEnabled()) {
						if (!user.isAllowManageSystemViewType() 
						   && (PageId.PROJECT_VIEW.equals(user.getSystemViewTypeId()) || PageId.FUNCTIONAL_VIEW.equals(user.getSystemViewTypeId())) 
						   && VKCSysParameterSerializer.isProjectManagementEnabled()
						   && !projectFunctionalIds.contains(doc.getCategoryId())) {
							continue;
						} 

						userIdDocMailInfos.put(userId, new UserDocMailInfo(subject));
					}
				}

				docCategory = VKCCategorySerializer.retrieveCategory(docCategory.getParentCategoryId(), conn);
				DBHelper.close(rs1);
				rs1 = null;
			}
			

			//++++++++++
			//check all registered searchqueries
			Map searchQueries = VKCUserRegSearchQuerySerializer.retrieveAllRegisteredSearchQueries();
            HashSet docId = new HashSet(1);
            docId.add(doc.getDocumentId());
			
            for (Iterator iter = searchQueries.values().iterator(); iter.hasNext();) {
			    VKCSearchQuery query = (VKCSearchQuery) iter.next();
                
                if (query.getUserId().intValue() == publisherId) {
                    continue;
                }
                
                if (query.isEmpty()) {
                    continue;
                }
                
                user = VKCUserSerializer.retrieveUser(query.getUserId(), conn);
                
                if (!user.isUserValid()) {
                    continue;
                }
                
                if (!user.getMessagingEnabled()) {
                    continue;
                }
                
                if (!PermissionManager.hasPermission(acl, user, 'r')) {
                    continue;
                }
                
				if (!user.isAllowManageSystemViewType() 
				   && (PageId.PROJECT_VIEW.equals(user.getSystemViewTypeId()) || PageId.FUNCTIONAL_VIEW.equals(user.getSystemViewTypeId())) 
				   && VKCSysParameterSerializer.isProjectManagementEnabled()
				   && !projectFunctionalIds.contains(doc.getCategoryId())) {
					continue;
				} 

				// Suche durchführen
                
                Hits results = Search.search(
        				query.getSearchQuery(),
        				query.getFrom(),
        				query.getUntil(),
        				query.getSelectedResultLimitation(),
        				user,//null, //
        				query.getCategoryId(),
                        docId,
                        null,
                        new Sort());
                
                if (results.length() > 0) {
                    UserDocMailInfo udmi = new UserDocMailInfo(query.getSubjectForNotification());
                    udmi.relQueryId = query.getQueryId();
                    userIdDocMailInfos.put(query.getUserId(), udmi);

                }
                
                Search.searchFinished();
            }
			

			// ######## pruefen ob dieses Dokument aehnlich wie die Dokumente,
            // die von Benutzern registriert werden.
			Hits relDocuments = null;

			try {
                relDocuments = Search.whatsRelated(doc, null, null, "*", null,
                        null, null, null, new Sort());
            } catch (Exception ex) {
                VKCLogManager.errorLog(this, "", ex.getMessage(), ex);
            }

            if (relDocuments != null) {
                Integer relDocId = null;
                int score;

                pStmt4 = conn.prepareStatement("SELECT DISTINCT userId, subjectForNotification FROM USERREGISTEREDDOCUMENTS WHERE DocumentId = ? AND threshold <= ?");

                int relDocumentsLength = relDocuments.length();

                for (int i = 0; i < relDocumentsLength; i++) {

                    score = (int) (relDocuments.score(i) * 100);

                    try {
                        relDocId = new Integer(relDocuments.doc(i).get("documentId"));
                    } catch (NumberFormatException nfe) {
                    }

                    if (relDocId == null) {
                        continue;
                    }

                    pStmt4.setInt(1, relDocId.intValue());
                    pStmt4.setInt(2, score);
                    rs = pStmt4.executeQuery();

                    if (rs != null) {
                        while (rs.next()) {
                            int id = rs.getInt("userId");
                            String subject = rs.getString("subjectForNotification");
                            if(subject==null||subject.toLowerCase().equals("null")){
                                subject="";
                            }

                            if (id != publisherId) {
                                userId = new Integer(id);

                                if (userIdDocMailInfos.containsKey(userId)) {
                                    UserDocMailInfo udmi = (UserDocMailInfo) userIdDocMailInfos.get(userId);
                                    udmi.relDocId = relDocId;
                                } else {
                                    user = VKCUserSerializer.retrieveUser(userId, conn);

                                    if (PermissionManager.hasPermission(acl, user, 'r')
                                     && user.isUserValid()
                                     && user.getMessagingEnabled()) {
                                        if (!user.isAllowManageSystemViewType()
                                         && (PageId.PROJECT_VIEW.equals(user.getSystemViewTypeId()) || PageId.FUNCTIONAL_VIEW.equals(user.getSystemViewTypeId()))
                                         && VKCSysParameterSerializer.isProjectManagementEnabled()
                                         && !projectFunctionalIds.contains(doc.getCategoryId())) {
                                            continue;
                                        }

                                        UserDocMailInfo udmi = new UserDocMailInfo(subject);
                                        udmi.relDocId = relDocId;

                                        userIdDocMailInfos.put(userId, udmi);
                                    }
                                }
                            }
                        }

                        DBHelper.close(rs);
                        rs = null;
                    }
                }
                
			}
            
            Search.searchFinished();
            
			return userIdDocMailInfos;
		} catch (Exception e) {
			VKCLogManager.errorLog(this, "", e.getMessage(), e);
			throw new DBException(e);
		} finally {
			DBHelper.close(ucrs);
			DBHelper.close(rs1);
			DBHelper.close(ukrs);
			DBHelper.close(rs);
			DBHelper.close(pStmt);
			DBHelper.close(pStmt2);
			DBHelper.close(pStmt4);
		}
	}

	/**
	  * Get all <code>VKCUserDocMail</code>s in the database
	  *
	  * @return Map of <code>VKCUserDocMail</code>s<p>
	  * 
	  * @throws DBException
	  */
	public Collection getUserDocMails() throws DBException {
		Connection conn = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
	    Collection docMails = new Vector();

		try {
			conn = WebShare.database.getConnection();
			pStmt = conn.prepareStatement("SELECT udmId, userId, documentId, sendon, processed, wassendat, relDocId, QUERYID, subjectForNotification FROM userdocmails ");
			rs = pStmt.executeQuery();

			VKCDocument doc = null;

			while (rs.next()) {
				VKCUserDocMail docMail = new VKCUserDocMail();

				Integer userId = new Integer(rs.getInt("userId"));
				int udmId = rs.getInt("udmId");
				int docId = rs.getInt("documentId");
				 
				docMail.setUdmId(new Integer(udmId));
				docMail.setUser(VKCUserSerializer.retrieveUser(userId, conn));
                docMail.setSubject(rs.getString("subjectForNotification"));

				try {
					doc = VKCDocumentSerializer.retrieveDocument(new Integer(docId), conn);
				} catch (Exception ex) {
				    VKCLogManager.errorLog(this, "", ex.getMessage(), ex);
				}
				
				// remove VKCUserDocMail from database if this doc does not exist
				if (doc == null) {
					try {
                        //here delete this docMail
                        deleteUserDocMail(docMail.getUdmId());
                    } catch (Exception e1) {
                        VKCLogManager.errorLog(this, "", e1.getMessage(), e1);
                    }

					continue;
				}
				
				//Für gelöschte Dokumente im Papierkorb keine Benachrichtigung verschicken
				if (doc.isInTrash()) {
					continue;
				}

				docMail.setDocument(doc);

				docMail.setSendOn(rs.getTimestamp("sendOn"));
				docMail.setProcessed(new Integer(rs.getInt("processed")));
				docMail.setWasSendAt(rs.getTimestamp("wassendat"));

				int relDocId = rs.getInt("relDocId");

				if (!rs.wasNull()) {
					docMail.setRelDoc(VKCDocumentSerializer.retrieveDocument(new Integer(relDocId), conn));
				}
				
				int queryId = rs.getInt("QUERYID");
				
				if (!rs.wasNull()) {
				    docMail.setRelQuery(VKCUserRegSearchQuerySerializer.retrieveSearchQuery(conn, new Integer(queryId)));
				}

				docMails.add(docMail);
			}

			return docMails;
		} catch (Exception e) {
			VKCLogManager.errorLog(this, "", e.getMessage(), e);
			throw new DBException(e);
		} finally {
			DBHelper.close(rs);
			DBHelper.close(pStmt);
			DBHelper.close(conn);
		}
	}

	/**
	 * Set  a <code>VKCUserDocMail</code> to be processed (processed = 1). This means this mail has been sent.
	 * 
	 * @param udmId Id of the userdocmail
	 *
	 * @throws DBException
	 */
	public void setUserDocMailProcessed(Integer udmId) throws DBException {
		Connection conn = null;
		PreparedStatement pStmt = null;

		try {
			conn = WebShare.database.getConnection();
			pStmt = conn.prepareStatement("UPDATE userdocmails SET processed = " + VKCUserDocMail.PROCESSED + ", wassendat = ? WHERE udmId = ?");
			pStmt.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
			pStmt.setInt(2, udmId.intValue());
			pStmt.execute();
			conn.commit();
		} catch (Exception e) {
			VKCLogManager.errorLog(this, "", e.getMessage(), e);
			DBHelper.rollBack(conn);
			throw new DBException(e);
		} finally {
			DBHelper.close(pStmt);
			DBHelper.close(conn);
		}
	}
	
	/**
	 * Set <code>VKCUserDocMail</code> to be invalid (processed = 2). This means this <code>VKCUserDocMail</code> could be deleted if it is retrieved from database for the next time.
	 * @param udmId Id of <code>VKCUserDocMail</code>
	 * @throws DBException
	 */
	public void setUserDocMailInvalid(Integer udmId) throws DBException {
		Connection conn = null;
		PreparedStatement pStmt = null;

		try {
			conn = WebShare.database.getConnection();
			pStmt = conn.prepareStatement("UPDATE userdocmails SET processed = " + VKCUserDocMail.PROCESSED2 + " WHERE udmId = ?");
			pStmt.setInt(1, udmId.intValue());
			pStmt.execute();
			conn.commit();
		} catch (Exception e) {
			VKCLogManager.errorLog(this, "", e.getMessage(), e);
			DBHelper.rollBack(conn);
			throw new DBException(e);
		} finally {
			DBHelper.close(pStmt);
			DBHelper.close(conn);
		}
	}

	/**
	 * deletes a given <code>VKCUserDocMail</code> from the table
	 *
	 * @param udmId Id of a <code>VKCUserDocMail</code> 
	 *
	 * @throws DBException
	 */
	public void deleteUserDocMail(Integer udmId) throws DBException {
		Connection conn = null;
		PreparedStatement pStmt = null;

		try {
			conn = WebShare.database.getConnection();
			pStmt = conn.prepareStatement("DELETE FROM USERDOCMAILS WHERE udmId = ?");
			pStmt.setInt(1, udmId.intValue());
			pStmt.executeUpdate();
			conn.commit();
		} catch (Exception e) {
			DBHelper.rollBack(conn);
			VKCLogManager.errorLog(this, "1", "Error occured while deleting userdocmail ID=" + udmId, e);
			throw new DBException(e);
		} finally {
			DBHelper.close(pStmt);
			DBHelper.close(conn);
		}
	}
	
	/**
	 * Gibt alle Dokumente, die geloescht werden sollen, zurueck
	 *
	 * @return
	 *
	 * @throws DBException
	 */
	public Collection getDocumentsForDelete() throws DBException {
		int delete_document_period = new Long(VKCSysParameterSerializer.retrieveValue("MAIL", "DeleteDocumentPeriod")).intValue();
		Connection conn = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		Collection docs = new ArrayList();

		try {
			conn = WebShare.database.getConnection();
			pStmt = conn.prepareStatement("SELECT documentId FROM Documents WHERE expires is not null and flag = " + VKCDocument.FLAG_EXPIRED + " AND expires < ?" );

			Date d = DateUtils.addDays(new Date(), -delete_document_period);
			Timestamp ts = new Timestamp(d.getTime());
			pStmt.setTimestamp(1, ts);

			rs = pStmt.executeQuery();

			while (rs.next()) {
				VKCDocument doc = VKCDocumentSerializer.retrieveDocument(new Integer(rs.getInt("documentId")), conn);
				docs.add(doc);
			}

			return docs;
		} catch (Exception e) {
			VKCLogManager.errorLog(this, "", e.getMessage(), e);
			throw new DBException(e);
		} finally {
			DBHelper.close(rs);
			DBHelper.close(pStmt);
			DBHelper.close(conn);
		}
	}
	
	private class UserDocMailInfo {
        Integer relQueryId = null;
	    Integer relDocId = null;
        String subject   = null;
        public UserDocMailInfo(){
            
        }
        public UserDocMailInfo(String subject){
            this.subject=subject;
        }
	}
}