How to convert StoredProcedures to views

USE [ActatekDB]
GO
/****** Object:  StoredProcedure [dbo].[SP_GenerateAbsentPresentReport]    Script Date: 05/19/2014 11:46:11 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

/* Purpose:  
Created on 03-11-2010
*/

ALTER PROCEDURE [dbo].[SP_GenerateAbsentPresentReport]
(
	@pUserID nvarchar(50),
	@pDepartmentID int,
	@pStartDateTime datetime,
	@pEndDateTime datetime,
	@pMondaySelected int,
	@pTuesdaySelected int,
	@pWednesdaySelected int,
	@pThursdaySelected int,
	@pFridaySelected int,
	@pSaturdaySelected int,
	@pSundaySelected int,
	@pHolidaySelected int
)
AS

	-------------------------------------------------------------------------------------------------------------------
	--An absolute 'MUST HAVE' statements in every store procedure //HEAD
	Declare @ErrorCode int
	Select @ErrorCode = @@Error

	Declare @TransactionCountOnEntry int
	If @ErrorCode = 0
	Begin
	  	Select @TransactionCountOnEntry = @@TranCount
	   	BEGIN TRANSACTION
	End
	--end absolute 'Must Have' Statement
	-------------------------------------------------------------------------------------------------------------------


	If @ErrorCode = 0
	Begin		
				
		-- Create table variable instead of SQL temp table because report wont pick up the temp table
		DECLARE @tempAbsentPresentReport TABLE
		(
			UserID nvarchar(50),
			LogDate datetime,  
			LogDay varchar(20), 
			Status nvarchar(50),			
			PivotValue int,
			PivotValue2 int
		)			
		
		-- Declare variables for the while loop
		Declare @LogUserID nvarchar(50)		
		
		-- Filter off userID, departmentID (get distinct list of users)	
		Declare LogCursor Cursor For 
		Select distinct access_event_logs.USERID
		From access_event_logs
			Left Join access_user on access_user.User_ID = access_event_logs.USERID
			Left Join access_user_dept on access_user.User_ID = access_user_dept.User_ID
		Where ((Dept_ID = @pDepartmentID) OR (@pDepartmentID IS NULL))
			And ((access_event_logs.USERID LIKE '%' + @pUserID + '%') OR (@pUserID IS NULL)) 	
			And (access_event_logs.USERID  !='UNKNOWN USER') -- Ignore UNKNOWN USER
		Order by access_event_logs.USERID
		
		Open LogCursor

		Fetch Next 
		From LogCursor
		Into @LogUserID
		
		Declare @LoopStartDateTime datetime
		Declare @LoopEndDateTime datetime	
		
		Declare @PivotValue2 int	-- Report (row group by)	
		Set @PivotValue2 = 0	
		
		Declare @PivotValue int  -- Report (column group by)
		Set @PivotValue = 0 
		-- Set the piviot counter, used to determine the coloumn position
		Declare @PivotCount int
		Set @PivotCount = 1 
		Declare @MaxCols int -- Number of coloums per page of report
		Set @MaxCols = 8
		
		-- Loop thru each user
		While @@FETCH_STATUS = 0
		Begin	
			Print 'Process user ID: ' + @LogUserID	
			
			-- initialise
			Set @LoopStartDateTime = @pStartDateTime
			
			-- inclusive of end day (so add a day to make it the next day)			
			Set @LoopEndDateTime = DATEADD(day, 1, @pEndDateTime) 
			
			Set @PivotValue2 = 0   -- next user, reset pivot value	
			Set @PivotCount = @MaxCols -- next user, set piviot count to max value
			
			-- Loop thru each day (from start datetime to end datetime)
			While (@LoopEndDateTime > @LoopStartDateTime)
			Begin					
				-- Reset piviot counter when reach max coloumns
				if @PivotCount= @MaxCols
				Begin 
					set @PivotCount=1
					If @PivotValue2 >= 5000000 Set @PivotValue2 = 0 -- reset when reach a limit	
					set @PivotValue2 = @PivotValue2 + 1	
				End
			
							
				Declare @WeekDay varchar(20) -- week day
				Set @WeekDay = DATENAME(dw, @LoopStartDateTime)
				
				Declare @Status varchar(50) 
				Set @Status = '' -- reset
								
				-- Check for holiday
				Declare @IsHoliday int
				Set @IsHoliday = null 
				
				Select @IsHoliday = holidayID 
				From access_holiday
				Where CONVERT(datetime, convert(varchar,access_holiday.iyear) + '-' + convert(varchar,access_holiday.imonth) + '-' + convert(varchar,access_holiday.iday) ) = @LoopStartDateTime
				
				Declare @Proceed int
				Set @Proceed = 1 -- initialised
				
				If @IsHoliday is not null -- it is a holiday
				Begin
					Set @Status = 'Holiday'
					If (@pHolidaySelected =1) -- dont want show holiday
					Begin					
						Set @Proceed = 0; -- set flag to indicate no insertion for current interation 
					End
				End
				
				If @Proceed = 1
				BEGIN
					
					If LOWER(@WeekDay) = 'monday'
					Begin
						If (@pMondaySelected =1) -- dont want show monday
						Begin					
							Set @Proceed = 0; -- dont proceed further go to next iteration
						End
						else 
						Begin 
							Set @PivotValue = @PivotCount
							Set @PivotCount = @PivotCount +1
							Set @WeekDay = 'Mon'
						End
						
					End
					Else If LOWER(@WeekDay) = 'tuesday'
					Begin
						If (@pTuesdaySelected =1) -- dont want show tuesday
						Begin
							Set @Proceed = 0; -- dont proceed further go to next iteration
						End
						else 
						Begin
							Set @PivotValue = @PivotCount
							Set @PivotCount = @PivotCount +1	
							Set @WeekDay = 'Tue'
						End		
					End	
					Else If LOWER(@WeekDay) = 'wednesday'
					Begin
						If (@pWednesdaySelected =1) -- dont want show wednesday
						Begin
							Set @Proceed = 0; -- dont proceed further go to next iteration
						End
						else 
						Begin
							Set @PivotValue = @PivotCount
							Set @PivotCount = @PivotCount +1	
							Set @WeekDay = 'Wed'
						End		
					End
					Else If LOWER(@WeekDay) = 'thursday'
					Begin
						If (@pThursdaySelected =1) -- dont want show thursday
						Begin
							Set @Proceed = 0; -- dont proceed further go to next iteration
						End
						else 
						Begin
							Set @PivotValue = @PivotCount
							Set @PivotCount = @PivotCount +1
							Set @WeekDay = 'Thur'	
						End		
					End
					Else If LOWER(@WeekDay) = 'friday'
					Begin
						If (@pFridaySelected =1) -- dont want show friday
						Begin
							Set @Proceed = 0; -- dont proceed further go to next iteration
						End
						else 
						Begin
							Set @PivotValue = @PivotCount
							Set @PivotCount = @PivotCount +1	
						End		
					End
					Else If LOWER(@WeekDay) = 'saturday'
					Begin
						If (@pSaturdaySelected =1) -- dont want show saturday
						Begin
							Set @Proceed = 0; -- dont proceed further go to next iteration
						End
						else 
						Begin
							Set @PivotValue = @PivotCount
							Set @PivotCount = @PivotCount +1
							Set @WeekDay = 'Sat'	
						End		
					End
					Else If LOWER(@WeekDay) = 'sunday'
					Begin
						If (@pSundaySelected =1) -- dont want show sunday
						Begin
							Set @Proceed = 0; -- dont proceed further go to next iteration
						End
						else 
						Begin
							Set @PivotValue = @PivotCount
							Set @PivotCount = @PivotCount +1	
							Set @WeekDay = 'Sun'
						End		
					End
				END
				
				If (@Proceed = 1) -- can proceed to insert
				Begin
				
					Declare @Present int
					Set @Present = null
					
					-- Check whether he/she show up or not					
					Select @Present = id
					From access_event_logs
					Where LOWER(EVENTID) = 'in' And USERID = @LogUserID 
						And LOCALTIMESTAMP >= @LoopStartDateTime And LOCALTIMESTAMP < DATEADD(DAY, 1, @LoopStartDateTime)					
						
					If @Present is not null
					Begin
						If (LEN(@Status) > 0) -- if there are other status already - append at the end
							Set @Status = @Status + '(Present)'
						Else
							Set @Status = 'Present'
					End
					Else -- absent
					Begin
						if (LOWER(@Status) != 'holiday')
							Set @Status = 'Absent'
					End
					
					Print '- Date: ' + Convert(varchar, @LoopStartDateTime, 13) + ', Day:' + @WeekDay + ', status: ' + @Status
					
					Insert into @tempAbsentPresentReport (UserID, LogDate, LogDay, Status, PivotValue, PivotValue2)
					values(@LogUserID, DATEADD(HOUR, 0, DATEDIFF(DAY, 0, @LoopStartDateTime)), @WeekDay, @Status, 
						@PivotValue, @PivotValue2 )		
						
				End
				
				Set @LoopStartDateTime = DATEADD(DAY, 1, @LoopStartDateTime) -- increment a day
			End			
		
			Fetch Next 
			From LogCursor
			Into @LogUserID
		End	
			
		Close LogCursor
		Deallocate LogCursor	
		
		Select * 
		From @tempAbsentPresentReport tempTable
			Left Join access_user on access_user.User_ID = tempTable.UserID
		Order By tempTable.UserID, LogDate, PivotValue2, PivotValue
				
	End


	-------------------------------------------------------------------------------------------------------------------
	--An absolute 'MUST HAVE' statement in every store procedure //TAIL
	If @@TranCount > @TransactionCountOnEntry
	Begin
	  	 If @ErrorCode = 0
	      		COMMIT TRANSACTION
	  	 Else
	     	 	ROLLBACK TRANSACTION
	End

	return @ErrorCode
	-------------------------------------------------------------------------------------------------------------------