CREATE PROCEDURE dbo.OnlineGetWaresForOrders
(
	@Today date,
	@countrynumber int,
	@hotelNumber int,
	@GetImages bit
)
AS
BEGIN
	DECLARE @OnlineWareOrderingRuleType int = 28;

	DECLARE @R TABLE
	(
		RecurringPatternId int,
		WareNumber int,
		WareGroupNumber int
	)
	;
	WITH R
	AS
	(
		SELECT P.Id RecurringPatternId
		FROM RecurringRule R WITH (NOLOCK)
		INNER JOIN RecurringPattern P WITH (NOLOCK) ON P.Id=R.FK_RecurringPatternId
		WHERE R.RuleType=@OnlineWareOrderingRuleType
		AND CAST(P.StartRecurringDateTime AS date) <= @Today
		AND (CAST(P.EndRecurringDateTime as int) <=0 OR  CAST(P.EndRecurringDateTime as date) >= @Today)
	)
	INSERT INTO @R (RecurringPatternId, WareNumber, WareGroupNumber)
	SELECT R.RecurringPatternId, W.WareNumber, W.WareGroupNumber
	FROM R
	INNER JOIN WareLinkGroup WLG WITH (NOLOCK) ON WLG.FK_RecurringPatternId=R.RecurringPatternId
	INNER JOIN Ware W ON W.WareLinkNumber=WLG.Id AND ISNULL(W.markasdeleted, 0) = 0 
	UNION
	SELECT R.RecurringPatternId, W.WareNumber, W.WareGroupNumber
	FROM R
	INNER JOIN RecurringPatternWareXTab X WITH (NOLOCK) ON X.FK_RecurringPatternId=R.RecurringPatternId
	INNER JOIN Ware W ON W.WareNumber=X.FK_WareNumber AND ISNULL(W.markasdeleted, 0) = 0

	SELECT CO.CountryNumber, CO.ISO_3166_A2
	FROM dbo.CUSTOMER CU
	INNER JOIN dbo.Countries CO ON CO.CountryNumber=CU.CountryNumber
	WHERE CU.CustomerTypeNumber=7
	AND CU.HotelNumber=@hotelNumber

	SELECT R.WareNumber, S.StartDateTime, S.EndDateTime, S.FK_RecurringPatternId RecurringPatternId
	FROM RecurringPatternSpecified S
	INNER JOIN @R R ON R.RecurringPatternId=S.FK_RecurringPatternId
	WHERE S.Deleted=0
	AND CAST(S.StartDateTime AS date) <= @Today
	AND (CAST(S.EndDateTime as int) <=0 OR  CAST(S.EndDateTime as date) >= @Today)

	SELECT 
		WG.WareGroupNumber, 
		ISNULL(T.Name, WG.Text) WareGroupName
	FROM WareGroups WG WITH (NOLOCK)
	CROSS APPLY (SELECT TOP 1 * FROM @R R WHERE R.WareGroupNumber=WG.WareGroupNumber) 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

	SELECT 
		W.WareGroupNumber,
		W.WareNumber, 
		ISNULL(T.Name, W.WareName) as WareName, 
		ISNULL(W.Price, 0) As Price, 
		COALESCE (T.Description1, W.Note, '') As Description,
		W.WareName As PicassoWareName
	FROM Ware W WITH (NOLOCK)
	INNER JOIN @R R ON R.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 Translation T WITH (NOLOCK) ON T.ObjectNumber = W.Warenumber AND T.Countrynumber = @countrynumber and T.Type = 1
	;

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

END
