CREATE OR ALTER FUNCTION [App].[Fn_ApplicationStep3Checks]
(
	@ApplicationDetailID BIGINT,
	@UserID BIGINT
)
-- SELECT * FROM [App].[Fn_ApplicationStep3Checks](1584037, 1001)
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 @IncludeRenewalType BIT
	DECLARE @IncludeCovType BIT
	DECLARE @IncludeIncreaseInCapacityType BIT
	DECLARE @IncludeDeceasedTransferType BIT
	DECLARE @IncludeNormalTransferType BIT
	DECLARE @IncludeNewOperatingLicenceType BIT
	DECLARE @ApplicationLicenceID BIGINT

	SELECT @ApplicationRequestTypeID = Ard.ApplicationRequestTypeID, 
		@DivisionTypeID = Ard.OfficeLocationDivisionTypeID, 
		@ProvinceID = Ard.ApplicationProvinceID,
		@IncludeRenewalType = Art.IncludeRenewalType,
		@IncludeCovType = Art.IncludeCovType, 
		@IncludeIncreaseInCapacityType = Art.IncludeIncreaseInCapacityType, 
		@IncludeDeceasedTransferType = Art.IncludeDeceasedTransferType, 
		@IncludeNormalTransferType = Art.IncludeNormalTransferType, 
		@IncludeNewOperatingLicenceType = Art.IncludeNewOperatingLicenceType
	FROM App.ApplicationDetails Ard INNER JOIN App.ApplicationApplicantDetails Aad 
	ON Ard.ApplicationDetailID = Aad.ApplicationDetailID INNER JOIN Md.ApplicationRequestTypes Art 
	ON Art.ApplicationRequestTypeID = Ard.ApplicationRequestTypeID
	WHERE Ard.ApplicationDetailID = @ApplicationDetailID 

	IF @DivisionTypeID <> 3 /*TAT*/
	BEGIN
		/****************************************************************************
		Application NPTR Details - Vehicle particulars
		****************************************************************************/		
		IF @ApplicationRequestTypeID = 31 OR @ApplicationRequestTypeID = 38   /*New accreditation*/
		BEGIN
			IF EXISTS
				(
					SELECT TOP 1 1 
					FROM App.ApplicationNPTRDetails
					WHERE ApplicationDetailID = @ApplicationDetailID 
					AND 
					(
						(
					
							ISNULL(TotalMotorcar,0) = 0 AND
							ISNULL(TotalMinibus,0) = 0 AND
							ISNULL(TotalMidibus,0) = 0 AND
							ISNULL(TotalBus,0) = 0 AND
							ISNULL(TotalOther,0) = 0
						)
						OR
						(
							(ISNULL(TotalMotorcar,0) > 0 AND ISNULL(MotorcarCapacity,0) = 0) OR
							(ISNULL(TotalMinibus,0) > 0 AND ISNULL(MinibusCapacity,0) = 0) OR
							(ISNULL(TotalMidibus,0) > 0 AND ISNULL(MidibusCapacity,0) = 0) OR
							(ISNULL(TotalBus,0) > 0 AND ISNULL(BusCapacity,0) = 0) OR
							(ISNULL(TotalOther,0) > 0 AND ISNULL(OtherCapacity,0) = 0)
						)
					)
				) 
			BEGIN
				INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
				VALUES('Is the vehicle particulars section complete?', 0, '',1)			
			END
			ELSE
			BEGIN
				INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
				VALUES('Is the vehicle particulars section complete?', 1, '',1)			
			END
		END

		/****************************************************************************
		Application NPTR Details - vehicle maintenance
		****************************************************************************/	
		IF @ApplicationRequestTypeID = 31 OR @ApplicationRequestTypeID = 38  /*New accreditation*/
		BEGIN
			IF EXISTS
				(
					SELECT TOP 1 1 
					FROM App.ApplicationNPTRDetails
					WHERE ApplicationDetailID = @ApplicationDetailID AND 
					(					
						(ISNULL(IsMaintenanceProgrammeInPlace, -1) = 1 AND ISNULL(MaintenanceProgrammeDetail, '') = '') OR
						(ISNULL(IsVehiclesServicedAtGarage , -1 ) = 0 AND  ISNULL(ServiceAddress, '') = '' ) OR
						(ISNULL(IsVehiclesServicedAtGarage , -1 ) = 1 AND (ISNULL(GarageName, '') = '' OR  ISNULL(ServiceAddress, '') = '') )
					)
				) 
			BEGIN
				INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
				VALUES('Is the vehicle maintenance section complete?', 0, '',1)			
			END
			ELSE
			BEGIN
				INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
				VALUES('Is the vehicle maintenance section complete?', 1, '',1)			
			END
		END

		/****************************************************************************
		Application NPTR Details - livery and sigange
		****************************************************************************/	
		IF @ApplicationRequestTypeID = 31 OR @ApplicationRequestTypeID = 38  /*New accreditation*/
		BEGIN
			IF EXISTS
				(
					SELECT TOP 1 1  
					FROM App.ApplicationNPTRDetails
					WHERE ApplicationDetailID = @ApplicationDetailID AND 
					(					
						ISNULL(LiveryDetails, '') = ''
					)
				) 
			BEGIN
				INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
				VALUES('Is the livery and signage section complete?', 0, '',1)			
			END
			ELSE
			BEGIN
				INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
				VALUES('Is the livery and signage section complete?', 1, '',1)			
			END
		END

		/****************************************************************************
		Licence Details
		****************************************************************************/	
		IF @ApplicationRequestTypeID = 34  /*Upliftment Extension*/
		BEGIN
			IF EXISTS (
				SELECT TOP 1 1  
				FROM App.ApplicationLicences
				WHERE ApplicationDetailID = @ApplicationDetailID AND ISNULL(ReasonForAmendment,'') = '' 
			) 
			BEGIN			
				INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
				VALUES('Is the reason for amendment complete?', 0, '',1)						
			END
			ELSE 
			BEGIN
				INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
				VALUES('Is the reason for amendment complete?', 1, '',1)	
			END

			IF EXISTS (
				SELECT TOP 1 1  
				FROM App.ApplicationLicences
				WHERE ApplicationDetailID = @ApplicationDetailID AND ISNULL(OperatingLicenceDocID,-1) = -1 
			) 
			BEGIN			
				INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
				VALUES('Is the operating licence document uploaded?', 0, '',1)						
			END
			ELSE 
			BEGIN
				INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
				VALUES('Is the operating licence document uploaded?', 1, '',1)	
			END

			IF EXISTS (
				SELECT TOP 1 1  
				FROM App.ApplicationLicences
				WHERE ApplicationDetailID = @ApplicationDetailID AND ISNULL(ApplicationFormDocID,-1) = -1
			) 
			BEGIN			
				INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
				VALUES('Is the application form uploaded?', 0, '',1)						
			END
			ELSE 
			BEGIN
				INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
				VALUES('Is the application form uploaded?', 1, '',1)	
			END
		END

		/****************************************************************************
		Authority Details
		****************************************************************************/	
		IF @ApplicationRequestTypeID IN (2,36,37)  /*Add Existing Authorisations/Routes, Add New Authorisations/Routes, Amend Authorisations/Routes*/
		BEGIN
			IF EXISTS
			(
				SELECT TOP 1 1 
				FROM App.ApplicationLicences
				WHERE ApplicationDetailID = @ApplicationDetailID AND 
				(
					(ISNULL(ApplicationFormDocID,-1) = -1)  					
				)
			) 
			BEGIN			
				INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
				VALUES('Is the authority detail section complete?', 0, '',1)						
			END
			ELSE 
			BEGIN
				-- Check if at least one route is selected for Additional Authority applications
				IF NOT EXISTS
				(
					SELECT TOP 1 1 
					FROM App.ApplicationLicences Al
						INNER JOIN App.ApplicationLicenceRouteDetails Alrd ON Alrd.ApplicationLicenceID = Al.ApplicationLicenceID
					WHERE Al.ApplicationDetailID = @ApplicationDetailID
						AND Alrd.RouteChangeTypeID <> 4  -- Exclude routes marked for removal
				)
				BEGIN
					INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
					VALUES('Are routes selected for the additional authority request?', 0, 'At least one route must be selected for Additional Authority applications. Routes marked for removal are not counted.',1)
				END
				ELSE
				BEGIN
					INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
					VALUES('Are routes selected for the additional authority request?', 1, '',1)
					INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
					VALUES('Is the authority detail section complete?', 1, '',1)	
				END
			END
		END
	
		/****************************************************************************
		Temporary licence details
		****************************************************************************/	
		IF @ApplicationRequestTypeID = 11 /*temporary licence*/
		BEGIN
			IF EXISTS
			(
				SELECT TOP 1 1 
				FROM App.ApplicationLicences
				WHERE ApplicationDetailID = @ApplicationDetailID AND 
				(
					ISNULL(SpecialLicenceDetails,'') = '' OR 
					ISNULL(SpecialLicenceDateFrom,'') = '' OR 
					ISNULL(SpecialLicenceDateTo,'') = '' 
				)
			) 
			BEGIN			
				INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
				VALUES('Is the temporary licence details complete?', 0, '',1)						
			END
			ELSE 
			BEGIN
				INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
				VALUES('Is the temporary licence details complete?', 1, '',1)	

				IF EXISTS
				(
					SELECT TOP 1 1  
					FROM App.ApplicationLicences
					WHERE ApplicationDetailID = @ApplicationDetailID AND 
					(					
						SpecialLicenceDateFrom < GETDATE() OR
						SpecialLicenceDateFrom > SpecialLicenceDateTo
					)
				) 
				BEGIN			
					INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
					VALUES('Is the temporary licence dates correct?', 0, 'The temporary licence start date must be in the future, and before or on the end date',1)						
				END
				ELSE 
				BEGIN
					INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
					VALUES('Is the temporary licence dates correct?', 1, '',1)	
				END 
			END 		
		END

		/****************************************************************************
		Licence Service Details
		****************************************************************************/
		IF (@ApplicationRequestTypeID NOT IN (34) /*Upliftment Extension)*/)
		BEGIN
			IF EXISTS
				(
					SELECT TOP 1 1
					FROM App.ApplicationLicences
					WHERE ApplicationDetailID = @ApplicationDetailID AND 
					(
						(ISNULL(VehicleTypeID,-1) = -1) 
					) 				
				) 
			BEGIN			
				INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
				VALUES('Is the licence vehicle type complete?', 0, '',1)						
			END
			ELSE
			BEGIN
				--/****************************************************************************
				--Licence Service Details - make sure the service type is valid for the selected vehicle type
				--****************************************************************************/
				--IF EXISTS(	
				--	SELECT TOP 1 1 
				--	FROM App.ApplicationLicences Al
				--		INNER JOIN App.ApplicationLicenceServiceDetails Alsd ON Alsd.ApplicationLicenceID = Al.ApplicationLicenceID					
				--	WHERE Al.ApplicationDetailID = @ApplicationDetailID
				--		AND ServiceTypeID NOT IN 
				--		(
				--			SELECT ServiceTypeID FROM Md.ServiceVehicleTypes WHERE VehicleTypeID = Al.VehicleTypeID
				--		)
				--)
				--BEGIN
				--	INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
				--	VALUES('Is the licence service valid for the selected vehicle type?', 0, '',1)
				--END
				--ELSE
				--BEGIN
				--	INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
				--	VALUES('Is the licence service valid for the selected vehicle type?', 1, '',1)
				--END

				/****************************************************************************
				Licence Service Details - check if at least one service type is selected
				****************************************************************************/
				IF EXISTS(SELECT TOP 1 1 FROM App.ApplicationLicences WHERE ApplicationDetailID = @ApplicationDetailID AND ApplicationLicenceID NOT IN (SELECT ApplicationLicenceID FROM App.ApplicationLicenceServiceDetails))
				BEGIN
					INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
					VALUES('Is the licence service details section complete?', 0, '',1)			
				END
				ELSE
				BEGIN
					INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
					VALUES('Is the licence service details section complete?', 1, '',1)			
				END		
			
				/****************************************************************************
				Licence Service Details - check if the areas of operation is complete
				****************************************************************************/
				-- Check if there are route details to verify before checking the route details
				IF EXISTS (SELECT TOP 1 1 FROM App.ApplicationLicences INNER JOIN App.ApplicationLicenceRouteDetails 
					ON ApplicationLicences.ApplicationLicenceID = ApplicationLicenceRouteDetails.ApplicationLicenceID
					WHERE ApplicationLicences.ApplicationDetailID = @ApplicationDetailID)
				BEGIN
					IF EXISTS(SELECT TOP 1 1 FROM App.ApplicationLicences 
						WHERE ApplicationDetailID = @ApplicationDetailID AND ApplicationLicenceID NOT IN 
							(
								SELECT ApplicationLicenceID 
								FROM App.ApplicationLicenceRouteDetails 
								WHERE ISNULL(RouteOrigin,'') <> '' AND ((ISNULL(RouteDestination,'') <> '' AND ServiceTypeID <> 10) OR (ISNULL(RouteDestination,'') = '' AND ServiceTypeID = 10 )) AND ISNULL(RouteDescription,'') <> ''
							)
						)
					BEGIN
						INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
						VALUES('Is the licence service area of operation/route section complete?', 0, '',1)			
					END
					ELSE
					BEGIN
						INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
						VALUES('Is the licence service area of operation/route section complete?', 1, '',1)			
					END	
				END
			END
		END
		
		/****************************************************************************
		Ensure that the vehicle type has been changed if it's an increase in carrying capacity application
		****************************************************************************/	
		--IF @IncludeIncreaseInCapacityType = 1
		--BEGIN
		--	IF EXISTS(SELECT TOP 1 1 FROM App.ApplicationLicences WHERE ApplicationDetailID = @ApplicationDetailID AND VehicleTypeID = CurrentvehicleTypeID)
		--	BEGIN
		--		INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
		--		VALUES('Is the vehicle type different from what it used to be?', 0, '',1)			
		--	END		
		--END

		/****************************************************************************
		Vehicle details
		****************************************************************************/		
		IF EXISTS
			(
				SELECT TOP 1 1 
				FROM App.ApplicationLicences Al
					INNER JOIN App.ApplicationLicenceVehicleDetails Alvd ON Alvd.ApplicationLicenceID = Al.ApplicationLicenceID
					INNER JOIN App.ApplicationLicenceServiceDetails Alsd ON Alsd.ApplicationLicenceID = Al.ApplicationLicenceID
				WHERE ApplicationDetailID = @ApplicationDetailID AND 
				(
					ISNULL(Alvd.IsStillToBeAcquired,-1) = -1
					OR
					(
						Alvd.IsStillToBeAcquired = 0 AND
						(
							(ISNULL(VehicleMakeID,-1) = -1) OR
							(ISNULL(Alvd.VehicleTypeID, -1) = -1) OR
							(ISNULL(Alvd.CarryingCapacityID, -1) = -1) OR
							(ISNULL(VIN, '') = '') OR
							(ISNULL(EngineNumber, '') = '') OR
							(ISNULL(YearOfManufacture, '') = '') OR
							(ISNULL(VehicleRegistrationNumber, '') = '')  OR 
							(ISNULL(SeatedCapacity, 0) = 0) OR
							(ISNULL(StandingCapacity, -1) = -1) OR
							(ISNULL(GrossMass, -1) = -1) OR
							(ISNULL(Tare, -1) = -1) OR
							--(ISNULL(CurrentOdometerReading, -1) = -1) OR
							(ISNULL(RoadworthyCertificateDocID, -1) = -1) OR
							((ISNULL(ProfessionalDriversPermitDocID, -1) = -1)  AND @DivisionTypeID != 2) OR
							--((ISNULL(RankPermitDocID, -1) = -1) AND Alsd.ServiceTypeID = 2 /*Unscheduled*/) OR
							(ISNULL(CertificateOfRegistrationDocID, -1) = -1)
						)
					)				
				)
			) 
		BEGIN
			INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
			VALUES('Is the licence vehicle details section complete?', 0, '',1)			
		END
		ELSE
		BEGIN
			--todo make sure vehicle type is valid for the licence vehicle type

			INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
			VALUES('Is the licence vehicle details section complete?', 1, '',1)			
		END
	END	

	SELECT TOP 1 @ApplicationLicenceID = Al.ApplicationLicenceID 
	FROM App.ApplicationLicences Al					
	WHERE Al.ApplicationDetailID = @ApplicationDetailID 

	/****************************************************************************
	Area of operation
	****************************************************************************/	
	IF (@ApplicationRequestTypeID = 1 OR @ApplicationRequestTypeID = 31) AND @ApplicationLicenceID IS NOT NULL
	BEGIN
		IF EXISTS (SELECT TOP 1 1
			FROM App.ApplicationLicenceRouteDetails Alrd 
			WHERE ApplicationLicenceID = @ApplicationLicenceID)
		BEGIN
			IF EXISTS (SELECT TOP 1 1
				FROM App.ApplicationLicenceRouteDetails Alrd 
				WHERE RouteDescription IS NULL AND ApplicationLicenceID = @ApplicationLicenceID)
			BEGIN
				INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
				VALUES('Are all the operating licence area of operation details captured?', 0, '',1)			
			END
			ELSE
			BEGIN
				INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
				VALUES('Are all the operating licence area of operation details captured?', 1, '',1)						
			END
		END
		ELSE
		BEGIN
			INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
			VALUES('Are all the operating licence area of operation details captured?', 0, '',1)	
		END
	END
			
	RETURN
END
GO


