CREATE PROCEDURE dbo.HotelSetupSettingUpdateValue @ASearchKey VARCHAR(60), @ANewValue VARCHAR (2000) AS
SET NOCOUNT ON
--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
SELECT @DemoHotelKeyValue = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'DemoHotel'
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 --On demo installations insert missing values to make sure the default values are overriden
ELSE IF @DemoHotelKeyValue = 1 
BEGIN
  INSERT INTO dbo.HotelSetup (TextValue, SearchKey, LabelName, KeyValue) VALUES ( @ASearchKey , @ASearchKey , @ASearchKey , @ANewValue )
  SET @HotelSetupNumber = @@Identity
END
IF @DemoHotelKeyValue = 1 AND 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
/*
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
*/