--用户组功能,删除用户组权限
if exists (select * from dbo.sysobjects where id = object_id(N'DeleteGroupAssign') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure DeleteGroupAssign
GO
create PROCEDURE DeleteGroupAssign
(
	@groupId int,
	@ModuleId int
)
AS

DELETE FROM groupAssign WHERE (groupId=@groupId and MenuId=@ModuleId)

GO
--用户组功能
if exists (select * from dbo.sysobjects where id = object_id(N'GetUsersGroupByGroupName') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetUsersGroupByGroupName
GO
create PROCEDURE GetUsersGroupByGroupName
@groupName  varchar(100)=NULL
AS

select * from usersGroup where groupName=@groupName

GO
--用户组功能,新增用户组权限
if exists (select * from dbo.sysobjects where id = object_id(N'InsertGroupAssign') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure InsertGroupAssign
GO
create PROCEDURE InsertGroupAssign
@pkId int=null,
@groupId  int,	
@moduleId int,
@privilegeSet varchar(1000)
AS

insert groupAssign(groupId, menuId, privilegeSet) values(@groupId, @moduleId, @privilegeSet)

GO
--用户组功能，根据用户组ID查询用户组权限
if exists (select * from dbo.sysobjects where id = object_id(N'GetGroupAssignByGroupId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetGroupAssignByGroupId
GO
create PROCEDURE GetGroupAssignByGroupId
@groupId int=null
AS

select * from groupAssign where groupId=@groupId

GO
--用户组功能，根据用户组ID查询用户权限
if exists (select * from dbo.sysobjects where id = object_id(N'GetUsersByUsersGroupId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetUsersByUsersGroupId
GO
create PROCEDURE GetUsersByUsersGroupId
(
	@usersGroupId int
)
AS

select * from users where usersGroupId=@usersGroupId

GO
--用户组功能，删除用户组。（老版本中已经有此存储过程所以此次属于修改）
if exists (select * from dbo.sysobjects where id = object_id(N'DeleteUsersGroup') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure DeleteUsersGroup
GO
create PROCEDURE DeleteUsersGroup
(
	@pkId int
)
AS
	SET NOCOUNT OFF;
DELETE FROM UsersGroup WHERE pkId = @pkId
DELETE FROM groupAssign where groupId = @pkId

GO
if exists (select * from dbo.sysobjects where id = object_id(N'GetMenuAssign') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetMenuAssign
GO
create PROCEDURE GetMenuAssign
@gradeId int=1,
@userId int

AS

Declare @groupid int 

select @groupid = usersGroupId from users where pkid=@userId

if(@groupid=1)
begin
	SELECT m.*, g.name grade, o.name mode
	FROM menu m,grade g,mode o,moduleAssign a
	where m.gradeId<=@gradeId
	  and m.gradeId=g.pkId
	  and m.modeId=o.pkId
	  and a.usersId=@userId
	  and m.pkId=a.menuId
	order by ordering desc
end
else
begin
	SELECT m.*, g.name grade, o.name mode
	FROM menu m,grade g,mode o,groupAssign a
	where m.gradeId<=@gradeId
	  and m.gradeId=g.pkId
	  and m.modeId=o.pkId
	  and a.groupId=@groupid
	  and m.pkId=a.menuId
	order by ordering desc
end

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

	--判断当前用户是否属于用户组
	Declare @groupid int 
select @groupid = usersGroupId from users where pkid=@usersId
if(@groupid=1)
begin
	select * from moduleAssign where usersId=@usersId and menuId=@menuId
end
else
begin
	select * from groupAssign where groupId=@groupid and menuId=@menuId
end

RETURN 

GO

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

--判断当前用户是否属于用户组
	Declare @groupid int 
select @groupid = usersGroupId from users where pkid=@usersId
if(@groupid=1)
begin
	select a.* from moduleAssign a, menu m
	where m.pkId=a.menuId
	and a.usersId=@usersId
	and m.bigClassId=@bigClassId
end
else
begin
	select a.* 
	from groupAssign a, menu m
	where m.pkId=a.menuId
	and a.groupId=@groupid
	and m.bigClassId=@bigClassId
end
RETURN

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(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 #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 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 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 = '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,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.*,u.userName,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.*,u.userName,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.*,u.userName,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'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

	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

	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,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,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,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,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 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 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,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,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,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,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 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 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,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,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,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,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'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 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 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'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 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 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,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,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,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,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(4000)
	--设置查询表名称
	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 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 and n.departmentId=d.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.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 and n.departmentId=d.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 = '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,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.*,u.userName,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.*,u.userName,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.*,u.userName,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'GetMenuTreeByParentId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetMenuTreeByParentId
GO
CREATE PROCEDURE GetMenuTreeByParentId
@parentId int
AS

	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 parentId=@parentId and m.bigClassId<>-1 and modeId<>4 order by ordering  desc
	RETURN 

GO

--根据布局ID查询栏目最新最热等参数设置
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 * from bigClassDesign where menuid=@menuId
END

GO

--在布局中添加栏目的同时，插入对应的栏目设置记录
if exists (select * from dbo.sysobjects where id = object_id(N'InsertBigClassDesign') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure InsertBigClassDesign
GO
CREATE  PROCEDURE InsertBigClassDesign
(
	@pkId int,
	@menuId int,
	@cssname VarChar(100),
	@isFlag Int,
	@imageFlag VarChar(100),
	@isNew Int, 
	@imageNew VarChar(100),
	@isHot Int, 
	@imageHot VarChar(100),
	@isTop Int, 
	@imageTop VarChar(100),
	@isMore Int,
	@imageMore VarChar(100),
	@isDept Int,
	@isClick Int,
	@isTime Int
)
AS
	SET NOCOUNT OFF;
INSERT INTO bigClassDesign(menuId, cssname, isFlag,imageFlag,isNew,imageNew,isHot,imageHot,isTop,imageTop,isMore,imageMore,isDept,isClick,isTime)
 VALUES (@menuId, @cssname,@isFlag,@imageFlag,@isNew,@imageNew,@isHot,@imageHot,@isTop,@imageTop,@isMore,@imageMore,@isDept,@isClick,@isTime)



GO
--更新栏目设置参数

if exists (select * from dbo.sysobjects where id = object_id(N'UpDateBigClassDesign') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure UpDateBigClassDesign
GO
CREATE PROCEDURE UpDateBigClassDesign
(
	@pkId int,
	@menuId int,
	@cssname VarChar(100),
	@isFlag Int,
	@imageFlag VarChar(100),
	@isNew Int, 
	@imageNew VarChar(100),
	@isHot Int, 
	@imageHot VarChar(100),
	@isTop Int, 
	@imageTop VarChar(100),
	@isMore Int,
	@imageMore VarChar(100),
	@isDept Int,
	@isClick Int,
	@isTime Int
)
AS
SET NOCOUNT OFF;
BEGIN TRANSACTION;		-- added on 201212 by shijian
	UPDATE bigClassDesign SET cssname = @cssname, isFlag = @isFlag ,imageFlag=@imageFlag,isNew=@isNew,imageNew=@imageNew,isHot=@isHot,imageHot=@imageHot,isTop=@isTop, imageTop=@imageTop,isMore = @isMore,imageMore=@imageMore,isDept=@isDept,isClick=@isClick,isTime=@isTime  WHERE (pkId = @pkId);
COMMIT TRANSACTION ;


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(passed) 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






--删除布局时同步删除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 @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'InsertBigClass') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure InsertBigClass
GO
CREATE PROCEDURE InsertBigClass
(
	@pkId int output,
	@name varchar(100),
	@remark varchar(100),
	@templateId int,
	@adminId int,
	@updateTime datetime,
	@isSystemItem int,
	@cssName varchar(100), -- added on 20060328 by wjh
    @morePageSize int
)
AS
	SET NOCOUNT OFF;
INSERT INTO bigClass(name, remark, templateId,adminId,updateTime,isSystemItem,cssName,morePageSize) VALUES (@name, @remark, @templateId,@adminId,@updateTime,@isSystemItem, @cssName,@morePageSize)
SET @pkId = SCOPE_IDENTITY() 

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
)
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) VALUES (@parentId, @name, @remark, @ordering, @address,@bigClassId, @modeId, @gradeId,@gate,@recorderNum,@menuPic,@itemId,@layoutTemplateId,@adminId,@displayRoot,@userIntercept,@interceptNums,@columnNum,@artDesignTemplateId, @contributeSortType,@navigate,@newsTemplateId)
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'GetShowItem') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetShowItem
GO
CREATE PROCEDURE GetShowItem
@gradeId int=1,
@parentId int,
@gateId int
AS
	select m.*,d.*,t.code,b.name as bigclassname
	  from menu m 
	  inner join bigClass b on b.pkId=m.bigClassId
	  inner join template t on t.pkId=b.templateId
	  left  join bigclassDesign d on  d.menuId =m.pkId
	  where m.parentId=@parentId
	    and m.gate<>@gateId
	    and m.gradeId<=@gradeId
	order by m.pkId desc,ordering desc
	RETURN

GO

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 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


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'GetArtDesignByState') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetArtDesignByState
GO
Create PROCEDURE GetArtDesignByState
AS
	SET NOCOUNT ON;
--TODO：是否更新被使用标志，待定
update artDesignTemplate set artstate ='0';
update artDesignTemplate set artstate ='1' where pkid in (SELECT distinct m.artDesignTemplateId FROM menu m 
inner join artDesignTemplate a 
on  m.artDesignTemplateId=a.pkId
where m.modeId=4 and m.artDesignTemplateId!=0)

--返回频道页的模板集合
SELECT pkId FROM  artDesignTemplate  
where artState='1'

GO

if exists (select * from dbo.sysobjects where id = object_id(N'InsertArtDesignTemplate') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure InsertArtDesignTemplate
GO
CREATE PROCEDURE InsertArtDesignTemplate
(
	@pkId int,
	@name varchar(2000),
	@leftTopFrontImageVar varchar(100),
	@leftMiddleFrontImageVar varchar(100),
	@leftMiddleBackImageVar varchar(100),
	@leftVert varchar(100),
	@leftMiddleFrontWidth varchar(100),
	@leftMiddleFrontHeight varchar(100),
	@leftMiddleFrontLink varchar(100),
	@leftBottomFrontImageVar varchar(100),
	@centerTopFrontImageVar varchar(100),
	@centerTopBackImageVar varchar(100),
	@centerTopFrontWidth varchar(100),
	@centerTopFrontHeight varchar(100),
	@centerTopFrontLink varchar(100),
	@centerMiddleBackImageVar varchar(100),
	@itemImageVar varchar(100),
	@centerBottomBackImageVar varchar(100),
	@centerBottomFrontImageVar varchar(100),
	@rightTopFrontImageVar varchar(100),
	@rightMiddleFrontImageVar varchar(100),
	@rightMiddleBackImageVar varchar(100),
	@rightVert varchar(100),
	@rightMiddleFrontWidth varchar(100),
	@rightMiddleFrontHeight varchar(100),
	@rightMiddleFrontLink varchar(100),
	@rightBottomFrontImageVar varchar(100),
	@fontPosition varchar(100),
	@color varchar(100),
	@size varchar(100),
	@space varchar(100),
	@font varchar(100),
	@bold varchar(100),
	@italic varchar(100),
	@topColor varchar(100),
	@middleColor varchar(100),
	@bottomColor varchar(100),
	@border varchar(100),
	@cellPad varchar(100),
	@cellSpace varchar(100),
	@brdrColor varchar(100),
	@centerTopBackHeight  varchar(100),
	@middleTableBorderColor varchar(100),
	@middleTableBorderShow varchar(100),
	@releaseDateVar int,
	@releaseDepartmentVar int,
	@click int,
	@newImageVar varchar(100),
	@topImageVar varchar(100),
	@showTopMoreInfo int,
	@topMoreInfoImageVar varchar(100),
	@showBottomMoreInfo int,
	@bottomMoreInfoImageVar varchar(100),
	@hotImageVar varchar(100),	
	@zdyName varchar(4000),
    @artCode varchar(100),
	@artStyle varchar(4000),
	@artState int,
	@isSystem int
)
AS
	SET NOCOUNT OFF;
INSERT INTO artDesignTemplate(middleTableBorderColor,middleTableBorderShow,centerTopBackHeight,centerBottomFrontImageVar,name, leftTopFrontImageVar, leftMiddleFrontImageVar, leftMiddleBackImageVar, leftVert, leftMiddleFrontWidth, leftMiddleFrontHeight, leftMiddleFrontLink, leftBottomFrontImageVar, centerTopFrontImageVar, centerTopBackImageVar, centerTopFrontWidth, centerTopFrontHeight, centerTopFrontLink, centerMiddleBackImageVar, itemImageVar, centerBottomBackImageVar, rightTopFrontImageVar, rightMiddleFrontImageVar, rightMiddleBackImageVar, rightVert, rightMiddleFrontWidth, rightMiddleFrontHeight, rightMiddleFrontLink, rightBottomFrontImageVar, fontPosition, color, size, [space], font, bold, italic, topColor, middleColor, bottomColor, border, cellPad, cellSpace, brdrColor,releaseDateVar,releaseDepartmentVar,click,newImageVar,topImageVar,showTopMoreInfo ,topMoreInfoImageVar,	showBottomMoreInfo,bottomMoreInfoImageVar,hotImageVar,zdyName,artCode,artStyle,artState,isSystem) VALUES (@middleTableBorderColor,@middleTableBorderShow,@centerTopBackHeight,@centerBottomFrontImageVar,@name, @leftTopFrontImageVar, @leftMiddleFrontImageVar, @leftMiddleBackImageVar, @leftVert, @leftMiddleFrontWidth, @leftMiddleFrontHeight, @leftMiddleFrontLink, @leftBottomFrontImageVar, @centerTopFrontImageVar, @centerTopBackImageVar, @centerTopFrontWidth, @centerTopFrontHeight, @centerTopFrontLink, @centerMiddleBackImageVar, @itemImageVar, @centerBottomBackImageVar, @rightTopFrontImageVar, @rightMiddleFrontImageVar, @rightMiddleBackImageVar, @rightVert, @rightMiddleFrontWidth, @rightMiddleFrontHeight, @rightMiddleFrontLink, @rightBottomFrontImageVar, @fontPosition, @color, @size, @space, @font, @bold, @italic, @topColor, @middleColor, @bottomColor, @border, @cellPad, @cellSpace, @brdrColor,@releaseDateVar,@releaseDepartmentVar,@click,@newImageVar,@topImageVar,
@showTopMoreInfo ,@topMoreInfoImageVar,	@showBottomMoreInfo,@bottomMoreInfoImageVar,@hotImageVar,@zdyName,@artCode,@artStyle,@artState,@isSystem)

GO

if exists (select * from dbo.sysobjects where id = object_id(N'UpdateArtDesignTemplate') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure UpdateArtDesignTemplate
GO
CREATE PROCEDURE UpdateArtDesignTemplate
(
	@name varchar(2000),
	@leftTopFrontImageVar varchar(100),
	@leftMiddleFrontImageVar varchar(100),
	@leftMiddleBackImageVar varchar(100),
	@leftVert varchar(100),
	@leftMiddleFrontWidth varchar(100),
	@leftMiddleFrontHeight varchar(100),
	@leftMiddleFrontLink varchar(100),
	@leftBottomFrontImageVar varchar(100),
	@centerTopFrontImageVar varchar(100),
	@centerTopBackImageVar varchar(100),
	@centerTopFrontWidth varchar(100),
	@centerTopFrontHeight varchar(100),
	@centerTopFrontLink varchar(100),
	@centerMiddleBackImageVar varchar(100),
	@itemImageVar varchar(100),
	@centerBottomBackImageVar varchar(100),
	@centerBottomFrontImageVar varchar(100),
	@rightTopFrontImageVar varchar(100),
	@rightMiddleFrontImageVar varchar(100),
	@rightMiddleBackImageVar varchar(100),
	@rightVert varchar(100),
	@rightMiddleFrontWidth varchar(100),
	@rightMiddleFrontHeight varchar(100),
	@rightMiddleFrontLink varchar(100),
	@rightBottomFrontImageVar varchar(100),
	@fontPosition varchar(100),
	@color varchar(100),
	@size varchar(100),
	@space varchar(100),
	@font varchar(100),
	@bold varchar(100),
	@italic varchar(100),
	@topColor varchar(100),
	@middleColor varchar(100),
	@bottomColor varchar(100),
	@border varchar(100),
	@cellPad varchar(100),
	@cellSpace varchar(100),
	@brdrColor varchar(100),
	@centerTopBackHeight   varchar(100),
	@pkId int,
	@middleTableBorderColor varchar(100),
	@middleTableBorderShow varchar(100),
    @releaseDateVar int,@releaseDepartmentVar int,
	@click int,
	@newImageVar varchar(100),
	@topImageVar varchar(100),
	@showTopMoreInfo int,
	@topMoreInfoImageVar varchar(100),
	@showBottomMoreInfo int,
	@bottomMoreInfoImageVar varchar(100),
	@hotImageVar varchar(100),
	@zdyName varchar(100),
	@artCode varchar(100),
	@artStyle varchar(4000),
	@artState int,
	@isSystem int
)
AS
	SET NOCOUNT OFF;
UPDATE artDesignTemplate SET hotImageVar=@hotImageVar, middleTableBorderColor=@middleTableBorderColor,middleTableBorderShow=@middleTableBorderShow,centerTopBackHeight=@centerTopBackHeight,centerBottomFrontImageVar=@centerBottomFrontImageVar,name = @name, leftTopFrontImageVar = @leftTopFrontImageVar, leftMiddleFrontImageVar = @leftMiddleFrontImageVar, leftMiddleBackImageVar = @leftMiddleBackImageVar, leftVert = @leftVert, leftMiddleFrontWidth = @leftMiddleFrontWidth, leftMiddleFrontHeight = @leftMiddleFrontHeight, leftMiddleFrontLink = @leftMiddleFrontLink, leftBottomFrontImageVar = @leftBottomFrontImageVar, centerTopFrontImageVar = @centerTopFrontImageVar, centerTopBackImageVar = @centerTopBackImageVar, centerTopFrontWidth = @centerTopFrontWidth, centerTopFrontHeight = @centerTopFrontHeight, centerTopFrontLink = @centerTopFrontLink, centerMiddleBackImageVar = @centerMiddleBackImageVar, itemImageVar = @itemImageVar, centerBottomBackImageVar = @centerBottomBackImageVar, rightTopFrontImageVar = @rightTopFrontImageVar, rightMiddleFrontImageVar = @rightMiddleFrontImageVar, rightMiddleBackImageVar = @rightMiddleBackImageVar, rightVert = @rightVert, rightMiddleFrontWidth = @rightMiddleFrontWidth, rightMiddleFrontHeight = @rightMiddleFrontHeight, rightMiddleFrontLink = @rightMiddleFrontLink, rightBottomFrontImageVar = @rightBottomFrontImageVar, fontPosition = @fontPosition, color = @color, size = @size, space = @space, font = @font, bold = @bold, italic = @italic, topColor = @topColor, middleColor = @middleColor, bottomColor = @bottomColor, border = @border, cellPad = @cellPad, cellSpace = @cellSpace, brdrColor = @brdrColor ,releaseDateVar=@releaseDateVar ,releaseDepartmentVar=@releaseDepartmentVar,click=@click,newImageVar=@newImageVar,topImageVar=@topImageVar ,showTopMoreInfo=@showTopMoreInfo ,topMoreInfoImageVar=@topMoreInfoImageVar,	showBottomMoreInfo=@showBottomMoreInfo,bottomMoreInfoImageVar=@bottomMoreInfoImageVar,zdyName=@zdyName,artCode=@artCode,artStyle=@artStyle,artState=@artState,isSystem=@isSystem WHERE (pkId = @pkId)


GO

if exists (select * from dbo.sysobjects where id = object_id(N'GetLibrariesTemplate') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetLibrariesTemplate
GO
CREATE PROCEDURE GetLibrariesTemplate
AS
	SET NOCOUNT ON;
SELECT * FROM librariesTemplate
order by isSystem desc,pkId

GO

if exists (select * from dbo.sysobjects where id = object_id(N'GetLibrariesTemplateByAdminId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetLibrariesTemplateByAdminId
GO
CREATE PROCEDURE GetLibrariesTemplateByAdminId
@adminId int
AS
	SET NOCOUNT ON;
select pkId,name,isSystem,adminId from librariesTemplate
where isSystem=1
union
SELECT pkId,name,isSystem,adminId FROM librariesTemplate
where isSystem=0
and @adminId=adminId
order by isSystem desc,pkId
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
@gradeId int=1 

AS
	/* SET NOCOUNT ON */
	SELECT m.*,
	       g.name grade,
	       o.name mode
	FROM menu m,grade g,mode o
	where m.gradeId<=@gradeId
	  and 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 

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

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

COMMIT TRANSACTION


GO



if exists (select * from dbo.sysobjects where id = object_id(N'GetLayoutTemplate') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetLayoutTemplate
GO
CREATE PROCEDURE GetLayoutTemplate
AS
	SET NOCOUNT ON;
SELECT pkid,name,itemnum,itemName,isartdesign,layouttemplatetype,issystem,adminid,bgpic FROM layoutTemplate;

--优化存储过程提升查询性能，不采用select * 方式查询，没有用到的大数据字段不用查询出来。

GO

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

SELECT pkid,name,isartdesign,layouttemplatetype,issystem,adminid FROM layoutTemplate where layoutTemplateType=@layoutTemplateType

--V6.1 优化存储过程提升查询性能，弃用原来的select * 方式查询，本表有text型字段
GO


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

	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.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, 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) + '
	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) = ''
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

	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.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, 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) + '
	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'GetNewsByBigClassIdAndPassed') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetNewsByBigClassIdAndPassed
GO
CREATE PROCEDURE GetNewsByBigClassIdAndPassed
@gradeid int=1,
@gateId int,
@bigClassId int=null,
@currentPage int=1,
@pageSize int=5,
@passed int=1
AS
BEGIN


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

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

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

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

	DELETE #PageIndexs
	RETURN 
END

GO

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

	SELECT TOP 1 pkId,intactTitle FROM news WHERE gradeId <= @gradeId AND gate <> @gateId AND pkId < @pkId  AND passed=1
	AND bigClassId = (SELECT bigClassId FROM news where pkId = @pkId)ORDER BY pkId DESC

GO

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


GO

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


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


select * from news where pkId=@pkId and gradeId<=@gradeId and gate<>@gateId
-- 改为通过程序去判断and passed = 1
	
 COMMIT TRANSACTION
 RETURN 

GO


if exists (select * from dbo.sysobjects where id = object_id(N'GetImageNewsByRecommand') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetImageNewsByRecommand
GO
CREATE PROCEDURE GetImageNewsByRecommand
@pageSize int=5,
@gradeid int=1,
@gateId int
AS
	 SET NOCOUNT ON 
	
select top 1 pkId
	,specialId
	,gradeId
	,gate
	,departmentId
	,listTitle
	,intactTitle
	,subheading
	,isRecommand
	,isTop
	,useListTitle
	,showReviewLink
	,Author
	,AuthorMail
	,CopyFrom
	,copyFromLink
	,about
	,pageType
	,autoPageCharNum
	,readPoint
	,itemType
	,descript
	,usersId
	,editor
	,click
	,updateTime
	,passed
	,isReview
	,isImageNews
	,imageName
	,width
	,height
	,MenuId
	,bigClassId
	,updateIp
	,useSubHeading
	,isDuplicated
	,duplicateFrom
	,duplicatedPkId
	,validDays
	,mediaAutostart
	,AuditUserID from news 
where validDays <> 0 and passed=1 
and isRecommand=1  
and IsImageNews=1 
and gate<>@gateId
and gradeId<=@gradeId
order by istop desc,pkId desc

RETURN

GO


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

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

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

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

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

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

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

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

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

GO

if exists (select * from dbo.sysobjects where id = object_id(N'GetTopOneNewsByBigClassId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetTopOneNewsByBigClassId
GO
CREATE PROCEDURE GetTopOneNewsByBigClassId
	@bigClassId int 
AS 
	select width,height from news where pkId=(select MAX(pkId) from news where bigClassId=@bigClassId and isDuplicated=0)
	
GO

if exists (select * from dbo.sysobjects where id = object_id(N'GetMenuByParentId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetMenuByParentId
GO
CREATE PROCEDURE GetMenuByParentId
@parentId int
AS
	/* SET NOCOUNT ON */
	SELECT m.*, g.name grade, o.name mode FROM menu m,grade g,mode o where m.gradeId=g.pkId and m.modeId=o.pkId and m.parentId=@parentId and m.bigClassId<>-1 order by ordering DESC ,m.pkId 
	RETURN 

GO

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

AS
	/* SET NOCOUNT ON */
	SELECT m.*,g.name grade, o.name mode FROM menu m,grade g,mode o,adminAssign a where a.menuId=m.pkId
	  and m.gradeId=g.pkId
	  and m.modeId=o.pkId
	  and a.adminId=@adminId
	  and m.parentId=@parentId
	  and m.bigClassId<>-1 order by ordering desc ,m.pkId
	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
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)  
		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.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)  
			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)  
		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

--bugfix：文字链接组点击下一页出错 更新排序Bug

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'UpdateSitePara') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure UpdateSitePara
GO
CREATE PROCEDURE UpdateSitePara
@ptype varchar(20),
@pcode varchar(20),
@pvalue varchar(100),
@pseq int,
@pnote varchar(100)

AS
    --先获取记录总数
	DECLARE @tempNum int
	SET @tempNum = (select count(*) from sitePara where pcode=@pcode)
	if(@tempNum=0)
	begin
		insert into sitePara(ptype,pcode,pvalue,pseq,pnote)values(@ptype,@pcode,@pvalue,@pseq,@pnote)
	end
	else
	begin
		update sitePara set ptype = @ptype,pvalue=@pvalue,pseq=@pseq,pnote=@pnote WHERE pcode=@pcode 
	end
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
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+'%' )

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

	end
	else
	begin
	

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

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

	end 

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

    -- Insert into our temp table
    INSERT	INTO #PageIndexs (ID)
	SELECT	n.pkId
	FROM	[News] AS n 
	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+'%')

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

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

DELETE #PageIndexs
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
	
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+'%' ) 
	ORDER BY n.istop desc,n.Priority desc,n.pkid desc

	end
	else
	begin
	

	-- Insert into our temp table
    INSERT	INTO #PageIndexs (ID)
    SELECT	n.pkId
	FROM	[News] AS n,[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+'%' )

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

	end 

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

    -- Insert into our temp table
    INSERT	INTO #PageIndexs (ID)
	SELECT	n.pkId
	FROM	[News] AS n 
	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+'%')

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

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

	ORDER BY n.istop desc,n.Priority 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.pkid desc

DELETE #PageIndexs
RETURN

GO



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

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

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

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

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

	end
	else
	begin

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

	end


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

	DELETE #PageIndexs
	RETURN 
END

GO




if exists (select * from dbo.sysobjects where id = object_id(N'DeleteDepartment') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure DeleteDepartment
GO
CREATE PROCEDURE DeleteDepartment
(
	@pkId int,
	@rtn varchar(100)output
)
AS
--news
DECLARE @count int
SELECT @count=Count(pkId) FROM news WHERE departmentId=@pkId
if( @count >0)
begin
set @rtn='news'
return
end
--users
SELECT @count=Count(pkId) FROM users WHERE departmentId=@pkId
if( @count >0)
begin
set @rtn='users'
return
end

DELETE FROM department WHERE (pkId = @pkId)
set @rtn='success'

GO



if exists (select * from dbo.sysobjects where id = object_id(N'DeleteGrade') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure DeleteGrade
GO
CREATE PROCEDURE DeleteGrade
(
	@pkId int,
    @rtn varchar(100) output 
)
AS
--news
DECLARE @count int
SELECT @count=Count(pkId) FROM news WHERE gradeId=@pkId
if( @count >0)
begin
set @rtn='news'
return
end

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

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

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

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

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

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

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

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

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

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

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

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

DELETE FROM grade WHERE (pkId = @pkId) and pkId!=1
set @rtn='success'

GO



alter 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)
)
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
	) VALUES (
	@title, 
	@checked, 
	@author,
	@tel, 
	@isSecrecy, 
	@email, 
	@sex, 
	@homePage, 
	@Content, 
	@reContent, 
	@userId, 
	@updateTime, 
	@updateIp, 
	@click, 
	@isTop, 
	@MenuId, 
	@gradeId, 
	@bigClassId, 
	@gate, 
	@username, 
	@password, 
	@retime,
	@redept
	)
  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)
)
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

WHERE (pkId = @pkId)
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,pkId desc) '+@searchStr+' order by istop desc,m.pkId desc'
Execute (@vSQL)	
	RETURN
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'DeleteBigClass') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure DeleteBigClass
GO
CREATE PROCEDURE DeleteBigClass
(
	@pkId int
)
AS
	SET NOCOUNT OFF;
BEGIN TRANSACTION

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

delete from siteContributeClass where bigClassId = @pkId

delete from moduleAssign where menuId in (select m.pkId from menu m where @pkId=bigClassId)

delete from menu where @pkId=bigClassId

delete from bigclass where @pkId=pkId

delete from news where @pkId=bigclassId

COMMIT TRANSACTION   


GO


if exists (select * from dbo.sysobjects where id = object_id(N'GetNewsReleaseTopByDepartment') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetNewsReleaseTopByDepartment
GO
CREATE PROCEDURE GetNewsReleaseTopByDepartment
	@pageIndex int, --当前页
	@pageSize int=5, --分页数
	@bigClassPkId int, --栏目ID
	@selectType int, --查询类型
	@aparamTime datetime --当前时间
AS

BEGIN

	DECLARE @num int
	select @num=0
	select @num = count(pkId) from BigClassAssociation where thisBigClassId=@bigClassPkId
	if(@num = 0) --未选择的情况下查询所有栏目
	begin
	
			if(@selectType=21) --累计所有
			begin
				select TOP (@pageSize) N.departmentId as UserID, D.name as UserName,count(N.pkId) as NewsCount 
				from news N left join department D on N.departmentId=D.pkId 
				where passed=1 and --统计审核成功的信息
				(n.validDays > 0 OR n.validDays < 0) and 
				isDuplicated != 1 --不统计抄送信息
				group by N.departmentId, D.name 
				order by NewsCount desc
				
			end	
			else --按照时间条件查询
			begin
				
				select TOP (@pageSize) N.departmentId as UserID, D.name as UserName,count(N.pkId) as NewsCount 
				from news N left join department D on N.departmentId=D.pkId 
				where passed=1 and --统计审核成功的信息
				(n.validDays > 0 OR n.validDays < 0) and 
				isDuplicated != 1 and --不统计抄送信息
				updateTime >= @aparamTime  
				group by N.departmentId, D.name 
				order by NewsCount desc
			end
		
	end
	else
	begin
		
			if(@selectType=21) --累计所有
			begin
				select TOP (@pageSize) N.departmentId as UserID, D.name as UserName,count(N.pkId) as NewsCount 
				from news N left join department D on N.departmentId=D.pkId 
				where passed=1 and 
				(n.validDays > 0 OR n.validDays < 0) and 
				n.bigClassId in (select bigClassId from BigClassAssociation where thisBigClassId=@bigClassPkId) and 
				isDuplicated != 1 
				group by N.departmentId, D.name 
				order by NewsCount desc
				
			end
			else --按照时间条件查询
			begin
				
				select TOP (@pageSize) N.departmentId as UserID, D.name as UserName,count(N.pkId) as NewsCount 
				from news N left join department D on N.departmentId=D.pkId 
				where passed=1 and 
				(n.validDays > 0 OR n.validDays < 0) and 
				n.bigClassId in (select bigClassId from BigClassAssociation where thisBigClassId=@bigClassPkId) and 
				isDuplicated != 1 and
				updateTime >= @aparamTime 
				group by N.departmentId, D.name 
				order by NewsCount desc
			end	
	end

	RETURN 
END

GO


if exists (select * from dbo.sysobjects where id = object_id(N'GetNewsReleaseTopByUser') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetNewsReleaseTopByUser
GO
CREATE PROCEDURE GetNewsReleaseTopByUser
	@pageIndex int, --当前页
	@pageSize int=5, --分页数
	@bigClassPkId int, --栏目ID
	@selectType int, --查询类型
	@aparamTime datetime --时间
AS

BEGIN

	DECLARE @num int
	select @num=0
	select @num = count(pkId) from BigClassAssociation where thisBigClassId=@bigClassPkId
	if(@num = 0) --未选择的情况下查询所有栏目
	begin
	
			if(@selectType=11) --累计所有
			begin
				select TOP (@pageSize) N.usersid as UserID, U.fullName as UserName,count(N.pkId) as NewsCount 
				from news N left join users U on N.usersid=U.pkId 
				where passed=1 and --统计审核成功的信息
				(n.validDays > 0 OR n.validDays < 0) and 
				isDuplicated != 1 --不统计抄送信息
				group by N.usersid, U.fullName 
				order by NewsCount desc
				
			end	
			else --按照时间条件查询
			begin
					
				select TOP (@pageSize) N.usersid as UserID, U.fullName as UserName,count(N.pkId) as NewsCount 
				from news N left join users U on N.usersid=U.pkId 
				where passed=1 and --统计审核成功的信息
				(n.validDays > 0 OR n.validDays < 0) and 
				isDuplicated != 1 and --不统计抄送信息
				updateTime >= @aparamTime 
				group by N.usersid, U.fullName 
				order by NewsCount desc
			end
			
	end
	else
	begin	
			if(@selectType=11) --累计所有
			begin
				select TOP (@pageSize) N.usersid as UserID, U.fullName as UserName,count(N.pkId) as NewsCount 
				from news N left join users U on N.usersid=U.pkId 
				where passed=1 and 
				(n.validDays > 0 OR n.validDays < 0) and 
				n.bigClassId in (select bigClassId from BigClassAssociation where thisBigClassId=@bigClassPkId) and 
				isDuplicated != 1 
				group by N.usersid, U.fullName 
				order by NewsCount desc
				
			end	
			else --按照时间查询
			begin
				
				select TOP (@pageSize) N.usersid as UserID, U.fullName as UserName,count(N.pkId) as NewsCount 
				from news N left join users U on N.usersid=U.pkId 
				where passed=1 and 
				(n.validDays > 0 OR n.validDays < 0) and 
				n.bigClassId in (select bigClassId from BigClassAssociation where thisBigClassId=@bigClassPkId) and 
				isDuplicated != 1 and
				updateTime >= @aparamTime 
				group by N.usersid, U.fullName 
				order by NewsCount desc
			end
	end

	RETURN 
END


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 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 m.bigClassId=b.pkId
		and b.templateId=t.pkId		
	RETURN

GO




if exists (select * from dbo.sysobjects where id = object_id(N'GetMessageBoardSummaryByBigClassIdAndpassed') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetMessageBoardSummaryByBigClassIdAndpassed
GO
CREATE PROCEDURE GetMessageBoardSummaryByBigClassIdAndpassed
	@currentPage int=1,
	@pageSize int=7,
	@bigClassId int=null,
	@gradeid int=1,
	@gateId int,
	@passed int=1,
	@recordCount int output	
AS	
	--转换成索引
	SELECT @currentPage = @currentPage-1	

	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT @count = Count(pkId) from messageBoard where gate<>'+Convert(varchar,@gateId)+' and bigClassId in (select bigClassId from BigClassAssociation where thisBigClassId='+Convert(varchar,@bigClassId)+') and gradeId<='+convert(varchar,@gradeId)+' and checked='+convert(varchar,@passed)+' and isSecrecy =0'
	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) + ' messageBoard.*,bigClass.name as bigClassName FROM messageBoard,bigclass WHERE bigclass.pkId = messageBoard.bigClassId and messageBoard.checked=1 and messageBoard.isSecrecy =0 and messageBoard.bigClassId in (select bigClassId from BigClassAssociation where thisBigClassId='+Convert(varchar,@bigClassId)+') and messageBoard.gate<>'+Convert(varchar,@gateId)+' and messageBoard.gradeId<='+convert(varchar,@gradeId)+' order by messageBoard.updatetime desc'	
	--查询最后一页
	ELSE IF @currentPage = @pageCount -1			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' messageBoard.*,bigClass.name as bigClassName FROM messageBoard,bigclass where bigclass.pkId = messageBoard.bigClassId and messageBoard.checked=1 and messageBoard.isSecrecy =0 and messageBoard.bigClassId in (select bigClassId from BigClassAssociation where thisBigClassId='+Convert(varchar,@bigClassId)+') and messageBoard.gate<>'+Convert(varchar,@gateId)+' and messageBoard.gradeId<='+convert(varchar,@gradeId)+' order by messageBoard.updatetime desc ) TempTable ORDER BY messageBoard.isTop DESC '	
	--查询中间页后面的页
	ELSE IF @currentPage > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @currentPage ) + ' messageBoard.*,bigClass.name as bigClassName FROM messageBoard,bigclass where bigclass.pkId = messageBoard.bigClassId and messageBoard.checked=1 and messageBoard.isSecrecy =0 and messageBoard.bigClassId in (select bigClassId from BigClassAssociation where thisBigClassId='+Convert(varchar,@bigClassId)+') and messageBoard.gate<>'+Convert(varchar,@gateId)+' and messageBoard.gradeId<='+convert(varchar,@gradeId)+' ORDER BY messageBoard.updatetime desc) TempTable ORDER BY messageBoard.isTop DESC'
	--查询中间页前面的页
 	ELSE BEGIN							
		SET @currentPage=@currentPage+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @currentPage ) + ' messageBoard.*,bigClass.name as bigClassName FROM messageBoard,bigclass where bigclass.pkId = messageBoard.bigClassId and messageBoard.checked=1 and messageBoard.isSecrecy =0 and messageBoard.bigClassId in (select bigClassId from BigClassAssociation where thisBigClassId='+Convert(varchar,@bigClassId)+') and messageBoard.gate<>'+Convert(varchar,@gateId)+' and messageBoard.gradeId<='+convert(varchar,@gradeId)+' ORDER BY messageBoard.updatetime DESC) TempTable1 '
					+ ' ORDER BY messageBoard.isTop ASC ) TempTable2 ORDER BY messageBoard.isTop DESC, messageBoard.pkId DESC'
	END

	--执行查询
	EXEC(@sqlStr)

RETURN


GO

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

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

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

 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

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'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