CREATE PROCEDURE [dbo].[GetKitchen4Output] @FolioId INT, @ReceiptLayoutId INT, @NoteIdCSV VARCHAR(50)
AS
SET NOCOUNT ON
--nb20180820 Created
/*
Paraams:
@FolioId INT, 
@ReceiptLayoutId INT, 
@NoteId INT : NoteId value
Used fields
ProductNumber, DateStamp, OriginalDate, WareNumber, Pieces, Price, Note, TotalPrice, Vat, PriceExclVAT 
*/

CREATE TABLE dbo.#Transaction(
  Copy BIT NOT NULL,
  KitchenStatementNumber INT NOT NULL,
  KitchenNoteNumber INT NULL,
  StatementNote VARCHAR(250) NULL,
  Note VARCHAR(50) NULL,  
  Changed BIT NULL,
  TransactionNumber int NOT NULL,
  DateStamp DATETIME NULL,
  OriginalDate DATETIME NULL,
  ReceiptText VARCHAR(250),
  Pieces INT NOT NULL,
  Price MONEY NULL,
  MovedDeleted BIT NULL,
  MovedFolioNr INT NULL,
  WareNumber INT not NULL,  
  UserCode VARCHAR(50) NULL, 
  PrinterNumber INT NULL, 
  ConferenceRoomNumber INT NULL,
  TableNumber INT NULL,
  ChairNumber INT NULL,
  TablePax INT NULL,
  RoomNumber INT NULL,
  HallNumber INT NULL,
  MovedTableNumber INT NULL,
  TableType VARCHAR(50) NULL,

  TotalPrice MONEY NULL,
  Vat MONEY NULL,
  PriceExclVAT MONEY NULL,
  WareGroupSortNumberOnBill INT NULL,
  WareOrderNumber INT NULL
  )
DECLARE @CursorOpen BIT
, @DoInsert TINYINT
--select statement fields begin
, @Copy BIT
, @KitchenStatementNumber INT
, @KitchenNoteNumber INT
, @StatementNote VARCHAR(250)
, @Note VARCHAR(50)
, @Changed BIT 
, @TransactionNumber INT
, @DateStamp DATETIME
, @OriginalDate DATETIME
, @ReceiptText VARCHAR (250)
, @Pieces INT 
, @Price MONEY
, @MovedDeleted BIT
, @MovedFolioNr INT
, @WareNumber INT 
, @UserCode VARCHAR(50)
, @PrinterNumber INT
, @ConferenceRoomNumber INT
, @TableNumber INT
, @ChairNumber INT
, @TablePax INT
, @RoomNumber INT
, @HallNumber INT
, @MovedTableNumber INT
, @TableType VARCHAR(50)
--select statement fields end
--sort helper fields begin
, @TotalPrice FLOAT --Money is not precise enough for summerize functionality
, @Vat MONEY
, @PriceExclVAT MONEY
, @BillLayoutOrderByDate BIT
, @LFkCustomerHotel INT
, @InternalCourseProduction BIT
, @WareGroupSortNumberOnBill INT
, @WareOrderNumber INT
, @SortPeriodNumber INT
, @ShowPrint BIT
--sort helper fields end
SET @ShowPrint = 0
IF @ReceiptLayoutId=0
  SET @DoInsert = 1
ELSE
BEGIN
  SELECT @BillLayoutOrderByDate = OrderByDate FROM Billtypes WITH (NOLOCK) WHERE BillTypeNumber = @ReceiptLayoutId 
  SELECT @LFkCustomerHotel = HotelCustomerNumber FROM Folios WITH (NOLOCK) WHERE FolioNumber = @FolioId
/*@ShowDepositAsWare = ISNULL(dbo.GetHotelSetupKeyValue ('ShowDepositAsWare', @LFkCustomerHotel), 0),
  @ShowExpensesAsWare = ISNULL(dbo.GetHotelSetupKeyValue ('ShowExpensesAsWare', @LFkCustomerHotel), 1),
  @ShowTipsAsWare = ISNULL(dbo.GetHotelSetupKeyValue ('ShowTipsAsWare', @LFkCustomerHotel), 0),
  @ExtMovedInfo = ISNULL(dbo.GetHotelSetupKeyValue ('ShowMovedFromOnBill', @LFkCustomerHotel), 0),
  @ExtOriginalDateInfo = ISNULL(dbo.GetHotelSetupKeyValue ('ShowOriginalDateOnBill', @LFkCustomerHotel), 0),
*/SELECT @InternalCourseProduction = ISNULL(dbo.GetHotelSetupKeyValue ('InternalCourseProduction', @LFkCustomerHotel), 0)
END

SET @CursorOpen = 0
IF @NoteIdCSV <> 'o' --Folio Kitchen
BEGIN
  DECLARE #KitchenCur CURSOR FAST_FORWARD READ_ONLY FOR
  WITH KitchenCTE (TransactionId, StatementId, Note, Changed) AS (
  SELECT TKSX.TransactionNumber, TKSX.KitchenStatementNumber, TKSX.Note, TKSX.Changed
  FROM dbo.Transactions TR WITH (NOLOCK)
    INNER JOIN dbo.TransactionKitchenStatementXTab TKSX WITH (NOLOCK) ON TR.TransactionNumber = TKSX.TransactionNumber AND 
      (TKSX.Changed = 1 or TKSX.Changed IS NULL)
  WHERE TR.FolioNumber = @FolioId
  UNION
  SELECT TKSX.TransactionNumber, TKSX.KitchenStatementNumber, TKSX.Note, TKSX.Changed
  FROM dbo.Transactions TR WITH (NOLOCK)
    INNER JOIN dbo.TransactionKitchenStatementXTab TKSX WITH (NOLOCK) ON TR.TransactionNumber = TKSX.TransactionNumber AND 
      (@NoteIdCSV <> '' or TKSX.Changed = 1 or TKSX.Changed IS NULL)
  WHERE TR.FolioNumber = @FolioId
  )
  SELECT CAST(0 AS BIT) AS Copy, KS.KitchenStatementNumber
  , 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.Note, TKSX.Changed, TR.TransactionNumber, TR.DateStamp, TR.OriginalDate, TR.TextOverwrite, TR.Pieces, TR.PriceOverwrite, TR.MovedDeleted, TR.MovedFolioNr, TR.WareNumber
  , Personel.UserIDCode AS UserCode
  , WPX.PrinterNumber, Ware.SortNumber, THL.ConferenceRoomNumber
  , case ISNULL(FRTCX.ChairNumber,0) when 0 then THall.Number else THall1.Number end AS TableNumber
  , FRTCX.ChairNumber AS ChairNumber
  , (ISNULL(THL.Adults, 0) + ISNULL(THL.Children1, 0) + ISNULL(THL.Children2, 0)) AS TablePax
  , (SELECT TOP 1 Number FROM ReservationRoomLineXtab RLX (NOLOCK)
    INNER JOIN ReservationRoomline RL (NOLOCK) ON RLX.LineNumber = RL.LINENUMBER
    INNER JOIN Rooms (NOLOCK) ON Rooms.RoomNumber= RL.RoomNumber
  WHERE RLX.ReferenceNumber = F.ReferenceNumber AND RLX.GuestNumber = F.GuestNumber) AS RoomNumber
  , (SELECT TOP 1 Number FROM ReservationConferenceLine HL (NOLOCK)
    INNER JOIN ConferenceRooms Hall (NOLOCK) ON HL.ConferenceRoomNumber = Hall.ConferenceRoomNumber
  WHERE HL.ReferenceNumber = F.ReferenceNumber) 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 FRTCX.FolioNumber =MovedFolioNr ORDER BY THL1.ScreenLine) As MovedTableNumber
  , (SELECT TOP 1 CRT.Type FROM ConferenceRooms THTT(NOLOCK) 
   INNER JOIN ConferenceRoomTypes CRT (NOLOCK) ON CRT.ConferenceRoomTypeNumber = THTT.ConferenceRoomTypeNumber
  WHERE THTT.ConferenceRoomNumber=THL.ConferenceRoomNumber) AS TableType
  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
    JOIN dbo.Ware WITH (NOLOCK) ON TR.WareNumber = Ware.WareNumber
      AND (@NoteIdCSV = '' or Ware.fkKitchenNote IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@NoteIdCSV)))
    JOIN dbo.WarePrinterXtab WPX (NOLOCK) ON Ware.WareNumber = WPX.WareNumber
    JOIN dbo.Printers WITH (NOLOCK) ON WPX.PrinterNumber = Printers.PrinterNumber
    INNER JOIN dbo.Personel WITH (NOLOCK) ON TR.UserIDNumber = PERSONEL.UserIDNumber
    LEFT JOIN dbo.FolioRestResTableChairsXTab FRTCX WITH (NOLOCK) ON F.FolioNumber = FRTCX .FolioNumber
    LEFT JOIN dbo.ReservationConferenceLine THL 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 dbo.KitchenStatement KSF WITH (NOLOCK) ON KSF.KitchenStatementNumber in (
      SELECT TOP 1 KitchenStatementNumber FROM KitchenStatement (NOLOCK) WHERE FolioNumber = @FolioId ORDER BY KitchenStatementNumber DESC)
  ORDER BY Ware.PrinterNumber, KS.KitchenStatementNumber, TR.TransactionNumber
END
ELSE
BEGIN --Order Kitchen
  DECLARE #KitchenCur CURSOR FAST_FORWARD READ_ONLY FOR
  SELECT CAST(0 AS BIT) AS Copy, ORO.ReservationOrderId AS KitchenStatementNumber, NULL AS KitchenNoteNumber
  --, 'Delivery Time: '+ ISNULL(CONVERT(VARCHAR, ORO.DeliveryTime, 108), 'Empty') + ' Place: ' + ISNULL(ORO.DeliveryPlace, 'Empty') AS StatementNote
  , ISNULL(CONVERT(VARCHAR, ORO.DeliveryTime, 108), 'ASAP') + ' ' + ISNULL(ORO.DeliveryPlace, '% place') AS StatementNote
  , NULL AS Note, CAST(0 AS BIT) AS Changed
  , TR.TransactionNumber, TR.DateStamp, TR.OriginalDate, TR.TextOverwrite, TR.Pieces, TR.PriceOverwrite, TR.MovedDeleted, TR.MovedFolioNr, TR.WareNumber
  , NULL AS UserCode, WPX.PrinterNumber, Ware.SortNumber, NULL AS ConferenceRoomNumber, NULL AS TableNumber, NULL AS ChairNumber, NULL AS TablePax
  , (SELECT TOP 1 Number FROM ReservationRoomLineXtab RLX (NOLOCK)
    INNER JOIN ReservationRoomline RL (NOLOCK) ON RLX.LineNumber = RL.LINENUMBER
    INNER JOIN Rooms (NOLOCK) ON Rooms.RoomNumber= RL.RoomNumber
    WHERE RLX.ReferenceNumber = ORO.ReferenceNumber AND RLX.GuestNumber = ORO.GuestNumber) AS RoomNumber
  , NULL AS HallNumber, NULL AS MovedTableNumber, NULL AS TableType
  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
    JOIN dbo.Ware WITH (NOLOCK) ON TR.WareNumber = Ware.WareNumber 
    JOIN dbo.WarePrinterXtab WPX (NOLOCK) ON Ware.WareNumber = WPX.WareNumber
  WHERE ORO.ReservationOrderId = @FolioId
  ORDER BY Ware.PrinterNumber, TR.TransactionNumber
END
OPEN #KitchenCur
SET @CursorOpen = 1
FETCH NEXT FROM #KitchenCur
INTO @Copy, @KitchenStatementNumber, @KitchenNoteNumber, @StatementNote, @Note, @Changed, @TransactionNumber, @DateStamp, @OriginalDate
, @ReceiptText, @Pieces, @TotalPrice, @MovedDeleted, @MovedFolioNr, @WareNumber, @UserCode, @PrinterNumber, @WareOrderNumber, @ConferenceRoomNumber
, @TableNumber, @ChairNumber, @TablePax, @RoomNumber, @HallNumber, @MovedTableNumber, @TableType
WHILE @@FETCH_STATUS = 0 -- safe
BEGIN
  SET @WareGroupSortNumberOnBill = 0 -- Cannot insert NULL into #Transactions.WareGroupSortNumberOnBill
  SET @Price = CONVERT(MONEY, @TotalPrice/@Pieces)
  SET @Vat = 0
  SET @PriceExclVat = 0
  SET @Note = ISNULL(@Note, '')
  IF @ReceiptLayoutId>0
  BEGIN
    DECLARE @SortOrderNumberDummy INT
 	  EXEC dbo.TransactionDecorate @WareNumber, -2, @ReceiptLayoutId, @DateStamp, @OriginalDate
    , 0, 0, 0, @Note, @TotalPrice, @Vat, @PriceExclVAT
    , @Pieces, @Price, 0, @InternalCourseProduction, 0, @BillLayoutOrderByDate, 0, 0, 0
    , @WareGroupSortNumberOnBill OUTPUT, @WareOrderNumber OUTPUT, @SortOrderNumberDummy OUTPUT, @SortPeriodNumber OUTPUT, @DoInsert OUTPUT, @ReceiptText OUTPUT
  END
IF @ShowPrint = 1
BEGIN
  PRINT @DoInsert
END
  IF @DoInsert > 0
    INSERT INTO #Transaction(Copy, KitchenStatementNumber, KitchenNoteNumber, StatementNote, Note, Changed, TransactionNumber, DateStamp, OriginalDate
    , ReceiptText, Pieces, Price, TotalPrice, MovedDeleted, MovedFolioNr, WareNumber, UserCode, PrinterNumber, WareOrderNumber, ConferenceRoomNumber
    , TableNumber, ChairNumber, TablePax, RoomNumber, HallNumber, MovedTableNumber, TableType, Vat, PriceExclVAT, WareGroupSortNumberOnBill) VALUES
    (@Copy, @KitchenStatementNumber, @KitchenNoteNumber, @StatementNote, @Note, @Changed, @TransactionNumber, @DateStamp, @OriginalDate
    , @ReceiptText, @Pieces, @Price, @TotalPrice, @MovedDeleted, @MovedFolioNr, @WareNumber, @UserCode, @PrinterNumber, @WareOrderNumber, @ConferenceRoomNumber
    , @TableNumber, @ChairNumber, @TablePax, @RoomNumber, @HallNumber, @MovedTableNumber, @TableType, @Vat, @PriceExclVAT, @WareGroupSortNumberOnBill)
  FETCH NEXT FROM #KitchenCur
  INTO @Copy, @KitchenStatementNumber, @KitchenNoteNumber, @StatementNote, @Note, @Changed, @TransactionNumber, @DateStamp, @OriginalDate
  , @ReceiptText, @Pieces, @TotalPrice, @MovedDeleted, @MovedFolioNr, @WareNumber, @UserCode, @PrinterNumber, @WareOrderNumber, @ConferenceRoomNumber
  , @TableNumber, @ChairNumber, @TablePax, @RoomNumber, @HallNumber, @MovedTableNumber, @TableType
END
CLOSE #KitchenCur
DEALLOCATE #KitchenCur
IF @ReceiptLayoutId=0
  SELECT DISTINCT Copy, KitchenStatementNumber, KitchenNoteNumber, StatementNote, T.Note, Changed, TransactionNumber
  , ReceiptText AS TextOverwrite, Pieces, TotalPrice AS PriceOverwrite, MovedDeleted, MovedFolioNr, UserCode, T.PrinterNumber
  , ConferenceRoomNumber, TableNumber, ChairNumber, TablePax, RoomNumber, HallNumber, MovedTableNumber, TableType, WareGroupSortNumberOnBill, W.Note AS WareNote
  FROM #Transaction T WITH (nolock) 
    JOIN Ware W (nolock) on W.WareNumber = T.WareNumber 
  ORDER BY PrinterNumber, KitchenStatementNumber, WareGroupSortNumberOnBill, TransactionNumber
ELSE IF @BillLayoutOrderByDate = 0
  SELECT DISTINCT Copy, KitchenStatementNumber, KitchenNoteNumber, StatementNote, T.Note, Changed, TransactionNumber
  , ReceiptText AS TextOverwrite, Pieces, TotalPrice AS PriceOverwrite, MovedDeleted, MovedFolioNr, UserCode, T.PrinterNumber
  , ConferenceRoomNumber, TableNumber, ChairNumber, TablePax, RoomNumber, HallNumber, MovedTableNumber, TableType, WareGroupSortNumberOnBill, W.Note AS WareNote
  FROM #Transaction T WITH (nolock) 
    JOIN Ware W (nolock) on W.WareNumber = T.WareNumber 
  ORDER BY PrinterNumber, KitchenStatementNumber, WareGroupSortNumberOnBill, WareOrderNumber, DateStamp, OriginalDate
ELSE
  SELECT Copy, KitchenStatementNumber, KitchenNoteNumber, StatementNote, T.Note, Changed, TransactionNumber
  , ReceiptText AS TextOverwrite, Pieces, TotalPrice AS PriceOverwrite, MovedDeleted, MovedFolioNr, UserCode, T.PrinterNumber
  , ConferenceRoomNumber, TableNumber, ChairNumber, TablePax, RoomNumber, HallNumber, MovedTableNumber, TableType, WareGroupSortNumberOnBill, W.Note AS WareNote
  FROM #Transaction T WITH (nolock) 
    JOIN Ware W (nolock) on W.WareNumber = T.WareNumber 
  ORDER BY PrinterNumber, KitchenStatementNumber, CONVERT(VARCHAR,DateStamp,112), CONVERT(VARCHAR,OriginalDate,112), WareGroupSortNumberOnBill, WareOrderNumber
DROP TABLE #Transaction

