using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Xml;
using Neo.Afx.ComponentModel;
using Neo.Afx.Services.Forms;
using Neo.Afx.Services.Workflows.Entities;
using Neo.Afx.ViewModels;

namespace Neo.Afx.Services.Workflows
{
    /// <summary>
    /// Workflow database context container
    /// </summary>
    public class WorkflowsDatabase : Database<WorkflowContext>
    {
        /// <summary>
        /// Initializes a new instance of the <see cref="WorkflowsDatabase"/> class.
        /// </summary>
        public WorkflowsDatabase()
            : base(new WorkflowContext(), 120) //set the command timeout to 2 minutes
        {            
        }

        #region GetActiveWorkflowTemplates
        /// <summary>
        /// Gets the active workflow templates.
        /// </summary>
        /// <returns></returns>
        public List<Vw_WorkflowTemplate> GetActiveWorkflowTemplates()
        {
            return (from wt in Context.Vw_WorkflowTemplates
                    where wt.WorkflowTemplateStatusID == 1 //change to enum. 1=Active
                    select wt).ToList();
        }

        /// <summary>
        /// Gets the active workflow templates.
        /// </summary>
        /// <param name="workflowId">The workflow ID.</param>
        /// <returns></returns>
        public List<Vw_WorkflowTemplate> GetActiveWorkflowTemplates(long workflowId)
        {
            return (from wt in Context.Vw_WorkflowTemplates
                    where wt.WorkflowTemplateStatusID == 1 //change to enum. 1=Active
                        && wt.WorkflowID == workflowId
                    select wt).ToList();
        }
        #endregion

        #region GetWorkflowInstances
        /// <summary>
        /// Gets the workflow instances.
        /// </summary>        
        /// <returns></returns>
        public List<Vw_WorkflowInstance> GetWorkflowInstances(long workflowID, string workflowSteps, string workflowStatusList, long userID)
        {
            return Context.Database.SqlQuery<Vw_WorkflowInstance>(
                "EXEC Wf.Pr_WorkflowInstanceSearch @WorkflowID, @WorkflowSteps, @WorkflowInstanceStatus, @UserID",
                new SqlParameter("WorkflowID", workflowID),
                new SqlParameter("WorkflowSteps", workflowSteps),
                new SqlParameter("WorkflowInstanceStatus", workflowStatusList),
                new SqlParameter("UserID", userID)).ToList();
        }


        /// <summary>
        /// Returns all workflows for the specified entity and item
        /// </summary>
        /// <param name="entityID"></param>
        /// <param name="itemID"></param>
        /// <returns></returns>
        public List<Vw_WorkflowInstance> GetWorkflowInstances(long entityID, long itemID)
        {
            return Context.Vw_WorkflowInstances.Where(a => a.EntityID == entityID && a.ItemID == itemID).ToList();
        }
        #endregion

        #region GetTasks
        /// <summary>
        /// Gets the tasks
        /// </summary>
        /// <param name="assignedToUserID">The assigned to user ID.</param>
        /// <param name="workflowInstanceTaskStatusID">The workflow instance task status ID.</param>
        /// <param name="workflowID">The workflow ID.</param>
        /// <returns></returns>
        public List<Vw_WorkflowInstanceTask> GetTasks(long workflowTemplateStepID, WorkflowInstanceTaskStatusEnum workflowInstanceTaskStatusID)
        {
            return (from t in Context.Vw_WorkflowInstanceTasks
                    where t.WorkflowInstanceTaskStatusID == (short)workflowInstanceTaskStatusID
                        && t.WorkflowTemplateStepID == workflowTemplateStepID
                    select t).ToList();
        }
        
        /// <summary>
        /// Gets the tasks.
        /// </summary>
        /// <param name="assignedToUserID">The assigned to user ID.</param>
        /// <param name="workflowInstanceTaskStatusID">The workflow instance task status ID.</param>
        /// <returns></returns>
        public List<Vw_WorkflowInstanceTask> GetTasks(long assignedToUserID, short workflowInstanceTaskStatusID)
        {
            return (from t in Context.Vw_WorkflowInstanceTasks
                    where t.AssignedToID == assignedToUserID
                        && t.WorkflowInstanceTaskStatusID == workflowInstanceTaskStatusID
                    select t).ToList();
        }

        /// <summary>
        /// Gets the tasks
        /// </summary>
        /// <param name="assignedToUserID">The assigned to user ID.</param>
        /// <param name="workflowInstanceTaskStatusID">The workflow instance task status ID.</param>
        /// <param name="workflowID">The workflow ID.</param>
        /// <returns></returns>
        public List<Vw_WorkflowInstanceTask> GetTasks(long assignedToUserID, short workflowInstanceTaskStatusID, long workflowID)
        {
            return (from t in Context.Vw_WorkflowInstanceTasks
                    where t.AssignedToID == assignedToUserID
                        && t.WorkflowInstanceTaskStatusID == workflowInstanceTaskStatusID
                        && t.WorkflowID == workflowID
                    select t).ToList();
        }

        /// <summary>
        /// Gets the tasks
        /// </summary>
        /// <param name="workflowInstanceID">The workflow Instance ID.</param>
        /// <returns></returns>
        public List<Vw_WorkflowInstanceTask> GetTasks(long workflowInstanceID)
        {
            return (from t in Context.Vw_WorkflowInstanceTasks
                    where t.WorkflowInstanceID == workflowInstanceID
                    select t).ToList();
        }

        /// <summary>
        /// Gets the tasks
        /// </summary>
        /// <param name="entityID">The entity ID.</param>
        /// <param name="itemID">The item ID.</param> 
        /// <returns></returns>
        public List<Vw_WorkflowInstanceTask> GetTasks(long entityID, long itemID)
        {
            return (from t in Context.Vw_WorkflowInstanceTasks
                    where t.WorkflowInstanceEntityID == entityID
                        && t.WorkflowInstanceItemID == itemID
                    select t).ToList();
        }
       
        #endregion

        #region GetWorkflow
        /// <summary>
        /// Gets the workflow.
        /// </summary>
        /// <param name="workflowID">The workflow ID.</param>
        /// <param name="userID">The user ID.</param>
        /// <returns></returns>
        public Workflow GetWorkflow(long workflowID, long userID)
        {
            return (from w in Context.Workflows.Include("WorkflowEntity")
                    where w.WorkflowID == workflowID
                    select w).FirstOrDefault();
        }
        #endregion

        #region GetWorkflowTemplate
        /// <summary>
        /// Gets the workflow template.
        /// </summary>
        /// <param name="workflowTemplateID">The workflow template ID.</param>
        /// <param name="userID">The user ID.</param>
        /// <returns></returns>
        public WorkflowTemplate GetWorkflowTemplate(long workflowTemplateID, long userID)
        {
            return (from wt in Context.WorkflowTemplates.Include("WorkflowTemplateDefaults").Include("WorkflowTemplateSteps").Include("WorkflowTemplateParticipants").Include("WorkflowTemplateParticipants.WorkflowTemplateParticipantRoles")
                    where wt.WorkflowTemplateID == workflowTemplateID
                    select wt).FirstOrDefault();
        }
        #endregion

        #region GetActiveWorkflowTemplate
        /// <summary>
        /// Gets the active workflow template.
        /// </summary>
        /// <param name="workflowID">The workflow ID.</param>
        /// <param name="userID">The user ID.</param>
        /// <returns></returns>
        public WorkflowTemplate GetActiveWorkflowTemplate(long workflowID, long userID)
        {
            var obj = (from wt in Context.WorkflowTemplates.Include("WorkflowTemplateDefaults").Include("WorkflowTemplateSteps").Include("WorkflowTemplateParticipants").Include("WorkflowTemplateParticipants.WorkflowTemplateParticipantRoles")
                       where wt.WorkflowID == workflowID
                          && wt.WorkflowTemplateStatusID == 1 //active
                       select wt).FirstOrDefault();

            obj.WorkflowTemplateDefaults = obj.WorkflowTemplateDefaults.ToList();
            obj.WorkflowTemplateSteps = obj.WorkflowTemplateSteps.ToList();
            obj.WorkflowTemplateParticipants = obj.WorkflowTemplateParticipants.ToList();

            return obj;
        }
        #endregion

        #region GetWorkflowInstance
        /// <summary>
        /// Gets the workflow instance.
        /// </summary>
        /// <param name="workflowInstanceID">The workflow instance ID.</param>
        /// <param name="userID">The user ID.</param>
        /// <returns></returns>
        public WorkflowInstance GetWorkflowInstance(long workflowInstanceID, long userID)
        {
            if(workflowInstanceID == -1)
            {
                var workflowTemplateStep = new WorkflowTemplateStep()
                {
                    Step = 1,
                    WorkflowTemplateStepTypeID = 2
                };

                var tasks = new List<WorkflowInstanceTask>();
                tasks.Add(new WorkflowInstanceTask()
                {
                    CreatedDate = DateTime.Now,
                    AssignedToID = userID,
                    CreatedByID = userID,
                    WorkflowTemplateStepID = 1,
                    WorkflowInstanceTaskStatusID = 1,
                    WorkflowTemplateStep = workflowTemplateStep,
                    AssignedTo = new WorkflowUser()
                    {
                        KnownAs = ""
                    },
                    WorkflowInstanceTaskStatus = new WorkflowInstanceTaskStatus()
                    {
                        Description = ""
                    },
                    CompletedBy = new WorkflowUser()
                    {
                        KnownAs = ""
                    }
                });

                return new WorkflowInstance()
                {
                    CreatedByID = userID,
                    WorkflowTemplateID = 1,
                    WorkflowTemplateStepID = 1,
                    WorkflowTemplateStep = workflowTemplateStep,
                    WorkflowInstanceTasks = tasks,
                    WorkflowInstanceStatus = new WorkflowInstanceStatus()
                    {
                        WorkflowInstanceStatusID = 1,
                        Description = "New Request"
                    }
                };
            }

            var results = (from wi in Context.WorkflowInstances.Include("WorkflowInstanceStatus").Include("WorkflowTemplateStep").Include("WorkflowInstanceTasks").Include("WorkflowInstanceTasks.AssignedTo").Include("WorkflowInstanceTasks.CompletedBy").Include("WorkflowInstanceTasks.WorkflowInstanceTaskStatus").Include("WorkflowInstanceTasks.WorkflowTemplateStep").Include("WorkflowInstanceParticipants").Include("WorkflowInstanceParticipants.WorkflowTemplateParticipant").Include("WorkflowInstanceParticipants.WorkflowTemplateParticipant.WorkflowTemplateParticipantRoles")
                           where wi.WorkflowInstanceID == workflowInstanceID
                           select wi).FirstOrDefault();

            results.WorkflowInstanceTasks = results.WorkflowInstanceTasks.OrderBy(a => a.CreatedDate).ToList();

            return results;
        }
        #endregion

        #region GetWorkflowInstance
        /// <summary>
        /// Gets the workflow instance based on the entity and itemid.
        /// </summary>
        /// <returns></returns>
        public WorkflowInstance GetWorkflowInstance(int entityID, long itemID)
        {
            var instance = (from wi in Context.WorkflowInstances.Include("WorkflowInstanceStatus").Include("WorkflowTemplateStep").Include("WorkflowInstanceTasks").Include("WorkflowInstanceTasks.AssignedTo").Include("WorkflowInstanceTasks.CompletedBy").Include("WorkflowInstanceTasks.WorkflowInstanceTaskStatus").Include("WorkflowInstanceTasks.WorkflowTemplateStep").Include("WorkflowInstanceParticipants").Include("WorkflowInstanceParticipants.WorkflowTemplateParticipant").Include("WorkflowInstanceParticipants.WorkflowTemplateParticipant.WorkflowTemplateParticipantRoles")
                    where wi.EntityID == entityID && wi.ItemID == itemID
                    select wi).FirstOrDefault();

            instance.WorkflowInstanceTasks = instance.WorkflowInstanceTasks.OrderBy(a => a.CreatedDate).ToList();

            return instance;
        }
        #endregion

        #region GetWorkflowInstanceView
        /// <summary>
        /// Gets the workflow instance view based on the entity and itemid.
        /// </summary>
        /// <returns></returns>
        public Vw_WorkflowInstance GetWorkflowInstanceView(long entityID, long itemID)
        {
            return (from wi in Context.Vw_WorkflowInstances
                    where wi.EntityID == entityID && wi.ItemID == itemID
                    select wi).FirstOrDefault();
        }
        #endregion

        #region GetWorkflowInstanceRoute
        /// <summary>
        /// Gets the workflow instance route.
        /// </summary>
        /// <param name="workflowTemplateID">The workflow template ID.</param>
        /// <param name="workflowInstanceID">The workflow instance ID.</param>
        /// <param name="userID">The user ID.</param>
        /// <returns></returns>
        public WorkflowInstanceRoute GetWorkflowInstanceRoute(long workflowTemplateID, long workflowInstanceID, long userID)
        {
            var xmlDoc = new XmlDocument();
            xmlDoc.LoadXml(Context.Database.SqlQuery<string>("EXEC Wf.Pr_WorkflowInstanceRouteGet @workflowTemplateID, @workflowInstanceID, @userID", new SqlParameter("WorkflowTemplateID", workflowTemplateID), new SqlParameter("WorkflowInstanceID", workflowInstanceID), new SqlParameter("UserID", userID)).FirstOrDefault());

            return new WorkflowInstanceRoute(xmlDoc);
        }
        #endregion

        #region SubmitWorkflowTaskAction
        /// <summary>
        /// Submits the workflow task action.
        /// </summary>
        /// <param name="workflowInstanceTaskID">The workflow instance task ID.</param>
        /// <param name="workflowInstanceTaskStatusID">The workflow instance task status ID.</param>
        /// <param name="completedByUserName">Name of the completed by user.</param>
        /// <param name="requestCorrectionWorkflowTemplateStepID">The request correction workflow template step ID.</param>
        /// <param name="comments">The comments.</param>
        /// <returns></returns>
        public string SubmitWorkflowTaskAction(long workflowInstanceTaskID, int workflowInstanceTaskStatusID, string completedByUserName, long requestCorrectionWorkflowTemplateStepID, string comments)
        {
            string errorMessage = "";
            var errorParam = new SqlParameter("@ErrorMessage", SqlDbType.VarChar, 1000)
            {
                Direction = ParameterDirection.Output
            };

            Context.Database.ExecuteSqlCommand(
                "EXEC Wf.Pr_WorkflowInstanceTaskAction @WorkflowInstanceTaskID, @WorkflowInstanceTaskStatusID, @CompletedByUserName, @RequestCorrectionWorkflowTemplateStepID, @Comments, @ErrorMessage OUTPUT",
                new SqlParameter("WorkflowInstanceTaskID", workflowInstanceTaskID),
                new SqlParameter("WorkflowInstanceTaskStatusID", workflowInstanceTaskStatusID),
                new SqlParameter("CompletedByUserName", completedByUserName),
                new SqlParameter("RequestCorrectionWorkflowTemplateStepID", requestCorrectionWorkflowTemplateStepID),
                new SqlParameter("Comments", comments),
                errorParam);

            if(!string.IsNullOrEmpty(errorParam.Value.ToStringSafe()))
            {
                errorMessage = CleanError(errorParam.Value.ToString());
            }

            return errorMessage;
        }
        #endregion

        #region SubmitWorkflowInstanceErrorAction
        /// <summary>
        /// Submits the workflow instance error action.
        /// </summary>
        /// <param name="workflowInstanceID">The workflow instance ID.</param>
        /// <param name="errorActionId">The error action id. (1=Retry, 2=Override, 3=Request Correction)</param>
        /// <param name="completedByUserName">Name of the completed by user.</param>
        /// <param name="requestCorrectionWorkflowTemplateStepID">The request correction workflow template step ID.</param>
        /// <param name="comments">The comments.</param>
        /// <returns></returns>
        public string SubmitWorkflowInstanceErrorAction(long workflowInstanceID, int errorActionId, string completedByUserName, long requestCorrectionWorkflowTemplateStepID, string comments)
        {
            var errorMessage = "";
            var errorParam = new SqlParameter("@ErrorMessage", SqlDbType.VarChar, 1000)
            {
                Direction = ParameterDirection.Output
            };

            Context.Database.ExecuteSqlCommand(
                "EXEC Wf.Pr_WorkflowInstanceErrorAction @WorkflowInstanceID, @WorkflowInstanceErrorActionID, @CompletedByUserName, @RequestCorrectionWorkflowTemplateStepID, @Comments, @ErrorMessage OUTPUT",
                new SqlParameter("WorkflowInstanceID", workflowInstanceID),
                new SqlParameter("WorkflowInstanceErrorActionID", errorActionId),
                new SqlParameter("CompletedByUserName", completedByUserName),
                new SqlParameter("RequestCorrectionWorkflowTemplateStepID", requestCorrectionWorkflowTemplateStepID),
                new SqlParameter("Comments", comments),
                errorParam);

            if(!string.IsNullOrEmpty(errorParam.Value.ToStringSafe()))
            {
                errorMessage = CleanError(errorParam.Value.ToString());
            }

            return errorMessage;
        }
        #endregion

        #region StartWorkflowInstance
        /// <summary>
        /// Starts the workflow instance.
        /// </summary>
        /// <param name="workflowID">The workflow ID.</param>
        /// <param name="entityID">The entity ID.</param>
        /// <param name="itemID">The item ID.</param>
        /// <param name="workflowParticipants">The workflow participants.</param>
        /// <param name="userName">Name of the user.</param>
        /// <returns></returns>
        public string StartWorkflowInstance(int workflowID, int entityID, long itemID, List<WorkflowInstanceParticipant> workflowParticipants, string referenceNumber, string userName)
        {
            var errorMessage = "";
            var errorParam = new SqlParameter("@ErrorMessage", SqlDbType.VarChar, 1000)
            {
                Direction = ParameterDirection.Output
            };

            var participantList = workflowParticipants.Aggregate("", (current, p) => current + (p.WorkflowTemplateParticipantID + "_" + p.UserID + ','));
            if(participantList.Length > 0)
            {
                participantList = participantList.Substring(0, participantList.Length - 1);
            }

            var returnVal = Context.Database.ExecuteSqlCommand(
                "EXEC Wf.Pr_WorkflowInstanceStart @WorkflowID, @EntityID, @ItemID, @WorkflowParticipants, @UserName, @ReferenceNumber, @ErrorMessage OUTPUT",
                new SqlParameter("WorkflowID", workflowID),
                new SqlParameter("EntityID", entityID),
                new SqlParameter("ItemID", itemID),
                new SqlParameter("WorkflowParticipants", participantList),
                new SqlParameter("ReferenceNumber", referenceNumber),
                new SqlParameter("UserName", userName),
                errorParam);

            if(returnVal <= 0)
            {
                errorMessage = CleanError(errorParam.Value.ToString());
            }

            return errorMessage;
        }
        #endregion

        #region SaveWorkflowInstance
        /// <summary>
        /// Saves the workflow instance.
        /// </summary>
        /// <param name="workflowInstanceID">The workflow instance ID.</param>
        /// <param name="workflowParticipants">The workflow participants.</param>
        /// <param name="userName">Name of the user.</param>
        /// <returns></returns>
        public string SaveWorkflowInstance(long workflowInstanceID, List<WorkflowInstanceParticipant> workflowParticipants, string userName)
        {
            var errorMessage = "";
            var errorParam = new SqlParameter("@ErrorMessage", SqlDbType.VarChar, 1000)
            {
                Direction = ParameterDirection.Output
            };

            var participantList = workflowParticipants.Aggregate("", (current, p) => current + (p.WorkflowTemplateParticipantID + "_" + p.UserID + ','));
            if(participantList.Length > 0)
            {
                participantList = participantList.Substring(0, participantList.Length - 1);
            }

            var returnVal = Context.Database.ExecuteSqlCommand(
                "EXEC Wf.Pr_WorkflowInstanceSave @WorkflowInstanceID, @WorkflowParticipants, @UserName, @ErrorMessage OUTPUT",
                new SqlParameter("WorkflowInstanceID", workflowInstanceID),
                new SqlParameter("WorkflowParticipants", participantList),
                new SqlParameter("UserName", userName),
                errorParam);

            if(returnVal <= 0)
            {
                errorMessage = CleanError(errorParam.Value.ToString());
            }

            return errorMessage;
        }
        #endregion

        #region ReAssignWorkflowInstanceTask
        /// <summary>
        /// Res the assign workflow instance task.
        /// </summary>
        /// <param name="workflowInstanceTaskID">The workflow instance task ID.</param>
        /// <param name="reassignedToID">The reassigned to ID.</param>
        /// <param name="comments">The comments.</param>
        /// <param name="userName">Name of the user.</param>
        /// <returns></returns>
        public string ReAssignWorkflowInstanceTask(long workflowInstanceTaskID, long reassignedToID, string comments, string userName)
        {
            var errorMessage = "";
            var errorParam = new SqlParameter("@ErrorMessage", SqlDbType.VarChar, 1000)
            {
                Direction = ParameterDirection.Output
            };

            var returnVal = 0;

            try
            {
                returnVal = Context.Database.ExecuteSqlCommand(
                "EXEC Wf.Pr_WorkflowInstanceTaskReAssign @WorkflowInstanceTaskID, @ReassignedByUserName, @ReassignedTo, @Comments, @ErrorMessage OUTPUT",
                new SqlParameter("WorkflowInstanceTaskID", workflowInstanceTaskID),
                new SqlParameter("ReassignedByUserName", userName),
                new SqlParameter("ReassignedTo", reassignedToID),
                new SqlParameter("Comments", comments),
                errorParam);
            }
            catch(Exception ex)
            {
                return ex.Message;
            }

            if(returnVal <= 0)
            {
                errorMessage = CleanError(errorParam.Value.ToString());
            }

            return errorMessage;
        }
        #endregion

        #region EscalateWorkflowInstanceTask
        /// <summary>
        /// Escalate a workflow instance task (or return it to the original user when done).
        /// </summary>
        /// <param name="workflowInstanceTaskID"></param>
        /// <param name="escalateToID"></param>
        /// <param name="returnEscalation"></param>
        /// <param name="comments"></param>
        /// <param name="userName"></param>
        /// <returns></returns>
        public string EscalateWorkflowInstanceTask(long workflowInstanceTaskID, long escalateToID, bool returnEscalation, string comments, string userName)
        {
            var errorMessage = "";
            var errorParam = new SqlParameter("@ErrorMessage", SqlDbType.VarChar, 1000)
            {
                Direction = ParameterDirection.Output
            };

            var returnVal = 0;

            try
            {
                returnVal = Context.Database.ExecuteSqlCommand(
                "EXEC Wf.Pr_WorkflowInstanceTaskEscalation @WorkflowInstanceTaskID, @EscalationByUserName, @EscalateTo, @ReturnEscalation, @Comments, @ErrorMessage OUTPUT",
                new SqlParameter("WorkflowInstanceTaskID", workflowInstanceTaskID),
                new SqlParameter("EscalationByUserName", userName),
                new SqlParameter("EscalateTo", escalateToID),
                new SqlParameter("ReturnEscalation", returnEscalation),
                new SqlParameter("Comments", comments),
                errorParam);
            }
            catch (Exception ex)
            {
                return ex.Message;
            }

            if (returnVal <= 0)
            {
                errorMessage = CleanError(errorParam.Value.ToString());
            }

            return errorMessage;
        }
        #endregion

        #region CancelWorkflow
        /// <summary>
        /// Cancel's a workflow 
        /// </summary>
        public string CancelWorkflow(long workflowInstanceID, string completedByUserName, string comments)
        {
            string errorMessage = "";
            var errorParam = new SqlParameter("@ErrorMessage", SqlDbType.VarChar, 1000)
            {
                Direction = ParameterDirection.Output
            };

            var returnVal = Context.Database.ExecuteSqlCommand(
                "EXEC Wf.Pr_WorkflowInstanceCancel @WorkflowInstanceID, @WorkflowCancelledByUserName, @Comments, @ErrorMessage OUTPUT",
                new SqlParameter("WorkflowInstanceID", workflowInstanceID),
                new SqlParameter("WorkflowCancelledByUserName", completedByUserName),
                new SqlParameter("Comments", comments),
                errorParam);

            if(returnVal <= 0)
            {
                errorMessage = CleanError(errorParam.Value.ToString());
            }

            return errorMessage;
        }
        #endregion

        #region CheckWorkflowInstanceSecurity
        /// <summary>
        /// Checks if a user has access to a workflow.
        /// </summary>
        /// <param name="workflowID">The workflow ID.</param>
        /// <param name="itemID">The item ID.</param>
        /// <param name="completedByUserName">Name of the completed by user.</param>
        /// <returns></returns>
        public bool CheckWorkflowInstanceSecurity(long workflowID, long itemID, string completedByUserName)
        {
            var returnVal = Context.Database.SqlQuery<Int16>(
                "EXEC Wf.Pr_WorkflowInstanceCheckSecurity @WorkflowID, @ItemID, @CompletedByUserName",
                new SqlParameter("WorkflowID", workflowID),
                new SqlParameter("ItemID", itemID),
                new SqlParameter("CompletedByUserName", completedByUserName)).FirstOrDefault();

            return (returnVal == 1) ? true : false;
        }
        #endregion

        #region GetWorkflowConsoleViewModel
        public WorkflowConsoleViewModelBase GetWorkflowConsoleViewModel(long workflowid, long workflowInstanceID, long itemID, long userID, IEnumerable<int> roles)
        {
            return GetWorkflowConsoleViewModel(workflowid, workflowInstanceID, itemID, userID, roles, null);
        }

        /// <summary>
        /// Gets a populated console view model.
        /// </summary>
        /// <param name="workflowid">The workflowid.</param>
        /// <param name="workflowInstanceID">The workflow instance ID.</param>
        /// <param name="itemID">The item ID.</param>
        /// <param name="userID">The user ID.</param>
        /// <param name="roles">The roles.</param>
        /// <param name="workflowBuddies">The workflowbuddies.</param>
        /// <returns>WorkflowConsoleViewModelBase with the following pupulated:
        ///  ControllerName
        ///  UserID
        ///  UserRoles
        ///  ItemID
        ///  WorkflowID
        ///  WorkflowFriendlyName
        ///  WorkflowTemplateDefaults
        ///  EntityID
        ///  EntityKey
        ///  WorkflowInstance
        ///  WorkflowInstanceRoute
        ///  FormChecklistResults
        ///  Ui.canReAssignTasks
        /// </returns>
        public WorkflowConsoleViewModelBase GetWorkflowConsoleViewModel(long workflowid, long workflowInstanceID, long itemID, long userID, IEnumerable<int> roles, List<KeyValuePair<long, long>> workflowBuddies)
        {
            var workflowDatabase = new WorkflowsDatabase();
            var formDatabase = new FormsDatabase();
            Workflow workflow;
            WorkflowTemplate workflowTemplate;
            WorkflowInstance workflowInstance;
            ////var checklistResults = new List<Forms.Entities.ChecklistResult>();

            if(workflowInstanceID != -1)
            {
                workflowInstance = workflowDatabase.GetWorkflowInstance(workflowInstanceID, userID);
                workflowTemplate = workflowDatabase.GetWorkflowTemplate(workflowInstance.WorkflowTemplateID, userID);
                workflow = workflowDatabase.GetWorkflow(workflowTemplate.WorkflowID, userID);

                if (workflowBuddies != null)
                { 
                    foreach (var t in workflowInstance.WorkflowInstanceTasks.Where(a => a.WorkflowInstanceTaskStatusID == 1)) //1=Outstanding
                    {
                        foreach (var w in workflowBuddies)
                        {
                            if (workflow.WorkflowID == w.Key && t.AssignedToID == w.Value)
                            {
                                t.canReAssignToSelf = true; //users can re-asisgn tasks to themselves if they have been set up as a buddy of the user that has the current task assigned
                            }
                        }
                    }
                }

                foreach(var wip in workflowInstance.WorkflowInstanceParticipants)
                {
                    wip.WorkflowTemplateParticipantFieldName = wip.WorkflowTemplateParticipant.FieldName;
                    wip.WorkflowTemplateParticipantDisplayName = wip.WorkflowTemplateParticipant.DisplayName;
                    wip.IsVisible = wip.WorkflowTemplateParticipant.IsVisible;
                    wip.WorkflowTemplateParticipantStaticParameters = new List<KeyValuePair<string, string>>()
                    {
                        new KeyValuePair<string, string>("RoleIDs", string.Join(",", wip.WorkflowTemplateParticipant.WorkflowTemplateParticipantRoles.Select(s => s.RoleID.ToString()).ToArray()))
                    };
                }

                ////checklistResults.AddRange(formDatabase.GetChecklistResults(workflow.EntityID, itemID));
            }
            else
            {
                workflow = workflowDatabase.GetWorkflow(workflowid, userID);
                workflowTemplate = workflowDatabase.GetActiveWorkflowTemplate(workflowid, userID);
                workflowInstance = workflowDatabase.GetWorkflowInstance(workflowInstanceID, userID);

                workflowInstance.WorkflowTemplate = workflowTemplate;
                workflowInstance.WorkflowInstanceParticipants = new List<WorkflowInstanceParticipant>();

                foreach(var wtp in workflowTemplate.WorkflowTemplateParticipants.OrderBy(a => a.OrderBy))
                {
                    workflowInstance.WorkflowInstanceParticipants.Add(new WorkflowInstanceParticipant()
                    {
                        WorkflowInstanceID = -1,
                        UserID = wtp.DefaultUserID,
                        WorkflowTemplateParticipantID = wtp.WorkflowTemplateParticipantID,
                        WorkflowTemplateParticipantFieldName = wtp.FieldName,
                        WorkflowTemplateParticipantDisplayName = wtp.DisplayName,
                        IsVisible = wtp.IsVisible,
                        WorkflowTemplateParticipantStaticParameters = new List<KeyValuePair<string, string>>()
                            {
                                new KeyValuePair<string, string>("RoleIDs", string.Join(",", wtp.WorkflowTemplateParticipantRoles.Select(s => s.RoleID.ToString()).ToArray()))                                                                              
                            }
                    });
                }

                ////foreach(var ctq in formDatabase.GetChecklistTemplateQuestionsAll(5, workflowTemplate.WorkflowTemplateID))
                ////{
                ////    checklistResults.Add(new Forms.Entities.ChecklistResult()
                ////    {
                ////        EntityID = workflow.EntityID,
                ////        ItemID = itemID,
                ////        ChecklistTemplateQuestionID = ctq.ChecklistTemplateQuestionID,
                ////        ChecklistTemplateQuestion = ctq
                ////    });
                ////}

                SetDefaultParticipant(workflowInstance.WorkflowInstanceParticipants, "Initiator", userID);
            }

            var workflowInstanceRoute = workflowDatabase.GetWorkflowInstanceRoute(workflowTemplate.WorkflowTemplateID, workflowInstanceID, userID);

            itemID = (itemID != -1) ? itemID : (new Random().Next(2000000, 3000000) * -1);

            ////var isAdmin = false;
            ////foreach(var adminRole in workflow.AdministratorRoles.Split(','))
            ////{
            ////    var roleID = adminRole.ToInt16();
            ////    var matchedRoles = roles.Where(a => a == roleID);

            ////    if(matchedRoles.Count() > 0)
            ////    {
            ////        isAdmin = true;
            ////        break;
            ////    }
            ////}

            return new WorkflowConsoleViewModelBase
            {
                ControllerName = workflow.WorkflowControllerName,
                DashboardControllerName = workflow.DashboardControllerName,
                UserID = userID,
                UserRoles = roles,
                ////UserIsAdmin = isAdmin,
                ItemID = itemID,
                WorkflowID = workflow.WorkflowID,
                WorkflowFriendlyName = workflow.WorkflowFriendlyName,
                WorkflowTemplateDefaults = workflowTemplate.WorkflowTemplateDefaults,
                DocumentStoreLocationID = workflowTemplate.DocumentStoreLocationID,
                EntityID = workflow.EntityID,
                EntityKey = workflow.WorkflowEntity.EntityKey,
                WorkflowInstance = workflowInstance,
                WorkflowInstanceRoute = workflowInstanceRoute
                ////FormChecklistResults = checklistResults,
                ////Ui = new WorkflowConsoleUIProperties()
                ////{
                ////    canReAssignTasks = isAdmin
                ////}
            };
        }
        #endregion

        #region SetDefaultParticipant
        /// <summary>
        /// Set's the userID property in the participants list based on a specified key (WorkflowTemplateParticipantFieldName)
        /// </summary>
        /// <param name="participants"></param>
        /// <param name="key"></param>
        /// <param name="value"></param>
        private static void SetDefaultParticipant(IEnumerable<WorkflowInstanceParticipant> participants, string key, long value)
        {
            foreach(var p in participants.Where(p => p.WorkflowTemplateParticipantFieldName.ToLower() == key.ToLower()))
            {
                p.UserID = value;
            }
        }
        #endregion

        #region CanActionWorkflowInstanceTask
        /// <summary>
        /// Determines whether this instance [can action workflow instance task] the specified workflow instance task ID.
        /// </summary>
        /// <param name="workflowInstanceTaskID">The workflow instance task ID.</param>
        /// <param name="userID">The user ID.</param>
        /// <returns></returns>
        public bool? CanActionWorkflowInstanceTask(long workflowInstanceTaskID, long userID)
        {
            return Context.Pr_WorkflowInstanceTaskCanAction(workflowInstanceTaskID, userID).FirstOrDefault();
        }
        #endregion

        #region GetWorkflowInstanceTaskStepsForCorrection
        public List<Pr_WorkflowInstanceTaskStepsGetForCorrection_Result> GetWorkflowInstanceTaskStepsForCorrection(long workflowInstanceTaskID)
        {
            return Context.Pr_WorkflowInstanceTaskStepsGetForCorrection(workflowInstanceTaskID).ToList();
        }
        #endregion

        #region GetWorkflowInstanceTaskEscalationUsers
        public List<Pr_WorkflowInstanceTaskEscalationUsersGet_Result> GetWorkflowInstanceTaskEscalationUsers(long workflowInstanceTaskID, bool includeUserSettings = false)
        {
            return Context.Pr_WorkflowInstanceTaskEscalationUsersGet(workflowInstanceTaskID, includeUserSettings).ToList();
        }
        #endregion

        #region IsWorkflowBuddy
        /// <summary>
        /// Determines whether this specified user is a buddy of another user
        /// </summary>
        /// <returns></returns>
        public bool IsWorkflowBuddy(long workflowID, long assignedToUserID, long buddyUserID)
        {
            var b = (from t in Context.WorkflowBuddies
                     where t.UserID == assignedToUserID
                         && t.WorkflowID == workflowID
                         && t.BuddyUserID == buddyUserID
                     select t).FirstOrDefault();

            return (b != null);
        }
        #endregion

        #region ReAssignWorkflowInstanceGroupTask
        /// <summary>
        /// Reassigns a group task from the system user to the current user, if the user belongs to the role linked to the step
        /// </summary>
        /// <param name="workflowInstanceID">The workflow instance ID.</param>
        /// <param name="reassignedToID">The reassigned to ID.</param>
        /// <param name="comments">The comments.</param>
        /// <param name="userID">The user id of the user performing the re-assign.</param>
        /// <returns></returns>
        public string ReAssignWorkflowInstanceGroupTask(long workflowInstanceID, long reassignedToID, string comments, long userID)
        {
            var errorMessage = "";
            var errorParam = new SqlParameter("@ErrorMessage", SqlDbType.VarChar, 1000)
            {
                Direction = ParameterDirection.Output
            };

            var returnVal = 0;

            try
            {
                returnVal = Context.Database.ExecuteSqlCommand(
                "EXEC Wf.Pr_WorkflowInstanceTaskReAssignGroupTask @WorkflowInstanceID, @ReassignedToID, @UserID, @Comments, @ErrorMessage OUTPUT",
                new SqlParameter("WorkflowInstanceID", workflowInstanceID),
                new SqlParameter("ReassignedToID", reassignedToID),
                new SqlParameter("UserID", userID),                
                new SqlParameter("Comments", comments),
                errorParam);
            }
            catch (Exception ex)
            {
                return ex.Message;
            }

            if (returnVal <= 0)
            {
                errorMessage = CleanError(errorParam.Value.ToString());
            }

            return errorMessage;
        }
        #endregion

        #region CleanError
        /// <summary>
        /// Cleans the error.
        /// </summary>
        /// <param name="err">The error message.</param>
        /// <returns></returns>
        static string CleanError(string err)
        {
            if(err.Contains("was deadlocked on lock"))
            {
                err = "Error completing action.  Please try again.";
            }

            return err;
        }
        #endregion

        #region GetWorkflowInstanceTaskView
        public Vw_WorkflowInstanceTask GetWorkflowInstanceTaskView(long workflowInstanceTaskID)
        {
            return Context.Vw_WorkflowInstanceTasks.Where(a => a.WorkflowInstanceTaskID == workflowInstanceTaskID).FirstOrDefault();
        }
        #endregion

        #region UpdateWorkflowInstanceDocumentID
        public void UpdateWorkflowInstanceDocumentID(long workflowInstanceDocumentID, long documentID, long userID)
        {
            Context.Pr_WorkflowInstanceDocumentIdUpdate(workflowInstanceDocumentID, documentID, userID);
        }
        #endregion

        #region UpdateWorkflowInstanceDocumentExpiryDate
        public void UpdateWorkflowInstanceDocumentExpiryDate(long workflowInstanceDocumentID, DateTime expiryDate, long userID)
        {
            Context.Pr_WorkflowInstanceDocumentExpiryDateUpdate(workflowInstanceDocumentID, expiryDate, userID);
        }
        #endregion

        #region UpdateWorkflowInstanceDocumentTitle
        public void UpdateWorkflowInstanceDocumentTitle(long workflowInstanceDocumentID, string title, long userID)
        {
            Context.Pr_WorkflowInstanceDocumentTitleUpdate(workflowInstanceDocumentID, title, userID);
        }
        #endregion

        #region GetWorkflowInstanceDocuments
        public List<Vw_WorkflowInstanceDocument> GetWorkflowInstanceDocuments(long workflowInstanceID, long userID)
        {
            return Context.Pr_WorkflowInstanceDocumentsGet(workflowInstanceID, userID).ToList();
        }
        #endregion

        #region GetReferenceNumber
        public string GetReferenceNumber(long entityID, long itemID)
        {
            var wi = Context.WorkflowInstances.Where(a => a.EntityID == entityID && a.ItemID == itemID).FirstOrDefault();

            return (wi != null) ? wi.ReferenceNumber : null;
        }
        #endregion

        #region PerformCanSubmitToNextStepChecks
        public List<SubmitToNextStepOutcome> PerformCanSubmitToNextStepChecks(long workflowInstanceID, long? taskID, long userID)
        {
            return Context.Pr_WorkflowInstanceSubmitToNextStepChecks(workflowInstanceID, taskID, userID).ToList();
        }
        #endregion

        #region ExecutePostFormSave
        public void ExecutePostFormSave(long workflowInstanceID, long userID)
        {
            Context.Pr_WorkflowInstancePostFormSave(workflowInstanceID, userID);
        }
        #endregion

        #region WorkflowInstanceTaskCanAssignToMe
        /// <summary>
        /// Gets the workflow instance based on the entity and itemid.
        /// </summary>
        /// <returns></returns>
        public bool WorkflowInstanceTaskCanAssignToMe(long workflowInstanceTaskID, long userID)
        {
            bool? result = Context.Pr_WorkflowInstanceTaskCanAssignToMe(workflowInstanceTaskID, userID).FirstOrDefault();
            return (result == null) ? false : result.Value;
        }
        #endregion

        #region WorkflowInstanceRetryFailedTask
        /// <summary>
        /// Gets the workflow instance based on the entity and itemid.
        /// </summary>
        /// <returns></returns>
        public void WorkflowInstanceRetryFailedTask(long workflowInstanceID,long workflowInstanceTaskID, string comments, long userID)
        {
            Context.Pr_WorkflowInstanceRetryFailedTask(workflowInstanceID, workflowInstanceTaskID, comments, userID);
        }
        #endregion
    }
}