CREATE FUNCTION [dbo].[tf_MCC_GetDBRoomNosWithToDosForUserx]( @AUserIdNumber INT,@DirtyTaskBoth TINYINT = 3, @daysLookBack INT =0 ) RETURNS @RT TABLE(DBRoomNo INT)  AS -- RETURNS @T TABLE(Col1 INT, Cool2 INT) AS 
BEGIN

--SET NOCOUNT ON
/*
  Created by Carsten Cremer November 2018 
  Notes:
    Based on FollowupRoomCleaningList by Kent Johansen 
  Purpose:
   Return ONLY the roomnumbers for further processing by other SP's
   TaskMums = (FK_FollowupNumber) TaskBabies = (FK_FollowupNumberSub)
   DirtyTaskBoth controls whether to return 
   = 1: ONLY rooms whose clean status <> CLEAN and which have had a TaskMum assigned to the UserID during the LookBack time frame
   = 2: ONLY rooms which currently have a TaskMum assigned to the UserID in question
   = 3: Both of the above combined, which is the default for the IOS Piccoline at this time

   20200123 Enable service user functionality with techotel login
   20230315 Turns out that this generous parameterization really can slow things down when there are a lot of tasks and rooms-
		    DirtyTaskBoth = 3 does not really make sense as a separate parameter since rooms with tasks ALWAYS should be returned
			Instead the parameters need to be redefined to 1 = Rooms which are either dirty OR have tasks and 2 ONLY rooms with tasks.				
   20230503 Reversed the SUB thingy to produce the desired result without understanding why
*/

-- CC 20200123 START
DECLARE @AUserIdNumbers  TABLE ( Uid int);
IF (@AUserIdNumber < 1) 
  INSERT INTO @AUserIdNumbers SELECT UserIdNumber FROM PERSONEL WHERE ( ActivePerson = 1 ) AND ( ISNULL( MarkAsDeleted, 0) = 0)  AND ( UserTypeNumber = 1);
ELSE
  INSERT INTO @AUserIdNumbers VALUES (@AUserIdNumber);

DECLARE @WorkingDate DATETIME
SET		@WorkingDate = CAST((SELECT TOP 1 SystemDate FROM System WITH (NOLOCK)) AS DATETIME)
-- CC 20200123 STOP


-- print @DirtyTaskBoth

-- 1a. Select all rooms whose cleaningstatus <> CLEAN but have at some time had a TaskMum assigned to 
--		this user within the lookback frame - CONDITION TasksOnly = FALSE
--		include rooms which currently have open tasks for this user regardless of the cleaning status.	-- CC 20230315 	
IF ((@DirtyTaskBoth = 1 ) OR (@DirtyTaskBoth = 3 )) -- CC 20230315
BEGIN
	insert into @RT(DBRoomNo) 
	(
	SELECT DISTINCT 
	 frx.FK_RoomNumber
	FROM Followup f WITH (NOLOCK)
	  INNER JOIN PersonalFollowUpXTab pfx WITH (NOLOCK) ON pfx.FollowupNumber = f.FollowupNumber 
	-- CC 20200123            AND ( (pfx.UserIDNumber = @AUserIdNumber))
				 AND ( (pfx.UserIDNumber IN( SELECT Uid FROM @AUserIdNumbers ) ))-- CC 20200123
	  INNER JOIN FollowupFollowupXtab ffx WITH (NOLOCK) ON ffx.FK_FollowupNumber = f.FollowupNumber
	  INNER JOIN FollowupRoomsXtab frx WITH (NOLOCK) ON frx.FK_FollowupNumber = f.FollowupNumber
	  INNER JOIN Rooms WITH (NOLOCK) ON Rooms.RoomNumber = frx.FK_RoomNumber
-- 			AND (Rooms.CleanStatus <> 0) -- 0=Clean -- CC 20230315 			
--	WHERE (@DirtyTaskBoth IN(1,3))-- CC 20230315
	WHERE ((Rooms.CleanStatus <> 0) OR ( ISNULL(f.State, 0) IN (0,-1))) -- 0=Clean -- CC 20230315
	  AND f.Type = 19 -- Only cleaning type
	  AND f.FollowupNumber IN (SELECT DISTINCT FK_FollowupNumber FROM FollowupFollowupXTab WITH (NOLOCK))
	  AND Date >= @WorkingDate - @daysLookBack
	  AND Date <= @WorkingDate
	GROUP BY frx.FK_RoomNumber
	)
END
-- CC 20230315 UNION
-- CC 20230315)
ELSE    -- CC 20230315
-- 2a. Now select all the rooms which have active open tasks for this user 
--		regardless of their clean status - CONDITION TasksOnly = TRUE
-- CC 20230315 ONLY return rooms which have open tasks
BEGIN   -- CC 20230315
	insert into @RT(DBRoomNo) -- CC 20230315
	( -- CC 20230315
	SELECT DISTINCT 
	 frx.FK_RoomNumber
	FROM Followup f WITH (NOLOCK)
		INNER JOIN PersonalFollowUpXTab pfx WITH (NOLOCK) ON pfx.FollowupNumber = f.FollowupNumber
	-- CC 20200123            AND ( (pfx.UserIDNumber = @AUserIdNumber))
				 AND ( (pfx.UserIDNumber IN( SELECT Uid FROM @AUserIdNumbers ) ))-- CC 20200123
		
-- CC 20231212 Speed boost		INNER JOIN FollowupFollowupXtab ffx WITH (NOLOCK) ON ffx.FK_FollowupNumberSUB = f.FollowupNumber -- CC 20230315 // CC 20230503 Reversed the SUB thingy to produce the desired result without understanding why -- CC 20231212 this speed boost depends on a clean database with no orphan task mums
--		INNER JOIN FollowupFollowupXtab ffx WITH (NOLOCK) ON ffx.FK_FollowupNumber = f.FollowupNumber	 -- CC 20230315 // CC 20230503 Reversed the SUB thingy to produce the desired result without understanding why

		INNER JOIN FollowupRoomsXtab frx WITH (NOLOCK) ON frx.FK_FollowupNumber = f.FollowupNumber
		INNER JOIN Rooms WITH (NOLOCK) ON Rooms.RoomNumber = frx.FK_RoomNumber
-- CC 20230315	WHERE(@DirtyTaskBoth IN(2,3))
--		AND f.Type = 19 -- Only cleaning type -- CC 20230315
		WHERE f.Type = 19 -- Only cleaning type -- CC 20230315

-- CC 20231212 Speed boost		 AND f.FollowupNumber IN (SELECT DISTINCT FK_FollowupNumberSUB FROM FollowupFollowupXTab WITH (NOLOCK)) -- CC 20230315 // CC 20230503 Reversed the SUB thingy to produce the desired result without understanding why -- CC 20231212 this speed boost depends on a clean database with no orphan task mums
		-- AND f.FollowupNumber IN (SELECT DISTINCT FK_FollowupNumber FROM FollowupFollowupXTab WITH (NOLOCK))	    -- CC 20230315 // CC 20230503 Reversed the SUB thingy to produce the desired result without understanding why

		AND ISNULL(f.State, 0) IN (0,-1)
		AND Date >= (@WorkingDate - @daysLookBack )
		AND Date <= @WorkingDate
	GROUP BY
	 frx.FK_RoomNumber
 
	 )
 END-- CC 20230315
 RETURN

-- CC 20180530 , f.FollowupNumber
-- CC OLD SQL SERVER FAILS OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'))  -- CC 20180529 Optimization

END

/*
SELECT DISTINCT 
 frx.FK_RoomNumber
FROM Followup f WITH (NOLOCK)
	INNER JOIN PersonalFollowUpXTab pfx WITH (NOLOCK) ON pfx.FollowupNumber = f.FollowupNumber
			AND ( (pfx.UserIDNumber = @AUserIdNumber))
	INNER JOIN FollowupFollowupXtab ffx WITH (NOLOCK) ON ffx.FK_FollowupNumberSub = f.FollowupNumber
	INNER JOIN FollowupRoomsXtab frx WITH (NOLOCK) ON frx.FK_FollowupNumber = f.FollowupNumber
	INNER JOIN Rooms WITH (NOLOCK) ON Rooms.RoomNumber = frx.FK_RoomNumber
WHERE(@DirtyTaskBoth IN(2,3))
	AND f.Type = 19 -- Only cleaning type
	AND f.FollowupNumber IN (SELECT DISTINCT FK_FollowupNumberSub FROM FollowupFollowupXTab WITH (NOLOCK))
	AND ISNULL(f.State, 0) IN (0,-1)
	AND Date >= @WorkingDate - @daysLookBack
	AND Date <= @WorkingDate
ORDER BY
 frx.FK_RoomNumber
*/
