/*
 * $Id: DocumentUtils.java,v 1.23 2007/11/22 08:44:36 marbobo Exp $
 *
 * Copyright (c) Siemens AG 2010 
 *           (c) Universität Paderborn 2010 
 * Licensed under EUPL 1.1. 
 *
 * $Log: DocumentUtils.java,v $
 * Revision 1.23  2007/11/22 08:44:36  marbobo
 * + Bugfix
 *
 * Revision 1.22  2006/07/17 13:04:01  lawern
 * Neuer typografiegewichteter Index und k-NN-Klassifikation aus meiner Diss.
 *
 * Revision 1.21  2006/02/27 09:25:34  lawern
 * Falls retrieveDocumentIdsByCategoryIdAndName fälschlicherweise mehr als ein Ergebniss liefert, das Ergebnis absteigend nach Versionen sortieren.
 *
 * Revision 1.20  2005/09/15 00:48:11  marbobo
 * + Sprachabhängiges Sortieren implementiert
 *
 * Revision 1.19  2005/09/13 00:23:25  marbobo
 * + getAllDocumentIdsRecursiv() um Flag-Parameter erweitert
 *
 * Revision 1.18  2005/09/07 22:06:58  marbobo
 * + Sortierung nach Herausgeber implementiert
 *
 * Revision 1.17  2005/09/05 22:48:25  marbobo
 * + Suche nach Metadaten-Attribute (Erstellungsdatum) für Dokumente implementiert
 *
 * Revision 1.16  2005/09/05 13:52:49  marbobo
 * + Refactoring (Methode umbenannt)
 *
 * Revision 1.15  2005/02/15 09:26:53  bigmac
 * Refactorings
 *
 * Revision 1.14  2005/02/09 10:25:37  lawern
 * Anpassung des WebDavServlet wegen Entity-Kodierung in der DB.
 *
 * Revision 1.13  2004/12/09 12:45:19  lawern
 * vergessene PreparedStatements geschlossen
 *
 * Revision 1.12  2004/12/06 14:08:01  marbobo
 * + Statement auf PreparedStatement umgestellt
 *
 * Revision 1.11  2004/11/24 09:25:57  lawern
 * Hibernate ist VKC-Geschichte!
 *
 * Revision 1.10  2004/09/07 12:29:10  marbobo
 * +  getAllDocumentIdsRecursiv()
 *
 * Revision 1.9  2004/09/03 00:42:44  marbobo
 * + Kategorieauswahl über ein Explorer für die Suche eingebaut
 *
 * Revision 1.8  2004/07/20 08:46:28  jonyp
 * added copyright info + cvs log
 *
 *
 */

package vkc.util;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

import java.sql.Connection;

import vkc.db.entities.VKCDocument;
import vkc.db.entities.VKCSearchQuery;
import vkc.db.entities.VKCUser;
import vkc.db.helper.DBHelper;
import vkc.db.serializer.VKCCategorySerializer;
import vkc.db.serializer.VKCDocumentSerializer;
import vkc.exception.DBException;
import vkc.search.Search;
import vkc.search.SearchResultEntry;
import vkc.util.sort.ComparatorFactory;
import vkc.util.sort.DocumentSortFactory;
import vkc.view.WebShare;
import vkc.webdav.URLUtil;

public class DocumentUtils {
    /**
     * Methode sucht alle Versionen ( incl. Nachfolger- und Vorgänger )
     * eines Dokuments zusammen
     */
    public final static List getDocumentVersions(Integer documentId) throws DBException {
        VKCDocument doc = VKCDocumentSerializer.retrieveDocument(documentId);

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

        ArrayList list = new ArrayList();

        if (doc.getPreDocId() != null) { // alle Vorgängerversionen holen
            // Weitere Versionen, falls vorhanden
            Integer currentPreDocId = doc.getPreDocId();

            while (currentPreDocId != null) {
                VKCDocument preDoc = VKCDocumentSerializer.retrieveDocument(currentPreDocId);
                list.add(preDoc);
                currentPreDocId = preDoc.getPreDocId();
            }
        }

        list.add(doc);

        while (true) { // alle Nachfolgeversionen holen
            VKCDocument succDoc = DocumentUtils.retrieveSuccessorDocument(doc.getDocumentId());
            
            if (succDoc == null) {
                break;
            } 

            list.add(succDoc);
            doc = succDoc;
        }

        DocumentSortFactory.sort(list, DocumentSortFactory.SORT_BY_PUBLISH_DATE_DOWN);
        
        return list;
    }

    /**
     * Lädt das Nachfolgedokument für die angegebene Dokumenten-ID
     */
    public final static VKCDocument retrieveSuccessorDocument(Integer documentId) throws DBException {
        if (documentId == null || documentId.intValue() <= 0) {
            return null;
        }

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

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

            if (rs.next()) {
                return VKCDocumentSerializer.retrieveDocument(new Integer(rs.getInt("DOCUMENTID")), conn);
            } 

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

	/**
	 * Gibt das VKCDocument zurück, welches auf die gegebene CONVERSION_STATUS_ID
	 * verweist
	 * @param id die CONVERSION_STATUS_ID
	 * @return
	 */
	public final static VKCDocument getDocumentByConversionStatusId(Integer id) throws DBException {
		Connection conn = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		try {

			conn = WebShare.database.getConnection();
			pStmt = conn.prepareStatement("SELECT DOCUMENTID FROM DOCUMENTS WHERE CONVERSION_STATUS_ID = ?");
			pStmt.setInt(1, id.intValue());
			rs = pStmt.executeQuery();

			if  (rs.next()) {
				return VKCDocumentSerializer.retrieveDocument(new Integer(rs.getInt("DOCUMENTID")), conn);
			} 

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

	/**
	 * Gibt eine ArrayList der IDs aller Dokumente einer Kategorie mit
	 * dem gegebenen Namen zurück.
	 * (Verwendung bei WebDAV-Servlet)
	 *
	 * @param catId Id der Kategorie in der die Dokumente gesucht werden sollen.
	 * @param docName Name des Dokuments das gesucht werden soll.
	 * @return ArrayList der VKCDocument-IDs
	 *
	 * @throws DBException
	 */
	public final static Collection retrieveDocumentIdsByCategoryIdAndName(Integer catId, String docName) throws DBException {
		Connection conn = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		Collection docs = new ArrayList();

		try {
			conn = WebShare.database.getConnection();
			pStmt = conn.prepareStatement(
			        "SELECT documents.documentId FROM categoryDocuments, documents " +
					"WHERE categoryDocuments.documentId = documents.documentId " +
					"AND categoryDocuments.categoryId = ? AND documents.name = ? " +
                    "order by documents.VERSION_NO desc");
			pStmt.setInt(1, catId.intValue());
			String filteredDocName =URLUtil.URLDecode(docName);
			pStmt.setString(2,filteredDocName );
			rs = pStmt.executeQuery();

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

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

	/**
	 * Bestimmt die ID des Projekts für ein Dokument falls es in einem
	 * Projekt- bzw. Projektunterverzeichnis liegt
	 * <p>
	 * @return Projekt-ID. null, falls Dokument in keinem Projektordner liegt.
	 */
	public final static Integer getProjectId(VKCDocument document) throws DBException {
		if (document == null || document.getCategoryId() == null) {
		    return null;
		}
	    
	    Connection conn = null;
		PreparedStatement pStmt1 = null;
		PreparedStatement pStmt2 = null;
		ResultSet rs1 = null;
		ResultSet rs2 = null;

		try {
			conn = WebShare.database.getConnection();
			pStmt1 = conn.prepareStatement("SELECT PROJECTID FROM PROJECTSUBFOLDERS WHERE CATEGORYID = ?");
			pStmt1.setInt(1, document.getCategoryId().intValue());
			rs1 = pStmt1.executeQuery();
			
			if (rs1.next()) { // Dokument liegt in einem Projektunterverzeichnis
				return new Integer(rs1.getInt("PROJECTID"));
			}

			pStmt2 = conn.prepareStatement("SELECT CATEGORYID FROM PROJECTS WHERE CATEGORYID = ?");
			pStmt2.setInt(1, document.getCategoryId().intValue());
			rs2 = pStmt2.executeQuery();
			
			if (rs2.next()) { // Dokument liegt in einem Projektverzeichnis
				return new Integer(rs2.getInt("CATEGORYID"));
			}

			return null;
		} catch (Exception e) {
			throw new DBException(e);
		} finally {
			DBHelper.close(rs1);
			DBHelper.close(rs2);
			DBHelper.close(pStmt1);
			DBHelper.close(pStmt2);
			DBHelper.close(conn);
		}
	}

	/**
	 * Methode liefert ein Set mit IDs von Dokumenten einer Kategorie
	 */
	public final static Set getDocumentIds(Integer categoryId) throws DBException {
		Connection conn = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		HashSet ids = new HashSet();

		try {
			conn = WebShare.database.getConnection();
			pStmt = conn.prepareStatement("SELECT DOCUMENTID FROM CATEGORYDOCUMENTS WHERE CATEGORYID = ?");
			pStmt.setInt(1, categoryId.intValue());
			rs = pStmt.executeQuery();
			
			while (rs.next()) {
				ids.add(new Integer(rs.getInt("DOCUMENTID")));
			}
			
			return ids;
		} catch(Exception ex) {
			throw new DBException(ex);
		} finally {
			DBHelper.close(rs);
			DBHelper.close(pStmt);
			DBHelper.close(conn);
		}
	}

	/**
	 * Methode bestimmt rekursiv alle Ids der als Veröffentlicht 
	 * markierten Dokumente einer Kategorie
	 */
	public final static Set getAllDocumentIdsRecursiv(Integer rootCategoryId, Integer flag)	throws DBException {
        if (rootCategoryId == null) {
            return new HashSet();
        }

        Connection conn = null;

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

	/**
	 * Methode bestimmt rekursiv alle Ids der als Veröffentlicht 
	 * markierten Dokumente einer Kategorie
	 */
	public final static Set getAllDocumentIdsRecursiv(Integer rootCategoryId, Integer flag, Connection conn) throws DBException {
        if (rootCategoryId == null) {
            return new HashSet();
        }

		try {
	        return getDocumentIdsRecursiv(rootCategoryId, flag, conn);
		} catch ( Exception e ) {
			throw new DBException( e );
		}
	}

	private final static Set getDocumentIdsRecursiv(Integer rootCategoryId, Integer flag, Connection conn) throws DBException {
	    PreparedStatement pStmt = null;
	    PreparedStatement pStmt2 = null;
        ResultSet rs = null;
        ResultSet rs1 = null;
        Set docIds = new HashSet();
        List params = new ArrayList();

		try {
			params.add(rootCategoryId);
			String statement = "SELECT Documents.documentId FROM CategoryDocuments, Documents " +
            				   "WHERE CategoryDocuments.categoryId = ?" +
            				   " AND CategoryDocuments.documentId = Documents.documentId";
            
			if (flag != null) {
				statement += " AND Documents.flag = ?";
				params.add(flag);
			}
			
			pStmt = conn.prepareStatement(statement);
            DBHelper.setElements(pStmt, params.toArray());
			rs = pStmt.executeQuery();
            
			while (rs.next()) {
			    docIds.add(new Integer(rs.getInt("documentId")));
			}

            pStmt2 = conn.prepareStatement("SELECT categoryId FROM Categories WHERE parentCategoryId = ?");
            pStmt2.setInt(1, rootCategoryId.intValue());
			rs1 = pStmt2.executeQuery();
			
			while (rs1.next()) {
			    Integer subCategoryId = new Integer(rs1.getInt("categoryId"));
			    docIds.addAll(getDocumentIdsRecursiv(subCategoryId, flag, conn));
			}
			
			return docIds;
		} catch (Exception e) {
			throw new DBException(e);
		} finally {
            DBHelper.close(rs);
            DBHelper.close(rs1);
            DBHelper.close(pStmt);
            DBHelper.close(pStmt2);
		}
	}

	/**
	 *  Methode holt alle virtuellen Dokumente einer Kategorie aus der DB
	 */
	public final static List getVirtualDocuments(Integer categoryId, VKCUser user) throws DBException {
		if (categoryId == null || user == null) {
			return new ArrayList();
		}

		VKCSearchQuery query = VKCCategorySerializer.retrieveSearchQuery(categoryId);
		
		if (query == null || StringUtils.isEmpty(query.getSearchQuery())) {
			return new ArrayList();
		}

		List results = Search.search(
				query.getSearchQuery(),
				query.getFrom(),
				query.getUntil(),
				query.getSelectedResultLimitation(),
				user,
				query.getCategoryId());

		switch (Integer.parseInt(query.getSelectedSortResults())) {
			case 0: Collections.sort(results, ComparatorFactory.getSearchResultAscendingByScore());
					break;
			case 1: Collections.sort(results, ComparatorFactory.getSearchResultDescendingByScore());
					break;
			case 2: Collections.sort(results, ComparatorFactory.getSearchResultAscendingByPublishingDate());
					break;
			case 3: Collections.sort(results, ComparatorFactory.getSearchResultDescendingByPublishingDate());
					break;
			case 4: Collections.sort(results, ComparatorFactory.getSearchResultAscendingByAuthor(user.getLanguage().getLocale()));
					break;
			case 5: Collections.sort(results, ComparatorFactory.getSearchResultDescendingByAuthor(user.getLanguage().getLocale()));
					break;
			case 6: Collections.sort(results, ComparatorFactory.getSearchResultAscendingByCreationDate());
					break;
			case 7: Collections.sort(results, ComparatorFactory.getSearchResultDescendingByCreationDate());
					break;
			case 8: Collections.sort(results, ComparatorFactory.getSearchResultAscendingByPublisher(user.getLanguage().getLocale()));
					break;
			case 9: Collections.sort(results, ComparatorFactory.getSearchResultDescendingByPublisher(user.getLanguage().getLocale()));
					break;
		}

		Set realDocumentIds = getDocumentIds(categoryId);
		List virtualDocuments = new ArrayList();

		// Alle Dokumente, die gefunden wurden und real in der kategorie liegen,
		// aus den Suchergebnissen ausgeblendet werden
		for (Iterator iter = results.iterator(); iter.hasNext();) {
			SearchResultEntry entry = (SearchResultEntry)iter.next();
			Integer entryDocumentId = new Integer(entry.getDocumentId());

			if (!realDocumentIds.contains(entryDocumentId)) {
				VKCDocument document = entry.getDocument();
				
				if (document != null) {
					virtualDocuments.add(document);
				}
			}
		}

		return virtualDocuments;
	}
    
    /**
     * Gibt für jede Kategorie mit mehr als einem Dokument das VKCDocument-Objekt des jüngsten Dokuments zurück.
     * (Dient zur Kreuzvalidierung der Parameter des NNClassifier)
     *
     * @return Collection (ArrayList) mit den VKCDocument-Objekten.
     *
     * @throws DBException
     */
    public final static Collection getNewestDocFromAllCategories()
        throws DBException {
        Connection conn = null;
        PreparedStatement pStmt = null;
        ResultSet rs = null;

//        String query = "select categories.categoryId, max(documents.documentId) as max_documentId " +
//            "from  categories, categoryDocuments, documents " +
//            "where categories.categoryId = categoryDocuments.categoryId " +
//            "and documents.documentId = categoryDocuments.documentId " +
//            "group by categories.categoryId " +
//            "having count(documents.documentId) > 1";

        String query = "select categories.categoryId, max(documents.documentId) as max_documentId " +
            "from  categories, categoryDocuments, documents, additionaldocumentformats " +
            "where categories.categoryId = categoryDocuments.categoryId " +
            "and documents.documentId = categoryDocuments.documentId " +
            "and documents.documentId = additionaldocumentformats.documentId " +
            "and additionaldocumentformats.internaldocformat = 1 " +
            "group by categories.categoryId " +
            "having count(documents.documentId) > 1";

        Collection docs = new ArrayList();

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

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

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