tasklist_getuserproperties.sql

该项目管理系统可对项目的过程进行管理和控制

-- =============================================
-- TaskList_GetUserProperties
-- =============================================
-- Retrieves statistics about a given user
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'TaskList_GetUserProperties' 
	   AND 	  type = 'P')
    DROP PROCEDURE TaskList_GetUserProperties
GO

CREATE PROCEDURE TaskList_GetUserProperties
	@Username VarChar(20),
	@PasswordHash VarChar(50),
	@UserID BigInt
AS
DECLARE @CurrentUserID BigInt, @IsAdministrator Bit
SET @CurrentUserID = NULL

--Make sure that this is a valid user, and that UserID = CurrentUserID OR CurrentUser 
--is an administrator
SELECT @CurrentUserID=ID, @IsAdministrator = IsAdministrator
FROM TaskListUsers 
WHERE Username = @Username AND PasswordHash = @PasswordHash;

IF (@CurrentUserID IS NULL) RETURN 1; --Invalid user
IF (@CurrentUserID != @UserID AND @IsAdministrator = 0) Return 1; --Invalid user

SELECT Count(TaskListItems.ID) AS TaskListItems,
			 Max(ModifiedDate) AS LastModified,
			 Max(CreatedDate) AS LastCreated
FROM TaskListItems 
LEFT JOIN TaskListAssignments ON TaskListAssignments.TaskListID = TaskListItems.TaskListID 
			AND TaskListAssignments.UserID = @UserID
WHERE TaskListAssignments.UserID IS NOT NULL