CREATE PROCEDURE [dbo].[PDA_GetRoomDetails] @ARoomNumber VARCHAR(8000) AS
SET NOCOUNT ON

-- =============================================
-- Author:		Lars Jensen/LJE
-- Create date: 2011.01.25
-- Description:	Extract details for current stay
--                  and next stay (pax, dates, ...)
-- =============================================

/*
 Change log:
   KJ 20161108: Commit of changes received from CC by mail
   KJ: 20110317: Bug: Fix for "Can not insert the value null into RoomID"
   KJ: 20110315: Bug: Fix for "Wrong result when more than one room in input". Case# CR021705
   LJE, 2011.02.23, Semicolon removed from end of sp (in ORDER statement).
   LJE, 2011.02.23, Due to use of SQL 2000 for a few hotels - sql is rearranged (prev. ORDER BY clauses did not work).
   LJE, 2011.02.23, SET NOCOUNT added.
   CC , 2014.04.16, Linen2BChanged added. needs the function MCC_Linen2BChanged
   CC , 20140430 SQL 2005 backwards compability 
   CC , 20140613 Add reservation notes 
   CC , 20140814 Add empty lines for filtering reservation notes in the app
   CC , 20180503 Buggfix 
*/

CREATE TABLE #NextStay
(
  Stay VARCHAR(20) COLLATE database_default NOT NULL,
  RoomID INT NOT NULL,
  RoomNote VARCHAR(8000) COLLATE database_default NULL, 
  ResNote VARCHAR(8000) 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 @ResNote VARCHAR (8000) /* 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 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
-- CC 20180503 PRINT '@Number'
-- CC 20180503 PRINT @Number
  SELECT TOP 1 
  @RoomID = RM.RoomNumber,
  @RoomNote = CONVERT(VARCHAR(8000),RM.Notes),  
  @ResNote = CONVERT(VARCHAR(8000),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 */ 

  WHERE RM.RoomNumber = @Number -- CC 20180503 WTF!? 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                    
   
   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  
, CONVERT(VARCHAR(8000),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  // CC 20191212 WeekNd */
,dbo.MCC_Linen2BChanged( RL1.ProductNumber, CONVERT(DateTime, RL1.ArrivalDate, 112 ) , CONVERT(DateTime, RL1.DepartureDate, 112 ),0 ) /* CC 20140416 */  /* CC 20140430 SQL 2005 backwards compability  // CC 20191212 WeekNd */

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

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".
                         
DROP TABLE #NextStay
