CREATE PROCEDURE dbo.OnlineAddReservationOrderItem
(
	@ReservationOrderId int,
	@ReferenceNumber int,
	@GuestNumber smallint,
	@FolioNumber int,
	@HotelCustomerNumber int,
	@SplitBillNumber int,
	@UserIDNumber int,
	@WareNumber int,
	@Price money,
	@Pieces int,
	@PostForDateTime DATETIME = NULL,
	@FromDateTime DATETIME = NULL,
	@ToDateTime DATETIME = NULL,
	@SaveAsArrangementLine BIT = 0,
	@HotelNumber int = NULL,
	@PlaceText varchar(30) = NULL,
  @IsTicket bit = 0,
	@MainWareNumber int = NULL,
	@MainIndex int = NULL,
  @DiscountValue money = NULL,
  @DiscountIsFixedAmount bit = NULL,
  @TreaterConferenceRoomNumber int = NULL,
  @HallConferenceRoomNumber int = NULL,
  @IsSubscription bit = 0,
  @FK_OnlinePaymentId int = NULL,
  @OnboardingURL varchar(1000) = NULL,
  @SubscriptionArrangementLineNumber INT = NULL,
  @ServiceToken varchar(100) = NULL,
  @AgreementIDPrefix varchar(100) = NULL,
  @TicketIndexOffset int = 0
)

/*
	Changes:
  2025-04-01 MLJ: Supports Ware.ArrangementLinePerPax
  2024-12-21 MLJ: @TicketIndexOffset added
	2024-12-03 MLJ: Supports subscriptions
  2024-10-22 MLJ: Sets ArrangementLines.Note from Ware.Note
	2024-10-08 MLJ: Added missing NOLOCK
 	2024-09-19 MLJ: Setting temporary value for ArrangementLineUniqueID when inserting into ArrangementLine to avoid unique violation with NULL
 	2024-09-05 MLJ: Added TRY CATCH. Updates ArrangementLineUniqueID
  2024-09-03 MLJ: Added missing NOLOCK
  2024-09-03 MLJ: Support wellness: treater-ConferenceRoomNumber and hall-ConferenceRoomNumber added to arrangementline
  2024-08-15 MLJ: Support wellness
  2024-03-18 MLJ: Added DateOfBirth to ticket information
  2024-01-29 MLJ: Added discount for extras
  2024-01-15 MLJ: Support for addon-wares
	2023-12-11 MLJ: Added support for additional ticket information
	2022-09-03 MLJ: Added placetext to arrangement lines
	2022-08-29 MLJ: Added support for saving order items to arrangement lines
	2022-08-02 MLJ: Added parameters @FromDateTime and @ToDateTime
*/
AS
BEGIN

BEGIN TRY

	SET NOCOUNT ON;
	DECLARE @CreateNewFolioIfNotExists bit = 1
	DECLARE @AddressType smallint
	DECLARE @TransNrShowOnBill int

	IF @SaveAsArrangementLine=0
	BEGIN
		IF (@FolioNumber IS NULL) OR (@FolioNumber=0)
		BEGIN
			EXECUTE [dbo].[FindFolioNumber] 
			   @ReferenceNumber
			  ,@GuestNumber
			  ,@UserIDNumber
			  ,@WareNumber
			  ,@SplitbillNumber
			  ,1 --CreateNewFolioIfNotExists
			  ,@FolioNumber OUTPUT
			  ,@AddressType OUTPUT
		END

		EXEC [dbo].[OnlinePosteringExt] 
			@WareNumber
			,@HotelCustomerNumber
			,@FolioNumber
			,@Price 
			,@Pieces 
			,'' -- @AWareTextOverwrite
			,@TransNrShowOnBill OUTPUT
			,@UserIDNumber 
			,'' -- @AOnlinePaymentId
			,'' --@AOnlineTransactionId
			,@PostForDateTime
			,@FromDateTime
			,@ToDateTime

		INSERT INTO [dbo].[OnlineReservationOrderItem]
			   ([ReservationOrderId]
			   ,[TransactionNumber])
			 VALUES
			   (@ReservationOrderId
			   ,@TransNrShowOnBill)
	END
	ELSE
	BEGIN
	-- Save as arrangementline
		DECLARE @ExDepartment int = NULL
		SELECT TOP 1 @ExDepartment=FK_Department 
		FROM WareDepartmentsXTab with (NOLOCK) 
		INNER JOIN Departments with (NOLOCK) ON Departments.PK_Department = WareDepartmentsXTab.FK_Department AND WareDepartmentsXTab.Type = 4 AND Departments.HotelNumber=@HotelNumber
		WHERE WareDepartmentsXTab.WareNumber = @WareNumber
		IF @ExDepartment IS NULL
			SELECT top 1 @ExDepartment=PK_Department 
			FROM Departments with (NOLOCK) 
			WHERE AutoCharge = 1 
			AND HotelNumber = @HotelNumber

		DECLARE @Date char(8) = CONVERT(CHAR(8), @FromDateTime, 112)
		DECLARE @ArrivalTime char(5) = CONVERT(CHAR(5), @FromDateTime, 108)
		DECLARE @DepartureTime char(5) = CASE WHEN CONVERT(CHAR(8), @ToDateTime, 112) > @Date THEN '23:59' ELSE CONVERT(CHAR(5), @ToDateTime, 108) END
    DECLARE @WareName varchar(50)
    DECLARE @PlaceTextDescription varchar(MAX)
    DECLARE @Index int = 0 
    DECLARE @MaxIndex int = 1
		DECLARE @ArrangementLineNumber INT
    DECLARE @FK_Department INT
    DECLARE @Note nvarchar(MAX)
    DECLARE @ArrangementLinePerPax bit

    SELECT @WareName=WareName, @PlaceTextDescription=PlaceTextDescription, @Note=Note, @ArrangementLinePerPax=ISNULL(ArrangementLinePerPax,0)
		FROM Ware WITH (NOLOCK)
		WHERE Ware.WareNumber=@WareNumber

		SELECT TOP 1 @FK_Department=FK_Department 
		FROM WareDepartmentsXTab WITH  (NOLOCK)
		INNER JOIN Departments (NOLOCK) ON WareDepartmentsXTab.FK_Department = Departments.PK_Department AND Departments.HotelNumber = @HotelNumber
		WHERE WareDepartmentsXTab.WareNumber = @WareNumber 
		AND WareDepartmentsXTab.Type = 2 

    IF (@IsTicket = 1) OR (@ArrangementLinePerPax=1 AND ISNULL(@MainWareNumber,0) <= 0)
    BEGIN
      SET @MaxIndex = @Pieces
      SET @Pieces = 1
    END

    WHILE @Index < @MaxIndex
    BEGIN

		  INSERT INTO ArrangementLines ([Date], [Time], DepartureTime, Pieces, WareText, Price, WareNumber, FK_ExDepartment, HallNumber, FK_HallNumber, Note, PlaceText, AttachedRoomLine, ArrangementLineUniqueID) 
      SELECT @Date, @ArrivalTime, @DepartureTime, @Pieces, @WareName, @Price, @WareNumber, @ExDepartment, @HallConferenceRoomNumber, @TreaterConferenceRoomNumber, @Note, ISNULL(@PlaceText,@PlaceTextDescription), NULL, -(ISNULL(MAX(ArrangementLineNumber),0)+1)
      FROM ArrangementLines WITH (NOLOCK)

		  SET @ArrangementLineNumber=SCOPE_IDENTITY()

      DECLARE @ArrangementLineUniqueID INT = ((@ArrangementLineNumber & 0x7ffff /*19 bits*/) * 4096 /* << 12 */) | (@ArrangementLineNumber / 524288  /* >> 19 */)
      UPDATE ArrangementLines
        SET ArrangementLineUniqueID = @ArrangementLineUniqueID
      WHERE ArrangementLineNumber = @ArrangementLineNumber

		  INSERT INTO ArrangementReservationsXTab (ArrangementLineNumber, ReferenceNumber, GuestNumber, PlusProductLineNumber) 
		  VALUES (@ArrangementLineNumber, @ReferenceNumber, @GuestNumber, 0)

      IF @FK_Department IS NOT NULL
		    INSERT INTO ArrangementLineDepartmentXTab (FK_ArrangementLineNumber, FK_DepartmentNumber) 
        VALUES (@ArrangementLineNumber, @FK_Department)

		  INSERT INTO [dbo].[OnlineReservationOrderArrangementLine]
			     ([ReservationOrderId]
			     ,[ArrangementLineNumber])
			   VALUES
			     (@ReservationOrderId
			     ,@ArrangementLineNumber)

      IF @IsTicket = 1
      BEGIN
        INSERT INTO dbo.ArrangementLinesTicket (ArrangementLineUniqueID,Firstname,Lastname,Phone,ImageContentType,[Image],[DateOfBirth] )
        SELECT @ArrangementLineUniqueID,Firstname,Lastname,Phone,ImageContentType,[Image],[DateOfBirth]
        FROM dbo.OnlineReservationOrderTempTicket WITH (NOLOCK)
        WHERE ReservationOrderId=@ReservationOrderId
        AND WareNumber = @WareNumber
        AND Indx = @TicketIndexOffset+@Index
      END
      IF @IsSubscription=1 
      BEGIN
        INSERT INTO [dbo].[ReservationSubscription]([FK_ReferenceNumber],[FK_GuestNumber],[ArrangementLineUniqueID],[SubscriptionDate],[TerminationDate],[LastUpdDate],[FK_OnlinePaymentId], OnBordingURL, ServiceToken, AgreementID)
        VALUES
        (@ReferenceNumber, @GuestNumber, @ArrangementLineUniqueID, @Date, NULL, GETDATE(), @FK_OnlinePaymentId, @OnboardingURL, @ServiceToken, @AgreementIDPrefix+CAST(@ArrangementLineUniqueID AS varchar))
      END
      IF @SubscriptionArrangementLineNumber IS NOT NULL
      BEGIN
        INSERT INTO ArrangementLineArrangementLineXTab (FK_ArrangementLineNumber, FK_ArrangementLineNumberSub, RelationType)
        VALUES (@SubscriptionArrangementLineNumber, @ArrangementLineNumber, 1)
      END
      SET @Index = @Index + 1
    END

    IF @MainWareNumber IS NOT NULL
    BEGIN
  		DECLARE @MainArrangementLineNumber INT
      SELECT @MainArrangementLineNumber=L.ArrangementLineNumber
      FROM ArrangementReservationsXtab X WITH (NOLOCK)
      INNER JOIN ArrangementLines L WITH (NOLOCK) ON L.ArrangementLineNumber=X.ArrangementLineNumber
      WHERE X.ReferenceNumber=@ReferenceNumber
      AND X.GuestNumber=@GuestNumber
      AND L.WareNumber=@MainWareNumber
      ORDER BY L.ArrangementLineNumber OFFSET @MainIndex ROWS FETCH NEXT 1 ROWS ONLY

      INSERT INTO [dbo].[ArrangementLineArrangementLineXTab] ([FK_ArrangementLineNumber], [FK_ArrangementLineNumberSub], [RelationType]) VALUES (@MainArrangementLineNumber, @ArrangementLineNumber, 0)
    END

    IF ISNULL(@DiscountValue,0) > 0
    BEGIN
      MERGE [dbo].[ReservationAddressWareDiscountXTab] T
      USING (
        SELECT AddressNumber, @WareNumber WareNumber, @DiscountValue DiscountValue, @DiscountIsFixedAmount IsFixedAmount
        FROM [dbo].[RESERVATIONADDRESSXTAB] WITH (NOLOCK)
        WHERE [REFERENCENUMBER]=@ReferenceNumber
        AND [GUESTNUMBER]=@GuestNumber
        AND [AddressTypeNumber]=1
      ) S
      ON (T.FK_ReservationAddressNumber=S.AddressNumber AND T.FK_WareNumber=S.WareNumber)
      WHEN NOT MATCHED BY TARGET THEN
        INSERT (FK_ReservationAddressNumber, FK_WareNumber, DiscountValue, IsFixedAmount)
        VALUES (S.AddressNumber, S.WareNumber, S.DiscountValue, S.IsFixedAmount)
      ;
    END
    IF (ISNULL(@TreaterConferenceRoomNumber,0) > 0) AND (ISNULL(@HallConferenceRoomNumber,0) > 0)
    BEGIN
      DECLARE @TreaterConferenceRoomLineNumber INT
      DECLARE @HallConferenceRoomLineNumber INT
			EXEC GetNextNumber 'RESERVATIONROOMLINEIDENTITY', 1, @TreaterConferenceRoomLineNumber OUTPUT
			EXEC GetNextNumber 'RESERVATIONROOMLINEIDENTITY', 1, @HallConferenceRoomLineNumber OUTPUT
      DECLARE @ConferenceRoomPriceOverwrite int = ISNULL(TRY_CONVERT(int,  dbo.GetHotelSetupKeyValue('CheckInAlwaysSetPriceOverwrite', -1)), 0)

      INSERT INTO ReservationConferenceLine (ReservationConfLineNumber, ConferenceRoomNumber, ReferenceNumber,GuestNo, BookedConferenceRoomTypeNumber, NumberOfRooms, 
        ArrivalDate, ArrivalTime, DepartureDate, DepartureTime,
        ConferenceRoomSetupNumber, Adults, Children1, Children2, TableBooking, TableStatus, PriceOverWritten)
      SELECT T.ConferenceRoomLineNumber, T.ConferenceRoomNumber, @ReferenceNumber, @GuestNumber, R.ConferenceRoomTypeNumber, 1,
        CONVERT(CHAR(8), @FromDateTime, 112), CONVERT(CHAR(5), @FromDateTime, 108), CONVERT(CHAR(8), @ToDateTime, 112), CONVERT(CHAR(5), @ToDateTime, 108),
        NULL, @Pieces, 0, 0, 0, 0, @ConferenceRoomPriceOverwrite
      FROM (
        SELECT @TreaterConferenceRoomLineNumber ConferenceRoomLineNumber, @TreaterConferenceRoomNumber ConferenceRoomNumber
        UNION ALL 
        SELECT @HallConferenceRoomLineNumber ConferenceRoomLineNumber, @HallConferenceRoomNumber ConferenceRoomNumber
      ) T
      INNER JOIN ConferenceRooms R WITH (NOLOCK) ON R.ConferenceRoomNumber = T.ConferenceRoomNumber
    END
  END

END TRY
BEGIN CATCH
  THROW
END CATCH

END
