CREATE PROCEDURE [dbo].[OnlineGetWaresForOrders]
(
	@Today date,
	@countrynumber int,
	@hotelNumber int,
	@GetImages bit,
  @WareNumber int = NULL,
  @ReturnCountry bit = 1,
  @GetAddons bit = 0,
  @GetWellness bit = 0,
  @GetSubscriptions bit = 0,
  @SubscriptionWareNumber INT = NULL
)
/*
	Changes:
  2025-03-12 MLJ: Sort order changed for wares. Now sorted by OnlineSortNumber and WareName
  2024-11-25 MLJ: Supports subscriptions 
  2024-08-20 MLJ: Supports sql server 2016
  2024-08-13 MLJ: Supports wellness
  2024-03-21 MLJ: Returns BookTimeInMinutes from Ware
  2024-03-18 MLJ: Returns DateOfBirth... columns from WareTicket
  2024-03-13 MLJ: Supports DoNotShowDepartureTime
  2024-03-04 MLJ: Supports ware create table
  2024-02-16 MLJ: Supports ware price from rules
  2024-02-05 MLJ: Returns OnlineDescription column from OnlineWareDelivery
  2024-01-15 MLJ: Returns Addon-wares
  2023-12-08 MLJ: Return columns from new table WareTicket
  2023-04-25 MLJ: Fixed bug. Overlapping periods caused duplicate periods.
  2023-02-10 MLJ Fixed bug: Missing NOLOCK
  2023-01-20 MLJ Fixed bug: did not return waregroups
  2023-01-19 Fixed bug: periods for max-number-for-sale was returned even if no matching period for online-ware-ordering
  2023-01-19 MLJ EXT:: Added parameter @WareNumber
  2022-12-16 KJ  Ext: Multiple rules per WareLinkGroup.
  2022-12-14 MLJ Added UseArrangementTimeAsAvailabilityTime and DefaultArrangementTime
  2022-10-31 MLJ Fixed bug with deleted periods
	2022-09-07 MLJ Fixed bug with duplicate wares when multiple recurring rules were created
*/
AS
BEGIN
	DECLARE @OnlineWareOrderingRuleType int = 28;
	DECLARE @MaxNumberForSaleRuleType int = 4;
	DECLARE @WarePriceRuleType int = 1;
  DECLARE @StartDateTime datetime = @Today
  DECLARE @EndDateTime datetime = DATEADD(day, 1, @Today)

  DECLARE @SelectedWareNumbersEmpty BIT = 1
  DECLARE @SelectedWareNumbers TABLE (WareNumber INT NOT NULL, NumberOfTickets INT NULL)
  IF @WareNumber IS NOT NULL
  BEGIN
    SET @SelectedWareNumbersEmpty = 0
    INSERT INTO @SelectedWareNumbers VALUES (@WareNumber, NULL)
    IF @GetAddons=1
      INSERT INTO @SelectedWareNumbers
      SELECT A.FK_WareNumberRelated, NULL
      FROM WareAddon A WITH (NOLOCK)
      WHERE FK_WareNumber=@WareNumber
      AND A.AddOnType=1
  END
  ELSE
  IF NOT @SubscriptionWareNumber IS NULL
  BEGIN
    SET @SelectedWareNumbersEmpty = 0
    INSERT INTO @SelectedWareNumbers 
    SELECT FK_WareNumberRelated, A.NumberOfTickets
    FROM WareAddon A WITH (NOLOCK) 
    WHERE A.FK_WareNumber=@SubscriptionWareNumber
    AND A.AddOnType=3
  END


	DECLARE @R TABLE
	(
		RuleType int NOT NULL,
		RecurringPatternId int NOT NULL,
		WareNumber int NOT NULL,
		WareGroupNumber int NOT NULL,
    RuleValue varchar(50)
	)
	;
	WITH R
	AS
	(
		SELECT R.RuleType, P.Id RecurringPatternId, R.RuleValue
		FROM RecurringRule R WITH (NOLOCK)
		INNER JOIN RecurringPattern P WITH (NOLOCK) ON P.Id=R.FK_RecurringPatternId
		CROSS APPLY (SELECT TOP 1 1 Found FROM RecurringPatternSpecified WITH (NOLOCK) WHERE FK_RecurringPatternId=P.Id AND ISNULL(Deleted,0)=0 AND CAST(StartDateTime AS Date)<=@StartDateTime AND EndDateTime>=@StartDateTime) RS
		WHERE R.RuleType IN (@OnlineWareOrderingRuleType, @MaxNumberForSaleRuleType,@WarePriceRuleType)
		AND P.StartRecurringDateTime < @EndDateTime
		AND (CAST(P.EndRecurringDateTime as int) <=0 OR  P.EndRecurringDateTime > @StartDateTime)
	)
	INSERT INTO @R (RuleType,RecurringPatternId, WareNumber, WareGroupNumber, RuleValue)
	SELECT R.RuleType, R.RecurringPatternId, W.WareNumber, W.WareGroupNumber, R.RuleValue
	FROM R
  INNER JOIN WareLinkGroupRecurringPatternXTab WITH (NOLOCK) ON WareLinkGroupRecurringPatternXTab.FKRecurringPatternId = R.RecurringPatternId
	INNER JOIN WareLinkGroup WLG WITH (NOLOCK) ON WLG.Id = WareLinkGroupRecurringPatternXTab.FKWareLinkGroupId
	INNER JOIN Ware W  WITH (NOLOCK) ON W.WareLinkNumber=WLG.Id AND ISNULL(W.markasdeleted, 0) = 0 
  WHERE (@SelectedWareNumbersEmpty=1 OR W.WareNumber IN (SELECT WareNumber FROM @SelectedWareNumbers))
	UNION
	SELECT R.RuleType, R.RecurringPatternId, W.WareNumber, W.WareGroupNumber, R.RuleValue
	FROM R
	INNER JOIN RecurringPatternWareXTab X WITH (NOLOCK) ON X.FK_RecurringPatternId=R.RecurringPatternId
	INNER JOIN Ware W WITH (NOLOCK) ON W.WareNumber=X.FK_WareNumber AND ISNULL(W.markasdeleted, 0) = 0
  WHERE (@SelectedWareNumbersEmpty=1 OR W.WareNumber IN (SELECT WareNumber FROM @SelectedWareNumbers))


  IF @ReturnCountry=1
	  SELECT CO.CountryNumber, CO.ISO_3166_A2
	  FROM dbo.CUSTOMER CU WITH (NOLOCK)
	  INNER JOIN dbo.Countries CO  WITH (NOLOCK) ON CO.CountryNumber=CU.CountryNumber
	  WHERE CU.CustomerTypeNumber=7
	  AND CU.HotelNumber=@hotelNumber

  DECLARE @RS TABLE
  (
    WareNumber int, 
    StartDateTime datetime, 
    EndDateTime datetime, 
    RecurringPatternId int, 
    RuleType int, 
    RuleValue varchar(50)
  )
  INSERT INTO @RS (WareNumber, StartDateTime, EndDateTime, RecurringPatternId, RuleType, RuleValue)
	SELECT R.WareNumber, S.StartDateTime, S.EndDateTime, S.FK_RecurringPatternId RecurringPatternId, R.RuleType, R.RuleValue
	FROM RecurringPatternSpecified S WITH (NOLOCK)
	INNER JOIN @R R ON R.RecurringPatternId=S.FK_RecurringPatternId
	WHERE ISNULL(S.Deleted,0)=0
	AND S.StartDateTime < @EndDateTime
	AND (CAST(S.EndDateTime as int) <=0 OR S.EndDateTime > @StartDateTime)

  IF NOT @SubscriptionWareNumber IS NULL
  BEGIN
    MERGE @R T
    USING (SELECT W.WareNumber, W.WareGroupNumber FROM @SelectedWareNumbers SWN INNER JOIN Ware W WITH (NOLOCK) ON W.WareNumber=SWN.WareNumber AND ISNULL(W.markasdeleted, 0) = 0) S
    ON (T.WareNumber=S.WareNumber AND T.RuleType=@OnlineWareOrderingRuleType)
    WHEN NOT MATCHED BY TARGET THEN
      INSERT (RuleType,RecurringPatternId, WareNumber, WareGroupNumber, RuleValue) VALUES (@OnlineWareOrderingRuleType, -1, S.WareNumber, S.WareGroupNumber, NULL)
     ;
    INSERT INTO @RS (WareNumber, StartDateTime, EndDateTime, RecurringPatternId, RuleType, RuleValue)
	  SELECT R.WareNumber, @StartDateTime, @EndDateTime, R.RecurringPatternId, R.RuleType, R.RuleValue
	  FROM @R R 
    WHERE R.RecurringPatternId=-1
  END

  SELECT  WareNumber, StartDateTime, EndDateTime, RecurringPatternId, RuleType, ISNULL(TRY_CAST(RuleValue as Int),-1) MaxNumberForSale 
  FROM @RS
  WHERE RuleType IN (@OnlineWareOrderingRuleType, @MaxNumberForSaleRuleType)

  DECLARE @WareNumbers TABLE
  (
    WareNumber int
  )
  INSERT INTO @WareNumbers
  SELECT DISTINCT WareNumber
  FROM @RS
  WHERE RuleType=@OnlineWareOrderingRuleType

  DECLARE @RulePrice TABLE
  (
    WareNumber INT NOT NULL,
    Price MONEY NOT NULL
  )
  ;WITH T
  AS
  (
    SELECT WareNumber, TRY_CAST(REPLACE(RuleValue,',','.') as Money) Price
    FROM @RS
    WHERE RuleType = @WarePriceRuleType
    -- check that the price is valid the entire day from @StartDateTime to @EndDateTime
	  AND StartDateTime <= @StartDateTime
	  AND (CAST(EndDateTime as int) <=0 OR DATEADD(MINUTE,1,EndDateTime) >= @EndDateTime)
  )
  INSERT INTO @RulePrice
  SELECT WareNumber, MIN(Price)
  FROM T
  WHERE Price IS NOT NULL
  GROUP BY WareNumber

	SELECT 
		WG.WareGroupNumber, 
		ISNULL(T.Name, WG.Text) WareGroupName
	FROM WareGroups WG WITH (NOLOCK)
	CROSS APPLY (
    SELECT TOP 1 1 OK 
    FROM @R R 
    INNER JOIN @WareNumbers WN ON WN.WareNumber=R.WareNumber
    WHERE R.WareGroupNumber=WG.WareGroupNumber AND R.RuleType=@OnlineWareOrderingRuleType) R
	LEFT OUTER JOIN Translation T WITH (NOLOCK) ON T.ObjectNumber = WG.WareGroupNumber AND T.Countrynumber = @countrynumber and T.Type = 15
	ORDER BY WG.SortNumber, ISNULL(T.Name, WG.Text)

	SELECT 
		W.WareGroupNumber,
		W.WareNumber, 
		ISNULL(T.Name, W.WareName) as WareName, 
		COALESCE(RP.Price, W.Price, 0) As Price, 
    CASE WHEN ISNULL(T.Description1,'')<>'' THEN ISNULL(T.Description1,'') ELSE ISNULL(W.Note,'') END As Description,
		W.WareName As PicassoWareName,
		W.OutputKindFk,
    W.UseArrangementTimeAsAvailabilityTime,
    W.DefaultArrangementTime,
    O.Duration,
    WT.NameUsed,
    WT.NameRequired,
    WT.PhoneUsed,
    WT.PhoneRequired,
    WT.ImageUsed,
    WT.ImageRequired,
    CASE WHEN ISNULL(TONL.Description2,'')<>'' THEN ISNULL(TONL.Description2,'') ELSE ISNULL(O.OnlineDescription, '') END OnlineDescription,
    WareCreateTable,
    DoNotShowDepartureTime,
    WT.DateOfBirthUsed,
    WT.DateOfBirthRequired,
    W.BookTimeInMinutes,
    SW.NumberOfTickets
	FROM Ware W WITH (NOLOCK)
  INNER JOIN @WareNumbers WN ON WN.WareNumber=W.WareNumber
  LEFT OUTER JOIN dbo.OnlineWareDelivery O WITH (NOLOCK) ON O.WareNumber=W.WareNumber
  LEFT OUTER JOIN dbo.WareTicket WT WITH (NOLOCK) ON WT.WareNumber=W.WareNumber
  LEFT OUTER JOIN @SelectedWareNumbers SW ON SW.WareNumber=W.WareNumber
	CROSS APPLY (
		SELECT TOP 1 D.HotelNumber FROM WareDepartmentsXtab WDX WITH (NOLOCK) 
		INNER JOIN Departments D WITH (NOLOCK) ON D.PK_Department = WDX.FK_Department AND (D.HotelNumber = @hotelNumber OR @hotelNumber = -1)
		WHERE WDX.WareNumber = W.WareNumber AND WDX.Type = 1
	) D
  LEFT OUTER JOIN @RulePrice RP ON RP.WareNumber=W.WareNumber
	LEFT OUTER JOIN Translation T WITH (NOLOCK) ON T.ObjectNumber = W.Warenumber AND T.Countrynumber = @countrynumber and T.Type = 1
	LEFT OUTER JOIN Translation TONL WITH (NOLOCK) ON TONL.ObjectNumber = W.Warenumber AND TONL.Countrynumber = @countrynumber and TONL.Type = 101
	ORDER BY W.SortNumberOnline, ISNULL(T.Name, W.WareName)
	;

	IF @GetImages=1
		SELECT 
			I.WareNumber, 
			I.ImageName
		FROM @WareNumbers WN 
		CROSS APPLY (SELECT TOP 1 * FROM WareImagesXTAB I WITH (NOLOCK) WHERE I.WareNumber=WN.WareNumber ORDER BY I.SortNumber) I

  IF @GetAddons=1
  BEGIN
    SELECT A.FK_WareNumber WareNumber, A.FK_WareNumberRelated WareNumberRelated
    FROM WareAddon A WITH (NOLOCK)
    INNER JOIN @WareNumbers WN ON WN.WareNumber=A.FK_WareNumber AND A.AddOnType=1

    IF @@ROWCOUNT > 0
      SELECT DISTINCT
		    W.WareNumber, 
		    ISNULL(T.Name, W.WareName) as WareName, 
		    COALESCE(RP.Price, W.Price, 0) As Price, 
        CASE WHEN ISNULL(T.Description1,'')<>'' THEN ISNULL(T.Description1,'') ELSE ISNULL(W.Note,'') END As Description,
        CASE WHEN ISNULL(TONL.Description2,'')<>'' THEN ISNULL(TONL.Description2,'') ELSE ISNULL(OWD.OnlineDescription, '') END OnlineDescription
      FROM WareAddon A WITH (NOLOCK)
      INNER JOIN @WareNumbers WN ON WN.WareNumber=A.FK_WareNumber AND A.AddOnType=1
      INNER JOIN Ware W WITH (NOLOCK) ON W.WareNumber=A.FK_WareNumberRelated
      LEFT OUTER JOIN @RulePrice RP ON RP.WareNumber=W.WareNumber
  	  LEFT OUTER JOIN Translation T WITH (NOLOCK) ON T.ObjectNumber = W.Warenumber AND T.Countrynumber = @countrynumber and T.Type = 1
      LEFT OUTER JOIN OnlineWareDelivery OWD WITH (NOLOCK) ON OWD.WareNumber=W.WareNumber
    	LEFT OUTER JOIN Translation TONL WITH (NOLOCK) ON TONL.ObjectNumber = W.Warenumber AND TONL.Countrynumber = @countrynumber and TONL.Type = 101
  END

  IF @GetWellness=1
  BEGIN
    DECLARE @WellnessWares TABLE ( WareNumber INT )
    INSERT INTO @WellnessWares
    SELECT DISTINCT WN.WareNumber
    FROM @WareNumbers WN 
		INNER JOIN WareHallXTab X WITH (NOLOCK) ON X.FK_WareNumber=WN.WareNumber
    INNER JOIN ConferenceRooms WITH (NOLOCK) ON ConferenceRooms.ConferenceRoomNumber = X.FK_HallNumber 
    INNER JOIN CONFERENCEROOMTYPES WITH (NOLOCK) ON CONFERENCEROOMTYPES.ConferenceRoomTypeNumber = ConferenceRooms.ConferenceRoomTypeNumber

    DECLARE @WareNumberCSV varchar(MAX)
    SELECT @WareNumberCSV=CASE WHEN @WareNumberCSV IS NULL THEN '' ELSE @WareNumberCSV+',' END+CAST(WareNumber AS VARCHAR)
    FROM @WellnessWares

    SELECT WareNumber
    FROM @WellnessWares

    EXEC GenerateWellnessAvailability @today, @EndDateTime, @WareNumberCSV

  END

  DECLARE @SubscriptionWares TABLE
  (
    WareNumber INT NOT NULL,
    PeriodStartType INT,
    PriceCurrentPeriod MONEY,
    PriceNextPeriod MONEY,
    NextPeriodStart DATETIME ,
    BookingFeeAmount MONEY,
    BookingFeeWareNumber INT,
    CurrentPeriodEnd DATETIME ,
    NextPeriodEnd DATETIME
  )
  DECLARE @SubscriptionWareNumbers TABLE
  (
    WareNumber INT NOT NULL,
    PeriodStartType INT
  )
  IF @GetSubscriptions=1
  BEGIN
    INSERT INTO @SubscriptionWareNumbers
    SELECT WN.WareNumber, TT.PeriodStartType
    FROM WareTicketType TT
    INNER JOIN @WareNumbers WN ON WN.WareNumber=TT.FK_WareNumber

    WHERE TT.IsSubscription=1

    IF @@ROWCOUNT > 0
    BEGIN
  
      DECLARE @TmpWareNumber INT
      DECLARE @PeriodStartType INT
      DECLARE @PriceCurrentPeriod MONEY
      DECLARE @CurrentPeriodEnd DATETIME 
      DECLARE @PriceNextPeriod MONEY
      DECLARE @NextPeriodStart DATETIME 
      DECLARE @NextPeriodEnd DATETIME 
      DECLARE @BookingFeeAmount MONEY
      DECLARE @BookingFeeWareNumber INT 
      DECLARE SubscriptionCursor CURSOR FOR
        SELECT WareNumber, PeriodStartType FROM @SubscriptionWareNumbers
      OPEN SubscriptionCursor
      WHILE (1=1)
      BEGIN
          FETCH NEXT FROM SubscriptionCursor
          INTO @TmpWareNumber, @PeriodStartType
          IF (@@FETCH_STATUS <> 0)
            BREAK
          EXEC dbo.SubscriptionCreate @TmpWareNumber, @Today, @PriceCurrentPeriod OUTPUT, @PriceNextPeriod OUTPUT, @NextPeriodStart OUTPUT, @BookingFeeAmount OUTPUT, @BookingFeeWareNumber OUTPUT 

          SELECT @CurrentPeriodEnd=PeriodEnd FROM [dbo].[WareTicketGetPeriodStartEnd] (@PeriodStartType, @Today)
          IF @NextPeriodStart IS NOT NULL
            SELECT @NextPeriodEnd=PeriodEnd FROM [dbo].[WareTicketGetPeriodStartEnd] (@PeriodStartType, @NextPeriodStart)
          ELSE
            SET @NextPeriodEnd=NULL

          INSERT INTO @SubscriptionWares
          VALUES (@TmpWareNumber, @PeriodStartType, @PriceCurrentPeriod, @PriceNextPeriod, @NextPeriodStart, @BookingFeeAmount, @BookingFeeWareNumber,@CurrentPeriodEnd,@NextPeriodEnd)
      END
      CLOSE SubscriptionCursor;
      DEALLOCATE SubscriptionCursor;
    END
    SELECT SW.WareNumber, PeriodStartType, PriceCurrentPeriod, PriceNextPeriod, NextPeriodStart, BookingFeeAmount, BookingFeeWareNumber, ISNULL(BT.Name, BW.WareName) as BookingFeeWareName, SW.CurrentPeriodEnd, SW.NextPeriodEnd
    FROM @SubscriptionWares SW
    LEFT OUTER JOIN Ware BW WITH (NOLOCK) ON BW.WareNumber=SW.BookingFeeWareNumber
  	LEFT OUTER JOIN Translation BT WITH (NOLOCK) ON BT.ObjectNumber = SW.BookingFeeWareNumber AND BT.Countrynumber = @countrynumber and BT.Type = 1
  END
END
