CREATE OR ALTER PROCEDURE [App].[Pr_ApplicationAwaitingCorrectionsGet]
(
	@AssignedToID BIGINT,
	@UserID BIGINT
)
AS
BEGIN	
	/********************************************************************************************
	Get user details and perform security checks
	*********************************************************************************************/	
	DECLARE @EntityIDToCheck INT = 1000 /*Application*/
	DECLARE @ItemIDToCheck BIGINT = NULL
	DECLARE @Action VARCHAR(255) = 'Search'
	DECLARE @OfficeLocationID BIGINT, @DivisionTypeID SMALLINT, @ProvinceID SMALLINT, @RegionID SMALLINT, @HasAccess BIT
	
	EXEC App.Pr_GetUserLocationAndSecurity @UserID, @EntityIDToCheck, @ItemIDToCheck, @Action, @OfficeLocationID OUTPUT, @DivisionTypeID OUTPUT, @ProvinceID OUTPUT, @RegionID OUTPUT, @HasAccess OUTPUT

	IF @HasAccess = 0
	BEGIN
		RAISERROR('User location and security error', 16, 1)
		RETURN
	END	
	
	/********************************************************************************************
	Perform search
	*********************************************************************************************/
	SELECT DISTINCT WIT.* 
	FROM Wf.Vw_WorkflowInstanceTasks WIT INNER JOIN App.Vw_ApplicationDetails_Light Ard 
	ON WIT.WorkflowInstanceID = Ard.WorkflowInstanceID INNER JOIN App.Fn_ApplicationSearch(NULL, 0, 1, 1, '1,3'/*Steps*/, '2' /*Data Capture*/, '3' /*Awaiting Correction*/, @UserID) Searchresults 
	ON Searchresults.ApplicationDetailID = Ard.ApplicationDetailID
	WHERE WIT.AssignedToID = @AssignedToID AND 
		WIT.WorkflowInstanceTaskStatusID = 1 /*Outstanding*/
END
GO

CREATE OR ALTER PROCEDURE [App].[Pr_ApplicationSearch]
(
	@ApplicationRequestTypes VARCHAR(255),
	@IncludeWorkflowsAwaitingInfoOnly BIT,
	@IncludeActiveWorkflowsOnly BIT,
	@IncludeActiveTasksOnly BIT,
	@ApplicationStatusID SMALLINT,
	@ApplicationSubStatusID SMALLINT,
	@Steps VARCHAR(255),
	@UserID BIGINT
)
-- EXEC [App].[Pr_ApplicationSearch] @ApplicationRequestTypes='=31,32,33,38',@IncludeWorkflowsAwaitingInfoOnly=0,@IncludeActiveWorkflowsOnly=1,@IncludeActiveTasksOnly=0,@ApplicationStatusID=NULL,@ApplicationSubStatusID=NULL,@Steps='12',@UserID=1001
AS
BEGIN	
	/********************************************************************************************
	Get user details and perform security checks
	*********************************************************************************************/	
	DECLARE @EntityIDToCheck INT = 1000 /*Application*/
	DECLARE @ItemIDToCheck BIGINT = NULL
	DECLARE @Action VARCHAR(255) = 'Search'
	DECLARE @OfficeLocationID BIGINT, @DivisionTypeID SMALLINT, @ProvinceID SMALLINT, @RegionID SMALLINT, @HasAccess BIT
	
	EXEC App.Pr_GetUserLocationAndSecurity @UserID, @EntityIDToCheck, @ItemIDToCheck, @Action, @OfficeLocationID OUTPUT, @DivisionTypeID OUTPUT, @ProvinceID OUTPUT, @RegionID OUTPUT, @HasAccess OUTPUT

	IF @HasAccess = 0
	BEGIN
		RAISERROR('User location and security error', 16, 1)
		RETURN
	END	
	
	/********************************************************************************************
	Perform search
	*********************************************************************************************/	
	SELECT Ard.*
	FROM App.Vw_ApplicationDetails_Light Ard INNER JOIN App.Fn_ApplicationSearch(@ApplicationRequestTypes, @IncludeWorkflowsAwaitingInfoOnly, @IncludeActiveWorkflowsOnly, @IncludeActiveTasksOnly, @Steps, @ApplicationStatusID, @ApplicationSubStatusID, @UserID) Searchresults 
	ON Searchresults.ApplicationDetailID = Ard.ApplicationDetailID
END
GO

CREATE OR ALTER FUNCTION [App].[Fn_ApplicationSearch]
(	
	@ApplicationRequestTypes VARCHAR(255),
	@IncludeWorkflowsAwaitingInfoOnly BIT,
	@IncludeActiveWorkflowsOnly BIT,
	@IncludeActiveTasksOnly BIT,	
	@Steps VARCHAR(255),
	@ApplicationStatusIDs VARCHAR(255),
	@ApplicationSubStatusIDs VARCHAR(255),
	@UserID BIGINT
)
-- SELECT * FROM App.Fn_ApplicationSearch('awaitingcapturing', 0, 1, 1, '3'/*Steps*/, '2' /*Data Capture*/, '3' /*Awaiting Correction*/, 1000)
RETURNS @Applications TABLE 
(
	ApplicationDetailID BIGINT
)
AS
BEGIN
	/****************************************************************************
	Get the current user's office location id
	****************************************************************************/
	DECLARE @OfficeLocationID BIGINT

	SELECT @OfficeLocationID = U.OfficeLocationID
	FROM [Security].Users U	
	WHERE UserID = @UserID		

	/****************************************************************************
	Get the list of outstanding tasks
	****************************************************************************/
	DECLARE @OutstandingTasks TABLE (WorkflowInstanceID BIGINT) 

	IF @IncludeActiveTasksOnly = 1
	BEGIN
		INSERT INTO @OutstandingTasks(WorkflowInstanceID)
		SELECT Wi.WorkflowInstanceID
		FROM Wf.WorkflowInstances Wi INNER JOIN Wf.WorkflowInstanceTasks Wit 
		ON Wi.WorkflowInstanceID = Wit.WorkflowInstanceID
		WHERE WorkflowInstanceTaskStatusID = 1 --Outstanding
			AND 
			(
				CASE 
					WHEN AssignedToID = @UserID THEN 1
					ELSE 0
				END
			) = 1
	END

	/****************************************************************************
	Create table of application request type ids
	****************************************************************************/
	DECLARE @IncludeApplicationRequestTypeID BIT = 0
	DECLARE @ApplicationApplicationRequestTypeIDsTable TABLE(ID BIGINT)

	IF ISNULL(@ApplicationRequestTypes,'') <> ''
	BEGIN
		SET @IncludeApplicationRequestTypeID = 1

		INSERT INTO @ApplicationApplicationRequestTypeIDsTable(ID)
		SELECT Value
		FROM [System].Fn_GenSplitCSV(@ApplicationRequestTypes,',')
	END

	/****************************************************************************
	Create table of status ids
	****************************************************************************/
	DECLARE @IncludeApplicationStatusID BIT = 0
	DECLARE @ApplicationStatusIDsTable TABLE(ID BIGINT)

	IF ISNULL(@ApplicationStatusIDs,'') <> ''
	BEGIN
		SET @IncludeApplicationStatusID = 1

		INSERT INTO @ApplicationStatusIDsTable(ID)
		SELECT Value
		FROM [System].Fn_GenSplitCSV(@ApplicationStatusIDs,',')
	END

	/****************************************************************************
	Create table of sub status ids
	****************************************************************************/
	DECLARE @IncludeApplicationSubStatusID BIT = 0
	DECLARE @ApplicationSubStatusIDsTable TABLE(ID BIGINT)
	IF ISNULL(@ApplicationSubStatusIDs,'') <> ''
	BEGIN
		SET @IncludeApplicationSubStatusID = 1

		INSERT INTO @ApplicationSubStatusIDsTable(ID)
		SELECT Value
		FROM [System].Fn_GenSplitCSV(@ApplicationSubStatusIDs,',')
	END
	
	/****************************************************************************
	Get the list of steps
	****************************************************************************/
	DECLARE @StepsTable TABLE(Step FLOAT)
	DECLARE @IncludeSpecificStepsOnly BIT = 0

	IF ISNULL(@Steps,'') <> ''
	BEGIN
		SET @IncludeSpecificStepsOnly = 1

		INSERT INTO @StepsTable (Step)
		SELECT CAST(Value AS FLOAT)
		FROM [System].Fn_GenSplitCSV(@Steps,',')
	END    

	/****************************************************************************
	Find the actual list of applications
	****************************************************************************/
	INSERT INTO @Applications(ApplicationDetailID)
	SELECT DISTINCT Ard.ApplicationDetailID
	FROM App.ApplicationDetails Ard LEFT JOIN Wf.WorkflowInstances Wi 
	ON Ard.WorkflowInstanceID = Wi.WorkflowInstanceID		
	WHERE Wi.OfficeLocationID = @OfficeLocationID
		AND
		(
			CASE 
				WHEN @IncludeActiveWorkflowsOnly = 0 THEN 1
				WHEN @IncludeActiveWorkflowsOnly = 1 AND Wi.WorkflowInstanceStatusID IN (1 /*In Progress*/, 6 /*Error*/) THEN 1
				ELSE 0			
			END
		) = 1
		AND
		(
			CASE 
				WHEN @IncludeActiveTasksOnly = 0 THEN 1
				ELSE
					(
						CASE WHEN Wi.WorkflowInstanceID IN (SELECT WorkflowInstanceID FROM @OutstandingTasks) 
							THEN 1
							ELSE 0
						END
					)
			END
		) = 1
		AND
		(
			CASE WHEN @IncludeSpecificStepsOnly = 0 THEN 1
			ELSE
				(
					CASE 
						WHEN 
							EXISTS
							(
								SELECT TOP 1 1 
								FROM Wf.WorkflowInstanceTasks Wit INNER JOIN Wf.WorkflowTemplateSteps Wts 
								ON Wit.WorkflowTemplateStepID = Wts.WorkflowTemplateStepID
								WHERE Wit.WorkflowInstanceID = Wi.WorkflowInstanceID 
									AND Wit.WorkflowInstanceTaskStatusID = 1 /*in progress*/ 
									AND Wts.Step IN (SELECT Step FROM @StepsTable)
							)
						THEN 1
						ELSE 0
					END
				)
			END
		) = 1
		AND
		(
			CASE 
				WHEN @IncludeApplicationStatusID = 0 THEN 1
				WHEN ApplicationStatusID IN (SELECT ID FROM @ApplicationStatusIDsTable) THEN 1
				ELSE 0			
			END
		) = 1
		AND
		(
			CASE 
				WHEN @IncludeApplicationSubStatusID = 0 THEN 1
				WHEN ApplicationSubStatusID IN (SELECT ID FROM @ApplicationSubStatusIDsTable) THEN 1
				ELSE 0			
			END
		) = 1
		AND
		(
			CASE 
				WHEN @IncludeApplicationRequestTypeID = 0 THEN 1
				WHEN ApplicationRequestTypeID IN (SELECT ID FROM @ApplicationApplicationRequestTypeIDsTable) THEN 1
				ELSE 0			
			END
		) = 1
	
	RETURN 
END
GO


