CREATE PROCEDURE [dbo].[GetMCCRoomsWithToDosForUserx] @AUserIdNumber INT, @daysLookBack INT =0 AS
SET NOCOUNT ON
/*
  Created by Carsten Cremer October 2013 
  
  Notes:
    Based on FollowupRoomCleaningList by Kent Johansen 

  Change log:
*/


/*set @daysLookBack = 0*/
declare @WorkingDate DATETIME
set @WorkingDate = CAST((SELECT TOP 1 SystemDate FROM System WITH (NOLOCK)) AS DATETIME)

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
  AND ((@AUserIdNumber = -1) OR (pfx.UserIDNumber = @AUserIdNumber))
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
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 >= @WorkingDate - @daysLookBack--3--@AStartDate
AND Date <= @WorkingDate

--lje: Only extract row with max FollowupNumber.
AND f.FollowupNumber in (select Top 1
  frx2.FK_FollowupNumber
FROM Followup f2 WITH (NOLOCK)
  INNER JOIN PersonalFollowUpXTab pfx2 WITH (NOLOCK) ON pfx2.FollowupNumber = f2.FollowupNumber
  AND ((@AUserIdNumber = -1) OR (pfx2.UserIDNumber = @AUserIdNumber))
  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 Date >= @WorkingDate - @daysLookBack
  AND Date <= @WorkingDate
  AND frx2.FK_RoomNumber = frx.FK_RoomNumber
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



UNION ALL

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
  AND ((@AUserIdNumber = -1) OR (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
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 (0,-1)
AND Date >= @WorkingDate - @daysLookBack
AND Date <= @WorkingDate


ORDER BY Rooms.Number, f.FollowupNumber