# "ListUserTimeSummary" 存储过程

说明：

这个过程将根据时间范围来获取多个用户总结。

定义：

    	CREATE  PROCEDURE TT_ListUserTimeSummary
    	(
    		@ManagerUserID int,
    		@UserIDList nvarchar(512),
    		@StartDate datetime,
    		@EndDate datetime
    	)
    	AS

    	DECLARE
    		@sSqlString nvarchar(1024),
    		@sSubSql nvarchar(1024),
    		@RoleID int

    	SELECT @RoleID = RoleID
    	FROM TT_Users
    	WHERE UserID = @ManagerUserID;

    	IF (@RoleID = 1)
    		BEGIN
    		SET @sSqlString = 'SELECT Sum(EL.Duration) TotalHours, U.UserID, U.UserName'
    		SET @sSqlString = @sSqlString + ' FROM TT_EntryLog EL Inner Join TT_Users U On EL.UserID = U.UserID WHERE U.UserID IN (' + @UserIDList + ')'
    		SET @sSqlString = @sSqlString + ' and EL.EntryDate >= @1 and EL.EntryDate <= @2  GROUP BY U.UserID, U.UserName'
    		END
    	ELSE IF (@RoleID = 2)
    		BEGIN
    		SET @sSubSql = 'SELECT PM.UserID FROM TT_Projects P INNER JOIN TT_ProjectMembers PM'
    		SET @sSubSql = @sSubSql + ' ON P.ProjectID = PM.ProjectID WHERE P.ManagerUserID = @3 AND PM.UserID IN (' + @UserIDList + ')'

    		SET @sSqlString = 'SELECT Sum(EL.Duration) TotalHours, U.UserID, U.UserName'
    		SET @sSqlString = @sSqlString + ' FROM TT_EntryLog EL Inner Join TT_Users U On EL.UserID = U.UserID WHERE U.UserID IN (' + @sSubSql + ')'
    		SET @sSqlString = @sSqlString + ' AND EL.ProjectID IN (SELECT ProjectID From TT_Projects Where ManagerUserID = @3) '
    		SET @sSqlString = @sSqlString + ' and EL.EntryDate >= @1 and EL.EntryDate <= @2  GROUP BY U.UserID, U.UserName'
    		END
    	ELSE
    		SET @sSqlString = 'SELECT U.UserID AS TotalHours, U.UserID, U.UserName From TT_Users U Where 1=0'

    	EXEC sp_executesql @sSqlString, N'@1 datetime, @2 datetime, @3 int', @StartDate, @EndDate, @ManagerUserID

用到的数据库表：

EntryLog 表的主键为EntryLogID.

Users 表的主键为UserID