CREATE FUNCTION [dbo].[MCC_DoesThisRoomHaveOpenTasksForUserx]( @AUserIdNumber INT, @DBRoomNumber INT, @daysLookBack INT =0 ) RETURNS INT  AS 
BEGIN

--SET NOCOUNT ON
/*
  Created by Carsten Cremer December 2019 
  Notes:
    Loosely based on FollowupRoomCleaningList by Kent Johansen 
  Purpose:
   Return YES OR NO (1/0) Depending on whether there are tasks open for the given userid on this room.
   Used to colour the tasks in Piccoline.

-- CC 20200127 Enable service user functionality with Techotel login
-- CC 20240112 Return the date of the oldest task in order to facilitate sorting in the app
*/



-- CC 20200127 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);
-- CC 20200127 STOP



DECLARE @Result INT = -1;
DECLARE @WorkingDate DATETIME
SET		@WorkingDate = CAST((SELECT TOP 1 SystemDate FROM System WITH (NOLOCK)) AS DATETIME)


-- Select all the rooms which have active open tasks for this user regardless of their clean status 
-- SET @Result = ISNULL(( SELECT  frx.FK_RoomNumber --CC 20240112
SET @Result = ISNULL(( SELECT MIN(CONVERT(INT, CONVERT(VARCHAR, f.Date, 112)))  --CC 20240112
FROM Followup f WITH (NOLOCK)
	INNER JOIN PersonalFollowUpXTab pfx WITH (NOLOCK) ON pfx.FollowupNumber = f.FollowupNumber		
	-- CC 20200127            AND ( (pfx.UserIDNumber = @AUserIdNumber))
             AND ( (pfx.UserIDNumber IN( SELECT Uid FROM @AUserIdNumbers ) ))-- CC 20200127
-- CC 20231212 Speed boost	INNER JOIN FollowupFollowupXtab ffx WITH (NOLOCK) ON ffx.FK_FollowupNumberSUB = f.FollowupNumber -- CC 20231212 this speed boost depends on a clean database with no orphan task mums
	INNER JOIN FollowupRoomsXtab frx WITH (NOLOCK) ON frx.FK_FollowupNumber = f.FollowupNumber AND (frx.FK_RoomNumber = @DBRoomNumber )
	
WHERE(f.Type = 19 -- Only cleaning type
-- CC 20231212 Speed boost	AND f.FollowupNumber IN (SELECT DISTINCT FK_FollowupNumberSUB FROM FollowupFollowupXTab WITH (NOLOCK)) -- CC 20231212 this speed boost depends on a clean database with no orphan task mums
	AND ISNULL(f.State, 0) IN (0,-1)
	AND Date >= @WorkingDate - @daysLookBack
	AND Date <= @WorkingDate)
	
	),0)

--IF @Result > 0      --CC 20240112
--   SET @Result = 1; --CC 20240112

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



RETURN @Result;

END




