CREATE PROCEDURE [dbo].[LayoutInner] @ReceiptLayoutId SmallInt, @GetDatasetKind SmallInt, @HotelCustomerId INT, @LocaleId INT
, @lOrderDateOriginal BIT, @lOrderDate BIT
AS
SET NOCOUNT ON
--nb20230816 Transaction independant 4 ArrangementLines scenario support added
--nb20220719 Created: Current implementation for kitchen receipt purpose only: lGroupKind=TotalPrice & Order by WG and ware
--intended to be extended 4 Invoice & Bill receipts
DECLARE @DebugLevel TINYINT
SET @DebugLevel = 0

DECLARE @HotelNumber INT, @PkReceiptLayout SmallInt, @LFkCustomerHotel INT
SET @PkReceiptLayout = @ReceiptLayoutId
SET @LFkCustomerHotel= @HotelCustomerId
SELECT @HotelNumber = C.HotelNumber FROM dbo.Customer C WITH (NOLOCK) WHERE C.CustomerNumber = @LFkCustomerHotel
SET @HotelNumber = ISNULL(@HotelNumber, -1)
;WITH LayoutCTE (WareNumber, lGroupIndex, lGroupKind, lOrderKind, lOrderWG, lOrderW, lPeriodGroupKind, lText) AS (
SELECT W.WareNumber, ISNULL(LWG.GroupIndex, 0) AS lGroupIndex, ISNULL(LW.SortTypeNumber, LWG.SortTypeNumber) AS lGroupKind
, ISNULL(LW.FKBillSortOrder, LWG.FKBillSortOrder) AS lOrderKind, ISNULL(LWG.OrderNumber, WG.SortNumber) AS lOrderWG
, ISNULL(LW.OrderNumber, W.SortNumber) AS lOrderW, ISNULL(LW.GroupByPeriod, LWG.GroupByPeriod) AS lPeriodGroupKind
, CASE ISNULL(LW.TextOverwrite, 2) 
  WHEN 2 THEN CASE WHEN ISNULL(LWG.TextOverwrite, 1) = 1 THEN ISNULL(LLWG.Name, LWG.Text) ELSE NULL END
  WHEN 1 THEN ISNULL(LLW.Name, LW.Text) 
  ELSE NULL END AS lText
FROM dbo.Ware W (NOLOCK)
  JOIN dbo.WareGroups WG (NOLOCK) ON WG.WareGroupNumber = W.WareGroupNumber
  LEFT JOIN dbo.WareGroupBillSortTypesXTab LWG (NOLOCK) ON LWG.WaregroupNumber = W.WareGroupNumber AND LWG.BillTypeNumber = @PkReceiptLayout
  LEFT JOIN dbo.WareBillSortTypesXTab LW (NOLOCK) ON LW.WareNumber = W.WareNumber AND (LW.BillTypeNumber = @PkReceiptLayout)
  LEFT JOIN dbo.Translation L WITH (NOLOCK) ON L.ObjectNumber = W.WareNumber AND L.TYPE = 1 AND L.CountryNumber = @LocaleId
  LEFT JOIN dbo.Translation LLW (NOLOCK) ON LLW.ObjectNumber = LW.ItemId AND LLW.CountryNumber = @LocaleId AND LLW.Type = 16 --ttReceiptLayoutWare
  LEFT JOIN dbo.Translation LLWG (NOLOCK) ON LLWG.ObjectNumber = LWG.ItemId AND LLWG.CountryNumber = @LocaleId AND LLWG.Type = 17 --ttReceiptLayoutWaregroup
WHERE W.WareNumber IN (SELECT LC.WareNumber FROM #LayoutCommon LC) 
) 
INSERT INTO #TransactionLayout
SELECT SUM(LC.PriceXTax) AS PriceXTax, SUM(LC.PriceTax) AS PriceTax, SUM(LC.Price) AS Price
, MAX(LC.TaxPercent) AS TaxPercent--?
, SUM(LC.Pieces) AS Pieces, MIN(ISNULL(LCE.lText, LC.Name)) AS TextOverwrite
, MIN(ISNULL(LC.Note, '')) AS Note, MIN(LC.TransactionNumber) AS TransactionNumber, MIN(LC.WareNumber) AS WareNumber
, MIN(LC.GuestNumber) AS GuestNumber, MIN(LC.DateStamp) AS Date, MIN(LC.OriginalDate) AS OriginalDate, MIN(LC.LogicSortNumber) AS LogicSortNumber
, MIN(LCE.lOrderWG) AS OrderWGRL, MIN(LCE.lOrderW) AS OrderWRL
, MAX(CASE ISNULL(LCE.lOrderKind, 0) 
  WHEN 1 THEN ISNULL(LC.GuestNumber,0)
  WHEN 2 THEN CONVERT(INT, ISNULL(LC.Note, '0'))
  WHEN 3 THEN RT.SortNumber
  ELSE ISNULL(LCE.lOrderKind, 0) END) AS OrderRL, MAX(LC.PriceSingle) AS PriceSingle
, MAX(CASE WHEN LCE.lGroupKind = 6 THEN 1 ELSE 0 END) AS Summerize
, MAX(CASE WHEN LC.ProductWareLinkNumber > 0 THEN LC.ProductNumber ELSE NULL END) AS ProductNumber, MIN(LC.Link) AS Link, MIN(LC.LinkX) AS LinkX
--, WG.Payment AS PaymentWG, TR.ProductWareLinkNumber
--, CASE WHEN W.Type=4 THEN 5 ELSE TR.Payment END AS Payment, RT.RoomtypeNumber
FROM #LayoutCommon LC (NOLOCK) 
  LEFT JOIN dbo.Rooms RM WITH (NOLOCK) ON CAST(RM.Number AS VARCHAR(50)) = LC.Note and (@HotelNumber = -1 OR RM.HotelNumber = @HotelNumber)
  LEFT JOIN dbo.RoomTypes RT WITH (NOLOCK) ON RT.RoomTypeNumber = RM.RoomTypeNumber
  LEFT JOIN LayoutCTE LCE ON LCE.WareNumber = LC.WareNumber
WHERE LC.LogicSortNumber = 99 --Do not disable, only main lines, package content is summerized in parallel
GROUP BY CASE lPeriodGroupKind WHEN 1 THEN CASE @lOrderDateOriginal WHEN 1 THEN YEAR(OriginalDate) ELSE YEAR(LC.DateStamp) END ELSE NULL END
, CASE lPeriodGroupKind WHEN 1 THEN CASE @lOrderDateOriginal WHEN 1 THEN MONTH(OriginalDate) ELSE MONTH(LC.DateStamp) END ELSE NULL END
, CASE WHEN LC.ProductNumber = -2 THEN LC.ProductWareLinkNumber ELSE NULL END  --Kitchen group outer by kitchenstatementnumber & PrinterNumber
, CASE WHEN LCE.lGroupKind = 2 THEN LC.WareNumber ELSE LC.TransactionNumber END 
, CASE WHEN LCE.lGroupKind = 2 THEN LC.Note ELSE NULL END 
--DELETE records with no show on bill line
DELETE #TransactionLayout WHERE Link IN (SELECT Link FROM #TransactionLayout TL1 (NOLOCK) WHERE NOT EXISTS (
  SELECT * FROM #TransactionLayout TL2 (NOLOCK) WHERE TL1.Link = TL2.Link AND LogicSortNumber=99))

DECLARE @LOrderBySQL VARCHAR(150), @LSelectSQL VARCHAR(600), @LDistinctSQL VARCHAR(10)
  SET @LSelectSQL = ''
  SET @LDistinctSQL = ''
  SET @LOrderBySQL = 'ORDER BY '
  IF @PkReceiptLayout<=0
    IF @lOrderDateOriginal = 0
      SET @LOrderBySQL = @LOrderBySQL+'Date, OriginalDate, TransactionNumber'
    ELSE
      SET @LOrderBySQL = @LOrderBySQL+'OriginalDate, Date, TransactionNumber'
  ELSE  
    IF @lOrderDateOriginal = 0
      IF @lOrderDate = 0
        SET @LOrderBySQL = @LOrderBySQL+'OrderWGRL, OrderWRL, OrderRL, Date, OriginalDate, TransactionNumber'
      ELSE
        SET @LOrderBySQL = @LOrderBySQL+'Date, OriginalDate, OrderWGRL, OrderWRL, OrderRL, TransactionNumber'
    ELSE IF @lOrderDate = 0
      SET @LOrderBySQL = @LOrderBySQL+'OrderWGRL, OrderWRL, OrderRL, OriginalDate, Date'
    ELSE
      SET @LOrderBySQL = @LOrderBySQL+'OriginalDate, Date, OrderWGRL, OrderWRL, OrderRL'
  SET @LSelectSQL = 'UPDATE #TransactionLayout SET Link = TS.RowNumber FROM (SELECT ROW_NUMBER() OVER('+@LOrderBySQL+
    ') AS RowNumber, Link, LinkX FROM #TransactionLayout (NOLOCK) WHERE LogicSortNumber=99) AS TS WHERE TS.Link = #TransactionLayout.Link AND TS.LinkX = #TransactionLayout.LinkX'
  EXEC (@LSelectSQL)  
--  PRINT @LSelectSQL

--SELECT * FROM #TransactionLayout TL
