CREATE OR ALTER FUNCTION [App].[Fn_ApplicationStep2Checks]
(
	@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
	DECLARE @ReceiptDetailID BIGINT
	DECLARE @ProofOfPaymentRefNo VARCHAR(50)

	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 
		
	SELECT TOP 1 @ReceiptDetailID = Rd.ReceiptDetailID FROM App.ReceiptDetails Rd WHERE ApplicationDetailID = @ApplicationDetailID	
	
	/****************************************************************************
	Receipt details
	****************************************************************************/		
	IF NOT EXISTS (SELECT TOP 1 1 FROM App.ReceiptDetails Rd WHERE ApplicationDetailID = @ApplicationDetailID) 
	BEGIN
		INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
		VALUES('Has the application receipt been created?', 0, '',1)			
	END
	ELSE
	BEGIN
		IF EXISTS(SELECT TOP 1 1 FROM App.ReceiptDetails Rd WHERE ApplicationDetailID = @ApplicationDetailID)
		BEGIN
			IF EXISTS (SELECT TOP 1 1 FROM App.ReceiptDetails Rd WHERE ApplicationDetailID = @ApplicationDetailID AND ISNULL(Rd.ProofOfPaymentDocID,-1) = -1) 
			BEGIN
				INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
				VALUES('Has the proof of payment been uploaded?', 0, '',1)			
			END
			ELSE
			BEGIN	
				INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
				VALUES('Has the proof of payment been uploaded?', 1, '',1)			
			END
		END

		IF EXISTS(SELECT TOP 1 1 FROM App.ReceiptDetails Rd WHERE ApplicationDetailID = @ApplicationDetailID)
		BEGIN
			IF EXISTS (SELECT TOP 1 1 FROM Verified.Printouts P WHERE P.EntityID = 1001 /*Receipt details*/ AND P.ItemID = @ReceiptDetailID AND P.PrintedSuccussfully = 1) 
			BEGIN
				INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
				VALUES('Has the proof of payment been printed?', 1, '',1)			
			END
			ELSE
			BEGIN	
				INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
				VALUES('Has the proof of payment been printed?', 0, '',1)			
			END
		END
	END
	/****************************************************************************
	Check Amount Due
	****************************************************************************/		
	IF EXISTS (SELECT TOP 1 1 FROM App.ApplicationDetails Rd WHERE ApplicationDetailID = @ApplicationDetailID AND ISNULL(AmountDue,0) = 0) 
	BEGIN
		INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
		VALUES('Has the application been fully paid?', 1, '',1)			
	END
	ELSE
	BEGIN
		DECLARE @AmountDue MONEY
		SET @AmountDue = (SELECT AmountDue FROM App.ApplicationDetails WHERE ApplicationDetailID = @ApplicationDetailID)
		INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
		VALUES('Has the application been fully paid?', 0, 'R'+CAST(@AmountDue AS VARCHAR(255)) + ' still outstanding on the application',1)				
	END

	/****************************************************************************
	Check Reference Number
	****************************************************************************/		
	IF EXISTS (SELECT TOP 1 1 FROM App.ReceiptDetails Rd WHERE ApplicationDetailID = @ApplicationDetailID AND ProofOfPaymentRefNo IS NOT NULL) 
	BEGIN
		INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
		VALUES('Has the Payment Reference been captured?', 1, '',1)			
	END
	ELSE
	BEGIN
		INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
		VALUES('Has the Payment Reference been captured?', 0, '',1)				
	END

	SELECT @ProofOfPaymentRefNo = ProofOfPaymentRefNo FROM App.ReceiptDetails Rd WHERE ApplicationDetailID = @ApplicationDetailID AND ProofOfPaymentRefNo IS NOT NULL

	/****************************************************************************
	Check Reference Number Duplicate
	****************************************************************************/		
	IF (SELECT COUNT(1) FROM App.ReceiptDetails WHERE ProofOfPaymentRefNo = @ProofOfPaymentRefNo) >= 2 
	BEGIN
		INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
		VALUES('Has the Payment Reference been used before?', 0, '',1)			
	END
	ELSE
	BEGIN
		INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
		VALUES('Has the Payment Reference been used before?', 1, '',1)				
	END
		
	RETURN
END
GO