USE [LegitimateKZN_QA_SO]
GO
/****** Object:  StoredProcedure [App].[Pr_ReceiptApplications]    Script Date: 2025/11/29 10:39:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [App].[Pr_ReceiptApplications]
(
	@ApplicationRequestDetailID BIGINT,
	@UserID BIGINT,
	@GeneratedReceiptDetailID BIGINT OUTPUT
)
AS
BEGIN
	DECLARE @ApplicationsToReceiptCount INT

	SET @GeneratedReceiptDetailID = -1

	BEGIN TRANSACTION CreateReceiptTransaction
	BEGIN TRY

		--Ensure that all the applications selected are for the same member
		DECLARE @MemberIDNumber VARCHAR(20)
		DECLARE @MemberName VARCHAR(100)
		DECLARE @OldReceiptCost MONEY
		DECLARE @NewReceiptCost MONEY
		DECLARE @CorrectReceipteCost MONEY
		DECLARE @ApplicationRequestTypeID SMALLINT
						
		SELECT TOP 1 
			@MemberIDNumber = Aad.IDNumberOrBusinessRegistrationNo, 
			@MemberName = UPPER(CASE WHEN AAD.IDTypeID IN ( 3, 6 ) THEN AAD.TradeName ELSE ISNULL(AAD.FirstNames, '') + ' ' + ISNULL(AAD.SurnameOrCompanyName, '') END), 
			@ApplicationRequestDetailID = Ard.ApplicationRequestDetailID,
			@ApplicationRequestTypeID = Ard.ApplicationRequestTypeID
		FROM App.ApplicationRequestDetails Ard
			INNER JOIN App.ApplicationApplicantDetails Aad ON Aad.ApplicationRequestDetailID = Ard.ApplicationRequestDetailID
		WHERE Ard.ApplicationRequestDetailID = @ApplicationRequestDetailID
			
		SELECT @CorrectReceipteCost = App.Fn_GetReceiptCost(Ard.ApplicationRequestDetailID,Ard.ApplicationRequestTypeID,Ard.IsRenewal,Ard.IsLtpsConversion,Ard.VehicleCount)
		FROM App.ApplicationRequestDetails Ard
		WHERE ApplicationRequestDetailID = @ApplicationRequestDetailID
		
		SELECT @MemberName = UPPER(CASE WHEN AAD.IDTypeID IN ( 3, 6 ) THEN Aad.TradeName ELSE ISNULL(Aad.FirstNames, '') + ' ' + ISNULL(Aad.SurnameOrCompanyName, '') END), 
		@OldReceiptCost = 
		(
			SELECT SUM(Rd.TotalCost)
			FROM App.ReceiptDetails Rd
			WHERE Rd.ApplicationRequestDetailID = @ApplicationRequestDetailID
		)
		FROM App.ApplicationRequestDetails Ard
			INNER JOIN App.ApplicationApplicantDetails Aad ON Aad.ApplicationRequestDetailID = Ard.ApplicationRequestDetailID
		WHERE Ard.ApplicationRequestDetailID = @ApplicationRequestDetailID

		SET @OldReceiptCost = ISNULL(@OldReceiptCost,0)
		SET @NewReceiptCost = @CorrectReceipteCost - @OldReceiptCost;

		--Change 20250212
		--allowing R0 receipts to cater for R0 COV ZN registration number changes
		IF(@NewReceiptCost > 0 OR (App.Fn_IsCovType(@ApplicationRequestTypeID) = 1 AND @NewReceiptCost = 0))
		BEGIN
			INSERT INTO App.ReceiptDetails(PaymentTypeID, TotalCost, CreatedById, CreatedDate, UpdatedById, UpdatedDate, IsCancelled, ApplicantData, ReceiptItems, ApplicationRequestDetailID)
			SELECT MAX(PaymentTypeID), @NewReceiptCost, @UserID, GETDATE(), @UserID, GETDATE(), 0, @MemberName,
			(
				SELECT 
					ARD.ApplicationNumber + ',' + 
					ISNULL(ARD.IDNumberOrBusinessRegistrationNo,'Not Available') + ',' + 
					ISNULL(REPLACE(ARD.Member,',',''), 'Not Available')  + ',' + 
					ISNULL(REPLACE(ARD.ApplicationDescription,',',''), 'Not Available') + 
						CASE
							WHEN ARD.VehicleCount > 1 THEN ' for ' + CAST(Ard.VehicleCount AS VARCHAR(20)) + ' vehicles'
							ELSE ''
						END						
						+ ',' + 
					CAST(ARD.VehicleCount AS VARCHAR(10)) + ',' + 
					CAST(@NewReceiptCost AS VARCHAR(10)) + ',' + 
					ISNULL(REPLACE(ARD.AssociationName,',',''), 'Not Available') + ',' + 
					CASE 
						WHEN App.Fn_IsCovType(ARD.ApplicationRequestTypeID) = 1 OR ARD.ApplicationRequestTypeID = 1 OR ARD.ApplicationRequestTypeID = 27 /*replacement*/ THEN 
							ISNULL(ARD.VehicleRegNumber, 'Not Available') + ',' + 
							ISNULL(ARD.ChassisNumber, 'Not Available') + ','  
						ELSE
							ISNULL(ARD.ExistingVehicleRegNumber, 'Not Available') + ',' + 
							ISNULL(ARD.ExistingVehicleChassisNumber, 'Not Available') + ','  
					END
					+ ISNULL(ARD.Region, 'Not Available') + ',' + 
		
					CASE 
						WHEN App.Fn_IsCovType(ARD.ApplicationRequestTypeID) = 1 OR ARD.ApplicationRequestTypeID = 1 OR ARD.ApplicationRequestTypeID = 27 /*replacement*/ THEN 
							ISNULL(ARD.ExistingVehicleRegNumber, 'Not Available') + ',' + 
							ISNULL(ARD.ExistingVehicleChassisNumber, 'Not Available') + ','  
						ELSE
							ISNULL(ARD.VehicleRegNumber, 'Not Available') + ',' + 
							ISNULL(ARD.ChassisNumber, 'Not Available') + ','  				
					END		
					+ '#'
				FROM App.Vw_ApplicationRequestDetails AS ARD
				WHERE ARD.ApplicationRequestDetailID = @ApplicationRequestDetailID
				FOR XML PATH('')
			), @ApplicationRequestDetailID
			FROM App.Vw_ApplicationRequestDetails
			WHERE ApplicationRequestDetailID = @ApplicationRequestDetailID

			SELECT @GeneratedReceiptDetailID = SCOPE_IDENTITY()

			UPDATE App.ReceiptDetails
				SET ReceiptItems = SUBSTRING(ReceiptItems,0,LEN(ReceiptItems)),
					HasBulkVehicleApplications = 
						CASE 
							WHEN EXISTS(SELECT * FROM App.ApplicationRequestDetails WHERE ApplicationRequestDetailID = @ApplicationRequestDetailID AND IsBulkVehicleApplication = 1) THEN 1
							ELSE 0
						END
			WHERE ReceiptDetailID = @GeneratedReceiptDetailID
						
			UPDATE App.ApplicationRequestDetails
				SET UpdatedByID = @UserID,
					UpdatedDate = GETDATE()
			WHERE ApplicationRequestDetailID = @ApplicationRequestDetailID

			DECLARE @Description VARCHAR(MAX)
			DECLARE @Receiptumber VARCHAR(20)

			SELECT @Receiptumber = ReceiptNumber
			FROM App.ReceiptDetails
			WHERE ReceiptDetailID = @GeneratedReceiptDetailID

			SET @Description = 'Receipt ' + @Receiptumber + ' created'

			INSERT INTO Audit.History(SourceID, ActionID, Description, IsCritical, IsException, EntityID, ItemID, CreatedByID, CreatedDate)
			VALUES( 2, 1000, @Description, 0, 0, 1000, @ApplicationRequestDetailID, @UserID, GETDATE()) --2=Application, 1000=created receipt, 1000=Application request
		
			-- Auto complete the task
			DECLARE @WorkflowInstanceTaskID BIGINT

			SELECT @WorkflowInstanceTaskID = WIT.WorkflowInstanceTaskID
			FROM Wf.WorkflowInstanceTasks AS WIT
				INNER JOIN Wf.WorkflowTemplateSteps AS WTS ON WIT.WorkflowTemplateStepID = WTS.WorkflowTemplateStepID
				INNER JOIN App.Vw_ApplicationRequestDetails AS ARD ON WIT.WorkflowInstanceID = ARD.WorkflowInstanceID
			WHERE WTS.Step = 3 -- Step is 3
				AND WorkflowInstanceTaskStatusID = 1 -- In Progress
				AND WIT.AssignedToID = @UserID
				AND ApplicationRequestDetailID = @ApplicationRequestDetailID

			IF ISNULL(@WorkflowInstanceTaskID,-1) <> -1
			BEGIN
				DECLARE @ErrorMessage VARCHAR(255)
				DECLARE @Username VARCHAR(200)

				SELECT @Username = Email
				FROM Security.Users
				WHERE UserID = @UserID

				EXEC Wf.Pr_WorkflowInstanceTaskAction @WorkflowInstanceTaskID, 4 /*completed*/, @Username, -1, @Description, @ErrorMessage OUTPUT

				IF ISNULL(@ErrorMessage,'') <> ''
				BEGIN
					RAISERROR(@ErrorMessage, 16, 1)
				END                				
			END

		END
				
		COMMIT TRANSACTION CreateReceiptTransaction
	END TRY
	BEGIN CATCH
		-- Roll back any active or uncommittable transactions 
		-- before rethrowing the error
		IF XACT_STATE() <> 0
		BEGIN
			ROLLBACK TRANSACTION CreateReceiptTransaction;

			SET @GeneratedReceiptDetailID = -1
		END
		EXEC [System].Pr_SysErrorRethrow
	END CATCH

	SELECT @GeneratedReceiptDetailID
END




