createtasklistdbsql7.sql

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

-- =============================================
-- TaskList_GetUserTaskLists
-- =============================================
-- Retrieves a list of task lists that this user belongs to
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'TaskList_GetUserTaskLists' 
	   AND 	  type = 'P')
    DROP PROCEDURE TaskList_GetUserTaskLists
GO

CREATE PROCEDURE TaskList_GetUserTaskLists
	@Username VarChar(20),
	@PasswordHash VarChar(50),
	@UserToLookUpID int
AS
DECLARE @CurrentUserID int, @IsAdministrator Bit
SET @CurrentUserID = NULL

--Check to make sure this is a valid user, and that they are looking up their own 
--properties, or this user has Administrator priviledges
SELECT @CurrentUserID=ID, @IsAdministrator = IsAdministrator
FROM TaskListUsers 
WHERE Username = @Username AND PasswordHash = @PasswordHash;

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

	SELECT TaskListNames.ID AS TaskListID, 
				 TaskListNames.[Name] AS TaskListName
	FROM TaskListUsers
	LEFT JOIN TaskListAssignments ON TaskListAssignments.UserID = TaskListUsers.ID
	LEFT JOIN TaskListNames ON TaskListNames.ID = TaskListAssignments.TaskListID
	WHERE TaskListUsers.ID = @UserToLookUpID AND TaskListNames.ID IS NOT NULL AND TaskListNames.Name IS NOT NULL
  ORDER BY TaskListNames.[Name] ASC
	
	

GO

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

CREATE PROCEDURE TaskList_LogUserInFromCookie
	@Username VarChar(20)
AS
DECLARE @CurrentUserID int
SET @CurrentUserID = NULL

	SELECT @CurrentUserID=ID
	FROM TaskListUsers 
	WHERE Username = @Username

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

GO

-- =============================================
-- TaskList_ModifyUser
-- =============================================
-- Allows an administrator user to change a user's properties
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'TaskList_ModifyUser' 
	   AND 	  type = 'P')
    DROP PROCEDURE TaskList_ModifyUser
GO

CREATE PROCEDURE TaskList_ModifyUser
	@Username VarChar(20),
	@PasswordHash VarChar(50),
	@OldUserName VarChar(20),
	@NewUserName VarChar(20),
	@OldPasswordHash VarChar(50),
	@NewPasswordHash VarChar(50),
  @IsManager Bit,
  @IsAdministrator Bit
AS
DECLARE @CurrentUserID int
SET @CurrentUserID = NULL

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

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

UPDATE TaskListUsers SET 
  Username = @NewUserName, 
  PasswordHash = @NewPasswordHash,
  IsAdministrator = @IsAdministrator,
  IsManager = @IsManager
WHERE Username = @OldUserName AND PasswordHash = @OldPasswordHash;

IF @@Error <> 0 Return 1;
IF @@RowCount <> 0 Return 1;

GO

-- =============================================
-- TaskList_RemoveUserFromTaskList
-- =============================================
-- Removes a user from the list of authorized users for the task list ID specified
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'TaskList_RemoveUserFromTaskList' 
	   AND 	  type = 'P')
    DROP PROCEDURE TaskList_RemoveUserFromTaskList
GO

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

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

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

GO

-- =============================================
-- TaskList_RenameTaskList
-- =============================================
-- Renames an existing task list
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'TaskList_RenameTaskList' 
	   AND 	  type = 'P')
    DROP PROCEDURE TaskList_RenameTaskList
GO

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

--Make sure that 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

UPDATE TaskListNames SET [Name] = @NewName WHERE [ID] = @TaskListID;
IF @@Error <> 0 Return 1;

GO

-- =============================================
-- TaskList_SetNumberOfItemsToDisplayPerPage
-- =============================================
-- 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_SetNumberOfItemsToDisplayPerPage' 
	   AND 	  type = 'P')
    DROP PROCEDURE TaskList_SetNumberOfItemsToDisplayPerPage
GO

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

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

UPDATE TaskListUsers SET ItemsToDisplay = @NumberOfItems WHERE ID = @CurrentUserID;

GO

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

CREATE PROCEDURE TaskList_UpdateCategory
	@CategoryID int,
  @Description VarChar(25),
  @IconUrl VarChar(500)
AS

UPDATE TaskListCategories SET
  Description = @Description,
  IconUrl = @IconUrl
WHERE ID = @CategoryID;

GO

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

CREATE PROCEDURE TaskList_UpdateNotificationEmail
	@Username VarChar(20),
  @PasswordHash VarChar(50),
  @NotificationEmail VarChar(5000)
AS
DECLARE @UserID int

SELECT @UserID = ID FROM TaskListUsers WHERE
  Username = @Username AND PasswordHash = @PasswordHash AND IsAdministrator = 1

IF @UserID > 0
BEGIN
  DELETE FROM TaskListNotification;
  INSERT INTO TaskListNotification
  (NotificationEmail)
  VALUES
  (@NotificationEmail)
  END
ELSE
  Return(-1);
GO

-- =============================================
-- TaskList_UpdateSortOrderPreferences
-- =============================================
-- Changes sort order preferences for a user
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'TaskList_UpdateSortOrderPreferences' 
	   AND 	  type = 'P')
    DROP PROCEDURE TaskList_UpdateSortOrderPreferences
GO

CREATE PROCEDURE TaskList_UpdateSortOrderPreferences
 	@Username VarChar(50), 
  @PasswordHash VarChar(50),
  @StatusID int, 
  @SortOrder Int
AS
DECLARE @CurrentUserID int
SET @CurrentUserID = null

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

IF (@CurrentUserID IS NULL) Return 1;

--Okay now update their sort order
UPDATE TaskListSortOrderPreferences SET SortOrder = @SortOrder
WHERE UserID = @CurrentUserID AND StatusID = @StatusID

GO

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

CREATE PROCEDURE TaskList_UpdateStatusType
  @StatusID int,
	@Description VarChar(25),
  @StatusFontFlags VarChar(500)
AS

UPDATE TaskListStatus SET
  StatusDescription = @Description,
  StatusFontFlags = @StatusFontFlags
WHERE ID = @StatusID;

GO

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

CREATE PROCEDURE TaskList_UpdateUserNotification
	@Username VarChar(20),
  @PasswordHash VarChar(50),
  @ShouldNotify bit,
  @NotifyPeriod int
AS

UPDATE TaskListUsers SET ShouldNotify = @ShouldNotify, NotifyPeriod = @NotifyPeriod, LastNotified = GetDate()
WHERE Username = @Username AND PasswordHash = @PasswordHash;

GO

 CREATE  UNIQUE  CLUSTERED  INDEX [IX_TaskListAssignments] ON [dbo].[TaskListAssignments]([ID]) ON [PRIMARY]
GO

 CREATE  UNIQUE  CLUSTERED  INDEX [IX_TaskListItems] ON [dbo].[TaskListItems]([ID]) ON [PRIMARY]
GO

 CREATE  UNIQUE  CLUSTERED  INDEX [IX_TaskListNames] ON [dbo].[TaskListNames]([ID]) ON [PRIMARY]
GO

 CREATE  UNIQUE  CLUSTERED  INDEX [IX_TaskListUsers] ON [dbo].[TaskListUsers]([ID]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TaskListAssignments] ADD 
	CONSTRAINT [FK_TaskListAssignments_TaskListNames] FOREIGN KEY 
	(
		[TaskListID]
	) REFERENCES [dbo].[TaskListNames] (
		[ID]
	),
	CONSTRAINT [FK_TaskListAssignments_TaskListUsers] FOREIGN KEY 
	(
		[UserID]
	) REFERENCES [dbo].[TaskListUsers] (
		[ID]
	)
GO

ALTER TABLE [dbo].[TaskListItems] ADD 
	CONSTRAINT [FK_TaskListItems_TaskListNames] FOREIGN KEY 
	(
		[TaskListID]
	) REFERENCES [dbo].[TaskListNames] (
		[ID]
	)
GO

go

--Now update the TaskListStatus table with our default status values
INSERT INTO TaskListStatus (StatusDescription, StatusValue, StatusFontFlags, DefaultSortOrder)
VALUES ('In Progress', 3, '<Font Style="font=style: italic; color: Green">', 0)
GO
INSERT INTO TaskListStatus (StatusDescription, StatusValue, StatusFontFlags, DefaultSortOrder)
VALUES ('Open', 0, '<Font Style="font-style: italic">', 1)
GO
INSERT INTO TaskListStatus (StatusDescription, StatusValue, StatusFontFlags, DefaultSortOrder)
VALUES ('Indeterminate', 4, '<Font Style="font-style: italic; color: Blue">', 2)
GO
INSERT INTO TaskListStatus (StatusDescription, StatusValue, StatusFontFlags, DefaultSortOrder)
VALUES ('Hold', 2, '<Font Style="font-style: italic; color: Gray">', 3)
GO
INSERT INTO TaskListStatus (StatusDescription, StatusValue, StatusFontFlags, DefaultSortOrder)
VALUES ('Closed', 1, '<Font Style="font-style: italic; text-decoration: line-through">', 4)
GO

--Now create our default Categories
INSERT INTO TaskListCategories (Description, IconUrl)
VALUES ('General', 'images/task.gif')
GO
INSERT INTO TaskListCategories (Description, IconUrl)
VALUES ('Information', 'images/info.gif')
GO

--Now create our Administrator user (creates an administrator user with the password "newpass")
INSERT INTO TaskListUsers (Username, PasswordHash, IsAdministrator, IsManager, StartupViewOption, StartupViewArgument, ItemsToDisplay, ShouldNotify, NotifyPeriod, LastNotified)
VALUES ('administrator', 'E6053EB8D35E02AE40BEEEACEF203C1A', 1, 1, 'LastTaskList',2,'All',0,24,GetDate())

DECLARE @AdministratorUserID int
SET @AdministratorUserID = @@Identity;

--Now create our default task list ("General Task List")
EXEC TaskList_CreateTaskList 'administrator', 'E6053EB8D35E02AE40BEEEACEF203C1A', 'General Task List';

--Now assign administrator to that task list
INSERT INTO TaskListAssignments (UserID, TaskListID)
(SELECT Min(TaskListUsers.ID) AS UserID, 
				Min(TaskListNames.ID) AS TaskListID 
 FROM TaskListUsers, TaskListNames);

--Now set up the administrator user's filter and sort order
INSERT INTO TaskListSortOrderPreferences (UserID, StatusID, SortOrder)
SELECT @AdministratorUserID, TaskListStatus.ID, TaskListStatus.DefaultSortOrder 
FROM TaskListStatus;

INSERT INTO TaskListStatusShowPreferences (UserID, StatusID)
SELECT UserID, StatusID 
FROM TaskListSortOrderPreferences 
WHERE UserID = @AdministratorUserID;
GO

use master

GO