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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

if (select DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled')) <> 1 
exec sp_fulltext_database N'enable' 

GO


if exists (select * from dbo.sysfulltextcatalogs where name = N'TWNWContent')
exec sp_fulltext_catalog N'TWNWContent', N'drop'

GO

if not exists (select * from dbo.sysfulltextcatalogs where name = N'TWNWContent')
exec sp_fulltext_catalog N'TWNWContent', N'create' 

GO

CREATE TABLE [dbo].[tblFiles] (
	[pkFileID] [int] IDENTITY (1, 1) NOT NULL ,
	[fkTeamID] [int] NOT NULL ,
	[fkFolderID] [int] NOT NULL ,
	[Name] [varchar] (120) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[FileSize] [bigint] NOT NULL ,
	[StoredAs] [varchar] (120) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[UploadDate] [datetime] NOT NULL ,
	[fkCreatorID] [int] NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblFolders] (
	[pkFolderID] [int] IDENTITY (1, 1) NOT NULL ,
	[fkTeamID] [int] NOT NULL ,
	[Name] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CreationDate] [datetime] NOT NULL ,
	[fkCreatorID] [int] NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblMemberHistory] (
	[pkHistoryID] [int] IDENTITY (1, 1) NOT NULL ,
	[fkMemberID] [int] NOT NULL ,
	[fkTeamID] [int] NOT NULL ,
	[Subsystem] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[LastVisit] [datetime] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblMembers] (
	[pkMemberID] [int] IDENTITY (1, 1) NOT NULL ,
	[FirstName] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[LastName] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[UserName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Password] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[DisplayProfile] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[HomePage] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[HomePhone] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[WorkPhone] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[MobilePhone] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Fax] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[AboutMe] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblMessages] (
	[pkMessageID] [int] IDENTITY (1, 1) NOT NULL ,
	[fkMessageToMemberID] [int] NOT NULL ,
	[fkMessageFromMemberID] [int] NOT NULL ,
	[MessageDate] [datetime] NOT NULL ,
	[MessageSubject] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[MessageText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[MessageRead] [bit] NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblPosts] (
	[pkPostID] [int] IDENTITY (1, 1) NOT NULL ,
	[fkAuthorID] [int] NOT NULL ,
	[fkTeamID] [int] NULL ,
	[fkOriginalPostID] [int] NULL ,
	[PostDate] [datetime] NOT NULL ,
	[Subject] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[MessageText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblProjects] (
	[pkProjectID] [int] IDENTITY (1, 1) NOT NULL ,
	[fkTeamID] [int] NOT NULL ,
	[fkCreatorID] [int] NOT NULL ,
	[Name] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[StartDate] [datetime] NOT NULL ,
	[DueDate] [datetime] NOT NULL ,
	[IsCompleted] [bit] NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblTasks] (
	[pkTaskID] [int] IDENTITY (1, 1) NOT NULL ,
	[fkProjectID] [int] NOT NULL ,
	[Name] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[DueDate] [datetime] NOT NULL ,
	[Budget] [money] NOT NULL ,
	[DefaultHourlyRate] [money] NOT NULL ,
	[IsCompleted] [bit] NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblTeamMembers] (
	[pkTeamMemberID] [int] IDENTITY (1, 1) NOT NULL ,
	[fkTeamID] [int] NOT NULL ,
	[fkMemberID] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblTeams] (
	[pkTeamID] [int] IDENTITY (1, 1) NOT NULL ,
	[fkLeaderID] [int] NOT NULL ,
	[Name] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblTimecards] (
	[pkTimecardID] [int] IDENTITY (1, 1) NOT NULL ,
	[fkTaskID] [int] NOT NULL ,
	[fkMemberID] [int] NOT NULL ,
	[TimecardDate] [datetime] NOT NULL ,
	[WorkDescription] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[HourCount] [money] NOT NULL ,
	[HourlyRate] [money] NOT NULL ,
	[IsBilled] [bit] NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblFiles] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblFiles] PRIMARY KEY  CLUSTERED 
	(
		[pkFileID]
	)  ON [PRIMARY] 
GO

if (select DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled')) <> 1 
exec sp_fulltext_database N'enable' 

GO

if not exists (select * from dbo.sysfulltextcatalogs where name = N'TWNWContent')
exec sp_fulltext_catalog N'TWNWContent', N'create' 

GO

exec sp_fulltext_table N'[dbo].[tblFiles]', N'create', N'TWNWContent', N'PK_tblFiles'
GO

exec sp_fulltext_column N'[dbo].[tblFiles]', N'Name', N'add', 1033  
GO

exec sp_fulltext_column N'[dbo].[tblFiles]', N'Description', N'add', 1033  
GO

exec sp_fulltext_table N'[dbo].[tblFiles]', N'activate'  
GO

ALTER TABLE [dbo].[tblFolders] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblFolders] PRIMARY KEY  CLUSTERED 
	(
		[pkFolderID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblMemberHistory] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblMemberHistory] PRIMARY KEY  CLUSTERED 
	(
		[pkHistoryID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblMembers] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblMembers] PRIMARY KEY  CLUSTERED 
	(
		[pkMemberID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblMessages] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblMessages] PRIMARY KEY  CLUSTERED 
	(
		[pkMessageID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblPosts] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblPosts] PRIMARY KEY  CLUSTERED 
	(
		[pkPostID]
	)  ON [PRIMARY] 
GO

if (select DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled')) <> 1 
exec sp_fulltext_database N'enable' 

GO

if not exists (select * from dbo.sysfulltextcatalogs where name = N'TWNWContent')
exec sp_fulltext_catalog N'TWNWContent', N'create' 

GO

exec sp_fulltext_table N'[dbo].[tblPosts]', N'create', N'TWNWContent', N'PK_tblPosts'
GO

exec sp_fulltext_column N'[dbo].[tblPosts]', N'Subject', N'add', 1033  
GO

exec sp_fulltext_column N'[dbo].[tblPosts]', N'MessageText', N'add', 1033  
GO

exec sp_fulltext_table N'[dbo].[tblPosts]', N'activate'  
GO

ALTER TABLE [dbo].[tblProjects] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblProjects] PRIMARY KEY  CLUSTERED 
	(
		[pkProjectID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblTasks] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblTasks] PRIMARY KEY  CLUSTERED 
	(
		[pkTaskID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblTeamMembers] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblTeamMembers] PRIMARY KEY  CLUSTERED 
	(
		[pkTeamMemberID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblTeams] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblTeams] PRIMARY KEY  CLUSTERED 
	(
		[pkTeamID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblTimecards] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblTimecards] PRIMARY KEY  CLUSTERED 
	(
		[pkTimecardID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblFiles] WITH NOCHECK ADD 
	CONSTRAINT [DF_tblFiles_fkFolderID] DEFAULT (0) FOR [fkFolderID]
GO

ALTER TABLE [dbo].[tblMemberHistory] WITH NOCHECK ADD 
	CONSTRAINT [DF_tblMemberHistory_fkTeamID] DEFAULT (0) FOR [fkTeamID],
	CONSTRAINT [DF_tblMemberHistory_LastVisit] DEFAULT (getdate()) FOR [LastVisit]
GO

ALTER TABLE [dbo].[tblMembers] WITH NOCHECK ADD 
	CONSTRAINT [DF_tblMembers_DisplayProfile] DEFAULT ('N') FOR [DisplayProfile]
GO

ALTER TABLE [dbo].[tblMessages] WITH NOCHECK ADD 
	CONSTRAINT [DF_tblMessages_MessageDate] DEFAULT (getdate()) FOR [MessageDate],
	CONSTRAINT [DF_tblMessages_MessageRead] DEFAULT (0) FOR [MessageRead]
GO

ALTER TABLE [dbo].[tblTimecards] WITH NOCHECK ADD 
	CONSTRAINT [DF_tblTimecards_TimecardDate] DEFAULT (getdate()) FOR [TimecardDate]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE VIEW dbo.vw_DiscussionList
AS
SELECT     ISNULL(fkTeamID, 0) AS fkTeamID, COUNT(*) AS TotalMessageCount, MAX(PostDate) AS LastPostDate
FROM         dbo.tblPosts
GROUP BY fkTeamID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE VIEW dbo.vw_SummarizeTasksByProject
AS
SELECT     T.fkProjectID, SUM(V.HoursByTask) AS HoursByProject, SUM(V.TotalByTask) AS TotalByProject
FROM         dbo.tblTasks T INNER JOIN
                      dbo.vw_SummarizeTimecardsByTask V ON T.pkTaskID = V.fkTaskID
GROUP BY T.fkProjectID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE VIEW vw_SummarizeTimecardsByTask
AS
SELECT 
   fkTaskID, 
   SUM(HourCount) As HoursByTask,
   SUM(HourCount * HourlyRate) As TotalByTask
FROM tblTimecards
GROUP BY fkTaskID


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.sp_CheckForDuplicateEmail
@Email varchar(100)
AS
SELECT COUNT(*) As DuplicateCount
FROM tblMembers
WHERE Lower(Email) = Lower(@Email)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.sp_CheckForDuplicateUserName
@UserName varchar(20)
AS
SELECT COUNT(*) As DuplicateCount
FROM tblMembers
WHERE Lower(UserName) = Lower(@UserName)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.sp_CheckForTeamDelete
@TeamID int
AS
SELECT TeamMembers = (SELECT COUNT(*) FROM tblTeamMembers WHERE fkTeamID = @TeamID)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.sp_CheckLogin
@UserName varchar(20),
@Password varchar(20)
AS
SELECT *
FROM tblMembers
WHERE Lower(UserName) = Lower(@UserName)
AND Lower(Password) = Lower(@Password)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE  PROCEDURE dbo.sp_CheckProfileVisibility
@ViewerID int,
@ProfileID int
AS
SELECT 'T' As IsProfileVisible
FROM tblMembers
WHERE pkMemberID = @ProfileID 
AND 
   (DisplayProfile = 'P'
   OR 
   @ViewerID = @ProfileID
   OR
   (DisplayProfile = 'T' AND
   @ViewerID IN 
      (SELECT DISTINCT fkMemberID 
      FROM tblTeamMembers 
      WHERE fkTeamID IN 
         (SELECT DISTINCT fkTeamID 
         FROM tblTeamMembers 
         WHERE fkMemberID = @ProfileID)
         )
      )
   )

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE  PROCEDURE dbo.sp_RetrieveAllMembers
AS
SELECT *, 
LastName + ', ' + FirstName As MemberName,
ProfileVisible = 
	(CASE DisplayProfile 
	WHEN 'P' 
	THEN 'Yes' 
	ELSE 'No' 
	END),
ProfileURL = 
	(CASE DisplayProfile 
	WHEN 'P' 
	THEN '<a href="member_view.aspx?id=' + convert(varchar, pkMemberID) + '">' 
	ELSE '' 
	END)

FROM tblMembers
ORDER BY LastName, FirstName
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO


CREATE  PROCEDURE dbo.sp_RetrieveDiscussionsByMember
@MemberID int
AS
SELECT '<a href="posts_view.aspx?tID=' 
   + convert(varchar, IsNull(fkTeamID, '')) + '">' 
   + IsNull(T.Name, 'Public Forum') 
   + '</a>' As TeamName, V.*
FROM tblTeams T, vw_DiscussionList V
WHERE V.fkTeamID *= T.pkTeamID
AND (V.fkTeamID IN 
   (SELECT fkTeamID FROM tblTeamMembers WHERE fkMemberID = @MemberID)
OR V.fkTeamID = 0)
ORDER BY TeamName
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.sp_RetrieveFilesByFolder
@FolderID int
AS
SELECT *
FROM tblFiles
WHERE fkFolderID = @FolderID
ORDER BY Name
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.sp_RetrieveFilesByTeamByFolder
@TeamID int,
@FolderID int
AS
SELECT *
FROM tblFiles
WHERE fkTeamID = @TeamID
AND fkFolderID = @FolderID
ORDER BY Name
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.sp_RetrieveFolderStatistics
@FolderID int
AS
SELECT COUNT(*) As TotalFileCount,
Max(UploadDate) As LastUploadDate
FROM tblFiles
WHERE fkFolderID = @FolderID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.sp_RetrieveFoldersByTeam
@TeamID int
AS
SELECT *
FROM tblFolders
WHERE fkTeamID = @TeamID
ORDER BY Name
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.sp_RetrieveMessageCountByMember
@MemberID int
AS
SELECT NewCount = 
   (SELECT COUNT(*) FROM tblMessages 
   WHERE fkMessageToMemberID = @MemberID AND MessageRead = 0),
OldCount = 
   (SELECT COUNT(*) FROM tblMessages 
   WHERE fkMessageToMemberID = @MemberID And MessageRead = 1)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.sp_RetrieveMessagesByMember
@MemberID int
AS
SELECT MSG.*,
	M.LastName + ', ' + M.FirstName As MessageFrom,
	MessageIndicator = (CASE MessageRead WHEN 0 THEN 'newmessage.gif'  ELSE 'spacer.gif' END),
	MessageIndicatorText = (CASE MessageRead WHEN 0 THEN 'New Message'  ELSE '' END)
FROM tblMembers M, tblMessages MSG
WHERE MSG.fkMessageFromMemberID = M.pkMemberID
AND MSG.fkMessageToMemberID = @MemberID
ORDER BY MSG.MessageDate DESC
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.sp_RetrieveNewFilesByMember
@MemberID int,
@TeamID int
AS
DECLARE @LastVisit datetime
SELECT @LastVisit = LastVisit
FROM tblMemberHistory
WHERE fkTeamID = @TeamID
AND fkMemberID = @MemberID
AND Subsystem = 'F'

SELECT COUNT(*) AS NewFileCount,
MAX(UploadDate) As LastUploadDate
FROM tblFiles
WHERE fkTeamID = @TeamID
AND UploadDate >= IsNull(@LastVisit, '1-1-1900 12:00 AM')
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.sp_RetrieveNewPostsByMember
@MemberID int,
@TeamID int
AS
DECLARE @LastVisit datetime
SELECT @LastVisit = LastVisit
FROM tblMemberHistory
WHERE fkTeamID = @TeamID
AND fkMemberID = @MemberID
AND Subsystem = 'D'

SELECT COUNT(*) AS NewPostCount
FROM tblPosts
WHERE fkTeamID = @TeamID
AND PostDate >= IsNull(@LastVisit, '1-1-1900 12:00 AM')
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE procedure sp_RetrieveOriginalPostsByTeam
@TeamID int
AS
select M.LastName + ', ' + M.FirstName As Author,
   '<a href="posts_read.aspx?pID=' 
   + convert(varchar, pkPostID)
   + '&tID=' + convert(varchar, @TeamID)
   + '">' 
   + P.Subject
   + '</a>' As Subject,
   P.*
FROM tblPosts P, tblMembers M
WHERE IsNull(fkTeamID, 0) = @TeamID
AND P.fkAuthorID = M.pkMemberID
AND fkOriginalPostID IS NULL
ORDER BY PostDate DESC
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.sp_RetrieveProjectCountByTeam
@TeamID int
AS
SELECT COUNT(*) AS Total
FROM tblProjects
WHERE fkTeamID = @TeamID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE    procedure sp_RetrieveProjectDetails
@ProjectID int
AS
SELECT
   P.pkProjectID,
   P.fkTeamID,
   P.fkCreatorID,
   P.Name,
   M.LastName + ', ' + M.FirstName As ProjectLead,
   P.Description,
   P.StartDate,
   P.DueDate,
   P.IsCompleted,
   CompletedStatus = (CASE WHEN IsCompleted = 1 THEN 'Yes' ELSE 'No' END),
   TaskCount = (SELECT COUNT(*) FROM tblTasks WHERE fkProjectID = @ProjectID),
   ProjectBudget = CONVERT(varchar, (SELECT SUM(Budget) FROM tblTasks WHERE fkProjectID = @ProjectID), 1), 
   ProjectHours = IsNull((SELECT HoursByProject FROM vw_SummarizeTasksByProject WHERE fkProjectID = @ProjectID), 0), 
   ProjectTotal = CONVERT(varchar, IsNull((SELECT TotalByProject FROM vw_SummarizeTasksByProject WHERE fkProjectID = @ProjectID), 0), 1)
FROM tblProjects P, tblMembers M
WHERE P.fkCreatorID = M.pkMemberID
AND P.pkProjectID = @ProjectID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.sp_RetrieveProjectsByTeam
@TeamID int
AS
SELECT 
   '<a href="project_view.aspx?id=' 
   + convert(varchar, pkProjectID) 
   + '">' 
   + P.Name 
   + '</a>' As ProjectName,
P.*, M.LastName + ', ' + M.FirstName As ProjectLead
FROM tblProjects P, tblMembers M
WHERE fkTeamID = @TeamID
AND fkCreatorID = M.pkMemberID
ORDER BY P.Name
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.sp_RetrieveStatisticsByPost
@PostID int
AS
SELECT COUNT(*) As ReplyCount, 
Max(PostDate) As LastReplyDate
FROM tblPosts
WHERE fkOriginalPostID = @PostID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE   procedure sp_RetrieveTaskDetails
@TaskID int
as
select 
   T.*,
   P.fkCreatorID, 
   TaskBudget = CONVERT(varchar, Budget, 1),
   TaskHours = IsNull((SELECT HoursByTask FROM vw_SummarizeTimecardsByTask WHERE fkTaskID = @TaskID), 0), 
   TaskTotal = CONVERT(varchar, IsNull((SELECT TotalByTask FROM vw_SummarizeTimecardsByTask WHERE fkTaskID = @TaskID), 0), 1),
   TimecardCount = (SELECT COUNT(*) FROM tblTimecards WHERE fkTaskID = @TaskID)
FROM tblTasks T, tblProjects P
WHERE T.pkTaskID = @TaskID
AND T.fkProjectID = P.pkProjectID




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE procedure sp_RetrieveTaskStatistics
@TaskID int
as
SELECT IsNull(HoursByTask, 0) As TaskHours, IsNull(TotalByTask, 0) As TaskMoney
FROM vw_SummarizeTimecardsByTask
WHERE fkTaskID = @TaskID


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE     PROCEDURE dbo.sp_RetrieveTasksByProject
@ProjectID int
AS
SELECT
   T.pkTaskID,
   T.fkProjectID,
   T.Name,
   '<a href="task_view.aspx?id=' 
      + convert(varchar, T.pkTaskID) 
      + '">' 
      + T.Name 
      + '</a>' As TaskNameWithLink,
   T.Description,
   T.DueDate,
   DueDateStatus = 
      (CASE 
         WHEN DueDate < getdate() 
         THEN '#FF0000' 
         ELSE '#000000' 
      END),
   T.Budget,
   TaskStatus = (CASE WHEN T.IsCompleted = 1 THEN 'Yes' ELSE 'No' END)
FROM tblTasks T
WHERE T.fkProjectID = @ProjectID
ORDER BY DueDate
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.sp_RetrieveTeamMembers
@TeamID int
AS
SELECT pkTeamMemberID, 
M.LastName + ', ' + M.FirstName As MemberName,
M.UserName As UserName
FROM tblMembers M, tblTeamMembers TM
WHERE TM.fkTeamID = @TeamID
AND TM.fkMemberID = M.pkMemberID
ORDER BY M.LastName, M.FirstName
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.sp_RetrieveTeamsByMember
@MemberID int
AS
SELECT *
FROM tblTeams
WHERE fkLeaderID = @MemberID
OR pkTeamID IN (SELECT fkTeamID FROM tblTeamMembers WHERE fkMemberID = @MemberID)
ORDER BY Name
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE procedure sp_RetrieveThreadByPostID
@PostID int
as
select '<a href="member_view.aspx?id=' 
   + convert(varchar, M.pkMemberID) 
   + '">' +  M.LastName + ', ' + M.FirstName + '</a>' As Author,
   P.*
FROM tblPosts P, tblMembers M
WHERE P.fkAuthorID = M.pkMemberID
AND (pkPostID = @PostID OR fkOriginalPostID = @PostID)
ORDER BY PostDate ASC
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE sp_RetrieveTimecardsByTask
@TaskID int,
@MemberID int
as
SELECT T.pkTimecardID, 
M.LastName + ', ' + M.FirstName As MemberName,
CardDate = 
   (CASE 
    WHEN IsBilled = 0 AND 
   (T.fkMemberID = @MemberID OR 
    fkTaskID IN 
   (SELECT pkTaskID FROM tblTasks WHERE fkProjectID IN 
      (SELECT pkProjectID FROM tblProjects WHERE fkCreatorID = @MemberID)))
   THEN
   '<a href="tcard_create.aspx?id=' + convert(varchar, pkTimecardID) + '">' + convert(varchar, TimecardDate, 101) + '</a>'
   ELSE
    convert(varchar, TimecardDate, 101) 
   END),
WorkDescription,
HourCount,
convert(varchar, HourlyRate, 1) as Rate,
IsBilled,
BilledStatus = (CASE WHEN T.IsBilled = 0 THEN 'No' ELSE 'Yes' END)
FROM tblTimecards T, tblMembers M
WHERE T.fkMemberID = M.pkMemberID
AND T.fkTaskID = @TaskID
ORDER BY TimecardDate
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.sp_RetrieveTotalFilesByTeam
@TeamID int
AS
SELECT COUNT(*) AS TotalFileCount,
MAX(UploadDate) As LastUploadDate
FROM tblFiles
WHERE fkTeamID = @TeamID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE sp_RetrieveUnbilledTimecardsByProject
@ProjectID int
AS
SELECT 
   TC.TimecardDate,
   M.LastName + ', ' + M.FirstName As MemberName,
   T.Name As TaskName, 
   TC.WorkDescription,
   TC.HourCount,
   CONVERT(varchar, TC.HourlyRate, 1) As Rate,
   CONVERT(varchar, TC.HourlyRate * TC.HourCount, 1) As TimecardTotal
FROM tblTimecards TC, tblTasks T, tblMembers M
WHERE fkTaskID IN 
   (SELECT pkTaskID FROM tblTasks WHERE fkProjectID = @ProjectID)
AND TC.fkTaskID = T.pkTaskID
AND TC.fkMemberID = M.pkMemberID
AND IsBilled = 0


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE  PROCEDURE sp_RetrieveUnbilledTotalByProject
@ProjectID int
AS
SELECT CONVERT(varchar, IsNull(SUM(TC.HourlyRate * TC.HourCount), 0), 1) As InvoiceTotal
FROM tblTimecards TC
WHERE fkTaskID IN 
   (SELECT pkTaskID FROM tblTasks WHERE fkProjectID = @ProjectID)
AND IsBilled = 0



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE sp_SearchFiles
@MemberID int,
@Keywords varchar(200)
AS
SELECT *
FROM tblFiles
WHERE (Description LIKE '%' + @Keywords + '%'
OR Name LIKE '%' + @Keywords + '%')
AND (fkTeamID IN 
   (SELECT fkTeamID FROM tblTeamMembers WHERE fkMemberID = @MemberID)
   OR fkTeamID IS NULL OR fkTeamID = 0)
ORDER BY UploadDate DESC

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE  PROCEDURE sp_SearchPosts
@MemberID int,
@Keywords varchar(200)
AS
SELECT *
FROM tblPosts
WHERE (MessageText LIKE '%' + @Keywords + '%'
OR Subject LIKE '%' + @Keywords + '%')
AND (fkTeamID IN 
   (SELECT fkTeamID FROM tblTeamMembers WHERE fkMemberID = @MemberID)
   OR fkTeamID IS NULL OR fkTeamID = 0)
ORDER BY PostDate DESC



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.sp_UpdateMemberDiscussionHistory
@MemberID int,
@TeamID int
AS
DECLARE @RecordCount int
SELECT @RecordCount = COUNT(*) FROM tblMemberHistory
WHERE fkMemberID = @MemberID
AND fkTeamID = @TeamID
AND Subsystem = 'D'

IF @RecordCount > 0
BEGIN
   UPDATE tblMemberHistory
   SET LastVisit = getdate()
   WHERE fkMemberID = @MemberID
   AND fkTeamID = @TeamID
   AND Subsystem = 'D'
END
ELSE
BEGIN
   INSERT INTO tblMemberHistory
   (fkMemberID, fkTeamID, Subsystem)
   VALUES
   (@MemberID, @TeamID, 'D')
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.sp_UpdateMemberFileHistory
@MemberID int,
@TeamID int
AS
DECLARE @RecordCount int
SELECT @RecordCount = COUNT(*) FROM tblMemberHistory
WHERE fkMemberID = @MemberID
AND fkTeamID = @TeamID
AND Subsystem = 'F'

IF @RecordCount > 0
BEGIN
   UPDATE tblMemberHistory
   SET LastVisit = getdate()
   WHERE fkMemberID = @MemberID
   AND fkTeamID = @TeamID
   AND Subsystem = 'F'
END
ELSE
BEGIN
   INSERT INTO tblMemberHistory
   (fkMemberID, fkTeamID, Subsystem)
   VALUES
   (@MemberID, @TeamID, 'F')
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE sp_UpdateTimecardsByProject
@ProjectID int
AS
UPDATE tblTimecards
SET IsBilled = 1
WHERE IsBilled = 0
AND fkTaskID IN 
   (SELECT pkTaskID FROM tblTasks WHERE fkProjectID = @ProjectID)


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

