--v6.5.0版 补丁包

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

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

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

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

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

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

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

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

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

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



GO

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

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

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

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

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

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

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

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



GO

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

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

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

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

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

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

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

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

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

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

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


GO

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

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


GO




--处理用户组冗余数据
delete groupAssign where menuId not in (select pkId from menu)
--处理用户授权冗余数据
delete moduleAssign where menuId not in (select pkId from menu)
--处理抄送信息审核异常数据
update news set passed='1' where passed is null

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

GO

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

GO


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

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

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

GO



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

end
else
begin

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

end

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

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

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

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

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

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


GO



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

end
else
begin

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

end

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

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

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

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

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

--20170427 
--先清理栏目权限关系表  防止引起外键约束删除失败。
truncate table templatePrivilege 
GO
--清理PKID大于74的栏目。
delete template where pkId > 74
GO

--20170427  v6.1版本增加了两个栏目：发文统计排行、文章列表(头条)
--新增发文统计排行栏目
if not exists(select * from template where pkId=75)
begin
SET IDENTITY_INSERT [template] ON
insert into template ([pkId],[name],[remark],[code],[previewTemplate],[typeId])values('75','发文统计排行','发文统计排行','releaseTop','releaseTop.htm',5)
SET IDENTITY_INSERT [template] OFF
end
GO

--新增文章列表（头条）栏目
if not exists(select * from template where pkId=76)
begin
SET IDENTITY_INSERT [template] ON
insert into template ([pkId],[name],[remark],[code],[previewTemplate],[typeId])values('76','文章列表(头条)','文章列表(头条)','news1','news1.htm',1)
SET IDENTITY_INSERT [template] OFF
end
GO


--20170427 增加修复系统栏目表的数据   v6.5增加了三个栏目： 图文翻动(宽版)、最新图文翻动、信息查询(新版)
--新增图文翻动(宽版)栏目
if not exists(select * from template where pkId=77)
begin
SET IDENTITY_INSERT [dbo].[template] ON
insert into template ([pkId],[name],[remark],[code],[previewTemplate],[typeId])values('77','图文翻动(宽版)','图文翻动(宽版)','scrollFlashNews1','scrollFlashNews1.htm',2)
SET IDENTITY_INSERT [dbo].[template] OFF
end
GO

--v6.3版新增脚本请在此后开始维护
--新增最新图文翻动栏目
if not exists(select * from template where pkId=78)
begin
SET IDENTITY_INSERT [template] ON
insert into template ([pkId],[name],[remark],[code],[previewTemplate],[typeId])values('78','最新图文翻动','最新图文翻动','latestscrollFlashNews','latestscrollFlashNews.htm',2)
SET IDENTITY_INSERT [template] OFF
end
GO

--新增信息查询(新版)栏目
if not exists(select * from template where pkId=79)
begin
SET IDENTITY_INSERT [template] ON
insert into template ([pkId],[name],[remark],[code],[previewTemplate],[typeId])values('79','信息查询(新版)','信息查询(新版)','infoSearch1','infoSearch1.htm',5)
SET IDENTITY_INSERT [template] OFF
end
GO


--20170427 v6.5  增加修复系统权限表13、14两个状态
--增加留言本用到的系统权限
delete privilege where pkId > 12
GO

if not exists(select * from privilege where pkId=13)
begin
SET IDENTITY_INSERT [privilege] ON
insert into privilege ([pkId],[name],nameRemark,accreditValue,accreditRemark,showType,itemEnglish,itemChinese) 
values ('13','canAuditReply','回复审核','0|1','禁止|允许','Select','forAuditing|audited','待审|通过')
SET IDENTITY_INSERT [privilege] OFF
end
go

if not exists(select * from privilege where pkId=14)
begin
SET IDENTITY_INSERT [privilege] ON
insert into privilege ([pkId],[name],nameRemark,accreditValue,accreditRemark,showType,itemEnglish,itemChinese) 
values ('14','defaultReplyPassed','回复默认审核状态','0|1','未审|已审','Select','','')
SET IDENTITY_INSERT [privilege] OFF
end
go


--重置v6.5系统栏目权限关系表 201704
GO
SET IDENTITY_INSERT [dbo].[templatePrivilege] ON
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (1, 1, 1)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (2, 1, 3)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (3, 1, 4)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (4, 1, 5)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (5, 1, 6)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (6, 1, 7)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (7, 3, 1)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (8, 3, 4)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (9, 3, 5)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (10, 3, 6)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (11, 3, 7)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (12, 4, 1)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (13, 4, 4)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (14, 4, 5)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (15, 4, 6)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (16, 4, 7)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (17, 5, 1)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (18, 5, 4)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (19, 5, 5)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (20, 5, 6)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (21, 5, 7)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (22, 6, 1)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (23, 6, 4)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (24, 6, 5)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (25, 6, 6)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (26, 6, 7)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (27, 12, 1)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (28, 12, 3)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (29, 12, 4)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (30, 12, 5)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (31, 12, 6)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (32, 12, 7)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (33, 14, 5)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (34, 14, 6)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (35, 14, 7)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (36, 15, 1)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (37, 15, 4)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (38, 15, 5)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (39, 15, 6)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (40, 15, 7)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (41, 16, 1)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (42, 16, 4)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (43, 16, 5)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (44, 16, 6)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (45, 16, 7)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (46, 23, 1)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (47, 23, 3)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (48, 23, 4)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (49, 23, 5)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (50, 23, 6)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (51, 23, 7)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (52, 24, 1)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (53, 24, 3)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (54, 24, 4)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (55, 24, 5)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (56, 24, 6)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (57, 24, 7)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (58, 25, 1)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (59, 25, 3)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (60, 25, 4)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (61, 25, 5)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (62, 25, 6)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (63, 25, 7)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (64, 26, 1)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (65, 26, 3)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (66, 26, 4)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (67, 26, 5)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (68, 26, 6)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (69, 26, 7)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (70, 27, 1)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (71, 27, 3)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (72, 27, 4)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (73, 27, 5)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (74, 27, 6)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (75, 27, 7)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (76, 32, 1)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (77, 32, 4)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (78, 32, 5)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (79, 32, 6)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (80, 32, 7)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (81, 34, 1)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (82, 34, 4)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (83, 34, 5)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (84, 34, 6)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (85, 34, 7)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (86, 35, 1)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (87, 35, 4)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (88, 35, 5)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (89, 35, 6)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (90, 35, 7)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (91, 36, 1)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (92, 36, 3)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (93, 36, 4)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (94, 36, 5)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (95, 36, 6)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (96, 36, 7)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (97, 37, 1)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (98, 37, 4)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (99, 37, 5)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (100, 37, 6)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (101, 37, 7)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (102, 45, 2)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (103, 45, 3)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (104, 45, 4)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (105, 45, 5)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (106, 45, 6)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (107, 45, 7)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (108, 1, 8)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (109, 3, 8)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (110, 4, 8)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (111, 5, 8)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (112, 6, 8)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (113, 12, 8)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (115, 15, 8)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (116, 16, 8)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (117, 23, 8)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (118, 24, 8)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (119, 25, 8)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (120, 26, 8)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (121, 27, 8)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (122, 32, 8)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (123, 34, 8)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (124, 35, 8)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (125, 36, 8)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (126, 37, 8)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (127, 1, 9)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (128, 3, 9)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (129, 4, 9)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (130, 5, 9)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (131, 6, 9)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (132, 12, 9)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (134, 15, 9)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (135, 16, 9)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (136, 23, 9)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (137, 24, 9)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (138, 25, 9)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (139, 26, 9)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (140, 27, 9)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (141, 32, 9)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (142, 34, 9)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (143, 35, 9)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (144, 36, 9)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (145, 37, 9)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (146, 43, 1)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (147, 43, 3)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (148, 43, 4)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (149, 43, 5)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (150, 43, 6)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (151, 43, 7)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (152, 43, 8)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (153, 43, 9)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (154, 49, 1)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (155, 49, 3)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (156, 49, 4)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (157, 49, 5)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (158, 49, 6)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (159, 49, 7)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (160, 49, 8)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (161, 49, 9)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (162, 50, 1)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (163, 50, 3)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (164, 50, 4)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (165, 50, 5)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (166, 50, 6)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (167, 50, 7)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (168, 50, 8)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (169, 50, 9)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (170, 53, 1)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (171, 53, 3)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (172, 53, 4)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (173, 53, 5)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (174, 53, 6)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (175, 53, 7)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (176, 53, 8)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (177, 53, 9)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (178, 54, 1)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (179, 54, 3)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (180, 54, 4)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (181, 54, 5)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (182, 54, 6)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (183, 54, 7)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (184, 54, 8)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (185, 54, 9)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (186, 58, 5)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (187, 58, 6)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (192, 60, 5)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (193, 60, 6)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (194, 60, 7)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (198, 9, 6)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (199, 17, 6)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (200, 62, 6)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (201, 63, 1)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (202, 63, 4)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (203, 63, 5)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (204, 63, 6)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (205, 63, 7)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (206, 63, 8)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (207, 63, 9)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (208, 65, 1)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (209, 65, 4)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (210, 65, 5)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (211, 65, 6)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (212, 65, 7)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (213, 65, 8)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (214, 65, 9)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (215, 64, 5)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (216, 64, 6)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (217, 64, 7)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (218, 64, 9)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (219, 64, 10)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (220, 64, 11)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (221, 64, 12)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (222, 67, 6)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (223, 70, 5)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (224, 70, 6)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (225, 70, 9)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (226, 76, 1)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (227, 76, 3)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (228, 76, 4)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (229, 76, 5)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (230, 76, 6)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (231, 76, 7)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (232, 76, 8)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (233, 76, 9)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (234, 77, 1)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (235, 77, 3)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (236, 77, 4)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (237, 77, 5)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (238, 77, 6)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (239, 77, 7)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (240, 77, 8)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (241, 77, 9)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (242, 32, 13)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (243, 32, 14)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (244, 63, 13)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (245, 63, 14)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (246, 79, 1)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (247, 79, 3)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (248, 79, 4)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (249, 79, 5)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (250, 79, 6)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (251, 79, 7)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (252, 79, 8)
INSERT [dbo].[templatePrivilege] ([pkId], [templateId], [privilegeId]) VALUES (253, 79, 9)
SET IDENTITY_INSERT [dbo].[templatePrivilege] OFF
