CREATE PROCEDURE [dbo].[MCC_GetRoomData] @userIDNo SMALLINT , @iNoteIdx SMALLINT, @dirtyTaskBoth TINYINT = 3, @lookBackDays INT =0  AS
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. 
*/
DECLARE @WorkingDate DATETIME = CAST((SELECT TOP 1 SystemDate FROM System WITH (NOLOCK)) AS DATETIME)
DECLARE @IncludeArrivalsBelowDate DATETIME = DATEADD(MONTH, 6, @WorkingDate) ; -- 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

DECLARE @LinenChangeByArrWare SMALLINT = 0; 
	EXECUTE GPIGetHotelSetupData 'Interface.Piccoline.LinenChangeControlledByArrangement',0,'0', @RESULT OUT;	
	IF @RESULT > 0 
	  SET @LinenChangeByArrWare = @RESULT		
-- CC 20211117 Support for control of linen change by wares on arrangements 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,@WorkingDate)) = '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;	
			IF @RESULT > 0 -- CC 20201001 Support for 'pre-clean' on Fridays 
			  SET @AlsoCheckWeekndDays = @RESULT		
		END
		ELSE IF (( DATENAME( weekday,@WorkingDate)) = '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;	
			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


CREATE TABLE #RoomSelection (RoomDBNo INT)
-- INSERT INTO #RoomSelection (RoomDBNo) ( SELECT * FROM dbo.tf_MCC_GetDBRoomNosWithToDosForUserx( @userIDNo , @dirtyTaskBoth, @lookBackDays)) -- CC 20191211
INSERT INTO #RoomSelection (RoomDBNo) ( SELECT * FROM dbo.tf_MCC_GetDBRoomNosWithToDosForUserx( @userIDNo , @dirtyTaskBoth, ABS(@lookBackDays))) -- CC 20191211
;WITH T1 AS
(SELECT
  RoomNumber				   AS R_DBNO,	
  Number					   AS R_RDNO,
  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

  Description				   AS R_RoomDescription,
  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

 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,
  (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, @WorkingDate, 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 >= @WorkingDate 
  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,
  (RL1.ArrivalDate + ' ' + RL1.ArrivalTime)		AS CG_ArrivalDateTime,
  (RL1.DepartureDate + ' ' + RL1.DepartureTime) AS CG_DepartureDateTime, 
  (CASE WHEN CONVERT(DateTime, RL1.DepartureDate, 112 ) = CONVERT(DateTime, @WorkingDate, 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, @WorkingDate) 
   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,
	   (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 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



	   

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
ORDER BY R_RDNO, NG_ArrivalDateTime -- RoomDorNumber, Next arriving guest





DROP TABLE #RoomSelection