/*
  Changes:
    VD 20130902 Mail addresses moved to table of their own
    KJ 20080701: Bug: Fix for "Lastname sometimes empty after webupdate"
*/

IF ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey LIKE 'HotelDominoUpdated'), 0) = 0 
BEGIN
DECLARE @HotelName VARCHAR(50)
, @Address VARCHAR(50) 
, @ZipCode VARCHAR(10) 
, @HotelNumber INT
, @CountryNumber INT
, @City VARCHAR(50) 
, @SENr VARCHAR(50) 
, @Phone VARCHAR(50) 
, @Fax VARCHAR(50) 
, @EMail VARCHAR(50) 
, @Creditor VARCHAR(50) 

SELECT @HotelName =KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey LIKE 'HotelName'
SELECT @Address =KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey LIKE 'Address'
SELECT @ZipCode =KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey LIKE 'ZipCode'
SELECT @HotelNumber = CONVERT(INT, KeyValue) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey LIKE 'HotelId'
IF ISNULL(@HotelNumber, 0) = 0 SET @HotelNumber = 0
SELECT @CountryNumber =KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey LIKE 'CountryCode'
SELECT @City =KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey LIKE 'CityName'
SELECT @SENr =KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey LIKE 'SENo'
SELECT @Phone =KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey LIKE 'Telephone'
SELECT @Fax =KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey LIKE 'TeleFax'
SELECT @EMail =KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey LIKE 'EMail'
SELECT @Creditor =KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey LIKE 'HotelCreditorNumber'

DECLARE @CustomerNumber INT
SELECT @CustomerNumber= CustomerNumber FROM Customer WITH (NOLOCK) WHERE HotelNumber = @HotelNumber
IF ISNULL(@CustomerNumber, 0) > 0 
BEGIN

INSERT HOTELSETUP (KEYVALUE, SearchKey) VALUES ('1', 'HotelDominoUpdated')  
UPDATE Customer SET LastName = @HotelName WHERE CustomerNumber = @CustomerNumber AND ISNULL(LastName, '') = ''
UPDATE Customer SET Address1 = @Address WHERE CustomerNumber = @CustomerNumber AND ISNULL(Address1, '') = ''
UPDATE Customer SET ZipCodeOverwrite = @ZipCode WHERE CustomerNumber = @CustomerNumber AND ISNULL(ZipCodeOverwrite, '') = ''
UPDATE Customer SET CountryNumber = @CountryNumber WHERE CustomerNumber = @CustomerNumber AND ISNULL(CountryNumber, 0) = 0
UPDATE Customer SET CityOverWrite = @City WHERE CustomerNumber = @CustomerNumber AND ISNULL(CityOverWrite, '') = ''
UPDATE Customer SET COMPANYNUMBER = COMPANYNUMBER WHERE CustomerNumber = @CustomerNumber AND ISNULL(COMPANYNUMBER, '') = ''
UPDATE Customer SET PHONENUMBER = @Phone WHERE CustomerNumber = @CustomerNumber AND ISNULL(PHONENUMBER, '') = ''
UPDATE Customer SET FAXNUMBER = @Fax WHERE CustomerNumber = @CustomerNumber AND ISNULL(FAXNUMBER, '') = ''

IF NOT EXISTS (SELECT 1 
               FROM MailAddresses 
               WHERE 
                 CustomerNumber = @CustomerNumber AND
                 MailAddressType = 0)
  INSERT INTO MailAddresses (CustomerNumber, MailAddress, MailAddressType) 
    VALUES (@CustomerNumber, @EMail, 0)  

IF ISNUMERIC(@Creditor) = 1
  UPDATE Customer SET ExternDebitorNumber = @Creditor WHERE CustomerNumber = @CustomerNumber AND ISNULL(ExternDebitorNumber, '') = ''
END

END
