DECLARE @CustomerNumber INT
DECLARE @Old_ValueNumber INT
DECLARE @New_ValueNumber INT
DECLARE @HotelSetupNumber INT
DECLARE @KeySystemExists BIT

SET @KeySystemExists = 0
IF EXISTS(SELECT * FROM HotelSetup (NOLOCK) WHERE SearchKey = 'KeySystem')
	SET @KeySystemExists = 1


IF EXISTS(select * from HotelSetup hs (nolock)
	INNER JOIN HotelSetupValueXTab hsvx (nolock) ON hs.HotelSetUpNumber = hsvx.HotelSetupNumber
	INNER JOIN HotelSetupValue hv (nolock) ON hsvx.FK_ValueNumber = hv.PK_ValueNumber
	where SearchKey = 'UseTesaKeySystem' AND hv.KeyValue = '1')
	BEGIN	
		IF @KeySystemExists = 0
			INSERT INTO HotelSetup (SearchKey, LabelName)
			VALUES ('KeySystem', 'Key system')

		SELECT @HotelSetupNumber = @@IDENTITY
		DECLARE #cur CURSOR FOR 
			select hsvx.CustomerNumber, PK_ValueNumber from HotelSetup hs (nolock)
			INNER JOIN HotelSetupValueXTab hsvx (nolock) ON hs.HotelSetUpNumber = hsvx.HotelSetupNumber
			INNER JOIN HotelSetupValue hv (nolock) ON hsvx.FK_ValueNumber = hv.PK_ValueNumber
			where SearchKey = 'UseTesaKeySystem' AND hv.KeyValue = '1'

		OPEN #cur

		FETCH NEXT FROM #cur INTO @CustomerNumber, @Old_ValueNumber

		WHILE @@FETCH_STATUS = 0 
		BEGIN
			IF @KeySystemExists = 0
			BEGIN
				INSERT INTO HotelSetupValue (KeyValue)
				VALUES ('2')

				SELECT @New_ValueNumber = @@Identity

				INSERT INTO HotelSetupValueXTab (HotelSetupNumber, CustomerNumber, FK_ValueNumber)
				VALUES (@HotelSetupNumber, @CustomerNumber, @New_ValueNumber)
			END

			DELETE FROM HotelSetupValueXTAB
			WHERE FK_ValueNumber = @Old_ValueNumber
			
			DELETE FROM HotelSetupValue 
			WHERE PK_ValueNumber = @Old_ValueNumber

			FETCH NEXT FROM #cur INTO @CustomerNumber, @Old_ValueNumber
			END
			CLOSE #cur
			DEALLOCATE #cur
	
		
		DELETE FROM HotelSetupLog
		FROM HotelSetupLog hsl
		INNER JOIN HotelSetup hs ON hsl.HotelSetupNumber = hs.HotelSetUpNumber
		WHERE SearchKey = 'UseTesaKeySystem'

		DELETE FROM HotelSetup
		WHERE SearchKey = 'UseTesaKeySystem'
	END
	ELSE
		
		IF EXISTS( 
			SELECT * FROM HotelSetup (NOLOCK)
			where SearchKey = 'UseTesaKeySystem' AND
			KeyValue = '1')
			BEGIN
				IF @KeySystemExists = 0
				BEGIN
					SELECT TOP 1 @CustomerNumber = CustomerNumber FROM Customer (NOLOCK)
					WHERE MarkAsDeleted = 0 AND ISNULL(HotelNumber, -1) > -1
					ORDER BY HotelNumber

					INSERT INTO HotelSetup (SearchKey, LabelName)
					VALUES ('KeySystem', 'Key system')
					SELECT @HotelSetupNumber = @@IDENTITY

					INSERT INTO HotelSetupValue (KeyValue)
					VALUES ('2')

					SELECT @New_ValueNumber = @@Identity

					INSERT INTO HotelSetupValueXTab (HotelSetupNumber, CustomerNumber, FK_ValueNumber)
					VALUES (@HotelSetupNumber, @CustomerNumber, @New_ValueNumber)
				END
				DELETE FROM HotelSetupLog
				FROM HotelSetupLog hsl
				INNER JOIN HotelSetup hs ON hsl.HotelSetupNumber = hs.HotelSetUpNumber
				WHERE SearchKey = 'UseTesaKeySystem'
				
				DELETE FROM HotelSetup
				WHERE SearchKey = 'UseTesaKeySystem'
			END

IF EXISTS(SELECT * FROM HotelSetup
			where SearchKey = 'UseHostelKeyCardInfo' AND
			KeyValue = '1')
	BEGIN
		SELECT @HotelSetupNumber = HotelSetupNumber FROM HotelSetup (NOLOCK)
		WHERE SearchKey = 'KeySystem'

		SET @HotelSetupNumber = ISNULL(@HotelSetupNumber, 0)
		
		IF @KeySystemExists = 0 AND @HotelSetupNumber = 0 
		BEGIN
			INSERT INTO HotelSetup (SearchKey, LabelName)
			VALUES ('KeySystem', 'Key system')

			SELECT @HotelSetupNumber = @@IDENTITY
		END

		DECLARE #cur CURSOR FOR
			SELECT CustomerNumber FROM CUSTOMER (NOLOCK)
			WHERE ISNULL(HotelNumber, -1) > -1 AND MarkAsDeleted = 0

		OPEN #cur
		FETCH NEXT FROM #cur INTO @CustomerNumber
		WHILE @@FETCH_STATUS = 0 
		BEGIN
			IF @KeySystemExists = 0
			BEGIN
				INSERT INTO HotelSetupValue (KeyValue)
				VALUES ('1')

				SELECT @New_ValueNumber = @@Identity

				INSERT INTO HotelSetupValueXTab (HotelSetupNumber, CustomerNumber, FK_ValueNumber)
				VALUES (@HotelSetupNumber, @CustomerNumber, @New_ValueNumber)
			END

			FETCH NEXT FROM #cur INTO @CustomerNumber
		END

		Close #cur
		DEALLOCATE #cur

		DELETE FROM HotelSetupLog
		FROM HotelSetupLog hsl
		INNER JOIN HotelSetup hs ON hsl.HotelSetupNumber = hs.HotelSetUpNumber
		WHERE SearchKey = 'UseHostelKeyCardInfo'

		DELETE FROM HotelSetup
		WHERE SearchKey = 'UseHostelKeyCardInfo'
	END