2005_4_2.sql

解压即可使用

		jS.IsActive = 1
		AND jS.ApplicationType = 2
		AND (jT.SectionID = @SectionID OR -1 = @SectionID)
		AND jT.SettingsID = @SettingsID
		AND isnull(jP2.allowmask,jP.allowmask) & convert(bigint,0x0000000000000001) = convert(bigint,0x0000000000000001)
	ORDER BY
		jT.TotalViews DESC
end
else if @SortBy = 4
begin
	-- order by total replies
	INSERT INTO #PageIndex (ThreadID)
	SELECT
		jT.ThreadID
	FROM cs_Threads jT
	JOIN cs_Sections jS ON jS.SectionID = jT.SectionID
	JOIN cs_SiteSettings jSS ON jSS.SettingsID = jS.SettingsID
	JOIN cs_vw_EveryOne_Role jE ON jE.LoweredApplicationName = jSS.ApplicationName
	JOIN cs_ProductPermissions jP ON (jP.RoleID = jE.RoleId and jP.ApplicationType = jS.ApplicationType AND jP.SettingsID = @SettingsID)
	LEFT OUTER JOIN cs_SectionPermissions jP2 ON (jP2.RoleID = jE.RoleId and jP2.SectionID = jT.SectionID)
	WHERE
		jS.IsActive = 1
		AND jS.ApplicationType = 2
		AND (jT.SectionID = @SectionID OR -1 = @SectionID)
		AND jT.SettingsID = @SettingsID
		AND isnull(jP2.allowmask,jP.allowmask) & convert(bigint,0x0000000000000001) = convert(bigint,0x0000000000000001)
	ORDER BY
		jT.TotalReplies DESC
end
else
begin
	-- order by postdate
	INSERT INTO #PageIndex (ThreadID)
	SELECT
		jT.ThreadID
	FROM cs_Threads jT
	JOIN cs_Sections jS ON jS.SectionID = jT.SectionID
	JOIN cs_SiteSettings jSS ON jSS.SettingsID = jS.SettingsID
	JOIN cs_vw_EveryOne_Role jE ON jE.LoweredApplicationName = jSS.ApplicationName
	JOIN cs_ProductPermissions jP ON (jP.RoleID = jE.RoleId and jP.ApplicationType = jS.ApplicationType AND jP.SettingsID = @SettingsID)
	LEFT OUTER JOIN cs_SectionPermissions jP2 ON (jP2.RoleID = jE.RoleId and jP2.SectionID = jT.SectionID)
	WHERE
		jS.IsActive = 1
		AND jS.ApplicationType = 2
		AND (jT.SectionID = @SectionID OR -1 = @SectionID)
		AND jT.SettingsID = @SettingsID
		AND isnull(jP2.allowmask,jP.allowmask) & convert(bigint,0x0000000000000001) = convert(bigint,0x0000000000000001)
	ORDER BY
		jT.PostDate DESC
end


SELECT 
	jT.*,
	SortOrder = 0,
	jP.PostID,
	jP.Subject,
	jP.Body,
	jP.FormattedBody,
	UserName = jT.PostAuthor,
	jp.PropertyNames as PostPropertyNames, jp.PropertyValues as PostPropertyValues
FROM 
	#PageIndex jPI
	JOIN cs_Threads jT ON jPI.ThreadID = jT.ThreadID
	JOIN cs_Posts jP ON jPI.ThreadID = jP.ThreadID
WHERE 
	jP.PostLevel = 1 	-- PostLevel=1 should mean it's a top-level thread starter
	AND jp.SettingsID = @SettingsID AND jT.SettingsID = @SettingsID
ORDER BY
	jPI.IndexID	-- this is the ordering system we're using populated from the @sqlPopulate

DROP TABLE #PageIndex
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].cs_gallery_GetPortalPictures to public
go
/***********************************************
* SP: cs_gallery_Post_GetSortOrder
* File Date: 2/23/2005 12:22:13 AM
***********************************************/
Print 'Creating...cs_gallery_Post_GetSortOrder'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

CREATE PROCEDURE [dbo].cs_gallery_Post_GetSortOrder
	@PostID int,
	@sqlPopulate nvarchar(4000),
	@SettingsID int
AS

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

CREATE INDEX page_index ON #PageIndex(IndexID)

INSERT INTO #PageIndex (ThreadID)
EXEC (@sqlPopulate)

SELECT IndexID
	FROM #PageIndex I
	LEFT JOIN cs_Posts P ON P.ThreadID = I.ThreadID
	WHERE P.PostID = @PostID
		AND P.SettingsID = @SettingsID

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].cs_gallery_Post_GetSortOrder to public
go
/***********************************************
* SP: cs_gallery_Search
* File Date: 2/23/2005 12:22:15 AM
***********************************************/
Print 'Creating...cs_gallery_Search'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

CREATE    procedure [dbo].cs_gallery_Search (
	@SearchSQL nvarchar(4000),
	@PageIndex int = 0,
	@PageSize int = 25,
	@SettingsID int
)
AS
BEGIN

	DECLARE @StartTime datetime
	DECLARE @RowsToReturn int
	DECLARE @PageLowerBound int
	DECLARE @PageUpperBound int
	DECLARE @Count int

	-- Used to calculate cost of query
	SET @StartTime = GetDate()

	-- Set the rowcount
	SET @RowsToReturn = @PageSize * (@PageIndex + 1)
	--SET ROWCOUNT @RowsToReturn

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

	-- Create a temp table to store the results in
	CREATE TABLE #SearchResults
	(
		IndexID int IDENTITY (1, 1) NOT NULL,
		PostID int,
		SectionID int,
		Weight int,
		PostDate datetime
	)

	-- Fill the temp table
	INSERT INTO #SearchResults (PostID, SectionID, Weight, PostDate)
	exec (@SearchSQL)

	-- SELECT actual search results from this table
	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,
		U.*,
		T.ThreadDate,
		T.IsLocked,
		T.IsSticky, T.StickyDate, T.RatingSum, T.TotalRatings, 
		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(*) from cs_Moderators where UserID = P.UserID),
		HasRead = 0, -- not used,
		Convert(bit,0) as IsTracked -- Convert(bit, (Select Count(*) FROM cs_TrackedThreads where UserID = @UserID and ThreadID = P.ThreadID))
	FROM 
		cs_Posts P,
		cs_vw_Users_FullUser U,
		cs_Threads T,
		#SearchResults R
	WHERE
		P.PostID = R.PostID AND
		T.ThreadID = P.ThreadID AND
		U.cs_UserID = P.UserID AND
		R.IndexID > @PageLowerBound AND
		R.IndexID < @PageUpperBound AND
		P.SettingsID = @SettingsID and
		U.SettingsID = @SettingsID

	Select Count(*) FROM #SearchResults

	DROP Table #SearchResults

	SELECT Duration = GetDate() - @StartTime
END

















GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].cs_gallery_Search to public
go
/***********************************************
* SP: cs_gallery_Search_PostReindex
* File Date: 2/23/2005 12:22:15 AM
***********************************************/
Print 'Creating...cs_gallery_Search_PostReindex'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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


CREATE  PROCEDURE [dbo].cs_gallery_Search_PostReindex 
(
	@RowCount int,
	@SettingsID int
)
AS

SET ROWCOUNT @RowCount

SELECT
	T.*,
	SortOrder = 1,
	P.PostID,
	P.Subject,
	P.Body,
	P.FormattedBody,
	P.PostLevel,
	P.PropertyNames as PostPropertyNames, P.PropertyValues as PostPropertyValues,
	Username = P.PostAuthor,
	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)
FROM 
	cs_Posts P (nolock),
	cs_Threads T,
	cs_Sections F
WHERE
	F.SectionID = P.SectionID AND
	F.IsActive = 1 AND
	F.IsSearchable = 1 AND 
	P.IsApproved = 1 AND
	T.ThreadID = P.ThreadID and 
	P.IsIndexed = 0 and
	F.SettingsID = @SettingsID and
	F.ApplicationType = 2 AND
	P.PostLevel = 1
ORDER BY
	T.ThreadDate DESC
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].cs_gallery_Search_PostReindex  to public
go
/***********************************************
* SP: cs_gallery_Threads_GetAllThreads
* File Date: 2/23/2005 12:22:13 AM
***********************************************/
Print 'Creating...cs_gallery_Threads_GetAllThreads'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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



CREATE    procedure [dbo].cs_gallery_Threads_GetAllThreads
(
	@SectionID int,
	@SettingsID int
)
AS

SELECT 
	jT.*,
	SortOrder = 1,
	jP.PostID,
	jP.Subject,
	jP.Body,
	jP.FormattedBody,
	UserName = jT.PostAuthor,
	jp.PropertyNames as PostPropertyNames, jp.PropertyValues as PostPropertyValues
FROM 
	cs_Threads jT
	JOIN cs_Posts jP ON jP.ThreadID = jT.ThreadID
WHERE 
	jP.PostLevel = 1 	-- PostLevel=1 should mean it's a top-level thread starter
	AND jT.SectionID = @SectionID AND jP.SectionID = @SectionID
	AND jp.SettingsID = @SettingsID AND jT.SettingsID = @SettingsID

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].cs_gallery_Threads_GetAllThreads to public
go
/***********************************************
* SP: cs_gallery_Threads_GetThreadSet
* File Date: 2/23/2005 12:22:14 AM
***********************************************/
Print 'Creating...cs_gallery_Threads_GetThreadSet'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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



CREATE    procedure [dbo].cs_gallery_Threads_GetThreadSet
(
	@SectionID int,
	@PageIndex int, 
	@PageSize int,
	@sqlCount nvarchar(4000),
	@sqlPopulate nvarchar(4000),
	@UserID int,
	@SettingsID int,
	@ReturnRecordCount bit
)
AS
BEGIN

DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int
DECLARE @TotalThreads int

-- First set the rowcount
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,
	ThreadID int
)

CREATE INDEX page_index ON #PageIndex(IndexID)

INSERT INTO #PageIndex (ThreadID)
EXEC (@sqlPopulate)

SELECT 
	jT.*,
	SortOrder = jPI.IndexID,
	jP.PostID,
	jP.Subject,
	jP.Body,
	jP.FormattedBody,
	UserName = jT.PostAuthor,
	jp.PropertyNames as PostPropertyNames, jp.PropertyValues as PostPropertyValues
FROM 
	#PageIndex jPI
	JOIN cs_Threads jT ON jPI.ThreadID = jT.ThreadID
	JOIN cs_Posts jP ON jPI.ThreadID = jP.ThreadID
WHERE 
	jPI.IndexID > @PageLowerBound
	AND jPI.IndexID < @PageUpperBound
	AND jP.PostLevel = 1 	-- PostLevel=1 should mean it's a top-level thread starter
	AND jp.SettingsID = @SettingsID AND jT.SettingsID = @SettingsID
ORDER BY
	jPI.IndexID	-- this is the ordering system we're using populated from the @sqlPopulate

DROP TABLE #PageIndex

-- Update that the user has read this forum
IF @UserID > 0
	EXEC cs_Section_MarkRead @UserID, @SectionID, @SettingsID

-- Do we need to return a record count?
-- *************************************
IF (@ReturnRecordCount = 1)
	EXEC (@sqlCount)

-- Return the users that the message is to if this
-- is a private message
IF @SectionID = 0
	SELECT 
		U.*,
		P2.ThreadID 
	FROM
		cs_PrivateMessages P1, 
		cs_PrivateMessages P2,
		cs_vw_Users_FullUser U
		
	WHERE 
		P1.UserID = @UserID AND 
		P2.UserID <> @UserID AND 
		P2.UserID = U.cs_UserID AND
		P1.ThreadID = P2.ThreadID and U.SettingsID = @SettingsID and P1.SettingsID = @SettingsID and P2.SettingsID = @SettingsID

END


GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].cs_gallery_Threads_GetThreadSet to public
go
/***********************************************
* SP: cs_GetAnonymousUserID
* File Date: 2/23/2005 12:22:14 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


Create Proc [dbo].cs_GetAnonymousUserID
(
	@SettingsID int,
	@UserID int output
)
as

Select @UserID = cs_UserID FROM cs_vw_Users_FullUser where SettingsID = @SettingsID and IsAnonymous = 1

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 

GO

grant execute on [dbo].cs_GetAnonymousUserID to public
go
/***********************************************
* SP: cs_GetSectionSubscriptionType
* File Date: 2/23/2005 12:22:15 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
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
/***********************************************
* SP: cs_GetTotalPostCount
* File Date: 2/23/2005 12:22:13 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