ALTER PROCEDURE [App].[Pr_UpdateIsUsedFlags]
AS
BEGIN
	SET NOCOUNT ON;		
	
	/*
	Flag bypassed licences as used so they can't be used again
	*/
	DECLARE @UsedLicences TABLE(UsedLicenceNumber VARCHAR(100), IssueNumber SMALLINT)

	INSERT INTO @UsedLicences(UsedLicenceNumber, IssueNumber)
	SELECT  App.Fn_StripVersionNumber(UsedLicences.OperatingLicenceNumber,'/'), App.Fn_GetVersionNumber(UsedLicences.OperatingLicenceNumber, '/')
	FROM    App.ApplicationExistingLicences UsedLicences
		INNER JOIN App.ApplicationRequestDetails Ard ON UsedLicences.ApplicationRequestDetailID = Ard.ApplicationRequestDetailID 
		INNER JOIN Wf.WorkflowInstances Wi ON Ard.WorkflowInstanceID = Wi.WorkflowInstanceID
	WHERE Wi.WorkflowInstanceStatusID <> 4 /*cancelled*/
		AND Ard.ApplicationRequestTypeID <> 11 /*Temporary (Special) licence*/

	UPDATE  App.ApplicationAllowedExpiredLicences
	SET IsUsed = CASE WHEN EXISTS ( SELECT  *
											FROM @UsedLicences UsedLicences
											WHERE   UsedLicences.UsedLicenceNumber LIKE App.Fn_StripVersionNumber(OperatingLicenceNumber,'/') 
											) THEN 1
								ELSE 0
							END
	FROM App.ApplicationAllowedExpiredLicences
	WHERE OperatingLicenceNumber NOT LIKE 'LG%';

	UPDATE  App.ApplicationAllowedExpiredLicences
	SET IsUsed = CASE WHEN EXISTS ( SELECT  *
									FROM @UsedLicences UsedLicences
									WHERE   UsedLicences.UsedLicenceNumber LIKE App.Fn_StripVersionNumber(App.ApplicationAllowedExpiredLicences.OperatingLicenceNumber,'/') 
											AND UsedLicences.IssueNumber = App.ApplicationAllowedExpiredLicences.IssueNumber
											) THEN 1
								ELSE 0
							END
	FROM App.ApplicationAllowedExpiredLicences
	WHERE OperatingLicenceNumber LIKE 'LG%';

	/*
	Flag allowed new licences as used so they can't be used again
	*/
	DECLARE @ApplicationAllowedNewLicenceID BIGINT
	DECLARE @IdNumberOrBusinessRegistrationNumber VARCHAR(20)
	DECLARE @AssociationID BIGINT
	DECLARE @RegistrationNumber VARCHAR(50)
	DECLARE @ApplicationAllowedNewLicenceTypeID SMALLINT
	DECLARE @AllowedNewCreatedDate DATETIME
	DECLARE @Count BIGINT = 0

	DECLARE allowednewTypeCursor CURSOR FAST_FORWARD READ_ONLY FOR
	SELECT ApplicationAllowedNewLicenceID,
		   IdNumberOrBusinessRegistrationNumber,
		   AssociationID,
		   RegistrationNumber,
		   ApplicationAllowedNewLicenceTypeID,
		   CreatedDate
	FROM App.ApplicationAllowedNewLicences
	WHERE IsUsed = 0;

	OPEN allowednewTypeCursor;

	FETCH NEXT FROM allowednewTypeCursor
	INTO @ApplicationAllowedNewLicenceID,     @IdNumberOrBusinessRegistrationNumber,     @AssociationID,     @RegistrationNumber,     @ApplicationAllowedNewLicenceTypeID,  @AllowedNewCreatedDate;
	WHILE @@FETCH_STATUS = 0
	BEGIN
		DECLARE @ApplicationRequestDetailID BIGINT = NULL;
    
		/*1st. Check if application can be found*/
		SELECT @ApplicationRequestDetailID = Ard.ApplicationRequestDetailID
		FROM App.ApplicationRequestDetails Ard
			INNER JOIN App.ApplicationApplicantDetails Aad ON Ard.ApplicationRequestDetailID = Aad.ApplicationRequestDetailID
			INNER JOIN App.ApplicationVehicleDetails Avd ON Ard.ApplicationRequestDetailID = Avd.ApplicationRequestDetailID
		WHERE Ard.ApplicationRequestTypeID = 1 /*New*/
				AND REPLACE(Aad.IDNumberOrBusinessRegistrationNo, ' ', '') LIKE REPLACE(@IdNumberOrBusinessRegistrationNumber,' ','')
				AND REPLACE(Avd.VehicleRegistrationNumber, ' ', '') LIKE REPLACE(@RegistrationNumber, ' ', '')
				AND (
					CASE
						WHEN @ApplicationAllowedNewLicenceTypeID <> 4 /*3b*/ THEN 1
						WHEN @ApplicationAllowedNewLicenceTypeID = 4 /*3b*/ AND Ard.CreatedDate >= @AllowedNewCreatedDate THEN 1
						WHEN @ApplicationAllowedNewLicenceTypeID = 4 /*3b*/ AND Ard.CreatedDate < @AllowedNewCreatedDate THEN 0
					END					
				) = 1;
		
		/*2nd. If no application can be found check if the vehicle in question was added to a licence linked to the applicant id number*/
		IF ISNULL(@ApplicationRequestDetailID,-1) = -1
		BEGIN
			SELECT @ApplicationRequestDetailID = Ard.ApplicationRequestDetailID
			FROM App.ApplicationRequestDetails Ard
				INNER JOIN Verified.Licences L ON Ard.ApplicationRequestDetailID = L.ApplicationRequestDetailID
				INNER JOIN Verified.Vehicles V ON V.VehicleID = L.VehicleID
				INNER JOIN Verified.Members M ON M.MemberID = L.MemberID
			WHERE Ard.ApplicationRequestTypeID IN (1 /*New*/)
					AND REPLACE(M.IDNumberOrBusinessRegistrationNo, ' ', '') LIKE REPLACE(@IdNumberOrBusinessRegistrationNumber,' ','')					
					AND 
					(
						CASE 
							WHEN CHARINDEX('_', V.RegistrationNumber) > 0 AND REPLACE(@RegistrationNumber, ' ', '') LIKE LEFT(V.RegistrationNumber, CHARINDEX('_', V.RegistrationNumber) - 1)  THEN 1
							WHEN @RegistrationNumber LIKE V.RegistrationNumber THEN 1
						ELSE 0
						END
					) = 1
					AND (
						CASE
							WHEN @ApplicationAllowedNewLicenceTypeID <> 4 /*3b*/ THEN 1
							WHEN @ApplicationAllowedNewLicenceTypeID = 4 /*3b*/ AND Ard.CreatedDate >= @AllowedNewCreatedDate THEN 1
							WHEN @ApplicationAllowedNewLicenceTypeID = 4 /*3b*/ AND Ard.CreatedDate < @AllowedNewCreatedDate THEN 0
						END					
					) = 1;
		END

		IF ISNULL(@ApplicationRequestDetailID,-1) <> -1
		BEGIN
			UPDATE App.ApplicationAllowedNewLicences
			SET
				IsUsed = 1,
				ApplicationRequestDetailID = @ApplicationRequestDetailID
			WHERE ApplicationAllowedNewLicenceID = @ApplicationAllowedNewLicenceID
		END
		ELSE
		BEGIN
			UPDATE App.ApplicationAllowedNewLicences
			SET
				IsUsed = 0,
				ApplicationRequestDetailID = NULL
			WHERE ApplicationAllowedNewLicenceID = @ApplicationAllowedNewLicenceID
		END

		SET @Count = @Count + 1

		IF @Count = 50
		BEGIN
			WAITFOR DELAY '00:00:01'
			SET @Count = 0
		END

		FETCH NEXT FROM allowednewTypeCursor
		INTO @ApplicationAllowedNewLicenceID,         @IdNumberOrBusinessRegistrationNumber,         @AssociationID,         @RegistrationNumber,         @ApplicationAllowedNewLicenceTypeID, @AllowedNewCreatedDate;
	END;

	CLOSE allowednewTypeCursor;
	DEALLOCATE allowednewTypeCursor;

END



