IF EXISTS
(
  SELECT Name 
  FROM SysObjects WITH (NOLOCK) 
  WHERE xtype = 'U' AND Name = 'Cashbox'
)
BEGIN
	IF NOT EXISTS
	(
		SELECT SysColumns.Name 
		FROM SysObjects WITH (NOLOCK) 
			INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
			AND SysColumns.Name = 'FK_CustomerHotel'
			AND SysObjects.Name = 'CashBox'
	)
		ALTER TABLE Cashbox ADD FK_CustomerHotel int NULL
	IF NOT EXISTS
	(
		SELECT SysColumns.Name 
		FROM SysObjects WITH (NOLOCK) 
			INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
			AND SysColumns.Name = 'Externals'
			AND SysObjects.Name = 'CashBox'
	)
		ALTER TABLE Cashbox ADD Externals bit NULL
END
GO
IF EXISTS
(
  SELECT Name 
  FROM SysObjects WITH (NOLOCK) 
  WHERE xtype = 'U' AND Name = 'Cashbox'
) 
AND EXISTS
	(
		SELECT SysColumns.Name 
		FROM SysObjects WITH (NOLOCK) 
			INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
			AND SysColumns.Name = 'FK_CustomerHotel'
			AND SysObjects.Name = 'CashBox'
	)
BEGIN
	DECLARE @HotelCount INT
	SELECT @HotelCount = COUNT(*) FROM CUSTOMER C WITH (NOLOCK) 
		INNER JOIN Countries CT WITH (NOLOCK) ON C.CountryNumber = CT.CountryNumber
	WHERE CustomerTypeNumber = 7 and (HotelNumber > 0 or LocalHotel = 1) AND CT.CountryCode = 'S'
	IF @HotelCount = 1 
	BEGIN
		DECLARE @HotelCustomerNumber INT
		SELECT @HotelCustomerNumber = CustomerNumber FROM CUSTOMER WITH (NOLOCK) WHERE CustomerTypeNumber = 7 and (HotelNumber > 0 or LocalHotel = 1)
		Update CashBox SET FK_CustomerHotel = @HotelCustomerNumber
	END
	ELSE IF @HotelCount > 1
		RAISERROR('WARNING: Several hotels discovered. You have to relate each Cashbox to a specific hotel manually after DB Update and commit in HotelSetup/Types/CashBox.',15,1)
   
END

