createtasklistdbsql7.sql

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

CREATE PROCEDURE TaskList_ClearStatusShowPreferences
	@Username VarChar(50),
  @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;

-- Clear out the status show preferences table.  This will make it so that effectively,
-- tasks of ALL status types will be filtered OUT.  Call TaskList_AddStatusShowPreference to add
-- entries to show task types.
DELETE FROM TaskListStatusShowPreferences WHERE UserID = @CurrentUserID

GO

-- =============================================
-- TaskList_CreateTaskList
-- =============================================
-- Creates a new task list
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'TaskList_CreateTaskList' 
	   AND 	  type = 'P')
    DROP PROCEDURE TaskList_CreateTaskList
GO

CREATE PROCEDURE TaskList_CreateTaskList
	@Username VarChar(20),
	@PasswordHash VarChar(50),
	@TaskListName VarChar(255)
AS
DECLARE @CurrentUserID int, @Exists int
SET @CurrentUserID = NULL

--Check to make sure this user 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

--Check to make sure a task list doesn't already exist with this name
SELECT @Exists = Count(ID) FROM TaskListNames WHERE [Name] = @TaskListName
IF (@Exists > 0) Return 1;

--Finally, add the task list
INSERT INTO TaskListNames (Name) VALUES (@TaskListName)
IF @@Error <> 0 Return 1;

GO

-- =============================================
-- TaskList_Delete
-- =============================================
-- Deletes a task list item
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'TaskList_Delete' 
	   AND 	  type = 'P')
    DROP PROCEDURE TaskList_Delete
GO

CREATE PROCEDURE TaskList_Delete
	@UserName VarChar(20),
	@PasswordHash VarChar(50),
	@TaskListID int,
	@ID int
AS
DECLARE @CurrentUserID int
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
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; --Invalid user

DELETE FROM TaskListItems WHERE ID = @ID AND TaskListID = @TaskListID;
IF @@Error <> 0 RETURN 1;

GO

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

CREATE PROCEDURE TaskList_DeleteCategory
	@CategoryID int
AS

DELETE FROM TaskListCategories WHERE ID = @CategoryID;

GO

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

CREATE PROCEDURE TaskList_DeleteStatusType
	@StatusID int
AS
DELETE FROM TaskListStatus WHERE ID = @StatusID;

GO

-- =============================================
-- TaskList_DeleteTaskList
-- =============================================
-- Deletes a task list
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'TaskList_DeleteTaskList' 
	   AND 	  type = 'P')
    DROP PROCEDURE TaskList_DeleteTaskList
GO

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

--Check to 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

BEGIN TRANSACTION DeleteTaskList;

	DELETE FROM TaskListItems WHERE TaskListID = @TaskListID
	IF @@Error <> 0 BEGIN Rollback Transaction DeleteTaskList; END

	DELETE FROM TaskListAssignments WHERE TaskListID = @TaskListID
	IF @@Error <> 0 BEGIN Rollback Transaction DeleteTaskList; END

	DELETE FROM TaskListNames WHERE ID = @TaskListID
	IF @@Error <> 0 BEGIN Rollback Transaction DeleteTaskList; END

COMMIT TRANSACTION DeleteTaskList;

GO

-- =============================================
-- TaskList_DeleteUser
-- =============================================
-- Deletes a user
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'TaskList_DeleteUser' 
	   AND 	  type = 'P')
    DROP PROCEDURE TaskList_DeleteUser
GO

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

--Check to make sure that 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

BEGIN TRANSACTION DeleteUser;

	DELETE FROM TaskListAssignments WHERE UserID = @UserIDToDelete;
	IF @@Error <> 0 BEGIN Rollback Transaction DeleteUser; Return 1; END

	DELETE FROM TaskListSortOrderPreferences WHERE UserID = @UserIDToDelete;
	IF @@Error <> 0 BEGIN Rollback Transaction DeleteUser; Return 1; END

	DELETE FROM TaskListStatusShowPreferences WHERE UserID = @UserIDToDelete;
	IF @@Error <> 0 BEGIN Rollback Transaction DeleteUser; Return 1; END

	DELETE FROM TaskListUsers WHERE ID = @UserIDToDelete;
	IF @@Error <> 0 BEGIN Rollback Transaction DeleteUser; Return 1; END

COMMIT TRANSACTION DeleteUser;

GO

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

CREATE PROCEDURE Tasklist_GetCategoryByID
	@CategoryID int
AS

SELECT ID AS CategoryID, Description AS Description, IconUrl AS IconUrl
FROM TaskListCategories WHERE ID = @CategoryID;

GO

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

CREATE PROCEDURE TaskList_GetCategoryList
	@Username VarChar(25),
  @PasswordHash VarChar(50)
AS

DECLARE @CurrentUserID int
SET @CurrentUserID = NULL
--Check to see if this is a valid user
SELECT @CurrentUserID = ID FROM TaskListUsers WHERE Username = @Username AND PasswordHash = @PasswordHash
IF (@CurrentUserID IS NULL) Return 1;

SELECT ID AS CategoryID,
       Description AS Description,
			 IconUrl AS IconUrl
FROM TaskListCategories
ORDER BY CategoryID ASC

GO

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

CREATE PROCEDURE TaskList_GetChangedTasksFromTaskList
	@Username VarChar(20),
  @PasswordHash VarChar(50),
  @TaskListID int
AS
DECLARE @UserID int
DECLARE @LastNotified DateTime

SELECT @UserID = ID, @LastNotified = LastNotified FROM TaskListUsers
WHERE Username = @Username AND PasswordHash = @PasswordHash

-- Invalid User
IF @UserID IS NULL OR @UserID = 0
  Return(-1);

SELECT @UserID = UserID FROM TaskListAssignments WHERE
UserID = @UserID AND TaskListID = @TaskListID

-- User doesn't have access to this task list
IF @UserID IS NULL OR @UserID = 0
  Return(-2);

SELECT Username, Subject, ModifiedDate 
FROM TaskListItems
LEFT JOIN TaskListUsers ON TaskListUsers.ID = TaskListItems.OwnerUserID
WHERE TaskListID = @TaskListID AND ModifiedDate > @LastNotified;

GO

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

CREATE PROCEDURE TaskList_GetNotificationEmail
AS

SELECT * FROM TaskListNotification

GO

-- =============================================
-- TaskList_GetNumberOfItemsToDisplayPerPage
-- =============================================
-- Retrieves the number of task list items to display per page on the task list 
-- for the user specified
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'TaskList_GetNumberOfItemsToDisplayPerPage' 
	   AND 	  type = 'P')
    DROP PROCEDURE TaskList_GetNumberOfItemsToDisplayPerPage
GO

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

SELECT @CurrentUserID = ID, @NumberOfItems = ItemsToDisplay 
FROM TaskListUsers 
WHERE Username = @Username AND PasswordHash = @PasswordHash

IF (@CurrentUserID IS NULL) Return 1;

IF (@NumberOfItems IS NULL) 
  SELECT 'All' AS NumberOfItems;
ELSE
  SELECT @NumberOfItems AS NumberOfItems;

GO

-- =============================================
-- TaskList_GetStartupTaskListID
-- =============================================
-- Retrieves the TaskListID of the task list this user should first see when logging in.
-- If none exists, then it shows the alphabetical first task list.
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'TaskList_GetStartupTaskListID' 
	   AND 	  type = 'P')
    DROP PROCEDURE TaskList_GetStartupTaskListID
GO

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

SELECT @CurrentUserID = ID, @TaskListID = StartupViewArgument
FROM TaskListUsers WHERE Username = @Username AND PasswordHash = @PasswordHash

IF (@CurrentUserID IS NULL) Return 1;

SELECT @TaskListID = ID FROM TaskListNames WHERE ID = @TaskListID
IF (@TaskListID IS NULL) --This task list no longer exists
BEGIN
  SELECT Max(TaskListNames.ID) AS TaskListID 
  FROM TaskListNames 
  LEFT JOIN TaskListAssignments ON TaskListAssignments.TaskListID = TaskListNames.ID
  WHERE TaskListAssignments.UserID = @CurrentUserID
END
ELSE
BEGIN
  SELECT @TaskListID AS TaskListID
END

GO

-- =============================================
-- TaskList_GetStartupViewOption
-- =============================================
-- Retrieves the StartupViewOption from the Users table
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'TaskList_GetStartupViewOption' 
	   AND 	  type = 'P')
    DROP PROCEDURE TaskList_GetStartupViewOption
GO

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

SELECT @CurrentUserID = ID, @StartupViewOption = StartupViewOption
FROM TaskListUsers WHERE Username = @Username AND PasswordHash = @PasswordHash
IF (@CurrentUserID IS NULL) Return 1;
IF (@StartupViewOption IS NULL) 
  SELECT 'LastTaskList' AS StartupViewOption
ELSE
  SELECT @StartupViewOption AS StartupViewOption

GO

-- =============================================
-- TaskList_GetStatusList
-- =============================================
-- Retrieves a list of status items
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'TaskList_GetStatusList' 
	   AND 	  type = 'P')
    DROP PROCEDURE TaskList_GetStatusList
GO

CREATE PROCEDURE TaskList_GetStatusList
	@Username VarChar(20),
	@PasswordHash VarChar(50)
AS
DECLARE @CurrentUserID int
SET @CurrentUserID = NULL
--Check to see if this is a valid user
SELECT @CurrentUserID = ID FROM TaskListUsers WHERE Username = @Username AND PasswordHash = @PasswordHash
IF (@CurrentUserID IS NULL) Return 1;