CREATE PROCEDURE dbo.HotelSetupSettingUpdateValue @ASearchKey VARCHAR(60), @ANewValue VARCHAR (2000) AS
SET NOCOUNT ON
--nb20220307 If hotelsetup exists update all existing chain property values else insert hotelsetup and all property values in chain
--nb20211011 cleanup and on demo insert added (which for sure do override defaults)
--Created by Kent Johansen March 2012

/*
begin tran
DECLARE @ASearchKey VARCHAR(60), @ANewValue VARCHAR (2000)
SET @ASearchKey = 'PaymentLinkInEmailBody'
SET @ANewValue = 0
*/
DECLARE @HotelSetupNumber INT, @hsValueNumber INT, @DemoHotelKeyValue BIT
SELECT TOP 1 @HotelSetupNumber = hs.HotelSetupNumber, @hsValueNumber = hvx.FK_ValueNumber FROM HotelSetup hs WITH (NOLOCK)
   LEFT JOIN HotelSetupValueXtab hvx (nolock) on hvx.HotelSetupNumber = hs.HotelSetUpNumber
WHERE SearchKey = @ASearchKey
IF @HotelSetupNumber > 0
BEGIN --Update existing records
  UPDATE HotelSetup SET KeyValue = @ANewValue WHERE HotelSetUpNumber = @HotelSetupNumber
  UPDATE HotelSetupValue SET KeyValue = @ANewValue
  WHERE PK_ValueNumber IN (
    SELECT FK_ValueNumber FROM HotelSetupValueXTab WHERE HotelSetupNumber = @HotelSetupNumber
  )
END --insert missing values to make sure the default values are overriden
ELSE
BEGIN
  INSERT INTO dbo.HotelSetup (TextValue, SearchKey, LabelName, KeyValue) VALUES ( @ASearchKey , @ASearchKey , @ASearchKey , @ANewValue )
  SET @HotelSetupNumber = @@Identity
END
IF ISNULL(@hsValueNumber, 0) = 0
BEGIN
  INSERT INTO HotelSetupValue (KeyValue) VALUES (@ANewValue)
  SET @hsValueNumber = @@Identity
END
IF ISNULL(@hsValueNumber, 0) > 0 AND ISNULL(@HotelSetupNumber, 0) > 0
  INSERT INTO HotelSetupValueXtab (HotelSetupNumber, CustomerNumber, FK_ValueNumber)
  SELECT @HotelSetupNumber, h.CustomerNumber, @hsValueNumber
  FROM Customer h (nolock)
    LEFT JOIN HotelSetupValueXtab hvx (NOLOCK) ON hvx.CustomerNumber = h.CustomerNumber AND hvx.HotelSetupNumber = @HotelSetupNumber
  WHERE h.CustomerTypeNumber = 7 and (h.LocalHotel = 1 or h.HotelNumber > 0) and hvx.CustomerNumber IS NULL
/*
PRINT @HotelSetupNumber
PRINT @DemoHotelKeyValue
select hs.KeyValue AS PerHotelFalse, hv.KeyValue AS PerHotelTrue, h.LastName from hotelsetup hs (nolock)
  left join hotelsetupvaluextab hvx (nolock) on hvx.HotelSetupNumber = hs.HotelSetUpNumber
  left join HotelSetupValue hv (nolock) on hv.PK_ValueNumber = hvx.FK_ValueNumber
  left join Customer h (nolock) on hvx.CustomerNumber = h.CustomerNumber
where searchkey = @ASearchKey
Rollback tran
*/