CREATE OR ALTER PROCEDURE [Wf].[Pr_WorkflowInstanceTaskAction]
(
	@WorkflowInstanceTaskID BIGINT,
	@WorkflowInstanceTaskStatusID SMALLINT,
	@CompletedByUserName VARCHAR(100),
	@RequestCorrectionWorkflowTemplateStepID SMALLINT,
	@Comments VARCHAR(1000),
	@ErrorMessage VARCHAR(1000) OUTPUT
)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @Trancount INT;
	DECLARE @Success BIT
	DECLARE @WorkflowTemplateID BIGINT
	DECLARE @WorkflowInstanceID BIGINT
	DECLARE @WorkflowTemplateStepIDNext BIGINT 
	DECLARE @WorkflowTemplateStepTypeIDNext SMALLINT
	DECLARE @WorkflowTemplateStepIDCurrent BIGINT 
	DECLARE @WorkflowTemplateStepTypeIDCurrent SMALLINT
	DECLARE @StepNextBranch FLOAT
	DECLARE @StepCurrentBranch FLOAT		
	DECLARE @StepDescriptionNext VARCHAR(MAX)
	DECLARE @StepCurrent FLOAT	
	DECLARE @StepNext FLOAT	
	DECLARE @CompletedByID BIGINT
	DECLARE @Sql VARCHAR(MAX)
	
	SET @Trancount = @@trancount;

	BEGIN TRY	
		IF @Trancount = 0
			BEGIN TRANSACTION
		ELSE
			SAVE TRANSACTION Pr_WorkflowInstanceTaskAction

		--Ignore action if set to the same as current status	
		IF NOT EXISTS(SELECT * FROM Wf.WorkflowInstanceTasks WHERE WorkflowInstanceTaskID = @WorkflowInstanceTaskID AND WorkflowInstanceTaskStatusID = @WorkflowInstanceTaskStatusID)
		BEGIN												
			--Get workitem details
			SELECT 
				@WorkflowTemplateID = Wi.WorkflowTemplateID, 
				@WorkflowInstanceID = Wit.WorkflowInstanceID,
				@WorkflowTemplateStepIDCurrent = Wts.WorkflowTemplateStepID,
				@WorkflowTemplateStepTypeIDCurrent = Wts.WorkflowTemplateStepTypeID,
				@StepCurrentBranch = Wts.Branch,
				@StepCurrent = Wts.Step
			FROM Wf.WorkflowInstanceTasks Wit
				INNER JOIN Wf.WorkflowInstances Wi ON Wit.WorkflowInstanceID = Wi.WorkflowInstanceID 
				INNER JOIN Wf.WorkflowTemplateSteps Wts ON Wi.WorkflowTemplateStepID = Wts.WorkflowTemplateStepID
			WHERE WorkflowInstanceTaskID = @WorkflowInstanceTaskID
							
			--Ensure user has access
			SET @CompletedByID = Wf.Fn_GetValidWorkflowUserID(@CompletedByUserName, @WorkflowTemplateID, @WorkflowInstanceID, @WorkflowInstanceTaskID, @WorkflowInstanceTaskStatusID,'WorkflowInstanceTaskAction')
			IF (@CompletedByID = 0)
			BEGIN
				SET @ErrorMessage = 'Security error: Invalid user - ' + @CompletedByUserName
				RAISERROR(@ErrorMessage,16,1)
			END

			--Ensure task is still outstanding
			IF NOT EXISTS(SELECT * FROM Wf.WorkflowInstanceTasks Wit WHERE WorkflowInstanceTaskID = @WorkflowInstanceTaskID AND WorkflowInstanceTaskStatusID = 1) --1=outstanding
			BEGIN
				DECLARE @CurrentStatus VARCHAR(100)
				DECLARE @DateCompleted DATETIME
				
				SELECT @CurrentStatus = Wits.Description, @DateCompleted = DateCompleted
				FROM Wf.WorkflowInstanceTasks Wit
					INNER JOIN Wf.WorkflowInstanceTaskStatus Wits ON Wit.WorkflowInstanceTaskStatusID = Wits.WorkflowInstanceTaskStatusID 
				WHERE WorkflowInstanceTaskID = @WorkflowInstanceTaskID
			
				SET @ErrorMessage = 'This task is not outstanding any more.  The task current status is ' + @CurrentStatus + ' and was completed on ' + CAST(@DateCompleted AS VARCHAR(20)) + '.'
				RAISERROR(@ErrorMessage,16,1)
			END
							
			--Log Comments
			IF LEN(@Comments) > 0
			BEGIN
				DECLARE @EntityID INT
				DECLARE @ItemID BIGINT

				SELECT @EntityID = EntityID, @ItemID = ItemID
				FROM Wf.WorkflowInstances
				WHERE WorkflowInstanceID = @WorkflowInstanceID

				SET @Comments = 'Step ' + CAST(@StepCurrent AS VARCHAR(20)) + ': ' + @Comments

				EXEC Crm.Pr_CommentCreate @EntityID, @ItemID, @Comments, @CompletedByUserName
			END
			
			--Error
			IF @WorkflowInstanceTaskStatusID = 9
			BEGIN				
				--Flag current task as "error"			
				EXEC Wf.Pr_WorkflowInstanceTaskStatusUpdate @WorkflowInstanceTaskID, @WorkflowInstanceTaskStatusID, @CompletedByID, @Comments 
			
				--Change the workflow instance status. 6=Error
				EXEC Wf.Pr_WorkflowInstanceStatusUpdate @WorkflowInstanceID, 6, @CompletedByID, 'Workflow error'
			END
			
			--Expired
			IF @WorkflowInstanceTaskStatusID = 11
			BEGIN				
				--Flag current task as "expired"			
				EXEC Wf.Pr_WorkflowInstanceTaskStatusUpdate @WorkflowInstanceTaskID, @WorkflowInstanceTaskStatusID, @CompletedByID, @Comments 	
				
				--Get next step's details
				SELECT 
					@WorkflowTemplateStepIDNext = WorkflowTemplateStepID,
					@StepDescriptionNext = StepDescription,
					@StepNext = StepNext,
					@WorkflowTemplateStepTypeIDNext = WorkflowTemplateStepTypeID
				FROM Wf.Fn_GetWorkflowTemplateNextStep(@WorkflowTemplateID, @WorkflowInstanceTaskID, @WorkflowInstanceTaskStatusID)
				
				--Move workflow to next step
				EXEC Wf.Pr_WorkflowInstanceCreateNextStep @WorkflowTemplateID, @WorkflowInstanceID, @WorkflowInstanceTaskID, @StepCurrentBranch, @StepNext, @WorkflowTemplateStepIDNext, @WorkflowTemplateStepTypeIDNext, @StepDescriptionNext, @CompletedByID
			END
				
			--Rejected 
			IF @WorkflowInstanceTaskStatusID = 3
			BEGIN				
				--Flag current task as "rejected"			
				EXEC Wf.Pr_WorkflowInstanceTaskStatusUpdate @WorkflowInstanceTaskID, @WorkflowInstanceTaskStatusID, @CompletedByID, @Comments 
			
				--Reject the workflow instance. 3=Rejected
				EXEC Wf.Pr_WorkflowInstanceStatusUpdate @WorkflowInstanceID, 3, @CompletedByID, 'Workflow rejected'
			END
			
			--Request Correction
			IF @WorkflowInstanceTaskStatusID = 5 
			BEGIN
				EXEC Wf.Pr_WorkflowInstanceRequestCorrection @WorkflowTemplateID, @WorkflowInstanceID, @RequestCorrectionWorkflowTemplateStepID, @WorkflowInstanceTaskID, @StepCurrentBranch, @CompletedByID, @Comments
			END
			
			--approve or complete step
			IF @WorkflowInstanceTaskStatusID = 2 OR @WorkflowInstanceTaskStatusID = 4  --2=Approved. 4=Completed
			BEGIN																						
				--Flag current task as completed
				EXEC Wf.Pr_WorkflowInstanceTaskStatusUpdate @WorkflowInstanceTaskID, @WorkflowInstanceTaskStatusID, @CompletedByID, @Comments
				
				--Update required steps to esnure correct route is used. Completing current task sometimes changes required route.
				EXEC Wf.Pr_WorkflowInstanceRequiredStepsCalculate @WorkflowInstanceID
				
				--Get next step's details
				SELECT 
					@WorkflowTemplateStepIDNext = WorkflowTemplateStepID,
					@StepDescriptionNext = StepDescription,
					@StepNext = StepNext,
					@WorkflowTemplateStepTypeIDNext = WorkflowTemplateStepTypeID
				FROM Wf.Fn_GetWorkflowTemplateNextStep(@WorkflowTemplateID, @WorkflowInstanceTaskID, @WorkflowInstanceTaskStatusID)
				
				--Move workflow to next step
				EXEC Wf.Pr_WorkflowInstanceCreateNextStep @WorkflowTemplateID, @WorkflowInstanceID, @WorkflowInstanceTaskID, @StepCurrentBranch, @StepNext, @WorkflowTemplateStepIDNext, @WorkflowTemplateStepTypeIDNext, @StepDescriptionNext, @CompletedByID
			END
		END

		IF @Trancount = 0	
			COMMIT

		SET @Success = 1
	END TRY
	BEGIN CATCH	
		DECLARE @xstate INT
		SELECT @ErrorMessage = CAST(ERROR_MESSAGE() AS Varchar(MAX)), @xstate = XACT_STATE();

		IF @xstate = -1
			ROLLBACK
		IF @xstate = 1 and @Trancount = 0
			ROLLBACK
		IF @xstate = 1 and @Trancount > 0
			ROLLBACK TRANSACTION Pr_WorkflowInstanceTaskAction

		SET @Success = 0
	END CATCH
				
	SELECT @Success
END
GO