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
  ,@FollowUpSubType2 smallint = NULL
  ,@SignatureImage varbinary(MAX) = NULL
)
/*
	Changes:
  2025-03-27 MLJ Set DateOfBirth on customer (if any)
  2024-07-11 MLJ Set Subtype=2 in followup
  2024-04-04 MLJ Fixed missing default value in @SignatureImage
  2024-03-27 MLJ Added @SignatureImage
  2023-03-13 MLJ Fixed bug not saved Note when current note in database was NULL
	2022-08-04 MLJ Fixed bug causing existing note to be partly overwritten
*/
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] = ISNULL(CAST(T.[Text] AS varchar(MAX)),'') + CHAR(13) + CHAR(10) + S.Note
		;
	END

  MERGE [dbo].[ReservationsExt] T
  USING (SELECT @ReferenceNumber FK_ReferenceNumber, @GuestNumber FK_GuestNumber, @SignatureImage SignatureImage) S
  ON (T.FK_ReferenceNumber=S.FK_ReferenceNumber AND T.FK_GuestNumber=S.FK_GuestNumber)
  WHEN NOT MATCHED BY TARGET THEN
    INSERT (FK_ReferenceNumber,FK_GuestNumber,LastUpdDateStamp,SignatureImage, RegistrationCardUpdated) VALUES (S.FK_ReferenceNumber,S.FK_GuestNumber, GETDATE(),S.SignatureImage, 1)
  WHEN MATCHED THEN
    UPDATE SET SignatureImage=S.SignatureImage,
	       RegistrationCardUpdated = 1     
  ;

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

	IF (@CustomerNumber IS NOT NULL) AND EXISTS(SELECT * FROM Customer WHERE CustomerNumber=@CustomerNumber AND CustomerTypeNumber=1)
	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), DateOfBirth=@DateOfBirth
				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
	SET @FollowUpSubType2 = ISNULL(@FollowUpSubType2,0)
	EXEC [dbo].[OnlineCreateReservationFollowup] 
		@AReferenceNumber=@ReferenceNumber,
		@AGuestNumber=@GuestNumber,
		@AUserIdNumber=@UserID, 
		@AFollowupText=@FollowupText, 
		@AHotelNumber=@HotelNumber,
		@AFollowupSubType2=@FollowUpSubType2,
    @ASubtype=2

END
