CREATE PROCEDURE [dbo].[LayoutKitchen] @CustomId INT, @ReceiptLayoutId INT, @NoteIdCSV VARCHAR(50)
AS
SET NOCOUNT ON
--nb20230816 ArrangementLines scenario support added
--nb20220719 Created
/*
Params:
@FolioId INT, 
@ReceiptLayoutId INT, 
@NoteIdCSV: NoteId CSV; o= By OrderId; or s= By KitchenStatmentId 
*/
--exec dbo.GetKitchen4Output @CustomId,@ReceiptLayoutId,@NoteIdCSV
DECLARE @OrderId INT
IF @NoteIdCSV = 'o'
  SET @OrderId = @CustomId

  ;WITH KitchenCTE (TransactionId, StatementId, Note, Changed) AS (
  SELECT TKSX.TransactionNumber, TKSX.KitchenStatementNumber, TKSX.Note, TKSX.Changed
  FROM dbo.Transactions TR WITH (NOLOCK)
    JOIN dbo.TransactionKitchenStatementXTab TKSX WITH (NOLOCK) ON TR.TransactionNumber = TKSX.TransactionNumber 
	  AND TKSX.Changed <> 0
  WHERE @NoteIdCSV <> 'o' AND @NoteIdCSV <> 's' AND TR.FolioNumber = @CustomId
  UNION
  SELECT TKSX.TransactionNumber, TKSX.KitchenStatementNumber, TKSX.Note, TKSX.Changed
  FROM dbo.Transactions TR WITH (NOLOCK)
    JOIN dbo.TransactionKitchenStatementXTab TKSX WITH (NOLOCK) ON TR.TransactionNumber = TKSX.TransactionNumber 
	  AND (@NoteIdCSV <> '' and TR.MovedDeleted <> 1 or TKSX.Changed = 1 or TKSX.Changed IS NULL)
	--nb20220804 Call statements mod
    JOIN dbo.Ware W (NOLOCK) ON TR.WareNumber = W.WareNumber
      AND (@NoteIdCSV = '' or W.fkKitchenNote IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@NoteIdCSV))) 

  WHERE @NoteIdCSV <> 'o' AND @NoteIdCSV <> 's' AND TR.FolioNumber = @CustomId
  UNION 
  SELECT TKSX.TransactionNumber, TKSX.KitchenStatementNumber, TKSX.Note, TKSX.Changed
  FROM dbo.Transactions TR WITH (NOLOCK)
    JOIN dbo.TransactionKitchenStatementXTab TKSX WITH (NOLOCK) ON TR.TransactionNumber = TKSX.TransactionNumber 
  WHERE @NoteIdCSV = 's' AND TKSX.KitchenStatementNumber = @CustomId
  )

SELECT * 
INTO #LayoutCommon
FROM (
  --layout fields
  SELECT NULL AS PriceXTax, NULL AS PriceTax, TR.PriceOverwrite AS Price, TR.PriceOverwrite/ABS(TR.Pieces) AS PriceSingle, NULL AS TaxPercent, TKSX.Note
  , TR.TransactionNumber, TR.GuestNumber, TR.GlobalTransactionNumber AS Link, 0 AS LinkX, 99 AS LogicSortNumber
  , -2 AS ProductNumber, NULL AS ProductWareLinkNumber, NULL AS Payment, NULL AS RoomtypeNumber, NULL AS SortNumber
  , (SELECT TOP 1 PrinterNumber FROM dbo.WarePrinterXtab WPX (NOLOCK) WHERE TR.WareNumber = WPX.WareNumber Order By PrinterNumber ASC) AS PrinterNumber
  --Custom fields  
  , KS.KitchenStatementNumber, F.ReferenceNumber
  , CASE WHEN ISNULL(KSF.KitchenNoteNumber, 0) > 0 AND TKSX.Changed = 1 THEN KSF.KitchenNoteNumber ELSE KS.KitchenNoteNumber END AS KitchenNoteNumber
  , CASE WHEN ISNULL(KSF.Note, '') <> '' AND TKSX.Changed = 1 THEN KSF.Note ELSE KS.Note END AS StatementNote
  , TKSX.Changed
  , TR.Pieces
  , TR.WareNumber
  , TR.DateStamp
  , TR.OriginalDate
  , TR.TextOverwrite AS Name
  FROM KitchenCTE TKSX
    JOIN dbo.TRANSACTIONS TR WITH (NOLOCK) ON TR.TransactionNumber = TKSX.TransactionId
    JOIN dbo.FOLIOS F WITH (NOLOCK) ON TR.FolioNumber = F.FolioNumber
    JOIN dbo.KitchenStatement KS WITH (NOLOCK) ON KS.KitchenStatementNumber = TKSX.StatementId
--      AND (@NoteIdCSV = '' or @NoteIdCSV = 's' or Ware.fkKitchenNote IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@NoteIdCSV)))
    LEFT JOIN dbo.KitchenStatement KSF WITH (NOLOCK) ON KSF.KitchenStatementNumber in (
      SELECT TOP 1 KitchenStatementNumber FROM KitchenStatement (NOLOCK) WHERE FolioNumber = @CustomId ORDER BY KitchenStatementNumber DESC)
  UNION
  SELECT NULL AS PriceXTax, NULL AS PriceTax, TR.PriceOverwrite AS Price, TR.PriceOverwrite/ABS(TR.Pieces) AS PriceSingle, NULL AS TaxPercent, NULL AS Note
  , TR.TransactionNumber, ORO.GuestNumber, TR.GlobalTransactionNumber AS Link, 0 AS LinkX, 99 AS LogicSortNumber
  , -2 AS ProductNumber, NULL AS ProductWareLinkNumber, NULL AS Payment, NULL AS RoomtypeNumber, NULL AS SortNumber
  , (SELECT TOP 1 PrinterNumber FROM dbo.WarePrinterXtab WPX (NOLOCK) WHERE TR.WareNumber = WPX.WareNumber Order By PrinterNumber ASC) AS PrinterNumber
  --Custom fields
  , ORO.ReservationOrderId AS KitchenStatementNumber, ORO.ReferenceNumber
  , NULL AS KitchenNoteNumber
  , ISNULL(CONVERT(VARCHAR, ORO.DeliveryTime, 105) + ' ' + CONVERT(CHAR(5), ORO.DeliveryTime, 108), 'ASAP')  + ' ' + ISNULL(ORO.DeliveryPlace, '% place') AS StatementNote
  , CAST(0 AS BIT) AS Changed
  , TR.Pieces
  , TR.WareNumber
  , TR.DateStamp
  , TR.OriginalDate
  , TR.TextOverwrite 
  FROM dbo.OnlineReservationOrder ORO WITH (NOLOCK) 
    JOIN dbo.OnlineReservationOrderItem OROI WITH (NOLOCK) ON OROI.ReservationOrderId = ORO.ReservationOrderId
    JOIN dbo.TRANSACTIONS TR WITH (NOLOCK) ON OROI.TransactionNumber = TR.TransactionNumber
  WHERE ORO.ReservationOrderId = @OrderId
  UNION
  SELECT NULL, NULL, L.Price, L.Price/ABS(L.Pieces), NULL, NULL
  , L.ArrangementLineNumber, ORO.GuestNumber, L.ArrangementLineNumber AS Link, 0 AS LinkX, 99 AS LogicSortNumber
  , -2 AS ProductNumber, NULL AS ProductWareLinkNumber, NULL AS Payment, NULL AS RoomtypeNumber, NULL AS SortNumber
  , (SELECT TOP 1 PrinterNumber FROM dbo.WarePrinterXtab WPX (NOLOCK) WHERE L.WareNumber = WPX.WareNumber Order By PrinterNumber ASC) AS PrinterNumber
  --Custom fields
  , ORO.ReservationOrderId AS KitchenStatementNumber, ORO.ReferenceNumber
  , NULL AS KitchenNoteNumber
  , ISNULL(CONVERT(VARCHAR, ORO.DeliveryTime, 105) + ' ' + CONVERT(CHAR(5), ORO.DeliveryTime, 108), 'ASAP')  + ' ' + ISNULL(ORO.DeliveryPlace, '% place') AS StatementNote
  , CAST(0 AS BIT) AS Changed
  , L.Pieces
  , L.WareNumber
  , L.Date
  , L.Date
  , L.Waretext 
  FROM dbo.OnlineReservationOrder ORO WITH (NOLOCK) 
    JOIN OnlineReservationOrderArrangementLine OL ON OL.ReservationOrderId=ORO.ReservationOrderId
    JOIN ArrangementLines L ON L.ArrangementLineNumber=OL.ArrangementLineNumber
  WHERE ORO.ReservationOrderId = @OrderId
) TC

UPDATE #LayoutCommon SET ProductWareLinkNumber = TS.RowNumber FROM --create group outer by kitchenstatementnumber & PrinterNumber 
(SELECT ROW_NUMBER() OVER(ORDER BY PrinterNumber, KitchenStatementNumber) AS RowNumber, PrinterNumber, KitchenStatementNumber FROM #LayoutCommon (NOLOCK)
GROUP BY PrinterNumber, KitchenStatementNumber) AS TS WHERE TS.PrinterNumber = #LayoutCommon.PrinterNumber AND TS.KitchenStatementNumber = #LayoutCommon.KitchenStatementNumber 
CREATE TABLE dbo.#TransactionLayout(
  PriceXTax MONEY NULL
  , PriceTax MONEY NULL
  , Price MONEY NOT NULL
  , TaxPercent INT NULL
  , Pieces INT NOT NULL
  , TextOverwrite VARCHAR(250) NOT NULL
  , Note VARCHAR(400) NULL
  , TransactionNumber INT NULL
  , WareNumber INT NOT NULL
  , GuestNumber INT NULL --4 grouping by receipt layout not used on client
  , Date DATETIME NOT NULL
  , OriginalDate DATETIME NOT NULL
  , LogicSortNumber SMALLINT NOT NULL
  , OrderWGRL INT NOT NULL --WareGroupSortNumberOnBill int not null,
  , OrderWRL INT NOT NULL  --WareOrderNumber int null,

  , OrderRL INT NULL
  , PriceSingle MONEY NULL
  , Summerize bit NULL

  , ProductNumber INT NULL --4 grouping by receipt layout
  , Link INT NOT NULL
  , LinkX INT NOT NULL
)
DECLARE @FolioNumber INT, @LFkCustomerHotel INT, @lOrderDate BIT
IF @NoteIdCSV <> 'o' AND @NoteIdCSV <> 's' --Folio Kitchen
  SELECT @LFkCustomerHotel = HotelCustomerNumber, @FolioNumber = FolioNumber FROM Folios WITH (NOLOCK) WHERE FolioNumber = @CustomId
IF @NoteIdCSV = 's'
  SELECT @LFkCustomerHotel = F.HotelCustomerNumber, @FolioNumber = F.FolioNumber
  FROM dbo.Folios F (NOLOCK) 
    JOIN dbo.Transactions TR (NOLOCK) ON TR.FolioNumber = F.FolioNumber
    JOIN dbo.TransactionKitchenStatementXTab TKSX (NOLOCK) ON TR.TransactionNumber = TKSX.TransactionNumber 
  WHERE TKSX.KitchenStatementNumber = @CustomId
ELSE
  SELECT @LFkCustomerHotel = CASE WHEN NOT F.HotelCustomerNumber IS NULL THEN F.HotelCustomerNumber ELSE RM.FkCustomerHotel END
  FROM dbo.OnlineReservationOrder ORO WITH (NOLOCK)
    LEFT JOIN Folios F (NOLOCK) ON F.FolioNumber = ORO.FolioNumber
	LEFT JOIN ReservationMain RM (NOLOCK) ON RM.ReferenceNumber = ORO.ReferenceNumber
  WHERE ORO.ReservationOrderId = @CustomId
SELECT @lOrderDate = OrderByDate FROM Billtypes WITH (NOLOCK) WHERE BillTypeNumber = @ReceiptLayoutId 
SET @lOrderDate = ISNULL(@lOrderDate, 0)

EXEC dbo.LayoutInner @ReceiptLayoutId, 0, @LFkCustomerHotel, 0, 0, 0

DECLARE @HallId INT, @TableNumber INT, @ChairNumber INT, @TablePax INT, @RoomNumber INT, @HallNumber INT, @TableType VARCHAR(50)
IF @NoteIdCSV <> 'o'
BEGIN
SELECT TOP 1 @HallId = THL.ConferenceRoomNumber, @ChairNumber = FRTCX.ChairNumber
, @TableNumber = case ISNULL(FRTCX.ChairNumber,0) when 0 then THall.Number else THall1.Number end 
, @TablePax = (ISNULL(THL.Adults, 0) + ISNULL(THL.Children1, 0) + ISNULL(THL.Children2, 0))
, @TableType = CRT.Type
FROM dbo.ReservationConferenceLine THL WITH (NOLOCK) 
  JOIN dbo.FolioRestResTableChairsXTab FRTCX WITH (NOLOCK) ON FRTCX.RestaurantReservationNumber = THL.ReservationConfLineNumber
  LEFT JOIN dbo.ConferenceRooms THall WITH (NOLOCK) ON THL.ConferenceRoomNumber = THall.ConferenceRoomNumber
  LEFT JOIN dbo.ConferenceRooms THall1 WITH (NOLOCK) ON FRTCX.TableNumber = THall1.ConferenceRoomNumber
  LEFT JOIN ConferenceRoomTypes CRT (NOLOCK) ON CRT.ConferenceRoomTypeNumber = THall.ConferenceRoomTypeNumber
WHERE FRTCX.FolioNumber = @FolioNumber
SELECT TOP 1 @HallNumber = Number FROM ReservationConferenceLine HL (NOLOCK)
    JOIN ConferenceRooms Hall (NOLOCK) ON HL.ConferenceRoomNumber = Hall.ConferenceRoomNumber
    JOIN Folios F (NOLOCK) ON HL.ReferenceNumber = F.ReferenceNumber
  WHERE F.FolioNumber = @FolioNumber
END
IF @OrderId IS NULL
SELECT TOP 1 @RoomNumber = Number 
FROM ReservationRoomLineXtab RLX (NOLOCK)
  JOIN ReservationRoomline RL (NOLOCK) ON RLX.LineNumber = RL.LINENUMBER
  JOIN Rooms R (NOLOCK) ON R.RoomNumber= RL.RoomNumber
  JOIN Folios F (NOLOCK) ON RLX.ReferenceNumber = F.ReferenceNumber AND RLX.GuestNumber = F.GuestNumber
WHERE F.FolioNumber = @FolioNumber
ELSE
SELECT TOP 1 @RoomNumber = Number 
FROM ReservationRoomLineXtab RLX (NOLOCK)
  JOIN ReservationRoomline RL (NOLOCK) ON RLX.LineNumber = RL.LINENUMBER
  JOIN Rooms R (NOLOCK) ON R.RoomNumber= RL.RoomNumber
  JOIN OnlineReservationOrder ORO (NOLOCK) ON ORO.ReferenceNumber = RLX.ReferenceNumber AND ORO.GuestNumber = RLX.GuestNumber
WHERE ORO.ReservationOrderId = @OrderId

SELECT CAST(0 AS BIT) AS Copy, LC.KitchenStatementNumber, LC.KitchenNoteNumber, LC.StatementNote, TL.Note, LC.Changed
, TL.TransactionNumber, TL.TextOverwrite, TL.Pieces, TL.Price AS PriceOverwrite, TR.MovedDeleted, TR.FolioNumber, TR.MovedFolioNr
, Personel.UserIDCode AS UserCode, WPX.PrinterNumber, @HallId AS ConferenceRoomNumber, @TableNumber AS TableNumber
, @ChairNumber AS ChairNumber, @TablePax AS TablePax, LC.ReferenceNumber, LC.GuestNumber, @RoomNumber AS RoomNumber, @HallNumber AS HallNumber
, (SELECT TOP 1 THall1.Number AS TableNumber 
  FROM FolioRestResTableChairsXTab FRTCX1 (NOLOCK)
  INNER JOIN ReservationConferenceLine THL1 (NOLOCK) ON FRTCX1.RestaurantReservationNumber = THL1.ReservationConfLineNumber
  INNER JOIN ConferenceRooms THall1 (NOLOCK) ON THL1.ConferenceRoomNumber = THall1.ConferenceRoomNumber
  WHERE FRTCX1.FolioNumber = MovedFolioNr ORDER BY THL1.ScreenLine) AS MovedTableNumber
, @TableType AS TableType, TL.OrderWGRL AS WareGroupSortNumberOnBill, W.Note AS WareNote
--Debug fields
--, TL.OrderWRL, TL.OrderRL, TL.Link
  FROM #TransactionLayout TL
    JOIN #LayoutCommon LC ON TL.TransactionNumber = LC.TransactionNumber
    JOIN dbo.Ware W WITH (NOLOCK) ON TL.WareNumber = W.WareNumber
    JOIN dbo.WarePrinterXtab WPX (NOLOCK) ON W.WareNumber = WPX.WareNumber
    JOIN dbo.Printers WITH (NOLOCK) ON WPX.PrinterNumber = Printers.PrinterNumber
    LEFT JOIN dbo.TRANSACTIONS TR WITH (NOLOCK) ON TR.TransactionNumber = TL.TransactionNumber
    LEFT JOIN dbo.Personel WITH (NOLOCK) ON TR.UserIDNumber = PERSONEL.UserIDNumber
  ORDER BY WPX.PrinterNumber, LC.KitchenStatementNumber, TL.Link, TL.LogicSortNumber, TR.TransactionNumber
  DROP TABLE #TransactionLayout
