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

-- 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
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
WHERE (@DirtyTaskBoth IN(1,3))
  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

UNION


-- 2a. Now select all the rooms which have active open tasks for this user 
--		regardless of their clean status - CONDITION TasksOnly = TRUE
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_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
GROUP BY
 frx.FK_RoomNumber

 )
 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
*/