CREATE PROCEDURE [dbo].[FollowupRoomCleaningList] @AUserIdNumber INT, @AFollowupState VARCHAR(100), @AStartDate DATETIME, @AEndDate DATETIME AS
SET NOCOUNT ON
/*
  Created by Kent Johansen May 2009
  Notes:

  Change log:

    2011.11.24 LJE: New approach - due to error when showing groups in todo-list.
    2011.11.22 LJE: NOTES retrieved for subtask - not "Notes not found".
    === 
    JIM: Bug: Description used, corrected to Name - from the ROOMS table
    2010.10.27 LJE: New column added in each select: convert(varchar(200),Rooms.NOTES) as NOTES  
                                                    - and 'Notes not found.' as NOTES

    2011.02.15 LJE: Extraction also include tasks which is "NOT CLEAN" - 14 days back!
                        (adjust expression "Date >=..." if neccessary - found 3 places in sql-code)
						
    2011.02.28 LJE: Only look 3 days back!		

    2011.03.01 LJE: Only look 1 day back! declare variable introduced.	
	
	2011.03.02 LJE: Rooms.Description added.
*/


declare @daysLookBack int
set @daysLookBack = 1

-- Declare vars used by cursor.
declare @FollowupNumber int
declare @FollowupNote varchar(8000)
declare @Date datetime
declare @RoomNumber int
declare @Deviation bit
declare @Priority tinyint
declare @FK_FollowupNumber1 int
declare @Number int
declare @Name1 varchar(50)
declare @CleanStatus tinyint
declare @State tinyint
declare @Name2 varchar(20)
declare @FK_FollowupNumber2 int
declare @RoomNote varchar(100)
declare @RoomDescription varchar(50)
declare @GroupName varchar(100)   
declare @HeadFollowupNumber int  
declare @LastHeadFollowupNumber int
set @LastHeadFollowupNumber = 0

-- Temp table holding initial result - with all todos. Groupname for todo is found in todo's-row.
CREATE TABLE #Result (
  FollowupNumber int
  ,FollowupNote varchar(8000)
  ,Date datetime
  ,RoomNumber int
  ,Deviation bit
  ,Priority tinyint
  ,FK_FollowupNumber1 int
  ,Number int
  ,Name1 varchar(50)
  ,CleanStatus tinyint
  ,State tinyint
  ,Name2 varchar(20)
  ,FK_FollowupNumber2 int
  ,RoomNote varchar(100)
  ,RoomDescription varchar(50)
  ,GroupName varchar(100)   -- name of todos' group
  ,HeadFollowupNumber int  -- same as FK_FollowupNumber2
)

DECLARE InitialDataSet_Cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT DISTINCT f.FollowupNumber --0
, CONVERT(VARCHAR(8000), f.Note) --1
, f.Date --2
, frx.FK_RoomNumber --3
, ISNULL(f.Deviation, 0) AS Deviation --4
, f.Priority --5
, frx.FK_FollowupNumber --6
, Rooms.Number --7
, Rooms.Name --8
, Rooms.CleanStatus --9
, f.State --10
, Sectors.Name --11
, ffx.FK_FollowupNumber --12
, convert(varchar(100),rooms.notes) --13
, Rooms.Description as RoomsDescription  --14
, CONVERT(VARCHAR(100), fHead.Note) GroupName --15
, fhead.FollowupNumber HeadFollowupNumber-- 16
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
INNER JOIN Followup fHead WITH (NOLOCK) ON fHead.FollowupNumber = ffx.FK_FollowupNumber
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 F.Date >= CAST((SELECT TOP 1 SystemDate FROM System WITH (NOLOCK)) AS DATETIME) - @daysLookBack--3--@AStartDate
AND F.Date <= @AEndDate

ORDER BY Rooms.Number, fhead.FollowupNumber, f.FollowupNumber

-- Open cursor.
OPEN InitialDataSet_Cursor
  FETCH NEXT FROM InitialDataSet_Cursor INTO 
 @FollowupNumber
 ,@FollowupNote
 ,@Date
 ,@RoomNumber
 ,@Deviation
 ,@Priority
 ,@FK_FollowupNumber1
 ,@Number
 ,@Name1
 ,@CleanStatus
 ,@State
 ,@Name2
 ,@FK_FollowupNumber2
 ,@RoomNote
 ,@RoomDescription
 ,@GroupName
 ,@HeadFollowupNumber
 
  WHILE(@@Fetch_status = 0) 
  BEGIN

    if @HeadFollowupNumber > @LastHeadFollowupNumber
    begin
     -- Insert group row. 
     insert into #Result values (@HeadFollowupNumber,@GroupName,@Date,@RoomNumber,0,@Priority,-1,@Number,@Name1,@CleanStatus,0,@Name2,0,@RoomNote,@RoomDescription,'','')
     set @LastHeadFollowupNumber = @HeadFollowupNumber
    end

    -- Insert todo row.
	insert into #Result values (
	 @FollowupNumber
	 ,@FollowupNote
	 ,@Date
	 ,@RoomNumber
	 ,@Deviation
	 ,@Priority
	 ,@FK_FollowupNumber1
	 ,@Number
	 ,@Name1
	 ,@CleanStatus
	 ,@State
	 ,@Name2
	 ,@FK_FollowupNumber2
	 ,@RoomNote
	 ,@RoomDescription
	 ,''--@GroupName
	 ,0--@HeadFollowupNumber
      )
  
    -- Fetch next row from inital data set.
	  FETCH NEXT FROM InitialDataSet_Cursor INTO 
	 @FollowupNumber
	 ,@FollowupNote
	 ,@Date
	 ,@RoomNumber
	 ,@Deviation
	 ,@Priority
	 ,@FK_FollowupNumber1
	 ,@Number
	 ,@Name1
	 ,@CleanStatus
	 ,@State
	 ,@Name2
	 ,@FK_FollowupNumber2
	 ,@RoomNote
	 ,@RoomDescription
	 ,@GroupName
	 ,@HeadFollowupNumber
 END

CLOSE InitialDataSet_Cursor
DEALLOCATE InitialDataSet_Cursor

-- Show final result.
SELECT FollowupNumber  --0
	 ,FollowupNote   --1
	 ,Date   --2
	 ,RoomNumber  --3
	 ,Deviation   --4
	 ,Priority  --5
	 ,FK_FollowupNumber1  --6
	 ,Number  --7
	 ,Name1  --8
	 ,CleanStatus  --9
	 ,State  --10
	 ,Name2   --11
	 ,FK_FollowupNumber2  --12
	 ,RoomNote  --13
	 ,RoomDescription  --14
FROM #Result
DROP TABLE #Result
