/*
 * $Id: ProjectUtils.java,v 1.34 2007/11/29 11:25:38 marbobo Exp $
 *
 * Copyright (c) Siemens AG 2008 
 *           (c) Universität Paderborn 2008 
 * Licensed under EUPL 1.1. 
 *
 * $Log: ProjectUtils.java,v $
 * Revision 1.34  2007/11/29 11:25:38  marbobo
 * + Bugfix
 *
 * Revision 1.33  2007/03/08 12:35:19  marbobo
 * + Bugfix
 *
 * Revision 1.32  2006/11/27 13:01:27  lawern
 * Daniels Bugfix bzgl. Projekt mit Template anlegen.
 *
 * Revision 1.31  2006/11/11 22:04:32  marbobo
 * + Beim Kopieren/Verschieben von Kategorien/Projekten/Projektordnern werden in Abhängigkeit des Zielordners die entsprechenden Umkonvertierungen vorgenommen
 *
 * Revision 1.30  2006/10/04 03:15:20  marbobo
 * + Vollständige Unterstützung für Templates implementiert
 * + Alles von Virtoweb auf ProCoBa umgestellt
 *
 * Revision 1.29  2006/08/24 22:16:25  marbobo
 * + Bugfix
 *
 * Revision 1.28  2006/07/12 12:21:55  marbobo
 * + Bugfix
 *
 * Revision 1.27  2006/04/01 19:04:22  marbobo
 * + Bugfix (gelöschte Projekte nicht anzeigen)
 *
 * Revision 1.26  2005/02/16 19:15:18  marbobo
 * + Feld Creator korriegiert in CreatorId
 *
 * Revision 1.25  2005/02/16 16:32:04  jonyp
 * put creator/created into projectinfo
 *
 * Revision 1.24  2004/12/09 12:45:19  lawern
 * vergessene PreparedStatements geschlossen
 *
 * Revision 1.23  2004/12/06 14:08:01  marbobo
 * + Statement auf PreparedStatement umgestellt
 *
 * Revision 1.22  2004/11/24 13:01:14  lawern
 * Global "conn.setAutoCommit( false );" enfernt, weil überflüssig.
 *
 * Revision 1.21  2004/11/24 09:25:57  lawern
 * Hibernate ist VKC-Geschichte!
 *
 * Revision 1.20  2004/11/18 19:38:24  marbobo
 * + changed order of close-stmt to resultset/connect/session  in finally-blocks
 *
 * Revision 1.19  2004/11/10 15:02:47  koppy
 * Projektweite Überarbeitung von Error-Logs, Stacktraces,..
 *
 * Revision 1.18  2004/11/09 14:35:42  marbobo
 * + Umstellung von authorId auf publisherId
 * + Hinzufügen von AuthorName für Dokumente
 * + Nötwendige Änderungen betreffend AuthorName und PublisherId
 *
 * Revision 1.17  2004/10/31 18:19:36  marbobo
 * + Bei den Kopier- und Verschiebefunktionen   Benutzerrechte berücksichtigen
 * + Der Benutzer der Kopier- und Verschiebefunktionen ausführt bekommt standardmäßig alle Rechte
 *
 * Revision 1.16  2004/09/07 13:24:48  marbobo
 * + Statt getSubCategoryIds() -> getAllSubCategoryIdsRecursiv() verwendet
 *
 * Revision 1.15  2004/08/04 23:20:46  marbobo
 * + Flag für Kategorien berücksichtigen
 *
 * Revision 1.14  2004/07/20 08:46:28  jonyp
 * added copyright info + cvs log
 *
 *
 */
package vkc.util;

import org.apache.struts.util.MessageResources;

import java.sql.Connection;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.Locale;
import java.util.Set;

import java.util.List;

import vkc.db.entities.VKCCategory;
import vkc.db.entities.VKCDocument;
import vkc.db.entities.VKCUser;
import vkc.db.serializer.VKCAclSerializer;
import vkc.db.serializer.VKCCategorySerializer;
import vkc.db.serializer.VKCNoteSerializer;
import vkc.db.serializer.VKCSysParameterSerializer;
import vkc.db.serializer.VKCUserSerializer;
import vkc.exception.DBException;
import vkc.db.helper.DBHelper;
import vkc.misc.VKCLogManager;
import vkc.procoba.project.ProjectViewForm;
import vkc.procoba.project.entities.VKCProject;
import vkc.procoba.project.entities.VKCProjectMessage;
import vkc.procoba.project.entities.VKCProjectSubfolder;
import vkc.security.PermissionManager;
import vkc.security.VKCAcl;
import vkc.view.WebShare;
import vkc.view.bean.ProjectInfo;
import vkc.view.bean.UserInfo;

public class ProjectUtils {
	private ProjectUtils() {
	}

	private final static void copyCategoryProperties(VKCCategory source, VKCCategory target) {
		// Dynamische Attribute duplizieren
		target.setAddAttributes(source.getAddAttributes());
		target.setAllowedDocumentTypes(source.getAllowedDocumentTypes());
		target.setCreated(source.getCreated());
		target.setCreatorId(source.getCreatorId());
		target.setDescription(source.getDescription());
		target.setKeywordsByString(source.getKeywordsAsString());
		target.setLastAccessed(source.getLastAccessed());
		target.setLastAccessorId(source.getLastAccessorId());
		target.setLastChanged(source.getLastChanged());
		target.setLastChangerId(source.getLastChangerId());
		target.setName(source.getName());
		target.setShowDescription(source.getShowDescription());
		target.setStatus(source.getStatus());
		target.setFlag(source.getFlag());
	}

	public final static VKCProject convertToProject(VKCCategory source) {
		VKCProject target = new VKCProject();
		ProjectUtils.copyCategoryProperties(source, target);

		return target;
	}

	public final static VKCProjectSubfolder convertToProjectSubfolder(VKCCategory source) {
		VKCProjectSubfolder target = new VKCProjectSubfolder();
		ProjectUtils.copyCategoryProperties(source, target);

		return target;
	}

	/**
	 * Methode legt ein Projekt auf Basis einer Templatekategorie an.
	 * <p>
	 * @param name - Name des Projektverzeichnisses
	 * @param keywords - Schlüsselwörter des Projektordners
	 * @param description - Beschreibung des Projektordners
	 * @param projectSteps - Initiale Projektschritte
	 * @param targetCategoryId - Zielverzeichnis für den Projektordner
	 * @param templateCategoryId - Vorlage für die Ordnerstruktur
	 * @param acl - Zugriffsrechte für den Projektordner
	 * @param creator -
	 * @return projectId
	 * @throws DBException
	 */
	public final static Integer createProjectByTemplate(String name,
														String keywords, 
														String description, 
														List projectSteps,
														Integer targetCategoryId, 
														Integer templateCategoryId, 
														VKCAcl acl,
														VKCUser creator) 
	throws DBException {
		Connection conn = null;

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

			VKCCategory targetCategory = VKCCategorySerializer.retrieveCategory(targetCategoryId, conn);

			VKCCategory projectCategory = WebShare.database.copyCategory(conn, targetCategory, templateCategoryId, name, true, creator);

			projectCategory.setName(name);
			projectCategory.setKeywordsByString(keywords);
			projectCategory.setDescription(description);
			projectCategory.setParentCategoryId(targetCategoryId);

			if (acl != null) {
				acl.setAclId(null);
				projectCategory.setAcl(acl);
			}

			VKCCategorySerializer.saveCategory(projectCategory, conn);

			convertToProject(projectCategory.getCategoryId(), conn);

			VKCProject project = (VKCProject) VKCCategorySerializer.retrieveCategory(projectCategory.getCategoryId(), conn);
			project.setProjectSteps(projectSteps);
			VKCCategorySerializer.saveCategory(project, conn);

			conn.commit();

			return project.getCategoryId();
		} catch (Exception e) {
            e.printStackTrace();
			DBHelper.rollBack(conn);
			throw new DBException(e);
		} finally {
			DBHelper.close(conn);
		}
	}

	/**
	 * Methode wandelt eine Kategorie in einen Projektordner um
	 */
	public final static void convertToProject(Integer categoryId, Connection conn) throws DBException {
		PreparedStatement pStmt = null;
		PreparedStatement pStmt2 = null;
		PreparedStatement pStmt3 = null;

		try {
			Set subcategoryIds = CategoryUtils.getAllSubCategoryIdsRecursiv(categoryId, conn);

			pStmt = conn.prepareStatement("UPDATE CATEGORIES SET CATEGORYTYPE = " + VKCCategory.TYPE_PROJECT + " WHERE CATEGORYID = ?");
			pStmt.setInt(1, categoryId.intValue());
			pStmt.execute();
			pStmt2 = conn.prepareStatement("DELETE FROM PROJECTS WHERE CATEGORYID = ?");
			pStmt2.setInt(1, categoryId.intValue());
			pStmt2.execute();
			pStmt3 = conn.prepareStatement("INSERT INTO PROJECTS ( EXTERNALCONTACTS, CATEGORYID ) values ( '', ? )");
			pStmt3.setInt(1, categoryId.intValue());
			pStmt3.execute();

			DBHelper.close(pStmt);
			DBHelper.close(pStmt2);
			DBHelper.close(pStmt3);

			pStmt = conn.prepareStatement("UPDATE CATEGORIES SET CATEGORYTYPE = " + VKCCategory.TYPE_PROJECT_SUBFOLDER + " WHERE CATEGORYID = ?");
			pStmt2 = conn.prepareStatement("DELETE FROM PROJECTSUBFOLDERS WHERE CATEGORYID = ?");
			pStmt3 = conn.prepareStatement("INSERT INTO PROJECTSUBFOLDERS ( PROJECTID, CATEGORYID ) values ( ?, ? )");

			for (Iterator iter = subcategoryIds.iterator(); iter.hasNext();) {
				Integer id = (Integer) iter.next();

				pStmt.setInt(1, id.intValue());
				pStmt.execute();
				pStmt2.setInt(1, id.intValue());
				pStmt2.execute();
				pStmt3.setInt(1, categoryId.intValue());
				pStmt3.setInt(2, id.intValue());
				pStmt3.execute();
			}
		} catch (Exception ex) {
			throw new DBException(ex);
		} finally {
			DBHelper.close(pStmt);
			DBHelper.close(pStmt2);
			DBHelper.close(pStmt3);
		}
	}

	/**
	 * Gibt eine Liste mit ProjectInfos aller vom einem Benutzer lesbaren
	 * Projekt-Kategorien zurück
	 * <p>
	 * @param user - Referenz auf ein VKCUser-Objekt
	 * <p>
	 * @return java.util.List bestehend aus ProjectInfo-Objekten
	 */
	public final static List getProjectInfos(VKCUser user) throws DBException {
		Connection conn = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		List infos = new ArrayList();

		try {
			conn = WebShare.database.getConnection();
			pStmt = conn.prepareStatement("SELECT name, categoryId, aclid, noteid, status, flag, creatorid, created from CATEGORIES WHERE CATEGORYTYPE = " + VKCCategory.TYPE_PROJECT + " AND flag <> ? AND parentCategoryId = ?");
			pStmt.setInt(1, VKCCategory.FLAG_DELETED.intValue());
			pStmt.setInt(2, VKCSysParameterSerializer.getProjectsRootCategoryId());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				VKCAcl catAcl = VKCAclSerializer.retrieveAcl(new Integer(rs.getInt("aclId")), conn);

				if (PermissionManager.hasPermission(catAcl, user, 'r')) {
					ProjectInfo info = new ProjectInfo();

					info.setId(new Integer(rs.getInt("categoryId")));
					info.setName(rs.getString("name"));

			        int noteId = rs.getInt("noteId");
			        
			        if (rs.wasNull()) {
			        	info.setDescription("");
			        } else {
			        	info.setDescription(VKCNoteSerializer.retrieveNote(new Integer(noteId), conn));
			        }

			        info.setProjectstatus(new Integer(rs.getInt("status")));
					info.setProjectflag(new Integer(rs.getInt("flag")));

					UserInfo userInfo = VKCUserSerializer.retrieveUserInfo(new Integer(rs.getInt("creatorid")), conn);
					info.setCreator(userInfo.getFullName());
					info.setCreated((rs.getTimestamp("created")).toString());

					infos.add(info);
				}
			}

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

	public final static void populateNewDocumentPublishedMessages(VKCCategory category, VKCDocument doc, Connection conn) {
		try {
			if (!category.isProjectCategory() && !category.isProjectSubfolder()) {
				return;
			}

			Set userIds = CategoryUtils.getUserIdsWithReadAccess(category, conn);

			Integer projectId = category.isProjectCategory() ? ((VKCProject) category).getCategoryId() : ((VKCProjectSubfolder) category).getProjectId();

			Integer publisherId = doc.getPublisherId();
			VKCUser publisher = VKCUserSerializer.retrieveUser(publisherId);
			String publisherName = publisher.getFirstname() + " " + publisher.getLastname();

			for (Iterator iter = userIds.iterator(); iter.hasNext();) {
				Integer recepientId = (Integer) iter.next();
				VKCUser recepient = VKCUserSerializer.retrieveUser(recepientId);

				// if recipient = author ==> do not give him a message
				if (recepient != null && recepient.getUserId().intValue() != publisherId.intValue()) {
					VKCProjectMessage msg = new VKCProjectMessage();
					msg.setProjectId(projectId);
					msg.setCreationDate(new Date());
					msg.setCreatorId(null);
					msg.setRecepientId(recepientId);

					Locale locale = recepient.getLanguage().getLocale();

					MessageResources res = MessageResources.getMessageResources("vkc.resources.application");

					if (doc.isLink()) {
						msg.setText(res.getMessage(locale, "project.message.new.link", new String[] {doc.getName(), publisherName }));
					} else if (doc.isNote()) {
						msg.setText(res.getMessage(locale, "project.message.new.note", publisherName));
					} else if (doc.isFile()) {
						msg.setText(res.getMessage(locale, "project.message.new.document", new String[] {doc.getName(), publisherName }));
					}

					msg.setLink("/projectView.do?projectId=" + projectId + 
								"&subfolderId=" + category.getCategoryId() + 
								"&viewMode=" + ProjectViewForm.VIEW_MODE_FOLDER + 
								"&anchor=doc" + doc.getDocumentId());

					msg.setLinkKey("project.message.link.document.category");

					saveProjectMessage(msg, conn);
				}
			}
		} catch (Exception e) {
			VKCLogManager.errorLog(ProjectUtils.class, "", e.getMessage(), e);
		}
	}

	/**
	 * Methode holt alle projektbezogenen Nachrichten für einen User  aus der DB
	 */
	public static final List retrieveProjectMessages(Integer projectId, Integer userId) throws DBException {
		Connection conn = null;

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

			return retrieveProjectMessages(projectId, userId, conn);
		} catch (Exception e) {
			throw new DBException(e);
		} finally {
			DBHelper.close(conn);
		}
	}

	/**
	 * Methode holt alle projektbezogenen Nachrichten für einen User  aus der DB
	 */
	public static List retrieveProjectMessages(Integer projectId, Integer userId, Connection conn) throws SQLException {
		List messages = new ArrayList();

		if (projectId == null || projectId.intValue() <= 0 || userId == null || userId.intValue() <= 0) {
			return messages;
		}

		PreparedStatement pStmt = null;
		ResultSet rs = null;

		try {
			pStmt = conn.prepareStatement("SELECT * FROM PROJECTMESSAGES WHERE PROJECTID = ? AND RECEPIENTID = ?");
			pStmt.setInt(1, projectId.intValue());
			pStmt.setInt(2, userId.intValue());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				VKCProjectMessage m = new VKCProjectMessage();

				m.setMessageId(new Integer(rs.getInt("messageId")));
				m.setProjectId(new Integer(rs.getInt("projectId")));
				m.setText(rs.getString("messageText"));
				m.setLink(rs.getString("link"));
				m.setLinkKey(rs.getString("linkKey"));
				m.setCreatorId(new Integer(rs.getInt("creatorId")));
				m.setCreationDate(rs.getTimestamp("created"));

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

				if (rs.wasNull()) {
					m.setRecepientId(null);
				} else {
					m.setRecepientId(new Integer(recepientId));
				}

				messages.add(m);
			}

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

	/**
	 * Methode speichert eine Projektnachricht
	 */
	public static final void saveProjectMessage(VKCProjectMessage msg) throws DBException {
		Connection conn = null;

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

	/**
	 * Methode speichert eine Projektnachricht
	 */
	public static final void saveProjectMessage(VKCProjectMessage msg, Connection conn) throws DBException {
		PreparedStatement pStmt = null;

		try {
			// evtl. vorhandene Message löschen
			if (msg.getMessageId() != null) {
				DBHelper.deleteEntry(conn, "PROJECTMESSAGES", "MESSAGEID", msg.getMessageId());
			}

			msg.setMessageId(DBHelper.getNewId(conn, "PROJECTMESSAGES", "MESSAGEID"));

			String sqlStatement = "INSERT INTO PROJECTMESSAGES ( MESSAGEID, PROJECTID, RECEPIENTID, MESSAGETEXT, LINK, LINKKEY, CREATED, CREATORID ) " + 
								  "VALUES ( ?, ?, ?, ?, ?, ?, ?, ? )";

			pStmt = conn.prepareStatement(sqlStatement);

			pStmt.setInt(1, msg.getMessageId().intValue());
			pStmt.setInt(2, msg.getProjectId().intValue());
			pStmt.setInt(3, msg.getRecepientId().intValue());
			pStmt.setString(4, msg.getText());

			if (msg.getLink() == null || msg.getLink().length() == 0) {
				pStmt.setNull(5, Types.VARCHAR);
			} else {
				pStmt.setString(5, msg.getLink());
			}

			if (msg.getLinkKey() == null || msg.getLinkKey().length() == 0) {
				pStmt.setNull(6, Types.VARCHAR);
			} else {
				pStmt.setString(6, msg.getLinkKey());
			}

			pStmt.setTimestamp(7, new Timestamp(msg.getCreationDate().getTime()));

			if (msg.getCreatorId() == null) {
				pStmt.setNull(8, Types.INTEGER);
			} else {
				pStmt.setInt(8, msg.getCreatorId().intValue());
			}

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

	/**
	 * Methode löscht eine Projektnachricht
	 */
	public static final void deleteProjectMessage(Integer id) throws DBException {
		if (id == null || id.intValue() <= 0) {
			return;
		}

		Connection conn = null;
		PreparedStatement pStmt = null;

		try {
			conn = WebShare.database.getConnection();
			pStmt = conn.prepareStatement("DELETE FROM PROJECTMESSAGES WHERE MESSAGEID = ?");
			pStmt.setInt(1, id.intValue());
			pStmt.execute();
			conn.commit();
		} catch (Exception e) {
			DBHelper.rollBack(conn);
			throw new DBException(e);
		} finally {
			DBHelper.close(pStmt);
			DBHelper.close(conn);
		}
	}

	public static void convertToProjectSubfolder(Integer categoryId, Integer projectId, Connection conn) throws DBException {
		PreparedStatement pStmt = null;
		PreparedStatement pStmt1 = null;
		PreparedStatement pStmt2 = null;
		PreparedStatement pStmt3 = null;

		try {
			Set subcategoryIds = CategoryUtils.getAllSubCategoryIdsRecursiv(categoryId, conn);

			pStmt = conn.prepareStatement("UPDATE CATEGORIES SET CATEGORYTYPE = " + VKCCategory.TYPE_PROJECT_SUBFOLDER + " WHERE CATEGORYID = ?");
            pStmt.setInt(1, categoryId.intValue());
            pStmt.execute();
			pStmt1 = conn.prepareStatement("DELETE FROM PROJECTS WHERE CATEGORYID = ?");
            pStmt1.setInt(1, categoryId.intValue());
            pStmt1.execute();
			pStmt2 = conn.prepareStatement("DELETE FROM PROJECTSUBFOLDERS WHERE CATEGORYID = ?");
            pStmt2.setInt(1, categoryId.intValue());
            pStmt2.execute();
			pStmt3 = conn.prepareStatement("INSERT INTO PROJECTSUBFOLDERS ( PROJECTID, CATEGORYID ) values ( ?, ? )");
            pStmt3.setInt(1, projectId.intValue());
            pStmt3.setInt(2, categoryId.intValue());
            pStmt3.execute();

            DBHelper.close(pStmt);
            DBHelper.close(pStmt1);
            DBHelper.close(pStmt2);
            DBHelper.close(pStmt3);

            for (Iterator iter = subcategoryIds.iterator(); iter.hasNext();) {
				Integer id = (Integer) iter.next();

				pStmt.setInt(1, id.intValue());
				pStmt.execute();
				pStmt1.setInt(1, id.intValue());
				pStmt1.execute();
				pStmt2.setInt(1, id.intValue());
				pStmt2.execute();
				pStmt3.setInt(1, projectId.intValue());
				pStmt3.setInt(2, id.intValue());
				pStmt3.execute();
			}
		} catch (Exception ex) {
			throw new DBException(ex);
		} finally {
			DBHelper.close(pStmt);
			DBHelper.close(pStmt1);
			DBHelper.close(pStmt2);
			DBHelper.close(pStmt3);
		}
	}
}