/*
 * $Id: VKCDocumentSerializer.java,v 1.106 2008/02/12 09:39:50 korowa Exp $
 *
 * Copyright (c) Siemens AG 2008 
 *           (c) Universität Paderborn 2008 
 * Licensed under EUPL 1.1. 
 *
 * $Log: VKCDocumentSerializer.java,v $
 * Revision 1.106  2008/02/12 09:39:50  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.105  2007/10/30 13:45:02  marbobo
 * + removed unnecessary statements
 *
 * Revision 1.104  2007/09/05 09:42:42  korowa
 * Bei Importieren von Dokumenten werden jetzt Benachrichtigungen verschickt
 *
 * Revision 1.103  2007/08/28 12:15:52  korowa
 * Export- und Importfunktionen hinzugefügt
 *
 * Revision 1.102  2007/08/08 12:14:40  marbobo
 * + License-Funktionalität komplett umgeschrieben
 * + Bugfix bzgl. Beschreibungsfelder von Entitäten
 * + Bugfix mailing
 *
 * Revision 1.101  2007/03/01 15:02:56  korowa
 * Beschreibungen zur Kategorien und Dokumenten und News werden jetzt in eigener Tabelle NOTES über VKCNoteSerializer gespeichert und sind nicht begrenzt an Zeichenanzahl
 *
 * Revision 1.100  2006/10/09 12:19:26  marbobo
 * + Beim erhöhen der Zugriffsanzahl wird nicht mehr saveDocument aufgerufen
 */

package vkc.db.serializer;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
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.List;
import java.util.Set;


import vkc.db.VKCDatabase;

import vkc.db.entities.VKCCategory;
import vkc.db.entities.VKCDocument;
import vkc.db.entities.VKCEntity;
import vkc.db.entities.VKCUser;
import vkc.db.helper.DBHelper;
import vkc.exception.DBException;
import vkc.messenger.mail.UserDocMailsPopulator;
import vkc.misc.VKCLogManager;
import vkc.search.Search;
import vkc.security.PermissionManager;
import vkc.security.VKCAcl;
import vkc.server.archive.DataStoreException;
import vkc.util.DateUtils;
import vkc.util.ProjectUtils;
import vkc.util.StringUtils;
import vkc.view.WebShare;
import vkc.view.bean.RegisteredDocument;
import vkc.view.bean.VKCQuotaInfo;
import vkc.util.CategoryUtils;

public class VKCDocumentSerializer {
    private VKCDocumentSerializer() {
    }

    /**
     * @return Collection of VKCDocument-Objects
     */
    public final static Collection getDocumentsByQuery(Connection conn, String query, Object[] params) throws DBException {
        Collection docs = new ArrayList();
        PreparedStatement pStmt = null;
        ResultSet rs = null;

        try {
            pStmt = conn.prepareStatement(query);
            DBHelper.setElements(pStmt, params);
            rs = pStmt.executeQuery();

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

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

    /**
     * Gibt eine Sammlung von VKCDocument-Objekten zurück anhand einer
     * DB-Anfrage
     *
     * <p>
     * SELECT <b>documentId</b> FROM ...
     * </p>
     *
     * @param query Anfrage z.B. "SELECT documentId FROM Documents WHERE FLAG =
     *        1"
     *
     * @return Sammlung(ArrayList) von VKCDocument-Objekte
     *
     * @throws DBException
     */
    public final static Collection getDocumentsByQuery(String query, Object[] params) throws DBException {
        Connection conn = null;

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

    /**
     * Gibt eine Sammlung(ArrayList) aller vorhandenen Dokumente  eines
     * VKCUsers(userId) zurück (Verwendung beim "Papierkorb")
     *
     * @param user Referenz auf ein VKCUser-Objekt
     *
     * @return Sammlung(ArrayList) aller VKCDocument-Objekte eines Users
     *
     * @throws DBException
     */
    public final static Collection getDocuments(VKCUser user) throws DBException {
        String query = "SELECT documentId FROM Documents WHERE publisherId = ?";
        Object[] params = {user.getUserId()};
        
        return getDocumentsByQuery(query, params);
    }

    public final static Collection getDocuments(Integer userId) throws DBException {
        String query = "SELECT documentId FROM Documents WHERE publisherId = ?";
        Object[] params = {userId};
        
        return getDocumentsByQuery(query, params);
    }

	/**
	 * Gibt eine Sammlung(ArrayList) aller vorhandenen abgelaufenen Dokumente
	 * eines VKCUsers zurück
	 */
	public final static Collection getExpiredDocuments(VKCUser user) throws DBException {
        String query = "SELECT documentId FROM Documents WHERE publisherId = ? AND FLAG = " + VKCDocument.FLAG_EXPIRED;
        Object[] params = {user.getUserId()};

        return getDocumentsByQuery(query, params);
	}

	/**
	 * Gibt eine Sammlung(ArrayList) aller noch zu publizierenden Dokumente
	 * eines VKCUsers unter einer Kategorie zurück: Wenn der Benutzer Adminrechte
	 * hat, dann werden alle noch zu publizierenden Dokumente zurück gegeben. Ausserdem wenn
	 * categoryId NULL ist wird die Kategorie nicht berücksichtigt
	 */
	public final static Collection getToBePublishedDocuments(VKCUser user, Integer categoryId) throws DBException {
		if (user == null) {
		    return new ArrayList();
		}
		
		if (categoryId == null) {
		    if (user.hasAdminPrivileges()) {
		        String query = " SELECT Documents.documentId FROM Documents " +
                			   " WHERE Documents.flag = " + VKCDocument.FLAG_TOBEPUBLISHED +
                			   " order by Documents.published desc" ;

		        return getDocumentsByQuery(query, null);		    
		    }
		    
		    String query = " SELECT Documents.documentId FROM Documents " +
            			   " WHERE Documents.publisherId = ?" + 
            			   " AND Documents.flag = " + VKCDocument.FLAG_TOBEPUBLISHED +
            			   " order by Documents.published desc";
	        Object[] params = {user.getUserId()};

		    return getDocumentsByQuery(query, params);		    
		}
		
		if (user.hasAdminPrivileges()) {
		    String query = " SELECT Documents.documentId FROM CategoryDocuments, Documents " +
            			   " WHERE CategoryDocuments.categoryId = ?" + 
            			   " AND CategoryDocuments.documentId = Documents.documentId" +
            			   " AND Documents.flag = " + VKCDocument.FLAG_TOBEPUBLISHED +
            			   " order by Documents.published desc";
			Object[] params = {categoryId};

			return getDocumentsByQuery(query, params);		    
		}

	    String query = " SELECT Documents.documentId FROM CategoryDocuments, Documents " +
        			   " WHERE CategoryDocuments.categoryId = ?" + 
        			   " AND CategoryDocuments.documentId = Documents.documentId" +
        			   " AND Documents.publisherId = ?" + 
        			   " AND Documents.flag = " + VKCDocument.FLAG_TOBEPUBLISHED +
        			   " order by Documents.published desc";
	    Object[] params = {categoryId, user.getUserId()};

	    return getDocumentsByQuery(query , params);
	}

	/**
	 * Gibt eine Sammlung(ArrayList) aller Dokumente unter einer Kategorie zurück
	 * die jetzt publiziert werden sollen
	 */
	public final static Collection getToBePublishedDocuments() throws DBException {
	    String query = " SELECT Documents.documentId FROM Documents " +
        			   " WHERE Documents.flag = " + VKCDocument.FLAG_TOBEPUBLISHED +
        			   " AND Documents.published <= ?" +
        			   " ORDER by Documents.published desc";
	    Object[] params = {new Timestamp(System.currentTimeMillis())};
	    
	    return getDocumentsByQuery(query, params);
	}

    /**
     * Gibt eine Sammlung(ArrayList) aller vorhandenen Dokument-IDs zurück
     * (Verwendung bei der Suchmaschine)
     *
     * @return Sammlung(ArrayList) aller VKCDocument-IDs
     *
     * @throws DBException
     */
    public final static Collection getAllDocumentIds() throws DBException {
        Connection conn = null;
        PreparedStatement pStmt = null;
        ResultSet rs = null;

        String query = "SELECT documents.documentId " +
            "  FROM categoryDocuments, documents" +
            " WHERE categoryDocuments.documentId = documents.documentId" +
            "   AND documents.flag = " + VKCDocument.FLAG_PUBLISHED;

        Collection docs = new ArrayList();

        try {
            conn = WebShare.database.getConnection();
            pStmt = conn.prepareStatement(query);
            rs = pStmt.executeQuery();

            while (rs.next()) {
                Integer doc = new Integer(rs.getInt("documentId"));
                docs.add(doc);
            }

            return docs;
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(rs);
            DBHelper.close(pStmt);
            DBHelper.close(conn);
        }
    }
    
    /**
     * Gibt eine Sammlung(ArrayList) aller vorhandenen Dokument-Objekte zurück
     * (Verwendung bei der Klassifikation)
     *
     * @return Sammlung(ArrayList) aller VKCDocument-IDs
     *
     * @throws DBException
     */
    public final static Collection getAllDocuments() throws DBException {
        Connection conn = null;
        PreparedStatement pStmt = null;
        ResultSet rs = null;

        String query = "SELECT documents.documentId " +
            "  FROM categoryDocuments, documents" +
            " WHERE categoryDocuments.documentId = documents.documentId" +
            "   AND documents.flag = " + VKCDocument.FLAG_PUBLISHED;

        Collection docs = new ArrayList();

        try {
            conn = WebShare.database.getConnection();
            pStmt = conn.prepareStatement(query);
            rs = pStmt.executeQuery();

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

            return docs;
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(rs);
            DBHelper.close(pStmt);
            DBHelper.close(conn);
        }
    }

    /**
     * Liefert eine benutzerfreundliche Repräsentation des
     * Dokuments mit der gegebenen Dokumenten-ID
     */
    public final static String getDocumentRepresentation(Integer documentId) throws DBException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet result = null;
        PreparedStatement pstmt2 = null;
        ResultSet result2 = null;
        String name = "";
        
        try {
            conn = WebShare.database.getConnection();
            pstmt = conn.prepareStatement("SELECT NAME, TITLE FROM DOCUMENTS WHERE DOCUMENTID = ?");
            pstmt.setInt(1, documentId.intValue());
            
            pstmt2 = conn.prepareStatement("SELECT CATEGORYID FROM CATEGORYDOCUMENTS WHERE DOCUMENTID = ?");
            pstmt2.setInt(1, documentId.intValue());

            result = pstmt.executeQuery();
            
            if (result.next()) {
                name = result.getString("NAME");
                
                if (result.wasNull()) {
                    name = result.getString("TITLE");
                    
                    if (result.wasNull()) {
                        name = "";
                    }
                }
            }
            
            result2 = pstmt2.executeQuery();
            
            if (result2.next()) {
                return VKCCategorySerializer.getCategoryAbsPath(new Integer(result2.getInt("CATEGORYID")), conn).getAbsolutePathString() + name;
            }
            
            return name;
        } catch (Exception e) {
            VKCLogManager.errorLog(VKCDocumentSerializer.class, "", "Exception obtaining documentrepresentation for docId= " + documentId, e);
            throw new DBException(e);
        } finally {
            DBHelper.close(result);
            DBHelper.close(result2);
            DBHelper.close(pstmt);
            DBHelper.close(pstmt2);
            DBHelper.close(conn);
        }
    }
    
    /**
     * Liefert Beschreibungsfeld des angegebenen Dokumenten
     */
    public final static String getDocumentDescription(Integer documentId) throws DBException {
        Connection conn = null;

        try {
            conn = WebShare.database.getConnection();
            
            return getDocumentDescription(documentId, conn);
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(conn);
        }
    }
    
    /**
     * Liefert Beschreibungsfeld des angegebenen Dokumenten
     */
    public final static String getDocumentDescription(Integer documentId, Connection conn) throws DBException {
        if (documentId == null) {
            return "";
        }
        
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        String description = "";

        try {
            pStmt = conn.prepareStatement("SELECT noteId FROM Documents WHERE documentId = ?");
            pStmt.setInt(1, documentId.intValue());
            rs = pStmt.executeQuery();

            if (rs.next()) {
                description = VKCNoteSerializer.retrieveNote(new Integer(rs.getInt("noteId")));
                
                if (rs.wasNull() || description == null) {
                	description = "";
                }
            }

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

    /**
     * Liefert Zugriffsanzahl des angegebenen Dokumenten
     */
    public final static Integer getAccessCount(Integer documentId) throws DBException {
        Connection conn = null;

        try {
            conn = WebShare.database.getConnection();
            
            return getAccessCount(documentId, conn);
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(conn);
        }
    }
    
    /**
     * Liefert Zugriffsanzahl des angegebenen Dokumenten
     */
    public final static Integer getAccessCount(Integer documentId, Connection conn) throws DBException {
        if (documentId == null) {
            return new Integer(0);
        }
        
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        Integer accesscount = new Integer(0);

        try {
            pStmt = conn.prepareStatement("SELECT accesscounter FROM Documents WHERE documentId = ?");
            pStmt.setInt(1, documentId.intValue());
            rs = pStmt.executeQuery();

            if (rs.next()) {
                accesscount = new Integer(rs.getInt("accesscounter"));
                
                if (rs.wasNull()) {
                    accesscount = new Integer(0);
                }
            }

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

    /**
     * Gibt eine Menge von Dokument-Ids und die gesamt Anzahl der
     * Zugriffs der Dokumente an
     */
    public final static HashMap getAccessCounterIds() throws DBException {
        Connection conn = null;

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

    /**
     * Gibt eine Menge von Dokument-Ids und die gesamt Anzahl der
     * Zugriffs der Dokumente an
     */
    public final static HashMap getAccessCounterIds(Connection conn) throws DBException {
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        HashMap accessCounterIds = new HashMap();

        try {
            pStmt = conn.prepareStatement("SELECT documentId, accesscounter FROM Documents");
            rs = pStmt.executeQuery();

            while (rs.next()) {
                accessCounterIds.put(new Integer(rs.getInt("documentId")), new Integer(rs.getInt("accesscounter")));
            }

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

    /**
     * Liefert letztes Zugriffszeitpunkt des angegebenen Dokumenten
     */
    public final static Timestamp getLastAccessed(Integer documentId) throws DBException {
        Connection conn = null;

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

    /**
     * Liefert letztes Zugriffszeitpunkt des angegebenen Dokumenten
     */
    public final static Timestamp getLastAccessed(Integer documentId, Connection conn) throws DBException {
        if (documentId == null) {
            return null;
        }
        
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        Timestamp lastAccessed = null;

        try {
            pStmt = conn.prepareStatement("SELECT lastaccessed FROM Documents WHERE documentId = ?");
            pStmt.setInt(1, documentId.intValue());
            rs = pStmt.executeQuery();

            if (rs.next()) {
                lastAccessed = rs.getTimestamp("lastaccessed");
                
                if (rs.wasNull()) {
                    lastAccessed = null;
                }
            }

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

    public final static void setFlag(Integer documentId, Integer flag, boolean recursiv) throws DBException {
        Connection conn = null;

        try {
            conn = WebShare.database.getConnection();
            setFlag(documentId, flag, recursiv, conn);
            conn.commit();
        } catch (Exception e) {
            VKCLogManager.errorLog(VKCCategorySerializer.class, "", e.getMessage(), e);
            DBHelper.rollBack(conn);
            throw new DBException(e);
        } finally {
            DBHelper.close(conn);
        }
    }

    public final static void setFlag(Integer documentId, Integer flag, boolean recursiv, Connection conn) throws DBException {
        if (documentId == null) {
            return;
        }
        
        PreparedStatement pStmt = null;
        PreparedStatement pStmt1 = null;
        ResultSet rs = null;

        try {
            pStmt = conn.prepareStatement("UPDATE documents SET flag = ? WHERE documentId = ?");
            pStmt.setInt(1, flag.intValue());
            pStmt.setInt(2, documentId.intValue());
  
            pStmt.execute();
            
            if (recursiv) {
            	int curDocId = 0;
            	
            	pStmt1 = conn.prepareStatement("SELECT preDocId FROM documents WHERE documentId = ?");
            	pStmt1.setInt(1, documentId.intValue());
            	rs = pStmt1.executeQuery();
            	
            	if (rs.next()) {
            		curDocId = rs.getInt("preDocId");
            		
            		if (!rs.wasNull()) {
                		while (curDocId != 0) {
                            pStmt.setInt(1, flag.intValue());
                            pStmt.setInt(2, curDocId);
                            pStmt.execute();
                            
                            DBHelper.close(rs);
                        	pStmt1.setInt(1, curDocId);
                        	rs = pStmt1.executeQuery();
                        	
                        	if (rs.next()) {
                        		curDocId = rs.getInt("preDocId");
                        		
                        		if (rs.wasNull()) {
                            		curDocId = 0;
                        		}
                        	} else {
                        		curDocId = 0;
                        	}
                		}
            		}
            	}
            }
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
        	DBHelper.close(rs);
            DBHelper.close(pStmt);
            DBHelper.close(pStmt1);
        }
    }
    
    /**
     * Liefert Id Benutzer der zuletzt das angegebene Dokumente geöffnet hat
     */
    public final static Integer getLastAccessorId(Integer documentId) throws DBException {
        Connection conn = null;

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

    /**
     * Liefert Id Benutzer der zuletzt das angegebene Dokumente geöffnet hat
     */
    public final static Integer getLastAccessorId(Integer documentId, Connection conn) throws DBException {
        if (documentId == null) {
            return VKCEntity.ID_UNDEFINED;
        }
        
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        Integer lastAccessorId = VKCEntity.ID_UNDEFINED;

        try {
            pStmt = conn.prepareStatement("SELECT lastaccessorid FROM Documents WHERE documentId = ?");
            pStmt.setInt(1, documentId.intValue());
            rs = pStmt.executeQuery();

            if (rs.next()) {
                lastAccessorId = new Integer(rs.getInt("lastaccessorid"));
                
                if (rs.wasNull()) {
                    lastAccessorId = VKCEntity.ID_UNDEFINED;
                }
            }

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

    /**
     * Gibt eine Menge von Dokument-Ids der Dokumente an, 
     * auf die innerhalb des angegebenen Zeitraums zugegriffen wurde
     */
    public final static Set getLastAccessedDocumentIds(Date from, Date until) throws DBException {
        Connection conn = null;

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

    /**
     * Gibt eine Menge von Dokument-Ids der Dokumente an, 
     * auf die innerhalb des angegebenen Zeitraums zugegriffen wurde
     */
    public final static Set getLastAccessedDocumentIds(Date from, Date until, Connection conn) throws DBException {
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        List params = new ArrayList();

        try {
            StringBuffer query = new StringBuffer();
            query.append("  SELECT Documents.documentId AS documentId");
            query.append("    FROM Documents, CategoryDocuments, Categories ");
            query.append("   WHERE Documents.FLAG = ");
            query.append(VKCDocument.FLAG_PUBLISHED);
            query.append("     AND Documents.documentId = CategoryDocuments.documentId ");
            query.append("     AND CategoryDocuments.categoryId = Categories.categoryId ");
            
            if (from != null) {
                query.append("     AND (Documents.lastaccessed >= ? OR Documents.lastaccessed IS NULL)");
                params.add(new Timestamp(from.getTime()));
            }
            
            if (until != null) {
                query.append("     AND (Documents.lastaccessed <= ? OR Documents.lastaccessed IS NULL)");
                params.add(new Timestamp(until.getTime()));
            }
            
            query.append(" ORDER BY Documents.lastaccessed DESC ");

            Set lastIds = new HashSet();

            pStmt = conn.prepareStatement(query.toString());
            DBHelper.setElements(pStmt, params.toArray());
            rs = pStmt.executeQuery();

            while (rs.next()) {
                lastIds.add(new Integer(rs.getInt("documentId")));
            }

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

    /**
     * Liefert letztes Änderungszeitpunkt des angegebenen Dokumenten
     */
    public final static Timestamp getLastChanged(Integer documentId) throws DBException {
        Connection conn = null;

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

    /**
     * Liefert letztes Änderungszeitpunkt des angegebenen Dokumenten
     */
    public final static Timestamp getLastChanged(Integer documentId, Connection conn) throws DBException {
        if (documentId == null) {
            return null;
        }
        
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        Timestamp lastChanged = null;

        try {
            pStmt = conn.prepareStatement("SELECT lastchanged FROM Documents WHERE documentId = ?");
            pStmt.setInt(1, documentId.intValue());
            rs = pStmt.executeQuery();

            if (rs.next()) {
                lastChanged = rs.getTimestamp("lastchanged");
                
                if (rs.wasNull()) {
                    lastChanged = null;
                }
            }

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

    /**
     * Liefert Id Benutzer der zuletzt das angegebene Dokumente geändert hat
     */
    public final static Integer getLastChangerId(Integer documentId) throws DBException {
        Connection conn = null;

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

    /**
     * Liefert Id Benutzer der zuletzt das angegebene Dokumente geändert hat
     */
    public final static Integer getLastChangerId(Integer documentId, Connection conn) throws DBException {
        if (documentId == null) {
            return VKCEntity.ID_UNDEFINED;
        }
        
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        Integer lastChangerId = VKCEntity.ID_UNDEFINED;

        try {
            pStmt = conn.prepareStatement("SELECT lastchangerid FROM Documents WHERE documentId = ?");
            pStmt.setInt(1, documentId.intValue());
            rs = pStmt.executeQuery();

            if (rs.next()) {
                lastChangerId = new Integer(rs.getInt("lastchangerid"));
                
                if (rs.wasNull()) {
                    lastChangerId = VKCEntity.ID_UNDEFINED;
                }
            }

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

    /**
     * Gibt eine Sammlung(ArrayList) aller in einem angegebenem Zeitraum
     * publizierten Dokumente auf die der Benutzer Leserechte hat
     */
    public final static List getPublishedDocuments(VKCUser user, Date from, Date until) throws DBException {
        Connection conn = null;

        try {
            conn = WebShare.database.getConnection();
            
            return getPublishedDocuments(user, from, until, conn);
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(conn);
        }
    }

    /**
     * Gibt eine Sammlung(ArrayList) aller in einem angegebenem Zeitraum
     * publizierten Dokumente auf die der Benutzer Leserechte hat
     */
    public final static List getPublishedDocuments(VKCUser user, Date from, Date until, Connection conn) throws DBException {
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        List params = new ArrayList();

        try {
            StringBuffer query = new StringBuffer();
            query.append("  SELECT Documents.documentId AS documentId, Categories.aclId AS aclId");
            query.append("    FROM Documents, CategoryDocuments, Categories ");
            query.append("   WHERE Documents.FLAG = ");
            query.append(VKCDocument.FLAG_PUBLISHED);
            query.append("     AND Documents.documentId = CategoryDocuments.documentId ");
            query.append("     AND CategoryDocuments.categoryId = Categories.categoryId ");
            
            if (from != null) {
                query.append("     AND (Documents.published >= ? OR Documents.published IS NULL)");
                params.add(new Timestamp(from.getTime()));
            }
            
            if (until != null) {
                query.append("     AND (Documents.published <= ? OR Documents.published IS NULL)");
                params.add(new Timestamp(until.getTime()));
            }
            
            query.append(" ORDER BY Documents.published DESC ");

            List latestAdds = new ArrayList();

            pStmt = conn.prepareStatement(query.toString());
            DBHelper.setElements(pStmt, params.toArray());
            rs = pStmt.executeQuery();

            int ranking = 1;

            while (rs.next()) {
                VKCAcl catAcl = VKCAclSerializer.retrieveAcl(new Integer(rs.getInt("aclId")), conn);
                
                if (PermissionManager.hasPermission(catAcl, user, 'r')) {
                    VKCDocument doc = retrieveDocument(new Integer(rs.getInt("documentId")), conn);
                    doc.setRanking(ranking++);
                    latestAdds.add(doc);
                }
            }

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

    /**
     * Löscht die Referenz eines Dokumentes auf eine Kategorie
     *
     * @param documentId ID des zu löschenden Dokuments
     */
    public final static void deleteCategoryDocument(Integer documentId) throws DBException {
        Connection conn = null;

        try {
            conn = WebShare.database.getConnection();
            deleteCategoryDocument(documentId, conn);
            conn.commit();
        } catch (Exception e) {
            DBHelper.rollBack(conn);
            VKCLogManager.errorLog(VKCDocumentSerializer.class, "1", "Error occured while deleting document ID=" + documentId, e);
            throw new DBException(e);
        } finally {
            DBHelper.close(conn);
        }
    }

    /**
     * Löscht die Referenz eines Dokumentes auf eine Kategorie
     *
     * @param documentId ID des zu löschenden Dokuments
     */
    public final static void deleteCategoryDocument(Integer documentId, Connection conn) throws DBException {
		if (documentId == null || documentId.intValue() <= 0) {
			return;
		}

		PreparedStatement pStmt = null;
		
        try {
	        pStmt = conn.prepareStatement("DELETE FROM CategoryDocuments WHERE documentId = ?");
	        pStmt.setInt(1, documentId.intValue());
	        pStmt.execute();
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(pStmt);
        }
    }

    public final static void deleteDocument(VKCUser user, Integer documentId, boolean definite, Integer userTrashCategoryId) throws DBException {
        Connection conn = null;

        try {
            conn = WebShare.database.getConnection();
            deleteDocument(user, documentId, definite, userTrashCategoryId, true, conn);
            conn.commit();
        } catch (Exception e) {
            DBHelper.rollBack(conn);
            VKCLogManager.errorLog(VKCDocumentSerializer.class, "1", "Error occured while deleting document ID=" + documentId, e);
            throw new DBException(e);
        } finally {
            DBHelper.close(conn);
        }
    }

    public final static void deleteDocument(VKCUser user,
                                            Integer documentId,
            								boolean definite,
            								Integer userTrashCategoryId,
            								boolean updateClassifyIndex, 
            								Connection conn) throws DBException {
		if (documentId == null || documentId.intValue() <= 0) {
			return;
		}
		
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		
        try {
            VKCDocument document = retrieveDocument(documentId, conn);

            if (document == null) {
            	return;
            }

            // Dokument aus dem Suchindex entfernen
            Search.removeDocument(documentId, true);

            Integer categoryId = document.getCategoryId();

            if (definite) {
                do {
                    // Definiv löschen
                    DBHelper.close(pStmt);
                    pStmt = conn.prepareStatement("DELETE FROM Userdocmails WHERE documentId = ?");
                    pStmt.setInt(1, documentId.intValue());
                    pStmt.execute();
                    DBHelper.close(pStmt);
                    pStmt = conn.prepareStatement("DELETE FROM CategoryDocuments WHERE documentId = ? AND categoryId = ?");
                    pStmt.setInt(1, documentId.intValue());
                    pStmt.setInt(2, categoryId.intValue());
                    pStmt.execute();
                    DBHelper.close(pStmt);
                    pStmt = conn.prepareStatement("DELETE FROM DocumentKeywords WHERE documentId = ?");
                    pStmt.setInt(1, documentId.intValue());
                    pStmt.execute();
                    DBHelper.close(pStmt);
                    pStmt = conn.prepareStatement("DELETE FROM UserRegisteredDocuments WHERE documentId = ?");
                    pStmt.setInt(1, documentId.intValue());
                    pStmt.execute();
                    DBHelper.close(pStmt);
                    pStmt = conn.prepareStatement("DELETE FROM AddDocAttrValues WHERE documentId = ?");
                    pStmt.setInt(1, documentId.intValue());
                    pStmt.execute();
    				VKCUserDocHistorySerializer.deleteDocumentHistory(null, documentId, conn);
                    VKCDocumentFavoritesSerializer.deleteDocumentFavorite(null, documentId, conn);			

                    // find out every additionaldocumentformat and delete it from the datastore
                    DBHelper.close(rs);
                    DBHelper.close(pStmt);
                    pStmt = conn.prepareStatement("SELECT archiveId FROM AdditionalDocumentFormats WHERE documentId = ?");
                    pStmt.setInt(1, documentId.intValue());
                    rs = pStmt.executeQuery();

                    while (rs.next()) {
                        String archiveId = rs.getString( "archiveId");

                        try {
                            WebShare.archive.delete(archiveId);
                        } catch (DataStoreException e) {
                            VKCLogManager.errorLog(VKCDocumentSerializer.class, "", e.getMessage(), e);
                        }
                    }

                    DBHelper.close(pStmt);
                    pStmt = conn.prepareStatement("DELETE FROM AdditionalDocumentFormats WHERE documentId = ?");
                    pStmt.setInt(1, documentId.intValue());
                    pStmt.execute();

    				if (document.getDynaAttrsId() != null) {
                        DBHelper.close(pStmt);
                        pStmt = conn.prepareStatement("DELETE FROM DynAttributes WHERE dynAttrId = ?");
                        pStmt.setInt(1, document.getDynaAttrsId().intValue());
                        pStmt.execute();
    				}

    				//deletes the corresponding conversion status entry if one exists
                    DBHelper.close(pStmt);
                    pStmt = conn.prepareStatement("DELETE FROM ConversionStati WHERE documentId = ?");
                    pStmt.setInt(1, documentId.intValue());
                    pStmt.execute();
                    
                    //Beschreibung löschen
                    VKCNoteSerializer.deleteNote(documentId, "DOCUMENTS", conn);

                    DBHelper.close(pStmt);
                    pStmt = conn.prepareStatement("DELETE FROM Documents WHERE documentId = ?");
                    pStmt.setInt(1, documentId.intValue());
                    pStmt.execute();

                    if (document.getPreDocId() != null) {
                    	document = retrieveDocument(document.getPreDocId(), conn);
                        documentId = document != null ? document.getDocumentId() : null;
                    } else {
                        documentId = null;
                    }
                } while (documentId != null);               
            } else {
                // Nur Flag ändern
                /*
                pStmt = conn.prepareStatement("UPDATE Documents SET flag = " + VKCDocument.FLAG_DELETED + " WHERE documentId = ?");
                pStmt.setInt(1, documentId.intValue());
                pStmt.execute();
                */
                
                move2Trash(user, documentId, userTrashCategoryId, conn);
            }
 
            if (updateClassifyIndex) {
                // Classify-Index aktualisieren
                VKCCategory category = VKCCategorySerializer.retrieveCategory(categoryId, conn);
    			WebShare.classifier.updateCategory(category);

                //veränderte Keywords abspeichern
                VKCKeywordSerializer.saveKeywords(conn, category.getKeywords(), "CategoryKeywords", "categoryId", category.getCategoryId());
            }
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(rs);
            DBHelper.close(pStmt);
        }
    }

    public final static void move2Trash(VKCUser user, Integer documentId, Integer userTrashCategoryId) throws DBException {
        Connection conn = null;

        try {
            conn = WebShare.database.getConnection();
            move2Trash(user, documentId, userTrashCategoryId, conn);
            conn.commit();
        } catch (Exception e) {
            DBHelper.rollBack(conn);
            VKCLogManager.errorLog(VKCDocumentSerializer.class, "1", "Error occured while deleting document ID=" + documentId, e);
            throw new DBException(e);
        } finally {
            DBHelper.close(conn);
        }
    }
    
    private final static void move2Trash(VKCUser user, Integer documentId, Integer userTrashCategoryId, Connection conn) throws DBException {
        if (documentId == null || documentId.intValue() <= 0) {
        	return;
		}

        if (user == null || user.getUserId().intValue() <= 0) {
    		return;
   		}
        
        if (userTrashCategoryId == null || userTrashCategoryId.intValue() <= 0) {
        	userTrashCategoryId = CategoryUtils.createUserTrashCategory(user, conn).getCategoryId();
        }
        
        PreparedStatement pStmt = null;

        try {
            VKCDocument document = retrieveDocument(documentId, conn);

            if (document == null) {
               return;
            }

            do {
                pStmt = conn.prepareStatement("UPDATE CategoryDocuments SET categoryId = ? WHERE documentId = ?");
                pStmt.setInt(1, userTrashCategoryId.intValue());
                pStmt.setInt(2, documentId.intValue());
                pStmt.execute();
                DBHelper.close(pStmt);
                
                pStmt = conn.prepareStatement("DELETE FROM Userdocmails WHERE documentId = ?");
                pStmt.setInt(1, documentId.intValue());
                pStmt.execute();
                DBHelper.close(pStmt);
                
                pStmt = conn.prepareStatement("DELETE FROM UserRegisteredDocuments WHERE documentId = ?");
                pStmt.setInt(1, documentId.intValue());
                pStmt.execute();
                DBHelper.close(pStmt);
                
                VKCUserDocHistorySerializer.deleteDocumentHistory(null, documentId, conn);
                VKCDocumentFavoritesSerializer.deleteDocumentFavorite(null, documentId, conn);			

                //deletes the corresponding conversion status entry if one exists
                pStmt = conn.prepareStatement("DELETE FROM ConversionStati WHERE documentId = ?");
                pStmt.setInt(1, documentId.intValue());
                pStmt.execute();
                
                VKCDocumentSerializer.setFlag(documentId, VKCDocument.FLAG_DELETED, false, conn);
				
                if (document.getPreDocId() != null) {
                	document = retrieveDocument(document.getPreDocId(), conn);
                    documentId = document != null ? document.getDocumentId() : null;
                } else {
                    documentId = null;
                }
            } while (documentId != null);
        } catch (Exception ex) {
            throw new DBException(ex);
        } finally {
            DBHelper.close(pStmt);
        }
    }
    
    
    /**
     * Löscht das Dokument aus der Versionskette aus der Datenbank
     *
     * @param documentId ID des zu löschenden Dokuments
     */
    public final static void deleteDocumentVersion(Integer documentId, VKCUser user) throws DBException {
        Connection conn = null;

        try {
            conn = WebShare.database.getConnection();
            deleteDocumentVersion(documentId, user, conn);
            conn.commit();
        } catch (Exception e) {
            DBHelper.rollBack(conn);
            VKCLogManager.errorLog(VKCDocumentSerializer.class, "1", "Error occured while deleting document ID=" + documentId, e);
            throw new DBException(e);
        } finally {
            DBHelper.close(conn);
        }
    }

    /**
     * Löscht das Dokument aus der Versionskette aus der Datenbank
     *
     * @param documentId ID des zu löschenden Dokuments
     * @param conn
     */
    public final static void deleteDocumentVersion(Integer documentId, VKCUser user, Connection conn) throws DBException {
		if (documentId == null || documentId.intValue() <= 0) {
			return;
		}

		PreparedStatement pStmt = null;
		PreparedStatement pStmt1 = null;
		ResultSet rs = null;
		
        try {
            VKCDocument document = retrieveDocument(documentId, conn);

            if (document == null) {
            	return;
            }

            boolean definite = !WebShare.isTrashEnabled();
            
            Integer categoryId = document.getCategoryId() != null ? document.getCategoryId() : new Integer(-1);

            if (documentId.equals(getTopVersionDocId(documentId, conn))) {
                // Dokument aus dem Suchindex entfernen
                Search.removeDocument(documentId, true);

                if (document.getPreDocId() != null && document.getPreDocId().intValue() > 0) {
                    pStmt = conn.prepareStatement("DELETE FROM CategoryDocuments WHERE documentId = ? AND categoryId = ?");
                    pStmt.setInt(1, documentId.intValue());
                    pStmt.setInt(2, categoryId.intValue());
                    pStmt.execute();
                    DBHelper.close(pStmt);
                    
                    pStmt = conn.prepareStatement("INSERT INTO CategoryDocuments ( CATDOCID, CATEGORYID, DOCUMENTID, INITIALCATEGORY, CREATED, CREATORID ) VALUES ( ?, ?, ?, ?, ?, ? )");
                    pStmt.setInt(1, DBHelper.getNewId(conn, "CategoryDocuments", "catDocId").intValue());
                    pStmt.setInt(2, document.getCategoryId().intValue());
                    pStmt.setInt(3, document.getPreDocId().intValue());
                    pStmt.setInt(4, VKCCategory.ROOT_CATEGORY_ID);
                    pStmt.setTimestamp(5, new Timestamp(System.currentTimeMillis()));
                    pStmt.setInt(6, document.getPublisherId().intValue());
                    pStmt.execute();
                    
                    VKCDocument preDocument = retrieveDocument(document.getPreDocId(), conn);
                    Search.addDocument(preDocument, true);
                }
                
                // Classify-Index aktualisieren
                VKCCategory category = VKCCategorySerializer.retrieveCategory(categoryId, conn);
    			WebShare.classifier.updateCategory(category);

                //veränderte Keywords abspeichern
                VKCKeywordSerializer.saveKeywords(conn, category.getKeywords(), "CategoryKeywords", "categoryId", category.getCategoryId());
            } else if (documentId.equals(getFirstVersionDocId(documentId, conn))) {
                Integer successorId = getSuccessorVersionDocId(documentId, conn);
    			pStmt = conn.prepareStatement("UPDATE documents SET preDocId = ? WHERE documentId = ?");
    			pStmt.setNull(1, Types.INTEGER);
    			pStmt.setInt(2, successorId.intValue());
    			pStmt.execute();
            } else {
                Integer successorId = getSuccessorVersionDocId(documentId, conn);
                Integer predecessorId = document.getPreDocId();
    			pStmt = conn.prepareStatement("UPDATE documents SET preDocId = ? WHERE documentId = ?");
    			pStmt.setInt(1, predecessorId.intValue());
    			pStmt.setInt(2, successorId.intValue());
    			pStmt.execute();
            }
            
            if (definite) {
                // Definitiv löschen
                DBHelper.close(pStmt);
                pStmt = conn.prepareStatement("DELETE FROM Userdocmails WHERE documentId = ?");
                pStmt.setInt(1, documentId.intValue());
                pStmt.execute();
                DBHelper.close(pStmt);
                pStmt = conn.prepareStatement("DELETE FROM CategoryDocuments WHERE documentId = ? AND categoryId = ?");
                pStmt.setInt(1, documentId.intValue());
                pStmt.setInt(2, categoryId.intValue());
                pStmt.execute();
                DBHelper.close(pStmt);
                pStmt = conn.prepareStatement("DELETE FROM DocumentKeywords WHERE documentId = ?");
                pStmt.setInt(1, documentId.intValue());
                pStmt.execute();
                DBHelper.close(pStmt);
                pStmt = conn.prepareStatement("DELETE FROM UserRegisteredDocuments WHERE documentId = ?");
                pStmt.setInt(1, documentId.intValue());
                pStmt.execute();
                DBHelper.close(pStmt);
                pStmt = conn.prepareStatement("DELETE FROM AddDocAttrValues WHERE documentId = ?");
                pStmt.setInt(1, documentId.intValue());
                pStmt.execute();
    			VKCUserDocHistorySerializer.deleteDocumentHistory(null, documentId, conn);
                VKCDocumentFavoritesSerializer.deleteDocumentFavorite(null, documentId, conn);			

                // find out every additionaldocumentformat and delete it from the datastore
                DBHelper.close(rs);
                DBHelper.close(pStmt);
                pStmt = conn.prepareStatement("SELECT archiveId FROM AdditionalDocumentFormats WHERE documentId = ?");
                pStmt.setInt(1, documentId.intValue());
                rs = pStmt.executeQuery();

                while (rs.next()) {
                    String archiveId = rs.getString("archiveId");

                    try {
                        WebShare.archive.delete(archiveId);
                    } catch (DataStoreException e) {
                        VKCLogManager.errorLog(VKCDocumentSerializer.class, "", e.getMessage(), e);
                    }
                }

                DBHelper.close(pStmt);
                pStmt = conn.prepareStatement("DELETE FROM AdditionalDocumentFormats WHERE documentId = ?");
                pStmt.setInt(1, documentId.intValue());
                pStmt.execute();

    			if (document.getDynaAttrsId() != null) {
                    DBHelper.close(pStmt);
                    pStmt = conn.prepareStatement("DELETE FROM DynAttributes WHERE dynAttrId = ?");
                    pStmt.setInt(1, document.getDynaAttrsId().intValue());
                    pStmt.execute();
    			}

    			//deletes the corresponding conversion status entry if one exists
                DBHelper.close(pStmt);
                pStmt = conn.prepareStatement("DELETE FROM ConversionStati WHERE documentId = ?");
                pStmt.setInt(1, documentId.intValue());
                pStmt.execute();
                
                //Beschreibung löschen
                VKCNoteSerializer.deleteNote(documentId, "DOCUMENTS", conn);

                DBHelper.close(pStmt);
                pStmt = conn.prepareStatement("DELETE FROM Documents WHERE documentId = ?");
                pStmt.setInt(1, documentId.intValue());
                pStmt.execute();
            } else {
                pStmt1 = conn.prepareStatement("INSERT INTO CategoryDocuments ( CATDOCID, CATEGORYID, DOCUMENTID, INITIALCATEGORY, CREATED, CREATORID ) VALUES ( ?, ?, ?, ?, ?, ? )");
                pStmt1.setInt(1, DBHelper.getNewId(conn, "CategoryDocuments", "catDocId").intValue());
                pStmt1.setInt(2, VKCCategory.ROOT_CATEGORY_ID);
                pStmt1.setInt(3, documentId.intValue());
                pStmt1.setInt(4, VKCCategory.ROOT_CATEGORY_ID);
                pStmt1.setTimestamp(5, new Timestamp(System.currentTimeMillis()));
                pStmt1.setInt(6, document.getPublisherId().intValue());
                pStmt1.execute();
            	
                DBHelper.close(pStmt1);
    			pStmt1 = conn.prepareStatement("UPDATE documents SET preDocId = ? WHERE documentId = ?");
    			pStmt1.setNull(1, Types.INTEGER);
    			pStmt1.setInt(2, documentId.intValue());
    			pStmt1.execute();

    			move2Trash(user, documentId, null, conn);
            }
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(rs);
            DBHelper.close(pStmt);
            DBHelper.close(pStmt1);
        }
    }

    /**
     * Lädt die Dokument mit der übergebenen ID aus der Datenbank
     *
     * @param documentId ID des zu ladenden Dokumentes
     *
     * @return das geladene VKCDocument Objekt
     */
    public final static VKCDocument retrieveDocument(Integer documentId) throws DBException {
        Connection conn = null;

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

    /**
     * Lädt die Dokument mit der übergebenen ID aus der Datenbank
     *
     * @param documentId - ID des zu ladenden Dokumentes
     * @param conn - Hibernate-Session
     *
     * @return das geladene VKCDocument Objekt
     */
    public final static VKCDocument retrieveDocument(Integer documentId, Connection conn) throws DBException {
        if (documentId == null || documentId.intValue() <= 0) {
            return null;
        }

        PreparedStatement pStmt1 = null;
        PreparedStatement pStmt2 = null;
        ResultSet rs = null;
        ResultSet rs_catId = null;

        try {
            pStmt1 = conn.prepareStatement("SELECT * FROM DOCUMENTS WHERE DOCUMENTID = ?");
            pStmt1.setInt(1, documentId.intValue());
            rs = pStmt1.executeQuery();
            
            if (!rs.next()) {
                return null;
            }

            VKCDocument doc = new VKCDocument();
            setFields(doc, rs);

            // Kategorie-ID
            pStmt2 = conn.prepareStatement("SELECT categoryId FROM CategoryDocuments WHERE documentId = ?");
            pStmt2.setInt(1, doc.getDocumentId().intValue());
            rs_catId = pStmt2.executeQuery();

            //jpjpjp: hier die schleife rein...
            if (rs_catId.next()) {
                doc.setCategoryId(new Integer(rs_catId.getInt("categoryId")));
            } else {
                doc.setCategoryId(null);
            }

            //Beschreibung setzen
            if (doc.getNoteId() != null) {
                String description = VKCNoteSerializer.retrieveNote(doc.getNoteId(), conn);
                
                if (description != null) {
                    doc.setDescription(description);
                } else {
                    doc.setDescription("");
                }
            } else {
                doc.setDescription("");
            }
            
            // get associated keywords...
            doc.setKeywords(VKCKeywordSerializer.retrieveKeywords(conn, "documentKeywords", "documentId", doc.getDocumentId()));

            // Dokument Typ
            doc.setDocumentType(VKCDocumentTypeSerializer.getDocumentTypeById(doc.getDocTypeId()));

            // Dokument-Klasse
            doc.setDocumentClass(VKCDocumentClassSerializer.getDocumentClass(doc.getDocClassId()));

            // globale Attribute anbinden
            doc.setAddAttributes(VKCAddAttributeSerializer.retrieveAddDocAttributes(conn, doc));

            // dynamische lokale Attribute anbinden
            VKCDynaAttributeSerializer.retrieveDynaAttributes(conn, doc);

			// zusätzliche Dokumentformate holen
			doc.setAdditionalDocumentFormats(VKCDocumentFormatSerializer.retrieveAdditionalDocumentFormats(doc.getDocumentId(), conn));

            return doc;
        } catch (Exception ex) {
			VKCLogManager.errorLog(VKCDocumentSerializer.class, "1", "Error retrieving document ID=" + documentId, ex);
            throw new DBException(ex);
        } finally {
            DBHelper.close(rs);
            DBHelper.close(rs_catId);
            DBHelper.close(pStmt1);
            DBHelper.close(pStmt2);
        }
    }

    /**
     * Methode setzt die Dokumenteigenschaften für das übergebene
     * Dokument per Zugriff auf das ResultSet
     * @throws DBException 
     */
    private static final void setFields(VKCDocument document, ResultSet rs) throws SQLException, DBException {
        document.setDocumentId(new Integer(rs.getInt("documentId")));

        document.setAuthorName(rs.getString("authorname"));
        document.setName(rs.getString("name"));
        document.setTitle(rs.getString("title"));
        document.setArchiveId(rs.getString("archiveId"));
        document.setVersionDescr(rs.getString("version_descr"));
        document.setSummary(rs.getString("summary"));
        document.setCreationDate(rs.getTimestamp("creationdate"));
        document.setExpires(rs.getTimestamp("expires"));
        document.setPublished(rs.getTimestamp("published"));
        document.setLastChanged(rs.getTimestamp("lastChanged"));
        document.setLastAccessed(rs.getTimestamp("lastAccessed"));

        document.setDocTypeId(new Integer(rs.getInt("typeId")));
        
        if (rs.wasNull()) {
        	document.setDocTypeId(null);
        }

        document.setDocClassId(new Integer(rs.getInt("docClassId")));
        
        if (rs.wasNull()) {
        	document.setDocClassId(null);
        }

        document.setAclId(new Integer(rs.getInt("aclId")));
        
        if (rs.wasNull()) {
        	document.setAclId(null);
        }

        document.setDynaAttrsId(new Integer(rs.getInt("dynattrsid")));
        
        if (rs.wasNull()) {
        	document.setDynaAttrsId(null);
        }

        document.setPublisherId(new Integer(rs.getInt("publisherId")));
        
        if (rs.wasNull()) {
        	document.setPublisherId(null);
        }

        document.setVersionNo(new Integer(rs.getInt("version_no")));
        
        if (rs.wasNull()) {
        	document.setVersionNo(null);
        }

        document.setVersionLow(new Integer(rs.getInt("version_low")));
        
        if (rs.wasNull()) {
        	document.setVersionLow(null);
        }

        document.setVersionHigh(new Integer(rs.getInt("version_high")));

        if (rs.wasNull()) {
        	document.setVersionHigh(null);
        }

        document.setStatus(new Integer(rs.getInt("status")));
        
        if (rs.wasNull()) {
        	document.setStatus(null);
        }

        document.setFlag(new Integer(rs.getInt("flag")));
        
        if (rs.wasNull()) {
        	document.setFlag(null);
        }

        document.setLastAccessorId(new Integer(rs.getInt("lastAccessorId")));
        
        if (rs.wasNull()) {
        	document.setLastAccessorId(null);
        }

        document.setLastChangerId(new Integer(rs.getInt("lastChangerId")));
        
        if (rs.wasNull()) {
        	document.setLastChangerId(null);
        }

        document.setAccessCount(new Integer(rs.getInt("accessCounter")));
        
        if (rs.wasNull()) {
        	document.setAccessCount(null);
        }

        document.setPreDocId(new Integer(rs.getInt("preDocId")));
        
        if (rs.wasNull()) {
        	document.setPreDocId(null);
        }

        document.setDocSize(new Long(rs.getLong("docSize")));
        
        if (rs.wasNull()) {
        	document.setDocSize(null);
        }

        document.setZipped(new Integer(rs.getInt("zipped")));
        
        if (rs.wasNull()) {
        	document.setZipped(null);
        }

        document.setLangPackId(new Integer(rs.getInt("langPackId")));
        
        if (rs.wasNull()) {
        	document.setLangPackId(null);
        }

        document.setLockUserId(new Integer(rs.getInt("lockUserId")));
        
        if (rs.wasNull()) {
        	document.setLockUserId(null);
        }

        document.setNoteId(new Integer(rs.getInt("noteId")));

        if (rs.wasNull()) {
            document.setNoteId(null);
        }
    }

    /**
     * Speichert ein Dokument in der Datenbank ab
     *
     * @param document das zu speichernde Dokument
     * @param updateSearchIndex <code>true</code>, wenn die Statistik aktualisiert werden soll
     * @param sendMails <code>true</code>, wenn Benachritigungsmails verschickt werden sollen
     *
     * @throws DBException
     */
    public static final void saveDocument(VKCDocument document,
        								  boolean updateSearchIndex,
        								  boolean sendMails)
    throws DBException {
        Connection conn = null;

        try {
            conn = WebShare.database.getConnection();
            
            saveDocument(document, conn, updateSearchIndex);

            VKCCategory category = null;
            
            if (updateSearchIndex || sendMails){
            	category = VKCCategorySerializer.retrieveCategory(document.getCategoryId(), conn);
            }

            if (updateSearchIndex && category != null) {
                //Classify-Index aktualisieren
                try {
                    if (!category.getDocuments().contains(document)) {
                        category.addDocument(document);
                    }

					WebShare.classifier.updateCategory(category);

                    //Veränderte Keywords wegschreiben
                    VKCKeywordSerializer.saveKeywords(conn, category.getKeywords(), "CategoryKeywords", "categoryId", category.getCategoryId());
                } catch (IOException ioe) {
                    VKCLogManager.errorLog(VKCDocumentSerializer.class, "1", "Classifyindex could not be updated!", ioe);
                }
            }

			if (sendMails && category != null && (category.isProjectCategory() || category.isProjectSubfolder())) {
				ProjectUtils.populateNewDocumentPublishedMessages(category, document, conn);
			} //else
			if (WebShare.docPublishMail.isEnabled() && sendMails) {
			    UserDocMailsPopulator.getInstance().prepareForPopulation(document);
			}
			
			conn.commit();
        } catch (Exception e) {
        	UserDocMailsPopulator.getInstance().removePreparedForPopulation(document);
            DBHelper.rollBack(conn);

            if (!e.getMessage().equals(VKCDatabase.QUOTA_EXCEEDED)) {
                VKCLogManager.errorLog(VKCDocumentSerializer.class, "1", "Error saving document: " + document, e);
            }

            throw new DBException(e);
        } finally {
        	UserDocMailsPopulator.getInstance().completeOutstandingDocument(document);
            DBHelper.close(conn);
        }
    }

    /**
     * Speichert ein Dokument in der Datenbank ab
     *
     * @param conn unter dieser Session
     * @param document das zu speichernde Dokument
     */
    public static final void saveDocument(VKCDocument document,
                                          Connection conn,
                                          boolean updateSearchIndex)
    throws DBException {
        PreparedStatement pStmt = null;

        try {
            if (DBHelper.existsEntry(conn, "Documents", "documentId", document.getDocumentId())) {
                executeDocumentStatement(document, conn);
                
                if (document.getCategoryId() != null) {
                    pStmt = conn.prepareStatement("UPDATE CategoryDocuments SET categoryId = ? WHERE documentId = ?");
                    pStmt.setInt(1, document.getCategoryId().intValue());
                    pStmt.setInt(2, document.getDocumentId().intValue());
                    pStmt.execute();
                }

                // Additional Attributes speichern
                VKCAddAttributeSerializer.saveAddDocAttributes(conn, document);

                // Dynamische Attribute speichern
                VKCDynaAttributeSerializer.saveDynaAttributes(conn, document);
                
                // Dokument im Suchindex aktualisieren
                // !!! Muss unbedinngt vor dem saveKeywords und nach saveDynaAttributes aufgerufen werden, damit 
                // die in updateDocument erzeugten internen Keywords auch in die Datenbank geschrieben werden !!!
                if (updateSearchIndex) {
                    Search.updateDocument(document, true);
                }

                // Assozierte Schlüsselbegriffe speichern
                VKCKeywordSerializer.saveKeywords(conn, document.getKeywords(), "DocumentKeywords", "documentId", document.getDocumentId());
            } else {
                // NEU INSERIEREN
                VKCQuotaInfo qi = WebShare.database.getQuotaInfo(document.getPublisherId());

                if (qi != null && qi.getQuota() != null) {
                    if ((qi.getUsedQuota() + document.getDocSize().longValue()) > (qi.getQuota().longValue() * 1024 * 1024)) {
                        // quota exceeded
                        throw new DBException(VKCDatabase.QUOTA_EXCEEDED);
                    }
                }

                executeDocumentStatement(document, conn);

                if (document.getPreDocId() != null) {
                    // Vorgängerversion aus dem Suchindex löschen
                    if (updateSearchIndex) {
                        Search.removeDocument(document.getPreDocId(), true);
                    }

                    // Vorgängerversion aus der zuordnung zur category löschen und expires = null setzten
                    pStmt = conn.prepareStatement("DELETE from CategoryDocuments WHERE documentId = ?");
                    pStmt.setInt(1, document.getPreDocId().intValue());
                    pStmt.execute();
                    DBHelper.close(pStmt);
                    pStmt = conn.prepareStatement("UPDATE documents SET expires = ? WHERE documentId = ?");
                    pStmt.setNull(1, Types.TIMESTAMP);
                    pStmt.setInt(2, document.getPreDocId().intValue());
                    pStmt.execute();
                    // Vorgängerversion bei den Histories entfernen, indem die Referenz auf die Vorgängerversion aktualisiert wird
                    DBHelper.close(pStmt);
                    pStmt = conn.prepareStatement("UPDATE userDocHistories SET documentId = ? WHERE documentId = ?");
                    pStmt.setInt(1, document.getDocumentId().intValue());
                    pStmt.setInt(2, document.getPreDocId().intValue());
                    pStmt.execute();
                }

                if (document.getCategoryId() != null) {
                    DBHelper.close(pStmt);
                    pStmt = conn.prepareStatement("INSERT INTO CategoryDocuments ( CATDOCID, CATEGORYID, DOCUMENTID, INITIALCATEGORY, CREATED, CREATORID ) VALUES ( ?, ?, ?, ?, ?, ? )");
                    pStmt.setInt(1, DBHelper.getNewId(conn, "CategoryDocuments", "catDocId").intValue());
                    pStmt.setInt(2, document.getCategoryId().intValue());
                    pStmt.setInt(3, document.getDocumentId().intValue());
                    pStmt.setInt(4, VKCCategory.ROOT_CATEGORY_ID);
                    pStmt.setTimestamp(5, new Timestamp(System.currentTimeMillis()));
                    pStmt.setInt(6, document.getPublisherId().intValue());
                    pStmt.execute();
                }
                
                // Additional Attributes speichern
                VKCAddAttributeSerializer.saveAddDocAttributes(conn, document);

                // Dynamische Attribute speichern
                VKCDynaAttributeSerializer.saveDynaAttributes(conn, document);
                
                // Dokument dem Suchindex zufügen
                // !!! Danach muss unbedinngt die langPackId gespeichert werden, da diese erst beim Einfügen 
                // in den Suchindex ermittelt und im VKCDocument-Objekt gesetzt wird !!!
                if (updateSearchIndex) {
                    Search.addDocument(document, true);
                    
                    if (document.getLangPackId() != null) {
                        DBHelper.close(pStmt);
                        pStmt = conn.prepareStatement("UPDATE documents SET langPackId = ? WHERE documentId = ?");
                        pStmt.setInt(1, document.getLangPackId().intValue());
                        pStmt.setInt(2, document.getDocumentId().intValue());
                        pStmt.execute();
                    }
                }

                // Assozierte Schlüsselbegriffe speichern
                VKCKeywordSerializer.saveKeywords(conn, document.getKeywords(), "DocumentKeywords", "documentId", document.getDocumentId());
            }
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(pStmt);
        }
    }

    private static final void executeDocumentStatement(VKCDocument doc, Connection conn) throws SQLException, DBException {
        PreparedStatement pStmt = null;
        StringBuffer stmt = new StringBuffer();

        try {
            if (doc.getDocumentId() == null || doc.getDocumentId().intValue() <= 0) {
                doc.setDocumentId(DBHelper.getNewId(conn, "Documents", "documentId"));
                stmt.append("INSERT INTO DOCUMENTS ( ");
                stmt.append("name, ");
                stmt.append("title, ");
                stmt.append("archiveId, ");
                stmt.append("version_descr, ");
                stmt.append("summary, ");
                stmt.append("noteId, ");
                stmt.append("creationdate, ");
                stmt.append("expires, ");
                stmt.append("published, ");
                stmt.append("lastChanged, ");
                stmt.append("lastAccessed, ");
                stmt.append("typeId, ");
                stmt.append("docClassId, ");
                stmt.append("aclId, ");
                stmt.append("dynattrsid, ");
                stmt.append("publisherId, ");
                stmt.append("version_no, ");
                stmt.append("version_low, ");
                stmt.append("version_high, ");
                stmt.append("status, ");
                stmt.append("flag, ");
                stmt.append("lastAccessorId, ");
                stmt.append("lastChangerId, ");
                stmt.append("accessCounter, ");
                stmt.append("preDocId, ");
                stmt.append("docSize, ");
                stmt.append("zipped, ");
                stmt.append("langPackId, ");
                stmt.append("lockUserId, ");
                stmt.append("authorName, ");
                stmt.append("DOCUMENTID ) ");
                stmt.append("values ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )");
            } else {
                stmt.append("UPDATE DOCUMENTS SET ");
                stmt.append("name = ?, ");
                stmt.append("title = ?, ");
                stmt.append("archiveId = ?, ");
                stmt.append("version_descr = ?, ");
                stmt.append("summary = ?, ");
                stmt.append("noteId = ?, ");
                stmt.append("creationdate = ?, ");
                stmt.append("expires = ?, ");
                stmt.append("published = ?, ");
                stmt.append("lastChanged = ?, ");
                stmt.append("lastAccessed = ?, ");
                stmt.append("typeId = ?, ");
                stmt.append("docClassId = ?, ");
                stmt.append("aclId = ?, " );
                stmt.append("dynattrsid = ?, ");
                stmt.append("publisherId = ?, ");
                stmt.append("version_no = ?, ");
                stmt.append("version_low = ?, ");
                stmt.append("version_high = ?, ");
                stmt.append("status = ?, ");
                stmt.append("flag = ?, ");
                stmt.append("lastAccessorId = ?, ");
                stmt.append("lastChangerId = ?, ");
                stmt.append("accessCounter = ?, ");
                stmt.append("preDocId = ?, ");
                stmt.append("docSize = ?, ");
                stmt.append("zipped = ?, ");
                stmt.append("langPackId = ?, ");
                stmt.append("lockUserId = ?, ");
                stmt.append("authorName = ? ");
                stmt.append("WHERE DOCUMENTID = ?");
            }

            //NoteId in abh. von der Beschreibung setzen
            if (StringUtils.isEmpty(doc.getDescription())) {
                doc.setNoteId(null);
            } else {
                doc.setNoteId(VKCNoteSerializer.saveNote(doc.getDocumentId(), "DOCUMENTS", doc.getDescription(), conn));
            }

            pStmt = conn.prepareStatement(stmt.toString());
            int index = 1;

            if (doc.getName() != null) {
                //diese Abfrage ist wegen Oracle notwendig
                if (doc.getName().length() == 0) {
                    pStmt.setString(index++, " ");
                } else {
                    pStmt.setString(index++, doc.getName());
                }
            } else {
                pStmt.setNull(index++, Types.VARCHAR);
            }

            if (doc.getTitle() != null) {
                pStmt.setString(index++, doc.getTitle());
            } else {
                pStmt.setNull(index++, Types.VARCHAR);
            }

            if (doc.getArchiveId() != null) {
                pStmt.setString(index++, doc.getArchiveId());
            } else {
                pStmt.setNull(index++, Types.VARCHAR);
            }

            if (doc.getVersionDescr() != null) {
                pStmt.setString(index++, doc.getVersionDescr());
            } else {
                pStmt.setNull(index++, Types.VARCHAR);
            }

            if (doc.getSummary() != null) {
                pStmt.setString(index++, doc.getSummary());
            } else {
                pStmt.setNull(index++, Types.VARCHAR);
            }

            if (doc.getNoteId() != null) {
                pStmt.setInt(index++, doc.getNoteId().intValue());
            } else {
                pStmt.setNull(index++, Types.INTEGER);
            }

            if (doc.getCreationDate() != null) {
                pStmt.setTimestamp(index++, new Timestamp(doc.getCreationDate().getTime()));
            } else {
                pStmt.setNull(index++, Types.TIMESTAMP);
            }

            if (doc.getExpires() != null) {
                pStmt.setTimestamp(index++, new Timestamp(doc.getExpires().getTime()));
            } else {
                pStmt.setNull(index++, Types.TIMESTAMP);
            }

            if (doc.getPublished() != null) {
                pStmt.setTimestamp(index++, new Timestamp(doc.getPublished().getTime()));
            } else {
                pStmt.setNull(index++, Types.TIMESTAMP);
            }

            if (doc.getLastChanged() != null) {
                pStmt.setTimestamp(index++, new Timestamp(doc.getLastChanged().getTime()));
            } else {
                pStmt.setNull(index++, Types.TIMESTAMP);
            }

            if (doc.getLastAccessed() != null) {
                pStmt.setTimestamp(index++, new Timestamp(doc.getLastAccessed().getTime()));
            } else {
                pStmt.setNull(index++, Types.TIMESTAMP);
            }

            if (doc.getDocTypeId() != null) {
                pStmt.setInt(index++, doc.getDocTypeId().intValue());
            } else {
                pStmt.setNull(index++, Types.INTEGER);
            }

            if (doc.getDocClassId() != null) {
                pStmt.setInt(index++, doc.getDocClassId().intValue());
            } else {
                pStmt.setNull(index++, Types.INTEGER);
            }

            if (doc.getAclId() != null) {
                pStmt.setInt(index++, doc.getAclId().intValue());
            } else {
                pStmt.setNull(index++, Types.INTEGER);
            }

            if (doc.getDynaAttrsId() != null) {
                pStmt.setInt(index++, doc.getDynaAttrsId().intValue());
            } else {
                pStmt.setNull(index++, Types.INTEGER);
            }

            if (doc.getPublisherId() != null) {
                pStmt.setInt(index++, doc.getPublisherId().intValue());
            } else {
                pStmt.setNull(index++, Types.INTEGER);
            }

            if (doc.getVersionNo() != null) {
                pStmt.setInt(index++, doc.getVersionNo().intValue());
            } else {
                pStmt.setNull(index++, Types.INTEGER);
            }

            if (doc.getVersionLow() != null) {
                pStmt.setInt(index++, doc.getVersionLow().intValue());
            } else {
                pStmt.setNull(index++, Types.INTEGER);
            }

            if (doc.getVersionHigh() != null) {
                pStmt.setInt(index++, doc.getVersionHigh().intValue());
            } else {
                pStmt.setNull(index++, Types.INTEGER);
            }

            if (doc.getStatus() != null) {
                pStmt.setInt(index++, doc.getStatus().intValue());
            } else {
                pStmt.setNull(index++, Types.INTEGER);
            }

            if (doc.getFlag() != null) {
                pStmt.setInt(index++, doc.getFlag().intValue());
            } else {
                pStmt.setNull(index++, Types.INTEGER);
            }

            if (doc.getLastAccessorId() != null) {
                pStmt.setInt(index++, doc.getLastAccessorId().intValue());
            } else {
                pStmt.setNull(index++, Types.INTEGER);
            }

            if (doc.getLastChangerId() != null) {
                pStmt.setInt(index++, doc.getLastChangerId().intValue());
            } else {
                pStmt.setNull(index++, Types.INTEGER);
            }

            if (doc.getAccessCount() != null) {
                pStmt.setInt(index++, doc.getAccessCount().intValue());
            } else {
                pStmt.setNull(index++, Types.INTEGER);
            }

            if (doc.getPreDocId() != null) {
                pStmt.setInt(index++, doc.getPreDocId().intValue());
            } else {
                pStmt.setNull(index++, Types.INTEGER);
            }

            if (doc.getDocSize() != null) {
                pStmt.setLong(index++, doc.getDocSize().longValue());
            } else {
                pStmt.setNull(index++, Types.BIGINT);
            }

            if (doc.getZipped() != null) {
                pStmt.setInt(index++, doc.getZipped().intValue());
            } else {
                pStmt.setNull(index++, Types.INTEGER);
            }

            if (doc.getLangPackId() != null) {
                pStmt.setInt(index++, doc.getLangPackId().intValue());
            } else {
                pStmt.setNull(index++, Types.INTEGER);
            }

            if (doc.getLockUserId() != null) {
                pStmt.setInt(index++, doc.getLockUserId().intValue());
            } else {
                pStmt.setNull(index++, Types.INTEGER);
            }

            if (doc.getAuthorName() != null) {
                pStmt.setString(index++, doc.getAuthorName());
            } else {
                pStmt.setNull(index++, Types.VARCHAR);
            }

            pStmt.setInt(index++, doc.getDocumentId().intValue());

            pStmt.execute();
        } finally {
            DBHelper.close(pStmt);
        }
    }

    /**
     * Lädt alle registrierten Dokumente eines Benutzers aus der Datenbank
     *
     * @param userId ID des Benutzers
     *
     * @return HashMap registierte Dokumente
     */
    public static final HashMap retrieveUserRegisteredDocuments(Integer userId) throws DBException {
        Connection conn = null;

        if (userId == null) {
            return null;
        }

        try {
            conn = WebShare.database.getConnection();
            
            return retrieveRegisteredDocuments(conn, "UserRegisteredDocuments", "userId", userId);
        } catch (Exception e) {
            VKCLogManager.errorLog(VKCDocumentSerializer.class, "1", "Error while retrieving userregistereddocuments ", e);
            throw new DBException(e);
        } finally {
            DBHelper.close(conn);
        }
    }

    /**
     * Diese Methode liefert alle registrierten Dokumente einer Entität
     *
     * @param conn verwendete Session
     * @param associationTable Name der Tabelle in der gesucht werden soll
     * @param keyColumn Name der Spalte in der gesucht werden soll
     * @param keyId Wert der Spalte nach dem es gesucht werden soll
     *
     * @return HashMap registrierte Dokumente
     * @throws DBException 
     */
    private static final HashMap retrieveRegisteredDocuments(Connection conn,
                                                             String associationTable,
                                                             String keyColumn,
                                                             Integer keyId)
    throws SQLException, DBException {
        PreparedStatement pStmt = null;
        PreparedStatement pStmt1 = null;
        PreparedStatement pStmt2 = null;
        ResultSet rs = null;
        HashMap documents = new HashMap();

        try {

            String sqlStatement = "SELECT documentId, threshold, subjectForNotification FROM " + associationTable + " WHERE " + keyColumn + " = ?";
            pStmt = conn.prepareStatement(sqlStatement);
            pStmt.setInt(1, keyId.intValue());

            rs = pStmt.executeQuery();

            while (rs.next()) {
                RegisteredDocument document = new RegisteredDocument();
                document.setDocumentId(new Integer(rs.getInt("documentId")));
                document.setThreshold(new Integer(rs.getInt("threshold")));
                document.setSubjectForNotification(rs.getString("subjectForNotification"));
                documents.put(document.getDocumentId(), document);
            }

            sqlStatement = "SELECT name, title, noteId, typeId FROM documents WHERE documentId = ?";
            pStmt1 = conn.prepareStatement(sqlStatement);

            sqlStatement = "SELECT categoryId FROM categoryDocuments WHERE documentId = ?";
            pStmt2 = conn.prepareStatement(sqlStatement);

            Iterator it = documents.keySet().iterator();

            while (it.hasNext()) {
                Integer docId = (Integer) it.next();
                RegisteredDocument document = (RegisteredDocument) documents.get(docId);

                DBHelper.close(rs);

                pStmt1.setInt(1, docId.intValue());
                rs = pStmt1.executeQuery();

                while (rs.next()) {
                    document.setName(rs.getString("name"));
                    document.setTitle(rs.getString("title"));
                    document.setDocType(VKCDocumentTypeSerializer.getDocumentTypeById(new Integer(rs.getInt("typeId"))));
                    document.setDescription(VKCNoteSerializer.retrieveNote(new Integer(rs.getInt("noteId")), conn));
                    
                    if (rs.wasNull() || document.getDescription() == null) {
                        document.setDescription("");
                    }
                }

                DBHelper.close(rs);

                pStmt2.setInt(1, docId.intValue());
                rs = pStmt2.executeQuery();
                
                if (rs.next()) {
                    document.setCategoryId(new Integer(rs.getInt("categoryId")));
                }
                
                documents.put(document.getDocumentId(), document);
            }

            return documents;
        } finally {
            DBHelper.close(rs);
            DBHelper.close(pStmt);
            DBHelper.close(pStmt1);
            DBHelper.close(pStmt2);
        }
    }

    /**
     * Speichert die registrierten Dokumente eines Benutzers
     * in die Datenbank
     *
     * @param user Benutzer
     */
    public static final void saveUserRegisteredDocuments(VKCUser user) throws DBException {
        Connection conn = null;

        try {
            conn = WebShare.database.getConnection();
            saveRegisteredDocuments(conn, user.getRegisteredDocuments(), "UserRegisteredDocuments", "userId", user.getUserId());
            conn.commit();
        } catch (Exception e) {
            DBHelper.rollBack(conn);
            VKCLogManager.errorLog(VKCDocumentSerializer.class, "1", "Error while saving userregistereddocuments: ", e);
            throw new DBException(e);
        } finally {
            DBHelper.close(conn);
        }
    }

    /**
     * Speichert registrierten Dokumente einer Entität
     */
    private final static void saveRegisteredDocuments(Connection conn,
                                                      HashMap documents,
                                                      String associationTable,
                                                      String keyColumn,
                                                      Integer keyId)
    throws DBException {
		PreparedStatement pStmt = null;

		try {
			// Erstmal evtl. vorhandene Einträge löschen
	        DBHelper.deleteEntry(conn, associationTable, keyColumn, keyId);

	        String statement = "INSERT INTO " + associationTable + " (" + keyColumn + ", documentId, threshold, subjectForNotification ) VALUES ( ?, ?, ?, ? )";
	        pStmt = conn.prepareStatement(statement);

	        Iterator it = documents.values().iterator();

	        while (it.hasNext()) {
	            RegisteredDocument document = (RegisteredDocument) it.next();

	            // Assoziation hinzufügen in die Zwischentabelle
	            pStmt.setInt(1, keyId.intValue());
	            pStmt.setInt(2, document.getDocumentId().intValue());
	            pStmt.setInt(3, document.getThreshold().intValue());
                pStmt.setString(4, document.getSubjectForNotification());
	            pStmt.execute();
	        }
		} catch (Exception e) {
			throw new DBException(e);
		} finally {
			DBHelper.close(pStmt);
		} 
    }

	/**
	 * Speichert Zugriffszähler, Leser, Zugriffsdatum eines Dokumentes
	 *
	 * @param doc Referenz auf ein VKCDocument-Objekt
	 */
	public static final void updateAccessData(VKCDocument doc) throws DBException {
		Connection conn = null;
		PreparedStatement pStmt = null;

		try {
			conn = WebShare.database.getConnection();
			pStmt = conn.prepareStatement("UPDATE Documents SET accessCounter = ?, lastAccessorId = ?, lastAccessed = ? WHERE documentId = ?");
            pStmt.setInt(1, doc.getAccessCount().intValue());
            pStmt.setInt(2, doc.getLastAccessorId().intValue());
            pStmt.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
            pStmt.setInt(4, doc.getDocumentId().intValue());
            pStmt.execute();
			conn.commit();
		} catch (Exception e) {
			DBHelper.rollBack(conn);
			throw new DBException(e);
		} finally {
			DBHelper.close(pStmt);
			DBHelper.close(conn);
		}
	}
    
    /**
     * Aktualisiert die langPackId eines Dokuments
     *
     * @param doc Referenz auf ein VKCDocument-Objekt
     */
    public static final void updateLangPackId(VKCDocument doc) throws DBException {
        Connection conn = null;
        PreparedStatement pStmt = null;

        try {
            conn = WebShare.database.getConnection();
            pStmt = conn.prepareStatement("UPDATE documents SET langPackId = ? WHERE documentId = ?");
            pStmt.setInt(1, doc.getLangPackId().intValue());
            pStmt.setInt(2, doc.getDocumentId().intValue());
            pStmt.execute();
            conn.commit();
        } catch (Exception e) {
            DBHelper.rollBack(conn);
            throw new DBException(e);
        } finally {
            DBHelper.close(pStmt);
            DBHelper.close(conn);
        }
    }
    
	
	/**
	 * changes the publisherId from old to new of a given document or all of a given publisher  
	 * 
	 * @param documentId = id of document or NULL (for all docs of the given oldPublisherId)
	 * @param oldPublisherId = old owner of the documents
	 * @param newPublisherId = new owner of the documents
     * @param changerId 
	 * @return
	 * @throws DBException
	 */	
	public static final void changePublisherId(Integer documentId, Integer oldPublisherId, Integer newPublisherId, Integer changerId) throws DBException {
		Connection conn = null;
		PreparedStatement pStmt = null;
		
		try {
		    conn = WebShare.database.getConnection();

		    if (documentId == null || documentId.intValue() <= 0) {
                // do it for all documents
		        pStmt = conn.prepareStatement("UPDATE Documents SET publisherId = ?, lastChangerId = ?, lastChanged = ? WHERE publisherId = ? ");
	            pStmt.setInt(1, newPublisherId.intValue());
	            pStmt.setInt(2, changerId.intValue());
	            pStmt.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
	            pStmt.setInt(4, oldPublisherId.intValue());
		    } else {
                // do it for a given document
		        pStmt = conn.prepareStatement("UPDATE Documents SET publisherId = ?, lastChangerId = ?, lastChanged = ? WHERE publisherId = ? AND documentId = ?");
	            pStmt.setInt(1, newPublisherId.intValue());
	            pStmt.setInt(2, changerId.intValue());
	            pStmt.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
	            pStmt.setInt(4, oldPublisherId.intValue());
	            pStmt.setInt(5, documentId.intValue());
		    }
		    
            pStmt.execute();
			conn.commit();
		} catch (Exception e) {
			DBHelper.rollBack(conn);
			throw new DBException(e);
		} finally {
			DBHelper.close(pStmt);
			DBHelper.close(conn);
		}
	}

	public static final Integer getFirstVersionDocId(Integer documentId) throws DBException { 
        Connection conn = null;

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

			return getFirstVersionDocId(documentId, conn);
		} catch (Exception e) { 	
			throw new DBException(e);
		} finally {
			DBHelper.close(conn);
		}        
    }	
	
    /**
     * tries to find the documentId of the first version of the given document  
     * 
     * @param documentId
     * @return
     * @throws DBException
     */
	public static final Integer getFirstVersionDocId(Integer documentId, Connection conn) throws DBException { 
		if (documentId == null || documentId.intValue() <= 0) {
			return null;
		}

		PreparedStatement pStmt = null;
		ResultSet rs = null;
		String sqlStatement = "SELECT preDocId FROM documents WHERE documentId = ?";
		int firstVersionDocId = documentId.intValue();
		int currentPreDocId = 0;		
		
		try {
			pStmt = conn.prepareStatement(sqlStatement);
			
			// loop until no other valid preDocId is found ! 
			do {
				pStmt.setInt(1, firstVersionDocId);    
				
				DBHelper.close(rs);
				rs = pStmt.executeQuery();
				rs.next();
				
				currentPreDocId = rs.getInt("preDocId");
			
				if (!rs.wasNull()) {
					firstVersionDocId = currentPreDocId;
				}	 
			} while (!rs.wasNull());
			
			// return last valid docId wich has no preDocId
			return new Integer(firstVersionDocId);
		} catch (Exception e) { 	
			throw new DBException(e);
		} finally {
			DBHelper.close(rs);
			DBHelper.close(pStmt);
		}        
    }	
	
	/**
	 * tries to find the documentId of the first version of the given document  
	 * 
	 * @param documentId
	 * @return
	 * @throws DBException
	 */
	public static final Integer getTopVersionDocId(Integer documentId) throws DBException { 
		Connection conn = null;

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

			return getTopVersionDocId(documentId, conn);
		} catch (Exception e) { 	
			throw new DBException(e);
		} finally {
		    DBHelper.close(conn);
		}        
	}

	/**
	 * tries to find the documentId of the first version of the given document  
	 * 
	 * @param documentId
	 * @return
	 * @throws DBException
	 */
	public static final Integer getTopVersionDocId(Integer documentId, Connection conn) throws DBException { 
		if (documentId == null || documentId.intValue() <= 0) {
			return null;
		}

		PreparedStatement pStmt = null;
		ResultSet rs = null;
		String sqlStatement = "SELECT documentId FROM documents WHERE preDocId = ?";
		int topVersionDocId = documentId.intValue();
		
		try {
			pStmt = conn.prepareStatement(sqlStatement);
			pStmt.setInt(1, topVersionDocId);    
				
			rs = pStmt.executeQuery();
			
			// loop until no other valid documentId is found ! 
			while (rs.next()) {
				topVersionDocId = rs.getInt("documentId");
				
				DBHelper.close(rs);
				pStmt.setInt(1, topVersionDocId);    
				rs = pStmt.executeQuery();
			}
			
			// return last valid docId
			return new Integer(topVersionDocId);
		} catch (Exception e) { 	
			throw new DBException(e);
		} finally {
			DBHelper.close(rs);
			DBHelper.close(pStmt);
		}        
	}

	/**
	 * tries to find the documentId of the first version of the given document  
	 * 
	 * @param documentId
	 * @return
	 * @throws DBException
	 */
	public static final Integer getPredecessorVersionDocId(Integer documentId) throws DBException { 
		Connection conn = null;

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

			return getPredecessorVersionDocId(documentId, conn);
		} catch (Exception e) { 	
			throw new DBException(e);
		} finally {
		    DBHelper.close(conn);
		}        
	}
	
	/**
	 * tries to find the documentId of the first version of the given document  
	 * 
	 * @param documentId
	 * @return
	 * @throws DBException
	 */
	public static final Integer getPredecessorVersionDocId(Integer documentId, Connection conn) throws DBException { 
		if (documentId == null || documentId.intValue() <= 0) {
			return null;
		}

		PreparedStatement pStmt = null;
		ResultSet rs = null;
		String sqlStatement = "SELECT preDocId FROM documents WHERE documentId = ?";
		Integer preDocId = null;
		
		try {
			pStmt = conn.prepareStatement(sqlStatement);
			pStmt.setInt(1, documentId.intValue());    
				
			rs = pStmt.executeQuery();		
 
			if (rs.next()) {
			    preDocId = new Integer(rs.getInt("preDocId"));

			    if (rs.wasNull()) {
			        preDocId = null;
				}
			}
			
			return preDocId;
		} catch (Exception e) { 	
			throw new DBException(e);
		} finally {
			DBHelper.close(rs);
			DBHelper.close(pStmt);
		}        
	}
	
	/**
	 * tries to find the documentId of the first version of the given document  
	 * 
	 * @param documentId
	 * @return
	 * @throws DBException
	 */
	public static final Integer getSuccessorVersionDocId(Integer documentId) throws DBException { 
		Connection conn = null;

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

			return getSuccessorVersionDocId(documentId, conn);
		} catch (Exception e) { 	
			throw new DBException(e);
		} finally {
		    DBHelper.close(conn);
		}        
	}
	
	/**
	 * tries to find the documentId of the first version of the given document  
	 * 
	 * @param documentId
	 * @return
	 * @throws DBException
	 */
	public static final Integer getSuccessorVersionDocId(Integer documentId, Connection conn) throws DBException { 
		if (documentId == null || documentId.intValue() <= 0) {
			return null;
		}

		PreparedStatement pStmt = null;
		ResultSet rs = null;
		String sqlStatement = "SELECT documentId FROM documents WHERE preDocId = ?";
		Integer succDocId = null;
		
		try {
			pStmt = conn.prepareStatement(sqlStatement);
			pStmt.setInt(1, documentId.intValue());    
				
			rs = pStmt.executeQuery();		
 
			if (rs.next()) {
				succDocId = new Integer(rs.getInt("documentId"));
			    
				if (rs.wasNull()) {
			        succDocId = null;
				}
			}
			
			return succDocId;
		} catch (Exception e) { 	
			throw new DBException(e);
		} finally {
			DBHelper.close(rs);
			DBHelper.close(pStmt);
		}        
	}
	
    /**
     * Prüft ob der Benutzer irgendwelche Dokumente hat
     *
     * @param userId ID des Benutzers
     *
     * @return true falls Benutzer noch Dokumente hat, sonst false
     */
    public static final boolean hasUserAnyDocuments(Integer userId) throws DBException {
        Connection conn = null;
        PreparedStatement pStmt = null;
        ResultSet rs = null;

        try {
			conn = WebShare.database.getConnection();
            String statement = " SELECT documentId" +
                			   " FROM documents WHERE publisherId = ?" +
                			   " AND ( FLAG = " + VKCDocument.FLAG_PUBLISHED +
                			   " OR FLAG = " + VKCDocument.FLAG_TOBEPUBLISHED +
                			   " OR FLAG = " + VKCDocument.FLAG_EXPIRED + " )";
			pStmt = conn.prepareStatement(statement);
			pStmt.setInt(1, userId.intValue());
            rs = pStmt.executeQuery();
            
            return rs.next();
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(rs);
            DBHelper.close(pStmt);
            DBHelper.close(conn);
        }
    }

    public final static Integer[] getDocumentIdsAscendingByPublishingDate(Integer[] documentIds) throws DBException {
        Connection conn = null;

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

    public final static Integer[] getDocumentIdsAscendingByPublishingDate(Integer[] documentIds, Connection conn) throws DBException {
        if (documentIds == null || documentIds.length == 0) {
        	return new Integer[0];
        }
    	
    	PreparedStatement pStmt = null;
        ResultSet rs = null;
        Integer[] docIds = new Integer[documentIds.length];
        int index = 0;

        try {
        	String query = "SELECT documentId FROM documents WHERE ";
        	
        	for (int i = 0; i < documentIds.length; i++) {
				if (i == 0) {
	        		query += "documentId = ? ";
				} else {
	        		query += "OR documentId = ? ";
				}
			}
        	
        	query += "ORDER BY published ASC";
            
        	pStmt = conn.prepareStatement(query);
            DBHelper.setElements(pStmt, documentIds);
            rs = pStmt.executeQuery();

            while (rs.next()) {
                docIds[index++] = new Integer(rs.getInt("documentId"));
            }

            return docIds;
        } catch (Exception ex) {
            throw new DBException(ex);
        } finally {
            DBHelper.close(rs);
            DBHelper.close(pStmt);
        }
    }
    
    
    
    public static final void importDocument(List documents
			) throws DBException {
		Connection conn = null;

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

			importDocument(documents, conn);		

			conn.commit();
		} catch (Exception e) {			
			DBHelper.rollBack(conn);

			if (!e.getMessage().equals(VKCDatabase.QUOTA_EXCEEDED)) {
				VKCLogManager.errorLog(VKCDocumentSerializer.class, "1",
						"Error import document:s " , e);
			}

			throw new DBException(e);
		} finally {			
			DBHelper.close(conn);
		}
	}

	/**
	 * Speichert ein Dokument in der Datenbank ab
	 * 
	 * @param conn
	 *            unter dieser Session
	 * @param document
	 *            das zu speichernde Dokument
	 */
	public static final void importDocument(List documents,
			Connection conn) throws DBException {
		PreparedStatement pStmt = null;
		Iterator iterDocuments = documents.iterator();
		Set categories = new HashSet();
		 StringBuffer stmt = new StringBuffer();
         stmt.append("INSERT INTO DOCUMENTS ( ");
         stmt.append("name, ");
         stmt.append("title, ");
         stmt.append("archiveId, ");
         stmt.append("version_descr, ");
         stmt.append("summary, ");
         stmt.append("noteId, ");
         stmt.append("creationdate, ");
         stmt.append("expires, ");
         stmt.append("published, ");
         stmt.append("lastChanged, ");
         stmt.append("lastAccessed, ");
         stmt.append("typeId, ");
         stmt.append("docClassId, ");
         stmt.append("aclId, ");
         stmt.append("dynattrsid, ");
         stmt.append("publisherId, ");
         stmt.append("version_no, ");
         stmt.append("version_low, ");
         stmt.append("version_high, ");
         stmt.append("status, ");
         stmt.append("flag, ");
         stmt.append("lastAccessorId, ");
         stmt.append("lastChangerId, ");
         stmt.append("accessCounter, ");
         stmt.append("preDocId, ");
         stmt.append("docSize, ");
         stmt.append("zipped, ");
         stmt.append("langPackId, ");
         stmt.append("lockUserId, ");
         stmt.append("authorName, ");
         stmt.append("DOCUMENTID ) ");
         stmt.append("values ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )");
		 while(iterDocuments.hasNext()){
			VKCDocument document = (VKCDocument) iterDocuments.next();			
			try {			
					                
	                if(document.getDescription()!=null && !document.getDescription().equals("")){
	                	document.setNoteId(VKCNoteSerializer.saveNote(document.getDocumentId(),"DOCUMENTS",document.getDescription()));
	                }
	                try{                
	               
	                
	                pStmt = conn.prepareStatement(stmt.toString());
	                int index = 1;
	
	                if (document.getName() != null) {
	                    //diese Abfrage ist wegen Oracle notwendig
	                    if (document.getName().length() == 0) {
	                        pStmt.setString(index++, " ");
	                    } else {
	                        pStmt.setString(index++, document.getName());
	                    }
	                } else {
	                    pStmt.setNull(index++, Types.VARCHAR);
	                }
	
	                if (document.getTitle() != null) {
	                    pStmt.setString(index++, document.getTitle());
	                } else {
	                    pStmt.setNull(index++, Types.VARCHAR);
	                }
	
	                if (document.getArchiveId() != null) {
	                    pStmt.setString(index++, document.getArchiveId());
	                } else {
	                    pStmt.setNull(index++, Types.VARCHAR);
	                }
	
	                if (document.getVersionDescr() != null) {
	                    pStmt.setString(index++, document.getVersionDescr());
	                } else {
	                    pStmt.setNull(index++, Types.VARCHAR);
	                }
	
	                if (document.getSummary() != null) {
	                    pStmt.setString(index++, document.getSummary());
	                } else {
	                    pStmt.setNull(index++, Types.VARCHAR);
	                }
	
	                if (document.getNoteId() != null) {
	                    pStmt.setInt(index++, document.getNoteId().intValue());
	                } else {
	                    pStmt.setNull(index++, Types.VARCHAR);
	                }
	
	                if (document.getCreationDate() != null) {
	                    pStmt.setTimestamp(index++, new Timestamp(document.getCreationDate().getTime()));
	                } else {
	                    pStmt.setNull(index++, Types.TIMESTAMP);
	                }
	
	                if (document.getExpires() != null) {
	                    pStmt.setTimestamp(index++, new Timestamp(document.getExpires().getTime()));
	                } else {
	                    pStmt.setNull(index++, Types.TIMESTAMP);
	                }
	
	                if (document.getPublished() != null) {
	                    pStmt.setTimestamp(index++, new Timestamp(document.getPublished().getTime()));
	                } else {
	                    pStmt.setNull(index++, Types.TIMESTAMP);
	                }
	
	                if (document.getLastChanged() != null) {
	                    pStmt.setTimestamp(index++, new Timestamp(document.getLastChanged().getTime()));
	                } else {
	                    pStmt.setNull(index++, Types.TIMESTAMP);
	                }
	
	                if (document.getLastAccessed() != null) {
	                    pStmt.setTimestamp(index++, new Timestamp(document.getLastAccessed().getTime()));
	                } else {
	                    pStmt.setNull(index++, Types.TIMESTAMP);
	                }
	
	                if (document.getDocTypeId() != null) {
	                    pStmt.setInt(index++, document.getDocTypeId().intValue());
	                } else {
	                    pStmt.setNull(index++, Types.INTEGER);
	                }
	
	                if (document.getDocClassId() != null) {
	                    pStmt.setInt(index++, document.getDocClassId().intValue());
	                } else {
	                    pStmt.setNull(index++, Types.INTEGER);
	                }
	
	                if (document.getAclId() != null) {
	                    pStmt.setInt(index++, document.getAclId().intValue());
	                } else {
	                    pStmt.setNull(index++, Types.INTEGER);
	                }
	
	                if (document.getDynaAttrsId() != null) {
	                    pStmt.setInt(index++, document.getDynaAttrsId().intValue());
	                } else {
	                    pStmt.setNull(index++, Types.INTEGER);
	                }
	
	                if (document.getPublisherId() != null) {
	                    pStmt.setInt(index++, document.getPublisherId().intValue());
	                } else {
	                    pStmt.setNull(index++, Types.INTEGER);
	                }
	
	                if (document.getVersionNo() != null) {
	                    pStmt.setInt(index++, document.getVersionNo().intValue());
	                } else {
	                    pStmt.setNull(index++, Types.INTEGER);
	                }
	
	                if (document.getVersionLow() != null) {
	                    pStmt.setInt(index++, document.getVersionLow().intValue());
	                } else {
	                    pStmt.setNull(index++, Types.INTEGER);
	                }
	
	                if (document.getVersionHigh() != null) {
	                    pStmt.setInt(index++, document.getVersionHigh().intValue());
	                } else {
	                    pStmt.setNull(index++, Types.INTEGER);
	                }
	
	                if (document.getStatus() != null) {
	                    pStmt.setInt(index++, document.getStatus().intValue());
	                } else {
	                    pStmt.setNull(index++, Types.INTEGER);
	                }
	
	                if (document.getFlag() != null) {
	                    pStmt.setInt(index++, document.getFlag().intValue());
	                } else {
	                    pStmt.setNull(index++, Types.INTEGER);
	                }
	
	                if (document.getLastAccessorId() != null) {
	                    pStmt.setInt(index++, document.getLastAccessorId().intValue());
	                } else {
	                    pStmt.setNull(index++, Types.INTEGER);
	                }
	
	                if (document.getLastChangerId() != null) {
	                    pStmt.setInt(index++, document.getLastChangerId().intValue());
	                } else {
	                    pStmt.setNull(index++, Types.INTEGER);
	                }
	
	                if (document.getAccessCount() != null) {
	                    pStmt.setInt(index++, document.getAccessCount().intValue());
	                } else {
	                    pStmt.setNull(index++, Types.INTEGER);
	                }
	
	                if (document.getPreDocId() != null) {
	                    pStmt.setInt(index++, document.getPreDocId().intValue());
	                } else {
	                    pStmt.setNull(index++, Types.INTEGER);
	                }
	
	                if (document.getDocSize() != null) {
	                    pStmt.setLong(index++, document.getDocSize().longValue());
	                } else {
	                    pStmt.setNull(index++, Types.BIGINT);
	                }
	
	                if (document.getZipped() != null) {
	                    pStmt.setInt(index++, document.getZipped().intValue());
	                } else {
	                    pStmt.setNull(index++, Types.INTEGER);
	                }
	
	                if (document.getLangPackId() != null) {
	                    pStmt.setInt(index++, document.getLangPackId().intValue());
	                } else {
	                    pStmt.setNull(index++, Types.INTEGER);
	                }
	
	                if (document.getLockUserId() != null) {
	                    pStmt.setInt(index++, document.getLockUserId().intValue());
	                } else {
	                    pStmt.setNull(index++, Types.INTEGER);
	                }
	
	                if (document.getAuthorName() != null) {
	                    pStmt.setString(index++, document.getAuthorName());
	                } else {
	                    pStmt.setNull(index++, Types.VARCHAR);
	                }
	
	                pStmt.setInt(index++, document.getDocumentId().intValue());
	
	                pStmt.execute();
	            } finally {
	                DBHelper.close(pStmt);
	            }
            	
				
				if (document.getCategoryId() != null) {
					try{
					pStmt = conn
							.prepareStatement("INSERT INTO CategoryDocuments ( CATDOCID, CATEGORYID, DOCUMENTID, INITIALCATEGORY, CREATED, CREATORID ) VALUES ( ?, ?, ?, ?, ?, ? )");
					pStmt.setInt(1, DBHelper.getNewId(conn,
							"CategoryDocuments", "catDocId").intValue());
					pStmt.setInt(2, document.getCategoryId().intValue());
					pStmt.setInt(3, document.getDocumentId().intValue());
					pStmt.setInt(4, VKCCategory.ROOT_CATEGORY_ID);
					pStmt.setTimestamp(5, new Timestamp(System
							.currentTimeMillis()));
					pStmt.setInt(6, document.getPublisherId().intValue());
					pStmt.execute();
					}catch (Exception e) {
						throw new DBException(e);
					}finally{
						DBHelper.close(pStmt);
					}
				}

				// Additional Attributes speichern
				VKCAddAttributeSerializer.saveAddDocAttributes(conn, document);

				// Dynamische Attribute speichern
				VKCDynaAttributeSerializer.saveDynaAttributes(conn, document);
				
				// Dokument dem Suchindex zufügen
				// !!! Danach muss unbedinngt die langPackId gespeichert werden,
				// da diese erst beim Einfügen
				// in den Suchindex ermittelt und im VKCDocument-Objekt gesetzt
				// wird !!!
				
				if (getPublishNow(document)&&document.getCategoryId()!=null) {
					categories.add(document.getCategoryId());
                    Search.addDocument(document, true);
                    
                    if (document.getLangPackId() != null) {
                        DBHelper.close(pStmt);
                        pStmt = conn.prepareStatement("UPDATE documents SET langPackId = ? WHERE documentId = ?");
                        pStmt.setInt(1, document.getLangPackId().intValue());
                        pStmt.setInt(2, document.getDocumentId().intValue());
                        pStmt.execute();
                    }
                }

				
				// Assozierte Schlüsselbegriffe speichern
				VKCKeywordSerializer.saveKeywords(conn, document.getKeywords(),
						"DocumentKeywords", "documentId", document
								.getDocumentId());				
			
			} catch (Exception e) {
				throw new DBException(e);
			}
		}
		
	}

	
	public static boolean getPublishNow(VKCDocument doc) {
            Date now = DateUtils.getMinimizedDayTime(new Date(System.currentTimeMillis()));
            Date p = doc.getPublished();            
            if (now.before(p)) {
                return false;
            }
       

        return true;
	}
}