sqlserver_taskvision_create_db.sql

TaskVision 应用程序展示了用于个人和公司相关任务管理的一个完整的 n 层应用程序。该示例附带了用于运行某个私有任务服务器应用程序的随时可运行的示范客户端、全部客户端源代码

GRANT  EXECUTE  ON [GetPriorities]  TO [ASPNET];
GO


CREATE PROCEDURE [GetProjectHistory]
(
@ProjectID int
)
AS
SET NOCOUNT ON;
SELECT TaskHistory.TaskHistoryID, TaskHistory.TaskID, TaskHistory.ProjectID, TaskHistory.ModifiedBy, TaskHistory.AssignedTo, TaskHistory.TaskSummary, TaskHistory.TaskDescription, TaskHistory.PriorityID, TaskHistory.StatusID, TaskHistory.Progress, TaskHistory.IsDeleted, TaskHistory.DateDue, TaskHistory.DateModified, TaskHistory.DateCreated, Priorities.PriorityText, Statuses.StatusText, Users.UserFullName As AssignedToText, u.UserFullName As ModifiedByText 
FROM TaskHistory 
JOIN Priorities ON TaskHistory.PriorityID = Priorities.PriorityID 
JOIN Users ON TaskHistory.AssignedTo = Users.UserID 
JOIN Users u ON TaskHistory.ModifiedBy = u.UserID 
JOIN Statuses ON TaskHistory.StatusID = Statuses.StatusID 
WHERE TaskHistory.ProjectID = @ProjectID AND TaskHistory.IsDeleted = 0 ORDER BY DateModified DESC
GO
GRANT  EXECUTE  ON [GetProjectHistory]  TO [ASPNET];
GO


CREATE PROCEDURE [GetProjects]
AS
SET NOCOUNT ON;
SELECT ProjectID, ProjectName, ProjectDescription, DateCreated FROM Projects WHERE (IsDeleted = 0)
GO
GRANT  EXECUTE  ON [GetProjects]  TO [ASPNET];
GO


CREATE PROCEDURE [GetStatuses]
AS
SET NOCOUNT ON;
SELECT StatusID, StatusText FROM Statuses
GO
GRANT  EXECUTE  ON [GetStatuses]  TO [ASPNET];
GO


CREATE PROCEDURE [ResetData]
AS
Declare @Project1 int,
@Project2 int,
@User1 int,
@User2 int

Delete Projects
Delete Tasks
Delete TaskHistory
Delete Users

-- Insert Projects
INSERT INTO Projects VALUES ('Acme', 'This is a test project.', 0, GetDate());
Set @Project1 = @@IDENTITY;
INSERT INTO Projects VALUES ('Microsoft', 'This is a test project.', 0, GetDate());
Set @Project2 = @@IDENTITY;

-- Insert Users
INSERT INTO Users VALUES ('jdoe', 'welcome', 'John Doe', '[e-mail]', 0, 1, GetDate());
Set @User1 = @@IDENTITY;
INSERT INTO Users VALUES ('administrator', 'welcome', 'Admin', '[e-mail]', 0, 1, GetDate());
Set @User2 = @@IDENTITY;

--Insert Tasks
INSERT INTO Tasks VALUES (@Project1, @User1, @User2, 'Application requires testing.', '<Sample Description>', 1, 1, 25, 0, GetDate(), GetDate(), GetDate())
INSERT INTO Tasks VALUES (@Project1, @User1, @User2, 'Photocopier Jammed.', '<Sample Description>', 2, 1, 0, 0, GetDate(), GetDate(), GetDate())
INSERT INTO Tasks VALUES (@Project1, @User2, @User1, 'Website error appearing.', '<Sample Description>', 3, 1, 0, 0, GetDate(), GetDate(), GetDate())
INSERT INTO Tasks VALUES (@Project1, @User2, @User1, 'Computer broken.', '<Sample Description>.', 1, 1, 25, 0, GetDate(), GetDate(), GetDate())
INSERT INTO Tasks VALUES (@Project1, @User2, @User1, 'Consumer complaint.', '<Sample Description>', 2, 1, 100, 0, GetDate(), GetDate(), GetDate())
INSERT INTO Tasks VALUES (@Project1, @User1, @User2, 'Bug found.', '<Sample Description>', 3, 1, 100, 0, GetDate(), GetDate(), GetDate())
GO
GRANT  EXECUTE  ON [ResetData]  TO [ASPNET];
GO


CREATE PROCEDURE [GetTasks] (
@ProjectID int
)
AS
SET NOCOUNT ON;
SELECT Tasks.TaskID, Tasks.ProjectID, Tasks.ModifiedBy, Tasks.AssignedTo, Tasks.TaskSummary, Tasks.TaskDescription, Tasks.PriorityID, Tasks.StatusID, Tasks.Progress, Tasks.IsDeleted, Tasks.DateDue, Tasks.DateModified, Tasks.DateCreated, Priorities.PriorityText, Statuses.StatusText, Users.UserFullName As AssignedToText, u.UserFullName As ModifiedByText
FROM Tasks 
JOIN Priorities ON Tasks.PriorityID = Priorities.PriorityID 
JOIN Users ON Tasks.AssignedTo = Users.UserID 
JOIN Users u ON Tasks.ModifiedBy = u.UserID 
JOIN Statuses ON Tasks.StatusID = Statuses.StatusID
WHERE Tasks.ProjectID = @ProjectID And Tasks.IsDeleted = 0
GO
GRANT  EXECUTE  ON [GetTasks]  TO [ASPNET];
GO


CREATE PROCEDURE [GetUserInfo]
(
@UserID int
)
AS
SELECT  UserID, UserName, UserFullName, UserEmail, IsAdministrator, IsAccountLocked
FROM    Users
WHERE   (UserID = @UserID)
GO
GRANT  EXECUTE  ON [GetUserInfo]  TO [ASPNET];
GO


CREATE PROCEDURE [GetUsers]
AS
SET NOCOUNT ON;
SELECT UserID, UserName, UserFullName, UserEmail, IsAdministrator, IsAccountLocked FROM Users
GO
GRANT  EXECUTE  ON [GetUsers]  TO [ASPNET];
GO


CREATE PROCEDURE [InsertProject]
(
	@ProjectName varchar(20),
	@ProjectDescription varchar(100)
)
AS
SET NOCOUNT OFF;
INSERT INTO Projects (ProjectName, ProjectDescription, DateCreated) VALUES (@ProjectName, @ProjectDescription, getdate());
SELECT @@IDENTITY As ProjectID
GO
GRANT  EXECUTE  ON [InsertProject]  TO [ASPNET];
GO


CREATE PROCEDURE [InsertTask]
(
@ProjectID int,
@ModifiedBy int,
@AssignedTo int,
@TaskSummary varchar(70),
@TaskDescription varchar(500),
@PriorityID int,
@StatusID int,
@Progress int,
@DateDue datetime
)
AS
SET NOCOUNT OFF;
INSERT INTO Tasks (ProjectID, ModifiedBy, AssignedTo, TaskSummary, TaskDescription, PriorityID, StatusID, Progress, DateDue) 
	VALUES (@ProjectID, @ModifiedBy, @AssignedTo, @TaskSummary, @TaskDescription, @PriorityID, @StatusID, @Progress, @DateDue)
SELECT TaskID, ProjectID, ModifiedBy, AssignedTo, TaskSummary, TaskDescription, PriorityID, StatusID, Progress, IsDeleted, DateDue, DateModified, DateCreated FROM Tasks WHERE (TaskID = @@IDENTITY)
GO
GRANT  EXECUTE  ON [InsertTask]  TO [ASPNET];
GO


CREATE PROCEDURE [InsertUser]
(
@UserName varchar(16),
@UserPassword varchar(16),
@UserFullName varchar(50),
@UserEmail varchar(50),
@IsAdministrator bit,
@IsAccountLocked bit
)
AS
SET NOCOUNT OFF;
INSERT INTO Users (UserName, UserPassword, UserFullName, UserEmail, IsAdministrator, IsAccountLocked) VALUES (@UserName, @UserPassword, @UserFullName, @UserEmail, @IsAdministrator, @IsAccountLocked);
SELECT @@IDENTITY AS UserID
GO
GRANT  EXECUTE  ON [InsertUser]  TO [ASPNET];
GO


CREATE PROCEDURE [UpdateTask]
(
	@TaskID int,
	@ProjectID int,
	@ModifiedBy int,
	@AssignedTo int,
	@TaskSummary varchar(70),
	@TaskDescription varchar(500),
	@PriorityID int,
	@StatusID int,
	@Progress int,
	@IsDeleted bit,
	@DateDue datetime,
	@DateModified datetime,
	@DateCreated datetime,
	@Original_ProjectID int,
	@Original_ModifiedBy int,
	@Original_AssignedTo int,
	@Original_TaskSummary varchar(70),
	@Original_TaskDescription varchar(500),
	@Original_PriorityID int,
	@Original_StatusID int,
	@Original_Progress int,
	@Original_IsDeleted bit,
	@Original_DateDue datetime,
	@Original_DateModified datetime,
	@Original_DateCreated datetime
)
AS
SET NOCOUNT OFF;
--note we are using convert to varchar on the date comparison so that the pocket pc app can use this sp.
--the pocket pc app stores offline data in Sql CE which only supports a 4 byte datetime.
UPDATE Tasks 
SET ProjectID = @ProjectID, ModifiedBy = @ModifiedBy, AssignedTo = @AssignedTo, TaskSummary = @TaskSummary, TaskDescription = @TaskDescription, PriorityID = @PriorityID, StatusID = @StatusID, Progress = @Progress, IsDeleted = @IsDeleted, DateDue = @DateDue, DateModified = @DateModified 
WHERE (TaskID = @TaskID) AND (ProjectID = @Original_ProjectID) AND (ModifiedBy = @Original_ModifiedBy) AND (AssignedTo = @Original_AssignedTo) AND (TaskSummary = @Original_TaskSummary) AND (TaskDescription = @Original_TaskDescription) AND (ProjectID = @Original_ProjectID) AND (StatusID = @Original_StatusID) AND (Progress = @Original_Progress) AND (IsDeleted = @Original_IsDeleted) AND (convert(varchar(20), DateDue) = convert(varchar(20), @Original_DateDue)) AND (convert(varchar(20), DateModified) = convert(varchar(20), @Original_DateModified)) AND (convert(varchar(20), DateCreated) = convert(varchar(20), @Original_DateCreated)) AND (PriorityID = @Original_PriorityID);
SELECT TaskID, ProjectID, ModifiedBy, AssignedTo, TaskSummary, TaskDescription, PriorityID, StatusID, Progress, IsDeleted, DateDue, DateModified, DateCreated FROM Tasks WHERE (TaskID = @TaskID)
GO
GRANT  EXECUTE  ON [UpdateTask]  TO [ASPNET];
GO

CREATE PROCEDURE [ChangePassword]
(
@UserPassword varchar(16), 
@UserID int
)
AS
SET NOCOUNT OFF;
UPDATE Users Set UserPassword = @UserPassword Where UserID = @UserID;
GO
GRANT  EXECUTE  ON [ChangePassword]  TO [ASPNET];
GO

CREATE PROCEDURE [UpdateUser]
(
@UserPassword varchar(16), 
@UserFullName varchar(50), 
@UserEmail varchar(50), 
@IsAdministrator bit, 
@IsAccountLocked bit,
@UserID int
)
AS
SET NOCOUNT OFF;
If @UserPassword = ''
UPDATE Users Set UserFullName = @UserFullName, UserEmail = @UserEmail, IsAdministrator = @IsAdministrator, IsAccountLocked = @IsAccountLocked Where UserID = @UserID;
Else
UPDATE Users Set UserPassword = @UserPassword, UserFullName = @UserFullName, UserEmail = @UserEmail, IsAdministrator = @IsAdministrator, IsAccountLocked = @IsAccountLocked Where UserID = @UserID;
GO
GRANT  EXECUTE  ON [UpdateUser]  TO [ASPNET];
GO


CREATE TRIGGER trgTasksUpdate ON [Tasks] 
FOR INSERT, UPDATE 
AS
INSERT INTO TaskHistory SELECT TaskID, ProjectID, ModifiedBy, AssignedTo, TaskSummary, TaskDescription, PriorityID, StatusID, Progress, IsDeleted, DateDue, DateModified, DateCreated FROM INSERTED
GO