createdbobjects.sql

Time Tracker Starter Kit 使您能轻松创建这样一个应用程序

USE [~~##CMRCStarterKitDB##~~]
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