CREATE PROCEDURE dbo.OnlineUpdateReservationRegistrationCard
(
	   @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)
	  ,@Note varchar(max) = NULL
)
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @OK bit = 0;
	SET LOCK_TIMEOUT 0;

	BEGIN TRY
		IF NOT EXISTS(SELECT * FROM dbo.LockedReservations WHERE ReferenceNumber=@ReferenceNumber AND GuestNumber=@GuestNumber)
		BEGIN
			EXEC dbo.UpdateReservationRegistrationCard
				@ReferenceNumber 
				,@GuestNumber 
				,@CompanyName 
				,@LASTNAME 
				,@FIRSTNAME
				,@ADDRESS1 
				,@ADDRESS2 
				,@ZIPCODE 
				,@CITY 
				,@COUNTRYCODE 
				,@EMAIL 
				,@PHONE 
				,@DateOfBirth 
				,@PassPortNumber
				,'Registration card saved online'
				,@Note

			SET @OK = 1
		END
	END TRY
	BEGIN CATCH
	END CATCH

	IF @OK = 0 
	BEGIN
		MERGE dbo.ReservationRegistrationCard T
		USING (SELECT @ReferenceNumber ReferenceNumber, @GuestNumber GuestNumber) S
		ON (T.ReferenceNumber=S.ReferenceNumber AND T.GuestNumber=S.GuestNumber)
		WHEN NOT MATCHED BY TARGET THEN
			INSERT (ReferenceNumber, GuestNumber, LASTNAME,FIRSTNAME,ADDRESS1,ADDRESS2,ZIPCODE,CITY,COUNTRYCODE,EMAIL,PHONE,DateOfBirth,PassPortNumber,CompanyName, Note)
			VALUES (@ReferenceNumber, @GuestNumber, @LASTNAME,@FIRSTNAME,@ADDRESS1,@ADDRESS2,@ZIPCODE,@CITY,@COUNTRYCODE,@EMAIL,@PHONE,@DateOfBirth,@PassPortNumber,@CompanyName,@Note)
		WHEN MATCHED THEN
			UPDATE SET LASTNAME=@LASTNAME,FIRSTNAME=@FIRSTNAME,ADDRESS1=@ADDRESS1,ADDRESS2=@ADDRESS2,ZIPCODE=@ZIPCODE,CITY=@CITY,COUNTRYCODE=@COUNTRYCODE,EMAIL=@EMAIL,PHONE=@PHONE,DateOfBirth=@DateOfBirth, PassportNumber=@PassportNumber, CompanyName=@CompanyName, Note=@Note
		;

		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='Registration card saved online to temporay storage', 
			@AHotelNumber=@HotelNumber

	END

END
