


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

CREATE PROCEDURE [dbo].[GetUsersByPkId]
@pkId int
AS
	SET NOCOUNT ON;
SELECT u.* ,d.name 'department' FROM Users u,department d where @pkId=u.pkId and d.pkId=u.departmentId


GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

CREATE PROCEDURE [dbo].[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)
--用临时表先读取授权的栏目和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 @str = @str + ' and ' + @manageAll
print @str
--SET @str = @str + ' and m.pkId in (1795,1796,1803)'
	--设置查询表名称
	DECLARE @tableName nvarchar(100)

	SET @tableName = '#test z, link n,users u,grade g, template t, bigClass b '
print @tableName
	--设置查询排序（不带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


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


CREATE PROCEDURE [dbo].[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)
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 '
	
print @str
--SET @str = @str + ' and m.pkId in (1795,1796,1803)'
	--设置查询表名称
	DECLARE @tableName nvarchar(100)

	SET @tableName = '#test z, news n,users u,department d,grade g, template t, bigClass b '
print @tableName
	--设置查询排序（不带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


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


CREATE PROCEDURE [dbo].[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)
--用临时表先读取授权的栏目和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 @str = @str + ' and ' + @manageAll
print @str
--SET @str = @str + ' and m.pkId in (1795,1796,1803)'
	--设置查询表名称
	DECLARE @tableName nvarchar(100)

	SET @tableName = '#test z, people n,users u,grade g, template t, bigClass b '
print @tableName
	--设置查询排序（不带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



SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


CREATE PROCEDURE [dbo].[GetNewsByPage]
	@searchStr varchar(500),
	@pageIndex int,
	@pageSize int,
	@pageCount int output,
	@recordCount int output
AS
	SELECT @pageIndex = @pageIndex - 1
	
	DECLARE @tempNum int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT @count = Count(pkId) FROM news WHERE ' + @searchStr
	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 (@pageIndex = 0) OR (@pageIndex > @pageCount-1)	
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM news WHERE ' + @searchStr + ' ORDER BY pkId DESC'	
	ELSE IF @pageIndex = @pageCount -1			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' * FROM news WHERE ' + @searchStr + ' ORDER BY pkId ASC) TempTable ORDER BY pkId DESC'	
	ELSE IF @pageIndex > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @pageIndex ) + ' * FROM news WHERE ' + @searchStr + ' ORDER BY pkId ASC) TempTable ORDER BY pkId DESC'
	ELSE BEGIN							
		SET @pageIndex=@pageIndex+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @pageIndex ) + ' * FROM news WHERE ' + @searchStr + ' ORDER BY pkId DESC) TempTable1 '
					+ ' ORDER BY pkId ASC) TempTable2 ORDER BY pkId DESC'
	END
	EXEC(@sqlStr)
	RETURN

GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

CREATE PROCEDURE [dbo].[UpdateRegServiceList]
(
	@pkId int,
	@serviceId int,
	@regServiceCode varchar(250),
	@isAllowService int,
	@regTime datetime,
	@auditTime datetime	
)
AS
	SET NOCOUNT OFF;
Update RegServiceList set
	serviceId = @serviceId, 
	regServiceCode = @regServiceCode, 
	isAllowService = @isAllowService,
	regTime = @regTime,
	auditTime = @regTime where pkid=@pkid

GO



SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

CREATE PROCEDURE [dbo].[GetMenuByPkId]
@gradeId int=1,
@pkId 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.pkId=@pkId

	RETURN 

GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

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

GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


CREATE PROCEDURE [dbo].[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)
)
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) 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)


GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


CREATE PROCEDURE [dbo].[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)
	
)
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 WHERE (pkId = @pkId)

GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


CREATE PROCEDURE [dbo].[InsertMenu]
(
	@pkId int,
	@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
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


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


CREATE PROCEDURE [dbo].[UpdateMenu]
(
	@parentId int,
	@name varchar(100),
	@remark varchar(100),
	@ordering int,
	@address varchar(100),
	@bigClassId int,
	@modeId int,
	@gradeId int,
	@pkId int,
	@gate int,
	@recorderNum int,
	@menuPic varchar(100),
	@itemId  varchar(100),
	@layoutTemplateId int,
	@adminId int,
	@displayRoot int,
	@userIntercept int,
	@interceptNums int,
    @columnNum int,
    @artDesignTemplateId int,
	@contributeSortType int,
	@navigate int,
	@newsTemplateId int
)
AS
	SET NOCOUNT OFF;
BEGIN TRANSACTION
if(@modeId='1')
begin
select @address='index.aspx?pkId='+convert(varchar,@pkId)
end
UPDATE menu SET artDesignTemplateId=@artDesignTemplateId,columnNum=@columnNum,interceptNums=@interceptNums,userIntercept=@userIntercept, displayRoot=@displayRoot,parentId = @parentId, [name] = @name, remark = @remark, ordering = @ordering, [address] = @address, bigClassId = @bigClassId, modeId = @modeId, gradeId = @gradeId,gate=@gate ,recorderNum=@recorderNum,menuPic=@menuPic,itemId=@itemId,layoutTemplateId=@layoutTemplateId ,adminId=@adminId, contributeSortType=@contributeSortType,navigate=@navigate,newsTemplateId=@newsTemplateId WHERE (pkId = @pkId) 
if(@modeId='4')
begin
update bigClass set [name]=@name where pkId=@bigClassId
end
COMMIT TRANSACTION

GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


CREATE PROCEDURE [dbo].[GetShowItem]
@gradeId int=1,
@parentId int,
@gateId int
AS

select m.*,t.code,b.name as bigclassname,b.cssname
	  from menu m,bigClass b,template t
	  where m.parentId=@parentId
	    and m.gate<>@gateId
	    and m.gradeId<=@gradeId
	    and m.bigClassId=b.pkId
	    and b.templateId=t.pkId
	order by m.pkId desc,ordering desc

RETURN

GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

CREATE PROCEDURE [dbo].[GetLayoutTemplateByType]
@layoutTemplateType int
AS
	SET NOCOUNT ON;

SELECT * FROM layoutTemplate where layoutTemplateType=@layoutTemplateType

GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

CREATE PROCEDURE [dbo].[UpdateNewsNewTemplateByBigClassId] 
	@bigClassId int,
	@newTemplateId int
AS
	SET NOCOUNT ON;

update news set itemType=@newTemplateId where bigClassId=@bigClassId

GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

CREATE PROCEDURE [dbo].[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 menu WHERE parentId=@parentId and bigClassId=@bigClassId and modeId='4'
COMMIT TRANSACTION

GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

CREATE PROCEDURE [dbo].[CheckMenuSetName]
@menuName varchar(100),
@menuSetId int
AS
	SET NOCOUNT ON
if (@menuSetId=0)
begin
	select pkId from menuset where [name]=@menuName
end
else
begin
	select pkId from menuset where [name]=@menuName and pkid<>@menuSetId
end

GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


CREATE PROCEDURE [dbo].[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'
				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)+' 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'
				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



SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

CREATE PROCEDURE [dbo].[InsertBigClassAssociation]
@pkId int=null,
@menuId	int,
@bigClassId int,
@thisBigClassId int	
AS
	SET NOCOUNT ON 
	insert BigClassAssociation(menuId, bigClassId,thisBigClassId) values(@menuId, @bigClassId,@thisBigClassId)
RETURN

GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


CREATE PROCEDURE [dbo].[GetNewsCalendarSearch]
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeId int,
	@searchTimeBegin DateTime,
	@searchTimeEnd DateTime,
	@gateId int,
	@bigClassId int,
	@NewSearchbigClassId int
AS	
    
	--转换成索引
	SELECT @currentPage = @currentPage - 1
	--设置查询表名称
	DECLARE @tableName nvarchar(50)
	--初始化查询条件(不带where)
	DECLARE @str nvarchar(1000)
    DECLARE @sCount int,@sNum int
    DECLARE @sSql nvarchar(200)
    DECLARE @searchStrBegin nvarchar(100)
    DECLARE @searchStrEnd nvarchar(100)
    SET @sSQL = N'SELECT @count=Count(searchOptions.pkId) from searchOptions where bigClassId = ' + STR(@NewSearchbigClassId)
    EXEC sp_ExecuteSQL @sSQL,N'@count int output',@sNum output
    SELECT @sCount = @sNum
    
    SET @str = ' validDays <> 0 '
    SET @searchStrBegin = Convert(varchar,@searchTimeBegin)
	SET @searchStrEnd = Convert(varchar,@searchTimeEnd)
	IF (@bigClassId <> 0)
    BEGIN    
        SET @tableName = 'news,bigClass'
		SET @str = @str + ' and news.bigClassId='+STR(@bigClassId)
    END
    ELSE IF(@sCount > 0)
    BEGIN
        SET @tableName = 'news,searchOptions,bigClass'
        SET @str = @str + ' and patindex(''%|''+' + 'Convert(Varchar(50),news.bigClassId)'+ '+''|%'',searchOptions.OptionsValue)>0 and searchOptions.bigClassId = ' +STR(@NewSearchbigClassId)
	END
	ELSE IF(@sCount = 0)
    BEGIN
        SET @tableName = 'news,bigClass'
    END
	SET @str = @str + ' and news.updateTime >''' + @searchStrBegin + ''' and news.updateTime <''' + @searchStrEnd + '''' + ' and news.bigClassId = bigClass.pkId '
	SET @str = @str + ' and news.passed=1 and (news.isDuplicated<>1 or news.isDuplicated is null) and news.gradeId<='+STR(@gradeId)
	--SET @str = @str + ' and gate<>'+STR(@gateId)

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

	--计算页数
	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) + ' news.*,bigClass.name FROM '+Convert(nvarchar(50),@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) + ' news.*,bigClass.name  FROM '+Convert(nvarchar(50),@tableName)+' where '+Convert(nvarchar(1000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable ORDER BY '+Convert(nvarchar(100),@orderTypeD_out)	
	--查询中间页后面的页
	ELSE IF @currentPage > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @currentPage ) + ' news.*,bigClass.name FROM '+Convert(nvarchar(50),@tableName)+' where '+Convert(nvarchar(1000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable ORDER BY '+Convert(nvarchar(100),@orderTypeD_out)
	--查询中间页前面的页
 	ELSE BEGIN							
		SET @currentPage=@currentPage+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @currentPage ) + ' news.*,bigClass.name FROM '+Convert(nvarchar(50),@tableName)+' where '+Convert(nvarchar(1000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeD)+') TempTable1 '
					+ ' ORDER BY '+Convert(nvarchar(100),@orderTypeA_out)+') TempTable2 ORDER BY '+Convert(nvarchar(100),@orderTypeD_out)
	END

	--执行查询
	EXEC(@sqlStr)

RETURN

GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

CREATE PROCEDURE [dbo].[GetMenuByParentIdAndGradeId]
@parentId int,
@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.parentId=@parentId
	  and m.bigClassId<>-1	  
	order by ordering  desc
	RETURN

GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

CREATE PROCEDURE [dbo].[GetMenuByPkIdAndGradeId]
@gradeId int=1,
@pkId int
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.pkId=@pkId

RETURN 

GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


CREATE PROCEDURE [dbo].[GetNewsByRecommand]
@pageSize int=5,
@gradeid int=1,
@gateId int
AS
SET NOCOUNT ON 

declare @vSQL varchar(1000)
select @vSQL ='select top ' + convert(varchar, @pageSize) + ' n.*,d.name as Department from news AS n INNER JOIN [Department] AS d ON n.departmentId = d.PKID where gate<>'+Convert(varchar,@gateId)+' and IsImageNews=0 and isDuplicated<>1 and gradeId<='+convert(varchar,@gradeId)+' and passed=1 and isRecommand=1 and validDays<>0 order by n.istop desc,n.pkId desc'
Execute (@vSQL)

RETURN

GO



SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


create PROCEDURE [dbo].[GetMenuByBigClassIdA]
@bigClassId int
AS
BEGIN
	-- Create a temp table TO store the select results
    CREATE TABLE #PageIndexs
    (
        IndexId int IDENTITY (0, 1) NOT NULL,
        ID int,
		NAVID int
    )

    -- Insert into our temp table
    INSERT	INTO #PageIndexs (ID,NAVID) 
	select parentid, navigate from menu where bigclassid = @bigClassId


	SELECT pkid,parentid,name,address,P.NAVID as navigate,layouttemplateid 
	FROM menu AS m INNER JOIN [#PageIndexs] AS p ON m.PKID = p.ID 
	
	DELETE #PageIndexs
	RETURN

END 

go


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


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

GO



SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


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

GO



SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

CREATE PROCEDURE [dbo].[UpdateLayoutTemplate]
(
	@name varchar(100),
	@template text,
	@simple text,
	@itemNum int,
	@itemName varchar(7000),
	@isArtDesign int,
	@pkId int,
	@layoutTemplateType int,
	@adminId int,
	@bgpic varchar(100)
)
AS
	SET NOCOUNT OFF;
UPDATE layoutTemplate SET name = @name, template = @template, simple = @simple, itemNum = @itemNum, itemName = @itemName, isArtDesign = @isArtDesign , layoutTemplateType=@layoutTemplateType,adminId=@adminId,bgpic=@bgpic WHERE pkId = @pkId


GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


CREATE PROCEDURE [dbo].[InsertLayoutTemplate]
(
	@name varchar(100),
	@template text,
	@simple text,
	@itemNum int,
	@itemName varchar(7000),
	@isArtDesign int,
	@pkId int output,
	@layoutTemplateType int,
	@adminId int,
	@bgpic varchar(100)
)
AS
	SET NOCOUNT OFF;
INSERT INTO layoutTemplate(name, template, simple, itemNum, itemName, isArtDesign,layoutTemplateType,adminId,bgpic) VALUES (@name, @template, @simple, @itemNum, @itemName, @isArtDesign,@layoutTemplateType,@adminId,@bgpic)
SET @pkId = @@IDENTITY

GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

CREATE PROCEDURE [dbo].[GetSelectCounts]
@bigclassid int=0,
@depid int=0,
@gradeid int=0,
@passed int=2,
@cb int =0,
@startime datetime,
@endtime datetime,
@userid int 

AS
	/* SET NOCOUNT ON */
if (@bigclassid= 0)
begin
	if(@depid=0)
	begin
		if(@gradeid=0)
		begin
			if(@passed=2)
			begin
				if(@cb = 0)
				begin 
					if(@userid = 0)
					begin
						select count(*) from news
					end
					else
					begin
						select count(*) from news where usersid = @userid
					end
				end
				else
				begin
					if(@userid = 0)
					begin
					select count(*) from news where updatetime between @startime and @endtime
					end
					else
					begin
						select count(*) from news where usersid = @userid and updatetime between @startime and @endtime
					end
				end
			end
			else
			begin
				if(@cb = 0)
				begin 
					if(@userid = 0)
					begin
						select count(*) from news where passed=@passed
					end
					else
					begin
						select count(*) from news where passed=@passed and usersid = @userid
					end
				end
				else
				begin
					if(@userid = 0)
					begin
					select count(*) from news where passed=@passed and updatetime between @startime and @endtime
					end
					else
					begin
					select count(*) from news where passed=@passed and usersid = @userid and updatetime between @startime and @endtime
					end 
				end
			end
		end
		else
		begin
			if(@passed=2)
			begin
				if(@cb = 0)
				begin 
					if(@userid = 0)
					begin
						select count(*) from news where gradeid=@gradeid
					end
					else
					begin
						select count(*) from news where gradeid=@gradeid and usersid = @userid
					end
				end
				else
				begin
					if(@userid = 0)
					begin
				select count(*) from news where gradeid=@gradeid and updatetime between @startime and @endtime
					end
					else
					begin
					select count(*) from news where gradeid=@gradeid and usersid = @userid and updatetime between @startime and @endtime
					end
				end
			end
			else
			begin
				if(@cb = 0)
				begin 
					if(@userid = 0)
					begin
				select count(*) from news where passed=@passed and gradeid=@gradeid
					end
					else
					begin
				select count(*) from news where passed=@passed and gradeid=@gradeid and usersid = @userid
					end
				end
				else
				begin
				if(@userid = 0)
					begin
				select count(*) from news where gradeid=@gradeid and passed=@passed and updatetime between @startime and @endtime 
					end 
					else
					begin
					select count(*) from news where gradeid=@gradeid and passed=@passed and usersid = @userid and updatetime between @startime and @endtime
					end
				end
			end
		end
	end
	else
	begin

	if(@gradeid=0)
		begin
			if(@passed=2)
			begin
				if(@cb = 0)
				begin 
					if(@userid = 0)
					begin
				select count(*) from news where departmentid=@depid
					end
					else
					begin
				select count(*) from news where departmentid=@depid and usersid = @userid
					end
				end
				else
				begin
					if(@userid = 0)
					begin
				select count(*) from news where departmentid=@depid and updatetime between @startime and @endtime
					end
					else
					begin
				select count(*) from news where departmentid=@depid and usersid = @userid and updatetime between @startime and @endtime
					end
				end
			end
			else
			begin
				if(@cb = 0)
				begin 
					if(@userid = 0)
					begin
				select count(*) from news where departmentid=@depid and passed=@passed
					end
					else
					begin
				select count(*) from news where departmentid=@depid and passed=@passed and usersid = @userid
					end
				end
				else
				begin
					if(@userid = 0)
					begin
				select count(*) from news where departmentid=@depid and passed=@passed and updatetime between @startime and @endtime 
					end
					else
					begin
				select count(*) from news where departmentid=@depid and passed=@passed and usersid = @userid and updatetime between @startime and @endtime
					end
				end
			end
		end
		else
		begin
			if(@passed=2)
			begin
				if(@cb = 0)
				begin 
					if(@userid = 0)
					begin
				select count(*) from news where departmentid=@depid and gradeid=@gradeid
					end
					else
					begin
				select count(*) from news where departmentid=@depid and gradeid=@gradeid and usersid = @userid
					end
				end
				else
				begin
					if(@userid = 0)
					begin
				select count(*) from news where departmentid=@depid and gradeid=@gradeid and updatetime between @startime and @endtime
					end
					else
					begin
			select count(*) from news where departmentid=@depid and gradeid=@gradeid and usersid = @userid and updatetime between @startime and @endtime
					end
				end
			end
			else
			begin
				if(@cb = 0)
				begin 
					if(@userid = 0)
					begin
				select count(*) from news where departmentid=@depid and passed=@passed and gradeid=@gradeid
					end
					else
					begin
				select count(*) from news where departmentid=@depid and passed=@passed and gradeid=@gradeid and usersid = @userid
					end
				end
				else
				begin
					if(@userid = 0)
					begin
				select count(*) from news where departmentid=@depid and gradeid=@gradeid and passed=@passed and updatetime between @startime and @endtime 
					end
					else
					begin
				select count(*) from news where departmentid=@depid and gradeid=@gradeid and passed=@passed and usersid = @userid and updatetime between @startime and @endtime 
					end
				end
			end
		end	
	end
end
else
begin

	if(@depid=0)
	begin
		if(@gradeid=0)
		begin
			if(@passed=2)
			begin
				if(@cb = 0)
				begin 
					if(@userid = 0)
					begin
					select count(*) from news where bigclassid=@bigclassid
					end
					else
					begin
					select count(*) from news where bigclassid=@bigclassid and usersid = @userid
					end
				end
				else
				begin
					if(@userid = 0)
					begin
				select count(*) from news where bigclassid=@bigclassid and updatetime between @startime and @endtime
					end
					else
					begin
				select count(*) from news where bigclassid=@bigclassid and usersid = @userid and updatetime between @startime and @endtime
					end
				end
			end
			else
			begin
				if(@cb = 0)
				begin 
					if(@userid = 0)
					begin
				select count(*) from news where bigclassid=@bigclassid and passed=@passed
					end
					else
					begin
				select count(*) from news where bigclassid=@bigclassid and passed=@passed and usersid = @userid
					end
				end
				else
				begin
					if(@userid = 0)
					begin
				select count(*) from news where bigclassid=@bigclassid and passed=@passed and updatetime between @startime and @endtime 
					end 
					else 
					begin 
				select count(*) from news where bigclassid=@bigclassid and passed=@passed and usersid = @userid and updatetime between @startime and @endtime 
					end
				end
			end
		end
		else
		begin
			if(@passed=2)
			begin
				if(@cb = 0)
				begin 
					if(@userid = 0)
					begin
				select count(*) from news where bigclassid=@bigclassid and gradeid=@gradeid
					end
					else
					begin
				select count(*) from news where bigclassid=@bigclassid and gradeid=@gradeid and usersid = @userid
					end
				end
				else
				begin
					if(@userid = 0)
					begin
				select count(*) from news where bigclassid=@bigclassid and gradeid=@gradeid and updatetime between @startime and @endtime
					end
					else
					begin
				select count(*) from news where bigclassid=@bigclassid and gradeid=@gradeid and usersid = @userid and updatetime between @startime and @endtime
					end
				end
			end
			else
			begin
				if(@cb = 0)
				begin 
					if(@userid = 0)
					begin
				select count(*) from news where bigclassid=@bigclassid and passed=@passed and gradeid=@gradeid
					end
					else
					begin
				select count(*) from news where bigclassid=@bigclassid and passed=@passed and gradeid=@gradeid and usersid = @userid 
					end
				end
				else
				begin
					if(@userid = 0)
					begin
				select count(*) from news where bigclassid=@bigclassid and gradeid=@gradeid and passed=@passed and updatetime between @startime and @endtime 
					end
					else
					begin
				select count(*) from news where bigclassid=@bigclassid and gradeid=@gradeid and usersid = @userid and passed=@passed and updatetime between @startime and @endtime 
					end
				end
			end
		end
	end
	else
	begin

	if(@gradeid=0)
		begin
			if(@passed=2)
			begin
				if(@cb = 0)
				begin 
					if(@userid = 0)
					begin
				select count(*) from news where bigclassid=@bigclassid and departmentid=@depid
					end
					else
					begin
				select count(*) from news where bigclassid=@bigclassid and departmentid=@depid and usersid = @userid
					end
				end
				else
				begin
					if(@userid = 0)
					begin
				select count(*) from news where bigclassid=@bigclassid and departmentid=@depid and updatetime between @startime and @endtime
					end
					else
					begin
				select count(*) from news where bigclassid=@bigclassid and departmentid=@depid and usersid = @userid and updatetime between @startime and @endtime
					end
				end
			end
			else
			begin
				if(@cb = 0)
				begin 
					if(@userid = 0)
					begin
				select count(*) from news where bigclassid=@bigclassid and departmentid=@depid and passed=@passed
					end
					else
					begin
				select count(*) from news where bigclassid=@bigclassid and departmentid=@depid and passed=@passed and usersid = @userid
					end
				end
				else
				begin
					if(@userid = 0)
					begin
				select count(*) from news where bigclassid=@bigclassid and departmentid=@depid and passed=@passed and updatetime between @startime and @endtime 
					end
					else
					begin
				select count(*) from news where bigclassid=@bigclassid and departmentid=@depid and passed=@passed and usersid = @userid and updatetime between @startime and @endtime 
					end
				end
			end
		end
		else
		begin
			if(@passed=2)
			begin
				if(@cb = 0)
				begin 
					if(@userid = 0)
					begin
				select count(*) from news where bigclassid=@bigclassid and departmentid=@depid and gradeid=@gradeid
					end
					else
					begin
				select count(*) from news where bigclassid=@bigclassid and departmentid=@depid and gradeid=@gradeid and usersid = @userid
					end
				end
				else
				begin
					if(@userid = 0)
					begin
				select count(*) from news where bigclassid=@bigclassid and departmentid=@depid and gradeid=@gradeid and updatetime between @startime and @endtime
					end
					else
					begin
				select count(*) from news where bigclassid=@bigclassid and departmentid=@depid and usersid = @userid and gradeid=@gradeid and updatetime between @startime and @endtime
					end
				end
			end
			else
			begin
				if(@cb = 0)
				begin 
					if(@userid = 0)
					begin
				select count(*) from news where bigclassid=@bigclassid and departmentid=@depid and passed=@passed and gradeid=@gradeid
					end
					else
					begin
				select count(*) from news where bigclassid=@bigclassid and departmentid=@depid and usersid = @userid and passed=@passed and gradeid=@gradeid
					end
				end
				else
				begin
					if(@userid = 0)
					begin
				select count(*) from news where bigclassid=@bigclassid and departmentid=@depid and gradeid=@gradeid and passed=@passed and updatetime between @startime and @endtime 
					end
					else
					begin
				select count(*) from news where bigclassid=@bigclassid and departmentid=@depid and usersid = @userid and gradeid=@gradeid and passed=@passed and updatetime between @startime and @endtime 
					end
				end
			end
		end	
	end
end

RETURN


GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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



CREATE PROCEDURE [dbo].[GetNewsByContent]
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeId int,
	@content 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.content like '%'+@content+'%')

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

	DELETE #PageIndexs
	RETURN
	end
	else
	begin
	

	-- Insert into our temp table
    INSERT	INTO #PageIndexs (ID)
    SELECT	n.pkId
	FROM	[News] AS n,[searchOptions] AS so
	WHERE	n.gradeId <= @gradeId and 
			n.passed = 1 and 
			(n.validDays > 0 OR n.validDays < 0) and
			(isDuplicated>1 OR isDuplicated<1 or isDuplicated is null) and
			n.bigClassId=@bigClassId and
			patindex('%|'+Convert(Varchar(50),n.bigClassId)+'|%',so.OptionsValue)>0 and so.bigClassId = @NewSearchbigClassId and
			(n.content like '%'+@content+'%')

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

	DELETE #PageIndexs
	RETURN



	end 

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

    -- Insert into our temp table
    INSERT	INTO #PageIndexs (ID)
	SELECT	n.pkId
	FROM	[News] AS n 
	WHERE	n.gradeId <= @gradeId and 
			n.passed = 1 and 
			(n.validDays > 0 OR n.validDays < 0) and
			(n.isDuplicated>1 OR n.isDuplicated<1 or n.isDuplicated is null) and 
			(n.content like '%'+@content+'%')


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

	DELETE #PageIndexs
	RETURN 

	end
	else
	begin
	
	-- Insert into our temp table
    INSERT	INTO #PageIndexs (ID)
	SELECT	n.pkId
	FROM	[News] AS n 
	WHERE	n.gradeId <= @gradeId and 
			n.passed = 1 and 
			(n.validDays > 0 OR n.validDays < 0) and
			(isDuplicated>1 OR isDuplicated<1 or isDuplicated is null) and
			n.bigClassId=@bigClassId and
			(n.content like '%'+@content+'%')


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

	DELETE #PageIndexs
	RETURN 
	end
end


GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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



CREATE PROCEDURE [dbo].[GetNewsByCopyFrom]
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeId int,
	@copyFrom 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.CopyFrom like '%'+@copyFrom+'%')

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

	DELETE #PageIndexs
	RETURN
	end
	else
	begin
	

	-- Insert into our temp table
    INSERT	INTO #PageIndexs (ID)
    SELECT	n.pkId
	FROM	[News] AS n,[searchOptions] AS so
	WHERE	n.gradeId <= @gradeId and 
			n.passed = 1 and 
			(n.validDays > 0 OR n.validDays < 0) and
			(isDuplicated>1 OR isDuplicated<1 or isDuplicated is null) and
			n.bigClassId=@bigClassId and
			patindex('%|'+Convert(Varchar(50),n.bigClassId)+'|%',so.OptionsValue)>0 and so.bigClassId = @NewSearchbigClassId and
			(n.CopyFrom like '%'+@copyFrom+'%')

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

	DELETE #PageIndexs
	RETURN



	end 

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

    -- Insert into our temp table
    INSERT	INTO #PageIndexs (ID)
	SELECT	n.pkId
	FROM	[News] AS n 
	WHERE	n.gradeId <= @gradeId and 
			n.passed = 1 and 
			(n.validDays > 0 OR n.validDays < 0) and
			(n.isDuplicated>1 OR n.isDuplicated<1 or n.isDuplicated is null) and
			(n.CopyFrom like '%'+@copyFrom+'%')


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

	DELETE #PageIndexs
	RETURN 

	end
	else
	begin
	
	-- Insert into our temp table
    INSERT	INTO #PageIndexs (ID)
	SELECT	n.pkId
	FROM	[News] AS n 
	WHERE	n.gradeId <= @gradeId and 
			n.passed = 1 and 
			(n.validDays > 0 OR n.validDays < 0) and
			(isDuplicated>1 OR isDuplicated<1 or isDuplicated is null) and
			n.bigClassId=@bigClassId and
			(n.CopyFrom like '%'+@copyFrom+'%')


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

	DELETE #PageIndexs
	RETURN 
	end
end

GO



SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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




CREATE PROCEDURE [dbo].[GetNewsByAuthor]
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeId int,
	@author 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.author like '%'+@author+'%')

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

	DELETE #PageIndexs
	RETURN
	end
	else
	begin
	

	-- Insert into our temp table
    INSERT	INTO #PageIndexs (ID)
    SELECT	n.pkId
	FROM	[News] AS n,[searchOptions] AS so
	WHERE	n.gradeId <= @gradeId and 
			n.passed = 1 and 
			(n.validDays > 0 OR n.validDays < 0) and
			(isDuplicated>1 OR isDuplicated<1 or isDuplicated is null) and
			n.bigClassId=@bigClassId and
			patindex('%|'+Convert(Varchar(50),n.bigClassId)+'|%',so.OptionsValue)>0 and so.bigClassId = @NewSearchbigClassId and
			(n.author like '%'+@author+'%')

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

	DELETE #PageIndexs
	RETURN



	end 

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

    -- Insert into our temp table
    INSERT	INTO #PageIndexs (ID)
	SELECT	n.pkId
	FROM	[News] AS n 
	WHERE	n.gradeId <= @gradeId and 
			n.passed = 1 and 
			(n.validDays > 0 OR n.validDays < 0) and
			(n.isDuplicated>1 OR n.isDuplicated<1 or n.isDuplicated is null) and	
			(n.author like '%'+@author+'%')


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

	DELETE #PageIndexs
	RETURN 

	end
	else
	begin
	
	-- Insert into our temp table
    INSERT	INTO #PageIndexs (ID)
	SELECT	n.pkId
	FROM	[News] AS n 
	WHERE	n.gradeId <= @gradeId and 
			n.passed = 1 and 
			(n.validDays > 0 OR n.validDays < 0) and
			(isDuplicated>1 OR isDuplicated<1 or isDuplicated is null) and
			n.bigClassId=@bigClassId and
			(n.author like '%'+@author+'%')


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

	DELETE #PageIndexs
	RETURN 
	end
end

GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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



CREATE PROCEDURE [dbo].[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+'%' )

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

	DELETE #PageIndexs
	RETURN
	end
	else
	begin
	

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

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

	DELETE #PageIndexs
	RETURN



	end 

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

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


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

	DELETE #PageIndexs
	RETURN 

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


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

	DELETE #PageIndexs
	RETURN 
	end
end

GO





SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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




CREATE PROCEDURE [dbo].[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+'%' )

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

	DELETE #PageIndexs
	RETURN
	end
	else
	begin
	

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

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

	DELETE #PageIndexs
	RETURN

	end 

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

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

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

	DELETE #PageIndexs
	RETURN 

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

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

	DELETE #PageIndexs
	RETURN 
	end
end



GO



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

create PROCEDURE [dbo].[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,N.usersId, ';
	set	@select=@select+' case U.isDel when 1 then U.username'+'+'+'''<已删除>'''+' else U.username end username,COUNT(N.pkId) fbcount,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,N.usersId,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';
		end
	else
		set @select= @select+',''所有栏目''as name';
	if(@passed <> 2)
	set @where=@where+' and N.passed='+STR(@passed);
	--执行查询
	EXEC(@select + @from + @where + @group );

	
END

GO




SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


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

create PROCEDURE [dbo].[GetContributeByStatTime]
	@beginTime varchar(20),
	@endTime varchar(20),
	@pageIndex int,
	@pageSize int,
	@recordCount int output,
	@contributeSortType int
AS
BEGIN	
	SET NOCOUNT ON;
	
	DECLARE @isSql2005 int, @isSql2000 int, @tempTable varchar(20)		
	
	-- 判断数据库版本  修正支持sql2005 以上版本
	IF PATINDEX('%2005 - 9%', @@VERSION)<>0
	BEGIN
		SET @isSql2005 = 1
		SET @isSql2000 = 0
	END
	ELSE IF PATINDEX('%2000 - 8%', @@VERSION)<>0
	BEGIN
		SET @isSql2000 = 1
		SET @isSql2005 = 0
		SET @tempTable = 'TempA' + LTRIM(STR(YEAR(GetDate()))) + LTRIM(STR(MONTH(GetDate()))) + LTRIM(STR(DAY(GetDate()))) 
					+ LTRIM(STR(DATEPART(hh,GetDate()))) + LTRIM(STR(DATEPART(n,GetDate()))) + LTRIM(STR(DATEPART(ss,GetDate())))
	END
	else
	begin
		SET @isSql2005 = 1
		SET @isSql2000 = 0
	end
		
	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(2000), @sortFiled varchar(50)
	if(@contributeSortType=0)
		SET @sortFiled = 'articleNums'
	Else
		SET @sortFiled = 'totalNums'
	
	SET @vSQL = N'SELECT @count = Count(*) FROM (SELECT u.pkId as userId, Count(n.pkId) as articleNums FROM Users u, news n, contribute c '
			+'WHERE u.pkId = c.userId AND c.newsId = n.pkId AND c.state = 1 AND c.updateTime >= ''' + @beginTime + ''' AND c.updateTime <''' 
			+ @endTime + ''' GROUP BY u.pkId) TempTable'
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum
	
	--转换成索引
	DECLARE @pageIndex_1 int
	SELECT @pageIndex_1 = @pageIndex-1	
	
	--计算页数
	SET @pageCount = (@recordCount +@pageSize-1)/@pageSize

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

	--判断分页大小，查询最后一页用
	SET @size = @recordCount%@pageSize	
	IF(@size=0)
		SET @size = @pageSize	

	DECLARE @insert varchar(1000)
		
	IF (@isSql2005 = 1) AND (@isSql2000 = 0)	--sql2005执行
	BEGIN
		if(@contributeSortType = 0)
		BEGIN
		SET	@sqlStr = 'SELECT * FROM (SELECT  u.pkId AS userId, u.userName AS userName, u.fullName AS fullName, u.tel AS tel, u.email AS email, '
				+ 'Count(n.pkId) AS articleNums,(select Count(*) from contribute where userId=u.pkId and updateTime>=''' + @beginTime+ ''' and updateTime<''' + @endTime + ''' and state<>5) AS totalNums, rank() over(order by Count(n.pkId) DESC, u.fullName ASC) AS ID , row_number() over(order by Count(n.pkId) DESC, u.fullName ASC) AS pkId '
				+ 'FROM Users u, news n, contribute c WHERE u.pkId = c.userId AND c.newsId = n.pkId AND c.state = 1 AND c.updateTime >= '''
				+ @beginTime + ''' AND c.updateTime <''' + @endTime + ''' GROUP BY u.pkId, userName, fullName, tel, email) '
				+ 'TempTable WHERE pkId > ' + STR(@pageIndex_1*@pageSize) + ' AND pkId<=' + STR(@pageIndex*@pageSize) + 'ORDER BY ID asc'
		END
		ELSE
		BEGIN
			SET	@sqlStr = 'SELECT * FROM (SELECT  u.pkId AS userId, u.userName AS userName, u.fullName AS fullName, u.tel AS tel, u.email AS email, '
				+ 'Count(n.pkId) AS articleNums,(select Count(*) from contribute where userId=u.pkId and updateTime>=''' + @beginTime+ ''' and updateTime<''' + @endTime + ''' and state<>5) AS totalNums, rank() over(order by (select Count(*) from contribute where userId=u.pkId and updateTime>=''' + @beginTime+ ''' and updateTime<''' + @endTime + ''' and state<>5) DESC, u.fullName ASC) AS ID , row_number() over(order by (select Count(*) from contribute where userId=u.pkId and updateTime>=''' + @beginTime+ ''' and updateTime<''' + @endTime + ''' and state<>5) DESC, u.fullName ASC) AS pkId '
				+ 'FROM Users u, news n, contribute c WHERE u.pkId = c.userId AND c.newsId = n.pkId AND c.state = 1 AND c.updateTime >= '''
				+ @beginTime + ''' AND c.updateTime <''' + @endTime + ''' GROUP BY u.pkId, userName, fullName, tel, email) '
				+ 'TempTable WHERE pkId > ' + STR(@pageIndex_1*@pageSize) + ' AND pkId<=' + STR(@pageIndex*@pageSize) + 'ORDER BY ID asc'
		END
	END
	ELSE IF (@isSql2005 = 0) AND (@isSql2000 =1) --sql2000执行
	BEGIN	
		SET @insert = 'SELECT Count(n.pkId) AS articleNums,(select Count(*) from contribute where userId=u.pkId and updateTime>=''' + @beginTime+ ''' and updateTime<''' + @endTime + ''' and state<>5) AS totalNums, u.pkId AS userId, u.userName AS userName, u.fullName AS fullName, u.tel AS tel, u.email AS email'
				+ ' into ' + @tempTable + ' FROM Users u, news n, contribute c WHERE u.pkId = c.userId AND c.newsId = n.pkId AND '
				+ 'c.state = 1 AND c.updateTime >= ''' + @beginTime + ''' AND c.updateTime <''' + @endTime + ''' GROUP BY u.pkId, userName, fullName, tel, email '
				+ 'ORDER BY '+@sortFiled+' DESC,  u.fullName ASC'	
		EXEC(@insert)
		 
		DECLARE @tempTable1 varchar(1000)
		SET @tempTable1 = 'TempC' + LTRIM(STR(YEAR(GetDate()))) + LTRIM(STR(MONTH(GetDate()))) + LTRIM(STR(DAY(GetDate()))) 
					+ LTRIM(STR(DATEPART(hh,GetDate()))) + LTRIM(STR(DATEPART(n,GetDate()))) + LTRIM(STR(DATEPART(ss,GetDate())))

		SET @insert = 'SELECT identity(int, 1, 1) as pkId , 1 as ID, * into ' + @tempTable1 + ' FROM  '+@tempTable
		EXEC(@insert)
		SET @insert = 'DROP TABLE ' + @tempTable	
		EXEC(@insert)		
		SET @insert = 'declare @id int, @nums int declare myCursor CURSOR FOR SELECT MIN(pkId) as Id, '+@sortFiled+' as nums FROM ' + @tempTable1 + ' GROUP BY '+@sortFiled+' ORDER BY Id asc '
				+ 'OPEN myCursor FETCH NEXT FROM myCursor INTO @id, @nums WHILE @@FETCH_STATUS = 0 BEGIN UPDATE ' + @tempTable1 + ' SET ID=@id WHERE '+@sortFiled+'=@nums '
				+ 'FETCH NEXT FROM myCursor INTO @id, @nums END CLOSE myCursor DEALLOCATE myCursor'
		EXEC(@insert)
		IF @pageIndex_1 <= 0
		BEGIN
			SET	@sqlStr = 'SELECT TOP ' + STR(@pageSize) + '* FROM ' + @tempTable1 + ' ORDER BY ID ASC, fullName ASC'
		END
		ELSE IF @pageIndex_1 >= @pageCount -1
		BEGIN
			SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' * FROM ' + @tempTable1 + ' ORDER BY ID DESC, fullName DESC) TempTable ORDER BY ID ASC, fullName ASC'
		END
		ELSE IF @pageIndex_1 > @pageCount/2
		BEGIN
			SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + '* FROM (SELECT TOP ' + STR(@recordCount - @pageIndex * @pageSize) + ' * FROM ' + @tempTable1
					+ ' ORDER BY ID DESC, fullName DESC) TempTable ORDER BY ID ASC, fullName ASC'
		END
		ELSE BEGIN
			SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + '* FROM (SELECT TOP ' + STR(@pageIndex * @pageSize) + ' * FROM ' + @tempTable1
					+ ' ORDER BY ID ASC, fullName ASC) TempTable ORDER BY ID DESC, fullName DESC) TempTable1 ORDER BY ID ASC, fullName ASC'
		END		
		SET @insert = 'DROP TABLE ' + @tempTable1
	END
	--执行查询
	EXEC(@sqlStr)
	
	EXEC(@insert)
END

GO



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


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

create PROCEDURE [dbo].[UpdateSiteOtherProperty]
	@pkId int,
	@newsDefaultClick int,
	@showNewHours int,
	@showHotClick int,
	@isAllowNewsUpdtime int
AS

SET NOCOUNT ON

UPDATE [dbo].[siteOtherProperty] SET
	[newsDefaultClick] = @newsDefaultClick,
	[showNewHours] = @showNewHours,
	[showHotClick] = @showHotClick,
	[isAllowNewsUpdtime]=@isAllowNewsUpdtime

GO



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


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

create PROCEDURE [dbo].[GetNewsCountByBigClassId]
(
	@bigClassId int
)
AS

select count(*) from news where bigclassid=@bigClassId

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


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

create PROCEDURE [dbo].[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

  COMMIT TRANSACTION   

GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


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

create PROCEDURE [dbo].[DeleteUsers]
(
	@pkId int
)
AS
	SET NOCOUNT OFF;
 BEGIN TRANSACTION
DELETE FROM moduleAssign where(usersId=@pkId)
--DELETE FROM riches where (usersId=@pkId)
--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)
         COMMIT TRANSACTION

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


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


create PROCEDURE [dbo].[UpDateBigClass]
(
	@pkId int,
	@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;
BEGIN TRANSACTION;		-- added on 201212 by shijian
	UPDATE bigClass SET name = @name, remark = @remark, templateId = @templateId ,adminId=@adminId,updateTime=@updateTime,isSystemItem=@isSystemItem, cssName = @cssName,morePageSize=@morePageSize  WHERE (pkId = @pkId);
	update menu set name = @name where  bigClassId=@pkId and bigClassId > 0 ; -- added on 201212 by shijian,同步更新menu表的栏目名称
COMMIT TRANSACTION ;	

GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


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

create PROCEDURE [dbo].[GetShowItem]
@gradeId int=1,
@parentId int,
@gateId int
AS
	select m.*,t.code,b.name as bigclassname,b.cssname from menu m,bigClass b,template t where m.parentId=@parentId and m.gate<>@gateId and m.gradeId<=@gradeId and m.bigClassId=b.pkId and b.templateId=t.pkId order by m.pkId desc,ordering desc

RETURN


GO



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


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

create PROCEDURE [dbo].[InsertMenuSet]
(
	@pkId int,
	@name varchar(100),
	@bgPic varchar(100),
	@backGroundPic varchar(100),
	@width int,
	@effect varchar(100),
	@BackgroundColor varchar(100),
	@SelectedColor varchar(100),
	@OuterHighlightColor varchar(100),
	@InnerShadowColor varchar(100),
	@InnerHighlightColor varchar(100),
	@OuterShadowColor varchar(100),
	@SelectedTextColor varchar(100),
	@fontSize varchar(100),
	@fontColor varchar(100),
	@direction varchar(100),
	@menuDirection varchar(100),
	@itemBgPic varchar(100),
	@itemIconWidth int,
	@itemIconHeight int,
	@itemIconRightWidth int,
	@PaddingLeft int,
	@DividerHeight int,
	@DividerColor varchar(100),
	@DividerImageAlignment varchar(100),
	@MenuShow int,
	@adminId int,
    @rowNum int,
    @fontWeight varchar(100),
    @subMenuFontSize varchar(100),
	@menulist text
)
AS
	SET NOCOUNT OFF;

INSERT INTO menuSet([name], bgPic, backGroundPic, width, effect, BackgroundColor, SelectedColor, OuterHighlightColor, InnerShadowColor, InnerHighlightColor, OuterShadowColor, SelectedTextColor, fontSize, fontColor, direction	,menuDirection,itemBgPic,itemIconWidth,itemIconHeight,itemIconRightWidth,PaddingLeft,DividerHeight,DividerColor,DividerImageAlignment,MenuShow,adminId,rowNum,fontWeight,subMenuFontSize,menulist
) VALUES (@name, @bgPic, @backGroundPic, @width, @effect, @BackgroundColor, @SelectedColor, @OuterHighlightColor, @InnerShadowColor, @InnerHighlightColor, @OuterShadowColor, @SelectedTextColor, @fontSize, @fontColor, @direction,@menuDirection,@itemBgPic,@itemIconWidth,@itemIconHeight,@itemIconRightWidth,@PaddingLeft,@DividerHeight,@DividerColor,@DividerImageAlignment,@MenuShow,@adminId,@rowNum,@fontWeight,@subMenuFontSize,@menulist)

GO



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


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

create PROCEDURE [dbo].[UpdateMenuSet]
(
    @pkId int,
	@name varchar(100),
	@bgPic varchar(100),
	@backGroundPic varchar(100),
	@width int,
	@effect varchar(100),
	@BackgroundColor varchar(100),
	@SelectedColor varchar(100),
	@OuterHighlightColor varchar(100),
	@InnerShadowColor varchar(100),
	@InnerHighlightColor varchar(100),
	@OuterShadowColor varchar(100),
	@SelectedTextColor varchar(100),
	@fontSize varchar(100),
	@fontColor varchar(100),
	@direction varchar(100),
	@menuDirection varchar(100),
	@itemBgPic varchar(100),
	@itemIconWidth int,
	@itemIconHeight int,
	@itemIconRightWidth int,
	@PaddingLeft int,
	@DividerHeight int,
	@DividerColor varchar(100),
	@DividerImageAlignment varchar(100),
	@MenuShow int,
	@adminId int,
    @rowNum int,
    @fontWeight varchar(100),
	@subMenuFontSize varchar(100),
	@menulist text
	
)
AS
	SET NOCOUNT OFF;
UPDATE menuSet SET adminId=@adminId,[name] = @name, bgPic = @bgPic, backGroundPic = @backGroundPic, width = @width, effect = @effect, BackgroundColor = @BackgroundColor, SelectedColor = @SelectedColor, OuterHighlightColor = @OuterHighlightColor, InnerShadowColor = @InnerShadowColor, InnerHighlightColor = @InnerHighlightColor, OuterShadowColor = @OuterShadowColor, SelectedTextColor = @SelectedTextColor, fontSize = @fontSize, fontColor = @fontColor, direction = @direction  ,menuDirection=@menuDirection,itemBgPic=@itemBgPic,itemIconWidth=@itemIconWidth,itemIconHeight=@itemIconHeight,itemIconRightWidth=@itemIconRightWidth,	paddingLeft=@PaddingLeft,DividerHeight=@DividerHeight,DividerColor=@DividerColor,DividerImageAlignment=@DividerImageAlignment,MenuShow=@MenuShow,rowNum=@rowNum,fontWeight=@fontWeight,subMenuFontSize = @subMenuFontSize, menulist=@menulist WHERE (pkId = @pkId)

GO




SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


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

create PROCEDURE [dbo].[GetLinkByBigClassId]
@bigClassId int=null,
@currentPage int=1,
@pageSize int=5,
@gradeid int=1,
@gateId int
AS
	SET NOCOUNT ON 
	SET @currentPage = @currentPage - 1
	IF (@currentPage < 0)
		BEGIN
			SET @currentPage = 1
		END
	-- Set the page bounds
    DECLARE @PageLowerBound INT
    DECLARE @PageUpperBound INT
    DECLARE @TotalRecords   INT
    SET @PageLowerBound = @pageSize * @currentPage
    SET @PageUpperBound = @pageSize - 1 + @PageLowerBound
	-- Create a temp table TO store the select results
    CREATE TABLE #PageIndexs
    (
        IndexId int IDENTITY (0, 1) NOT NULL,
        ID int
    )

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

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

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

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

	DELETE #PageIndexs


RETURN 


GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


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

create PROCEDURE [dbo].[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
    )

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

	SELECT  n.*, 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

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


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

create PROCEDURE [dbo].[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
    )

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

	SELECT  n.*, 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
