CREATE OR ALTER FUNCTION [Verified].[Fn_GetLicenceExceptions]
(
	@LicenceID BIGINT
)
-- SELECT [Verified].[Fn_GetLicenceExceptions](2679116)
RETURNS VARCHAR(2000)
AS
BEGIN
	DECLARE @LicenceExceptions VARCHAR(2000) = ''
	DECLARE @ApplicationDetailID BIGINT
	DECLARE @ApplicationRequestTypeID BIGINT
	DECLARE @LicenceStatusID SMALLINT
	DECLARE @OperatingLicenceNumber VARCHAR(20)
	DECLARE @MemberID BIGINT
	DECLARE @VehicleID BIGINT
	DECLARE @CertficateNumber VARCHAR(20)
	DECLARE @OriginatingLicenceNumber VARCHAR(20)
	DECLARE @VehicleTypeID BIGINT
	DECLARE @OriginalOLASNumber VARCHAR(20)
	DECLARE @OriginalLTPSPermitNumber VARCHAR(20)
	DECLARE @AssociationID BIGINT
	DECLARE @ApplicationWorkflowInstanceID BIGINT
	DECLARE @IdNumber VARCHAR(20)
	DECLARE @ApplicantIDNumber VARCHAR(20)
	DECLARE @IncludeRenewalType BIT
	DECLARE @IncludeCovType BIT
	DECLARE @IncludeIncreaseInCapacityType BIT
	DECLARE @IncludeDeceasedTransferType BIT
	DECLARE @IncludeNormalTransferType BIT
	DECLARE @IncludeNewOperatingLicenceType BIT

	SELECT 
		@ApplicationDetailID = L.ApplicationDetailID,
		@MemberID = MemberID, 
		@VehicleID = VehicleID, 
		@OperatingLicenceNumber = App.Fn_StripVersionNumber(OperatingLicenceNumber,'/'), 
		@CertficateNumber = CertificateNumber, 
		@LicenceStatusID = LicenceStatusID, 
		@OriginalOLASNumber = OriginalOLASNumber, 
		@OriginalLTPSPermitNumber = OriginalLTPSPermitNumber,
		@IncludeRenewalType = Art.IncludeRenewalType,
		@IncludeCovType = Art.IncludeCovType, 
		@IncludeIncreaseInCapacityType = Art.IncludeIncreaseInCapacityType, 
		@IncludeDeceasedTransferType = Art.IncludeDeceasedTransferType, 
		@IncludeNormalTransferType = Art.IncludeNormalTransferType, 
		@IncludeNewOperatingLicenceType = Art.IncludeNewOperatingLicenceType
	FROM Verified.Licences L INNER JOIN App.ApplicationDetails Ad 
	ON Ad.ApplicationDetailID = L.ApplicationDetailID INNER JOIN Md.ApplicationRequestTypes Art ON Art.ApplicationRequestTypeID = Ad.ApplicationRequestTypeID
	WHERE LicenceID = @LicenceID

	SELECT @ApplicantIDNumber = IDNumberOrBusinessRegistrationNo
	FROM App.ApplicationApplicantDetails
	WHERE ApplicationDetailID = @ApplicationDetailID

	SELECT @VehicleTypeID = LicenceVehicleTypeID
	FROM Verified.Licences
	WHERE LicenceID = @LicenceID

	SELECT @AssociationID = VerifiedAssociationID
	FROM app.ApplicationDetails
	WHERE ApplicationDetailID = @ApplicationDetailID

	IF @LicenceStatusID = 7 /*cancelled*/
	BEGIN
		RETURN @LicenceExceptions --RETURN a blank string as no exceptions have  to be calculated FOR cancelled licences
	END

	IF EXISTS(SELECT TOP 1 1 FROM App.ApplicationDetails WHERE ApplicationDetailID = @ApplicationDetailID)
	BEGIN
		/*
		Make sure related application is in a completed state
		*/
		IF NOT EXISTS(SELECT TOP 1 1 FROM App.ApplicationDetails INNER JOIN wf.WorkflowInstances ON WorkflowInstances.WorkflowInstanceID = ApplicationDetails.WorkflowInstanceID  WHERE ApplicationDetailID = @ApplicationDetailID AND WorkflowInstanceStatusID = 2) --2=completed 
		BEGIN
			SET @LicenceExceptions = @LicenceExceptions + 'Related application is not in a completed status,'
		END
		ELSE 
		BEGIN
			/*
			Make sure all the required steps was completed
			*/
			IF EXISTS(
				SELECT TOP 1 1
				FROM Wf.WorkflowInstanceSteps Wis
					INNER JOIN Wf.WorkflowTemplateSteps Wts ON Wts.WorkflowTemplateStepID = Wis.WorkflowTemplateStepID
				WHERE WorkflowInstanceID = @ApplicationWorkflowInstanceID
					AND Wts.WorkflowTemplateStepTypeID <> 3 /*branch*/
					AND IsRequired = 1
					AND Wis.WorkflowTemplateStepID NOT IN
					(
						SELECT WorkflowTemplateStepID
						FROM Wf.WorkflowInstanceTasks
						WHERE WorkflowInstanceID = @ApplicationWorkflowInstanceID
							AND WorkflowInstanceTaskStatusID IN (2 /*approved*/, 4 /*Completed*/, 12 /*Override*/)
					)
				)
			BEGIN
				SET @LicenceExceptions = @LicenceExceptions + 'Not all the required steps on the application was completed successfully,'
			END
		END	
	END
	ELSE
	BEGIN
		SET @LicenceExceptions = @LicenceExceptions + 'Related application not found,'
	END

	IF EXISTS(SELECT TOP 1 1 FROM Verified.Members WHERE MemberID = @MemberID)
	BEGIN		
		SELECT @IdNumber = IDNumberOrBusinessRegistrationNo
		FROM Verified.Members
		WHERE MemberID = @MemberID

		IF ISNULL(@IdNumber,'') <> ''
		BEGIN
			/*
			Make sure the member id is unique in the verified members database
			*/
			IF EXISTS(SELECT TOP 1 1 FROM Verified.Members WHERE IDNumberOrBusinessRegistrationNo LIKE @IdNumber AND MemberID <> @MemberID)
			BEGIN
				SET @LicenceExceptions = @LicenceExceptions + 'No id number can also be found on another member,'
			END
		END
		ELSE
		BEGIN
			SET @LicenceExceptions = @LicenceExceptions + 'No id number specified for member,'
		END
	END
	ELSE
	BEGIN
		SET @LicenceExceptions = @LicenceExceptions + 'No member linked to the licence,'
	END

	DECLARE @VIN VARCHAR(150)
	DECLARE @EngineNumber VARCHAR(150)
	DECLARE @IgnoreVinException BIT = 0
	DECLARE @VehicleRegistrationNumber VARCHAR(100)

	IF ISNULL(@VehicleID,0) = 0
	BEGIN
		SET @LicenceExceptions = @LicenceExceptions + 'No vehicle linked to the licence,'
	END

	IF @OperatingLicenceNumber NOT LIKE '%T%' /*don't validate the vehicle for temporary licences*/
	BEGIN	
		IF ISNULL(@VehicleID,0) <> 0 /*vehicle specified*/
		BEGIN			
			SELECT @VIN = VIN, @EngineNumber = EngineNumber, @VehicleRegistrationNumber = RegistrationNumber, @IgnoreVinException = ISNULL(IgnoreVinException,0)
			FROM Verified.Vehicles
			WHERE VehicleID = @VehicleID

			IF ISNULL(@VIN,'') <> '' AND @VIN NOT LIKE '%_delinked%'
			BEGIN						
				IF System.Fn_IsValidVin(@VIN) = 0 AND @IgnoreVinException = 0
				BEGIN						
					SET @LicenceExceptions = @LicenceExceptions + 'The vehicle VIN appears to be incorrect,'
				END
			END	
		END

		IF @LicenceStatusID <> 1 /*not awaiting issuing*/ OR ISNULL(@VehicleID,0) <> 0 /*vehicle specified*/
		BEGIN	
			IF EXISTS(SELECT TOP 1 1 FROM Verified.Vehicles WHERE VehicleID = @VehicleID)
			BEGIN				
				SELECT @VIN = VIN, @EngineNumber = EngineNumber, @VehicleRegistrationNumber = RegistrationNumber, @IgnoreVinException = IgnoreVinException
				FROM Verified.Vehicles
				WHERE VehicleID = @VehicleID
		
				IF ISNULL(@VIN,'') <> '' AND ISNULL(@EngineNumber,'') <> ''
				BEGIN								
					IF @VIN LIKE '%_delinked%'
					BEGIN
						SET @LicenceExceptions = @LicenceExceptions + 'The vehicle VIN is in a delinked status,'
					END
					ELSE
					BEGIN															
						/*
						Make sure the vehicle is not currently linke to a different, active licence
						*/
						IF EXISTS(SELECT TOP 1 1 FROM Verified.Licences WHERE LicenceID <> @LicenceID AND VehicleID = @VehicleID AND LicenceStatusID IN (1,2,3,4,5) AND CleanOperatingLicenceNumber NOT LIKE @OperatingLicenceNumber)
						BEGIN
							SET @LicenceExceptions = @LicenceExceptions + 'The vehicle has also been linked to another licence,'
						END

						/*
						Make sure the VIN is unique in the verified vehicles database
						*/
						IF EXISTS(SELECT TOP 1 1 FROM Verified.Vehicles WHERE VIN LIKE @VIN AND VehicleID <> @VehicleID AND VehicleID IN (SELECT VehicleID FROM Verified.Licences WHERE VehicleID = @VehicleID AND LicenceStatusID IN (1,2,3,4,5)))
						BEGIN
							SET @LicenceExceptions = @LicenceExceptions + 'The VIN number has also been linked to another vehicle,'
						END

						/*
						Make sure the Engine number is unique in the verified vehicles database
						*/
						IF EXISTS(SELECT TOP 1 1 FROM Verified.Vehicles WHERE EngineNumber LIKE @EngineNumber AND VehicleID <> @VehicleID AND VehicleID IN (SELECT VehicleID FROM Verified.Licences WHERE VehicleID = @VehicleID AND LicenceStatusID IN (1,2,3,4,5)))
						BEGIN
							SET @LicenceExceptions = @LicenceExceptions + 'The Engine number has also been linked to another vehicle,'
						END
					END			
				END
				ELSE
				BEGIN
					SET @LicenceExceptions = @LicenceExceptions + 'No VIN or engine number specified for vehicle,'
				END

				/*
				Make sure all the requied vehicle details have been captured
				*/
				IF EXISTS(SELECT TOP 1 1 FROM Verified.Vehicles WHERE VehicleID = @VehicleID AND 
					(
						ISNULL(RegistrationNumber,'') = '' OR
						ISNULL(SeatedCapacity, 0) = 0 OR
						ISNULL(GrossMass, 0) = 0 OR
						ISNULL(VehicleMakeID, 0) = 0 OR
						ISNULL(VehicleTypeID, 0) = 0 OR
						ISNULL(CarryingCapacityID, 0) = 0 OR
						ISNULL(YearOfManufacture, '') = ''
					)
				)
				BEGIN
					SET @LicenceExceptions = @LicenceExceptions + 'Not all the vehicle details have been specified,'
				END
			END
			ELSE
			BEGIN
				SET @LicenceExceptions = @LicenceExceptions + 'No vehicle linked to the licence,'
			END
		END
	END


	/*Check if the application is allowed if it is a new OL*/
	IF @ApplicationRequestTypeID = 1 /*new ol*/
	BEGIN				
		IF @VehicleTypeID = 10 /*midibus*/ OR @VehicleTypeID = 2 /*minibus*/
		BEGIN
			SET @VehicleRegistrationNumber = UPPER(REPLACE(@VehicleRegistrationNumber, ' ',''))

			----IF ISNULL(@VehicleRegistrationNumber,'') <> ''
			----BEGIN
			----	IF NOT EXISTS(SELECT * FROM Purification.ApplicationAllowedNewLicences WHERE AssociationID = @AssociationID AND IdNumberOrBusinessRegistrationNumber = @IDNumber AND UPPER(REPLACE(RegistrationNumber, ' ','')) LIKE @VehicleRegistrationNumber)
			----	BEGIN
			----		SET @LicenceExceptions = @LicenceExceptions + 'The association/member/vehicle registration number has not been enabled for new operating licence applications,'
			----	END

			----	IF EXISTS(
			----		SELECT *
			----		FROM App.ApplicationDetails Ard
			----			INNER JOIN App.ApplicationApplicantDetails Aad ON Aad.ApplicationDetailID = Ard.ApplicationDetailID
			----			INNER JOIN App.ApplicationVehicleDetails Avd ON Avd.ApplicationDetailID = Ard.ApplicationDetailID
			----			INNER JOIN Wf.WorkflowInstances Wi ON Wi.WorkflowInstanceID = Ard.WorkflowInstanceID
			----		WHERE Aad.ApplicationDetailID <> @ApplicationDetailID
			----			AND Ard.ApplicationRequestTypeID = 1 /*New operating licence*/
			----			AND  REPLACE(Aad.IDNumberOrBusinessRegistrationNo, ' ','') LIKE REPLACE(@IdNumber,' ','')
			----			AND REPLACE(Avd.VehicleRegistrationNumber,' ','') LIKE REPLACE(@VehicleRegistrationNumber,' ','')
			----			AND Wi.WorkflowInstanceStatusID IN (1 /*In Progress*/)
			----		)
			----		BEGIN
			----			SET @LicenceExceptions = @LicenceExceptions + 'The association/member/vehicle registration number is linked to another in-progress application,'
			----		END 

			----	IF EXISTS(
			----		SELECT *
			----		FROM Verified.Vw_OperatingLicenceDetails
			----		WHERE ApplicationDetailID <> @ApplicationDetailID
			----			AND ApplicationRequestTypeID = 1 /*New operating licence*/
			----			AND  REPLACE(IDNumberOrBusinessRegistrationNo, ' ','') LIKE REPLACE(@IdNumber,' ','')
			----			AND REPLACE(RegistrationNumber,' ','') LIKE REPLACE(@VehicleRegistrationNumber,' ','')
			----			AND LicenceStatus <> 'Cancelled'
			----		)
			----		BEGIN
			----			SET @LicenceExceptions = @LicenceExceptions + 'The association/member/vehicle registration number is linked to another in-progress application,'
			----		END 			               				
			----END        		
		END	
	END

	/*
	Check for duplcate certificate numbers
	*/
	IF ISNULL(@CertficateNumber,'') <> ''
	BEGIN
		IF EXISTS(SELECT TOP 1 1 FROM Verified.Licences WHERE CertificateNumber LIKE @CertficateNumber AND LicenceStatusID IN (1,2,3,4,5) AND LicenceID <> @LicenceID)
		BEGIN
			SET @LicenceExceptions = @LicenceExceptions + 'The certificate number has been used on another licence,'		
		END
	END

	/*
	Check that the issue and expire dates are present is after the issue date, and after today
	*/
	IF EXISTS(SELECT TOP 1 1 FROM Verified.Licences WHERE LicenceID = @LicenceID AND (DateOfIssue IS NULL OR DateOfExpiry IS NULL) AND LicenceStatusID IN (1,2,3,4,5))
	BEGIN
		SET @LicenceExceptions = @LicenceExceptions + 'The issued or expire date is missing,'
	END
	ELSE
	BEGIN
		IF EXISTS(SELECT * FROM Verified.Licences WHERE LicenceID = @LicenceID AND (DateOfExpiry < DateOfIssue) AND LicenceStatusID IN (1,2,3,4,5))
		BEGIN
			SET @LicenceExceptions = @LicenceExceptions + 'The expiry date is after the issue date,'
		END
	END

	/*
	Check that the originating licence has not been used before
	*/
	----IF LEN(@OriginatingLicenceNumber) > 0 AND @OriginatingLicenceNumber NOT LIKE 'LG%'
	----BEGIN
	----	IF @ApplicationRequestTypeID <> 11
	----	BEGIN
	----		IF EXISTS(
	----			SELECT * 
	----			FROM App.ApplicationExistingLicences  Ael
	----				INNER JOIN App.ApplicationDetails Ard ON Ael.ApplicationDetailID = Ard.ApplicationDetailID
	----				INNER JOIN Wf.WorkflowInstances Wi ON Ard.WorkflowInstanceID = Wi.WorkflowInstanceID
	----			WHERE Ael.ApplicationDetailID <> @ApplicationDetailID
	----				AND Wi.WorkflowInstanceStatusID NOT IN (3,4)
	----					AND Ard.ApplicationRequestTypeID <> 11 --Temporary (Special) licence
	----					AND Ard.ApplicationRequestTypeID <> 27 --Temporary Replacements
	----				AND OperatingLicenceNumber  NOT LIKE 'LG%'
	----				AND App.Fn_StripVersionNumber(OperatingLicenceNumber,'/') LIKE @OriginatingLicenceNumber			
	----				)
	----		BEGIN
	----			SET @LicenceExceptions = @LicenceExceptions + 'The originating licence has also been used on another application,'
	----		END
	----	END
	----END

	/*
	Check that the OriginalOLASNumber has not been used on any other licences
	--*/
	--IF ISNULL(@OriginalOLASNumber,'') <> ''
	--BEGIN
	--	IF EXISTS(SELECT TOP 1 1 FROM Verified.Licences WHERE LicenceID <> @LicenceID AND OperatingLicenceNumber NOT LIKE 'LT%' AND LicenceStatusID <> 6 /*archived*/ AND LicenceStatusID <> 7 /*cancelled*/ AND App.Fn_StripVersionNumber(OriginalOLASNumber,'/') LIKE App.Fn_StripVersionNumber(@OriginalOLASNumber,'/'))
	--	BEGIN
	--		SET @LicenceExceptions = @LicenceExceptions + 'The originating OL number has been used on another licence,'		
	--	END
	--END

	/*
	Check that the OriginalPermitNumber has not been used on any other licences
	*/
	--IF ISNULL(@OriginalLTPSPermitNumber,'') <> ''
	--BEGIN
	--	IF EXISTS(SELECT TOP 1 1 FROM Verified.Licences WHERE LicenceID <> @LicenceID AND OperatingLicenceNumber NOT LIKE 'LT%' AND LicenceStatusID <> 6 /*archived*/  AND LicenceStatusID <> 7 /*cancelled*/ AND App.Fn_StripVersionNumber(OriginalLTPSPermitNumber,'/') LIKE App.Fn_StripVersionNumber(@OriginalLTPSPermitNumber,'/'))
	--	BEGIN
	--		SET @LicenceExceptions = @LicenceExceptions + 'The originating permit number has been used on another licence,'		
	--	END
	--END

	/*
	Check if we have more than one issue workflow in progress for the same licence
	*/
	--IF @OriginatingLicenceNumber LIKE 'LG%'
	--BEGIN
		DECLARE @IssueCount INT = 0

		SELECT @IssueCount = COUNT(1)
		FROM Verified.Licences L
		WHERE LicenceStatusID = 1 /*awaiting issue*/ AND CleanOperatingLicenceNumber LIKE App.Fn_StripVersionNumber(@OriginatingLicenceNumber,'/')
		GROUP BY CleanOperatingLicenceNumber

		IF @IssueCount > 1
		BEGIN
			SET @LicenceExceptions = @LicenceExceptions + 'The licence is linked to more than one in-progress issue workflow,'		
		END
	--END

	/*
	Check that the originating licence is not on the blocked list
	*/
	IF LEN(@OriginatingLicenceNumber) > 0
	BEGIN
		IF EXISTS(SELECT TOP 1 1 FROM Purification.ApplicationBlockedLicences WHERE [App].[Fn_StripVersionNumber](OperatingLicenceNumber, '/') LIKE [App].[Fn_StripVersionNumber](@OriginatingLicenceNumber, '/') AND IsCancelled = 0)
		BEGIN
			SET @LicenceExceptions = @LicenceExceptions + 'The originating licence is currently blocked,'
		END
	END

	/*
	Check that the ID number is not on the blocked list
	*/
	IF LEN(@ApplicantIDNumber) > 0
	BEGIN
		IF EXISTS(SELECT TOP 1 1 FROM Purification.ApplicationBlockedIdNumbers WHERE IDNumber LIKE @ApplicantIDNumber AND IsCancelled = 0)
		BEGIN
			SET @LicenceExceptions = @LicenceExceptions + 'The ID number is currently blocked,'
		END
	END

	/*
	Check if we have at least one route linked to the licence
	*/
	IF @ApplicationRequestTypeID <> 11  --temp/special licence
	BEGIN
		IF NOT EXISTS(SELECT TOP 1 1 FROM Verified.LicenceRoutes Lr INNER JOIN Verified.Routes R ON R.RouteID = Lr.RouteID  WHERE Lr.LicenceID = @LicenceID AND R.RouteStatusID <> 4 /*cancelled*/)
		BEGIN
			SET @LicenceExceptions = @LicenceExceptions + 'No active routes linked to the licence,'
		END
	END

	/*
	Check if we have at least one service type linked to the licence
	*/
	IF @ApplicationRequestTypeID <> 11 OR (@ApplicationRequestTypeID = 11 /*LG*/) --temp/special licence
	BEGIN
		IF NOT EXISTS(SELECT TOP 1 1 FROM Verified.LicenceServiceDetails WHERE LicenceID = @LicenceID)
		BEGIN
			SET @LicenceExceptions = @LicenceExceptions + 'No service types linked to the licence,'
		END
	END

	/*
	Check to make sure the mode and service types are correct
	*/
	IF @ApplicationRequestTypeID <> 11
	BEGIN
		IF EXISTS(	
			SELECT TOP 1 1 
			FROM Verified.LicenceServiceDetails
			WHERE LicenceID = @LicenceID
				AND ServiceTypeID NOT IN 
				(
					SELECT ServiceTypeID FROM App.Fn_AllowedServiceTypesForVehicleType(@VehicleTypeID)
				) 
		)
		BEGIN
			SET @LicenceExceptions = @LicenceExceptions + 'The service types are not valid for the specified mode,'
		END
	END

	/*
	Check to make sure the vehicle type is valid for the mode
	*/
	----IF ISNULL(@VehicleID,-1) <> -1
	----BEGIN
	----	DECLARE @VehicleTypeID SMALLINT

	----	SELECT @VehicleTypeID = VehicleTypeID
	----	FROM Verified.Vehicles 
	----	WHERE VehicleID = @VehicleID

	----	IF ISNULL(@VehicleTypeID,-1) <> -1
	----	BEGIN
	----		IF 
	----			(@ModeID = 1 /*Bus*/ AND @VehicleTypeID <> 3 /*BUS*/) OR
	----			(@ModeID = 2 /*Midibus*/ AND @VehicleTypeID <> 10 /*MIDIBUS*/) OR
	----			(@ModeID = 3 /*Minibus Taxi*/ AND @VehicleTypeID <> 2 /*MINIBUS*/) OR
	----			(@ModeID = 4 /*Metered Taxi*/ AND (@VehicleTypeID NOT IN (1 /*MOTORCAR*/, 11 /*OTHER*/))) OR
	----			(@ModeID = 5 /*Other*/ AND @VehicleTypeID <> 11 /*OTHER*/) 
	----		BEGIN
	----			SET @LicenceExceptions = @LicenceExceptions + 'The vehicle type is not valid for the specified mode,'
	----		END
	----	END
	----END

	/*
	Check if the applicant and existing licence holder is different if it is a transfer application
	*/
	IF @IncludeDeceasedTransferType = 1 OR @IncludeNormalTransferType = 1
	BEGIN
		DECLARE @ApplicantHolderID VARCHAR(255)
		DECLARE @ExistingLicenceHolderID VARCHAR(255)

		SELECT @ApplicantHolderID = IDNumberOrBusinessRegistrationNo 
		FROM App.ApplicationApplicantDetails 
		WHERE ApplicationDetailID = @ApplicationDetailID
	
		SELECT @ExistingLicenceHolderID = IDNumberOrBusinessRegistrationNo 
		FROM App.ApplicationPreviousLicenceHolder 
		WHERE ApplicationDetailID = @ApplicationDetailID

		IF @ApplicantHolderID = @ExistingLicenceHolderID
		BEGIN
			SET @LicenceExceptions = @LicenceExceptions + 'No applicant change on transfer application,'
		END	
	END

	IF LEN(ISNULL(@LicenceExceptions,'')) <> 0
	BEGIN
		SET @LicenceExceptions = SUBSTRING(@LicenceExceptions, 1, LEN(@LicenceExceptions)-1)
	END

	RETURN @LicenceExceptions
END
GO