# "ListTimeEntries" 存储过程

说明：

"ListTimeEntries" 存储过程能得到设定的时间范围内某一给定用户的登陆时间记录..要输入的参数包括发出请求用户的id,给定用户的id以及开始时间及截止时间。

定义：

    	CREATE PROCEDURE TT_ListTimeEntries
    	(
    		@QueryUserID int,
    		@UserID int,
    		@StartDate datetime,
    		@EndDate datetime
    	)
    	AS
    	DECLARE @@QueryUserRoleID int

    	SELECT @@QueryUserRoleID = TT_Users.RoleID FROM TT_Users WHERE TT_Users.UserID = @QueryUserID

    	IF @@QueryUserRoleID = 2
    		BEGIN
    			SELECT
    				EntryLogID, TT_EntryLog.Description, Duration, EntryDate, TT_EntryLog.ProjectID AS ProjectID,
    				TT_EntryLog.CategoryID AS CategoryID, TT_Categories.Abbreviation AS CategoryName, TT_Projects.Name AS ProjectName,
    				ManagerUserID, TT_Categories.Abbreviation AS CatShortName
    			FROM
    				TT_EntryLog
    					INNER JOIN
    					TT_Categories
    					ON
    					TT_EntryLog.CategoryID = TT_Categories.CategoryID
    					INNER JOIN
    					TT_Projects
    					ON
    					TT_EntryLog.ProjectID = TT_Projects.ProjectID
    			WHERE
    				UserID = @UserID
    				AND
    				Convert(nvarchar, EntryDate, 1) >= Convert(nvarchar, @StartDate, 1)
    				AND
    				Convert(nvarchar, EntryDate, 1) <= Convert(nvarchar, @EndDate, 1)
    				AND
    				ManagerUserID = @QueryUserID
    		END

    	ELSE IF @@QueryUserRoleID = 1 or @QueryUserID = @UserID
    		BEGIN
    			SELECT
    				EntryLogID, TT_EntryLog.Description, Duration, EntryDate, TT_EntryLog.ProjectID AS ProjectID,
    				TT_EntryLog.CategoryID AS CategoryID, TT_Categories.Abbreviation AS CategoryName, TT_Projects.Name AS ProjectName,
    				ManagerUserID, TT_Categories.Abbreviation AS CatShortName
    			FROM
    				TT_EntryLog
    					INNER JOIN
    					TT_Categories
    					ON
    					TT_EntryLog.CategoryID = TT_Categories.CategoryID
    					INNER JOIN
    					TT_Projects
    					ON
    					TT_EntryLog.ProjectID = TT_Projects.ProjectID
    			WHERE
    				UserID = @UserID
    				AND
    				Convert(nvarchar, EntryDate, 1) >= Convert(nvarchar, @StartDate, 1)
    				AND
    				Convert(nvarchar, EntryDate, 1) <= Convert(nvarchar, @EndDate, 1)
    		END

用到的数据库表：

EntryLog表中的主键是EntryLogID字段，它的表结构如下图所示。EntryLog表通过CategoryID字段和Category表的主键进行外部关联，这种关联关系保证了两个表进行数据插入与更新时的一致性。另外, 字段ProjectID也是Projects表的主键。

Projects表的主键是ProjectID，其表结构如下图所示：

Categories表的主键是CategoryID字段，其表结构如下图所示：