--v7.0版
--获取Menu表中所有modeid=4的栏目（生成栏目更多页的静态页）
if exists (select * from dbo.sysobjects where id = object_id(N'GetMenuAndGate') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetMenuAndGate
GO
CREATE PROCEDURE GetMenuAndGate
@gradeId int=1,
@gate int
AS
	/* SET NOCOUNT ON */
	SELECT m.*,
	       g.name grade,
	       o.name mode
	FROM menu m,grade g,mode o
	where m.gradeId=g.pkId
	  and m.modeId=o.pkId
	  and m.bigClassId<>-1
	  and m.modeid=4

	order by ordering desc
	RETURN 


GO


--修正用户组抄送权限问题（静态页功能涉及的改动）
if exists (select * from dbo.sysobjects where id = object_id(N'GetNewsByBigClassIdAndPassed') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetNewsByBigClassIdAndPassed
GO
CREATE PROCEDURE GetNewsByBigClassIdAndPassed
@gradeid int=1,
@gateId int,
@bigClassId int=null,
@currentPage int=1,
@pageSize int=5,
@passed int=1
AS
BEGIN


	SET @currentPage = @currentPage - 1
	IF (@currentPage < 0)
		BEGIN
			SET @currentPage = 1
		END
	-- Set the page bounds
    DECLARE @PageLowerBound INT
    DECLARE @PageUpperBound INT
    DECLARE @TotalRecords   INT
    SET @PageLowerBound = @PageSize * @currentPage
    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound

    -- Create a temp table TO store the select results
    CREATE TABLE #PageIndexs
    (
        IndexId int IDENTITY (0, 1) NOT NULL,
        ID int
    )

    if(@currentPage=0) --优化第一页访问速度
	begin
		-- Insert into our temp table
		INSERT	INTO #PageIndexs (ID)
		SELECT	TOP (@pageSize) n.pkId
		FROM	[News] AS n 
				INNER JOIN [Users] AS u ON n.UsersID = u.PKID
				INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
		WHERE	(n.gate > @gateId OR n.gate < @gateId) and   
				bigClassId =		@bigClassId and 
				n.gradeId <=		@gradeId and 
				n.passed =			@passed and 
				(n.validDays > 0 OR n.validDays < 0)
		ORDER BY n.istop desc,n.Priority desc,n.updateTime desc,n.pkid desc
	end
	else
	begin
    	-- Insert into our temp table
    	INSERT	INTO #PageIndexs (ID)
		SELECT	n.pkId
		FROM	[News] AS n 
				INNER JOIN [Users] AS u ON n.UsersID = u.PKID
				INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
		WHERE	(n.gate > @gateId OR n.gate < @gateId) and   
				bigClassId =		@bigClassId and 
				n.gradeId <=		@gradeId and 
				n.passed =			@passed and 
				(n.validDays > 0 OR n.validDays < 0)
		ORDER BY n.istop desc,n.Priority desc,n.updateTime desc,n.pkid desc
	end

	SELECT n.pkId ,n.specialId ,n.gradeId ,n.gate ,n.departmentId ,n.listTitle ,n.intactTitle ,n.subheading
				,n.isRecommand ,n.isTop ,n.useListTitle ,n.showReviewLink ,n.Author ,n.AuthorMail ,n.CopyFrom ,n.copyFromLink
				,n.about ,n.pageType ,n.autoPageCharNum ,n.readPoint ,n.itemType ,n.descript ,n.usersId ,n.editor
				,n.click ,n.updateTime ,n.passed ,n.isReview ,n.isImageNews ,n.imageName ,n.width ,n.height
				,n.MenuId ,n.bigClassId ,n.updateIp ,n.useSubHeading ,n.isDuplicated ,n.duplicateFrom ,n.duplicatedPkId ,n.validDays
				,n.mediaAutostart ,n.AuditUserID,n.isStatic,n.staticUrl,n.staticTime, u.UserName, d.name as Department
	FROM	[News] AS n
			INNER JOIN [Users] AS u ON n.UsersID = u.PKID
			INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
			INNER JOIN [#PageIndexs] AS p ON n.PKID = p.ID
	WHERE	p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
	ORDER BY n.istop desc,n.Priority desc,n.updateTime desc,n.pkid desc

	DELETE #PageIndexs
	RETURN 
END


GO

if exists (select * from dbo.sysobjects where id = object_id(N'GetNewsByBeforePkId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetNewsByBeforePkId
GO
CREATE PROCEDURE GetNewsByBeforePkId
	@pkId int =null,
	@gradeId int=1,
	@gateId int 
AS
	SET NOCOUNT ON 	
	SELECT TOP 1 pkId,intactTitle,isStatic,staticUrl FROM news WHERE gradeId <= @gradeId AND gate <> @gateId AND pkId > @pkId AND passed=1
	AND bigClassId = (SELECT bigClassId FROM news where pkId = @pkId) ORDER BY pkId ASC


GO

if exists (select * from dbo.sysobjects where id = object_id(N'GetNewsByAfterPkId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetNewsByAfterPkId
GO
CREATE PROCEDURE GetNewsByAfterPkId
	@pkId int =null,
	@gradeId int=1,
	@gateId int 
AS
	SET NOCOUNT ON 	
	SELECT TOP 1 pkId,intactTitle,isStatic,staticUrl FROM news WHERE gradeId <= @gradeId AND gate <> @gateId AND pkId < @pkId AND passed=1
	AND bigClassId = (SELECT bigClassId FROM news where pkId = @pkId) ORDER BY pkId DESC

GO


if exists (select * from dbo.sysobjects where id = object_id(N'InsertNews') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure InsertNews
GO
CREATE PROCEDURE InsertNews
(
	@specialId int,
	@gradeId int,
	@gate int,
	@departmentId int,
	@listTitle varchar(1000),
	@intactTitle varchar(500),
	@subheading varchar(500),
	@isRecommand int,
	@isTop int,
	@useListTitle int,
	@showReviewLink int,
	@Author varchar(100),
	@AuthorMail varchar(100),
	@CopyFrom varchar(100),
	@copyFromLink varchar(100),
	@about varchar(100),
	@pageType int,
	@autoPageCharNum int,
	@readPoint int,
	@itemType int,
	@content text,
	@descript varchar(1000),
	@usersId int,
	@editor varchar(100),
	@click int,
	@updateTime datetime,
	@passed int,
	@isReview int,
	@isImageNews int,
	@imageName varchar(100),
	@MenuId int,
	@bigClassId int,
	@width int,
	@height int,
	@pkId int output,
	@updateIp varchar(100),
	@useSubHeading int,
	@isDuplicated int=0,
	@duplicateFrom varchar(100),
	@duplicatedPkId int,
	@validDays int,
	@AuditUserID	int,
        @Priority int,
	@isStatic	int,
        @staticUrl varchar(100),
	@staticTime datetime,
	@albumId	int
)
AS
	SET NOCOUNT OFF;

BEGIN TRY 	 
BEGIN TRANSACTION;

IF (@Priority IS NULL)
	BEGIN
	set @Priority = 0;
	END

IF (@AuditUserID IS NULL)
	BEGIN
		SET @AuditUserID = 0;
	END

IF (@validDays is null)
	set @validDays = -1	

IF (@isDuplicated is null)
	set @isDuplicated = 0

IF (@duplicatedPkId is null)
	set @duplicatedPkId = 0

INSERT INTO news(
	specialId, 
	gradeId, 
	gate, 
	departmentId, 
	listTitle, 
	intactTitle, 
	subheading, 
	isRecommand, 
	isTop, 
	useListTitle, 
	showReviewLink, 
	Author, 
	AuthorMail, 
	CopyFrom, 
	copyFromLink, 
	about, 
	pageType, 
	autoPageCharNum, 
	readPoint, 
	itemType, 
	--content, 
	descript,
	usersId,
	editor, 
	click, 
	updateTime, 
	passed, 
	isReview, 
	isImageNews, 
	imageName, 
	MenuId, 
	bigClassId, 
	width, 
	height,
	updateIp,
	useSubHeading, 
	isDuplicated, 
	duplicateFrom, 
	duplicatedPkId, 
	validDays,
	AuditUserID,
        Priority,
	isStatic,
        staticUrl,
	staticTime,
	albumId) 
VALUES (
	@specialId, 
	@gradeId, 
	@gate, 
	@departmentId, 
	@listTitle, 
	@intactTitle, 
	@subheading, 
	@isRecommand, 
	@isTop, 
	@useListTitle, 
	@showReviewLink, 
	@Author, 
	@AuthorMail, 
	@CopyFrom, 
	@copyFromLink, 
	@about, 
	@pageType, 
	@autoPageCharNum, 
	@readPoint, 
	@itemType, 
	--@content, 
	@descript, 
	@usersId, 
	@editor, 
	@click, 
	@updateTime, 
	@passed, 
	@isReview, 
	@isImageNews, 
	@imageName, 
	@MenuId, 
	@bigClassId, 
	@width, 
	@height,
	@updateIp,
	@useSubHeading, 
	@isDuplicated, 
	@duplicateFrom, 
	@duplicatedPkId, 
	@validDays,
	@AuditUserID,
        @Priority,
	@isStatic,
        @staticUrl ,
	@staticTime,
	@albumId)

SET @pkId = @@IDENTITY;

INSERT INTO newsContent (newsId,content) values (@pkId,@content); --shijian201611 for 7.0
--UPDATE news SET Priority=@pkid  WHERE pkid=@pkid
COMMIT TRANSACTION;
END TRY 
BEGIN CATCH 
	ROLLBACK TRANSACTION ;
	SET @pkId = 0;
END CATCH 
GO



if exists (select * from dbo.sysobjects where id = object_id(N'UpdateNews') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure UpdateNews
GO
CREATE PROCEDURE UpdateNews
(
	@specialId int,
	@gradeId int,
	@gate int,
	@departmentId int,
	@listTitle varchar(1000),
	@intactTitle varchar(500),
	@subheading varchar(500),
	@isRecommand int,
	@isTop int,
	@useListTitle int,
	@showReviewLink int,
	@Author varchar(100),
	@AuthorMail varchar(100),
	@CopyFrom varchar(100),
	@copyFromLink varchar(100),
	@about varchar(100),
	@pageType int,
	@autoPageCharNum int,
	@readPoint int,
	@itemType int,
	@content text,
	@descript varchar(1000),
	@usersId int,
	@editor varchar(100),
	@click int,
	@updateTime datetime,
	@passed int,
	@isReview int,
	@isImageNews int,
	@imageName varchar(100),
	@MenuId int,
	@bigClassId int,
	@width int,
	@height int,
	@pkId int,
	@updateIp  varchar(100),
	@useSubHeading int,
	@isDuplicated int=0,
	@duplicateFrom varchar(100),
	@duplicatedPkId	int,
	@validDays int,
	@AuditUserID int,
        @Priority int,
	@isStatic	int,
        @staticUrl varchar(100),
	@staticTime datetime,
	@albumId	int
)
AS
	SET NOCOUNT OFF;
IF	(@AuditUserID IS NULL)
	BEGIN
		SET @AuditUserID = 0;
	END

IF (@validDays is null)
	set @validDays = -1	;

BEGIN TRY 	 
BEGIN TRANSACTION;
	UPDATE news SET useSubHeading=@useSubHeading,updateIp=@updateIp,specialId = @specialId, gradeId = @gradeId, gate = @gate, departmentId = @departmentId, listTitle = @listTitle, intactTitle = @intactTitle, subheading = @subheading, isRecommand = @isRecommand, isTop = @isTop, useListTitle = @useListTitle, showReviewLink = @showReviewLink, Author = @Author, AuthorMail = @AuthorMail, CopyFrom = @CopyFrom, copyFromLink = @copyFromLink, about = @about, pageType = @pageType, autoPageCharNum = @autoPageCharNum, readPoint = @readPoint, itemType = @itemType,  descript = @descript, usersId = @usersId, editor = @editor, click = @click, updateTime = @updateTime, passed = @passed, isReview = @isReview, isImageNews = @isImageNews, imageName = @imageName, MenuId = @MenuId, bigClassId = @bigClassId, width = @width, height = @height, isDuplicated = @isDuplicated, duplicateFrom = @duplicateFrom, duplicatedPkId = @duplicatedPkId, validDays = @validDays, AuditUserID = @AuditUserID ,Priority=@Priority,isStatic=@isStatic, staticUrl=@staticUrl ,staticTime=@staticTime, albumId=@albumId WHERE pkId = @pkId;
	UPDATE newsContent set content = @content WHERE newsId = @pkId;
	--UPDATE news SET passed = @passed WHERE duplicatedPkId=@pkId AND duplicateFrom='news'			-- 更新news时，同时更新news抄送的信息
	--UPDATE board SET passed = @passed WHERE duplicatedPkId=@pkId AND duplicateFrom = 'news'		-- 更新news时，同时更新board抄送的信息
COMMIT TRANSACTION;
END TRY 
BEGIN CATCH 
	ROLLBACK TRANSACTION ;
END CATCH

GO

if exists (select * from dbo.sysobjects where id = object_id(N'GetNewsByBigClassIdAndisImageNewsAndPassed') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetNewsByBigClassIdAndisImageNewsAndPassed
GO
CREATE PROCEDURE GetNewsByBigClassIdAndisImageNewsAndPassed
@bigClassId int=null,
@currentPage int=1,
@pageSize int=5,
@gradeid int=1,
@gateId int,
@isRandom int
AS
BEGIN

	SET @currentPage = @currentPage - 1
	IF (@currentPage < 0)
		BEGIN
			SET @currentPage = 1
		END
	-- Set the page bounds
    DECLARE @PageLowerBound INT
    DECLARE @PageUpperBound INT
    DECLARE @TotalRecords   INT
    SET @PageLowerBound = @PageSize * @currentPage
    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound

    -- Create a temp table TO store the select results
    CREATE TABLE #PageIndexs
    (
        IndexId int IDENTITY (0, 1) NOT NULL,
        ID int
    )

	CREATE TABLE #PageIndexs1
    (
        IndexId int IDENTITY (0, 1) NOT NULL,
        ID int
    )

	IF (@isRandom = 0)
		BEGIN
			--不随机
			-- Insert into our temp table
			INSERT	INTO #PageIndexs (ID)
			SELECT	n.pkId
			FROM	[News] AS n 
					INNER JOIN [Users] AS u ON n.UsersID = u.PKID
					INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
					INNER JOIN [grade] AS g ON n.gradeId = g.pkId
					INNER JOIN [special] AS s ON n.specialId = s.pkId
			WHERE	(n.gate > @gateId OR n.gate < @gateId) and   
					bigClassId =		@bigClassId and 
					n.gradeId <=		@gradeId and 
					n.passed =	1 and 
					(isImageNews=1 OR isImageNews=2) and 
					(n.validDays > 0 OR n.validDays < 0)
			ORDER BY n.istop DESC,n.priority desc,n.updateTime desc, n.pkId DESC

			SELECT  n.pkId ,n.specialId ,n.gradeId ,n.gate ,n.departmentId ,n.listTitle ,n.intactTitle ,n.subheading
				,n.isRecommand ,n.isTop ,n.useListTitle ,n.showReviewLink ,n.Author ,n.AuthorMail ,n.CopyFrom ,n.copyFromLink
				,n.about ,n.pageType ,n.autoPageCharNum ,n.readPoint ,n.itemType ,n.descript ,n.usersId ,n.editor
				,n.click ,n.updateTime ,n.passed ,n.isReview ,n.isImageNews ,n.imageName ,n.width ,n.height
				,n.MenuId ,n.bigClassId ,n.updateIp ,n.useSubHeading ,n.isDuplicated ,n.duplicateFrom ,n.duplicatedPkId ,n.validDays
				,n.mediaAutostart ,n.AuditUserID,n.isStatic,n.staticUrl,n.staticTime, u.UserName, d.name as Department,g.name as grade,s.name as special
			FROM	[News] AS n
					INNER JOIN [Users] AS u ON n.UsersID = u.PKID
					INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
					INNER JOIN [grade] AS g ON n.gradeId = g.pkId
					INNER JOIN [special] AS s ON n.specialId = s.pkId
					INNER JOIN [#PageIndexs] AS p ON n.PKID = p.ID
			WHERE	p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
			ORDER BY n.istop DESC,n.priority desc,n.updateTime desc, n.pkId DESC

		END
	ELSE IF(@isRandom = -1)
		BEGIN
			
			--全表随机
			-- Insert into our temp table
			INSERT	INTO #PageIndexs (ID)
			SELECT	n.pkId
			FROM	[News] AS n 
					INNER JOIN [Users] AS u ON n.UsersID = u.PKID
					INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
					INNER JOIN [grade] AS g ON n.gradeId = g.pkId
					INNER JOIN [special] AS s ON n.specialId = s.pkId
			WHERE	(n.gate > @gateId OR n.gate < @gateId) and   
					bigClassId =		@bigClassId and 
					n.gradeId <=		@gradeId and 
					n.passed =	1 and 
					(isImageNews=1 OR isImageNews=2) and 
					(n.validDays > 0 OR n.validDays < 0)
			ORDER BY newid()
			

			SELECT  n.pkId ,n.specialId ,n.gradeId ,n.gate ,n.departmentId ,n.listTitle ,n.intactTitle ,n.subheading
				,n.isRecommand ,n.isTop ,n.useListTitle ,n.showReviewLink ,n.Author ,n.AuthorMail ,n.CopyFrom ,n.copyFromLink
				,n.about ,n.pageType ,n.autoPageCharNum ,n.readPoint ,n.itemType ,n.descript ,n.usersId ,n.editor
				,n.click ,n.updateTime ,n.passed ,n.isReview ,n.isImageNews ,n.imageName ,n.width ,n.height
				,n.MenuId ,n.bigClassId ,n.updateIp ,n.useSubHeading ,n.isDuplicated ,n.duplicateFrom ,n.duplicatedPkId ,n.validDays
				,n.mediaAutostart ,n.AuditUserID,n.isStatic,n.staticUrl,n.staticTime, u.UserName, d.name as Department,g.name as grade,s.name as special
				FROM	[News] AS n
						INNER JOIN [Users] AS u ON n.UsersID = u.PKID
						INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
						INNER JOIN [grade] AS g ON n.gradeId = g.pkId
						INNER JOIN [special] AS s ON n.specialId = s.pkId
						INNER JOIN [#PageIndexs] AS p ON n.PKID = p.ID
				WHERE	p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
				ORDER BY newid()
		END
	ELSE
		BEGIN

			--范围随机
			-- Insert into our temp table
			INSERT	INTO #PageIndexs (ID)
			SELECT	TOP (@isRandom)  n.pkId
			FROM	[News] AS n 
					INNER JOIN [Users] AS u ON n.UsersID = u.PKID
					INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
					INNER JOIN [grade] AS g ON n.gradeId = g.pkId
					INNER JOIN [special] AS s ON n.specialId = s.pkId
			WHERE	(n.gate > @gateId OR n.gate < @gateId) and   
					bigClassId =		@bigClassId and 
					n.gradeId <=		@gradeId and 
					n.passed =	1 and 
					(isImageNews=1 OR isImageNews=2) and 
					(n.validDays > 0 OR n.validDays < 0)
			ORDER BY n.istop DESC,n.priority desc,n.updateTime desc, n.pkId DESC

			INSERT	INTO #PageIndexs1 (ID)
			SELECT	ID 
			FROM [#PageIndexs] ORDER BY newid()
			
			SELECT n.pkId ,n.specialId, n.gradeId ,n.gate ,n.departmentId ,n.listTitle ,n.intactTitle ,n.subheading
				,n.isRecommand	,n.isTop, n.useListTitle ,n.showReviewLink, n.Author ,n.AuthorMail ,n.CopyFrom ,n.copyFromLink
				,n.about ,n.pageType ,n.autoPageCharNum	,n.readPoint ,n.itemType ,n.descript ,n.usersId	,n.editor
				,n.click ,n.updateTime ,n.passed ,n.isReview ,n.isImageNews	,n.imageName ,n.width ,n.height
				,n.MenuId ,n.bigClassId	,n.updateIp	,n.useSubHeading,n.isDuplicated	,n.duplicateFrom ,n.duplicatedPkId ,n.validDays
				,n.mediaAutostart ,n.AuditUserID,n.isStatic,n.staticUrl,n.staticTime, u.UserName, d.name as Department,g.name as grade,s.name as special
				FROM	[News] AS n
						INNER JOIN [Users] AS u ON n.UsersID = u.PKID
						INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
						INNER JOIN [grade] AS g ON n.gradeId = g.pkId
						INNER JOIN [special] AS s ON n.specialId = s.pkId
						INNER JOIN [#PageIndexs1] AS p ON n.PKID = p.ID
				WHERE	p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
				--ORDER BY newid()
			
		END
	DELETE #PageIndexs
	DELETE #PageIndexs1
	RETURN 
END

GO


if exists (select * from dbo.sysobjects where id = object_id(N'GetNewsByBigClassIdAndUsersId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetNewsByBigClassIdAndUsersId
GO
CREATE PROCEDURE GetNewsByBigClassIdAndUsersId
@bigClassId int=null,
@currentPage int=1,
@pageSize int=5,
@gradeid int=1,
@usersId int,
@gateId int,
@searchStr varchar(100) = '',
@recordCount int output
AS
BEGIN

	SET @currentPage = @currentPage - 1
	IF (@currentPage < 0)
		BEGIN
			SET @currentPage = 1
		END
	-- Set the page bounds
    DECLARE @PageLowerBound INT
    DECLARE @PageUpperBound INT
    DECLARE @TotalRecords   INT
    SET @PageLowerBound = @PageSize * @currentPage
    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
	
	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(4000)
	
	SET @vSQL = N'SELECT @count = Count(n.pkId) from 
			[News] AS n 
			INNER JOIN [Users] AS u ON n.UsersID = u.PKID
			INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
			inner join [grade] as g on n.gradeId=g.pkId
			WHERE (n.gate > ' + STR(@gateId) + ' OR n.gate < ' + STR(@gateId) + ') and   
			n.bigClassId = ' + STR(@bigClassId) +' and 
			(n.passed = 0 or n.passed = 1 or n.passed = 2) and 
			n.usersId = '+ STR(@usersId) +' and 
			n.gradeId <= ' + STR(@gradeId) + ' ' +
			@searchStr 

	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum
	
	DECLARE @sql nvarchar(4000)
    
    SET @sql = '
	-- Create a temp table TO store the select results
	CREATE TABLE #PageIndexs
    (
        IndexId int IDENTITY (0, 1) NOT NULL,
        ID int
    );

    -- Insert into our temp table
    INSERT	INTO #PageIndexs (ID)
	SELECT	n.pkId
	FROM	[News] AS n 
			INNER JOIN [Users] AS u ON n.UsersID = u.PKID
			INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
			INNER JOIN [grade] AS g ON n.gradeId = g.pkId
			
	WHERE	(n.gate > ' + STR(@gateId) + ' OR n.gate < ' + STR(@gateId) + ') and   
			n.bigClassId = ' + STR(@bigClassId) +' and 
			(n.passed = 0 or n.passed = 1 or n.passed = 2) and 
			n.usersId = '+ STR(@usersId) +' and 
			n.gradeId <= ' + STR(@gradeId) + ' ' +
			@searchStr + ' ' +
	'ORDER BY n.istop DESC,n.Priority desc,n.updateTime desc, n.pkId DESC;

	SELECT  n.pkId
	,n.specialId
	,n.gradeId
	,n.gate
	,n.departmentId
	,n.listTitle
	,n.intactTitle
	,n.subheading
	,n.isRecommand
	,n.isTop
	,n.useListTitle
	,n.showReviewLink
	,n.Author
	,n.AuthorMail
	,n.CopyFrom
	,n.copyFromLink
	,n.about
	,n.pageType
	,n.autoPageCharNum
	,n.readPoint
	,n.itemType
	,n.descript
	,n.usersId
	,n.editor
	,n.click
	,n.updateTime
	,n.passed
	,n.isReview
	,n.isImageNews
	,n.imageName
	,n.width
	,n.height
	,n.MenuId
	,n.bigClassId
	,n.updateIp
	,n.useSubHeading
	,n.isDuplicated
	,n.duplicateFrom
	,n.duplicatedPkId
	,n.validDays
	,n.mediaAutostart
	,n.AuditUserID
	,n.Priority,n.isStatic,n.staticUrl,n.staticTime, u.UserName,u.fullName, d.name as Department,g.name as grade
	FROM	[News] AS n
			INNER JOIN [Users] AS u ON n.UsersID = u.PKID
			INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
			INNER JOIN [grade] AS g ON n.gradeId = g.pkId
			INNER JOIN [#PageIndexs] AS p ON n.PKID = p.ID
	WHERE	p.IndexId >= ' + STR(@PageLowerBound) + ' AND p.IndexId <= ' + STR(@PageUpperBound) + ' 
	and (n.passed = 0 or n.passed = 1 or n.passed = 2) 
	ORDER BY n.istop DESC,n.Priority desc,n.updateTime desc, n.pkId DESC

	DELETE #PageIndexs'

	EXEC(@sql)
	
	RETURN 
END


GO

if exists (select * from dbo.sysobjects where id = object_id(N'GetNewsByBigClassId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetNewsByBigClassId
GO
CREATE PROCEDURE GetNewsByBigClassId
@bigClassId int=null,
@currentPage int=1,
@pageSize int=5,
@gradeid int=1,
@gateId int,
@searchStr varchar(100) = '',
@recordCount int output
AS
BEGIN

	SET @currentPage = @currentPage - 1
	IF (@currentPage < 0)
		BEGIN
			SET @currentPage = 1
		END
	-- Set the page bounds
    DECLARE @PageLowerBound INT
    DECLARE @PageUpperBound INT
    DECLARE @TotalRecords   INT
    SET @PageLowerBound = @PageSize * @currentPage
    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
	
	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(4000)
	
	SET @vSQL = N'SELECT @count = Count(n.pkId) from 
			[News] AS n 
			INNER JOIN [Users] AS u ON n.UsersID = u.PKID
			INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
			inner join [grade] as g on n.gradeId=g.pkId
			WHERE (n.gate > ' + STR(@gateId) + ' OR n.gate < ' + STR(@gateId) + ') and   
			n.bigClassId = ' + STR(@bigClassId) +' and 
			(n.passed = 0 or n.passed = 1 or n.passed = 2) and 
			n.gradeId <= ' + STR(@gradeId) + ' ' +
			@searchStr 

	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum
	
	DECLARE @sql nvarchar(4000)
 
    SET @sql = '
	
	CREATE TABLE #PageIndexs
    (
        IndexId int IDENTITY (0, 1) NOT NULL,
        ID int
    );

    
    INSERT	INTO #PageIndexs (ID)
	SELECT	n.pkId
	FROM	[News] AS n 
			INNER JOIN [Users] AS u ON n.UsersID = u.PKID
			INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
			inner join [grade] as g on n.gradeId=g.pkId
	WHERE	(n.gate > ' + STR(@gateId) + ' OR n.gate < ' + STR(@gateId) + ') and   
			n.bigClassId = ' + STR(@bigClassId) +' and 
			(n.passed = 0 or n.passed = 1 or n.passed = 2) and 
			n.gradeId <= ' + STR(@gradeId) + ' ' +
			@searchStr + ' ' +
	'ORDER BY n.istop desc,n.Priority desc,n.updateTime desc,n.pkid desc;

	SELECT  n.pkId
	,n.specialId
	,n.gradeId
	,n.gate
	,n.departmentId
	,n.listTitle
	,n.intactTitle
	,n.subheading
	,n.isRecommand
	,n.isTop
	,n.useListTitle
	,n.showReviewLink
	,n.Author
	,n.AuthorMail
	,n.CopyFrom
	,n.copyFromLink
	,n.about
	,n.pageType
	,n.autoPageCharNum
	,n.readPoint
	,n.itemType
	,n.descript
	,n.usersId
	,n.editor
	,n.click
	,n.updateTime
	,n.passed
	,n.isReview
	,n.isImageNews
	,n.imageName
	,n.width
	,n.height
	,n.MenuId
	,n.bigClassId
	,n.updateIp
	,n.useSubHeading
	,n.isDuplicated
	,n.duplicateFrom
	,n.duplicatedPkId
	,n.validDays
	,n.mediaAutostart
	,n.AuditUserID
	,n.Priority,n.isStatic,n.staticUrl,n.staticTime, u.UserName,u.fullName, d.name as Department,g.name as grade
	FROM	[News] AS n
			INNER JOIN [Users] AS u ON n.UsersID = u.PKID
			INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
			INNER JOIN [grade] as g on n.gradeId=g.pkId
			INNER JOIN [#PageIndexs] AS p ON n.PKID = p.ID
	WHERE	p.IndexId >= ' + STR(@PageLowerBound) + ' AND p.IndexId <= ' + STR(@PageUpperBound) + ' 
	and (n.passed = 0 or n.passed = 1 or n.passed = 2) 
	ORDER BY n.istop desc,n.Priority desc,n.updateTime desc,n.pkid desc

	DELETE #PageIndexs'

	EXEC(@sql)
	
	RETURN 
END

GO

if exists (select * from dbo.sysobjects where id = object_id(N'GetSearchAllNewsByUserId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetSearchAllNewsByUserId
GO
CREATE PROCEDURE GetSearchAllNewsByUserId
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeId int,
	@userId int,
	@gateId int,
	@manageAll varchar(8000),
	@searchStr varchar(200) = '',	 
	@recordCount int output
AS	
	--转换成索引
	SELECT @currentPage = @currentPage - 1
	
	--初始化查询条件(不带where)
	DECLARE @str nvarchar(4000)
	--设置查询表名称
	DECLARE @tableName nvarchar(100)
--判断当前用户是否属于用户组
	Declare @groupid int 
select @groupid = usersGroupId from users where pkid=@userId
if(@groupid=1)
begin
	--用临时表先读取授权的栏目和MenuName，然后在读取授权的新闻，
    --这样设计不会对news表中的menuId产生依赖，避免因栏目移动产生错误
	select b.pkId bigclassId, m.name menuName,max(m.pkId) sourceMenuId into #test from bigClass b, menu m, moduleAssign a where m.bigClassId=b.pkId and m.pkId=a.menuId and usersId=@userId group by b.pkId,m.name
	SET @str = ' 1=1 '
	SET @str = @str + ' and n.bigclassId = z.bigclassId'
	SET @str = @str + ' and z.bigclassId = b.pkid'
	SET @str = @str + ' and b.templateId = t.pkId'
	SET @str = @str + ' and (n.passed=0 or n.passed=1 or n.passed=2)'
	SET @str = @str + ' and n.gradeId<='+STR(@gradeId) 
	SET @str = @str + ' and n.usersId='+STR(@userId) 
	--SET @str = @str + ' and n.gate<>'+STR(@gateId)
	SET @str = @str + ' and n.usersId=u.pkId and n.gradeId=g.pkId and n.departmentId=d.pkId '
	--SET @str = @str + ' and (n.duplicatedPkId=0 or n.duplicatedPkId is null)'
	IF(@searchStr <> '')
		SET @str = @str + @searchStr
	
	SET @tableName = '#test z, news n,users u,department d,grade g, template t, bigClass b '

end
else
begin

	--用临时表先读取授权的栏目和MenuName，然后在读取授权的新闻，
    --这样设计不会对news表中的menuId产生依赖，避免因栏目移动产生错误
	select b.pkId bigclassId, m.name menuName,max(m.pkId) sourceMenuId into #test1 from bigClass b, menu m, groupAssign a where m.bigClassId=b.pkId and m.pkId=a.menuId and groupId=@groupid group by b.pkId,m.name
	SET @str = ' 1=1 '
	SET @str = @str + ' and n.bigclassId = z.bigclassId'
	SET @str = @str + ' and z.bigclassId = b.pkid'
	SET @str = @str + ' and b.templateId = t.pkId'
	SET @str = @str + ' and (n.passed=0 or n.passed=1 or n.passed=2)'
	SET @str = @str + ' and n.gradeId<='+STR(@gradeId)
	SET @str = @str + ' and n.usersId='+STR(@userId) 
	--SET @str = @str + ' and n.gate<>'+STR(@gateId)
	SET @str = @str + ' and n.usersId=u.pkId and n.gradeId=g.pkId and n.departmentId=d.pkId '
	--SET @str = @str + ' and (n.duplicatedPkId=0 or n.duplicatedPkId is null)'
	IF(@searchStr <> '')
		SET @str = @str + @searchStr
	
	SET @tableName = '#test1 z, news n,users u,department d,grade g, template t, bigClass b '

end

	--设置查询排序（不带order by）
	DECLARE @orderTypeA nvarchar(100)
	DECLARE @orderTypeD nvarchar(100)
	SET @orderTypeA = 'ntime ASC, sourceMenuId ASC, newsId ASC'
	SET @orderTypeD = 'ntime DESC, sourceMenuId DESC, newsId DESC'
	
	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(4000)

	SET @vSQL = N'SELECT @count = Count(n.pkId) from '+Convert(nvarchar(100),@tableName)+' where ' + Convert(nvarchar(4000),@str)
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum

	--计算页数
	SET @pageCount = (@recordCount +@pageSize-1)/@pageSize

	--构造查询语句
	DECLARE @sqlStr varchar(4000),@size int

	--判断分页大小，查询最后一页用
	SET @size = @recordCount%@pageSize	
	IF(@size=0)
		SET @size = @pageSize	
	
	--查询第一页
	IF (@currentPage = 0) OR (@currentPage > @pageCount-1)	
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' n.pkId
	,n.specialId
	,n.gradeId
	,n.gate
	,n.departmentId
	,n.listTitle
	,n.intactTitle
	,n.subheading
	,n.isRecommand
	,n.isTop
	,n.useListTitle
	,n.showReviewLink
	,n.Author
	,n.AuthorMail
	,n.CopyFrom
	,n.copyFromLink
	,n.about
	,n.pageType
	,n.autoPageCharNum
	,n.readPoint
	,n.itemType
	,n.descript
	,n.usersId
	,n.editor
	,n.click
	,n.updateTime
	,n.updateTime as ntime 
	,n.passed
	,n.isReview
	,n.isImageNews
	,n.imageName
	,n.width
	,n.height
	,n.MenuId
	,n.bigClassId
	,n.updateIp
	,n.useSubHeading
	,n.isDuplicated
	,n.duplicateFrom
	,n.duplicatedPkId
	,n.validDays
	,n.mediaAutostart
	,n.AuditUserID,n.isStatic,n.staticUrl,n.staticTime,u.userName,u.fullName,d.name "department",g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId , t.code FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(4000),@str)+' and b.pkId = n.bigClassId and b.templateId = t.pkId ORDER BY '+Convert(nvarchar(100),@orderTypeD)	
	--查询最后一页
	ELSE IF @currentPage = @pageCount -1			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' n.pkId
	,n.specialId
	,n.gradeId
	,n.gate
	,n.departmentId
	,n.listTitle
	,n.intactTitle
	,n.subheading
	,n.isRecommand
	,n.isTop
	,n.useListTitle
	,n.showReviewLink
	,n.Author
	,n.AuthorMail
	,n.CopyFrom
	,n.copyFromLink
	,n.about
	,n.pageType
	,n.autoPageCharNum
	,n.readPoint
	,n.itemType
	,n.descript
	,n.usersId
	,n.editor
	,n.click
	,n.updateTime
	,n.updateTime as ntime 
	,n.passed
	,n.isReview
	,n.isImageNews
	,n.imageName
	,n.width
	,n.height
	,n.MenuId
	,n.bigClassId
	,n.updateIp
	,n.useSubHeading
	,n.isDuplicated
	,n.duplicateFrom
	,n.duplicatedPkId
	,n.validDays
	,n.mediaAutostart
	,n.AuditUserID,n.isStatic,n.staticUrl,n.staticTime,u.userName,u.fullName,d.name "department" ,g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId , t.code FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(4000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable ORDER BY '+Convert(nvarchar(100),@orderTypeD)	
	--查询中间页后面的页
	ELSE IF @currentPage > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @currentPage ) + ' n.pkId
	,n.specialId
	,n.gradeId
	,n.gate
	,n.departmentId
	,n.listTitle
	,n.intactTitle
	,n.subheading
	,n.isRecommand
	,n.isTop
	,n.useListTitle
	,n.showReviewLink
	,n.Author
	,n.AuthorMail
	,n.CopyFrom
	,n.copyFromLink
	,n.about
	,n.pageType
	,n.autoPageCharNum
	,n.readPoint
	,n.itemType
	,n.descript
	,n.usersId
	,n.editor
	,n.click
	,n.updateTime
	,n.updateTime as ntime 
	,n.passed
	,n.isReview
	,n.isImageNews
	,n.imageName
	,n.width
	,n.height
	,n.MenuId
	,n.bigClassId
	,n.updateIp
	,n.useSubHeading
	,n.isDuplicated
	,n.duplicateFrom
	,n.duplicatedPkId
	,n.validDays
	,n.mediaAutostart
	,n.AuditUserID,n.isStatic,n.staticUrl,n.staticTime,u.userName,u.fullName,d.name "department" ,g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId, t.code FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(4000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable ORDER BY '+Convert(nvarchar(100),@orderTypeD)
	--查询中间页前面的页
 	ELSE BEGIN							
		SET @currentPage=@currentPage+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @currentPage ) + ' n.pkId
	,n.specialId
	,n.gradeId
	,n.gate
	,n.departmentId
	,n.listTitle
	,n.intactTitle
	,n.subheading
	,n.isRecommand
	,n.isTop
	,n.useListTitle
	,n.showReviewLink
	,n.Author
	,n.AuthorMail
	,n.CopyFrom
	,n.copyFromLink
	,n.about
	,n.pageType
	,n.autoPageCharNum
	,n.readPoint
	,n.itemType
	,n.descript
	,n.usersId
	,n.editor
	,n.click
	,n.updateTime
	,n.updateTime as ntime 
	,n.passed
	,n.isReview
	,n.isImageNews
	,n.imageName
	,n.width
	,n.height
	,n.MenuId
	,n.bigClassId
	,n.updateIp
	,n.useSubHeading
	,n.isDuplicated
	,n.duplicateFrom
	,n.duplicatedPkId
	,n.validDays
	,n.mediaAutostart
	,n.AuditUserID,n.isStatic,n.staticUrl,n.staticTime,u.userName,u.fullName,d.name "department" ,g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId, t.code FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(4000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeD)+') TempTable1 '
					+ ' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable2 ORDER BY '+Convert(nvarchar(100),@orderTypeD)
	END

	--执行查询
	EXEC(@sqlStr)
RETURN



GO

if exists (select * from dbo.sysobjects where id = object_id(N'GetSearchAllNews') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetSearchAllNews
GO
CREATE PROCEDURE GetSearchAllNews
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeId int,
	@userId int,
	@gateId int,
	@manageAll varchar(8000),
	@searchStr varchar(200) = '',	 
	@recordCount int output
AS	
	--转换成索引
	SELECT @currentPage = @currentPage - 1
	
	--初始化查询条件(不带where)
	DECLARE @str nvarchar(4000)
	--设置查询表名称
	DECLARE @tableName nvarchar(100)
--判断当前用户是否属于用户组
	Declare @groupid int 
select @groupid = usersGroupId from users where pkid=@userId
if(@groupid=1)
begin
	--用临时表先读取授权的栏目和MenuName，然后在读取授权的新闻，
    --这样设计不会对news表中的menuId产生依赖，避免因栏目移动产生错误
	select b.pkId bigclassId, m.name menuName,max(m.pkId) sourceMenuId into #test from bigClass b, menu m, moduleAssign a where m.bigClassId=b.pkId and m.pkId=a.menuId and usersId=@userId group by b.pkId,m.name
	SET @str = ' 1=1 '
	SET @str = @str + ' and n.bigclassId = z.bigclassId'
	SET @str = @str + ' and z.bigclassId = b.pkid'
	SET @str = @str + ' and b.templateId = t.pkId'
	--SET @str = @str + ' and (n.passed=0 or n.passed=1 or n.passed=2)'
	--SET @str = @str + ' and n.passed=1'
	SET @str = @str + ' and (n.passed=1 or ((n.passed=0 or n.passed=2) and n.usersId='+STR(@userId) +'))'
	SET @str = @str + ' and n.gradeId<='+STR(@gradeId) 
	--SET @str = @str + ' and (n.usersId='+STR(@userId)
	--SET @str = @str + ' and n.gate<>'+STR(@gateId)
	SET @str = @str + ' and n.usersId=u.pkId and n.gradeId=g.pkId and n.departmentId=d.pkId '
	--SET @str = @str + ' and (n.duplicatedPkId=0 or n.duplicatedPkId is null)'
	IF(@searchStr <> '')
		SET @str = @str + @searchStr
	
	SET @tableName = '#test z, news n,users u,department d,grade g, template t, bigClass b '

end
else
begin

	--用临时表先读取授权的栏目和MenuName，然后在读取授权的新闻，
    --这样设计不会对news表中的menuId产生依赖，避免因栏目移动产生错误
	select b.pkId bigclassId, m.name menuName,max(m.pkId) sourceMenuId into #test1 from bigClass b, menu m, groupAssign a where m.bigClassId=b.pkId and m.pkId=a.menuId and groupId=@groupid group by b.pkId,m.name
	SET @str = ' 1=1 '
	SET @str = @str + ' and n.bigclassId = z.bigclassId'
	SET @str = @str + ' and z.bigclassId = b.pkid'
	SET @str = @str + ' and b.templateId = t.pkId'
	--SET @str = @str + ' and (n.passed=0 or n.passed=1 or n.passed=2)' 
	--SET @str = @str + ' and n.passed=1'
	SET @str = @str + ' and (n.passed=1 or ((n.passed=0 or n.passed=2) and n.usersId='+STR(@userId) +'))'
	SET @str = @str + ' and n.gradeId<='+STR(@gradeId)
	--SET @str = @str + ' and n.usersId='+STR(@userId) 
	--SET @str = @str + ' and n.gate<>'+STR(@gateId)
	SET @str = @str + ' and n.usersId=u.pkId and n.gradeId=g.pkId and n.departmentId=d.pkId '
	--SET @str = @str + ' and (n.duplicatedPkId=0 or n.duplicatedPkId is null)'
	IF(@searchStr <> '')
		SET @str = @str + @searchStr
	
	SET @tableName = '#test1 z, news n,users u,department d,grade g, template t, bigClass b '

end

	--设置查询排序（不带order by）
	DECLARE @orderTypeA nvarchar(100)
	DECLARE @orderTypeD nvarchar(100)
	SET @orderTypeA = 'ntime ASC, sourceMenuId ASC, newsId ASC'
	SET @orderTypeD = 'ntime DESC, sourceMenuId DESC, newsId DESC'
	
	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(4000)

	SET @vSQL = N'SELECT @count = Count(n.pkId) from '+Convert(nvarchar(100),@tableName)+' where ' + Convert(nvarchar(4000),@str)
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum

	--计算页数
	SET @pageCount = (@recordCount +@pageSize-1)/@pageSize

	--构造查询语句
	DECLARE @sqlStr varchar(4000),@size int

	--判断分页大小，查询最后一页用
	SET @size = @recordCount%@pageSize	
	IF(@size=0)
		SET @size = @pageSize	
	
	--查询第一页
	IF (@currentPage = 0) OR (@currentPage > @pageCount-1)	
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' n.pkId
	,n.specialId,n.gradeId,n.gate,n.departmentId,n.listTitle,n.intactTitle,n.subheading,n.isRecommand,n.isTop,n.useListTitle,n.showReviewLink
	,n.Author,n.AuthorMail,n.CopyFrom,n.copyFromLink,n.about,n.pageType,n.autoPageCharNum,n.readPoint,n.itemType
	,n.descript,n.usersId,n.editor,n.click,n.updateTime,n.updateTime as ntime ,n.passed,n.isReview,n.isImageNews,n.imageName,n.width,n.height,n.MenuId,n.bigClassId,n.updateIp,n.useSubHeading,n.isDuplicated
	,n.duplicateFrom,n.duplicatedPkId,n.validDays,n.mediaAutostart
	,n.AuditUserID,n.isStatic,n.staticUrl,n.staticTime,u.userName,u.fullName,d.name "department",g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId , t.code FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(4000),@str)+' and b.pkId = n.bigClassId and b.templateId = t.pkId ORDER BY '+Convert(nvarchar(100),@orderTypeD)	
	--查询最后一页
	ELSE IF @currentPage = @pageCount -1			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' n.pkId
	,n.specialId
	,n.gradeId
	,n.gate
	,n.departmentId
	,n.listTitle
	,n.intactTitle
	,n.subheading
	,n.isRecommand
	,n.isTop
	,n.useListTitle
	,n.showReviewLink
	,n.Author
	,n.AuthorMail
	,n.CopyFrom
	,n.copyFromLink
	,n.about
	,n.pageType
	,n.autoPageCharNum
	,n.readPoint
	,n.itemType
	,n.descript
	,n.usersId
	,n.editor
	,n.click
	,n.updateTime
	,n.updateTime as ntime 
	,n.passed
	,n.isReview
	,n.isImageNews
	,n.imageName
	,n.width
	,n.height
	,n.MenuId
	,n.bigClassId
	,n.updateIp
	,n.useSubHeading
	,n.isDuplicated
	,n.duplicateFrom
	,n.duplicatedPkId
	,n.validDays
	,n.mediaAutostart
	,n.AuditUserID,n.isStatic,n.staticUrl,n.staticTime,u.userName,u.fullName,d.name "department" ,g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId , t.code FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(4000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable ORDER BY '+Convert(nvarchar(100),@orderTypeD)	
	--查询中间页后面的页
	ELSE IF @currentPage > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @currentPage ) + ' n.pkId
	,n.specialId
	,n.gradeId
	,n.gate
	,n.departmentId
	,n.listTitle
	,n.intactTitle
	,n.subheading
	,n.isRecommand
	,n.isTop
	,n.useListTitle
	,n.showReviewLink
	,n.Author
	,n.AuthorMail
	,n.CopyFrom
	,n.copyFromLink
	,n.about
	,n.pageType
	,n.autoPageCharNum
	,n.readPoint
	,n.itemType
	,n.descript
	,n.usersId
	,n.editor
	,n.click
	,n.updateTime
	,n.updateTime as ntime 
	,n.passed
	,n.isReview
	,n.isImageNews
	,n.imageName
	,n.width
	,n.height
	,n.MenuId
	,n.bigClassId
	,n.updateIp
	,n.useSubHeading
	,n.isDuplicated
	,n.duplicateFrom
	,n.duplicatedPkId
	,n.validDays
	,n.mediaAutostart
	,n.AuditUserID,n.isStatic,n.staticUrl,n.staticTime,u.userName,u.fullName,d.name "department" ,g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId, t.code FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(4000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable ORDER BY '+Convert(nvarchar(100),@orderTypeD)
	--查询中间页前面的页
 	ELSE BEGIN							
		SET @currentPage=@currentPage+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @currentPage ) + ' n.pkId
	,n.specialId
	,n.gradeId
	,n.gate
	,n.departmentId
	,n.listTitle
	,n.intactTitle
	,n.subheading
	,n.isRecommand
	,n.isTop
	,n.useListTitle
	,n.showReviewLink
	,n.Author
	,n.AuthorMail
	,n.CopyFrom
	,n.copyFromLink
	,n.about
	,n.pageType
	,n.autoPageCharNum
	,n.readPoint
	,n.itemType
	,n.descript
	,n.usersId
	,n.editor
	,n.click
	,n.updateTime
	,n.updateTime as ntime 
	,n.passed
	,n.isReview
	,n.isImageNews
	,n.imageName
	,n.width
	,n.height
	,n.MenuId
	,n.bigClassId
	,n.updateIp
	,n.useSubHeading
	,n.isDuplicated
	,n.duplicateFrom
	,n.duplicatedPkId
	,n.validDays
	,n.mediaAutostart
	,n.AuditUserID,n.isStatic,n.staticUrl,n.staticTime,u.userName,u.fullName,d.name "department" ,g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId, t.code FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(4000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeD)+') TempTable1 '
					+ ' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable2 ORDER BY '+Convert(nvarchar(100),@orderTypeD)
	END

	--执行查询
	EXEC(@sqlStr)
RETURN




GO


if exists (select * from dbo.sysobjects where id = object_id(N'GetNewsByClick') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetNewsByClick
GO
CREATE PROCEDURE GetNewsByClick
@pageSize int=5,
@gradeid int=1,
@gateId int,
@menuId int
AS

BEGIN
	
	-- Set the page bounds
    DECLARE @PageLowerBound INT
    DECLARE @PageUpperBound INT
    DECLARE @TotalRecords   INT
    SET @PageLowerBound = @pageSize * 0
    SET @PageUpperBound = @pageSize - 1 + @PageLowerBound

	-- Create a temp table TO store the select results
    CREATE TABLE #PageIndexs
    (
        IndexId int IDENTITY (0, 1) NOT NULL,
        ID int
    )

    DECLARE @num int
	select @num=0
	select @num = count(pkId) from BigClassAssociation where thisBigClassId=@menuId
	if(@num = 0)
	begin

	-- Insert into our temp table
    INSERT	INTO #PageIndexs (ID)
	SELECT	TOP (@pageSize) n.pkId
	FROM	[News] AS n 
			INNER JOIN [Users] AS u ON n.UsersID = u.PKID
			INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
	WHERE	(n.gate > @gateId OR n.gate < @gateId) and   
			n.gradeId <= @gradeId and 
			n.passed =	1 and 
			isDuplicated != 1 and
			(n.validDays > 0 OR n.validDays < 0)  
	ORDER BY n.click desc,n.istop desc,n.Priority desc,n.pkid desc

	end
	else
	begin

    -- Insert into our temp table
    INSERT	INTO #PageIndexs (ID)
	SELECT	TOP (@pageSize) n.pkId
	FROM	[News] AS n 
			INNER JOIN [Users] AS u ON n.UsersID = u.PKID
			INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
	WHERE	(n.gate > @gateId OR n.gate < @gateId) and   
			n.gradeId <= @gradeId and 
			n.passed =	1 and 
			n.bigClassId in (select bigClassId from BigClassAssociation where thisBigClassId=@menuId) and	
			isDuplicated != 1 and
			(n.validDays > 0 OR n.validDays < 0)  
	ORDER BY n.click desc,n.istop desc,n.Priority desc,n.pkid desc

	end


	SELECT  n.pkId
	,n.specialId
	,n.gradeId
	,n.gate
	,n.departmentId
	,n.listTitle
	,n.intactTitle
	,n.subheading
	,n.isRecommand
	,n.isTop
	,n.useListTitle
	,n.showReviewLink
	,n.Author
	,n.AuthorMail
	,n.CopyFrom
	,n.copyFromLink
	,n.about
	,n.pageType
	,n.autoPageCharNum
	,n.readPoint
	,n.itemType
	,n.descript
	,n.usersId
	,n.editor
	,n.click
	,n.updateTime
	,n.passed
	,n.isReview
	,n.isImageNews
	,n.imageName
	,n.width
	,n.height
	,n.MenuId
	,n.bigClassId
	,n.updateIp
	,n.useSubHeading
	,n.isDuplicated
	,n.duplicateFrom
	,n.duplicatedPkId
	,n.validDays
	,n.mediaAutostart
	,n.AuditUserID,n.isStatic,n.staticUrl,n.staticTime, u.UserName, d.name as Department ,b.name as BigClassName
	FROM	[News] AS n
			INNER JOIN [Users] AS u ON n.UsersID = u.PKID
			INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
			INNER JOIN [BigClass] AS b ON n.bigClassId=b.pkId
			INNER JOIN [Template] AS t ON t.pkId = b.templateId
			INNER JOIN [#PageIndexs] AS p ON n.PKID = p.ID
	WHERE	p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
	ORDER BY n.click desc,n.istop desc ,n.Priority desc,n.pkid desc

	DELETE #PageIndexs
	RETURN 
END

GO

if exists (select * from dbo.sysobjects where id = object_id(N'GetLatestNewsByPassed') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetLatestNewsByPassed
GO
CREATE PROCEDURE GetLatestNewsByPassed
	@pageIndex int,
	@pageSize int=5,
	@gradeid int=1,
	@passed int=1,
	@gateId int,
	@menuId int,
	@sortType int
AS

BEGIN

	SET @pageIndex = @pageIndex - 1
	IF (@pageIndex < 0)
		BEGIN
			SET @pageIndex = 1
		END
	-- Set the page bounds
    DECLARE @PageLowerBound INT
    DECLARE @PageUpperBound INT
    DECLARE @TotalRecords   INT
    SET @PageLowerBound = @PageSize * @pageIndex
    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
	
    -- Create a temp table TO store the select results
    CREATE TABLE #PageIndexs
    (
        IndexId int IDENTITY (0, 1) NOT NULL,
        ID int
    )

	DECLARE @num int
	select @num=0
	select @num = count(pkId) from BigClassAssociation where thisBigClassId=@menuId
	if(@num = 0)
	begin

	if(@pageIndex=0) --优化首页访问速度
	begin
		--增加排序方式判断
		if(@sortType=1)
		begin
			-- Insert into our temp table
			INSERT	INTO #PageIndexs (ID)
			SELECT  TOP (@pageSize)  n.pkId
			FROM	[News] AS n 
					INNER JOIN [Users] AS u ON n.UsersID = u.PKID
					INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
			WHERE	(n.gate > @gateId OR n.gate < @gateId) and   
					n.gradeId <=		@gradeId and 
					n.passed =			@passed and 
					isDuplicated != 1 and
					(n.validDays > 0 OR n.validDays < 0) and
					(n.albumid=0 OR n.albumid is null)  
			ORDER BY n.updateTime desc,n.istop desc,n.Priority desc,n.pkid desc
		end
		else
		begin
			-- Insert into our temp table
			INSERT	INTO #PageIndexs (ID)
			SELECT  TOP (@pageSize)  n.pkId
			FROM	[News] AS n 
					INNER JOIN [Users] AS u ON n.UsersID = u.PKID
					INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
			WHERE	(n.gate > @gateId OR n.gate < @gateId) and   
					n.gradeId <=		@gradeId and 
					n.passed =			@passed and 
					isDuplicated != 1 and
					(n.validDays > 0 OR n.validDays < 0)  and
					(n.albumid=0 OR n.albumid is null) 
			ORDER BY n.istop desc,n.updateTime desc,n.Priority desc,n.pkid desc
		end
	end
	else
	begin
		if(@sortType=1)
		begin
			-- Insert into our temp table
			INSERT	INTO #PageIndexs (ID)
			SELECT	n.pkId
			FROM	[News] AS n 
					INNER JOIN [Users] AS u ON n.UsersID = u.PKID
					INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
			WHERE	(n.gate > @gateId OR n.gate < @gateId) and   
					n.gradeId <=		@gradeId and 
					n.passed =			@passed and 
					isDuplicated != 1 and
					(n.validDays > 0 OR n.validDays < 0) and
					(n.albumid=0 OR n.albumid is null)  
			ORDER BY n.updateTime desc,n.istop desc,n.Priority desc,n.pkid desc
		end
		else
		begin
			-- Insert into our temp table
			INSERT	INTO #PageIndexs (ID)
			SELECT	n.pkId
			FROM	[News] AS n 
					INNER JOIN [Users] AS u ON n.UsersID = u.PKID
					INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
			WHERE	(n.gate > @gateId OR n.gate < @gateId) and   
					n.gradeId <=		@gradeId and 
					n.passed =			@passed and 
					isDuplicated != 1 and
					(n.validDays > 0 OR n.validDays < 0)  and
					(n.albumid=0 OR n.albumid is null) 
			ORDER BY n.istop desc,n.updateTime desc,n.Priority desc,n.pkid desc
		end
	end	
	end
	else
	begin
		if(@pageIndex=0) --优化首页访问速度
		begin
			if(@sortType=1)
			begin
				-- Insert into our temp table
				INSERT	INTO #PageIndexs (ID)
				SELECT TOP (@pageSize) n.pkId
				FROM	[News] AS n 
						INNER JOIN [Users] AS u ON n.UsersID = u.PKID
						INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
				WHERE	(n.gate > @gateId OR n.gate < @gateId) and   
						n.gradeId <=		@gradeId and 
						n.passed =			@passed and 
						isDuplicated = 0 and
						(n.validDays > 0 OR n.validDays < 0) and	
						exists (select 1 from BigClassAssociation ba where ba.bigclassid = n.bigclassid and  ba.thisBigclassId=@menuId) 
						--n.bigClassId in (select bigClassId from BigClassAssociation where thisBigClassId=@menuId) 
						  
				ORDER BY n.updateTime desc,n.istop desc,n.Priority desc,n.pkid desc
			end
			else
			begin
				-- Insert into our temp table
				INSERT	INTO #PageIndexs (ID)
				SELECT TOP (@pageSize) n.pkId
				FROM	[News] AS n 
						INNER JOIN [Users] AS u ON n.UsersID = u.PKID
						INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
				WHERE	(n.gate > @gateId OR n.gate < @gateId) and   
						n.gradeId <=		@gradeId and 
						n.passed =			@passed and 
						isDuplicated = 0 and
						(n.validDays > 0 OR n.validDays < 0)  and
						exists (select 1 from BigClassAssociation ba where ba.bigclassid = n.bigclassid and  ba.thisBigclassId=@menuId) 
						--n.bigClassId in (select bigClassId from BigClassAssociation where thisBigClassId=@menuId)	
						 
				ORDER BY n.istop desc,n.updateTime desc,n.Priority desc,n.pkid desc
			end
		end
		else
		begin

			if(@sortType=1)
			begin
				-- Insert into our temp table
				INSERT	INTO #PageIndexs (ID)

				SELECT	n.pkId
				FROM	[News] AS n 
						INNER JOIN [Users] AS u ON n.UsersID = u.PKID
						INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
				WHERE	(n.gate > @gateId OR n.gate < @gateId) and   
						n.gradeId <=		@gradeId and 
						n.passed =			@passed and 
						isDuplicated = 0 and
						(n.validDays > 0 OR n.validDays < 0) and	
						exists (select 1 from BigClassAssociation ba where ba.bigclassid = n.bigclassid and  ba.thisBigclassId=@menuId) 
						--n.bigClassId in (select bigClassId from BigClassAssociation where thisBigClassId=@menuId) 
						 
				ORDER BY n.updateTime desc,n.istop desc,n.Priority desc,n.pkid desc
			end
			else
			begin
				-- Insert into our temp table
				INSERT	INTO #PageIndexs (ID)

				SELECT	n.pkId
				FROM	[News] AS n 
						INNER JOIN [Users] AS u ON n.UsersID = u.PKID
						INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
				WHERE	(n.gate > @gateId OR n.gate < @gateId) and   
						n.gradeId <=		@gradeId and 
						n.passed =			@passed and 
						isDuplicated = 0 and
						(n.validDays > 0 OR n.validDays < 0)  and
						exists (select 1 from BigClassAssociation ba where ba.bigclassid = n.bigclassid and  ba.thisBigclassId=@menuId) 
						--n.bigClassId in (select bigClassId from BigClassAssociation where thisBigClassId=@menuId) 	
						
				ORDER BY n.istop desc,n.updateTime desc,n.Priority desc,n.pkid desc
			end
		end
	end

	if(@sortType=1)
	begin

		SELECT  n.pkId ,n.specialId, n.gradeId ,n.gate ,n.departmentId ,n.listTitle ,n.intactTitle ,n.subheading
					,n.isRecommand	,n.isTop, n.useListTitle ,n.showReviewLink, n.Author ,n.AuthorMail ,n.CopyFrom ,n.copyFromLink
					,n.about ,n.pageType ,n.autoPageCharNum	,n.readPoint ,n.itemType ,n.descript ,n.usersId	,n.editor
					,n.click ,n.updateTime ,n.passed ,n.isReview ,n.isImageNews	,n.imageName ,n.width ,n.height
					,n.MenuId ,n.bigClassId	,n.updateIp	,n.useSubHeading,n.isDuplicated	,n.duplicateFrom ,n.duplicatedPkId ,n.validDays
					,n.mediaAutostart ,n.AuditUserID,n.isStatic,n.staticUrl,n.staticTime, u.UserName, d.name as Department ,b.name as BigClassName
		FROM	[News] AS n
				INNER JOIN [Users] AS u ON n.UsersID = u.PKID
				INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
				INNER JOIN [BigClass] AS b ON n.bigClassId=b.pkId
				INNER JOIN [Template] AS t ON t.pkId = b.templateId
				INNER JOIN [#PageIndexs] AS p ON n.PKID = p.ID
		WHERE	p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
		ORDER BY n.updateTime desc,n.istop desc ,n.Priority desc,n.pkid desc
	end
	else
	begin
		SELECT  n.pkId ,n.specialId, n.gradeId ,n.gate ,n.departmentId ,n.listTitle ,n.intactTitle ,n.subheading
				,n.isRecommand	,n.isTop, n.useListTitle ,n.showReviewLink, n.Author ,n.AuthorMail ,n.CopyFrom ,n.copyFromLink
				,n.about ,n.pageType ,n.autoPageCharNum	,n.readPoint ,n.itemType ,n.descript ,n.usersId	,n.editor
				,n.click ,n.updateTime ,n.passed ,n.isReview ,n.isImageNews	,n.imageName ,n.width ,n.height
				,n.MenuId ,n.bigClassId	,n.updateIp	,n.useSubHeading,n.isDuplicated	,n.duplicateFrom ,n.duplicatedPkId ,n.validDays
				,n.mediaAutostart ,n.AuditUserID,n.isStatic,n.staticUrl,n.staticTime, u.UserName, d.name as Department ,b.name as BigClassName
		FROM	[News] AS n
				INNER JOIN [Users] AS u ON n.UsersID = u.PKID
				INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
				INNER JOIN [BigClass] AS b ON n.bigClassId=b.pkId
				INNER JOIN [Template] AS t ON t.pkId = b.templateId
				INNER JOIN [#PageIndexs] AS p ON n.PKID = p.ID
		WHERE	p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
		ORDER BY n.istop desc ,n.updateTime desc,n.Priority desc,n.pkid desc
	end

	DELETE #PageIndexs
	RETURN 
END


GO

if exists (select * from dbo.sysobjects where id = object_id(N'GetNewsBySpecialAndpassed') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetNewsBySpecialAndpassed
GO
CREATE PROCEDURE GetNewsBySpecialAndpassed
@special int=null,
@currentPage int=1,
@pageSize int=5,
@gradeid int=1,
@passed int=1,
@gateId int
AS
	SET NOCOUNT ON 
	
	declare @vSQL varchar(2000)
	select @vSQL = 'select top ' + convert(varchar, @pageSize) 
	+ ' n.pkId ,n.specialId ,n.gradeId ,n.gate ,n.departmentId ,n.listTitle ,n.intactTitle ,n.subheading
				,n.isRecommand ,n.isTop ,n.useListTitle ,n.showReviewLink ,n.Author ,n.AuthorMail ,n.CopyFrom ,n.copyFromLink
				,n.about ,n.pageType ,n.autoPageCharNum ,n.readPoint ,n.itemType ,n.descript ,n.usersId ,n.editor
				,n.click ,n.updateTime ,n.passed ,n.isReview ,n.isImageNews ,n.imageName ,n.width ,n.height
				,n.MenuId ,n.bigClassId ,n.updateIp ,n.useSubHeading ,n.isDuplicated ,n.duplicateFrom ,n.duplicatedPkId ,n.validDays
				,n.mediaAutostart ,n.AuditUserID,n.isStatic,n.staticUrl,n.staticTime,u.userName,d.name "department" from news n,users u,department d where n.gate<>'
	+Convert(varchar,@gateId)+' and (specialId='+Convert(varchar,@special)
	+') and n.usersId=u.pkId and (n.isDuplicated=0 or n.isDuplicated is null) and n.departmentId=d.pkId and n.gradeId<='
	+convert(varchar,@gradeId)+' and n.passed='+convert(varchar,@passed)
	+' and validDays <> 0 and n.pkId < all (select top '+Convert(varchar,((@currentPage-1)*@pageSize))
	+' pkId from news where gate<>'+Convert(varchar,@gateId)+' and (specialId='
	+Convert(varchar,@special)+') and gradeId<='+convert(varchar,@gradeId)+' and isDuplicated<>1 and passed='
	+convert(varchar,@passed)+' and validDays <> 0 order by istop desc,pkId desc) order by istop desc,n.pkId desc'
	Execute (@vSQL)
	

	RETURN 

GO

if exists (select * from dbo.sysobjects where id = object_id(N'GetNewsByRecommand') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetNewsByRecommand
GO
CREATE PROCEDURE GetNewsByRecommand
@pageSize int=5,
@gradeid int=1,
@gateId int
AS
SET NOCOUNT ON 


select top (@pageSize)  n.pkId ,n.specialId ,n.gradeId ,n.gate ,n.departmentId ,n.listTitle ,n.intactTitle ,n.subheading
				,n.isRecommand ,n.isTop ,n.useListTitle ,n.showReviewLink ,n.Author ,n.AuthorMail ,n.CopyFrom ,n.copyFromLink
				,n.about ,n.pageType ,n.autoPageCharNum ,n.readPoint ,n.itemType ,n.descript ,n.usersId ,n.editor
				,n.click ,n.updateTime ,n.passed ,n.isReview ,n.isImageNews ,n.imageName ,n.width ,n.height
				,n.MenuId ,n.bigClassId ,n.updateIp ,n.useSubHeading ,n.isDuplicated ,n.duplicateFrom ,n.duplicatedPkId ,n.validDays
				,n.mediaAutostart ,n.AuditUserID,n.isStatic,n.staticUrl,n.staticTime,d.name as Department from news AS n INNER JOIN [Department] AS d ON n.departmentId = d.PKID where gate<>@gateId and IsImageNews=0 and isDuplicated<>1 and gradeId<=@gradeId and passed=1 and isRecommand=1 and validDays<>0 order by n.istop desc,n.pkId desc



RETURN

GO


if exists (select * from dbo.sysobjects where id = object_id(N'GetLatestScrollFlashNews') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetLatestScrollFlashNews
GO
CREATE PROCEDURE GetLatestScrollFlashNews
	@pageIndex int,
	@pageSize int=5,
	@gradeid int=1,
	@passed int=1,
	@gateId int,
	@menuId int
AS

BEGIN

	SET @pageIndex = @pageIndex - 1
	IF (@pageIndex < 0)
		BEGIN
			SET @pageIndex = 1
		END
	-- Set the page bounds
    DECLARE @PageLowerBound INT
    DECLARE @PageUpperBound INT
    DECLARE @TotalRecords   INT
    SET @PageLowerBound = @PageSize * @pageIndex
    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
	
    -- Create a temp table TO store the select results
    CREATE TABLE #PageIndexs
    (
        IndexId int IDENTITY (0, 1) NOT NULL,
        ID int
    )

	DECLARE @num int
	select @num=0
	select @num = count(pkId) from BigClassAssociation where thisBigClassId=@menuId
	if(@num = 0)
	begin

	if(@pageIndex=0) --优化首页访问速度
	begin
		-- Insert into our temp table
		INSERT	INTO #PageIndexs (ID)
		SELECT  TOP (@pageSize)  n.pkId
		FROM	[News] AS n 
				INNER JOIN [Users] AS u ON n.UsersID = u.PKID
				INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
		WHERE	(n.gate > @gateId OR n.gate < @gateId) and   
				n.gradeId <=		@gradeId and 
				n.passed =			@passed and 
				isDuplicated != 1 and
				(n.validDays > 0 OR n.validDays < 0) and
					(n.albumid=0 OR n.albumid is null) and
n.imageName!='' and CHARINDEX('/Images/uploadfiles/nopic.gif',n.imageName)=0
		ORDER BY n.updateTime desc,n.istop desc,n.Priority desc,n.pkid desc
	end
	else
	begin
		-- Insert into our temp table
		INSERT	INTO #PageIndexs (ID)
		SELECT	n.pkId
		FROM	[News] AS n 
				INNER JOIN [Users] AS u ON n.UsersID = u.PKID
				INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
		WHERE	(n.gate > @gateId OR n.gate < @gateId) and   
				n.gradeId <=		@gradeId and 
				n.passed =			@passed and 
				isDuplicated != 1 and
				(n.validDays > 0 OR n.validDays < 0)   and
					(n.albumid=0 OR n.albumid is null) and
				n.imageName!='' and CHARINDEX('/Images/uploadfiles/nopic.gif',n.imageName)=0
		ORDER BY n.updateTime desc,n.istop desc,n.Priority desc,n.pkid desc
	end	
	end
	else
	begin
		if(@pageIndex=0) --优化首页访问速度
		begin
			-- Insert into our temp table
			INSERT	INTO #PageIndexs (ID)
			SELECT TOP (@pageSize) n.pkId
			FROM	[News] AS n 
					INNER JOIN [Users] AS u ON n.UsersID = u.PKID
					INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
			WHERE	(n.gate > @gateId OR n.gate < @gateId) and   
					n.gradeId <=		@gradeId and 
					n.passed =			@passed and 
					n.bigClassId in (select bigClassId from BigClassAssociation where thisBigClassId=@menuId) and	
					isDuplicated != 1 and
					(n.validDays > 0 OR n.validDays < 0)  and
					n.imageName!='' and CHARINDEX('/Images/uploadfiles/nopic.gif',n.imageName)=0 
			ORDER BY n.updateTime desc,n.istop desc,n.Priority desc,n.pkid desc
		end
		else
		begin
		-- Insert into our temp table
		INSERT	INTO #PageIndexs (ID)

		SELECT	n.pkId
		FROM	[News] AS n 
				INNER JOIN [Users] AS u ON n.UsersID = u.PKID
				INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
		WHERE	(n.gate > @gateId OR n.gate < @gateId) and   
				n.gradeId <=		@gradeId and 
				n.passed =			@passed and 
				n.bigClassId in (select bigClassId from BigClassAssociation where thisBigClassId=@menuId) and	
				isDuplicated != 1 and
				(n.validDays > 0 OR n.validDays < 0) and
				n.imageName!='' and CHARINDEX('/Images/uploadfiles/nopic.gif',n.imageName)=0  
		ORDER BY n.updateTime desc,n.istop desc,n.Priority desc,n.pkid desc
		end
	end

	SELECT  n.pkId ,n.specialId, n.gradeId ,n.gate ,n.departmentId ,n.listTitle ,n.intactTitle ,n.subheading
				,n.isRecommand	,n.isTop, n.useListTitle ,n.showReviewLink, n.Author ,n.AuthorMail ,n.CopyFrom ,n.copyFromLink
				,n.about ,n.pageType ,n.autoPageCharNum	,n.readPoint ,n.itemType ,n.descript ,n.usersId	,n.editor
				,n.click ,n.updateTime ,n.passed ,n.isReview ,n.isImageNews	,n.imageName ,n.width ,n.height
				,n.MenuId ,n.bigClassId	,n.updateIp	,n.useSubHeading,n.isDuplicated	,n.duplicateFrom ,n.duplicatedPkId ,n.validDays
				,n.mediaAutostart ,n.AuditUserID,n.isStatic,n.staticUrl,n.staticTime, u.UserName, d.name as Department ,b.name as BigClassName
	FROM	[News] AS n
			INNER JOIN [Users] AS u ON n.UsersID = u.PKID
			INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
			INNER JOIN [BigClass] AS b ON n.bigClassId=b.pkId
			INNER JOIN [Template] AS t ON t.pkId = b.templateId
			INNER JOIN [#PageIndexs] AS p ON n.PKID = p.ID
	WHERE	p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
	ORDER BY n.updateTime desc,n.istop desc ,n.Priority desc,n.pkid desc

	DELETE #PageIndexs
	RETURN 
END


GO

--修正工作台出现列名updateTime不明确
if exists (select * from dbo.sysobjects where id = object_id(N'GetAuditingNewsByUserId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetAuditingNewsByUserId
GO
CREATE PROCEDURE GetAuditingNewsByUserId
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeId int,
	@userId int,
	@gateId int,
	@manageAll varchar(8000),
	@recordCount int output
	
AS	
	--转换成索引
	SELECT @currentPage = @currentPage - 1
	
	--初始化查询条件(不带where)
	DECLARE @str nvarchar(1000)
	--设置查询表名称
	DECLARE @tableName nvarchar(100)

	--判断当前用户是否属于用户组
	Declare @groupid int 
select @groupid = usersGroupId from users where pkid=@userId
if(@groupid=1)
begin

--用临时表先读取授权的栏目和MenuName，然后在读取授权的新闻，
--这样设计不会对news表中的menuId产生依赖，避免因栏目移动产生错误
--根据栏目Id进行分组，以消除不用菜单位置的相同栏目产生的重复记录，20091010
select b.pkId bigclassId, m.name menuName,max(m.pkId) sourceMenuId into #test from bigClass b, menu m, moduleAssign a where m.bigClassId=b.pkId and m.pkId=a.menuId and usersId=@userId and charindex('canAuditNews=1',privilegeSet)>0 group by b.pkId,m.name

	SET @str = ' 1=1 '
	SET @str = @str + ' and n.bigclassId = z.bigclassId'
	SET @str = @str + ' and z.bigclassId = b.pkid'
	SET @str = @str + ' and b.templateId = t.pkId'
	SET @str = @str + ' and n.passed=0 and n.gradeId<='+STR(@gradeId)
	SET @str = @str + ' and n.gate<>'+STR(@gateId)
	SET @str = @str + ' and n.usersId=u.pkId and n.gradeId=g.pkId and n.departmentId=d.pkId ' --and n.MenuId=m.pkId 去掉了 fengyb 2008年-1-9日and n.MenuId=m.pkId 
	
	SET @tableName = '#test z, news n,users u,department d,grade g, template t, bigClass b '

end
else
begin
	
	SELECT b.pkId bigclassId, m.name menuName,max(m.pkId) sourceMenuId into #test1 from bigClass b, menu m, groupAssign a where m.bigClassId=b.pkId and m.pkId=a.menuId and groupId=@groupid and charindex('canAuditNews=1',privilegeSet)>0 group by b.pkId,m.name

	SET @str = ' 1=1 '
	SET @str = @str + ' and n.bigclassId = z.bigclassId'
	SET @str = @str + ' and z.bigclassId = b.pkid'
	SET @str = @str + ' and b.templateId = t.pkId'
	SET @str = @str + ' and n.passed=0 and n.gradeId<='+STR(@gradeId)
	SET @str = @str + ' and n.gate<>'+STR(@gateId)
	SET @str = @str + ' and n.usersId=u.pkId and n.gradeId=g.pkId and n.departmentId=d.pkId ' --and n.MenuId=m.pkId 去掉了 fengyb 2008年-1-9日and n.MenuId=m.pkId 

	SET @tableName = '#test1 z, news n,users u,department d,grade g, template t, bigClass b '
end
 
	--设置查询排序（不带order by）
	DECLARE @orderTypeA nvarchar(100)
	DECLARE @orderTypeD nvarchar(100)
	SET @orderTypeA = 'ntime ASC, sourceMenuId ASC, newsId ASC'
	SET @orderTypeD = 'ntime DESC, sourceMenuId DESC, newsId DESC'
	
	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(4000)
	
	SET @vSQL = N'SELECT @count = Count(n.pkId) from '+Convert(nvarchar(100),@tableName)+' where ' + Convert(nvarchar(1000),@str)
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum

	--计算页数
	SET @pageCount = (@recordCount +@pageSize-1)/@pageSize

	--构造查询语句
	DECLARE @sqlStr varchar(4000),@size int

	--判断分页大小，查询最后一页用
	SET @size = @recordCount%@pageSize	
	IF(@size=0)
		SET @size = @pageSize	
	
	--查询第一页
	IF (@currentPage = 0) OR (@currentPage > @pageCount-1)	
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' n.pkId ,n.specialId ,n.gradeId ,n.gate ,n.departmentId ,n.listTitle ,n.intactTitle ,n.subheading
				,n.isRecommand ,n.isTop ,n.useListTitle ,n.showReviewLink ,n.Author ,n.AuthorMail ,n.CopyFrom ,n.copyFromLink
				,n.about ,n.pageType ,n.autoPageCharNum ,n.readPoint ,n.itemType ,n.usersId ,n.editor
				,n.click ,n.updateTime ,n.updateTime as ntime,n.passed ,n.isReview ,n.isImageNews ,n.imageName ,n.width ,n.height
				,n.MenuId ,n.bigClassId ,n.updateIp ,n.useSubHeading ,n.isDuplicated ,n.duplicateFrom ,n.duplicatedPkId ,n.validDays
				,n.mediaAutostart ,n.AuditUserID,u.userName,u.fullName,d.name "department" ,g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId , t.code FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(1000),@str)+' and b.pkId = n.bigClassId and b.templateId = t.pkId ORDER BY '+Convert(nvarchar(100),@orderTypeD)	
	--查询最后一页
	ELSE IF @currentPage = @pageCount -1			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' n.pkId ,n.specialId ,n.gradeId ,n.gate ,n.departmentId ,n.listTitle ,n.intactTitle ,n.subheading
				,n.isRecommand ,n.isTop ,n.useListTitle ,n.showReviewLink ,n.Author ,n.AuthorMail ,n.CopyFrom ,n.copyFromLink
				,n.about ,n.pageType ,n.autoPageCharNum ,n.readPoint ,n.itemType ,n.usersId ,n.editor
				,n.click ,n.updateTime ,n.updateTime as ntime ,n.passed ,n.isReview ,n.isImageNews ,n.imageName ,n.width ,n.height
				,n.MenuId ,n.bigClassId ,n.updateIp ,n.useSubHeading ,n.isDuplicated ,n.duplicateFrom ,n.duplicatedPkId ,n.validDays
				,n.mediaAutostart ,n.AuditUserID,u.userName,u.fullName,d.name "department" ,g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId , t.code FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(1000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable ORDER BY '+Convert(nvarchar(100),@orderTypeD)	
	--查询中间页后面的页
	ELSE IF @currentPage > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @currentPage ) + ' n.pkId ,n.specialId ,n.gradeId ,n.gate ,n.departmentId ,n.listTitle ,n.intactTitle ,n.subheading
				,n.isRecommand ,n.isTop ,n.useListTitle ,n.showReviewLink ,n.Author ,n.AuthorMail ,n.CopyFrom ,n.copyFromLink
				,n.about ,n.pageType ,n.autoPageCharNum ,n.readPoint ,n.itemType ,n.usersId ,n.editor
				,n.click ,n.updateTime ,n.updateTime as ntime ,n.passed ,n.isReview ,n.isImageNews ,n.imageName ,n.width ,n.height
				,n.MenuId ,n.bigClassId ,n.updateIp ,n.useSubHeading ,n.isDuplicated ,n.duplicateFrom ,n.duplicatedPkId ,n.validDays
				,n.mediaAutostart ,n.AuditUserID,u.userName,u.fullName,d.name "department" ,g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId, t.code FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(1000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable ORDER BY '+Convert(nvarchar(100),@orderTypeD)
	--查询中间页前面的页
 	ELSE BEGIN							
		SET @currentPage=@currentPage+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @currentPage ) + ' n.pkId ,n.specialId ,n.gradeId ,n.gate ,n.departmentId ,n.listTitle ,n.intactTitle ,n.subheading
				,n.isRecommand ,n.isTop ,n.useListTitle ,n.showReviewLink ,n.Author ,n.AuthorMail ,n.CopyFrom ,n.copyFromLink
				,n.about ,n.pageType ,n.autoPageCharNum ,n.readPoint ,n.itemType ,n.usersId ,n.editor
				,n.click ,n.updateTime ,n.updateTime as ntime ,n.passed ,n.isReview ,n.isImageNews ,n.imageName ,n.width ,n.height
				,n.MenuId ,n.bigClassId ,n.updateIp ,n.useSubHeading ,n.isDuplicated ,n.duplicateFrom ,n.duplicatedPkId ,n.validDays
				,n.mediaAutostart ,n.AuditUserID,u.userName,u.fullName,d.name "department" ,g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId, t.code FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(1000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeD)+') TempTable1 '
					+ ' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable2 ORDER BY '+Convert(nvarchar(100),@orderTypeD)
	END
	
	--执行查询
	EXEC(@sqlStr)
	if(@groupid=1)
	begin
		DELETE #test
	end
	else
	begin
		DELETE #test1
	end
RETURN



GO

if exists (select * from dbo.sysobjects where id = object_id(N'GetAuditingReviewsByUserId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetAuditingReviewsByUserId
GO
CREATE PROCEDURE GetAuditingReviewsByUserId
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeId int,
	@userId int,
	@gateId int,
	@manageAll varchar(8000),
	@recordCount int output
	
AS	
	--转换成索引
	SELECT @currentPage = @currentPage - 1
	DECLARE @str nvarchar(1000)
	--设置查询表名称
	DECLARE @tableName nvarchar(100)
	--判断当前用户是否属于用户组
	Declare @groupid int 
select @groupid = usersGroupId from users where pkid=@userId
if(@groupid=1)
begin
	select b.pkId bigclassId, m.name menuName,max(m.pkId) sourceMenuId into #test from bigClass b, menu m, moduleAssign a where m.bigClassId=b.pkId and m.pkId=a.menuId and usersId=@userId and charindex('canAuditNews=1',privilegeSet)>0 group by b.pkId,m.name
	
	SET @str = ' 1=1 '
	SET @str = @str + ' and n.bigclassId = z.bigclassId'
	SET @str = @str + ' and z.bigclassId = b.pkid'
	SET @str = @str + ' and b.templateId = t.pkId'
	SET @str = @str + ' and n.pkid in (select newsid from review where passed=0) and n.gradeId<='+STR(@gradeId)
	SET @str = @str + ' and n.gate<>'+STR(@gateId)
	SET @str = @str + ' and n.usersId=u.pkId and n.gradeId=g.pkId '
	
	SET @tableName = '#test z, news n,users u,grade g, template t, bigClass b'

end
else
begin
	select b.pkId bigclassId, m.name menuName,max(m.pkId) sourceMenuId into #test1 from bigClass b, menu m, groupAssign a where m.bigClassId=b.pkId and m.pkId=a.menuId and groupId=@groupid and charindex('canAuditNews=1',privilegeSet)>0 group by b.pkId,m.name

	SET @str = ' 1=1 '
	SET @str = @str + ' and n.bigclassId = z.bigclassId'
	SET @str = @str + ' and z.bigclassId = b.pkid'
	SET @str = @str + ' and b.templateId = t.pkId'
	SET @str = @str + ' and n.pkid in (select newsid from review where passed=0) and n.gradeId<='+STR(@gradeId)
	SET @str = @str + ' and n.gate<>'+STR(@gateId)
	SET @str = @str + ' and n.usersId=u.pkId and n.gradeId=g.pkId '
	
	SET @tableName = '#test1 z, news n,users u,grade g, template t, bigClass b'
end
	--设置查询排序（不带order by）
	DECLARE @orderTypeA nvarchar(100)
	DECLARE @orderTypeD nvarchar(100)
	SET @orderTypeA = 'ntime ASC,sourceMenuId ASC, newsId ASC'
	SET @orderTypeD = 'ntime DESC,sourceMenuId DESC, newsId DESC'
	
	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(4000)

	SET @vSQL = N'SELECT @count = Count(n.pkId) from '+Convert(nvarchar(100),@tableName)+' where ' + Convert(nvarchar(1000),@str)
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum

	--计算页数
	SET @pageCount = (@recordCount +@pageSize-1)/@pageSize

	--构造查询语句
	DECLARE @sqlStr varchar(4000),@size int

	--判断分页大小，查询最后一页用
	SET @size = @recordCount%@pageSize	
	IF(@size=0)
		SET @size = @pageSize	
	
	--查询第一页
	IF (@currentPage = 0) OR (@currentPage > @pageCount-1)	
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' n.pkId ,n.specialId ,n.gradeId ,n.gate ,n.departmentId ,n.listTitle ,n.intactTitle ,n.subheading
				,n.isRecommand ,n.isTop ,n.useListTitle ,n.showReviewLink ,n.Author ,n.AuthorMail ,n.CopyFrom ,n.copyFromLink
				,n.about ,n.pageType ,n.autoPageCharNum ,n.readPoint ,n.itemType ,n.usersId ,n.editor
				,n.click ,n.updateTime ,n.updateTime as ntime ,n.passed ,n.isReview ,n.isImageNews ,n.imageName ,n.width ,n.height
				,n.MenuId ,n.bigClassId ,n.updateIp ,n.useSubHeading ,n.isDuplicated ,n.duplicateFrom ,n.duplicatedPkId ,n.validDays
				,n.mediaAutostart ,n.AuditUserID,u.userName,u.fullName,g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId , t.code FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(1000),@str)+' and b.pkId = n.bigClassId and b.templateId = t.pkId ORDER BY '+Convert(nvarchar(100),@orderTypeD)	
	--查询最后一页
	ELSE IF @currentPage = @pageCount -1			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' n.pkId ,n.specialId ,n.gradeId ,n.gate ,n.departmentId ,n.listTitle ,n.intactTitle ,n.subheading
				,n.isRecommand ,n.isTop ,n.useListTitle ,n.showReviewLink ,n.Author ,n.AuthorMail ,n.CopyFrom ,n.copyFromLink
				,n.about ,n.pageType ,n.autoPageCharNum ,n.readPoint ,n.itemType ,n.usersId ,n.editor
				,n.click ,n.updateTime ,n.updateTime as ntime ,n.passed ,n.isReview ,n.isImageNews ,n.imageName ,n.width ,n.height
				,n.MenuId ,n.bigClassId ,n.updateIp ,n.useSubHeading ,n.isDuplicated ,n.duplicateFrom ,n.duplicatedPkId ,n.validDays
				,n.mediaAutostart ,n.AuditUserID,u.userName,u.fullName,g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId , t.code FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(1000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable ORDER BY '+Convert(nvarchar(100),@orderTypeD)	
	--查询中间页后面的页
	ELSE IF @currentPage > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @currentPage ) + ' n.pkId ,n.specialId ,n.gradeId ,n.gate ,n.departmentId ,n.listTitle ,n.intactTitle ,n.subheading
				,n.isRecommand ,n.isTop ,n.useListTitle ,n.showReviewLink ,n.Author ,n.AuthorMail ,n.CopyFrom ,n.copyFromLink
				,n.about ,n.pageType ,n.autoPageCharNum ,n.readPoint ,n.itemType ,n.usersId ,n.editor
				,n.click ,n.updateTime ,n.updateTime as ntime ,n.passed ,n.isReview ,n.isImageNews ,n.imageName ,n.width ,n.height
				,n.MenuId ,n.bigClassId ,n.updateIp ,n.useSubHeading ,n.isDuplicated ,n.duplicateFrom ,n.duplicatedPkId ,n.validDays
				,n.mediaAutostart ,n.AuditUserID,u.userName,u.fullName ,g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId, t.code FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(1000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable ORDER BY '+Convert(nvarchar(100),@orderTypeD)
	--查询中间页前面的页
 	ELSE BEGIN							
		SET @currentPage=@currentPage+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @currentPage ) + ' n.pkId ,n.specialId ,n.gradeId ,n.gate ,n.departmentId ,n.listTitle ,n.intactTitle ,n.subheading
				,n.isRecommand ,n.isTop ,n.useListTitle ,n.showReviewLink ,n.Author ,n.AuthorMail ,n.CopyFrom ,n.copyFromLink
				,n.about ,n.pageType ,n.autoPageCharNum ,n.readPoint ,n.itemType ,n.usersId ,n.editor
				,n.click ,n.updateTime ,n.updateTime as ntime ,n.passed ,n.isReview ,n.isImageNews ,n.imageName ,n.width ,n.height
				,n.MenuId ,n.bigClassId ,n.updateIp ,n.useSubHeading ,n.isDuplicated ,n.duplicateFrom ,n.duplicatedPkId ,n.validDays
				,n.mediaAutostart ,n.AuditUserID,u.userName,u.fullName,g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId, t.code FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(1000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeD)+') TempTable1 '
					+ ' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable2 ORDER BY '+Convert(nvarchar(100),@orderTypeD)
	END

	--执行查询
	EXEC(@sqlStr)
	if(@groupid=1)
	begin
		DELETE #test
	end
	else
	begin
		DELETE #test1
	end
RETURN



GO

if exists (select * from dbo.sysobjects where id = object_id(N'GetMyDraftByUserId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetMyDraftByUserId
GO
CREATE PROCEDURE GetMyDraftByUserId
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeId int,
	@userId int,
	@gateId int,
	@manageAll varchar(8000),
	@recordCount int output
	
AS	
	--转换成索引
	SELECT @currentPage = @currentPage - 1
	
	--初始化查询条件(不带where)
	DECLARE @str nvarchar(1000)
	--设置查询表名称
	DECLARE @tableName nvarchar(100)

	--判断当前用户是否属于用户组
	Declare @groupid int 
select @groupid = usersGroupId from users where pkid=@userId
if(@groupid=1)
begin

	--用临时表先读取授权的栏目和MenuName，然后在读取授权的新闻，
	--这样设计不会对news表中的menuId产生依赖，避免因栏目移动产生错误
	--根据栏目Id进行分组，以消除不用菜单位置的相同栏目产生的重复记录，20091010
	select b.pkId bigclassId, m.name menuName,max(m.pkId) sourceMenuId into #test from bigClass b, menu m, moduleAssign a where m.bigClassId=b.pkId and m.pkId=a.menuId and usersId=@userId group by b.pkId,m.name

	SET @str = ' 1=1 '
	SET @str = @str + ' and n.bigclassId = z.bigclassId'
	SET @str = @str + ' and z.bigclassId = b.pkid'
	SET @str = @str + ' and b.templateId = t.pkId'
	SET @str = @str + ' and n.passed=3'
	SET @str = @str + ' and n.usersId='+STR(@userId)
	SET @str = @str + ' and n.usersId=u.pkId and n.gradeId=g.pkId and n.departmentId=d.pkId ' --and n.MenuId=m.pkId 去掉了 fengyb 2008年-1-9日and n.MenuId=m.pkId 
	
	SET @tableName = '#test z, news n,users u,department d,grade g, template t, bigClass b '

end
else
begin
	
	select b.pkId bigclassId, m.name menuName,max(m.pkId) sourceMenuId into #test1 from bigClass b, menu m, groupAssign a where m.bigClassId=b.pkId and m.pkId=a.menuId and groupId=@groupid group by b.pkId,m.name

	SET @str = ' 1=1 '
	SET @str = @str + ' and n.bigclassId = z.bigclassId'
	SET @str = @str + ' and z.bigclassId = b.pkid'
	SET @str = @str + ' and b.templateId = t.pkId'
	SET @str = @str + ' and n.passed=3'
	SET @str = @str + ' and n.usersId='+STR(@userId)
	SET @str = @str + ' and n.usersId=u.pkId and n.gradeId=g.pkId and n.departmentId=d.pkId ' --and n.MenuId=m.pkId 去掉了 fengyb 2008年-1-9日and n.MenuId=m.pkId 

	SET @tableName = '#test1 z, news n,users u,department d,grade g, template t, bigClass b '
end

	--设置查询排序（不带order by）
	DECLARE @orderTypeA nvarchar(100)
	DECLARE @orderTypeD nvarchar(100)
	SET @orderTypeA = 'ntime ASC, sourceMenuId ASC, newsId ASC'
	SET @orderTypeD = 'ntime DESC, sourceMenuId DESC, newsId DESC'
	
	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(4000)
	
	SET @vSQL = N'SELECT @count = Count(n.pkId) from '+Convert(nvarchar(100),@tableName)+' where ' + Convert(nvarchar(1000),@str)
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum

	--计算页数
	SET @pageCount = (@recordCount +@pageSize-1)/@pageSize

	--构造查询语句
	DECLARE @sqlStr varchar(4000),@size int

	--判断分页大小，查询最后一页用
	SET @size = @recordCount%@pageSize	
	IF(@size=0)
		SET @size = @pageSize	
	
	--查询第一页
	IF (@currentPage = 0) OR (@currentPage > @pageCount-1)	
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' n.pkId ,n.specialId ,n.gradeId ,n.gate ,n.departmentId ,n.listTitle ,n.intactTitle ,n.subheading
				,n.isRecommand ,n.isTop ,n.useListTitle ,n.showReviewLink ,n.Author ,n.AuthorMail ,n.CopyFrom ,n.copyFromLink
				,n.about ,n.pageType ,n.autoPageCharNum ,n.readPoint ,n.itemType ,n.usersId ,n.editor
				,n.click ,n.updateTime ,n.updateTime as ntime ,n.passed ,n.isReview ,n.isImageNews ,n.imageName ,n.width ,n.height
				,n.MenuId ,n.bigClassId ,n.updateIp ,n.useSubHeading ,n.isDuplicated ,n.duplicateFrom ,n.duplicatedPkId ,n.validDays
				,n.mediaAutostart ,n.AuditUserID,u.userName,u.fullName,d.name "department" ,g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId , t.code FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(1000),@str)+' and b.pkId = n.bigClassId and b.templateId = t.pkId ORDER BY '+Convert(nvarchar(100),@orderTypeD)	
	--查询最后一页
	ELSE IF @currentPage = @pageCount -1			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' n.pkId ,n.specialId ,n.gradeId ,n.gate ,n.departmentId ,n.listTitle ,n.intactTitle ,n.subheading
				,n.isRecommand ,n.isTop ,n.useListTitle ,n.showReviewLink ,n.Author ,n.AuthorMail ,n.CopyFrom ,n.copyFromLink
				,n.about ,n.pageType ,n.autoPageCharNum ,n.readPoint ,n.itemType ,n.usersId ,n.editor
				,n.click ,n.updateTime ,n.updateTime as ntime ,n.passed ,n.isReview ,n.isImageNews ,n.imageName ,n.width ,n.height
				,n.MenuId ,n.bigClassId ,n.updateIp ,n.useSubHeading ,n.isDuplicated ,n.duplicateFrom ,n.duplicatedPkId ,n.validDays
				,n.mediaAutostart ,n.AuditUserID,u.userName,u.fullName,d.name "department" ,g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId , t.code FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(1000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable ORDER BY '+Convert(nvarchar(100),@orderTypeD)	
	--查询中间页后面的页
	ELSE IF @currentPage > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @currentPage ) + ' n.pkId ,n.specialId ,n.gradeId ,n.gate ,n.departmentId ,n.listTitle ,n.intactTitle ,n.subheading
				,n.isRecommand ,n.isTop ,n.useListTitle ,n.showReviewLink ,n.Author ,n.AuthorMail ,n.CopyFrom ,n.copyFromLink
				,n.about ,n.pageType ,n.autoPageCharNum ,n.readPoint ,n.itemType ,n.usersId ,n.editor
				,n.click ,n.updateTime ,n.updateTime as ntime ,n.passed ,n.isReview ,n.isImageNews ,n.imageName ,n.width ,n.height
				,n.MenuId ,n.bigClassId ,n.updateIp ,n.useSubHeading ,n.isDuplicated ,n.duplicateFrom ,n.duplicatedPkId ,n.validDays
				,n.mediaAutostart ,n.AuditUserID,u.userName,u.fullName,d.name "department" ,g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId, t.code FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(1000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable ORDER BY '+Convert(nvarchar(100),@orderTypeD)
	--查询中间页前面的页
 	ELSE BEGIN							
		SET @currentPage=@currentPage+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @currentPage ) + ' n.pkId ,n.specialId ,n.gradeId ,n.gate ,n.departmentId ,n.listTitle ,n.intactTitle ,n.subheading
				,n.isRecommand ,n.isTop ,n.useListTitle ,n.showReviewLink ,n.Author ,n.AuthorMail ,n.CopyFrom ,n.copyFromLink
				,n.about ,n.pageType ,n.autoPageCharNum ,n.readPoint ,n.itemType ,n.usersId ,n.editor
				,n.click ,n.updateTime ,n.updateTime as ntime ,n.passed ,n.isReview ,n.isImageNews ,n.imageName ,n.width ,n.height
				,n.MenuId ,n.bigClassId ,n.updateIp ,n.useSubHeading ,n.isDuplicated ,n.duplicateFrom ,n.duplicatedPkId ,n.validDays
				,n.mediaAutostart ,n.AuditUserID,u.userName,u.fullName,d.name "department" ,g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId, t.code FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(1000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeD)+') TempTable1 '
					+ ' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable2 ORDER BY '+Convert(nvarchar(100),@orderTypeD)
	END
	
	--执行查询
	EXEC(@sqlStr)
	if(@groupid=1)
	begin
		DELETE #test
	end
	else
	begin
		DELETE #test1
	end
RETURN


GO


--微信推送
if exists (select * from dbo.sysobjects where id = object_id(N'DeletePushNews') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure DeletePushNews
GO
CREATE PROCEDURE DeletePushNews
(
	@pkid int
)
AS
	SET NOCOUNT OFF;
DELETE FROM PushNews WHERE (pkid = @pkid) 

GO

if exists (select * from dbo.sysobjects where id = object_id(N'GetPushNewsByNewsId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetPushNewsByNewsId
GO
CREATE PROCEDURE GetPushNewsByNewsId    
    (    
		@type varchar(10),   
		@newsid int
     )    
     AS    
 SELECT * FROM pushNews WHERE (type=@type and newsid=@newsid and state='未推送')

GO

if exists (select * from dbo.sysobjects where id = object_id(N'GetPushNewsByType') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetPushNewsByType
GO
CREATE PROCEDURE GetPushNewsByType  
    (    
		@type varchar(10)
     )    
     AS    
 SELECT * FROM pushNews WHERE (type=@type and state='未推送')

GO



if exists (select * from dbo.sysobjects where id = object_id(N'InsertPushNews') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure InsertPushNews
GO
CREATE PROCEDURE InsertPushNews
(
	@pkId int output,
	@type varchar(10),
	@newsid int,
	@auther varchar(100),
	@intactTitle varchar(100),
	@content text,
	@descript varchar(1000),
	@imageUrl varchar(100),
	@Url varchar(100),
	@isCover int,
	@pushTime datetime,
	@state varchar(200)
)
AS
	SET NOCOUNT OFF;	 
    BEGIN TRANSACTION

INSERT INTO pushNews(
	type, 
	newsid, 
	auther, 
	intactTitle, 
	content, 
	descript, 
	imageUrl, 
	Url, 
	isCover, 
	pushTime, 
	state) 
VALUES (
	@type, 
	@newsid, 
	@auther, 
	@intactTitle, 
	@content, 
	@descript, 
	@imageUrl, 
	@Url, 
	@isCover, 
	@pushTime, 
	@state)

SET @pkId = @@IDENTITY

--UPDATE pushNews SET Priority=@pkid  WHERE pkid=@pkid
COMMIT TRANSACTION

GO



if exists (select * from dbo.sysobjects where id = object_id(N'UpDatePushNews') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure UpDatePushNews
GO
CREATE PROCEDURE UpDatePushNews
(
	@pkId int output,
	@type varchar(10),
	@newsid int,
	@auther varchar(100),
	@intactTitle varchar(100),
	@content text,
	@descript varchar(1000),
	@imageUrl varchar(100),
	@Url varchar(100),
	@isCover int,
	@pushTime datetime,
	@state varchar(200)
)
AS
	SET NOCOUNT OFF;

UPDATE pushNews SET type=@type,newsid=@newsid,auther = @auther, intactTitle = @intactTitle, content = @content, descript = @descript, imageUrl = @imageUrl, Url = @Url, isCover = @isCover, pushTime = @pushTime, state = @state   WHERE (newsid = @newsid and type=@type)


GO


if exists (select * from dbo.sysobjects where id = object_id(N'UpdateStat') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure UpdateStat
GO
CREATE PROCEDURE UpdateStat
AS
	SET NOCOUNT OFF;
declare @num int
select @num=count(*) from stat where pvdate=convert(varchar(10),getdate(),120)
if(@num=0)
insert into stat ([time],[pvdate])values(1,convert(varchar(10),getdate(),120))
UPDATE stat SET [time] = [time]+1 where pvdate=convert(varchar(10),getdate(),120)


GO


--xuyn

if exists (select * from dbo.sysobjects where id = object_id(N'GetNewsByAlbumId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetNewsByAlbumId
GO
CREATE PROCEDURE GetNewsByAlbumId   
     (    
        @albumid int=null,
@currentPage int=1,
@pageSize int=5,
@gradeid int=1,
@gateId int,
@searchStr varchar(100) = '',
@recordCount int output  
     )    
     AS    
 BEGIN

	SET @currentPage = @currentPage - 1
	IF (@currentPage < 0)
		BEGIN
			SET @currentPage = 1
		END
	-- Set the page bounds
    DECLARE @PageLowerBound INT
    DECLARE @PageUpperBound INT
    DECLARE @TotalRecords   INT
    SET @PageLowerBound = @PageSize * @currentPage
    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
	
	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(4000)
	
	SET @vSQL = N'SELECT @count = Count(n.pkId) from 
			[News] AS n 
			INNER JOIN [Users] AS u ON n.UsersID = u.PKID
			INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
			inner join [grade] as g on n.gradeId=g.pkId
			WHERE (n.gate > ' + STR(@gateId) + ' OR n.gate < ' + STR

(@gateId) + ') and   
			n.albumid = ' + STR(@albumid) +' and 
			(n.passed = 0 or n.passed = 1 or n.passed = 2) and 
			n.gradeId <= ' + STR(@gradeId) + ' ' +
			@searchStr 

	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum
	
	DECLARE @sql nvarchar(4000)
 
    SET @sql = '
	
	CREATE TABLE #PageIndexs
    (
        IndexId int IDENTITY (0, 1) NOT NULL,
        ID int
    );

    
    INSERT	INTO #PageIndexs (ID)
	SELECT	n.pkId
	FROM	[News] AS n 
			INNER JOIN [Users] AS u ON n.UsersID = u.PKID
			INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
			inner join [grade] as g on n.gradeId=g.pkId
	WHERE	(n.gate > ' + STR(@gateId) + ' OR n.gate < ' + STR(@gateId) + ') and  

 
			n.albumid = ' + STR(@albumid) +' and 
			(n.passed = 0 or n.passed = 1 or n.passed = 2) and 
			n.gradeId <= ' + STR(@gradeId) + ' ' +
			@searchStr + ' ' +
	'ORDER BY n.istop desc,n.Priority desc,n.updateTime desc,n.pkid desc;

	SELECT  n.pkId
	,n.specialId
	,n.gradeId
	,n.gate
	,n.departmentId
	,n.listTitle
	,n.intactTitle
	,n.subheading
	,n.isRecommand
	,n.isTop
	,n.useListTitle
	,n.showReviewLink
	,n.Author
	,n.AuthorMail
	,n.CopyFrom
	,n.copyFromLink
	,n.about
	,n.pageType
	,n.autoPageCharNum
	,n.readPoint
	,n.itemType
	,n.descript
	,n.usersId
	,n.editor
	,n.click
	,n.updateTime
	,n.passed
	,n.isReview
	,n.isImageNews
	,n.imageName
	,n.width
	,n.height
	,n.MenuId
	,n.bigClassId
	,n.updateIp
	,n.useSubHeading
	,n.isDuplicated
	,n.duplicateFrom
	,n.duplicatedPkId
	,n.validDays
	,n.mediaAutostart
	,n.AuditUserID
	,n.Priority,n.isStatic,n.staticUrl,n.staticTime,n.albumid, 

u.UserName,u.fullName, d.name as Department,g.name as grade
	FROM	[News] AS n
			INNER JOIN [Users] AS u ON n.UsersID = u.PKID
			INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
			INNER JOIN [grade] as g on n.gradeId=g.pkId
			INNER JOIN [#PageIndexs] AS p ON n.PKID = p.ID
	WHERE	p.IndexId >= ' + STR(@PageLowerBound) + ' AND p.IndexId <= ' + STR

(@PageUpperBound) + ' 
	and (n.passed = 0 or n.passed = 1 or n.passed = 2) 
	ORDER BY n.istop desc,n.Priority desc,n.updateTime desc,n.pkid desc

	DELETE #PageIndexs'

	EXEC(@sql)
	
	RETURN 
END

GO

--xuyn
if exists (select * from dbo.sysobjects where id = object_id(N'GetNewsByAlbumIdAndUsersId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetNewsByAlbumIdAndUsersId
GO
CREATE PROCEDURE GetNewsByAlbumIdAndUsersId
     (    
       @albumid int=null,
@currentPage int=1,
@pageSize int=5,
@gradeid int=1,
@usersId int,
@gateId int,
@searchStr varchar(100) = '',
@recordCount int output
     )    
     AS    
 BEGIN

	SET @currentPage = @currentPage - 1
	IF (@currentPage < 0)
		BEGIN
			SET @currentPage = 1
		END
	-- Set the page bounds
    DECLARE @PageLowerBound INT
    DECLARE @PageUpperBound INT
    DECLARE @TotalRecords   INT
    SET @PageLowerBound = @PageSize * @currentPage
    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
	
	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(4000)
	
	SET @vSQL = N'SELECT @count = Count(n.pkId) from 
			[News] AS n 
			INNER JOIN [Users] AS u ON n.UsersID = u.PKID
			INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
			inner join [grade] as g on n.gradeId=g.pkId
			WHERE (n.gate > ' + STR(@gateId) + ' OR n.gate < ' + STR(@gateId) + ') and   
			n.albumid = ' + STR(@albumid) +' and 
			(n.passed = 0 or n.passed = 1 or n.passed = 2) and 
			n.usersId = '+ STR(@usersId) +' and 
			n.gradeId <= ' + STR(@gradeId) + ' ' +
			@searchStr 

	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum
	
	DECLARE @sql nvarchar(4000)
    
    SET @sql = '
	-- Create a temp table TO store the select results
	CREATE TABLE #PageIndexs
    (
        IndexId int IDENTITY (0, 1) NOT NULL,
        ID int
    );

    -- Insert into our temp table
    INSERT	INTO #PageIndexs (ID)
	SELECT	n.pkId
	FROM	[News] AS n 
			INNER JOIN [Users] AS u ON n.UsersID = u.PKID
			INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
			INNER JOIN [grade] AS g ON n.gradeId = g.pkId
			
	WHERE	(n.gate > ' + STR(@gateId) + ' OR n.gate < ' + STR(@gateId) + ') and   
			n.albumid = ' + STR(@albumid) +' and 
			(n.passed = 0 or n.passed = 1 or n.passed = 2) and 
			n.usersId = '+ STR(@usersId) +' and 
			n.gradeId <= ' + STR(@gradeId) + ' ' +
			@searchStr + ' ' +
	'ORDER BY n.istop DESC,n.Priority desc,n.updateTime desc, n.pkId DESC;

	SELECT  n.pkId
	,n.specialId
	,n.gradeId
	,n.gate
	,n.departmentId
	,n.listTitle
	,n.intactTitle
	,n.subheading
	,n.isRecommand
	,n.isTop
	,n.useListTitle
	,n.showReviewLink
	,n.Author
	,n.AuthorMail
	,n.CopyFrom
	,n.copyFromLink
	,n.about
	,n.pageType
	,n.autoPageCharNum
	,n.readPoint
	,n.itemType
	,n.descript
	,n.usersId
	,n.editor
	,n.click
	,n.updateTime
	,n.passed
	,n.isReview
	,n.isImageNews
	,n.imageName
	,n.width
	,n.height
	,n.MenuId
	,n.bigClassId
	,n.updateIp
	,n.useSubHeading
	,n.isDuplicated
	,n.duplicateFrom
	,n.duplicatedPkId
	,n.validDays
	,n.mediaAutostart
	,n.AuditUserID
	,n.Priority,n.isStatic,n.staticUrl,n.staticTime,n.albumid, u.UserName,u.fullName, d.name as Department,g.name as grade
	FROM	[News] AS n
			INNER JOIN [Users] AS u ON n.UsersID = u.PKID
			INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
			INNER JOIN [grade] AS g ON n.gradeId = g.pkId
			INNER JOIN [#PageIndexs] AS p ON n.PKID = p.ID
	WHERE	p.IndexId >= ' + STR(@PageLowerBound) + ' AND p.IndexId <= ' + STR(@PageUpperBound) + ' 
	and (n.passed = 0 or n.passed = 1 or n.passed = 2) 
	ORDER BY n.istop DESC,n.Priority desc,n.updateTime desc, n.pkId DESC

	DELETE #PageIndexs'

	EXEC(@sql)
	
	RETURN 
END

GO

--xuyn
if exists (select * from dbo.sysobjects where id = object_id(N'GetNewsByBigClassIdAndPassed') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetNewsByBigClassIdAndPassed
GO
CREATE PROCEDURE GetNewsByBigClassIdAndPassed
@gradeid int=1,
@gateId int,
@bigClassId int=null,
@currentPage int=1,
@pageSize int=5,
@passed int=1
AS
BEGIN


	SET @currentPage = @currentPage - 1
	IF (@currentPage < 0)
		BEGIN
			SET @currentPage = 1
		END
	-- Set the page bounds
    DECLARE @PageLowerBound INT
    DECLARE @PageUpperBound INT
    DECLARE @TotalRecords   INT
    SET @PageLowerBound = @PageSize * @currentPage
    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound

    -- Create a temp table TO store the select results
    CREATE TABLE #PageIndexs
    (
        IndexId int IDENTITY (0, 1) NOT NULL,
        ID int
    )

    if(@currentPage=0) --优化第一页访问速度
	begin
		-- Insert into our temp table
		INSERT	INTO #PageIndexs (ID)
		SELECT	TOP (@pageSize) n.pkId
		FROM	[News] AS n 
				INNER JOIN [Users] AS u ON n.UsersID = u.PKID
				INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
		WHERE	(n.gate > @gateId OR n.gate < @gateId) and   
				bigClassId =		@bigClassId and 
				n.gradeId <=		@gradeId and 
				n.passed =			@passed and 
				(n.validDays > 0 OR n.validDays < 0)
		ORDER BY n.istop desc,n.Priority desc,n.updateTime desc,n.pkid desc
	end
	else
	begin
    	-- Insert into our temp table
    	INSERT	INTO #PageIndexs (ID)
		SELECT	n.pkId
		FROM	[News] AS n 
				INNER JOIN [Users] AS u ON n.UsersID = u.PKID
				INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
		WHERE	(n.gate > @gateId OR n.gate < @gateId) and   
				bigClassId =		@bigClassId and 
				n.gradeId <=		@gradeId and 
				n.passed =			@passed and 
				(n.validDays > 0 OR n.validDays < 0)
		ORDER BY n.istop desc,n.Priority desc,n.updateTime desc,n.pkid desc
	end

	SELECT n.pkId ,n.specialId ,n.gradeId ,n.gate ,n.departmentId ,n.listTitle ,n.intactTitle ,n.subheading
				,n.isRecommand ,n.isTop ,n.useListTitle ,n.showReviewLink ,n.Author ,n.AuthorMail ,n.CopyFrom ,n.copyFromLink
				,n.about ,n.pageType ,n.autoPageCharNum ,n.readPoint ,n.itemType ,n.descript ,n.usersId ,n.editor
				,n.click ,n.updateTime ,n.passed ,n.isReview ,n.isImageNews ,n.imageName ,n.width ,n.height
				,n.MenuId ,n.bigClassId ,n.updateIp ,n.useSubHeading ,n.isDuplicated ,n.duplicateFrom ,n.duplicatedPkId ,n.validDays
				,n.mediaAutostart ,n.AuditUserID,n.isStatic,n.staticUrl,n.staticTime, u.UserName, d.name as Department
	FROM	[News] AS n
			INNER JOIN [Users] AS u ON n.UsersID = u.PKID
			INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
			INNER JOIN [#PageIndexs] AS p ON n.PKID = p.ID
	WHERE	p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
	ORDER BY n.istop desc,n.Priority desc,n.updateTime desc,n.pkid desc

	DELETE #PageIndexs
	RETURN 
END

GO


--增加用户密码等级字段
if exists (select * from dbo.sysobjects where id = object_id(N'InsertUsers') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure InsertUsers
GO
CREATE PROCEDURE InsertUsers
(
	@userName varchar(100),
	@fullName varchar(100),
	@password varchar(100),
	@question varchar(100),
	@answer varchar(100),
	@sex varchar(10),
	@born datetime,
	@QQ varchar(100),
	@email varchar(100),
	@intro varchar(100),
	@loginIp varchar(100),
	@loginNum int,
	@loginTime datetime,
	@logIp varchar(100),
	@logTime datetime,
	@tel varchar(100),
	@photo varchar(100),
	@examine int,
	@gradeId int,
	@departmentId int,
	@company varchar(100),
	@appear int,
	@networkTest int,
	@Auditing int,
	@manageAll int,
	@usersGroupId int,
	@readInfoType int,
	@topRight int,
	@recommandRight int,
	@isSbwUser int,
	@isDel int,
	@pwdlevel int,
	@pkId int output
)
AS
SET NOCOUNT OFF;
	
	BEGIN TRANSACTION
	declare @count int
    declare @point int
    declare @days int
	
	select @gradeId=1
	select @topRight=0
	select @recommandRight=0
	select @count=Count(*) from users
	where userName=@userName
if @count!=0
 return 
	select @examine=pvalue from sitepara where ptype='RegUser' and pcode='examine'
 
INSERT INTO Users(userName, fullName, password, question, answer, sex, born, QQ, email, intro, 
loginIp, loginNum, loginTime, logIp, logTime, tel, photo, examine, gradeId, departmentId,
company, appear, networkTest, Auditing, manageAll, usersGroupId, readInfoType, topRight,
recommandRight, isSbwUser,isDel,pwdlevel) VALUES (@userName, @fullName, @password, @question, @answer, @sex, @born, @QQ,
@email, @intro, @loginIp, @loginNum, @loginTime, @logIp, @logTime, @tel, @photo, @examine, 
@gradeId, @departmentId, @company, @appear, @networkTest, @Auditing, @manageAll,
@usersGroupId, @readInfoType, @topRight, @recommandRight, @isSbwUser,@isDel,@pwdlevel)
select @pkId=@@IDENTITY

INSERT INTO moduleAssign (usersId,menuId,privilegeSet) SELECT usersId=@pkId,menuId,privilegeSet FROM moduleAssign WHERE usersId=-1

COMMIT TRANSACTION




GO




if exists (select * from dbo.sysobjects where id = object_id(N'UpdateUsers') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure UpdateUsers
GO
CREATE PROCEDURE UpdateUsers
(
	@userName varchar(100),
	@fullName varchar(100),
	@password varchar(100),
	@question varchar(100),
	@answer varchar(100),
	@sex varchar(10),
	@born datetime,
	@QQ varchar(100),
	@email varchar(100),
	@intro varchar(100),
	@loginIp varchar(100),
	@loginNum int,
	@loginTime datetime,
	@logIp varchar(100),
	@logTime datetime,
	@tel varchar(100),
	@photo varchar(100),
	@examine int,
	@gradeId int,
	@departmentId int,
	@company varchar(100),
	@appear int,
	@networkTest int,
	@Auditing int,
	@manageAll int,
	@usersGroupId int,
	@readInfoType int,
	@topRight int,
	@recommandRight int,
	@pkId int,
	@isSbwUser int,
	@isDel int,
	@pwdlevel int
)
AS
	SET NOCOUNT OFF;
UPDATE Users SET isDel=@isDel, userName = @userName, fullName = @fullName, password = @password, question = @question, answer = @answer, sex = @sex, born = @born, QQ = @QQ, email = @email, intro = @intro, loginIp = @loginIp, loginNum = @loginNum, loginTime = @loginTime, logIp = @logIp, logTime = @logTime, tel = @tel, photo = @photo, examine = @examine, gradeId = @gradeId, departmentId = @departmentId, company = @company, appear = @appear, networkTest = @networkTest, Auditing = @Auditing, manageAll = @manageAll, usersGroupId = @usersGroupId, readInfoType = @readInfoType, topRight = @topRight, recommandRight = @recommandRight, isSbwUser=@isSbwUser, pwdlevel=@pwdlevel WHERE (pkId = @pkId)


GO


--xuyn
if exists (select * from dbo.sysobjects where id = object_id(N'GetNewsByAlbumIdAndPassed') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetNewsByAlbumIdAndPassed
GO
CREATE PROCEDURE GetNewsByAlbumIdAndPassed
     (    
       @gradeid int=1,
@gateId int,
@albumid int=null,
@currentPage int=1,
@pageSize int=5,
@passed int=1
     )    
     AS    
 BEGIN


	SET @currentPage = @currentPage - 1
	IF (@currentPage < 0)
		BEGIN
			SET @currentPage = 1
		END
	-- Set the page bounds
    DECLARE @PageLowerBound INT
    DECLARE @PageUpperBound INT
    DECLARE @TotalRecords   INT
    SET @PageLowerBound = @PageSize * @currentPage
    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound

    -- Create a temp table TO store the select results
    CREATE TABLE #PageIndexs
    (
        IndexId int IDENTITY (0, 1) NOT NULL,
        ID int
    )

    if(@currentPage=0) --优化第一页访问速度
	begin
		-- Insert into our temp table
		INSERT	INTO #PageIndexs (ID)
		SELECT	TOP (@pageSize) n.pkId
		FROM	[News] AS n 
				INNER JOIN [Users] AS u ON n.UsersID = u.PKID
				INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
		WHERE	(n.gate > @gateId OR n.gate < @gateId) and   
				albumid =		@albumid and 
				n.gradeId <=		@gradeId and 
				n.passed =			@passed and 
				(n.validDays > 0 OR n.validDays < 0)
		ORDER BY n.istop desc,n.Priority desc,n.updateTime desc,n.pkid desc
	end
	else
	begin
    	-- Insert into our temp table
    	INSERT	INTO #PageIndexs (ID)
		SELECT	n.pkId
		FROM	[News] AS n 
				INNER JOIN [Users] AS u ON n.UsersID = u.PKID
				INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
		WHERE	(n.gate > @gateId OR n.gate < @gateId) and   
				albumid =		@albumid and 
				n.gradeId <=		@gradeId and 
				n.passed =			@passed and 
				(n.validDays > 0 OR n.validDays < 0)
		ORDER BY n.istop desc,n.Priority desc,n.updateTime desc,n.pkid desc
	end

	SELECT n.pkId ,n.specialId ,n.gradeId ,n.gate ,n.departmentId ,n.listTitle ,n.intactTitle ,n.subheading
				,n.isRecommand ,n.isTop ,n.useListTitle ,n.showReviewLink ,n.Author ,n.AuthorMail ,n.CopyFrom ,n.copyFromLink
				,n.about ,n.pageType ,n.autoPageCharNum ,n.readPoint ,n.itemType ,n.descript ,n.usersId ,n.editor
				,n.click ,n.updateTime ,n.passed ,n.isReview ,n.isImageNews ,n.imageName ,n.width ,n.height
				,n.MenuId ,n.bigClassId ,n.updateIp ,n.useSubHeading ,n.isDuplicated ,n.duplicateFrom ,n.duplicatedPkId ,n.validDays
				,n.mediaAutostart ,n.AuditUserID,n.isStatic,n.staticUrl,n.staticTime,n.albumId, u.UserName, d.name as Department
	FROM	[News] AS n
			INNER JOIN [Users] AS u ON n.UsersID = u.PKID
			INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
			INNER JOIN [#PageIndexs] AS p ON n.PKID = p.ID
	WHERE	p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
	ORDER BY n.istop desc,n.Priority desc,n.updateTime desc,n.pkid desc

	DELETE #PageIndexs
	RETURN 
END

GO

--xuyn
if exists (select * from dbo.sysobjects where id = object_id(N'GetStat') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetStat
GO
CREATE PROCEDURE GetStat
AS
	 SET NOCOUNT ON 
	 select sum(time)+(select pvalue from sitepara where pcode='stattime') as pvtime from stat

RETURN 

GO

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

--修改栏目管理中显示错误，将admin表改为users表
if exists (select * from dbo.sysobjects where id = object_id(N'GetBigClass') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetBigClass
GO

CREATE PROCEDURE GetBigClass
AS
SET NOCOUNT ON;
SELECT b.*, t.name 'template',t.code,t.previewTemplate,a.username as adminName, tp.type, tp.pkid as typeid 
       
FROM bigClass b,template t,users a, templateType tp
where b.templateId=t.pkId and b.adminId=a.pkId and tp.pkId = t.typeId order by b.isSystemItem desc, b.pkId

GO




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

CREATE PROCEDURE GetBigClassByAdminId
@adminId int
AS
	SET NOCOUNT ON;
	SELECT b.*, t.name 'template',t.code,t.previewTemplate,a.username as adminName, tp.type, tp.pkid as typeid 
       
FROM bigClass b,template t,users a, templateType tp
where 
   b.templateId=t.pkId
   and b.isSystemItem=1
   and b.adminId=a.pkId
   and t.typeId=tp.pkId
   
UNION
SELECT b.*, t.name 'template',t.code,t.previewTemplate,a.username as adminName, tp.type, tp.pkId
       
FROM bigClass b,template t,users a, templateType tp
where 
   b.templateId=t.pkId
   and b.isSystemItem=0
   and b.adminId=@adminId
   and b.adminId=a.pkId
   and t.typeId=tp.pkId
   
  order by b.isSystemItem desc,t.name, tp.pkId, b.pkId



GO



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

CREATE PROCEDURE GetBigClassByPkId
@pkId int=null
AS
	/* SET NOCOUNT ON */
	SELECT b.*,
       t.name 'template',
       t.code 'code',t.previewTemplate,a.username as adminName, tp.pkId as typeId	
       
FROM bigClass b,template t,users a, templateType tp
where b.pkId=@pkId
  
  and b.templateId=t.pkId
  and a.pkId=b.adminId
  and t.typeId = tp.pkId
	RETURN 



GO


--解决站点公告不显示的问题
if exists (select * from dbo.sysobjects where id = object_id(N'GetBoardByBigClassIdAndPassed') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetBoardByBigClassIdAndPassed
GO

CREATE PROCEDURE GetBoardByBigClassIdAndPassed
@bigClassId int=null,
@gradeid int=1,
@gateId int
AS
	SET NOCOUNT ON
	
	select b.*,u.userName 'userName', g.name 'grade'
	 from board b,users u,grade g
	where  b.gradeId<=@gradeId
	 and  b.passed=1
	 
	 and gate<>@gateId
	 and @bigClassId=bigClassId
	 and b.userId=u.pkId
	  and @gradeId=g.pkId
	order by IsTop desc,b.pkId desc
	RETURN 


GO




--修正更换布局中的栏目时，导致用户组授权出错的bug
if exists (select * from dbo.sysobjects where id = object_id(N'DeleteMenuByParentIdAndBigClassId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure DeleteMenuByParentIdAndBigClassId
GO
CREATE PROCEDURE DeleteMenuByParentIdAndBigClassId
(
	@parentId int,
	@BigClassId int
)
AS
	SET NOCOUNT OFF;
BEGIN TRANSACTION
declare @pkId int
select @pkId=pkId from menu where parentId=@parentId and bigClassId=@bigClassId
delete from moduleAssign where menuId=@pkId
delete from groupAssign where menuId=@pkId
delete from artDesign where menuId=@pkId
delete from bigclassDesign where menuId=@pkId
DELETE FROM menu WHERE parentId=@parentId and bigClassId=@bigClassId and modeId='4'
COMMIT TRANSACTION

GO

--删除布局时同步删除bigClassDesign表中对应记录
if exists (select * from dbo.sysobjects where id = object_id(N'DeleteMenu') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure DeleteMenu
GO
CREATE PROCEDURE DeleteMenu
(
@pkId int
)
as
 BEGIN TRANSACTION
	declare @mpkId int
	declare @count int
	declare @npkId int
	set @npkId=@pkId
	set @mpkId=-1
	select  @count=Count(*) from menu where  @pkId=parentId
	while(@count<>0 or @mpkId<>@pkId)
	begin
		SELECT top 1 @mpkId=pkId
		FROM menu
		WHERE @npkId=parentId
		select  @count=Count(*) from menu where  @pkId=parentId
		if(@count=0)
		begin
			break;
		end
		select @count=Count(*) from menu where  @mpkId=parentId
		set @npkId=@mpkId
		if(@count>0)
		begin
			continue
		end
		else
		begin
			delete from contributeClass where contributeClassId= @mpkId	-- edit contributeClass
			delete from moduleAssign where @mpkId=menuId
			delete from groupAssign where @mpkId=menuId --20141226 处理用户组授权bug
			delete from artDesign where @mpkId=menuId
			delete from menu where @mpkId=pkId  
			delete from bigClassDesign where menuId=@mpkId --edit bigclassDesign
			set @npkId=@pkId  
		end
	 END	 
	delete from BigClassAssociation where menuId = @pkId 
	delete from contributeClass where contributeClassId= @pkId	-- edit contributeClass
	delete from moduleAssign where @pkId=menuId
	delete from groupAssign where @pkId=menuId --20141226 处理用户组授权bug
	delete from artDesign where @pkId=menuId
	delete from menu where @pkId=pkId   
	delete from bigClassDesign where menuId=@pkId --edit bigclassDesign
 COMMIT TRANSACTION 
 return

GO


--修正用户组抄送权限问题
if exists (select * from dbo.sysobjects where id = object_id(N'GetDuplicateBigClassByUserIdAndTemplateId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetDuplicateBigClassByUserIdAndTemplateId
GO
create PROCEDURE GetDuplicateBigClassByUserIdAndTemplateId
(
	@userId int,
	@templateId int
)
AS

	--判断当前用户是否属于用户组
	Declare @groupid int 
	select @groupid = usersGroupId from users where pkid=@userId
	if(@groupid=1)
	begin
	--用户权限
	select distinct b.pkId, b.name, b.templateId as templateId, t.name as templateName from bigClass b, moduleAssign m, users u, menu e, template t
	where m.usersid = u.pkId and m.menuId=e.pkId and e.bigClassId=b.pkId and b.templateId = t.pkId 
		and b.templateId=@templateId and u.pkId = @userId
	order by b.pkId

	end
	else
	begin
	--用户组权限
	select distinct b.pkId, b.name, b.templateId as templateId, t.name as templateName from bigClass b, groupAssign m, users u, menu e, template t
	where m.groupid = u.usersGroupId and m.menuId=e.pkId and e.bigClassId=b.pkId and b.templateId = t.pkId 
		and b.templateId=@templateId and u.pkId = @userId
	order by b.pkId
	end

GO





--徐垚楠
if exists (select * from dbo.sysobjects where id = object_id(N'DeleteUsers') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure DeleteUsers
GO
create PROCEDURE DeleteUsers
(
	@pkId int,
	@rtn varchar(100) output
)
AS
	SET NOCOUNT OFF;
--V522：删除用户时没判断，直接把news、board等9表数据删除。易导致删除一人就删除网站大部分数据的后果
--V6.0和201410之前的V6.1：软删除方式，设置users.isDel=1，同时不再删除news等9表数据。且只能手动恢复，
--坏处：导致1，发文统计仍有被删除的用户；导致2：无法再次注册该用户名。
--V6.1：判断和提示应删除哪些数据，方可删除用户；另外，本功能结合禁止登录功能使用，足以满足各种需求。
--好处：可删除真正应该删除的用户，如随便建的测试帐号、长时间不用且发文很少的用户。

DECLARE @count int
--news
SELECT @count=Count(pkId) FROM news WHERE usersid=@pkId
if( @count >0)
begin
set @rtn='news'
return
end
--messageBoard
SELECT @count=Count(pkId) FROM messageBoard WHERE userId=@pkId
if( @count >0)
begin
set @rtn='messageboard'
return
end
--link
SELECT @count=Count(pkId) FROM link WHERE userId=@pkId
if( @count >0)
begin
set @rtn='link'
return
end
--linkGroup
SELECT @count=Count(pkId) FROM linkGroup WHERE userId=@pkId
if( @count >0)
begin
set @rtn='linkgroup'
return
end
--linkGang
SELECT @count=Count(pkId) FROM linkGang WHERE userId=@pkId
if( @count >0)
begin
set @rtn='linkgang'
return
end
--ad
SELECT @count=Count(pkId) FROM ad WHERE userId=@pkId
if( @count >0)
begin
set @rtn='ad'
return
end
--people
SELECT @count=Count(pkId) FROM people WHERE userId=@pkId
if( @count >0)
begin
set @rtn='people'
return
end

--board
SELECT @count=Count(pkId) FROM board WHERE userId=@pkId
if( @count >0)
begin
set @rtn='board'
return
end
--popwindow
SELECT @count=Count(pkId) FROM popwindow WHERE userId=@pkId
if( @count >0)
begin
set @rtn='popwindow'
return
end

--bigclass  v7.0新增判断bigclass表中有无数据
SELECT @count=Count(pkId) FROM bigclass WHERE adminid=@pkId
if( @count >0)
begin
set @rtn='bigclass'
return
end

 BEGIN TRANSACTION
--以下注释V522版遗留问题:riches有些V6.x已删除
begin
IF  EXISTS (SELECT name from sysobjects where  type='U' and name='Riches')
DELETE FROM riches where (usersId=@pkId);   --riches为财富表，有FK与usersid关联,本表V6.1一般已删除
end
--DELETE FROM news WHERE (usersId = @pkId)
--DELETE FROM popwindow WHERE (userId = @pkId)
--DELETE FROM board where (userId=@pkId)
--DELETE FROM link WHERE (userId = @pkId)
--DELETE FROM people WHERE (userId = @pkId)
--DELETE FROM ad WHERE (userId = @pkId)
--DELETE FROM linkGang WHERE (userId=@pkId)
--DELETE FROM linkGroup WHERE (userId=@pkId)
--DELETE FROM messageBoard WHERE (userId=@pkId)
--update Users set isDel='1' WHERE (pkId = @pkId)
--6.0版本为软删除方式，6.1版本修改为真实删除。
DELETE FROM moduleAssign where(usersId=@pkId);
DELETE FROM Users WHERE (pkId = @pkId);
set @rtn='success'
 COMMIT TRANSACTION

GO

--增加事物控制，增加删除newscontent表中数据的功能
if exists (select * from dbo.sysobjects where id = object_id(N'DeleteNews') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure DeleteNews
GO
CREATE PROCEDURE DeleteNews
(
	@pkId int
)
AS
	SET NOCOUNT OFF;

	BEGIN TRY 	 
	BEGIN TRANSACTION;

	DECLARE @duplicatedId int

	-- 查询被抄送新闻并删除其对应评论
	DECLARE myCursor CURSOR FOR 
	SELECT pkId FROM news WHERE duplicatedPkId=@pkId
	OPEN myCursor

	FETCH NEXT FROM myCursor 
	INTO @duplicatedId
	WHILE @@FETCH_STATUS = 0
	BEGIN
		DELETE FROM review WHERE (newsId=@duplicatedId)
		FETCH NEXT FROM myCursor INTO @duplicatedId
	END
	CLOSE myCursor
	DEALLOCATE myCursor

-- 从新闻、公告中删除抄送新闻
	DELETE FROM newsContent WHERE newsId in(select pkid from news where duplicatedPkId=@pkId)   --删除抄送新闻content
	DELETE FROM news WHERE (duplicatedPkId=@pkId)--删除抄送新闻
	DELETE FROM board WHERE (duplicatedPkId=@pkId) and duplicateFrom='news'

	DELETE FROM review where (newsId=@pkId)
	DELETE FROM newsContent WHERE (newsId = @pkId)--删除源新闻content
	DELETE FROM news WHERE (pkId = @pkId)--删除源新闻
	--删除历史上的今天记录
	if exists(select pkId from todayInHistory where newsId = @pkId)
	delete from todayInHistory where newsId = @pkId

	COMMIT TRANSACTION;
	END TRY 
	BEGIN CATCH 
		ROLLBACK TRANSACTION ;
	END CATCH
GO

--增加事物控制，增加删除newscontent表中数据的功能
if exists (select * from dbo.sysobjects where id = object_id(N'DeleteDuplicatedInfoByNewsIdAndBigClassId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure DeleteDuplicatedInfoByNewsIdAndBigClassId
GO
CREATE PROCEDURE DeleteDuplicatedInfoByNewsIdAndBigClassId
(
	@pkId int,
	@bigClassId int
)
AS
	SET NOCOUNT OFF
	
BEGIN TRY 	 
	BEGIN TRANSACTION;
	
	DECLARE @duplicatedId int

	-- 查询被抄送新闻并删除其对应评论
	SELECT @duplicatedId=pkId FROM news WHERE duplicatedPkId=@pkId and bigClassId = @bigClassId	
	DELETE FROM review WHERE (newsId=@duplicatedId)		
	DELETE FROM newsContent WHERE (newsId=@duplicatedId)
	-- 从新闻、公告中删除抄送新闻
	DELETE FROM news WHERE (duplicatedPkId=@pkId) and bigClassId = @bigClassId		
	DELETE FROM board WHERE (duplicatedPkId=@pkId) and duplicateFrom='news' and bigClassId = @bigClassId	
	
	COMMIT TRANSACTION;
END TRY 
BEGIN CATCH 
	ROLLBACK TRANSACTION ;
END CATCH
GO

--增加事务控制，bug修复：不会删除groupAssign的权限
if exists (select * from dbo.sysobjects where id = object_id(N'DeleteBigClass') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure DeleteBigClass
GO
CREATE PROCEDURE  DeleteBigClass
(
	@pkId int
)
AS
	SET NOCOUNT OFF;
BEGIN TRY 
BEGIN TRANSACTION

delete from BigClassAssociation where bigClassId = @pkId 
delete from contributeClass where bigClassId = @pkId
delete from contributeClass where contributeClassId in (select m.pkId from menu m where bigClassId=@pkId)

delete from siteContributeClass where bigClassId = @pkId

delete from moduleAssign where menuId in (select m.pkId from menu m where bigClassId=@pkId)
delete from groupAssign  where menuId in (select m.pkId from menu m where bigClassId=@pkId)   --add by shijian201701 for V7.0
delete from menu where bigClassId=@pkId

delete from bigclass where pkId=@pkId

delete from newsContent where newsid in (select pkid from news where bigclassId=@pkid ) --where bigclassId = @pkid
delete from news where bigclassId=@pkId

delete from messageBoard where bigClassId=@pkId  --留言本表
delete from people where bigClassId=@pkId  --人事物介绍表
delete from board where bigClassId=@pkId  --站点公告表
delete from link where bigClassId=@pkId  --链接表
delete from album where bigClassId=@pkId --相册表

COMMIT TRANSACTION   
END TRY 

BEGIN CATCH 
	ROLLBACK TRANSACTION ;
END CATCH

GO

-- 搜索栏目从newscontent表中获取搜索内容
if exists (select * from dbo.sysobjects where id = object_id(N'GetNewsByContent') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetNewsByContent
GO
CREATE  PROCEDURE  GetNewsByContent
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeId int,
	@content varchar(100),
	@gateId int,
	@bigClassId int,
	@NewSearchbigClassId int,
	@sdate varchar(20),
	@edate varchar(20)
AS	
	--转换成索引
	SET @currentPage = @currentPage - 1
	IF (@currentPage < 0)
		BEGIN
			SET @currentPage = 1
		END
	-- Set the page bounds
    DECLARE @PageLowerBound INT
    DECLARE @PageUpperBound INT
    DECLARE @TotalRecords   INT
    SET @PageLowerBound = @PageSize * @currentPage
    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound

	-- Create a temp table TO store the select results
    CREATE TABLE #PageIndexs
    (
        IndexId int IDENTITY (0, 1) NOT NULL,
        ID int
    )


--查询是否有指定的查询栏目 

DECLARE @sCount int
set @sCount = (SELECT Count(pkId) from searchOptions where bigClassId = @NewSearchbigClassId)
if(@sCount>0)
begin

	--查询的栏目ID
	if (@bigClassId = 0)
	begin
	
	--已规定查询栏目范围
	--选择查询所有

    -- Insert into our temp table
    INSERT	INTO #PageIndexs (ID)
    SELECT	n.pkId
	FROM	[News] AS n,[NewsContent] AS nc,[searchOptions] AS so
	WHERE	n.gradeId <= @gradeId and 
			n.passed = 1 and 
			(n.validDays > 0 OR n.validDays < 0) and
			(isDuplicated>1 OR isDuplicated<1 or isDuplicated is null) and
			patindex('%|'+Convert(Varchar(50),n.bigClassId)+'|%',so.OptionsValue)>0 and so.bigClassId = @NewSearchbigClassId and
			n.pkid=nc.newsid and (nc.content like '%'+@content+'%')
			and datediff(d,n.updateTime,(case when @sdate ='' then n.updateTime else cast(@sdate as datetime) end))<=0 
		    and  datediff(d,n.updateTime,(case when @edate ='' then n.updateTime else cast(@edate as datetime) end))>=0

	SELECT  n.*
	FROM	[News] AS n
			INNER JOIN [#PageIndexs] AS p ON n.PKID = p.ID
	WHERE	p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
	ORDER BY n.istop desc,n.Priority desc,n.pkid desc

	DELETE #PageIndexs
	RETURN
	end
	else
	begin
	

	-- Insert into our temp table
    INSERT	INTO #PageIndexs (ID)
    SELECT	n.pkId
	FROM	[News] AS n,[NewsContent] AS nc,[searchOptions] AS so
	WHERE	n.gradeId <= @gradeId and 
			n.passed = 1 and 
			(n.validDays > 0 OR n.validDays < 0) and
			(isDuplicated>1 OR isDuplicated<1 or isDuplicated is null) and
			n.bigClassId=@bigClassId and
			patindex('%|'+Convert(Varchar(50),n.bigClassId)+'|%',so.OptionsValue)>0 and so.bigClassId = @NewSearchbigClassId and
			n.pkid=nc.newsid and (nc.content like '%'+@content+'%')
			and datediff(d,n.updateTime,(case when @sdate ='' then n.updateTime else cast(@sdate as datetime) end))<=0 
		    and  datediff(d,n.updateTime,(case when @edate ='' then n.updateTime else cast(@edate as datetime) end))>=0

	SELECT  n.*
	FROM	[News] AS n
			INNER JOIN [#PageIndexs] AS p ON n.PKID = p.ID
	WHERE	p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
	ORDER BY n.istop desc,n.Priority desc,n.pkid desc

	DELETE #PageIndexs
	RETURN



	end 

end
else
begin
	
	--没有规定查询栏目范围
	--选择查询所有
	--查询的栏目ID
	if (@bigClassId = 0)
	begin

    -- Insert into our temp table
    INSERT	INTO #PageIndexs (ID)
	SELECT	n.pkId
	FROM	[News] AS n,[NewsContent] AS nc
	WHERE	n.gradeId <= @gradeId and 
			n.passed = 1 and 
			(n.validDays > 0 OR n.validDays < 0) and
			(n.isDuplicated>1 OR n.isDuplicated<1 or n.isDuplicated is null) and 
			n.pkid=nc.newsid and (nc.content like '%'+@content+'%')
			and datediff(d,n.updateTime,(case when @sdate ='' then n.updateTime else cast(@sdate as datetime) end))<=0 
		    and  datediff(d,n.updateTime,(case when @edate ='' then n.updateTime else cast(@edate as datetime) end))>=0


	SELECT  n.*
	FROM	[News] AS n
			INNER JOIN [#PageIndexs] AS p ON n.PKID = p.ID
	WHERE	p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
	ORDER BY n.istop desc,n.Priority desc,n.pkid desc

	DELETE #PageIndexs
	RETURN 

	end
	else
	begin
	
	-- Insert into our temp table
    INSERT	INTO #PageIndexs (ID)
	SELECT	n.pkId
	FROM	[News] AS n,[NewsContent] AS nc
	WHERE	n.gradeId <= @gradeId and 
			n.passed = 1 and 
			(n.validDays > 0 OR n.validDays < 0) and
			(isDuplicated>1 OR isDuplicated<1 or isDuplicated is null) and
			n.bigClassId=@bigClassId and
			n.pkid=nc.newsid and (nc.content like '%'+@content+'%')
			and datediff(d,n.updateTime,(case when @sdate ='' then n.updateTime else cast(@sdate as datetime) end))<=0 
		    and  datediff(d,n.updateTime,(case when @edate ='' then n.updateTime else cast(@edate as datetime) end))>=0


	SELECT  n.*
	FROM	[News] AS n
			INNER JOIN [#PageIndexs] AS p ON n.PKID = p.ID
	WHERE	p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
	ORDER BY n.istop desc,n.Priority desc,n.pkid desc

	DELETE #PageIndexs
	RETURN 
	end
end
GO

if exists (select * from dbo.sysobjects where id = object_id(N'GetNewsByPkIdAndClick') and OBJECTPROPERTY(id,N'IsProcedure') = 1)
drop procedure GetNewsByPkIdAndClick
GO
CREATE PROCEDURE GetNewsByPkIdAndClick
@pkId int =null,
@gradeId int=1,
@gateId int 
AS
	 SET NOCOUNT ON 

BEGIN TRANSACTION


	--更新新闻点击量，并同步更新抄送新闻点击量
	update news set click = click + 1 where pkId=@pkId or duplicatedPkId=@pkId


select N.[pkId],N.[specialId],N.[gradeId],N.[gate],N.[departmentId],N.[listTitle],N.[intactTitle],N.[subheading],N.[isRecommand],N.[isTop],N.[useListTitle],N.[showReviewLink],N.[Author],N.[AuthorMail],N.[CopyFrom],N.[copyFromLink],N.[about],N.[pageType],N.[autoPageCharNum],N.[readPoint],N.[itemType],N.[descript],N.[usersId],N.[editor],N.[click],N.[updateTime],N.[passed],N.[isReview],N.[isImageNews],N.[imageName],N.[MenuId],N.[bigClassId],N.[width],N.[height],N.[updateIp],N.[useSubHeading],N.[isDuplicated],N.[duplicateFrom],N.[duplicatedPkId],N.[validDays],N.[mediaAutostart],N.[AuditUserID],N.[Priority],N.[isStatic],N.[staticUrl],N.[staticTime],N.[albumId],C.* from news N,newsContent C where N.pkid=C.newsid and N.pkId=@pkId and N.gradeId<=@gradeId and N.gate<>@gateId
-- 改为通过程序去判断and passed = 1
	
 COMMIT TRANSACTION
 RETURN 
GO


--静态化更新状态脚本
if exists (select * from dbo.sysobjects where id = object_id(N'UpDateNewsStatic') and OBJECTPROPERTY(id,N'IsProcedure') = 1)
drop procedure UpDateNewsStatic
GO
CREATE PROCEDURE UpDateNewsStatic
(
	@pkId int,
	@isStatic int,
	@staticUrl varchar(100),
	@staticTime datetime
)
AS
	SET NOCOUNT OFF;

UPDATE news SET isStatic = @isStatic, staticUrl = @staticUrl, staticTime = @staticTime WHERE pkId = @pkId

GO


--子站报表调用的存储过程
if exists (select * from dbo.sysobjects where id = object_id(N'StatNewsByStatType') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure StatNewsByStatType
GO
CREATE PROCEDURE StatNewsByStatType
(
	@statType int,		-- 统计类型
	@lastStatTime datetime	-- 上次统计时间，处理后传过来
)
AS
	SET NOCOUNT ON
	/*
	@lastStatTime 为开始统计时间，具体到每月1号
	查询范围为：大于开始时间，小于服务器当前时间1号
	*/
	
	DECLARE @nowTime varchar(10)	-- 设置当前时间
	SET @nowTime = RTrim(Year(GetDate())) + '-' + RTrim(Month(GetDate())) + '-01'
	
	IF(@statType=1)			-- 月报
	BEGIN
		SELECT Count(*) AS newsCount, Max(updateTime) as lastUpdateTime 
		FROM News 
			WHERE (updateTime >= @lastStatTime) and (updateTime < @nowTime)
			GROUP BY Year(updateTime),Month(updateTime)
			ORDER BY lastUpdateTime DESC
	END
	ELSE IF(@statType=2)	-- 年报
	BEGIN
		DECLARE @years int, @tempTime varchar(10)
		--SET @years = Year(@nowTime) - Year(@lastStatTime)
		--IF (@years > 0)
		--BEGIN
			--SET	@years = Year(@lastStatTime) + @years
			--SET @tempTime = Convert(varchar(4),@years) + '-12-31'
		--END
		SET @tempTime = GetDate()
		SELECT Count(*) AS newsCount, Max(updateTime) as lastUpdateTime 
		FROM News 
		WHERE (updateTime >= @lastStatTime) and (updateTime < @tempTime) --and (year(@lastStatTime)<>RTrim(Year(GetDate()))) 
		GROUP BY Year(updateTime)
		ORDER BY lastUpdateTime DESC		
	END	
	-- 周报 @statType=0
	RETURN


GO




if exists (select * from dbo.sysobjects where id = object_id(N'GetAuditingCount') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetAuditingCount
GO
CREATE PROCEDURE GetAuditingCount
	@gradeId int,
	@userId int,
	@gateId int,
	@manageAll varchar(8000),
	@newsCount int output, --news表待审条数
	@reviewsCount int output, --评论待审条数
	@messageBoardCount int output,--留言待审条数
	@linkCount int output,--友情链接待审条数
	@peopleCount int output,--人事物介绍栏目待审条数
	@boardCount int output,--公告待审条数
	@linkGangCount int output  --列表链接待审条数
	
AS
	
	--初始化查询条件(不带where)
	DECLARE @str1 nvarchar(1000)
	DECLARE @str2 nvarchar(1000)
	DECLARE @str3 nvarchar(4000)
	DECLARE @str4 nvarchar(4000)
	DECLARE @str5 nvarchar(4000)
	DECLARE @str6 nvarchar(4000)
	DECLARE @str7 nvarchar(4000)

	--设置查询表名称
	DECLARE @tableName nvarchar(100)

	--判断当前用户是否属于用户组
	Declare @groupid int 
	select @groupid = usersGroupId from users where pkid=@userId
--------------新闻开始--------------
	--新闻条件
	SET @str1 = ' 1=1 '
	SET @str1 = @str1 + ' and n.bigclassId = z.bigclassId'
	SET @str1 = @str1 + ' and z.bigclassId = b.pkid'
	SET @str1 = @str1 + ' and b.templateId = t.pkId'
	SET @str1 = @str1 + ' and n.passed=0 and n.gradeId<='+STR(@gradeId)
	SET @str1 = @str1 + ' and n.gate<>'+STR(@gateId)
	SET @str1 = @str1 + ' and n.usersId=u.pkId and n.gradeId=g.pkId and n.departmentId=d.pkId '

if(@groupid=1)
begin
	--新闻 用户权限
	--用临时表先读取授权的栏目和MenuName，然后在读取授权的新闻，
	--这样设计不会对news表中的menuId产生依赖，避免因栏目移动产生错误
	--根据栏目Id进行分组，以消除不用菜单位置的相同栏目产生的重复记录，20091010
	select b.pkId bigclassId, m.name menuName,max(m.pkId) sourceMenuId into #testnews from bigClass b, menu m, moduleAssign a where m.bigClassId=b.pkId and m.pkId=a.menuId and usersId=@userId and charindex('canAuditNews=1',privilegeSet)>0 group by b.pkId,m.name

	SET @tableName = '#testnews z, news n,users u,department d,grade g, template t, bigClass b '
end
else
begin
	--新闻 用户组权限
	select b.pkId bigclassId, m.name menuName,max(m.pkId) sourceMenuId into #testnews1 from bigClass b, menu m, groupAssign a where m.bigClassId=b.pkId and m.pkId=a.menuId and groupId=@groupid and charindex('canAuditNews=1',privilegeSet)>0 group by b.pkId,m.name
	
	SET @tableName = '#testnews1 z, news n,users u,department d,grade g, template t, bigClass b '
end

	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int
	DECLARE @vSQL nvarchar(4000)
	SET @vSQL = N'SELECT @count1 = Count(n.pkId) from '+Convert(nvarchar(100),@tableName)+' where ' + Convert(nvarchar(1000),@str1)
	EXEC sp_ExecuteSQL @vSQL, N'@count1 int output', @tempNum output
	SELECT @newsCount = @tempNum
--------------评论开始---------------

	--评论条件
	SET @str2 = ' 1=1 '
	SET @str2 = @str2 + ' and n.bigclassId = z.bigclassId'
	SET @str2 = @str2 + ' and z.bigclassId = b.pkid'
	SET @str2 = @str2 + ' and b.templateId = t.pkId'
	SET @str2 = @str2 + ' and r.passed=0 and n.gradeId<='+STR(@gradeId)
	SET @str2 = @str2 + ' and n.gate<>'+STR(@gateId)
	SET @str2 = @str2 + ' and n.usersId=u.pkId and n.gradeId=g.pkId '
	SET @str2 = @str2 + ' and r.newsId=n.pkId '

if(@groupid=1)
begin
	--评论 用户权限
	select b.pkId bigclassId, m.name menuName,max(m.pkId) sourceMenuId into #testreview from bigClass b, menu m, moduleAssign a where m.bigClassId=b.pkId and m.pkId=a.menuId and usersId=@userId and charindex('canAuditNews=1',privilegeSet)>0 group by b.pkId,m.name

	SET @tableName = '#testreview z, news n,users u,grade g, template t, bigClass b, review r '
end
else
begin
	--评论 用户组权限
	select b.pkId bigclassId, m.name menuName,max(m.pkId) sourceMenuId into #testreview1 from bigClass b, menu m, groupAssign a where m.bigClassId=b.pkId and m.pkId=a.menuId and groupId=@groupid and charindex('canAuditNews=1',privilegeSet)>0 group by b.pkId,m.name
	
	SET @tableName = '#testreview1 z, news n,users u,grade g, template t, bigClass b, review r '
end

	SET @vSQL = N'SELECT @count2 = Count(r.pkId) from '+Convert(nvarchar(100),@tableName)+' where ' + Convert(nvarchar(1000),@str2) 
	EXEC sp_ExecuteSQL @vSQL, N'@count2 int output', @tempNum output
	SELECT @reviewsCount = @tempNum

--------------留言本开始-----------------
	
	--留言本
	SET @str3 = ' 1=1 '
	SET @str3 = @str3 + ' and  m.pkid in (select m.pkId from bigClass b, menu m, moduleAssign a where m.bigClassId=b.pkId and m.pkId=a.menuId and a.usersId='+STR(@userId)+' and charindex(''canAuditNews=1'',privilegeSet)>0 )'
	SET @str3 = @str3 + ' and b.bigclassid=m.bigclassid  and checked=0 and b.gradeId<='+STR(@gradeId)
	SET @str3 = @str3 + ' and b.gate<>'+STR(@gateId)


----------------友情链接开始-----------------
	SET @str4 = ' 1=1 '
	SET @str4 = @str4 + ' and n.bigClassId in (select b.pkId from bigClass b, menu m, moduleAssign a where m.bigClassId=b.pkId and m.pkId=a.menuId and a.usersId='+STR(@userId)+' and charindex(''canAuditNews=1'',privilegeSet)>0 )'
	SET @str4 = @str4 + ' and n.passed=0 and n.gradeId<='+STR(@gradeId)
	SET @str4 = @str4 + ' and n.gate<>'+STR(@gateId)
	SET @str4 = @str4 + ' and n.userId=u.pkId'
	--SET @str4 = @str4 + ' and ' + @manageAll 
	--由于link表中字段为userid 和 news表中的usersid不一致，导致生成的@manageAll条件不成立

---------------人物事物开始-------------------


	SET @str5 = ' 1=1 '
	SET @str5 = @str5 + ' and n.bigClassId in (select b.pkId from bigClass b, menu m, moduleAssign a where m.bigClassId=b.pkId and m.pkId=a.menuId and a.usersId='+STR(@userId)+' and charindex(''canAuditNews=1'',privilegeSet)>0 )'
	SET @str5 = @str5 + ' and n.passed=0 and n.gradeId<='+STR(@gradeId)
	SET @str5 = @str5 + ' and n.gate<>'+STR(@gateId)
	--SET @str5 = @str5 + ' and n.userId=u.pkId'
	--SET @str5 = @str5 + ' and ' + @manageAll
	--由于people表中字段为userid 和 news表中的usersid不一致，导致生成的@manageAll条件不成立


--------------------公告-----------------------
	SET @str6 = ' 1=1 '
	SET @str6 = @str6 + ' and n.bigClassId in (select b.pkId from bigClass b, menu m, moduleAssign a where m.bigClassId=b.pkId and m.pkId=a.menuId and a.usersId='+STR(@userId)+' and charindex(''canAuditNews=1'',privilegeSet)>0 )'
	SET @str6 = @str6 + ' and n.passed=0 and n.gradeId<='+STR(@gradeId)
	SET @str6 = @str6 + ' and n.gate<>'+STR(@gateId)
	SET @str6 = @str6 + ' and n.userId=u.pkId'
	--SET @str6 = @str6 + ' and ' + @manageAll

-------------------列表链接-------------------
	SET @str7 = ' 1=1 '
	SET @str7 = @str7 + ' and n.groupid=l.pkid and exists (select m.pkId from bigClass b, menu m, moduleAssign a where m.bigClassId=b.pkId and m.pkId=a.menuId and a.usersId='+STR(@userId)+' and charindex(''canAuditNews=1'',privilegeSet)>0 and n.menuid=m.pkid )'
	SET @str7 = @str7 + ' and n.passed=0 and n.gradeId<='+STR(@gradeId)
	SET @str7 = @str7 + ' and n.gate<>'+STR(@gateId)
	SET @str7 = @str7 + ' and n.userId=u.pkId'
	--SET @str7 = @str7 + ' and ' + @manageAll



--清理零时表
if(@groupid=1)
begin
delete #testnews
delete #testreview
end
else
begin
delete #testnews1
delete #testreview1
end


	SET @vSQL = N'SELECT @count3 = Count(b.pkId) from  messageBoard b,menu m where ' + Convert(nvarchar(4000),@str3)
	EXEC sp_ExecuteSQL @vSQL, N'@count3 int output', @tempNum output
	SELECT @messageBoardCount = @tempNum
	
	
	SET @vSQL = N'SELECT @count4 = Count(n.pkId) from link n,users u where ' + Convert(nvarchar(4000),@str4)
	EXEC sp_ExecuteSQL @vSQL, N'@count4 int output', @tempNum output
	SELECT @linkCount = @tempNum	
	
	SET @vSQL = N'SELECT @count5 = Count(n.pkId) from people n where ' + Convert(nvarchar(4000),@str5)
	EXEC sp_ExecuteSQL @vSQL, N'@count5 int output', @tempNum output
	SELECT @peopleCount = @tempNum
	
	SET @vSQL = N'SELECT @count6 = Count(n.pkId) from board n,users u where ' + Convert(nvarchar(4000),@str6)
	EXEC sp_ExecuteSQL @vSQL, N'@count6 int output', @tempNum output
	SELECT @boardCount = @tempNum	
	
	SET @vSQL = N'SELECT @count7 = Count(n.pkId) from linkGang n, linkgroup l,users u where ' + Convert(nvarchar(4000),@str7)
	EXEC sp_ExecuteSQL @vSQL, N'@count7 int output', @tempNum output
	SELECT @linkGangCount = @tempNum

RETURN


GO




if exists (select * from dbo.sysobjects where id = object_id(N'UpdateMenu') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure UpdateMenu
GO
CREATE PROCEDURE UpdateMenu
(
	@parentId int,
	@name varchar(100),
	@remark varchar(100),
	@ordering int,
	@address varchar(100),
	@bigClassId int,
	@modeId int,
	@gradeId int,
	@pkId int,
	@gate int,
	@recorderNum int,
	@menuPic varchar(100),
	@itemId  varchar(100),
	@layoutTemplateId int,
	@adminId int,
	@displayRoot int,
	@userIntercept int,
	@interceptNums int,
    @columnNum int,
    @artDesignTemplateId int,
	@contributeSortType int,
	@navigate int,
	@newsTemplateId int,
	@width int,
	@height int,
	@isStatic int,
	@staticUrl varchar(100),
	@staticTime datetime,
	@deslength int
)
AS
	SET NOCOUNT OFF;
BEGIN TRANSACTION

if(@modeId='4')
begin
UPDATE menu SET  [name] = @name WHERE (bigClassId = @bigClassId) 
update bigClass set [name]=@name where pkId=@bigClassId
end
UPDATE menu SET artDesignTemplateId=@artDesignTemplateId,columnNum=@columnNum,interceptNums=@interceptNums,userIntercept=@userIntercept, displayRoot=@displayRoot,parentId = @parentId, [name] = @name, remark = @remark, ordering = @ordering, [address] = @address, bigClassId = @bigClassId, modeId = @modeId, gradeId = @gradeId,gate=@gate ,recorderNum=@recorderNum,menuPic=@menuPic,itemId=@itemId,layoutTemplateId=@layoutTemplateId ,adminId=@adminId, contributeSortType=@contributeSortType,navigate=@navigate,newsTemplateId=@newsTemplateId,width=@width ,height=@height,isStatic=@isStatic,staticUrl=@staticUrl ,staticTime=@staticTime,deslength=@deslength WHERE (pkId = @pkId) 
COMMIT TRANSACTION

GO


--修改关联newsContent表
if exists (select * from dbo.sysobjects where id = object_id(N'GetNewsByAll') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetNewsByAll
GO
CREATE PROCEDURE GetNewsByAll
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeId int,
	@searchStr varchar(100),
	@gateId int,
	@bigClassId int, --查询时选择的栏目范围 0为选择的全部栏目
    @NewSearchbigClassId int, --查询栏目ID
	@sdate varchar(20),
	@edate varchar(20)
	
AS	
	
	SET @currentPage = @currentPage - 1
	IF (@currentPage < 0)
		BEGIN
			SET @currentPage = 1
		END
	-- Set the page bounds
    DECLARE @PageLowerBound INT
    DECLARE @PageUpperBound INT
    DECLARE @TotalRecords   INT
    SET @PageLowerBound = @PageSize * @currentPage
    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
	
	-- Create a temp table TO store the select results
    CREATE TABLE #PageIndexs
    (
        IndexId int IDENTITY (0, 1) NOT NULL,
        ID int
    )
	
--查询是否有指定的查询栏目 
DECLARE @sCount int
set @sCount = (SELECT Count(pkId) from searchOptions where bigClassId = @NewSearchbigClassId)
if(@sCount>0)
begin
	
	--查询的栏目ID
	if (@bigClassId = 0)
	begin
	
	--已规定查询栏目范围
	--选择查询所有
    -- Insert into our temp table
    INSERT	INTO #PageIndexs (ID)
    SELECT	n.pkId
	FROM	[News] AS n,[NewsContent] AS nc,[searchOptions] AS so
	WHERE	n.gradeId <= @gradeId and 
			n.passed = 1 and 
			(n.validDays > 0 OR n.validDays < 0) and
			(isDuplicated>1 OR isDuplicated<1 or isDuplicated is null) and
			patindex('%|'+Convert(Varchar(50),n.bigClassId)+'|%',so.OptionsValue)>0 and so.bigClassId = @NewSearchbigClassId and
			n.pkid=nc.newsid and (nc.content like '%'+@searchStr+'%' or n.author like '%'+@searchStr+'%' or n.CopyFrom like '%'+@searchStr+'%' or n.intactTitle like '%'+@searchStr+'%' or n.subheading like '%'+@searchStr+'%' )
			and datediff(d,n.updateTime,(case when @sdate ='' then n.updateTime else cast(@sdate as datetime) end))<=0 
		    and datediff(d,n.updateTime,(case when @edate ='' then n.updateTime else cast(@edate as datetime) end))>=0
	ORDER BY n.istop desc,n.Priority desc,n.pkid desc
	
	end
	else
	begin
	
	-- Insert into our temp table
    INSERT	INTO #PageIndexs (ID)
    SELECT	n.pkId
	FROM	[News] AS n,[NewsContent] AS nc,[searchOptions] AS so
	WHERE	n.gradeId <= @gradeId and 
			n.passed = 1 and 
			(n.validDays > 0 OR n.validDays < 0) and
			(isDuplicated>1 OR isDuplicated<1 or isDuplicated is null) and
			n.bigClassId=@bigClassId and
			patindex('%|'+Convert(Varchar(50),n.bigClassId)+'|%',so.OptionsValue)>0 and so.bigClassId = @NewSearchbigClassId and
			n.pkid=nc.newsid and (nc.content like '%'+@searchStr+'%' or n.author like '%'+@searchStr+'%' or n.CopyFrom like '%'+@searchStr+'%' or n.intactTitle like '%'+@searchStr+'%' or n.subheading like '%'+@searchStr+'%' )
			and datediff(d,n.updateTime,(case when @sdate ='' then n.updateTime else cast(@sdate as datetime) end))<=0 
		    and datediff(d,n.updateTime,(case when @edate ='' then n.updateTime else cast(@edate as datetime) end))>=0
	ORDER BY n.istop desc,n.Priority desc,n.pkid desc
	
	end 
end
else
begin
	
	--没有规定查询栏目范围
	--选择查询所有
	--查询的栏目ID
	if (@bigClassId = 0)
	begin
	
    -- Insert into our temp table
    INSERT	INTO #PageIndexs (ID)
	SELECT	n.pkId
	FROM	[News] AS n ,[NewsContent] AS nc
	WHERE	n.gradeId <= @gradeId and 
			n.passed = 1 and 
			(n.validDays > 0 OR n.validDays < 0) and
			(n.isDuplicated>1 OR n.isDuplicated<1 or n.isDuplicated is null) and
			n.pkid=nc.newsid and (nc.content like '%'+@searchStr+'%' or n.author like '%'+@searchStr+'%' or n.CopyFrom like '%'+@searchStr+'%' or n.intactTitle like '%'+@searchStr+'%' or n.subheading like '%'+@searchStr+'%')
			and datediff(d,n.updateTime,(case when @sdate ='' then n.updateTime else cast(@sdate as datetime) end))<=0 
		    and datediff(d,n.updateTime,(case when @edate ='' then n.updateTime else cast(@edate as datetime) end))>=0
	ORDER BY n.istop desc,n.Priority desc,n.pkid desc
	
	end
	else
	begin
	
	-- Insert into our temp table
    INSERT	INTO #PageIndexs (ID)
	SELECT	n.pkId
	FROM	[News] AS n ,[NewsContent] AS nc
	WHERE	n.gradeId <= @gradeId and 
			n.passed = 1 and 
			(n.validDays > 0 OR n.validDays < 0) and
			(isDuplicated>1 OR isDuplicated<1 or isDuplicated is null) and
			n.bigClassId=@bigClassId and
			n.pkid=nc.newsid and (nc.content like '%'+@searchStr+'%' or n.author like '%'+@searchStr+'%' or n.CopyFrom like '%'+@searchStr+'%' or n.intactTitle like '%'+@searchStr+'%' or n.subheading like '%'+@searchStr+'%' ) 
			and datediff(d,n.updateTime,(case when @sdate ='' then n.updateTime else cast(@sdate as datetime) end))<=0 
		    and datediff(d,n.updateTime,(case when @edate ='' then n.updateTime else cast(@edate as datetime) end))>=0
	ORDER BY n.istop desc,n.Priority desc,n.pkid desc
	end
end

	SELECT  n.pkId ,n.specialId ,n.gradeId ,n.gate ,n.departmentId ,n.listTitle ,n.intactTitle ,n.subheading
				,n.isRecommand ,n.isTop ,n.useListTitle ,n.showReviewLink ,n.Author ,n.AuthorMail ,n.CopyFrom ,n.copyFromLink
				,n.about ,n.pageType ,n.autoPageCharNum ,n.readPoint ,n.itemType ,n.descript ,n.usersId ,n.editor
				,n.click ,n.updateTime ,n.passed ,n.isReview ,n.isImageNews ,n.imageName ,n.width ,n.height
				,n.MenuId ,n.bigClassId ,n.updateIp ,n.useSubHeading ,n.isDuplicated ,n.duplicateFrom ,n.duplicatedPkId ,n.validDays
				,n.mediaAutostart ,n.AuditUserID,n.isStatic,n.staticUrl,n.staticTime
	FROM	[News] AS n
			INNER JOIN [#PageIndexs] AS p ON n.PKID = p.ID
	WHERE	p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
	ORDER BY n.istop desc,n.Priority desc,n.pkid desc
	
DELETE #PageIndexs
RETURN


GO


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


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

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

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

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

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

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

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

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

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

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


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

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

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

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

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

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

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

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

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

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


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


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

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

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

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

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

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

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

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

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

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



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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


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

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

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

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

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

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

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

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

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

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


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

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

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

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

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

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

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