IF COL_LENGTH('App.ReceiptDetails', 'ProofOfPaymentRefNo') IS NULL
BEGIN
	ALTER TABLE [App].[ReceiptDetails] ADD [ProofOfPaymentRefNo] VARCHAR(50)
END
GO

CREATE OR ALTER PROCEDURE [App].[Pr_ApplicationReceiptsGet]
	@ApplicationDetailID BIGINT,	
	@UserID BIGINT
AS
-- EXEC [App].[Pr_ApplicationReceiptsGet] 2716518, 1001
BEGIN	
	SET NOCOUNT ON;

	/********************************************************************************************
	Get user details and perform security checks
	*********************************************************************************************/	
	DECLARE @EntityIDToCheck INT = 1000 /*Application details*/
	DECLARE @ItemIDToCheck BIGINT = @ApplicationDetailID
	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 Rd.ReceiptDetailID, PaymentTypeID, Rd.ReceiptNumber, Rd.TotalCost,U.KnownAs AS CreatedBy, Rd.CreatedDate, Rd.ProofOfPaymentDocID, Rd.ProofOfPaymentRefNo,
		CAST((
			CASE
				WHEN EXISTS(SELECT TOP 1 1 FROM Verified.Printouts P WHERE P.EntityID = 1001 /*Receipt details*/ AND P.ItemID = Rd.ReceiptDetailID AND P.PrintedSuccussfully = 1) THEN 1
				ELSE 0
			END
		) AS BIT) AS Printed
	FROM App.ReceiptDetails Rd INNER JOIN [Security].Users U 
	ON U.UserID = Rd.CreatedById
	WHERE ApplicationDetailID = @ApplicationDetailID	
END
GO

CREATE OR ALTER PROCEDURE [App].[Pr_ReceiptApplicationProofOfPaymentRefNoUpdate]
(
	@ApplicationDetailID BIGINT,
	@ReceiptDetailID BIGINT,
	@ProofOfPaymentRefNo VARCHAR(50),
	@UserID BIGINT
)
AS
BEGIN
		
	/********************************************************************************************
	Get user details and perform security checks
	*********************************************************************************************/	
	DECLARE @EntityIDToCheck INT = 1001 /*ReceiptDetails*/
	DECLARE @ItemIDToCheck BIGINT = @ReceiptDetailID
	DECLARE @Action VARCHAR(255) = 'Update'
	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	
	
	/********************************************************************************************
	Update receipt
	*********************************************************************************************/
	UPDATE App.ReceiptDetails
	SET ProofOfPaymentRefNo = @ProofOfPaymentRefNo
	WHERE ReceiptDetailID =  @ReceiptDetailID
		AND ApplicationDetailID = @ApplicationDetailID
END
GO

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

	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
		
	RETURN
END
GO