IF EXISTS (select * from sysobjects where name = 'Fn_ApplicationStep10Checks')
	DROP FUNCTION [App].[Fn_ApplicationStep10Checks]
GO

CREATE OR ALTER FUNCTION [App].[Fn_ApplicationStep11Checks]
(
	@ApplicationDetailID BIGINT,
	@UserID BIGINT
)
RETURNS @Outcomes TABLE(Title VARCHAR(500) NOT NULL, IsSuccessfull BIT NOT NULL, OutcomeDetails VARCHAR(2000) NULL, RequiredForSubmitting BIT NOT NULL)
AS
BEGIN
	
	DECLARE @ApplicationRequestTypeID SMALLINT
	DECLARE @DivisionTypeID SMALLINT
	DECLARE @ProvinceID SMALLINT

	SELECT @ApplicationRequestTypeID = Ard.ApplicationRequestTypeID, @DivisionTypeID = Ard.OfficeLocationDivisionTypeID, @ProvinceID = Ard.ApplicationProvinceID
	FROM App.ApplicationDetails Ard 
		INNER JOIN App.ApplicationApplicantDetails Aad ON Ard.ApplicationDetailID = Aad.ApplicationDetailID
	WHERE Ard.ApplicationDetailID = @ApplicationDetailID 

	/****************************************************************************
	Applicant Inspection details
	****************************************************************************/	
	IF EXISTS (SELECT TOP 1 1
			FROM App.ApplicationInspectionDetails 
			WHERE ApplicationDetailID = @ApplicationDetailID AND 
			(
				ISNULL(InspectionDateFrom,'') = '' OR 
				ISNULL(InspectionDateTo,'') = '' OR 			
				ISNULL(InspectionResultDocID,-1) = -1 OR 
				ISNULL(InspectionBackToOfficeDocID,-1) = -1
			)
		) 
		BEGIN
			INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
			VALUES('Is the applicant inspection details section complete?', 0, '',1)			
		END
		ELSE
		BEGIN
			INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
			VALUES('Is the applicant inspection details section complete?', 1, '',1)			
		END

		IF EXISTS (SELECT TOP 1 1
			FROM App.ApplicationInspectionDetails 
			WHERE ApplicationDetailID = @ApplicationDetailID AND 
			( 			
				ISNULL(InspectionResultDocID,-1) = -1 
			)
		) 
		BEGIN
			INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
			VALUES('Is the Inspection result document uploaded?', 0, '',1)			
		END
		ELSE
		BEGIN
			INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
			VALUES('Is the Inspection result document uploaded?', 1, '',1)			
		END

		IF EXISTS (SELECT TOP 1 1
			FROM App.ApplicationInspectionDetails 
			WHERE ApplicationDetailID = @ApplicationDetailID AND 
			( 			
				ISNULL(InspectionBackToOfficeDocID,-1) = -1 
			)
		) 
		BEGIN
			INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
			VALUES('Is the Inspection Back To Office document uploaded?', 0, '',1)			
		END
		ELSE
		BEGIN
			INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
			VALUES('Is the Inspection Back To Office document uploaded?', 1, '',1)			
		END
				
	RETURN
END
GO

CREATE OR ALTER PROCEDURE [App].[Pr_ApplicationSubmitToNextStepChecks]
	@ApplicationDetailID BIGINT,
	@TaskID BIGINT,
	@UserID BIGINT
AS
-- EXEC [App].[Pr_ApplicationSubmitToNextStepChecks] 974252, 73,1001
BEGIN
	SET NOCOUNT ON;

	DECLARE @Outcomes TABLE([Title] [VARCHAR](500) NOT NULL, [IsSuccessfull] [BIT] NOT NULL,[OutcomeDetails] [VARCHAR](2000) NULL, RequiredForSubmitting BIT NOT NULL)
	DECLARE @DoChecks BIT = 1
	DECLARE @CurrentWorkflowStep FLOAT

	IF ISNULL(@TaskID,-1) <> -1
	BEGIN
		IF NOT EXISTS(SELECT * FROM Wf.WorkflowInstanceTasks WHERE WorkflowInstanceTaskID = @TaskID AND AssignedToID = @UserID AND WorkflowInstanceTaskStatusID = 1 /*in progress*/)
		BEGIN			
			SET @DoChecks = 0
		END
		ELSE
        BEGIN
			SELECT @CurrentWorkflowStep = Wts.Step
			FROM Wf.WorkflowInstanceTasks Wit 
				INNER JOIN Wf.WorkflowTemplateSteps Wts ON Wts.WorkflowTemplateStepID = Wit .WorkflowTemplateStepID
			WHERE Wit.WorkflowInstanceTaskID = @TaskID
		END
	END
	ELSE
	BEGIN
		SELECT @CurrentWorkflowStep = Wts.Step
		FROM App.ApplicationDetails Ard
			INNER JOIN Wf.WorkflowInstances Wi ON Ard.WorkflowInstanceID = Wi.WorkflowInstanceID			
			INNER JOIN Wf.WorkflowTemplateSteps Wts ON Wts.WorkflowTemplateStepID = Wi.WorkflowTemplateStepID
		WHERE Ard.ApplicationDetailID = @ApplicationDetailID
	END	

	IF @DoChecks = 1
	BEGIN
		IF @CurrentWorkflowStep = 1.0
		BEGIN
			INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
			SELECT Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting
			FROM App.Fn_ApplicationStep1Checks(@ApplicationDetailID, @UserID)
		END
		ELSE IF @CurrentWorkflowStep = 2.0
		BEGIN
			INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
			SELECT Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting
			FROM App.Fn_ApplicationStep2Checks(@ApplicationDetailID, @UserID)
		END	
		ELSE IF @CurrentWorkflowStep = 3.0
		BEGIN
			INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
			SELECT Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting
			FROM App.Fn_ApplicationStep3Checks(@ApplicationDetailID, @UserID)
		END	
		ELSE IF @CurrentWorkflowStep = 11.0
		BEGIN
			INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
			SELECT Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting
			FROM App.Fn_ApplicationStep11Checks(@ApplicationDetailID, @UserID)
		END	
	END
    
	SELECT * 
	FROM @Outcomes	
END
GO