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', 'jdoe@mycompany.com', 0, 1, GetDate());
Set @User1 = @@IDENTITY;
INSERT INTO Users VALUES ('administrator', 'welcome', 'Admin', 'admin@mycompany.com', 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