USE [LegitimateNational]
GO
/****** Object:  StoredProcedure [App].[Pr_ApplicationLicenceRouteDescriptionCalculate]    Script Date: 2025/11/03 10:49:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [App].[Pr_ApplicationLicenceRouteDescriptionCalculate]
(
	@ApplicationLicenceRouteDetailID BIGINT,
	@UserID BIGINT
)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @ApplicationLicenceID BIGINT
	DECLARE @ApplicationDetailID BIGINT
	DECLARE @Description VARCHAR(MAX) = 'The conveyance of tourists from place of business situated at [[origin]] to places of interest or attraction [[destination]] and return.'
	
	SELECT @ApplicationLicenceID = Alrd.ApplicationLicenceID, @ApplicationDetailID = ApplicationDetailID
	FROM App.ApplicationLicenceRouteDetails Alrd
		INNER JOIN App.ApplicationLicences Al ON Al.ApplicationLicenceID = Alrd.ApplicationLicenceID
	WHERE ApplicationLicenceRouteDetailID = @ApplicationLicenceRouteDetailID

	/********************************************************************************************
	Get user details and perform security checks
	*********************************************************************************************/	
	DECLARE @EntityIDToCheck INT = 1022 /*ApplicationLicences*/
	DECLARE @ItemIDToCheck BIGINT = @ApplicationLicenceID
	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	
	
	IF @DivisionTypeID = 1 /*PRE*/
	BEGIN
		SET @Description = 'The conveyance of passengers from [[origin]] to [[destination]].'
	END
	
	/********************************************************************************************
	Update route details
	*********************************************************************************************/	
	DECLARE @RouteOrigin VARCHAR(MAX)
	DECLARE @RouteDestination VARCHAR(MAX)
	DECLARE @DestinationPlaces VARCHAR(MAX)

	DECLARE @IsOriginSameAsPhysicalAddress BIT
	DECLARE @IsDestinationArea BIT

	SELECT @RouteOrigin = RouteOrigin
		, @RouteDestination = RouteDestination
		, @IsOriginSameAsPhysicalAddress = IsOriginSameAsPhysicalAddress
		, @IsDestinationArea = IsDestinationArea
	FROM App.ApplicationLicenceRouteDetails
	WHERE ApplicationLicenceID = @ApplicationLicenceID

	IF(@IsOriginSameAsPhysicalAddress = 1)
	BEGIN
		SET @RouteOrigin = ISNULL((SELECT PhysicalAddress FROM App.ApplicationApplicantDetails WHERE ApplicationDetailID = @ApplicationDetailID),'')
	END 	

	SET @RouteOrigin = REPLACE(@RouteOrigin, CHAR(10), ' ')
	SET @RouteOrigin = REPLACE(@RouteOrigin, CHAR(13), '')

	IF(@IsDestinationArea = 1)
	BEGIN
		 DECLARE @RSA VARCHAR(MAX) = ''
		 DECLARE @Provinces VARCHAR(MAX) = ''
		 DECLARE @ProvinceList VARCHAR(MAX) = ''
		 DECLARE @Municipalities VARCHAR(MAX) = ''
		 DECLARE @MunicipalityList VARCHAR(MAX) = ''

		 /*************************************************************
		 Country
		 *************************************************************/
		 BEGIN
			 SELECT @RSA = ' within the boundries of ' + VP.Description 
			 FROM App.ApplicationLicenceRouteDetailPlaces P
				INNER JOIN Verified.Places VP ON VP.PlaceID = P.PlaceID
			 WHERE ApplicationLicenceRouteDetailID = @ApplicationLicenceRouteDetailID
			 AND VP.PlaceTypeID = 7 -- COUNTRY
		 END

		 /*************************************************************
		 Provinces
		 *************************************************************/
		 BEGIN
			 SELECT @ProvinceList = @ProvinceList + VP.Description + ', '
			 FROM App.ApplicationLicenceRouteDetailPlaces P
				INNER JOIN Verified.Places VP ON VP.PlaceID = P.PlaceID
			 WHERE ApplicationLicenceRouteDetailID = @ApplicationLicenceRouteDetailID
			 AND VP.PlaceTypeID = 8 -- PROVINCE
			 
			 
			 IF(ISNULL(@ProvinceList,'')<>'')
			 BEGIN
				SET @ProvinceList = LEFT(@ProvinceList, len(@ProvinceList)-1)
				IF @ProvinceList NOT LIKE '%,%'
				BEGIN
					SET @Provinces = ' within the ' + @ProvinceList +  ' province' 
				END
				ELSE
				BEGIN
					SET @Provinces = ' within the ' + @ProvinceList +  ' provinces' 
				END
			 END
		 END

		 /*************************************************************
		 MUNICIPALITIES
		 *************************************************************/
		 BEGIN
			 SELECT @MunicipalityList = @MunicipalityList + VP.Description + ', '
			 FROM App.ApplicationLicenceRouteDetailPlaces P
			 INNER JOIN Verified.Places VP ON VP.PlaceID = P.PlaceID
			 WHERE ApplicationLicenceRouteDetailID = @ApplicationLicenceRouteDetailID
			 AND VP.PlaceTypeID IN (9 /*METROPOLITAN MUNICIPALITY*/, 10 /*DISTRICT MUNICIPALITY */, 11 /*LOCAL MUNICIPALITY*/)

			 IF(ISNULL(@MunicipalityList,'')<>'')
			 BEGIN
				SET @MunicipalityList = LEFT(@MunicipalityList, len(@MunicipalityList)-1)
				IF @MunicipalityList NOT LIKE '%,%'
				BEGIN
					SET @Municipalities = ' within the ' + @MunicipalityList +  ' municipality' 
				END
				ELSE
				BEGIN
					SET @Municipalities = ' within the ' + @MunicipalityList +  ' municipalities' 
				END
			 END
		 END
		 
		 SET @DestinationPlaces = @RSA + @Provinces + @Municipalities 
		 SET @RouteDestination = @DestinationPlaces
		 SET @Description = REPLACE(@Description, '[[destination]]', @DestinationPlaces)
	END
	ELSE BEGIN
		SET @Description = REPLACE(@Description, '[[destination]]', @RouteDestination)
	END

	SET @Description = REPLACE(@Description, '[[origin]]', @RouteOrigin)
	

	UPDATE App.ApplicationLicenceRouteDetails
	SET RouteOrigin = UPPER(@RouteOrigin)
		, RouteDestination = UPPER(@RouteDestination)
		, RouteDescription= UPPER(@Description)
	WHERE ApplicationLicenceRouteDetailID = @ApplicationLicenceRouteDetailID

	SELECT UPPER(@Description)
END
