/*
 * $Id: VKCCategorySerializer.java,v 1.100 2008/02/12 09:39:48 korowa Exp $
 *
 * Copyright (c) Siemens AG 2008 
 *           (c) Universität Paderborn 2008 
 * Licensed under EUPL 1.1. 
 *
 * $Log: VKCCategorySerializer.java,v $
 * Revision 1.100  2008/02/12 09:39:48  korowa
 * + Eine Möglichkeit für den Benutzer hinzugefügt die Emailsart auszuwählen (HTML oder Plaintext)
 * + Betreff für den Messenger der als Emailsubject bei Benachrichtigung übers erscheinen von VKC Dokumenten verschickt wird.
 *
 * Revision 1.99  2007/10/30 13:44:15  marbobo
 * + removed unnecessary statements
 *
 * Revision 1.98  2007/08/28 12:15:50  korowa
 * Export- und Importfunktionen hinzugefügt
 *
 * Revision 1.97  2007/08/14 14:49:55  marbobo
 * + Licenseverwaltung komplett umgeschrieben
 * + Kategoriesuche und verlorene Kategorien anzeigen komplett umgeschrieben
 * + Fehler beim Speichern von Beschreibenungsfeldern für Entitäten beseitigt.
 *
 * Revision 1.96  2007/08/08 12:14:41  marbobo
 * + License-Funktionalität komplett umgeschrieben
 * + Bugfix bzgl. Beschreibungsfelder von Entitäten
 * + Bugfix mailing
 *
 * Revision 1.95  2007/05/29 08:18:26  korowa
 * Suchen nach Kategorien hinzugefügt
 *
 * Revision 1.94  2007/03/05 09:54:43  marbobo
 * + NPE Abgefangen
 *
 * Revision 1.93  2007/03/01 15:02:55  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.92  2006/11/14 09:13:25  korowa
 * Anpassung retrieveLostFolder() an Syntax von Firebird 1.5
 *
 * Revision 1.91  2006/11/10 12:52:06  korowa
 * Funktion hinzugefügt, die alle Kategorien findet auf die kein Benutzer Leserechte hat
 *
 */

package vkc.db.serializer;

import java.io.IOException;
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.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.Vector;

import java.sql.Connection;

import vkc.db.entities.VKCCatProjectStep;
import vkc.db.entities.VKCCategory;
import vkc.db.entities.VKCDocument;
import vkc.db.entities.VKCEntity;
import vkc.db.entities.VKCPermission;
import vkc.db.entities.VKCSearchQuery;
import vkc.db.entities.VKCSubCategory;
import vkc.db.entities.VKCUser;
import vkc.db.helper.DBHelper;
import vkc.exception.DBException;
import vkc.misc.VKCLogManager;
import vkc.procoba.project.entities.VKCProject;
import vkc.procoba.project.entities.VKCProjectSubfolder;
import vkc.search.Search;
import vkc.util.CategoryUtils;
import vkc.util.StringUtils;
import vkc.util.VKCCategoryAbsPath;
import vkc.util.VKCCategoryPathNode;
import vkc.util.sort.SubCategorySortFactory;
import vkc.util.treeview.TreeViewManager;
import vkc.view.WebShare;
import vkc.view.bean.RegisteredCategory;

public class VKCCategorySerializer {
    private VKCCategorySerializer() {
    }

    /**
     * Lädt die Kategorie mit der übergebenen ID aus der Datenbank
     *
     * @param categoryId ID der zu ladenden Kategorie
     */
    public final static VKCCategory retrieveCategory(Integer categoryId) throws DBException {
        if (categoryId == null) {
            return null;
        }

        Connection conn = null;

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

    /**
     * Lädt die Kategorie mit der übergebenen ID aus der Datenbank
     *
     * @param categoryId ID der zu ladenden Kategorie
     */
    public final static VKCCategory retrieveCategory(Integer categoryId, Connection conn) throws DBException {
        if (categoryId == null || categoryId.intValue() <= 0) {
            return null;
        }

        PreparedStatement pStmt1 = null;
        PreparedStatement pStmt2 = null;
        ResultSet rs = null;
        VKCCategory category = null;
        
        try {
            pStmt1 = conn.prepareStatement("SELECT * FROM CATEGORIES WHERE CATEGORYID = ?");
            pStmt1.setInt(1, categoryId.intValue());
            rs = pStmt1.executeQuery();

            if (rs.next()) {
                int type = rs.getInt("categoryType");

                if (type == VKCCategory.TYPE_PROJECT) {
                    category = new VKCProject();
                    setFields(category, rs);
                    retrieveProjectFields((VKCProject)category, conn);
                } else if (type == VKCCategory.TYPE_PROJECT_SUBFOLDER) {
                    category = new VKCProjectSubfolder();
                    setFields(category, rs);
                    retrieveProjectSubfolderFields((VKCProjectSubfolder)category, conn);
                } else {
                    category = new VKCCategory();
                    setFields(category, rs);
                }
            } else {
                return null;
            }
            
            //Beschreibung setzen
            if (category.getNoteId() != null) {
                String description = VKCNoteSerializer.retrieveNote(category.getNoteId(), conn);
                
                if (description != null) {
                    category.setDescription(description);
                } else {
                    category.setDescription("");
                }
            } else {
                category.setDescription("");
            }
            

            //  get associated keywords...
            category.setKeywords(VKCKeywordSerializer.retrieveKeywords(conn, "categoryKeywords", "categoryId", category.getCategoryId()));

            // set Acl
            category.setAcl(VKCAclSerializer.retrieveAcl(category.getAcl().getAclId(), conn));
            
            // consider if subcategory is empty ?
			boolean computeCount = Integer.parseInt(WebShare.getSysParameterValue("CATEGORYVIEW", "DisplayEmptyFoldersWithEmptyIcon", "0")) != 0 ? true : false;  

			// is trash enabled ?
			boolean trashEnabled = Integer.parseInt(WebShare.getSysParameterValue("TRASH", "EnableTrash", "0")) != 0 ? true : false;
			Integer trashCatId = new Integer(WebShare.getSysParameterValue("TRASH", "TrashCategoryId", "0"));
			
			if (!trashEnabled) {
	            pStmt2 = conn.prepareStatement("SELECT categoryId, flag, " +
                                               "       status, name, " +
                                               "       creatorId, created," +
                                               "       lastChangerId, lastchanged," +
                                               "       lastAccessorId, lastaccessed " +
                                               "  FROM Categories " +
                                               " WHERE parentCategoryId = ?");
                pStmt2.setInt(1, category.getCategoryId().intValue());
			} else {
			    // prevent to load TrashCategory	            
			    pStmt2 = conn.prepareStatement("SELECT categoryId, flag, " +
          		                               "       status, name, " +
	            		                       "       creatorId, created," +
	            		                       "       lastChangerId, lastchanged," +
	            		                       "       lastAccessorId, lastaccessed " +
	            		                       "  FROM Categories " +
	            		                       "  WHERE parentCategoryId = ?" +
	            		                       "    AND categoryId <> ?");
	            pStmt2.setInt(1, category.getCategoryId().intValue());
	            pStmt2.setInt(2, trashCatId.intValue());
			}
			    
            ResultSet result = pStmt2.executeQuery();

            try {
                while (result.next()) {
                    VKCSubCategory subCat = new VKCSubCategory();
                    subCat.setSubCategoryId(new Integer(result.getInt("categoryId")));
                    subCat.setCreatorId(new Integer(result.getInt("creatorId")));
                    subCat.setCreated(result.getTimestamp("created"));
                    subCat.setStatus(new Integer(result.getInt("status")));
					subCat.setFlag(new Integer(result.getInt("flag")));
                    subCat.setName(result.getString("name"));
                    subCat.setLastChangerId(new Integer(result.getInt("lastChangerId")));
                    subCat.setLastChanged(result.getTimestamp("lastchanged"));
                    subCat.setLastAccessorId(new Integer(result.getInt("lastAccessorId")));
                    subCat.setLastAccessed(result.getTimestamp("lastaccessed"));
                    
                    if (computeCount) {
                        subCat.setEmpty(isEmpty(subCat.getSubCategoryId(), conn));                  	
                    } else {
                    	// by default: this subcategory is defined not to be empty
                    	subCat.setEmpty(false);
                    }
                    
                    category.addSubCategory(subCat);
                }
            } finally {
                DBHelper.close(result);
            }
            // Unterkategorien sortieren
            SubCategorySortFactory.sort(category.getSubCategories(), SubCategorySortFactory.SORT_BY_NAME_UP);

            
            // alle dokumente holen           
            Integer flag = VKCDocument.FLAG_PUBLISHED;

            if (trashCatId.intValue() == categoryId.intValue() || VKCCategory.FLAG_DELETED.equals(VKCCategorySerializer.retrieveFlag(categoryId, conn))) {
                flag = VKCDocument.FLAG_DELETED;   
            } 
            
            String query = " SELECT Documents.documentId FROM CategoryDocuments, Documents " +
 			                " WHERE CategoryDocuments.categoryId = ?" +
 			                  " AND CategoryDocuments.documentId = Documents.documentId" +
 			                  " AND Documents.flag = " + flag +
 			                  " order by Documents.published desc";
		    
		    Object[] params = {categoryId};
            Collection docs = VKCDocumentSerializer.getDocumentsByQuery(conn, query, params);

            category.getDocuments().addAll(docs);

            // globale Attribute anbinden
            category.setAddAttributes(VKCAddAttributeSerializer.retrieveAddCatAttributes(conn, category));

            // Dynamische Attribute anbinden
            VKCDynaAttributeSerializer.retrieveDynaAttributes(conn, category);

            return category;
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(rs);
            DBHelper.close(pStmt1);
            DBHelper.close(pStmt2);
        }
    }
    
    private final static void retrieveProjectFields(VKCProject project, Connection conn) throws DBException {
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        
        try {
            pStmt = conn.prepareStatement("SELECT * FROM PROJECTS WHERE CATEGORYID = ?");
            pStmt.setInt(1, project.getCategoryId().intValue());
            rs = pStmt.executeQuery();
            
            if (rs.next()) {
                project.setExternalContacts(rs.getString("EXTERNALCONTACTS"));
            } else {
            	throw new DBException("Project fields for categeory with id " + project.getCategoryId() + " missing in table PROJECTS");
            }
                        
            // Project Steps laden
            retrieveProjectSteps(conn, project);
        } catch (Exception ex) {
            throw new DBException(ex);
        } finally {
            DBHelper.close(rs);
            DBHelper.close(pStmt);
        }
    }

    private final static void retrieveProjectSubfolderFields(VKCProjectSubfolder projectSubfolder, Connection conn) throws DBException {
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        
        try {
            pStmt = conn.prepareStatement("SELECT * FROM PROJECTSUBFOLDERS WHERE CATEGORYID = ?");
            pStmt.setInt(1, projectSubfolder.getCategoryId().intValue());
            rs = pStmt.executeQuery();
            
            if (rs.next()) {
                projectSubfolder.setProjectId(new Integer(rs.getInt("PROJECTID")));
			} else {
				throw new DBException("Projectsubfolder fields for categeory with id " + projectSubfolder.getCategoryId() + " missing in table PROJECTSUBFOLDERS");
            }
        } catch (Exception ex) {
            throw new DBException(ex);
        } finally {
            DBHelper.close(rs);
            DBHelper.close(pStmt);
        }
    }

    private final static void setFields(VKCCategory category, ResultSet rs) throws SQLException, DBException {
        category.setCategoryId(new Integer(rs.getInt("categoryId")));

        category.setName(rs.getString("name"));
        
        category.setLastAccessorId(new Integer(rs.getInt("lastAccessorId")));
        if (rs.wasNull()) category.setLastAccessorId(null);
        
        category.setParentCategoryId(new Integer(rs.getInt("parentCategoryId")));
        if (rs.wasNull()) category.setParentCategoryId(null);

        category.getAcl().setAclId(new Integer(rs.getInt("aclId")));
        if (rs.wasNull()) category.getAcl().setAclId(null);

        category.setDynaAttrsId(new Integer(rs.getInt("dynattrsid")));
        if (rs.wasNull()) category.setDynaAttrsId(null);

        category.setLastChangerId(new Integer(rs.getInt("lastChangerId")));
        if (rs.wasNull()) category.setLastChangerId(null);

        category.setCreatorId(new Integer(rs.getInt("creatorId")));
        if (rs.wasNull()) category.setCreatorId(null);

        category.setAccessCount(new Integer(rs.getInt("accessCounter")));
        if (rs.wasNull()) category.setAccessCount(null);

        category.setStatus(new Integer(rs.getInt("status")));
        if (rs.wasNull()) category.setStatus(null);

        category.setFlag(new Integer(rs.getInt("flag")));
        if (rs.wasNull()) category.setFlag(null);

        category.setCreated(rs.getTimestamp("created"));
        if (rs.wasNull()) category.setCreated(null);
        
        category.setLastChanged(rs.getTimestamp("lastChanged"));
        if (rs.wasNull()) category.setLastChanged(null);
        
        category.setLastAccessed(rs.getTimestamp("lastAccessed"));
        if (rs.wasNull()) category.setLastAccessed(null);

        category.setShowDescription(rs.getInt("showDescription") != 0);
        category.setInitialCategory(rs.getInt("initialCategory") != 0);       

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

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

    public final static void saveCategory(VKCCategory category, Connection conn) throws DBException {
        try {
            VKCAclSerializer.saveAcl(category.getAcl(), conn);            
            if (category.getCategoryId() != null && category.getCategoryId().intValue() > 0) {
                String stmt = "UPDATE CATEGORIES SET ";
                stmt += "name = ?, ";
                stmt += "lastAccessorId = ?, ";
                stmt += "parentCategoryId = ?, ";
                stmt += "aclId = ?, ";
                stmt += "dynAttrsId = ?, ";
                stmt += "showDescription = ?, ";
                stmt += "lastChangerId = ?, ";
                stmt += "lastChanged = ?, ";
                stmt += "initialCategory = ?, ";
                stmt += "lastAccessed = ?, ";
                stmt += "noteId = ?, ";
                stmt += "creatorId = ?, ";
                stmt += "created = ?, ";
                stmt += "accessCounter = ?, ";
                stmt += "categoryType = ?, ";				
                stmt += "status = ?, ";				
                stmt += "flag = ? ";				
                stmt += "WHERE CATEGORYID = ?";

                executeCategoryStatement(stmt, category, conn);

				if (category.isProjectCategory()) {
					String projectStmt = "UPDATE PROJECTS SET ";
					projectStmt += "EXTERNALCONTACTS = ? ";
					projectStmt += "WHERE CATEGORYID = ?";
					
					executeProjectStatement(projectStmt, (VKCProject)category, conn);
				} else if (category.isProjectSubfolder()) {
					String folderStmt = "UPDATE PROJECTSUBFOLDERS SET ";
					folderStmt += "PROJECTID = ? ";
					folderStmt += "WHERE CATEGORYID = ?";

					executeProjectSubfolderStatement(folderStmt, (VKCProjectSubfolder)category, conn);
				}

                // Kategorie im Suchindex aktualisieren
				WebShare.classifier.updateCategory(category);

                // Update the tree view
                TreeViewManager.getInstance().update(category);
            } else {
                // NEU INSERIEREN
                category.setCategoryId(DBHelper.getNewId(conn, "Categories", "categoryId"));
                
                String stmt = "INSERT INTO CATEGORIES ( ";
                stmt += "name, ";
                stmt += "lastAccessorId, ";
                stmt += "parentCategoryId, ";
                stmt += "aclId, ";
                stmt += "dynAttrsId, ";
                stmt += "showDescription, ";
                stmt += "lastChangerId, ";
                stmt += "lastChanged, ";
                stmt += "initialCategory, ";
                stmt += "lastAccessed, ";
                stmt += "noteId, ";
                stmt += "creatorId, ";
                stmt += "created, ";
                stmt += "accessCounter, ";
                stmt += "categoryType, ";				
                stmt += "status, ";				
                stmt += "flag, ";				
                stmt += "CATEGORYID ) ";                
                stmt += "values ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) ";

                executeCategoryStatement(stmt, category, conn);

				if (category.isProjectCategory()) {
					String projectStmt = "INSERT INTO PROJECTS ( ";
					projectStmt += "EXTERNALCONTACTS, ";
					projectStmt += "CATEGORYID ) ";
					projectStmt += "values ( ?, ? ) ";
					
					executeProjectStatement(projectStmt, (VKCProject)category, conn);
				} else if (category.isProjectSubfolder()) {
					String folderStmt = "INSERT INTO PROJECTSUBFOLDERS ( ";
					folderStmt += "PROJECTID, ";
					folderStmt += "CATEGORYID ) ";
					folderStmt += "values ( ?, ? ) ";

					executeProjectSubfolderStatement(folderStmt, (VKCProjectSubfolder)category, conn);
				}

                //Kategorie dem Suchindex zufügen
				WebShare.classifier.addCategory(category);

                // Update the tree view
                TreeViewManager.getInstance().add(category);
            }

            // Assozierte Schlüsselbegriffe speichern
            VKCKeywordSerializer.saveKeywords(conn, category.getKeywords(), "CategoryKeywords", "categoryId", category.getCategoryId());

            // additional category attributes speichern
            VKCAddAttributeSerializer.saveAddCatAttributes(conn, category);

            // Dynamische Attribute speichern/updaten
            VKCDynaAttributeSerializer.saveDynaAttributes(conn, category);
            
            if (category instanceof VKCProject) {
				VKCProject project = (VKCProject)category;
				saveProjectSteps(conn, project);
			}

            WebShare.database.setStructureChangedAt(System.currentTimeMillis());
        } catch (Exception ex) {
            if (category.getCategoryId() != null) {
                try { // Kategorie aus dem Suchindex löschen
					WebShare.classifier.removeCategory(category.getCategoryId());
                } catch (IOException ioe) { 
                    VKCLogManager.errorLog(VKCCategorySerializer.class, "", ioe.getMessage(), ioe);
                }
            }

            WebShare.database.setStructureChangedAt(System.currentTimeMillis());

            throw new DBException(ex);
        }
    }

    /**
     * Speichert eine Kategorie in der Datenbank ab Falls die Kategorie bereits
     * existiert, werden die Eigenschaften dieser Kategorie aktualisiert.
     *
     * @param category das zu speichernde Kategorieobjekt
     */
    public final static void saveCategory(VKCCategory category) throws DBException {
        Connection conn = null;

        try {
            conn = WebShare.database.getConnection();
            saveCategory(category, conn);
            conn.commit();
        } catch (Exception e) {
            VKCLogManager.errorLog(VKCCategorySerializer.class, "", e.getMessage(), e);
            DBHelper.rollBack(conn);

            // Mögliche Änderungen am Treeview zurücksetzen
            TreeViewManager.getInstance().requestUpdate();

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

    private final static void executeCategoryStatement(String stmt, VKCCategory category, Connection conn) throws SQLException, DBException {
        PreparedStatement pStmt = null; 

        try {
            //NoteId in abh. von der Beschreibung setzen
            if (StringUtils.isEmpty(category.getDescription())) {
                category.setNoteId(null);
            } else {
                category.setNoteId(VKCNoteSerializer.saveNote(category.getCategoryId(), "CATEGORIES", category.getDescription(), conn));
            }

            pStmt = conn.prepareStatement(stmt);
            int index = 1;
            
            pStmt.setString(index++, category.getName());
                    
            if (category.getLastAccessorId() == null) {
                pStmt.setNull(index++, Types.INTEGER);
            } else {
                pStmt.setInt(index++, category.getLastAccessorId().intValue());
            }
            
            if (category.getParentCategoryId() == null) {
                pStmt.setNull(index++, Types.INTEGER);
            } else {
                pStmt.setInt(index++, category.getParentCategoryId().intValue());
            }
            
            pStmt.setInt(index++, category.getAcl().getAclId().intValue());
    
            if (category.getDynaAttrsId() == null) {
                pStmt.setNull(index++, Types.INTEGER);
            } else {
                pStmt.setInt(index++, category.getDynaAttrsId().intValue());
            }
            
            pStmt.setInt(index++, category.getShowDescription() ? 1 : 0);
    
            if (category.getLastChangerId() == null) {
                pStmt.setNull(index++, Types.INTEGER);
            } else {
                pStmt.setInt(index++, category.getLastChangerId().intValue());
            }
            
            if (category.getLastChanged() == null) {
                pStmt.setNull(index++, Types.TIMESTAMP);
            } else {
                pStmt.setTimestamp(index++, new Timestamp(category.getLastChanged().getTime()));
            }
            
            pStmt.setInt(index++, category.isInitialCategory() ? 1 : 0);
    
            if (category.getLastAccessed() == null) {
                pStmt.setNull(index++, Types.TIMESTAMP);
            } else {
                pStmt.setTimestamp(index++, new Timestamp(category.getLastAccessed().getTime()));
            }
            
            if (category.getNoteId() == null) {
                pStmt.setNull(index++, Types.INTEGER);
            } else {
                pStmt.setInt(index++, category.getNoteId().intValue());
            }
    
            if (category.getCreatorId() == null) {
                pStmt.setNull(index++, Types.INTEGER);
            } else {
                pStmt.setInt(index++, category.getCreatorId().intValue());
            }
            
            if (category.getCreated() == null) {
                pStmt.setNull(index++, Types.TIMESTAMP);
            } else {
                pStmt.setTimestamp(index++, new Timestamp(category.getCreated().getTime()));
            }
            
            if (category.getAccessCount() == null) {
                pStmt.setNull(index++, Types.INTEGER);
            } else {
                pStmt.setInt(index++, category.getAccessCount().intValue());
            }
            
            if (category.isProjectCategory()) {
                pStmt.setInt(index++, VKCCategory.TYPE_PROJECT);
            } else if (category.isProjectSubfolder()) {
                pStmt.setInt(index++, VKCCategory.TYPE_PROJECT_SUBFOLDER);
            } else { 
                pStmt.setInt(index++, VKCCategory.TYPE_CATEGORY);
            }

            if (category.getStatus() == null) {
                pStmt.setNull(index++, Types.INTEGER);
            } else {
                pStmt.setInt(index++, category.getStatus().intValue());
            }
            
            if (category.getFlag() == null) {
                pStmt.setNull(index++, Types.INTEGER);
            } else {
                pStmt.setInt(index++, category.getFlag().intValue());
            }
            
            pStmt.setInt(index++, category.getCategoryId().intValue());
            pStmt.execute();
        } finally {
            DBHelper.close(pStmt);
        }
    }

	private final static void executeProjectStatement(String stmt, VKCProject project, Connection conn)	throws SQLException {
		PreparedStatement pStmt = null; 

		try {
			pStmt = conn.prepareStatement(stmt);
			pStmt.setString(1, project.getExternalContacts());
			pStmt.setInt(2, project.getCategoryId().intValue());
			pStmt.execute();
		} finally {
			DBHelper.close(pStmt);
		}
	}

	private final static void executeProjectSubfolderStatement(String stmt, VKCProjectSubfolder subfolder, Connection conn)	throws SQLException {
		PreparedStatement pStmt = null; 

		try {
			pStmt = conn.prepareStatement(stmt);
			pStmt.setInt(1, subfolder.getProjectId().intValue());
			pStmt.setInt(2, subfolder.getCategoryId().intValue());
			pStmt.execute();
		} finally {
			DBHelper.close(pStmt);
		}
	}

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

        try {
            conn = WebShare.database.getConnection();
            deleteCategory(user, categoryDelId, definite,  userTrashCategoryId, conn);
            conn.commit();
        } catch (Exception e) {
            VKCLogManager.errorLog(VKCCategorySerializer.class, "", e.getMessage(), e);
            DBHelper.rollBack(conn);
            throw new DBException(e);
        } finally {
            DBHelper.close(conn);
        }
    }
	
	
    /**
     * Löscht eine Kategorie aus der Datenbank Es werden jeweils nur die
     * Verknüpfungen aus den assozierten Tabellen gelöscht, <b>nicht</b> die
     * verknüpften Entitäten selber
     *
     * @param categoryDelId ID der zu löschenden Kategorie
     */
    public static final void deleteCategory(VKCUser user, Integer categoryDelId, boolean definite, Integer userTrashCategoryId, Connection conn) throws DBException {
        PreparedStatement pStmt = null;
        PreparedStatement pStmt1 = null;
        PreparedStatement pStmt2 = null;

        try {
            Vector tmpCatIds = new Vector();
            tmpCatIds.add(categoryDelId);
            
            if (definite) {
	            while (!tmpCatIds.isEmpty()) {
	                Integer categoryId = (Integer) tmpCatIds.lastElement();
	
	                // Kategorie aus dem Suchindex löschen
					WebShare.classifier.removeCategory(categoryId);

                    DBHelper.close(pStmt);
					pStmt = conn.prepareStatement("SELECT categoryId FROM Categories WHERE parentCategoryId = ?");
	                pStmt.setInt(1, categoryId.intValue());
		            ResultSet result = pStmt.executeQuery();
	
	                boolean hasSubCategories = false;
	
	                // alle Unterkategorien löschen
	                while (result.next()) {
	                    tmpCatIds.add(new Integer(result.getInt("categoryId")));
	                    hasSubCategories = true;
	                }
	
	                result.close();
	
	                if (hasSubCategories) {
	                    continue;
	                }
	
	                DBHelper.close(pStmt);
		            pStmt = conn.prepareStatement("DELETE FROM CategoryKeywords WHERE categoryId = ?");
	                pStmt.setInt(1, categoryId.intValue());
	                pStmt.execute();
	                DBHelper.close(pStmt);
		            pStmt = conn.prepareStatement("DELETE FROM AllowedDocumentTypes WHERE categoryId = ?");
	                pStmt.setInt(1, categoryId.intValue());
	                pStmt.execute();
	                DBHelper.close(pStmt);
		            pStmt = conn.prepareStatement("DELETE FROM UserRegisteredCategories WHERE categoryId = ?");
	                pStmt.setInt(1, categoryId.intValue());
	                pStmt.execute();
	                DBHelper.close(pStmt);
		            pStmt = conn.prepareStatement("DELETE FROM AddCatAttrValues WHERE categoryId = ?");
	                pStmt.setInt(1, categoryId.intValue());
	                pStmt.execute();
	                DBHelper.close(pStmt);
		            pStmt = conn.prepareStatement("DELETE FROM DefaultCatAcls WHERE startcatid = ?");
	                pStmt.setInt(1, categoryId.intValue());
	                pStmt.execute();
	                DBHelper.close(pStmt);
		            pStmt = conn.prepareStatement("DELETE FROM SearchQueries WHERE categoryId = ?");
	                pStmt.setInt(1, categoryId.intValue());
	                pStmt.execute();
	                DBHelper.close(pStmt);
		            pStmt = conn.prepareStatement("DELETE FROM CatProjectSteps WHERE categoryId = ?");
	                pStmt.setInt(1, categoryId.intValue());
	                pStmt.execute();
	
	                VKCCategoryFavoritesSerializer.deleteCategoryFavorite(null, categoryId, conn);
	
	                // get aclId
	                int aclId = 0;
	                DBHelper.close(pStmt);
		            pStmt = conn.prepareStatement("SELECT aclId FROM categories WHERE categoryId = ?");
	                pStmt.setInt(1, categoryId.intValue());
	                result = pStmt.executeQuery();
	
	                while (result.next()) {
	                    aclId = result.getInt("aclId");
	                }
	
	                result.close();
	                DBHelper.close(pStmt);
	                
		            pStmt = conn.prepareStatement("DELETE FROM CategoryAcls WHERE aclId = ?");
	                pStmt.setInt(1, aclId);
	                pStmt.execute();
	                DBHelper.close(pStmt);
	                    
	                // Enthaltene Dokumente löschen...
		            pStmt = conn.prepareStatement("SELECT documentId FROM CategoryDocuments WHERE categoryId = ?");
	                pStmt.setInt(1, categoryId.intValue());
	                result = pStmt.executeQuery();
	
	                Vector ids = new Vector();
	
	                while (result.next()) {
	                    ids.add(new Integer(result.getInt("documentId")));
	                }

	                result.close();
	                //JP: an dieser stelle wäre ein parameter geeignet...
	                
	                for (int i = 0; i < ids.size(); i++) {
	                    VKCDocumentSerializer.deleteDocument(user, (Integer) ids.get(i), true, null, false, conn);
	                }
	                
                    //Beschreibung löschen
                    VKCNoteSerializer.deleteNote(categoryId, "CATEGORIES", conn);

                    DBHelper.close(pStmt);
		            pStmt = conn.prepareStatement("DELETE FROM Categories WHERE categoryId = ?");
	                pStmt.setInt(1, categoryId.intValue());
	                pStmt.execute();
                    
	                tmpCatIds.remove(categoryId);
	            }
	            
	            TreeViewManager.getInstance().remove(categoryDelId.toString());
	        } else {
	            move2Trash(conn, categoryDelId, user, userTrashCategoryId);
	        }

            WebShare.database.setStructureChangedAt(System.currentTimeMillis());
        } catch (Exception e) {
            WebShare.database.setStructureChangedAt(System.currentTimeMillis());
            throw new DBException(e);
        } finally {
            DBHelper.close(pStmt);
            DBHelper.close(pStmt1);
            DBHelper.close(pStmt2);            
        }
    }

    private final static void move2Trash(Connection conn, Integer categoryId, VKCUser user, Integer userTrashCategoryId) throws DBException {
	     if (categoryId == null || categoryId.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;
	     PreparedStatement pStmt2 = null;
	
	     try {
	         pStmt = conn.prepareStatement("UPDATE Categories SET parentCategoryId = ? WHERE categoryId = ?");
	         pStmt.setInt(1, userTrashCategoryId.intValue());
	         pStmt.setInt(2, categoryId.intValue());
	         pStmt.execute();
	         DBHelper.close(pStmt);

	         // für die lösch-root-kategorie und alle unterkategorien...
	         Set catIds = CategoryUtils.getAllSubCategoryIdsRecursiv(categoryId, conn);
	         catIds.add(categoryId);
	         
	         for (Iterator iter = catIds.iterator(); iter.hasNext();) {
                Integer delCatId = (Integer) iter.next();	             

                // ... aus dem Suchindex löschen
				WebShare.classifier.removeCategory(delCatId);
             
				// ... aus den Kategoriefavoriten löschen
				VKCCategoryFavoritesSerializer.deleteCategoryFavorite(null, delCatId, conn);

				// ... aus den registrierten Kategorien löschen
				pStmt = conn.prepareStatement("DELETE FROM UserRegisteredCategories WHERE categoryId = ?");
                pStmt.setInt(1, delCatId.intValue());
                pStmt.execute();
                DBHelper.close(pStmt);
				
				// ... alle vorhandenen Dokumente dieser Kategorie löschen
	            pStmt = conn.prepareStatement("SELECT documentId FROM CategoryDocuments WHERE categoryId = ?");
                pStmt.setInt(1, delCatId.intValue());
                ResultSet rs = pStmt.executeQuery();
                
                while (rs.next()) {
                    Integer documentId = new Integer(rs.getInt("documentId"));

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

	                pStmt2 = conn.prepareStatement("DELETE FROM ConversionStati WHERE documentId = ?");
	                pStmt2.setInt(1, documentId.intValue());
	                pStmt2.execute();
	                DBHelper.close(pStmt2);
	                
	                VKCUserDocHistorySerializer.deleteDocumentHistory(null, documentId, conn);
	                VKCDocumentFavoritesSerializer.deleteDocumentFavorite(null, documentId, conn);
	                
                    //	Dokument aus dem Suchindex entfernen
	                Search.removeDocument(documentId, true);
	                
	                VKCDocumentSerializer.setFlag(documentId, VKCDocument.FLAG_DELETED, true, conn);
                }
                
                DBHelper.close(rs);
                DBHelper.close(pStmt);

                // ... alles getan, also flag auf deleted setzen
                VKCCategorySerializer.setFlag(delCatId, VKCCategory.FLAG_DELETED, null, false, conn);
	         }	         
         
	         TreeViewManager.getInstance().move(categoryId, userTrashCategoryId.toString());
	     } catch (Exception ex) {
	         throw new DBException(ex);
	     }
    }
    
    public final static void setFlag(Integer categoryId, Integer categoryFlag, Integer documentFlag, boolean recursive) throws DBException {
        Connection conn = null;

        try {
            conn = WebShare.database.getConnection();
            setFlag(categoryId, categoryFlag, documentFlag, recursive, 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 categoryId, Integer categoryFlag, Integer documentFlag, boolean recursive, Connection conn) throws DBException {
        if (categoryId == null) {
            return;
        }
        
        PreparedStatement pStmt = null;
        PreparedStatement pStmt2 = null;
        
        try {
            if (!recursive) {
                pStmt = conn.prepareStatement("UPDATE categories SET flag = ? WHERE categoryId = ?");
                pStmt.setInt(1, categoryFlag.intValue());
                pStmt.setInt(2, categoryId.intValue());
                pStmt.execute();
            } else {
                Set catIds = CategoryUtils.getAllSubCategoryIdsRecursiv(categoryId, conn);
	            catIds.add(categoryId);
	            
                // select all documents
	            String statement = " SELECT Documents.documentId " + 
            					   " FROM CategoryDocuments, Documents " + 
            					   " WHERE CategoryDocuments.categoryId = ?" + 
            					   " AND CategoryDocuments.documentId = Documents.documentId";
	            pStmt = conn.prepareStatement(statement);
	            
	            pStmt2 = conn.prepareStatement("UPDATE Categories SET flag = " + categoryFlag + " WHERE categoryId = ?");
	            
	            for (Iterator it = catIds.iterator(); it.hasNext();) {
	                Integer catId = (Integer)it.next();
	                Set docIds = new HashSet();

	                pStmt.setInt(1, catId.intValue());
	                ResultSet rs = pStmt.executeQuery();
	                
	                while (rs.next()) {
	                    docIds.add(new Integer(rs.getInt("documentId")));
	                }
	                
		            for (Iterator i = docIds.iterator(); i.hasNext();) {
		                Integer docId = (Integer)i.next();
			            
		                VKCDocumentSerializer.setFlag(docId, documentFlag, true, conn);
		            }
		            
	                rs.close();

	                pStmt2.setInt(1, catId.intValue());
	                pStmt2.execute();
	            }
            }
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(pStmt);
            DBHelper.close(pStmt2);
        }
    }

    public final static Integer retrieveFlag(Integer categoryId) throws DBException {
        Connection conn = null;

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

    public final static Integer retrieveFlag(Integer categoryId, Connection conn) throws DBException {
        if (categoryId == null) {
            return null;
        }
        
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        
        try {
            String statement = " SELECT flag " + 
        					   " FROM categories " + 
        					   " WHERE categoryId = ?"; 

            pStmt = conn.prepareStatement(statement);
            pStmt.setInt(1, categoryId.intValue());

            rs = pStmt.executeQuery();
                
            if (rs.next()) {
            	Integer flag = new Integer(rs.getInt("flag"));
            	
            	if (rs.wasNull()) {
            		return null;
            	}
                
            	return flag; 
            }

            return null;
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
        	DBHelper.close(rs);
            DBHelper.close(pStmt);
        }
    }
    
    /**
     * Gibt eine Sammlung(ArrayList) aller vorhandenen Kategorie-IDs zurück
     *
     * @return categories Sammlung aller VKCCategory-IDs
     *
     * @throws DBException
     */
    public static final Collection getAllCategoryIds() throws DBException {
        Connection conn = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		Collection categories = new ArrayList();

        try {
            conn = WebShare.database.getConnection();
            pStmt = conn.prepareStatement("SELECT CATEGORYID FROM CATEGORIES");
			rs = pStmt.executeQuery();            

            while (rs.next()) {
				Integer cat = new Integer(rs.getInt("CATEGORYID"));
                
                categories.add(cat);
            }

            return categories;
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
			DBHelper.close(rs);
			DBHelper.close(pStmt);
            DBHelper.close(conn);
        }
    }
    
    /**
     * Gibt alle IDs vonf Kategorien zurück, die das gegebene dyn. Attribut
     * mit dem gegebenen Wert haben 
     *
     * @param dynAttrName NAme des dyn. Attrs
     * @param dynAttrValue Wert des dyn. Attrs
     * @return die gewünschten Kategorie-IDs (Integer-Objekte) 
     *
     * @throws DBException
     */
    public static final Collection getIdsOfCategoriesWithDynaAttribute(String dynAttrName, String dynAttrValue) throws DBException {
        Connection conn = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		Collection catIds = new ArrayList();

        try {
            conn = WebShare.database.getConnection();
            String statement = "SELECT CATEGORIES.CATEGORYID AS CATID " +
							   "FROM CATEGORIES, DYNATTRIBUTES " +
							   "WHERE CATEGORIES.DYNATTRSID = DYNATTRIBUTES.DYNATTRID " +
							   "AND DYNATTRIBUTES.NAME = ?" +
							   "AND DYNATTRIBUTES.VALUESTRING = ?";
            pStmt = conn.prepareStatement(statement);
            pStmt.setString(1, dynAttrName);
            pStmt.setString(2, dynAttrValue);
			rs = pStmt.executeQuery();            

            while (rs.next()) {
				Integer catId = new Integer(rs.getInt("CATID"));
                
				catIds.add(catId);
            }

            return catIds;
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
			DBHelper.close(rs);
			DBHelper.close(pStmt);
            DBHelper.close(conn);
        }
    }
    
    /**
      * Speichert Zugriffszähler, Leser, Zugriffsdatum einer Kategorie
      *
      * @param cat Referenz auf ein VKCCategory-Objekt
      */
    public static void updateAccessInformation(VKCCategory cat) throws DBException {
        Connection conn = null;
		PreparedStatement pStmt = null;

        try {
            conn = WebShare.database.getConnection();
            pStmt = conn.prepareStatement("UPDATE Categories SET accessCounter = ?, lastAccessorId = ?, lastAccessed = ? WHERE categoryId = ?");
            pStmt.setInt(1, cat.getAccessCount().intValue());
            pStmt.setInt(2, cat.getLastAccessorId().intValue());
            pStmt.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
            pStmt.setInt(4, cat.getCategoryId().intValue());
            pStmt.execute();
            conn.commit();
        } catch (Exception e) {
            DBHelper.rollBack(conn);
            throw new DBException(e);
        } finally {
            DBHelper.close(pStmt);
            DBHelper.close(conn);
        }
    }

    /**
     * Lädt alle verfügbaren Projekschritte einer Category aus der DB und gibt sie als Vector 
     * zurück.
     * 
     * @return List mit VKCProjectStep Objekten. 
     * @throws DBException
     */
	private static void retrieveProjectSteps(Connection conn, VKCProject p) throws SQLException {
		if (p == null || p.getCategoryId().equals(VKCEntity.ID_UNDEFINED)) {
			return;
		}

		PreparedStatement pStmt = null;
		ResultSet rs = null;

		try {
			pStmt = conn.prepareStatement("SELECT * FROM CATPROJECTSTEPS WHERE CATEGORYID = ? ORDER BY STEP ASC");
			pStmt.setInt(1, p.getCategoryId().intValue());
			rs = pStmt.executeQuery();

			List list = p.getProjectSteps();

			while (rs.next()) {
				VKCCatProjectStep s = new VKCCatProjectStep();
				s.setCatProjStepId(new Integer(rs.getInt("catProjStepId")));
				s.setCategoryId(new Integer(rs.getInt("categoryId")));
				s.setStep(new Integer(rs.getInt("step")));
				s.setEnabled(rs.getInt("enabled") != 0);
				s.setStartDate(rs.getTimestamp("startdate"));
				s.setEndDate(rs.getTimestamp("enddate"));
				s.setActivity(rs.getString("activity"));
				
				int processorId = rs.getInt("processorId");
				
				if (rs.wasNull()) { 
					s.setProcessorId(null);
				} else {
					s.setProcessorId(new Integer(processorId));
				}
				
				s.setProcessedTS(rs.getTimestamp("processedts"));

				int checkerId = rs.getInt("checkerId");
				
				if (rs.wasNull()) { 
					s.setCheckerId(null);
				} else {
					s.setCheckerId(new Integer(checkerId));
				}
				
				s.setCheckedTS(rs.getTimestamp("checkedts"));

				list.add(s);
			}
		} finally {
			DBHelper.close(rs);
			DBHelper.close(pStmt);
		}
	}

	private static void saveProjectSteps(Connection conn, VKCProject p) throws DBException {
		PreparedStatement pStmt = null;

		try { 
			// evtl. vorhandene Aktivitäten löschen
			DBHelper.deleteEntry(conn, "CATPROJECTSTEPS", "CATEGORYID", p.getCategoryId());

			if (!p.getProjectSteps().isEmpty()) {
				String sqlStatement = 
					"INSERT INTO CATPROJECTSTEPS ( CATPROJSTEPID, CATEGORYID, " + 
					"STEP, ENABLED, ACTIVITY, STARTDATE, ENDDATE, PROCESSORID, " + 
					"PROCESSEDTS, CHECKERID, CHECKEDTS ) " +
					"VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )";

				pStmt = conn.prepareStatement(sqlStatement);

				for (Iterator it = p.getProjectSteps().iterator(); it.hasNext();) {
					VKCCatProjectStep step = (VKCCatProjectStep)it.next();
					pStmt.setInt(1, DBHelper.getNewId(conn, "CATPROJECTSTEPS", "CATPROJSTEPID").intValue());
					pStmt.setInt(2, p.getCategoryId().intValue());
					pStmt.setInt(3, step.getStep().intValue());
					pStmt.setInt(4, step.isEnabled() ? 1 : 0);
					pStmt.setString(5, step.getActivity());
					pStmt.setTimestamp(6, step.getStartDate());
					pStmt.setTimestamp(7, step.getEndDate());
                	
					if (step.getProcessorId() == null) {
						pStmt.setNull(8, Types.INTEGER);
					} else {
						pStmt.setInt(8, step.getProcessorId().intValue());
					}

					if (step.getProcessedTS() == null) {
						pStmt.setNull(9, Types.TIMESTAMP);
					} else {
						pStmt.setTimestamp(9, step.getProcessedTS());
					}
								
					if (step.getCheckerId() == null) {
						pStmt.setNull(10, Types.INTEGER);
					} else {
						pStmt.setInt(10, step.getCheckerId().intValue());
					}

					if (step.getCheckedTS() == null) {
						pStmt.setNull(11, Types.TIMESTAMP);
					} else {
						pStmt.setTimestamp(11, step.getCheckedTS());
					}

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

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

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

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

    /**
     * Diese Methode liefert alle registrierten Kategorien 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 Kategorien
     */
    private static final HashMap retrieveRegisteredCategories(Connection conn,
                                                              String associationTable, 
                                                              String keyColumn, 
                                                              Integer keyId)
    throws DBException, SQLException {
        HashMap categories = new HashMap();
        PreparedStatement pStmt = null;
        PreparedStatement pStmt2 = null;
        ResultSet rs = null;

        try {
            String sqlStatement = "SELECT categoryId, inclSubDirs, subjectForNotification FROM " + associationTable + " WHERE " + keyColumn + " = ?";
            pStmt = conn.prepareStatement(sqlStatement);
            pStmt.setInt(1, keyId.intValue());
            rs = pStmt.executeQuery();

            while (rs.next()) {
                RegisteredCategory category = new RegisteredCategory();
                category.setCategoryId(new Integer(rs.getInt("categoryId")));
                category.setIncludeSubDirs(new Boolean(rs.getInt("inclSubDirs") != 0));
                category.setSubjectForNotification(new String(rs.getString("subjectForNotification")));
                categories.put(category.getCategoryId(), category);
            }

            rs.close();
            rs = null;
            DBHelper.close(pStmt);

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

            pStmt = conn.prepareStatement("SELECT parentCategoryId, name, noteId FROM categories WHERE categoryId = ?");
            pStmt2 = conn.prepareStatement("SELECT categoryId, name FROM categories WHERE parentCategoryId = ?");

            while (it.hasNext()) {
                Integer catId = (Integer) it.next();
                RegisteredCategory category = (RegisteredCategory) categories.get(catId);

                pStmt.setInt(1, catId.intValue());
                rs = pStmt.executeQuery();

                rs.next();

                category.setParentCategoryId(new Integer(rs.getInt("parentCategoryId")));
                category.setName(rs.getString("name"));
                category.setDescription(VKCNoteSerializer.retrieveNote(new Integer(rs.getInt("noteId")), conn));
                
                if (rs.wasNull() || category.getDescription() == null) {
                    category.setDescription("");
                }

                DBHelper.close(rs);

                // Unterkategorien
                pStmt2.setInt( 1, catId.intValue() );
                rs = pStmt2.executeQuery();

                Vector subCats = new Vector();

                while (rs.next()) {
                    VKCSubCategory subCat = new VKCSubCategory();
                    subCat.setSubCategoryId(new Integer(rs.getInt("categoryId")));
                    subCat.setName(rs.getString("name"));
                    subCats.add(subCat);
                }

                // Unterkategorien sortieren
                SubCategorySortFactory.sort(subCats, SubCategorySortFactory.SORT_BY_NAME_UP);
                category.setSubCategories(subCats);

                // die pfadbeschreibung dieser categorie holen
                category.setAbsPathDescr(getCategoryAbsPath(catId, conn));

                categories.put(category.getCategoryId(), category);
            }

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

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

        try {
            conn = WebShare.database.getConnection();
            saveRegisteredCategories(conn, user.getRegisteredCategories(), "UserRegisteredCategories", "userId", user.getUserId());
            conn.commit();
        } catch (Exception e) {
            DBHelper.rollBack(conn);
            VKCLogManager.errorLog(VKCCategorySerializer.class, "1", "Error while saving userregisteredcategories: ", e);
            throw new DBException(e);
        } finally {
            DBHelper.close(conn);
        }
    }

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

		try {
			// Erstmal evtl. vorhandene Schlüsselbegriffe löschen
	        DBHelper.deleteEntry(conn, associationTable, keyColumn, keyId);

	        String statement = "INSERT INTO " + associationTable + " (" + keyColumn + ", categoryId, inclSubDirs, subjectForNotification) VALUES ( ?, ?, ?, ? )";
	        pStmt = conn.prepareStatement(statement);
	        
	        Iterator it = categories.values().iterator();

	        while (it.hasNext()) {
	            RegisteredCategory category = (RegisteredCategory) it.next();

	            // Assoziation hinzufügen in die Zwischentabelle
	            Integer subDirs = category.getIncludeSubDirs().booleanValue() ? new Integer(1) : new Integer(0);
	            
	            pStmt.setInt(1, keyId.intValue());
	            pStmt.setInt(2, category.getCategoryId().intValue());
	            pStmt.setInt(3, subDirs.intValue());
                pStmt.setString(4,category.getSubjectForNotification());
	            pStmt.execute();
	        }
		} catch (Exception e) {
			throw new DBException(e);
		} finally {
			DBHelper.close(pStmt);
		} 
    }

	/**
	 *  Holt den absoluten Pfad einer Categorie
	 *
	 *  @param targetCatId ID der zu erruierenden Kategorie
	 *  @return VKCCategoryAbsPath object
	 */
	public static final VKCCategoryAbsPath getCategoryAbsPath(Integer catId, Connection conn)	throws DBException {
		PreparedStatement pStmt = null;

		try {
			VKCCategoryAbsPath catAbsPath = new VKCCategoryAbsPath();
			int categoryId = (catId != null) ? catId.intValue() : 0;

			pStmt = conn.prepareStatement("SELECT Name, CategoryId, ParentCategoryId FROM Categories WHERE CategoryId = ?");

			// iterate bottom up to root
			int count = 0;

			while (categoryId > 0) {
				ResultSet rs = null;
				
				try {
					pStmt.setInt(1, categoryId);
				    rs = pStmt.executeQuery();
	
					if (rs.next()) {
						VKCCategoryPathNode pathNode = new VKCCategoryPathNode();
						pathNode.setLastNode(count == 0 ? true : false);
						pathNode.setPathName(rs.getString("Name"));
						pathNode.setPathId(new Integer(rs.getInt("CategoryId")));
						catAbsPath.addNode(pathNode);
						catAbsPath.setAbsolutePathString(pathNode.getPathName() + "\u200E/ \u200E" + catAbsPath.getAbsolutePathString());
	
						categoryId = rs.getInt("ParentCategoryId");
	
						if (categoryId == 0) {
							pathNode.setRootNode(true);
						}
	
						count++;
					} else {
						categoryId = 0;
					}
				} finally {
					DBHelper.close(rs);
				}
			}

			return count > 0 ? catAbsPath : null;
		} catch (Exception e) {
			VKCLogManager.errorLog(VKCCategorySerializer.class, "1", "Error while retrieving absoulte path of categoryId =" + catId, e);
			throw new DBException(e);
		} finally {
			DBHelper.close(pStmt);
		}
	}

	/**
	 *  Holt den absoluten Pfad einer Categorie
	 *
	 *  @param targetCatId ID der zu erruierenden Kategorie
	 *  @return VKCCategoryAbsPath object
	 */
	public static final VKCCategoryAbsPath getCategoryAbsPath(Integer catId) throws DBException {
		Connection conn = null;

		try {
			conn = WebShare.database.getConnection();
			
			return getCategoryAbsPath(catId, conn);
		} catch (Exception e) {
			VKCLogManager.errorLog(VKCCategorySerializer.class, "1", "Error while retrieving absoulte path of categoryId =" + catId, e);
			throw new DBException(e);
		} finally {
			DBHelper.close(conn);
		}
	}

	/**
	 * Lädt das Query-Objekt mit der übergebenen ID aus der Datenbank
	 *
	 * @param categoryId ID der Kategorie
	 */
	public final static VKCSearchQuery retrieveSearchQuery(Integer categoryId) throws DBException {
		if (categoryId == null) {
			return null;
		}

		Connection conn = null;

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

	/**
	 * Lädt das Query-Objekt mit der übergebenen ID aus der Datenbank
	 *
	 * @param categoryId ID der Kategorie
	 */
	public static VKCSearchQuery retrieveSearchQuery(Integer categoryId, Connection conn) throws SQLException {
		if (categoryId == null) {
			return null;
		}

		PreparedStatement pStmt = null;
		ResultSet rs = null;

		try {
			pStmt = conn.prepareStatement("SELECT * FROM SEARCHQUERIES WHERE CATEGORYID = ?");
			pStmt.setInt(1, categoryId.intValue());
			rs = pStmt.executeQuery();

			HashMap map = new HashMap();
			
			while (rs.next()) {
				String value = rs.getString("queryfield");
    		    int normalIndex = value.indexOf(":(");
    		    int rangeIndex = value.indexOf(":[");
    		    String key = "";
    		    
    		    if (normalIndex != -1 && rangeIndex != -1) {
    		    	if (normalIndex < rangeIndex) {
    	    			key = value.substring(0, value.indexOf("(") + 1);
    		    	} else {
    	    			key = value.substring(0, value.indexOf("[") + 1);
    		    	}
    		    } else if (normalIndex != -1 && rangeIndex == -1) {
	    			key = value.substring(0, value.indexOf("(") + 1);
    		    } else if (normalIndex == -1 && rangeIndex != -1) {
	    			key = value.substring(0, value.indexOf("[") + 1);
    		    }
    		    
    			map.put(key, value);
			}
			
			VKCSearchQuery query = new VKCSearchQuery();
			query.setQueryElements(map);

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

	/**
	 * Speichert das Query-Objekt für die angegebene Kategorie
	 *
	 * @param query das Query-Objekt der gespeichert werden soll 
	 * @param categoryId ID des Kategorieobjekts für welches das Query-Objekt gespeichert werden soll 
	 */
	public final static void saveSearchQuery(VKCSearchQuery query, Integer categoryId) throws DBException {
		Connection conn = null;

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

	/**
	 * Speichert das Query-Objekt für die angegebene Kategorie
	 *
	 * @param query das Query-Objekt der gespeichert werden soll 
	 * @param categoryId ID des Kategorieobjekts für welches das Query-Objekt gespeichert werden soll 
	 */
	public static void saveSearchQuery(VKCSearchQuery query, Integer categoryId, Connection conn) throws DBException {
		PreparedStatement pStmt = null;
	    
	    try {
			// Erstmal evtl. vorhandene Schlüsselbegriffe löschen
			DBHelper.deleteEntry(conn, "SEARCHQUERIES", "categoryId", categoryId);

			if (!query.isEmpty()) {
				Iterator it = query.getQueryElements().values().iterator();

		        pStmt = conn.prepareStatement("INSERT INTO SEARCHQUERIES ( queryId, categoryId, queryField ) VALUES ( ?, ?, ? )");

		        while (it.hasNext()) {
					String field = (String) it.next();
					Integer id = DBHelper.getNewId(conn, "SEARCHQUERIES", "QUERYID");
					
					pStmt.setInt(1, id.intValue());
					pStmt.setInt(2, categoryId.intValue());
					pStmt.setString(3, field);
					pStmt.execute();
				}
			}
		} catch (Exception e) {
			throw new DBException(e);
		} finally {
			DBHelper.close(pStmt);
		}
	}

	public final static boolean isEmpty(Integer categoryId) throws DBException {
		if (categoryId == null) {
			return true;
		}

		Connection conn = null;

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

	private static boolean isEmpty(Integer categoryId, Connection conn) throws SQLException {
		if (categoryId == null) {
			return true;
		}

		PreparedStatement pStmt = null;
		ResultSet rs = null;
		int countSubCats = 0;
		int countDocs = 0;

		try {
			// count all subcategories...
			pStmt = conn.prepareStatement("SELECT count(*) as count_subCats FROM categories WHERE parentCategoryId = ?");
			pStmt.setInt(1, categoryId.intValue());
			rs = pStmt.executeQuery();
            rs.next();
            countSubCats = rs.getInt("count_subCats");
            rs.close();
            DBHelper.close(pStmt);
            
            // count all documents...
			pStmt = conn.prepareStatement("SELECT count(*) as count_docs FROM categoryDocuments WHERE categoryId = ?");
			pStmt.setInt(1, categoryId.intValue());
			rs = pStmt.executeQuery();
			rs.next();			
			countDocs = rs.getInt("count_docs");
			
			if (countSubCats > 0 || countDocs > 0) {
				return false;
			}
			
			return true;
		} finally {
			DBHelper.close(rs);
			DBHelper.close(pStmt);
		}
	}
	
	/**
	 * changes the creatorId from old to new of a given category or all of a given creator  
	 * 
	 * @param categoryId = id of category or NULL (for all cats of the given oldCreatorId)
	 * @param oldCreatorId = old owner of the categories
	 * @param newCreatorId = new owner of the categories
     * @param changerId 
	 * @return
	 * @throws DBException
	 */	
	public static final void changeCreatorId(Integer categoryId, Integer oldCreatorId, Integer newCreatorId, Integer changerId) throws DBException {
		Connection conn = null;
		PreparedStatement pStmt = null;
		
		try {
		    conn = WebShare.database.getConnection();

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

    /**
     * Lädt die Kategorien ohne bzw mit einem leeren ACL-Eintrag aus der
     * Datenbank
     * 
     * @return Liste mit VKCCategories
     */
    public static final List retrieveLostFolders() throws DBException {
        Connection conn = null;

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

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

    /**
     * Lädt die Kategorien ohne bzw mit einem leeren ACL-Eintrag aus der
     * Datenbank
     * 
     * @return Liste mit VKCCategories
     */
    public static final List retrieveLostFolders(Connection conn) throws DBException {
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        HashSet ids = new HashSet();

        try {
            // alle Kategorien die keinen ACL haben
            pStmt = conn.prepareStatement("SELECT * FROM CATEGORIES WHERE ACLID = ? AND FLAG <> ?");
            pStmt.setNull(1, Types.INTEGER);
            pStmt.setInt(2, VKCCategory.FLAG_DELETED.intValue());
            rs = pStmt.executeQuery();

            while (rs.next()) {
                ids.add(new Integer(rs.getInt("CATEGORYID")));
            }
            
            DBHelper.close(rs);
            DBHelper.close(pStmt);
            
             // alle Kategorien die leeren Acl haben
            pStmt = conn.prepareStatement("SELECT CATEGORYID FROM CATEGORIES WHERE CATEGORIES.FLAG <> ? AND CATEGORIES.ACLID NOT IN" + 
                                           " (SELECT CATEGORIES.ACLID FROM CATEGORIES, CATEGORYACLS WHERE CATEGORIES.FLAG <> ? AND CATEGORIES.ACLID = CATEGORYACLS.ACLID)");
            pStmt.setInt(1, VKCCategory.FLAG_DELETED.intValue());
            pStmt.setInt(2, VKCCategory.FLAG_DELETED.intValue());
            rs = pStmt.executeQuery();
            
            while (rs.next()) {
                Integer id = new Integer(rs.getInt("CATEGORYID"));
                ids.add(id);
            }
            
            Map permissions = VKCPermissionSerializer.getPermissions(VKCPermission.CONTEXT_CATEGORY);
            
            // alle Kategorien die kein Benutzer lesen darf
            if (permissions != null) {
                VKCPermission read = (VKCPermission)permissions.get(VKCPermission.r);

                if (read != null) {
                    DBHelper.close(rs);
                    DBHelper.close(pStmt);
                    
                    pStmt = conn.prepareStatement("SELECT CATEGORYID FROM CATEGORIES WHERE CATEGORIES.FLAG <> ? AND CATEGORIES.ACLID NOT IN" + 
                                                   " (SELECT CATEGORIES.ACLID FROM CATEGORIES, CATEGORYACLS WHERE CATEGORIES.FLAG <> ? AND CATEGORIES.ACLID = CATEGORYACLS.ACLID AND CATEGORYACLS.PERMISSIONID = ?)");
                    pStmt.setInt(1, VKCCategory.FLAG_DELETED.intValue());
                    pStmt.setInt(2, VKCCategory.FLAG_DELETED.intValue());
                    pStmt.setInt(3, read.getPermissionId().intValue());
                    rs = pStmt.executeQuery();

                    while (rs.next()) {
                        Integer id = new Integer(rs.getInt("CATEGORYID"));
                        ids.add(id);
                    }
                }
            }

            
            List cats = new ArrayList();

            for (Iterator iter = ids.iterator(); iter.hasNext();) {
                Integer id = (Integer) iter.next();
                cats.add(retrieveCategory(id, conn));
            }
            
            return cats;
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(rs);
            DBHelper.close(pStmt);
        }
    }
    
    public static final List retrieveCategoriesForUserByQueries(Integer fromCategoryId, VKCUser user, String[] queries) throws DBException {
        Connection conn = null;

        try {
            conn = WebShare.database.getConnection();
            
            return retrieveCategoriesForUserByQueries(fromCategoryId, user, queries, conn);
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(conn);
        }
    }
    
    public static final List retrieveCategoriesForUserByQueries(Integer fromCategoryId, VKCUser user, String[] queries, Connection conn) throws DBException {
        if (user == null || queries == null || queries.length == 0) {
            return new ArrayList();
        }
        
        PreparedStatement pStmt1 = null;
        ResultSet rs = null;
        List categories = new ArrayList();
        Set ids = null; 
        
        try {
            String condition = "( ";
            Object[] params = new Object[2 * queries.length];
            
            for (int i = 0; i < queries.length; i++) {
                condition += "( UPPER(name) LIKE UPPER(?) ESCAPE '\\' )";
                condition += " OR ";
                condition += "( UPPER(name) LIKE UPPER(?) ESCAPE '\\' )";

                if (i < queries.length - 1) {
                    condition += " OR ";
                }
                
                String upper = queries[i].toUpperCase();
                String lower = queries[i].toLowerCase();
                int offset = 2 * i;

                params[offset] = upper;
                params[offset + 1] = lower;
            }
            
            condition += " )";

            pStmt1 = conn.prepareStatement("SELECT categoryId FROM CATEGORIES WHERE " + condition);
            DBHelper.setElements(pStmt1, params);
            rs = pStmt1.executeQuery();
            
            while (rs.next()) {
                if (ids == null) {
                    ids = CategoryUtils.getReadableCategoryIds(user);
                    
                    if (fromCategoryId != null) {
                        Set catIds = CategoryUtils.getAllSubCategoryIdsRecursiv(fromCategoryId, conn);
                        catIds.add(fromCategoryId);
                        ids.retainAll(catIds);
                    }
                }
                
                Integer categoryId = new Integer(rs.getInt("categoryId"));
                
                if (ids.contains(categoryId)) {
                    categories.add(retrieveCategory(categoryId, conn));
                }
            }

            return categories;
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(rs);
            DBHelper.close(pStmt1);
        }
    }
    
    /**
     * Speichert eine Kategorie in der Datenbank ab Falls die Kategorie bereits
     * existiert, werden die Eigenschaften dieser Kategorie aktualisiert.
     *
     * @param categories das zu speichernde Kategorieobjekt
     */
    public final static void importCategory(ArrayList categories) throws DBException {
        Connection conn = null;

        try {
            conn = WebShare.database.getConnection();
            importCategory(categories, conn);
            conn.commit();
        } catch (Exception e) {
            VKCLogManager.errorLog(VKCCategorySerializer.class, "", e.getMessage(), e);
            DBHelper.rollBack(conn);

            // Mögliche Änderungen am Treeview zurücksetzen
            TreeViewManager.getInstance().requestUpdate();

            throw new DBException(e);
        } finally {
            DBHelper.close(conn);
        }
    }
    public final static void importCategory(ArrayList categories, Connection conn) throws DBException {
       
    	  String stmt = "INSERT INTO CATEGORIES ( ";
          stmt += "name, ";
          stmt += "lastAccessorId, ";
          stmt += "parentCategoryId, ";
          stmt += "aclId, ";
          stmt += "dynAttrsId, ";
          stmt += "showDescription, ";
          stmt += "lastChangerId, ";
          stmt += "lastChanged, ";
          stmt += "initialCategory, ";
          stmt += "lastAccessed, ";
          stmt += "noteId, ";
          stmt += "creatorId, ";
          stmt += "created, ";
          stmt += "accessCounter, ";
          stmt += "categoryType, ";				
          stmt += "status, ";				
          stmt += "flag, ";				
          stmt += "CATEGORYID ) ";                
          stmt += "values ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) ";
          // es ist wichtig das Wurzelknoten vor den Bläter eingetragen wird
          
			
        	Iterator iterCategories = categories.iterator();
        	while(iterCategories.hasNext()){
        		VKCCategory category = (VKCCategory) iterCategories.next();
        	try {
        	VKCAclSerializer.saveAcl(category.getAcl(), conn);            
            if (category.getCategoryId() == null && category.getCategoryId().intValue() < 0) {
            	category.setCategoryId(DBHelper.getNewId(conn, "Categories", "categoryId"));
            }                 
                // Beschreibung speichern/updaten
                if(category.getDescription()!=null && !category.getDescription().equals("")){
                	category.setNoteId(VKCNoteSerializer.saveNote(category.getCategoryId(),"CATEGORIES",category.getDescription()));
                }
                
              

                executeCategoryStatement(stmt, category, conn);

				if (category.isProjectCategory()) {
					String projectStmt = "INSERT INTO PROJECTS ( ";
					projectStmt += "EXTERNALCONTACTS, ";
					projectStmt += "CATEGORYID ) ";
					projectStmt += "values ( ?, ? ) ";
					
					executeProjectStatement(projectStmt, (VKCProject)category, conn);
				} else if (category.isProjectSubfolder()) {
					String folderStmt = "INSERT INTO PROJECTSUBFOLDERS ( ";
					folderStmt += "PROJECTID, ";
					folderStmt += "CATEGORYID ) ";
					folderStmt += "values ( ?, ? ) ";

					executeProjectSubfolderStatement(folderStmt, (VKCProjectSubfolder)category, conn);
				}

                //Kategorie dem Suchindex zufügen
				WebShare.classifier.addCategory(category);

                // Update the tree view
                TreeViewManager.getInstance().add(category);
            

            // Assozierte Schlüsselbegriffe speichern
            VKCKeywordSerializer.saveKeywords(conn, category.getKeywords(), "CategoryKeywords", "categoryId", category.getCategoryId());

            // additional category attributes speichern
            VKCAddAttributeSerializer.saveAddCatAttributes(conn, category);

            // Dynamische Attribute speichern/updaten
            VKCDynaAttributeSerializer.saveDynaAttributes(conn, category);
            
            if (category instanceof VKCProject) {
				VKCProject project = (VKCProject)category;
				saveProjectSteps(conn, project);
			}
        	
            WebShare.database.setStructureChangedAt(System.currentTimeMillis());
        } catch (Exception ex) {
            if (category.getCategoryId() != null) {
                try { // Kategorie aus dem Suchindex löschen
					WebShare.classifier.removeCategory(category.getCategoryId());
                } catch (IOException ioe) { 
                    VKCLogManager.errorLog(VKCCategorySerializer.class, "", ioe.getMessage(), ioe);
                }
            }

            WebShare.database.setStructureChangedAt(System.currentTimeMillis());

            throw new DBException(ex);
        }
        	}
    }   

}