CREATE TABLE #PageIndex 
(
	IndexID int IDENTITY (1, 1) NOT NULL,
	PostID int
)

-- Sort by Post Date
IF @SortBy = 0 AND @SortOrder = 0
    INSERT INTO #PageIndex (PostID)
    SELECT PostID FROM cs_Posts P (nolock) WHERE IsApproved = 0 AND SectionID = @SectionID and SettingsID = @SettingsID ORDER BY PostDate

ELSE IF @SortBy = 0 AND @SortOrder = 1
    INSERT INTO #PageIndex (PostID)
    SELECT PostID FROM cs_Posts P (nolock) WHERE IsApproved = 0 AND SectionID = @SectionID and SettingsID = @SettingsID ORDER BY PostDate DESC

-- Select the individual posts
SELECT
	P.PostID, P.ThreadID, P.ParentID, P.PostAuthor, P.UserID, P.SectionID, P.PostLevel, P.SortOrder, P.Subject, P.PostDate, P.IsApproved,
	P.IsLocked, P.IsIndexed, P.TotalViews, P.Body, P.FormattedBody, P.IPAddress, P.PostType, P.PostMedia, P.EmoticonID, P.SettingsID, P.AggViews,
	P.PropertyNames as PostPropertyNames, P.PropertyValues as PostPropertyValues,
	P.PostConfiguration,P.UserTime, P.ApplicationPostType, P.PostName, P.PostStatus, P.SpamScore,
	P.Points as PostPoints, P.RatingSum as PostRatingSum, P.TotalRatings as PostTotalRatings,
	T.*, U.*, #PageIndex.*,
	T.IsLocked,
	T.IsSticky,
	Username = P.PostAuthor,
	EditNotes = (SELECT EditNotes FROM cs_PostEditNotes WHERE PostID = P.PostID),
	AttachmentFilename = ISNULL ( (SELECT [FileName] FROM cs_PostAttachments WHERE PostID = P.PostID), ''),
	Replies = (SELECT COUNT(P2.PostID) FROM cs_Posts P2 (nolock) WHERE P2.ParentID = P.PostID AND P2.PostLevel != 1),
	IsModerator = (SELECT count(UserID) from cs_Moderators where UserID = @UserID),
	HasRead = 0 -- not used
FROM 
	cs_Posts P (nolock),
	cs_Threads T,
	cs_vw_Users_FullUser U,
	#PageIndex
WHERE 
	P.PostID = #PageIndex.PostID AND
	P.UserID = U.UserID AND
	T.ThreadID = P.ThreadID AND
	#PageIndex.IndexID > @PageLowerBound AND
	#PageIndex.IndexID < @PageUpperBound and U.SettingsID = @SettingsID
ORDER BY
	IndexID
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[cs_forums_Moderate_PostSet]  TO [public]
GO


/***********************************************
* Sproc: cs_forums_Post
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_forums_Post'

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE dbo.cs_forums_Post
(
	@PostID int,
	@UserID int,
	@TrackViews bit,
	@SettingsID int,
	@MarkRead bit = 0,
	@IncludeCategories bit = 0
) 
AS

/* Procedure for getting basic information on a single post. */

SET Transaction Isolation Level Read UNCOMMITTED

DECLARE @NextThreadID int
DECLARE @PrevThreadID int
DECLARE @ThreadID int 
DECLARE @SectionID int
DECLARE @SortOrder int
DECLARE @IsApproved bit
DECLARE @IsAnonymousUser bit

SELECT 
	@ThreadID = ThreadID, 
	@SectionID = SectionID, 
	@SortOrder=SortOrder,
	@IsApproved = IsApproved
FROM 
	cs_Posts (nolock) 
WHERE 
	PostID = @PostID and SettingsID = @SettingsID

-- Do we have an anonymous user?
SET @IsAnonymousUser = (SELECT IsAnonymous FROM cs_vw_Users_FullUser WHERE UserID = @UserID and SettingsID = @SettingsID)

DECLARE @TrackingThread bit

IF @TrackViews = 1
BEGIN
	-- Update the counter for the number of times this post is viewed
	UPDATE cs_Posts SET TotalViews = (TotalViews + 1) WHERE PostID = @PostID and SettingsID = @SettingsID
	UPDATE cs_Threads SET TotalViews = (TotalViews + 1) WHERE ThreadID = @ThreadID and SettingsID = @SettingsID
END

-- Mark the post as read if this user is not anonymous
IF @MarkRead = 1 AND @IsAnonymousUser = 0 AND @IsApproved = 1
	EXECUTE cs_MarkThreadAsRead @ThreadID, @SectionID, @UserID, @SettingsID

---- get the anonymous user id for this site
--if( @UserID = 0 ) 
--BEGIN
--	exec cs_GetAnonymousUserID @SettingsID, @UserID output
--END
--
--
--IF EXISTS(SELECT ThreadID FROM cs_TrackedThreads (nolock) WHERE ThreadID = @ThreadID AND UserID=@UserID)
--	SELECT @TrackingThread = 1
--ELSE
--	SELECT @TrackingThread = 0

-- Get tracking thread information
IF (@IsAnonymousUser = 0) AND EXISTS(SELECT ThreadID FROM cs_TrackedThreads WHERE ThreadID = @ThreadID AND UserID=@UserID)
	SET @TrackingThread = 1
ELSE
	SET @TrackingThread = 0

SELECT
	P.PostID, P.ThreadID, P.ParentID, P.PostAuthor, P.UserID, P.SectionID, P.PostLevel, P.SortOrder, P.Subject, P.PostDate, P.IsApproved,
	P.IsLocked, P.IsIndexed, P.TotalViews, P.Body, P.FormattedBody, P.IPAddress, P.PostType, P.PostMedia, P.EmoticonID, P.SettingsID, P.AggViews,
	P.PostPropertyNames as PostPropertyNames, P.PostPropertyValues as PostPropertyValues,
	P.PostConfiguration,P.UserTime, P.ApplicationPostType, P.PostName, P.PostStatus, P.SpamScore,
	P.Points as PostPoints, P.RatingSum as PostRatingSum, P.TotalRatings as PostTotalRatings,
	U.*, P.PostAuthor as [Username],
	T.ThreadDate,
	T.StickyDate,
	T.IsLocked,
	T.IsSticky,
	T.RatingSum,
	T.TotalRatings,
	HasRead = 0,
	EditNotes = (SELECT EditNotes FROM cs_PostEditNotes WHERE PostID = P.PostID),
	IndexInThread = (SELECT Count(PostID) FROM cs_Posts P1 WHERE IsApproved = 1 AND ThreadID = @ThreadID AND SortOrder <= (SELECT SortOrder FROM cs_Posts where PostID = @PostID)),
	AttachmentFilename,ContentType, IsRemote, FriendlyFileName, ContentSize, [FileName],P.Created, Height, Width,
--	AttachmentFilename = ISNULL ( (SELECT [FileName] FROM cs_PostAttachments WHERE PostID = P.PostID), ''),
	IsModerator = (SELECT Count(*) FROM cs_Moderators WHERE UserID = U.UserID),
	Replies = (SELECT COUNT(*) FROM cs_Posts P2 (nolock) WHERE P2.ParentID = P.PostID AND P2.PostLevel != 1),
	PrevThreadID = 0,
	NextThreadID = 0,
	UserIsTrackingThread = @TrackingThread
FROM 
	cs_vw_PostsWithAttachmentDetails P,
	--cs_Posts P,
	cs_Threads T,
	cs_vw_Users_FullUser U
WHERE 
	P.PostID = @PostID AND
	P.ThreadID = T.ThreadID AND
	P.UserID = U.UserID and P.SettingsID = @SettingsID and U.SettingsID = @SettingsID


IF @IncludeCategories = 1
BEGIN
	SELECT 
		Cats.[Name]
	FROM 
		cs_Posts_InCategories PIC
		JOIN cs_Post_Categories Cats ON PIC.CategoryID = Cats.CategoryID
	WHERE 
		PIC.PostID = @PostID
End
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[cs_forums_Post]  TO [public]
GO


/***********************************************
* Sproc: cs_forums_Posts_PostSet
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_forums_Posts_PostSet'

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE PROCEDURE dbo.cs_forums_Posts_PostSet
(
	@PostID	int,
	@PageIndex int,
	@PageSize int,
	@SortBy int,
	@SortOrder bit,
	@UserID int,
	@ReturnRecordCount bit,
	@AllowUnapproved bit = 0,
	@SettingsID int,
	@IncludeCategories bit = 0
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN

DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @ThreadID int
DECLARE @SectionID int

-- First set the rowcount
DECLARE @RowsToReturn int
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET ROWCOUNT @RowsToReturn

-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1

-- Get the ThreadID
SELECT
	@ThreadID = ThreadID,
	@SectionID = SectionID
FROM 
	cs_Posts 
WHERE 
	PostID = @PostID and SettingsID = @SettingsID

-- Create a temp table to store the select results
CREATE TABLE #PageIndex 
(
	IndexID int IDENTITY (1, 1) NOT NULL,
	PostID int
)

-- Sort by Post Date
IF @SortBy = 0 AND @SortOrder = 0
    INSERT INTO #PageIndex (PostID)
    SELECT PostID FROM cs_Posts (nolock) WHERE (IsApproved = 1 OR 1 = @AllowUnapproved) AND ThreadID = @ThreadID and SettingsID = @SettingsID ORDER BY PostDate

ELSE IF @SortBy = 0 AND @SortOrder = 1
    INSERT INTO #PageIndex (PostID)
    SELECT PostID FROM cs_Posts (nolock) WHERE (IsApproved = 1 OR 1 = @AllowUnapproved) AND ThreadID = @ThreadID and SettingsID = @SettingsID  ORDER BY PostDate DESC

-- Sort by Author
IF @SortBy = 1 AND @SortOrder = 0
    INSERT INTO #PageIndex (PostID)
    SELECT PostID FROM cs_Posts (nolock) WHERE (IsApproved = 1 OR 1 = @AllowUnapproved) AND ThreadID = @ThreadID and SettingsID = @SettingsID  ORDER BY UserID

ELSE IF @SortBy = 1 AND @SortOrder = 1
    INSERT INTO #PageIndex (PostID)
    SELECT PostID FROM cs_Posts (nolock) WHERE (IsApproved = 1 OR 1 = @AllowUnapproved) AND ThreadID = @ThreadID and SettingsID = @SettingsID  ORDER BY UserID DESC

-- Sort by SortOrder
IF @SortBy = 2 AND @SortOrder = 0
    INSERT INTO #PageIndex (PostID)
    SELECT PostID FROM cs_Posts (nolock) WHERE (IsApproved = 1 OR 1 = @AllowUnapproved) AND ThreadID = @ThreadID and SettingsID = @SettingsID  ORDER BY SortOrder

ELSE IF @SortBy = 2 AND @SortOrder = 1
    INSERT INTO #PageIndex (PostID)
    SELECT PostID FROM cs_Posts (nolock) WHERE (IsApproved = 1 OR 1 = @AllowUnapproved) AND ThreadID = @ThreadID and SettingsID = @SettingsID  ORDER BY SortOrder DESC

-- Select the individual posts
SELECT
	P.PostID, P.ThreadID, P.ParentID, P.PostAuthor, P.UserID, P.SectionID, P.PostLevel, P.SortOrder, P.Subject, P.PostDate, P.IsApproved,
	P.IsLocked, P.IsIndexed, P.TotalViews, P.Body, P.FormattedBody, P.IPAddress, P.PostType, P.PostMedia, P.EmoticonID, P.SettingsID, P.AggViews,
	P.PropertyNames as PostPropertyNames, P.PropertyValues as PostPropertyValues,
	P.PostConfiguration, P.UserTime, P.ApplicationPostType, P.PostName, P.PostStatus, P.SpamScore,
	P.Points as PostPoints, P.RatingSum as PostRatingSum, P.TotalRatings as PostTotalRatings,
	T.*, U.*, #PageIndex.*,
	T.IsLocked,
	T.IsSticky,
	Username = P.PostAuthor,
	ThreadStarterAuthor = T.PostAuthor,
	ThreadStartDate = T.PostDate,	
	EditNotes = (SELECT EditNotes FROM cs_PostEditNotes WHERE PostID = P.PostID),
	AttachmentFilename = ISNULL ( (SELECT [FileName] FROM cs_PostAttachments WHERE PostID = P.PostID), ''),
	Replies = 0, --not used(SELECT COUNT(P2.PostID) FROM cs_Posts P2 (nolock) WHERE P2.ParentID = P.PostID AND P2.PostLevel != 1),
	IsModerator = 0, -- not used
	HasRead = 0 -- not used
FROM 
	cs_Posts P (nolock),
	cs_Threads T,
	cs_vw_Users_FullUser U,
	#PageIndex
WHERE 
	P.PostID = #PageIndex.PostID AND
	P.UserID = U.UserID AND
	T.ThreadID = P.ThreadID AND
	#PageIndex.IndexID > @PageLowerBound AND
	#PageIndex.IndexID < @PageUpperBound and U.SettingsID = @SettingsID
	AND (U.IsIgnored = 0 OR U.UserID = @UserID)	-- Ensure that you are not getting threads by ignored users unless the ignored user is requesting them
ORDER BY
	IndexID
END

IF @IncludeCategories = 1
BEGIN
	SET ROWCOUNT 0
	SELECT 
		Cats.[Name], jPI.PostID
	FROM 
		#PageIndex jPI
		JOIN cs_Posts_InCategories PIC ON jPI.PostID = PIC.PostID
		JOIN cs_Post_Categories Cats ON PIC.CategoryID = Cats.CategoryID
	WHERE 
		jPI.IndexID > @PageLowerBound
		AND jPI.IndexID < @PageUpperBound
		AND  PIC.SettingsID = @SettingsID
End

IF @ReturnRecordCount = 1
  SELECT count(PostID) FROM cs_Posts (nolock) WHERE (IsApproved = 1 OR 1 = @AllowUnapproved) AND ThreadID = @ThreadID  and SettingsID = @SettingsID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[cs_forums_Posts_PostSet]  TO [public]
GO


/***********************************************
* Sproc: cs_forums_Search
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_forums_Search'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_forums_Search]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_forums_Search]
GO
/***********************************************
* Sproc: cs_forums_Threads_ThreadsRead
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_forums_Threads_ThreadsRead'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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



CREATE procedure [dbo].cs_forums_threads_ThreadsRead (
	@SectionID as int,
	@UserID as int,
	@SettingsID as int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
DECLARE @ReadAfter int

SELECT 
	@ReadAfter = MarkReadAfter 
FROM 
	cs_SectionsRead 
WHERE 
	UserID = @UserID AND 
	SectionID = @SectionID AND
	SettingsID = @SettingsID

IF @ReadAfter IS NOT NULL
	SELECT ThreadID = @ReadAfter
ELSE
	SELECT ThreadID = 0

IF @SectionID = -1
BEGIN
SELECT DISTINCT
	ThreadID
FROM
	cs_ThreadsRead
WHERE
	SettingsID = @SettingsID
	AND UserID = @UserID
END
ELSE
BEGIN
SELECT DISTINCT
	ThreadID
FROM
	cs_ThreadsRead
WHERE
	UserID = @UserID AND
	SectionID = @SectionID AND
	SettingsID = @SettingsID
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[cs_forums_threads_ThreadsRead]  TO [public]
GO


/***********************************************
* Sproc: cs_forums_Thread_Get
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_forums_Thread_Get'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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



CREATE PROCEDURE [dbo].cs_forums_Thread_Get
/* Procedure for getting basic information on a single thread. */
(
	@ThreadID int,
	@UserID int,
	@SettingsID int
) AS
SET Transaction Isolation Level Read UNCOMMITTED
SELECT
	T.*,
	P.PostID,
	P.Subject,
	P.Body,P.FormattedBody,
	P.PostMedia,
	P.PostType,
	P.PostConfiguration,
	P.PropertyNames as PostPropertyNames,
	P.PropertyValues as PostPropertyValues,
	UserName = T.PostAuthor,
	HasRead =  CASE
		WHEN @UserID = 0 THEN 0
		WHEN @UserID > 0 THEN (Select Convert(bit,Count(*)) FROM cs_vw_HasReadForum where ((ThreadID is null and MarkReadAfter > P.ThreadID) or (MarkReadAfter is null and ThreadID = P.ThreadID)) and UserID = @UserID and SectionID = P.SectionID)
		END
FROM 
	cs_Posts P,
	cs_Threads T,
	cs_vw_Users_FullUser U
WHERE 
	P.PostID = P.ParentID AND
	P.ThreadID = @ThreadID AND
	T.ThreadID = P.ThreadID AND
	P.UserID = U.UserID and P.SettingsID = @SettingsID


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].cs_forums_Thread_Get to public
go
/***********************************************
* Sproc: cs_fourm_Feedback_Get
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_fourm_Feedback_Get'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

/***********************************************
* Sproc: cs_FriendRequest_Delete
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_FriendRequest_Delete'

IF OBJECT_ID(N'[dbo].[cs_FriendRequest_Delete]') IS NOT NULL
	DROP PROCEDURE [dbo].[cs_FriendRequest_Delete]

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[cs_FriendRequest_Delete]
( 
	@UserID int,
	@FriendID int  
)

AS

SET NOCOUNT ON;

BEGIN
	DELETE FROM	[dbo].[cs_FriendRequests]
	WHERE		(RequestorUserID = @UserID AND RequesteeUserID = @FriendID)
	OR			(RequestorUserID = @FriendID AND RequesteeUserID = @UserID)

	
	DELETE FROM	[dbo].[cs_Friends_xref] 
	WHERE		UserID = @UserID AND FriendUserID = @FriendID
	
	
	DELETE FROM	[dbo].[cs_Friends_xref] 
	WHERE		UserID = @FriendID AND FriendUserID = @UserID
	
	RETURN 0;

END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
/***********************************************
* Sproc: cs_FriendRequest_DeleteByID
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_FriendRequest_DeleteByID'

IF OBJECT_ID(N'[dbo].[cs_FriendRequest_DeleteByID]') IS NOT NULL
	DROP PROCEDURE [dbo].[cs_FriendRequest_DeleteByID]

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[cs_FriendRequest_DeleteByID]
( 
	@FriendshipRequestID int 
)

AS

SET NOCOUNT ON;

BEGIN
	DECLARE @UserID int, @FriendID int 	
	
	SELECT @UserID = RequestorUserID FROM [dbo].[cs_FriendRequests] WHERE FriendshipRequestID = @FriendshipRequestID
	SELECT @FriendID = RequesteeUserID FROM [dbo].[cs_FriendRequests] WHERE FriendshipRequestID = @FriendshipRequestID
	
	DELETE FROM	[dbo].[cs_FriendRequests]
	WHERE		FriendshipRequestID = @FriendshipRequestID

	DELETE FROM	[dbo].[cs_Friends_xref] 
	WHERE		UserID = @UserID AND FriendUserID = @FriendID
	
	DELETE FROM	[dbo].[cs_Friends_xref] 
	WHERE		UserID = @FriendID AND FriendUserID = @UserID	

	RETURN 0;

END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
/***********************************************
* Sproc: cs_FriendRequest_Insert
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_FriendRequest_Insert'

IF OBJECT_ID(N'[dbo].[cs_FriendRequest_Insert]') IS NOT NULL
	DROP PROCEDURE [dbo].[cs_FriendRequest_Insert]

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[cs_FriendRequest_Insert]
(
	@RequestorUserID	int,
	@RequesteeUserID	int,
	@Message	varchar(2000),
	@FriendshipState			tinyint,
	@FriendshipRequestID				int out
)

AS
BEGIN

	SET Transaction Isolation Level Read UNCOMMITTED
	SET NOCOUNT ON;

	IF NOT EXISTS
	(
		SELECT 1 FROM [dbo].[cs_FriendRequests]
		WHERE	(RequestorUserID = @RequestorUserID	OR RequesteeUserID = @RequestorUserID)
			AND (RequestorUserID = @RequesteeUserID OR RequesteeUserID = @RequesteeUserID)
	)
	BEGIN

		INSERT INTO		[dbo].[cs_FriendRequests]
		(
			RequestorUserID,
			RequesteeUserID,
			Message,
			FriendshipState
		)
		VALUES
		(
			@RequestorUserID,
			@RequesteeUserID,
			@Message,
			@FriendshipState
		)

		SET @FriendshipRequestID = SCOPE_IDENTITY();

		EXECUTE [cs_FriendRequest_xref_Insert] @RequestorUserID, @RequesteeUserID, @FriendshipState

		RETURN 0;

	END ELSE BEGIN

		RETURN 1;

	END

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/***********************************************
* Sproc: cs_FriendRequest_Save
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_FriendRequest_Save'

IF OBJECT_ID(N'[dbo].[cs_FriendRequest_Save]') IS NOT NULL
	DROP PROCEDURE [dbo].[cs_FriendRequest_Save]

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[cs_FriendRequest_Save]
(
	@ID int,
	@RequestorUserID	int, 
	@RequesteeUserID	int, 
	@Message	varchar(2000), 
	@FriendshipState			tinyint,
	@FriendshipRequestID				int out
)

AS

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON;

BEGIN
IF @ID = 0
	BEGIN

	SELECT	@ID = ISNULL(FriendshipRequestID,0)
	FROM	[dbo].[cs_FriendRequests]
	WHERE	(RequestorUserID = @RequestorUserID OR RequesteeUserID = @RequestorUserID)
	AND		(RequestorUserID = @RequesteeUserID OR RequesteeUserID = @RequesteeUserID)
	
	END
	
	IF @ID = 0
	BEGIN
		EXEC cs_FriendRequest_Insert @RequestorUserID, @RequesteeUserID, @Message, @FriendshipState, @FriendshipRequestID out
	END
	ELSE
	BEGIN
		EXEC cs_FriendRequest_UpdateByID @ID, @RequestorUserID, @RequesteeUserID, @Message, @FriendshipState
		SET @FriendshipRequestID = @ID
	END
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
/***********************************************
* Sproc: cs_FriendRequest_SelectBetweenUsers
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_FriendRequest_SelectBetweenUsers'

IF OBJECT_ID(N'[dbo].[cs_FriendRequest_SelectBetweenUsers]') IS NOT NULL
	DROP PROCEDURE [dbo].[cs_FriendRequest_SelectBetweenUsers]

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[cs_FriendRequest_SelectBetweenUsers]
(
	@UserID1 int, 
	@UserID2 int
)

AS

SET Transaction Isolation Level Read UNCOMMITTED
SET NOCOUNT ON;

BEGIN
	SELECT		
		FR.FriendshipRequestID, 
		FR.RequestorUserID, 
		FR.RequesteeUserID, 
		FR.FriendshipState, 
		FR.[Message], 
		FR.CreatedDate, 
		FR.LastModifiedDate
	FROM [dbo].[cs_FriendRequests] FR
	WHERE (FR.RequestorUserID = @UserID1 AND FR.RequesteeUserID = @UserID2)
	   OR (FR.RequesteeUserID = @UserID1 AND FR.RequestorUserID = @UserID2)
	
	RETURN 0;			
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
/***********************************************
* Sproc: cs_FriendRequest_SelectByID
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_FriendRequest_SelectByID'

IF OBJECT_ID(N'[dbo].[cs_FriendRequest_SelectByID]') IS NOT NULL
	DROP PROCEDURE [dbo].[cs_FriendRequest_SelectByID]

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[cs_FriendRequest_SelectByID]
(
	@FriendshipRequestID int
)

AS

SET Transaction Isolation Level Read UNCOMMITTED
SET NOCOUNT ON;

BEGIN
	SELECT		
		FriendshipRequestID, 
		RequestorUserID, 
		RequesteeUserID, 
		FriendshipState, 
		Message, 
		CreatedDate, 
		LastModifiedDate
	FROM		[dbo].[cs_FriendRequests]
	WHERE		FriendshipRequestID = @FriendshipRequestID
	
	RETURN 0;			
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
/***********************************************
* Sproc: cs_FriendRequest_SelectByRequesteeUserID
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_FriendRequest_SelectByRequesteeUserID'

IF OBJECT_ID(N'[dbo].[cs_FriendRequest_SelectByRequesteeUserID]') IS NOT NULL
	DROP PROCEDURE [dbo].[cs_FriendRequest_SelectByRequesteeUserID]
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[cs_FriendRequest_SelectByRequesteeUserID]
(
	@UserID int, 
	@FriendshipState tinyint = NULL,
	@sortOrder int,
	@sortBy int,
	@pageIndex int,
        @pageSize int,
        @totalCount int output
)

AS


DECLARE @rowsToReturn int
DECLARE @pageLowerBound int
DECLARE @pageUpperBound int

-- Create temp table
CREATE TABLE #Temp
(
    IndexId int identity (1, 1) NOT NULL,
    FriendshipRequestID int NOT NULL,
    RequestorUserID int NOT NULL,
    RequesteeUserID int NOT NULL,
    FriendshipState tinyint NULL,
    [Message] nvarchar(2000) collate database_default NULL,
    CreatedDate datetime NULL,
    LastModifiedDate datetime NULL
)

IF (@sortBy = 0)
BEGIN

	IF (@sortOrder = 0)
	BEGIN

		INSERT INTO #Temp
			(FriendshipRequestID, RequestorUserID, RequesteeUserID, FriendshipState, [Message], CreatedDate, LastModifiedDate)
		SELECT		
			FriendshipRequestID, 
			RequestorUserID, 
			RequesteeUserID, 
			FriendshipState, 
			[Message], 
			CreatedDate, 
			LastModifiedDate
		FROM [dbo].[cs_FriendRequests]
		WHERE RequesteeUserID = @UserID
			AND	(@FriendshipState IS NULL OR FriendshipState = @FriendshipState)
		ORDER BY CreatedDate ASC

		SET @totalCount = @@ROWCOUNT

	END ELSE BEGIN

		INSERT INTO #Temp
			(FriendshipRequestID, RequestorUserID, RequesteeUserID, FriendshipState, [Message], CreatedDate, LastModifiedDate)
		SELECT		
			FriendshipRequestID, 
			RequestorUserID, 
			RequesteeUserID, 
			FriendshipState, 
			[Message], 
			CreatedDate, 
			LastModifiedDate
		FROM [dbo].[cs_FriendRequests]
		WHERE RequesteeUserID = @UserID
			AND	(@FriendshipState IS NULL OR FriendshipState = @FriendshipState)
		ORDER BY CreatedDate DESC

		SET @totalCount = @@ROWCOUNT

	END

END ELSE BEGIN

	IF (@sortOrder = 0)
	BEGIN

		INSERT INTO #Temp
			(FriendshipRequestID, RequestorUserID, RequesteeUserID, FriendshipState, [Message], CreatedDate, LastModifiedDate)
		SELECT		
			FriendshipRequestID, 
			RequestorUserID, 
			RequesteeUserID, 
			FriendshipState, 
			[Message], 
			CreatedDate, 
			LastModifiedDate
		FROM [dbo].[cs_FriendRequests]
		WHERE RequesteeUserID = @UserID
			AND	(@FriendshipState IS NULL OR FriendshipState = @FriendshipState)
		ORDER BY CreatedDate ASC

		SET @totalCount = @@ROWCOUNT

	END ELSE BEGIN

		INSERT INTO #Temp
			(FriendshipRequestID, RequestorUserID, RequesteeUserID, FriendshipState, [Message], CreatedDate, LastModifiedDate)
		SELECT		
			FriendshipRequestID, 
			RequestorUserID, 
			RequesteeUserID, 
			FriendshipState, 
			[Message], 
			CreatedDate, 
			LastModifiedDate
		FROM [dbo].[cs_FriendRequests]
		WHERE RequesteeUserID = @UserID
			AND	(@FriendshipState IS NULL OR FriendshipState = @FriendshipState)
		ORDER BY CreatedDate DESC

		SET @totalCount = @@ROWCOUNT

	END

END

-- Set the rowcount
SET @rowsToReturn = @pageSize * (@pageIndex + 1)
SET ROWCOUNT @rowsToReturn

-- Set the page bounds
SET @pageLowerBound = @pageSize * @pageIndex
SET @pageUpperBound = @pageLowerBound + @pageSize + 1

-- Select from temp table only the proper number of rows
SELECT FriendshipRequestID, RequestorUserID, RequesteeUserID, FriendshipState, [Message], CreatedDate, LastModifiedDate
FROM #Temp
WHERE #Temp.IndexId > @pageLowerBound
AND #Temp.IndexId <  @pageUpperBound

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
/***********************************************
* Sproc: cs_FriendRequest_SelectByRequestorUserID
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_FriendRequest_SelectByRequestorUserID'

IF OBJECT_ID(N'[dbo].[cs_FriendRequest_SelectByRequestorUserID]') IS NOT NULL
	DROP PROCEDURE [dbo].[cs_FriendRequest_SelectByRequestorUserID]
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
CREATE PROCEDURE [dbo].[cs_FriendRequest_SelectByRequestorUserID]
(
	@UserID int, 
	@FriendshipState tinyint = NULL,
	@sortOrder int,
	@sortBy int,
	@pageIndex int,
    	@pageSize int,
    	@totalCount int output
)

AS

DECLARE @rowsToReturn int
DECLARE @pageLowerBound int
DECLARE @pageUpperBound int

-- Create temp table
CREATE TABLE #Temp
(
	IndexId int identity (1, 1) NOT NULL,
	FriendshipRequestID int NOT NULL,
    RequestorUserID int NOT NULL,
	RequesteeUserID int NOT NULL,
    FriendshipState tinyint NULL,
    [Message] nvarchar(2000) collate database_default NULL,
    CreatedDate datetime NULL,
    LastModifiedDate datetime NULL
)

IF (@sortBy = 0)
BEGIN

	IF (@sortOrder = 0)
	BEGIN

		INSERT INTO #Temp
			(FriendshipRequestID, RequestorUserID, RequesteeUserID, FriendshipState, [Message], CreatedDate, LastModifiedDate)
		SELECT		
			FriendshipRequestID, 
			RequestorUserID, 
			RequesteeUserID, 
			FriendshipState, 
			[Message], 
			CreatedDate, 
			LastModifiedDate
		FROM [dbo].[cs_FriendRequests]
		WHERE RequestorUserID = @UserID
			AND	(@FriendshipState IS NULL OR FriendshipState = @FriendshipState)
		ORDER BY CreatedDate ASC

		SET @totalCount = @@ROWCOUNT

	END ELSE BEGIN

		INSERT INTO #Temp
			(FriendshipRequestID, RequestorUserID, RequesteeUserID, FriendshipState, [Message], CreatedDate, LastModifiedDate)
		SELECT		
			FriendshipRequestID, 
			RequestorUserID, 
			RequesteeUserID, 
			FriendshipState, 
			[Message], 
			CreatedDate, 
			LastModifiedDate
		FROM [dbo].[cs_FriendRequests]
		WHERE RequestorUserID = @UserID
			AND	(@FriendshipState IS NULL OR FriendshipState = @FriendshipState)
		ORDER BY CreatedDate DESC

		SET @totalCount = @@ROWCOUNT

	END

END ELSE BEGIN

	IF (@sortOrder = 0)
	BEGIN

		INSERT INTO #Temp
			(FriendshipRequestID, RequestorUserID, RequesteeUserID, FriendshipState, [Message], CreatedDate, LastModifiedDate)
		SELECT		
			FriendshipRequestID, 
			RequestorUserID, 
			RequesteeUserID, 
			FriendshipState, 
			[Message], 
			CreatedDate, 
			LastModifiedDate
		FROM [dbo].[cs_FriendRequests]
		WHERE RequestorUserID = @UserID
			AND	(@FriendshipState IS NULL OR FriendshipState = @FriendshipState)
		ORDER BY LastModifiedDate ASC

		SET @totalCount = @@ROWCOUNT

	END ELSE BEGIN

		INSERT INTO #Temp
			(FriendshipRequestID, RequestorUserID, RequesteeUserID, FriendshipState, [Message], CreatedDate, LastModifiedDate)
		SELECT		
			FriendshipRequestID, 
			RequestorUserID, 
			RequesteeUserID, 
			FriendshipState, 
			[Message], 
			CreatedDate, 
			LastModifiedDate
		FROM [dbo].[cs_FriendRequests]
		WHERE RequestorUserID = @UserID
			AND	(@FriendshipState IS NULL OR FriendshipState = @FriendshipState)
		ORDER BY LastModifiedDate DESC

		SET @totalCount = @@ROWCOUNT

	END

END

-- Set the rowcount
SET @rowsToReturn = @pageSize * (@pageIndex + 1)
SET ROWCOUNT @rowsToReturn

-- Set the page bounds
SET @pageLowerBound = @pageSize * @pageIndex
SET @pageUpperBound = @pageLowerBound + @pageSize + 1

-- Select from temp table only the proper number of rows
SELECT FriendshipRequestID, RequestorUserID, RequesteeUserID, FriendshipState, [Message], CreatedDate, LastModifiedDate
FROM #Temp
WHERE #Temp.IndexId > @pageLowerBound
AND #Temp.IndexId <  @pageUpperBound

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
/***********************************************
* Sproc: cs_FriendRequest_SelectByUserID
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_FriendRequest_SelectByUserID'

IF OBJECT_ID(N'[dbo].[cs_FriendRequest_SelectByUserID]') IS NOT NULL
	DROP PROCEDURE [dbo].[cs_FriendRequest_SelectByUserID]
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
CREATE PROCEDURE [dbo].[cs_FriendRequest_SelectByUserID]
(
	@UserID int, 
	@FriendshipState tinyint = NULL,
	@sortOrder int,
	@sortBy int,
	@pageIndex int,
    @pageSize int,
    @totalCount int output
)
AS

DECLARE @rowsToReturn int
DECLARE @pageLowerBound int
DECLARE @pageUpperBound int

-- Create temp table
CREATE TABLE #Temp
(
	IndexId int identity (1, 1) NOT NULL,
	FriendshipRequestID int NOT NULL,
    RequestorUserID int NOT NULL,
	RequesteeUserID int NOT NULL,
    FriendshipState tinyint NULL,
    [Message] nvarchar(2000) collate database_default NULL,
    CreatedDate datetime NULL,
    LastModifiedDate datetime NULL
)

IF (@sortBy = 0)
BEGIN
	IF (@sortOrder = 0)
	BEGIN

		INSERT INTO #Temp
			(FriendshipRequestID, RequestorUserID, RequesteeUserID, FriendshipState, [Message], CreatedDate, LastModifiedDate)
		SELECT		
			FriendshipRequestID, 
			RequestorUserID, 
			RequesteeUserID, 
			FriendshipState, 
			[Message], 
			CreatedDate, 
			LastModifiedDate
		FROM [dbo].[cs_FriendRequests]
		WHERE 
			(RequesteeUserID = @UserID OR RequestorUserID = @UserID)
			AND	(@FriendshipState IS NULL OR FriendshipState = @FriendshipState)
		ORDER BY CreatedDate ASC

		SET @totalCount = @@ROWCOUNT

	END ELSE BEGIN

		INSERT INTO #Temp
			(FriendshipRequestID, RequestorUserID, RequesteeUserID, FriendshipState, [Message], CreatedDate, LastModifiedDate)
		SELECT		
			FriendshipRequestID, 
			RequestorUserID, 
			RequesteeUserID, 
			FriendshipState, 
			[Message], 
			CreatedDate, 
			LastModifiedDate
		FROM [dbo].[cs_FriendRequests]
		WHERE 
			(RequesteeUserID = @UserID OR RequestorUserID = @UserID)
			AND	(@FriendshipState IS NULL OR FriendshipState = @FriendshipState)
		ORDER BY CreatedDate DESC

		SET @totalCount = @@ROWCOUNT

	END

END ELSE BEGIN

	IF (@sortOrder = 0)
	BEGIN

		INSERT INTO #Temp
			(FriendshipRequestID, RequestorUserID, RequesteeUserID, FriendshipState, [Message], CreatedDate, LastModifiedDate)
		SELECT		
			FriendshipRequestID, 
			RequestorUserID, 
			RequesteeUserID, 
			FriendshipState, 
			[Message], 
			CreatedDate, 
			LastModifiedDate
		FROM [dbo].[cs_FriendRequests]
		WHERE 
			(RequesteeUserID = @UserID OR RequestorUserID = @UserID)
			AND	(@FriendshipState IS NULL OR FriendshipState = @FriendshipState)
		ORDER BY LastModifiedDate ASC

		SET @totalCount = @@ROWCOUNT

	END ELSE BEGIN

		INSERT INTO #Temp
			(FriendshipRequestID, RequestorUserID, RequesteeUserID, FriendshipState, [Message], CreatedDate, LastModifiedDate)
		SELECT		
			FriendshipRequestID, 
			RequestorUserID, 
			RequesteeUserID, 
			FriendshipState, 
			[Message], 
			CreatedDate, 
			LastModifiedDate
		FROM [dbo].[cs_FriendRequests]
		WHERE 
			(RequesteeUserID = @UserID OR RequestorUserID = @UserID)
			AND	(@FriendshipState IS NULL OR FriendshipState = @FriendshipState)
		ORDER BY LastModifiedDate DESC

		SET @totalCount = @@ROWCOUNT

	END

END

-- Set the rowcount
SET @rowsToReturn = @pageSize * (@pageIndex + 1)
SET ROWCOUNT @rowsToReturn

-- Set the page bounds
SET @pageLowerBound = @pageSize * @pageIndex
SET @pageUpperBound = @pageLowerBound + @pageSize + 1

-- Select from temp table only the proper number of rows
SELECT FriendshipRequestID, RequestorUserID, RequesteeUserID, FriendshipState, [Message], CreatedDate, LastModifiedDate
FROM #Temp
WHERE #Temp.IndexId > @pageLowerBound
AND #Temp.IndexId <  @pageUpperBound

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
/***********************************************
* Sproc: cs_FriendRequest_Update
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_FriendRequest_Update'

IF OBJECT_ID(N'[dbo].[cs_FriendRequest_Update]') IS NOT NULL
	DROP PROCEDURE [dbo].[cs_FriendRequest_Update]
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
CREATE PROCEDURE [dbo].[cs_FriendRequest_Update]
(
	@RequestorUserID	int, 
	@RequesteeUserID	int, 
	@Message	varchar(2000), 
	@FriendshipState			tinyint 
)

AS

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON;

DECLARE @FriendshipRequestID int

BEGIN
	SELECT	@FriendshipRequestID = FriendshipRequestID
	FROM	[dbo].[cs_FriendRequests]
	WHERE	(RequestorUserID = @RequestorUserID OR RequesteeUserID = @RequestorUserID)
	AND		(RequestorUserID = @RequesteeUserID OR RequesteeUserID = @RequesteeUserID)
	
	
	IF @FriendshipRequestID IS NOT NULL
	BEGIN
	
		EXEC cs_FriendRequest_UpdateByID @FriendshipRequestID, @RequestorUserID, @RequesteeUserID, @Message, @FriendshipState
		RETURN 0;
	END
	ELSE
		RETURN 1;
END

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
/***********************************************
* Sproc: cs_FriendRequest_UpdateByID
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_FriendRequest_UpdateByID'

IF OBJECT_ID(N'[dbo].[cs_FriendRequest_UpdateByID]') IS NOT NULL
	DROP PROCEDURE [dbo].[cs_FriendRequest_UpdateByID]
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
CREATE PROCEDURE [dbo].[cs_FriendRequest_UpdateByID]
(
	@FriendshipRequestID int, 
	@RequestorUserID int, 
	@RequesteeUserID int, 
	@Message varchar(2000), 
	@FriendshipState tinyint 
)

AS

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON;

BEGIN
	
	UPDATE	[dbo].[cs_FriendRequests]
	SET		RequestorUserID = @RequestorUserID, 
			RequesteeUserID = @RequesteeUserID, 
			Message  = @Message, 
			FriendshipState			  = @FriendshipState, 
			LastModifiedDate		  = GETDATE()
	WHERE	FriendshipRequestID			  = @FriendshipRequestID

	EXECUTE [cs_FriendRequest_xref_Insert] @RequestorUserID, @RequesteeUserID, @FriendshipState

	RETURN 0;

END

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
/***********************************************
* Sproc: cs_FriendRequest_xref_Insert
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_FriendRequest_xref_Insert'

IF OBJECT_ID(N'[dbo].[cs_FriendRequest_xref_Insert]') IS NOT NULL
	DROP PROCEDURE [dbo].[cs_FriendRequest_xref_Insert]

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[cs_FriendRequest_xref_Insert]
(
	@RequestorUserID int
	, @RequesteeUserID int
	, @FriendshipState tinyint
)

AS

SET Transaction Isolation Level Read UNCOMMITTED
SET NOCOUNT ON;

BEGIN

	IF (@FriendshipState = 2)
	BEGIN

		IF NOT EXISTS (SELECT 1 FROM cs_Friends_xref WHERE UserID = @RequestorUserID AND FriendUserID = @RequesteeUserID)
			INSERT INTO [dbo].[cs_Friends_xref]
			(
				UserID,
				FriendUserID
			)
			VALUES
			(
				@RequestorUserID,
				@RequesteeUserID
			)

		IF NOT EXISTS (SELECT 1 FROM cs_Friends_xref WHERE UserID = @RequesteeUserID AND FriendUserID = @RequestorUserID)
			INSERT INTO [dbo].[cs_Friends_xref]
			(
				UserID,
				FriendUserID
			)
			VALUES
			(
				@RequesteeUserID,
				@RequestorUserID
			)

	END
	RETURN 0;

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/***********************************************
* Sproc: cs_GetAnonymousUserID
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_GetAnonymousUserID'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

/* This sproc is no longer needed, the user can just be looked up by name */
--as "Anonymous"
--
--Create Proc [dbo].cs_GetAnonymousUserID
--(
--	@SettingsID int,
--	@UserID int output
--)
--as
--SET Transaction Isolation Level Read UNCOMMITTED
--SELECT	@UserID = U.UserID
--FROM	cs_Users U
--WHERE	U.IsAnonymous = 1

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 

GO

--grant execute on [dbo].cs_GetAnonymousUserID to public
go
/***********************************************
* Sproc: cs_GetSectionsForUserSubscriptions
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_GetSectionsForUserSubscriptions'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

CREATE procedure dbo.cs_GetSectionsForUserSubscriptions
(
	@UserID		int,
	@SettingsID	int
)
AS
	SET Transaction Isolation Level Read UNCOMMITTED

	SELECT
		COALESCE(S.SectionID, T.SectionID) AS SectionID,
		S.ApplicationType
	FROM
		cs_Sections S
		LEFT JOIN cs_TrackedSections TS ON S.SectionID = TS.SectionID
		LEFT JOIN (cs_TrackedThreads TT INNER JOIN cs_Threads T ON TT.ThreadID = T.ThreadID) ON S.SectionID = T.SectionID
	WHERE
		(TS.UserID IS NULL OR TS.UserID = @UserID)
		AND (TT.UserID IS NULL OR TT.UserID = @UserID)
		AND TS.SettingsID = @SettingsID
		AND S.SettingsID = @SettingsID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXECUTE on [dbo].[cs_GetSectionsForUserSubscriptions] to public

/***********************************************
* Sproc: cs_GetSectionSubscriptionType
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_GetSectionSubscriptionType'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


CREATE  procedure [dbo].cs_GetSectionSubscriptionType
(
	@UserID int,
	@SectionID int,
	@SettingsID int,
	@SubType int OUTPUT
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
SELECT SubscriptionType FROM cs_TrackedSections WHERE SectionID=@SectionID AND UserID=@UserID and SettingsID = @SettingsID








GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].cs_GetSectionSubscriptionType to public
go
/***********************************************
* Sproc: cs_GetSectionSubscriptionTypes
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_GetSectionSubscriptionTypes'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE  procedure [dbo].cs_GetSectionSubscriptionTypes
(
	@UserID int,
	@SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
SELECT SubscriptionType, SectionID FROM cs_TrackedSections WHERE UserID=@UserID and SettingsID = @SettingsID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[cs_GetSectionSubscriptionTypes]  TO [public]
GO


/***********************************************
* Sproc: cs_GetTotalPostCount
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_GetTotalPostCount'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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



CREATE   PROCEDURE [dbo].cs_GetTotalPostCount
(
	@SettingsID int
)

 AS
SET Transaction Isolation Level Read UNCOMMITTED
	SELECT TOP 1 
		TotalPosts 
	FROM 
		forums_Statistics where SettingsID = @SettingsID






GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_GetTotalPostCount] to public
go
/***********************************************
* Sproc: cs_GetUserIDByAppToken
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_GetUserIDByAppToken'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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




CREATE         PROCEDURE [dbo].cs_GetUserIDByAppToken
(
	@AppUserToken varchar(128),
	@SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
SELECT
	U.UserID
FROM 
	cs_vw_Users_FullUser U (nolock)
WHERE 
	U.AppUserToken = @AppUserToken and SettingsID = @SettingsID






























GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_GetUserIDByAppToken] to public
go
/***********************************************
* Sproc: cs_GetUsersForSectionSubscriptions
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_GetUsersForSectionSubscriptions'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

CREATE procedure dbo.cs_GetUsersForSectionSubscriptions
(
	@SectionID	int,
	@SettingsID	int
)
AS
	SET Transaction Isolation Level Read UNCOMMITTED

	SELECT
		U.*
	FROM
		cs_vw_Users_FullUser U
		LEFT JOIN cs_TrackedSections TS ON U.UserID = TS.UserID
		LEFT JOIN (cs_TrackedThreads TT INNER JOIN cs_Threads T ON TT.ThreadID = T.ThreadID) ON U.UserID = TT.UserID
	WHERE
		(TS.SectionID IS NULL OR TS.SectionID = @SectionID)
		AND (T.SectionID IS NULL OR T.SectionID = @SectionID)
		AND TS.SettingsID = @SettingsID
		AND U.SettingsID = @SettingsID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXECUTE on [dbo].[cs_GetUsersForSectionSubscriptions] to public

/***********************************************
* Sproc: cs_Groups_Get
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_Groups_Get'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

CREATE  PROCEDURE [dbo].cs_Groups_Get
(
	@SettingsID int,
	@ApplicationType smallint = 0,
	@RequireModeration bit = 0

)
AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN

	IF (@RequireModeration = 0)
		SELECT
			*
		FROM
			cs_Groups
		WHERE
			(SettingsID = @SettingsID) and ApplicationType = @ApplicationType
	ELSE
		SELECT DISTINCT
			FG.*
		FROM
			cs_Sections S,
			cs_Groups FG
		WHERE
			S.GroupID = FG.GroupID AND
			S.IsActive = 1 AND
			(SELECT Count(PostID) FROM cs_Posts P WHERE SectionID = S.SectionID AND P.IsApproved = 0) > 0 AND
			(FG.SettingsID = @SettingsID) AND
			FG.ApplicationType = @ApplicationType

END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_Groups_Get] to public
go
/***********************************************
* Sproc: cs_Groups_ReOrder
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_Groups_ReOrder'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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


Create Proc [dbo].cs_Groups_ReOrder 
(
	@GroupID int,
	@Index int,
	@SettingsID int
	
)

as

Set NoCount On

Declare @ApplicationType int
Select @ApplicationType = ApplicationType From cs_Groups where GroupID = @GroupID and SettingsID = @SettingsID

exec cs_Groups_ResetOrder @ApplicationType, @SettingsID

update cs_Groups
Set SortOrder = SortOrder + 1 where SortOrder >= @Index and SettingsID = @SettingsID and ApplicationType = @ApplicationType

update cs_Groups
Set SortOrder = @Index where GroupID = @GroupID and SettingsID = @SettingsID and ApplicationType = @ApplicationType

exec cs_Groups_ResetOrder @ApplicationType, @SettingsID




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_Groups_ReOrder] to public
go
/***********************************************
* Sproc: cs_Groups_ResetOrder
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_Groups_ResetOrder'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

Create Proc [dbo].cs_Groups_ResetOrder
(
	@ApplicationType int,
	@SettingsID int
)
as

Declare @Sort int
Set @Sort = 0
Declare @GroupID int
DECLARE group_Cursor CURSOR FOR
SELECT GroupID FROM cs_Groups where ApplicationType = @ApplicationType and SettingsID = @SettingsID order by SortOrder

OPEN group_Cursor

FETCH NEXT FROM group_Cursor Into @GroupID
WHILE (@@FETCH_STATUS = 0)
Begin
	Update cs_Groups Set SortOrder = @Sort where GroupID = @GroupID
	Set @Sort = @Sort + 1
   FETCH NEXT FROM group_Cursor Into @GroupID
End

CLOSE group_Cursor
DEALLOCATE group_Cursor



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_Groups_ResetOrder] to public
go
/***********************************************
* Sproc: cs_Group_CreateUpdateDelete
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_Group_CreateUpdateDelete'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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


CREATE       PROCEDURE [dbo].cs_Group_CreateUpdateDelete
(
	@GroupID	int out,
	@ParentGroupID  int = 0,
	@Name		nvarchar(256),
	@Description	nvarchar(1000) = '',
	@ApplicationType smallint = 0,
	@Action 	int,
	@SettingsID 	int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
-- CREATE
IF @Action = 0
BEGIN
	DECLARE @SortOrder int

	SELECT @SortOrder = coalesce(MAX(SortOrder) + 1, 1) FROM cs_Groups where SettingsID = @SettingsID

	-- Create a new forum group
	INSERT INTO 
		cs_Groups 
		(
			Name,
			Description,
			SortOrder,
			ApplicationType,
			SettingsID,
			ParentGroupID
		)
	VALUES 
		(
			@Name,
			@Description,
			@SortOrder,
			@ApplicationType,
			@SettingsID,
			@ParentGroupID
		)
	
	SET @GroupID = SCOPE_IDENTITY()
END


-- UPDATE
ELSE IF @Action = 1
BEGIN

	IF EXISTS(SELECT GroupID FROM cs_Groups WHERE GroupID = @GroupID)
	BEGIN
		UPDATE
			cs_Groups
		SET
			Name = @Name,
			Description = @Description,
			ParentGroupID = @ParentGroupID
		WHERE
			GroupID = @GroupID and SettingsID = @SettingsID
	END

END

-- DELETE
ELSE IF @Action = 2
BEGIN
	DELETE cs_Groups WHERE GroupID = @GroupID and SettingsID = @SettingsID
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_Group_CreateUpdateDelete] to public
go
/***********************************************
* Sproc: cs_Group_Permissions_Get
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_Group_Permissions_Get'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

create procedure [dbo].[cs_Group_Permissions_Get]
(
	@SettingsID int,
	@GroupID int = 0,
	@ApplicationType smallint
)
AS
begin
	SET Transaction Isolation Level Read UNCOMMITTED
	
	IF (@GroupID > 0)
	BEGIN
		
		-- Return product permissions
		SELECT
			P.SettingsID, 
			Implied = convert(bit, 1), 
			SectionID = -1,
			GroupID = -1,
			ApplicationKey = null,
			P.RoleID, 
			AllowMask = convert(bigint, coalesce(P.AllowMask, 0x00)), 
			DenyMask = convert(bigint, coalesce(P.DenyMask, 0x00)), 
			P.ApplicationType 
		FROM
			cs_ProductPermissions P
		WHERE 
			(P.ApplicationType = @ApplicationType or @ApplicationType is null) AND
			P.SettingsID = @SettingsID

		UNION ALL
		
		-- Return the group permissions unioned
		SELECT
			P.SettingsID, 
			Implied = convert(bit, 0), 
			SectionID = -1,
			P.GroupID,
			ApplicationKey = null,
			P.RoleID, 
			AllowMask = convert(bigint, coalesce(P.AllowMask, 0x00)), 
			DenyMask = convert(bigint, coalesce(P.DenyMask, 0x00)), 
			ApplicationType = @ApplicationType
		FROM
			cs_GroupPermissions P
		WHERE 
			P.SettingsID = @SettingsID AND 
			P.GroupID = @GroupID
		order by Implied asc
	END
	ELSE -- Return Product Permissions
	BEGIN
		SELECT
			P.SettingsID, 
			Implied = convert(bit, 1), 
			SectionID = -1,
			GroupID = @GroupID,
			ApplicationKey = null,
			P.RoleID, 
			AllowMask = convert(bigint, coalesce(P.AllowMask, 0x00)), 
			DenyMask = convert(bigint, coalesce(P.DenyMask, 0x00)), 
			P.ApplicationType 
		FROM
			cs_ProductPermissions P
		WHERE 
			(P.ApplicationType = @ApplicationType or @ApplicationType is null) AND
			P.SettingsID = @SettingsID	
	END	

end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_Group_Permissions_Get] to public
go
/***********************************************
* Sproc: cs_guestbook_GetPosts
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_guestbook_GetPosts'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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



CREATE PROCEDURE dbo.cs_guestbook_GetPosts
(
	@PageIndex int,
	@PageSize int,
	@SortOrder bit,
	@SectionID int,
	@SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN

DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @ThreadID int

-- First set the rowcount
DECLARE @RowsToReturn int
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET ROWCOUNT @RowsToReturn

-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1


-- Create a temp table to store the select results
CREATE TABLE #PageIndex 
(
	IndexID int IDENTITY (1, 1) NOT NULL,
	PostID int
)

-- Sort by Post Date
IF  @SortOrder = 0
    INSERT INTO #PageIndex (PostID)
    SELECT PostID FROM cs_Posts (nolock) WHERE IsApproved = 1 AND SectionID = @SectionID and SettingsID = @SettingsID ORDER BY PostID

ELSE 
    INSERT INTO #PageIndex (PostID)
    SELECT PostID FROM cs_Posts (nolock) WHERE IsApproved = 1 AND SectionID = @SectionID and SettingsID = @SettingsID ORDER BY PostID DESC


-- Select the individual posts
SELECT
	P.PostID, P.ThreadID, P.ParentID, P.PostAuthor, P.UserID, P.SectionID, P.PostLevel, P.SortOrder, P.Subject, P.PostDate, P.IsApproved,
	P.IsLocked, P.IsIndexed, P.TotalViews, P.Body, P.FormattedBody, P.IPAddress, P.PostType, P.EmoticonID, P.SettingsID, P.AggViews,
	P.PropertyNames as PostPropertyNames, P.PropertyValues as PostPropertyValues,
	P.PostConfiguration,
	P.Points as PostPoints, P.RatingSum as PostRatingSum, P.TotalRatings as PostTotalRatings,
	T.*, U.*, #PageIndex.*,
	T.IsLocked,
	T.IsSticky,
	Username = P.PostAuthor,
	EditNotes = (SELECT EditNotes FROM cs_PostEditNotes WHERE PostID = P.PostID),
	AttachmentFilename = '',
	Replies = 0,
	IsModerator = 0,
	HasRead = 0 -- not used
FROM 
	cs_Posts P (nolock),
	cs_Threads T,
	cs_vw_Users_FullUser U,
	#PageIndex
WHERE 
	P.PostID = #PageIndex.PostID AND
	P.UserID = U.UserID AND
	T.ThreadID = P.ThreadID AND
	#PageIndex.IndexID > @PageLowerBound AND
	#PageIndex.IndexID < @PageUpperBound and U.SettingsID = @SettingsID
ORDER BY
	IndexID
END


  SELECT Count(PostID) FROM cs_Posts (nolock) WHERE IsApproved = 1 AND SectionID = @SectionID and SettingsID = @SettingsID

DROP Table #PageIndex



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[cs_guestbook_GetPosts]  TO [public]
GO


/***********************************************
* Sproc: cs_guestbook_Post_Create
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_guestbook_Post_Create'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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



CREATE   PROCEDURE [dbo].cs_guestbook_Post_Create
(
	@SectionID int,
	@Subject nvarchar(256),
	@UserID int,
	@PostAuthor nvarchar(64) = null,
	@Body ntext,
	@FormattedBody ntext,
	@EmoticonID int = 0,
	@PostType int = 0,
	@PostDate datetime = null,
	@UserHostAddress nvarchar(32),
	@PropertyNames ntext = null,
	@PropertyValues ntext = null,
	@SettingsID int,
	@PostID int out
) 
AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN
SET NOCOUNT ON
DECLARE @ThreadID int

-- set the PostDate
IF @PostDate IS NULL
	SET @PostDate = GetDate()

-- set the username
IF @PostAuthor IS NULL
	SELECT 
		@PostAuthor = UserName
	FROM 
		cs_vw_Users_FullUser 
	WHERE 
		UserID = @UserID

-- Do we care about duplicates?

Select @ThreadID = ThreadID FROM cs_Threads Where SectionID = @SectionID


SET NOCOUNT ON
BEGIN TRAN


IF @ThreadID is null
BEGIN


	INSERT cs_Threads 	
	( 
		SectionID,
		PostDate, 
		UserID, 
		PostAuthor, 
		ThreadDate, 
		MostRecentPostAuthor, 
		MostRecentPostAuthorID, 	
		MostRecentPostID, 
		IsLocked, 
		IsApproved,
		IsSticky, 
		StickyDate, 
		ThreadEmoticonID,
		SettingsID 
	)
	VALUES
	( 
		@SectionID, 
		@PostDate, 
		@UserID, 
		@PostAuthor,
		@PostDate,
		@PostAuthor,
		@UserID, 
		0,	-- MostRecentPostID, which we don't know until after post INSERT below.
		0,
		1,
		0,
		@PostDate,
		-1,
		@SettingsID 
	)

	-- Get the new ThreadID
	SELECT 
		@ThreadID = SCOPE_IDENTITY()
--	FROM
--		cs_Threads
		
	-- Now we add the new post
	INSERT cs_Posts 
		( SectionID, 
		ThreadID, 
		ParentID, 
		PostLevel, 
		SortOrder, 

		Subject, 
		UserID, 
		PostAuthor, 
		IsApproved, 
		IsLocked, 
		Body, 
		FormattedBody, 
		PostType, 
		PostDate, 
		IPAddress, 
		EmoticonID,
		PropertyNames,
		PropertyValues,
		SettingsID
	        )
	VALUES 
		( @SectionID, 
		@ThreadID, 
		0, 	-- ParentID, which we don't know until after INSERT
		1, 	-- PostLevel, 1 marks start/top/first post in thread.
		1, 	-- SortOrder (not in use at this time)
		@Subject, 
		@UserID, 
		@PostAuthor,
		1, 
		0, 
		@Body, 
		@FormattedBody, 
		@PostType, 
		@PostDate, 
		@UserHostAddress, 
		-1,
		@PropertyNames,
		@PropertyValues,
		@SettingsID )
		

	-- Get the new PostID
	SELECT 
		@PostID = SCOPE_IDENTITY()

	-- Update the new Thread with the new PostID
	UPDATE 
		cs_Threads
	SET 
		MostRecentPostID = @PostID
	WHERE 
		ThreadID = @ThreadID

END
ELSE BEGIN	-- @ParentID <> 0 means there is a reply to an existing post

	-- Insert the new post
	INSERT cs_Posts 
		( SectionID, 
		ThreadID, 
		ParentID, 
		PostLevel, 
		SortOrder, 
		Subject, 
		UserID, 
		PostAuthor, 
		IsApproved, 
		IsLocked, 
		Body, 
		FormattedBody, 
		PostType, 
		PostDate, 
		IPAddress, 
		EmoticonID,
		PropertyNames,
		PropertyValues,
		SettingsID )
	VALUES 
		( @SectionID, 
		@ThreadID, 
		0, 
		1, 
		0,
		@Subject, 
		@UserID, 
		@PostAuthor, 
		1, 
		0, 
		@Body, 
		@FormattedBody, 
		@PostType, 
		@PostDate, 
		@UserHostAddress, 
		-1,
		@PropertyNames,
		@PropertyValues,
		@SettingsID  )


		-- Grab the new PostID and update the ThreadID's info
		SELECT 
			@PostID = SCOPE_IDENTITY() 




		UPDATE
			cs_Threads 	
		SET 
			TotalReplies = (SELECT COUNT(*) FROM cs_Posts WHERE ThreadID = @ThreadID)
		WHERE
			ThreadID = @ThreadID
	END
END


COMMIT TRAN

SET NOCOUNT OFF

SELECT @PostID = @PostID






GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO





grant execute on [dbo].[cs_guestbook_Post_Create] to public
go
/***********************************************
* Sproc: cs_guestbook_Post_Delete
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_guestbook_Post_Delete'

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

CREATE   PROCEDURE [dbo].cs_guestbook_Post_Delete
(
	@SectionID int,
	@PostID int
)

as
SET Transaction Isolation Level Read UNCOMMITTED

Declare @ThreadID int
Select @ThreadID = ThreadID FROM cs_Posts where PostID = @PostID and SectionID = @SectionID

if(@ThreadID is null)
RETURN

Delete FROM cs_Posts where PostID = @PostID and SectionID = @SectionID

UPDATE
	cs_Threads 	
SET 
	TotalReplies = (SELECT COUNT(*) FROM cs_Posts WHERE ThreadID = @ThreadID)
WHERE
	ThreadID = @ThreadID
	
GO

grant execute on [dbo].[cs_guestbook_Post_Delete] to public
go
/***********************************************
* Sproc: cs_Image_CreateUpdateDelete
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_Image_CreateUpdateDelete'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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


/***********************************************
* Sproc: cs_InkData_Add
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_InkData_Add'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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


Create Proc [dbo].[cs_InkData_Add]
(	
	@SectionID int,
	@SettingsID int,
	@UserID int,
	@ApplicationType smallint,
	@Ink NTEXT,
	@DateUpdated datetime,
	@InkID int output
)
as

Insert cs_InkData (UserID, SettingsID, SectionID, Ink, DateUpdated, ApplicationType)
Values (@UserID, @SettingsID, @SectionID, @Ink, @DateUpdated, @ApplicationType)

Select @InkID = SCOPE_IDENTITY()



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_InkData_Add] to public
go
/***********************************************
* Sproc: cs_InkData_Delete
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_InkData_Delete'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

Create Proc [dbo].[cs_InkData_Delete]
(
	@InkID int,
	@SectionID int,
	@SettingsID int
)

as

Delete FROM cs_InkData Where SettingsID = @SettingsID and SectionID = @SectionID and InkID = @InkID





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_InkData_Delete] to public
go
/***********************************************
* Sproc: cs_InkData_Get
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_InkData_Get'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

Create Proc [dbo].[cs_InkData_Get]
(
	@InkID int,
	@SettingsID int
)
as
Select InkID, Ink, UserID, SectionID, SettingsID, DateUpdated, ApplicationType FROM cs_InkData where InkID = @InkID and SettingsID = @SettingsID


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_InkData_Get] to public
go
/***********************************************
* Sproc: cs_InkData_Update
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_InkData_Update'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

Create Proc [dbo].[cs_InkData_Update]
(	
	@SectionID int,
	@SettingsID int,
	@Ink NTEXT,
	@DateUpdated datetime,
	@InkID int
)
as

update cs_InkData 
SET
	Ink = @Ink, 
	DateUpdated = @DateUpdated
where
	SettingsID = @SettingsID and SectionID = @SectionID and InkID = @InkID






GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_InkData_Update] to public
go
/***********************************************
* Sproc: cs_Licenses_Delete
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_Licenses_Delete'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

CREATE PROCEDURE dbo.cs_Licenses_Delete
	@LicenseID uniqueidentifier
AS

delete from cs_Licenses where LicenseID = @LicenseID
GO


grant execute on [dbo].[cs_Licenses_Delete] to public
go
/***********************************************
* Sproc: cs_Licenses_Get
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_Licenses_Get'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

CREATE PROCEDURE dbo.cs_Licenses_Get AS

SELECT * FROM cs_Licenses
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_Licenses_Get] to public
go
/***********************************************
* Sproc: cs_Licenses_GetUsage
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_Licenses_GetUsage'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

CREATE PROCEDURE dbo.cs_Licenses_GetUsage
AS
begin

	set ansi_warnings off

	declare @NumberOfBlogs int
		, @NumberOfForums int
		, @NumberOfGalleries int
		, @NumberOfGroups int
		, @NumberOfContentMirrors int
		, @NumberOfAddresses int
		, @NumberOfUsers int
		, @NumberOfBlogPosts int
		, @NumberOfBlogComments int
		, @NumberOfForumPosts int
		, @NumberOfForumReplies int
		, @NumberOfMediaGalleryPosts int
		, @NumberOfMediaGalleryComments int
		, @NumberOfFriendRelationships int
		, @NumberOfMessages int

	select
		@NumberOfBlogPosts = NumberOfBlogPosts
		, @NumberOfBlogComments = NumberOfBlogComments
		, @NumberOfForumPosts = NumberOfForumPosts
		, @NumberOfForumReplies = NumberOfForumReplies
		, @NumberOfMediaGalleryPosts = NumberOfMediaGalleryPosts
		, @NumberOfMediaGalleryComments = NumberOfMediaGalleryComments
	from (
		select
			count(case when s.ApplicationType = 1 and p.PostLevel = 1 then p.PostID end) NumberOfBlogPosts
			, count(case when s.ApplicationType = 1 and p.PostLevel > 1 then p.PostID end) NumberOfBlogComments
			, count(case when s.ApplicationType = 0 and ForumType = 0 and p.PostLevel = 1 then p.PostID end) NumberOfForumPosts
			, count(case when s.ApplicationType = 0 and ForumType = 0 and p.PostLevel > 1 then p.PostID end) NumberOfForumReplies
			, count(case when s.ApplicationType = 13 and p.PostLevel = 1 then p.PostID end) NumberOfMediaGalleryPosts
			, count(case when s.ApplicationType = 13 and p.PostLevel > 1 then p.PostID end) NumberOfMediaGalleryComments
		from
			cs_Posts p
		inner join
			cs_Sections s on p.SettingsID = s.SettingsID and p.SectionID = s.SectionID
		where
			s.ApplicationType in (0, 1, 13)
			and s.IsActive = 1
	) as x

	select
		@NumberOfBlogs = NumberOfBlogs
		, @NumberOfForums = NumberOfForums
		, @NumberOfGalleries = NumberOfGalleries
		, @NumberOfGroups = NumberOfGroups
	from (
		select
			count(case when ApplicationType = 11 then SectionID end) as NumberOfGroups
			, count(case when ApplicationType = 0 and ForumType = 0 and HubSectionID = 0 then SectionID end) as NumberOfForums
			, count(case when ApplicationType = 1 and HubSectionID = 0 then SectionID end) as NumberOfBlogs
			, count(case when ApplicationType = 13 and HubSectionID = 0 then SectionID end) as NumberOfGalleries
		from cs_Sections
		where
			ApplicationType in (0, 1, 11, 13)
			and IsActive = 1
	) as x

	set @NumberOfContentMirrors = (select count(UrlID) from cs_RollerBlogFeeds where Enabled = 1)
	set @NumberOfAddresses = (select count(M.SectionID) from csm_MailingLists M left join cs_Sections S on (S.SectionID = M.SectionID) where S.IsActive = 1 and M.IsMailingList = 1)
	set @NumberOfUsers = (select count(UserID) from cs_Users)
	set @NumberOfFriendRelationships = (select count(UserID) from cs_Friends_xref)
	set @NumberOfMessages = (select count(*) from cs_Messaging_Messages)

	set ansi_warnings on

	select
		coalesce(@NumberOfBlogs, 0) NumberOfBlogs
		, coalesce(@NumberOfForums, 0) NumberOfForums
		, coalesce(@NumberOfGalleries, 0) NumberOfGalleries
		, coalesce(@NumberOfGroups, 0) NumberOfGroups
		, coalesce(@NumberOfContentMirrors, 0) NumberOfContentMirrors
		, coalesce(@NumberOfAddresses, 0) NumberOfAddresses
		, coalesce(@NumberOfBlogPosts, 0) NumberOfBlogPosts
		, coalesce(@NumberOfBlogComments, 0) NumberOfBlogComments
		, coalesce(@NumberOfForumPosts, 0) NumberOfForumPosts
		, coalesce(@NumberOfForumReplies, 0) NumberOfForumReplies
		, coalesce(@NumberOfMediaGalleryPosts, 0) NumberOfMediaGalleryPosts
		, coalesce(@NumberOfMediaGalleryComments, 0) NumberOfMediaGalleryComments
		, coalesce(@NumberOfFriendRelationships, 0) NumberOfFriendRelationships
		, coalesce(@NumberOfMessages, 0) NumberOfMessages
		, coalesce(@NumberOfUsers, 0) NumberOfUsers

end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_Licenses_GetUsage] to public
go
/***********************************************
* Sproc: cs_Licenses_Update
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_Licenses_Update'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

CREATE PROCEDURE dbo.cs_Licenses_Update
	@LicenseID uniqueidentifier,
	@LicenseValue ntext
AS

if exists(select LicenseID from cs_Licenses where LicenseID = @LicenseID)
begin
	delete from cs_Licenses where LicenseID = @LicenseID
end

insert into cs_Licenses (LicenseID, LicenseValue, InstallDate) values (@LicenseID, @LicenseValue, getdate())
GO


grant execute on [dbo].[cs_Licenses_Update] to public
go
/***********************************************
* Sproc: cs_LinkCategories_Get
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_LinkCategories_Get'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

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



CREATE PROCEDURE dbo.cs_LinkCategories_Get
(
	@SectionID int,
	@SettingsID int,
	@PreLoadLinks bit
)
AS
SET Transaction Isolation Level Read UNCOMMITTED

-- Categories
SELECT  [LinkCategoryID], [SectionID], [Name], [Description], [IsEnabled], [SortOrder], [SettingsID]
FROM cs_LinkCategories
WHERE SectionID = @SectionID and SettingsID = @SettingsID
Order By SortOrder

-- Do we need to return links as well?
IF (@PreLoadLinks = 1)
BEGIN
	SELECT [LinkID], l.[LinkCategoryID], [Title], [Url], l.[IsEnabled], l.[SortOrder], l.[SettingsID], [Rel], l.[Description], [DateCreated]
	FROM cs_Links l inner join cs_LinkCategories c on l.LinkCategoryID = c.LinkCategoryID
	WHERE c.SectionID = @SectionID and c.SettingsID = @SettingsID
	Order By c.SortOrder, l.SortOrder
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_LinkCategories_Get] to public
go



/***********************************************
* Sproc: cs_LinkCategory_CreateUpdateDelete
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_LinkCategory_CreateUpdateDelete'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

--exec dbo.cs_LinkCategory_CreateUpdateDelete @LinkCategoryID = 3, @DeleteLinkCategory = 1

CREATE PROCEDURE dbo.cs_LinkCategory_CreateUpdateDelete
	@DeleteLinkCategory bit=0,
	@SectionID int,
	@Name nvarchar(256)='',
	@Description nvarchar(2000)=null,
	@IsEnabled bit=1,
	@SortOrder int=0,
	@SettingsID int,
	@LinkCategoryID int=0 out
AS
SET Transaction Isolation Level Read UNCOMMITTED

-- Are we deleting?
if @DeleteLinkCategory = 1
begin
	DELETE FROM cs_Links WHERE LinkCategoryID = @LinkCategoryID and SettingsID = @SettingsID
	DELETE FROM cs_LinkCategories WHERE LinkCategoryID = @LinkCategoryID and SectionID = @SectionID and SettingsID = @SettingsID
	RETURN
end

-- Are we updating?
if @LinkCategoryID > 0
begin
	UPDATE cs_LinkCategories SET
		[Name] = @Name,
		[Description] = @Description,
		IsEnabled = @IsEnabled,
		SortOrder = @SortOrder
	WHERE LinkCategoryID = @LinkCategoryID and SectionID = @SectionID and SettingsID = @SettingsID
end
else
begin
	If( @SortOrder = 0 )
	Begin
		Select @SortOrder = max(SortOrder) + 1 from cs_LinkCategories where SectionID = @SectionID and SettingsID = @SettingsID

		if(@SortOrder is null)
			Select	@SortOrder = 0
	End
	INSERT INTO cs_LinkCategories (SectionID, [Name], [Description], IsEnabled, SortOrder, SettingsID)
		VALUES (@SectionID, @Name, @Description, @IsEnabled, @SortOrder, @SettingsID)
	set @LinkCategoryID = SCOPE_IDENTITY()
end


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_LinkCategory_CreateUpdateDelete] to public
go
/***********************************************
* Sproc: cs_LinkCategory_UpdateSortOrder
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_LinkCategory_UpdateSortOrder'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

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



CREATE procedure [dbo].cs_LinkCategory_UpdateSortOrder
( 
            @LinkCategoryID int, 
	    @SettingsID int,
            @MoveUp bit 
) 
AS 
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN 
	set nocount on
	
	DECLARE @currentSortValue int 
	DECLARE @replaceSortValue int 
	DECLARE @replaceLinkCategoryID int
	DECLARE @SectionID int
	
	-- Get the current sort order 
	SELECT @currentSortValue = SortOrder, @SectionID = SectionID FROM cs_LinkCategories WHERE LinkCategoryID = @LinkCategoryID  and SettingsID = @SettingsID
	
	-- Move the item up or down? 
	IF (@MoveUp = 1) 
	BEGIN 
		SELECT @replaceSortValue = coalesce(f.SortOrder, -1), @replaceLinkCategoryID = coalesce(f.LinkCategoryID, -1)
			FROM cs_LinkCategories f
				inner join (
					select top 1 * 
					from cs_LinkCategories 
					WHERE SectionID = @SectionID and SortOrder < @currentSortValue  and SettingsID = @SettingsID order by SortOrder DESC
				) as pf on 
					pf.LinkCategoryID = f.LinkCategoryID  and f.SettingsID = @SettingsID

		if( @replaceSortValue != -1 And @replaceSortValue is Not Null  )
		begin	
			UPDATE cs_LinkCategories SET SortOrder = @currentSortValue WHERE LinkCategoryID = @replaceLinkCategoryID  and SettingsID = @SettingsID
			UPDATE cs_LinkCategories SET SortOrder = @replaceSortValue WHERE LinkCategoryID = @LinkCategoryID  and SettingsID = @SettingsID
		END 
	END 
	ELSE 
	BEGIN 
		SELECT @replaceSortValue = coalesce(f.SortOrder, -1), @replaceLinkCategoryID = coalesce(f.LinkCategoryID, -1)
			FROM cs_LinkCategories f
				inner join (
					select top 1 * 
					FROM cs_LinkCategories 
					WHERE SectionID = @SectionID and SortOrder > @currentSortValue  and SettingsID = @SettingsID order by SortOrder ASC				
				) as pf on 
					pf.LinkCategoryID = f.LinkCategoryID  and f.SettingsID = @SettingsID


		if( @replaceSortValue != -1 And @replaceSortValue is Not Null  )
		begin		
			UPDATE cs_LinkCategories SET SortOrder = @currentSortValue WHERE LinkCategoryID = @replaceLinkCategoryID  and SettingsID = @SettingsID
			UPDATE cs_LinkCategories SET SortOrder = @replaceSortValue WHERE LinkCategoryID = @LinkCategoryID  and SettingsID = @SettingsID
		end
	END 
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_LinkCategory_UpdateSortOrder] to public
go
/***********************************************
* Sproc: cs_Links_Get
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_Links_Get'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

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



CREATE PROCEDURE dbo.cs_Links_Get
	@LinkCategoryID int,
	@SettingsID int
AS
SET Transaction Isolation Level Read UNCOMMITTED

	SELECT [LinkID], [LinkCategoryID], [Title], [Url], [IsEnabled], [SortOrder], [SettingsID], [Rel], [Description], [DateCreated]
	FROM cs_Links
	WHERE LinkCategoryID = @LinkCategoryID and SettingsID = @SettingsID
	Order By SortOrder

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_Links_Get] to public
go


/***********************************************
* Sproc: cs_Link_CreateUpdateDelete
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_Link_CreateUpdateDelete'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

CREATE PROCEDURE dbo.cs_Link_CreateUpdateDelete
	@DeleteLink bit=0,
	@LinkCategoryID int=0,
	@Title nvarchar(100)='',
	@Url nvarchar(255)='',
	@Rel nvarchar(100)='',
	@Description nvarchar(2000)='',
	@IsEnabled bit=1,
	@SortOrder int=0,
	@SettingsID int,
	@LinkID int=0 out
AS
SET Transaction Isolation Level Read UNCOMMITTED
-- Are we deleting?
if @DeleteLink = 1
begin
	DELETE FROM cs_Links WHERE LinkID = @LinkID
end

-- Are we updating?
if @LinkID > 0
begin
	UPDATE cs_Links SET
		Title = @Title,
		Url = @Url,
		Rel = @Rel,
		[Description] = @Description,
		IsEnabled = @IsEnabled,
		SortOrder = @SortOrder
	WHERE LinkID = @LinkID and LinkCategoryID = @LinkCategoryID and SettingsID = @SettingsID
end
else
begin
	If( @SortOrder = 0 )
	Begin
		Select @SortOrder = max(SortOrder) + 1 from cs_Links where LinkCategoryID = @LinkCategoryID and SettingsID = @SettingsID

		if(@SortOrder is null)
			Select @SortOrder = 0
	End
	INSERT INTO cs_Links (LinkCategoryID, Title, Url, Rel, [Description], IsEnabled, SortOrder, SettingsID)
		VALUES (@LinkCategoryID, @Title, @Url, @Rel, @Description, @IsEnabled, @SortOrder, @SettingsID)
	set @LinkID = SCOPE_IDENTITY()
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


grant execute on [dbo].[cs_Link_CreateUpdateDelete] to public
go
/***********************************************
* Sproc: cs_Link_UpdateSortOrder
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_Link_UpdateSortOrder'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

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



CREATE procedure [dbo].cs_Link_UpdateSortOrder
( 
            @LinkID int,
	    @SettingsID int, 
            @MoveUp bit 
) 
AS 
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN 
	set nocount on
	
	DECLARE @currentSortValue int 
	DECLARE @replaceSortValue int 
	DECLARE @replaceLinkID int
	DECLARE @LinkCategoryID int
	
	-- Get the current sort order 
	SELECT @currentSortValue = SortOrder, @LinkCategoryID = LinkCategoryID FROM cs_Links WHERE LinkID = @LinkID  and SettingsID = @SettingsID

	-- Move the item up or down? 
	IF (@MoveUp = 1) 
	BEGIN 
		SELECT @replaceSortValue = coalesce(f.SortOrder, -1), @replaceLinkID = coalesce(f.LinkID, -1)
			FROM cs_Links f
				inner join (
					select top 1 * 
					from cs_Links 
					WHERE LinkCategoryID = @LinkCategoryID and SortOrder < @currentSortValue and SettingsID = @SettingsID order by SortOrder DESC
				) as pf on 
					pf.LinkID = f.LinkID and f.SettingsID = @SettingsID
		if( @replaceSortValue != -1 And @replaceSortValue is Not Null )
		begin	
			UPDATE cs_Links SET SortOrder = @currentSortValue WHERE LinkID = @replaceLinkID and SettingsID = @SettingsID
			UPDATE cs_Links SET SortOrder = @replaceSortValue WHERE LinkID = @LinkID and SettingsID = @SettingsID
		END 
	END 
	ELSE 
	BEGIN 
		SELECT @replaceSortValue = coalesce(f.SortOrder, -1), @replaceLinkID = coalesce(f.LinkID, -1)
			FROM cs_Links f
				inner join (
					select top 1 * 
					FROM cs_Links 
					WHERE LinkCategoryID = @LinkCategoryID and SortOrder > @currentSortValue and SettingsID = @SettingsID order by SortOrder ASC				
				) as pf on 
					pf.LinkID = f.LinkID and f.SettingsID = @SettingsID


		if( @replaceSortValue != -1 And @replaceSortValue is Not Null )
		begin		
			UPDATE cs_Links SET SortOrder = @currentSortValue WHERE LinkID = @replaceLinkID and SettingsID = @SettingsID
			UPDATE cs_Links SET SortOrder = @replaceSortValue WHERE LinkID = @LinkID and SettingsID = @SettingsID
		end
	END 
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_Link_UpdateSortOrder] to public
go
/***********************************************
* Sproc: cs_MarkPostAsRead
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_MarkPostAsRead'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

CREATE         PROCEDURE [dbo].cs_MarkPostAsRead
(
	@PostID	int,
	@UserName nvarchar (50),
	@SettingsID int
)
 AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN

	-- If @UserName is null it is an anonymous user
	IF @UserName IS NOT NULL
	BEGIN
		DECLARE @SectionID int
		DECLARE @PostDate datetime

		-- Mark the post as read
		-- *********************

		-- Only for PostLevel = 1
		IF EXISTS (SELECT PostID FROM cs_Posts WHERE PostID = @PostID AND PostLevel = 1 and SettingsID = @SettingsID)
			IF NOT EXISTS (SELECT HasRead FROM PostsReadx WHERE UserName = @UserName and PostID = @PostID and SettingsID = @SettingsID)
				INSERT INTO PostsRead (UserName, PostID, SettingsID) VALUES (@UserName, @PostID, @SettingsID)

	END

END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_MarkPostAsRead] to public
go
/***********************************************
* Sproc: cs_MarkThreadAsRead
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_MarkThreadAsRead'



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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.cs_MarkThreadAsRead
	@ThreadID int,
	@SectionID int,
	@UserID int,
	@SettingsID int
 AS

IF NOT EXISTS (SELECT ThreadID FROM cs_ThreadsRead WHERE ThreadID = @ThreadID AND UserID = @UserID and SettingsID = @SettingsID and SectionID = @SectionID)
	INSERT INTO cs_ThreadsRead (UserID, ThreadID, SectionID, SettingsID) VALUES (@UserID, @ThreadID, @SectionID, @SettingsID)

IF @SectionID > 0
BEGIN
	DECLARE @MarkReadAfter int
	SELECT @MarkReadAfter = SR.MarkReadAfter
	FROM cs_SectionsRead SR
	WHERE SR.SettingsID = @SettingsID AND SR.UserID = @UserID AND SR.SectionID = @SectionID
	IF @MarkReadAfter IS NULL
		SET @MarkReadAfter = 0

	-- Check to see if all the threads in this section have been read
	IF (SELECT COUNT(*) 
		FROM cs_Threads T
		LEFT JOIN cs_ThreadsRead TR ON TR.SettingsID = T.SettingsID AND TR.UserID = @UserID AND TR.ThreadID = T.ThreadID AND TR.SectionID = T.SectionID
		WHERE T.SettingsID = @SettingsID AND T.SectionID = @SectionID AND TR.UserID IS NULL AND T.MostRecentPostID >= @MarkReadAfter) = 0
	BEGIN
		-- Update SectionsRead (forum)
		UPDATE cs_SectionsRead 
		SET
			NewPosts = 0,
			MarkReadAfter = (SELECT (MostRecentPostID + 1) FROM cs_Sections WHERE SectionID = @SectionID AND SettingsID = @SettingsID),
			LastActivity = GETDATE()
		WHERE
			SettingsID = @SettingsID
			AND UserID = @UserID
			AND SectionID = @SectionID

		IF @@ROWCOUNT = 0
		BEGIN
			INSERT INTO cs_SectionsRead (
				GroupID,
				SectionID,
				UserID,
				MarkReadAfter,
				NewPosts,
				LastActivity,
				SettingsID
			)
			SELECT
				GroupID,
				SectionID,
				@UserID,
				(MostRecentPostID + 1),
				0,
				GetDate(),
				SettingsID
			FROM
				cs_Sections
			WHERE
				SettingsID = @SettingsID
				AND SectionID = @SectionID
		END
	END
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[cs_MarkThreadAsRead]  TO [public]
GO


/***********************************************
* Sproc: cs_Media_Posts_Get
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_Media_Posts_Get'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

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

CREATE PROCEDURE [dbo].[cs_Media_Posts_Get]
(
	@SectionID int,
	@PageIndex int, 
	@PageSize int,
	@sqlPopulate nText,
	@UserID int,
	@IncludeCategories bit,
	@SettingsID int,
	@TotalRecords int output
)
AS
begin

	DECLARE @PageLowerBound int
	DECLARE @PageUpperBound int
	DECLARE @RowsToReturn int
	DECLARE @TotalThreads int
	
	-- First set the rowcount
	SET @RowsToReturn = @PageSize * (@PageIndex + 1)
	
	
	-- Set the page bounds
	SET @PageLowerBound = @PageSize * @PageIndex
	SET @PageUpperBound = @PageLowerBound + @PageSize + 1
	
	-- Create a temp table to store the select results
	CREATE TABLE #PageIndex 
	(
		IndexID int IDENTITY (1, 1) NOT NULL,
		PostID int
	)
	
	INSERT INTO #PageIndex (PostID)
	EXEC sp_executesql @sqlPopulate
	
	set @TotalRecords = @@ROWCOUNT
	
	SET ROWCOUNT @RowsToReturn
	
	SELECT 
		jT.ThreadStatus, jT.IsSticky, jT.IsLocked, jT.StickyDate, jT.MostRecentPostAuthorID, jT.MostRecentPostAuthor, jT.MostRecentPostID, jT.ThreadID, jT.ThreadDate, jT.RatingSum, jT.TotalRatings, jT.TotalReplies, jT.ThreadEmoticonID,	jP.PostID, jP.ThreadID, jP.ParentID, jP.PostAuthor, jP.UserID, jP.SectionID, jP.PostLevel, jP.SortOrder, jP.Subject, jP.PostDate, jP.IsApproved,
		jP.IsLocked, jP.IsIndexed, jP.TotalViews, jP.Body, jP.FormattedBody, jP.IPAddress, jP.PostType, jP.PostMedia, jP.EmoticonID, jP.SettingsID, jP.AggViews,
		jP.PostPropertyNames, jP.PostPropertyValues, jP.ApplicationPostType,
		jP.Points as PostPoints,
		jP.AttachmentFilename,jP.ContentType, jP.IsRemote, jP.Height, jP.Width, jP.FriendlyFileName, jP.ContentSize, jP.[FileName],jP.Created,
		HasRead = 1,
		UserName = jT.PostAuthor,
		Replies = (SELECT COUNT(P2.PostID) FROM cs_Posts P2 (nolock) WHERE P2.ParentID = jP.PostID AND P2.PostLevel != 1 AND IsApproved = 1),
		Downloads = (SELECT COUNT(fD.UserID) FROM cs_Posts_Downloads fD (nolock) WHERE fD.PostID = jP.PostID)
	FROM 
		#PageIndex jPI
		JOIN cs_vw_PostsWithAttachmentDetails jP ON jPI.PostID = jP.PostID
		JOIN cs_Threads jT ON jP.ThreadID = jT.ThreadID
	WHERE 
		jPI.IndexID > @PageLowerBound
		AND jPI.IndexID < @PageUpperBound
		AND jP.SettingsID = @SettingsID and jT.SettingsID = @SettingsID
	ORDER BY
		jPI.IndexID	-- this is the ordering system we're using populated from the @sqlPopulate
	
	IF @IncludeCategories = 1
	Begin
	
		Select 
			Cats.[Name], PIC.PostID
		FROM 
			#PageIndex jPI
			JOIN cs_Posts_InCategories PIC on jPI.PostID = PIC.PostID
			JOIN cs_Post_Categories Cats on PIC.CategoryID = Cats.CategoryID
		WHERE 
			jPI.IndexID > @PageLowerBound
			AND jPI.IndexID < @PageUpperBound
			AND Cats.SettingsID = @SettingsID
	
	End
	
	DROP TABLE #PageIndex

end
GO

grant execute on [dbo].[cs_Media_Posts_Get] to public
go

/***********************************************
* Sproc: cs_Media_Post_Get
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_Media_Post_Get'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

CREATE PROCEDURE [dbo].[cs_Media_Post_Get]
/* Procedure for getting basic information on a single post. */
(
	@PostID int,
	@UserID int,
	@TrackViews bit,
	@SettingsID int
) AS
begin


DECLARE @NextThreadID int
DECLARE @PrevThreadID int
DECLARE @ThreadID int 
DECLARE @SectionID int
DECLARE @SortOrder int
DECLARE @IsApproved bit

SELECT 
	@ThreadID = ThreadID, 
	@SectionID = SectionID, 
	@SortOrder = SortOrder,
	@IsApproved = IsApproved
FROM 
	cs_Posts (nolock) 
WHERE 
	PostID = @PostID

DECLARE @TrackingThread bit

IF @TrackViews = 1
BEGIN
	-- Update the counter for the number of times this post is viewed
	UPDATE cs_Posts SET TotalViews = (TotalViews + 1) WHERE PostID = @PostID
	UPDATE cs_Threads SET TotalViews = (TotalViews + 1) WHERE ThreadID = @ThreadID
END

-- If @UserID is 0 the user is anonymous
IF @UserID > 0 AND @IsApproved = 1
BEGIN

	-- Mark the post as read
	-- *********************
	IF NOT EXISTS (SELECT ThreadID FROM cs_ThreadsRead WHERE ThreadID = @ThreadID AND UserID = @UserID and SettingsID = @SettingsID)
		INSERT INTO cs_ThreadsRead (UserID, ThreadID, SectionID, SettingsID) VALUES (@UserID, @ThreadID, @SectionID, @SettingsID)

END

-- get the anonymous user id for this site
if( @UserID = 0 ) 
BEGIN
		SELECT @UserID = UserID FROM cs_vw_Users_FullUser WHERE UserName = N'Anonymous' and SettingsID = @SettingsID
END


IF EXISTS(SELECT ThreadID FROM cs_TrackedThreads (nolock) WHERE ThreadID = @ThreadID AND UserID=@UserID)
	SELECT @TrackingThread = 1
ELSE
	SELECT @TrackingThread = 0

SELECT
	P.PostID, P.ThreadID, P.ParentID, P.PostAuthor, P.UserID, P.SectionID, P.PostLevel, P.SortOrder, P.Subject, P.PostDate, P.IsApproved,
	P.IsLocked, P.IsIndexed, P.TotalViews, P.Body, P.FormattedBody, P.IPAddress, P.PostType, P.PostMedia, P.EmoticonID, P.SettingsID, P.AggViews,
	P.PostPropertyNames, P.PostPropertyValues, P.ApplicationPostType,
	P.Points as PostPoints,
	P.PostAuthor as [Username],
	T.ThreadDate,
	T.StickyDate,
	T.IsLocked,
	T.IsSticky,
	T.RatingSum,
	T.TotalRatings,
	HasRead = 0,
	/* TODO: the items below needs some massaging */
	EditNotes = null, /*(SELECT EditNotes FROM cs_PostEditNotes WHERE PostID = P.PostID),*/
	IndexInThread = (SELECT Count(P1.PostID) FROM cs_Posts P1 WHERE P1.IsApproved = 1 AND P1.ThreadID = @ThreadID AND P1.SortOrder <= @SortOrder),
	AttachmentFilename,ContentType, IsRemote, FriendlyFileName, ContentSize, [FileName],P.Created, P.Height, P.Width,
	IsModerator = (SELECT Count(*) FROM cs_Moderators WHERE UserID = P.UserID),
	Replies = (SELECT COUNT(*) FROM cs_Posts P2 (nolock) WHERE P2.ParentID = P.PostID AND P2.PostLevel != 1 AND IsApproved = 1),
	Downloads = (SELECT COUNT(fD.UserID) FROM cs_Posts_Downloads fD (nolock) WHERE fD.PostID = P.PostID),
	PrevThreadID = 0,
	NextThreadID = 0,
	UserIsTrackingThread = @TrackingThread
FROM 
	cs_vw_PostsWithAttachmentDetails P
INNER JOIN
	cs_Threads T ON P.ThreadID = T.ThreadID
WHERE 
	P.PostID = @PostID


end
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_Media_Post_Get] to public
go

/***********************************************
* Sproc: cs_Membership_ChangePasswordAnswer
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_Membership_ChangePasswordAnswer'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


CREATE PROCEDURE [dbo].cs_Membership_ChangePasswordAnswer
(
	@ApplicationName nvarchar(256),
	@UserName nvarchar(256),
	@PasswordQuestion  nvarchar(256),
	@PasswordAnswer  nvarchar(256)
)
AS

	UPDATE
		M
	SET
		M.PasswordQuestion = @PasswordQuestion,
		M.PasswordAnswer = @PasswordAnswer
	FROM
		aspnet_Applications A
		INNER JOIN aspnet_Users U ON A.ApplicationId = U.ApplicationId
		INNER JOIN aspnet_Membership M ON U.UserId = M.UserId
	WHERE
		A.ApplicationName = @ApplicationName
		AND U.UserName = @UserName

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_Membership_ChangePasswordAnswer] to public
go
/***********************************************
* Sproc: cs_Membership_GetUsersByName
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_Membership_GetUsersByName'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[cs_Membership_GetUsersByName]
    @ApplicationName       NVARCHAR(256),
    @UserNames         NTEXT

AS
BEGIN

    DECLARE @ApplicationId UNIQUEIDENTIFIER
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN 0


	DECLARE @tbNames	table(Name nvarchar(256) collate database_default NOT NULL PRIMARY KEY) --Has to be removed until we can ensure uniqueness
	DECLARE @tbUsers	table(UserId uniqueidentifier NOT NULL PRIMARY KEY)
	DECLARE @Num		int
	DECLARE @Pos		int
	DECLARE @NextPos	int
	DECLARE @Name		nvarchar(256)

    DECLARE @textpos  int --our chunks position in the ntext
    DECLARE @chunklen smallint
    DECLARE @UserNamesChunk   nvarchar(4000)
	DECLARE @delimiter nvarchar(1)
    DECLARE @leftover nvarchar(4000)

	SET @delimiter = N','
	SET @Num = 0
	SET @textpos = 1
	SET @leftover = ''

	WHILE @textpos <= DATALENGTH(@UserNames) /2
	BEGIN

		SET @chunklen = 4000 - DATALENGTH(@leftover) /2
		SET @UserNamesChunk = ltrim(@leftover + substring(@UserNames, @textpos, @chunklen))
		SET @textpos = @textpos + @chunklen

		SET @Pos = 1 

		WHILE(@Pos <= DATALENGTH(@UserNamesChunk))
		BEGIN
			SELECT @NextPos = CHARINDEX(@delimiter, @UserNamesChunk,  @Pos)
			IF (@NextPos = 0 OR @NextPos IS NULL)
			BEGIN
				--break us from the loop
				SELECT @NextPos = DATALENGTH(@UserNamesChunk) + 1
				--set the leftover string to use again in next iter
				SET @leftover = SUBSTRING(@UserNamesChunk, @Pos, @NextPos - @Pos)
			END
			ELSE
			BEGIN
				SELECT @Name = LOWER(RTRIM(LTRIM(SUBSTRING(@UserNamesChunk, @Pos, @NextPos - @Pos))))

				INSERT INTO @tbNames VALUES (@Name)

				SET @Num = @Num + 1
			END

			SELECT @Pos = @NextPos+1
		END

		IF(@Pos = DATALENGTH(@UserNamesChunk))
			SET @leftover = ''
	END

	INSERT INTO @tbNames VALUES (@leftover)


	INSERT INTO @tbUsers
	  SELECT UserId
	  FROM   dbo.aspnet_Users ar, @tbNames t
	  WHERE  LOWER(t.Name) = ar.LoweredUserName AND ar.ApplicationId = @ApplicationId


    SELECT u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
            m.CreateDate,
            m.LastLoginDate,
            u.LastActivityDate,
            m.LastPasswordChangedDate,
            u.UserId, m.IsLockedOut,
            m.LastLockoutDate
    FROM   dbo.aspnet_Membership m 
			JOIN dbo.aspnet_Users u ON u.UserId = m.UserId
			JOIN @tbUsers t ON u.UserId = t.UserId
    ORDER BY u.UserName

END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_Membership_GetUsersByName] to public
go
/***********************************************
* Sproc: cs_Membership_IsUserNameTaken
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_Membership_IsUserNameTaken'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[cs_Membership_IsUserNameTaken]
    @ApplicationName       NVARCHAR(256),
    @UserName              NVARCHAR(256)

AS
BEGIN

    DECLARE @ApplicationId UNIQUEIDENTIFIER
    SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName

    SELECT  COUNT(*)
    FROM    aspnet_Users
    WHERE   ApplicationId = @ApplicationId AND LoweredUserName = LOWER(@UserName)

END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_Membership_IsUserNameTaken] to public
go
/***********************************************
* Sproc: cs_Membership_RenameUser
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_Membership_RenameUser'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[cs_Membership_RenameUser]
    @ApplicationName       NVARCHAR(256),
    @OldUserName           NVARCHAR(256),
    @NewUserName           NVARCHAR(256)

AS
BEGIN

    DECLARE @ApplicationId UNIQUEIDENTIFIER
    SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName

    -- if new name is available
    IF NOT EXISTS (SELECT * FROM aspnet_Users WHERE ApplicationId = @ApplicationId AND LoweredUserName = LOWER(@NewUserName))
    BEGIN
        -- rename user
        UPDATE  aspnet_Users
        SET     UserName = @NewUserName,
                LoweredUserName = LOWER(@NewUserName)
        WHERE   ApplicationId = @ApplicationId AND UserName = @OldUserName
    END
    ELSE
    BEGIN
        -- new username was already taken
        SELECT 2
    END

    IF (@@ROWCOUNT = 0)
    BEGIN
        -- username not updated (possible old username was incorrect)
        SELECT 0
    END
    ELSE
    BEGIN
        -- username was updated
        SELECT 1
    END

END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_Membership_RenameUser] to public
go
/***********************************************
* Sproc: cs_Membership_ValidatePasswordAnswer
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_Membership_ValidatePasswordAnswer'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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


CREATE PROCEDURE [dbo].cs_Membership_ValidatePasswordAnswer
(
	@ApplicationName nvarchar(256),
	@UserName nvarchar(256),
	@PasswordAnswer nvarchar(256),
	@IsValid bit OUTPUT
)
AS

IF EXISTS
(
	SELECT *
	FROM
		aspnet_Applications A
		INNER JOIN aspnet_Users U ON A.ApplicationId = U.ApplicationId
		INNER JOIN aspnet_Membership M ON U.UserId = M.UserId
	WHERE
		A.ApplicationName = @ApplicationName
		AND U.UserName = @UserName
		AND LOWER(M.PasswordAnswer) = LOWER(@PasswordAnswer)
)
	SET @IsValid = 1
ELSE
	SET @IsValid = 0


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_Membership_ValidatePasswordAnswer] to public
go
/***********************************************
* Sproc: cs_Message_CreateUpdateDelete
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_Message_CreateUpdateDelete'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

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

create procedure [dbo].cs_Message_CreateUpdateDelete
(
	@MessageID int,
	@Title NVarChar(1024),
	@Body NVarChar(4000),
	@Action int,
	@SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
-- CREATE
IF @Action = 0
BEGIN
	SELECT 'Not Implemented'
END
-- UPDATE
ELSE IF @Action  = 1
BEGIN
	UPDATE
		cs_Messages
	SET
		Title = @Title,
		Body = @Body
	WHERE
		MessageID = @MessageID and SettingsID = @SettingsID
END

-- DELETE
ELSE IF @Action = 2
BEGIN
	SELECT 'Not Implemented'
END	


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].cs_Message_CreateUpdateDelete to public
go
/***********************************************
* Sproc: cs_Messaging_DeleteActivityMessagesForUser
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_Messaging_DeleteActivityMessagesForUser'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[cs_Messaging_DeleteActivityMessagesForUser]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[cs_Messaging_DeleteActivityMessagesForUser]
GO

CREATE PROCEDURE [dbo].[cs_Messaging_DeleteActivityMessagesForUser]
    @userId int 
AS

CREATE TABLE #ToDelete (MessageId uniqueidentifier)

INSERT INTO #ToDelete (MessageId)
    SELECT AM.MessageId
    FROM cs_Messaging_ActivityMessages AM
    INNER JOIN cs_Messaging_Messages M ON AM.MessageId = M.MessageId
    WHERE AM.UserId = @userId
    AND M.AuthorId = @userId

DELETE
FROM cs_Messaging_ActivityMessages
WHERE MessageId IN (SELECT MessageId FROM #ToDelete)

DELETE
FROM cs_Messaging_Messages
WHERE MessageId IN (SELECT MessageId FROM #ToDelete)

DROP TABLE #ToDelete

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXECUTE ON [dbo].[cs_Messaging_DeleteActivityMessagesForUser] TO PUBLIC
GO

/***********************************************
* Sproc: cs_Messaging_DeleteProfileAnnouncement
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_Messaging_DeleteProfileAnnouncement'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[cs_Messaging_DeleteProfileAnnouncement]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[cs_Messaging_DeleteProfileAnnouncement]
GO

CREATE PROCEDURE [dbo].[cs_Messaging_DeleteProfileAnnouncement]
    @messageId uniqueidentifier
AS

-- Announcements are defined as messages that have the same
-- authorId and userId (target). In the schema, it's where
-- cs_Messaging_Messages.AuthorId = cs_Messaging_ProfileMessages.UserId

-- First get the row we need into a table variable
DECLARE @temp TABLE (MessageId uniqueidentifier)
INSERT INTO @temp (MessageId)
    SELECT a.MessageId
    FROM cs_Messaging_ProfileMessages a, cs_Messaging_Messages b
    WHERE a.UserId = b.AuthorId
    AND a.MessageId = @messageId
    AND b.MessageId = @messageId

-- Then delete from cs_Messaging_ProfileMessages
DELETE a
FROM cs_Messaging_ProfileMessages a
WHERE a.MessageId IN (SELECT MessageId FROM @temp)

-- And then delete from cs_Messaging_Messages
DELETE a
FROM cs_Messaging_Messages a
WHERE a.MessageId IN (SELECT MessageId FROM @temp)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXECUTE ON [dbo].[cs_Messaging_DeleteProfileAnnouncement] TO PUBLIC
GO
/***********************************************
* Sproc: cs_Messaging_DeleteProfileAnnouncementsForUser
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_Messaging_DeleteProfileAnnouncementsForUser'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[cs_Messaging_DeleteProfileAnnouncementsForUser]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[cs_Messaging_DeleteProfileAnnouncementsForUser]
GO

CREATE PROCEDURE [dbo].[cs_Messaging_DeleteProfileAnnouncementsForUser]
    @userId int
AS

-- Announcements are defined as messages that have the same
-- authorId and userId (target). In the schema, it's where
-- cs_Messaging_Messages.AuthorId = cs_Messaging_ProfileMessages.UserId

-- First get the rows we need into a table variable
DECLARE @temp TABLE (UserId int, MessageId uniqueidentifier)
INSERT INTO @temp (UserId, MessageId)
    SELECT a.UserId, a.MessageId
    FROM cs_Messaging_ProfileMessages a, cs_Messaging_Messages b
    WHERE a.UserId = @userId
    AND a.UserId = b.AuthorId
    AND a.MessageId = b.MessageId

-- Then delete from cs_Messaging_ProfileMessages
DELETE a
FROM cs_Messaging_ProfileMessages a
WHERE a.MessageId IN (SELECT MessageId FROM @temp)

-- And then delete from cs_Messaging_Messages
DELETE a
FROM cs_Messaging_Messages a
WHERE a.MessageId IN (SELECT MessageId FROM @temp)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXECUTE ON [dbo].[cs_Messaging_DeleteProfileAnnouncementsForUser] TO PUBLIC
GO
/***********************************************
* Sproc: cs_Messaging_DeleteProfileComment
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_Messaging_DeleteProfileComment'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[cs_Messaging_DeleteProfileComment]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[cs_Messaging_DeleteProfileComment]
GO

CREATE PROCEDURE [dbo].[cs_Messaging_DeleteProfileComment]
    @userId int,
    @messageId uniqueidentifier 
AS

-- Coments are defined as messages that have different
-- authorId and userId (target). In the schema, it's where
-- cs_Messaging_Messages.AuthorId <> cs_Messaging_ProfileMessages.UserId

-- First get the row we need into a table variable
DECLARE @temp TABLE (MessageId uniqueidentifier)
INSERT INTO @temp (MessageId)
    SELECT a.MessageId
    FROM cs_Messaging_ProfileMessages a, cs_Messaging_Messages b
    WHERE a.UserId <> b.AuthorId
    AND a.UserId = @userId
    AND a.MessageId = @messageId
    AND b.MessageId = @messageId

-- Then delete from cs_Messaging_ProfileMessages
DELETE a
FROM cs_Messaging_ProfileMessages a
WHERE a.MessageId IN (SELECT MessageId FROM @temp)

-- And then delete from cs_Messaging_Messages
DELETE a
FROM cs_Messaging_Messages a
WHERE a.MessageId IN (SELECT MessageId FROM @temp)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXECUTE ON [dbo].[cs_Messaging_DeleteProfileComment] TO PUBLIC
GO
/***********************************************
* Sproc: cs_Messaging_GetActivityMessage
* File Date: 4/14/2008 2:47:59 PM
***********************************************/
Print 'Creating...cs_Messaging_GetActivityMessage'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[cs_Messaging_GetActivityMessage]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[cs_Messaging_GetActivityMessage]
GO

CREATE PROCEDURE [dbo].[cs_Messaging_GetActivityMessage]
    @messageId uniqueidentifier
AS

SELECT a.MessageId, a.AuthorId, a.[Subject], a.Body, a.DateCreated, b.[Type], b.GroupId
FROM cs_Messaging_Messages a, cs_Messaging_ActivityMessages b
WHERE a.MessageId = @messageId
AND b.MessageId = @messageId

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXECUTE ON [dbo].[cs_Messaging_GetActivityMessage] TO PUBLIC
GO

/***********************************************
* Sproc: cs_Messaging_GetActivityMessagesForHub
* File Date: 4/14/2008 4:36:03 PM
***********************************************/
Print 'Creating...cs_Messaging_GetActivityMessagesForHub'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[cs_Messaging_GetActivityMessagesForHub]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[cs_Messaging_GetActivityMessagesForHub]
GO

CREATE PROCEDURE [dbo].[cs_Messaging_GetActivityMessagesForHub]
    @hubId int,
    @pageIndex int,
    @pageSize int,
	@includedMessageTypes varchar(4000),
    @totalCount int output
AS

DECLARE @rowsToReturn int
DECLARE @pageLowerBound int
DECLARE @pageUpperBound int

-- Create temp table
CREATE TABLE #Temp
(
	IndexId int identity (1, 1) NOT NULL,
	MessageId uniqueidentifier NOT NULL,
    AuthorId int NOT NULL,
    [Subject] nvarchar(100) collate database_default NULL,
    Body ntext collate database_default NULL,
    DateCreated datetime NOT NULL,
    [Type] nvarchar(50) collate database_default NOT NULL,
    GroupId int NULL
)

IF (@includedMessageTypes IS NULL OR LEN(@includedMessageTypes) = 0)
BEGIN
	-- Fill temp table with all the hub's activity messages
	INSERT INTO #Temp (MessageId, AuthorId, [Subject], Body, DateCreated, [Type], GroupId)
		SELECT a.MessageId, a.AuthorId, a.Subject, a.Body, a.DateCreated, b.[Type], b.GroupId
		FROM cs_Messaging_Messages a, cs_Messaging_ActivityMessages b
		WHERE a.MessageId = b.MessageId
		AND b.GroupId = @hubId
		ORDER BY a.DateCreated DESC

	SET @totalCount = @@ROWCOUNT

END ELSE BEGIN

	CREATE TABLE #TempMessageTypes ([Type] nvarchar(50) collate database_default NOT NULL)
	
	INSERT INTO #TempMessageTypes exec [dbo].[cs_split_varchar] @str = @includedMessageTypes

	INSERT INTO #Temp (MessageId, AuthorId, [Subject], Body, DateCreated, [Type], GroupId)
		SELECT a.MessageId, a.AuthorId, a.Subject, a.Body, a.DateCreated, b.[Type], b.GroupId
		FROM cs_Messaging_Messages a, cs_Messaging_ActivityMessages b
		WHERE a.MessageId = b.MessageId
		AND b.GroupId = @hubId
		AND b.[Type] in (SELECT [Type] FROM #TempMessageTypes)
		ORDER BY a.DateCreated DESC

	SET @totalCount = @@ROWCOUNT

END

-- Set the rowcount
SET @rowsToReturn = @pageSize * (@pageIndex + 1)
SET ROWCOUNT @rowsToReturn

-- Set the page bounds
SET @pageLowerBound = @pageSize * @pageIndex
SET @pageUpperBound = @pageLowerBound + @pageSize + 1

-- Select from temp table only the proper number of rows
SELECT MessageId, AuthorId, [Subject], Body, DateCreated, [Type], GroupId
FROM #Temp
WHERE #Temp.IndexId > @pageLowerBound
AND #Temp.IndexId <  @pageUpperBound

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXECUTE ON [dbo].[cs_Messaging_GetActivityMessagesForHub] TO PUBLIC
GO
/***********************************************
* Sproc: cs_Messaging_GetActivityMessagesForSite
* File Date: 4/14/2008 2:47:59 PM
***********************************************/
Print 'Creating...cs_Messaging_GetActivityMessagesForSite'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[cs_Messaging_GetActivityMessagesForSite]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[cs_Messaging_GetActivityMessagesForSite]
GO

CREATE PROCEDURE [dbo].[cs_Messaging_GetActivityMessagesForSite]
    @pageIndex int,
    @pageSize int,
	@includedMessageTypes varchar(4000),
    @totalCount int output
AS

DECLARE @rowsToReturn int
DECLARE @pageLowerBound int
DECLARE @pageUpperBound int

-- Create temp table
CREATE TABLE #Temp
(
	IndexId int identity (1, 1) NOT NULL,
	MessageId uniqueidentifier NOT NULL,
    AuthorId int NOT NULL,
    [Subject] nvarchar(100) collate database_default NULL,
    Body ntext collate database_default NULL,
    DateCreated datetime NOT NULL,
    [Type] nvarchar(50) collate database_default NOT NULL,
    GroupId int NULL
)

IF (@includedMessageTypes IS NULL OR LEN(@includedMessageTypes) = 0)
BEGIN

	-- Put all user activity messages into temp table
	INSERT INTO #Temp (MessageId, AuthorId, [Subject], Body, DateCreated, [Type], GroupId)
		SELECT a.MessageId, a.AuthorId, a.[Subject], a.Body, a.DateCreated, b.[Type], b.GroupId
		FROM cs_Messaging_Messages a, cs_Messaging_ActivityMessages b
		WHERE b.GroupId IS NULL
		AND a.MessageId = b.MessageId
		ORDER BY a.DateCreated DESC

	SET @totalCount = @@ROWCOUNT

	-- Put all activity messages for groups into temp table (public groups only)
	INSERT INTO #Temp (MessageId, AuthorId, [Subject], Body, DateCreated, [Type], GroupId)
		SELECT a.MessageId, a.AuthorId, a.[Subject], a.Body, a.DateCreated, b.[Type], b.GroupId
		FROM cs_Messaging_Messages a, cs_Messaging_ActivityMessages b, cs_Sections c
		WHERE b.GroupId IS NOT NULL
		AND a.MessageId = b.MessageId
		AND b.GroupId = c.SectionID
		AND c.IsPublic = 1
		ORDER BY a.DateCreated DESC

	SET @totalCount = @totalCount + @@ROWCOUNT

END ELSE BEGIN

	CREATE TABLE #TempMessageTypes ([Type] nvarchar(50) collate database_default NOT NULL)
	
	INSERT INTO #TempMessageTypes exec [dbo].[cs_split_varchar] @str = @includedMessageTypes

	INSERT INTO #Temp (MessageId, AuthorId, [Subject], Body, DateCreated, [Type], GroupId)
		SELECT a.MessageId, a.AuthorId, a.[Subject], a.Body, a.DateCreated, b.[Type], b.GroupId
		FROM cs_Messaging_Messages a, cs_Messaging_ActivityMessages b
		WHERE b.GroupId IS NULL
		AND a.MessageId = b.MessageId
		AND b.[Type] IN (SELECT [Type] FROM #TempMessageTypes)
		ORDER BY a.DateCreated DESC

	SET @totalCount = @@ROWCOUNT

	INSERT INTO #Temp (MessageId, AuthorId, [Subject], Body, DateCreated, [Type], GroupId)
		SELECT a.MessageId, a.AuthorId, a.[Subject], a.Body, a.DateCreated, b.[Type], b.GroupId
		FROM cs_Messaging_Messages a, cs_Messaging_ActivityMessages b, cs_Sections c
		WHERE b.GroupId IS NOT NULL
		AND a.MessageId = b.MessageId
		AND b.GroupId = c.SectionID
		AND c.IsPublic = 1
		AND b.[Type] IN (SELECT [Type] FROM #TempMessageTypes)
		ORDER BY a.DateCreated DESC

	SET @totalCount = @totalCount + @@ROWCOUNT

END

-- Set the rowcount
SET @rowsToReturn = @pageSize * (@pageIndex + 1)
SET ROWCOUNT @rowsToReturn

-- Set the page bounds
SET @pageLowerBound = @pageSize * @pageIndex
SET @pageUpperBound = @pageLowerBound + @pageSize + 1

-- Select from temp table only the proper number of rows
SELECT MessageId, AuthorId, [Subject], Body, DateCreated, [Type]
FROM #Temp
WHERE #Temp.IndexId > @pageLowerBound
AND #Temp.IndexId <  @pageUpperBound 

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXECUTE ON [dbo].[cs_Messaging_GetActivityMessagesForSite] TO PUBLIC
GO
/***********************************************
* Sproc: cs_Messaging_GetActivityMessagesForUser
* File Date: 4/14/2008 2:47:59 PM
***********************************************/
Print 'Creating...cs_Messaging_GetActivityMessagesForUser'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[cs_Messaging_GetActivityMessagesForUser]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[cs_Messaging_GetActivityMessagesForUser]
GO

CREATE PROCEDURE [dbo].[cs_Messaging_GetActivityMessagesForUser]
    @userId int,
    @pageIndex int,
    @pageSize int,
	@includedMessageTypes varchar(4000),
    @totalCount int output
AS

DECLARE @rowsToReturn int
DECLARE @pageLowerBound int
DECLARE @pageUpperBound int

-- Create temp table
CREATE TABLE #Temp
(
	IndexId int identity (1, 1) NOT NULL,
	MessageId uniqueidentifier NOT NULL,
    AuthorId int NOT NULL,
    [Subject] nvarchar(100) collate database_default NULL,
    Body ntext collate database_default NULL,
    DateCreated datetime NOT NULL,
    [Type] nvarchar(50) collate database_default NOT NULL,
    GroupId int NULL
)

IF (@includedMessageTypes IS NULL OR LEN(@includedMessageTypes) = 0)
BEGIN

	-- Fill temp table with all the user's activity messages
	INSERT INTO #Temp (MessageId, AuthorId, [Subject], Body, DateCreated, [Type], GroupId)
		SELECT a.MessageId, a.AuthorId, a.Subject, a.Body, a.DateCreated, b.[Type], b.GroupId
		FROM cs_Messaging_Messages a, cs_Messaging_ActivityMessages b
		WHERE a.AuthorId = @userId
		AND b.UserId = @userId
		AND a.MessageId = b.MessageId
		AND (b.GroupId IS NULL OR EXISTS(SELECT IsPublic FROM cs_Sections WHERE SectionID = b.GroupId AND IsPublic = 1))
		ORDER BY a.DateCreated DESC

	SET @totalCount = @@ROWCOUNT

END ELSE BEGIN
	
	CREATE TABLE #TempMessageTypes ([Type] nvarchar(50) collate database_default NOT NULL)
	
	INSERT INTO #TempMessageTypes exec [dbo].[cs_split_varchar] @str = @includedMessageTypes
		
	INSERT INTO #Temp (MessageId, AuthorId, [Subject], Body, DateCreated, [Type], GroupId)
		SELECT a.MessageId, a.AuthorId, a.Subject, a.Body, a.DateCreated, b.[Type], b.GroupId
		FROM cs_Messaging_Messages a, cs_Messaging_ActivityMessages b
		WHERE a.AuthorId = @userId
		AND b.UserId = @userId
		AND a.MessageId = b.MessageId
		AND (b.GroupId IS NULL OR EXISTS(SELECT IsPublic FROM cs_Sections WHERE SectionID = b.GroupId AND IsPublic = 1))
		AND b.[Type] in (SELECT [Type] FROM #TempMessageTypes)
		ORDER BY a.DateCreated DESC
	
	SET @totalCount = @@ROWCOUNT

END

-- Set the rowcount
SET @rowsToReturn = @pageSize * (@pageIndex + 1)
SET ROWCOUNT @rowsToReturn

-- Set the page bounds
SET @pageLowerBound = @pageSize * @pageIndex
SET @pageUpperBound = @pageLowerBound + @pageSize + 1

-- Select from temp table only the proper number of rows
SELECT MessageId, AuthorId, [Subject], Body, DateCreated, [Type]
FROM #Temp
WHERE #Temp.IndexId > @pageLowerBound
AND #Temp.IndexId <  @pageUpperBound 

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXECUTE ON [dbo].[cs_Messaging_GetActivityMessagesForUser] TO PUBLIC
GO
/***********************************************
* Sproc: cs_Messaging_GetConversationByConversationId
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_Messaging_GetConversationByConversationId'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[cs_Messaging_GetConversationByConversationId]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[cs_Messaging_GetConversationByConversationId]
GO

CREATE PROCEDURE [dbo].[cs_Messaging_GetConversationByConversationId]
    @conversationId uniqueidentifier
AS

-- Get the metadata
SELECT ConversationId, DateCreated, DateUpdated, ParticipantCount, MessageCount
FROM cs_Messaging_Conversations
WHERE ConversationId = @conversationId

-- Get the messages
SELECT a.MessageId, a.AuthorId, a.[Subject], a.Body, a.DateCreated
FROM cs_Messaging_Messages a, cs_Messaging_Conversations b, cs_Messaging_ConversationMessages c
WHERE c.ConversationId = @conversationId
AND b.ConversationId = c.ConversationId
AND a.MessageId = c.MessageId

-- Get the participants
SELECT ConversationId, ParticipantId, Active
FROM cs_Messaging_ConversationParticipants
WHERE ConversationId = @conversationId

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXECUTE ON [dbo].[cs_Messaging_GetConversationByConversationId] TO PUBLIC
GO

/***********************************************
* Sproc: cs_Messaging_GetConversationByMessageId
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_Messaging_GetConversationByMessageId'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[cs_Messaging_GetConversationByMessageId]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[cs_Messaging_GetConversationByMessageId]
GO

CREATE PROCEDURE [dbo].[cs_Messaging_GetConversationByMessageId]
    @messageId uniqueidentifier
AS

DECLARE @conversationId uniqueidentifier

SET @conversationId = (SELECT ConversationId FROM cs_Messaging_ConversationMessages WHERE MessageId = @messageId)

EXECUTE cs_Messaging_GetConversationByConversationId @conversationId

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXECUTE ON [dbo].[cs_Messaging_GetConversationByMessageId] TO PUBLIC
GO

/***********************************************
* Sproc: cs_Messaging_GetConversationIdsForParticipant
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_Messaging_GetConversationIdsForParticipant'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[cs_Messaging_GetConversationIdsForParticipant]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[cs_Messaging_GetConversationIdsForParticipant]
GO

CREATE PROCEDURE [dbo].[cs_Messaging_GetConversationIdsForParticipant]
    @participantId int,
    @pageIndex int,
    @pageSize int,
    @totalCount int output
AS

DECLARE @rowsToReturn int
DECLARE @pageLowerBound int
DECLARE @pageUpperBound int

-- Create temp table
CREATE TABLE #Temp
(
	IndexId int identity (1, 1) NOT NULL,
	ConversationId uniqueidentifier NOT NULL
)

-- Fill temp table with all the participant's active conversation IDs
INSERT INTO #Temp (ConversationId)
    SELECT cm.ConversationId
	FROM cs_Messaging_Messages mm
	INNER JOIN cs_Messaging_ConversationMessages cm ON cm.MessageId = mm.MessageId
	INNER JOIN
		cs_Messaging_ConversationParticipants cp ON cp.ConversationId = cm.ConversationId
			AND cp.ParticipantId = @participantId
			AND cp.Active = 1
	GROUP BY cm.ConversationId
	ORDER BY max(mm.DateCreated) DESC

-- Select total # of conversations for the participant
SET @totalCount = @@ROWCOUNT

-- Set the rowcount
SET @rowsToReturn = @pageSize * (@pageIndex + 1)
SET ROWCOUNT @rowsToReturn

-- Set the page bounds
SET @pageLowerBound = @pageSize * @pageIndex
SET @pageUpperBound = @pageLowerBound + @pageSize + 1

-- Select from temp table only the proper number of rows
SELECT ConversationId
FROM #Temp
WHERE #Temp.IndexId > @pageLowerBound
AND #Temp.IndexId <  @pageUpperBound

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXECUTE ON [dbo].[cs_Messaging_GetConversationIdsForParticipant] TO PUBLIC
GO
/***********************************************
* Sproc: cs_Messaging_GetConversationMessage
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_Messaging_GetConversationMessage'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[cs_Messaging_GetConversationMessage]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[cs_Messaging_GetConversationMessage]
GO

CREATE PROCEDURE [dbo].[cs_Messaging_GetConversationMessage]
    @messageId uniqueidentifier
AS

SELECT a.MessageId, a.AuthorId, a.[Subject], a.Body, a.DateCreated
FROM cs_Messaging_Messages a, cs_Messaging_ConversationMessages b
WHERE a.MessageId = @messageId
AND b.MessageId = @messageId

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXECUTE ON [dbo].[cs_Messaging_GetConversationMessage] TO PUBLIC
GO

/***********************************************
* Sproc: cs_Messaging_GetFriendsActivityMessagesForUser
* File Date: 4/14/2008 2:47:59 PM
***********************************************/
Print 'Creating...cs_Messaging_GetFriendsActivityMessagesForUser'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[cs_Messaging_GetFriendsActivityMessagesForUser]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[cs_Messaging_GetFriendsActivityMessagesForUser]
GO

CREATE PROCEDURE [dbo].[cs_Messaging_GetFriendsActivityMessagesForUser]
	@userId int,
	@pageIndex int,
	@pageSize int,
	@includedMessageTypes varchar(4000),
	@totalCount int output
AS

DECLARE @rowsToReturn int
DECLARE @pageLowerBound int
DECLARE @pageUpperBound int

-- Create temp table
CREATE TABLE #Temp
(
	IndexId int identity (1, 1) NOT NULL,
	MessageId uniqueidentifier NOT NULL,
	AuthorId int NOT NULL,
	[Subject] nvarchar(100) collate database_default NULL,
	Body ntext collate database_default NULL,
	DateCreated datetime NOT NULL,
	[Type] nvarchar(50) collate database_default NOT NULL,
	GroupId int NULL
)


	IF (@includedMessageTypes IS NULL OR LEN(@includedMessageTypes) = 0)
	BEGIN
		-- Fill temp table with all activity messages for friends
		INSERT INTO #Temp (MessageId, AuthorId, [Subject], Body, DateCreated, [Type], GroupId)
			SELECT M.MessageId, M.AuthorId, M.Subject, M.Body, M.DateCreated, AM.[Type], AM.GroupId
			FROM cs_Messaging_Messages M
			INNER JOIN cs_Messaging_ActivityMessages AM ON M.MessageId = AM.MessageId
			INNER JOIN cs_Friends_xref F ON M.AuthorId = F.FriendUserID
			WHERE AM.UserId = F.FriendUserID
			AND AM.GroupId IS NULL
			AND F.UserID = @userId
			ORDER BY M.DateCreated DESC

		/* Select total # of activity messages */
		SET @totalCount = @@ROWCOUNT
	END ELSE BEGIN

		CREATE TABLE #TempMessageTypes ([Type] nvarchar(50) collate database_default NOT NULL)
	
		INSERT INTO #TempMessageTypes exec [dbo].[cs_split_varchar] @str = @includedMessageTypes

		INSERT INTO #Temp (MessageId, AuthorId, [Subject], Body, DateCreated, [Type], GroupId)
			SELECT M.MessageId, M.AuthorId, M.Subject, M.Body, M.DateCreated, AM.[Type], AM.GroupId
			FROM cs_Messaging_Messages M
			INNER JOIN cs_Messaging_ActivityMessages AM ON M.MessageId = AM.MessageId
			INNER JOIN cs_Friends_xref F ON M.AuthorId = F.FriendUserID
			WHERE AM.UserId = F.FriendUserID
			AND AM.GroupId IS NULL
			AND F.UserID = @userId
			AND AM.[Type] in (SELECT [Type] FROM #TempMessageTypes)
			ORDER BY M.DateCreated DESC

		/* Select total # of activity messages */
		SET @totalCount = @@ROWCOUNT
	END
    
-- Set the rowcount
SET @rowsToReturn = @pageSize * (@pageIndex + 1)
SET ROWCOUNT @rowsToReturn

-- Set the page bounds
SET @pageLowerBound = @pageSize * @pageIndex
SET @pageUpperBound = @pageLowerBound + @pageSize + 1

-- Select from temp table only the proper number of rows
SELECT MessageId, AuthorId, [Subject], Body, DateCreated, [Type]
FROM #Temp
WHERE #Temp.IndexId > @pageLowerBound
AND #Temp.IndexId <  @pageUpperBound 

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXECUTE ON [dbo].[cs_Messaging_GetFriendsActivityMessagesForUser] TO PUBLIC
GO

/***********************************************
* Sproc: cs_Messaging_GetProfileAnnouncement
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_Messaging_GetProfileAnnouncement'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[cs_Messaging_GetProfileAnnouncement]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[cs_Messaging_GetProfileAnnouncement]
GO

CREATE PROCEDURE [dbo].[cs_Messaging_GetProfileAnnouncement]
    @messageId uniqueidentifier
AS

-- Announcements are defined as messages that have the same
-- authorId and userId (target). In the schema, it's where
-- cs_Messaging_Messages.AuthorId = cs_Messaging_ProfileMessages.UserId

SELECT a.MessageId, a.AuthorId, a.[Subject], a.Body, a.DateCreated, b.UserId
FROM cs_Messaging_Messages a, cs_Messaging_ProfileMessages b
WHERE a.AuthorId = b.UserId
AND a.MessageId = @messageId
AND b.MessageId = @messageId

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXECUTE ON [dbo].[cs_Messaging_GetProfileAnnouncement] TO PUBLIC
GO
/***********************************************
* Sproc: cs_Messaging_GetProfileAnnouncementsForUser
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_Messaging_GetProfileAnnouncementsForUser'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[cs_Messaging_GetProfileAnnouncementsForUser]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[cs_Messaging_GetProfileAnnouncementsForUser]
GO

CREATE PROCEDURE [dbo].[cs_Messaging_GetProfileAnnouncementsForUser]
    @userId int,
    @pageIndex int,
    @pageSize int,
    @totalCount int output
AS

DECLARE @rowsToReturn int
DECLARE @pageLowerBound int
DECLARE @pageUpperBound int

-- Create temp table
CREATE TABLE #Temp
(
	IndexId int identity (1, 1) NOT NULL,
	MessageId uniqueidentifier NOT NULL,
    AuthorId int NOT NULL,
    [Subject] nvarchar(100) collate database_default NULL,
    Body ntext collate database_default NULL,
    DateCreated datetime NOT NULL,
    UserId int NOT NULL
)

-- Fill temp table with all the user's announcements
INSERT INTO #Temp (MessageId, AuthorId, [Subject], Body, DateCreated, UserId)
    SELECT a.MessageId, a.AuthorId, a.Subject, a.Body, a.DateCreated, b.UserId
    FROM cs_Messaging_Messages a, cs_Messaging_ProfileMessages b
    WHERE b.UserId = @userId
    AND a.AuthorId = b.UserId
    AND a.MessageId = b.MessageId
    ORDER BY a.DateCreated DESC

-- Select total # of announcements for the user
SET @totalCount = @@ROWCOUNT

-- Set the rowcount
SET @rowsToReturn = @pageSize * (@pageIndex + 1)
SET ROWCOUNT @rowsToReturn

-- Set the page bounds
SET @pageLowerBound = @pageSize * @pageIndex
SET @pageUpperBound = @pageLowerBound + @pageSize + 1

-- Select from temp table only the proper number of rows
SELECT MessageId, AuthorId, [Subject], Body, DateCreated, UserId
FROM #Temp
WHERE #Temp.IndexId > @pageLowerBound
AND #Temp.IndexId <  @pageUpperBound

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXECUTE ON [dbo].[cs_Messaging_GetProfileAnnouncementsForUser] TO PUBLIC
GO

/***********************************************
* Sproc: cs_Messaging_GetProfileCommentsByUser
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_Messaging_GetProfileCommentsByUser'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[cs_Messaging_GetProfileCommentsByUser]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[cs_Messaging_GetProfileCommentsByUser]
GO

CREATE PROCEDURE [dbo].[cs_Messaging_GetProfileCommentsByUser]
    @userId int,
    @pageIndex int,
    @pageSize int,
    @totalCount int output
AS

DECLARE @rowsToReturn int
DECLARE @pageLowerBound int
DECLARE @pageUpperBound int

-- Create temp table
CREATE TABLE #Temp
(
	IndexId int identity (1, 1) NOT NULL,
	MessageId uniqueidentifier NOT NULL,
    AuthorId int NOT NULL,
    [Subject] nvarchar(100) collate database_default NULL,
    Body ntext collate database_default NULL,
    DateCreated datetime NOT NULL,
    UserId int NOT NULL
)

-- Fill temp table with all profile comments made by the user
INSERT INTO #Temp (MessageId, AuthorId, [Subject], Body, DateCreated, UserId)
    SELECT a.MessageId, a.AuthorId, a.Subject, a.Body, a.DateCreated, b.UserId
    FROM cs_Messaging_Messages a, cs_Messaging_ProfileMessages b
    WHERE a.AuthorId = @userId
    AND a.AuthorId <> b.UserId
    AND a.MessageId = b.MessageId
    ORDER BY a.DateCreated DESC

-- Select total # of profile comments made by the user
SET @totalCount = @@ROWCOUNT

-- Set the rowcount
SET @rowsToReturn = @pageSize * (@pageIndex + 1)
SET ROWCOUNT @rowsToReturn

-- Set the page bounds
SET @pageLowerBound = @pageSize * @pageIndex
SET @pageUpperBound = @pageLowerBound + @pageSize + 1

-- Select from temp table only the proper number of rows
SELECT MessageId, AuthorId, [Subject], Body, DateCreated, UserId
FROM #Temp
WHERE #Temp.IndexId > @pageLowerBound
AND #Temp.IndexId <  @pageUpperBound

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXECUTE ON [dbo].[cs_Messaging_GetProfileCommentsByUser] TO PUBLIC
GO
/***********************************************
* Sproc: cs_Messaging_GetProfileCommentsForUser
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_Messaging_GetProfileCommentsForUser'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[cs_Messaging_GetProfileCommentsForUser]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[cs_Messaging_GetProfileCommentsForUser]
GO

CREATE PROCEDURE [dbo].[cs_Messaging_GetProfileCommentsForUser]
    @userId int,
    @pageIndex int,
    @pageSize int,
    @totalCount int output
AS

DECLARE @rowsToReturn int
DECLARE @pageLowerBound int
DECLARE @pageUpperBound int

-- Create temp table
CREATE TABLE #Temp
(
	IndexId int identity (1, 1) NOT NULL,
	MessageId uniqueidentifier NOT NULL,
    AuthorId int NOT NULL,
    [Subject] nvarchar(100) collate database_default NULL,
    Body ntext collate database_default NULL,
    DateCreated datetime NOT NULL,
    UserId int NOT NULL
)

-- Fill temp table with all profile comments for the user
INSERT INTO #Temp (MessageId, AuthorId, [Subject], Body, DateCreated, UserId)
    SELECT a.MessageId, a.AuthorId, a.Subject, a.Body, a.DateCreated, b.UserId
    FROM cs_Messaging_Messages a, cs_Messaging_ProfileMessages b
    WHERE b.UserId = @userId
    AND a.AuthorId <> b.UserId
    AND a.MessageId = b.MessageId
    ORDER BY a.DateCreated DESC

-- Select total # of profile comments for the user
SET @totalCount = @@ROWCOUNT

-- Set the rowcount
SET @rowsToReturn = @pageSize * (@pageIndex + 1)
SET ROWCOUNT @rowsToReturn

-- Set the page bounds
SET @pageLowerBound = @pageSize * @pageIndex
SET @pageUpperBound = @pageLowerBound + @pageSize + 1

-- Select from temp table only the proper number of rows
SELECT MessageId, AuthorId, [Subject], Body, DateCreated, UserId
FROM #Temp
WHERE #Temp.IndexId > @pageLowerBound
AND #Temp.IndexId <  @pageUpperBound

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXECUTE ON [dbo].[cs_Messaging_GetProfileCommentsForUser] TO PUBLIC
GO
/***********************************************
* Sproc: cs_Messaging_GetProfileMessage
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_Messaging_GetProfileMessage'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[cs_Messaging_GetProfileMessage]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[cs_Messaging_GetProfileMessage]
GO

CREATE PROCEDURE [dbo].[cs_Messaging_GetProfileMessage]
	@messageId uniqueidentifier
AS

SELECT a.MessageId, a.AuthorId, a.[Subject], a.Body, a.DateCreated, b.UserId
FROM cs_Messaging_Messages a, cs_Messaging_ProfileMessages b
WHERE a.MessageId = @messageId
AND b.MessageId = @messageId

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXECUTE ON [dbo].[cs_Messaging_GetProfileMessage] TO PUBLIC
GO

/***********************************************
* Sproc: cs_Messaging_GetProfileMessagesForUser
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_Messaging_GetProfileMessagesForUser'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[cs_Messaging_GetProfileMessagesForUser]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[cs_Messaging_GetProfileMessagesForUser]
GO

CREATE PROCEDURE [dbo].[cs_Messaging_GetProfileMessagesForUser]
    @userId int,
    @pageIndex int,
    @pageSize int,
    @totalCount int output
AS

DECLARE @rowsToReturn int
DECLARE @pageLowerBound int
DECLARE @pageUpperBound int

-- Create temp table
CREATE TABLE #Temp
(
	IndexId int identity (1, 1) NOT NULL,
	MessageId uniqueidentifier NOT NULL,
    AuthorId int NOT NULL,
    [Subject] nvarchar(100) collate database_default NULL,
    Body ntext collate database_default NULL,
    DateCreated datetime NOT NULL,
    UserId int NOT NULL
)

-- Fill temp table with all the user's profile messages (announcements and comments)
INSERT INTO #Temp (MessageId, AuthorId, [Subject], Body, DateCreated, UserId)
    SELECT a.MessageId, a.AuthorId, a.Subject, a.Body, a.DateCreated, b.UserId
    FROM cs_Messaging_Messages a, cs_Messaging_ProfileMessages b
    WHERE b.UserId = @userId
    AND a.MessageId = b.MessageId
    ORDER BY a.DateCreated DESC

-- Select total # of profile messages for the user
SET @totalCount = @@ROWCOUNT

-- Set the rowcount
SET @rowsToReturn = @pageSize * (@pageIndex + 1)
SET ROWCOUNT @rowsToReturn

-- Set the page bounds
SET @pageLowerBound = @pageSize * @pageIndex
SET @pageUpperBound = @pageLowerBound + @pageSize + 1

-- Select from temp table only the proper number of rows
SELECT MessageId, AuthorId, [Subject], Body, DateCreated, UserId
FROM #Temp
WHERE #Temp.IndexId > @pageLowerBound
AND #Temp.IndexId <  @pageUpperBound

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXECUTE ON [dbo].[cs_Messaging_GetProfileMessagesForUser] TO PUBLIC
GO
/***********************************************
* Sproc: cs_Messaging_InsertIntoActivityMessages
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_Messaging_InsertIntoActivityMessages'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[cs_Messaging_InsertIntoActivityMessages]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[cs_Messaging_InsertIntoActivityMessages]
GO

CREATE PROCEDURE [dbo].[cs_Messaging_InsertIntoActivityMessages]
    @messageId uniqueidentifier,
    @userId int,
    @groupId int = NULL,
    @type nvarchar(50)
AS

INSERT INTO cs_Messaging_ActivityMessages (MessageId, UserId, GroupId, [Type])
VALUES (@messageId, @userId, @groupId, @type)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXECUTE ON [dbo].[cs_Messaging_InsertIntoActivityMessages] TO PUBLIC
GO

/***********************************************
* Sproc: cs_Messaging_InsertIntoConversationMessages
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_Messaging_InsertIntoConversationMessages'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[cs_Messaging_InsertIntoConversationMessages]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[cs_Messaging_InsertIntoConversationMessages]
GO

CREATE PROCEDURE [dbo].[cs_Messaging_InsertIntoConversationMessages]
    @conversationId uniqueidentifier,
    @messageId uniqueidentifier
AS

INSERT INTO cs_Messaging_ConversationMessages (ConversationId, MessageId)
VALUES (@conversationId, @messageId)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXECUTE ON [dbo].[cs_Messaging_InsertIntoConversationMessages] TO PUBLIC
GO
/***********************************************
* Sproc: cs_Messaging_InsertIntoConversationParticipants
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_Messaging_InsertIntoConversationParticipants'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[cs_Messaging_InsertIntoConversationParticipants]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[cs_Messaging_InsertIntoConversationParticipants]
GO

CREATE PROCEDURE [dbo].[cs_Messaging_InsertIntoConversationParticipants]
    @conversationId uniqueidentifier,
    @participantId int,
    @active bit
AS

INSERT INTO cs_Messaging_ConversationParticipants (ConversationId, ParticipantId, Active)
VALUES (@conversationId, @participantId, @active)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXECUTE ON [dbo].[cs_Messaging_InsertIntoConversationParticipants] TO PUBLIC
GO
/***********************************************
* Sproc: cs_Messaging_InsertIntoConversations
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_Messaging_InsertIntoConversations'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[cs_Messaging_InsertIntoConversations]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[cs_Messaging_InsertIntoConversations]
GO

CREATE PROCEDURE [dbo].[cs_Messaging_InsertIntoConversations]
    @conversationId uniqueidentifier,
    @dateCreated datetime,
    @dateUpdated datetime,
    @participantCount int,
    @messageCount int
AS

INSERT INTO cs_Messaging_Conversations (ConversationId, DateCreated, DateUpdated, ParticipantCount, MessageCount)
VALUES (@conversationId, @dateCreated, @dateUpdated, @participantCount, @messageCount)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXECUTE ON [dbo].[cs_Messaging_InsertIntoConversations] TO PUBLIC
GO
/***********************************************
* Sproc: cs_Messaging_InsertIntoMessages
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_Messaging_InsertIntoMessages'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[cs_Messaging_InsertIntoMessages]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[cs_Messaging_InsertIntoMessages]
GO

CREATE PROCEDURE [dbo].[cs_Messaging_InsertIntoMessages]
    @messageId uniqueidentifier,
    @authorId int,
    @subject nvarchar(100),
    @body ntext,
    @dateCreated datetime
AS

INSERT INTO cs_Messaging_Messages (MessageId, AuthorId, [Subject], Body, DateCreated)
VALUES (@messageId, @authorId, @subject, @body, @dateCreated)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXECUTE ON [dbo].[cs_Messaging_InsertIntoMessages] TO PUBLIC
GO
/***********************************************
* Sproc: cs_Messaging_InsertIntoProfileMessages
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_Messaging_InsertIntoProfileMessages'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[cs_Messaging_InsertIntoProfileMessages]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[cs_Messaging_InsertIntoProfileMessages]
GO

CREATE PROCEDURE [dbo].[cs_Messaging_InsertIntoProfileMessages]
    @userId int,
    @messageId uniqueidentifier
AS

INSERT INTO cs_Messaging_ProfileMessages (UserId, MessageId)
VALUES (@userId, @messageId)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXECUTE ON [dbo].[cs_Messaging_InsertIntoProfileMessages] TO PUBLIC
GO
/***********************************************
* Sproc: cs_Messaging_UpdateConversationParticipants
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_Messaging_UpdateConversationParticipants'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[cs_Messaging_UpdateConversationParticipants]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[cs_Messaging_UpdateConversationParticipants]
GO

CREATE PROCEDURE [dbo].[cs_Messaging_UpdateConversationParticipants]
    @conversationId uniqueidentifier,
    @participantId int,
    @active bit 
AS

UPDATE cs_Messaging_ConversationParticipants SET
    Active = @active
WHERE ConversationId = @conversationId
AND ParticipantId = @participantId

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXECUTE ON [dbo].[cs_Messaging_UpdateConversationParticipants] TO PUBLIC
GO
/***********************************************
* Sproc: cs_Messaging_UpdateConversations
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_Messaging_UpdateConversations'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[cs_Messaging_UpdateConversations]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[cs_Messaging_UpdateConversations]
GO

CREATE PROCEDURE [dbo].[cs_Messaging_UpdateConversations]
    @conversationId uniqueidentifier,
    @dateUpdated datetime,
    @messageCount int
AS

UPDATE cs_Messaging_Conversations SET
    DateUpdated = @dateUpdated,
    MessageCount = @messageCount
WHERE ConversationId = @conversationId

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXECUTE ON [dbo].[cs_Messaging_UpdateConversations] TO PUBLIC
GO
/***********************************************
* Sproc: cs_Messaging_UpdateProfileAnnouncement
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_Messaging_UpdateProfileAnnouncement'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[cs_Messaging_UpdateProfileAnnouncement]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[cs_Messaging_UpdateProfileAnnouncement]
GO

CREATE PROCEDURE [dbo].[cs_Messaging_UpdateProfileAnnouncement]
    @messageId uniqueidentifier,
    @subject nvarchar(100),
    @body ntext
AS

-- Announcements are defined as messages that have the same
-- authorId and userId (target). In the schema, it's where
-- cs_Messaging_Messages.AuthorId = cs_Messaging_ProfileMessages.UserId

UPDATE cs_Messaging_Messages SET
    [Subject] = @subject,
    Body = @body
FROM cs_Messaging_ProfileMessages a, cs_Messaging_Messages b
WHERE a.UserId = b.AuthorId
AND a.MessageId = @messageId
AND b.MessageId = @messageId

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXECUTE ON [dbo].[cs_Messaging_UpdateProfileAnnouncement] TO PUBLIC
GO
/***********************************************
* Sproc: cs_Moderate_ApprovePost
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_Moderate_ApprovePost'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


CREATE         procedure [dbo].cs_Moderate_ApprovePost
(
	@PostID		int,
	@ApprovedBy	int,
	@SettingsID 	int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
DECLARE @SectionID 	int
DECLARE @ThreadID 	int
DECLARE @PostLevel 	int
DECLARE @UserID		int
DECLARE @IsLocked	bit

-- first make sure that the post is ALREADY non-approved
IF (SELECT IsApproved FROM cs_Posts (nolock) WHERE PostID = @PostID and SettingsID = @SettingsID) = 1
BEGIN
	print 'Post is already approved'
	SELECT 0
	RETURN
END
ELSE
BEGIN

	print 'Post is not approved'

	-- Get details about the thread and forum this post belongs in
	SELECT
		@SectionID = SectionID,
		@ThreadID = ThreadID,
		@PostLevel = PostLevel,
		@UserID	= UserID,
		@IsLocked = IsLocked
	FROM
		cs_Posts
	WHERE
		PostID = @PostID and SettingsID = @SettingsID

	-- Approve the post
	UPDATE 
		cs_Posts
	SET 
		IsApproved = 1
	WHERE 
		PostID = @PostID and SettingsID = @SettingsID

	-- Approved the thread if necessary
	IF @PostLevel = 1
		UPDATE
			cs_Threads
		SET
			IsApproved = 1
		WHERE
			ThreadID = @ThreadID and SettingsID = @SettingsID

	-- Update the user's post count
	exec cs_system_UpdateUserPostCount @SectionID, @UserID, @SettingsID

	-- Update the forum statistics
	exec cs_system_UpdateForum @SectionID, @ThreadID, @PostID, @SettingsID

	-- Clean up unnecessary columns in forumsread
	-- exec cs_system_CleanForumsRead @SectionID, @SettingsID

	-- update the thread stats
	exec cs_system_UpdateThread @ThreadID, @PostID, @SettingsID

	-- Update Moderation audit table
	-- Update the ModerationAudit table
	exec cs_system_ModerationAction_AuditEntry 1, @ApprovedBy, @PostID, null, @SectionID, @SettingsID, null

	-- Send back a success code
	SELECT 1
	
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_Moderate_ApprovePost] to public
go
/***********************************************
* Sproc: cs_Moderate_CheckUser
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_Moderate_CheckUser'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

CREATE procedure [dbo].cs_Moderate_CheckUser 
(
	@UserID		int,
	@SectionID	int,
	@SettingsID 	int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED

IF EXISTS(SELECT SectionID FROM cs_Moderators WHERE UserID = @UserID AND SectionID = 0 and SettingsID = @SettingsID)
  SELECT 1
ELSE
  IF EXISTS (SELECT SectionID FROM cs_Moderators WHERE UserID = @UserID AND SectionID = @SectionID AND SettingsID = @SettingsID)
    SELECT 1
  ELSE
    SELECT 0



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_Moderate_CheckUser] to public
go
/***********************************************
* Sproc: cs_Moderate_DeletePost
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_Moderate_DeletePost'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

CREATE PROCEDURE [dbo].cs_Moderate_DeletePost
(
	@PostID INT,
	@DeletedBy INT,
	@Reason NVARCHAR(1024) = '',
	@DeleteChildPosts BIT = 1,
	@SettingsID int
)
AS
-- Deletes the post

SET Transaction Isolation Level Read UNCOMMITTED

DECLARE @IsApproved bit
DECLARE @SectionID int

-- First, get information about the post that is about to be deleted.
SELECT
    @IsApproved = IsApproved,
    @SectionID = SectionID
FROM
    cs_Posts
WHERE
    PostID = @PostID and SettingsID = @SettingsID

-- If the post is not approved, permanently delete the post
IF (@IsApproved = 0)
BEGIN	
    	-- Delete the post.
	DELETE FROM cs_Posts WHERE PostID = @PostID AND SettingsID = @SettingsID

	-- Update moderation statistics
	UPDATE	cs_Sections
	SET		PostsToModerate = (SELECT Count(PostID) FROM cs_Posts WHERE SectionID = @SectionID AND IsApproved = 0 and SettingsID = @SettingsID)
	WHERE	SectionID = @SectionID AND SettingsID = @SettingsID

	-- Update Moderation Audit table
	IF (@Reason IS NULL OR @Reason = '')
		SET @Reason = 'Automatic generated reason: the post has been deleted on request.'

	EXEC cs_system_ModerationAction_AuditEntry 4, @DeletedBy, @PostID, null, null, @SettingsID, @Reason
END	
ELSE
BEGIN
	DECLARE @DeletedSectionID INT
	DECLARE @OldThreadID INT
	DECLARE @NewThreadID INT
	
	-- Init data	
	SELECT @DeletedSectionID = SectionID FROM cs_Sections WHERE SettingsID = @SettingsID and ForumType = 50
	SELECT @OldThreadID = ThreadID, @SectionID = SectionID FROM cs_Posts WHERE SettingsID = @SettingsID AND PostID = @PostID
	SET @NewThreadID = NULL

	IF (@SectionID <> 0)
	BEGIN
		IF (@DeleteChildPosts = 1)
			-- Move the post and its replies to 'Deleted Posts' forum
			EXEC cs_system_DeletePostAndChildren @PostID, @DeleteChildPosts, @SettingsID, @DeletedBy, @Reason, @NewThreadID OUT, NULL

		ELSE -- (@DeleteChildPosts <> 1)
			-- Move the post to 'Deleted Posts' forum, reassign children to deleted post's parent
			EXEC cs_system_DeletePostAndAdoptChildren @PostID, @SettingsID, @DeletedBy, @Reason, @NewThreadID OUTPUT

		-- Statistics update
		EXEC cs_system_ResetThreadStatistics @OldThreadID
		EXEC cs_system_ResetThreadStatistics @NewThreadID
		EXEC cs_system_ResetForumStatistics @SectionID
		EXEC cs_system_ResetForumStatistics @DeletedSectionID
	END
END

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


grant execute on [dbo].[cs_Moderate_DeletePost] to public
go
/***********************************************
* Sproc: cs_Moderate_Forums
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_Moderate_Forums'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

CREATE procedure [dbo].cs_Moderate_Forums
	(
		@SettingsID	int,
		@UserID int,	
		@ApplicationType smallint = 0
	)
	AS
	SET Transaction Isolation Level Read UNCOMMITTED
	BEGIN
	
SELECT
	LastUserActivity = '1/1/1797',
	F.SectionID, F.SettingsID, F.IsActive, F.ParentID, F.GroupID, F.Name, F.NewsgroupName, F.Description, F.DateCreated, F.Url, F.IsModerated,
	F.DaysToView, F.SortOrder, F.TotalPosts, F.TotalThreads, F.DisplayMask, F.EnablePostStatistics, F.EnablePostPoints, F.EnableAnonymousPosting,
	F.MostRecentPostID, F.MostRecentThreadID, F.MostRecentThreadReplies, F.MostRecentPostSubject, F.MostRecentPostAuthor,
	F.MostRecentPostAuthorID, F.MostRecentPostDate, F.PostsToModerate, F.ForumType, F.IsSearchable, F.ApplicationType, F.ApplicationKey, F.Path,
	F.PropertyNames as SectionPropertyNames, F.PropertyValues as SectionPropertyValues, F.DefaultLanguage, F.IsPublic, F.HubSectionID, F.IsCommunityAggregated, W.*
FROM
	cs_Sections F
	--will this ever return a weblog?
	LEFT OUTER JOIN cs_weblog_Weblogs W on F.SectionID = W.SectionID 
WHERE
	F.IsActive = 1 AND F.SettingsID = @SettingsID AND F.ApplicationType = @ApplicationType

	END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_Moderate_Forums] to public
go
/***********************************************
* Sproc: cs_Moderate_Forum_Roles
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_Moderate_Forum_Roles'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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




create procedure [dbo].cs_Moderate_Forum_Roles
(
	@SectionID	int,
	@SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED

DECLARE @Permissions TABLE
(
	RoleID uniqueidentifier NOT NULL,
	ProductAllow bit NULL,
	ProductDeny bit NULL,
	SectionAllow bit NULL,
	SectionDeny bit NULL
)

INSERT
	@Permissions
SELECT
	PP.RoleID,
	CASE WHEN (PP.AllowMask & CONVERT(bigint, 0x0000100000000000)) = (0x0000100000000000) THEN 1 ELSE 0 END,
	CASE WHEN (PP.DenyMask & CONVERT(bigint, 0x0000100000000000)) = (0x0000100000000000) THEN 1 ELSE 0 END, 
	NULL,
	NULL
FROM
	cs_ProductPermissions PP
WHERE
	PP.ApplicationType = 0
	AND PP.SettingsID = @SettingsID

UPDATE
	P
SET
	SectionAllow = CASE WHEN (SP.AllowMask & CONVERT(bigint, 0x0000100000000000)) = (0x0000100000000000) THEN 1 ELSE 0 END,
	SectionDeny = CASE WHEN (SP.DenyMask & CONVERT(bigint, 0x0000100000000000)) = (0x0000100000000000) THEN 1 ELSE 0 END
FROM
	@Permissions P INNER JOIN cs_SectionPermissions SP ON P.RoleID = SP.RoleID
WHERE
	SP.SectionID = @SectionID
	AND SP.SettingsID = @SettingsID

SELECT
	RoleID
FROM
	@Permissions
WHERE
	SectionAllow = 1 OR
	(ProductAllow = 1 AND ProductDeny = 0 AND SectionAllow IS NULL AND SectionDeny IS NULL)


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].cs_Moderate_Forum_Roles to public
go
/***********************************************
* Sproc: cs_Moderate_Post_Move
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_Moderate_Post_Move'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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



CREATE    PROCEDURE [dbo].cs_Moderate_Post_Move
(
    @PostID int,
    @MoveToSectionID int,
    @MovedBy int,
    @IsModeratorOfTargetForum bit,
    @SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
DECLARE @ThreadID INT
DECLARE @SectionID INT
DECLARE @IsApproved BIT
DECLARE @PostLevel INT
DECLARE @Notes NVARCHAR(1024)

-- First, get information about the post that is about to be moved.
SELECT
	@ThreadID = ThreadID,
	@SectionID = SectionID,
	@PostLevel = PostLevel,
	@IsApproved = IsApproved
FROM
	cs_Posts
WHERE
	PostID = @PostID and SettingsID = @SettingsID

-- EAD: We only move the post if it is a top level post.
IF @PostLevel = 1
BEGIN
	DECLARE @MoveToIsModerated SMALLINT
	
	-- Get information about the destination forum.
	SELECT 
		@MoveToIsModerated = IsModerated
	FROM 
		cs_Sections
	WHERE 
		SectionID = @MoveToSectionID and SettingsID = @SettingsID
	
	-- If the post is not already approved, check the moderation status and permissions on the moderator for approved status.
	IF @IsApproved = 0
	BEGIN
		-- If the destination forum requires moderation, make sure the moderator has permission.
		IF @MoveToIsModerated = 1
		BEGIN
			IF @IsModeratorOfTargetForum = 1
			BEGIN
				-- The moderator has permissions to move the post and approve it.		
				UPDATE
					cs_Posts
				SET 
					SectionID = @MoveToSectionID
				WHERE 
					ThreadID = @ThreadID and SettingsID = @SettingsID

				UPDATE
					cs_PostAttachments
				SET
					SectionID = @MoveToSectionID
				WHERE
					PostID IN (SELECT PostID FROM cs_Posts WHERE ThreadID = @ThreadID AND SettingsID = @SettingsID)

				UPDATE
					cs_Threads
				SET 
					SectionID = @MoveToSectionID
				WHERE 
					ThreadID = @ThreadID and SettingsID = @SettingsID

				-- approve the post
				EXEC cs_Moderate_ApprovePost @PostID, @MovedBy, @SettingsID
				
				SET @Notes = 'The post was moved and approved.'
				PRINT @Notes
				SELECT 2
			END
			ELSE BEGIN
				-- The moderator has permissions to move the post but not approve.			
				UPDATE
					cs_Posts
				SET 
					SectionID = @MoveToSectionID
				WHERE 
					ThreadID = @ThreadID and SettingsID = @SettingsID

				UPDATE
					cs_PostAttachments
				SET
					SectionID = @MoveToSectionID
				WHERE
					PostID IN (SELECT PostID FROM cs_Posts WHERE ThreadID = @ThreadID AND SettingsID = @SettingsID)

				UPDATE
					cs_Threads
				SET 
					SectionID = @MoveToSectionID
				WHERE 
					ThreadID = @ThreadID and SettingsID = @SettingsID
				
				SET @Notes = 'The post was moved but not approved.'
				PRINT @Notes
				SELECT 1
			END
		END
		ELSE BEGIN
			UPDATE
				cs_Posts
			SET 
				SectionID = @MoveToSectionID
			WHERE 
				ThreadID = @ThreadID and SettingsID = @SettingsID

			UPDATE
				cs_PostAttachments
			SET
				SectionID = @MoveToSectionID
			WHERE
				PostID IN (SELECT PostID FROM cs_Posts WHERE ThreadID = @ThreadID AND SettingsID = @SettingsID)

			UPDATE
				cs_Threads
			SET 
				SectionID = @MoveToSectionID
			WHERE 
				ThreadID = @ThreadID and SettingsID = @SettingsID

			-- The destination forum is not moderated, approve the post and move the post.
			EXEC cs_Moderate_ApprovePost @PostID, @MovedBy, @SettingsID
			
			SET @Notes = 'The post was moved and approved.'
			PRINT @Notes
			SELECT 2
		END
	END
	ELSE BEGIN
		-- The post is already approved, move the post.
		UPDATE
			cs_Posts
		SET 
			SectionID = @MoveToSectionID
		WHERE 
			ThreadID = @ThreadID and SettingsID = @SettingsID

		UPDATE
			cs_PostAttachments
		SET
			SectionID = @MoveToSectionID
		WHERE
			PostID IN (SELECT PostID FROM cs_Posts WHERE ThreadID = @ThreadID AND SettingsID = @SettingsID)

		UPDATE
			cs_Threads
		SET 
			SectionID = @MoveToSectionID
		WHERE 
			ThreadID = @ThreadID and SettingsID = @SettingsID
		
		print 'The approved post was moved.'
		SET @Notes = 'The approved post was moved.'
		SELECT 3
	
	END

	-- Reset the statistics on both forums.
	EXEC cs_system_ResetForumStatistics @SectionID
	EXEC cs_system_ResetForumStatistics @MoveToSectionID
	
	-- Reset the thread statistics on the moved thread.
	EXEC cs_system_ResetThreadStatistics @ThreadID
		
	-- Update Moderation Audit table
	EXEC cs_system_ModerationAction_AuditEntry 3, @MovedBy, @PostID, null, @SectionID, @SettingsID, @Notes

END
ELSE BEGIN
	print 'The post was not moved.'
	SELECT 0
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_Moderate_Post_Move] to public
go
/***********************************************
* Sproc: cs_Moderate_Post_UpdateParent
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_Moderate_Post_UpdateParent'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

CREATE    PROCEDURE [dbo].cs_Moderate_Post_UpdateParent
(
	@PostID int,
	@ParentID int,
	@MovedBy int,
	@SettingsID int
)
AS

SET Transaction Isolation Level Read UNCOMMITTED

DECLARE @ThreadID int

SELECT @ThreadID = ThreadID
FROM cs_Posts
WHERE PostID = @PostID
	 and SettingsID = @SettingsID

-- ensure the posts are in the same thread and that the post being moved is not the first post
IF @ThreadID is null 
	OR @ThreadID <> (SELECT ThreadID FROM cs_Posts WHERE PostID = @PostID and SettingsID = @SettingsID)
	OR @PostID = (SELECT PostID FROM cs_Posts WHERE ThreadID = @ThreadID and SettingsID = @SettingsID and PostLevel = 0)
	RETURN

BEGIN TRAN

-- Do the Update
UPDATE
	cs_Posts
SET
	ParentID = @ParentID
WHERE
	PostID = @PostID and SettingsID = @SettingsID

-- Update thread statistics
EXEC cs_system_UpdateThread @ThreadID, 0, @SettingsID

-- Update moderation actions
EXEC cs_system_ModerationAction_AuditEntry 3, @MovedBy, @PostID, null, null, @SettingsID, null

COMMIT TRAN


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_Moderate_Post_UpdateParent] to public
go
/***********************************************
* Sproc: cs_Moderate_Thread_Merge
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_Moderate_Thread_Merge'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


CREATE    PROCEDURE [dbo].cs_Moderate_Thread_Merge
(
	@ParentThreadID int,
	@ChildThreadID int,
	@JoinBy int,
	@SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
DECLARE @ParentSectionID int
DECLARE @LastPostInParent int
DECLARE @PostLevelInParent int
DECLARE @SortOrderInParent int
DECLARE @ChildSectionID int
DECLARE @FirstPostInChild int
DECLARE @PostLevelInChild int
DECLARE @SortOrderInChild int
DECLARE @LastPostInChild int

-- Check to ensure we can perform this opertation
IF ((SELECT ThreadID FROM cs_Threads WHERE ThreadID = @ChildThreadID and SettingsID = @SettingsID) = @ParentThreadID)
	RETURN

-- Get details on the parent thread
SELECT TOP 1
	@ParentSectionID = SectionID,
	@LastPostInParent = PostID,
	@PostLevelInParent = PostLevel,
	@SortOrderInParent = SortOrder
FROM
	cs_Posts
WHERE
	ThreadID = @ParentThreadID and SettingsID = @SettingsID
ORDER BY
	SortOrder DESC

-- Get details on the child thread
SELECT TOP 1
	@ChildSectionID = SectionID,
	@FirstPostInChild = PostID,
	@PostLevelInChild = PostLevel,
	@SortOrderInChild = SortOrder
FROM
	cs_Posts
WHERE
	ThreadID = @ChildThreadID and SettingsID = @SettingsID

-- don't know why this is here
-- Get the last post in the child thread
--SELECT 
--	@LastPostInChild = MostRecentPostID
--FROM
--	cs_Threads
--WHERE
--	ThreadID = @ChildThreadID

BEGIN TRAN

-- this is now done in the cs_system_UpdateThread sproc
-- Update the PostLevel and SortOrder for the Child posts before merging
--UPDATE 
--	cs_Posts
--SET
--	PostLevel = PostLevel + @PostLevelInParent
--WHERE
--	ThreadID = @ChildThreadID
--
--UPDATE 
--	cs_Posts
--SET
--	SortOrder = SortOrder + @SortOrderInParent
--WHERE
--	ThreadID = @ChildThreadID

-- Approve the post
UPDATE
	cs_Posts
SET
	IsApproved = 1
WHERE
	PostID = @FirstPostInChild AND SettingsID = @SettingsID

-- Do the Updates
UPDATE
	cs_PostAttachments
SET
	SectionID = @ParentSectionID
WHERE
	PostID IN (SELECT PostID FROM cs_Posts WHERE ThreadID = @ChildThreadID AND SettingsID = @SettingsID)

UPDATE
	cs_Posts
SET
	ThreadID = @ParentThreadID,
	SectionID = @ParentSectionID,
	PostLevel = PostLevel + @PostLevelInParent,
	SortOrder = SortOrder + @SortOrderInParent,
	ParentID = @LastPostInParent
WHERE
	ThreadID = @ChildThreadID and SettingsID = @SettingsID

-- Now delete all of the old thread info
DELETE FROM 
	cs_SearchBarrel
WHERE 
	ThreadID = @ChildThreadID and SettingsID = @SettingsID

-- Delete all thread tracking data.	
DELETE FROM 
	cs_TrackedThreads
WHERE 
	ThreadID = @ChildThreadID and SettingsID = @SettingsID

-- Cleanup ThreadsRead
DELETE
	cs_ThreadsRead
WHERE
	ThreadID = @ChildThreadID  and SettingsID = @SettingsID

-- Delete the child thread
DELETE 
	cs_Threads
WHERE
	ThreadID = @ChildThreadID and SettingsID = @SettingsID

-- Update thread statistics
EXEC cs_system_UpdateThread @ParentThreadID, 0, @SettingsID

-- Update forum statistics
EXEC cs_system_UpdateForum @ParentSectionID, @ParentThreadID, @LastPostInParent, @SettingsID
EXEC cs_system_ResetForumStatistics @ChildSectionID

-- Update moderation actions
EXEC cs_system_ModerationAction_AuditEntry 7, @JoinBy, @ChildThreadID, null, null, @SettingsID, null

COMMIT TRAN



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_Moderate_Thread_Merge] to public
go
/***********************************************
* Sproc: cs_Moderate_Thread_Split
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_Moderate_Thread_Split'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

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



CREATE       PROCEDURE [dbo].cs_Moderate_Thread_Split
(
	@PostID INT,
	@MoveToForum INT,
	@SplitBy INT,
	@SettingsID int
)
AS

SET Transaction Isolation Level Read UNCOMMITTED
DECLARE @IsSticky BIT
DECLARE @StickyDate DATETIME
DECLARE @IsLocked BIT
DECLARE @NewThreadID INT
DECLARE @OldThreadID INT
DECLARE @UserID INT
DECLARE @PostAuthor NVARCHAR(64)
DECLARE @PostDate DATETIME
DECLARE @EmoticonID INT
DECLARE @TopPostLevel INT
DECLARE @TopSortOrder INT
DECLARE @TotalReplies INT
DECLARE	@MostRecentPostAuthor NVARCHAR(64)
DECLARE	@MostRecentPostAuthorID INT
DECLARE	@MostRecentPostID INT

-- Get details on the post
SELECT 
	@PostDate = PostDate,
	@UserID = UserID,
	@PostAuthor = PostAuthor,
	@IsSticky = 0,			-- shouldn't be a stickie when splitting
	@IsLocked = IsLocked,
	@StickyDate = GetDate(),
	@EmoticonID = EmoticonID,
	@OldThreadID = ThreadID		-- to delete later if no more replies
FROM 
	cs_Posts 
WHERE 
	PostID = @PostID and SettingsID = @SettingsID

BEGIN TRAN

-- Create a new thread by inserting
INSERT cs_Threads 	
	( SectionID,
	PostDate, 
	UserID, 
	PostAuthor, 
	ThreadDate, 
	MostRecentPostAuthor, 
	MostRecentPostAuthorID, 	
	MostRecentPostID, 
	IsLocked, 
	IsApproved,
	IsSticky, 
	StickyDate, 
	ThreadEmoticonID,
	SettingsID )
VALUES
	( @MoveToForum, 	-- the forum we are moving to
	@PostDate, 
	@UserID, 
	@PostAuthor,
	@PostDate,
	@PostAuthor,	-- Dummy data until we move all posts below
	@UserID, 	-- Dummy data until we move all posts below
	0,		-- MostRecentPostID, which we don't know yet.
	@IsLocked,
	1,		-- Wouldn't be shown in the forum unless it wasn't approved already.
	@IsSticky,
	@StickyDate,
	@EmoticonID,
	@SettingsID )

SELECT @NewThreadID = SCOPE_IDENTITY()


-- Update post
UPDATE	cs_Posts
SET		PostLevel = 1,	-- this is now a thread starter
		IsApproved = 1	-- by splitting, if it wasn't approved, it is now
WHERE	PostID = @PostID AND SettingsID = @SettingsID

UPDATE
	cs_PostAttachments
SET
	SectionID = @MoveToForum
WHERE
	PostID IN (SELECT PostID FROM cs_Posts WHERE ThreadID = @OldThreadID AND (PostID = @PostID OR ParentID = @PostID) AND SettingsID = @SettingsID)

-- Update the post and it's childred (if any) with the new threadid
UPDATE 
	cs_Posts 
SET 
	ThreadID = @NewThreadID,
	SectionID = @MoveToForum,
	ParentID = @PostID	-- the toplevel post should now reference itself.
--	PostDate = GetDate()	-- We're not going to reset the DATETIME for the posts
WHERE
	ThreadID = @OldThreadID
	AND (PostID = @PostID OR ParentID = @PostID)  and SettingsID = @SettingsID

-- this is now controlled in the cs_system_UpdateThread sproc
-- Fix the PostLevel and SortOrder details of the new thread
--SELECT 
--	@TopPostLevel = PostLevel,
--	@TopSortOrder = SortOrder
--FROM 
--	cs_Posts 
--WHERE 
--	PostID = @PostID
--
--UPDATE 
--	cs_Posts 
--SET 
---	PostLevel = (PostLevel - @TopPostLevel) + 1,
--	SortOrder = (SortOrder - @TopSortOrder) + 1
--WHERE
--	ThreadID = @NewThreadID

-- Update the threads...
EXEC cs_system_UpdateThread @NewThreadID, 0, @SettingsID
EXEC cs_system_UpdateThread @OldThreadID, 0, @SettingsID

-- Update forum statistics
EXEC cs_system_UpdateForum @MoveToForum, @NewThreadID, @PostID, @SettingsID

-- #7. Update moderation actions
EXEC cs_system_ModerationAction_AuditEntry 8, @SplitBy, @PostID, null, null, @SettingsID, null


COMMIT TRAN



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_Moderate_Thread_Split] to public
go
/***********************************************
* Sproc: cs_ModerationAudit_Message_Get
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_ModerationAudit_Message_Get'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

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

CREATE PROCEDURE [dbo].cs_ModerationAudit_Message_Get
(
	@PostID int,
	@PageIndex int,
	@PageSize int,
	@ReturnRecordCount bit,
	@SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED

DECLARE @PageLowerBound int
DECLARE @PageUpperBound int

-- First set the rowcount
DECLARE @RowsToReturn int
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET ROWCOUNT @RowsToReturn

-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1

-- Create a temp table to store the select results
CREATE TABLE #TmpPageIndex 
(
	IndexID int IDENTITY (1, 1) NOT NULL primary key clustered,
	ID int
)

-- Select records
INSERT INTO #TmpPageIndex (ID)
	SELECT ID FROM cs_ModerationAudit (nolock) 
	WHERE PostID = @PostID AND SettingsID = @SettingsID 
	ORDER BY ModeratedOn ASC

SELECT
	MA.*,
	U.UserName AS ModeratorName
FROM 
	cs_ModerationAudit MA (nolock) LEFT JOIN cs_vw_Users_FullUser U ON U.UserID = MA.ModeratorID,
	#TmpPageIndex 
WHERE 
	MA.ID = #TmpPageIndex.ID AND
	#TmpPageIndex.IndexID > @PageLowerBound AND
	#TmpPageIndex.IndexID < @PageUpperBound AND 
	MA.SettingsID = @SettingsID
ORDER BY
	IndexID

IF @ReturnRecordCount = 1
	SELECT count(PostID) FROM cs_ModerationAudit (nolock) WHERE PostID = @PostID AND SettingsID = @SettingsID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].cs_ModerationAudit_Message_Get to public
go

/***********************************************
* Sproc: cs_ModerationAudit_Summary_Get
* File Date: 4/14/2008 11:35:03 AM
***********************************************/
Print 'Creating...cs_ModerationAudit_Summary_Get'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

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

CREATE PROCEDURE [dbo].cs_ModerationAudit_Summary_Get 
(
	@PostID int = null,
	@UserID int = null,
	@SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
IF (@PostID != NULL AND @UserID = NULL)
BEGIN
	SELECT 
		MAct.ModerationAction, 
		Total = (SELECT COUNT(MA.ModerationAction) FROM cs_ModerationAudit MA 
			WHERE MA.PostID = @PostID AND  MA.SettingsID =  @SettingsID AND MAct.ModerationAction = MA.ModerationAction 
			GROUP BY MA.ModerationAction)
	FROM 
		cs_ModerationAction MAct

END
ELSE IF (@UserID != NULL AND @PostID = NULL)
BEGIN
	SELECT 
		MAct.ModerationAction, 
		Total = (SELECT COUNT(MA.ModerationAction) FROM cs_ModerationAudit MA 
			WHERE MA.UserID = @UserID AND  MA.SettingsID =  @SettingsID AND MAct.ModerationAction = MA.ModerationAction 
			GROUP BY MA.ModerationAction)
	FROM 
		cs_ModerationAction MAct
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].cs_ModerationAudit_Summary_Get to public
go

/***********************************************
* Sproc: cs_ModerationAudit_User_Get
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_ModerationAudit_User_Get'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

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

CREATE PROCEDURE [dbo].cs_ModerationAudit_User_Get
(
	@UserID int,
	@ModerationAction int,
	@PageIndex int,
	@PageSize int,
	@ReturnRecordCount bit,
	@SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED

--
-- IF @UserActionsFilter = 1 then we want to have the following moderation actions for provided UserID:
-- baan (12), unban (13), moderate (11), unmoderate (10), rst passwd (14), chg passwd (15), edit user (9);
-- OTHERWISE we want to these moderation actions:
-- post approved (1), post edited (2), post moved (3), post deleted (4), 5, 6, 7, 8
--
-- Sometimes the moderator is the user itself, so take both of them into account
--

DECLARE @PageLowerBound int
DECLARE @PageUpperBound int

-- First set the rowcount
DECLARE @RowsToReturn int
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET ROWCOUNT @RowsToReturn

-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1

-- Create a temp table to store the select results
CREATE TABLE #TmpPageIndex 
(
	IndexID int IDENTITY (1, 1) NOT NULL primary key clustered,
	ID int
)

-- Select records
INSERT INTO #TmpPageIndex (ID)
	SELECT ID FROM cs_ModerationAudit (nolock) 
	WHERE UserID = @UserID AND ModerationAction = @ModerationAction AND SettingsID = @SettingsID
	ORDER BY ModeratedOn ASC

SELECT
	MA.*,
	U.UserName AS ModeratorName
FROM 
	cs_ModerationAudit MA (nolock) LEFT JOIN cs_vw_Users_FullUser U ON U.UserID = MA.ModeratorID,
	#TmpPageIndex
WHERE 
	MA.ID = #TmpPageIndex.ID AND
	#TmpPageIndex.IndexID > @PageLowerBound AND
	#TmpPageIndex.IndexID < @PageUpperBound AND 
	MA.SettingsID = @SettingsID
ORDER BY
	IndexID

IF @ReturnRecordCount = 1
	SELECT COUNT(UserID) 
	FROM cs_ModerationAudit (nolock) 
	WHERE UserID = @UserID AND ModerationAction = @ModerationAction AND SettingsID = @SettingsID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].cs_ModerationAudit_User_Get to public
go

/***********************************************
* Sproc: cs_ModerationAudit_User_SaveEvent
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_ModerationAudit_User_SaveEvent'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

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

CREATE   procedure [dbo].cs_ModerationAudit_User_SaveEvent
(
	@UserID int,
	@ModeratorID int,
	@PasswordChanged bit = 0,
	@PasswordReset bit = 0,
	@UserBanned bit = 0,
	@BanDetails nvarchar(1024) = '',
	@UserUnbanned bit = 0,
	@UserEdited bit = 0,
	@UserModerated bit = 0,
	@UserIgnored bit = 0,
	@UserUnignored bit = 0,
	@SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN

	IF @PasswordReset = 1
		exec cs_system_ModerationAction_AuditEntry 14, @ModeratorID, null, @UserID, null, @SettingsID, 'User password has been reset.'
	ELSE IF @PasswordChanged = 1
		exec cs_system_ModerationAction_AuditEntry 15, @ModeratorID, null, @UserID, null, @SettingsID, 'User password has been changed.'
	ELSE IF @UserBanned = 1
		exec cs_system_ModerationAction_AuditEntry 12, @ModeratorID, null, @UserID, null, @SettingsID, @BanDetails
	ELSE IF @UserUnbanned = 1
		exec cs_system_ModerationAction_AuditEntry 13, @ModeratorID, null, @UserID, null, @SettingsID, 'User profile  has been un-banned.'
	ELSE IF @UserEdited = 1
		exec cs_system_ModerationAction_AuditEntry 9, @ModeratorID, null, @UserID, null, @SettingsID, 'User profile has been updated.'
	ELSE IF @UserModerated = 1
		exec cs_system_ModerationAction_AuditEntry 11, @ModeratorID, null, @UserID, null, @SettingsID, 'User is moderated.'
	ELSE IF @UserIgnored = 1
		exec cs_system_ModerationAction_AuditEntry 20, @ModeratorID, null, @UserID, null, @SettingsID, 'User has been set to ignored.'
	ELSE IF @UserUnignored = 1
		exec cs_system_ModerationAction_AuditEntry 21, @ModeratorID, null, @UserID, null, @SettingsID, 'User has been unignored.'
	ELSE IF @UserModerated = 0
		exec cs_system_ModerationAction_AuditEntry 10, @ModeratorID, null, @UserID, null, @SettingsID, 'User is not moderated.'
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO



grant execute on [dbo].cs_ModerationAudit_User_SaveEvent to public
go

/***********************************************
* Sproc: cs_nntp_GetArticlesToPost
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_nntp_GetArticlesToPost'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


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

create procedure [dbo].cs_nntp_GetArticlesToPost
(
	@SectionID int
)
AS
DECLARE @MaxPostID int
DECLARE @ThreadID int
DECLARE @ParentID int
DECLARE @PostAuthor nvarchar(128)
DECLARE @Subject nvarchar(512)
DECLARE @PostDate datetime
DECLARE @SiteUrl nvarchar(512)

SET @MaxPostID = (SELECT max(PostID) FROM cs_nntp_Posts WHERE SectionID = @SectionID)

SET @SiteUrl = (	SELECT 
				SiteUrl 
			FROM 
				cs_Sites S, 
				cs_Sections SN,
				cs_SiteMappings F 
			WHERE 
				F.SiteID = S.SiteID AND 
				SN.SettingsID = F.SettingsID AND
				SN.SectionID = @SectionID
		)

CREATE TABLE #PostIndex 
(
	PostID int,
	NntpUniqueID nvarchar(1024) collate database_default
)

-- Temporary table for values we need to operate on
INSERT INTO #PostIndex (PostID, NntpUniqueID)
SELECT 
	PostID = P.PostID,
	NntpUniqueID = ('<' + CAST(P.PostID as nvarchar(64)) + '-' + CAST(P.ThreadID as nvarchar(64)) + '-' + CAST(P.ParentID as nvarchar(64)) + '@' + (@SiteUrl) + '>')
FROM 
	cs_Posts P
WHERE 
	P.PostID > @MaxPostID AND
	P.SectionID = @SectionID

-- Return values
SELECT 
	NntpReferenceId = (SELECT NntpUniqueID FROM cs_nntp_Posts WHERE PostID = P.ParentID),
	NntpGroup = (SELECT NntpGroup FROM cs_nntp_NewsGroups WHERE SectionID = @SectionID),
	P1.NntpUniqueID,
	P.*
FROM 
	cs_Posts P,
	#PostIndex P1
WHERE 
	P.PostID = P1.PostID

-- Mark items as NNTP Posts
INSERT INTO cs_nntp_Posts
SELECT 
	P.PostID,
	P.SectionID,
	0,
	P1.NntpUniqueID,
	P.SettingsID
FROM 
	cs_Posts P,
	#PostIndex P1
WHERE 
	P.PostID = P1.PostID
	




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO




grant execute on [dbo].[cs_nntp_GetArticlesToPost] to public
go
/***********************************************
* Sproc: cs_nntp_GetForumNewsgroups
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_nntp_GetForumNewsgroups'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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



CREATE    procedure [dbo].cs_nntp_GetForumNewsgroups
AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN

	SELECT 
		*
	FROM 
		cs_nntp_Newsgroups 

END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_nntp_GetForumNewsgroups] to public
go
/***********************************************
* Sproc: cs_nntp_GetLastArticleId
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_nntp_GetLastArticleId'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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



CREATE    procedure [dbo].cs_nntp_GetLastArticleId
(
	@SectionID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN

	SELECT 
		MAX(NntpPostID + 1)
	FROM 
		cs_nntp_Posts 
	WHERE 
		SectionID = @SectionID

END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_nntp_GetLastArticleId] to public
go
/***********************************************
* Sproc: cs_nntp_Post_Add
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_nntp_Post_Add'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

CREATE           procedure [dbo].cs_nntp_Post_Add
(
	@NntpPostID 		int,
	@SectionID 		int,
	@NntpUniqueID 		nvarchar(256),
	@NntpParentUniqueID	nvarchar(245) = null,
	@UserName 		nvarchar(256),
	@Email	 		nvarchar(256),
	@Subject		nvarchar(256),
	@Body			ntext,
	@FormattedBody		ntext,
	@PostDate		datetime
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN

DECLARE @ParentID int
DECLARE @PostID int
DECLARE @ThreadID int
DECLARE @UserID int
DECLARE @SettingsID int

SELECT @SettingsID = SettingsID FROM cs_Sections WHERE SectionID = @SectionID

-- Set the Parent ID to 0
SET @ParentID = 0

-- Attempt to find the user
SELECT @UserID = UserID FROM cs_vw_Users_FullUser WHERE Email = @Email

-- Not linked via email
IF @UserID IS NULL
BEGIN
	-- Attempt to link via the username
	SELECT @UserID = UserID FROM cs_vw_Users_FullUser WHERE UserName = @UserName

	-- Can't find the user
	IF @UserID IS NULL
	BEGIN

		SELECT @UserID = UserID FROM cs_vw_Users_FullUser WHERE UserName = 'Anonymous' and SettingsID = @SettingsID
	END
END

-- Already Added?
IF EXISTS (SELECT NntpPostID FROM cs_nntp_Posts WHERE NntpUniqueID = @NntpUniqueID)
	RETURN

-- Was a @NntpParentUniqueID specified?
IF @NntpParentUniqueID IS NOT NULL
BEGIN

	-- Attempt to find the ParentID of the Post
	SELECT 
		@ParentID = PostID
	FROM 
		cs_nntp_Posts 
	WHERE	
		NntpUniqueID = @NntpParentUniqueID

	IF @ParentID = 0
		RETURN
END

-- Insert the post into the cs_Posts table
exec cs_Post_CreateUpdate 
	@SectionID, 
	@ParentID, 
	1, 
	0,
	@Subject, 
	@UserID, 
	@UserName,
	@Body, 
	@FormattedBody, 
	0, 
	0, 
	0, 
	1,
	'1/1/1797', 
	0, 
	@PostDate, 
	'127.0.0.1', 
	null,
	null,
	@SettingsID,
	0,
	@PostID output,
	@ThreadID output

-- INSERT the post into the cs_nntp_Posts table
INSERT INTO
	cs_nntp_Posts
	(
		PostID,
		SectionID,
		NntpPostID,
		NntpUniqueID,
		SettingsID
	)
VALUES
	(
		@PostID,
		@SectionID,
		@NntpPostID,
		@NntpUniqueID,
		@SettingsID
	)

END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


grant execute on [dbo].[cs_nntp_Post_Add] to public
go

/***********************************************
* Sproc: cs_OpenId_AssociateUser
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_OpenId_AssociateUser'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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




CREATE PROCEDURE [dbo].[cs_OpenID_AssociateUser]
(
	@OpenID nvarchar(512),
	@Email nvarchar(128),
	@UserID int,
	@SettingsID int
)
AS

SET Transaction Isolation Level Read UNCOMMITTED

If Not Exists(Select UserID FROM cs_Users_OpenID where OpenID = @OpenID and SettingsID = @SettingsID)
	BEGIN
		Insert cs_Users_OpenID (OpenID, Email, UserID, SettingsID, LastAccessed, Created)
		Values (@OpenID, @Email, @UserID, @SettingsID, GetDate(), GetDate())
	END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_OpenID_AssociateUser] to public
go
/***********************************************
* Sproc: cs_OpenId_Exists
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_OpenId_Exists'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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




CREATE PROCEDURE [dbo].[cs_OpenID_Exists]
(
	@OpenID nvarchar(512),
	@SettingsID int,
	@Exists bit = 0 output
)
AS

SET Transaction Isolation Level Read UNCOMMITTED

	Set @Exists = 0

	If Exists(Select UserID FROM cs_Users_OpenID where OpenID = @OpenID and SettingsID = @SettingsID)
	BEGIN
		Set @Exists = 1
	END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_OpenID_Exists] to public
go
/***********************************************
* Sproc: cs_OpenId_GetOpenIds
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_OpenId_GetOpenIds'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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




CREATE PROCEDURE [dbo].[cs_OpenID_GetOpenIDs]
(
	@UserID int,
	@SettingsID int
)
AS

SET Transaction Isolation Level Read UNCOMMITTED

	Select OpenID From cs_Users_OpenID where UserID = @UserID and SettingsID = @SettingsID


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_OpenID_GetOpenIDs] to public
go
/***********************************************
* Sproc: cs_OpenId_GetUserId
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_OpenId_GetUserId'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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




CREATE PROCEDURE [dbo].[cs_OpenID_GetUserID]
(
	@OpenID nvarchar(512),
	@SettingsID int
)
AS

SET Transaction Isolation Level Read UNCOMMITTED

Declare @UserID int

	Select @UserID = UserID From cs_Users_OpenID where OpenID = @OpenID and SettingsID = @SettingsID

	if(@UserID is not null)
	Begin
		Update cs_Users_OpenID
		Set LastAccessed = GetDate() where OpenID = @OpenID and SettingsID = @SettingsID
	End

	Select IsNull(@UserID,-1) as UserID


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_OpenID_GetUserID] to public
go
/***********************************************
* Sproc: cs_OpenId_UnssociateUser
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_OpenId_UnssociateUser'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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




CREATE PROCEDURE [dbo].[cs_OpenID_UnassociateUser]
(
	@OpenID nvarchar(512),
	@UserID int,
	@SettingsID int
)
AS

SET Transaction Isolation Level Read UNCOMMITTED

delete from cs_Users_OpenID where OpenID = @OpenID and SettingsID = @SettingsID and UserID = @UserID


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_OpenID_UnassociateUser] to public
go
/***********************************************
* Sproc: cs_OverviewActivityReportRecords_Get
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_OverviewActivityReportRecords_Get'

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_OverviewActivityReportRecords_Get]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_OverviewActivityReportRecords_Get]
GO
/***********************************************
* Sproc: cs_PageViews_Add
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_PageViews_Add'

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_PageViews_Add]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_PageViews_Add]
GO
/***********************************************
* Sproc: cs_PageViews_Get
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_PageViews_Get'

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_PageViews_Get]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_PageViews_Get]
GO
/***********************************************
* Sproc: cs_Page_Visits_Add
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_Page_Visits_Add'

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_Page_Visits_Add]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_Page_Visits_Add]
GO
/***********************************************
* Sproc: cs_Permissions_DeleteByRole
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_Permissions_DeleteByRole'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


--We will need to filter all by application name

create procedure [dbo].cs_Permissions_DeleteByRole
(
	@RoleID uniqueidentifier = null,
	@SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN

	DELETE FROM cs_SectionPermissions WHERE RoleID = @RoleID AND SettingsID = @SettingsID
	DELETE FROM cs_GroupPermissions WHERE RoleID = @RoleID AND SettingsID = @SettingsID
	DELETE FROM cs_ProductPermissions WHERE RoleID = @RoleID AND SettingsID = @SettingsID

END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_Permissions_DeleteByRole] to public
go
/***********************************************
* Sproc: cs_Points_CalculateForPost
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_Points_CalculateForPost'

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_Points_CalculateForPost]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_Points_CalculateForPost]
GO
--------------------------------------------------------------------------------
--	cs_Points_CalculateForPost
--	Calculates the points for a post
--------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[cs_Points_CalculateForPost]
(
	@SettingsID			int,
	@PostID				int,
	@PostFactor			int,
	@ReplyFactor		int,
	@ReplierFactor		int,
	@RatingFactor		int,
	@DownloadFactor		int,
	@DownloaderFactor	int,
	@FavoritePostFactor	int,
	@ForumRatingType	int
)
AS
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


	-- get post's reply count
	DECLARE		@ReplyCount		int,
				@ReplierCount	int
	SELECT		@ReplyCount = COUNT(P.PostID),
				@ReplierCount = COUNT(DISTINCT(P.UserID))
	FROM		cs_Posts PP
	INNER JOIN	cs_Posts P
		ON		PP.PostID = P.ParentID
	INNER JOIN	cs_Sections S				-- exclude private messages
		ON		PP.SectionID = S.SectionID
	WHERE		PP.PostID = @PostID
		AND		PP.SettingsID = @SettingsID
		AND		S.EnablePostPoints = 1		-- ignore excluded sections
		AND		PP.UserID <> P.UserID		-- ignore self-replies
		AND		PP.PostID <> P.PostID		-- ignore thread starters
		AND		P.IsApproved = 1			-- ignore posts awaiting moderation


	-- get post's rating sum
	DECLARE		@RatingSum	int
	SELECT		@RatingSum = ISNULL(SUM(PR.Rating), 0)
	FROM		cs_PostRating PR
	INNER JOIN	cs_Posts P
		ON		PR.PostID = P.PostID
	INNER JOIN	cs_Sections S				-- exclude private messages
		ON		P.SectionID = S.SectionID
	WHERE		PR.PostID = @PostID
		AND		PR.SettingsID = @SettingsID
		AND		S.EnablePostPoints = 1		-- ignore excluded sections
		AND		PR.UserID <> P.UserID		-- ignore self-ratings
		AND		P.IsApproved = 1			-- ignore posts awaiting moderation
		AND		@ForumRatingType = 1		-- only used if setup for post rating; ignore thread rating


	-- get post's download count
	DECLARE		@DownloadCount		int,
				@DownloaderCount	int
	SELECT		@DownloadCount = COUNT(D.PostID),
				@DownloaderCount = COUNT(DISTINCT(D.UserID))
	FROM		cs_Posts_Downloads D
	INNER JOIN	cs_Posts P
		ON		D.PostID = P.PostID
	INNER JOIN	cs_Sections S
		ON		P.SectionID = S.SectionID
	WHERE		D.PostID = @PostID
		AND		P.SettingsID = @SettingsID
		AND		S.EnablePostPoints = 1		-- ignore excluded sections
		AND		D.UserID <> P.UserID		-- ignore self-authored favorites
		AND		P.IsApproved = 1			-- ignore posts awaiting moderation


	-- get post's favorite count
	DECLARE		@FavoriteCount	int
	SELECT		@FavoriteCount = COUNT(FP.PostID)
	FROM		cs_FavoritePosts FP
	INNER JOIN	cs_Posts P
		ON		FP.PostID = P.PostID
	INNER JOIN	cs_Sections S				-- exclude private messages
		ON		P.SectionID = S.SectionID
	WHERE		FP.PostID = @PostID
		AND		P.SettingsID = @SettingsID
		AND		S.EnablePostPoints = 1		-- ignore excluded sections
		AND		FP.OwnerID <> P.UserID		-- ignore self-authored favorites
		AND		P.IsApproved = 1			-- ignore posts awaiting moderation


	-- update post points
	UPDATE		P
	SET			Points =
				1 * @PostFactor
				+ @ReplyCount * @ReplyFactor
				+ @ReplierCount * @ReplierFactor
				+ @RatingSum * @RatingFactor
				+ @DownloadCount * @DownloadFactor
				+ @DownloaderCount * @DownloaderFactor
				+ @FavoriteCount * @FavoritePostFactor,
				PointsUpdated = GETDATE()
	FROM		cs_Posts P
	INNER JOIN	cs_Sections S				-- exclude private messages
		ON		P.SectionID = S.SectionID
	WHERE		P.PostID = @PostID
		AND		P.SettingsID = @SettingsID
		AND		S.EnablePostPoints = 1		-- ignore excluded sections
		AND		P.IsApproved = 1			-- ignore posts awaiting moderation


	RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT EXECUTE on [dbo].[cs_Points_CalculateForPost] to [public]

/***********************************************
* Sproc: cs_Points_CalculateForPostAndAuthor
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_Points_CalculateForPostAndAuthor'

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_Points_CalculateForPostAndAuthor]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_Points_CalculateForPostAndAuthor]
GO
--------------------------------------------------------------------------------
--	cs_Points_CalculateForPostAndAuthor
--	Calculates the points for a post and its author
--------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[cs_Points_CalculateForPostAndAuthor]
(
	@SettingsID			int,
	@PostID				int,
	@PostFactor			int,
	@ReplyFactor		int,
	@ReplierFactor		int,
	@RatingFactor		int,
	@DownloadFactor		int,
	@DownloaderFactor	int,
	@FavoritePostFactor	int,
	@FavoriteUserFactor	int,
	@RaterFactor		int,
	@ForumRatingType	int
)
AS
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


	-- recalculate post points
	EXECUTE cs_Points_CalculateForPost @SettingsID, @PostID, @PostFactor, @ReplyFactor, @ReplierFactor, @RatingFactor, @DownloadFactor, @DownloaderFactor, @FavoritePostFactor, @ForumRatingType


	-- get post author
	DECLARE @UserID	int
	EXECUTE cs_Post_GetAuthorID @SettingsID, @PostID, @UserID OUTPUT


	-- recalculate author points
	EXECUTE cs_Points_CalculateForUser @SettingsID, @UserID, @FavoriteUserFactor, @RaterFactor, @ForumRatingType


	RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT EXECUTE on [dbo].[cs_Points_CalculateForPostAndAuthor] to [public]

/***********************************************
* Sproc: cs_Points_CalculateForPost_Set
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_Points_CalculateForPost_Set'

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_Points_CalculateForPost_Set]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_Points_CalculateForPost_Set]
GO
--------------------------------------------------------------------------------
--	cs_Points_CalculateForPost_Set
--	Calculates the points for a post set
--------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[cs_Points_CalculateForPost_Set]
(
	@SettingsID			int,
	@FilterType			int,
	@FilterValue		int,
	@PostFactor			int,
	@ReplyFactor		int,
	@ReplierFactor		int,
	@RatingFactor		int,
	@DownloadFactor		int,
	@DownloaderFactor	int,
	@FavoritePostFactor	int,
	@ForumRatingType	int
)
AS
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

	-- declare temp structures
	CREATE TABLE	#PostSet		(PostID int)
	CREATE TABLE	#ReplyData		(PostID int, ReplyCount int, ReplierCount int)
	CREATE TABLE	#RatingData		(PostID int, RatingSum int)
	CREATE TABLE	#DownloadData	(PostID int, DownloadCount int, DownloaderCount int)
	CREATE TABLE	#FavoriteData	(PostID int, Value int)


	-- get working post set
	INSERT INTO	#PostSet
				(PostID)
	EXECUTE		cs_Posts_GetPostIDByFilter @SettingsID, @FilterType, @FilterValue


	-- get post's reply statistics
	INSERT INTO	#ReplyData
				(PostID, ReplyCount, ReplierCount)
	SELECT		PP.PostID, COUNT(P.PostID), COUNT(DISTINCT(P.UserID))
	FROM		cs_Posts PP
	INNER JOIN	cs_Posts P
		ON		PP.PostID = P.ParentID
	INNER JOIN	#PostSet PS
		ON		PP.PostID = PS.PostID
	WHERE		PP.SettingsID = @SettingsID
		AND		PP.UserID <> P.UserID		-- ignore self-replies
		AND		PP.PostID <> P.PostID		-- ignore thread starters
	GROUP BY	PP.PostID


	-- get post's rating statistics
	INSERT INTO	#RatingData
				(PostID, RatingSum)
	SELECT		PR.PostID, ISNULL(SUM(PR.Rating), 0)
	FROM		cs_PostRating PR
	INNER JOIN	cs_Posts P
		ON		PR.PostID = P.PostID
	INNER JOIN	#PostSet PS
		ON		PR.PostID = PS.PostID
	WHERE		PR.SettingsID = @SettingsID
		AND		PR.UserID <> P.UserID		-- ignore self-ratings
		AND		@ForumRatingType = 1		-- only used if setup for post rating; ignore thread rating
	GROUP BY	PR.PostID


	-- get post's download statistics
	INSERT INTO	#DownloadData
				(PostID, DownloadCount, DownloaderCount)
	SELECT		D.PostID, COUNT(D.PostID), COUNT(DISTINCT(D.UserID))
	FROM		cs_Posts_Downloads D
	INNER JOIN	cs_Posts P
		ON		D.PostID = P.PostID
	INNER JOIN	#PostSet PS
		ON		D.PostID = PS.PostID
	WHERE		P.SettingsID = @SettingsID
		AND		D.UserID <> P.UserID		-- ignore self-downloads
	GROUP BY	D.PostID


	-- get post's favorite count
	INSERT INTO	#FavoriteData
				(PostID, Value)
	SELECT		FP.PostID, COUNT(FP.PostID)
	FROM		cs_FavoritePosts FP
	INNER JOIN	cs_Posts P
		ON		FP.PostID = P.PostID
	INNER JOIN	#PostSet PS
		ON		FP.PostID = PS.PostID
	WHERE		FP.SettingsID = @SettingsID
		AND		FP.OwnerID <> P.UserID
	GROUP BY	FP.PostID


	-- disable triggers (nntp triggers make this update deathly SLOW!)
	ALTER TABLE cs_posts DISABLE TRIGGER ALL


	-- update post points
	UPDATE		P
	SET			Points =
				1 * @PostFactor
				+ ISNULL(RPD.ReplyCount, 0) * @ReplyFactor
				+ ISNULL(RPD.ReplierCount, 0) * @ReplierFactor
				+ ISNULL(RTD.RatingSum, 0) * @RatingFactor
				+ ISNULL(DD.DownloadCount, 0) * @DownloadFactor
				+ ISNULL(DD.DownloaderCount, 0) * @DownloaderFactor
				+ ISNULL(FD.Value, 0) * @FavoritePostFactor,
				PointsUpdated = GETDATE()
	FROM		cs_Posts P
	INNER JOIN	#PostSet PS
		ON		P.PostID = PS.PostID
	LEFT JOIN	#ReplyData RPD
		ON		P.PostID = RPD.PostID
	LEFT JOIN	#RatingData RTD
		ON		P.PostID = RTD.PostID
	LEFT JOIN	#DownloadData DD
		ON		P.PostID = DD.PostID
	LEFT JOIN	#FavoriteData FD
		ON		P.PostID = FD.PostID
	WHERE		P.SettingsID = @SettingsID


	-- enable triggers
	ALTER TABLE cs_posts ENABLE TRIGGER ALL


	-- clean up
	DROP TABLE	#FavoriteData
	DROP TABLE	#DownloadData
	DROP TABLE	#RatingData
	DROP TABLE	#ReplyData
	DROP TABLE	#PostSet


	RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT EXECUTE on [dbo].[cs_Points_CalculateForPost_Set] to [public]

/***********************************************
* Sproc: cs_Points_CalculateForUser
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_Points_CalculateForUser'

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_Points_CalculateForUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_Points_CalculateForUser]
GO
--------------------------------------------------------------------------------
--	cs_Points_CalculateForUser
--	Calculates the points for a user
--------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[cs_Points_CalculateForUser]
(
	@SettingsID			int,
	@UserID				int,
	@FavoriteUserFactor	int,
	@RaterFactor		int,
	@ForumRatingType	int,
	@UpdateAllSettingsID bit = 0
)
AS
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


	-- get user's post points
	DECLARE		@PostPoints		int
	SELECT		@PostPoints = ISNULL(SUM(P.Points), 0)
	FROM		cs_Posts P
	INNER JOIN	cs_Sections S				-- exclude private messages
		ON		P.SectionID = S.SectionID
	WHERE		P.UserID = @UserID
		AND		(P.SettingsID = @SettingsID or @UpdateAllSettingsID = 1)
		AND		S.EnablePostPoints = 1		-- ignore excluded sections
		AND		P.IsApproved = 1			-- ignore posts awaiting moderation


	-- get user's favorite count
	DECLARE		@FavoriteUserCount	int
	SELECT		@FavoriteUserCount = COUNT(FU.OwnerID)
	FROM		cs_FavoriteUsers FU
	WHERE		FU.UserID = @UserID
		AND		(FU.SettingsID = @SettingsID or @UpdateAllSettingsID = 1)
		AND		FU.UserID <> FU.OwnerID			-- ignore self-favorites


	-- get user's rating sum
	DECLARE		@RatingSum	int
	SELECT		@RatingSum = ISNULL(SUM(PR.Rating), 0)
	FROM		cs_PostRating PR
	INNER JOIN	cs_Posts P
		ON		PR.PostID = P.PostID
	INNER JOIN	cs_Sections S				-- exclude private messages
		ON		P.SectionID = S.SectionID
	WHERE		PR.UserID = @UserID
		AND		(P.SettingsID = @SettingsID or @UpdateAllSettingsID = 1)
		AND		S.EnablePostPoints = 1		-- ignore excluded sections
		AND		PR.UserID <> P.UserID		-- ignore self-ratings
		AND		P.IsApproved = 1			-- ignore posts awaiting moderation
		AND		@ForumRatingType = 1		-- only used if setup for post rating; ignore thread rating


	-- update user points
	UPDATE		UP
	SET			Points =
					1 * @PostPoints
					+ @FavoriteUserCount * @FavoriteUserFactor
					+ @RatingSum * @RaterFactor,
				PointsUpdated = GETDATE()
	FROM		cs_UserProfile UP
	WHERE		UP.UserID = @UserID
		AND		(UP.SettingsID = @SettingsID or @UpdateAllSettingsID = 1)


	RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT EXECUTE on [dbo].[cs_Points_CalculateForUser] to [public]

/***********************************************
* Sproc: cs_Points_CalculateForUser_Set
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_Points_CalculateForUser_Set'

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_Points_CalculateForUser_Set]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_Points_CalculateForUser_Set]
GO
--------------------------------------------------------------------------------
--	cs_Points_CalculateForUser_Set
--	Calculates the points for a user set
--------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[cs_Points_CalculateForUser_Set]
(
	@SettingsID			int,
	@FilterType			int,
	@FilterValue		varchar(255),
	@FavoriteUserFactor	int,
	@RaterFactor		int,
	@ForumRatingType	int,
	@UpdateAllSettingsID bit = 0
)
AS
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


	-- declare temp structures
	CREATE TABLE	#UserSet		(UserID int)
	CREATE TABLE	#PostData		(UserID int, Value int)
	CREATE TABLE	#FavoriteData	(UserID int, Value int)
	CREATE TABLE	#RatingData		(UserID int, Value int)


	-- get working user set
	INSERT INTO	#UserSet
				(UserID)
	EXECUTE		cs_Users_GetUserIDByFilter @SettingsID, @FilterType, @FilterValue


	-- get user's post points
	INSERT INTO	#PostData
				(UserID, Value)
	SELECT		P.UserID, SUM(P.Points)
	FROM		cs_Posts P
	INNER JOIN	cs_Sections S				-- exclude private messages
		ON		P.SectionID = S.SectionID
	INNER JOIN	#UserSet US
		ON		P.UserID = US.UserID
	WHERE		(P.SettingsID = @SettingsID or @UpdateAllSettingsID = 1)
		AND		S.EnablePostPoints = 1		-- ignore excluded sections
		AND		P.IsApproved = 1			-- ignore posts awaiting moderation
	GROUP BY	P.UserID


	-- get user's favorite count
	INSERT INTO	#FavoriteData
				(UserID, Value)
	SELECT		FU.UserID, COUNT(FU.OwnerID)
	FROM		cs_FavoriteUsers FU
	INNER JOIN	#UserSet US
		ON		FU.UserID = US.UserID
	WHERE		(FU.SettingsID = @SettingsID or @UpdateAllSettingsID = 1)
		AND		FU.UserID <> FU.OwnerID			-- ignore self-favorites
	GROUP BY	FU.UserID


	-- get user's rating sum
	INSERT INTO	#RatingData
				(UserID, Value)
	SELECT		PR.UserID, ISNULL(SUM(PR.Rating), 0)
	FROM		cs_PostRating PR
	INNER JOIN	cs_Posts P
		ON		PR.PostID = P.PostID
	INNER JOIN	cs_Sections S				-- exclude private messages
		ON		P.SectionID = S.SectionID
	INNER JOIN	#UserSet US
		ON		PR.UserID = US.UserID
	WHERE		(PR.SettingsID = @SettingsID or @UpdateAllSettingsID = 1)
		AND		S.EnablePostPoints = 1		-- ignore excluded sections
		AND		PR.UserID <> P.UserID		-- ignore self-ratings
		AND		P.IsApproved = 1			-- ignore posts awaiting moderation
		AND		@ForumRatingType = 1		-- only used if setup for post rating; ignore thread rating
	GROUP BY	PR.UserID


	-- update user points
	UPDATE		UP
	SET			Points =
					ISNULL(PD.Value, 0)	* 1
					+ ISNULL(FD.Value, 0) * @FavoriteUserFactor
					+ ISNULL(RD.Value, 0) * @RaterFactor,
				PointsUpdated = GETDATE()
	FROM		cs_UserProfile UP
	INNER JOIN	#UserSet US
		ON		UP.UserID = US.UserID
	LEFT JOIN	#PostData PD
		ON		UP.UserID = PD.UserID
	LEFT JOIN	#FavoriteData FD
		ON		UP.UserID = FD.UserID
	LEFT JOIN	#RatingData RD
		ON		UP.UserID = RD.UserID
	WHERE		(UP.SettingsID = @SettingsID or @UpdateAllSettingsID = 1)


	-- clean up
	DROP TABLE	#RatingData
	DROP TABLE	#FavoriteData
	DROP TABLE	#PostData
	DROP TABLE	#UserSet


	RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT EXECUTE on [dbo].[cs_Points_CalculateForUser_Set] to [public]

/***********************************************
* Sproc: cs_PostAttachment
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_PostAttachment'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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







create procedure [dbo].cs_PostAttachment
(
	@PostID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN

	
		SELECT
			[PostID], [SectionID], [UserID], [Created], [FileName],  
			[ContentType], [ContentSize], [SettingsID], [IsRemote], [Height], [Width], [FriendlyFileName]
		FROM
			cs_PostAttachments
		WHERE
			PostID = @PostID


END







GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_PostAttachment] to public
go
/***********************************************
* Sproc: cs_PostAttachment_Add
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_PostAttachment_Add'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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










CREATE PROCEDURE [dbo].[cs_PostAttachment_Add]
(
	@PostID int,
	@UserID int,
	@SectionID int,
	@Filename nvarchar(1024),
	@FriendlyFileName nvarchar(256) = null,
	@ContentType nvarchar(50),
	@ContentSize int,
	@IsRemote int,
	@SettingsID int,
	@Height int = 0,
	@Width int = 0
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN

	IF EXISTS (SELECT PostID FROM cs_PostAttachments WHERE PostID = @PostID and SettingsID = @SettingsID )
	begin
		UPDATE cs_PostAttachments SET
			[FileName] 	= @Filename,
			[FriendlyFileName] = @FriendlyFileName,
			ContentSize	= @ContentSize,
			IsRemote	= @IsRemote,
			[Height] 	= @Height,
			[Width]		= @Width
		WHERE
			PostID		= @PostID and
			SettingsID	= @SettingsID	
	end
	else
	begin
		INSERT INTO 
			cs_PostAttachments
		(
			PostID,
			SectionID,
			UserID,
			[FileName],
			[FriendlyFileName],
			ContentType,
			ContentSize,
			IsRemote,
			SettingsID,
			[Height],
			[Width]
		)
		VALUES
		(
			@PostID,
			@SectionID,
			@UserID,
			@Filename,
			@FriendlyFileName,
			@ContentType,
			@ContentSize,
			@IsRemote,
			@SettingsID,
			@Height,
			@Width
		)
	end
	EXEC cs_Section_DiskUsage_Update @SectionID, @SettingsID
END





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_PostAttachment_Add] to public
go

/***********************************************
* Sproc: cs_PostAttachment_Delete
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_PostAttachment_Delete'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


CREATE PROCEDURE dbo.cs_PostAttachment_Delete
(
	@PostID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN

	DECLARE @SettingsID int
	DECLARE @SectionID int

	SELECT @SettingsID = SettingsID, @SectionID = SectionID FROM cs_PostAttachments WHERE PostID = @PostID

	DELETE FROM cs_PostAttachments WHERE PostID = @PostID
	
	EXEC cs_Section_DiskUsage_Update @SectionID, @SettingsID

END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_PostAttachment_Delete] to public
go

/***********************************************
* Sproc: cs_PostAttachment_Delete_ForSection
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_PostAttachment_Delete_ForSection'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

create procedure [dbo].cs_PostAttachment_Delete_ForSection
(
	@SectionID int,
	@SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN

	SELECT PostID
	FROM cs_PostAttachments
	WHERE SectionID = @SectionID
		AND SettingsID = @SettingsID
		AND IsRemote = 0

	DELETE FROM cs_PostAttachments WHERE SectionID = @SectionID AND SettingsID = @SettingsID	
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on dbo.[cs_PostAttachment_Delete_ForSection] to public
go
/***********************************************
* Sproc: cs_PostAttachment_Temp_Create
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_PostAttachment_Temp_Create'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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



CREATE Procedure [dbo].cs_PostAttachment_Temp_Create
(
	@UserID int,
	@SettingsID int,
	@SectionID int,
	@Filename nvarchar(1024),
	@FriendlyFileName nvarchar(256) = null,
	@ContentType nvarchar(50),
	@ContentSize int,
	@IsRemote int,
	@AttachmentID uniqueidentifier,
	@Height int = 0,
	@Width int = 0
)

as
SET Transaction Isolation Level Read UNCOMMITTED

		INSERT INTO 
		cs_PostAttachments_TEMP
		(
			AttachmentID,
			SectionID,
			UserID,
			[FileName],
			[FriendlyFileName],
			ContentType,
			ContentSize,
			IsRemote,
			SettingsID,
			Created,
			[Height],
			[Width]
		)
		VALUES
		(
			@AttachmentID,
			@SectionID,
			@UserID,
			@Filename,
			@FriendlyFileName,
			@ContentType,
			@ContentSize,
			@IsRemote,
			@SettingsID,
			getdate(),
			@Height,
			@Width
		)


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[cs_PostAttachment_Temp_Create]  TO [public]
GO


/***********************************************
* Sproc: cs_PostAttachment_Temp_Expire
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_PostAttachment_Temp_Expire'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[cs_PostAttachment_Temp_Expire] 
(
	@Date datetime
)
AS

SET Transaction Isolation Level Read UNCOMMITTED
BEGIN

	SELECT AttachmentID
	FROM cs_PostAttachments_TEMP WHERE Created < @Date

	DELETE FROM cs_PostAttachments_TEMP WHERE Created < @Date

END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[cs_PostAttachment_Temp_Expire]  TO [public]
GO

/***********************************************
* Sproc: cs_PostAttachment_Temp_Get
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_PostAttachment_Temp_Get'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


CREATE procedure [dbo].[cs_PostAttachment_Temp_Get] 
(
	@UserID int,
	@SectionID int,
	@AttachmentID uniqueidentifier,
	@SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN

		SELECT
			-1 as [PostID],[SectionID], [UserID], [Created], [FileName], 
			[ContentType], [ContentSize], [SettingsID], [IsRemote], [FriendlyFileName], [Height], [Width]
		FROM
			cs_PostAttachments_TEMP
		WHERE
			AttachmentID = @AttachmentID and SettingsID = @SettingsID and UserID = @UserID and SectionID = @SectionID

END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[cs_PostAttachment_Temp_Get]  TO [public]
GO


/***********************************************
* Sproc: cs_PostAttachment_ToggleTemporary
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_PostAttachment_ToggleTemporary'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


CREATE procedure dbo.cs_PostAttachment_ToggleTemporary 
(
	@AttachmentID uniqueidentifier,
	@UserID int,
	@PostID int,
	@FriendlyFileName nvarchar(256),
	@SectionID int,
	@SettingsID int,
	@DeleteTempOnly bit = 0
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN

		SELECT
			@PostID as [PostID], [SectionID], [UserID], [Created], [FileName],  
			[ContentType], [ContentSize], [SettingsID], [IsRemote], [FriendlyFileName], [Height], [Width]
		FROM
			cs_PostAttachments_TEMP
		WHERE
			AttachmentID = @AttachmentID and SettingsID = @SettingsID


	IF(@DeleteTempOnly = 0)
	BEGIN
		IF EXISTS (SELECT PostID FROM cs_PostAttachments WHERE PostID = @PostID and SettingsID = @SettingsID )
		BEGIN
			UPDATE cs_PostAttachments SET
				cs_PostAttachments.[FileName] 	= T.[FileName],
				cs_PostAttachments.[FriendlyFileName] = IsNull(@FriendlyFileName,T.FriendlyFileName),
				cs_PostAttachments.ContentSize	= T.ContentSize,
				cs_PostAttachments.IsRemote	= T.IsRemote,
				cs_PostAttachments.Height	= T.Height,
				cs_PostAttachments.Width	= T.Width,
				cs_PostAttachments.ContentType = T.ContentType,
				cs_PostAttachments.Created = T.Created,
				cs_PostAttachments.SectionID = @SectionID,
				cs_PostAttachments.UserID = @UserID
			FROM cs_PostAttachments_TEMP T
			WHERE
				cs_PostAttachments.PostID = @PostID and
				cs_PostAttachments.SettingsID = @SettingsID and
				T.AttachmentID = @AttachmentID and T.SettingsID = @SettingsID
		END
		ELSE
		BEGIN
			INSERT INTO 
			cs_PostAttachments
			(
				PostID,
				SectionID,
				UserID,
				[FileName],
				[FriendlyFileName],
				ContentType,
				ContentSize,
				IsRemote,
				SettingsID,
				Created,
				[Height],
				[Width]
			)
			Select @PostID,	@SectionID, @UserID,T.[FileName],IsNull(@FriendlyFileName,T.FriendlyFileName), T.ContentType,
				T.ContentSize,	T.IsRemote, T.SettingsID,T.Created, T.Height, T.Width
			FROM cs_PostAttachments_TEMP T
			WHERE T.AttachmentID = @AttachmentID and T.SettingsID = @SettingsID
		END
	END

	DELETE FROM cs_PostAttachments_TEMP where AttachmentID = @AttachmentID and SettingsID = @SettingsID
	
	EXEC cs_Section_DiskUsage_Update @SectionID, @SettingsID
END




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_PostAttachment_ToggleTemporary] to public
go
/***********************************************
* Sproc: cs_PostAttachment_Update
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_PostAttachment_Update'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

create procedure [dbo].cs_PostAttachment_Update 
(
	@PostID int,
	@UserID int,
	@SectionID int,
	@Filename nvarchar(1024),
	@FriendlyFileName nvarchar(256) = null,
	@ContentType nvarchar(50),
	@ContentSize int,
	@IsRemote int,
	@SettingsID int,
	@Height int = 0,
	@Width int = 0
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN

	IF EXISTS (SELECT PostID FROM cs_PostAttachments WHERE PostID = @PostID and SettingsID = @SettingsID )
	begin
		UPDATE cs_PostAttachments SET
			[FileName] 	= @Filename,
			[FriendlyFileName] = @FriendlyFileName,
			ContentSize	= @ContentSize,
			IsRemote	= @IsRemote,
			[Height]	= @Height,
			[Width]		= @Width
		WHERE
			PostID		= @PostID and
			SettingsID	= @SettingsID	

		EXEC cs_Section_DiskUsage_Update @SectionID, @SettingsID
	end
	
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on dbo.[cs_PostAttachment_Update] to public
go
/***********************************************
* Sproc: cs_PostCategories_Get
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_PostCategories_Get'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

CREATE PROCEDURE dbo.cs_PostCategories_Get
	@SectionID int=0,
	@ApplicationKey nvarchar(256)=null
AS

SET Transaction Isolation Level Read UNCOMMITTED


if @ApplicationKey Is Not Null
begin
	SELECT
		C.CategoryID, C.SectionID, C.Name, C.IsEnabled, C.ParentID, C.Path, C.Description, C.SettingsID,
		C.TotalThreads, C.MostRecentPostDate, C.TotalSubThreads, C.MostRecentSubPostDate, C.DateCreated, C.FeaturedPostID
	FROM cs_Post_Categories C
	LEFT JOIN cs_Sections F ON F.SectionID = C.SectionID
	WHERE F.ApplicationKey = @ApplicationKey
end
else
begin
	SELECT
		C.CategoryID, C.SectionID, C.Name, C.IsEnabled, C.ParentID, C.Path, C.Description, C.SettingsID,
		C.TotalThreads, C.MostRecentPostDate, C.TotalSubThreads, C.MostRecentSubPostDate, C.DateCreated, C.FeaturedPostID
	FROM cs_Post_Categories C
	WHERE C.SectionID = @SectionID
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[cs_PostCategories_Get]  TO [public]
GO

/***********************************************
* Sproc: cs_PostCategories_Get_ByName
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_PostCategories_Get_ByName'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

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

CREATE PROCEDURE [dbo].[cs_PostCategories_Get_ByName]
	@Name nvarchar(256),
	@SectionQuerySQL ntext,
	@SettingsID int
AS

SET Transaction Isolation Level Read UNCOMMITTED

CREATE TABLE #Sections
(
	SectionID int
)

INSERT INTO #Sections (SectionID)
EXEC sp_executesql @SectionQuerySQL

SELECT
	C.CategoryID, C.SectionID, C.Name, C.IsEnabled, C.ParentID, C.Path, C.Description, C.SettingsID,
	C.TotalThreads, C.MostRecentPostDate, C.TotalSubThreads, C.MostRecentSubPostDate, C.DateCreated, C.FeaturedPostID
FROM cs_Post_Categories C
WHERE C.SectionID in (select SectionID from #Sections)
	and C.SettingsID = @SettingsID
	and C.[Name] = @Name
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[cs_PostCategories_Get_ByName]  TO [public]
GO


/***********************************************
* Sproc: cs_PostCategories_Get_WithoutPosts
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_PostCategories_Get_WithoutPosts'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

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

CREATE PROCEDURE [dbo].[cs_PostCategories_Get_WithoutPosts]
	@SectionQuerySQL ntext,
	@SettingsID int
AS

SET Transaction Isolation Level Read UNCOMMITTED

CREATE TABLE #Sections
(
	SectionID int
)

INSERT INTO #Sections (SectionID)
EXEC sp_executesql @SectionQuerySQL

SELECT
		C.CategoryID, C.SectionID, C.Name, C.IsEnabled, C.ParentID, C.Path, C.Description, C.SettingsID,
		C.TotalThreads, C.MostRecentPostDate, C.TotalSubThreads, C.MostRecentSubPostDate, C.DateCreated, C.FeaturedPostID
FROM cs_Post_Categories C
WHERE C.CategoryID not in (
		select CategoryID
		from cs_Posts_InCategories PiC
		inner join cs_Posts P on P.PostID = PiC.PostID
		where P.SectionID in (select SectionID from #Sections)
			and P.SettingsID = @SettingsID
		)
	and C.SectionID in (select SectionID from #Sections)
	and C.SettingsID = @SettingsID
	and C.CategoryID not in (
		select UpLevelID
		from cs_Post_Categories_Parents
		where SectionID in (select SectionID from #Sections)
			and UpLevelID <> CategoryID
		)	

DROP TABLE #Sections
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[cs_PostCategories_Get_WithoutPosts]  TO [public]
GO


/***********************************************
* Sproc: cs_PostCategories_Parents_RebuildIndex
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_PostCategories_Parents_RebuildIndex'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_PostCategories_Parents_RebuildIndex]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_PostCategories_Parents_RebuildIndex]
GO
CREATE PROCEDURE [dbo].[cs_PostCategories_Parents_RebuildIndex]
	@SectionID INT = NULL
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	--Create some temporary storage for the update
	DECLARE @pathtable TABLE(
		CategoryID int not null,
		UpLevelID int not null,
		[path] varchar(255) collate database_default,
		SectionID int not null,
		unique (CategoryID, UpLevelID))
	
	--Fix any orphaned categories
	UPDATE pc
	SET
		pc.ParentID = 0
	FROM
		cs_Post_Categories pc
	WHERE
		pc.ParentID <> 0
		AND pc.ParentID NOT IN ( SELECT pcx.CategoryID FROM cs_Post_Categories pcx )

	IF @SectionID IS NULL
	BEGIN

		--every post category is at least in itself
		INSERT INTO @pathtable (SectionID, CategoryID, UpLevelID, [path]) SELECT  SectionID, CategoryID, CategoryID, '/' + Convert(varchar(10),CategoryID) + '/' FROM cs_Post_Categories
		
		--Get all the non parents parents
		INSERT INTO @pathtable (SectionID, CategoryID, UpLevelID, [path]) SELECT  SectionID, CategoryID, ParentID, '/' + Convert(varchar(10),ParentID) + '/' + Convert(varchar(10),CategoryID) + '/'  FROM cs_Post_Categories WHERE ParentID > 0
	

		--Recurse until we have reached the root for all
		WHILE @@Rowcount > 0
		BEGIN
			
			INSERT INTO @pathtable (SectionID, CategoryID, UpLevelID, [path]) 

			SELECT  C.SectionID, P.CategoryID, C.ParentID,  RIGHT('/' + Convert(varchar(10),C.ParentID) + P.[path], 255)
			FROM @pathtable P
				INNER JOIN cs_Post_Categories C ON C.CategoryID = UpLevelID
				LEFT OUTER JOIN @pathtable DUPE ON P.CategoryID = DUPE.CategoryID AND C.ParentID = DUPE.UpLevelID
			WHERE ParentID > 0
				AND DUPE.UpLevelID IS NULL

		END
	

	END
	ELSE
	BEGIN
		--every post category is at least in itself (for this section)
		INSERT INTO @pathtable (SectionID, CategoryID, UpLevelID, [path]) SELECT SectionID, CategoryID, CategoryID, '/' + Convert(varchar(10),CategoryID) + '/'  FROM cs_Post_Categories WHERE SectionID = @SectionID

		--Get all the non parents parents (for this section)
		INSERT INTO @pathtable (SectionID, CategoryID, UpLevelID, [path]) SELECT SectionID, CategoryID, ParentID, '/' + Convert(varchar(10),ParentID) + '/' + Convert(varchar(10),CategoryID) + '/'  FROM cs_Post_Categories WHERE ParentID > 0 AND SectionID = @SectionID

		--Recurse until we have reached the root for all (for this section)
		WHILE @@Rowcount > 0
		BEGIN
			INSERT INTO @pathtable (SectionID, CategoryID, UpLevelID, [path]) 

			SELECT C.SectionID, P.CategoryID, C.ParentID,   RIGHT('/' + Convert(varchar(10),C.ParentID) + P.[path], 255)
			FROM @pathtable P
				INNER JOIN cs_Post_Categories C ON C.CategoryID = UpLevelID
				LEFT OUTER JOIN @pathtable DUPE ON P.CategoryID = DUPE.CategoryID AND C.ParentID = DUPE.UpLevelID
			WHERE ParentID > 0
				AND DUPE.UpLevelID IS NULL AND C.SectionID = @SectionID
		END
	END

	--Recalculate category stats for selected section
	UPDATE pc SET
		TotalSubThreads = QSUB.posts,
		MostRecentSubPostDate = QSUB.postdate,
		TotalThreads = QCURR.posts,
		MostRecentPostDate = QCURR.postdate
	FROM cs_Post_Categories pc
	INNER JOIN (
		SELECT P.UpLevelID CategoryID, COUNT(PIC.PostID) posts, MAX(jP.PostDate) postdate 
		FROM 
			cs_Posts_InCategories PIC 
			INNER JOIN cs_Posts jP ON jP.PostID = PIC.PostID AND jP.PostLevel = 1 --AND jP.IsApproved = 1 -- this was not originally included in this sproc
			INNER JOIN @pathtable P ON PIC.CategoryID = P.CategoryID
		GROUP BY P.UpLevelID
	) QSUB ON pc.CategoryID = QSUB.CategoryID
	INNER JOIN (
		SELECT PIC.CategoryID CategoryID, COUNT(PIC.PostID) posts, MAX(jP.PostDate) postdate 
		FROM 
			cs_Posts_InCategories PIC 
			INNER JOIN cs_Posts jP ON jP.PostID = PIC.PostID AND jP.PostLevel = 1 --AND jP.IsApproved = 1 -- this was not originally included in this sproc
			INNER JOIN @pathtable P ON PIC.CategoryID = P.CategoryID
		GROUP BY PIC.CategoryID
	) QCURR ON pc.CategoryID = QCURR.CategoryID

	--Commit the path table for use in adding / deleting posts in categories
	-- adding Uplevels that were 
	INSERT INTO cs_Post_Categories_Parents (SectionID, CategoryID, UpLevelID)
	SELECT pt.SectionID, pt.CategoryID, pt.UpLevelID
	FROM @pathtable pt
	WHERE NOT EXISTS (
		select 1 from cs_Post_Categories_Parents pcp
		where pt.CategoryID = pcp.CategoryID
			and pt.UpLevelID = pcp.UpLevelID
	)
	
	IF @SectionID IS NULL
		DELETE pcp
		FROM cs_Post_Categories_Parents pcp
		WHERE
			not exists (
				select 1 from @pathtable pt
				where pt.CategoryID = pcp.CategoryID
					and pt.UpLevelID = pcp.UpLevelID
			)
	ELSE
		-- consider an index on SectionID on this table
		DELETE pcp
		FROM cs_Post_Categories_Parents pcp
		WHERE
			pcp.SectionID = @SectionID
			and not exists (
				select 1 from @pathtable pt
				where pt.CategoryID = pcp.CategoryID
					and pt.UpLevelID = pcp.UpLevelID
			)
	
	--Rebuild the path (for legacy support, this value is not currently used by the galleries)

	SELECT P.CategoryID, P.[path] from @pathtable P JOIN cs_Post_Categories C on P.UpLevelID = C.CategoryID and C.ParentID = 0

	--Commit the new path to the Categoies table
	UPDATE cs_Post_Categories SET cs_Post_Categories.[Path] = NewPath.[path] from cs_Post_Categories 
		JOIN 
			(SELECT P.CategoryID, P.[path] FROM @pathtable P 
				JOIN cs_Post_Categories C ON P.UpLevelID = C.CategoryID AND C.ParentID = 0
			) NewPath ON cs_Post_Categories.CategoryID = NewPath.CategoryID

END
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[cs_PostCategories_Parents_RebuildIndex]  TO [public]
GO


/***********************************************
* Sproc: cs_PostCategory_CreateUpdateDelete
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_PostCategory_CreateUpdateDelete'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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





CREATE PROCEDURE [dbo].[cs_PostCategory_CreateUpdateDelete]
	@DeleteCategory bit=0,
	@SectionID int=0,
	@Name nvarchar(256)='',
	@IsEnabled bit=1,
	@ParentID int=0,
	@Description nvarchar(2000)=null,
	@SettingsID int,
	@FeaturedPostID int = null,
	@CategoryID int=0 out
AS
SET Transaction Isolation Level Read UNCOMMITTED
-- Are we deleting?
if @DeleteCategory = 1
begin
	DELETE FROM cs_Posts_InCategories WHERE CategoryID = @CategoryID
	DELETE FROM [cs_Post_Categories_Parents] WHERE CategoryID = @CategoryID
	DELETE FROM cs_Post_Categories WHERE CategoryID = @CategoryID and SectionID = @SectionID and SettingsID = @SettingsID
	EXEC dbo.cs_PostCategories_Parents_RebuildIndex @SectionID
	RETURN
end

-- Find out the new path
declare @Path nvarchar(255)
set @Path = isnull((select Path + convert(nvarchar, CategoryID) + '/' from cs_Post_Categories where CategoryID = @ParentID and SectionID = @SectionID and SettingsID = @SettingsID), '/')

-- Are we updating?
if @CategoryID > 0
begin
	IF EXISTS (SELECT CategoryID FROM cs_Post_Categories WHERE Name = @Name AND SectionID = @SectionID AND SettingsID = @SettingsID AND CategoryID <> @CategoryID) BEGIN
		SET @CategoryID = -1
		RETURN
	END

	UPDATE cs_Post_Categories SET
		Name = @Name,
		IsEnabled = @IsEnabled,
		ParentID = @ParentID,
		Description = @Description,
		Path = @Path,
		FeaturedPostID = @FeaturedPostID
	WHERE CategoryID = @CategoryID and SectionID = @SectionID and SettingsID = @SettingsID
end
else
begin
	IF EXISTS (SELECT CategoryID FROM cs_Post_Categories WHERE Name = @Name AND SectionID = @SectionID AND SettingsID = @SettingsID) BEGIN
		SET @CategoryID = -1
		RETURN
	END

	INSERT INTO cs_Post_Categories (SectionID, Name, IsEnabled, ParentID, [Description], [Path], SettingsID, FeaturedPostID)
		VALUES (@SectionID, @Name, @IsEnabled, @ParentID, @Description, @Path, @SettingsID, @FeaturedPostID)
	set @CategoryID = SCOPE_IDENTITY()
end
EXEC dbo.cs_PostCategories_Parents_RebuildIndex @SectionID

GO


SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[cs_PostCategory_CreateUpdateDelete]  TO [public]
GO

/***********************************************
* Sproc: cs_PostMetadata_Get
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_PostMetadata_Get'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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


CREATE PROCEDURE dbo.cs_PostMetadata_Get
	@PostID int,
	@SettingsID int
AS
SET Transaction Isolation Level Read UNCOMMITTED

select MetaKey, MetaType, MetaValue from cs_PostMetadata where PostID = @PostID
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_PostMetadata_Get] to public
go
/***********************************************
* Sproc: cs_PostMetadata_Update
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_PostMetadata_Update'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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


CREATE PROCEDURE dbo.cs_PostMetadata_Update
	@PostID int,
	@MetadataList ntext,
	@SettingsID int
AS
SET Transaction Isolation Level Read UNCOMMITTED
delete from cs_PostMetadata where PostID = @PostID

if @MetadataList is null --or len(ltrim(rtrim(@MetadataList))) = 0
return

declare @idoc int

EXEC sp_xml_preparedocument @idoc OUTPUT, @MetadataList

insert into cs_PostMetaData (PostID, MetaKey, MetaType, MetaValue)
	select @PostID, M.[key], M.[type], M.[value]
	from openxml(@idoc, '/entries/entry', 1)
		with ([key] nvarchar(100), [type] nvarchar(100), [value] nvarchar(100)) as M

EXEC sp_xml_removedocument @idoc
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_PostMetadata_Update] to public
go
/***********************************************
* Sproc: cs_PostPoints_Add
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_PostPoints_Add'

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

/***********************************************
* Sproc: cs_posts_AddAuditDeletedPostContent
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_posts_AddAuditDeletedPostContent'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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


Create Proc [dbo].cs_posts_AddAuditDeletedPostContent
(
	@PostID int = -1,
	@UserID int  = -1,
	@SectionID int = -1,
	@SettingsID int = -1,
	@ApplicationType int = 9999,
	@ParentID int = -1,
	@ThreadID int = -1,
	@PostType int = -1,
	@PostLevel int = -1,
	@Body ntext = null,
	@Subject nvarchar(500) = null,
	@IPAddress nvarchar(50) = null,
	@PropertyNames ntext = null,
	@PropertyValues ntext = null,
	@SpamScore int = -1,
	@PostStatus int = -1
)

as

Insert cs_posts_deleted_archive (PostID, UserID, SectionID, SettingsID, ApplicationType, Body, Subject, IPAddress, DeletedDate, ParentID, ThreadID, PostLevel, PostType, PropertyNames, PropertyValues, SpamScore, PostStatus)
Values (@PostID, @UserID, @SectionID, @SettingsID, @ApplicationType, @Body, @Subject, @IPAddress, getdate(), @ParentID, @ThreadID, @PostLevel, @PostType, @PropertyNames, @PropertyValues, @SpamScore, @PostStatus)


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXECUTE on dbo.cs_posts_AddAuditDeletedPostContent to public
GO
/***********************************************
* Sproc: cs_Posts_GetPostIDByFilter
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_Posts_GetPostIDByFilter'

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_Posts_GetPostIDByFilter]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_Posts_GetPostIDByFilter]
GO
--------------------------------------------------------------------------------
--	cs_Posts_GetPostIDByFilter
--	Returns a list of filtered PostIDs
--------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[cs_Posts_GetPostIDByFilter]
(
	@SettingsID		int,
	@FilterType		int,
	@FilterValue	varchar(255)
)
AS
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


	-- local variables
	DECLARE	@PostSetSize	int


	IF @FilterType = 1	-- StalePoints
	BEGIN

		-- filter value represent size of post set
		SET @PostSetSize = CONVERT(int, @FilterValue)

		-- limit number of posts to work with (non-positive = ALL)
		IF @PostSetSize > 0
			SET ROWCOUNT @PostSetSize

		-- get posts to work with
		SELECT		P.PostID
		FROM		cs_Posts P
		INNER JOIN	cs_Sections S				-- exclude private messages
			ON		P.SectionID = S.SectionID
		WHERE		P.SettingsID = @SettingsID
			AND		S.EnablePostPoints = 1		-- ignore excluded sections
			AND		P.IsApproved = 1			-- ignore posts awaiting moderation
		ORDER BY	P.PointsUpdated				-- start with "stalest" posts

		-- limit has been used; reset to default
		IF @PostSetSize > 0
			SET ROWCOUNT 0
	END


	RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT EXECUTE on [dbo].[cs_Posts_GetPostIDByFilter] to [public]

/***********************************************
* Sproc: cs_Posts_GetPostInCategories
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_Posts_GetPostInCategories'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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





CREATE PROCEDURE dbo.cs_Posts_GetPostInCategories
	@PostID int
AS

SET Transaction Isolation Level Read UNCOMMITTED

SELECT
	C.*
FROM cs_Posts_InCategories P
RIGHT JOIN cs_Post_Categories C ON C.CategoryID = P.CategoryID
WHERE P.PostID = @PostID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[cs_Posts_GetPostInCategories]  TO [public]
GO

/***********************************************
* Sproc: cs_Posts_GetPostsInNoCategories
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_Posts_GetPostsInNoCategories'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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





CREATE PROCEDURE dbo.cs_Posts_GetPostsInNoCategories
	@SectionID int
AS
SET Transaction Isolation Level Read UNCOMMITTED
SELECT
	P.PostID, P.ThreadID, P.ParentID, P.PostAuthor, P.UserID, P.SectionID, P.PostLevel, P.SortOrder, P.Subject, P.PostDate, P.IsApproved,
	P.IsLocked, P.IsIndexed, P.TotalViews, P.Body, P.FormattedBody, P.IPAddress, P.PostType, P.EmoticonID, P.SettingsID, P.AggViews,
	P.PropertyNames as PostPropertyNames, P.PropertyValues as PostPropertyValues
FROM cs_Posts P
WHERE P.SectionID = @SectionID
	AND P.ParentID = P.PostID
	AND P.PostID NOT IN ( SELECT PostID FROm cs_Posts_InCategories )
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[cs_Posts_GetPostsInNoCategories]  TO [public]
GO

/***********************************************
* Sproc: cs_Posts_ReindexByGroup
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_Posts_ReindexByGroup'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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


CREATE PROCEDURE [dbo].[cs_Posts_ReindexByGroup]
(
	@GroupID	int,
	@SettingsID	int
)
AS
	-- first, set the post to be removed from the index
	INSERT cs_es_Search_RemoveQueue (PostID, SettingsID) 
	SELECT P.PostID, P.SettingsID 
	FROM cs_Posts P 
	INNER JOIN cs_Sections S on S.SectionID = P.SectionID
	WHERE S.GroupID = @GroupID AND P.SettingsID = @SettingsID
	AND	PostID NOT IN (SELECT PostID FROM cs_es_Search_RemoveQueue)

	-- step 2, add post to reindex table
	UPDATE cs_Posts set IsIndexed = 0 
	FROM cs_Posts P 
	INNER JOIN cs_Sections S on S.SectionID = P.SectionID
	WHERE S.GroupID = @GroupID AND P.SettingsID = @SettingsID
GO


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_Posts_ReindexByGroup] to public
go
/***********************************************
* Sproc: cs_Posts_ReindexBySection
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_Posts_ReindexBySection'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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


CREATE PROCEDURE [dbo].[cs_Posts_ReindexBySection]
(
	@SectionID	int,
	@SettingsID	int
)
AS
	--this proc intentionally renames all posts across all settingsids by that user


	-- first, set the post to be removed from the index
	INSERT cs_es_Search_RemoveQueue (PostID, SettingsID) 
	SELECT P.PostID, P.SettingsID 
	FROM cs_Posts P 
	WHERE P.SectionID = @SectionID AND P.SettingsID = @SettingsID
	AND	PostID NOT IN (SELECT PostID FROM cs_es_Search_RemoveQueue)

	-- step 2, add post to reindex table
	UPDATE cs_Posts set IsIndexed = 0 
	FROM cs_Posts P 
	WHERE P.SectionID = @SectionID AND P.SettingsID = @SettingsID
GO


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_Posts_ReindexBySection] to public
go
/***********************************************
* Sproc: cs_Posts_ReindexByTag
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_Posts_ReindexByTag'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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


CREATE PROCEDURE [dbo].[cs_Posts_ReindexByTag]
(
	@CategoryID	int,
	@SettingsID	int
)
AS
	-- first, set the post to be removed from the index
	INSERT cs_es_Search_RemoveQueue (PostID, SettingsID) 
	SELECT P.PostID, P.SettingsID 
	FROM cs_Posts P 
		JOIN cs_Posts_InCategories PIC on P.PostID = PIC.PostID 
	WHERE PIC.CategoryID = @CategoryID AND P.SettingsID = @SettingsID
	AND	P.PostID NOT IN (SELECT PostID FROM cs_es_Search_RemoveQueue)

	-- step 2, reset post to be indexed
	UPDATE cs_Posts set IsIndexed = 0 
	From cs_Posts P 
		JOIN cs_Posts_InCategories PIC on P.PostID = PIC.PostID 
	WHERE PIC.CategoryID = @CategoryID AND P.SettingsID = @SettingsID
GO


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_Posts_ReindexByTag] to public
go
/***********************************************
* Sproc: cs_Posts_ReindexByUser
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_Posts_ReindexByUser'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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


CREATE PROCEDURE [dbo].[cs_Posts_ReindexByUser]
(
	@UserID	int
)
AS
	--this proc intentionally renames all posts across all settingsids by that user


	-- first, set the post to be removed from the index
	INSERT INTO	cs_es_Search_RemoveQueue (PostID, SettingsID)
	SELECT		PostID, SettingsID
	FROM		cs_Posts
	WHERE		UserID = @UserID
		AND	PostID NOT IN (SELECT PostID FROM cs_es_Search_RemoveQueue)

	-- step 2, add post to reindex table
	UPDATE	cs_Posts
	SET		IsIndexed =  0
	WHERE	UserID = @UserID
GO


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_Posts_ReindexByUser] to public
go
/***********************************************
* Sproc: cs_Posts_UpdatePostsInCategories
* File Date: 4/14/2008 11:35:02 AM
***********************************************/
Print 'Creating...cs_Posts_UpdatePostsInCategories'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

CREATE PROCEDURE [dbo].[cs_Posts_UpdatePostsInCategories]
(
	@CategoryList nvarchar(4000) = null,
	@SectionID int,
	@PostID int,
	@SettingsID int = null,
	@UpdateStats bit = 1
)
as
begin

	set nocount on
	
	-- If the post is not in any categories, we have to rebuild the index incase we are deleting a post or removing categories
	IF @CategoryList Is Not Null AND LEN(LTRIM(RTRIM(@CategoryList))) > 0
	BEGIN
		DECLARE @idoc int
		-- declare @CategoryList nvarchar(4000)
		-- select @CategoryList = "<?xml version=""1.0"" ?><Categories><Category>Test</Category></Categories>"

		DECLARE @CategoryNameList TABLE (
			CategoryName nvarchar(255) collate database_default not null
		)
		EXEC sp_xml_preparedocument @idoc OUTPUT, @CategoryList
		
		insert into @CategoryNameList(CategoryName)
		Select DISTINCT
			CONVERT(nvarchar(255), X.[text])
		FROM 
			OPENXML(@idoc, '/Categories/Category/', 2) X
		where 
			X.[text] is not null  

		EXEC sp_xml_removedocument @idoc
		
		--Insert Missing Categories
		INSERT INTO cs_Post_Categories (SectionID, [Name], IsEnabled, ParentID, [Description], SettingsID)
		Select
			@SectionID, X.CategoryName, 1, 0, null, @SettingsID
		FROM 
			@CategoryNameList X
		where 
			not exists (
				Select 1 FROM cs_Post_Categories pc
				where
					X.CategoryName = pc.[Name]
					and pc.SectionID = @SectionID
			) 
		
		IF @@ROWCOUNT > 0
			exec [cs_PostCategories_Parents_RebuildIndex] @SectionID

		--Insert Entry Categories
		INSERT INTO cs_Posts_InCategories ( PostID, CategoryID, SettingsID )
		SELECT
			@PostID, C.CategoryID, @SettingsID
		FROM
			cs_Post_Categories C
		WHERE
			C.SectionID = @SectionID
			and EXISTS (
				select 1 from @CategoryNameList x
				where
					C.[Name] = x.CategoryName
			)
			and not exists (
				select 1 from cs_Posts_InCategories pic
				where
					pic.PostID = @PostID
					and pic.CategoryID = C.CategoryID
					and pic.SettingsID = @SettingsID
			)
		
		DELETE pic FROM cs_Posts_InCategories pic
		WHERE
			pic.PostID = @PostID
			and not exists (
				select 1 from cs_Post_Categories C
				inner join @CategoryNameList x ON C.[Name] = x.CategoryName
				where
					C.CategoryID = pic.CategoryID
			)
		
	END ELSE BEGIN
	
		DELETE pic FROM cs_Posts_InCategories pic
		WHERE pic.PostID = @PostID
	
	END

	if(@UpdateStats = 1) --for changes we need to rebuild the entire section stats
	Begin
		UPDATE pc SET
			pc.TotalSubThreads = IsNull(QSUB.posts, 0),
			pc.MostRecentSubPostDate = QSUB.postdate,
			pc.TotalThreads = IsNull(QCURR.posts, 0),
			pc.MostRecentPostDate = QCURR.postdate
		FROM cs_Post_Categories pc
		LEFT JOIN (
			SELECT P.UpLevelID CategoryID, COUNT(PIC.PostID) posts, MAX(jP.PostDate) postdate
			FROM
				cs_Posts_InCategories PIC
				INNER JOIN cs_Posts jP
					ON jP.PostID = PIC.PostID AND jP.IsApproved = 1 AND jP.PostLevel = 1
				INNER JOIN cs_Post_Categories_Parents P
					ON PIC.CategoryID = P.CategoryID AND jP.SectionID = P.SectionID AND P.SectionID = @SectionID
				INNER JOIN cs_Post_Categories C
					ON C.CategoryID = P.CategoryID
			GROUP BY P.UpLevelID
		) QSUB ON pc.CategoryID = QSUB.CategoryID
		LEFT JOIN (
			SELECT PIC.CategoryID CategoryID, COUNT(PIC.PostID) posts, MAX(jP.PostDate) postdate 
			FROM 
				cs_Posts_InCategories PIC 
				INNER JOIN cs_Posts jP
					ON jP.PostID = PIC.PostID AND jP.SectionID = @SectionID AND jP.PostLevel = 1 AND jP.IsApproved = 1
			GROUP BY PIC.CategoryID
		) QCURR ON pc.CategoryID = QCURR.CategoryID
		WHERE
			pc.SectionID = @SectionID
	End
end
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_Posts_UpdatePostsInCategories] to public
go

/***********************************************
* Sproc: cs_Posts_Views_Add
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_Posts_Views_Add'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

Create Proc [dbo].[cs_Posts_Views_Add]
(
	@PostID int,
	@AggCount int,
	@WebCount int,
	@WebThreadCount int
)

as
begin
	SET Transaction Isolation Level Read UNCOMMITTED
	
	declare @ThreadID int
	declare @SectionID int
	
	-- using a non-documented "feature" of sql server that allows you
	-- to set a variable in an UPDATE statement.  This allows us to get
	-- a cheap read on the cs_Posts table.
	Update
		p
	Set 
		p.TotalViews = p.TotalViews + @WebCount,
		p.AggViews = p.AggViews + @AggCount,
		
		-- set some local variables for later queries
		@ThreadID = p.ThreadID,
		@SectionID = p.SectionID
	from
		cs_Posts p
	Where
		p.PostID = @PostID
	
	IF @WebThreadCount > 0 BEGIN
		update
			t
		set
			t.TotalViews = t.TotalViews + @WebThreadCount
		from
			cs_Threads t
		where
			t.ThreadID = @ThreadID
	END
	
	-- UPDATE Daily Views
	DECLARE @TotalViews int
	SET @TotalViews = @AggCount + @WebThreadCount
	IF @TotalViews > 0 BEGIN
		exec cs_Thread_Daily_Stats_Increment @SectionID, @ThreadID, @TotalViews
	END
end
go
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

grant execute on [dbo].[cs_Posts_Views_Add] to public
go
/***********************************************
* Sproc: cs_Post_CreateUpdate
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_Post_CreateUpdate'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

CREATE PROCEDURE [dbo].[cs_Post_CreateUpdate]
(
	@SectionID int,
	@ParentID int,
	@AllowDuplicatePosts bit,
	@DuplicateIntervalInMinutes int = 0,
	@Subject nvarchar(256),
 	@UserID int,
	@PostAuthor nvarchar(64) = null,
	@Body ntext,
	@FormattedBody ntext,
	@EmoticonID int = 0,
	@IsLocked bit,
	@IsSticky bit,
	@IsApproved bit,
	@StickyDate datetime,
	@PostType int = 0,
	@PostMedia int = 0,
	@PostDate datetime = null,
	@UserHostAddress nvarchar(32),
	@PropertyNames ntext = null,
	@PropertyValues ntext = null,
	@SettingsID int, 
	@IsTracked bit = 0,
	@PostID int out,
	@ThreadID int=0 out,
	@PostConfiguration int = 0,
	@UpdateSectionStatistics bit = 1,
	@UserTime datetime = null,
	@ApplicationPostType int,
	@PostName nvarchar(256) =  null,
	@ThreadStatus int = 0,
	@Points int = 0,
	@PostDisplayName nvarchar(64) = null,
	@AutoApprove bit = 0
) 
AS

--	stored procedure cs_Post_CreateUpdate
--	* Procedure now creates new threads here, from an identity column in cs_Threads.
--	* To update a Post, ParentID must not equal 0.
--	* To delete a post, use forums_Moderate_Posts_Move to move it to the Deleted Forum
--  * Default value for ThreadStatus is Not Set (0)

SET NOCOUNT ON
DECLARE @MaxSortOrder int
DECLARE @ParentLevel int
DECLARE @ParentSortOrder int
DECLARE @NextSortOrder int
DECLARE @ModeratedForum bit
DECLARE @EnablePostStatistics bit
DECLARE @TrackThread bit
DECLARE @ContextUserID int
DECLARE @ContextPostAuthor nvarchar(64)

-----------------------------
--	Enough data is not present here to accurately determine if this post is spam or not (specifically: PostStatus)
--	If this is updated to pass this information here, it needs a check for this bit and a call to cs_SpamPostArchive
-----------------------------

-- Set 'not set' for Sticky and Anouncement posts
IF ((@IsSticky = 1) OR ((@IsLocked = 1) AND ( @StickyDate > DATEADD( Year, 2, getdate() ) )))
	SET @ThreadStatus = 0 -- not set value

-- set the PostDate
IF @PostDate IS NULL
	SET @PostDate = GetDate()

IF @UserTime IS NULL
	SET @UserTime = GetDate()

-- Get the real username for poster
IF @PostAuthor IS NULL
	SELECT 
		@PostAuthor = UserName
	FROM 
		cs_vw_Users_FullUser  
	WHERE 
		UserID = @UserID and SettingsID = @SettingsID

-- Check if this is an anonymous post (value = 1)
IF (@PostConfiguration & 1) = 1
BEGIN
	-- Anonymous ID
	SELECT @ContextUserID = UserID FROM cs_vw_Users_FullUser WHERE SettingsID = @SettingsID and IsAnonymous = 1

	-- Set the username
	IF(@PostAuthor is not null)
	begin
		SET @ContextPostAuthor = @PostAuthor
	end
	else
	begin
		SET @ContextPostAuthor = ''
	end
END
ELSE
BEGIN
	-- Set the real user ID
	SET @ContextUserID = @UserID

	IF(@PostDisplayName is not null)
	Begin
		-- Set the real username
		SET @ContextPostAuthor = @PostDisplayName
	END
	ELSE
	BEGIN
		SET @ContextPostAuthor = @PostAuthor
	END
END

-- Do we care about duplicates?
IF @AllowDuplicatePosts = 0
BEGIN
	DECLARE @IsDuplicate bit
	exec cs_system_DuplicatePost @UserID, @Body, @DuplicateIntervalInMinutes, @SettingsID, @IsDuplicate output

	IF @IsDuplicate = 1
	BEGIN
		SET @PostID = -1	
		RETURN	-- Exit with error code.
	END
END

-- we need to get the SectionID, if the ParentID is not null (there should be a SectionID)
IF @SectionID = 0 AND @ParentID <> 0
	SELECT 
		@SectionID = SectionID
	FROM 
		cs_Posts (nolock) 
	WHERE 
		PostID = @ParentID and SettingsID = @SettingsID

-- Is this forum moderated?
SELECT 
	@ModeratedForum = IsModerated, @EnablePostStatistics = EnablePostStatistics
FROM 
	cs_Sections (nolock)
WHERE 
	SectionID = @SectionID and SettingsID = @SettingsID

-- Determine if this post will be approved.
-- If the forum is NOT moderated, then the post will be approved by default.
SET NOCOUNT ON
BEGIN TRAN

IF @AutoApprove = 1
BEGIN
	SET @IsApproved = 1
END
ELSE IF @IsApproved = 1 AND @ModeratedForum = 1 AND @SectionID <> 0
BEGIN
	-- ok, this is a moderated forum.  Is this user trusted?  If he is, then the post is approved ; else it is not
	SET @IsApproved = ( 
		SELECT
			ModerationLevel
		FROM
			cs_UserProfile (nolock)
		WHERE
			UserID = @UserID and SettingsID = @SettingsID )
END


-- EAD: Modifying this sproc to insert directly into cs_Threads.  We are no longer keying
-- cs_Threads.ThreadID to be same number as the top PostID for the thread.  This is to allow
-- for the FKs to be put into place.

IF @ParentID = 0	-- parameter indicating this is a top-level post (for a new thread)
BEGIN
	-- First we create a new ThreadID.

	-- check the StickyDate to ensure it's not null
	IF @StickyDate < @PostDate
		SET @StickyDate = @PostDate

	INSERT cs_Threads 	
		( SectionID,
		PostDate, 
		UserID, 
		PostAuthor, 
		ThreadDate, 
		MostRecentPostAuthor, 
		MostRecentPostAuthorID, 	
		MostRecentPostID,
		IsLocked, 
		IsApproved,
		IsSticky, 
		StickyDate, 
		ThreadEmoticonID,
		SettingsID,
		ThreadStatus )
	VALUES
		( @SectionID, 
		@PostDate, 
		@UserID, 
		@PostAuthor,
		@PostDate,
		@ContextPostAuthor,
		@ContextUserID, 
		0,	-- MostRecentPostID, which we don't know until after post INSERT below.
		@IsLocked,
		@IsApproved,
		@IsSticky,
		@StickyDate,
		@EmoticonID,
		@SettingsID,
		@ThreadStatus )

	-- Get the new ThreadID
	SELECT 
		@ThreadID = SCOPE_IDENTITY()
	--FROM
		--cs_Threads
		
	-- Now we add the new post
	INSERT cs_Posts 
		( SectionID, 
		ThreadID, 
		ParentID, 
		PostLevel, 
		SortOrder, 
		Subject, 
		UserID, 
		PostAuthor, 
		IsApproved, 
		IsLocked, 

		Body, 

		FormattedBody, 
		PostType, 
		PostMedia,
		PostDate, 
		IPAddress, 
		EmoticonID,
		PropertyNames,
		PropertyValues,
		SettingsID,
		PostConfiguration,
		UserTime,
		PostName,
		ApplicationPostType,
		Points )
	VALUES 
		( @SectionID, 
		@ThreadID, 
		0, 	-- ParentID, which we don't know until after INSERT
		1, 	-- PostLevel, 1 marks start/top/first post in thread.
		1, 	-- SortOrder (not in use at this time)
		@Subject, 
		@UserID, 
		@PostAuthor,
		@IsApproved, 
		@IsLocked, 
		@Body, 
		@FormattedBody, 
		@PostType, 
		@PostMedia, 
		@PostDate, 
		@UserHostAddress, 
		@EmoticonID,
		@PropertyNames,
		@PropertyValues,
		@SettingsID,
		@PostConfiguration,
		@UserTime,
		@PostName,
		@ApplicationPostType,
		@Points )
		

	-- Get the new PostID
	SELECT 
		@PostID = SCOPE_IDENTITY()
--	FROM
--		cs_Posts

	-- Update the new Thread with the new PostID
	UPDATE 
		cs_Threads
	SET 
		MostRecentPostID = @PostID
	WHERE 
		ThreadID = @ThreadID and SettingsID = @SettingsID

	-- Update the new Post's ParentID with the new PostID
	UPDATE 
		cs_Posts
	SET 
		ParentID = @PostID
	WHERE 
		PostID = @PostID and SettingsID = @SettingsID

END
ELSE BEGIN	-- @ParentID <> 0 means there is a reply to an existing post

	-- Get the Post Information for what we are replying to
	SELECT 
		@ThreadID = ThreadID,
		@SectionID = SectionID,
		@ParentLevel = PostLevel,
		@ParentSortOrder = SortOrder
	FROM 
		cs_Posts
	WHERE 
		PostID = @ParentID and SettingsID = @SettingsID

	-- Is there another post at the same level or higher?
	SET @NextSortOrder = (
		SELECT 	
			MIN(SortOrder) 
		FROM 
			cs_Posts 
		WHERE 
			PostLevel <= @ParentLevel 
			AND SortOrder > @ParentSortOrder 
			AND ThreadID = @ThreadID  and SettingsID = @SettingsID )

	IF @NextSortOrder > 0
	BEGIN
		-- Move the existing posts down
		UPDATE 
			cs_Posts
		SET 
			SortOrder = SortOrder + 1
		WHERE 
			ThreadID = @ThreadID
			AND SortOrder >= @NextSortOrder and SettingsID = @SettingsID

		SET @MaxSortOrder = @NextSortOrder

	END
   	ELSE BEGIN 	-- There are no posts at this level or above
	
		-- Find the highest sort order for this parent
		SELECT 
			@MaxSortOrder = MAX(SortOrder) + 1
		FROM 
			cs_Posts
		WHERE 
			ThreadID = @ThreadID and SettingsID = @SettingsID

	END 

	-- Insert the new post
	INSERT cs_Posts 
		( SectionID, 
		ThreadID, 
		ParentID, 
		PostLevel, 
		SortOrder, 
		Subject, 
		UserID, 
		PostAuthor, 
		IsApproved, 
		IsLocked, 
		Body, 
		FormattedBody, 
		PostType, 
		PostMedia, 
		PostDate, 
		IPAddress, 
		EmoticonID,
		PropertyNames,
		PropertyValues,
		SettingsID,
		PostConfiguration,
		UserTime,
		PostName,
		ApplicationPostType,
		Points )
	VALUES 
		( @SectionID, 
		@ThreadID, 
		@ParentID, 
		@ParentLevel + 1, 
		@MaxSortOrder,
		@Subject, 
		@UserID, 
		@PostAuthor, 
		@IsApproved, 
		@IsLocked, 
		@Body, 
		@FormattedBody, 
		@PostType, 
		@PostMedia, 
		@PostDate, 
		@UserHostAddress, 
		@EmoticonID,
		@PropertyNames,
		@PropertyValues,
		@SettingsID,
		@PostConfiguration,
		@UserTime,
		@PostName,
		@ApplicationPostType,
		@Points )


	-- Now check to see if this post is Approved by default.
	-- If so, we go ahead and update the Threads table for the MostRecent items.
	IF @IsApproved = 1
	BEGIN		
		-- Grab the new PostID and update the ThreadID's info
		SELECT 
			@PostID = SCOPE_IDENTITY() 
--		FROM 
--			cs_Posts

		-- To cut down on overhead, I've elected to update the thread's info
		-- directly from here, without running cs_system_UpdateThread since
		-- I already have all of the information that this sproc would normally have to lookup.
		Select @StickyDate = StickyDate FROM cs_Threads where ThreadID = @ThreadID and SettingsID = @SettingsID

		IF @StickyDate < @PostDate
			SET @StickyDate = @PostDate

		UPDATE
			cs_Threads 	
		SET 
			MostRecentPostAuthor = @ContextPostAuthor,
			MostRecentPostAuthorID = @ContextUserID,
			MostRecentPostID = @PostID,
			TotalReplies = TotalReplies + 1, -- (SELECT COUNT(*) FROM cs_Posts WHERE ThreadID = @ThreadID AND IsApproved = 1 AND PostLevel > 1),
			IsLocked = @IsLocked,
			StickyDate = @StickyDate,	-- this makes the thread a sticky/announcement, even if it's a reply.
			ThreadDate = @PostDate
		WHERE
			ThreadID = @ThreadID and SettingsID = @SettingsID
	END
	ELSE
	BEGIN
		-- Moderated Posts: get the new PostID
		SELECT @PostID = SCOPE_IDENTITY() 
	END

	-- Clean up ThreadsRead (this should work very well now)
	DELETE
		cs_ThreadsRead
	WHERE
		ThreadID = @ThreadID 
		AND UserID != @UserID and SettingsID = @SettingsID
END


-- Update the users tracking for the new post (if needed)
SELECT 
	@TrackThread = EnableThreadTracking 
FROM 
	cs_UserProfile (nolock) 
WHERE 
	UserID = @UserID and SettingsID = @SettingsID

IF @TrackThread = 1 and @IsTracked = 1
	-- If a row already exists to track this thread for this user, do nothing - otherwise add the row
	IF NOT EXISTS ( SELECT ThreadID FROM cs_TrackedThreads (nolock) WHERE ThreadID = @ThreadID AND UserID = @UserID  and SettingsID = @SettingsID)
		INSERT INTO cs_TrackedThreads 
			( ThreadID, UserID, SettingsID)
		VALUES
			( @ThreadID, @UserID, @SettingsID )

COMMIT TRAN
BEGIN TRAN

-- Update the forum statitics
IF @SectionID > 0 AND @UserID > 0
BEGIN
	IF @IsApproved = 1 AND @EnablePostStatistics = 1
	BEGIN
		EXEC cs_system_UpdateUserPostCount @SectionID, @UserID, @SettingsID
	END

	IF @UpdateSectionStatistics = 1
	BEGIN
		EXEC cs_system_UpdateForum @SectionID, @ThreadID, @PostID, @SettingsID, 1, @PostDisplayName
	END
END


COMMIT TRAN
SET NOCOUNT OFF

SELECT @PostID = @PostID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[cs_Post_CreateUpdate]  TO [public]
GO


/***********************************************
* Sproc: cs_Post_Delete
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_Post_Delete'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


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

CREATE PROCEDURE dbo.cs_Post_Delete
(
	@SectionID int,
	@SettingsID int,
	@PostID int,
	@ResetStatistics bit = 1,
	@DeletedBy INT,
	@Reason NVARCHAR(1024) = ''
)
AS
-- Permanently deletes one post at a time and eventually its thread.
-- If the post has some replies then subsequent calls are made till all the tree's nodes and leafs are removed.
-- Also if the post is level 1 then we can delete the thread itself without any other checkings.

SET Transaction Isolation Level Read UNCOMMITTED
BEGIN

DECLARE @PostLevel INT
DECLARE @ThreadID INT
DECLARE @HasReplies INT

-- Figure out if this is a thread starter post, who is its thread and if it has some replies
--------------------------------------------------------------------------------
SELECT 
	@PostLevel = PostLevel,
	@ThreadID = ThreadID	
FROM 
	cs_Posts 
WHERE 
	PostID = @PostID AND
	SectionID = @SectionID AND
	SettingsID = @SettingsID

SET @HasReplies = (SELECT COUNT(*) FROM cs_Posts WHERE ParentID = @PostID AND SectionID = @SectionID AND SettingsID = @SettingsID)
--------------------------------------------------------------------------------

-- Is a starter thread post?
IF (@PostLevel = 1)
BEGIN
	-- Easy job: delete the thread and its content with statistics update
	--------------------------------------------------------------------------------
	EXEC cs_Thread_Delete @SettingsID, @SectionID, @ThreadID, @ResetStatistics, @DeletedBy, ''

	RETURN 1
	--------------------------------------------------------------------------------
END
ELSE
BEGIN
	-- This is not a thread starter post, so we have to delete it carefully

	-- Delete parent post
	--------------------------------------------------------------------------------	
	DELETE FROM cs_PostAttachments WHERE PostID = @PostID AND SettingsID = @SettingsID
	
	DELETE FROM cs_PostEditNotes WHERE PostID = @PostID AND SettingsID = @SettingsID
	
	DELETE FROM cs_Posts_InCategories WHERE PostID = @PostID AND SettingsID = @SettingsID
		
	DELETE FROM cs_PostsArchive WHERE PostID = @PostID AND SettingsID = @SettingsID
	
	DELETE FROM cs_SearchBarrel WHERE SectionID = @SectionID  AND ThreadID = @ThreadID AND PostID = @PostID AND SettingsID = @SettingsID
	
	DELETE FROM cs_Posts WHERE PostID = @PostID AND ThreadID = @ThreadID AND SectionID = @SectionID AND SettingsID = @SettingsID
	
	IF (@Reason IS NULL OR @Reason = '')
		SET @Reason = 'Automatic generated reason: the post has been permanently deleted on request.'

	EXEC cs_system_ModerationAction_AuditEntry 4, @DeletedBy, @PostID, null, null, @SettingsID, @Reason	
	--------------------------------------------------------------------------------	

	-- Do we have some replies?
	--------------------------------------------------------------------------------	
	IF (@HasReplies > 0)
	BEGIN
		-- There should be some replies/children bound to this post.
		-- Get replying posts one by one through a local cursor and call its removal.

		DECLARE @CurrentPostID INT

		DECLARE Posts_Cursor CURSOR LOCAL FORWARD_ONLY FOR
		SELECT 
			PostID 
		FROM
			cs_Posts
		WHERE
			ParentID = @PostID AND
			ThreadID = @ThreadID AND
			SectionID = @SectionID AND
			SettingsID = @SettingsID

		OPEN Posts_Cursor
		
		FETCH NEXT FROM Posts_Cursor INTO @CurrentPostID

		WHILE (@@FETCH_STATUS = 0)
		BEGIN
			-- Recursively call with no statistics update
			EXEC cs_Post_Delete @SectionID, @SettingsID, @CurrentPostID, 0, @DeletedBy, @Reason

			FETCH NEXT FROM Posts_Cursor INTO @CurrentPostID
		END

		CLOSE Posts_Cursor

		DEALLOCATE Posts_Cursor		
	END
	--------------------------------------------------------------------------------	
	
	-- Update statistics?
	--------------------------------------------------------------------------------	
	IF (@ResetStatistics = 1)
	BEGIN
		EXEC cs_system_ResetThreadStatistics @ThreadID
		EXEC cs_system_ResetForumStatistics @SectionID
	END
	--------------------------------------------------------------------------------	

	RETURN 1
END

END
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


grant execute on [dbo].[cs_Post_Delete] to public
go
/***********************************************
* Sproc: cs_Post_Downloads_Add
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_Post_Downloads_Add'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

CREATE PROCEDURE [dbo].cs_Post_Downloads_Log
	@PostID int,
	@UserID int
AS

INSERT INTO cs_Posts_Downloads (UserID, PostID, DownloadDate) VALUES (@UserID, @PostID, getdate())

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].cs_Post_Downloads_Log to public
go
/***********************************************
* Sproc: cs_Post_GetAuthorID
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_Post_GetAuthorID'

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_Post_GetAuthorID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_Post_GetAuthorID]
GO
--------------------------------------------------------------------------------
--	cs_Post_GetAuthorID
--	Returns a post author's userID
--------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[cs_Post_GetAuthorID]
(
	@SettingsID	int,
	@PostID		int,
	@UserID		int	OUTPUT
)
AS
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


	-- lookup author for post
	SELECT	@UserID = P.UserID
	FROM	cs_Posts P
	WHERE	P.PostID = @PostID
		AND	P.SettingsID = @SettingsID


	RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT EXECUTE on [dbo].[cs_Post_GetAuthorID] to [public]

/***********************************************
* Sproc: cs_Post_GetPageIndex
* File Date: 4/14/2008 11:35:01 AM
***********************************************/
Print 'Creating...cs_Post_GetPageIndex'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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



CREATE   PROCEDURE dbo.cs_Post_GetPageIndex
(
	@PostID int,
	@PageSize int,
	@SortBy int,
	@SortOrder bit,
	@AllowUnapproved bit = 0,
	@SettingsID int
)
AS

DECLARE @ThreadID int
SELECT @ThreadID = ThreadID FROM cs_Posts WHERE PostID = @PostID AND SettingsID = @SettingsID

-- Create a temp table to store the select results
