//----------------------------BEGIN LICENSE----------------------------
/* 
 * Willow : the Open Source WorkFlow Project
 * Distributable under GNU LGPL license by gun.org
 *
 * Copyright (C) 2004-2010 huihoo.org 
 * Copyright (C) 2004-2010  ZosaTapo <dertyang@hotmail.com> 
 * 
 * ====================================================================
 * Project Homepage : http://www.huihoo.org/willow
 * Source Forge     : http://sourceforge.net/projects/huihoo
 * Mailing list     : willow@lists.sourceforge.net 
 */
//----------------------------END  LICENSE----------------------------

package org.huihoo.workflow.tools;

import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.huihoo.commons.jdbc.JDBCUtilities;
import org.huihoo.workflow.SchemaContext;
import org.huihoo.workflow.WorkflowException;
import org.huihoo.workflow.WorkflowTransaction;
import org.huihoo.workflow.coreImpl.server.WeightableWorkImpl;
import org.huihoo.workflow.coreImpl.server.WorkflowWorkImpl;
import org.huihoo.workflow.desktop.CaseDatabase;
import org.huihoo.workflow.desktop.WorkflowImplementation;
import org.huihoo.workflow.event.WorkEvent;
import org.huihoo.workflow.server.WorkflowCase;
import org.huihoo.workflow.server.WorkflowWork;
import org.huihoo.workflow.usermodel.WorkflowParticipant;
import org.huihoo.workflow.xpdl.ExtendedAttribute;
import org.huihoo.workflow.xpdl.WorkflowActivity;
import org.huihoo.workflow.xpdl.WorkflowPackage;
import org.huihoo.workflow.xpdl.WorkflowProcess;
import org.huihoo.workflow.xpdl.XPDLGlobals;
import org.huihoo.workflow.xpdl.util.DateTools;
import org.huihoo.workflow.xpdl.util.Duration;
import org.huihoo.workflow.xpdl.util.DurationUnit;

public class PersonalDesktop
{
	public final static int WORKFLOW_NEVER_TIMEOUT = 0;
	public final static int WORKFLOW_PROCESS_TIMEOUT = 1;
	public final static int WORKFLOW_ACTIVITY_TIMEOUT = 2;

	public static List getDraftTray(WorkflowImplementation workflow) throws WorkflowException, RemoteException
	{
		WorkflowPackage workflowPackage = workflow.getWorkflowPackage();
		WorkflowProcess workflowProcess = workflowPackage.getMainWorkflowProcess();

		CaseDatabase caseDatabase = workflow.getCaseDatabase();
		WorkflowTransaction userTransaction = caseDatabase.getWorkflowTransaction();
		Connection conn = userTransaction.getConnection();

		ArrayList workList = new ArrayList();
		WorkflowWorkImpl workflowWork = null;

		PreparedStatement pstmt = null;
		ResultSet jrs = null;

		try
		{
			SchemaContext schemaContext = caseDatabase.getSchemaContext();

			String packageId = workflowPackage.getUUID();
			String processId = workflowProcess.getUUID();

			String strSQL =
				"SELECT * FROM "
					+ schemaContext.getTableName(SchemaContext.SCHEMA_WORK)
					+ " "
					+ "WHERE  vc_packageId=?"
					+ " "
					+ "AND    vc_processid=?"
					+ " "
					+ "AND    vc_performer=?"
					+ " "
					+ "AND    int_eventType=? "
					+ " "
					+ "AND    dat_dispatchTime IS NULL "
					+ " "
					+ "ORDER BY dat_arriveTime";

			pstmt = conn.prepareStatement(strSQL);
			pstmt.setString(1, packageId);
			pstmt.setString(2, processId);
			pstmt.setString(3, valueOf(workflow.getDesktop().getSession().getParticipant()));
			pstmt.setInt(4, WorkEvent.CREATED);

			jrs = pstmt.executeQuery();
			makeWorkList(workflow, userTransaction, workflowPackage, workflowProcess, jrs, null, workList);
			JDBCUtilities.cleanupNoThrow(jrs);
		}
		catch (SQLException sqlex)
		{
			WorkflowException ne = new WorkflowException("can not get workflow work list");
			ne.setRootCause(sqlex);
			throw ne;
		}
		finally
		{
			JDBCUtilities.cleanupNoThrow(conn, pstmt);
		}
		return workList;
	}

	public static List getIncomingTray(WorkflowImplementation workflow) throws WorkflowException, RemoteException
	{
		WorkflowPackage workflowPackage = workflow.getWorkflowPackage();
		WorkflowProcess workflowProcess = workflowPackage.getMainWorkflowProcess();

		CaseDatabase caseDatabase = workflow.getCaseDatabase();
		WorkflowTransaction userTransaction = caseDatabase.getWorkflowTransaction();
		Connection conn = userTransaction.getConnection();

		ArrayList workList = new ArrayList();
		WorkflowWorkImpl workflowWork = null;

		PreparedStatement pstmt = null;
		ResultSet jrs = null;

		try
		{
			SchemaContext schemaContext = caseDatabase.getSchemaContext();
			String packageId = workflowPackage.getUUID();
			String processId = workflowProcess.getUUID();

			String strSQL =
				"SELECT work.* FROM "
					+ schemaContext.getTableName(SchemaContext.SCHEMA_WORK)
					+ " work,"
					+ schemaContext.getTableName(SchemaContext.SCHEMA_CASE)
					+ " casex"
					+ " "
					+ "WHERE  work.vc_packageId=?"
					+ " "
					+ "AND    work.vc_processid=?"
					+ " "
					+ "AND    work.vc_performer=?"
					+ " "
					+ "AND    work.dat_acceptTime IS NULL"
					+ " "
					+ "AND    work.vc_caseid = casex.vc_uuid "
					+ " "
					+ "AND    casex.int_state = ? ";

			pstmt = conn.prepareStatement(strSQL);
			pstmt.setString(1, packageId);
			pstmt.setString(2, processId);
			pstmt.setString(3, valueOf(workflow.getDesktop().getSession().getParticipant()));
			pstmt.setInt(4, WorkflowCase.RUNNING);

			jrs = pstmt.executeQuery();
			makeWorkList(workflow, userTransaction, workflowPackage, workflowProcess, jrs, null, workList);
			JDBCUtilities.cleanupNoThrow(jrs);
		}
		catch (SQLException sqlex)
		{
			WorkflowException ne = new WorkflowException("can not get workflow work list");
			ne.setRootCause(sqlex);
			throw ne;
		}
		finally
		{
			JDBCUtilities.cleanupNoThrow(conn, pstmt);
		}
		return workList;
	}

	public static List getPendingTray(WorkflowImplementation workflow) throws WorkflowException, RemoteException
	{
		WorkflowPackage workflowPackage = workflow.getWorkflowPackage();
		WorkflowProcess workflowProcess = workflowPackage.getMainWorkflowProcess();

		CaseDatabase caseDatabase = workflow.getCaseDatabase();
		WorkflowTransaction userTransaction = caseDatabase.getWorkflowTransaction();
		Connection conn = userTransaction.getConnection();

		ArrayList workList = new ArrayList();
		WorkflowWorkImpl workflowWork = null;

		PreparedStatement pstmt = null;
		ResultSet jrs = null;

		try
		{
			SchemaContext schemaContext = caseDatabase.getSchemaContext();
			String packageId = workflowPackage.getUUID();
			String processId = workflowProcess.getUUID();

			String strSQL =
				"SELECT work.* FROM "
					+ schemaContext.getTableName(SchemaContext.SCHEMA_WORK)
					+ " work,"
					+ schemaContext.getTableName(SchemaContext.SCHEMA_CASE)
					+ " casex"
					+ " "
					+ "WHERE  work.vc_packageId=?"
					+ " "
					+ "AND    work.vc_processid=?"
					+ " "
					+ "AND    work.vc_performer=?"
					+ " "
					+ "AND    work.dat_acceptTime IS NOT NULL"
					+ " "
					+ "AND    work.dat_dispatchTime IS NULL"
					+ " "
					+ "AND    work.vc_caseid = casex.vc_uuid "
					+ " "
					+ "AND    casex.int_state = ? "
					+ " "
					+ "ORDER BY work.dat_acceptTime";

			pstmt = conn.prepareStatement(strSQL);
			pstmt.setString(1, packageId);
			pstmt.setString(2, processId);
			pstmt.setString(3, valueOf(workflow.getDesktop().getSession().getParticipant()));
			pstmt.setInt(4, WorkflowCase.RUNNING);

			jrs = pstmt.executeQuery();
			makeWorkList(workflow, userTransaction, workflowPackage, workflowProcess, jrs, null, workList);
			JDBCUtilities.cleanupNoThrow(jrs);
		}
		catch (SQLException sqlex)
		{
			WorkflowException ne = new WorkflowException("can not get workflow work list");
			ne.setRootCause(sqlex);
			throw ne;
		}
		finally
		{
			JDBCUtilities.cleanupNoThrow(conn, pstmt);
		}
		return workList;
	}

	public static List getOutgoingTray(WorkflowImplementation workflow) throws WorkflowException, RemoteException
	{
		WorkflowPackage workflowPackage = workflow.getWorkflowPackage();
		WorkflowProcess workflowProcess = workflowPackage.getMainWorkflowProcess();

		CaseDatabase caseDatabase = workflow.getCaseDatabase();
		WorkflowTransaction userTransaction = caseDatabase.getWorkflowTransaction();
		Connection conn = userTransaction.getConnection();

		ArrayList workList = new ArrayList();
		WorkflowWorkImpl workflowWork = null;

		PreparedStatement pstmt = null;
		ResultSet jrs = null;

		try
		{
			SchemaContext schemaContext = caseDatabase.getSchemaContext();
			String packageId = workflowPackage.getUUID();
			String processId = workflowProcess.getUUID();

			String strSQL_FROM_WORK =
				"SELECT workx.* FROM "
					+ schemaContext.getTableName(SchemaContext.SCHEMA_WORK)
					+ " workx,"
					+ schemaContext.getTableName(SchemaContext.SCHEMA_CASE)
					+ " casex,"
					+ schemaContext.getTableName(SchemaContext.SCHEMA_PATH)
					+ " pathx,"
					+ schemaContext.getTableName(SchemaContext.SCHEMA_WORK)
					+ " workx2"
					+ " "
					+ "WHERE workx.dat_dispatchTime IS NOT NULL"
					+ " "
					+ "AND   workx.vc_performer = ? "
					+ " "
					+ "AND   workx.vc_caseid = casex.vc_uuid "
					+ " "
					+ "AND   casex.int_state = ?"
					+ " "
					+ "AND   casex.vc_packageid = ?"
					+ " "
					+ "AND   casex.vc_processid = ?"
					+ " "
					+ "AND   pathx.vc_from = workx.vc_uuid "
					+ " "
					+ "AND   pathx.vc_to = workx2.vc_uuid"
					+ " "
					+ "AND   workx2.dat_acceptTime IS NULL";

			pstmt = conn.prepareStatement(strSQL_FROM_WORK);
			pstmt.setString(1, valueOf(workflow.getDesktop().getSession().getParticipant()));
			pstmt.setInt(2, WorkflowCase.RUNNING);
			pstmt.setString(3, packageId);
			pstmt.setString(4, processId);
			
			jrs = pstmt.executeQuery();
			makeWorkList(workflow, userTransaction, workflowPackage, workflowProcess, jrs, null, workList);
			JDBCUtilities.cleanupNoThrow(jrs);
		}
		catch (SQLException sqlex)
		{
			WorkflowException ne = new WorkflowException("can not get workflow work list");
			ne.setRootCause(sqlex);
			throw ne;
		}
		finally
		{
			JDBCUtilities.cleanupNoThrow(conn, pstmt);
		}
		return workList;
	}

	public static List getAlertIncomingTray(WorkflowImplementation workflow) throws WorkflowException, RemoteException
	{
		WorkflowPackage workflowPackage = workflow.getWorkflowPackage();
		WorkflowProcess workflowProcess = workflowPackage.getMainWorkflowProcess();

		CaseDatabase caseDatabase = workflow.getCaseDatabase();
		WorkflowTransaction userTransaction = caseDatabase.getWorkflowTransaction();
		Connection conn = userTransaction.getConnection();

		ArrayList workList = new ArrayList();
		WorkflowWorkImpl workflowWork = null;

		PreparedStatement pstmt = null;
		ResultSet jrs = null;

		try
		{
			SchemaContext schemaContext = caseDatabase.getSchemaContext();
			String packageId = workflowPackage.getUUID();
			String processId = workflowProcess.getUUID();

			String strSQL =
				"SELECT work.* FROM "
					+ schemaContext.getTableName(SchemaContext.SCHEMA_WORK)
					+ " work,"
					+ schemaContext.getTableName(SchemaContext.SCHEMA_CASE)
					+ " casex"
					+ " "
					+ "WHERE  work.vc_packageId=?"
					+ " "
					+ "AND    work.vc_processid=?"
					+ " "
					+ "AND    work.vc_performer=?"
					+ " "
					+ "AND    work.dat_acceptTime IS NULL"
					+ " "
					+ "AND    work.vc_caseid = casex.vc_uuid "
					+ " "
					+ "AND    casex.int_state = ? "
					+ " "
					+ "ORDER BY work.dat_arriveTime";

			pstmt = conn.prepareStatement(strSQL);
			pstmt.setString(1, packageId);
			pstmt.setString(2, processId);
			pstmt.setString(3, valueOf(workflow.getDesktop().getSession().getParticipant()));
			pstmt.setInt(4, WorkflowCase.RUNNING);

			jrs = pstmt.executeQuery();

			makeWorkList(workflow, userTransaction, workflowPackage, workflowProcess, jrs, "dat_arriveTime", workList);
		}
		catch (SQLException sqlex)
		{
			WorkflowException ne = new WorkflowException("can not get workflow work list");
			ne.setRootCause(sqlex);
			throw ne;
		}
		finally
		{
			JDBCUtilities.cleanupNoThrow(conn, pstmt);
		}
		return workList;
	}

	public static List getAlertPendingTray(WorkflowImplementation workflow) throws WorkflowException, RemoteException
	{
		WorkflowPackage workflowPackage = workflow.getWorkflowPackage();
		WorkflowProcess workflowProcess = workflowPackage.getMainWorkflowProcess();

		CaseDatabase caseDatabase = workflow.getCaseDatabase();
		WorkflowTransaction userTransaction = caseDatabase.getWorkflowTransaction();
		Connection conn = userTransaction.getConnection();

		ArrayList workList = new ArrayList();
		WorkflowWorkImpl workflowWork = null;

		PreparedStatement pstmt = null;
		ResultSet jrs = null;

		try
		{
			SchemaContext schemaContext = caseDatabase.getSchemaContext();
			String packageId = workflowPackage.getUUID();
			String processId = workflowProcess.getUUID();

			String strSQL =
				"SELECT work.* FROM "
					+ schemaContext.getTableName(SchemaContext.SCHEMA_WORK)
					+ " work,"
					+ schemaContext.getTableName(SchemaContext.SCHEMA_CASE)
					+ " casex"
					+ " "
					+ "WHERE  work.vc_packageId=?"
					+ " "
					+ "AND    work.vc_processid=?"
					+ " "
					+ "AND    work.vc_performer=?"
					+ " "
					+ "AND    work.dat_acceptTime IS NOT NULL"
					+ " "
					+ "AND    work.dat_dispatchTime IS  NULL"
					+ " "
					+ "AND    work.vc_caseid = casex.vc_uuid "
					+ " "
					+ "AND    casex.int_state = ? "
					+ " "
					+ "ORDER BY work.dat_acceptTime";

			pstmt = conn.prepareStatement(strSQL);
			pstmt.setString(1, packageId);
			pstmt.setString(2, processId);
			pstmt.setString(3, valueOf(workflow.getDesktop().getSession().getParticipant()));
			pstmt.setInt(4, WorkflowCase.RUNNING);

			jrs = pstmt.executeQuery();
			makeWorkList(workflow, userTransaction, workflowPackage, workflowProcess, jrs, "dat_acceptTime", workList);
			JDBCUtilities.cleanupNoThrow(jrs);
		}
		catch (SQLException sqlex)
		{
			WorkflowException ne = new WorkflowException("can not get workflow work list");
			ne.setRootCause(sqlex);
			throw ne;
		}
		finally
		{
			JDBCUtilities.cleanupNoThrow(conn, pstmt);
		}
		return workList;
	}

	//-----------------------------------------------------------------
	//
	//----------------------------------------------------------------- 
	protected static String valueOf(Object obj)
	{
		return (obj == null) ? "" : obj.toString();
	}

	protected static String valueOf(WorkflowParticipant participant)
	{
		if (participant == null)
		{
			return null;
		}

		return participant.getUUID();
	}

	protected static int checkTimeout(
		WorkflowProcess check_process,
		WorkflowActivity check_activity,
		WorkflowCase check_case,
		Date check_point)
	{
		Date case_creation = check_case.getCreationTime();
		WorkflowPackage check_package = check_process.getWorkflowPackage();

		while (check_process != null)
		{
			Duration process_duration = check_process.getProcessHeader().getLimit();
			long creation_interval = check_point.getTime() - case_creation.getTime();
			if (process_duration != null)
			{
				if (creation_interval > process_duration.getDurationInMilliseconds(DurationUnit.DAY))
				{
					return WORKFLOW_PROCESS_TIMEOUT;
				}
			}
			ExtendedAttribute parent_process_id_attr = check_process.findExtendedAttribute(XPDLGlobals.ID_PROCESS_PARENT);
			check_process = null;
			if (parent_process_id_attr != null)
			{
				String parent_process_id = parent_process_id_attr.getValue();

				if (parent_process_id != null && parent_process_id.length() > 0)
				{
					check_process = check_package.findWorkflowProcess(parent_process_id);
				}
			}
		}

		Duration activity_duration = check_activity.getDuration();
		Date now_date = new Date();
		long check_interval = now_date.getTime() - check_point.getTime();
		if (activity_duration != null)
		{
			if (check_interval > activity_duration.getDurationInMilliseconds(DurationUnit.DAY))
			{
				return WORKFLOW_ACTIVITY_TIMEOUT;
			}
		}

		return WORKFLOW_NEVER_TIMEOUT;
	}

	protected static WorkflowWork makeWork(
		WorkflowImplementation workflow,
		WorkflowTransaction userTransaction,
		WorkflowPackage workflowPackage,
		WorkflowProcess workflowProcess,
		ResultSet jrs,
		String check_column)
		throws SQLException, WorkflowException, RemoteException
	{
		WorkflowWorkImpl workImpl = null;

		String parent_workId = null;
		String vc_performer = null;
		String vc_activityId = null;
		String vc_owner_processId = null;
		String vc_caseId = null;
		int int_weight_flag = 0;

		WorkflowProcess ownerProcess = null;
		WorkflowActivity ownerActivity = null;
		WorkflowCase ownerCase = null;

		if (jrs.next())
		{
			parent_workId = jrs.getString("vc_parent_workId");
			vc_performer = jrs.getString("vc_performer");
			vc_activityId = jrs.getString("vc_activityId");
			vc_owner_processId = jrs.getString("vc_owner_processId");
			vc_caseId = jrs.getString("vc_caseId");
			int_weight_flag = jrs.getInt("int_weight_flag");

			ownerProcess = workflowPackage.findWorkflowProcess(vc_owner_processId);
			ownerActivity = ownerProcess.findActivity(vc_activityId);
			ownerCase = workflow.getCaseDatabase().findCase(userTransaction, workflowProcess, vc_caseId);

			if (check_column != null)
			{
				Date checkTime = DateTools.toJavaDate(jrs.getTimestamp(check_column));

				if (checkTimeout(ownerProcess, ownerActivity, ownerCase, checkTime) == 0)
				{
					return workImpl;
				}
			}

			if (int_weight_flag != 0)
			{
				WeightableWorkImpl wworkImpl = new WeightableWorkImpl();
				wworkImpl.setWeight(jrs.getDouble("dbl_weight"));
				workImpl = wworkImpl;
			}
			else
			{
				workImpl = new WorkflowWorkImpl();
			}

			if (parent_workId != null)
			{
				workImpl.setParentWork(
					workflow.getCaseDatabase().findWork(userTransaction, workflowProcess, ownerCase, parent_workId));
			}

			workImpl.setCase(ownerCase);
			workImpl.setUUID(jrs.getString("vc_uuid"));

			workImpl.setActivity(ownerActivity);
			workImpl.setArriveTime(jrs.getTimestamp("dat_arriveTime"));
			workImpl.setAcceptTime(DateTools.toJavaDate(jrs.getTimestamp("dat_acceptTime")));
			workImpl.setDispatchTime(DateTools.toJavaDate(jrs.getTimestamp("dat_dispatchTime")));

			workImpl.setPerformer(workflow.getUserDatabase().findParticipant(vc_performer));

			workImpl.setUserObject(jrs.getString("vc_userobject"));
			workImpl.setEventType(jrs.getInt("int_eventType"));
			workImpl.setPriority(jrs.getInt("int_priority"));
		}

		return workImpl;
	}

	protected static void makeWorkList(
		WorkflowImplementation workflow,
		WorkflowTransaction userTransaction,
		WorkflowPackage workflowPackage,
		WorkflowProcess workflowProcess,
		ResultSet jrs,
		String check_column,
		List workList)
		throws SQLException, WorkflowException, RemoteException
	{

		WorkflowWorkImpl workImpl = null;

		String parent_workId = null;
		String vc_performer = null;
		String vc_activityId = null;
		String vc_owner_processId = null;
		String vc_caseId = null;
		int int_weight_flag = 0;

		WorkflowProcess ownerProcess = null;
		WorkflowActivity ownerActivity = null;
		WorkflowCase ownerCase = null;

		while (jrs.next())
		{
			parent_workId = jrs.getString("vc_parent_workId");
			vc_performer = jrs.getString("vc_performer");
			vc_activityId = jrs.getString("vc_activityId");
			vc_owner_processId = jrs.getString("vc_owner_processId");
			vc_caseId = jrs.getString("vc_caseId");
			int_weight_flag = jrs.getInt("int_weight_flag");

			ownerProcess = workflowPackage.findWorkflowProcess(vc_owner_processId);

			ownerActivity = ownerProcess.findActivity(vc_activityId);
			ownerCase = workflow.getCaseDatabase().findCase(userTransaction, workflowProcess, vc_caseId);
      
			if (check_column != null)
			{
				Date checkTime = DateTools.toJavaDate(jrs.getTimestamp(check_column));

				if (checkTimeout(ownerProcess, ownerActivity, ownerCase, checkTime) == 0)
				{
					continue;
				}
			}

			if (int_weight_flag != 0)
			{
				WeightableWorkImpl wworkImpl = new WeightableWorkImpl();
				wworkImpl.setWeight(jrs.getDouble("dbl_weight"));
				workImpl = wworkImpl;
			}
			else
			{
				workImpl = new WorkflowWorkImpl();
			}

			if (parent_workId != null)
			{
				workImpl.setParentWork(
					workflow.getCaseDatabase().findWork(userTransaction, workflowProcess, ownerCase, parent_workId));
			}

			workImpl.setCase(ownerCase);
			workImpl.setUUID(jrs.getString("vc_uuid"));

			workImpl.setActivity(ownerActivity);
			workImpl.setArriveTime(jrs.getTimestamp("dat_arriveTime"));
			workImpl.setAcceptTime(DateTools.toJavaDate(jrs.getTimestamp("dat_acceptTime")));
			workImpl.setDispatchTime(DateTools.toJavaDate(jrs.getTimestamp("dat_dispatchTime")));

			workImpl.setPerformer(workflow.getUserDatabase().findParticipant(vc_performer));

			workImpl.setUserObject(jrs.getString("vc_userobject"));
			workImpl.setEventType(jrs.getInt("int_eventType"));
			workImpl.setPriority(jrs.getInt("int_priority"));

			workList.add(workImpl);
		}
	}
}
