CREATE PROCEDURE [dbo].[GetCashierTablesDatasetUnAllocated] @StartDate datetime, @CheckHalls bit = 0, @StatusCSV varchar(20), @TypeNumbers varchar(255), @HotelNumberCSV varchar(255), @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(CHAR(8), @StartDate, 112)

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

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

SELECT 
  CAST(0 AS Int) AS [Index],
  CAST(0 AS Bit) AS DoNotShow,
  ReservationConferenceLine.ReservationConfLineNumber, 
  ReservationConferenceLine.BookedConferenceRoomTypeNumber,
  ConferenceRoomTypes.Type,
  ReservationConferenceLine.Plenum,
  ReservationConferenceLine.ReferenceNumber, 
  ReservationConferenceLine.GuestNo, 
  ReservationConferenceLine.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.ConferenceRoomNumber AS ConfRoomID,

  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 = ISNULL(ReservationConferenceLine.GuestNo, 0)
  ) 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 = ISNULL(ReservationConferenceLine.GuestNo, 0)
  ) 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,


  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,
  
  CASE WHEN ReservationConferenceLine.TableStatus = 5 THEN
    1
  ELSE
    0
  END AS IsOpenTable,

  Folios.FolioNumber,
  Customer.LastName AS HotelName,

  (
  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


  /*
  ,(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,

  CASE WHEN (DATALENGTH(LastKitchenTime) > 0) OR (DATALENGTH(LastKitchenStatement) > 0) OR (DATALENGTH(LastKitchenNote) > 0) THEN
    '1'
  ELSE
    '0'
  END AS ActiveKitchenNote
  */

INTO #ReservationConferenceLineUnAlloc
FROM ReservationConferenceLine (NOLOCK)

CROSS APPLY (
  SELECT
    FkCustomerHotel
  FROM ReservationMain (NOLOCK)
  WHERE ReservationMain.REFERENCENUMBER = RESERVATIONCONFERENCELINE.ReferenceNumber
    AND ReservationMain.HotelNumber IN (SELECT Number FROM iter_intlist_to_table(@HotelNumberCSV))
) AS ReservationMain

CROSS APPLY (
  SELECT
    LastName
  FROM Customer (NOLOCK) 
  WHERE Customer.CustomerNumber = ReservationMain.FkCustomerHotel AND Customer.CustomerTypeNumber = 7
) AS Customer
--INNER JOIN ReservationMain (NOLOCK) ON ReservationMain.ReferenceNumber = ReservationConferenceLine.ReferenceNumber
--INNER JOIN Customer (NOLOCK) ON Customer.CustomerNumber = ReservationMain.FkCustomerHotel  AND Customer.CustomerTypeNumber = 7
--INNER JOIN ReservationAddressXtab (NOLOCK) ON ReservationAddressXtab.REFERENCENUMBER = ReservationConferenceLine.ReferenceNumber AND ReservationAddressXtab.GUESTNUMBER = ReservationConferenceLine.GuestNo 
--INNER JOIN ReservationAddress (NOLOCK) ON ReservationAddress.AddressNumber = ReservationAddressXtab.AddressNumber AND ReservationAddress.ADDRESSTYPE = 1 -- atGuest

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 = ISNULL(ReservationConferenceLine.GuestNo, 0)
) 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 = ReservationConferenceLine.ReferenceNumber AND ReservationAddressXtab.GuestNumber = ISNULL(ReservationConferenceLine.GuestNo, 0)
    AND ReservationAddressXtab.AddressTypeNumber = 1 --Guest
) AS ReservationAddress

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

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

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

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

OUTER APPLY (
  SELECT ConferenceRoomTypes.Type
  FROM ConferenceRoomTypes (NOLOCK)
  WHERE ConferenceRoomTypes.ConferenceRoomTypeNumber = ReservationConferenceLine.BookedConferenceRoomTypeNumber
) AS ConferenceRoomTypes

WHERE (1=1)
  AND ISNULL(ReservationConferenceLine.TableBooking, 0) = 1
  AND (ISNULL(RESERVATIONCONFERENCELINE.ConferenceRoomNumber, 0) = 0 OR (ISNULL(ReservationConferenceLine.ConferenceRoomNumber, 0) NOT IN  (SELECT ConferenceRoomNumber FROM CONFERENCEROOMS (NOLOCK) WHERE Istable = 1 AND TableDate = @Tabledate)))
  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 <= @Tabledate and ReservationConferenceLine.DepartureDate >= @Tabledate and ReservationConferenceLine.ArrivalTime >= @Arr24Time) or (ReservationConferenceLine.ArrivalDate = @Tabledate and ReservationConferenceLine.ArrivalTime < @Arr24Time)) 

  AND ReservationConferenceLine.BookedConferenceRoomTypeNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@TypeNumbers))
  AND ISNULL(RESERVATIONCONFERENCELINE.LinkLineNumber, 0) = 0

SELECT
  [Index],
  DoNotShow,
  --LastKitchenNote,
  --LastKitchenTime,
  --LastKitchenStatement,
  --Number,
  --ConferenceRoomNumber,
  --ChairNumber,
  --TableName,
  Type,
  TableStatus,
  CASE WHEN (DATALENGTH(ReservationAddress.FIRSTNAME) > 0) THEN
    ReservationAddress.LastName + ', ' + ReservationAddress.FirstName
  ELSE
    ReservationAddress.LastName
  END AS Name,
  Smoker,
  --Balance,
  #ReservationConferenceLineUnAlloc.ReferenceNumber,
  GuestNo,
  Adults,
  Children1,
  Children2,
  ArrivalTime,
  DepartureTime,
  ArrivalDate,
  DepartureDate,
  RoomNumber,
  RoomName,
  GuestTypes.TEXT AS GuestType,
--  COUNTRYCODE,
  (SELECT TOP 1
     LastName
   FROM Customer (NOLOCK)
   WHERE CustomerNumber = ReservationMain.FkCustomerHotel
  ) AS HotelName,
--  HotelNumber,
  ISNULL(FolioNumber, 0) AS 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,

  (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 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 = #ReservationConferenceLineUnAlloc.ReferenceNumber
     AND rrlx.GuestNumber = ISNULL(#ReservationConferenceLineUnAlloc.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(#ReservationConferenceLineUnAlloc.GuestNo, 0) = 0 THEN rrl.ProductNumber ELSE resRoom.ProductNumber END
   ORDER BY rrl.ScreenLine ASC
   ) AS RoomProduct,
  ReservationAddress.PHONE,
  ReservationAddress.Cell,
  SeatingNumber,
  SeatingName,

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

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

FROM #ReservationConferenceLineUnAlloc
CROSS APPLY (
  SELECT 
    Reservations.ReferenceNumber,
	Reservations.GuestNumber,
    Reservations.Status,
	Reservations.GuestType,
	Reservations.MarketSegment,
	Reservations.PointOfSale,
	Reservations.PurposeCode
  FROM RESERVATIONS (NOLOCK) 
  WHERE Reservations.ReferenceNumber = #ReservationConferenceLineUnAlloc.ReferenceNumber AND RESERVATIONS.GuestNumber = ISNULL(#ReservationConferenceLineUnAlloc.GuestNo,0)
) AS Reservations

CROSS APPLY (
  SELECT 
    FkCustomerHotel,
	HOTELNUMBER
  FROM RESERVATIONMAIN (NOLOCK)
  WHERE RESERVATIONMAIN.REFERENCENUMBER = #ReservationConferenceLineUnAlloc.ReferenceNumber
) AS ReservationMain

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 = #ReservationConferenceLineUnAlloc.ReferenceNumber AND ReservationAddressXtab.GuestNumber = ISNULL(#ReservationConferenceLineUnAlloc.GuestNo, 0)
    AND ReservationAddressXtab.AddressTypeNumber = 1 --Guest
) AS ReservationAddress

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

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

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

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

ORDER BY #ReservationConferenceLineUnAlloc.BookedConferenceRoomTypeNumber ASC, #ReservationConferenceLineUnAlloc.ArrivalTime ASC, #ReservationConferenceLineUnAlloc.DepartureTime ASC

END
GO
