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


CREATE PROCEDURE [dbo].[DeleteContribute]
(
	@pkId int
)
AS
	SET NOCOUNT OFF;
	BEGIN TRANSACTION
	DELETE FROM contribute WHERE (pkId = @pkId)
	COMMIT TRANSACTION

GO

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

CREATE PROCEDURE [dbo].[DeleteContributeClassByContributeClassIdAndBigClassId]
(
	@contributeClassId int,	-- menuId
	@bigClassId int
)
AS
	SET NOCOUNT OFF;
       
       DELETE FROM contribute 
       WHERE MenuId = @contributeClassId AND contributeId = @bigClassId
       DELETE FROM contributeClass WHERE contributeClassId = @contributeClassId and bigClassId = @bigClassId

GO

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

CREATE PROCEDURE [dbo].[GetContributeByMenuIdAndBigClassId]
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@menuId int,
	@bigClassId int,
	@recordCount int output	
AS	
	--转换成索引
	SELECT @currentPage = @currentPage-1	

	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT @count = Count(c.pkId) FROM contribute c, users u WHERE c.menuId=' + CONVERT(nvarchar,@menuId) 
				+ N' AND c.bigClassId=' + CONVERT(nvarchar,@bigClassId) + N' AND c.userId=u.pkId and c.state<>5'
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum

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

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

	--判断分页大小，查询最后一页用
	SET @size = @recordCount%@pageSize	
	IF(@size=0)
		SET @size = @pageSize	
	
	--查询第一页
	IF (@currentPage = 0) OR (@currentPage > @pageCount-1)	
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' c.*,u.userName "userName",u.fullName "fullName" FROM contribute c, users u '
					+ 'WHERE c.menuId=' + STR(@menuId) + ' AND c.bigClassId=' + STR(@bigClassId) + ' AND c.userId=u.pkId and c.state<>5 ORDER BY c.pkId DESC'	
	--查询最后一页
	ELSE IF @currentPage = @pageCount -1			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' c.*,u.userName "userName",u.fullName "fullName" FROM contribute c, users u '
					+ 'WHERE c.menuId<=' + STR(@menuId) + ' AND c.bigClassId=' + STR(@bigClassId) + ' AND c.userId=u.pkId and c.state<>5 ORDER BY c.pkId ASC) TempTable ORDER BY pkId DESC'	
	--查询中间页后面的页
	ELSE IF @currentPage > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @currentPage ) + ' c.*,u.userName "userName",u.fullName "fullName"  FROM contribute c, users u '
					+ 'WHERE c.menuId=' + STR(@menuId) + ' AND c.bigClassId=' + STR(@bigClassId) + ' AND c.userId=u.pkId and c.state<>5 ORDER BY c.pkId ASC) TempTable ORDER BY pkId DESC'
	--查询中间页前面的页
 	ELSE BEGIN							
		SET @currentPage=@currentPage+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @currentPage ) + ' c.*,u.userName "userName",u.fullName "fullName"  FROM contribute c, users u '
					+ 'WHERE c.menuId=' + STR(@menuId) + ' AND c.bigClassId=' + STR(@bigClassId) + ' AND c.userId=u.pkId and c.state<>5 ORDER BY c.pkId DESC) TempTable1 '
					+ ' ORDER BY pkId ASC) TempTable2 ORDER BY pkId DESC'
	END

	--执行查询
	EXEC(@sqlStr)

RETURN

GO

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

CREATE PROCEDURE [dbo].[GetContributeByMenuIdAndBigClassIdAndState]
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@menuId int,
	@bigClassId int,
	@state int,
	@recordCount int output	
AS	
	--转换成索引
	SELECT @currentPage = @currentPage-1	

	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT @count = Count(c.pkId) FROM contribute c, users u WHERE c.menuId=' + CONVERT(nvarchar,@menuId) 
				+ N' AND c.bigClassId=' + CONVERT(nvarchar,@bigClassId) + N' AND c.userId=u.pkId and c.state='+CONVERT(nvarchar,@state)
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum

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

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

	--判断分页大小，查询最后一页用
	SET @size = @recordCount%@pageSize	
	IF(@size=0)
		SET @size = @pageSize	
	
	--查询第一页
	IF (@currentPage = 0) OR (@currentPage > @pageCount-1)	
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' c.*,u.userName "userName",u.fullName "fullName" FROM contribute c, users u '
					+ 'WHERE c.menuId=' + STR(@menuId) + ' AND c.bigClassId=' + STR(@bigClassId) + ' AND c.userId=u.pkId and c.state='+CONVERT(nvarchar,@state)+' ORDER BY c.pkId DESC'	
	--查询最后一页
	ELSE IF @currentPage = @pageCount -1			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' c.*,u.userName "userName",u.fullName "fullName" FROM contribute c, users u '
					+ 'WHERE c.menuId<=' + STR(@menuId) + ' AND c.bigClassId=' + STR(@bigClassId) + ' AND c.userId=u.pkId and c.state='+CONVERT(nvarchar,@state)+' ORDER BY c.pkId ASC) TempTable ORDER BY pkId DESC'	
	--查询中间页后面的页
	ELSE IF @currentPage > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @currentPage ) + ' c.*,u.userName "userName",u.fullName "fullName"  FROM contribute c, users u '
					+ 'WHERE c.menuId=' + STR(@menuId) + ' AND c.bigClassId=' + STR(@bigClassId) + ' AND c.userId=u.pkId and c.state='+CONVERT(nvarchar,@state)+' ORDER BY c.pkId ASC) TempTable ORDER BY pkId DESC'
	--查询中间页前面的页
 	ELSE BEGIN							
		SET @currentPage=@currentPage+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @currentPage ) + ' c.*,u.userName "userName",u.fullName "fullName"  FROM contribute c, users u '
					+ 'WHERE c.menuId=' + STR(@menuId) + ' AND c.bigClassId=' + STR(@bigClassId) + ' AND c.userId=u.pkId and c.state='+CONVERT(nvarchar,@state)+' ORDER BY c.pkId DESC) TempTable1 '
					+ ' ORDER BY pkId ASC) TempTable2 ORDER BY pkId DESC'
	END

	--执行查询
	EXEC(@sqlStr)

RETURN

GO






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

CREATE PROCEDURE [dbo].[GetContributeByMenuIdAndBigClassIdAndUserId]
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@menuId int,
	@bigClassId int,
	@userId int,
	@recordCount int output	
AS	
	--转换成索引
	SELECT @currentPage = @currentPage-1	

	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT @count = Count(c.pkId) FROM contribute c, users u WHERE c.menuId=' + CONVERT(nvarchar,@menuId) 
				+ N' AND c.bigClassId=' + CONVERT(nvarchar,@bigClassId) + N' AND c.userId=' + CONVERT(nvarchar,@userId) 
				+ N' AND c.userId=u.pkId'
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum

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

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

	--判断分页大小，查询最后一页用
	SET @size = @recordCount%@pageSize	
	IF(@size=0)
		SET @size = @pageSize	
		
	--查询第一页
	IF (@currentPage = 0) OR (@currentPage > @pageCount-1)	
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' c.*,u.userName "userName",u.fullName "fullName"  FROM contribute c, users u '
					+ 'WHERE c.menuId=' + STR(@menuId) + ' AND c.bigClassId=' + STR(@bigClassId) + ' AND c.userId=' + STR(@userId) 
					+ ' and c.userId=u.pkId ORDER BY c.pkId DESC'	
	--查询最后一页
	ELSE IF @currentPage = @pageCount -1	
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' c.*,u.userName "userName",u.fullName "fullName" FROM contribute c, users u '
					+ 'WHERE c.menuId=' + STR(@menuId) + ' AND c.bigClassId=' + STR(@bigClassId) + ' AND c.userId=' + STR(@userId) 
					+ ' and c.userId=u.pkId ORDER BY c.pkId ASC) TempTable ORDER BY pkId DESC'	
	--查询中间页后面的页
	ELSE IF @currentPage > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @currentPage ) + ' c.*,u.userName "userName",u.fullName "fullName"  FROM contribute c, users u '
					+ 'WHERE c.menuId<=' + STR(@menuId) + ' AND c.bigClassId=' + STR(@bigClassId) + ' AND c.userId=' + STR(@userId) 
					+ ' and c.userId=u.pkId ORDER BY c.pkId ASC) TempTable ORDER BY pkId DESC'
	--查询中间页前面的页
 	ELSE BEGIN							
		SET @currentPage=@currentPage+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @currentPage ) + ' c.*,u.userName "userName",u.fullName "fullName" FROM contribute c, users u '
					+ 'WHERE c.menuId=' + STR(@menuId) + ' AND c.bigClassId=' + STR(@bigClassId) + ' AND c.userId=' + STR(@userId) 
					+ ' AND c.userId=u.pkId ORDER BY c.pkId DESC) TempTable1 '
					+ ' ORDER BY pkId ASC) TempTable2 ORDER BY pkId DESC'
	END

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

GO


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

CREATE PROCEDURE [dbo].[GetContributeByMenuIdAndBigClassIdAndUserIdAndState]
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@menuId int,
	@bigClassId int,
	@userId int,
	@state int,
	@recordCount int output	
AS	
	--转换成索引
	SELECT @currentPage = @currentPage-1	

	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT @count = Count(c.pkId) FROM contribute c, users u WHERE c.menuId=' + CONVERT(nvarchar,@menuId) 
				+ N' AND c.bigClassId=' + CONVERT(nvarchar,@bigClassId) + N' AND c.state='+CONVERT(nvarchar,@state)+' and c.userId=' + CONVERT(nvarchar,@userId) 
				+ N' AND c.userId=u.pkId'
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum

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

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

	--判断分页大小，查询最后一页用
	SET @size = @recordCount%@pageSize	
	IF(@size=0)
		SET @size = @pageSize	
		
	--查询第一页
	IF (@currentPage = 0) OR (@currentPage > @pageCount-1)	
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' c.*,u.userName "userName",u.fullName "fullName"  FROM contribute c, users u '
					+ 'WHERE c.menuId=' + STR(@menuId) + ' AND c.bigClassId=' + STR(@bigClassId) + ' AND c.state='+CONVERT(nvarchar,@state)+' and c.userId=' + STR(@userId) 
					+ ' and c.userId=u.pkId ORDER BY c.pkId DESC'	
	--查询最后一页
	ELSE IF @currentPage = @pageCount -1	
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' c.*,u.userName "userName",u.fullName "fullName" FROM contribute c, users u '
					+ 'WHERE c.menuId=' + STR(@menuId) + ' AND c.bigClassId=' + STR(@bigClassId) + ' AND c.state='+CONVERT(nvarchar,@state)+' and c.userId=' + STR(@userId) 
					+ ' and c.userId=u.pkId ORDER BY c.pkId ASC) TempTable ORDER BY pkId DESC'	
	--查询中间页后面的页
	ELSE IF @currentPage > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @currentPage ) + ' c.*,u.userName "userName",u.fullName "fullName"  FROM contribute c, users u '
					+ 'WHERE c.menuId<=' + STR(@menuId) + ' AND c.bigClassId=' + STR(@bigClassId) + ' AND c.state='+CONVERT(nvarchar,@state)+' and c.userId=' + STR(@userId) 
					+ ' and c.userId=u.pkId ORDER BY c.pkId ASC) TempTable ORDER BY pkId DESC'
	--查询中间页前面的页
 	ELSE BEGIN							
		SET @currentPage=@currentPage+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @currentPage ) + ' c.*,u.userName "userName",u.fullName "fullName" FROM contribute c, users u '
					+ 'WHERE c.menuId=' + STR(@menuId) + ' AND c.bigClassId=' + STR(@bigClassId) + ' AND c.state='+CONVERT(nvarchar,@state)+' and c.userId=' + STR(@userId) 
					+ ' AND c.userId=u.pkId ORDER BY c.pkId DESC) TempTable1 '
					+ ' ORDER BY pkId ASC) TempTable2 ORDER BY pkId DESC'
	END

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


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

CREATE PROCEDURE [dbo].[GetContributeByMenuIdAndBigClassIdAndUserIdForAudit]
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@menuId int,
	@bigClassId int,
	@userId int,
	@recordCount int output	
AS	
	--转换成索引
	SELECT @currentPage = @currentPage-1	

	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT @count = Count(c.pkId) FROM contribute c, users u WHERE c.menuId=' + CONVERT(nvarchar,@menuId) 
				+ N' AND c.bigClassId=' + CONVERT(nvarchar,@bigClassId) + N' AND c.userId=' + CONVERT(nvarchar,@userId) 
				+ N' AND c.userId=u.pkId and (c.state=0 or c.state=2)'
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum

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

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

	--判断分页大小，查询最后一页用
	SET @size = @recordCount%@pageSize	
	IF(@size=0)
		SET @size = @pageSize	
		
	--查询第一页
	IF (@currentPage = 0) OR (@currentPage > @pageCount-1)	
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' c.*,u.userName "userName",u.fullName "fullName"  FROM contribute c, users u '
					+ 'WHERE c.menuId=' + STR(@menuId) + ' AND c.bigClassId=' + STR(@bigClassId) + ' AND c.userId=' + STR(@userId) 
					+ ' and c.userId=u.pkId and (c.state=0 or c.state=2) ORDER BY c.pkId DESC'	
	--查询最后一页
	ELSE IF @currentPage = @pageCount -1	
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' c.*,u.userName "userName",u.fullName "fullName" FROM contribute c, users u '
					+ 'WHERE c.menuId=' + STR(@menuId) + ' AND c.bigClassId=' + STR(@bigClassId) + ' AND c.userId=' + STR(@userId) 
					+ ' and c.userId=u.pkId and (c.state=0 or c.state=2) ORDER BY c.pkId ASC) TempTable ORDER BY pkId DESC'	
	--查询中间页后面的页
	ELSE IF @currentPage > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @currentPage ) + ' c.*,u.userName "userName",u.fullName "fullName"  FROM contribute c, users u '
					+ 'WHERE c.menuId<=' + STR(@menuId) + ' AND c.bigClassId=' + STR(@bigClassId) + ' AND c.userId=' + STR(@userId) 
					+ ' and c.userId=u.pkId and (c.state=0 or c.state=2) ORDER BY c.pkId ASC) TempTable ORDER BY pkId DESC'
	--查询中间页前面的页
 	ELSE BEGIN							
		SET @currentPage=@currentPage+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @currentPage ) + ' c.*,u.userName "userName",u.fullName "fullName" FROM contribute c, users u '
					+ 'WHERE c.menuId=' + STR(@menuId) + ' AND c.bigClassId=' + STR(@bigClassId) + ' AND c.userId=' + STR(@userId) 
					+ ' AND c.userId=u.pkId and (c.state=0 or c.state=2) ORDER BY c.pkId DESC) TempTable1 '
					+ ' ORDER BY pkId ASC) TempTable2 ORDER BY pkId DESC'
	END

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

GO


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

CREATE PROCEDURE [dbo].[GetContributeByMenuIdAndBigClassIdForAudit]
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@menuId int,
	@bigClassId int,
	@recordCount int output	
AS	
	--转换成索引
	SELECT @currentPage = @currentPage-1	

	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT @count = Count(c.pkId) FROM contribute c, users u WHERE c.menuId=' + CONVERT(nvarchar,@menuId) 
				+ N' AND c.bigClassId=' + CONVERT(nvarchar,@bigClassId) + N' AND c.userId=u.pkId and (c.state=0 or c.state=2)'
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum

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

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

	--判断分页大小，查询最后一页用
	SET @size = @recordCount%@pageSize	
	IF(@size=0)
		SET @size = @pageSize	
	
	--查询第一页
	IF (@currentPage = 0) OR (@currentPage > @pageCount-1)	
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' c.*,u.userName "userName",u.fullName "fullName" FROM contribute c, users u '
					+ 'WHERE c.menuId=' + STR(@menuId) + ' AND c.bigClassId=' + STR(@bigClassId) + ' AND c.userId=u.pkId and (c.state=0 or c.state=2) ORDER BY c.pkId DESC'	
	--查询最后一页
	ELSE IF @currentPage = @pageCount -1			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' c.*,u.userName "userName",u.fullName "fullName" FROM contribute c, users u '
					+ 'WHERE c.menuId<=' + STR(@menuId) + ' AND c.bigClassId=' + STR(@bigClassId) + ' AND c.userId=u.pkId and (c.state=0 or c.state=2) ORDER BY c.pkId ASC) TempTable ORDER BY pkId DESC'	
	--查询中间页后面的页
	ELSE IF @currentPage > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @currentPage ) + ' c.*,u.userName "userName",u.fullName "fullName"  FROM contribute c, users u '
					+ 'WHERE c.menuId=' + STR(@menuId) + ' AND c.bigClassId=' + STR(@bigClassId) + ' AND c.userId=u.pkId and (c.state=0 or c.state=2) ORDER BY c.pkId ASC) TempTable ORDER BY pkId DESC'
	--查询中间页前面的页
 	ELSE BEGIN							
		SET @currentPage=@currentPage+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @currentPage ) + ' c.*,u.userName "userName",u.fullName "fullName"  FROM contribute c, users u '
					+ 'WHERE c.menuId=' + STR(@menuId) + ' AND c.bigClassId=' + STR(@bigClassId) + ' AND c.userId=u.pkId and (c.state=0 or c.state=2) ORDER BY c.pkId DESC) TempTable1 '
					+ ' ORDER BY pkId ASC) TempTable2 ORDER BY pkId DESC'
	END

	--执行查询
	EXEC(@sqlStr)

RETURN

GO

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

CREATE PROCEDURE [dbo].[GetContributeByPkId]
@pkId int =null
AS
	 SET NOCOUNT ON 
	select *
	from contribute
	where pkId=@pkId
	  
	RETURN 
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
AS
BEGIN	
	SET NOCOUNT ON;
	
	DECLARE @isSql2005 int, @isSql2000 int, @tempTable varchar(20)
	
	-- 判断数据库版本
	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
		
	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(2000)
	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
		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 IF (@isSql2005 = 0) AND (@isSql2000 =1) --sql2000执行
	BEGIN	
		SET @insert = 'SELECT identity(int, 1, 1) as pkId , 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 articleNums 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 pkId 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(ID) as Id, articleNums as nums FROM ' + @tempTable1 + ' GROUP BY articleNums ORDER BY Id asc '
				+ 'OPEN myCursor FETCH NEXT FROM myCursor INTO @id, @nums WHILE @@FETCH_STATUS = 0 BEGIN UPDATE ' + @tempTable1 + ' SET id=@id WHERE articleNums=@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 pkId 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 pkId 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 pkId 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 pkId 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

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

CREATE PROCEDURE [dbo].[GetContributeClassById]
@pkId int
AS
	/* SET NOCOUNT ON */
	SELECT * FROM contributeClass where pkId=@pkId
	
	RETURN
GO

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

CREATE PROCEDURE [dbo].[GetContributesByPage]
	@pageIndex int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@recordCount int output	-- 
AS
--转换成索引
	SELECT @pageIndex = @pageIndex-1	

	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT @count = Count(*) FROM( SELECT Count(n.pkId) as nums, u.pkId as userId FROM news n,users u,department d,'
			+ 'contribute c where c.userId=u.pkId and u.departmentId=d.pkId and c.state=1 and c.newsId = n.pkId '
			+ 'group by u.pkId) TempTable'
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum	

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

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

	SET @tempTable = 'TempB' + 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 @size = @recordCount%@pageSize	
	IF(@size=0)
		SET @size = @pageSize	

	DECLARE @insert varchar(1000)
	
	SET @insert = 'SELECT identity(int, 1, 1) as ID, u.pkId as userId, u.userName as userName, '
					+ 'u.fullName as fullName, u.email as email, u.tel as tel, d.name as department, '
					+ 'Count(n.pkId) as articleNums,(select Count(*) from contribute where userId=u.pkId and state<>5) AS totalNums into ' + @tempTable + ' from news n,users u,department d, '
					+ 'contribute c where c.userId=u.pkId and u.departmentId=d.pkId and c.state=1 and '
					+ 'c.newsId = n.pkId Group by u.pkId, u.userName, u.fullName, u.tel, u.email, d.name '
					+ ' ORDER BY articleNums DESC,  u.fullName ASC '
	EXEC(@insert)					
	--查询第一页
	IF (@pageIndex = 0) OR (@pageIndex <0)	
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM ' + @tempTable + ' ORDER by ID ASC, fullName ASC'	
	--查询最后一页
	ELSE IF ((@pageIndex = @pageCount -1) OR (@pageIndex > @pageCount-1))			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' * from ' + @tempTable + ' ORDER BY ID DESC, fullName DESC) TempTable ORDER BY ID ASC, fullName ASC'	
	--查询中间页后面的页
	ELSE IF @pageIndex > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @pageIndex ) + ' * from ' + @tempTable + ' ORDER BY ID DESC, fullName DESC) TempTable ORDER BY ID ASC, fullName ASC'
	--查询中间页前面的页
 	ELSE BEGIN							
		SET @pageIndex=@pageIndex+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @pageIndex ) + ' * from ' + @tempTable + ' ORDER BY ID ASC, fullName ASC) TempTable1 '
					+ ' ORDER BY ID DESC, fullName DESC) TempTable2 ORDER BY ID ASC, fullName ASC'
	END

	--执行查询
	EXEC(@sqlStr)
	SET @insert = 'DROP TABLE ' + @tempTable
	EXEC(@insert)
RETURN

GO

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

CREATE PROCEDURE [dbo].[GetContributesByPassed]
	@gradeId int=1,
	@gateId int,
	@pageIndex int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@recordCount int output,	-- 
	@passed int	-- 
AS	
	--转换成索引
	SELECT @pageIndex = @pageIndex-1	

	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT @count=Count(*) from news n,users u,department d,contribute c where  n.gate<>'
			+Convert(nvarchar,@gateId)+' and c.userId=u.pkId and u.departmentId=d.pkId '
			+'and n.gradeId<='+convert(nvarchar,@gradeId)+' and n.pkId=c.newsId and c.state=1'
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum	

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

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

	--判断分页大小，查询最后一页用
	SET @size = @recordCount%@pageSize	
	IF(@size=0)
		SET @size = @pageSize	
	
	--查询第一页
	IF (@pageIndex = 0) OR (@pageIndex <0)	
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + '  n.*,u.userName, u.fullName ,d.name "department", '
					+ 'b.name "BigClassName" from news n,users u,department d,bigClass b, contribute c '
					+ ' where  n.gate<>'+Convert(varchar,@gateId)+' and  c.userId=u.pkId and u.departmentId=d.pkId '
					+ 'and n.bigClassId=b.pkId and n.gradeId<='+convert(varchar,@gradeId)+' and n.pkId=c.newsId and c.state=1 '
					+ 'ORDER BY n.isTop DESC, n.pkId DESC'	
	--查询最后一页
	ELSE IF ((@pageIndex = @pageCount -1) OR (@pageIndex > @pageCount-1))			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' n.*,u.userName, u.fullName ,d.name "department", '
					+ 'b.name "BigClassName" from news n,users u,department d,bigClass b, contribute c '
					+ ' where  n.gate<>'+Convert(varchar,@gateId)+' and   c.userId=u.pkId and u.departmentId=d.pkId '
					+ 'and n.bigClassId=b.pkId and n.gradeId<='+convert(varchar,@gradeId)+' and n.pkId=c.newsId and c.state=1 '
					+ 'ORDER BY n.isTop DESC, n.pkId ASC) TempTable ORDER BY isTop DESC, pkId DESC'	
	--查询中间页后面的页
	ELSE IF @pageIndex > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @pageIndex ) + ' n.*,u.userName, u.fullName ,d.name "department", '
					+ 'b.name "BigClassName" from news n,users u,department d,bigClass b, contribute c '
					+ ' where  n.gate<>'+Convert(varchar,@gateId)+' and   c.userId=u.pkId and u.departmentId=d.pkId '
					+ 'and n.bigClassId=b.pkId and n.gradeId<='+convert(varchar,@gradeId)+' and n.pkId=c.newsId and c.state=1 '
					+ 'ORDER BY n.isTop DESC, n.pkId ASC) TempTable ORDER BY isTop DESC,pkId DESC'
	--查询中间页前面的页
 	ELSE BEGIN							
		SET @pageIndex=@pageIndex+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @pageIndex ) + '  n.*,u.userName, u.fullName ,d.name "department", '
					+ 'b.name "BigClassName" from news n,users u,department d,bigClass b, contribute c '
					+ ' where  n.gate<>'+Convert(varchar,@gateId)+' and   c.userId=u.pkId and u.departmentId=d.pkId '
					+ 'and n.bigClassId=b.pkId and n.gradeId<='+convert(varchar,@gradeId)+' and n.pkId=c.newsId and c.state=1 '
					+ 'ORDER BY n.isTop DESC, n.pkId DESC) TempTable1 '
					+ ' ORDER BY isTop ASC, pkId ASC) TempTable2 ORDER BY isTop DESC, pkId DESC'
	END
	print @sqlStr

	--执行查询
	EXEC(@sqlStr)

RETURN

GO

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

CREATE PROCEDURE [dbo].[GetContributesByUserId]
	@pageIndex int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeid int=1,
	@gateId int,
	@userId int,
	@recordCount int output	-- 
AS	
	--转换成索引
	SELECT @pageIndex = @pageIndex-1	

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

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

	--构造查询语句
	DECLARE @sqlStr varchar(1000),@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) + ' n.*,u.userName "userName",g.name "grade", d.name "department"  , d.name "department" FROM news n, users u, grade g, contribute c, department d '
					+ 'WHERE n.gradeId<=' + STR(@gradeId) + ' AND c.userId=' + STR(@userId) + ' AND n.gate<>' + STR(@gateId) 
					+ 'AND n.gradeId=g.pkId and c.userId=u.pkId AND n.pkId = c.newsId AND u.departmentId = d.pkId ORDER BY n.isTop DESC, n.pkId DESC'	
	--查询最后一页
	ELSE IF @pageIndex = @pageCount -1			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' n.*,u.userName "userName",g.name "grade", d.name "department" FROM news n, users u, grade g, contribute c, department d '
					+ 'WHERE n.gradeId<=' + STR(@gradeId) + ' AND c.userId=' + STR(@userId) + ' AND n.gate<>' + STR(@gateId) 
					+ 'AND n.gradeId=g.pkId and c.userId=u.pkId AND n.pkId = c.newsId AND u.departmentId = d.pkId ORDER BY n.isTop ASC, n.pkId ASC) TempTable ORDER BY isTop DESC, pkId DESC'	
	--查询中间页后面的页
	ELSE IF @pageIndex > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @pageIndex ) + ' n.*,u.userName "userName",g.name "grade", d.name "department" FROM news n, users u, grade g, contribute c, department d '
					+ 'WHERE n.gradeId<=' + STR(@gradeId) + ' AND c.userId=' + STR(@userId) + ' AND n.gate<>' + STR(@gateId) 
					+ 'AND n.gradeId=g.pkId and c.userId=u.pkId AND n.pkId = c.newsId AND u.departmentId = d.pkId ORDER BY n.isTop ASC, n.pkId ASC) TempTable ORDER BY isTop DESC, pkId DESC'
	--查询中间页前面的页
 	ELSE BEGIN							
		SET @pageIndex=@pageIndex+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @pageIndex ) + ' n.*,u.userName "userName",g.name "grade", d.name "department" FROM news n, users u, grade g, contribute c, department d '
					+ 'WHERE n.gradeId<=' + STR(@gradeId) + ' AND c.userId=' + STR(@userId) + ' AND n.gate<>' + STR(@gateId) 
					+ 'AND n.gradeId=g.pkId and c.userId=u.pkId AND n.pkId = c.newsId AND u.departmentId = d.pkId ORDER BY n.isTop DESC, n.pkId DESC) TempTable1 '
					+ ' ORDER BY isTop ASC, pkId ASC) TempTable2 ORDER BY isTop DESC, pkId DESC'
	END

	--执行查询
	EXEC(@sqlStr)

RETURN
GO


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

CREATE PROCEDURE [dbo].[GetMenuByContributeClassAndBigClassId] 

	(
		@bigClassId int
	)
	
AS
	/* SET NOCOUNT ON */
	select m.* from menu m, contributeClass c where 
	m.pkId = c.contributeClassId and c.bigClassId = @bigClassId 
	RETURN
GO

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

CREATE PROCEDURE [dbo].[GetMenuByContributeId]
@contributeId int,
@gradeId int=1
AS
	/* SET NOCOUNT ON */
	SELECT b.*,
	       b.pkId "bigClassId"
	FROM bigClass b,contributeClass c
	where b.pkId=c.bigClassId
	  and c.contributeClassId=@contributeId
	 
	order by c.pkId  desc
	RETURN 
GO

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

CREATE PROCEDURE [dbo].[InsertContribute]
(
	@intactTitle varchar(100),
	@Author varchar(100),
	@AuthorMail varchar(100),
	@CopyFrom varchar(100),
	@copyFromLink varchar(100),
	@about varchar(100),
	@pageType int,
	@autoPageCharNum int,
	@content text,
	@userId int,
	@updateTime datetime,
	@state int,
	@MenuId int,
	@bigClassId int,
	@pkId int,
	@updateIp varchar(100),
	@contributeId int,
	@auditRemark text,
	@newsId int
)
AS
	SET NOCOUNT OFF;	 BEGIN TRANSACTION
INSERT INTO contribute(intactTitle, Author, AuthorMail, CopyFrom, copyFromLink, about, pageType, autoPageCharNum, content, userId, updateTime, state, MenuId, bigClassId, updateIp, contributeId,auditRemark,newsId) VALUES (@intactTitle, @Author, @AuthorMail, @CopyFrom, @copyFromLink, @about, @pageType, @autoPageCharNum, @content, @userId, @updateTime, @state, @MenuId, @bigClassId, @updateIp,@contributeId,@auditRemark,@newsId)
  COMMIT TRANSACTION

GO

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

CREATE PROCEDURE [dbo].[InsertContributeClass]
@pkId int=null,
@contributeClassId	int,
@bigClassId int	
AS
	SET NOCOUNT ON 
	insert contributeClass(contributeClassId, bigClassId)
	       values(@contributeClassId, @bigClassId)
	RETURN
GO


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

CREATE PROCEDURE [dbo].[UpdateContribute]
(
	@intactTitle varchar(100),
	@Author varchar(100),
	@AuthorMail varchar(100),
	@CopyFrom varchar(100),
	@copyFromLink varchar(100),
	@about varchar(100),
	@pageType int,
	@autoPageCharNum int,
	@content text,
	@userId int,
	@updateTime datetime,
	@state int,
	@MenuId int,
	@bigClassId int,
	@pkId int,
	@updateIp  varchar(100),
	@contributeId int,
	@auditRemark text,
	@newsId int
)
AS
	SET NOCOUNT OFF;
UPDATE contribute SET intactTitle = @intactTitle, 
					  Author = @Author, 
					  AuthorMail = @AuthorMail, 
					  CopyFrom = @CopyFrom, 
					  copyFromLink = @copyFromLink, 
					  about = @about, 
					  pageType = @pageType, 
					  autoPageCharNum = @autoPageCharNum, 
					  content = @content, 
					  userId = @userId, 
					  updateTime = @updateTime, 
					  state = @state, 
					  MenuId = @MenuId, 
					  bigClassId = @bigClassId, 
					  contributeId = @contributeId,
					  auditRemark = @auditRemark,
					  newsId = @newsId,
					  updateIp=@updateIp
					  WHERE (pkId = @pkId)

GO

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

CREATE PROCEDURE [dbo].[GetTemplatePrivilegeSet]
AS
	SELECT 
		t.pkId as templateId, 
		t.name as templateName, 
		t.code as templateCode, 
		p.name as privilegeName, 
		p.nameRemark as nameRemark, 
		p.accreditValue as accreditValue, 
		p.accreditRemark as accreditRemark, 
		p.showType as showType, 
		p.itemEnglish as itemEnglish,
		p.itemChinese as itemChinese
FROM template AS t 
		INNER JOIN
     templatePrivilege AS tp ON t.pkId = tp.templateId 
		INNER JOIN
     privilege AS p ON tp.privilegeId = p.pkId
ORDER BY t.pkId, p.pkId

GO

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

CREATE PROCEDURE [dbo].[GetSiteContributeClass]

AS
	/* SET NOCOUNT ON */
	SELECT b.*,
	       b.pkId "bigClassId"
	FROM bigClass b,siteContributeClass c
	where b.pkId=c.bigClassId
	  
	order by c.pkId  desc
	RETURN

GO


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

CREATE PROCEDURE [dbo].[DeleteSiteContributeClass]
(
	@bigClassId int
)
AS
	SET NOCOUNT OFF;
	 BEGIN TRANSACTION
DELETE FROM siteContributeClass WHERE (bigClassId = @bigClassId)
  COMMIT TRANSACTION

GO

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

CREATE PROCEDURE [dbo].[GetSiteContributeClassById]
@pkId int
AS
	/* SET NOCOUNT ON */
	SELECT * FROM siteContributeClass where pkId=@pkId
	
	RETURN

GO


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

CREATE PROCEDURE [dbo].[InsertSiteContributeClass]
@pkId int=null,
@bigClassId int	
AS
	SET NOCOUNT ON 
	insert siteContributeClass( bigClassId
	            
	             
	             )
	       values( @bigClassId
	            
	             
	             )
	RETURN
GO

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

CREATE PROCEDURE [dbo].[InsertSiteContribute]
(
	@intactTitle varchar(100),
	@Author varchar(100),
	@AuthorMail varchar(100),
	@CopyFrom varchar(100),
	@copyFromLink varchar(100),
	@about varchar(100),
	@pageType int,
	@autoPageCharNum int,
	@content text,
	
	@updateTime datetime,
	@state int,
	
	@bigClassId int,
	@pkId int,
	@updateIp varchar(100),
	@userName varchar(100),
	@auditRemark text,
	@newsId int,
	@originalNewsId int
)
AS
	SET NOCOUNT OFF;	 BEGIN TRANSACTION
INSERT INTO siteContribute(intactTitle, Author, AuthorMail, CopyFrom, copyFromLink, about, pageType, autoPageCharNum, content, updateTime, state, bigClassId, updateIp, userName,auditRemark,newsId,originalNewsId) VALUES (@intactTitle, @Author, @AuthorMail, @CopyFrom, @copyFromLink, @about, @pageType, @autoPageCharNum, @content, @updateTime, @state, @bigClassId, @updateIp, @userName,@auditRemark,@newsId,@originalNewsId)
  COMMIT TRANSACTION

GO


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

CREATE PROCEDURE [dbo].[UpdateSiteContribute]
(
	@intactTitle varchar(100),
	@Author varchar(100),
	@AuthorMail varchar(100),
	@CopyFrom varchar(100),
	@copyFromLink varchar(100),
	@about varchar(100),
	@pageType int,
	@autoPageCharNum int,
	@content text,
	
	@updateTime datetime,
	@state int,
	
	@bigClassId int,
	@pkId int,
	@updateIp  varchar(100),
	@userName varchar(100),
	@auditRemark text,
	@newsId int,
	@originalNewsId int
)
AS
	SET NOCOUNT OFF;
UPDATE siteContribute SET intactTitle = @intactTitle, 
					  Author = @Author, 
					  AuthorMail = @AuthorMail, 
					  CopyFrom = @CopyFrom, 
					  copyFromLink = @copyFromLink, 
					  about = @about, 
					  pageType = @pageType, 
					  autoPageCharNum = @autoPageCharNum, 
					  content = @content, 
					 
					  updateTime = @updateTime, 
					  state = @state, 
					
					  bigClassId = @bigClassId, 
					  userName = @userName,
					  auditRemark = @auditRemark,
					  newsId = @newsId,
					  updateIp=@updateIp,
					  originalNewsId=@originalNewsId
					  WHERE (pkId = @pkId)

GO


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

CREATE PROCEDURE [dbo].[GetSiteContributeByPkId]
@pkId int =null
AS
	 SET NOCOUNT ON 
	select *
	from siteContribute
	where pkId=@pkId
	  
	RETURN
GO


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

CREATE PROCEDURE [dbo].[GetSiteContributeByState]
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize	
	@state int,
	@recordCount int output	
AS	
	declare @searchStr varchar(1000)
	if (@state = -1)
		set @searchStr = '1=1'
	else
		set @searchStr = 'state=' + CONVERT(nvarchar,@state)

	--转换成索引
	SELECT @currentPage = @currentPage-1	

	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT @count = Count(pkId) FROM siteContribute  WHERE '+@searchStr
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum

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

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

	--判断分页大小，查询最后一页用
	SET @size = @recordCount%@pageSize	
	IF(@size=0)
		SET @size = @pageSize	
	
	--查询第一页
	IF (@currentPage = 0) OR (@currentPage > @pageCount-1)	
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM siteContribute '
					+ 'WHERE '+@searchStr+' ORDER BY pkId DESC'	
	--查询最后一页
	ELSE IF @currentPage = @pageCount -1			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' * FROM siteContribute '
					+ 'WHERE '+@searchStr+' ORDER BY pkId ASC) TempTable ORDER BY pkId DESC'	
	--查询中间页后面的页
	ELSE IF @currentPage > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @currentPage ) + ' *  FROM siteContribute '
					+ 'WHERE '+@searchStr+' ORDER BY pkId ASC) TempTable ORDER BY pkId DESC'
	--查询中间页前面的页
 	ELSE BEGIN							
		SET @currentPage=@currentPage+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @currentPage ) + ' *  FROM siteContribute '
					+ 'WHERE '+@searchStr+' ORDER BY pkId DESC) TempTable1 '
					+ ' ORDER BY pkId ASC) TempTable2 ORDER BY pkId DESC'
	END

	--执行查询
	EXEC(@sqlStr)

RETURN

GO


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

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

GO


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

CREATE PROCEDURE [dbo].[GetLatestGameByPassed]
	@pageIndex int,
	@pageSize int=5,
	@gradeid int=1,
	@passed int=1,
	@gateId int,	
	@recordCount int ,
	@pageCount int 
AS	
	--转换成索引
	SELECT @pageIndex=@pageIndex-1	

	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT @count = Count(*) FROM News WHERE bigclassId in (select bigclassId from bigClass where templateId in (49,50,53)) and gate<>'+Convert(varchar,@gateId)+' and gradeId<='+convert(varchar,@gradeId)+' and passed='+convert(varchar,@passed)+' and (isDuplicated=0 or isDuplicated is null) and  isImageNews=0'
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum

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

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

	--判断分页大小，查询最后一页用
	SET @size = @recordCount%@pageSize	
	IF(@size=0)
		SET @size = @pageSize	
	
	--查询第一页
	IF (@pageIndex = 0) OR (@pageIndex > @pageCount-1)	
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + '  n.*,u.userName,d.name "department",b.name "BigClassName" from news n,users u,department d,bigClass b where b.templateId in (49,53) and n.gate<>'+Convert(varchar,@gateId)+' and (n.isDuplicated=0 or n.isDuplicated is null) and n.usersId=u.pkId and n.departmentId=d.pkId and bigClassId=b.pkId and n.gradeId<='+convert(varchar,@gradeId)+' and n.passed='+convert(varchar,@passed)+'  ORDER BY n.updateTime DESC, n.pkId DESC'	
	--查询最后一页
	ELSE IF @pageIndex = @pageCount -1			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' +STR(@size)  + '  n.*,u.userName,d.name "department",b.name "BigClassName" from news n,users u,department d,bigClass b where b.templateId in (49,53) and  n.gate<>'+Convert(varchar,@gateId)+' and (n.isDuplicated=0 or n.isDuplicated is null) and n.usersId=u.pkId and n.departmentId=d.pkId and bigClassId=b.pkId and n.gradeId<='+convert(varchar,@gradeId)+' and n.passed='+convert(varchar,@passed)+'  ORDER BY n.updateTime ASC, n.pkId ASC) TempTable ORDER BY updateTime DESC, pkId DESC'	
	--查询中间页后面的页
	ELSE IF @pageIndex > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP '  + STR(@recordCount - @pageSize * @pageIndex ) + '  n.*,u.userName,d.name "department",b.name "BigClassName" from news n,users u,department d,bigClass b where b.templateId in (49,53) and n.gate<>'+Convert(varchar,@gateId)+' and (n.isDuplicated=0 or n.isDuplicated is null) and n.usersId=u.pkId and n.departmentId=d.pkId and bigClassId=b.pkId and n.gradeId<='+convert(varchar,@gradeId)+' and n.passed='+convert(varchar,@passed)+'  ORDER BY n.updateTime ASC, n.pkId ASC) TempTable ORDER BY updateTime DESC, pkId DESC'
	--查询中间页前面的页
 	ELSE BEGIN							
		SET @pageIndex=@pageIndex+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' + STR(@pageSize * @pageIndex ) + '  n.*,u.userName,d.name "department",b.name "BigClassName" from news n,users u,department d,bigClass b where b.templateId in (49,53) and n.gate<>'+Convert(varchar,@gateId)+' and (n.isDuplicated=0 or n.isDuplicated is null) and n.usersId=u.pkId and n.departmentId=d.pkId and bigClassId=b.pkId and n.gradeId<='+convert(varchar,@gradeId)+' and n.passed='+convert(varchar,@passed)+'  ORDER BY n.updateTime DESC, n.pkId DESC) TempTable1 ORDER BY updateTime ASC, pkId ASC) TempTable2 ORDER BY updateTime DESC, pkId DESC'
	END

	--查询具有图片的两个最新游戏
		set @sqlStr = @sqlStr + ' SELECT TOP 2  n.*,u.userName,d.name "department",b.name "BigClassName" from news n,users u,department d,bigClass b where b.templateId = 50 and n.gate<>'+Convert(varchar,@gateId)+' and (n.isDuplicated=0 or n.isDuplicated is null) and n.usersId=u.pkId and n.departmentId=d.pkId and bigClassId=b.pkId and n.gradeId<='+convert(varchar,@gradeId)+' and n.passed='+convert(varchar,@passed)+'  ORDER BY n.updateTime DESC, n.pkId DESC'
	
	--执行查询
	EXEC(@sqlStr)

GO

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

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

	declare @vSQL varchar(1000)
	select @vSQL = 
		'select top ' + convert(varchar, @pageSize) + ' n.*,u.userName,d.name as department 
		from news n,users u,department d 
		where bigclassId in
			(
				select pkId from bigclass where templateId in (49,50,53)
			)
			and n.gate<> '+Convert(varchar,@gateId)+' 
			and (n.isDuplicated=0 or n.isDuplicated is null) 
			and n.usersId=u.pkId  
			and n.departmentId=d.pkId 
			and n.gradeId<='+convert(varchar,@gradeId)+' 
			and n.passed=1  
		order by n.click desc'
	exec (@vSQL)
return

GO

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

CREATE PROCEDURE [dbo].[InsertTodayInHistory]
(
	@specialId int,
	@gradeId int,
	@gate int,
	@departmentId int,
	@listTitle varchar(1000),
	@intactTitle varchar(100),
	@subheading varchar(100),
	@isRecommand int,
	@isTop int,
	@useListTitle int,
	@showReviewLink int,
	@Author varchar(100),
	@AuthorMail varchar(100),
	@CopyFrom varchar(100),
	@copyFromLink varchar(100),
	@about varchar(100),
	@pageType int,
	@autoPageCharNum int,
	@readPoint int,
	@itemType int,
	@content text,
	@descript varchar(1000),
	@usersId int,
	@editor varchar(100),
	@click int,
	@updateTime datetime,
	@passed int,
	@isReview int,
	@isImageNews int,
	@imageName varchar(100),
	@MenuId int,
	@bigClassId int,
	@width int,
	@height int,
	@pkId int output,
	@updateIp varchar(100),
	@useSubHeading int,
	@isDuplicated int=0,
	@duplicateFrom varchar(100),
	@duplicatedPkId int,
	@happenType int,
	@happenDate datetime
)
AS
	SET NOCOUNT OFF;	 BEGIN TRANSACTION
IF (@isDuplicated is null)
	set @isDuplicated = 0
IF (@duplicatedPkId is null)
	set @duplicatedPkId = 0
INSERT INTO news(specialId, gradeId, gate, departmentId, listTitle, intactTitle, subheading, isRecommand, isTop, useListTitle, showReviewLink, Author, AuthorMail, CopyFrom, copyFromLink, about, pageType, autoPageCharNum, readPoint, itemType, content, descript, usersId, editor, click, updateTime, passed, isReview, isImageNews, imageName, MenuId, bigClassId, width, height,updateIp,useSubHeading, isDuplicated, duplicateFrom, duplicatedPkId) VALUES (@specialId, @gradeId, @gate, @departmentId, @listTitle, @intactTitle, @subheading, @isRecommand, @isTop, @useListTitle, @showReviewLink, @Author, @AuthorMail, @CopyFrom, @copyFromLink, @about, @pageType, @autoPageCharNum, @readPoint, @itemType, @content, @descript, @usersId, @editor, @click, @updateTime, @passed, @isReview, @isImageNews, @imageName, @MenuId, @bigClassId, @width, @height,@updateIp,@useSubHeading, @isDuplicated, @duplicateFrom, @duplicatedPkId)
SET @pkId = @@IDENTITY
insert into todayInHistory(newsId,happenType,happenDate) values(@pkId,@happenType,@happenDate)
  COMMIT TRANSACTION
  
GO

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

CREATE PROCEDURE [dbo].[UpdateTodayInHistory]
(
	@specialId int,
	@gradeId int,
	@gate int,
	@departmentId int,
	@listTitle varchar(1000),
	@intactTitle varchar(100),
	@subheading varchar(100),
	@isRecommand int,
	@isTop int,
	@useListTitle int,
	@showReviewLink int,
	@Author varchar(100),
	@AuthorMail varchar(100),
	@CopyFrom varchar(100),
	@copyFromLink varchar(100),
	@about varchar(100),
	@pageType int,
	@autoPageCharNum int,
	@readPoint int,
	@itemType int,
	@content text,
	@descript varchar(1000),
	@usersId int,
	@editor varchar(100),
	@click int,
	@updateTime datetime,
	@passed int,
	@isReview int,
	@isImageNews int,
	@imageName varchar(100),
	@MenuId int,
	@bigClassId int,
	@width int,
	@height int,
	@pkId int,
	@updateIp  varchar(100),
	@useSubHeading int,
	@isDuplicated int=0,
	@duplicateFrom varchar(100),
	@duplicatedPkId	int,
	@happenType int,
	@happenDate datetime
)
AS
	SET NOCOUNT OFF;
UPDATE news SET useSubHeading=@useSubHeading,updateIp=@updateIp,specialId = @specialId, gradeId = @gradeId, gate = @gate, departmentId = @departmentId, listTitle = @listTitle, intactTitle = @intactTitle, subheading = @subheading, isRecommand = @isRecommand, isTop = @isTop, useListTitle = @useListTitle, showReviewLink = @showReviewLink, Author = @Author, AuthorMail = @AuthorMail, CopyFrom = @CopyFrom, copyFromLink = @copyFromLink, about = @about, pageType = @pageType, autoPageCharNum = @autoPageCharNum, readPoint = @readPoint, itemType = @itemType, content = @content, descript = @descript, usersId = @usersId, editor = @editor, click = @click, updateTime = @updateTime, passed = @passed, isReview = @isReview, isImageNews = @isImageNews, imageName = @imageName, MenuId = @MenuId, bigClassId = @bigClassId, width = @width, height = @height, isDuplicated = @isDuplicated, duplicateFrom = @duplicateFrom, duplicatedPkId = @duplicatedPkId WHERE (pkId = @pkId)
UPDATE news SET passed = @passed WHERE duplicatedPkId=@pkId AND duplicateFrom='news'			-- 更新news时，同时更新news抄送的信息
UPDATE board SET passed = @passed WHERE duplicatedPkId=@pkId AND duplicateFrom = 'news'		-- 更新news时，同时更新board抄送的信息
update todayInHistory set happenType = @happenType,happenDate = @happenDate where newsId = @pkId

GO

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

CREATE PROCEDURE [dbo].[GetTodayInHistoryByBigClassIdAndisImageNewsAndPassed]
@currentPage int=1,
@pageSize int=5,
@gradeid int=1,
@passed int=1,
@gateId int,
@happenDate varchar(100)
AS
	 SET NOCOUNT ON 	
	declare @vSQL varchar(1000)
select @vSQL = 'select top ' + convert(varchar, @pageSize) + ' n.*,t.happenType,t.happenDate,u.userName,d.name "department" from news n,todayInHistory t,users u,department d where  n.gate<>'+Convert(varchar,@gateId)+' and   n.usersId=u.pkId and n.pkId = t.newsId and month(t.happenDate) = '+ substring(@happenDate,1,2)+' and day(t.happenDate) = '+ substring(@happenDate,3,2)+' and n.departmentId=d.pkId and n.bigClassId in (select pkId from bigClass where templateId = 54) and isImageNews = 1 and n.gradeId<='+convert(varchar,@gradeId)+' and n.passed='+convert(varchar,@passed)+'  and n.pkId <> all (select top '+Convert(varchar,((@currentPage-1)*@pageSize))+' pkId from news where  gate<>'+Convert(varchar,@gateId)+' and   bigClassId in (select pkId from bigClass where templateId = 54) and isImageNews = 1 and gradeId<='+convert(varchar,@gradeId)+' and passed='+convert(varchar,@passed)+' order by istop desc,pkId desc)  order by t.happenType asc,n.istop desc,n.pkId desc'
Execute (@vSQL)
	RETURN 
GO






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

CREATE PROCEDURE [dbo].[GetTodayInHistoryByBigClassIdAndPassed]
@bigClassId int=null,
@currentPage int=1,
@pageSize int=5,
@gradeid int=1,
@passed int=1,
@gateId int,
@happenDate varchar(100)
AS
	 SET NOCOUNT ON 	
	declare @vSQL varchar(1000)
select @vSQL = 'select top ' + convert(varchar, @pageSize) + ' n.*,t.happenType,t.happenDate,u.userName,d.name "department" from news n,todayInHistory t,users u,department d where  n.gate<>'+Convert(varchar,@gateId)+' and   n.usersId=u.pkId and n.pkId = t.newsId and month(t.happenDate) = '+ substring(@happenDate,1,2)+' and day(t.happenDate) = '+ substring(@happenDate,3,2)+' and n.departmentId=d.pkId and bigClassId='+Convert(varchar,@bigClassId)+' and n.gradeId<='+convert(varchar,@gradeId)+' and n.passed='+convert(varchar,@passed)+'  and n.pkId <> all (select top '+Convert(varchar,((@currentPage-1)*@pageSize))+' pkId from news where  gate<>'+Convert(varchar,@gateId)+' and   bigClassId='+Convert(varchar,@bigClassId)+' and gradeId<='+convert(varchar,@gradeId)+' and passed='+convert(varchar,@passed)+' order by istop desc,pkId desc)  order by istop desc,n.pkId desc'
Execute (@vSQL)
	RETURN 

GO


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

CREATE PROCEDURE [dbo].[GetTodayInHistoryByPkId]
@pkId int =null,
@gradeId int=1,
@gateId int
AS
	 SET NOCOUNT ON 
	select n.*,t.happenType,t.happenDate,u.userName,d.name "department" ,g.name "grade" 
	from news n, todayInHistory t, users u, department d, grade g
	where @pkId=n.pkId
	  and n.gradeId<=@gradeId
	  and n.gate<>@gateId and n.pkId = t.newsId and n.departmentId=d.pkId and n.gradeId=g.pkId and n.usersId=u.pkId 
	RETURN 
GO






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

CREATE PROCEDURE [dbo].[GetTodayInHistoryShortByPassed]
@currentPage int=1,
@pageSize int=5,
@gradeid int=1,
@passed int=1,
@gateId int,
@happenDate varchar(100)
AS
	 SET NOCOUNT ON 	
	declare @vSQL varchar(1000)
select @vSQL = 'select top ' + convert(varchar, @pageSize) + ' n.*,t.happenType,t.happenDate,u.userName,d.name "department" from news n,todayInHistory t,users u,department d where  n.gate<>'+Convert(varchar,@gateId)+' and   n.usersId=u.pkId and n.pkId = t.newsId and month(t.happenDate) = '+ substring(@happenDate,1,2)+' and day(t.happenDate) = '+ substring(@happenDate,3,2)+' and n.departmentId=d.pkId and n.bigClassId in (select pkId from bigClass where templateId = 54) and n.gradeId<='+convert(varchar,@gradeId)+' and n.passed='+convert(varchar,@passed)+'  and n.pkId <> all (select top '+Convert(varchar,((@currentPage-1)*@pageSize))+' pkId from news where  gate<>'+Convert(varchar,@gateId)+' and   bigClassId in (select pkId from bigClass where templateId = 54) and gradeId<='+convert(varchar,@gradeId)+' and passed='+convert(varchar,@passed)+' order by istop desc,pkId desc)  order by t.happenType asc,n.istop desc,n.pkId desc'
Execute (@vSQL)
	RETURN 
GO

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

create PROCEDURE [dbo].[GetTodayInHistoryByBigClassIdCount]
@bigClassId int=null,
@gradeId int=1,
@gateId int=-1,
@happenDate varchar(100),
@count int output,
@isForeGround int
AS
	SET NOCOUNT ON
	declare @num int
	declare @vSQL varchar(1000)
	IF (@isForeGround=0)
	BEGIN
		select @vSQL=
			'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news n,todayInHistory t where n.gate<> '+convert(varchar,@gateId)+' and n.bigclassId= '+convert(varchar,@bigclassId)+' and n.gradeId <= '+convert(varchar,@gradeId)+' and n.pkId = t.newsId and month(t.happenDate) = '+ substring(@happenDate,1,2)+' and day(t.happenDate) = '+ substring(@happenDate,3,2)+' return'			--魏兆佳20070801
				
	END 
	ELSE IF (@isForeGround=1)
	BEGIN
		select @vSQL=
			 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news n,todayInHistory t where n.gate<> '+convert(varchar,@gateId)+' and n.bigclassId= '+convert(varchar,@bigclassId)+' and n.gradeId <= '+convert(varchar,@gradeId)+' and n.pkId = t.newsId and month(t.happenDate) = '+ substring(@happenDate,1,2)+' and day(t.happenDate) = '+ substring(@happenDate,3,2)+' and passed = 1 return'		--魏兆佳20070801
				
	END	
	Execute (@vSQL)
	execute GetCount @count=@num output
	select @count=@num
RETURN

GO

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

CREATE PROCEDURE [dbo].[DeleteMenu]
(
@pkId int
)
as
 BEGIN TRANSACTION
	declare @mpkId int
	declare @count int
	declare @npkId int
	set @npkId=@pkId
	set @mpkId=-1
	select  @count=Count(*) from menu where  @pkId=parentId
	while(@count<>0 or @mpkId<>@pkId)
	begin
		SELECT top 1 @mpkId=pkId
		FROM menu
		WHERE @npkId=parentId
		select  @count=Count(*) from menu where  @pkId=parentId
		if(@count=0)
		begin
			break;
		end
		select @count=Count(*) from menu where  @mpkId=parentId
		set @npkId=@mpkId
		if(@count>0)
		begin
			continue
		end
		else
		begin
			delete from contributeClass where contributeClassId= @mpkId	-- edit contributeClass
			delete from moduleAssign where @mpkId=menuId
			delete from artDesign where @mpkId=menuId
			delete from menu  where @mpkId=pkId  
			set @npkId=@pkId  
		end
	 END	 
	 delete from contributeClass where contributeClassId= @pkId	-- edit contributeClass
	 delete from moduleAssign where @pkId=menuId
	 delete from artDesign where @pkId=menuId
	 delete from menu  where @pkId=pkId   
 COMMIT TRANSACTION 
 return

GO

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

CREATE PROCEDURE [dbo].[InsertModuleAssign]
@pkId int=null,
@usersId  int,	
@moduleId int,
@privilegeSet varchar(1000)
AS
	SET NOCOUNT ON
	insert ModuleAssign(usersId,
	                   menuId,
					   privilegeSet)
	             values(@usersId,
	                   @moduleId,
	                   @privilegeSet)
	RETURN 

GO

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

CREATE PROCEDURE [dbo].[InsertUsers]
(
	@userName varchar(100),
	@fullName varchar(100),
	@password varchar(100),
	@question varchar(100),
	@answer varchar(100),
	@sex varchar(10),
	@born datetime,
	@QQ varchar(100),
	@email varchar(100),
	@intro varchar(100),
	@loginIp varchar(100),
	@loginNum int,
	@loginTime datetime,
	@logIp varchar(100),
	@logTime datetime,
	@tel varchar(100),
	@photo varchar(100),
	@examine int,
	@gradeId int,
	@departmentId int,
	@company varchar(100),
	@appear int,
	@networkTest int,
	@Auditing int,
	@manageAll int,
	@usersGroupId int,
	@readInfoType int,
	@topRight int,
	@recommandRight int,
	@pkId int
)
AS
SET NOCOUNT OFF;
	
	 BEGIN TRANSACTION
	declare @count int
    declare @point int
    declare @days int
	
	select @usersGroupId=1
	select @gradeId=1
	select @topRight=0
	select @recommandRight=0
	select @count=Count(*) from users
	where userName=@userName
if @count!=0
 return 
	select @readInfoType= readInfoType,@examine=examine,@point=point,@days=days from RegUserInfo
		
INSERT INTO Users(userName, fullName, password, question, answer, sex, born, QQ, email, intro, 
loginIp, loginNum, loginTime, logIp, logTime, tel, photo, examine, gradeId, departmentId,
company, appear, networkTest, Auditing, manageAll, usersGroupId, readInfoType, topRight,
recommandRight) VALUES (@userName, @fullName, @password, @question, @answer, @sex, @born, @QQ,
@email, @intro, @loginIp, @loginNum, @loginTime, @logIp, @logTime, @tel, @photo, @examine, 
@gradeId, @departmentId, @company, @appear, @networkTest, @Auditing, @manageAll,
@usersGroupId, @readInfoType, @topRight, @recommandRight)
select @pkId=@@IDENTITY
insert into Riches(usersId,addPoint,subPoint,updateTime,addDays,subDays,remark) values
(@pkId,@point,0,GETDATE(),@days,0,'')
INSERT INTO moduleAssign 
   (usersId,menuId,privilegeSet)
SELECT usersId=@pkId,menuId,privilegeSet
FROM moduleAssign 
WHERE usersId=-1
 COMMIT TRANSACTION

GO


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

CREATE PROCEDURE [dbo].[UpdateSiteBasicProperty] 
(
	@pkId int,
	@grade int,
	@defaultPassed int,
	@review int,
	@showIp int,
	@reviewCheck int,
	@reviewFiltrate varchar(100),
	@reviewAlternation int,
	@showClick int,
	@navGate int,
	@navPage varchar(100),
	@pagesize int,
	@shareNewsUrl  varchar(100),
	@shareNewsLayoutTemplateID int,
	@outputCacheTime int,
	@newsOpenMode int,
	@IsAllowContribute int
)
AS
	SET NOCOUNT OFF;
UPDATE siteBasicProperty SET IsAllowContribute = @IsAllowContribute , newsOpenMode=@newsOpenMode,outputCacheTime=@outputCacheTime,shareNewsLayoutTemplateID=@shareNewsLayoutTemplateID,shareNewsUrl=@shareNewsUrl,pagesize=@pagesize,navPage=@navPage,grade = @grade, defaultPassed = @defaultPassed, review = @review, showIp = @showIp, reviewCheck = @reviewCheck, reviewFiltrate = @reviewFiltrate, reviewAlternation = @reviewAlternation, showClick = @showClick, navGate = @navGate


GO

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

CREATE PROCEDURE [dbo].[InsertSiteBasicProperty]
(
	@pkId int,
	@grade int,
	@defaultPassed int,
	@review int,
	@showIp int,
	@reviewCheck int,
	@reviewFiltrate varchar(100),
	@reviewAlternation int,
	@showClick int,
	@navGate int,
	@shareNewsUrl  varchar(100),
	@shareNewsLayoutTemplateID int,
	@newsOpenMode int,
	@IsAllowContribute int
)
AS
	SET NOCOUNT OFF;
INSERT INTO siteBasicProperty(pkId, grade, defaultPassed, review, showIp, reviewCheck, reviewFiltrate, reviewAlternation, showClick, navGate,shareNewsUrl,shareNewsLayoutTemplateID,newsOpenMode, IsAllowContribute) VALUES (@pkId, @grade, @defaultPassed, @review, @showIp, @reviewCheck, @reviewFiltrate, @reviewAlternation, @showClick, @navGate,@shareNewsUrl,@shareNewsLayoutTemplateID,@newsOpenMode, @IsAllowContribute)

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


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

CREATE PROCEDURE [dbo].[GetAuditingCount]
	@gradeId int,
	@userId int,
	@gateId int,
	@manageAll varchar(8000),
	@newsCount int output,
	@reviewsCount int output,
	@messageBoardCount int output
	
AS
	
	--初始化查询条件(不带where)
	DECLARE @str nvarchar(4000)
	DECLARE @str1 nvarchar(4000)
	DECLARE @str3 nvarchar(4000)
	--新闻
	SET @str = ' 1=1 '
	SET @str = @str + '  and n.isDuplicated<>1 and n.bigClassId in (select b.pkId from bigClass b, menu m, moduleAssign a where m.bigClassId=b.pkId and m.pkId=a.menuId and a.usersId='+STR(@userId)+')'
	SET @str = @str + ' and ' + @manageAll
	SET @str = @str + ' and n.passed=0 and n.gradeId<='+STR(@gradeId)
	SET @str = @str + ' and n.gate<>'+STR(@gateId)
	SET @str = @str + ' and n.usersId=u.pkId'
	--评论
	SET @str1 = ' 1=1 '
	SET @str1 = @str1 + ' and n.bigClassId in (select b.pkId from bigClass b, menu m, moduleAssign a where m.bigClassId=b.pkId and m.pkId=a.menuId and a.usersId='+STR(@userId)+')'
	SET @str1 = @str1 + ' and ' + @manageAll
	SET @str1 = @str1 + ' and r.passed=0 and n.gradeId<='+STR(@gradeId)
	SET @str1 = @str1 + ' and n.gate<>'+STR(@gateId)
	SET @str1 = @str1 + ' and r.newsId=n.pkId and n.usersId=u.pkId '
	--留言本
	SET @str3 = ' 1=1 '
	SET @str3 = @str3 + ' and bigClassId in (select b.pkId from bigClass b, menu m, moduleAssign a where m.bigClassId=b.pkId and m.pkId=a.menuId and a.usersId='+STR(@userId)+')'
	--IF (@manageAll<>1)
		--SET @str3 = @str3+ ' and n.usersId='+STR(@userId)
	SET @str3 = @str3 + ' and checked=0 and gradeId<='+STR(@gradeId)
	SET @str3 = @str3 + ' and gate<>'+STR(@gateId)

	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int
	DECLARE @vSQL nvarchar(4000)
	SET @vSQL = N'SELECT @count1 = Count(n.pkId) from news n , users u where ' + Convert(nvarchar(4000),@str)
	EXEC sp_ExecuteSQL @vSQL, N'@count1 int output', @tempNum output
	SELECT @newsCount = @tempNum

	SET @vSQL = N'SELECT @count2 = Count(*) from (select Count(n.pkId) as recordCount from review r, news n, users u where ' + Convert(nvarchar(4000),@str1) + ' group by r.newsid) TempTable'
	EXEC sp_ExecuteSQL @vSQL, N'@count2 int output', @tempNum output
	SELECT @reviewsCount = @tempNum

	SET @vSQL = N'SELECT @count3 = Count(pkId) from messageBoard where ' + Convert(nvarchar(4000),@str3)
	EXEC sp_ExecuteSQL @vSQL, N'@count3 int output', @tempNum output
	SELECT @messageBoardCount = @tempNum	

RETURN

GO


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

CREATE PROCEDURE [dbo].[GetAuditingMessageBoardByUserId]
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeId int,
	@userId int,
	@gateId int,
	@manageAll varchar(1000),
	@recordCount int output
	
AS	
	--转换成索引
	SELECT @currentPage = @currentPage - 1
	
	--初始化查询条件(不带where)
	DECLARE @str nvarchar(1000)
	SET @str = ' 1=1 '
	SET @str = @str + ' and m.bigClassId in (select b.pkId from bigClass b, menu m, moduleAssign a where m.bigClassId=b.pkId and m.pkId=a.menuId and usersId='+STR(@userId)+')'
	
	SET @str = @str + ' and m.checked=0 and m.gradeId<='+STR(@gradeId)
	SET @str = @str + ' and m.gate<>'+STR(@gateId)
	SET @str = @str + ' and m.gradeId=g.pkId '

	--设置查询表名称
	DECLARE @tableName nvarchar(50)
	SET @tableName = ' messageBoard m, grade g '

	--设置查询排序（不带order by）
	DECLARE @orderTypeA nvarchar(100)
	DECLARE @orderTypeD nvarchar(100)
	SET @orderTypeA = 'isTop ASC, msgId ASC'
	SET @orderTypeD = 'isTop DESC, msgId DESC'
	
	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT @count = Count(m.pkId) from '+Convert(nvarchar(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) + ' m.*,g.name "grade", m.pkId as msgId 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) + ' m.*,g.name "grade", m.pkId as msgId  FROM '+Convert(nvarchar(50),@tableName)+' where '+Convert(nvarchar(1000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable ORDER BY '+Convert(nvarchar(100),@orderTypeD)	
	--查询中间页后面的页
	ELSE IF @currentPage > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @currentPage ) + ' m.*,g.name "grade", m.pkId as msgId FROM '+Convert(nvarchar(50),@tableName)+' where '+Convert(nvarchar(1000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable ORDER BY '+Convert(nvarchar(100),@orderTypeD)
	--查询中间页前面的页
 	ELSE BEGIN							
		SET @currentPage=@currentPage+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @currentPage ) + ' m.*,g.name "grade", m.pkId as msgId FROM '+Convert(nvarchar(50),@tableName)+' where '+Convert(nvarchar(1000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeD)+') TempTable1 '
					+ ' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable2 ORDER BY '+Convert(nvarchar(100),@orderTypeD)
	END

	--执行查询
	EXEC(@sqlStr)

RETURN

GO

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

CREATE   PROCEDURE [dbo].[GetAuditingNewsByUserId]
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeId int,
	@userId int,
	@gateId int,
	@manageAll varchar(8000),
	@recordCount int output
	
AS	
	--转换成索引
	SELECT @currentPage = @currentPage - 1
	
	--初始化查询条件(不带where)
	DECLARE @str nvarchar(4000)
	SET @str = ' 1=1 '
	SET @str = @str + ' and (n.isDuplicated=0 or n.isDuplicated is null) and b.pkId = n.bigClassId and b.templateId = t.pkId and n.bigClassId in (select b.pkId from bigClass b, menu m, moduleAssign a where m.bigClassId=b.pkId and m.pkId=a.menuId and usersId='+STR(@userId)+')'
	SET @str = @str + ' and ' + @manageAll
	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.MenuId=m.pkId and n.usersId=u.pkId and n.gradeId=g.pkId and n.departmentId=d.pkId '

	--设置查询表名称
	DECLARE @tableName nvarchar(100)
	SET @tableName = ' news n,users u,department d,grade g, menu m, template t, bigClass b '

	--设置查询排序（不带order by）
	DECLARE @orderTypeA nvarchar(100)
	DECLARE @orderTypeD nvarchar(100)
	SET @orderTypeA = 'menuName ASC, newsId ASC'
	SET @orderTypeD = 'menuName 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",m.name "menuName", n.pkId as newsId , t.code FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(4000),@str)+' and (n.isDuplicated is null or n.isDuplicated =0 ) 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",m.name "menuName", 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",m.name "menuName", 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",m.name "menuName", n.pkId as newsId, t.code FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(4000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeD)+') TempTable1 '
					+ ' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable2 ORDER BY '+Convert(nvarchar(100),@orderTypeD)
	END

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



GO

if exists (select * from dbo.sysobjects where id = object_id(N'[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
	
	--初始化查询条件(不带where)
	DECLARE @str nvarchar(4000)
	SET @str = ' 1=1 '
	SET @str = @str + ' and n.bigClassId in (select b.pkId from bigClass b, menu m, moduleAssign a where m.bigClassId=b.pkId and m.pkId=a.menuId and usersId='+STR(@userId)+')'
	SET @str = @str + ' and ' + @manageAll
	SET @str = @str + ' and n.gradeId<='+STR(@gradeId)
	SET @str = @str + ' and n.gate<>'+STR(@gateId)
	SET @str = @str + ' and n.pkId in (select distinct newsId from review where passed=0) and n.MenuId=m.pkId and n.usersId=u.pkId and n.gradeId=g.pkId and n.departmentId=d.pkId '

	--设置查询表名称
	DECLARE @tableName nvarchar(100)
	SET @tableName = ' news n,users u,department d,grade g, menu m '

	--设置查询排序（不带order by）
	DECLARE @orderTypeA nvarchar(100)
	DECLARE @orderTypeD nvarchar(100)
	SET @orderTypeA = 'isTop ASC, newsId ASC'
	SET @orderTypeD = 'isTop DESC, newsId DESC'
	
	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(4000)
	SET @vSQL = N'SELECT @count = Count(*) from (select Count(n.pkId) as recordCount from'+Convert(nvarchar(100),@tableName)+' where ' + Convert(nvarchar(4000),@str) + ' group by n.pkId) TempTable'
	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) + ' n.*,u.userName, d.name "department" ,g.name "grade",m.name "menuName", n.pkId as newsId  FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(4000),@str) + ' 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",m.name "menuName", n.pkId as newsId 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",m.name "menuName", n.pkId as newsId 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",m.name "menuName", n.pkId as newsId FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(4000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeD)+') TempTable1 '
					+ ' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable2 ORDER BY '+Convert(nvarchar(100),@orderTypeD)
	END

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

GO


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

CREATE PROCEDURE [dbo].[GetMenuAssignAndParentId]
@parentId int,
@gradeId int=1,
@userId int
AS
	/* SET NOCOUNT ON */
	SELECT m.*,
	       g.name grade,
	       o.name mode
	FROM menu m,grade g,mode o,moduleAssign a
	where (m.gradeId<=@gradeId
	  and m.gradeId=g.pkId
	  and m.modeId=o.pkId
	  and m.parentId=@parentId
	  and a.usersId=@userId
	  and m.pkId=a.menuId)
	  or (a.menuId=0
	  and m.gradeId=g.pkId
	  and m.modeId=o.pkId
	  and a.usersId=@userId
	  and m.pkId=a.menuId)
	  or (a.menuId=-1
	  and m.gradeId=g.pkId
	  and m.modeId=o.pkId
	  and a.usersId=@userId
	  and m.pkId=a.menuId)
	order by ordering desc, m.pkId
	RETURN

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
AS
	SET NOCOUNT ON
	declare @num int
	declare @vSQL varchar(1000)
	IF (@isForeGround=0)
	BEGIN
		select @vSQL=
			case @table
				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)+' 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 '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'	--魏兆佳20070727
				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'		--魏兆佳20070727
				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'			--魏兆佳20070727
				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'			--魏兆佳20070801
				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'--李聪杰20060406
				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'--李聪杰20060406					
				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 '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 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 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 return'	--魏兆佳20070727
				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 return'		--魏兆佳20070727
				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 return'		--魏兆佳20070727
				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 return'		--魏兆佳20070801
				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 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 return'--李聪杰20060406
				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 return'--李聪杰20060406					
				ELSE  'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from '+convert(varchar,@table)+' where gate<> '+convert(varchar,@gateId)+' and bigclassId='+convert(varchar,@bigclassId)+' and  gradeId<='+convert(varchar,@gradeId)+' and passed=1 return'
			end
	END	
	Execute (@vSQL)
	execute GetCount @count=@num output
	select @count=@num
RETURN

GO

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

CREATE PROCEDURE [dbo].[DeleteNews]
(
	@pkId int
)
AS
	SET NOCOUNT OFF;
	 BEGIN TRANSACTION
	DECLARE @duplicatedId int

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

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

-- 从新闻、公告中删除抄送新闻
DELETE FROM news WHERE (duplicatedPkId=@pkId)
DELETE FROM board WHERE (duplicatedPkId=@pkId) and duplicateFrom='news'

DELETE FROM review where (newsId=@pkId)
DELETE FROM news WHERE (pkId = @pkId)

--删除历史上的今天记录
if exists(select pkId from todayInHistory where newsId = @pkId)
delete from todayInHistory where newsId = @pkId
  COMMIT TRANSACTION

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
AS
	SET NOCOUNT ON
	declare @num int
	declare @vSQL varchar(1000)
	IF (@isForeGround=0)
	BEGIN
		select @vSQL=
			case @table
				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)+' 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 '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'	--魏兆佳20070727
				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'		--魏兆佳20070727
				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'			--魏兆佳20070727
				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'			--魏兆佳20070801
				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'--李聪杰20060406
				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'--李聪杰20060406					
				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 '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 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 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 return'	--魏兆佳20070727
				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 return'		--魏兆佳20070727
				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 return'		--魏兆佳20070727
				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 return'		--魏兆佳20070801
				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 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 return'--李聪杰20060406
				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 return'--李聪杰20060406					
				ELSE  'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from '+convert(varchar,@table)+' where gate<> '+convert(varchar,@gateId)+' and bigclassId='+convert(varchar,@bigclassId)+' and  gradeId<='+convert(varchar,@gradeId)+' and passed=1 return'
			end
	END	
	Execute (@vSQL)
	execute GetCount @count=@num output
	select @count=@num
RETURN

GO

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

CREATE PROCEDURE [dbo].[GetAccredit]
AS
	select pkId,name,parentId,modeId
	from menu 
	where modeId<>4
	union
	SELECT m.pkId,m.name,m.parentId,m.modeId
   	FROM menu m,bigClass b,template t	
	 where t.code<>'newsTop'
	  and t.code<>'gameTop' --魏兆佳20070727
	  and t.code<>'latestGame' --魏兆佳20070727
	  and t.code<>'todayInHistoryShort' --魏兆佳20070801
	  and t.code<>'todayInHistoryPic' --魏兆佳20070801
	  and t.code<>'searchTodayInHistory' --魏兆佳20070801
	  and t.code<>'fileDownTop'
	  and t.code<>'special'
	  and t.code<>'login'
	  and t.code<>'nav'
	  and t.code<>'search'
	   and t.code<>'SearchHorizontal'
	   and t.code<>'LoginHorizontal'
	    and t.code<>'baidu'
	    and t.code<>'shareNews'
	  and t.code<>'LatestNews'/*李聪杰20060327*/
	 and t.code<>'AdminLogin'/*李聪杰20060328*/
	 and t.code<>'contributeStat'
	and t.code<>'LatestMessage'/*李聪杰20060405*/
	and t.code<>'contributeTop'
	and t.code<>'latestContribute'
	  and m.bigClassId=b.pkId
	  and b.templateId=t.pkId
		
	RETURN

GO

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

CREATE PROCEDURE [dbo].[GetAccreditByAdminId]
	@adminId int
AS
			select m.pkId,name,parentId, modeId
	from menu m,adminAssign a
	where 
	 a.menuId=0
	 and @adminId=a.adminId
	 and m.pkId=0
	union
		select m.pkId,name,parentId, m.modeId
	from menu m,adminAssign a
	where 
	parentId=0
	and a.adminId=@adminId
	and a.menuId=m.pkId
	
	union
	select pkId,name,parentId, modeId
	from menu 
	where 
	modeId<>4 
	and parentId<>0
	union
	SELECT m.pkId,m.name,m.parentId, m.modeId
   	FROM menu m,bigClass b,template t,adminAssign a	
	 where t.code<>'newsTop'
	  and t.code<>'gameTop' --魏兆佳20070727
	  and t.code<>'latestGame' --魏兆佳20070727
	  and t.code<>'todayInHistoryShort' --魏兆佳20070801
	  and t.code<>'todayInHistoryPic' --魏兆佳20070801
	  and t.code<>'searchTodayInHistory' --魏兆佳20070801
	  and t.code<>'fileDownTop'
	  and t.code<>'special'
	  and t.code<>'login'
	  and t.code<>'nav'
	  and t.code<>'search'
	  and t.code<>'SearchHorizontal'
	   and t.code<>'LoginHorizontal'
	    and t.code<>'baidu'
	    and t.code<>'shareNews'
	   and t.code<>'LatestNews'/*李聪杰20060327*/
	 and t.code<>'AdminLogin'/*李聪杰20060328*/
	 and t.code<>'contributeStat'
	and t.code<>'LatestMessage'/*李聪杰20060405*/
	and t.code<>'contributeTop'
	and t.code<>'latestContribute'
	  and m.bigClassId=b.pkId
	  and b.templateId=t.pkId
	  
	  
	RETURN

GO

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

CREATE PROCEDURE [dbo].[GetNewsByBigClassIdAndisImageNewsAndPassed]
@bigClassId int=null,
@currentPage int=1,
@pageSize int=5,
@gradeid int=1,
@gateId int
AS
	 SET NOCOUNT ON	
	declare @vSQL varchar(1000)
select @vSQL = 'select top ' + convert(varchar, @pageSize) + ' n.*,u.userName,s.name "special",d.name "department" ,g.name "grade" from news n,users u,department d  ,grade g, special s where    n.gate<>'+Convert(varchar,@gateId)+' and  n.passed=1 and n.bigClassId='+Convert(varchar,@bigClassId)+' and n.gradeId<='+convert(varchar,@gradeId)+' and n.departmentId=d.pkId and n.gradeId=g.pkId and n.usersId=u.pkId  and isImageNews=1 and n.specialId = s.pkId and n.pkId <> all (select top '+Convert(varchar,((@currentPage-1)*@pageSize))+' pkId from news  where   gate<>'+Convert(varchar,@gateId)+' and  passed=1 and bigClassId='+Convert(varchar,@bigClassId)+'  and isImageNews=1 and gradeId<='+convert(varchar,@gradeId)+' order by istop desc,pkId desc)  order by istop desc,n.pkId desc'
Execute (@vSQL)	
	RETURN
	
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetTemplateByMenuId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetTemplateByMenuId]
GO
CREATE PROCEDURE [dbo].[GetTemplateByMenuId]
(
	@menuId int
)
AS
	SELECT 
		t.pkId as templateId, 
		t.name as templateName, 
		t.code as templateCode, 
		b.pkId as bigClassId,
		b.name as bigClassName,
		m.pkId as menuId,
		m.name as menuName
FROM template t, bigClass b, menu m
WHERE m.bigClassId = b.pkId AND b.templateId = t.pkId AND m.pkId = @menuId
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
AS
	 SET NOCOUNT ON 
	
	declare @vSQL varchar(1000)
select @vSQL = 'select top ' + convert(varchar, @pageSize) + ' n.*,u.userName,d.name "department" from news n,users u,department d where   n.gate<>'+Convert(varchar,@gateId)+' and (n.isDuplicated=0 or n.isDuplicated is null) and n.bigClassId not in (select pkId from bigClass where templateId in (49,50,53,54)) and n.usersId=u.pkId and n.departmentId=d.pkId and n.gradeId<='+convert(varchar,@gradeId)+' and n.passed=1  order by n.click desc'
Execute (@vSQL)	
	RETURN 
	
GO




/*2007-9-19*/
GO
/****** 对象:  StoredProcedure [dbo].[GetNewsByPkId]    脚本日期: 09/18/2007 18:32:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[GetNewsByPkId]
@pkId int =null,
@gradeId int=1,
@gateId int
AS
	 SET NOCOUNT ON 
	select n.*,u.userName,s.name "special" ,d.name "department" ,g.name "grade" 
	from news n, users u, department d, grade g, special s
	where @pkId=n.pkId
	  and n.gradeId<=@gradeId
	  and n.gate<>@gateId and n.departmentId=d.pkId and n.gradeId=g.pkId and n.usersId=u.pkId and n.specialId=s.pkId
	RETURN 



GO
/****** 对象:  StoredProcedure [dbo].[GetNewsByPkIdForDuplicating]    脚本日期: 09/18/2007 18:37:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[GetNewsByPkIdForDuplicating]
@pkId int =null
AS
	 SET NOCOUNT ON 
	select n.*,u.userName,s.name "special" ,d.name "department" ,g.name "grade" 
	from news n, users u, department d, grade g, special s
	where @pkId=n.pkId
	  and n.departmentId=d.pkId and n.gradeId=g.pkId and n.usersId=u.pkId and n.specialId=s.pkId
	RETURN 