USE master

CREATE DATABASE [TimeTracker]  
GO

exec sp_dboption N'TimeTracker', N'autoclose', N'false'
GO

exec sp_dboption N'TimeTracker', N'bulkcopy', N'false'
GO

exec sp_dboption N'TimeTracker', N'trunc. log', N'false'
GO

exec sp_dboption N'TimeTracker', N'torn page detection', N'false'
GO

exec sp_dboption N'TimeTracker', N'read only', N'false'
GO

exec sp_dboption N'TimeTracker', N'dbo use', N'false'
GO

exec sp_dboption N'TimeTracker', N'single', N'false'
GO

exec sp_dboption N'TimeTracker', N'autoshrink', N'false'
GO

exec sp_dboption N'TimeTracker', N'ANSI null default', N'false'
GO

exec sp_dboption N'TimeTracker', N'recursive triggers', N'false'
GO

exec sp_dboption N'TimeTracker', N'ANSI nulls', N'false'
GO

exec sp_dboption N'TimeTracker', N'concat null yields null', N'false'
GO

exec sp_dboption N'TimeTracker', N'cursor close on commit', N'false'
GO

exec sp_dboption N'TimeTracker', N'default to local cursor', N'false'
GO

exec sp_dboption N'TimeTracker', N'quoted identifier', N'false'
GO

exec sp_dboption N'TimeTracker', N'ANSI warnings', N'false'
GO

exec sp_dboption N'TimeTracker', N'auto create statistics', N'true'
GO

exec sp_dboption N'TimeTracker', N'auto update statistics', N'true'
GO

use [TimeTracker]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_EntryLog_Categories]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TT_EntryLog] DROP CONSTRAINT FK_EntryLog_Categories
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_EntryLog_ProjectMembers]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TT_EntryLog] DROP CONSTRAINT FK_EntryLog_ProjectMembers
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Categories_Projects]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TT_Categories] DROP CONSTRAINT FK_Categories_Projects
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Roles_Projects]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TT_ProjectMembers] DROP CONSTRAINT FK_Roles_Projects
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Users_Roles]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TT_Users] DROP CONSTRAINT FK_Users_Roles
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_WorksOn_Users]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TT_ProjectMembers] DROP CONSTRAINT FK_WorksOn_Users
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Projects_Users]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TT_Projects] DROP CONSTRAINT FK_Projects_Users
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_AddProject]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_AddProject]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_AddProjectMember]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_AddProjectMember]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_AddTimeEntry]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_AddTimeEntry]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_AddUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_AddUser]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_DeleteProject]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_DeleteProject]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_DeleteTimeEntry]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_DeleteTimeEntry]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_DeleteUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_DeleteUser]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_GetManagerProjectCount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_GetManagerProjectCount]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_GetProject]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_GetProject]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_GetTimeEntry]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_GetTimeEntry]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_GetUserByUserName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_GetUserByUserName]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_GetUserDisplayName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_GetUserDisplayName]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ListAllProjects]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_ListAllProjects]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ListAllRoles]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_ListAllRoles]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ListCategories]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_ListCategories]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ListCategoriesByProject]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_ListCategoriesByProject]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ListManagers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_ListManagers]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ListMembers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_ListMembers]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ListProjects]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_ListProjects]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ListProjectsByIDs]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_ListProjectsByIDs]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ListProjectsWithMembership]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_ListProjectsWithMembership]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ListResourceByIDs]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_ListResourceByIDs]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ListTimeEntries]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_ListTimeEntries]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ListTimeEntriesByCategory]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_ListTimeEntriesByCategory]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ListTimeEntriesByUsers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_ListTimeEntriesByUsers]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ListUserTimeSummary]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_ListUserTimeSummary]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ListUsers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_ListUsers]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_UpdateProject]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_UpdateProject]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_UpdateTimeEntry]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_UpdateTimeEntry]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_UpdateUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_UpdateUser]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_UserLogin]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_UserLogin]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_Categories]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TT_Categories]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_EntryLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TT_EntryLog]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ProjectMembers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TT_ProjectMembers]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_Projects]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TT_Projects]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_Roles]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TT_Roles]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_Users]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TT_Users]
GO

CREATE TABLE [dbo].[TT_Categories] (
	[CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
	[ProjectID] [int] NOT NULL ,
	[Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Abbreviation] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[EstDuration] [decimal](10, 2) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TT_EntryLog] (
	[EntryLogID] [int] IDENTITY (1, 1) NOT NULL ,
	[Description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Duration] [decimal](10, 2) NOT NULL ,
	[EntryDate] [smalldatetime] NOT NULL ,
	[ProjectID] [int] NOT NULL ,
	[UserID] [int] NOT NULL ,
	[CategoryID] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TT_ProjectMembers] (
	[ProjectID] [int] NOT NULL ,
	[UserID] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TT_Projects] (
	[ProjectID] [int] IDENTITY (1, 1) NOT NULL ,
	[Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Description] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ManagerUserID] [int] NULL ,
	[EstCompletionDate] [datetime] NULL ,
	[EstDuration] [decimal](10, 2) NULL ,
	[CreationDate] [datetime] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TT_Roles] (
	[RoleID] [int] NOT NULL ,
	[Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TT_Users] (
	[DisplayName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Password] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[UserID] [int] IDENTITY (1, 1) NOT NULL ,
	[UserName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[RoleID] [int] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TT_Categories] WITH NOCHECK ADD 
	CONSTRAINT [PK_Categories] PRIMARY KEY  CLUSTERED 
	(
		[CategoryID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[TT_ProjectMembers] WITH NOCHECK ADD 
	CONSTRAINT [PK_ProjectMembers] PRIMARY KEY  CLUSTERED 
	(
		[ProjectID],
		[UserID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[TT_Roles] WITH NOCHECK ADD 
	CONSTRAINT [PK_Roles] PRIMARY KEY  CLUSTERED 
	(
		[RoleID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[TT_EntryLog] ADD 
	CONSTRAINT [PK_Tasks] PRIMARY KEY  NONCLUSTERED 
	(
		[EntryLogID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[TT_ProjectMembers] ADD 
	CONSTRAINT [IX_Roles] UNIQUE  NONCLUSTERED 
	(
		[ProjectID],
		[UserID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[TT_Projects] ADD 
	CONSTRAINT [DF_Projects_CreationDate] DEFAULT (getdate()) FOR [CreationDate],
	CONSTRAINT [PK_Projects] PRIMARY KEY  NONCLUSTERED 
	(
		[ProjectID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[TT_Users] ADD 
	CONSTRAINT [PK_Users] PRIMARY KEY  NONCLUSTERED 
	(
		[UserID]
	)  ON [PRIMARY] ,
	CONSTRAINT [IX_Users] UNIQUE  NONCLUSTERED 
	(
		[UserName]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[TT_Categories] ADD 
	CONSTRAINT [FK_Categories_Projects] FOREIGN KEY 
	(
		[ProjectID]
	) REFERENCES [dbo].[TT_Projects] (
		[ProjectID]
	) ON DELETE CASCADE 
GO

ALTER TABLE [dbo].[TT_EntryLog] ADD 
	CONSTRAINT [FK_EntryLog_Categories] FOREIGN KEY 
	(
		[CategoryID]
	) REFERENCES [dbo].[TT_Categories] (
		[CategoryID]
	) ON DELETE CASCADE ,
	CONSTRAINT [FK_EntryLog_ProjectMembers] FOREIGN KEY 
	(
		[ProjectID],
		[UserID]
	) REFERENCES [dbo].[TT_ProjectMembers] (
		[ProjectID],
		[UserID]
	)
GO

ALTER TABLE [dbo].[TT_ProjectMembers] ADD 
	CONSTRAINT [FK_Roles_Projects] FOREIGN KEY 
	(
		[ProjectID]
	) REFERENCES [dbo].[TT_Projects] (
		[ProjectID]
	) ON DELETE CASCADE ,
	CONSTRAINT [FK_WorksOn_Users] FOREIGN KEY 
	(
		[UserID]
	) REFERENCES [dbo].[TT_Users] (
		[UserID]
	)
GO

ALTER TABLE [dbo].[TT_Projects] ADD 
	CONSTRAINT [FK_Projects_Users] FOREIGN KEY 
	(
		[ManagerUserID]
	) REFERENCES [dbo].[TT_Users] (
		[UserID]
	)
GO

ALTER TABLE [dbo].[TT_Users] ADD 
	CONSTRAINT [FK_Users_Roles] FOREIGN KEY 
	(
		[RoleID]
	) REFERENCES [dbo].[TT_Roles] (
		[RoleID]
	)
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE         PROCEDURE TT_AddProject
(
	@Name nvarchar(50),
	@Description nvarchar(1024),
	@ManagerUserID int,
	@EstCompletionDate datetime,
	@EstDuration int,
	@Members nvarchar(2000),
	@Categories nvarchar(4000)
)
AS

	DECLARE @Error int
	DECLARE @ProjectID int
	DECLARE @TempString varchar(4000)
	DECLARE @Temp nvarchar(4000)
	DECLARE @Count int
	DECLARE @TempTable TABLE(UserID int PRIMARY KEY)
	DECLARE @InnerTemp nvarchar(50)
	DECLARE @CatName varchar(20)
	DECLARE @Abbrev varchar(5)
	DECLARE @Duration decimal(10,2)
	DECLARE @InnerCount int

	BEGIN TRANSACTION 

	INSERT INTO TT_Projects
	(
		[Name],
		[Description],
		ManagerUserID,
		EstCompletionDate,
		EstDuration,
		CreationDate
	)
	VALUES
	(   
		@Name,
		@Description,
		@ManagerUserID,
		@EstCompletionDate,
		@EstDuration,
		getdate()
	)

	SET @Error = @@ERROR
	IF @Error != 0 GOTO ERROR_HANDLER

	SET @ProjectID = @@Identity  

	SET @TempString = @Members

	SET @Count = CHARINDEX(',', @TempString)

	WHILE @Count > 0
	BEGIN
		SET @Temp = SUBSTRING(@TempString, 1, (@Count - 1))
		INSERT INTO @TempTable VALUES(CAST(@Temp AS int))
		SET @TempString = SUBSTRING(@TempString, (@Count + 1), (LEN(@TempString) - @Count))
		SET @Count = CHARINDEX(',', @TempString)
	END

	INSERT INTO @TempTable VALUES(CAST(@TempString AS int))

	INSERT INTO TT_ProjectMembers  
		SELECT @ProjectID, UserID FROM @TempTable

	SET @Error = @@ERROR
	IF @Error != 0 GOTO ERROR_HANDLER

	SET @TempString = @Categories
	
	SET @Count = CHARINDEX(';', @TempString)
	WHILE @Count > 0
	BEGIN
		SET @Temp = SUBSTRING(@TempString, 1, (@Count - 1))

		SET @InnerCount = CHARINDEX(',', @Temp)

		SET @InnerTemp = SUBSTRING(@Temp, 1, (@InnerCount - 1))
		SET @CatName = @InnerTemp

		SET @Temp = SUBSTRING(@Temp, (@InnerCount + 1), (LEN(@Temp) - @InnerCount))
		SET @InnerCount = CHARINDEX(',', @Temp)

		SET @InnerTemp = SUBSTRING(@Temp, 1, (@InnerCount - 1))
		SET @Abbrev = @InnerTemp
			
		SET @Temp = SUBSTRING(@Temp, (@InnerCount + 1), (LEN(@Temp) - @InnerCount))
		SET @InnerCount = CHARINDEX(',', @Temp)

 		SET @Duration =  CAST(@Temp AS int)

		INSERT INTO TT_Categories
		(
			ProjectID, 
			[Name], 
			Abbreviation,
			EstDuration
		)
		VALUES
		(   
			@ProjectID,
			@CatName,
			@Abbrev,
			@Duration
		)

		SET @Error = @@ERROR
		IF @Error != 0 GOTO ERROR_HANDLER
	
		SET @TempString = SUBSTRING(@TempString, (@Count + 1), (LEN(@TempString) - @Count))
		SET @Count = CHARINDEX(';', @TempString)

	END

	set @Temp =  @TempString
	SET @InnerCount = CHARINDEX(',', @Temp)

	SET @InnerTemp = SUBSTRING(@Temp, 1, (@InnerCount - 1))
	SET @CatName = @InnerTemp

	SET @Temp = SUBSTRING(@Temp, (@InnerCount + 1), (LEN(@Temp) - @InnerCount))
	SET @InnerCount = CHARINDEX(',', @Temp)

	SET @InnerTemp = SUBSTRING(@Temp, 1, (@InnerCount - 1))
	SET @Abbrev = @InnerTemp

	SET @Temp = SUBSTRING(@Temp, (@InnerCount + 1), (LEN(@Temp) - @InnerCount))
	SET @InnerCount = CHARINDEX(',', @Temp)

	SET @Duration =  CAST(@Temp AS int)

	INSERT INTO TT_Categories
	(
		ProjectID, 
		[Name], 
		Abbreviation,
		EstDuration
	)
	VALUES
	(   
		@ProjectID,
		@CatName,
		@Abbrev,
		@Duration
	)

	SET @Error = @@ERROR
	IF @Error != 0 GOTO ERROR_HANDLER

	COMMIT TRANSACTION

	SELECT @ProjectID AS ProjectID  

ERROR_HANDLER:
	IF @@TRANCOUNT != 0 ROLLBACK TRANSACTION
	RETURN @Error
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO








CREATE  PROCEDURE TT_AddProjectMember
(
	@ProjectID int,
	@UserID int
)
AS

INSERT INTO TT_ProjectMembers
(
	ProjectID,
	UserID
)
VALUES
(
	@ProjectID,
	@UserID
)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO








CREATE   PROCEDURE TT_AddTimeEntry
(
	@UserID int,
	@ProjectID int,
	@CategoryID int,
	@EntryDate datetime,
	@Description nvarchar(255),
	@Duration decimal(10,2)
)
AS

INSERT INTO TT_EntryLog
(
	Description, 
	Duration, 
	EntryDate,
	ProjectID,
	UserID,	
	CategoryID
)
VALUES
(   
	
	@Description,
	@Duration,
	@EntryDate,
	@ProjectID,
	@UserID,
	@CategoryID	
)

SELECT
    @@Identity AS EntryLogID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO










CREATE     PROCEDURE TT_AddUser
(
	@UserName nvarchar(50),
	@Password nvarchar(50),
	@DisplayName nvarchar(50),
--	@LastName nvarchar(50),
--	@Email nvarchar(50),
--	@Telephone nvarchar(50),
	@RoleID int
)
AS

--Verify that the UserName is not being added to a UserName that already exists
IF Not Exists (SELECT UserName FROM TT_Users WHERE UserName=@UserName)
  BEGIN

	INSERT INTO TT_Users
	(
		UserName, 
 		Password, 
 		DisplayName,
-- 		LastName,
-- 		Email,
-- 		Telephone,
		RoleID
	)
	VALUES
	(   
		@UserName,
 		@Password,
 		@DisplayName,
-- 		@LastName,
-- 		@Email,
-- 		@Telephone,
		@RoleID
	)
	
	SELECT
	    @@Identity AS UserID    
  END
ELSE
	SELECT -2 AS UserID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO





CREATE PROCEDURE TT_DeleteProject
(
	@ProjectID int
)
AS

DELETE FROM 
	TT_Projects 

WHERE 
	ProjectID = @ProjectID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO






CREATE PROCEDURE TT_DeleteTimeEntry
(
	@EntryLogID int
)
AS

DELETE FROM 
	TT_EntryLog
	
WHERE 
	EntryLogID = @EntryLogID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO






CREATE  PROCEDURE TT_DeleteUser
(
	@UserID int
)
AS

DELETE FROM 
	TT_Users
	
WHERE 
	UserID = @UserID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO






CREATE PROCEDURE TT_GetManagerProjectCount
	@UserID int 
AS
	SELECT COUNT(*) FROM TT_Projects WHERE ManagerUserID = @UserID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO







CREATE  PROCEDURE TT_ListMembers
(
	@ProjectID int
)
AS

SELECT 
	u.UserID, UserName
	
FROM 
	TT_ProjectMembers pm
INNER JOIN
	TT_Users u
On 
	u.UserID = pm.UserID
	
WHERE 
	ProjectID = @ProjectID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO



SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO








CREATE    PROCEDURE TT_ListCategoriesByProject
(
    @ProjectID int
)
AS

    SELECT C.CategoryID,
	    C.Name,
            C.Abbreviation AS CategoryShortName,
            EstDuration = CAST(Round(C.EstDuration,0) AS int),
            ISNULL(Sum(EL.Duration), 0) AS ActualHours
    FROM TT_Categories C 
        LEFT OUTER JOIN TT_EntryLog EL
            ON C.CategoryID = EL.CategoryID
    WHERE C.ProjectID = @ProjectID
    GROUP BY C.CategoryID, C.Name, C.Abbreviation, C.EstDuration
    
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


CREATE   PROCEDURE TT_GetProject
(
	@ProjectID int
)
AS

SELECT 
	Name, Description, ManagerUserID, EstCompletionDate, EstDuration = CAST(Round(EstDuration,0) AS int)

FROM 
	TT_Projects

WHERE 
	ProjectID = @ProjectID

exec TT_ListMembers @ProjectID
exec TT_ListCategoriesByProject @ProjectID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO







CREATE  PROCEDURE TT_GetTimeEntry
(
    @EntryLogID int
)
AS

SELECT 
    EL.Description, 
    EL.Duration, 
    EL.EntryDate, 
    EL.ProjectID, 
    EL.UserID, 
    EL.CategoryID, 
    P.Name AS ProjectName

FROM 
    TT_EntryLog EL
        INNER JOIN TT_Projects P
            ON EL.ProjectID = P.ProjectID
    
WHERE 
    EL.EntryLogID = @EntryLogID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO






CREATE  PROCEDURE TT_GetUserByUserName
(
	@UserName nvarchar(50)
)

AS
	
SELECT 
	UserID, 
	UserName, 
	Password,
-- 	LastName, 
-- 	Email, 
-- 	Telephone, 
	RoleID

FROM 
	TT_Users WHERE UserName = @UserName

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create proc TT_GetUserDisplayName
	@Username nvarchar(50)
AS
	SELECT DisplayName FROM TT_Users
	WHERE UserName = @Username

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO







CREATE  PROCEDURE TT_ListAllProjects

AS

	SELECT ProjectID, 
		Name as ProjectName, 
		Description, 
		ManagerUserID, 
		EstCompletionDate, 
		EstDuration
	FROM TT_Projects
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO





CREATE PROCEDURE TT_ListAllRoles

AS

SELECT 
	RoleID, 
 	Name
FROM 
	TT_Roles
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO






CREATE PROCEDURE TT_ListCategories
(
	@ProjectID int
)
AS

SELECT 
	CategoryID, Name, Abbreviation, CAST(EstDuration AS int) EstDuration
	
FROM 
	TT_Categories
	
WHERE 
	ProjectID = @ProjectID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO










CREATE    PROCEDURE TT_ListManagers

AS

SELECT 
	UserID, UserName, RoleID
	
FROM 
	TT_Users
Where 
	RoleID = 2
OR
	RoleID = 1
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO







CREATE   PROCEDURE TT_ListProjects
(
	@UserID int,
	@RoleID int
)
AS

IF @RoleID = 1
BEGIN
	SELECT ProjectID, 
		Name as ProjectName, 
		Description, 
		ManagerUserID, 
		UserName,
		EstCompletionDate, 
		EstDuration
	FROM 
		TT_Projects
	INNER JOIN
		TT_Users
	ON 
		ManagerUserID = UserID
END

ELSE IF @RoleID = 2
BEGIN
	SELECT ProjectID, 
		Name as ProjectName, 
		Description, 
		ManagerUserID, 
		UserName,
		EstCompletionDate, 
		EstDuration
	FROM 
		TT_Projects
	INNER JOIN
		TT_Users
	ON 
		ManagerUserID = UserID
	WHERE ManagerUserID = @UserID
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO






CREATE  PROCEDURE TT_ListProjectsByIDs
(
    @ProjectIDs nvarchar(512),
    @UserID int
)
AS

    DECLARE @sql nvarchar(1024),
            @RoleID int
    
    SELECT @RoleID = RoleID
    FROM TT_Users 
    WHERE UserID = @UserID;
    
    IF (@RoleID = 1)
        BEGIN
        SET @sql = 'SELECT P.ProjectID,'
        SET @sql = @sql + ' P.Name AS ProjectName,'
        SET @sql = @sql + ' P.EstCompletionDate,'
        SET @sql = @sql + ' P.EstDuration AS EstHours,'
        SET @sql = @sql + ' Sum(EL.Duration) AS ActualHours '
        SET @sql = @sql + 'FROM TT_Projects P INNER JOIN TT_EntryLog EL'
        SET @sql = @sql + ' ON P.ProjectID = EL.ProjectID '
        SET @sql = @sql + 'WHERE P.ProjectID IN (' + @ProjectIDs + ') '
        SET @sql = @sql + 'GROUP BY P.ProjectID, P.Name, P.EstCompletionDate, P.EstDuration'            
        END
    ELSE IF (@RoleID = 2)
        BEGIN
        SET @sql = 'SELECT P.ProjectID,'
        SET @sql = @sql + ' P.Name AS ProjectName,'
        SET @sql = @sql + ' P.EstCompletionDate,'
        SET @sql = @sql + ' P.EstDuration AS EstHours,'
        SET @sql = @sql + ' Sum(EL.Duration) AS ActualHours '
        SET @sql = @sql + 'FROM TT_Projects P INNER JOIN TT_EntryLog EL'
        SET @sql = @sql + ' ON P.ProjectID = EL.ProjectID '
        SET @sql = @sql + 'WHERE P.ProjectID IN (' + @ProjectIDs + ') '
        SET @sql = @sql + ' AND P.ManagerUserID = ' + CAST(@UserID AS nvarchar(20))
        SET @sql = @sql + 'GROUP BY P.ProjectID, P.Name, P.EstCompletionDate, P.EstDuration'            
        END
    ELSE
        BEGIN
        SET @sql = 'SELECT P.ProjectID,'
        SET @sql = @sql + ' P.Name AS ProjectName,'
        SET @sql = @sql + ' P.EstCompletionDate,'
        SET @sql = @sql + ' P.EstDuration AS EstHours,'
        SET @sql = @sql + ' P.EstDuration AS ActualHours '
        SET @sql = @sql + 'FROM TT_Projects P WHERE 1=0 '
        END
    
    EXEC sp_executesql @sql
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO





CREATE   PROCEDURE TT_ListProjectsWithMembership
(
	@QueryUserID int,
	@UserID int
)
AS

DECLARE @@QueryUserRoleID int
SELECT @@QueryUserRoleID = TT_Users.RoleID FROM TT_Users WHERE TT_Users.UserID = @QueryUserID

IF @@QueryUserRoleID = 1 OR @QueryUserID = @UserID
  BEGIN
	SELECT 	TT_Projects.ProjectID,
		Name, 
		Description, 
		ManagerUserID, 
		EstCompletionDate, 
		EstDuration
	FROM TT_Projects 
	INNER JOIN TT_ProjectMembers ON TT_ProjectMembers.ProjectID = TT_Projects.ProjectID
	WHERE UserID = @UserID
  END

IF @@QueryUserRoleID = 2
  BEGIN
	SELECT 	TT_Projects.ProjectID,
		Name, 
		Description, 
		ManagerUserID, 
		EstCompletionDate, 
		EstDuration
	FROM TT_Projects 
	INNER JOIN TT_ProjectMembers ON TT_ProjectMembers.ProjectID = TT_Projects.ProjectID
	WHERE UserID = @UserID AND ManagerUserID = @QueryUserID
  END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO





CREATE  PROCEDURE TT_ListResourceByIDs
(
    @UserIDs nvarchar(512),
    @StartDate DateTime,
    @EndDate DateTime
)
AS

    DECLARE @sql nvarchar(1024)
    
    SET @sql = 'SELECT U.UserID, U.UserName, '
    SET @sql = @sql + ' Sum(EL.Duration) AS totalHours'
    SET @sql = @sql + ' FROM TT_EntryLog EL INNER JOIN TT_Users U'
    SET @sql = @sql + ' ON EL.UserID = U.UserID '
    SET @sql = @sql + ' WHERE U.UserID IN (' + @UserIDs + ') '
    SET @sql = @sql + ' AND EL.EntryDate >= ' +CAST(@StartDate as nvarchar(20))+ ' AND EL.EntryDate <= ' + CAST(@EndDate as nvarchar(20))	
    SET @sql = @sql + ' GROUP BY U.UserID, U.UserName'

    EXEC sp_executesql @sql
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO









CREATE PROCEDURE TT_ListTimeEntries
(
	@QueryUserID int,
	@UserID int,
	@StartDate datetime,
	@EndDate datetime
)
AS
DECLARE @@QueryUserRoleID int

SELECT @@QueryUserRoleID = TT_Users.RoleID FROM TT_Users WHERE TT_Users.UserID = @QueryUserID

IF @@QueryUserRoleID = 1 or @QueryUserID = @UserID
	BEGIN
		SELECT 
			EntryLogID, TT_EntryLog.Description, Duration, EntryDate, TT_EntryLog.ProjectID AS ProjectID, 
			TT_EntryLog.CategoryID AS CategoryID, TT_Categories.Abbreviation AS CategoryName, TT_Projects.Name AS ProjectName,
			ManagerUserID, TT_Categories.Abbreviation AS CatShortName
		FROM 
			TT_EntryLog 
				INNER JOIN 
				  TT_Categories 
				ON 
				  TT_EntryLog.CategoryID = TT_Categories.CategoryID 
				INNER JOIN 
				  TT_Projects 
				ON 
				  TT_EntryLog.ProjectID = TT_Projects.ProjectID	
		WHERE 
			UserID = @UserID 
			AND 
			Convert(nvarchar, EntryDate, 1) >= Convert(nvarchar, @StartDate, 1)
			AND 
			Convert(nvarchar, EntryDate, 1) <= Convert(nvarchar, @EndDate, 1)
	END
ELSE IF @@QueryUserRoleID = 2
	BEGIN
		SELECT 
			EntryLogID, TT_EntryLog.Description, Duration, EntryDate, TT_EntryLog.ProjectID AS ProjectID, 
			TT_EntryLog.CategoryID AS CategoryID, TT_Categories.Abbreviation AS CategoryName, TT_Projects.Name AS ProjectName,
			ManagerUserID, TT_Categories.Abbreviation AS CatShortName
		FROM 
			TT_EntryLog 
				INNER JOIN 
				  TT_Categories 
				ON 
				  TT_EntryLog.CategoryID = TT_Categories.CategoryID 
				INNER JOIN 
				  TT_Projects 
				ON 
				  TT_EntryLog.ProjectID = TT_Projects.ProjectID	
		WHERE 
			UserID = @UserID 
			AND 
			Convert(nvarchar, EntryDate, 1) >= Convert(nvarchar, @StartDate, 1)
			AND 
			Convert(nvarchar, EntryDate, 1) <= Convert(nvarchar, @EndDate, 1)
			AND
			ManagerUserID = @QueryUserID
	END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO





CREATE  PROCEDURE TT_ListTimeEntriesByCategory
(
    @CategoryID int
)
AS

	SELECT 
		U.UserName, 
		U.UserID, 
		temp.MinEntryDate,
		temp.MaxEntryDate,
		Sum(EL.Duration) AS Duration
	FROM TT_EntryLog EL
	    INNER JOIN TT_Users U
	        ON EL.UserID = U.UserID
	    INNER JOIN (SELECT UserID, MIN(EntryDate) AS MinEntryDate, MAX(EntryDate) AS MaxEntryDate FROM TT_EntryLog GROUP BY UserID) AS temp
	    	ON temp.UserID = EL.UserID
	WHERE EL.CategoryID = @CategoryID
	GROUP BY U.UserName, U.UserID, temp.MinEntryDate, temp.MaxEntryDate
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO






CREATE   PROCEDURE TT_ListTimeEntriesByUsers
(
	@UserIDList nvarchar(255),
	@StartDate datetime,
	@EndDate datetime
)
AS

DECLARE 
	@sSqlString nvarchar(512)

SET
	@sSqlString = 'SELECT EntryLogID, Description, Duration, EntryDate, ProjectID, UserID, CategoryID'
	
SET
	@sSqlString = @sSqlString + ' FROM TT_EntryLog WHERE UserID IN (' + @UserIDList + ')'

SET	
	@sSqlString = @sSqlString + ' and EntryDate >= '+ CAST(@StartDate AS nvarchar(24)) + ' and EntryDate <=  ' + CAST(@EndDate AS nvarchar(24))

SET
	@sSqlString = @sSqlString + ' GROUP BY UserID'

EXEC sp_executesql @sSqlString
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO






CREATE  PROCEDURE TT_ListUserTimeSummary
(
    @ManagerUserID int,
    @UserIDList nvarchar(512),
    @StartDate datetime,
    @EndDate datetime
)
AS

DECLARE 
    @sSqlString nvarchar(1024),
    @sSubSql nvarchar(1024),
    @RoleID int
    
SELECT @RoleID = RoleID
FROM TT_Users 
WHERE UserID = @ManagerUserID;

IF (@RoleID = 1)
    BEGIN
    SET @sSqlString = 'SELECT Sum(EL.Duration) TotalHours, U.UserID, U.UserName'
    SET @sSqlString = @sSqlString + ' FROM TT_EntryLog EL Inner Join TT_Users U On EL.UserID = U.UserID WHERE U.UserID IN (' + @UserIDList + ')'
    SET @sSqlString = @sSqlString + ' and EL.EntryDate >= @1 and EL.EntryDate <= @2  GROUP BY U.UserID, U.UserName'
    END
ELSE IF (@RoleID = 2)
    BEGIN   
    SET @sSubSql = 'SELECT PM.UserID FROM TT_Projects P INNER JOIN TT_ProjectMembers PM'
    SET @sSubSql = @sSubSql + ' ON P.ProjectID = PM.ProjectID WHERE P.ManagerUserID = @3 AND PM.UserID IN (' + @UserIDList + ')' 
    
    SET @sSqlString = 'SELECT Sum(EL.Duration) TotalHours, U.UserID, U.UserName'
    SET @sSqlString = @sSqlString + ' FROM TT_EntryLog EL Inner Join TT_Users U On EL.UserID = U.UserID WHERE U.UserID IN (' + @sSubSql + ')'
    SET @sSqlString = @sSqlString + ' AND EL.ProjectID IN (SELECT ProjectID From TT_Projects Where ManagerUserID = @3) '
    SET @sSqlString = @sSqlString + ' and EL.EntryDate >= @1 and EL.EntryDate <= @2  GROUP BY U.UserID, U.UserName'
    END
ELSE 
    SET @sSqlString = 'SELECT U.UserID AS TotalHours, U.UserID, U.UserName From TT_Users U Where 1=0'

EXEC sp_executesql @sSqlString, N'@1 datetime, @2 datetime, @3 int', @StartDate, @EndDate, @ManagerUserID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO







CREATE       PROCEDURE TT_ListUsers
(
	@UserID int,
	@RoleID int
)
AS

IF @RoleID = 1
	BEGIN
	  SELECT 
		UserID, 
		UserName, 
		TT_Users.RoleID,
		TT_Roles.Name 'RoleName'
	  FROM 
		TT_Users INNER JOIN TT_Roles ON TT_Users.RoleID = TT_Roles.RoleID
	END
ELSE IF @RoleID = 2
	BEGIN
	  SELECT DISTINCT
		TT_Users.UserID,
		TT_Users.UserName,
		TT_Users.RoleID,
		TT_Roles.Name 'RoleName'
	  FROM TT_Users 
	  INNER JOIN TT_ProjectMembers ON TT_Users.UserID=TT_ProjectMembers.UserID
	  INNER JOIN TT_Projects ON TT_ProjectMembers.ProjectID=TT_Projects.ProjectID
	  INNER JOIN TT_Roles ON TT_Users.RoleID = TT_Roles.RoleID
	  WHERE	@UserID = TT_Projects.ManagerUserID OR @UserID = TT_Users.UserID

	END
ELSE
	SELECT
		UserID, 
		UserName, 
		TT_Users.RoleID,
		TT_Roles.Name 'RoleName'
	FROM 
		TT_Users INNER JOIN TT_Roles ON TT_Users.RoleID = TT_Roles.RoleID
	WHERE UserID = @UserID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO















CREATE          PROCEDURE TT_UpdateProject
(
	@ProjectID int,
	@Name nvarchar(50),
	@Description nvarchar(1024),
	@ManagerUserID int,
	@EstCompletionDate datetime,
	@EstDuration int,
	@SelectedMembers nvarchar(2000),
	@Categories nvarchar(4000)
)
AS


	DECLARE @Error int
	DECLARE @TempTable TABLE(UserID int PRIMARY KEY)
	DECLARE @TempString varchar(2000)
	DECLARE @Temp varchar(4000)
	DECLARE @Count int
	DECLARE @TempCatTable TABLE(CategoryID int primary key)
	DECLARE @InnerTemp nvarchar(50)
	DECLARE @CatID int
	DECLARE @CatName varchar(20)
	DECLARE @Abbrev varchar(5)
	DECLARE @Duration decimal(10,2)
	DECLARE @InnerCount int

	BEGIN TRANSACTION 

	-- Update the Project
	UPDATE 
		TT_Projects
	SET 	
		Name=@Name,
	        	Description = @Description,
		ManagerUserID = @ManagerUserID,
		EstCompletionDate = @EstCompletionDate,
		EstDuration = @EstDuration
	       
	WHERE 
		ProjectID = @ProjectID

	SET @Error = @@ERROR
	IF @Error != 0 GOTO ERROR_HANDLER

	SET @TempString = @SelectedMembers

	SET @Count = CHARINDEX(',', @TempString)

	WHILE @Count > 0
	BEGIN
		SET @Temp = SUBSTRING(@TempString, 1, (@Count - 1))
		INSERT INTO @TempTable VALUES(CAST(@Temp AS int))
		SET @TempString = SUBSTRING(@TempString, (@Count + 1), (LEN(@TempString) - @Count))
		SET @Count = CHARINDEX(',', @TempString)
	END

	INSERT INTO @TempTable VALUES(CAST(@TempString AS int))

	DELETE 
		TT_ProjectMembers 
	WHERE 
		ProjectID = @ProjectID
	AND UserID NOT IN(SELECT UserID FROM @TempTable)

	INSERT INTO TT_ProjectMembers  
		SELECT @ProjectID, UserID FROM @TempTable WHERE UserID NOT IN 
			(SELECT UserID FROM TT_ProjectMembers WHERE ProjectID = @ProjectID)

	SET @Error = @@ERROR
	IF @Error != 0 GOTO ERROR_HANDLER

	SET @TempString = @Categories
	
	SET @Count = CHARINDEX(';', @TempString)
	WHILE @Count > 0
	BEGIN
		SET @Temp = SUBSTRING(@TempString, 1, (@Count - 1))

		SET @InnerCount = CHARINDEX(',', @Temp)

		SET @InnerTemp = SUBSTRING(@Temp, 1, (@InnerCount - 1))
		SET @CatID = @InnerTemp

		SET @Temp = SUBSTRING(@Temp, (@InnerCount + 1), (LEN(@Temp) - @InnerCount))
		SET @InnerCount = CHARINDEX(',', @Temp)

		SET @InnerTemp = SUBSTRING(@Temp, 1, (@InnerCount - 1))
		SET @CatName = @InnerTemp

		SET @Temp = SUBSTRING(@Temp, (@InnerCount + 1), (LEN(@Temp) - @InnerCount))
		SET @InnerCount = CHARINDEX(',', @Temp)

		SET @InnerTemp = SUBSTRING(@Temp, 1, (@InnerCount - 1))
		SET @Abbrev = @InnerTemp
			
		SET @Temp = SUBSTRING(@Temp, (@InnerCount + 1), (LEN(@Temp) - @InnerCount))
		SET @InnerCount = CHARINDEX(',', @Temp)

 		SET @Duration =  CAST(Round(@Temp,0) AS int)

		IF not exists(SELECT CategoryID from TT_Categories WHERE ProjectID = @ProjectID AND CategoryID = @CatID)
		BEGIN

			INSERT INTO TT_Categories
			(
				ProjectID, 
				[Name], 
				Abbreviation,
				EstDuration
			)
			VALUES
			(   
				@ProjectID,
				@CatName,
				@Abbrev,
				@Duration
			)

			SET @Error = @@ERROR
			IF @Error != 0 GOTO ERROR_HANDLER
			
			SELECT @CatID = @@IDENTITY
		
		END
		else
		BEGIN
			UPDATE 
				TT_Categories
			SET
				[Name] = @CatName,
				Abbreviation = @Abbrev,
				EstDuration = @Duration
			WHERE 
				ProjectID = @ProjectID
			AND 
				CategoryID = @CatID

			SET @Error = @@ERROR
			IF @Error != 0 GOTO ERROR_HANDLER
		END
		
		INSERT INTO @TempCatTable VALUES(@CatID)
		
		SET @TempString = SUBSTRING(@TempString, (@Count + 1), (LEN(@TempString) - @Count))
		SET @Count = CHARINDEX(';', @TempString)
	END

	set @Temp =  @TempString
	SET @InnerCount = CHARINDEX(',', @Temp)

	SET @InnerTemp = SUBSTRING(@Temp, 1, (@InnerCount - 1))
	SET @CatID = @InnerTemp

	SET @Temp = SUBSTRING(@Temp, (@InnerCount + 1), (LEN(@Temp) - @InnerCount))
	SET @InnerCount = CHARINDEX(',', @Temp)

	SET @InnerTemp = SUBSTRING(@Temp, 1, (@InnerCount - 1))
	SET @CatName = @InnerTemp

	SET @Temp = SUBSTRING(@Temp, (@InnerCount + 1), (LEN(@Temp) - @InnerCount))
	SET @InnerCount = CHARINDEX(',', @Temp)

	SET @InnerTemp = SUBSTRING(@Temp, 1, (@InnerCount - 1))
	SET @Abbrev = @InnerTemp

	SET @Temp = SUBSTRING(@Temp, (@InnerCount + 1), (LEN(@Temp) - @InnerCount))
	SET @InnerCount = CHARINDEX(',', @Temp)

	SET @Duration =  CAST(Round(@Temp,0) AS int)

		IF not exists(SELECT CategoryID from TT_Categories WHERE ProjectID = @ProjectID AND CategoryID = @CatID)
		BEGIN
			INSERT INTO TT_Categories
			(
				ProjectID, 
				[Name], 
				Abbreviation,
				EstDuration
			)
			VALUES
			(   
				@ProjectID,
				@CatName,
				@Abbrev,
				@Duration
			)
		
			SET @Error = @@ERROR
			IF @Error != 0 GOTO ERROR_HANDLER
	
			SELECT @CatID = @@IDENTITY
		
		END
		ELSE
		BEGIN
			UPDATE 
				TT_Categories
			SET
				[Name] = @CatName,
				Abbreviation = @Abbrev,
				EstDuration = @Duration
			WHERE 
				ProjectID = @ProjectID
			AND 
				CategoryID = @CatID

			SET @Error = @@ERROR
			IF @Error != 0 GOTO ERROR_HANDLER
		END
		
		INSERT INTO @TempCatTable VALUES(@CatID)


	DELETE 
		TT_Categories 
	WHERE 
		ProjectID = @ProjectID
	AND 
		CategoryID NOT IN(SELECT CategoryID FROM @TempCatTable)

	SET @Error = @@ERROR
	IF @Error != 0 GOTO ERROR_HANDLER

	COMMIT TRANSACTION
	RETURN 0

ERROR_HANDLER:
	IF @@TRANCOUNT != 0 ROLLBACK TRANSACTION
	RETURN @Error


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO







CREATE  PROCEDURE TT_UpdateTimeEntry
(
	@EntryLogID int,
	@UserID int,
	@ProjectID int,
	@CategoryID int,
	@EntryDate datetime,
	@Description nvarchar(255),
	@Duration decimal(10,2)
)
AS

UPDATE
	 TT_EntryLog
		SET 	
			UserID=@UserID,
		     	ProjectID = @ProjectID,
			CategoryID = @CategoryID,
			EntryDate = @EntryDate,
			Description = @Description,
			Duration = @Duration
	       
WHERE 
	EntryLogID = @EntryLogID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO








CREATE   PROCEDURE TT_UpdateUser
(
	@UserID int,
	@UserName nvarchar(50),
	@Password nvarchar(50),
	@DisplayName nvarchar(50),
--	@FirstName nvarchar(50),
--	@LastName nvarchar(50),
--	@Email nvarchar(50),
--	@Telephone nvarchar(50),
	@RoleID int
)
AS

--Verify that the UserName is not being updated to a UserName that already exists
IF Not Exists (SELECT UserName FROM TT_Users WHERE UserName=@UserName AND UserID<>@UserID)
   BEGIN
	UPDATE 
		TT_Users
		SET UserName=@UserName,
			Password = @Password,
			DisplayName = @DisplayName,
--			FirstName=@FirstName,
--		        LastName=@LastName,
--			Email = @Email,
--			Telephone = @Telephone,
		        RoleID = @RoleID
	WHERE 
		UserID=@UserID

	IF (@@Error<>0) GOTO ErrorHandler
	SELECT 
		1 AS retval
   END
-- If UserName is not unique, indicate so with -2
ELSE
	SELECT
		-2 AS retval

IF @@Error<>0
	GOTO ErrorHandler
ELSE
   BEGIN
--	COMMIT TRANSACTION
	RETURN (0)
   END

ErrorHandler:
   BEGIN
--	ROLLBACK TRANSACTION
	RETURN (1)
   END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE  Procedure TT_UserLogin
    (
        @UserName  nvarchar(100),
        @Password nvarchar(50)
    )
    AS

    SELECT
        UserName

    FROM
        TT_Users

    WHERE
        UserName = @UserName
    AND
        Password = @Password


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO