CREATE PROCEDURE dbo.HotelSetupUpdateValue @ASearchKey VARCHAR(60), @ANewKeyValue INT AS
/*
  Created By Kent Johansen, October 2022
  Update hotelsetup value if value less than input, or set value if value hasn't be set before
*/
SET NOCOUNT ON

DECLARE @HotelSetupNumber INT
DECLARE @hsValueNumber INT
DECLARE @OldKeyValue VARCHAR(2000)
DECLARE @UpdateHotelSetupValue BIT

SET @UpdateHotelSetupValue = 0

SELECT TOP 1 @HotelSetupNumber = HotelSetup.HotelSetupNumber, @hsValueNumber = HotelSetupValueXtab.FK_ValueNumber, @OldKeyValue = KeyValue
FROM HotelSetup WITH (NOLOCK)
LEFT JOIN HotelSetupValueXtab WITH (NOLOCK) ON HotelSetupValueXtab.HotelSetupNumber = HotelSetup.HotelSetUpNumber
WHERE SearchKey = @ASearchKey

IF @HotelSetupNumber > 0
BEGIN --Update existing records
  IF @OldKeyValue < @ANewKeyValue
  BEGIN
    UPDATE HotelSetup SET KeyValue = @ANewKeyValue WHERE HotelSetUpNumber = @HotelSetupNumber
    UPDATE HotelSetupValue SET KeyValue = @ANewKeyValue
    WHERE PK_ValueNumber IN (SELECT FK_ValueNumber FROM HotelSetupValueXTab WHERE HotelSetupNumber = @HotelSetupNumber) 
    SET @UpdateHotelSetupValue = 1
  END
END
ELSE
BEGIN
  INSERT INTO dbo.HotelSetup (TextValue, SearchKey, LabelName, KeyValue) VALUES (@ASearchKey, @ASearchKey, @ASearchKey, @ANewKeyValue)
  SET @HotelSetupNumber = @@Identity
  SET @UpdateHotelSetupValue = 1
END

IF @UpdateHotelSetupValue = 1
BEGIN
  IF ISNULL(@hsValueNumber, 0) = 0
  BEGIN
    INSERT INTO HotelSetupValue (KeyValue) VALUES (@ANewKeyValue)
    SET @hsValueNumber = @@Identity
  END

  IF ISNULL(@hsValueNumber, 0) > 0 AND ISNULL(@HotelSetupNumber, 0) > 0
  BEGIN
    INSERT INTO HotelSetupValueXtab (HotelSetupNumber, CustomerNumber, FK_ValueNumber)
    SELECT @HotelSetupNumber, Customer.CustomerNumber, @hsValueNumber
    FROM Customer WITH (NOLOCK)
    LEFT JOIN HotelSetupValueXtab (NOLOCK) ON HotelSetupValueXtab.CustomerNumber = Customer.CustomerNumber 
      AND HotelSetupValueXtab.HotelSetupNumber = @HotelSetupNumber
    WHERE Customer.CustomerTypeNumber = 7 
    AND (Customer.LocalHotel = 1 OR Customer.HotelNumber > 0) 
    AND HotelSetupValueXtab.CustomerNumber IS NULL
  END
END