createtasklistdbsql7.sql

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

CREATE DATABASE TaskList
GO

USE TaskList
GO

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TaskListNames]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
 BEGIN
CREATE TABLE [dbo].[TaskListNames] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[Name] [varchar] (255) NOT NULL 
)
END

GO

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TaskListUsers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
 BEGIN
CREATE TABLE [dbo].[TaskListUsers] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[Username] [varchar] (50) NOT NULL CONSTRAINT [DF_TaskListUsers_Username] DEFAULT (''),
	[PasswordHash] [varchar] (50) NOT NULL CONSTRAINT [DF_TaskListUsers_PasswordHash] DEFAULT (''),
	[IsManager] [bit] NOT NULL CONSTRAINT [DF_TaskListUsers_IsManager] DEFAULT (0),
	[IsAdministrator] [bit] NOT NULL CONSTRAINT [DF_TaskListUsers_IsAdministrator] DEFAULT (0),
	[StartupViewOption] [varchar] (20) NOT NULL CONSTRAINT [DF_TaskListUsers_StartupViewOption] DEFAULT ('LastTaskList'),
	[StartupViewArgument] [int] NULL ,
	[ItemsToDisplay] [varchar] (20) NOT NULL CONSTRAINT [DF_TaskListUsers_ItemsToDisplay] DEFAULT ('All'),
	[ShouldNotify] [bit] NOT NULL CONSTRAINT [DF_TaskListUsers_ShouldNotify] DEFAULT (0),
	[NotifyPeriod] [int] NOT NULL CONSTRAINT [DF_TaskListUsers_NotifyPeriod] DEFAULT (24),
	[LastNotified] [datetime] NOT NULL CONSTRAINT [DF__tasklistu__LastN__095F58DF] DEFAULT (getdate())
)
END

GO

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TaskListItems]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
 BEGIN
CREATE TABLE [dbo].[TaskListItems] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[TaskListID] [int] NOT NULL ,
	[OwnerUserID] [int] NOT NULL ,
	[CreatedDate] [datetime] NOT NULL ,
	[ModifiedDate] [datetime] NOT NULL ,
	[Subject] [varchar] (255) NULL ,
	[Body] [varchar] (5000) NULL ,
	[Status] [int] NOT NULL CONSTRAINT [DF_TaskListItems_Status] DEFAULT (0),
	[CategoryID] [int] NULL ,
)
END

GO

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TaskListCategories]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
 BEGIN
CREATE TABLE [dbo].[TaskListCategories] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[Description] [varchar] (50) NOT NULL ,
	[IconUrl] [varchar] (500) NULL 
)
END

GO

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TaskListStatusShowPreferences]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
 BEGIN
CREATE TABLE [dbo].[TaskListStatusShowPreferences] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[UserID] [int] NOT NULL ,
	[StatusID] [int] NOT NULL 
)
END

GO

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TaskListAssignments]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
 BEGIN
CREATE TABLE [dbo].[TaskListAssignments] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[UserID] [int] NOT NULL ,
	[TaskListID] [int] NOT NULL ,
)
END

GO

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TaskListStatus]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
 BEGIN
CREATE TABLE [dbo].[TaskListStatus] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[StatusDescription] [varchar] (50) NOT NULL CONSTRAINT [DF_TaskListStatus_StatusDescription] DEFAULT (''),
	[StatusValue] [int] NOT NULL CONSTRAINT [DF_TaskListStatus_StatusValue] DEFAULT (0),
	[StatusFontFlags] [varchar] (500) NOT NULL CONSTRAINT [DF_TaskListStatus_StatusFontFlags] DEFAULT (''),
	[DefaultSortOrder] [int] NOT NULL CONSTRAINT [DF_TaskListStatus_DefaultSortOrder] DEFAULT (0)
)
END

GO

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TaskListSortOrderPreferences]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
 BEGIN
CREATE TABLE [dbo].[TaskListSortOrderPreferences] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[UserID] [int] NOT NULL ,
	[StatusID] [int] NOT NULL ,
	[SortOrder] [int] NOT NULL 
)
END

GO

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TaskListNotification]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
 BEGIN
CREATE TABLE [dbo].[TaskListNotification] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[NotificationEmail] [varchar] (5000) NOT NULL 
)
END

GO



-- =============================================
-- TaskList_Add
-- =============================================
-- First checks to see if the user specified is valid, then
-- adds a Task List Item to the TaskListItems table.
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'TaskList_Add' 
	   AND 	  type = 'P')
    DROP PROCEDURE TaskList_Add
GO

CREATE PROCEDURE TaskList_Add
	@Username VarChar(50),
	@PasswordHash VarChar(50),
	@TaskListID int,
	@Subject VarChar(255),
	@Body	VarChar(5000),
	@CreatedDate DateTime,
	@ModifiedDate DateTime,
	@Status Int,
  @CategoryID 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;

INSERT INTO TaskListItems
	(TaskListID, OwnerUserID, Subject, Body, CreatedDate, ModifiedDate, Status, CategoryID)
VALUES
	(@TaskListID, @CurrentUserID, @Subject, @Body, @CreatedDate, @ModifiedDate, @Status, @CategoryID);

IF @@Error <> 0 RETURN 1;

GO

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

CREATE PROCEDURE Tasklist_AddCategory 
	@Description VarChar(25),
  @IconUrl VarChar(500)
AS

INSERT INTO TaskListCategories(Description, IconUrl) VALUES (@Description, @IconUrl)
RETURN @@Identity;

GO

-- =============================================
-- TaskList_ChangeStartupTaskListID
-- =============================================
-- Sets the default task list to display when the user logs in
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'TaskList_ChangeStartupTaskListID' 
	   AND 	  type = 'P')
    DROP PROCEDURE TaskList_ChangeStartupTaskListID
GO

CREATE PROCEDURE TaskList_ChangeStartupTaskListID
	@Username VarChar(20),
  @PasswordHash VarChar(50),
  @TaskListID 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;

UPDATE TaskListUsers SET StartupViewArgument = @TaskListID WHERE ID = @CurrentUserID

GO

-- =============================================
-- TaskList_AddStatusShowPreference
-- =============================================
-- Adds a status id to the list of ids that are acceptable to show for this user
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'TaskList_AddStatusShowPreference' 
	   AND 	  type = 'P')
    DROP PROCEDURE TaskList_AddStatusShowPreference
GO

CREATE PROCEDURE TaskList_AddStatusShowPreference
	@Username VarChar(50),
  @PasswordHash VarChar(50),
  @StatusID int
AS
DECLARE @CurrentUserID int, @StatusCount int
SET @CurrentUserID = null;
SET @StatusCount = 0;

--Check to make sure this is a valid user
SELECT @CurrentUserID = ID FROM TaskListUsers WHERE Username = @Username AND PasswordHash = @PasswordHash
IF (@CurrentUserID IS NULL) Return 1;

--Check to make sure this status isn't already on there
SELECT @StatusCount = Count(ID) FROM TaskListStatusShowPreferences 
WHERE UserID = @CurrentUserID AND StatusID = @StatusID
IF (@StatusCount > 0) Return 1;

--Insert the status item
INSERT INTO TaskListStatusShowPreferences (UserID, StatusID)
VALUES (@CurrentUserID, @StatusID)

GO

-- =============================================
-- TaskList_AddStatusType
-- =============================================
-- Adds a status type to the database
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'TaskList_AddStatusType' 
	   AND 	  type = 'P')
    DROP PROCEDURE TaskList_AddStatusType
GO

CREATE PROCEDURE TaskList_AddStatusType
	@Description VarChar(25),
  @StatusFontFlags VarChar(500)
AS
DECLARE @SortOrder Integer

Select @SortOrder = Max(DefaultSortOrder) FROM TaskListStatus;
SET @SortOrder = @SortOrder + 1;

INSERT INTO TaskListStatus
(StatusDescription, StatusFontFlags, DefaultSortOrder)
VALUES
(@Description, @StatusFontFlags, @SortOrder)
RETURN @@Identity;

Go

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

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

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

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

Begin Transaction CreateUser;

INSERT INTO TaskListUsers (Username, PasswordHash, IsManager, IsAdministrator) 
VALUES (@NewUserName, @NewPasswordHash, @IsManager, @IsAdministrator);
IF @@Error <> 0 BEGIN Rollback Transaction CreateUser; Return 1; END
SET @NewUserID = @@Identity;

INSERT INTO TaskListSortOrderPreferences (UserID, StatusID, SortOrder)
SELECT @NewUserID, TaskListStatus.ID, TaskListStatus.DefaultSortOrder 
FROM TaskListStatus
IF @@Error <> 0 BEGIN Rollback Transaction CreateUser; Return 1; END

INSERT INTO TaskListStatusShowPreferences (UserID, StatusID)
SELECT UserID, StatusID 
FROM TaskListSortOrderPreferences 
WHERE UserID = @NewUserID  
IF @@Error <> 0 BEGIN Rollback Transaction CreateUser; Return 1; END

Commit Transaction CreateUser;

GO

-- =============================================
-- TaskList_AddUserToTaskList
-- =============================================
-- Adds a user to the TaskListAssignments table, granting them permission to see a task list
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'TaskList_AddUserToTaskList' 
	   AND 	  type = 'P')
    DROP PROCEDURE TaskList_AddUserToTaskList
GO

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

--First, 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;

--Make sure that this user isn't already in the TaskListAssignments table
SELECT @ExistingUserID = UserID FROM TaskListAssignments WHERE UserID = @UserID AND TaskListID = @TaskListID
IF (@ExistingUserID IS NOT NULL) Return 1; --User is already in

--Finally, add the user to the TaskListAssignments table
INSERT INTO TaskListAssignments (UserID, TaskListID) VALUES (@UserID, @TaskListID);
IF @@Error <> 0 Return 1;

GO

-- =============================================
-- TaskList_ChangeStartupTaskListID
-- =============================================
-- Sets the default task list to display when the user logs in
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'TaskList_ChangeStartupTaskListID' 
	   AND 	  type = 'P')
    DROP PROCEDURE TaskList_ChangeStartupTaskListID
GO

CREATE PROCEDURE TaskList_ChangeStartupTaskListID
	@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 access to this task list
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;

UPDATE TaskListUsers SET StartupViewArgument = @TaskListID WHERE ID = @CurrentUserID

GO

-- =============================================
-- TaskList_ChangeStartupViewOption
-- =============================================
-- Changes the method the application uses to decide what
-- task list to show the user when they first log in
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'TaskList_ChangeStartupViewOption' 
	   AND 	  type = 'P')
    DROP PROCEDURE TaskList_ChangeStartupViewOption
GO

CREATE PROCEDURE TaskList_ChangeStartupViewOption
	@Username VarChar(20),
  @PasswordHash VarChar(50),
  @StartupViewOption 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;

IF @StartupViewOption = 'LastTaskList'
  UPDATE TaskListUsers SET StartupViewOption = 'LastTaskList' WHERE ID = @CurrentUserID
ELSE
  UPDATE TaskListUsers SET StartupViewOption = 'SpecificTaskList' WHERE ID = @CurrentUserID

GO

-- =============================================
-- TaskList_ClearStatusShowPreferences
-- =============================================
-- Clears the list of status id's to show for this user
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'TaskList_ClearStatusShowPreferences' 
	   AND 	  type = 'P')
    DROP PROCEDURE TaskList_ClearStatusShowPreferences
GO