CREATE PROCEDURE [dbo].[MCC_GetRoomData] @userIDNo SMALLINT , @iNoteIdx SMALLINT, @dirtyTaskBoth TINYINT = 3, @lookBackDays INT =0, @OverRideSortOrder_SortByTaskDate BIT = 0 AS -- CC 20240112
--CREATE PROCEDURE [dbo].[MCC_GetRoomData] @userIDNo SMALLINT , @iNoteIdx SMALLINT, @dirtyTaskBoth TINYINT = 3, @lookBackDays INT =0  AS -- CC 20240112
SET NOCOUNT ON
/*
   Created By Carsten Cremer
   Purpose : Return all that's needed to populate a room list in Piccoline.
   -- 20211118: Changed by input from CC Req #3992
   -- 20191203 Support for Task Exists flag 
   -- 20191211 Since the Task Xists flag slows the query down by rough ly a factor of 10 I have parameterized it.
   --		   calling the function with a negative value on look back days will trigger the taskXists functionality 
   --		   a0 or a positive value will return zero.
   -- 20191212 Add option to check if the linen for in-house guests needed to be changed during the weekend.
   -- 20200924 Add option to only display rooms whith LinenChange
   -- 20201001 Add option to check if the linen for in-house guests need to be changed during the coming weekend. ( Exluding those departing during the weekend ) 
   -- 20211117 Add support for control of linen change by wares on arrangements Not needed for arrangements
   -- 20211123 Dynamically convert the two virtual clean statuses 'Stay' and 'Departure' to used so that android (already doing this locally) & ios behave the same. 
   -- 20220429 Hide NextGuests ResNote until the departure day of the currentGuest
   -- 20220602 Room name or description selectable as desription
   -- 20220812 Buggfix : But show Next guests res note when no current guest exists
   -- 20220819 Display extra beds in the room notes
   -- 20221021 Support to display out of order rooms
   -- 20230124 Control the sort order. Switch between Room number(0) = Default, Room Name (1) and Rooom Description(2) 
   -- 20240112 Overide Sort order by parameter. This enables sorting by the oldest task date for Janitors
*/
DECLARE @cNowDate DATETIME = CAST((SELECT TOP 1 SystemDate FROM System WITH (NOLOCK)) AS DATETIME)
DECLARE @IncludeArrivalsBelowDate DATETIME = DATEADD(MONTH, 6, @cNowDate) ; -- CC 20190626
DECLARE @SPACE CHAR = ' '; 

-- CC 20191212 Weeknd START 

DECLARE @RESULT VarChar(2) ='0' -- CC 20200122 Moved up

-- CC 20211117 Support for control of linen change by wares on arrangements START
DECLARE @AlsoCheckWeekndDays SMALLINT = 0; -- CC 20201001 Support for 'pre-clean' on Fridays // CC 20211117 Moved UpandOut



-- 20220602 Room name or description selectable as desription START
DECLARE @RoomNameInsteadOfRoomDescription TINYINT = 1;
--EXECUTE GPIGetHotelSetupData 'Interface.Piccoline.UseRoomNameInsteadOfRoomDescription',0,'0', @RESULT OUT;	-- CC 20221025 Buggfix HotelNumber should default to -1	
EXECUTE GPIGetHotelSetupData 'Interface.Piccoline.UseRoomNameInsteadOfRoomDescription',-1,'0', @RESULT OUT;	-- CC 20221025 Buggfix HotelNumber should default to -1	

SET @RoomNameInsteadOfRoomDescription = @RESULT	
-- 20220602 Room name or description selectable as desription STOP

DECLARE @LinenChangeByArrWare SMALLINT = 0; 
--	EXECUTE GPIGetHotelSetupData 'Interface.Piccoline.LinenChangeControlledByArrangement',0,'0', @RESULT OUT; -- CC 20221025 Buggfix HotelNumber should default to -1		
	EXECUTE GPIGetHotelSetupData 'Interface.Piccoline.LinenChangeControlledByArrangement',-1,'0', @RESULT OUT; -- CC 20221025 Buggfix HotelNumber should default to -1		
	IF @RESULT > 0 
	  SET @LinenChangeByArrWare = @RESULT		
-- CC 20211117 Support for control of linen change by wares on arrangements STOP


-- 20220819 Display extra beds in the room notes START
DECLARE @DisplayExtraBedsInNotes SMALLINT = 0; 
--	EXECUTE GPIGetHotelSetupData 'Interface.Piccoline.DisplayExtraBedsInNotes',0,'0', @RESULT OUT; -- CC 20221025 Buggfix HotelNumber should default to -1		
	EXECUTE GPIGetHotelSetupData 'Interface.Piccoline.DisplayExtraBedsInNotes',-1,'0', @RESULT OUT; -- CC 20221025 Buggfix HotelNumber should default to -1		
	IF @RESULT > 0 
	  SET @DisplayExtraBedsInNotes = @RESULT	
-- 20220819 Display extra beds in the room notes STOP


IF  (@LinenChangeByArrWare =0) -- CC 20211117 Support for control of linen change by wares on arrangements Not needed for arrangements
BEGIN							-- CC 20211117 Support for control of linen change by wares on arrangements
		SET LANGUAGE us_english;
		--DECLARE @AlsoCheckWeekndDays TINYINT = 0; -- CC 20201001 Support for 'pre-clean' on Fridays 
		--DECLARE @AlsoCheckWeekndDays SMALLINT = 0; -- CC 20201001 Support for 'pre-clean' on Fridays // CC 20211117 Moved UpandOut
		IF (( DATENAME( weekday,@cNowDate)) = 'Monday' ) 
		BEGIN  
		-- When 'Interface.Piccoline.NoWeekndCleaning_PostponeLinenChange' is POSITIVE, we postpone the weekend cleaning.
		-- This means that guests with linen change due during the weekend will have the linen changed on Monday instead.
			-- DECLARE @RESULT VarChar ='0' -- CC 20200122 Moved up
--			EXECUTE GPIGetHotelSetupData 'Interface.Piccoline.NoWeekndCleaning_PostponeLinenChange',0,'0', @RESULT OUT;	-- CC 20221025 Buggfix HotelNumber should default to -1	
			EXECUTE GPIGetHotelSetupData 'Interface.Piccoline.NoWeekndCleaning_PostponeLinenChange',-1,'0', @RESULT OUT;	-- CC 20221025 Buggfix HotelNumber should default to -1	
			IF @RESULT > 0 -- CC 20201001 Support for 'pre-clean' on Fridays 
			  SET @AlsoCheckWeekndDays = @RESULT		
		END
		ELSE IF (( DATENAME( weekday,@cNowDate)) = 'Friday' ) -- CC 20201001 Support for 'pre-clean' on Fridays 
		-- When 'Interface.Piccoline.NoWeekndCleaning_PostponeLinenChange' is NEGATIVE and it is Friday we do not postpone but prepone the weekend cleaning.
		-- This means that guests with linen change due during the weekend will have the linen changed on Friday instead. ( Not those departing during the weekend however )
		BEGIN  
--			EXECUTE GPIGetHotelSetupData 'Interface.Piccoline.NoWeekndCleaning_PostponeLinenChange',0,'0', @RESULT OUT;	-- CC 20221025 Buggfix HotelNumber should default to -1	
			EXECUTE GPIGetHotelSetupData 'Interface.Piccoline.NoWeekndCleaning_PostponeLinenChange',0,'0', @RESULT OUT;	-- CC 20221025 Buggfix HotelNumber should default to -1	
			IF @RESULT < 0  
			  SET @AlsoCheckWeekndDays = @RESULT		
		END

		--PRINT @AlsoCheckWeekndDays 

		-- CC 20191212 Weeknd STOP
END -- CC 20211117 Support for control of linen change by wares on arrangements

-- CC 20200122 OrangeDepartedRooms START
DECLARE @MarkDepartedRoomsAsDepartureCleanOrange TINYINT = 0;
EXECUTE GPIGetHotelSetupData 'Interface.Piccoline.MarkDepartedRoomsAsDepartureCleanOrange',-1,'0', @RESULT OUT;		
SET @MarkDepartedRoomsAsDepartureCleanOrange = @RESULT		
-- CC 20200122 OrangeDepartedRooms STOP

-- CC 20200924 Only display LinenChange-Rooms START
DECLARE @OnlyListRoomsSetForLinenChange TINYINT = 0;
EXECUTE GPIGetHotelSetupData 'Interface.Piccoline.OnlyListRoomsSetForLinenChange',-1,'0', @RESULT OUT;		
SET @OnlyListRoomsSetForLinenChange = @RESULT
-- CC 20200924 Only display LinenChange-Rooms STOP


-- CC 20221025 Color code OOO OFS rooms START
DECLARE @ColorCode_OOO_OFS TINYINT = 0;
EXECUTE GPIGetHotelSetupData 'Interface.Piccoline.ColorCodeOOO_OFS',-1,'0', @RESULT OUT;		
SET @ColorCode_OOO_OFS = @RESULT
-- CC 20221025 Color code OOO OFS rooms STOP


-- CC 20230124 Control the sort order START
DECLARE @SortOrder_Normal_RoomName_RoomDescription TINYINT = 0;

IF ((@lookBackDays <0) AND (@OverRideSortOrder_SortByTaskDate =1 ))	    			  -- CC 20240112 Overide Sort order by parameter
  SET @SortOrder_Normal_RoomName_RoomDescription = 3;								  -- CC 20240112 Overide Sort order by parameter
ELSE																				  -- CC 20240112 Overide Sort order by parameter
BEGIN
  EXECUTE GPIGetHotelSetupData 'Interface.Piccoline.SortBy_RNo_RName_RDesc_012',-1,'0', @RESULT OUT;		
  SET @SortOrder_Normal_RoomName_RoomDescription = @RESULT;
END

-- CC 20230124 Control the sort order STOP


CREATE TABLE #RoomSelection (RoomDBNo INT)
INSERT INTO #RoomSelection (RoomDBNo) ( SELECT * FROM dbo.tf_MCC_GetDBRoomNosWithToDosForUserx( @userIDNo , @dirtyTaskBoth, ABS(@lookBackDays))) -- CC 20191211 CC 20221025 Buggfix
;WITH T1 AS
(SELECT
  RoomNumber				   AS R_DBNO,	
  Number					   AS R_RDNO,
  HotelNumber			       AS R_HotelNo, -- CC 20230124

  CONVERT(VARCHAR(8000),Notes) AS R_RoomNote,	
	  	
-- CC 20211123 START						
--  CleanStatus				   AS R_CleanStatus, 
  (CASE WHEN (CleanStatus < 5) THEN
    Cleanstatus 
  ELSE
    1 END ) AS R_CleanStatus,  
-- CC 20211123 STOP

-- 20220602 Room name or description selectable as desription START
 ( CASE WHEN (@RoomNameInsteadOfRoomDescription = 1) THEN  Name				   
        ELSE Description END) AS R_RoomDescription,

-- 20220602  Description				   AS R_RoomDescription,
-- 20220602 Room name or description selectable as desription STOP
  Name						   AS R_RoomName,
  Sector					   AS R_Sector,	
  
  -- CC 20191211 the following addon slows down the query by roughly a factor of 10. This translates to around 50ms in the demo system instead of around 5
  --dbo.MCC_DoesThisRoomHaveOpenTasksForUserx(@userIDNo , RoomNumber, @lookBackDays) AS R_TaskXsits, -- CC 20191203 Support for Task Exists flag  -- CC 20191211
 (CASE WHEN (@lookBackDays <0) THEN
    dbo.MCC_DoesThisRoomHaveOpenTasksForUserx(@userIDNo , RoomNumber, ABS(@lookBackDays)) -- CC 20191203 Support for Task Exists flag  -- CC 20191211
  ELSE 0 END ) AS R_TaskXsits,
 
  -- CC 20191211 STOP
 
 (CASE WHEN (@ColorCode_OOO_OFS >0 ) THEN
   dbo.MCC_IsRoom_OOO_OFS (R.RoomNumber) 
 ELSE
   0 END) AS OOO_OFS, -- CC 20221021

 row_number() over (order by  R.RoomNumber) rn

FROM Rooms R WITH(NOLOCK)
  WHERE R.RoomNumber IN ( SELECT RoomDBNo FROM #RoomSelection))

,
T2 AS
(SELECT 
   RM.Number					  AS NG_RDNO ,
   RM.RoomNumber			      AS NG_DBNO,
   CONVERT(VARCHAR(max),RN.Text)  AS NG_ResNote,
   RA.FirstName					  AS NG_FirstName,
   RA.LastName					  AS NG_LastName,
	
  (RL1.Adults + RL1.Children1 + RL1.Children2)  AS NG_Pax,
  -- 20220819 Display extra beds in the room notes START
  (CASE WHEN ( @DisplayExtraBedsInNotes = 1)THEN 
		(CASE WHEN (ISNULL(RL1.Children2, 0) >0 ) THEN ' ' +LTRIM(STR(RL1.Children2))
		     +' EXTRA BED' + (CASE WHEN (ISNULL(RL1.Children2, 0) >1 ) THEN 'S'  ELSE '' END)+ CHAR(13)+CHAR(10) ELSE '' END)
	    WHEN ( @DisplayExtraBedsInNotes = 2) THEN 
		    ' PaxTypes:' + LTRIM(STR(RL1.Adults)) +'/'+ LTRIM(STR(RL1.Children1)) + '/'+ LTRIM(STR(RL1.Children2)) + CHAR(13)+CHAR(10) 
		WHEN ( @DisplayExtraBedsInNotes = 3) THEN 
		    ' Ad:' + LTRIM(STR(RL1.Adults)) +' ChOne:'+ LTRIM(STR(RL1.Children1)) + ' ChTwo:'+ LTRIM(STR(RL1.Children2)) + CHAR(13)+CHAR(10) 
		ELSE 
		   '' 
		END ) 
   AS NG_XtraBed ,  
-- 20220819 Display extra beds in the room notes STOP

  (RL1.ArrivalDate + ' ' + RL1.ArrivalTime)		AS NG_ArrivalDateTime,
  (RL1.DepartureDate + ' ' + RL1.DepartureTime) AS NG_DepartureDateTime, 
											  1 AS NG_Linen2BCHanged,
 (CASE WHEN CONVERT(DateTime, RL1.ArrivalDate, 112 ) = CONVERT(DateTime, @cNowDate, 112 ) THEN 1 ELSE  0 END) AS ArrivalToday,

  
   row_number() over (order by  RM.Number) rn
  
  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 =@iNoteIdx
  WHERE RM.RoomNumber IN ( SELECT RoomDBNo FROM #RoomSelection)

  AND RLX1.ReferenceNumber > 0 -- AND  RLX1.GuestNumber =0
  AND RL1.ArrivalDate >= @cNowDate 
  AND RL1.ArrivalDate < @IncludeArrivalsBelowDate -- CC 20190626

  AND R.Status IN (0, 1, 2, 3, 4)-- 0=confirmed, 1=provisional, 2=guaranteed, 3=waitinglist, 4=tentative
 )

,
T3 AS

(SELECT  
   RM.Number					  AS CG_RDNO ,
   RM.RoomNumber			      AS CG_DBNO,
   
   CONVERT(VARCHAR(max),RN.Text)  AS CG_ResNote,

   RA.FirstName					  AS CG_FirstName,
   RA.LastName					  AS CG_LastName,
  

  (RL1.Adults + RL1.Children1 + RL1.Children2)  AS CG_Pax,

-- 20220819 Display extra beds in the room notes START
  (CASE WHEN ( @DisplayExtraBedsInNotes = 1)THEN 
	        (CASE WHEN (ISNULL(RL1.Children2, 0) >0 ) THEN ' ' +LTRIM(STR(RL1.Children2))
		     +' EXTRA BED' + (CASE WHEN (ISNULL(RL1.Children2, 0) >1 ) THEN 'S'  ELSE '' END)+ CHAR(13)+CHAR(10) ELSE '' END)
	    WHEN ( @DisplayExtraBedsInNotes = 2) THEN 
		    ' PaxTypes:' + LTRIM(STR(RL1.Adults)) +'/'+ LTRIM(STR(RL1.Children1)) + '/'+ LTRIM(STR(RL1.Children2)) + CHAR(13)+CHAR(10) 
		WHEN ( @DisplayExtraBedsInNotes = 3) THEN 
		    ' Ad:' + LTRIM(STR(RL1.Adults)) +' ChOne:'+ LTRIM(STR(RL1.Children1)) + ' ChTwo:'+ LTRIM(STR(RL1.Children2)) + CHAR(13)+CHAR(10) 
		ELSE 
		   '' 
		END ) 
   AS CG_XtraBed ,  
-- 20220819 Display extra beds in the room notes STOP

  (RL1.ArrivalDate + ' ' + RL1.ArrivalTime)		AS CG_ArrivalDateTime,
  (RL1.DepartureDate + ' ' + RL1.DepartureTime) AS CG_DepartureDateTime, 
  (CASE WHEN CONVERT(DateTime, RL1.DepartureDate, 112 ) = CONVERT(DateTime, @cNowDate, 112 ) THEN 1 ELSE  0 END) AS DepartureToday,
  
--   dbo.MCC_Linen2BChanged( RL1.ProductNumber, CONVERT(DateTime, RL1.ArrivalDate, 112 ) , CONVERT(DateTime, RL1.DepartureDate, 112 ) ) AS CG_Linen2BChanged,  /* CC 20140416 */  /* CC 20140430 SQL 2005 backwards compability */ -- CC 20191211 Weeknd
--   dbo.MCC_Linen2BChanged( RL1.ProductNumber, CONVERT(DateTime, RL1.ArrivalDate, 112 ) , CONVERT(DateTime, RL1.DepartureDate, 112 ), @AlsoCheckWeekndDays ) AS CG_Linen2BChanged,  /* CC 20140416 */  /* CC 20140430 SQL 2005 backwards compability */ -- CC 20191212 Weeknd -- CC 20211117 Support for control of linen change by wares on arrangements 
-- CC 20211117 Support for control of linen change by wares on arrangements START
  (CASE WHEN (@LinenChangeByArrWare =1 ) THEN 
     dbo.MCC_Linen2BChanged_ByArrWare( RM.RoomNumber, @cNowDate) 
   ELSE
      dbo.MCC_Linen2BChanged( RL1.ProductNumber, CONVERT(DateTime, RL1.ArrivalDate, 112 ) , CONVERT(DateTime, RL1.DepartureDate, 112 ), @AlsoCheckWeekndDays ) END) AS CG_Linen2BChanged,  /* CC 20140416 */  /* CC 20140430 SQL 2005 backwards compability */ -- CC 20191212 Weeknd
-- CC 20211117 Support for control of linen change by wares on arrangements END

 
  row_number() over (order by  RM.Number) rn
  
  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 =@iNoteIdx
  WHERE RM.RoomNumber IN ( SELECT RoomDBNo FROM #RoomSelection)

AND RLX1.ReferenceNumber > 0 -- AND  RLX1.GuestNumber =0
AND R.Status IN (5) -- 5=inhouse
)



SELECT R_DBNO, R_RDNO AS DoorNumber,ISNULL(R_Sector, 0) AS Sector, ISNULL( R_RoomName, '') AS RoomName, ISNULL(R_RoomDescription, '') AS RoomDescription, ISNULL(R_RoomNote,'') AS RoomNote , R_CleanStatus AS CS,
-- ISNULL( ISNULL( NG_Linen2BChanged, CG_Linen2BChanged), 1) AS Linen2BChanged , (ISNULL (CG_FirstName, '') + @SPACE +  ISNULL( CG_LastName , '' )) AS CurrentGuest,  // CC 20190125
	   ISNULL( CG_Linen2BChanged, 1) AS Linen2BChanged , (ISNULL (CG_FirstName, '') + @SPACE +  ISNULL( CG_LastName , '' )) AS CurrentGuest, 

       ISNULL(CG_ArrivalDateTime, '') AS CG_Arrival, ISNULL(CG_DepartureDateTime, '') AS CG_Departure, ISNULL(CG_PAX, 0) AS CG_PAX,
	   -- ISNULL(CG_ResNote, '') AS CG_ResNote, -- 20220819 Display extra beds in the room notes 
	   CG_XtraBed + ISNULL(CG_ResNote, '') AS CG_ResNote, -- 20220819 Display extra beds in the room notes STOP

	   (ISNULL (NG_FirstName, '') + @SPACE +  ISNULL( NG_LastName , '' )) AS NextGuest,    
--       ISNULL(NG_ArrivalDateTime, '') AS NG_Arrival, ISNULL(NG_DepartureDateTime, '') AS NG_Departure, ISNULL(NG_PAX, 0) AS NG_PAX, ISNULL(NG_ResNote, '') AS NG_ResNote, -- CC 20220429 Hide NextGuests ResNote until the departure day of the currentGuest
	   ISNULL(NG_ArrivalDateTime, '') AS NG_Arrival, ISNULL(NG_DepartureDateTime, '') AS NG_Departure, ISNULL(NG_PAX, 0) AS NG_PAX, 
	       
		   --  (CASE( ISNULL (DepartureToday,0)) WHEN 0 THEN '' ELSE ISNULL(NG_ResNote, '') END) AS NG_ResNote, -- CC 20220429 Hide NextGuests ResNote until the departure day of the currentGuest -- CC 20220812 Buggfix : Show Next guests res note when no current guest exists
--			 (CASE( ISNULL ( CG_PAX,0 )) WHEN 0 THEN ISNULL(NG_ResNote, '') -- CC 20220812 Buggfix : But show Next guests res note when no current guest exists -- 20220819 Display extra beds in the room notes STOP
			 (CASE( ISNULL ( CG_PAX,0 )) WHEN 0 THEN  NG_XtraBed+ ISNULL( NG_ResNote, '') -- CC 20220812 Buggfix : But show Next guests res note when no current guest exists -- 20220819 Display extra beds in the room notes STOP
			  ELSE															-- CC 20220812 Buggfix : But show Next guests res note when no current guest exists	
			 --(CASE( ISNULL (DepartureToday,0)) WHEN 0 THEN '' ELSE ISNULL(NG_ResNote, '') END) END) AS NG_ResNote, -- CC 20220812 Buggfix : But show Next guests res note when no current guest exists -- 20220819 Display extra beds in the room notes STOP
			 (CASE( ISNULL (DepartureToday,0)) WHEN 0 THEN '' ELSE NG_XtraBed + ISNULL(NG_ResNote, '') END) END) AS NG_ResNote, -- CC 20220812 Buggfix : But show Next guests res note when no current guest exists -- 20220819 Display extra beds in the room notes STOP

-- CC 20200122	   ISNULL(ArrivalToday,0) AS ArrTD, ISNULL( DepartureToday, 1) AS DepTD, ISNULL(R_TaskXsits,0) AS TaskXists
	   ISNULL(ArrivalToday,0) AS ArrTD, ISNULL( DepartureToday, @MarkDepartedRoomsAsDepartureCleanOrange) AS DepTD, ISNULL(R_TaskXsits,0) AS TaskXists, -- CC 20200122
	   ISNULL( OOO_OFS,0) AS OOO_OFS



	   

FROM T1 FULL OUTER JOIN T2 ON  T2.NG_DBNO = T1.R_DBNO
FULL OUTER JOIN T3 ON T3.CG_DBNO =  R_DBNO  
-- WHERE ISNULL( CG_Linen2BChanged,1 ) IN (1,  @OnlyListRoomsSetForLinenChange) -- CC 20200924 Only display LinenChange-Rooms // CC 20201015 Bugfix, Always display rooms with tasks
 --WHERE (ISNULL( CG_Linen2BChanged,1 ) IN (1,  @OnlyListRoomsSetForLinenChange) OR (ISNULL(R_TaskXsits,0)  = 1)) -- CC 20200924 Only display LinenChange-Rooms // CC 20201015 Bugfix, Always display rooms with tasks // CC 20240112
 WHERE (ISNULL( CG_Linen2BChanged,1 ) IN (1,  @OnlyListRoomsSetForLinenChange) OR (ISNULL(R_TaskXsits,0)  > 0)) -- CC 20200924 Only display LinenChange-Rooms // CC 20201015 Bugfix, Always display rooms with tasks // CC 20240112
 -- CC 20230124 Control the sort order START
 -- ORDER BY RoomName, NG_ArrivalDateTime -- RoomDorNumber, Next arriving guest
 ORDER BY 
  R_HotelNo,
  CASE @SortOrder_Normal_RoomName_RoomDescription WHEN 0 THEN R_RDNO END,-- RoomDorNumber, Next arriving guest END,
  CASE @SortOrder_Normal_RoomName_RoomDescription WHEN 1 THEN R_RoomName END,
  CASE @SortOrder_Normal_RoomName_RoomDescription WHEN 2 THEN R_RoomDescription END ,
  CASE @SortOrder_Normal_RoomName_RoomDescription WHEN 3 THEN R_TaskXsits END , -- CC 20240112 Support sorting by oldest task first
  NG_ArrivalDateTime

 -- CC 20230124 Control the sort order STOP




DROP TABLE #RoomSelection
