CREATE PROCEDURE [dbo].[GetMaintenanceTasksForRoom] @AUserIdNumber INT, @ADBRoomNumber INT, @AFollowupState VARCHAR(100), @AStartDate DATETIME, @AEndDate DATETIME AS
SET NOCOUNT ON
/*
  Created by Carsten Cremer June 2012 based on the FollowupRoomCleaningList by Kent Johansen May 2009
  Notes:
-- CC 20200127 Enable service user functionality with Techotel login
*/

SET NOCOUNT ON


-- 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 @daysLookBack int
-- CC 20190513 Buggfix START
--set @daysLookBack = 1;
DECLARE @RESULT VarChar(768);
	SET @Result = '0';
	EXECUTE GPIGetHotelSetupData 'Interface.Piccoline.LookBackDays',0,'1500', @Result OUT;
-- CC 20191218	set @daysLookBack = @RESULT; 
	set @daysLookBack = ABS(@RESULT); -- CC 20191218
-- CC 20190513 Buggfix END




-- BEGIN of selection of TaskMums ---------------
SELECT  f.FollowupNumber   --0
  ,CONVERT(VARCHAR(8000), f.Note)   --1
  ,MAX(f.Date)     --2 
  ,frx.FK_RoomNumber  --3
  ,ISNULL(f.Deviation, 0) AS Deviation--0 as Deviation   --4
  ,f.Priority as Priority   --5
  ,-1 as FK_FollowupNumber  --6
  ,ROOMS.Number as Number  --7
  ,ROOMS.Name as Name  --8
  ,Rooms.CleanStatus as CleanStatus  --9
  ,f.[State] -- 0 as [State]  --10
  ,SECTORS.Name as Name  --11
  ,0 as FK_FollowupNumber  --12
  ,convert(varchar(200),Rooms.NOTES) as NOTES  --13
  ,ROOMS.Description as RoomsDescription  -- 14  lje: added 2011.03.02
FROM Followup f WITH (NOLOCK)

INNER JOIN PersonalFollowUpXTab pfx WITH (NOLOCK) ON pfx.FollowupNumber = f.FollowupNumber
-- CC 20200127            AND ( (pfx.UserIDNumber = @AUserIdNumber))
             AND ((@AUserIdNumber = -1) OR (pfx.UserIDNumber IN( SELECT Uid FROM @AUserIdNumbers ) ))-- CC 20200127
  
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
  AND Rooms.RoomNumber = @ADBRoomNumber
  
LEFT OUTER JOIN Sectors WITH (NOLOCK) ON Rooms.Sector = SectorNumber


WHERE f.Type = 19 -- Only cleaning type
AND f.FollowupNumber IN (SELECT DISTINCT FK_FollowupNumber FROM FollowupFollowupXTab WITH 
(NOLOCK))
AND Date >= CAST((SELECT TOP 1 SystemDate FROM System WITH (NOLOCK)) AS DATETIME) - 
@daysLookBack--3--@AStartDate
AND Date <= @AEndDate




AND f.FollowupNumber in (select TOP 100 
  frx2.FK_FollowupNumber
FROM Followup f2 WITH (NOLOCK)
  INNER JOIN PersonalFollowUpXTab pfx2 WITH (NOLOCK) ON pfx2.FollowupNumber = f2.FollowupNumber
-- CC 20200127            AND ( (pfx.UserIDNumber = @AUserIdNumber))
             AND ((@AUserIdNumber = -1) OR (pfx2.UserIDNumber IN( SELECT Uid FROM @AUserIdNumbers ) ))-- CC 20200127
  INNER JOIN FollowupRoomsXtab frx2 WITH (NOLOCK) ON frx2.FK_FollowupNumber = f2.FollowupNumber
WHERE f2.Type = 19 -- Only cleaning type
  --AND f2.FollowupNumber IN (SELECT DISTINCT frx3.FK_FollowupNumber FROM FollowupFollowupXTab frx3 WITH (NOLOCK))
  
  AND f2.FollowupNumber IN-- (SELECT frx3.FK_FollowupNumber FROM FollowupFollowupXTab frx3 WITH (NOLOCK))  
 (
-- BEGIN of collection of TaskBabies for MummmyFind
SELECT DISTINCT f.FollowupNumber
FROM Followup f WITH (NOLOCK)

INNER JOIN PersonalFollowUpXTab pfx WITH (NOLOCK) ON pfx.FollowupNumber = f.FollowupNumber
  -- CC 20200127            AND ( (pfx.UserIDNumber = @AUserIdNumber))
             AND ((@AUserIdNumber = -1) OR (pfx.UserIDNumber IN( SELECT Uid FROM @AUserIdNumbers ) ))-- CC 20200127

INNER JOIN FollowupRoomsXtab frx WITH (NOLOCK) ON frx.FK_FollowupNumber = f.FollowupNumber

WHERE f.Type = 19 -- Only cleaning type
AND frx.FK_RoomNumber = @ADBRoomNumber

--AND f.FollowupNumber IN (SELECT DISTINCT FK_FollowupNumberSub FROM FollowupFollowupXTab WITH (NOLOCK)) Den behövs nog inte

AND ISNULL(f.State, 0) IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AFollowupState))
AND Date >= CAST((SELECT TOP 1 SystemDate FROM System WITH (NOLOCK)) AS DATETIME) - @daysLookBack--3--@AStartDate
AND Date <= @AEndDate
-- END of collection of TaskBabies for MummmyFind 
)






  
  AND Date >= CAST((SELECT TOP 1 SystemDate FROM System WITH (NOLOCK)) AS DATETIME) - 
@daysLookBack--3--@AStartDate
  AND Date <= @AEndDate
  AND frx2.FK_RoomNumber = frx.FK_RoomNumber
  AND f2.State = @AFollowupState
  AND frx2.FK_RoomNumber = @ADBRoomNumber 
 
 order by 1 desc 
 ) 


GROUP BY f.FollowupNumber,frx.FK_RoomNumber,f.Deviation,ROOMS.Number,CONVERT(VARCHAR(8000), 
f.Note),Rooms.Name,Rooms.CleanStatus
,f.State,SECTORS.Name
,convert(varchar(200),Rooms.NOTES),f.Priority
,ROOMS.Description  -- lje: added 2011.03.02

-- END of selection of TaskMums ---------------



UNION ALL

-- BEGIN of collection of TaskBabies
SELECT DISTINCT f.FollowupNumber
, CONVERT(VARCHAR(8000), f.Note)
, f.Date
, frx.FK_RoomNumber
, ISNULL(f.Deviation, 0) AS Deviation
, f.Priority
, frx.FK_FollowupNumber
, Rooms.Number
, Rooms.Name
, Rooms.CleanStatus
, f.State
, Sectors.Name
, ffx.FK_FollowupNumber
, convert(varchar(100),Rooms.Notes)  --'Notes not found.' as NOTES, lje: fix 2011.11.22
, Rooms.Description as RoomsDescription  -- lje: added 2011.03.02
FROM Followup f WITH (NOLOCK)

INNER JOIN PersonalFollowUpXTab pfx WITH (NOLOCK) ON pfx.FollowupNumber = f.FollowupNumber
-- CC 20200127            AND ( (pfx.UserIDNumber = @AUserIdNumber))
             AND ((@AUserIdNumber = -1) OR (pfx.UserIDNumber IN( SELECT Uid FROM @AUserIdNumbers ) ))-- CC 20200127
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
  AND Rooms.RoomNumber = @ADBRoomNumber



LEFT OUTER JOIN Sectors WITH (NOLOCK) ON Rooms.Sector = SectorNumber

WHERE f.Type = 19 -- Only cleaning type

AND f.FollowupNumber IN (SELECT DISTINCT FK_FollowupNumberSub FROM FollowupFollowupXTab WITH 
(NOLOCK))
AND ISNULL(f.State, 0) IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AFollowupState))
AND Date >= CAST((SELECT TOP 1 SystemDate FROM System WITH (NOLOCK)) AS DATETIME) - 
@daysLookBack--3--@AStartDate
AND Date <= @AEndDate


ORDER BY Rooms.Number, f.FollowupNumber

-- END of collection of TaskBabies
