/*
 * $Id: CategoryUtils.java,v 1.59 2007/08/14 14:50:06 marbobo Exp $
 *
 * Copyright (c) Siemens AG 2010 
 *           (c) Universität Paderborn 2010 
 * Licensed under EUPL 1.1. 
 *
 * $Log: CategoryUtils.java,v $
 * Revision 1.59  2007/08/14 14:50:06  marbobo
 * + Licenseverwaltung komplett umgeschrieben
 * + Kategoriesuche und verlorene Kategorien anzeigen komplett umgeschrieben
 * + Fehler beim Speichern von Beschreibenungsfeldern für Entitäten beseitigt.
 *
 * Revision 1.58  2006/10/27 08:50:51  lawern
 * WRITABLE-Views eingefügt
 *
 * Revision 1.57  2006/07/28 12:24:51  lawern
 * WebDAV überarbeitet.
 *
 * Revision 1.56  2006/07/27 08:11:03  lawern
 * Fehlerhafte Änderung von Revision 1.55 zurückgenommen.
 *
 * Revision 1.55  2006/07/26 19:45:07  marbobo
 * + Bugfix (Verzeichnisse mit Umlauten werden wieder korrekt erkannt)
 *
 * Revision 1.54  2006/07/26 11:37:02  marbobo
 * + Bugfix
 *
 * Revision 1.53  2006/07/21 10:31:32  lawern
 * Korrektur von WebDav-Ferhler Nr. 060623/01
 *
 * Revision 1.52  2006/07/20 21:54:49  marbobo
 * + existCategory erweitert
 *
 * Revision 1.51  2006/07/17 13:04:00  lawern
 * Neuer typografiegewichteter Index und k-NN-Klassifikation aus meiner Diss.
 *
 * Revision 1.50  2006/07/11 23:14:02  marbobo
 * + Dateien im Papierkorb werden nicht mehr im Quotaberechnung berücksichtigt
 * + Dokumentversionen werden nun beim aktivierten Papierkorb nicht mehr endgültig gelöscht
 *
 * Revision 1.49  2006/03/23 11:44:31  marbobo
 * + Mögliche NPE abgefangen
 *
 * Revision 1.48  2005/09/13 09:51:45  jonyp
 * cleaning the code
 *
 * Revision 1.47  2005/09/13 00:22:10  marbobo
 * + isInTrash() berücksichtigt nun ob Papierkorb aktiviert ist oder nicht
 *
 * Revision 1.46  2005/09/05 22:48:24  marbobo
 * + Suche nach Metadaten-Attribute (Erstellungsdatum) für Dokumente implementiert
 *
 * Revision 1.45  2005/09/04 18:03:08  marbobo
 * + Bugfix (Commit-Statement ergänzt)
 *
 * Revision 1.44  2005/09/01 18:37:14  marbobo
 * + Bugfix (rollback-reihenfolge)
 *
 * Revision 1.43  2005/09/01 16:30:38  jonyp
 * + createUserTrashcategory
 *
 * Revision 1.42  2005/08/26 14:55:00  jonyp
 * added isInTrash function
 *
 * Revision 1.41  2005/02/09 10:25:37  lawern
 * Anpassung des WebDavServlet wegen Entity-Kodierung in der DB.
 *
 * Revision 1.40  2004/12/21 10:24:20  koppy
 * getParentCategoryId(..) nach CategoryUtils verschoben
 *
 * Revision 1.39  2004/12/17 01:10:48  marbobo
 * + Bugfix
 *
 * Revision 1.38  2004/12/09 21:07:40  marbobo
 * + kleine Optimierungen und Bugfixes
 * +an einigen Stellen Rreihenfolge der close-Anweisungen vereinheitlicht
 *
 * Revision 1.37  2004/12/09 11:29:07  jonyp
 * added TODO in getReadableCategories
 *
 * Revision 1.36  2004/12/09 11:07:09  jonyp
 * + check for readable categories of ldap-groups, clean preparedStatement leakage
 *
 * Revision 1.35  2004/12/07 15:15:19  jonyp
 * added support for ldap-groups in method "getReadableCategoryIds"
 *
 * Revision 1.34  2004/12/06 14:08:00  marbobo
 * + Statement auf PreparedStatement umgestellt
 *
 * Revision 1.33  2004/11/24 09:25:57  lawern
 * Hibernate ist VKC-Geschichte!
 *
 * Revision 1.32  2004/11/23 15:19:14  jonyp
 * delete obsolete rs.close in "getCategoryIds"-method
 *
 * Revision 1.31  2004/11/18 19:38:24  marbobo
 * + changed order of close-stmt to resultset/connect/session  in finally-blocks
 *
 * Revision 1.30  2004/10/31 03:00:38  marbobo
 * + getCategoryName()
 *
 * Revision 1.29  2004/10/26 22:27:32  marbobo
 * + getAccessibleCountCatDocs()
 * + Bei einigen Methoden Gültigkeit von Gruppen berücksichtigt
 *
 * Revision 1.28  2004/09/09 08:15:26  jonyp
 * changed method name "isInTreeOf" to "isInTree"
 *
 * Revision 1.27  2004/09/08 19:44:59  marbobo
 * + isInTreeOf() - Zur Bestimmung ob die angegebene Kategorie im Baumstruktur
 * der aktuellen Kategorie sich befindet
 *
 * Revision 1.26  2004/09/07 13:25:10  marbobo
 * - Überflüssige Methoden entfernt
 *
 * Revision 1.25  2004/08/23 14:46:11  marbobo
 * + getAllSubCategoryIdsRecursiv()
 *
 * Revision 1.24  2004/07/20 15:27:52  jonyp
 * + added cvs info
 * + changes due to quoatinfo computation with long values
 * + bug fix: added adddocformats size correctly
 *
 */
package vkc.util;

import vkc.db.entities.VKCAclEntry;
import vkc.db.entities.VKCCategory;
import vkc.db.entities.VKCDocument;
import vkc.db.entities.VKCGroup;
import vkc.db.entities.VKCSubCategory;
import vkc.db.entities.VKCUser;

import vkc.db.helper.DBHelper;

import vkc.db.serializer.VKCAclSerializer;
import vkc.db.serializer.VKCCategorySerializer;
import vkc.db.serializer.VKCGroupSerializer;

import vkc.exception.DBException;

import vkc.ldap.VKCLdapUser;

import vkc.misc.VKCLogManager;

import vkc.security.PermissionManager;
import vkc.security.VKCAcl;

import vkc.view.WebShare;

import vkc.view.bean.VKCCountCatDocs;
import vkc.webdav.URLUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;
import java.util.StringTokenizer;


public class CategoryUtils {
    private CategoryUtils() {
    }

    /**
     * Überprüft, ob der Name einer Kategorie bereits unter den Subkategorien
     * der Elternkategorie vorhanden ist
     *
     * @param catName der zu überprüfende Name
     * @param subCategories Collection von VKCSubCategory-Objekten
     *
     * @return true, falls der Name dieser Kategorie bereits vorhanden ist
     */
    public static final boolean isCategoryNameUnique(VKCCategory category, Collection subCategories) {
        VKCSubCategory subCat = null;
        String name = category.getName().toLowerCase();

        for (Iterator it = subCategories.iterator(); it.hasNext();) {
            subCat = (VKCSubCategory) it.next();

            if (!subCat.getSubCategoryId().equals(category.getCategoryId())) {
                if (subCat.getName().toLowerCase().equals(name)) {
                    return false;
                }
            }
        }

        return true;
    }

    /**
     * Überprüft, ob der Name einer Kategorie bereits unter den Subkategorien
     * der Elternkategorie vorhanden ist
     *
     * @param catName der zu überprüfende Name
     * @param subCategories Collection von VKCSubCategory-Objekten
     *
     * @return true, falls der Name dieser Kategorie bereits vorhanden ist
     */
    public static final boolean isContained(String catName, Collection subCategories) {
        if (catName == null) {
            return false;
        }

        catName = catName.trim().toLowerCase();

        VKCSubCategory subCat = null;

        for (Iterator it = subCategories.iterator(); it.hasNext();) {
            subCat = (VKCSubCategory) it.next();

            if (subCat.getName().toLowerCase().equals(catName)) {
                return true;
            }
        }

        return false;
    }

	/**
	 *  Prüft, ob eine Kategorie mit der übergebenen ID existiert
	 *
	 *  @param categoryId
	 *  @return boolean
	 */
	public static final boolean existsCategory(Integer categoryId, Integer flag, boolean flagPositivLogic) throws DBException {
		Connection conn = null;

		try {
			conn = WebShare.database.getConnection();
			
			return existsCategory(categoryId, flag, flagPositivLogic, conn);
		} catch (Exception e) {
			VKCLogManager.errorLog(VKCCategorySerializer.class, "1", "Error while resolving existence of categoryId =" + categoryId, e);
			throw new DBException(e);
		} finally {
			DBHelper.close(conn);
		}
	}

    public final static VKCCategory createUserTrashCategory(VKCUser user) throws DBException {
        Connection conn = null;

        try {
            conn = WebShare.database.getConnection();
            VKCCategory userCategory = createUserTrashCategory(user, conn);
            conn.commit();
            
            return userCategory;
        } catch (Exception e) {
            VKCLogManager.errorLog(VKCCategorySerializer.class, "", e.getMessage(), e);
            DBHelper.rollBack(conn);
            throw new DBException(e);
        } finally {
            DBHelper.close(conn);
        }
    }
	
	public static final VKCCategory createUserTrashCategory(VKCUser user, Connection conn) throws DBException {
        Integer trashCategoryId = new Integer(WebShare.getSysParameterValue("TRASH", "TrashCategoryId", "0"));
        
        // prüfen, ob schon eine datumskategorie für diesen tag existiert
        String dateCatName = DateUtils.getYYYY_MM_DD(new Date(System.currentTimeMillis()));
        VKCCategory dateCat = CategoryUtils.existsSubCategory(trashCategoryId, dateCatName);
           
        if (dateCat == null) {
            dateCat = new VKCCategory();
            dateCat.setName(dateCatName);
            dateCat.setParentCategoryId(trashCategoryId);
            dateCat.setCreatorId(user.getUserId());
            dateCat.setFlag(VKCCategory.FLAG_DELETED);
            VKCCategorySerializer.saveCategory(dateCat, conn);
        }                   

        // löschuser-kategorie mit zeitstempel anlegen
        VKCCategory userCat = new VKCCategory();
        userCat.setName(DateUtils.getYYYY_MM_DD_HH_MM_SS(new Date(System.currentTimeMillis())) + "_" + user.getUser());
        userCat.setParentCategoryId(dateCat.getCategoryId());
        userCat.setCreatorId(user.getUserId());
        userCat.setFlag(VKCCategory.FLAG_DELETED);
        VKCCategorySerializer.saveCategory(userCat, conn);
	    
        return userCat;
	}
	
	
	public static boolean existsCategory(Integer categoryId, Integer flag, boolean flagPositivLogic, Connection conn) throws SQLException {
		if (categoryId == null) {
			return false;
		}

		PreparedStatement pStmt = null;
		ResultSet rs = null;

		try {
			String statement = "SELECT COUNT(*) AS anzahl FROM categories WHERE categoryid = ?";
			
			if (flag != null) {
				if (flagPositivLogic) {
					statement += " AND (flag IS NOT NULL AND flag = ?)";
				} else {
					statement += " AND (flag IS NULL OR flag <> ?)";
				}
			}
			
			pStmt = conn.prepareStatement(statement);
			pStmt.setInt(1, categoryId.intValue());
			
			if (flag != null) {
				pStmt.setInt(2, flag.intValue());
			}
			
			rs = pStmt.executeQuery();
			
			rs.next();
			int value = rs.getInt("anzahl");
			
			if (value > 0) {
			    return true; 
			}

			return false;
		} finally {
			DBHelper.close(rs);
			DBHelper.close(pStmt);
		}
	}
    
    public static final VKCCategory existsSubCategory(Integer parentCatId, String subCatName) throws DBException {
        Connection conn = null;
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        Integer categoryId = null;
        VKCCategory foundCat = null;

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

            pStmt = conn.prepareStatement(
                    "SELECT categoryId" +
                    "  FROM categories" +
                    " WHERE parentCategoryId =  ? " +
                    "   AND name = ?");
            pStmt.setInt(1, parentCatId.intValue());
            pStmt.setString(2, subCatName);
            rs = pStmt.executeQuery();

            if (rs.next()) {
                categoryId = new Integer(rs.getInt("categoryId"));
                foundCat = VKCCategorySerializer.retrieveCategory(categoryId);
            }    
    
            return foundCat;
            
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(rs);
            DBHelper.close(pStmt);
            DBHelper.close(conn);
        }
    }
    
    
    /**
     * Gibt eine Menge(Set) aller IDs von Kategorien unterhalb einer
     * vorgegebenen Kategorie, in denen der VKCUser Schreibrechte hat,
     * (Verwendung bei der Suchmaschine)
     *
     * @param targetCatId unterhalb dieser Kategorie-ID
     * @param user Referenz auf ein VKCUser-Objekt
     *
     * @return Menge von Kategorie-Ids (Typ: String)
     *
     * @throws DBException
     */
    public static final Set getWritableCategoryIds(VKCUser user) throws DBException {
        Connection conn = null;
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        Set catIds = new HashSet();
        Set validGroupIds = null;

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

            if (!user.isUserValid()) {
                return catIds;
            }

            if (user.hasAdminPrivileges()) {
                catIds.addAll(VKCCategorySerializer.getAllCategoryIds());

                return catIds;
            }

            // Kategorien für die der User direkt oder per ANYOTHER ( userid==0 )
            // mit Schreibrecht eingetragen ist bestimmen
            pStmt = conn.prepareStatement("SELECT CATEGORYID from CATEGORIES_WRITABLE_BY_USER WHERE USERID = ? OR USERID = 0");
            pStmt.setInt(1, user.getUserId().intValue());
            rs = pStmt.executeQuery();

            while (rs.next()) {
                catIds.add(new Integer(rs.getInt("CATEGORYID")));
            }

            DBHelper.close(rs);

            if (!user.getGroupIds().isEmpty()) {
                validGroupIds = VKCGroupSerializer.getAllValidGroupIds(conn);
            } else {
                validGroupIds = new HashSet();
            }

            // Kategorien für die der User indirekt über seine Gruppen Schreibrechte besitzt
            DBHelper.close(pStmt);
            pStmt = conn.prepareStatement("SELECT CATEGORYID from CATEGORIES_WRITABLE_BY_GROUP WHERE GROUPID = ?");

            for (Iterator iter = user.getGroupIds().iterator(); iter.hasNext();) {
                Integer groupId = (Integer) iter.next();
                pStmt.setInt(1, groupId.intValue());
                rs = pStmt.executeQuery();

                while (rs.next()) {
                    if (validGroupIds.contains(groupId)) {
                        catIds.add(new Integer(rs.getInt("CATEGORYID")));
                    }
                }

                DBHelper.close(rs);
            }

            // kategorien, für die der user über ldap-gruppen zugriffrechte besitzt
            // sofern er ldap-user ist
            if (user instanceof VKCLdapUser) {
                VKCLdapUser ldapUser = (VKCLdapUser) user;

                // for future use:
                // ask here for writable categories for an LDAP-USER
                /*
                DBHelper.close( pStmt );
                pStmt = conn.prepareStatement( "SELECT categoryId, ldapGroupId FROM CATEGORIES_WRITABLE_BY_LUSER WHERE userId = ?" );
                    pStmt.setInt( 1, user.getUserId().intValue() );
                    rs = pStmt.executeQuery();

                    while ( rs.next() ) {

                        // check if user is member of given ldap-group
                        if ( ldapUser.isMemberOfLdapGroup( new Integer(rs.getInt( "ldapGroupId" ) ) ) ) {
                                catIds.add( new Integer( rs.getInt( "categoryId" ) ) );
                            }
                    }

                rs.close();
                */
                // get all known LDAP-GROUPS which have permission "w" for categories 
                // TODO mmmh, that perhaps not very performant
                // better to create an ldap-statement to get all valid ldap-groups where user is member
                DBHelper.close(pStmt);
                pStmt = conn.prepareStatement("SELECT categoryId, ldapGroupId FROM CATEGORIES_WRITABLE_BY_LGROUP");
                rs = pStmt.executeQuery();

                while (rs.next()) {
                    // check if user is member of given ldap-group
                    if (ldapUser.isMemberOfLdapGroup(new Integer(rs.getInt("ldapGroupId")))) {
                        catIds.add(new Integer(rs.getInt("categoryId")));
                    }
                }
            }

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

    /**
     * Gibt eine Menge(Set) aller vorhandenen IDs von Kategorien, in denen der
     * VKCUser Leserechte hat, (Verwendung bei der Suchmaschine)
     *
     * @param user Referenz auf ein VKCUser-Objekt
     *
     * @return Menge von Kategorie-Ids (Typ: Integer)
     *
     * @throws DBException
     */
    public static final Set getReadableCategoryIds(VKCUser user) throws DBException {
        Connection conn = null;
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        Set catIds = new HashSet();
        Set validGroupIds = null;

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

            if (!user.isUserValid()) {
                return catIds;
            }

            if (user.hasAdminPrivileges()) {
                catIds.addAll(VKCCategorySerializer.getAllCategoryIds());

                return catIds;
            }

            // Kategorien für die der User direkt oder per ANYOTHER ( userid==0 )
            // mit Leserecht eingetragen ist bestimmen
            pStmt = conn.prepareStatement("SELECT CATEGORYID from CATEGORIES_READABLE_BY_USER WHERE USERID = ? OR USERID = 0");
            pStmt.setInt(1, user.getUserId().intValue());
            rs = pStmt.executeQuery();

            while (rs.next()) {
                catIds.add(new Integer(rs.getInt("CATEGORYID")));
            }

            if (!user.getGroupIds().isEmpty()) {
                validGroupIds = VKCGroupSerializer.getAllValidGroupIds(conn);
            } else {
            	validGroupIds = new HashSet();
            }

            // Kategorien für die der User indirekt über seine Gruppen Leserechte besitzt
            DBHelper.close(rs);
            DBHelper.close(pStmt);
            pStmt = conn.prepareStatement("SELECT CATEGORYID from CATEGORIES_READABLE_BY_GROUP WHERE GROUPID = ?");

            for (Iterator iter = user.getGroupIds().iterator(); iter.hasNext();) {
                Integer groupId = (Integer) iter.next();
                pStmt.setInt(1, groupId.intValue());
                rs = pStmt.executeQuery();

                while (rs.next()) {
                    if (validGroupIds.contains(groupId)) {
                        catIds.add(new Integer(rs.getInt("CATEGORYID")));
                    }
                }

                DBHelper.close(rs);
            }

            // kategorien, für die der user über ldap-gruppen zugriffrechte besitzt
            // sofern er ldap-user ist
            if (user instanceof VKCLdapUser) {
                VKCLdapUser ldapUser = (VKCLdapUser) user;

                // for future use:
                // ask here for readable categories for an LDAP-USER
                /*
                DBHelper.close( pStmt );
                pStmt = conn.prepareStatement( "SELECT categoryId, ldapGroupId FROM CATEGORIES_READABLE_BY_LUSER WHERE userId = ?" );
                    pStmt.setInt( 1, user.getUserId().intValue() );
                    rs = pStmt.executeQuery();

                    while ( rs.next() ) {

                        // check if user is member of given ldap-group
                        if ( ldapUser.isMemberOfLdapGroup( new Integer(rs.getInt( "ldapGroupId" ) ) ) ) {
                                catIds.add( new Integer( rs.getInt( "categoryId" ) ) );
                            }
                    }

                rs.close();
                */
                // get all known LDAP-GROUPS which have permission "r" for categories 
                // TODO mmmh, that perhaps not very performant
                // better to create an ldap-statement to get all valid ldap-groups where user is member
                DBHelper.close(pStmt);
                pStmt = conn.prepareStatement("SELECT categoryId, ldapGroupId FROM CATEGORIES_READABLE_BY_LGROUP");
                rs = pStmt.executeQuery();

                while (rs.next()) {
                    // check if user is member of given ldap-group
                    if (ldapUser.isMemberOfLdapGroup(new Integer(rs.getInt("ldapGroupId")))) {
                        catIds.add(new Integer(rs.getInt("categoryId")));
                    }
                }
            }

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

    /**
     * Gibt eine Menge(Set) aller vorhandenen IDs von Kategorien, in denen der
     * VKCUser gegebene Rechte hat, (Verwendung bei der Suchmaschine)
     *
     * @param user Referenz auf ein VKCUser-Objekt
     * @param permToken one of 'r', 'w', etc...
     *
     * @return Menge von Kategorie-Ids (Typ: String)
     *
     * @throws DBException
     */
    public static final Set getCategoryIds(VKCUser user, char permToken) throws DBException {
        Connection conn = null;
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        Set catIds = new HashSet();

        try {
            conn = WebShare.database.getConnection();
            pStmt = conn.prepareStatement("SELECT categoryId, aclId FROM Categories");
            rs = pStmt.executeQuery();

            // Kategorie-IDs mit aclIds holen
            while (rs.next()) {
                VKCAcl catAcl = VKCAclSerializer.retrieveAcl(new Integer(rs.getInt("aclId")), conn);

                if (!PermissionManager.hasPermission(catAcl, user, permToken)) {
                    continue;
                }

                catIds.add(new Integer(rs.getInt("categoryId")));
            }

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

    /**
     * Holt zu einem durch "/" getrennten Kategorie-Pfad die ID der letzten
     * Kategorie.
     * (Verwendung bei WebDAV-Servlet)
     *
     * @param Pfad der gesuchten Kategorie
     * @return ID der letzten Kategorie
     *
     * @throws DBException
     */
    public static final Integer retrieveCategoryIdByPath(String path) throws DBException {
        //Unicode-Zeichen im Pfad in XML-Entities kodieren
        String filteredPath = URLUtil.URLDecode(path);

        //Pfad bereinigen und evtl. letzten '/' abschneiden
        int lastChar = filteredPath.length() - 1;
        
        if (filteredPath.charAt(lastChar) == '/') {
            filteredPath = filteredPath.substring(0, lastChar);
        }

        int currCatId = VKCCategory.ROOT_CATEGORY_ID;
        String currCatName;
        StringTokenizer tokenizer = new StringTokenizer(filteredPath, "/");
        String query = "SELECT categoryId FROM categories WHERE name = ? AND parentcategoryId = ?";

        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pStmt2 = null;

        try {
            conn = WebShare.database.getConnection();
            pStmt2 = conn.prepareStatement(query);

            while ((currCatId > -1) && tokenizer.hasMoreTokens()) {
                currCatName = tokenizer.nextToken();

                pStmt2.setString(1, currCatName);
                pStmt2.setInt(2, currCatId);

                rs = pStmt2.executeQuery();

                if (rs.next()) {
                    currCatId = rs.getInt("categoryId");
                } else if ((currCatName.indexOf("(CID ") == 0) && tokenizer.hasMoreTokens()) {
                    int idEnd = currCatName.indexOf(')');
                    String id = currCatName.substring(5, idEnd);
                    currCatId = Integer.parseInt(id);
                } else {
                    currCatId = -1;
                }

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

        if (currCatId < 0) {
            return null;
        }

        return new Integer(currCatId);
    }

    /**
     * Gibt eine Menge(Set) aller IDs von Kategorien unterhalb einer
     * vorgegebenen Kategorie, in denen der VKCUser Schreibrechte hat,
     * (Verwendung bei der Suchmaschine)
     *
     * @param categoryId unterhalb dieser Kategorie-ID
     * @param user Referenz auf ein VKCUser-Objekt
     *
     * @return Menge von Kategorie-Ids (Typ: String)
     *
     * @throws DBException
     */
    public static final Set getWriteableCategoryIds(Integer categoryId, VKCUser user) throws DBException {
        return getCategoryIds(categoryId, user, 'w');
    }

    /**
     * Gibt eine Menge(Set) aller IDs von Kategorien unterhalb einer
     * vorgegebenen Kategorie, in denen der VKCUser Leserechte hat,
     * (Verwendung bei der Suchmaschine)
     *
     * @param categoryId unterhalb dieser Kategorie-ID
     * @param user Referenz auf ein VKCUser-Objekt
     *
     * @return Menge von Kategorie-Ids (Typ: String)
     *
     * @throws DBException
     */
    public static final Set getReadableCategoryIds(Integer categoryId, VKCUser user) throws DBException {
        return getCategoryIds(categoryId, user, 'r');
    }

    /**
     * Gibt eine Menge(Set) aller IDs von Kategorien unterhalb einer
     * vorgegebenen Kategorie, in denen der VKCUser gegebene Rechte hat,
     * (Verwendung bei der Suchmaschine)
     *
     * @param categoryId unterhalb dieser Kategorie-ID
     * @param user Referenz auf ein VKCUser-Objekt
     * @param permToken one of 'r', 'w', etc...
     *
     * @return Menge von Kategorie-Ids (Typ: String)
     *
     * @throws DBException
     */
    public static final Set getCategoryIds(Integer categoryId, VKCUser user, char permToken) throws DBException {
        Connection conn = null;
        PreparedStatement pSubCatsStmt = null;
        ResultSet rs = null;

        try {
            conn = WebShare.database.getConnection();
            pSubCatsStmt = conn.prepareStatement("SELECT Categories.categoryId, Categories.aclId FROM Categories WHERE parentCategoryId = ?");

            Set catIds = new HashSet();
            catIds.add(categoryId);

            List tmpCatIds = new ArrayList();
            tmpCatIds.add(categoryId);

            Integer tmpCategoryId;

            while (!tmpCatIds.isEmpty()) {
                tmpCategoryId = (Integer) tmpCatIds.remove(0);

                pSubCatsStmt.setInt(1, tmpCategoryId.intValue());

                rs = pSubCatsStmt.executeQuery();

                while (rs.next()) {
                    Integer subCatId = new Integer(rs.getInt("categoryId"));
                    Integer aclId = new Integer(rs.getInt("aclId"));

                    if (PermissionManager.hasPermission(VKCAclSerializer.retrieveAcl(aclId), user, permToken)) {
                        tmpCatIds.add(subCatId);
                        catIds.add(subCatId);
                    }
                }

                DBHelper.close(rs);
            }

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

    /**
     * Gibt die Kategorie-ID aus, in der ein Dokument veröffentlicht ist.
     * (Verwendung bei WebDAV-Servlet)
     *
     * @param docId Id des Dokuments
     * @return Kategorie-ID
     *
     * @throws DBException
     */
    public final static Integer retrieveCategoryIdForDocument(Integer docId) throws DBException {
        Connection conn = null;
        PreparedStatement pStmt = null;
        ResultSet rs = null;

        try {
            conn = WebShare.database.getConnection();
            pStmt = conn.prepareStatement("SELECT categoryId FROM categorydocuments WHERE documentId = ?");
            pStmt.setInt(1, docId.intValue());
            rs = pStmt.executeQuery();

            Integer cat;

            if (rs.next()) {
                cat = new Integer(rs.getInt("categoryId"));
            } else {
                cat = null;
            }

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

    /**
     * Ermittle die Anzahl Untercategorien und aller enthaltenen Dokumente
     * einer gegebenen Catgegorie
     *
     * <p></p>
     *
     * @return VKCCountCatDocs
     */
    public final static VKCCountCatDocs getCountCatDocs(Integer categoryId) throws DBException {
        if (Integer.parseInt(WebShare.getSysParameterValue("CATEGORYVIEW", "EnableCountCatDocs", "0")) == 0) {
            return null;
        }

        Connection conn = null;

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

            return getCountCatDocs(categoryId, conn);
        } catch (Exception e) {
            VKCLogManager.errorLog(CategoryUtils.class, "1", "Error occured while retrieving Contexts of Sysparameters!", e);
            throw new DBException(e);
        } finally {
            DBHelper.close(conn);
        }
    }

    /**
     * Ermittle die Anzahl Untercategorien und aller enthaltenen Dokumente
     * einer gegebenen Categorie
     *
     * <p></p>
     *
     * @return VKCCountCatDocs
     */
    private static final VKCCountCatDocs getCountCatDocs(Integer categoryId, Connection conn) throws DBException {
        PreparedStatement pStmt = null;
        PreparedStatement pStmt1 = null;
        PreparedStatement pStmt2 = null;
        PreparedStatement pStmt3 = null;
        ResultSet rs = null;
        ResultSet rsVersion = null;
        ResultSet rsAddDoc = null;

        try {
            int countDocs = 0;
            long docSize = 0;
            long addDocSize = 0;
            int currDocId = 0;

            VKCCountCatDocs countCatDocsCurrent = new VKCCountCatDocs();
            countCatDocsCurrent.setCategoryId(categoryId.intValue());

            // retrieve all documents for this category
            pStmt = conn.prepareStatement(
                    "SELECT documents.documentId, documents.docsize, documents.preDocId" +
                    "  FROM categoryDocuments, documents" +
                    " WHERE categoryDocuments.documentId = documents.documentId" +
                    "   AND documents.flag = " + VKCDocument.FLAG_PUBLISHED +
                    "   AND categoryDocuments.categoryId = ?");
            pStmt.setInt(1, categoryId.intValue());
            rs = pStmt.executeQuery();

            pStmt1 = conn.prepareStatement("SELECT documentId, docsize, preDocId FROM documents WHERE documentId = ?");
            pStmt2 = conn.prepareStatement("SELECT SUM(docsize) as SUM_DOCSIZE FROM additionaldocumentformats WHERE documentId = ?");

            while (rs.next()) {
                countDocs++;
                docSize = docSize + rs.getLong("docsize");

                currDocId = rs.getInt("preDocId");

                if (!rs.wasNull()) {
                    //  durch alle versionen iterieren
                    while (currDocId != 0) {
                        pStmt1.setInt(1, currDocId);
                        rsVersion = pStmt1.executeQuery();

                        if (rsVersion.next()) {
                            countDocs++;
                            docSize = docSize + rsVersion.getLong("docsize");

                            // bytes der einzelne versionen aus den addDocFmt holen
                            pStmt2.setInt(1, currDocId);
                            rsAddDoc = pStmt2.executeQuery();

                            if (rsAddDoc.next()) {
                                addDocSize = addDocSize + rsAddDoc.getLong("SUM_DOCSIZE");
                            }

                            DBHelper.close(rsAddDoc);

                            // gibt es eine weitere vorgänger version ?
                            currDocId = rsVersion.getInt("preDocId");

                            if (rsVersion.wasNull()) {
                                currDocId = 0;
                            }
                        } else {
                            currDocId = 0;
                        }

                        DBHelper.close(rsVersion);
                    }
                }
            }

            countCatDocsCurrent.setCountDocs(countDocs);
            countCatDocsCurrent.setAmountDocs(docSize);
            countCatDocsCurrent.setAmountAddDocs(addDocSize);
            DBHelper.close(rs);

            // retrieve all subcategories for this category
            pStmt3 = conn.prepareStatement("SELECT categoryId AS subCatId FROM categories WHERE parentCategoryId = ?");
            pStmt3.setInt(1, categoryId.intValue());
            rs = pStmt3.executeQuery();
            VKCCategory trashCategory = WebShare.getTrashCategory();
            
            while (rs.next()) {
            	Integer subCategoryId = new Integer(rs.getInt("subCatId"));
            	
        		if (trashCategory != null && trashCategory.getCategoryId().equals(subCategoryId)) {
        			continue;
        		}
            	
                // dive into subcategories
                VKCCountCatDocs countCatDocsSub = getCountCatDocs(subCategoryId, conn);

                // increment count of subcategories
                countCatDocsCurrent.setCountCats(countCatDocsCurrent.getCountCats() + countCatDocsSub.getCountCats() + 1);

                // add all docs from subcategories
                countCatDocsCurrent.setCountDocs(countCatDocsCurrent.getCountDocs() + countCatDocsSub.getCountDocs());

                // add all docs from subcategories
                countCatDocsCurrent.setAmountDocs(countCatDocsCurrent.getAmountDocs() + countCatDocsSub.getAmountDocs());

                // add all additional docs from subcategories
                countCatDocsCurrent.setAmountAddDocs(countCatDocsCurrent.getAmountAddDocs() + countCatDocsSub.getAmountAddDocs());

                // add subcategories
                countCatDocsCurrent.addCountSubCatDoc(countCatDocsSub);
            }

            return countCatDocsCurrent;
        } catch (Exception ex) {
            throw new DBException(ex);
        } finally {
            DBHelper.close(rs);
            DBHelper.close(rsVersion);
            DBHelper.close(rsAddDoc);
            DBHelper.close(pStmt);
            DBHelper.close(pStmt1);
            DBHelper.close(pStmt2);
            DBHelper.close(pStmt3);
        }
    }

    /**
     * Ermittle die Anzahl Untercategorien und aller enthaltenen Dokumente
     * einer gegebenen Catgegorie die für den Benutzer sichtbar sind
     *
     * <p></p>
     *
     * @return VKCCountCatDocs
     */
    public final static VKCCountCatDocs getAccessibleCountCatDocs(Integer categoryId, VKCUser user) throws DBException {
        if (Integer.parseInt(WebShare.getSysParameterValue("CATEGORYVIEW", "EnableCountCatDocs", "0")) == 0) {
            return null;
        }

        Connection conn = null;

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

            if (user.hasAdminPrivileges() || (Integer.parseInt(WebShare.getSysParameterValue("CATEGORYVIEW", "ListAccessibleCategoriesOnly", "0")) == 0)) {
                return getCountCatDocs(categoryId, conn);
            }

            Set categoryIds = getReadableCategoryIds(categoryId, user);

            return getAccessibleCountCatDocs(categoryId, categoryIds, conn);
        } catch (Exception e) {
            VKCLogManager.errorLog(CategoryUtils.class, "1", "Error occured while retrieving Contexts of Sysparameters!", e);
            throw new DBException(e);
        } finally {
            DBHelper.close(conn);
        }
    }

    /**
     * Ermittle die Anzahl Untercategorien und aller enthaltenen Dokumente
     * einer gegebenen Categorie die für den Benutzer sichtbar sind
     *
     * <p></p>
     *
     * @return VKCCountCatDocs
     */
    private static final VKCCountCatDocs getAccessibleCountCatDocs(Integer categoryId, Set categoryIds, Connection conn) throws DBException {
        PreparedStatement pStmt = null;
        PreparedStatement pStmt1 = null;
        PreparedStatement pStmt2 = null;
        PreparedStatement pStmt3 = null;
        ResultSet rs = null;
        ResultSet rsVersion = null;
        ResultSet rsAddDoc = null;

        try {
            int countDocs = 0;
            long docSize = 0;
            long addDocSize = 0;
            int currDocId = 0;

            VKCCountCatDocs countCatDocsCurrent = new VKCCountCatDocs();
            countCatDocsCurrent.setCategoryId(categoryId.intValue());

            // retrieve all documents for this category
            pStmt = conn.prepareStatement(
                    "SELECT documents.documentId, documents.docsize, documents.preDocId" +
                    "  FROM categoryDocuments, documents" +
                    " WHERE categoryDocuments.documentId = documents.documentId" +
                    "   AND documents.flag = " + VKCDocument.FLAG_PUBLISHED +
                    "   AND categoryDocuments.categoryId = ?");
            pStmt.setInt(1, categoryId.intValue());
            rs = pStmt.executeQuery();

            pStmt1 = conn.prepareStatement("SELECT documentId, docsize, preDocId FROM documents WHERE documentId = ?");
            pStmt2 = conn.prepareStatement("SELECT SUM(docsize) as SUM_DOCSIZE FROM additionaldocumentformats WHERE documentId = ?");

            while (rs.next()) {
                countDocs++;
                docSize = docSize + rs.getLong("docsize");

                currDocId = rs.getInt("preDocId");

                if (!rs.wasNull()) {
                    //  durch alle versionen iterieren
                    while (currDocId != 0) {
                        pStmt1.setInt(1, currDocId);
                        rsVersion = pStmt1.executeQuery();

                        if (rsVersion.next()) {
                            countDocs++;
                            docSize = docSize + rsVersion.getLong("docsize");

                            // bytes der einzelne versionen aus den addDocFmt holen
                            pStmt2.setInt(1, currDocId);
                            rsAddDoc = pStmt2.executeQuery();

                            if (rsAddDoc.next()) {
                                addDocSize = addDocSize + rsAddDoc.getLong("SUM_DOCSIZE");
                            }

                            DBHelper.close(rsAddDoc);

                            // gibt es eine weitere vorgänger version ?
                            currDocId = rsVersion.getInt("preDocId");

                            if (rsVersion.wasNull()) {
                                currDocId = 0;
                            }
                        } else {
                            currDocId = 0;
                        }

                        DBHelper.close(rsVersion);
                    }
                }
            }

            countCatDocsCurrent.setCountDocs(countDocs);
            countCatDocsCurrent.setAmountDocs(docSize);
            countCatDocsCurrent.setAmountAddDocs(addDocSize);
            DBHelper.close(rs);

            // retrieve all subcategories for this category
            pStmt3 = conn.prepareStatement("SELECT categoryId AS subCatId FROM categories WHERE parentCategoryId = ?");
            pStmt3.setInt(1, categoryId.intValue());
            rs = pStmt3.executeQuery();
            VKCCategory trashCategory = WebShare.getTrashCategory();

            while (rs.next()) {
                Integer subCategoryId = new Integer(rs.getInt("subCatId"));

                if (categoryIds.contains(subCategoryId)) {
            		if (trashCategory != null && trashCategory.getCategoryId().equals(subCategoryId)) {
            			continue;
            		}
                	
                    // dive into subcategories
                    VKCCountCatDocs countCatDocsSub = getAccessibleCountCatDocs(subCategoryId, categoryIds, conn);

                    // increment count of subcategories
                    countCatDocsCurrent.setCountCats(countCatDocsCurrent.getCountCats() + countCatDocsSub.getCountCats() + 1);

                    // add all docs from subcategories
                    countCatDocsCurrent.setCountDocs(countCatDocsCurrent.getCountDocs() + countCatDocsSub.getCountDocs());

                    // add all docs from subcategories
                    countCatDocsCurrent.setAmountDocs(countCatDocsCurrent.getAmountDocs() + countCatDocsSub.getAmountDocs());

                    // add all additional docs from subcategories
                    countCatDocsCurrent.setAmountAddDocs(countCatDocsCurrent.getAmountAddDocs() + countCatDocsSub.getAmountAddDocs());

                    // add subcategories
                    countCatDocsCurrent.addCountSubCatDoc(countCatDocsSub);
                }
            }

            return countCatDocsCurrent;
        } catch (Exception ex) {
            throw new DBException(ex);
        } finally {
            DBHelper.close(rs);
            DBHelper.close(rsVersion);
            DBHelper.close(rsAddDoc);
            DBHelper.close(pStmt);
            DBHelper.close(pStmt1);
            DBHelper.close(pStmt2);
            DBHelper.close(pStmt3);
        }
    }

    /**
     * Methode bestimmt rekursiv alle Ids der Unterkategorien einer Kategorie
     */
    public final static Set getAllSubCategoryIdsRecursiv(Integer rootId) throws DBException {
        if (rootId == null) {
            return new HashSet();
        }

        Connection conn = null;

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

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

    /**
     * Methode bestimmt rekursiv alle Ids der Unterkategorien einer Kategorie
     */
    public final static Set getAllSubCategoryIdsRecursiv(Integer rootId, Connection conn) throws DBException {
        if (rootId == null) {
            return new HashSet();
        }

        Set catIds = new HashSet();

        try {
            catIds = getSubCategoryIdsRecursiv(rootId, conn);
            catIds.remove(rootId);

            return catIds;
        } catch (Exception e) {
            throw new DBException(e);
        }
    }

    private final static Set getSubCategoryIdsRecursiv(Integer rootId, Connection conn) throws DBException {
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        Set catIds = new HashSet();

        try {
            pStmt = conn.prepareStatement("SELECT categoryId FROM Categories WHERE parentCategoryId = ?");
            pStmt.setInt(1, rootId.intValue());
            rs = pStmt.executeQuery();

            while (rs.next()) {
                Integer subId = new Integer(rs.getInt("categoryId"));
                catIds.addAll(getSubCategoryIdsRecursiv(subId, conn));
            }

            catIds.add(rootId);

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

    
    /**
     * Diese Methode bestimmt ob die Kategorie mit der ID "categoryId" sich im
     * trash befindet
     */
    public final static boolean isInTrash(Integer categoryId) throws DBException {
        if (categoryId == null) {
            return false;
        }
        
        if (!WebShare.isTrashEnabled()) {
        	return false;
        }
        
        return isInTree(new Integer(WebShare.getSysParameterValue("TRASH", "TrashCategoryId", "0")), categoryId);
    }
    
    /**
     * Diese Methode bestimmt ob die Kategorie mit der ID "categoryId" sich im
     * hierarchischem Baumstruktur der Kategorie mit der ID "rootCategoryId" befindet
     */
    public final static boolean isInTree(Integer rootCategoryId, Integer categoryId) throws DBException {
        if ((rootCategoryId == null) || (categoryId == null)) {
            return false;
        }

        Connection conn = null;

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

            return isInTree(rootCategoryId, categoryId, conn);
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(conn);
        }
    }

    /**
     * Diese Methode bestimmt ob die Kategorie mit der ID "categoryId" sich im
     * hierarchischen Baumstruktur der Kategorie mit der ID "rootCategoryId" befindet
     */
    public final static boolean isInTree(Integer rootCategoryId, Integer categoryId, Connection conn) throws DBException {
        if ((rootCategoryId == null) || (categoryId == null)) {
            return false;
        }

        if (rootCategoryId.equals(categoryId)) {
            return true;
        }

        Set catIds = getAllSubCategoryIdsRecursiv(rootCategoryId, conn);

        return catIds.contains(categoryId);
    }

    
    /**
     * Methode liefert ein Set von (Integer) UserIds zurück, die in der
     * übergebenen Kategorie Leserechte besitzen ( direkt und über
     * Gruppenrechte)
     */
    public final static Set getUserIdsWithReadAccess(VKCCategory category, Connection conn) throws DBException {
        HashSet userIds = new HashSet();
        Set validGroupIds = VKCGroupSerializer.getAllValidGroupIds(conn);
        VKCAcl acl = category.getAcl();

        for (Iterator iter = acl.getAclEntries().iterator(); iter.hasNext();) {
            VKCAclEntry entry = (VKCAclEntry) iter.next();

            if (entry.getPermission().getToken() == 'r') {
                if (entry.getAclType().isGroupType()) { // Alle Benutzer der Gruppe hinzufügen

                    VKCGroup group = VKCGroupSerializer.retrieveGroup(entry.getAccessorId(), conn);

                    if ((group != null) && validGroupIds.contains(group.getGroupId())) {
                        userIds.addAll(group.getGroupUserIds());
                    }
                } else if (entry.getAclType().isUserType()) { // Einzelnen Benutzer hinzufügen
                    userIds.add(entry.getAccessorId());
                }
            }
        }

        return userIds;
    }

    /**
     * Liefert Name der angegeben Kategorie zurück
     */
    public final static String getCategoryName(Integer categoryId) throws DBException {
        Connection conn = null;

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

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

    /**
     * Liefert Name der angegeben Kategorie zurück
     */
    public final static String getCategoryName(Integer categoryId, Connection conn) throws DBException {
        if (categoryId == null) {
            return "";
        }

        PreparedStatement pStmt = null;
        ResultSet rs = null;
        String name = "";

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

            if (rs.next()) {
                name = rs.getString("name");

                if (rs.wasNull()) {
                    name = "";
                }
            }

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

    /**
     * Gibt die ID der übergeordneten Kategorie zurück
     *
     * @param categoryId ID einer Kategorie
     *
     * @throws DBException
     */
    public static final Integer getParentCategoryId(Integer categoryId) throws DBException {
        Connection conn = null;

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

            return getParentCategoryId(categoryId, conn);
        } catch (Exception e) {
            VKCLogManager.errorLog(VKCCategorySerializer.class, "", "Exception while retrieving parentCategoryId of category with id = " + categoryId, e);
            throw new DBException(e);
        } finally {
            DBHelper.close(conn);
        }
    }

    /**
     * Gibt die ID der übergeordneten Kategorie zurück
     *
     * @param categoryId ID einer Kategorie
     *
     * @throws DBException
     */
    public static final Integer getParentCategoryId(Integer categoryId, Connection conn) throws DBException {
        PreparedStatement pStmt = null;
        ResultSet rs = null;

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

            if (rs.next()) {
                return new Integer(rs.getInt("PARENTCATEGORYID"));
            }

            return null;
        } catch (Exception e) {
            VKCLogManager.errorLog(VKCCategorySerializer.class, "", "Exception while retrieving parentCategoryId of category with id = " + categoryId, e);
            throw new DBException(e);
        } finally {
            DBHelper.close(rs);
            DBHelper.close(pStmt);
        }
    }
}