createtasklistdbsql7.sql

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

SELECT ID AS StatusID,
       StatusDescription AS Description,
			 StatusValue AS Value,
       StatusFontFlags AS FontFlags
FROM TaskListStatus
ORDER BY StatusValue ASC

GO

-- =============================================
-- TaskList_GetStatusTypeByID
-- =============================================
-- creating the store procedure
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'TaskList_GetStatusTypeByID' 
	   AND 	  type = 'P')
    DROP PROCEDURE TaskList_GetStatusTypeByID
GO

CREATE PROCEDURE TaskList_GetStatusTypeByID
	@StatusID int
AS
SELECT ID AS StatusID, StatusDescription AS Description, StatusFontFlags AS FontFlags
FROM TaskListStatus WHERE ID = @StatusID

GO

-- =============================================
-- TaskList_GetTaskList
-- =============================================
-- Retrieves a user's task list
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'TaskList_GetTaskList' 
	   AND 	  type = 'P')
    DROP PROCEDURE TaskList_GetTaskList
GO

CREATE PROCEDURE TaskList_GetTaskList
	@Username VarChar(50),
	@PasswordHash VarChar(50),
	@TaskListID int
AS

DECLARE @CurrentUserID int, @StartupViewOption VarChar(20)
SET @CurrentUserID = NULL

--Check to make sure this is a valid user, and that they have access to this task list
SELECT @CurrentUserID=TaskListUsers.ID, @StartupViewOption = StartupViewOption
FROM TaskListUsers
LEFT JOIN TaskListAssignments ON TaskListAssignments.UserID = TaskListUsers.ID
	AND TaskListAssignments.TaskListID = @TaskListID
WHERE Username = @Username AND PasswordHash = @PasswordHash AND TaskListAssignments.ID IS NOT NULL;

IF (@CurrentUserID IS NULL) Return 1;

--Now, record this task list as the last task list viewed
IF (@StartupViewOption = 'LastTaskList')
  EXEC TaskList_ChangeStartupTaskListID @Username, @PasswordHash, @TaskListID

--Finally, grab the task list
SELECT TaskListItems.ID, 
       TaskListID, 
       OwnerUserID,
       CreatedDate, 
       ModifiedDate as LastModifiedDate,
       TaskListUsers.Username AS LastModifiedBy, 
       Subject,
       Body,
       TaskListStatus.StatusDescription AS Status,
       TaskListCategories.ID AS CategoryID,
       TaskListCategories.Description AS CategoryDescription,
       TaskListCategories.IconUrl AS IconUrl,
       TaskListSortOrderPreferences.SortOrder AS SortOrder
FROM TaskListItems
LEFT JOIN TaskListStatus ON TaskListStatus.StatusValue = TaskListItems.Status
LEFT JOIN TaskListSortOrderPreferences ON TaskListSortOrderPreferences.UserID = @CurrentUserID 
  AND TaskListSortOrderPreferences.StatusID = TaskListStatus.ID
LEFT JOIN TaskListCategories ON TaskListCategories.ID = TaskListItems.CategoryID
LEFT JOIN TaskListUsers ON TaskListUsers.ID = TaskListItems.OwnerUserID
WHERE TaskListID = @TaskListID AND TaskListStatus.ID IN
(
  SELECT StatusID FROM TaskListStatusShowPreferences WHERE UserID = @CurrentUserID
)
ORDER BY TaskListSortOrderPreferences.SortOrder ASC, ModifiedDate DESC
IF @@Error <> 0 Return 1;

GO

-- =============================================
-- TaskList_GetTaskListItem
-- =============================================
-- Retrieves a task list item from the database
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'TaskList_GetTaskListItem' 
	   AND 	  type = 'P')
    DROP PROCEDURE TaskList_GetTaskListItem
GO

CREATE PROCEDURE TaskList_GetTaskListItem
	@Username VarChar(20),
	@PasswordHash VarChar(50),
	@TaskID int,
	@TaskListID int
AS
DECLARE @CurrentUserID int
SET @CurrentUserID = NULL

--Make sure that this is a valid user, and that they have access to this task
SELECT @CurrentUserID=TaskListUsers.ID
FROM TaskListUsers 
LEFT JOIN TaskListAssignments AS Ta ON Ta.UserID = TaskListUsers.ID AND Ta.TaskListID = @TaskListID
WHERE Username = @Username AND PasswordHash = @PasswordHash AND Ta.ID IS NOT NULL;

IF (@CurrentUserID IS NULL)	RETURN 1; --Invalid user

SELECT TaskListItems.ID AS TaskID,
	OwnerUserID AS UserID,
	TaskListUsers.Username AS Username,
	CreatedDate AS CreatedOn,
	ModifiedDate AS ModifiedOn,
	Subject AS TaskSubject,
	Body AS TaskBody,
	Status AS TaskStatusValue,
	StatusDescription AS TaskStatusName,
  CategoryID AS CategoryID,
	TaskListID AS TaskListID
FROM TaskListItems
LEFT JOIN TaskListUsers ON TaskListUsers.ID = TaskListItems.OwnerUserID
LEFT JOIN TaskListStatus ON TaskListStatus.StatusValue = TaskListItems.Status
WHERE TaskListItems.ID = @TaskID
	
GO

-- =============================================
-- TaskList_GetTaskListList
-- =============================================
-- Retrieves a list task lists from the database
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'TaskList_GetTaskListList' 
	   AND 	  type = 'P')
    DROP PROCEDURE TaskList_GetTaskListList
GO

CREATE PROCEDURE TaskList_GetTaskListList
	@Username VarChar(20),
	@PasswordHash VarChar(50)
AS
DECLARE @CurrentUserID int
SET @CurrentUserID = NULL

--Make sure that this is a valid user, and that they have manager priviledges
SELECT @CurrentUserID=ID 
FROM TaskListUsers 
WHERE Username = @Username AND PasswordHash = @PasswordHash AND IsManager = 1;

IF (@CurrentUserID IS NULL) RETURN 1; --Invalid user

--Now get the list of task lists
SELECT ID as TaskListID, Name as TaskListName FROM TaskListNames ORDER BY TaskListNames.[Name]

GO

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

CREATE PROCEDURE TaskList_GetTaskListProperties
	@Username VarChar(20),
	@PasswordHash VarChar(50),
	@TaskListID int
AS
DECLARE @CurrentUserID int
SET @CurrentUserID = NULL

--Make sure that this is a valid user, and that this user has manager priviledges
SELECT @CurrentUserID=ID
FROM TaskListUsers 
WHERE Username = @Username AND PasswordHash = @PasswordHash AND IsManager = 1;

IF (@CurrentUserID IS NULL) RETURN 1; --Invalid user

SELECT Count(ID) AS TaskListItems,
			 Max(ModifiedDate) AS LastModified,
			 Max(CreatedDate) AS LastCreated
FROM TaskListItems WHERE TaskListID = @TaskListID

GO

-- =============================================
-- TaskList_LogUserIn
-- =============================================
-- Returns a user id if this is a valid user
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'TaskList_LogUserIn' 
	   AND 	  type = 'P')
    DROP PROCEDURE TaskList_LogUserIn
GO

CREATE PROCEDURE TaskList_LogUserIn
	@Username VarChar(20),
	@PasswordHash VarChar(50)
AS
DECLARE @CurrentUserID int
SET @CurrentUserID = NULL

	SELECT @CurrentUserID=ID
	FROM TaskListUsers 
	WHERE Username = @Username AND PasswordHash = @PasswordHash;

	IF (@CurrentUserID IS NULL)
		RETURN 1; --Invalid user
	ELSE
		SELECT @CurrentUserID

GO

-- =============================================
-- TaskList_GetUserInformation
-- =============================================
-- Retrieves a user object from the database
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'TaskList_GetUserInformation' 
	   AND 	  type = 'P')
    DROP PROCEDURE TaskList_GetUserInformation
GO

CREATE PROCEDURE TaskList_GetUserInformation
	@Username VarChar(20),
	@UserID int
AS
DECLARE @CurrentUserID int, @IsAdministrator Bit
SET @CurrentUserID = NULL

--Make sure that CurrentUserID = UserID OR this is an Administrator user.
SELECT @CurrentUserID = ID, @IsAdministrator = IsAdministrator
FROM TaskListUsers 
WHERE Username = @Username

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

SELECT ID AS UserID,
			 Username AS Username,
			 PasswordHash AS PasswordHash,
       IsManager AS IsManager,
       IsAdministrator AS IsAdministrator,
       StartupViewOption AS StartupViewOption,
       StartupViewArgument AS StartupViewArgument,
       ShouldNotify,
       NotifyPeriod,
       LastNotified
FROM TaskListUsers
WHERE ID = @UserID

GO


-- =============================================
-- TaskList_GetUserList
-- =============================================
-- Retrieves a list of users from the database
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'TaskList_GetUserList' 
	   AND 	  type = 'P')
    DROP PROCEDURE TaskList_GetUserList
GO

CREATE PROCEDURE TaskList_GetUserList
	@Username VarChar(20),
	@PasswordHash VarChar(50)
AS
DECLARE @CurrentUserID int
SET @CurrentUserID = NULL

--Make sure this is a valid user, and that they have administrator priviledges
SELECT @CurrentUserID=ID
FROM TaskListUsers 
WHERE Username = @Username AND PasswordHash = @PasswordHash AND IsAdministrator = 1;

IF (@CurrentUserID IS NULL) RETURN 1; --Invalid user

--Now retrieve the user list from the database
SELECT ID as UserID, Username FROM TaskListUsers;

GO

-- =============================================
-- 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 int
AS
DECLARE @CurrentUserID int, @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 

GO

-- =============================================
-- TaskList_GetUsersAssignedToTaskList
-- =============================================
-- Retrieves a list of users who are assigned to a specific task list
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'TaskList_GetUsersAssignedToTaskList' 
	   AND 	  type = 'P')
    DROP PROCEDURE TaskList_GetUsersAssignedToTaskList
GO

CREATE PROCEDURE TaskList_GetUsersAssignedToTaskList
	@Username VarChar(20),
	@PasswordHash VarChar(50),
	@TaskListID int
AS
DECLARE @CurrentUserID int
SET @CurrentUserID = NULL

--Make sure this is a valid user and that they have manager priviledges
SELECT @CurrentUserID=ID
FROM TaskListUsers 
WHERE Username = @Username AND PasswordHash = @PasswordHash AND IsManager = 1;

IF (@CurrentUserID IS NULL) RETURN 1; --Invalid user

SELECT 	TaskListUsers.ID as UserID,
				TaskListUsers.Username as Username
FROM TaskListAssignments
LEFT JOIN TaskListUsers ON TaskListUsers.ID = TaskListAssignments.UserID
WHERE TaskListAssignments.TaskListID = @TaskListID

GO

-- =============================================
-- TaskList_GetUserSortOrderPreferences
-- =============================================
-- Gets the sort order preferences for this user
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'TaskList_GetUserSortOrderPreferences' 
	   AND 	  type = 'P')
    DROP PROCEDURE TaskList_GetUserSortOrderPreferences
GO

CREATE PROCEDURE TaskList_GetUserSortOrderPreferences
	@Username VarChar(20),
  @PasswordHash VarChar(50)
AS
DECLARE @CurrentUserID int
SET @CurrentUserID = null

SELECT @CurrentUserID = ID FROM TaskListUsers WHERE Username = @Username AND PasswordHash = @PasswordHash
IF (@CurrentUserID IS NULL) Return 1;

SELECT  TaskListSortOrderPreferences.StatusID, 
        TaskListSortOrderPreferences.SortOrder,
        TaskListStatus.StatusDescription
FROM TaskListSortOrderPreferences
LEFT JOIN TaskListStatus ON TaskListStatus.ID = TaskListSortOrderPreferences.StatusID
WHERE UserID = @CurrentUserID
ORDER BY TaskListSortOrderPreferences.SortOrder ASC

GO

-- =============================================
-- TaskList_GetUserStatusShowPreferences
-- =============================================
-- Retrieves the status show preferences for this user (what status id's to show on
-- the user's task list)
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'TaskList_GetUserStatusShowPreferences' 
	   AND 	  type = 'P')
    DROP PROCEDURE TaskList_GetUserStatusShowPreferences
GO

CREATE PROCEDURE TaskList_GetUserStatusShowPreferences
	@Username VarChar(20),
  @PasswordHash VarChar(50)
AS
DECLARE @CurrentUserID int
SET @CurrentUserID = null

SELECT @CurrentUserID = ID FROM TaskListUsers WHERE Username = @Username AND PasswordHash = @PasswordHash
IF (@CurrentUserID IS NULL) Return 1;

SELECT StatusID, TaskListStatus.StatusDescription 
FROM TaskListStatusShowPreferences
LEFT JOIN TaskListStatus ON TaskListStatus.ID = TaskListStatusShowPreferences.StatusID
WHERE UserID = @CurrentUserID

GO