Hi,
how to convert StoredProcedures to Views
USE [ActatekDB]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
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
Declare @ErrorCode int
Select @ErrorCode = @@Error
Declare @TransactionCountOnEntry int
If @ErrorCode = 0
Begin
Select @TransactionCountOnEntry = @@TranCount
BEGIN TRANSACTION
End
If @ErrorCode = 0
Begin
DECLARE @tempAbsentPresentReport TABLE
(
UserID nvarchar(50),
LogDate datetime,
LogDay varchar(20),
Status nvarchar(50),
PivotValue int,
PivotValue2 int
)
Declare @LogUserID nvarchar(50)
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')
Order by access_event_logs.USERID
Open LogCursor
Fetch Next
From LogCursor
Into @LogUserID
Declare @LoopStartDateTime datetime
Declare @LoopEndDateTime datetime
Declare @PivotValue2 int
Set @PivotValue2 = 0
Declare @PivotValue int
Set @PivotValue = 0
Declare @PivotCount int
Set @PivotCount = 1
Declare @MaxCols int
Set @MaxCols = 8
While @@FETCH_STATUS = 0
Begin
Print 'Process user ID: ' + @LogUserID
Set @LoopStartDateTime = @pStartDateTime
Set @LoopEndDateTime = DATEADD(day, 1, @pEndDateTime)
Set @PivotValue2 = 0
Set @PivotCount = @MaxCols
While (@LoopEndDateTime > @LoopStartDateTime)
Begin
if @PivotCount= @MaxCols
Begin
set @PivotCount=1
If @PivotValue2 >= 5000000 Set @PivotValue2 = 0
set @PivotValue2 = @PivotValue2 + 1
End
Declare @WeekDay varchar(20)
Set @WeekDay = DATENAME(dw, @LoopStartDateTime)
Declare @Status varchar(50)
Set @Status = ''
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
If @IsHoliday is not null
Begin
Set @Status = 'Holiday'
If (@pHolidaySelected =1)
Begin
Set @Proceed = 0;
End
End
If @Proceed = 1
BEGIN
If LOWER(@WeekDay) = 'monday'
Begin
If (@pMondaySelected =1)
Begin
Set @Proceed = 0;
End
else
Begin
Set @PivotValue = @PivotCount
Set @PivotCount = @PivotCount +1
Set @WeekDay = 'Mon'
End
End
Else If LOWER(@WeekDay) = 'tuesday'
Begin
If (@pTuesdaySelected =1)
Begin
Set @Proceed = 0;
End
else
Begin
Set @PivotValue = @PivotCount
Set @PivotCount = @PivotCount +1
Set @WeekDay = 'Tue'
End
End
Else If LOWER(@WeekDay) = 'wednesday'
Begin
If (@pWednesdaySelected =1)
Begin
Set @Proceed = 0;
End
else
Begin
Set @PivotValue = @PivotCount
Set @PivotCount = @PivotCount +1
Set @WeekDay = 'Wed'
End
End
Else If LOWER(@WeekDay) = 'thursday'
Begin
If (@pThursdaySelected =1)
Begin
Set @Proceed = 0;
End
else
Begin
Set @PivotValue = @PivotCount
Set @PivotCount = @PivotCount +1
Set @WeekDay = 'Thur'
End
End
Else If LOWER(@WeekDay) = 'friday'
Begin
If (@pFridaySelected =1)
Begin
Set @Proceed = 0;
End
else
Begin
Set @PivotValue = @PivotCount
Set @PivotCount = @PivotCount +1
End
End
Else If LOWER(@WeekDay) = 'saturday'
Begin
If (@pSaturdaySelected =1)
Begin
Set @Proceed = 0;
End
else
Begin
Set @PivotValue = @PivotCount
Set @PivotCount = @PivotCount +1
Set @WeekDay = 'Sat'
End
End
Else If LOWER(@WeekDay) = 'sunday'
Begin
If (@pSundaySelected =1)
Begin
Set @Proceed = 0;
End
else
Begin
Set @PivotValue = @PivotCount
Set @PivotCount = @PivotCount +1
Set @WeekDay = 'Sun'
End
End
END
If (@Proceed = 1)
Begin
Declare @Present int
Set @Present = null
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)
Set @Status = @Status + '(Present)'
Else
Set @Status = 'Present'
End
Else
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)
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
If @@TranCount > @TransactionCountOnEntry
Begin
If @ErrorCode = 0
COMMIT TRANSACTION
Else
ROLLBACK TRANSACTION
End
return @ErrorCode