--v6.1版青岛定制功能脚本，此次统一集成在v6.3版本中
--修正用户删除问题
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

 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

--删除布局时同步删除bigClassDesign和groupAssign表中对应记录
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 @mpkId=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'GetLinkGangsByGroupId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetLinkGangsByGroupId
GO
CREATE PROCEDURE GetLinkGangsByGroupId
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeid int=1,
	@gateId int,
	@groupId int,
	@recordCount int output	-- 修改方法，返回记录数 王敬红20060404
AS	
	--转换成索引
	SELECT @currentPage = @currentPage-1	
	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT @count = Count(l.pkId) FROM linkGang l, users u, grade g WHERE l.gradeId<=' + CONVERT(nvarchar,@gradeId) 
				+ N' AND l.groupId=' + CONVERT(nvarchar,@groupId) + N' AND l.gate<>' + CONVERT(nvarchar,@gateId) 
				+ ' AND l.gradeId=g.pkId and l.userId=u.pkId'
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum

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

	--构造查询语句
	DECLARE @sqlStr varchar(1000),@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) + ' l.*,u.userName,u.fullName "fullName",g.name "grade"  FROM linkGang l, users u, grade g '
					+ 'WHERE l.gradeId<=' + STR(@gradeId) + ' AND l.groupId=' + STR(@groupId) + ' AND l.gate<>' + STR(@gateId) 
					+ 'AND l.gradeId=g.pkId and l.userId=u.pkId ORDER BY l.isTop DESC,l.priority desc, l.pkId DESC'	
	--查询最后一页
	ELSE IF @currentPage = @pageCount -1			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' l.*,u.userName,u.fullName "fullName",g.name "grade"  FROM linkGang l, users u, grade g '
					+ 'WHERE l.gradeId<=' + STR(@gradeId) + ' AND l.groupId=' + STR(@groupId) + ' AND l.gate<>' + STR(@gateId) 
					+ 'AND l.gradeId=g.pkId and l.userId=u.pkId ORDER BY l.isTop ASC,l.priority asc, l.pkId ASC) TempTable ORDER BY isTop DESC,priority desc, pkId DESC'	
	--查询中间页后面的页
	ELSE IF @currentPage > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @currentPage ) + ' l.*,u.userName,u.fullName "fullName",g.name "grade"  FROM linkGang l, users u, grade g '
					+ 'WHERE l.gradeId<=' + STR(@gradeId) + ' AND l.groupId=' + STR(@groupId) + ' AND l.gate<>' + STR(@gateId) 
					+ 'AND l.gradeId=g.pkId and l.userId=u.pkId ORDER BY l.isTop ASC,l.priority asc, l.pkId ASC) TempTable ORDER BY isTop DESC,priority desc, pkId DESC'
	--查询中间页前面的页
 	ELSE BEGIN							
		SET @currentPage=@currentPage+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @currentPage ) + ' l.*,u.userName,u.fullName "fullName",g.name "grade"  FROM linkGang l, users u, grade g '
					+ 'WHERE l.gradeId<=' + STR(@gradeId) + ' AND l.groupId=' + STR(@groupId) + ' AND l.gate<>' + STR(@gateId) 
					+ 'AND l.gradeId=g.pkId and l.userId=u.pkId ORDER BY l.isTop DESC,l.priority DESC, l.pkId DESC) TempTable1 '
					+ ' ORDER BY isTop ASC,priority asc, pkId ASC) TempTable2 ORDER BY isTop DESC,priority desc, pkId DESC'
	END
	--执行查询
	EXEC(@sqlStr)
RETURN
GO

--修正文字链接排序问题
if exists (select * from dbo.sysobjects where id = object_id(N'GetLinkGangsByGroupIdAndUserId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetLinkGangsByGroupIdAndUserId
GO
CREATE PROCEDURE GetLinkGangsByGroupIdAndUserId
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeid int=1,
	@gateId int,
	@groupId int,
	@userId int,
	@recordCount int output	-- 修改方法，返回记录数 王敬红20060404
AS	
	--转换成索引
	SELECT @currentPage = @currentPage-1	
	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT @count = Count(l.pkId) FROM linkGang l, users u, grade g WHERE l.gradeId<=' + CONVERT(nvarchar,@gradeId) 
				+ N' AND l.groupId=' + CONVERT(nvarchar,@groupId) + N' AND l.userId=' + CONVERT(nvarchar,@userId) 
				+ N' AND l.gate<>' + CONVERT(nvarchar,@gateId) + ' AND l.gradeId=g.pkId and l.userId=u.pkId'
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum

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

	--构造查询语句
	DECLARE @sqlStr varchar(1000),@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) + ' l.*,u.userName,u.fullName "fullName",g.name "grade"  FROM linkGang l, users u, grade g '
					+ 'WHERE l.gradeId<=' + STR(@gradeId) + ' AND l.groupId=' + STR(@groupId) + ' AND l.userId=' + STR(@userId) 
					+ ' AND l.gate<>' + STR(@gateId) + 'AND l.gradeId=g.pkId and l.userId=u.pkId ORDER BY l.isTop DESC,priority desc, l.pkId DESC'	
	--查询最后一页
	ELSE IF @currentPage = @pageCount -1	
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' l.*,u.userName,u.fullName "fullName",g.name "grade"  FROM linkGang l, users u, grade g '
					+ 'WHERE l.gradeId<=' + STR(@gradeId) + ' AND l.groupId=' + STR(@groupId) + ' AND l.userId=' + STR(@userId) 
					+ ' AND l.gate<>' + STR(@gateId) + 'AND l.gradeId=g.pkId and l.userId=u.pkId ORDER BY l.isTop ASC,l.priority asc, l.pkId ASC) TempTable ORDER BY isTop DESC,priority desc, pkId DESC'	
	--查询中间页后面的页
	ELSE IF @currentPage > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @currentPage ) + ' l.*,u.userName,u.fullName "fullName",g.name "grade"  FROM linkGang l, users u, grade g '
					+ 'WHERE l.gradeId<=' + STR(@gradeId) + ' AND l.groupId=' + STR(@groupId) + ' AND l.userId=' + STR(@userId) 
					+ ' AND l.gate<>' + STR(@gateId) + 'AND l.gradeId=g.pkId and l.userId=u.pkId ORDER BY l.isTop ASC,l.priority asc, l.pkId ASC) TempTable ORDER BY isTop DESC,priority desc, pkId DESC'
	--查询中间页前面的页
 	ELSE BEGIN							
		SET @currentPage=@currentPage+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @currentPage ) + ' l.*,u.userName,u.fullName "fullName",g.name "grade"  FROM linkGang l, users u, grade g '
					+ 'WHERE l.gradeId<=' + STR(@gradeId) + ' AND l.groupId=' + STR(@groupId) + ' AND l.userId=' + STR(@userId) 
					+ ' AND l.gate<>' + STR(@gateId) + 'AND l.gradeId=g.pkId and l.userId=u.pkId ORDER BY l.isTop DESC,l.priority desc, l.pkId DESC) TempTable1 '
					+ ' ORDER BY isTop ASC,priority asc, pkId ASC) TempTable2 ORDER BY isTop DESC,priority desc, pkId DESC'
	END
	--执行查询
	EXEC(@sqlStr)
RETURN

GO

--修正留言本栏目有保密留言的情况下在更多页中分页数量不正确的问题
if exists (select * from dbo.sysobjects where id = object_id(N'GetNewsByBigClassIdCount') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetNewsByBigClassIdCount
GO
CREATE PROCEDURE GetNewsByBigClassIdCount
@bigClassId int=null,
@gradeId int=1,
@gateId int=-1,
@table varchar(100)=null,
@count int output,
@isForeGround int,
@searchStr varchar(100) = ''
AS
	SET NOCOUNT ON
	declare @num int
	declare @vSQL varchar(1000)
	IF (@isForeGround=0)
	BEGIN
		select @vSQL=
			case @table
				when 'LatestNews' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and bigClassId in (select bigClassId from BigClassAssociation where thisBigclassId= '+convert(varchar,@bigclassId)+') and  gradeId<='+convert(varchar,@gradeId)+' return'
				when 'users' then 'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from users where isDel<>1 return'
				when 'popwindow' then  'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from '+convert(varchar,@table)+' where gate<> '+convert(varchar,@gateId)+' and gradeId<='+convert(varchar,@gradeId)+' return'
				when 'newss' then  'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and (specialId1='+convert(varchar,@bigclassId)+' or specialId2='+convert(varchar,@bigclassId)+' )   and  gradeId<='+convert(varchar,@gradeId)+' return'
				when 'news' then  'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from '+convert(varchar,@table)+' where gate<> '+convert(varchar,@gateId)+' and bigclassId='+convert(varchar,@bigclassId)+' and  gradeId<='+convert(varchar,@gradeId) + @searchStr + ' return'
				when 'gameTitleList' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and bigclassId= '+convert(varchar,@bigclassId)+' and gradeId <= '+convert(varchar,@gradeId)+' return'
				when 'gamePicList' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and bigclassId= '+convert(varchar,@bigclassId)+' and gradeId <= '+convert(varchar,@gradeId)+' return'
				when 'gameList' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and bigclassId= '+convert(varchar,@bigclassId)+' and gradeId <= '+convert(varchar,@gradeId)+' return'
				when 'todayInHistory' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and bigclassId= '+convert(varchar,@bigclassId)+' and gradeId <= '+convert(varchar,@gradeId)+' return'
				when 'picNews' then 'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and  bigclassId='+convert(varchar,@bigclassId)+'  and isImageNews=1 and gradeId<='+convert(varchar,@gradeId)+' return'
				when 'picNews1' then 'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and  bigclassId='+convert(varchar,@bigclassId)+'  and isImageNews=1 and gradeId<='+convert(varchar,@gradeId)+' return'
				when 'picNews2' then 'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and  bigclassId='+convert(varchar,@bigclassId)+'  and isImageNews=1 and gradeId<='+convert(varchar,@gradeId)+' return'
				when 'mediaNews' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and  bigclassId='+convert(varchar,@bigclassId)+'  and isImageNews=2 and gradeId<='+convert(varchar,@gradeId)+' return'
				--when 'special' then ''ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from '+convert(varchar,@table)+' where gate<> '+convert(varchar,@gateId)+' and bigclassId='+convert(varchar,@bigclassId)+' and  gradeId<='+convert(varchar,@gradeId) + @searchStr + ' return''
				ELSE 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from '+convert(varchar,@table)+' where gate<> '+convert(varchar,@gateId)+' and bigclassId='+convert(varchar,@bigclassId)+' and  gradeId<='+convert(varchar,@gradeId)+@searchStr+' return'
			end
	END 
	ELSE IF (@isForeGround=1)
	BEGIN
		select @vSQL=
			case @table
				when 'LatestNews' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and bigClassId in (select bigClassId from BigClassAssociation where thisBigclassId= '+convert(varchar,@bigclassId)+') and  gradeId<='+convert(varchar,@gradeId)+' return'
				when 'users' then 'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from users return'
				when 'popwindow' then  'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from '+convert(varchar,@table)+' where gate<> '+convert(varchar,@gateId)+' and gradeId<='+convert(varchar,@gradeId)+' and passed=1 and validDays <> 0 return'
				when 'newss' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and (specialId1='+convert(varchar,@bigclassId)+' or specialId2='+convert(varchar,@bigclassId)+' )   and  gradeId<='+convert(varchar,@gradeId)+' and passed=1 and validDays <> 0 return'
				when 'news' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from '+convert(varchar,@table)+' where gate<> '+convert(varchar,@gateId)+' and bigclassId='+convert(varchar,@bigclassId)+' and  gradeId<='+convert(varchar,@gradeId)+' and passed=1 and validDays <> 0 return'
				when 'gameTitleList' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and bigclassId= '+convert(varchar,@bigclassId)+' and gradeId <= '+convert(varchar,@gradeId)+' and passed=1 and validDays <> 0 return'
				when 'gamePicList' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and bigclassId= '+convert(varchar,@bigclassId)+' and gradeId <= '+convert(varchar,@gradeId)+' and passed=1 and validDays <> 0 return'
				when 'gameList' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and bigclassId= '+convert(varchar,@bigclassId)+' and gradeId <= '+convert(varchar,@gradeId)+' and passed=1 and validDays <> 0 return'
				when 'todayInHistory' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and bigclassId= '+convert(varchar,@bigclassId)+' and gradeId <= '+convert(varchar,@gradeId)+' and passed=1 and validDays <> 0 return'
				when 'picNews' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and  bigclassId='+convert(varchar,@bigclassId)+'  and isImageNews=1 and gradeId<='+convert(varchar,@gradeId)+' and passed=1 and validDays <> 0 return'
				when 'picNews1' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and  bigclassId='+convert(varchar,@bigclassId)+'  and isImageNews=1 and gradeId<='+convert(varchar,@gradeId)+' and passed=1 and validDays <> 0 return'
				when 'picNews2' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and  bigclassId='+convert(varchar,@bigclassId)+'  and isImageNews=1 and gradeId<='+convert(varchar,@gradeId)+' and passed=1 and validDays <> 0 return'
				when 'mediaNews' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and  bigclassId='+convert(varchar,@bigclassId)+'  and isImageNews=2 and gradeId<='+convert(varchar,@gradeId)+' and passed=1 and validDays <> 0 return'
				when 'special' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and specialId='+convert(varchar,@bigclassId)+' and  gradeId<='+convert(varchar,@gradeId) + @searchStr + ' return'	
				when 'messageBoard' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from messageBoard where gate<> '+convert(varchar,@gateId)+' and bigclassId='+convert(varchar,@bigclassId)+' and  gradeId<='+convert(varchar,@gradeId)+' and checked=1 and isSecrecy=0 return'	--留言本专用		
				ELSE 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from '+convert(varchar,@table)+' where gate<> '+convert(varchar,@gateId)+' and bigclassId='+convert(varchar,@bigclassId)+' and  gradeId<='+convert(varchar,@gradeId)+' and passed=1 return'
			end
	END	
	Execute (@vSQL)
	execute GetCount @count=@num output
	select @count=@num
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
)
AS
	SET NOCOUNT OFF;
BEGIN TRANSACTION

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 WHERE (pkId = @pkId) 
if(@modeId='4')
begin
update bigClass set [name]=@name where pkId=@bigClassId
end
COMMIT TRANSACTION

GO


if exists (select * from dbo.sysobjects where id = object_id(N'InsertMenu') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure InsertMenu
GO
CREATE PROCEDURE InsertMenu
(
	@pkId int output,
	@parentId int,
	@name varchar(100),
	@remark varchar(100)=null,
	@ordering int=0,
	@address varchar(100)=null,
	@bigClassId int=0,
	@modeId int,
	@gradeId int,
	@gate int=0,
	@recorderNum int=7,
	@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
)
AS
	SET NOCOUNT OFF;
BEGIN TRANSACTION
INSERT INTO menu(parentId, [name], remark, ordering, address, bigClassId, modeId, gradeId,gate,recorderNum,menuPic,itemId,layoutTemplateId,adminId,displayRoot, userIntercept,interceptNums,columnNum,artDesignTemplateId, contributeSortType,navigate,newsTemplateId,width,height,isStatic,staticUrl ,staticTime ) VALUES (@parentId, @name, @remark, @ordering, @address,@bigClassId, @modeId, @gradeId,@gate,@recorderNum,@menuPic,@itemId,@layoutTemplateId,@adminId,@displayRoot,@userIntercept,@interceptNums,@columnNum,@artDesignTemplateId, @contributeSortType,@navigate,@newsTemplateId,@width,@height,@isStatic,@staticUrl,@staticTime)
declare @menuId int
select @menuId=@@IDENTITY
set @pkId=@menuId
if(@modeId='1')
begin
	select @address='index.aspx?pkId='+convert(varchar,@menuId)
	update menu set address=@address where pkId=@menuId
end
if(@modeId='4')
begin
     insert into artDesign([name],menuId,leftVert,rightVert,fontPosition,color,[size],[space],font,bold,italic,topColor,middleColor,bottomColor,border,cellPad,cellSpace,brdrColor,leftMiddleFrontWidth,leftMiddleFrontHeight,centerTopFrontWidth,centerTopFrontHeight,rightMiddleFrontWidth,rightMiddleFrontHeight,centerTopBackHeight,middleTableBorderColor,bottomMoreInfoImageVar) values(@name,@menuId,'middle','middle','left','#000000',3,2,'宋体',0,0,'','','',0,0,0,'',20,20,20,20,20,20,25,'','more.gif')
     select @layoutTemplateId=pkId from layoutTemplate where layoutTemplateType=1 and isSystem=1
     update menu set layoutTemplateId=@layoutTemplateId where pkId=@menuId
end
COMMIT TRANSACTION

GO


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

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,m.pkId 
	RETURN

GO












--------------------------------------------------------------------------------------------------------------
--v6.3版新增脚本请在此后开始维护
--新增最新图文翻动栏目，相关权限更新

if exists (select * from dbo.sysobjects where id = object_id(N'GetAccreditByAdminId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetAccreditByAdminId
GO
CREATE PROCEDURE GetAccreditByAdminId
	@adminId int
AS
			select m.pkId,name,parentId, modeId
	from menu m,adminAssign a
	where 
	 a.menuId=0
	 and @adminId=a.adminId
	 and m.pkId=0
	union
		select m.pkId,name,parentId, m.modeId
	from menu m,adminAssign a
	where 
	parentId=0
	and a.adminId=@adminId
	and a.menuId=m.pkId
	
	union
	select pkId,name,parentId, modeId
	from menu 
	where 
	modeId<>4 
	and parentId<>0
	union
	SELECT m.pkId,m.name,m.parentId, m.modeId
   	FROM menu m,bigClass b,template t,adminAssign a	
	where t.code<>'newsTop'
		and t.code<>'gameTop' 
		and t.code<>'latestGame' 
		and t.code<>'todayInHistoryShort' 
		and t.code<>'todayInHistoryPic' 
		and t.code<>'searchTodayInHistory' 
		and t.code<>'fileDownTop'
		and t.code<>'special'
		and t.code<>'login'
		and t.code<>'nav'
		and t.code<>'search'
		and t.code<>'SearchHorizontal'
		and t.code<>'LoginHorizontal'
	    	and t.code<>'baidu'
	   	and t.code<>'shareNews'
		and t.code<>'LatestNews'
		and t.code<>'LatestImageNews'
		and t.code<>'ArticleStatistics'
		and t.code<>'VisitorsSessions'
		and t.code<>'OnlineStatistics'
		and t.code<>'AdminLogin'
		and t.code<>'contributeStat'
		and t.code<>'LatestMessage'
		and t.code<>'contributeTop'
		and t.code<>'latestContribute'
		and t.code<>'newsSummary'
		and t.code<>'messageboardSummary'
		and t.code<>'releaseTop'
		and t.code<>'latestscrollFlashNews' 
		and m.bigClassId=b.pkId
		and b.templateId=t.pkId		
	  
	RETURN

GO



if exists (select * from dbo.sysobjects where id = object_id(N'GetAccredit') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetAccredit
GO
CREATE PROCEDURE GetAccredit
AS
	select pkId,name,parentId,modeId
	from menu 
	where modeId<>4
	union
	SELECT m.pkId,m.name,m.parentId,m.modeId
   	FROM menu m,bigClass b,template t	
	 where t.code<>'newsTop'
		and t.code<>'gameTop' 
		and t.code<>'latestGame' 
		and t.code<>'todayInHistoryShort' 
		and t.code<>'todayInHistoryPic' 
		and t.code<>'searchTodayInHistory' 
		and t.code<>'fileDownTop'
		and t.code<>'special'
		and t.code<>'login'
		and t.code<>'nav' 
		and t.code<>'LoginHorizontal'
		and t.code<>'baidu'
		and t.code<>'shareNews'
		and t.code<>'LatestNews'
		and t.code<>'LatestImageNews'
		and t.code<>'ArticleStatistics'
		and t.code<>'VisitorsSessions'
		and t.code<>'OnlineStatistics'
		and t.code<>'AdminLogin' 
		and t.code<>'contributeStat'
		and t.code<>'LatestMessage' 
		and t.code<>'contributeTop'
		and t.code<>'latestContribute'
		and t.code<>'newsSummary'
		and t.code<>'messageboardSummary' 
		and t.code<>'releaseTop'
		and t.code<>'latestscrollFlashNews' 
		and m.bigClassId=b.pkId
		and b.templateId=t.pkId		
	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 [dbo].[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.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.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, 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



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

create PROCEDURE GetBigClassDesignByMenuId
@menuId int
AS
BEGIN

	SET NOCOUNT ON;
	SELECT b.* ,m.width,m.height from menu m,bigClassDesign b where m.pkId=b.menuId and b.menuid=@menuId
END

GO

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

CREATE PROCEDURE GetMenuById
@pkId int
AS
	/* SET NOCOUNT ON */
	SELECT m.*, t.code,t.typeId FROM menu m, bigClass b, template t where m.bigClassId=b.pkId and b.templateId=t.pkId and m.pkId=@pkId
	
	RETURN


GO



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

CREATE PROCEDURE InsertMenu
(
	@pkId int output,
	@parentId int,
	@name varchar(100),
	@remark varchar(100)=null,
	@ordering int=0,
	@address varchar(100)=null,
	@bigClassId int=0,
	@modeId int,
	@gradeId int,
	@gate int=0,
	@recorderNum int=7,
	@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
INSERT INTO menu(parentId, [name], remark, ordering, address, bigClassId, modeId, gradeId,gate,recorderNum,menuPic,itemId,layoutTemplateId,adminId,displayRoot, userIntercept,interceptNums,columnNum,artDesignTemplateId, contributeSortType,navigate,newsTemplateId,width,height,isStatic,staticUrl ,staticTime,deslength ) VALUES (@parentId, @name, @remark, @ordering, @address,@bigClassId, @modeId, @gradeId,@gate,@recorderNum,@menuPic,@itemId,@layoutTemplateId,@adminId,@displayRoot,@userIntercept,@interceptNums,@columnNum,@artDesignTemplateId, @contributeSortType,@navigate,@newsTemplateId,@width,@height,@isStatic,@staticUrl,@staticTime,@deslength)
declare @menuId int
select @menuId=@@IDENTITY
set @pkId=@menuId
if(@modeId='1')
begin
	select @address='index.aspx?pkId='+convert(varchar,@menuId)
	update menu set address=@address where pkId=@menuId

end
if(@modeId='4')
begin
     insert into artDesign([name],menuId,leftVert,rightVert,fontPosition,color,[size],[space],font,bold,italic,topColor,middleColor,bottomColor,border,cellPad,cellSpace,brdrColor,leftMiddleFrontWidth,leftMiddleFrontHeight,centerTopFrontWidth,centerTopFrontHeight,rightMiddleFrontWidth,rightMiddleFrontHeight,centerTopBackHeight,middleTableBorderColor,bottomMoreInfoImageVar) values(@name,@menuId,'middle','middle','left','#000000',3,2,'宋体',0,0,'','','',0,0,0,'',20,20,20,20,20,20,25,'','more.gif')
     select @layoutTemplateId=pkId from layoutTemplate where layoutTemplateType=1 and isSystem=1
     update menu set layoutTemplateId=@layoutTemplateId where pkId=@menuId
end
COMMIT TRANSACTION


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

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) 
if(@modeId='4')
begin
update bigClass set [name]=@name where pkId=@bigClassId
end
COMMIT TRANSACTION

GO


if exists (select * from dbo.sysobjects where id = object_id(N'GetNewsSearchCount') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetNewsSearchCount
GO
CREATE PROCEDURE GetNewsSearchCount
    @NewSearchbigClassId int,
    @searchStr varchar(100),
	@bigClassId int,
	@type int,
	@gradeId int,
	@gateId int,
	@recordCount int output,
	@date varchar(100)
AS	
	--设置查询表名称
	DECLARE @tableName nvarchar(50)
	--初始化查询条件(不带where)
	DECLARE @str nvarchar(1000)
    DECLARE @sCount int,@sNum int
    DECLARE @sSql nvarchar(200)
    SET @sSQL = N'SELECT @count=Count(searchOptions.pkId) from searchOptions where bigClassId = ' + STR(@NewSearchbigClassId)
    EXEC sp_ExecuteSQL @sSQL,N'@count int output',@sNum output
    SELECT @sCount = @sNum

	SET @str = ' validDays <> 0 '
	SET @searchStr = '%'+Convert(varchar,@searchStr)+'%'
	IF (@bigClassId <> 0)
    BEGIN
        SET @tableName = 'news'
		SET @str = @str + ' and news.bigClassId='+STR(@bigClassId)
    END
    ELSE IF(@sCount > 0)
    BEGIN
        SET @tableName = 'news,searchOptions'
        SET @str = @str + ' and patindex(''%|''+' + 'Convert(Varchar(50),news.bigClassId)'+ '+''|%'',searchOptions.OptionsValue)>0 and searchOptions.bigClassId = ' +STR(@NewSearchbigClassId)
	END
    ELSE IF(@sCount = 0)
    BEGIN
        SET @tableName = 'news'
    END

    IF (@type=0)
		BEGIN
			SET @str = @str + ' and (listTitle like ''' + @searchStr + ''''
			SET @str = @str + ' or intactTitle like ''' + @searchStr + ''''
			SET @str = @str + ' or subheading like ''' + @searchStr + ''''
			SET @str = @str + ' or content like ''' + @searchStr + ''''
			SET @str = @str + ' or author like ''' + @searchStr + ''')' 
		END
	IF(@type=1)
		BEGIN
			SET @str = @str + ' and (listTitle like ''' + @searchStr + ''''
			SET @str = @str + ' or intactTitle like ''' + @searchStr + ''''
			SET @str = @str + ' or subheading like ''' + @searchStr + ''')'
		END
	IF(@type=2)
		SET @str = @str + ' and author like ''' + @searchStr + ''''
	IF(@type=3)
		SET @str = @str + ' and content like ''' + @searchStr + ''''
	IF(@type=5)
		SET @str = @str + ' and copyFrom like ''' + @searchStr + ''''

	SET @str = @str + ' and passed=1 and (isDuplicated<>1 or isDuplicated is null) and gradeId<='+Convert(varchar,@gradeId)+@date
	--SET @str = @str + ' and gate<>'+Convert(varchar,@gateId)
	
	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT @count = Count(news.pkId) from '+Convert(nvarchar(50),@tableName)+' where ' + Convert(nvarchar(1000),@str)
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum
RETURN


GO

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,[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.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.updateTime desc,n.pkid desc

	end
	else
	begin
	
	-- Insert into our temp table
    INSERT	INTO #PageIndexs (ID)
    SELECT	n.pkId
	FROM	[News] AS n,[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.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.updateTime 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 
	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.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.updateTime desc,n.pkid desc 
	end
	else
	begin
	
	-- Insert into our temp table
    INSERT	INTO #PageIndexs (ID)
	SELECT	n.pkId
	FROM	[News] AS n 
	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.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.updateTime desc,n.pkid desc

	end
end


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.updateTime desc,n.pkid desc

DELETE #PageIndexs
RETURN



GO

if exists (select * from dbo.sysobjects where id = object_id(N'GetNewsByTitle') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetNewsByTitle
GO
CREATE PROCEDURE GetNewsByTitle
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeId int,
	@title 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,[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.intactTitle like '%'+@title+'%' or n.subheading like '%'+@title+'%' )
			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.updateTime desc,n.pkid desc

	end
	else
	begin
	
	-- Insert into our temp table
    INSERT	INTO #PageIndexs (ID)
    SELECT	n.pkId
	FROM	[News] AS n,[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.intactTitle like '%'+@title+'%' or n.subheading like '%'+@title+'%') and
			n.bigClassId=@bigClassId 
			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.updateTime 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 
	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.intactTitle like '%'+@title+'%' or n.subheading like '%'+@title+'%')
			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.updateTime desc,n.pkid desc

	end
	else
	begin
	
	-- Insert into our temp table
    INSERT	INTO #PageIndexs (ID)
	SELECT	n.pkId
	FROM	[News] AS n 
	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.intactTitle like '%'+@title+'%' or n.subheading like '%'+@title+'%') and 
			n.bigClassId=@bigClassId 
			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.updateTime desc,n.pkid desc 
	end
end

SELECT  n.*,d.name as Department 
	FROM	[News] AS n
			INNER JOIN [#PageIndexs] AS p ON n.PKID = p.ID
			INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
	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


GO

if exists (select * from dbo.sysobjects where id = object_id(N'GetNewsByAuthor') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetNewsByAuthor
GO
CREATE PROCEDURE GetNewsByAuthor
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeId int,
	@author 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,[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.author like '%'+@author+'%')
			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.updateTime 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,[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.author like '%'+@author+'%')
			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.updateTime 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 
	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.author like '%'+@author+'%')
			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.updateTime 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 
	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.author like '%'+@author+'%')
			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.updateTime desc,n.pkid desc

	DELETE #PageIndexs
	RETURN 
	end
end




GO

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,[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.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.updateTime 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,[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.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.updateTime 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 
	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.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.updateTime 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 
	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.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.updateTime desc,n.pkid desc

	DELETE #PageIndexs
	RETURN 
	end
end


GO

if exists (select * from dbo.sysobjects where id = object_id(N'GetNewsByCopyFrom') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetNewsByCopyFrom
GO
CREATE PROCEDURE GetNewsByCopyFrom
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeId int,
	@copyFrom 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,[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.CopyFrom like '%'+@copyFrom+'%')
			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.updateTime 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,[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.CopyFrom like '%'+@copyFrom+'%')
			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.updateTime 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 
	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.CopyFrom like '%'+@copyFrom+'%')
			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.updateTime 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 
	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.CopyFrom like '%'+@copyFrom+'%')
			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.updateTime desc,n.pkid desc

	DELETE #PageIndexs
	RETURN 
	end
end

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
)
AS
	SET NOCOUNT OFF;	 
	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

IF (@passed is null)
	set @passed = 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) 
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)

SET @pkId = @@IDENTITY
--排序默认为pkid
--UPDATE news SET Priority=@pkid  WHERE pkid=@pkid
COMMIT TRANSACTION


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
)
AS
	SET NOCOUNT OFF;
IF	(@AuditUserID IS NULL)
	BEGIN
		SET @AuditUserID = 0;
	END

IF (@validDays is null)
	BEGIN
		set @validDays = -1	
	END
IF (@passed is null)
	BEGIN
		set @passed = 0	
	END

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, content = @content, 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  WHERE (pkId = @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抄送的信息


GO



--解决文字链接排序问题的sql
if exists (select * from dbo.sysobjects where id = object_id(N'GetLinkGangsByGroupId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetLinkGangsByGroupId
GO
CREATE PROCEDURE GetLinkGangsByGroupId
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeid int=1,
	@gateId int,
	@groupId int,
	@recordCount int output	-- 修改方法，返回记录数 王敬红20060404
AS	
	--转换成索引
	SELECT @currentPage = @currentPage-1	

	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT @count = Count(l.pkId) FROM linkGang l, users u, grade g WHERE l.gradeId<=' + CONVERT(nvarchar,@gradeId) 
				+ N' AND l.groupId=' + CONVERT(nvarchar,@groupId) + N' AND l.gate<>' + CONVERT(nvarchar,@gateId) 
				+ ' AND l.gradeId=g.pkId and l.userId=u.pkId'
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum

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

	--构造查询语句
	DECLARE @sqlStr varchar(1000),@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) + ' l.*,u.userName,u.fullName "fullName",g.name "grade"  FROM linkGang l, users u, grade g '
					+ 'WHERE l.gradeId<=' + STR(@gradeId) + ' AND l.groupId=' + STR(@groupId) + ' AND l.gate<>' + STR(@gateId) 
					+ 'AND l.gradeId=g.pkId and l.userId=u.pkId ORDER BY l.isTop DESC,l.priority desc, l.pkId DESC'	
	--查询最后一页
	ELSE IF @currentPage = @pageCount -1			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' l.*,u.userName,u.fullName "fullName",g.name "grade"  FROM linkGang l, users u, grade g '
					+ 'WHERE l.gradeId<=' + STR(@gradeId) + ' AND l.groupId=' + STR(@groupId) + ' AND l.gate<>' + STR(@gateId) 
					+ 'AND l.gradeId=g.pkId and l.userId=u.pkId ORDER BY l.isTop ASC,l.priority asc, l.pkId ASC) TempTable ORDER BY isTop DESC,priority desc, pkId DESC'	
	--查询中间页后面的页
	ELSE IF @currentPage > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @currentPage ) + ' l.*,u.userName,u.fullName "fullName",g.name "grade"  FROM linkGang l, users u, grade g '
					+ 'WHERE l.gradeId<=' + STR(@gradeId) + ' AND l.groupId=' + STR(@groupId) + ' AND l.gate<>' + STR(@gateId) 
					+ 'AND l.gradeId=g.pkId and l.userId=u.pkId ORDER BY l.isTop ASC,l.priority asc, l.pkId ASC) TempTable ORDER BY isTop DESC,priority desc, pkId DESC'
	--查询中间页前面的页
 	ELSE BEGIN							
		SET @currentPage=@currentPage+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @currentPage ) + ' l.*,u.userName,u.fullName "fullName",g.name "grade"  FROM linkGang l, users u, grade g '
					+ 'WHERE l.gradeId<=' + STR(@gradeId) + ' AND l.groupId=' + STR(@groupId) + ' AND l.gate<>' + STR(@gateId) 
					+ 'AND l.gradeId=g.pkId and l.userId=u.pkId ORDER BY l.isTop DESC,l.priority DESC, l.pkId DESC) TempTable1 '
					+ ' ORDER BY isTop ASC,priority asc, pkId ASC) TempTable2 ORDER BY isTop DESC,priority desc, pkId DESC'
	END
	--执行查询
	EXEC(@sqlStr)
RETURN
GO




if exists (select * from dbo.sysobjects where id = object_id(N'GetLinkGangsByGroupIdAndUserId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetLinkGangsByGroupIdAndUserId
GO
CREATE PROCEDURE GetLinkGangsByGroupIdAndUserId
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeid int=1,
	@gateId int,
	@groupId int,
	@userId int,
	@recordCount int output	-- 修改方法，返回记录数 王敬红20060404
AS	
	--转换成索引
	SELECT @currentPage = @currentPage-1	

	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT @count = Count(l.pkId) FROM linkGang l, users u, grade g WHERE l.gradeId<=' + CONVERT(nvarchar,@gradeId) 
				+ N' AND l.groupId=' + CONVERT(nvarchar,@groupId) + N' AND l.userId=' + CONVERT(nvarchar,@userId) 
				+ N' AND l.gate<>' + CONVERT(nvarchar,@gateId) + ' AND l.gradeId=g.pkId and l.userId=u.pkId'
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum

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

	--构造查询语句
	DECLARE @sqlStr varchar(1000),@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) + ' l.*,u.userName,u.fullName "fullName",g.name "grade"  FROM linkGang l, users u, grade g '
					+ 'WHERE l.gradeId<=' + STR(@gradeId) + ' AND l.groupId=' + STR(@groupId) + ' AND l.userId=' + STR(@userId) 
					+ ' AND l.gate<>' + STR(@gateId) + 'AND l.gradeId=g.pkId and l.userId=u.pkId ORDER BY l.isTop DESC,priority desc, l.pkId DESC'	
	--查询最后一页
	ELSE IF @currentPage = @pageCount -1	
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' l.*,u.userName,u.fullName "fullName",g.name "grade"  FROM linkGang l, users u, grade g '
					+ 'WHERE l.gradeId<=' + STR(@gradeId) + ' AND l.groupId=' + STR(@groupId) + ' AND l.userId=' + STR(@userId) 
					+ ' AND l.gate<>' + STR(@gateId) + 'AND l.gradeId=g.pkId and l.userId=u.pkId ORDER BY l.isTop ASC,l.priority asc, l.pkId ASC) TempTable ORDER BY isTop DESC,priority desc, pkId DESC'	
	--查询中间页后面的页
	ELSE IF @currentPage > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @currentPage ) + ' l.*,u.userName,u.fullName "fullName",g.name "grade"  FROM linkGang l, users u, grade g '
					+ 'WHERE l.gradeId<=' + STR(@gradeId) + ' AND l.groupId=' + STR(@groupId) + ' AND l.userId=' + STR(@userId) 
					+ ' AND l.gate<>' + STR(@gateId) + 'AND l.gradeId=g.pkId and l.userId=u.pkId ORDER BY l.isTop ASC,l.priority asc, l.pkId ASC) TempTable ORDER BY isTop DESC,priority desc, pkId DESC'
	--查询中间页前面的页
 	ELSE BEGIN							
		SET @currentPage=@currentPage+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @currentPage ) + ' l.*,u.userName,u.fullName "fullName",g.name "grade"  FROM linkGang l, users u, grade g '
					+ 'WHERE l.gradeId<=' + STR(@gradeId) + ' AND l.groupId=' + STR(@groupId) + ' AND l.userId=' + STR(@userId) 
					+ ' AND l.gate<>' + STR(@gateId) + 'AND l.gradeId=g.pkId and l.userId=u.pkId ORDER BY l.isTop DESC,l.priority desc, l.pkId DESC) TempTable1 '
					+ ' ORDER BY isTop ASC,priority asc, pkId ASC) TempTable2 ORDER BY isTop DESC,priority desc, pkId DESC'
	END
	--执行查询
	EXEC(@sqlStr)
RETURN


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) + '-01-01'
		--END
		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'GetMessageBoardByBigClassId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetMessageBoardByBigClassId
GO
CREATE PROCEDURE GetMessageBoardByBigClassId
@bigClassId int=null,
@currentPage int=1,
@pageSize int=5,
@gradeid int=1,
@gateId int,
@searchStr varchar(100)=''
AS
	 SET NOCOUNT ON
	declare @vSQL varchar(1000)
select @vSQL = 'select top ' + convert(varchar, @pageSize) + ' m.*,g.name "grade" from messageBoard m, grade g where  m.gate<>'+Convert(varchar,@gateId)+' and   m.bigClassId='+Convert(varchar,@bigClassId)+' and m.gradeId<='+convert(varchar,@gradeId)+' and m.gradeId=g.pkId and m.pkId <> all (select top '+Convert(varchar,((@currentPage-1)*@pageSize))+' pkId from messageBoard where gate<>'+Convert(varchar,@gateId)+' and bigClassId='+Convert(varchar,@bigClassId)+' and gradeId<='+convert(varchar,@gradeId)+' order by istop desc,reaudit asc,m.updatetime desc,pkId desc) '+@searchStr+' order by istop desc,reaudit asc,m.updatetime desc,m.pkId desc'
Execute (@vSQL)	
	RETURN

GO

--修改排序，置顶>待审>留言时间
if exists (select * from dbo.sysobjects where id = object_id(N'GetMessageBoardByBigClassIdAndForwardUser') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetMessageBoardByBigClassIdAndForwardUser
GO
CREATE PROCEDURE GetMessageBoardByBigClassIdAndForwardUser
@bigClassId int=null,
@currentPage int=1,
@pageSize int=5,
@gradeid int=1,
@gateId int,
@userName varchar(100)
AS
	 SET NOCOUNT ON
	declare @vSQL varchar(1000)
select @vSQL = 'select top ' + convert(varchar, @pageSize) + ' m.*,g.name "grade" from messageBoard m,messageForward mf, grade g where mf.messageId = m.pkId and mf.ForwardToUserName =''' + @userName + ''' and m.gate<>'+Convert(varchar,@gateId)+' and   m.bigClassId='+Convert(varchar,@bigClassId)+' and m.gradeId<='+convert(varchar,@gradeId)+' and m.gradeId=g.pkId and m.pkId <> all (select top '+Convert(varchar,((@currentPage-1)*@pageSize))+' pkId from messageBoard where   gate<>'+Convert(varchar,@gateId)+' and   bigClassId='+Convert(varchar,@bigClassId)+' and gradeId<='+convert(varchar,@gradeId)+' order by istop desc,reaudit asc,m.updatetime desc,pkId desc)  order by istop desc,reaudit asc,m.updatetime desc,m.pkId desc'
Execute (@vSQL)
	
	RETURN

GO

--新增字段
if exists (select * from dbo.sysobjects where id = object_id(N'InsertMessageBoard') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure InsertMessageBoard
GO
CREATE PROCEDURE InsertMessageBoard
(
	@pkId int,
	@title varchar(100),
	@checked int,
	@author varchar(100),
	@tel varchar(100),
	@isSecrecy int,
	@email varchar(100),
	@sex varchar(10),
	@homePage varchar(100),
	@Content ntext,
	@reContent ntext,
	@userId int,
	@updateTime datetime,
	@updateIp varchar(100),
	@click int,
	@isTop int,
	@MenuId int,
	@gradeId int,
	@bigClassId int,
	@gate int,
	@username varchar(100),
	@password varchar(50),
	@retime datetime,
	@redept varchar(100),
	@reAudit int
)
AS
	SET NOCOUNT OFF;	 
	BEGIN TRANSACTION;
	INSERT INTO messageBoard(
	title, 
	checked, 
	author,
	tel, 
	isSecrecy, 
	email, 
	sex, 
	homePage, 
	[Content], 
	reContent, 
	userId, 
	updateTime, 
	updateIp, 
	click, 
	isTop, 
	MenuId, 
	gradeId, 
	bigClassId, 
	gate, 
	username, 
	[password], 
	retime,
	redept,
	reAudit
	) VALUES (
	@title, 
	@checked, 
	@author,
	@tel, 
	@isSecrecy, 
	@email, 
	@sex, 
	@homePage, 
	@Content, 
	@reContent, 
	@userId, 
	@updateTime, 
	@updateIp, 
	@click, 
	@isTop, 
	@MenuId, 
	@gradeId, 
	@bigClassId, 
	@gate, 
	@username, 
	@password, 
	@retime,
	@redept,
	@reAudit
	)
  COMMIT TRANSACTION;


GO

--新增字段
if exists (select * from dbo.sysobjects where id = object_id(N'UpDateMessageBoard') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure UpDateMessageBoard
GO
CREATE PROCEDURE UpDateMessageBoard
(
	@pkId int,
	@title varchar(100),
	@checked int,
	@author varchar(100),
	@tel varchar(100),
	@isSecrecy int,
	@email varchar(100),
	@sex varchar(10),
	@homePage varchar(100),
	@Content ntext,
	@reContent ntext,
	@userId int,
	@updateTime datetime,
	@updateIp varchar(100),
	@click int,
	@isTop int,
	@MenuId int,
	@gradeId int,
	@bigClassId int,
	@gate int,
	@username varchar(100),
	@password varchar(50),
	@retime datetime,
	@redept varchar(100),
	@reAudit int
)
AS
	SET NOCOUNT OFF;
UPDATE messageBoard SET 
	title=@title,
	checked=@checked,
	author = @author, 
	tel = @tel, 
	isSecrecy = @isSecrecy, 
	email = @email, 
	sex = @sex, 
	homePage = @homePage, 
	[Content] = @Content, 
	reContent = @reContent, 
	userId = @userId, 
	updateTime = @updateTime, 
	updateIp = @updateIp, 
	click = @click, 
	isTop = @isTop, 
	MenuId = @MenuId, 
	gradeId = @gradeId, 
	bigClassId = @bigClassId, 
	gate = @gate, 
	username = @username, 
	[password] = @password, 
	retime = @retime,
	redept=@redept,
	reAudit=@reAudit

WHERE (pkId = @pkId)

GO


----------------------------------------------------------------------
--v6.3版本20150805更新存储过程
----------------------------------------------------------------------

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 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.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, 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'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, 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 = 'updateTime ASC, sourceMenuId ASC, newsId ASC'
	SET @orderTypeD = 'updateTime 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.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(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.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(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.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(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.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(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'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 = 'updateTime ASC,sourceMenuId ASC, newsId ASC'
	SET @orderTypeD = 'updateTime 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.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.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.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.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'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)  
			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)  
			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)  
			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)  
			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, 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, 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'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 = 'updateTime ASC, sourceMenuId ASC, newsId ASC'
	SET @orderTypeD = 'updateTime 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.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.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.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.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'GetAuditingBoardByUserId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetAuditingBoardByUserId
GO
CREATE PROCEDURE GetAuditingBoardByUserId
	@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(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,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 

	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.userId=u.pkId and n.gradeId=g.pkId '

	SET @tableName = '#test z, board n,users u,grade g, template t, bigClass b '

end
else
begin

	select b.pkId bigclassId, m.name menuName,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 

	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.userId=u.pkId and n.gradeId=g.pkId '

	SET @tableName = '#test1 z, board n,users u,grade g, template t, bigClass b '

end

	--设置查询排序（不带order by）
	DECLARE @orderTypeA nvarchar(100)
	DECLARE @orderTypeD nvarchar(100)
	SET @orderTypeA = 'sourceMenuId ASC, newsId ASC'
	SET @orderTypeD = '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.*,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(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.*,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(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.*,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(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.*,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(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'GetAuditingPeopleByUserId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetAuditingPeopleByUserId
GO
CREATE PROCEDURE GetAuditingPeopleByUserId
	@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(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 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.userId=u.pkId and n.gradeId=g.pkId '

	SET @tableName = '#test z, people 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.passed=0 and n.gradeId<='+STR(@gradeId)
	SET @str = @str + ' and n.gate<>'+STR(@gateId)
	SET @str = @str + ' and n.userId=u.pkId and n.gradeId=g.pkId '

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

	--设置查询排序（不带order by）
	DECLARE @orderTypeA nvarchar(100)
	DECLARE @orderTypeD nvarchar(100)
	SET @orderTypeA = 'sourceMenuId ASC, newsId ASC'
	SET @orderTypeD = '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.*,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(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.*,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(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.*,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(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.*,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(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'GetAuditingLinkByUserId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetAuditingLinkByUserId
GO
CREATE PROCEDURE GetAuditingLinkByUserId
	@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(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 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.userId=u.pkId and n.gradeId=g.pkId '

	SET @tableName = '#test z, link 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.passed=0 and n.gradeId<='+STR(@gradeId)
	SET @str = @str + ' and n.gate<>'+STR(@gateId)
	SET @str = @str + ' and n.userId=u.pkId and n.gradeId=g.pkId '

	SET @tableName = '#test1 z, link n,users u,grade g, template t, bigClass b '

end
	--设置查询排序（不带order by）
	DECLARE @orderTypeA nvarchar(100)
	DECLARE @orderTypeD nvarchar(100)
	SET @orderTypeA = 'sourceMenuId ASC, newsId ASC'
	SET @orderTypeD = '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.*,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(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.*,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(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.*,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(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.*,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(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'GetAuditingLinkGangByUserId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetAuditingLinkGangByUserId
GO
CREATE PROCEDURE GetAuditingLinkGangByUserId
	@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(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 and charindex('canAuditNews=1',privilegeSet)>0 group by b.pkId,m.name

	SET @str = ' 1=1 '
	SET @str = @str + ' and l.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.userId=u.pkId and n.gradeId=g.pkId and n.groupId=l.pkId'

	SET @tableName = '#test z, linkGang n,users u,grade g, template t, bigClass b, linkGroup l '
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 l.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.userId=u.pkId and n.gradeId=g.pkId and n.groupId=l.pkId'

	SET @tableName = '#test1 z, linkGang n,users u,grade g, template t, bigClass b, linkGroup l '

end
	--设置查询排序（不带order by）
	DECLARE @orderTypeA nvarchar(100)
	DECLARE @orderTypeD nvarchar(100)
	SET @orderTypeA = 'sourceMenuId ASC, newsId ASC'
	SET @orderTypeD = '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.*,u.userName,u.fullName,g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId , t.code, l.bigClassId FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(4000),@str)+' and b.pkId = l.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.*,u.userName,u.fullName,g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId , t.code, l.bigClassId 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.*,u.userName,u.fullName,g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId, t.code, l.bigClassId 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.*,u.userName,u.fullName,g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId, t.code, l.bigClassId 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'GetNewsByBigClassIdCount') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetNewsByBigClassIdCount
GO
CREATE PROCEDURE GetNewsByBigClassIdCount
@bigClassId int=null,
@gradeId int=1,
@gateId int=-1,
@table varchar(100)=null,
@count int output,
@isForeGround int,
@searchStr varchar(100) = ''
AS
	SET NOCOUNT ON
	declare @num int
	declare @vSQL varchar(1000)
	IF (@isForeGround=0)
	BEGIN
		--user后台调用
		select @vSQL=
			case @table
				--when 'LatestNews' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and bigClassId in (select bigClassId from BigClassAssociation where thisBigclassId= '+convert(varchar,@bigclassId)+') and gradeId<='+convert(varchar,@gradeId)+' return'
				when 'users' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from users where isDel<>1 return'
				when 'popwindow' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from '+convert(varchar,@table)+' where gate<> '+convert(varchar,@gateId)+' and gradeId<='+convert(varchar,@gradeId)+' return'
				--when 'newss' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and (specialId1='+convert(varchar,@bigclassId)+' or specialId2='+convert(varchar,@bigclassId)+' ) and (passed = 0 or passed = 1 or passed = 2) and gradeId<='+convert(varchar,@gradeId)+' return'
				when 'news' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from '+convert(varchar,@table)+' where gate<> '+convert(varchar,@gateId)+' and bigclassId='+convert(varchar,@bigclassId)+' and (passed = 0 or passed = 1 or passed = 2) and gradeId<='+convert(varchar,@gradeId) + @searchStr + ' return'
				--when 'gameTitleList' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and bigclassId= '+convert(varchar,@bigclassId)+' and gradeId <= '+convert(varchar,@gradeId)+' return'
				--when 'gamePicList' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and bigclassId= '+convert(varchar,@bigclassId)+' and gradeId <= '+convert(varchar,@gradeId)+' return'
				--when 'gameList' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and bigclassId= '+convert(varchar,@bigclassId)+' and gradeId <= '+convert(varchar,@gradeId)+' return'
				--when 'todayInHistory' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and bigclassId= '+convert(varchar,@bigclassId)+' and gradeId <= '+convert(varchar,@gradeId)+' return'
				--when 'picNews' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and bigclassId='+convert(varchar,@bigclassId)+' and isImageNews=1 and (passed = 0 or passed = 1 or passed = 2) and gradeId<='+convert(varchar,@gradeId)+' return'
				--when 'picNews1' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and bigclassId='+convert(varchar,@bigclassId)+' and isImageNews=1 and (passed = 0 or passed = 1 or passed = 2) and gradeId<='+convert(varchar,@gradeId)+' return'
				--when 'picNews2' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and bigclassId='+convert(varchar,@bigclassId)+' and isImageNews=1 and (passed = 0 or passed = 1 or passed = 2) and gradeId<='+convert(varchar,@gradeId)+' return'
				--when 'mediaNews' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and bigclassId='+convert(varchar,@bigclassId)+' and isImageNews=2 and (passed = 0 or passed = 1 or passed = 2) and gradeId<='+convert(varchar,@gradeId)+' return'
				--when 'special' then ''ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from '+convert(varchar,@table)+' where gate<> '+convert(varchar,@gateId)+' and bigclassId='+convert(varchar,@bigclassId)+' and  gradeId<='+convert(varchar,@gradeId) + @searchStr + ' return''
				ELSE 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from '+convert(varchar,@table)+' where gate<> '+convert(varchar,@gateId)+' and bigclassId='+convert(varchar,@bigclassId)+' and gradeId<='+convert(varchar,@gradeId)+@searchStr+' return'
			end
	END 
	ELSE IF (@isForeGround=1)
	BEGIN
		--前台调用
		select @vSQL=
			case @table
				when 'LatestNews' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and bigClassId in (select bigClassId from BigClassAssociation where thisBigclassId= '+convert(varchar,@bigclassId)+') and gradeId<='+convert(varchar,@gradeId)+' and isDuplicated = 0 and (validDays > 0 OR validDays < 0) and passed=1 return'
				when 'users' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from users where isDel<>1 return'
				when 'popwindow' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from '+convert(varchar,@table)+' where gate<> '+convert(varchar,@gateId)+' and gradeId<='+convert(varchar,@gradeId)+' and passed=1 and (validDays > 0 OR validDays < 0) return'
				--when 'newss' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and (specialId1='+convert(varchar,@bigclassId)+' or specialId2='+convert(varchar,@bigclassId)+' ) and gradeId<='+convert(varchar,@gradeId)+' and passed=1 and (validDays > 0 OR validDays < 0) return'
				when 'news' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from '+convert(varchar,@table)+' where gate<> '+convert(varchar,@gateId)+' and bigclassId='+convert(varchar,@bigclassId)+' and gradeId<='+convert(varchar,@gradeId)+' and passed=1 and (validDays > 0 OR validDays < 0) return'
				when 'gameTitleList' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and bigclassId= '+convert(varchar,@bigclassId)+' and gradeId <= '+convert(varchar,@gradeId)+' and passed=1 and (validDays > 0 OR validDays < 0) return'
				when 'gamePicList' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and bigclassId= '+convert(varchar,@bigclassId)+' and gradeId <= '+convert(varchar,@gradeId)+' and passed=1 and (validDays > 0 OR validDays < 0) return'
				when 'gameList' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and bigclassId= '+convert(varchar,@bigclassId)+' and gradeId <= '+convert(varchar,@gradeId)+' and passed=1 and (validDays > 0 OR validDays < 0) return'
				when 'todayInHistory' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and bigclassId= '+convert(varchar,@bigclassId)+' and gradeId <= '+convert(varchar,@gradeId)+' and passed=1 and (validDays > 0 OR validDays < 0) return'
				when 'picNews' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and bigclassId='+convert(varchar,@bigclassId)+' and isImageNews=1 and gradeId<='+convert(varchar,@gradeId)+' and passed=1 and (validDays > 0 OR validDays < 0) return'
				when 'picNews1' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and bigclassId='+convert(varchar,@bigclassId)+' and isImageNews=1 and gradeId<='+convert(varchar,@gradeId)+' and passed=1 and (validDays > 0 OR validDays < 0) return'
				when 'picNews2' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and bigclassId='+convert(varchar,@bigclassId)+' and isImageNews=1 and gradeId<='+convert(varchar,@gradeId)+' and passed=1 and (validDays > 0 OR validDays < 0) return'
				when 'mediaNews' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and bigclassId='+convert(varchar,@bigclassId)+' and isImageNews=2 and gradeId<='+convert(varchar,@gradeId)+' and passed=1 and (validDays > 0 OR validDays < 0) return'
				when 'special' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and specialId='+convert(varchar,@bigclassId)+' and gradeId<='+convert(varchar,@gradeId) + @searchStr + ' and passed=1 return'	
				when 'messageBoard' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from messageBoard where gate<> '+convert(varchar,@gateId)+' and bigclassId='+convert(varchar,@bigclassId)+' and gradeId<='+convert(varchar,@gradeId)+' and checked=1 and isSecrecy=0 return'	--留言本专用		
				ELSE 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from '+convert(varchar,@table)+' where gate<> '+convert(varchar,@gateId)+' and bigclassId='+convert(varchar,@bigclassId)+' and gradeId<='+convert(varchar,@gradeId)+' and passed=1 return'
			end
	END	
	Execute (@vSQL)
	execute GetCount @count=@num output
	select @count=@num
RETURN




GO

if exists (select * from dbo.sysobjects where id = object_id(N'GetNewsByBigClassIdAndUsersIdCount') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetNewsByBigClassIdAndUsersIdCount
GO
CREATE PROCEDURE GetNewsByBigClassIdAndUsersIdCount
@bigClassId int=null,
@gradeId int=1,
@gateId int=-1,
@usersId int,
@table varchar(100),
@searchStr varchar(100) = '',
@count int output
AS
	 SET NOCOUNT ON	
     declare @num int
     declare @vSQL varchar(1000)
     select @vSQL=
     case @table
        when 'popwindow' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from '+convert(varchar,@table)+' where gate<>'+convert(varchar,@gateId)+' and userId='+convert(varchar,@usersId)+'and gradeId<='+convert(varchar,@gradeId)+' return'
        --when 'newss' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<>'+convert(varchar,@gateId)+' and (specialId1='+convert(varchar,@bigclassId)+' or specialId2='+convert(varchar,@bigclassId)+' ) and usersId='+convert(varchar,@usersId)+' and gradeId<='+convert(varchar,@gradeId)+' return'
        when 'picNews'then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<>'+convert(varchar,@gateId)+' and bigclassId='+convert(varchar,@bigclassId)+' and usersId='+convert(varchar,@usersId)+' and isImageNews=1 and (passed = 0 or passed = 1 or passed = 2) and gradeId<='+convert(varchar,@gradeId)+' return'
        when 'news' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from '+convert(varchar,@table)+' where gate<>'+convert(varchar,@gateId)+' and bigclassId='+convert(varchar,@bigclassId)+' and usersId='+convert(varchar,@usersId)+' and (passed = 0 or passed = 1 or passed = 2) and gradeId<='+convert(varchar,@gradeId)+ @searchStr + ' return'     
        ELSE 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from '+convert(varchar,@table)+' where gate<>'+convert(varchar,@gateId)+' and bigclassId='+convert(varchar,@bigclassId)+' and userId='+convert(varchar,@usersId)+' and gradeId<='+convert(varchar,@gradeId)+' return'
     end
Execute (@vSQL)
execute GetCount @count=@num output
select @count=@num

RETURN

GO

if exists (select * from dbo.sysobjects where id = object_id(N'GetArticleStatistics') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetArticleStatistics
GO
CREATE PROCEDURE GetArticleStatistics
(
	@bigClassId int ,
	@passed int,
    @gradeid int ,
    @gateid int
)	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	declare @num int
	select @num=0 
	select @num= count(pkId) from BigClassAssociation where thisBigClassId = @bigClassId
	if (@num = 0)
		BEGIN			
			
			select count(*) from news n,users u,department d,bigClass b 
							where n.usersId=u.pkId 
							and n.departmentId=d.pkId 
							and n.bigClassId=b.pkId	
							and (n.validDays > 0 OR n.validDays < 0) 
							and n.passed = 1 and isDuplicated != 1
			
		END
	else
		BEGIN
			select count(*) from news n,users u,department d,bigClass b 
						 where n.usersId=u.pkId 
							and n.departmentId=d.pkId 
							and n.bigClassId=b.pkId	
							and (n.validDays > 0 OR n.validDays < 0) 
							and n.passed = 1 
							and isDuplicated != 1 
							--n.bigClassId in (select bigClassId from BigClassAssociation where thisBigClassId=@bigClassId)
							and exists (select 1 from BigClassAssociation ba where ba.bigclassid = n.bigclassid and  ba.thisBigclassId=@bigClassId) 	
		End
END

GO

if exists (select * from dbo.sysobjects where id = object_id(N'InsertWebResearList') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure InsertWebResearList
GO
CREATE PROCEDURE InsertWebResearList
(
	-- Add the parameters for the stored procedure here
	@pkId int,
	@bigClassId int, 
	@questionnaireSpecialName varchar(1000), 
	@validDays int, 
	@gate int, 
	@gradeId int, 
	@IsAnonymity int, 
	@restrictObject int, 
	@restrictMode int, 
	@restrictTime int, 
	@IsVerify int, 
	@generateDate datetime, 
	@IsShow int,
	@IsShowNumber int,
	@ItemType int
)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT OFF;
    -- Insert statements for procedure here
	insert into questionnaireSpecial(bigClassId,questionnaireSpecialName,validDays,gate,gradeId,IsAnonymity,restrictObject,restrictMode,restrictTime,IsVerify,generateDate,IsShow,IsShowNumber,ItemType) values(@bigClassId,@questionnaireSpecialName,@validDays,@gate,@gradeId,@IsAnonymity,@restrictObject,@restrictMode,@restrictTime,@IsVerify,@generateDate,@IsShow,@IsShowNumber,@ItemType)

END

GO

if exists (select * from dbo.sysobjects where id = object_id(N'UpdateWebResearList') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure UpdateWebResearList
GO
CREATE PROCEDURE UpdateWebResearList
	-- Add the parameters for the stored procedure here
	@pkId int,
	@bigClassId int,
	@questionnaireSpecialName varchar(1000), 
	@validDays int, 
	@gate int, 
	@gradeId int, 
	@IsAnonymity int, 
	@restrictObject int, 
	@restrictMode int, 
	@restrictTime int, 
	@IsVerify int, 
	@generateDate datetime, 
	@IsShow int,
	@IsShowNumber int,
	@ItemType int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	update questionnaireSpecial set bigClassId = @bigClassId,
	questionnaireSpecialName=@questionnaireSpecialName,
	validDays=@validDays,
	gate=@gate,
	gradeId=@gradeId,
	IsAnonymity=@IsAnonymity,
	restrictObject=@restrictObject,
	restrictMode=@restrictMode,
	restrictTime=@restrictTime,
	IsVerify =@IsVerify,
	generateDate=@generateDate,
	IsShow=@IsShow,
	IsShowNumber=@IsShowNumber,
	ItemType=@ItemType 
	where pkId = @pkId

END


GO

if exists (select * from dbo.sysobjects where id = object_id(N'GetWebResearSpecialNameByBigClassId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetWebResearSpecialNameByBigClassId
GO
CREATE PROCEDURE GetWebResearSpecialNameByBigClassId
(@pkId int)
AS
SELECT * from questionnaireSpecial where pkId = @pkId

GO

if exists (select * from dbo.sysobjects where id = object_id(N'GetBoardByBigClassId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetBoardByBigClassId
GO
CREATE PROCEDURE GetBoardByBigClassId
@bigClassId int=null,
@gradeid int=1,
@gateId int
AS
	SET NOCOUNT ON
	
	select b.*,u.userName 'userName', g.name 'grade',u.fullName from board b,users u,grade g
	where  b.gradeId<=@gradeId
	 and gate<>@gateId
	 and @bigClassId=bigClassId
	 and b.userId=u.pkId
	 and @gradeId=g.pkId
	order by IsTop desc,b.pkId desc
	RETURN 

GO

if exists (select * from dbo.sysobjects where id = object_id(N'GetBoardByBigClassIdAndUserId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetBoardByBigClassIdAndUserId
GO
CREATE PROCEDURE GetBoardByBigClassIdAndUserId
@bigClassId int=null,
@gradeid int=1,
@gateId int,
@userId int
AS
	SET NOCOUNT ON
	
	select b.*,u.userName 'userName', g.name 'grade',u.fullName from board b,users u,grade g
	where  b.gradeId<=@gradeId
	 and gate<>@gateId
	 and @bigClassId=bigClassId
	 and @userId=b.userId
	 and b.userId=u.pkId
	 and @gradeId=g.pkId
	order by IsTop desc,b.pkId desc
	RETURN 

GO

if exists (select * from dbo.sysobjects where id = object_id(N'GetPeopleByBigClassId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetPeopleByBigClassId
GO
CREATE PROCEDURE GetPeopleByBigClassId
@bigClassId int=null,
@currentPage int=1,
@pageSize int=5,
@gradeid int=1,
@gateId 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
    )

    -- Insert into our temp table
    INSERT	INTO #PageIndexs (ID)
	SELECT	p.pkId
	FROM	[people] AS p 
			INNER JOIN [Users] AS u ON p.userId=u.pkId
			INNER JOIN [grade] AS g ON p.gradeId=g.pkId
	WHERE	(p.gate > @gateId OR p.gate < @gateId) and   
			bigClassId =		@bigClassId and 
			p.gradeId <=		@gradeId 

	ORDER BY p.istop DESC, p.pkId DESC

	SELECT  p.*,u.userName as userName,u.fullName,g.name as grade
	FROM	[people] AS p 
			INNER JOIN [Users] AS u ON p.userId=u.pkId
			INNER JOIN [grade] AS g ON p.gradeId=g.pkId
			INNER JOIN [#PageIndexs] AS pp ON p.PKID = pp.ID
	WHERE	pp.IndexId >= @PageLowerBound AND pp.IndexId <= @PageUpperBound
	ORDER BY p.istop DESC, p.pkId DESC
	
	DELETE #PageIndexs
	
	RETURN 
END

GO

if exists (select * from dbo.sysobjects where id = object_id(N'GetPeopleByBigClassIdAndUserId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetPeopleByBigClassIdAndUserId
GO
CREATE PROCEDURE GetPeopleByBigClassIdAndUserId
@bigClassId int=null,
@currentPage int=1,
@pageSize int=5,
@gradeid int=1,
@userId int,
@gateId int
AS
	SET NOCOUNT ON
	
	declare @vSQL varchar(1000)
select @vSQL = 'select top ' + convert(varchar, @pageSize) + ' p.*,u.userName "userName",u.fullName,g.name "grade" from people p,users u,grade g where p.gate<>'+Convert(varchar,@gateId)+' and  bigClassId='+Convert(varchar,@bigClassId)+' and p.gradeId<='+convert(varchar,@gradeId)+' and userId='+convert(varchar,@userId)+' and p.userId=u.pkId and p.gradeId=g.pkId and p.pkId <> all (select top '+Convert(varchar,((@currentPage-1)*@pageSize))+' pkId from people where gate<>'+Convert(varchar,@gateId)+' and bigClassId='+Convert(varchar,@bigClassId)+' and userId='+convert(varchar,@userId)+' and p.gradeId<='+convert(varchar,@gradeId)+' order by istop desc,p.pkId desc) order by istop desc,p.pkId desc'
Execute (@vSQL)	
	RETURN

GO

if exists (select * from dbo.sysobjects where id = object_id(N'GetPopWindow') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetPopWindow
GO
CREATE PROCEDURE GetPopWindow
@currentPage int=1,
@pageSize int=5,
@gradeid int=1,
@gateId int
AS
	SET NOCOUNT ON;
  
  	declare @vSQL varchar(1000)
select @vSQL = 'select top ' + convert(varchar, @pageSize) + ' p.*,u.userName "userName",u.fullName,g.name "grade" from popwindow p,users u,grade g where p.gate<>'+Convert(varchar,@gateId)+' and p.gradeId<='+convert(varchar,@gradeId)+' and p.gradeId=g.pkId and p.userId=u.pkId and p.pkId <> all (select top '+Convert(varchar,((@currentPage-1)*@pageSize))+' pkId from popwindow where p.gate<>'+Convert(varchar,@gateId)+' and p.gradeId<='+convert(varchar,@gradeId)+' order by istop desc,pkId desc) order by istop desc,p.pkId desc'
Execute (@vSQL)	
return

GO

if exists (select * from dbo.sysobjects where id = object_id(N'GetPopWindowByUserId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetPopWindowByUserId
GO
CREATE PROCEDURE GetPopWindowByUserId
@currentPage int=1,
@pageSize int=5,
@gradeid int=1,
@userId int,
@gateId int
AS
	SET NOCOUNT ON;
	declare @vSQL varchar(1000)
	select @vSQL = 'select top ' + convert(varchar, @pageSize) + ' p.*,u.userName "userName",u.fullName,g.name "grade" from popWindow p,users u,grade g where p.gate<>'+Convert(varchar,@gateId)+' and  p.gradeId<='+convert(varchar,@gradeId)+' and userId='+convert(varchar,@userId)+' and p.userId=u.pkId and p.gradeId=g.pkId and p.pkId < all (select top '+Convert(varchar,((@currentPage-1)*@pageSize))+' pkId from popWindow where p.gate<>'+Convert(varchar,@gateId)+' and p.gradeId<='+convert(varchar,@gradeId)+' and userId='+convert(varchar,@userId)+' order by istop desc,pkId desc) order by istop desc,p.pkId desc'
Execute (@vSQL)	

GO

if exists (select * from dbo.sysobjects where id = object_id(N'GetLinkByBigClassId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetLinkByBigClassId
GO
CREATE PROCEDURE GetLinkByBigClassId
@bigClassId int=null,
@currentPage int=1,
@pageSize int=5,
@gradeid int=1,
@gateId int
AS
	SET NOCOUNT ON 
	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
    )

    -- Insert into our temp table
    INSERT	INTO #PageIndexs (ID)

	SELECT	l.pkId
	FROM	[link] AS l 
			INNER JOIN [Users] AS u ON l.userId = u.pkId
			INNER JOIN [grade] AS g ON l.gradeId=g.pkId
	WHERE	(l.gate > @gateId OR l.gate < @gateId) and   
			l.gradeId <=	@gradeId and 
			l.bigClassId = @bigClassId 

	ORDER BY l.istop desc,l.Priority desc,l.pkid desc

	SELECT  l.*,u.fullname as userName,g.name as grade 
	FROM	[link] AS l 
			INNER JOIN [Users] AS u ON l.userId = u.PKID
			INNER JOIN [grade] AS g ON l.gradeId=g.pkId
			INNER JOIN [#PageIndexs] AS p ON l.PKID = p.ID
	WHERE	p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
	ORDER BY l.istop desc ,l.Priority desc,l.pkid desc

	DELETE #PageIndexs


RETURN 

GO

if exists (select * from dbo.sysobjects where id = object_id(N'GetLinkByBigClassIdAndUserId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetLinkByBigClassIdAndUserId
GO
CREATE PROCEDURE GetLinkByBigClassIdAndUserId
@bigClassId int=null,
@currentPage int=1,
@pageSize int=5,
@gradeid int=1,
@userId int,
@gateId int
AS
	 SET NOCOUNT ON 
	
	declare @vSQL varchar(1000)
	select @vSQL = 'select top ' + convert(varchar, @pageSize) + ' l.*,u.fullname "userName",g.name "grade" from link l,users u,grade g where   l.gate<>'+Convert(varchar,@gateId)+' and   l.bigClassId='+Convert(varchar,@bigClassId)+' and l.gradeId<='+convert(varchar,@gradeId)+' and l.userId='+convert(varchar,@userId)+' and l.gradeId=g.pkId and l.userId=u.pkId  and l.pkId <> all (select top '+Convert(varchar,((@currentPage-1)*@pageSize))+' pkId from link  where bigClassId='+Convert(varchar,@bigClassId)+' and userId='+convert(varchar,@userId)+' and  gradeId<='+convert(varchar,@gradeId)+' order by istop desc,l.Priority,pkId desc)  order by istop desc,l.Priority,l.pkId desc'
	Execute (@vSQL)
	
	RETURN 

GO

if exists (select * from dbo.sysobjects where id = object_id(N'GetAccreditByAdminId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetAccreditByAdminId
GO
CREATE PROCEDURE GetAccreditByAdminId
	@adminId int
AS
	select m.pkId,name,parentId, modeId
	from menu m,adminAssign a
	where 
	a.menuId=0
	and @adminId=a.adminId
	and m.pkId=0
	union
		select m.pkId,name,parentId, m.modeId
	from menu m,adminAssign a
	where 
	parentId=0
	and a.adminId=@adminId
	and a.menuId=m.pkId
	
	union
	select pkId,name,parentId, modeId
	from menu 
	where 
	modeId<>4 
	and parentId<>0
	union
	SELECT m.pkId,m.name,m.parentId, m.modeId
   	FROM menu m,bigClass b,template t,adminAssign a	
	where t.code<>'newsTop'
		and t.code<>'gameTop' 
		and t.code<>'latestGame' 
		and t.code<>'todayInHistoryShort' 
		and t.code<>'todayInHistoryPic' 
		and t.code<>'searchTodayInHistory' 
		and t.code<>'fileDownTop'
		and t.code<>'special'
		and t.code<>'login'
		and t.code<>'nav'
		and t.code<>'search'
		and t.code<>'SearchHorizontal'
		and t.code<>'LoginHorizontal'
	    and t.code<>'baidu'
	   	and t.code<>'shareNews'
		and t.code<>'LatestNews'
		and t.code<>'LatestImageNews'
		and t.code<>'ArticleStatistics'
		and t.code<>'VisitorsSessions'
		and t.code<>'OnlineStatistics'
		and t.code<>'AdminLogin'
		and t.code<>'contributeStat'
		and t.code<>'LatestMessage'
		and t.code<>'contributeTop'
		and t.code<>'latestContribute'
		and t.code<>'newsSummary'
		and t.code<>'messageboardSummary'
		and t.code<>'releaseTop'
		and t.code<>'latestscrollFlashNews' 
		and t.code<>'newsCalendar'
		and m.bigClassId=b.pkId
		and b.templateId=t.pkId		
	  
	RETURN

GO

if exists (select * from dbo.sysobjects where id = object_id(N'GetAccredit') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetAccredit
GO
CREATE PROCEDURE GetAccredit
AS
	select pkId,name,parentId,modeId
	from menu 
	where modeId<>4
	union
	SELECT m.pkId,m.name,m.parentId,m.modeId
   	FROM menu m,bigClass b,template t	
	 where t.code<>'newsTop'
		and t.code<>'gameTop' 
		and t.code<>'latestGame' 
		and t.code<>'todayInHistoryShort' 
		and t.code<>'todayInHistoryPic' 
		and t.code<>'searchTodayInHistory' 
		and t.code<>'fileDownTop'
		and t.code<>'special'
		and t.code<>'login'
		and t.code<>'nav' 
		and t.code<>'LoginHorizontal'
		and t.code<>'baidu'
		and t.code<>'shareNews'
		and t.code<>'LatestNews'
		and t.code<>'LatestImageNews'
		and t.code<>'ArticleStatistics'
		and t.code<>'VisitorsSessions'
		and t.code<>'OnlineStatistics'
		and t.code<>'AdminLogin' 
		and t.code<>'contributeStat'
		and t.code<>'LatestMessage' 
		and t.code<>'contributeTop'
		and t.code<>'latestContribute'
		and t.code<>'newsSummary'
		and t.code<>'messageboardSummary' 
		and t.code<>'releaseTop'
		and t.code<>'latestscrollFlashNews' 
		and t.code<>'newsCalendar'
		and m.bigClassId=b.pkId
		and b.templateId=t.pkId		
	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 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 @str3 = @str3 + ' and checked=0 and gradeId<='+STR(@gradeId)
	SET @str3 = @str3 + ' and 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.menuid 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 @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(pkId) from messageBoard 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,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'GetAuditingMessageBoardByUserId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetAuditingMessageBoardByUserId
GO
CREATE PROCEDURE GetAuditingMessageBoardByUserId
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeId int,
	@userId int,
	@gateId int,
	@manageAll varchar(1000),
	@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产生依赖，避免因栏目移动产生错误
--根据栏目Id进行分组，以消除不用菜单位置的相同栏目产生的重复记录，20091010
select b.pkId bigclassId, m.name menuName,max(m.pkId) sourceMenuId into #MessageBoardTemp 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 m.bigclassId = z.bigclassId'
	SET @str = @str + ' and z.bigclassId = b.pkid'
	SET @str = @str + ' and m.checked=0 and m.gradeId<='+STR(@gradeId)
	SET @str = @str + ' and m.gate<>'+STR(@gateId)
	SET @str = @str + ' and m.gradeId=g.pkId '

	SET @tableName = '#MessageBoardTemp z, messageBoard m, grade g , bigClass b '

end
else
begin
	select b.pkId bigclassId, m.name menuName,max(m.pkId) sourceMenuId into #MessageBoardTemp1 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 m.bigclassId = z.bigclassId'
	SET @str = @str + ' and z.bigclassId = b.pkid'
	SET @str = @str + ' and m.checked=0 and m.gradeId<='+STR(@gradeId)
	SET @str = @str + ' and m.gate<>'+STR(@gateId)
	SET @str = @str + ' and m.gradeId=g.pkId '

	SET @tableName = '#MessageBoardTemp1 z, messageBoard m, grade g , bigClass b '
end
	--设置查询排序（不带order by）
	DECLARE @orderTypeA nvarchar(100)
	DECLARE @orderTypeD nvarchar(100)
	SET @orderTypeA = 'isTop ASC, msgId ASC'
	SET @orderTypeD = 'isTop DESC, msgId DESC'
	
	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT @count = Count(m.pkId) from '+Convert(nvarchar(150),@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(2000),@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) + ' z.menuName,z.sourceMenuId, m.*,g.name "grade", m.pkId as msgId FROM '+Convert(nvarchar(150),@tableName)+' where '+Convert(nvarchar(1000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeD)	
	--查询最后一页
	ELSE IF @currentPage = @pageCount -1			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' z.menuName,z.sourceMenuId, m.*,g.name "grade", m.pkId as msgId  FROM '+Convert(nvarchar(150),@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 ) + ' z.menuName,z.sourceMenuId, m.*,g.name "grade", m.pkId as msgId FROM '+Convert(nvarchar(150),@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 ) + ' z.menuName,z.sourceMenuId,m.*,g.name "grade", m.pkId as msgId FROM '+Convert(nvarchar(150),@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)

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 = 'updateTime ASC, sourceMenuId ASC, newsId ASC'
	SET @orderTypeD = 'updateTime 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.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.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.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.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'GetPeopleByBigClassIdAndPassed') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetPeopleByBigClassIdAndPassed
GO

CREATE PROCEDURE GetPeopleByBigClassIdAndPassed
@bigClassId int=null,
@currentPage int=1,
@pageSize int=5,
@gradeid int=1,
@gateId int,
@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
    )

    -- Insert into our temp table
    INSERT	INTO #PageIndexs (ID)
	SELECT	p.pkId
	FROM	[people] AS p 
			INNER JOIN [Users] AS u ON p.userId=u.pkId
			INNER JOIN [grade] AS g ON p.gradeId=g.pkId
	WHERE	(p.gate > @gateId OR p.gate < @gateId) and   
			bigClassId = @bigClassId and 
			p.gradeId <= @gradeId and 
			passed = @passed
	ORDER BY p.istop DESC, p.pkId DESC

	SELECT  p.*,u.userName as userName,u.fullName,g.name as grade
	FROM	[people] AS p 
			INNER JOIN [Users] AS u ON p.userId=u.pkId
			INNER JOIN [grade] AS g ON p.gradeId=g.pkId
			INNER JOIN [#PageIndexs] AS pp ON p.PKID = pp.ID
	WHERE pp.IndexId >= @PageLowerBound AND pp.IndexId <= @PageUpperBound
	ORDER BY p.istop DESC, p.pkId DESC
	
	DELETE #PageIndexs
	
	RETURN 
END
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 = 'updateTime ASC, sourceMenuId ASC, newsId ASC'
	SET @orderTypeD = 'updateTime 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.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(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.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(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.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(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.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(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

--中策烹饪博物馆手机APP数据同步接口
if exists (select * from dbo.sysobjects where id = object_id(N'ZCGetMessageByTime') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure ZCGetMessageByTime
GO

CREATE PROCEDURE ZCGetMessageByTime
@beginTime datetime,
@endTime datetime,
@bigClassId int
AS
BEGIN
	if(@bigClassId=0)
	begin
		SELECT pkId as ID,intactTitle as Title ,descript as Introduction ,ImageName,[content] as [Content],UpdateTime
		FROM [News] 
		WHERE bigclassid in (272,270,469) and updatetime between @beginTime and @endTime
		ORDER BY istop desc,Priority desc,updateTime desc,pkid desc
	end
	else
	begin
		SELECT pkId as ID,intactTitle as Title ,descript as Introduction ,ImageName,[content] as [Content],UpdateTime
		FROM [News] 
		WHERE bigclassid=@bigClassId and updatetime between @beginTime and @endTime
		ORDER BY istop desc,Priority desc,updateTime desc,pkid desc
	end
	RETURN 
END

GO

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

CREATE PROCEDURE GetMessageBoardByBigClassId
@bigClassId int=null,
@currentPage int=1,
@pageSize int=5,
@gradeid int=1,
@gateId int,
@searchStr varchar(100)='',
@recordCount int output

AS
	SET NOCOUNT ON

	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(m.pkId) from 
			messageBoard AS m 
			INNER JOIN [grade] as g on m.gradeId=g.pkId
			WHERE (m.gate > ' + STR(@gateId) + ' OR m.gate < ' + STR(@gateId) + ') and  
			m.bigClassId = ' + STR(@bigClassId) +' and 
			m.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	m.pkId
	FROM	messageBoard AS m 
			INNER JOIN [grade] as g on m.gradeId=g.pkId
	WHERE	(m.gate > ' + STR(@gateId) + ' OR m.gate < ' + STR(@gateId) + ') and   
			m.bigClassId = ' + STR(@bigClassId) +' and 	
			m.gradeId <= ' + STR(@gradeId) + ' ' +
			@searchStr + ' ' + '
	ORDER BY m.istop desc,m.updateTime desc,m.pkid desc;
	
	select m.pkId,m.title,m.checked,m.author,m.tel,m.isSecrecy,m.email,m.sex,m.userId
	,m.updatetime ,m.updateIp, m.click,m.isTop, m.MenuId,m.gradeId,m.bigclassId,m.gate,m.username,m.password
	,m.retime,m.redept,m.reAudit ,g.name as grade
	FROM	messageBoard AS m
			INNER JOIN [grade] as g on m.gradeId=g.pkId
			INNER JOIN [#PageIndexs] AS p ON m.pkId = p.ID
	WHERE	p.IndexId >= ' + STR(@PageLowerBound) + ' AND p.IndexId <= ' + STR(@PageUpperBound) + ' 
	ORDER BY m.istop desc,m.updateTime desc,m.pkid desc;

	DELETE #PageIndexs'
	EXEC(@sql)
	
	RETURN 
GO

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

CREATE PROCEDURE InsertInfoSearch
	@pkId int output,
	@MenuID int,
	@BigClassID int,
	@Condition varchar(1500),
	@WhereCondition varchar(1500),
	@FieldList varchar(2000),
	@Result varchar(2000),
	@TableName varchar(255),
	@Memo ntext,
	@NewsID int
AS
BEGIN
	SET NOCOUNT OFF;
	BEGIN TRANSACTION
		INSERT INTO InfoSearch(menuId, bigClassId, condition, wherecondition, fieldList, result, tableName, memo,newsId) 
		VALUES (@MenuID, @BigClassID, @Condition, @WhereCondition, @FieldList, @Result, @TableName, @Memo,@NewsID)
	COMMIT TRANSACTION
	SET @pkId = SCOPE_IDENTITY()
END
GO

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

CREATE PROCEDURE UpdateInfoSearch
	@pkId int,
	@MenuID int,
	@BigClassID int,
	@Condition varchar(1500),
	@WhereCondition varchar(1500),
	@FieldList varchar(2000),
	@Result varchar(2000),
	@TableName varchar(255),
	@Memo ntext,
	@NewsID int
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE InfoSearch SET 
		menuId = @MenuID, 
		bigClassId = @BigClassID, 
		condition = @Condition, 
		wherecondition = @WhereCondition, 
		fieldList = @FieldList,
		result = @Result, 
		tableName = @TableName, 
		memo = @Memo,
		newsId = @NewsID
	WHERE pkId = @pkId
END

GO

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

CREATE PROCEDURE GetInfoSearchByNewsId
	@NewsID int 
AS
BEGIN
	SET NOCOUNT ON;
	SELECT TOP 1 * FROM InfoSearch WHERE newsId = @NewsID
END

GO

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

CREATE PROCEDURE DeleteInfoSearchByNewsId
	@NewsID int
AS
BEGIN	
	SET NOCOUNT ON;
	DELETE FROM InfoSearch WHERE newsId = @NewsID
END

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 = 'updateTime ASC, sourceMenuId ASC, newsId ASC'
	SET @orderTypeD = 'updateTime 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.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(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.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(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.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(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.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(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'GetPeopleByBigClassIdAndPassed') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetPeopleByBigClassIdAndPassed
GO

CREATE PROCEDURE GetPeopleByBigClassIdAndPassed
@bigClassId int=null,
@currentPage int=1,
@pageSize int=5,
@gradeid int=1,
@gateId int,
@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
    )

    -- Insert into our temp table
    INSERT	INTO #PageIndexs (ID)
	SELECT	p.pkId
	FROM	[people] AS p 
			INNER JOIN [Users] AS u ON p.userId=u.pkId
			INNER JOIN [grade] AS g ON p.gradeId=g.pkId
	WHERE	(p.gate > @gateId OR p.gate < @gateId) and   
			bigClassId = @bigClassId and 
			p.gradeId <= @gradeId and 
			passed = @passed
	ORDER BY p.istop DESC, p.pkId DESC

	SELECT  p.*,u.userName as userName,u.fullName,g.name as grade
	FROM	[people] AS p 
			INNER JOIN [Users] AS u ON p.userId=u.pkId
			INNER JOIN [grade] AS g ON p.gradeId=g.pkId
			INNER JOIN [#PageIndexs] AS pp ON p.PKID = pp.ID
	WHERE pp.IndexId >= @PageLowerBound AND pp.IndexId <= @PageUpperBound
	ORDER BY p.istop DESC, p.pkId DESC
	
	DELETE #PageIndexs
	
	RETURN 
END

GO



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

CREATE PROCEDURE GetNewsByPage
	@searchStr varchar(500),
	@pageIndex int,
	@pageSize int,
	@pageCount int output,
	@recordCount int output
AS
BEGIN
	DECLARE @tempNum int
	DECLARE @vSQL nvarchar(2000)
	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 [bigclass] As b ON n.bigclassId=b.pkId 
				WHERE' + @searchStr
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum
	SET @pageCount = (@recordCount + @pageSize-1)/@pageSize
	
	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 @s Nvarchar(1000)

	if(@pageIndex=0) --优化第一页访问速度
	begin
		-- Insert into our temp table
	    SET @s='INSERT INTO #PageIndexs (ID)
		SELECT	TOP '+STR(@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
				INNER JOIN [bigclass] As b ON n.bigclassId=b.pkId
		WHERE ' +@searchStr+'
		ORDER BY n.istop desc,n.Priority desc,n.pkid desc'
		EXEC(@s)
	end
	else
	begin
    	-- Insert into our temp table
    	SET @s='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 [bigclass] As b ON n.bigclassId=b.pkId
		WHERE '+@searchStr+'
		ORDER BY n.istop desc,n.Priority desc,n.pkid desc'
		EXEC(@s)
	end
	SELECT n.pkId,n.intactTitle,n.updateTime,u.fullName, n.isduplicated,
case n.auditUserID when 0 then '' else (select fullname from users where pkid=n.auditUserID) end auditUser,
d.name as deptname,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 [#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
GO

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

CREATE PROCEDURE GetNewsCount
 (
	-- Add the parameters for the stored procedure here
	@gradeid int=0,
	@begintime varchar(20), 
	@endtime varchar(20),
	@bigclassyn int=0,
	@bigclassid int=0,
	@passed int=1
)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT OFF;
	declare @select varchar(500);  
	declare @from varchar(500);
	declare @group varchar(500) ;
	declare @where varchar(500);
    -- Insert statements for procedure here
    set @select='Select (select name from department where pkid=N.departmentId) deptname, ';
	set	@select=@select+' case U.isDel when 1 then U.username'+'+'+'''<已删除>'''+' else U.username end username,U.fullname,';
	set	@select=@select+ ' COUNT(N.pkId) fbcount,SUM(case passed when 1 then 1 else 0 end) passedcount,SUM(N.click) clicksum ';
	set @from = ' From news N, bigclass B, users U ';
	set @where =' Where N.bigclassId=B.pkId and N.usersId = U.pkId ';
	set @group = ' Group by N.departmentId,U.fullname,U.username,U.isDel ';
	
	if(@gradeid <>0)
		set @where=@where+' and N.gradeId='+STR(@gradeId);
		
	if(@begintime !='')
		set @where = @where + ' and N.updatetime >='''+ @begintime+'''';
	if(@endtime  !='')
		set @where = @where + ' and N.updatetime <=''' + @endtime+'''';			
	
	if (@bigclassYn =1 )  
		begin
			if (@bigclassId <> 0 )
			begin
			    set @where = @where + ' and N.bigclassId ='+STR(@bigclassid);
			end		
			
			set @select = @select+',B.name'	--如果选择了按栏目查，就自动按栏目分组
			set @group = @group+',B.name order by N.departmentId ';
		end
	else
		begin
			set @select= @select+',''所有栏目''as name';
			set @group = @group+' order by N.departmentId ';
		end
	if(@passed <> 2)
	set @where=@where+' and N.passed='+STR(@passed);
	--执行查询
	EXEC(@select + @from + @where + @group );

END

GO