CREATE PROCEDURE [dbo].[MCC_GetRoomDetails] @ARoomNumber VARCHAR(8000) , @iNoteIdx integer AS
SET NOCOUNT ON

CREATE TABLE #NextStay
(
  Stay VARCHAR(20) COLLATE database_default NOT NULL,
  RoomID INT NOT NULL,
  --RoomNote VARCHAR(8000) COLLATE database_default NULL, 
RoomNote nVARCHAR(max) COLLATE database_default NULL, 
--  ResNote VARCHAR(8000) COLLATE database_default NULL, /* CC 20140613 Add reservation notes */  
  ResNote nvarchar(max) COLLATE database_default NULL, /* CC 20140613 Add reservation notes */  

  FirstName VARCHAR(50) COLLATE database_default NULL,
  LastName VARCHAR(50) COLLATE database_default NULL,

  CustomerTypeNumber INT NULL,
  Pax INT NULL,
  ArrivalDateTime VARCHAR(20) COLLATE database_default NULL,
  DepartureDateTime VARCHAR(20) COLLATE database_default NULL,
  LineNumber INT NULL,
  RoomDoorNumber INT NULL,
  Linen2BChanged INT NULL /* CC 20140416 */
)

DECLARE @Number VARCHAR(10)

DECLARE @RoomID INT
--DECLARE @RoomNote VARCHAR(8000)
DECLARE @RoomNote nVARCHAR(max)
--DECLARE @ResNote VARCHAR (8000) /* CC 20140613 Add reservation notes */ 
DECLARE @ResNote nvarchar(max) /* CC 20140613 Add reservation notes */ 


DECLARE @FirstName VARCHAR(50)
DECLARE @LastName VARCHAR(50)
DECLARE @CustomerTypeNumber INT
DECLARE @Pax INT
DECLARE @ArrivalDateTime VARCHAR(20)
DECLARE @DepartureDateTime VARCHAR(20)
DECLARE @LineNumber INT
DECLARE @RoomDoorNumber INT
DECLARE @LinenProductNumber INT /* CC 20140416 */
DECLARE @Linen2BChanged INT /* CC 20140416 */
DECLARE @NoteIndex INT 

-- CC 201804-05 START
-- CC 20180518 cARDnOiNnOTES IF ( @iNoteIdx < 1 )  
-- CC 20180518 cARDnOiNnOTES   SET @NoteIndex = 2
-- CC 20180518 cARDnOiNnOTES  ELSE
  SET @NoteIndex = @iNoteIdx
-- CC 201804-05 END

DECLARE db_cur CURSOR FOR
  SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@ARoomNumber)
OPEN db_cur

FETCH NEXT FROM db_cur INTO @Number

WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT '@Number' -- CC 20180502 BuggFix
--PRINT @Number   -- CC 20180502 BuggFix
  SELECT TOP 1 
  @RoomID = RM.RoomNumber,

  --@RoomNote = CONVERT(VARCHAR(8000),RM.Notes),	-- CC 20180529 Optimization
  @RoomNote = RM.Notes,								-- CC 20180529 Optimization
  @ResNote = CONVERT(nVARCHAR(max),RN.Text), /* CC 20140613 Add reservation notes */ 

  @FirstName = RA.FirstName,
  @LastName = RA.LastName,

  @CustomerTypeNumber = CUSTOMER.CustomerTypeNumber,
  @Pax = RL1.Adults + RL1.Children1 + RL1.Children2,
  @ArrivalDateTime = RL1.ArrivalDate + ' ' + RL1.ArrivalTime,
  @DepartureDateTime = RL1.DepartureDate + ' ' + RL1.DepartureTime,
  @LineNumber = RL1.LineNumber,
  @RoomDoorNumber = RM.Number,  
  @Linen2BChanged =0 -- Always zero for future guests. dbo.MCC_Linen2BChanged( RL1.ProductNumber, CONVERT(Date, RL1.ArrivalDate, 112 ) , CONVERT(Date, RL1.DepartureDate, 112 ) ) /* CC 20140416 */    
  

  FROM Rooms RM WITH (NOLOCK) 
  LEFT JOIN ReservationRoomLine RL1 WITH (NOLOCK) ON RL1.RoomNumber = RM.RoomNumber 
  INNER JOIN ReservationRoomLineXTAB RLX1 WITH (NOLOCK) ON RL1.LineNumber = RLX1.LineNumber 
  INNER JOIN Reservations R WITH (NOLOCK) ON R.ReferenceNumber = RLX1.ReferenceNumber
    AND R.GuestNumber = RLX1.GuestNumber
  LEFT OUTER JOIN CUSTOMER WITH (NOLOCK) ON RM.CustomerNumber = CUSTOMER.CustomerNumber /*Owner of the Room*/
  INNER JOIN ReservationAddressXTab RAX WITH (NOLOCK) ON RAX.ReferenceNumber = RLX1.ReferenceNumber     
    AND RAX.GuestNumber = RLX1.GuestNumber   
  INNER JOIN ReservationAddress RA WITH (NOLOCK) ON RA.AddressNumber = RAX.addressnumber AND RAX.AddressTypeNumber = 1 /*Guest*/ 
  --LEFT OUTER JOIN RESERVATIONNOTES RN WITH (NOLOCK) ON RN.ReferenceNumber = RLX1.ReferenceNumber AND RN.GuestNumber = RLX1.GuestNumber AND RN.NotePage = 2 /* CC 20140613 Add reservation notes */ -- CC 201804-05
  LEFT OUTER JOIN RESERVATIONNOTES RN WITH (NOLOCK) ON RN.ReferenceNumber = RLX1.ReferenceNumber AND RN.GuestNumber = RLX1.GuestNumber AND RN.NotePage = @NoteIndex /* CC 20140613 Add reservation notes */ -- CC 201804-05

  WHERE RM.RoomNumber = @Number -- CC 20180502 BuggFix  IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@Number))
  AND RLX1.ReferenceNumber > 0
  AND RL1.ArrivalDate >= (SELECT TOP 1 SystemDate FROM System WITH (NOLOCK))
  AND R.Status IN (0, 1, 2, 3, 4)-- 0=confirmed, 1=provisional, 2=guaranteed, 3=waitinglist, 4=tentative
  ORDER BY RL1.ArrivalDate + ' ' + RL1.ArrivalTime                    
-- CC OLD SQL SERVER FAILS  OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'))  -- CC 20180529 Optimization
   
   IF @@ROWCOUNT > 0 
   -- INSERT #NextStay VALUES ('NextStay', @RoomID, @RoomNote,         @FirstName, @LastName, @CustomerTypeNumber, @Pax, @ArrivalDateTime, @DepartureDateTime, @LineNumber, @RoomDoorNumber )  /* CC 20140416 */
   -- INSERT #NextStay VALUES ('NextStay', @RoomID, @RoomNote,         @FirstName, @LastName, @CustomerTypeNumber, @Pax, @ArrivalDateTime, @DepartureDateTime, @LineNumber, @RoomDoorNumber, @Linen2BChanged )  /* CC 20140416 */ /* CC 20140613 Add reservation notes */
    INSERT #NextStay VALUES ('NextStay', @RoomID, @RoomNote, @ResNote, @FirstName, @LastName, @CustomerTypeNumber, @Pax, @ArrivalDateTime, @DepartureDateTime, @LineNumber, @RoomDoorNumber, @Linen2BChanged )  /* CC 20140416 */ /* CC 20140613 Add reservation notes */ 
   ELSE																												  /* CC 20140814  Add empty lines for filtering reservation notes in the app */ 
    INSERT #NextStay VALUES ('NextStay', @Number, '', '', '', '', '-1', '0', '22140101', '22140101', '0', '0', '0' )  /* CC 20140814  Add empty lines for filtering reservation notes in the app */ 
   
  FETCH NEXT FROM db_cur INTO @Number
END  

CLOSE db_cur
DEALLOCATE db_cur

SELECT * FROM #NextStay

UNION
                         
SELECT 'CurrentStay' AS Stay  /* PDA_GetRoomDetails */
, RM.RoomNumber AS RoomID
--, CONVERT(VARCHAR(8000),RM.Notes) AS RoomNote		-- CC 20180529 Optimization
, RM.Notes AS RoomNote								-- CC 20180529 Optimization
, CONVERT(nVARCHAR(max),RN.Text) AS ResNote /* CC 20140613 Add reservation notes */ 

, RA.FirstName
, RA.LastName

, CUSTOMER.CustomerTypeNumber
, RL1.Adults + RL1.Children1 + RL1.Children2 AS Pax
, RL1.ArrivalDate + ' ' + RL1.ArrivalTime AS [ArrivalDateTime]
, RL1.DepartureDate + ' ' + RL1.DepartureTime AS [DepartureDateTime]
, RL1.LineNumber
, RM.Number AS RoomDoorNumber
-- , dbo.Get_MCC_ProductLinenChangeInterval( RL1.ProductNumber ) /* CC 20140416 */  
  --,dbo.Get_MCC_ProductLinenChangeInterval( RL1.ProductNumber ) /* CC 20140416 */  
--,dbo.MCC_Linen2BChanged( RL1.ProductNumber, CONVERT(Date, RL1.ArrivalDate, 112 ) , CONVERT(Date, RL1.DepartureDate, 112 ) ) /* CC 20140416 */  /* CC 20140430 SQL 2005 backwards compability */
,dbo.MCC_Linen2BChanged( RL1.ProductNumber, CONVERT(DateTime, RL1.ArrivalDate, 112 ) , CONVERT(DateTime, RL1.DepartureDate, 112 ) ) /* CC 20140416 */  /* CC 20140430 SQL 2005 backwards compability */

--, (SELECT dbo.Get_MCC_ProductLinenChangeInterval( RL1.ProductNumber )) AS LinenChangeDays
FROM Rooms RM WITH (NOLOCK) 
LEFT JOIN ReservationRoomLine RL1 WITH (NOLOCK) ON RL1.RoomNumber = RM.RoomNumber 
INNER JOIN ReservationRoomLineXTAB RLX1 WITH (NOLOCK) ON RL1.LineNumber = RLX1.LineNumber 
INNER JOIN Reservations R WITH (NOLOCK) ON R.ReferenceNumber = RLX1.ReferenceNumber
  AND R.GuestNumber = RLX1.GuestNumber
LEFT OUTER JOIN CUSTOMER WITH (NOLOCK) ON RM.CustomerNumber = CUSTOMER.CustomerNumber /*Owner of the Room*/
INNER JOIN ReservationAddressXTab RAX WITH (NOLOCK) ON RAX.ReferenceNumber = RLX1.ReferenceNumber 
  AND RAX.GuestNumber = RLX1.GuestNumber 
INNER JOIN ReservationAddress RA WITH (NOLOCK) ON RA.AddressNumber = RAX.addressnumber AND RAX.AddressTypeNumber = 1 /*Guest*/ 
--LEFT OUTER JOIN RESERVATIONNOTES RN WITH (NOLOCK) ON RN.ReferenceNumber = RLX1.ReferenceNumber AND RN.GuestNumber = RLX1.GuestNumber AND RN.NotePage = 2 /* CC 20140613 Add reservation notes */ -- CC 201804-05
LEFT OUTER JOIN RESERVATIONNOTES RN WITH (NOLOCK) ON RN.ReferenceNumber = RLX1.ReferenceNumber AND RN.GuestNumber = RLX1.GuestNumber AND RN.NotePage = @NoteIndex /* CC 20140613 Add reservation notes */ -- CC 201804-05

WHERE RM.RoomNumber IN  (SELECT DISTINCT Number FROM iter_intlist_to_table(@ARoomNumber))
AND RLX1.ReferenceNumber > 0
AND R.Status IN (5) -- 5=inhouse

order by RoomID,ArrivalDateTime  -- RoomID is new name for: "ROOMS.RoomNumber".
-- CC OLD SQL SERVER FAILS OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'))  -- CC 20180529 Optimization
                         
DROP TABLE #NextStay
