CREATE PROCEDURE dbo.UpdateReservationRegistrationCard
(
	   @ReferenceNumber int
      ,@GuestNumber smallint
      ,@CompanyName varchar(50)
      ,@LASTNAME varchar(50)
      ,@FIRSTNAME varchar(50)
      ,@ADDRESS1 varchar(100)
      ,@ADDRESS2 varchar(100)
      ,@ZIPCODE varchar(10)
	  ,@CITY varchar(50)
	  ,@COUNTRYCODE varchar(3)
      ,@EMAIL varchar(150)
      ,@PHONE varchar(255)
	  ,@DateOfBirth datetime
	  ,@PassPortNumber varchar(25)
	  ,@FollowupText varchar(8000)
	  ,@Note varchar(max) = NULL
)
AS
BEGIN
	DECLARE @AddressNumber int
	DECLARE @AddressTypeNumber int =1
	DECLARE @CountryNumber int
	DECLARE @CustomerNumber int = NULL
	IF @PHONE = ''
		SET @PHONE=NULL
	SELECT @CountryNumber=CountryNumber FROM dbo.Countries WHERE CountryCode=@COUNTRYCODE
	SELECT @AddressNumber=AddressNumber FROM dbo.RESERVATIONADDRESSXTAB WHERE REFERENCENUMBER=@ReferenceNumber AND GUESTNUMBER=@GuestNumber AND AddressTypeNumber=@AddressTypeNumber
	IF @AddressNumber IS NULL
	BEGIN
		EXEC GetNextNumber 'RESERVATIONADDRESSIDENTITY', 1, @AddressNumber OUTPUT
		INSERT INTO dbo.RESERVATIONADDRESS (AddressNumber, ADDRESSTYPE, LASTNAME,FIRSTNAME,ADDRESS1,ADDRESS2,ZIPCODE,CITY,COUNTRYCODE,EMAIL,PHONE,DateOfBirth,CountryNumber) 
		VALUES (@AddressNumber, @AddressTypeNumber, @LASTNAME,@FIRSTNAME,@ADDRESS1,@ADDRESS2,@ZIPCODE,@CITY,@COUNTRYCODE,@EMAIL,@PHONE,@DateOfBirth,@CountryNumber)
		INSERT INTO dbo.RESERVATIONADDRESSXTAB (REFERENCENUMBER,GUESTNUMBER,AddressNumber,AddressTypeNumber)
		VALUES (@ReferenceNumber,@GuestNumber,@AddressNumber,@AddressTypeNumber)
	END
	ELSE
	BEGIN
		UPDATE dbo.RESERVATIONADDRESS 
		SET @CustomerNumber=Link,LASTNAME=@LASTNAME,FIRSTNAME=@FIRSTNAME,ADDRESS1=@ADDRESS1,ADDRESS2=@ADDRESS2,ZIPCODE=@ZIPCODE,CITY=@CITY,COUNTRYCODE=@COUNTRYCODE,EMAIL=@EMAIL,PHONE=ISNULL(@PHONE,PHONE),DateOfBirth=@DateOfBirth,CountryNumber=@CountryNumber
		WHERE AddressNumber=@AddressNumber
	END

	SET @AddressTypeNumber=2
	SET @AddressNumber=NULL
	SELECT @AddressNumber=AddressNumber FROM dbo.RESERVATIONADDRESSXTAB WHERE REFERENCENUMBER=@ReferenceNumber AND GUESTNUMBER=@GuestNumber AND AddressTypeNumber=@AddressTypeNumber
	IF @AddressNumber IS NULL
	BEGIN
		IF @CompanyName IS NOT NULL
		BEGIN
			EXEC GetNextNumber 'RESERVATIONADDRESSIDENTITY', 1, @AddressNumber OUTPUT
			INSERT INTO dbo.RESERVATIONADDRESS (AddressNumber, ADDRESSTYPE, LASTNAME) 
			VALUES (@AddressNumber, @AddressTypeNumber, @CompanyName)
			INSERT INTO dbo.RESERVATIONADDRESSXTAB (REFERENCENUMBER,GUESTNUMBER,AddressNumber,AddressTypeNumber)
			VALUES (@ReferenceNumber,@GuestNumber,@AddressNumber,@AddressTypeNumber)
		END
	END
	ELSE
	BEGIN
		UPDATE dbo.RESERVATIONADDRESS 
		SET LASTNAME=@CompanyName
		WHERE AddressNumber=@AddressNumber
	END

	IF ISNULL(@Note,'') <> ''
	BEGIN
		MERGE [dbo].[RESERVATIONNOTES] T
		USING (SELECT @ReferenceNumber ReferenceNumber, @GuestNumber GuestNumber, 1 NotePage, @Note Note) S
		ON (T.ReferenceNumber=S.ReferenceNumber AND T.GuestNumber=S.GuestNumber AND T.NotePage=S.NotePage)
		WHEN NOT MATCHED BY TARGET THEN
			INSERT ([ReferenceNumber],[GuestNumber],[Departments],[Text],[NotePage],[DateStamp],[Lists],[FK_HotelCustomerNumber])
			VALUES (S.ReferenceNumber,S.GuestNumber,NULL,S.Note,S.NotePage,GETDATE(),NULL,-1)
		WHEN MATCHED THEN
			UPDATE SET [Text] = CAST(T.[Text] AS varchar) + CHAR(13) + CHAR(10) + S.Note
		;
	END

	UPDATE dbo.RESERVATIONS
	SET PassPortNumber=@PassPortNumber
	WHERE REFERENCENUMBER=@ReferenceNumber 
	AND GUESTNUMBER=@GuestNumber

	IF @CustomerNumber IS NOT NULL
	BEGIN
		DECLARE @UpdateSetting int = 0
		SELECT @UpdateSetting=KEYVALUE FROM HOTELSETUP WHERE SearchKey LIKE 'UpdateCustomerFromOnlineRegistrationCard'
		
		IF @UpdateSetting<2
		BEGIN
			IF @UpdateSetting=0
			BEGIN
				IF ISNULL(@PHONE,'') <> ''
				BEGIN
					UPDATE dbo.CUSTOMER
					SET PHONENUMBER=@PHONE
					WHERE CustomerNumber=@CustomerNumber
				END
			END
			ELSE
			BEGIN
				UPDATE dbo.CUSTOMER
				SET PHONENUMBER=ISNULL(@PHONE,PHONENUMBER), 
					FIRSTNAME=@FIRSTNAME, LASTNAME=@LASTNAME, 
					ADDRESS1=@ADDRESS1, ADDRESS2=@ADDRESS2, ZipCodeOverwrite=@ZIPCODE, CityOverWrite=@CITY, CountryNumber=@CountryNumber, 
					PassPortNumber=ISNULL(@PassPortNumber, PassPortNumber)
				WHERE CustomerNumber=@CustomerNumber
			END

			IF ISNULL(@Email,'') <> ''
			BEGIN
			;
				MERGE MailAddresses T
				USING (SELECT @Email MailAddress, 0 MailAddressType) S
				ON (T.CustomerNumber=@CustomerNumber AND T.MailAddressType=S.MailAddressType)
				WHEN NOT MATCHED BY TARGET THEN
					INSERT (CustomerNumber, MailAddressType, MailAddress) VALUES (@CustomerNumber, S.MailAddressType, S.MailAddress)
				WHEN MATCHED THEN
					UPDATE SET MailAddress=S.MailAddress
				;
			END
		END
	END
	DELETE dbo.ReservationRegistrationCard WHERE ReferenceNumber=@ReferenceNumber AND GuestNumber=@GuestNumber

	DECLARE @UserID int
	SELECT @UserID=UserIDNumber FROM dbo.Personel WHERE UserIDCode like 'picassoonline'
	IF @UserID IS NULL
		SELECT @UserID=UserIDNumber FROM dbo.Personel WHERE UserIDCode like 'TECHOTEL'
	DECLARE @HotelNumber int
	SELECT @HotelNumber=HotelNumber FROM dbo.RESERVATIONMAIN WHERE REFERENCENUMBER=@ReferenceNumber
	EXEC [dbo].[OnlineCreateReservationFollowup] 
		@AReferenceNumber=@ReferenceNumber,
		@AGuestNumber=@GuestNumber,
		@AUserIdNumber=@UserID, 
		@AFollowupText=@FollowupText, 
		@AHotelNumber=@HotelNumber

END
