CREATE PROCEDURE dbo.GetCashierTablesDataset @StartDate datetime, @CheckHalls bit = 0, @StatusCSV varchar(20), @TypeNumbers varchar(255), @HotelNumberCSV varchar(255), @SeatingCSV varchar(8000), @WareNumber Int = 0, @ShowRoomNumber bit = 0, @ShowRoomName bit = 0  AS
BEGIN
SET NOCOUNT ON
/*
  20211104 RN Created
*/

DECLARE @StartDate1 CHAR(8)
DECLARE @EndDate1 CHAR(8)

DECLARE @Arr24Time CHAR(5)
DECLARE @Tabledate CHAR(8)

SET @Tabledate = CONVERT(VARCHAR(8), @StartDate, 112)
SET @StartDate1 = CONVERT(VARCHAR, DATEADD(DAY, 1, CONVERT(DATETIME, @StartDate)), 112)
SET @EndDate1 = CONVERT(VARCHAR, DATEADD(DAY, 1, CONVERT(DATETIME, @StartDate)), 112)

SELECT @Arr24Time = dbo.GetHotelSetupKeyValue('Arr24HoursStarts', -1)
SET @Arr24Time = ISNULL(@Arr24Time, '07:00')

SELECT CAST(0 AS Int) AS [Index], CAST(0 AS Bit) AS DoNotShow, ConferenceRooms.Number, ConferenceRooms.ConferenceRoomNumber, ConferenceRooms.TableDate, 
  ConferenceRooms.ConferenceRoomTypeNumber, ConferenceRoomTypes.Type, ISNULL(CONFERENCEROOMS.ChairNumber, 0) AS ChairNumber, ConferenceRooms.Name AS TableName, 
  ConferenceRooms.HotelNumber, ConferenceRooms.Color, CUSTOMER.HotelName, ConferenceRooms.Pax AS ConferenceRoomPAX
INTO #ConferenceRooms
FROM ConferenceRooms (NOLOCK)
INNER JOIN CONFERENCEROOMTYPES (NOLOCK) ON ConferenceRoomTypes.ConferenceRoomTypeNumber = ConferenceRooms.ConferenceRoomTypeNumber
OUTER APPLY (
  SELECT
    LastName AS HotelName
  FROM CUSTOMER WITH (NOLOCK)
  WHERE CUSTOMER.HotelNumber = CONFERENCEROOMS.HotelNumber
) AS Customer
WHERE (1=1) 
  AND (ISNULL(ConferenceRooms.IsTable, 0) = 1)
  AND ConferenceRooms.TableDate = @StartDate
  AND ConferenceRooms.ConferenceRoomTypeNumber IN (SELECT Number FROM iter_intlist_to_table(@TypeNumbers))
  AND (ConferenceRooms.HotelNumber IN (SELECT Number FROM iter_intlist_to_table(@HotelNumberCSV)))
ORDER BY dbo.ConferenceRooms.Istable,dbo.ConferenceRooms.HotelNumber,dbo.ConferenceRooms.Number

SELECT 
  ReservationConferenceLine.ReservationConfLineNumber, 
  ReservationConferenceLine.Plenum,
  ReservationConferenceLine.ReferenceNumber, 
  ReservationConferenceLine.GuestNo, 
  ISNULL(ReservationConferenceLine.TableStatus, 9) AS TableStatus, 
  ReservationConferenceLine.TableBooking, 
  ReservationConferenceLine.ArrivalDate, 
  ReservationConferenceLine.ArrivalTime, 
  ReservationConferenceLine.DepartureDate, 
  ReservationConferenceLine.DepartureTime, 
  ReservationConferenceLine.Adults, 
  ReservationConferenceLine.Children1, 
  ReservationConferenceLine.Children2,
  ReservationConferenceLine.ProductNumber, 
  ReservationConferenceLine.PackageNumber,
  CASE WHEN ReservationConferenceLine.Smoker = 0 THEN
	'No'
  ELSE
	''
  END AS Smoker,  
  --ReservationConferenceLine.Smoker, 
  ReservationConferenceLine.ConferenceRoomNumber AS ConfRoomID,
  ConferenceRoomsXtab.CollectsConferenceRoomNumber,

  Reservations.Status,
  GuestTypes.Text AS GuestType,
  CAST(0 AS money) AS Balance,

  CASE WHEN (DATALENGTH(ReservationAddress.FIRSTNAME) > 0) THEN
    ReservationAddress.LastName + ', ' + ReservationAddress.FirstName
  ELSE
    ReservationAddress.LastName
  END AS Name,

  ReservationAddress.Countrycode,
  ReservationAddress.Phone,
  ReservationAddress.Cell,

  (SELECT TOP 1 
     Segments.Text 
   FROM Segments (NOLOCK)
   WHERE Segments.Number = Reservations.MarketSegment 
  ) AS Segment,

  (SELECT TOP 1 
     PurposeCodes.Text 
   FROM PurposeCodes (NOLOCK)
   WHERE PurposeCodes.PurposeCodeNumber = Reservations.PURPOSECODE 
  ) AS PurposeCode,

  (SELECT TOP 1 
     PointOfSales.Text 
   FROM PointOfSales (NOLOCK)
   WHERE PointOfSales.Number = Reservations.POINTOFSALE 
  ) AS PointOfSale,

  (SELECT TOP 1
     Rooms.Number
   FROM ReservationRoomLineXtab WITH (NOLOCK)
   INNER JOIN RESERVATIONROOMLINE (NOLOCK) ON RESERVATIONROOMLINE.LineNumber = ReservationRoomLineXtab.LINENUMBER
   INNER JOIN Rooms (NOLOCK) ON Rooms.RoomNumber = ReservationRoomLine.RoomNumber
   WHERE ReservationRoomLineXtab.ReferenceNumber = ReservationConferenceLine.ReferenceNumber AND ReservationRoomLineXtab.GuestNumber = ReservationConferenceLine.GuestNo
  ) AS RoomNumber,

  (SELECT TOP 1
     Rooms.Name
   FROM ReservationRoomLineXtab WITH (NOLOCK)
   INNER JOIN RESERVATIONROOMLINE (NOLOCK) ON RESERVATIONROOMLINE.LineNumber = ReservationRoomLineXtab.LINENUMBER
   INNER JOIN Rooms (NOLOCK) ON Rooms.RoomNumber = ReservationRoomLine.RoomNumber
   WHERE ReservationRoomLineXtab.ReferenceNumber = ReservationConferenceLine.ReferenceNumber AND ReservationRoomLineXtab.GuestNumber = ReservationConferenceLine.GuestNo
  ) AS RoomName,

  (SELECT TOP 1 resProd.ProductName
   FROM Reservations resRoom WITH (NOLOCK)
   INNER JOIN ReservationRoomLineXTab rrlx WITH (NOLOCK) ON rrlx.ReferenceNumber = resRoom.ReferenceNumber 
     AND rrlx.GuestNumber = resRoom.GuestNumber
     AND rrlx.ReferenceNumber = RESERVATIONCONFERENCELINE.ReferenceNumber
     --AND rrlx.GuestNumber = ISNULL(RESERVATIONCONFERENCELINE.GuestNo, 0)
   INNER JOIN ReservationRoomLine rrl WITH (NOLOCK) ON rrl.LineNumber = rrlx.LineNumber
   INNER JOIN Products resProd WITH (NOLOCK) ON resProd.ProductNumber = CASE WHEN ISNULL(RESERVATIONCONFERENCELINE.GuestNo, 0) = 0 THEN rrl.ProductNumber ELSE resRoom.ProductNumber END
   ORDER BY rrl.ScreenLine ASC
   ) AS RoomProduct,
 
  CASE WHEN ReservationConferenceLine.TableStatus = 5 THEN
    1
  ELSE
    0
  END AS IsOpenTable,
  
  (SELECT TOP 1
	KS.Printed
  FROM dbo.FOLIOS F  WITH (NOLOCK)
  INNER JOIN dbo.FolioRestResTableChairsXTab FRTCX  WITH (NOLOCK) ON F.FolioNumber = FRTCX.FolioNumber
  INNER JOIN dbo.KitchenStatement KS  WITH (NOLOCK) ON F.FolioNumber = KS.FolioNumber
  LEFT JOIN dbo.KitchenNote KN  WITH (NOLOCK) ON KS.KitchenNoteNumber = KN.KitchenNoteNumber
  RIGHT JOIN dbo.ConferenceRooms H  WITH (NOLOCK) ON FRTCX.TableNumber = H.ConferenceRoomNumber AND FRTCX.ChairNumber = H.ChairNumber AND (F.OpenFolio = 1)
  WHERE F.FolioNumber = Folios.FolioNumber and H.ConferenceRoomNumber = #ConferenceRooms.ConferenceRoomNumber AND H.ChairNumber = #ConferenceRooms.ChairNumber
  ORDER BY KS.Printed DESC, KS.KitchenStatementNumber DESC, H.ConferenceRoomNumber, H.ChairNumber
  ) AS LastKitchenTime,

(SELECT TOP 1
  KS.KitchenStatementNumber
FROM dbo.FOLIOS F  WITH (NOLOCK)
INNER JOIN dbo.FolioRestResTableChairsXTab FRTCX  WITH (NOLOCK) ON F.FolioNumber = FRTCX.FolioNumber
INNER JOIN dbo.KitchenStatement KS  WITH (NOLOCK) ON F.FolioNumber = KS.FolioNumber
LEFT JOIN dbo.KitchenNote KN  WITH (NOLOCK) ON KS.KitchenNoteNumber = KN.KitchenNoteNumber
RIGHT JOIN dbo.ConferenceRooms H  WITH (NOLOCK) ON FRTCX.TableNumber = H.ConferenceRoomNumber AND FRTCX.ChairNumber = H.ChairNumber AND (F.OpenFolio = 1)
WHERE F.FolioNumber = Folios.FolioNumber and H.ConferenceRoomNumber = #ConferenceRooms.ConferenceRoomNumber AND H.ChairNumber = #ConferenceRooms.ChairNumber
ORDER BY KS.Printed DESC, KS.KitchenStatementNumber DESC, H.ConferenceRoomNumber, H.ChairNumber
) AS LastKitchenStatement,

(SELECT TOP 1
  KN.Init
FROM dbo.FOLIOS F  WITH (NOLOCK)
INNER JOIN dbo.FolioRestResTableChairsXTab FRTCX  WITH (NOLOCK) ON F.FolioNumber = FRTCX.FolioNumber
INNER JOIN dbo.KitchenStatement KS  WITH (NOLOCK) ON F.FolioNumber = KS.FolioNumber
LEFT JOIN dbo.KitchenNote KN  WITH (NOLOCK) ON KS.KitchenNoteNumber = KN.KitchenNoteNumber
RIGHT JOIN dbo.ConferenceRooms H  WITH (NOLOCK) ON FRTCX.TableNumber = H.ConferenceRoomNumber AND FRTCX.ChairNumber = H.ChairNumber AND (F.OpenFolio = 1)
WHERE F.FolioNumber = Folios.FolioNumber and H.ConferenceRoomNumber = #ConferenceRooms.ConferenceRoomNumber AND H.ChairNumber = #ConferenceRooms.ChairNumber
ORDER BY KS.Printed DESC, KS.KitchenStatementNumber DESC, H.ConferenceRoomNumber, H.ChairNumber
) AS LastKitchenNote,


ISNULL(Folios.FolioNumber, 0) AS FolioNumber,

(
  SELECT TOP 1 Seating.SeatingNumber
  FROM Seating WITH (NOLOCK)
  WHERE CONVERT(CHAR(5), SeatingStartTime, 108) <= ReservationConferenceLine.ArrivalTime
    AND CONVERT(CHAR(5), SeatingEndTime, 108) >= ReservationConferenceLine.ArrivalTime
  ORDER BY SeatingStartTime
) AS SeatingNumber,

(
  SELECT TOP 1 Seating.SeatingName
  FROM Seating WITH (NOLOCK)
  WHERE CONVERT(CHAR(5), SeatingStartTime, 108) <= ReservationConferenceLine.ArrivalTime
    AND CONVERT(CHAR(5), SeatingEndTime, 108) >= ReservationConferenceLine.ArrivalTime
  ORDER BY SeatingStartTime
) AS SeatingName

INTO #ReservationConferenceLine
FROM ReservationConferenceLine (NOLOCK)
INNER JOIN #ConferenceRooms (NOLOCK) ON #ConferenceRooms.ConferenceRoomNumber = RESERVATIONCONFERENCELINE.ConferenceRoomNumber AND #ConferenceRooms.ConferenceRoomTypeNumber = ReservationConferenceLine.BookedConferenceRoomTypeNumber AND
  RESERVATIONCONFERENCELINE.BookedConferenceRoomTypeNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@TypeNumbers))
LEFT JOIN ConferenceRoomsXtab (NOLOCK) ON ConferenceRoomsXtab.ConferenceRoomNumber = #ConferenceRooms.ConferenceRoomNumber -- AND ConferenceRoomsXtab.ConferenceRoomNumber <> ConferenceRoomsXtab.CollectsConferenceRoomNumber

CROSS APPLY (
  SELECT 
    Reservations.ReferenceNumber,
	Reservations.GuestNumber,
    Reservations.Status,
	Reservations.GuestType,
	Reservations.MarketSegment,
	Reservations.PointOfSale,
	Reservations.PurposeCode
  FROM RESERVATIONS (NOLOCK) 
  WHERE Reservations.ReferenceNumber = ReservationConferenceLine.ReferenceNumber AND RESERVATIONS.GuestNumber = RESERVATIONCONFERENCELINE.GuestNo
) AS Reservations

OUTER APPLY (
  SELECT 
    ReservationAddress.FirstName,
    ReservationAddress.Lastname,
    ReservationAddress.Countrycode,
    ReservationAddress.Phone,
    ReservationAddress.Cell
  FROM ReservationAddress (NOLOCK)
  INNER JOIN ReservationAddressXtab (NOLOCK) ON ReservationAddressXtab.AddressNumber = ReservationAddress.AddressNumber AND ReservationAddressXtab.ReferenceNumber = Reservations.ReferenceNumber AND ReservationAddressXtab.GuestNumber = Reservations.GuestNumber
    AND ReservationAddressXtab.AddressTypeNumber = 1 --Guest
) AS ReservationAddress

OUTER APPLY (
  SELECT Text
  FROM GuestTypes (NOLOCK)
  WHERE GuestTypes.TypeNumber = Reservations.GuestType
) GuestTypes

OUTER APPLY (
  SELECT Folios.FolioNumber
  FROM Folios (NOLOCK)
  INNER JOIN FolioRestResTableChairsXTab (NOLOCK) ON FolioRestResTableChairsXTab.FolioNumber = Folios.FolioNumber 
    AND FolioRestResTableChairsXTab.RestaurantReservationNumber = ReservationConferenceLine.ReservationConfLineNumber
) AS Folios

WHERE (1=1)
  AND ISNULL(RESERVATIONCONFERENCELINE.LinkLineNumber, 0) = 0
  AND ReservationConferenceLine.TableBooking = 1
  AND ReservationConferenceline.TableStatus IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@StatusCSV))
  AND (ReservationConferenceline.ArrivalDate <= @Tabledate AND ReservationConferenceline.ArrivalTime >= @Arr24Time OR ReservationConferenceline.ArrivalDate >= @Tabledate AND ReservationConferenceline.ArrivalTime < @Arr24Time)
  AND (ReservationConferenceline.DepartureDate >= @Tabledate AND ReservationConferenceline.DepartureTime >= @Arr24Time OR ReservationConferenceline.DepartureDate = @EndDate1 AND ReservationConferenceline.DepartureTime < @Arr24Time)

/*
  AND ReservationConferenceLine.ArrivalDate <= #ConferenceRooms.TableDate AND RESERVATIONCONFERENCELINE.ArrivalTime >= @Arr24Time OR ReservationConferenceLine.ArrivalDate = #ConferenceRooms.TableDate AND RESERVATIONCONFERENCELINE.ArrivalTime < @Arr24Time
  AND ReservationConferenceLine.DepartureDate >= #ConferenceRooms.TableDate AND ReservationConferenceLine.DepartureTime >= @Arr24Time
   OR ReservationConferenceLine.DepartureDate = #ConferenceRooms.TableDate AND ReservationConferenceLine.DepartureTime < @Arr24Time
*/
SELECT *
INTO #TempTablebookings1
FROM #ConferenceRooms
OUTER APPLY (
  SELECT *
  FROM #ReservationConferenceLine
  WHERE #ConferenceRooms.ConferenceRoomNumber = #ReservationConferenceLine.CollectsConferenceRoomNumber
) AS HL

UPDATE #TempTablebookings1
SET
  TableStatus = 12, -- Show OutOfOrder colour as status for linked tables (nbr 12 in DMStyleRepository.imgDots image list in Delphi)
  Adults = 0,
  Children1 = 0,
  Children2 = 0
WHERE (1=1)
  AND ConfRoomID <> CollectsConferenceRoomNumber

/*
SELECT *
INTO #TempTablebookings2
FROM #ConferenceRooms
CROSS APPLY (
  SELECT *
  FROM #ReservationConferenceLine 
  WHERE #ConferenceRooms.ConferenceRoomNumber = #ReservationConferenceLine.CollectsConferenceRoomNumber
) AS HL2
*/

SELECT *
INTO #Tablebookings
FROM #TempTablebookings1
/*
UNION
SELECT *
FROM #TempTablebookings2
*/
SELECT 
  [Index],
  DoNotShow,
  LastKitchenNote,
  LastKitchenTime,
  LastKitchenStatement,
  Number,
  ConferenceRoomNumber,
  ChairNumber,
  TableName,
  Type,
  TableStatus,
  Name,
  Smoker,
  Balance,
  ReferenceNumber,
  GuestNo,
  Adults,
  Children1,
  Children2,
  ArrivalTime,
  DepartureTime,
  ArrivalDate,
  DepartureDate,
  RoomNumber,
  RoomName,
  GuestType,
  COUNTRYCODE,
  HotelName,
  HotelNumber,
  FolioNumber,
  ReservationConfLineNumber,
  Color,
  ResNote1.Text AS Note1,
  ResNote2.Text AS Note2,
  ResNote3.Text AS Note3,
  CASE WHEN (DataLength(ResNote1.Text) > 0) OR (DataLength(ResNote2.Text) > 0) OR (DataLength(ResNote3.Text) > 0)  THEN
    'N'
  ELSE
    ''
  END AS NoteExists,
  Plenum,
  IsOpenTable,
  Segment,
  PointOfSale,
  PurposeCode,
  RoomProduct,
  PHONE,
  Cell,
 
  CASE WHEN (DATALENGTH(LastKitchenTime) > 0) OR (DATALENGTH(LastKitchenStatement) > 0) OR (DATALENGTH(LastKitchenNote) > 0) THEN
    '1'
  ELSE
    '0'
  END AS ActiveKitchenNote,

  SeatingNumber,
  SeatingName,

  ConferenceRoomPAX,

  CAST('' AS varchar(max)) AS PreviewFieldName,

  dbo.GetArrangementLineText(@Tabledate, ReferenceNumber, GuestNo, 0) AS ArrangementLineText

FROM #Tablebookings
OUTER APPLY (
  SELECT ResNote1.Text
  FROM ReservationNotes ResNote1 WITH (NOLOCK)
  WHERE ResNote1.ReferenceNumber = #Tablebookings.ReferenceNumber AND ResNote1.GuestNumber = #Tablebookings.GuestNo AND ResNote1.NotePage = 1
) AS ResNote1

OUTER APPLY (
  SELECT ResNote2.Text
  FROM ReservationNotes ResNote2 WITH (NOLOCK)
  WHERE ResNote2.ReferenceNumber = #Tablebookings.ReferenceNumber AND ResNote2.GuestNumber = #Tablebookings.GuestNo AND ResNote2.NotePage = 2
) AS ResNote2

OUTER APPLY (
  SELECT ResNote3.Text
  FROM ReservationNotes ResNote3 WITH (NOLOCK)
  WHERE ResNote3.ReferenceNumber = #Tablebookings.ReferenceNumber AND ResNote3.GuestNumber = #Tablebookings.GuestNo AND ResNote3.NotePage = 3
) AS ResNote3

ORDER BY HotelNumber, Number

END
GO

