CREATE PROCEDURE dbo.OnlineAddReservationOrder
(
	@ReferenceNumber int,
	@GuestNumber smallint,
	@FolioNumber int,
	@HotelNumber int,
	@DeliveryPlace nvarchar(100),
	@DeliveryTime datetime,
	@CreateTimeLocal datetime,
	@ReservationOrderId int OUTPUT,
	@HotelCustomerNumber int OUTPUT,
	@SplitBillNumber int OUTPUT,
	@SaveAsArrangementLine BIT = 0,
	@FirstName varchar(50) = NULL,
	@LastName varchar(50) = NULL,
	@Email varchar(150) = NULL,
	@Phone varchar(255) = NULL,
	@ArrivalTime datetime = NULL,
	@DepartureTime datetime = null,
	@UserIDNumber int = NULL,
	@LogText varchar(MAX) = NULL,
	@Comment varchar(MAX) = NULL,
	@CampaignId int = NULL,
	@DiscountPct smallmoney = NULL,
	@Address1 varchar(100) = NULL,
	@Address2 varchar(100) = NULL,
	@Address3 varchar(100) = NULL,
	@ZipCode varchar(10) = NULL,
	@City varchar(50) = NULL,
	@CountryCode varchar(3) = NULL,
	@SegmentNumber int = NULL,
	@PointOfSalesNumber int = NULL,
	@CreatedReferenceNumber INT = NULL,
  @AllDicountIsFixedAmount BIT = NULL,
  @ExtrasDiscountRate MONEY = NULL,
  @ExtraDicountIsFixedAmount BIT = NULL,
  @GuestTypeNumber INT = NULL,
  @PurposeCodeNumber INT = NULL,
	@CompanyName varchar(50) = NULL,
  @AddCustomer bit = 0,
  @CustomerNumber INT = NULL,
  @BeeperNumber int = NULL,
  @OrderJson nvarchar(MAX) = NULL
)
AS
BEGIN
	/*
	Changes:
  2025-06-27 MLJ Added @BeeperNumber and @OrderJson
  2024-11-08 MLJ SUpports subscriptions
 	2024-10-01 MLJ: Now saves country number on reservation
 	2024-09-24 MLJ: Added @CompanyName
 	2024-09-05 MLJ: Added TRY CATCH
 	2024-09-03 MLJ: Added missing NOLOCK
 	2024-03-12 MLJ: Added @GuestTypeNumber and @PurposeCodeNumber
  2024-01-29 MLJ: Added discount for extras
	2023-10-03 MLJ Added @CreatedReferenceNumber
	2023-09-27 MLJ Added SegmentNumber and PointOfSalesNumber
	2023-08-07 MLJ Order ID saved as AltResNo on reservation
	2023-01-25 MLJ Supports campaign codes and guest address
	2023-01-25 MLJ Added parameter @Comment that is saved to note on reservation
*/
SET NOCOUNT ON;

BEGIN TRY

	IF (@ReferenceNumber IS NULL OR @ReferenceNumber <= 0) AND ((@FolioNumber IS NULL) OR (@FolioNumber<=0))
	BEGIN

    DECLARE @AddressNumber INT

    DECLARE @CountryNumber INT
	  SELECT @HotelCustomerNumber=CustomerNumber, @CountryNumber=CountryNumber FROM CUSTOMER C WITH (NOLOCK) WHERE C.CustomerTypeNumber=7 AND C.HotelNumber=@HotelNumber

    DECLARE @CustomerTypeNumber INT = 1
    IF @CustomerNumber IS NULL
    BEGIN
      IF @AddCustomer=1
      BEGIN
        SELECT TOP 1 @CustomerNumber=M.CustomerNumber
        FROM MailAddresses M WITH (NOLOCK)
        INNER JOIN CUSTOMER C WITH (NOLOCK) ON C.CustomerNumber=M.CustomerNumber AND ISNULL(C.MARKASDELETED,0)=0 AND C.CustomerTypeNumber=@CustomerTypeNumber
        WHERE M.MailAddress=@Email
        AND M.MailAddressType=0

        IF @CustomerNumber IS NULL
        BEGIN
          EXEC GetNextNumber 'CUSTOMERIDENTITY', 1, @CustomerNumber OUTPUT

          INSERT INTO [dbo].[CUSTOMER]([CustomerNumber],[FIRSTNAME],[ADDRESS1],[ADDRESS2],[ADDRESS3],[ZipCodeOverwrite],[PHONENUMBER],[CityOverWrite],[CustomerTypeNumber],[LASTNAME],[CELLPHONENUMBER], CountryNumber) 
          VALUES (@CustomerNumber,@FirstName,@Address1,@Address2,@Address3,@ZipCode,@Phone,@City,@CustomerTypeNumber,@LastName,@Phone, @CountryNumber)    

          INSERT INTO [dbo].[MailAddresses] ([CustomerNumber],[MailAddress],[MailAddressType])
          VALUES (@CustomerNumber,@Email,0)
        END
      END
    END
    ELSE
    BEGIN
      SELECT TOP 1 
        @FirstName=CASE ISNULL(@FirstName,'') WHEN '' THEN C.FirstName ELSE @FirstName END,
        @LastName=CASE ISNULL(@LastName,'') WHEN '' THEN C.LASTNAME ELSE @LastName END, 
        @Address1=CASE ISNULL(@Address1,'') WHEN '' THEN C.Address1 ELSE @Address1 END,
        @Address2=CASE ISNULL(@Address2,'') WHEN '' THEN C.Address2 ELSE @Address2 END,
        @ZipCode=CASE ISNULL(@ZipCode,'') WHEN '' THEN C.ZipCodeOverwrite ELSE @ZipCode END,
        @City=CASE ISNULL(@City,'') WHEN '' THEN C.CityOverWrite ELSE @City END,
        @Phone=CASE ISNULL(@Phone,'') WHEN '' THEN ISNULL(C.PhoneNumber,C.CellPhoneNumber) ELSE @Phone END,
        @CountryNumber=CASE ISNULL(@CountryNumber,0) WHEN 0 THEN C.CountryNumber ELSE @CountryNumber END,
        @Email=CASE ISNULL(@Email,'') WHEN '' THEN M.MailAddress ELSE @Email END
      FROM Customer C WITH (NOLOCK)
      LEFT OUTER JOIN [dbo].[MailAddresses] M WITH (NOLOCK) ON M.CustomerNumber=C.CustomerNumber AND MailAddressType=0
      WHERE C.CustomerNumber=@CustomerNumber 
      AND ISNULL(C.MARKASDELETED,0)=0 
    END

    IF ISNULL(@CountryCode,'') IS NULL
      SELECT @CountryCode=CountryCode
      FROM Countries WITH (NOLOCK)
      WHERE CountryNumber= @CountryNumber
    ELSE
      SELECT @CountryNumber=CountryNumber
      FROM Countries WITH (NOLOCK)
      WHERE CountryCode = @CountryCode

		IF @SaveAsArrangementLine=0
		BEGIN
			EXEC GetNextNumber 'ReservationAddressIdentity', 1, @AddressNumber OUTPUT

			INSERT INTO ReservationAddress (AddressNumber, AddressType,FirstName,LastName, Phone, Email,Address1,Address2,Address3,ZipCode,City,CountryCode, LINK)
			VALUES (@AddressNumber, 1, @FirstName, @LastName, @Phone, @Email,@Address1,@Address2,@Address3,@ZipCode,@City,@CountryCode, @CustomerNumber)

			INSERT INTO Folios (GuestNumber, ReservationAddressNumber, OpenFolio, Note, HotelCustomerNumber)
			VALUES (0, @AddressNumber, 1, '', @HotelCustomerNumber)

			SET @FolioNumber = SCOPE_IDENTITY()
		END
		ELSE
		BEGIN
			DECLARE @MethodOfPayment int = TRY_CONVERT(int,  dbo.GetHotelSetupKeyValue('PicassoOnlinePosteringWarenumber', @HotelCustomerNumber))
			DECLARE @Status tinyint = TRY_CONVERT(tinyint,  dbo.GetHotelSetupKeyValue('PicassoOnlineConfirmedState', @HotelCustomerNumber))
			EXEC OnlineCreateReservation @HotelChainNumber=@HotelNumber,@ProductTypeNumber=0,@ReferenceNumber=@ReferenceNumber output,
				@UserIDNumber=@UserIDNumber, @MethodOfPayment=@MethodOfPayment,@Status=@Status,@FirstName=@FirstName,@LastName=@LastName,
				@Email=@Email,@Phone=@Phone,@ArrivalTime=@ArrivalTime,@DepartureTime=@DepartureTime,@CreationInProgress=0, 
				@CampaignId=@CampaignId, @DiscountPct=@DiscountPct,
				@Address1=@Address1,@Address2=@Address2,@Address3=@Address3,@ZipCode=@ZipCode,@City=@City,@CountryCode=@CountryCode, @CountryNumber=@CountryNumber,
				@PointOfSaleNumber = @PointOfSalesNumber, @SegmentNumber = @SegmentNumber, @CreatedReferenceNumber = @CreatedReferenceNumber,
        @ExtrasDiscountRate = @ExtrasDiscountRate, @ExtraDicountIsFixedAmount = @ExtraDicountIsFixedAmount, @AllDicountIsFixedAmount = @AllDicountIsFixedAmount,
        @GuestTypeNumber=@GuestTypeNumber, @PurposeCodeNumber=@PurposeCodeNumber, @CustomerNumber=@CustomerNumber

			SET @GuestNumber=0

			IF @CreatedReferenceNumber IS NULL
				UPDATE Reservations 
					SET AltResNo = CAST(@ReservationOrderId AS varchar(30))
				FROM Reservations R WITH (NOLOCK)
				WHERE R.ReferenceNumber=@ReferenceNumber
				AND R.GuestNumber=@GuestNumber	

      IF ISNULL(@CompanyName,'') <> '' 
      BEGIN
	      EXEC GetNextNumber 'RESERVATIONADDRESSIDENTITY', 1, @AddressNumber OUTPUT
			
        INSERT INTO ReservationAddress (AddressNumber, AddressType,FirstName,LastName, Phone, Email,Address1,Address2,Address3,ZipCode,City,CountryCode)
			  VALUES (@AddressNumber, 2, '', @CompanyName, @Phone, @Email,@Address1,@Address2,@Address3,@ZipCode,@City,@CountryCode)

      	INSERT INTO ReservationAddressXTab (ReferenceNumber, GuestNumber, AddressNumber, AddressTypeNumber) VALUES (@ReferenceNumber, @GuestNumber, @AddressNumber, 2)
      END
		END
	END

	IF (@FolioNumber IS NULL) OR (@FolioNumber<=0)
	BEGIN
		IF @GuestNumber=0
			SELECT @SplitbillNumber=CASE 
				WHEN ISNULL(Day2,0) = 0 OR DayNo < Day2 THEN SplitBillNumber1
				WHEN ISNULL(Day3,0) = 0 OR DayNo < Day3 THEN SplitBillNumber2
				WHEN ISNULL(Day4,0) = 0 OR DayNo < Day4 THEN SplitBillNumber3
				ELSE SplitBillNumber4
			END 
			FROM RESERVATIONROOMLINE RL WITH (NOLOCK)
			INNER JOIN RESERVATIONROOMLINEXTAB X WITH (NOLOCK) on X.LINENUMBER=RL.LINENUMBER AND X.ReferenceNumber=@ReferenceNumber AND X.GuestNumber=@GuestNumber 
			INNER JOIN (SELECT ReferenceNumber, GuestNumber, DATEDIFF(day, ArrivalDate, @CreateTimeLocal)+1 DayNo FROM Reservations R WITH (NOLOCK)) R ON  R.ReferenceNumber=X.ReferenceNumber AND R.GuestNumber=X.GuestNumber
		ELSE
			SELECT @SplitbillNumber=CASE 
				WHEN ISNULL(Day2,0) = 0 OR DayNo < Day2 THEN SplitBillNumber1
				WHEN ISNULL(Day3,0) = 0 OR DayNo < Day3 THEN SplitBillNumber2
				WHEN ISNULL(Day4,0) = 0 OR DayNo < Day4 THEN SplitBillNumber3
				ELSE SplitBillNumber4
			END 
			FROM (
				SELECT DATEDIFF(day, ArrivalDate, @CreateTimeLocal)+1 DayNo, Day2,Day3,Day4,SplitBillNumber1, SplitBillNumber2, SplitBillNumber3, SplitBillNumber4
				FROM Reservations R WITH (NOLOCK)
				WHERE R.ReferenceNumber=@ReferenceNumber
				AND R.GuestNumber=@GuestNumber
			) R
	END

	IF (@ReservationOrderId IS NULL) OR (@ReservationOrderId<=0)
	BEGIN
		INSERT INTO [dbo].[OnlineReservationOrder]
		(
			[ReferenceNumber]
			,[GuestNumber]
			,[DeliveryPlace]
			,[DeliveryTime]
			,[CreateTime]
			,[CreateTimeLocal]
			,[FolioNumber]
      ,[Comment]
      ,[BeeperNumber]
      ,[OrderJson]
		)
		 VALUES
		 (
		   @ReferenceNumber
		   ,@GuestNumber
		   ,@DeliveryPlace
		   ,@DeliveryTime
		   ,GETDATE()
		   ,@CreateTimeLocal
		   ,@FolioNumber
       ,@Comment
       ,@BeeperNumber
       ,@OrderJson
		)
		SET @ReservationOrderId=@@IDENTITY
	END
	ELSE
	BEGIN
		UPDATE [dbo].[OnlineReservationOrder]
		SET 
			[ReferenceNumber] = @ReferenceNumber
			,[GuestNumber] = @GuestNumber
			,[DeliveryPlace] = @DeliveryPlace
			,[DeliveryTime] = @DeliveryTime
			,[CreateTimeLocal] = @CreateTimeLocal
			,[FolioNumber] = @FolioNumber
      ,[Comment] = @Comment
      ,[BeeperNumber] = @BeeperNumber
      ,[OrderJson] = @OrderJson
		WHERE ReservationOrderID=@ReservationOrderId
	END

	IF @ReferenceNumber > 0 AND @LogText IS NOT NULL AND @LogText <> ''
	BEGIN
		DECLARE @FollowUpText varchar(MAX) = 'Added order '+CAST(@ReservationOrderId AS VARCHAR(MAX))
		EXEC OnlineCreateReservationFollowup @AReferenceNumber=@ReferenceNumber,@AGuestNumber=@GuestNumber,@AUserIdNumber=@UserIDNumber,@AFollowupText=@FollowUpText,@AHotelNumber=@HotelNumber,@ReservationLog=@LogText,@AType=5,@ASubType=0
	END
	IF @ReferenceNumber > 0 AND @Comment IS NOT NULL AND @Comment <> ''
	BEGIN
		MERGE [dbo].[RESERVATIONNOTES] T
		USING (SELECT @ReferenceNumber ReferenceNumber, @GuestNumber GuestNumber, 1 NotePage, @Comment 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(MAX)) + CHAR(13) + CHAR(10) + S.Note
		;
	END
END TRY
BEGIN CATCH
  THROW
END CATCH

END
