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

CREATE PROCEDURE [dbo].[GetImageNewsByRecommand]
@pageSize int=5,
@gradeid int=1,
@gateId int
AS
	 SET NOCOUNT ON 
	
	declare @vSQL varchar(1000)
select @vSQL ='select top 1 * from news where passed=1   and isRecommand=1  and IsImageNews=1   and gate<>'+Convert(varchar,@gateId)+'  and gradeId<='+Convert(varchar,@gradeId)+' order by istop desc,pkId desc'
Execute (@vSQL)
RETURN

GO

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



CREATE PROCEDURE [dbo].[UpdateStatForTime]
	@time int
AS
	SET NOCOUNT OFF;
declare @num int
select @num=count(*) from stat
if(@num=0)
insert into stat ([time])values(@time)
UPDATE stat SET [time] = @time




GO





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



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

SET NOCOUNT ON

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








GO





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




CREATE PROCEDURE [dbo].[GetSiteOtherProperty]
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	*
FROM
	[dbo].[siteOtherProperty]







GO





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




CREATE PROCEDURE [dbo].[GetNewsSearchCount]
	@searchStr varchar(100),
	@bigClassId int,
	@type int,
	@gradeId int,
	@gateId int,
	@recordCount int output
AS	
	
	--初始化查询条件(不带where)
	DECLARE @str nvarchar(1000)
	SET @str = ' 1=1 '
	SET @searchStr = '%'+Convert(varchar,@searchStr)+'%'
	IF (@bigClassId <> 0)
		SET @str = ' and bigClassId='+STR(@bigClassId)
	
	IF (@type=0)
		BEGIN
			SET @str = @str + ' and (listTitle like ''' + @searchStr + ''''
			SET @str = @str + ' or intactTitle like ''' + @searchStr + ''''
			SET @str = @str + ' or subheading like ''' + @searchStr + ''''
			SET @str = @str + ' or content like ''' + @searchStr + ''''
			SET @str = @str + ' or author like ''' + @searchStr + ''')' 
		END
	IF(@type=1)
		BEGIN
			SET @str = @str + ' and (listTitle like ''' + @searchStr + ''''
			SET @str = @str + ' or intactTitle like ''' + @searchStr + ''''
			SET @str = @str + ' or subheading like ''' + @searchStr + ''')'
		END
	IF(@type=2)
		SET @str = @str + ' and author like ''' + @searchStr + ''''
	IF(@type=3)
		SET @str = @str + ' and content like ''' + @searchStr + ''''

	SET @str = @str + ' and passed=1 and isDuplicated<>1 and gradeId<='+Convert(varchar,@gradeId)
	SET @str = @str + ' and gate<>'+Convert(varchar,@gateId)

	--设置查询表名称
	DECLARE @tableName nvarchar(50)
	SET @tableName = 'news'
	
	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT @count = Count(pkId) from '+Convert(nvarchar(50),@tableName)+' where ' + Convert(nvarchar(1000),@str)
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum

	select * from news where 1=0
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 int,
	@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)+')'
	--IF (@manageAll<>1)
		--SET @str = @str + ' and n.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 int,
	@recordCount int output
	
AS	
	--转换成索引
	SELECT @currentPage = @currentPage - 1
	
	--初始化查询条件(不带where)
	DECLARE @str nvarchar(1000)
	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 usersId='+STR(@userId)+')'
	IF (@manageAll<>1)
		SET @str = @str + ' and n.usersId='+STR(@userId)
	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(50)
	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(2000)
	SET @vSQL = N'SELECT @count = Count(n.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) + ' n.*,u.userName,d.name "department" ,g.name "grade",m.name "menuName", n.pkId as newsId FROM '+Convert(nvarchar(50),@tableName)+' where '+Convert(nvarchar(1000),@str)+' and (n.isDuplicated is null or n.isDuplicated =0 )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(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 ) + ' n.*,u.userName,d.name "department" ,g.name "grade",m.name "menuName", n.pkId as newsId 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 ) + ' n.*,u.userName,d.name "department" ,g.name "grade",m.name "menuName", n.pkId as newsId 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
	print @sqlStr

	--执行查询
	EXEC(@sqlStr)

RETURN

GO





/****** 对象: 存储过程 dbo.GetAuditingReviewsByUserId    脚本日期: 2006-12-1 9:36:21 ******/
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 int,
	@recordCount int output
	
AS	
	--转换成索引
	SELECT @currentPage = @currentPage - 1
	
	--初始化查询条件(不带where)
	DECLARE @str nvarchar(1000)
	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)+')'
	IF (@manageAll<>1)
		SET @str = @str + ' and n.usersId='+STR(@userId)
	SET @str = @str + ' and r.passed=0 and n.gradeId<='+STR(@gradeId)
	SET @str = @str + ' and n.gate<>'+STR(@gateId)
	SET @str = @str + ' and r.newsId=n.pkId 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, review r '

	--设置查询排序（不带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(2000)
	SET @vSQL = N'SELECT @count = Count(*) from (select Count(n.pkId) as recordCount from'+Convert(nvarchar(100),@tableName)+' where ' + Convert(nvarchar(1000),@str) + ' group by r.newsId) 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" FROM '+Convert(nvarchar(100),@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) + ' n.*,u.userName,d.name "department" ,g.name "grade",m.name "menuName" FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(1000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable ORDER BY '+Convert(nvarchar(100),@orderTypeD)	
	--查询中间页后面的页
	ELSE IF @currentPage > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @currentPage ) + ' n.*,u.userName,d.name "department" ,g.name "grade",m.name "menuName" FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(1000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable ORDER BY '+Convert(nvarchar(100),@orderTypeD)
	--查询中间页前面的页
 	ELSE BEGIN							
		SET @currentPage=@currentPage+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @currentPage ) + ' n.*,u.userName,d.name "department" ,g.name "grade",m.name "menuName" FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(1000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeD)+') TempTable1 '
					+ ' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable2 ORDER BY '+Convert(nvarchar(100),@orderTypeD)
	END

	--执行查询
	EXEC(@sqlStr)

RETURN
GO





/****** 对象: 存储过程 dbo.GetAuditingCount    脚本日期: 2006-12-1 9:36:21 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetAuditingCount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetAuditingCount]
GO


/* 创建过程/函数 GetAuditingCount;1。                                                                */

CREATE PROCEDURE [dbo].[GetAuditingCount]
	@gradeId int,
	@userId int,
	@gateId int,
	@manageAll int,
	@newsCount int output,
	@reviewsCount int output,
	@messageBoardCount int output
	
AS
	
	--初始化查询条件(不带where)
	DECLARE @str nvarchar(1000)
	DECLARE @str1 nvarchar(1000)
	DECLARE @str3 nvarchar(1000)
	--新闻
	SET @str = ' 1=1 '
	SET @str = @str + '  and isDuplicated<>1 and 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)+')'
	IF (@manageAll<>1)
		SET @str = @str + ' and usersId='+STR(@userId)
	SET @str = @str + ' and passed=0 and gradeId<='+STR(@gradeId)
	SET @str = @str + ' and gate<>'+STR(@gateId)
	--评论
	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 usersId='+STR(@userId)+')'
	IF (@manageAll<>1)
		SET @str1 = @str1 + ' and n.usersId='+STR(@userId)
	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 '
	--留言本
	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 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(2000)
	SET @vSQL = N'SELECT @count1 = Count(pkId) from news where ' + Convert(nvarchar(1000),@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 where ' + Convert(nvarchar(1000),@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(1000),@str3)
	EXEC sp_ExecuteSQL @vSQL, N'@count3 int output', @tempNum output
	SELECT @messageBoardCount = @tempNum
	

RETURN


GO





/****** 对象: 存储过程 dbo.GetAspProperty    脚本日期: 2006-12-1 9:36:21 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetAspProperty]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetAspProperty]
GO


/* 创建过程/函数 GetAdmin;1。                                                                        */
CREATE PROCEDURE [dbo].[GetAspProperty]
AS
	SET NOCOUNT ON;
SELECT * FROM AspProperty



GO





/****** 对象: 存储过程 dbo.DeleteSignUpField    脚本日期: 2006-12-1 9:36:21 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DeleteSignUpField]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DeleteSignUpField]
GO




CREATE PROCEDURE [dbo].[DeleteSignUpField]
	@PkId int
AS

SET NOCOUNT ON

DELETE FROM [dbo].[SignUpField]
WHERE
	[PkId] = @PkId







GO





/****** 对象: 存储过程 dbo.GetAllSignUpField    脚本日期: 2006-12-1 9:36:21 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetAllSignUpField]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetAllSignUpField]
GO




CREATE PROCEDURE [dbo].[GetAllSignUpField]
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	*
FROM
	[dbo].[SignUpField]







GO





/****** 对象: 存储过程 dbo.GetSignUpFieldByPkId    脚本日期: 2006-12-1 9:36:21 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetSignUpFieldByPkId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetSignUpFieldByPkId]
GO



/* 创建过程/函数 GetBackupInfoByPkId;1。                                                             */
CREATE PROCEDURE [dbo].[GetSignUpFieldByPkId]
@PkId int
AS
	SET NOCOUNT ON;
SELECT * FROM SignUpField
WHERE @PkId=PkId




GO





/****** 对象: 存储过程 dbo.InsertSignUpField    脚本日期: 2006-12-1 9:36:21 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InsertSignUpField]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertSignUpField]
GO




CREATE PROCEDURE [dbo].[InsertSignUpField]
	@Name varchar(50),
	@Type varchar(50),
	@Size int,
	@ShowName varchar(500),
	@ShowType varchar(250),
	@ShowValue varchar(1024),
	@ShowComments varchar(1024),
	@AllowNull int,
	@IsSystem int,
	@IsOnly int,
	@PkId int
AS

SET NOCOUNT ON

INSERT INTO [dbo].[SignUpField] (
	[Name],
	[Type],
	[Size],
	[ShowName],
	[ShowType],
	[ShowValue],
	[ShowComments],
	[AllowNull],
	[IsSystem],
	[IsOnly]
) VALUES (
	@Name,
	@Type,
	@Size,
	@ShowName,
	@ShowType,
	@ShowValue,
	@ShowComments,
	@AllowNull,
	@IsSystem,
	@IsOnly
)








GO





/****** 对象: 存储过程 dbo.UpdateSignUpField    脚本日期: 2006-12-1 9:36:21 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateSignUpField]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateSignUpField]
GO






CREATE PROCEDURE [dbo].[UpdateSignUpField]
	@PkId int,
	@Name varchar(50),
	@Type varchar(50),
	@Size int,
	@ShowName varchar(500),
	@ShowType varchar(250),
	@ShowValue varchar(1024),
	@ShowComments varchar(1024),
	@AllowNull int,
	@IsSystem int,
	@IsOnly int
AS

SET NOCOUNT ON

UPDATE [dbo].[SignUpField] SET
	[Name] = @Name,
	[Type] = @Type,
	[Size] = @Size,
	[ShowName] = @ShowName,
	[ShowType] = @ShowType,
	[ShowValue] = @ShowValue,
	[ShowComments] = @ShowComments,
	[AllowNull] = @AllowNull,
	[IsSystem] = @IsSystem,
	[IsOnly] = @IsOnly
WHERE
	[PkId] = @PkId







GO





/****** 对象: 存储过程 dbo.GetSignUpBuildFieldByTableName    脚本日期: 2006-12-1 9:36:21 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetSignUpBuildFieldByTableName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetSignUpBuildFieldByTableName]
GO



/* 创建过程/函数 GetResear;1。                                                                       */
CREATE PROCEDURE [dbo].[GetSignUpBuildFieldByTableName]
   @TableName varchar(100)
AS
	SET NOCOUNT ON;
  select * from SignUpBuildField where TableName = @TableName




GO





/****** 对象: 存储过程 dbo.GetSignUpRelationByBigClassId    脚本日期: 2006-12-1 9:36:21 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetSignUpRelationByBigClassId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetSignUpRelationByBigClassId]
GO



/* 创建过程/函数 GetBackupInfoByPkId;1。                                                             */
CREATE PROCEDURE [dbo].[GetSignUpRelationByBigClassId]
@BigClassId int
AS
	SET NOCOUNT ON;
SELECT * FROM SignUpRelation
WHERE BigClassId=@BigClassId




GO





/****** 对象: 存储过程 dbo.DeleteSignUpRelation    脚本日期: 2006-12-1 9:36:21 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DeleteSignUpRelation]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DeleteSignUpRelation]
GO




CREATE PROCEDURE [dbo].[DeleteSignUpRelation]
	@pkId int
AS

SET NOCOUNT ON

DELETE FROM [dbo].[SignUpRelation]
WHERE
	BigClassId = @pkId







GO





/****** 对象: 存储过程 dbo.InsertSignUpRelation    脚本日期: 2006-12-1 9:36:21 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InsertSignUpRelation]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertSignUpRelation]
GO




CREATE PROCEDURE [dbo].[InsertSignUpRelation]
	@BigClassId int,
	@TableName varchar(100),
	@PkId int
AS

SET NOCOUNT ON

INSERT INTO [dbo].[SignUpRelation] (
	[BigClassId],
	[TableName]
) VALUES (
	@BigClassId,
	@TableName
)







GO





/****** 对象: 存储过程 dbo.UpdateSignUpRelation    脚本日期: 2006-12-1 9:36:21 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateSignUpRelation]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateSignUpRelation]
GO




CREATE PROCEDURE [dbo].[UpdateSignUpRelation]
	@PkId int,
	@BigClassId int,
	@TableName varchar(100)
AS

SET NOCOUNT ON

UPDATE [dbo].[SignUpRelation] SET
	[BigClassId] = @BigClassId,
	[TableName] = @TableName
WHERE
	[PkId] = @PkId







GO





/****** 对象: 存储过程 dbo.DeleteSignUpBuildFieldByName    脚本日期: 2006-12-1 9:36:21 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DeleteSignUpBuildFieldByName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DeleteSignUpBuildFieldByName]
GO






CREATE PROCEDURE [dbo].[DeleteSignUpBuildFieldByName]
	@TableName varchar(100),
	@Name varchar(50)
AS

SET NOCOUNT ON

DELETE FROM [dbo].[SignUpBuildField]
WHERE
	[Name] = @Name and TableName=@TableName







GO





/****** 对象: 存储过程 dbo.DeleteSignUpBuildFieldByTableName    脚本日期: 2006-12-1 9:36:21 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DeleteSignUpBuildFieldByTableName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DeleteSignUpBuildFieldByTableName]
GO





CREATE PROCEDURE [dbo].[DeleteSignUpBuildFieldByTableName]
	@TableName varchar(100)
AS

SET NOCOUNT ON

DELETE FROM [dbo].[SignUpBuildField]
WHERE
	TableName = @TableName






GO





/****** 对象: 存储过程 dbo.InsertSignUpBuildField    脚本日期: 2006-12-1 9:36:22 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InsertSignUpBuildField]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertSignUpBuildField]
GO







CREATE PROCEDURE [dbo].[InsertSignUpBuildField]
	@TableName varchar(100),
	@Name varchar(50),
	@Type varchar(50),
	@Size int,
	@ShowName varchar(500),
	@ShowType varchar(250),
	@ShowValue varchar(1024),
	@ShowComments varchar(1024),
	@AllowNull int,
	@PkId int,
	@IsSystem int,
	@IsOnly int,
	@IsAddMark int,
	@AddMarkField varchar(50)
AS

SET NOCOUNT ON

INSERT INTO [dbo].[SignUpBuildField] (
	[TableName],
	[Name],
	[Type],
	[Size],
	[ShowName],
	[ShowType],
	[ShowValue],
	[ShowComments],
	[AllowNull],
	[IsSystem],
	[IsOnly],
	[IsAddMark],
	[AddMarkField]
) VALUES (
	@TableName,
	@Name,
	@Type,
	@Size,
	@ShowName,
	@ShowType,
	@ShowValue,
	@ShowComments,
	@AllowNull,
	@IsSystem,
	@IsOnly,
	@IsAddMark,
	@AddMarkField
)










GO





/****** 对象: 存储过程 dbo.UpdateSignUpBuildField    脚本日期: 2006-12-1 9:36:22 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateSignUpBuildField]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateSignUpBuildField]
GO








CREATE PROCEDURE [dbo].[UpdateSignUpBuildField]
	@PkId int,
	@TableName varchar(100),
	@Name varchar(50),
	@Type varchar(50),
	@Size int,
	@ShowName varchar(500),
	@ShowType varchar(250),
	@ShowValue varchar(1024),
	@ShowComments varchar(1024),
	@AllowNull int,
	@IsSystem int,
	@IsOnly int,
	@IsAddMark int,
	@AddMarkField varchar(50)
AS

SET NOCOUNT ON

UPDATE [dbo].[SignUpBuildField] SET
	[TableName] = @TableName,
	[Name] = @Name,
	[Type] = @Type,
	[Size] = @Size,
	[ShowName] = @ShowName,
	[ShowType] = @ShowType,
	[ShowValue] = @ShowValue,
	[ShowComments] = @ShowComments,
	[AllowNull] = @AllowNull,
	[IsSystem] = @IsSystem,
	[IsOnly] = @IsOnly,
	[IsAddMark] = @IsAddMark,
	[AddMarkField] = @AddMarkField
WHERE
	[PkId] = @PkId










GO





/****** 对象: 存储过程 dbo.GetSignUpBuildField    脚本日期: 2006-12-1 9:36:22 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetSignUpBuildField]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetSignUpBuildField]
GO




CREATE PROCEDURE [dbo].[GetSignUpBuildField]
	@PkId int
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	*
FROM
	[dbo].[SignUpBuildField]
WHERE
	[PkId] = @PkId







GO





/****** 对象: 存储过程 dbo.GetSignUpFieldByName    脚本日期: 2006-12-1 9:36:22 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetSignUpFieldByName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetSignUpFieldByName]
GO



/* 创建过程/函数 GetBackupInfoByPkId;1。                                                             */
CREATE PROCEDURE [dbo].[GetSignUpFieldByName]
@Name varchar(50)
AS
	SET NOCOUNT ON;
SELECT * FROM SignUpField
WHERE Name=@Name




GO





/****** 对象: 存储过程 dbo.DeleteSignUpBuildFieldByPkId    脚本日期: 2006-12-1 9:36:22 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DeleteSignUpBuildFieldByPkId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DeleteSignUpBuildFieldByPkId]
GO






CREATE PROCEDURE [dbo].[DeleteSignUpBuildFieldByPkId]
	@PkId int
AS

SET NOCOUNT ON

DELETE FROM [dbo].[SignUpBuildField]
WHERE
	PkId = @PkId







GO





/****** 对象: 存储过程 dbo.GetAllSignUpTable    脚本日期: 2006-12-1 9:36:22 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetAllSignUpTable]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetAllSignUpTable]
GO


CREATE PROCEDURE [dbo].[GetAllSignUpTable]
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@tableName varchar(255),
	@recordCount int output
AS	
	--转换成索引
	SELECT @currentPage = @currentPage - 1
	
	--初始化查询条件(不带where)
	DECLARE @str nvarchar(1000)
	
		SET @str = '1=1'

	

	--设置查询排序（不带order by）
	DECLARE @orderTypeA nvarchar(100)
	DECLARE @orderTypeD nvarchar(100)
	SET @orderTypeA = 'PkId ASC'
	SET @orderTypeD = 'PkId DESC'
	
	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT @count = Count(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) + ' * 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) + ' *  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 ) + ' * 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 ) + ' * 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





/****** 对象: 存储过程 dbo.GetNewsByPkIdForDuplicating    脚本日期: 2006-12-1 9:36:22 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetNewsByPkIdForDuplicating]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetNewsByPkIdForDuplicating]
GO


/* 创建过程/函数 GetNewsByPkId;1。                                                                   */
CREATE PROCEDURE [dbo].[GetNewsByPkIdForDuplicating]
@pkId int =null
/*
	(
		@parameter1 datatype = default value,
		@parameter2 datatype OUTPUT
	)
*/
AS
	 SET NOCOUNT ON 
	select n.*,u.userName,d.name "department" ,g.name "grade" 
	from news n, users u, department d, grade g
	where @pkId=n.pkId
	  and n.departmentId=d.pkId and n.gradeId=g.pkId and n.usersId=u.pkId 
	RETURN 



GO





/****** 对象: 存储过程 dbo.GetBoardByPkIdForDuplicating    脚本日期: 2006-12-1 9:36:22 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetBoardByPkIdForDuplicating]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetBoardByPkIdForDuplicating]
GO


CREATE PROCEDURE [dbo].[GetBoardByPkIdForDuplicating]
	@pkId int=null
/*
	(
		@parameter1 datatype = default value,
		@parameter2 datatype OUTPUT
	)
*/
AS
	SET NOCOUNT ON
	SELECT b.*,u.userName 'userName',g.name 'grade' FROM board b,users u,grade g
where b.pkId=@pkId
  and b.userId=u.pkId
  and b.gradeId=g.pkId
	RETURN 


GO





/****** 对象: 存储过程 dbo.UpdateAspProperty    脚本日期: 2006-12-1 9:36:22 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateAspProperty]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateAspProperty]
GO



CREATE PROCEDURE [dbo].[UpdateAspProperty]
	@pkId int,
	@url varchar(100),
	@space int,
	@usedImagesSpace float,
	@usedBackupSpace float,
	@usedSpace float,
	@buildByAsp int
AS

SET NOCOUNT ON

UPDATE [dbo].[AspProperty] SET
	[url] = @url,
	[space] = @space,
	[usedImagesSpace] = @usedImagesSpace,
	[usedBackupSpace] = @usedBackupSpace,
	[usedSpace] = @usedSpace,
	[buildByAsp] = @buildByAsp
WHERE
	[pkId] = @pkId




GO





/****** 对象: 存储过程 dbo.InsertAspProperty    脚本日期: 2006-12-1 9:36:22 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InsertAspProperty]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertAspProperty]
GO




CREATE PROCEDURE [dbo].[InsertAspProperty]
	@url varchar(100),
	@space int,
	@usedImagesSpace float,
	@usedBackupSpace float,
	@usedSpace float,
	@buildByAsp int,
	@pkId int
AS

SET NOCOUNT ON

INSERT INTO [dbo].[AspProperty] (
	[url],
	[space],
	[usedImagesSpace],
	[usedBackupSpace],
	[usedSpace],
	[buildByAsp]
) VALUES (
	@url,
	@space,
	@usedImagesSpace,
	@usedBackupSpace,
	@usedSpace,
	@buildByAsp
)




GO





/****** 对象: 存储过程 dbo.InsertArtDesign    脚本日期: 2006-12-1 9:36:22 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InsertArtDesign]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertArtDesign]
GO





/* 创建过程/函数 InsertArtDesign;1。                                                                 */
CREATE PROCEDURE [dbo].[InsertArtDesign]
(
	@pkId int,
	@name varchar(2000),
	@leftTopFrontImageVar varchar(100),
	@leftMiddleFrontImageVar varchar(100),
	@leftMiddleBackImageVar varchar(100),
	@leftVert varchar(100),
	@leftMiddleFrontWidth varchar(100),
	@leftMiddleFrontHeight varchar(100),
	@leftMiddleFrontLink varchar(100),
	@leftBottomFrontImageVar varchar(100),
	@centerTopFrontImageVar varchar(100),
	@centerTopBackImageVar varchar(100),
	@centerTopFrontWidth varchar(100),
	@centerTopFrontHeight varchar(100),
	@centerTopFrontLink varchar(100),
	@centerMiddleBackImageVar varchar(100),
	@itemImageVar varchar(100),
	@centerBottomBackImageVar varchar(100),
	@rightTopFrontImageVar varchar(100),
	@rightMiddleFrontImageVar varchar(100),
	@rightMiddleBackImageVar varchar(100),
	@rightVert varchar(100),
	@rightMiddleFrontWidth varchar(100),
	@rightMiddleFrontHeight varchar(100),
	@rightMiddleFrontLink varchar(100),
	@rightBottomFrontImageVar varchar(100),
	@fontPosition varchar(100),
	@color varchar(100),
	@size varchar(100),
	@space varchar(100),
	@font varchar(100),
	@bold varchar(100),
	@italic varchar(100),
	@topColor varchar(100),
	@middleColor varchar(100),
	@bottomColor varchar(100),
	@border varchar(100),
	@cellPad varchar(100),
	@cellSpace varchar(100),
	@brdrColor varchar(100),
	@centerBottomFrontImageVar varchar(100),
	@menuId int,
	@flag int,
	@middleTableBorderColor varchar(100),
	@middleTableBorderShow varchar(100),
	@releaseDateVar int,
	@releaseDepartmentVar int,
	@click int,
	@newImageVar varchar(100),
	@topImageVar varchar(100),
	@showTopMoreInfo int,
	@topMoreInfoImageVar varchar(100),
	@showBottomMoreInfo int,
	@bottomMoreInfoImageVar varchar(100),
	@hotImageVar varchar(100)
)
AS
	SET NOCOUNT OFF;
INSERT INTO artDesign(middleTableBorderColor,middleTableBorderShow,name, leftTopFrontImageVar, leftMiddleFrontImageVar, leftMiddleBackImageVar, leftVert, leftMiddleFrontWidth, leftMiddleFrontHeight, leftMiddleFrontLink, leftBottomFrontImageVar, centerTopFrontImageVar, centerTopBackImageVar, centerTopFrontWidth, centerTopFrontHeight, centerTopFrontLink, centerMiddleBackImageVar, itemImageVar, centerBottomBackImageVar, rightTopFrontImageVar, rightMiddleFrontImageVar, rightMiddleBackImageVar, rightVert, rightMiddleFrontWidth, rightMiddleFrontHeight, rightMiddleFrontLink, rightBottomFrontImageVar, fontPosition, color, size, space, font, bold, italic, topColor, middleColor, bottomColor, border, cellPad, cellSpace, brdrColor, centerBottomFrontImageVar, menuId,releaseDateVar,releaseDepartmentVar,click,newImageVar,topImageVar,showTopMoreInfo ,topMoreInfoImageVar,	showBottomMoreInfo,bottomMoreInfoImageVar,hotImageVar) VALUES (@middleTableBorderColor,@middleTableBorderShow,@name, @leftTopFrontImageVar, @leftMiddleFrontImageVar, @leftMiddleBackImageVar, @leftVert, @leftMiddleFrontWidth, @leftMiddleFrontHeight, @leftMiddleFrontLink, @leftBottomFrontImageVar, @centerTopFrontImageVar, @centerTopBackImageVar, @centerTopFrontWidth, @centerTopFrontHeight, @centerTopFrontLink, @centerMiddleBackImageVar, @itemImageVar, @centerBottomBackImageVar, @rightTopFrontImageVar, @rightMiddleFrontImageVar, @rightMiddleBackImageVar, @rightVert, @rightMiddleFrontWidth, @rightMiddleFrontHeight, @rightMiddleFrontLink, @rightBottomFrontImageVar, @fontPosition, @color, @size, @space, @font, @bold, @italic, @topColor, @middleColor, @bottomColor, @border, @cellPad, @cellSpace, @brdrColor, @centerBottomFrontImageVar, @menuId,@releaseDateVar,@releaseDepartmentVar,@click,@newImageVar,@topImageVar,
@showTopMoreInfo ,@topMoreInfoImageVar,	@showBottomMoreInfo,@bottomMoreInfoImageVar,@hotImageVar)






GO





/****** 对象: 存储过程 dbo.UpdateArtDesign    脚本日期: 2006-12-1 9:36:22 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateArtDesign]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateArtDesign]
GO




/* 创建过程/函数 UpdateArtDesign;1。                                                                 */
CREATE PROCEDURE [dbo].[UpdateArtDesign]
(
	@name varchar(2000),
	@leftTopFrontImageVar varchar(100),
	@leftMiddleFrontImageVar varchar(100),
	@leftMiddleBackImageVar varchar(100),
	@leftVert varchar(100),
	@leftMiddleFrontWidth varchar(100),
	@leftMiddleFrontHeight varchar(100),
	@leftMiddleFrontLink varchar(100),
	@leftBottomFrontImageVar varchar(100),
	@centerTopFrontImageVar varchar(100),
	@centerTopBackImageVar varchar(100),
	@centerTopFrontWidth varchar(100),
	@centerTopFrontHeight varchar(100),
	@centerTopFrontLink varchar(100),
	@centerMiddleBackImageVar varchar(100),
	@itemImageVar varchar(100),
	@centerBottomBackImageVar varchar(100),
	@rightTopFrontImageVar varchar(100),
	@rightMiddleFrontImageVar varchar(100),
	@rightMiddleBackImageVar varchar(100),
	@rightVert varchar(100),
	@rightMiddleFrontWidth varchar(100),
	@rightMiddleFrontHeight varchar(100),
	@rightMiddleFrontLink varchar(100),
	@rightBottomFrontImageVar varchar(100),
	@fontPosition varchar(100),
	@color varchar(100),
	@size varchar(100),
	@space varchar(100),
	@font varchar(100),
	@bold varchar(100),
	@italic varchar(100),
	@topColor varchar(100),
	@middleColor varchar(100),
	@bottomColor varchar(100),
	@border varchar(100),
	@cellPad varchar(100),
	@cellSpace varchar(100),
	@brdrColor varchar(100),
	@centerBottomFrontImageVar varchar(100),
	@centerTopBackHeight  varchar(100),
	@menuId int,
	@pkId int,
	@saveMode int,
	@parentId int,
	@middleTableBorderColor varchar(100),
	@middleTableBorderShow varchar(100),
	@releaseDateVar int,
	@releaseDepartmentVar int,
	@click int,
	@newImageVar varchar(100),
	@topImageVar varchar(100),
	@showTopMoreInfo int,
	@topMoreInfoImageVar varchar(100),
	@showBottomMoreInfo int,
	@bottomMoreInfoImageVar varchar(100),
	@hotImageVar varchar(100)
)
AS
	SET  NOCOUNT OFF;
		if @saveMode=-2
	UPDATE artDesign   SET  middleTableBorderColor=@middleTableBorderColor,middleTableBorderShow=@middleTableBorderShow,centerTopBackHeight=@centerTopBackHeight, leftTopFrontImageVar = @leftTopFrontImageVar, leftMiddleFrontImageVar = @leftMiddleFrontImageVar, leftMiddleBackImageVar = @leftMiddleBackImageVar, leftVert = @leftVert, leftMiddleFrontWidth = @leftMiddleFrontWidth, leftMiddleFrontHeight = @leftMiddleFrontHeight, leftMiddleFrontLink = @leftMiddleFrontLink, leftBottomFrontImageVar = @leftBottomFrontImageVar, centerTopFrontImageVar = @centerTopFrontImageVar, centerTopBackImageVar = @centerTopBackImageVar, centerTopFrontWidth = @centerTopFrontWidth, centerTopFrontHeight = @centerTopFrontHeight, centerTopFrontLink = @centerTopFrontLink, centerMiddleBackImageVar = @centerMiddleBackImageVar, itemImageVar = @itemImageVar, centerBottomBackImageVar = @centerBottomBackImageVar, rightTopFrontImageVar = @rightTopFrontImageVar, rightMiddleFrontImageVar = @rightMiddleFrontImageVar, rightMiddleBackImageVar = @rightMiddleBackImageVar, rightVert = @rightVert, rightMiddleFrontWidth = @rightMiddleFrontWidth, rightMiddleFrontHeight = @rightMiddleFrontHeight, rightMiddleFrontLink = @rightMiddleFrontLink, rightBottomFrontImageVar = @rightBottomFrontImageVar, fontPosition = @fontPosition, color = @color, size = @size, space = @space, font = @font, bold = @bold, italic = @italic, topColor = @topColor, middleColor = @middleColor, bottomColor = @bottomColor, border = @border, cellPad = @cellPad, cellSpace = @cellSpace, brdrColor = @brdrColor, centerBottomFrontImageVar = @centerBottomFrontImageVar,releaseDateVar=@releaseDateVar,releaseDepartmentVar=@releaseDepartmentVar,click=@click,newImageVar=@newImageVar, hotImageVar = @hotImageVar,topImageVar=@topImageVar ,showTopMoreInfo=@showTopMoreInfo ,topMoreInfoImageVar=@topMoreInfoImageVar,	showBottomMoreInfo=@showBottomMoreInfo,bottomMoreInfoImageVar=@bottomMoreInfoImageVar from menu m WHERE menuId=m.pkId
		if @saveMode=-1
	UPDATE artDesign   SET  middleTableBorderColor=@middleTableBorderColor,middleTableBorderShow=@middleTableBorderShow,centerTopBackHeight=@centerTopBackHeight, leftTopFrontImageVar = @leftTopFrontImageVar, leftMiddleFrontImageVar = @leftMiddleFrontImageVar, leftMiddleBackImageVar = @leftMiddleBackImageVar, leftVert = @leftVert, leftMiddleFrontWidth = @leftMiddleFrontWidth, leftMiddleFrontHeight = @leftMiddleFrontHeight, leftMiddleFrontLink = @leftMiddleFrontLink, leftBottomFrontImageVar = @leftBottomFrontImageVar, centerTopFrontImageVar = @centerTopFrontImageVar, centerTopBackImageVar = @centerTopBackImageVar, centerTopFrontWidth = @centerTopFrontWidth, centerTopFrontHeight = @centerTopFrontHeight, centerTopFrontLink = @centerTopFrontLink, centerMiddleBackImageVar = @centerMiddleBackImageVar, itemImageVar = @itemImageVar, centerBottomBackImageVar = @centerBottomBackImageVar, rightTopFrontImageVar = @rightTopFrontImageVar, rightMiddleFrontImageVar = @rightMiddleFrontImageVar, rightMiddleBackImageVar = @rightMiddleBackImageVar, rightVert = @rightVert, rightMiddleFrontWidth = @rightMiddleFrontWidth, rightMiddleFrontHeight = @rightMiddleFrontHeight, rightMiddleFrontLink = @rightMiddleFrontLink, rightBottomFrontImageVar = @rightBottomFrontImageVar, fontPosition = @fontPosition, color = @color, size = @size, space = @space, font = @font, bold = @bold, italic = @italic, topColor = @topColor, middleColor = @middleColor, bottomColor = @bottomColor, border = @border, cellPad = @cellPad, cellSpace = @cellSpace, brdrColor = @brdrColor, centerBottomFrontImageVar = @centerBottomFrontImageVar,releaseDateVar=@releaseDateVar,releaseDepartmentVar=@releaseDepartmentVar,click=@click,newImageVar=@newImageVar, hotImageVar=@hotImageVar,topImageVar=@topImageVar ,showTopMoreInfo=@showTopMoreInfo ,topMoreInfoImageVar=@topMoreInfoImageVar,	showBottomMoreInfo=@showBottomMoreInfo,bottomMoreInfoImageVar=@bottomMoreInfoImageVar	from menu m WHERE m.parentId=@parentId  and menuId=m.pkId
	if @saveMode=0
	UPDATE artDesign   SET  middleTableBorderColor=@middleTableBorderColor,middleTableBorderShow=@middleTableBorderShow,centerTopBackHeight=@centerTopBackHeight,name = @name, leftTopFrontImageVar = @leftTopFrontImageVar, leftMiddleFrontImageVar = @leftMiddleFrontImageVar, leftMiddleBackImageVar = @leftMiddleBackImageVar, leftVert = @leftVert, leftMiddleFrontWidth = @leftMiddleFrontWidth, leftMiddleFrontHeight = @leftMiddleFrontHeight, leftMiddleFrontLink = @leftMiddleFrontLink, leftBottomFrontImageVar = @leftBottomFrontImageVar, centerTopFrontImageVar = @centerTopFrontImageVar, centerTopBackImageVar = @centerTopBackImageVar, centerTopFrontWidth = @centerTopFrontWidth, centerTopFrontHeight = @centerTopFrontHeight, centerTopFrontLink = @centerTopFrontLink, centerMiddleBackImageVar = @centerMiddleBackImageVar, itemImageVar = @itemImageVar, centerBottomBackImageVar = @centerBottomBackImageVar, rightTopFrontImageVar = @rightTopFrontImageVar, rightMiddleFrontImageVar = @rightMiddleFrontImageVar, rightMiddleBackImageVar = @rightMiddleBackImageVar, rightVert = @rightVert, rightMiddleFrontWidth = @rightMiddleFrontWidth, rightMiddleFrontHeight = @rightMiddleFrontHeight, rightMiddleFrontLink = @rightMiddleFrontLink, rightBottomFrontImageVar = @rightBottomFrontImageVar, fontPosition = @fontPosition, color = @color, size = @size, space = @space, font = @font, bold = @bold, italic = @italic, topColor = @topColor, middleColor = @middleColor, bottomColor = @bottomColor, border = @border, cellPad = @cellPad, cellSpace = @cellSpace, brdrColor = @brdrColor, centerBottomFrontImageVar = @centerBottomFrontImageVar,releaseDateVar=@releaseDateVar,releaseDepartmentVar=@releaseDepartmentVar,click=@click,newImageVar=@newImageVar,hotImageVar=@hotImageVar,topImageVar=@topImageVar ,showTopMoreInfo=@showTopMoreInfo ,topMoreInfoImageVar=@topMoreInfoImageVar,	showBottomMoreInfo=@showBottomMoreInfo,bottomMoreInfoImageVar=@bottomMoreInfoImageVar	WHERE (menuId=@menuId)
	





GO





/****** 对象: 存储过程 dbo.InsertMenu    脚本日期: 2006-12-1 9:36:22 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InsertMenu]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertMenu]
GO




/* 创建过程/函数 InsertMenu;1。                                                                      */
CREATE PROCEDURE [dbo].[InsertMenu]
(
	@pkId int,
	@parentId int,
	@name varchar(100),
	@remark varchar(100)=null,
	@ordering int=0,
	@address varchar(100)=null,
	@bigClassId int=0,
	@modeId int,
	@gradeId int,
	@gate int=0,
	@recorderNum int=7,
	@menuPic varchar(100),
	@itemId varchar(100),
	@layoutTemplateId int,
	@adminId int,
	@displayRoot int,
	@userIntercept int,
	@interceptNums int
)
AS
	SET NOCOUNT OFF;
 BEGIN TRANSACTION
INSERT INTO menu(parentId, [name], remark, ordering, address, bigClassId, modeId, gradeId,gate,recorderNum,menuPic,itemId,layoutTemplateId,adminId,displayRoot, userIntercept,interceptNums) VALUES (@parentId, @name, @remark, @ordering, @address,@bigClassId, @modeId, @gradeId,@gate,@recorderNum,@menuPic,@itemId,@layoutTemplateId,@adminId,@displayRoot,@userIntercept,@interceptNums)
declare @menuId int
select @menuId=@@IDENTITY
declare @n varchar(100)
select @n=[name] from mode where pkId=@modeId
if(@n='频道')
begin
select @address='index.aspx?pkId='+convert(varchar,@menuId)
update menu set address=@address where pkId=@menuId
end
if(@n='栏目')
begin
     insert into artDesign([name],menuId,leftVert,rightVert,fontPosition,color,[size],[space],font,bold,italic,topColor,middleColor,bottomColor,border,cellPad,cellSpace,brdrColor,leftMiddleFrontWidth,leftMiddleFrontHeight,centerTopFrontWidth,centerTopFrontHeight,rightMiddleFrontWidth,rightMiddleFrontHeight,centerTopBackHeight,middleTableBorderColor,bottomMoreInfoImageVar) values(@name,@menuId,'middle','middle','left','#000000',3,2,'宋体',0,0,'','','',0,0,0,'',20,20,20,20,20,20,25,'','more.gif')
     select @layoutTemplateId=pkId 
        from layoutTemplate
        where layoutTemplateType=1 
          and isSystem=1
     update menu set layoutTemplateId=@layoutTemplateId where pkId=@menuId
  end
 COMMIT TRANSACTION





GO





/****** 对象: 存储过程 dbo.UpdateMenu    脚本日期: 2006-12-1 9:36:22 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateMenu]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateMenu]
GO




/* 创建过程/函数 UpdateMenu;1。                                                                      */
CREATE PROCEDURE [dbo].[UpdateMenu]
(
	@parentId int,
	@name varchar(100),
	@remark varchar(100),
	@ordering int,
	@address varchar(100),
	@bigClassId int,
	@modeId int,
	@gradeId int,
	@pkId int,
	@gate int,
	@recorderNum int,
	@menuPic varchar(100),
	@itemId  varchar(100),
	@layoutTemplateId int,
	@adminId int,
	@displayRoot int,
	@userIntercept int,
	@interceptNums int
)
AS
	SET NOCOUNT OFF;
 BEGIN TRANSACTION
declare @n varchar(100)
select @n=[name] from mode where pkId=@modeId
if(@n='频道')
begin
select @address='index.aspx?pkId='+convert(varchar,@pkId)
end
UPDATE menu SET interceptNums=@interceptNums,userIntercept=@userIntercept, displayRoot=@displayRoot,parentId = @parentId, name = @name, remark = @remark, ordering = @ordering, address = @address, bigClassId = @bigClassId, modeId = @modeId, gradeId = @gradeId,gate=@gate ,recorderNum=@recorderNum,menuPic=@menuPic,itemId=@itemId,layoutTemplateId=@layoutTemplateId ,adminId=@adminId WHERE (pkId = @pkId)
 COMMIT TRANSACTION





GO





/****** 对象: 存储过程 dbo.GetShowItem    脚本日期: 2006-12-1 9:36:22 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetShowItem]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetShowItem]
GO




/* 创建过程/函数 GetShowItem;1。                                                                     */
CREATE PROCEDURE [dbo].[GetShowItem]
@gradeId int=1,
@parentId int,
@gateId int
/*
	(
		@parameter1 datatype = default value,
		@parameter2 datatype OUTPUT
	)
*/
AS
	/* SET NOCOUNT ON */
	select m.pkId,m.name,m.itemId,m.bigClassId 'bigClassId',m.gate,m.userIntercept,m.interceptNums,ordering,m.recorderNum,m.menuPic,m.gradeId,t.code,m.layoutTemplateId
	  from menu m,bigClass b,template t
	  where m.parentId=@parentId
	    and m.gate<>@gateId
	    and m.gradeId<=@gradeId
	    and m.bigClassId=b.pkId
	    and b.templateId=t.pkId
	order by m.pkId desc,ordering desc
	RETURN





GO





/****** 对象: 存储过程 dbo.GetMenuById    脚本日期: 2006-12-1 9:36:22 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetMenuById]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetMenuById]
GO





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




GO





/****** 对象: 存储过程 dbo.GetNewsByAll    脚本日期: 2006-12-1 9:36:22 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetNewsByAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetNewsByAll]
GO



CREATE PROCEDURE [dbo].[GetNewsByAll]
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeId int,
	@searchStr varchar(100),
	@gateId int,
	@bigClassId int
	
AS	
	--转换成索引
	SELECT @currentPage = @currentPage - 1
	
	--初始化查询条件(不带where)
	DECLARE @str nvarchar(1000)
	SET @str = ' 1=1 '
	SET @searchStr = '%'+@searchStr+'%'
	IF (@bigClassId <> 0)
		SET @str = @str + ' and bigClassId='+STR(@bigClassId)
	
	SET @str = @str + ' and (content like ''' + @searchStr + ''''
	SET @str = @str + ' or author like ''' + @searchStr + ''''
	SET @str = @str + ' or listTitle like ''' + @searchStr + ''''
	SET @str = @str + ' or intactTitle like ''' + @searchStr + ''''
	SET @str = @str + ' or subheading like ''' + @searchStr + ''''
	SET @str = @str + ') and passed=1 and isDuplicated<>1 and gradeId<='+STR(@gradeId)
	SET @str = @str + ' and gate<>'+STR(@gateId)

	--设置查询表名称
	DECLARE @tableName nvarchar(50)
	SET @tableName = 'news'

	--设置查询排序（不带order by）
	DECLARE @orderTypeA nvarchar(100)
	DECLARE @orderTypeD nvarchar(100)
	SET @orderTypeA = 'isTop ASC, pkId ASC'
	SET @orderTypeD = 'isTop DESC, pkId DESC'
	
	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int, @recordCount int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT @count = Count(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) + ' * 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) + ' *  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 ) + ' * 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 ) + ' * 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





/****** 对象: 存储过程 dbo.GetNewsByTitle    脚本日期: 2006-12-1 9:36:22 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetNewsByTitle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetNewsByTitle]
GO






CREATE PROCEDURE [dbo].[GetNewsByTitle]
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeId int,
	@title varchar(100),
	@gateId int,
	@bigClassId int
	
AS	
	--转换成索引
	SELECT @currentPage = @currentPage - 1
	
	--初始化查询条件(不带where)
	DECLARE @str nvarchar(1000)
	SET @str = ' 1=1 '
	SET @title = '%'+@title+'%'
	IF (@bigClassId <> 0)
		SET @str = @str + ' and bigClassId='+STR(@bigClassId)
	
	SET @str = @str + ' and (listTitle like ''' + @title + ''''
	SET @str = @str + ' or intactTitle like ''' + @title + ''''
	SET @str = @str + ' or subheading like ''' + @title + ''''
	SET @str = @str + ') and passed=1 and isDuplicated<>1 and gradeId<='+STR(@gradeId)
	SET @str = @str + ' and gate<>'+STR(@gateId)

	--设置查询表名称
	DECLARE @tableName nvarchar(50)
	SET @tableName = 'news'

	--设置查询排序（不带order by）
	DECLARE @orderTypeA nvarchar(100)
	DECLARE @orderTypeD nvarchar(100)
	SET @orderTypeA = 'isTop ASC, pkId ASC'
	SET @orderTypeD = 'isTop DESC, pkId DESC'
	
	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int,@recordCount int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT @count = Count(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) + ' * 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) + ' *  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 ) + ' * 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 ) + ' * 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





/****** 对象: 存储过程 dbo.GetNewsByAuthor    脚本日期: 2006-12-1 9:36:22 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetNewsByAuthor]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetNewsByAuthor]
GO







CREATE PROCEDURE [dbo].[GetNewsByAuthor]
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeId int,
	@author varchar(100),
	@gateId int,
	@bigClassId int
	
AS	
	--转换成索引
	SELECT @currentPage = @currentPage - 1
	
	--初始化查询条件(不带where)
	DECLARE @str nvarchar(1000)
	SET @str = ' 1=1 '
	SET @author = '%'+@author+'%'
	IF (@bigClassId <> 0)
		SET @str = @str + ' and bigClassId='+STR(@bigClassId)
	
	SET @str = @str + ' and author like ''' + @author + ''''
	SET @str = @str + ' and passed=1 and isDuplicated<>1 and gradeId<='+STR(@gradeId)
	SET @str = @str + ' and gate<>'+STR(@gateId)

	--设置查询表名称
	DECLARE @tableName nvarchar(50)
	SET @tableName = 'news'

	--设置查询排序（不带order by）
	DECLARE @orderTypeA nvarchar(100)
	DECLARE @orderTypeD nvarchar(100)
	SET @orderTypeA = 'isTop ASC, pkId ASC'
	SET @orderTypeD = 'isTop DESC, pkId DESC'
	
	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int,@recordCount int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT @count = Count(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) + ' * 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) + ' *  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 ) + ' * 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 ) + ' * 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





/****** 对象: 存储过程 dbo.GetNewsByAuthor    脚本日期: 2006-12-1 9:36:22 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetNewsByAuthor]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetNewsByAuthor]
GO







CREATE PROCEDURE [dbo].[GetNewsByAuthor]
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeId int,
	@author varchar(100),
	@gateId int,
	@bigClassId int
	
AS	
	--转换成索引
	SELECT @currentPage = @currentPage - 1
	
	--初始化查询条件(不带where)
	DECLARE @str nvarchar(1000)
	SET @str = ' 1=1 '
	SET @author = '%'+@author+'%'
	IF (@bigClassId <> 0)
		SET @str = @str + ' and bigClassId='+STR(@bigClassId)
	
	SET @str = @str + ' and author like ''' + @author + ''''
	SET @str = @str + ' and passed=1 and isDuplicated<>1 and gradeId<='+STR(@gradeId)
	SET @str = @str + ' and gate<>'+STR(@gateId)

	--设置查询表名称
	DECLARE @tableName nvarchar(50)
	SET @tableName = 'news'

	--设置查询排序（不带order by）
	DECLARE @orderTypeA nvarchar(100)
	DECLARE @orderTypeD nvarchar(100)
	SET @orderTypeA = 'isTop ASC, pkId ASC'
	SET @orderTypeD = 'isTop DESC, pkId DESC'
	
	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int,@recordCount int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT @count = Count(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) + ' * 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) + ' *  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 ) + ' * 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 ) + ' * 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





/****** 对象: 存储过程 dbo.UpdateBackupInfo    脚本日期: 2006-12-1 9:36:22 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateBackupInfo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateBackupInfo]
GO



/* 创建过程/函数 UpdateBackupInfo;1。                                                                */
CREATE PROCEDURE [dbo].[UpdateBackupInfo]
(
	@name varchar(100),
	@zipName varchar(100),
	@updateTime datetime,
	@isSystem int,
	@pkId int,
	@remark varchar(1000),
	@type int
)
AS
	SET NOCOUNT OFF;
UPDATE BackupInfo SET name = @name, zipName = @zipName, updateTime = @updateTime, isSystem = @isSystem, remark=@remark,type=@type WHERE (pkId = @pkId)




GO





/****** 对象: 存储过程 dbo.InsertBackupInfo    脚本日期: 2006-12-1 9:36:22 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InsertBackupInfo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertBackupInfo]
GO



/* 创建过程/函数 InsertBackupInfo;1。                                                                */
CREATE PROCEDURE [dbo].[InsertBackupInfo]
(
	@name varchar(100),
	@zipName varchar(100),
	@updateTime datetime,
	@isSystem int,
	@pkId int,
	@remark varchar(1000),
	@type int
)
AS
	SET NOCOUNT OFF;
INSERT INTO BackupInfo(name, zipName, updateTime, isSystem,remark,type) VALUES (@name, @zipName, @updateTime, @isSystem,@remark,@type)




GO





/****** 对象: 存储过程 dbo.InsertBoard    脚本日期: 2006-12-1 9:36:22 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InsertBoard]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertBoard]
GO






/* 创建过程/函数 InsertBoard;1。                                                                     */
CREATE PROCEDURE [dbo].[InsertBoard]
(
	@pkId int output,
	@name varchar(100),
	@boardContent ntext,
	@IsTop int,
	@showDay int,
	@gate int,
	@gradeId int,
	@userId int,
	@updateIp varchar(100),
	@updateTime datetime,
	@passed int,
	@ordering int,
	@bigClassId int,
	@isDuplicated int=0,
	@duplicateFrom varchar(100),
	@duplicatedPkId int
)
AS
	SET NOCOUNT OFF;
	 BEGIN TRANSACTION
INSERT INTO board(name,ordering,bigClassId,boardContent, IsTop, showDay, gate, gradeId, userId, updateIp, updateTime, passed, isDuplicated, duplicateFrom, duplicatedPkId) VALUES (@name,@ordering,@bigClassId,@boardContent, @IsTop, @showDay, @gate, @gradeId, @userId, @updateIp, @updateTime, @passed, @isDuplicated, @duplicateFrom, @duplicatedPkId)
SET @pkId = @@IDENTITY
  COMMIT TRANSACTION







GO





/****** 对象: 存储过程 dbo.UpdateBoard    脚本日期: 2006-12-1 9:36:22 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateBoard]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateBoard]
GO




/* 创建过程/函数 UpdateBoard;1。                                                                     */
CREATE PROCEDURE [dbo].[UpdateBoard]
(
	@boardContent ntext,
	@IsTop int,
	@showDay int,
	@gate int,
	@gradeId int,
	@userId int,
	@updateIp varchar(100),
	@updateTime datetime,
	@passed int,
	@pkId int,
	@name varchar(100),
	@bigClassId int,
	@ordering int,
	@isDuplicated int=0,
	@duplicateFrom varchar(100),
	@duplicatedPkId int
)
AS
	SET NOCOUNT OFF;
UPDATE board SET bigClassId=@bigClassId,name=@name,ordering=@ordering,boardContent = @boardContent, IsTop = @IsTop, showDay = @showDay, gate = @gate, gradeId = @gradeId, userId = @userId, updateIp = @updateIp, updateTime = @updateTime, passed = @passed, isDuplicated = @isDuplicated, duplicateFrom = @duplicateFrom, duplicatedPkId = @duplicatedPkId WHERE (pkId = @pkId)





GO





/****** 对象: 存储过程 dbo.InsertNews    脚本日期: 2006-12-1 9:36:23 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InsertNews]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertNews]
GO





/* 创建过程/函数 InsertNews;1。                                                                      */
CREATE PROCEDURE [dbo].[InsertNews]
(
	@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
)
AS
	SET NOCOUNT OFF;	 BEGIN TRANSACTION
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
  COMMIT TRANSACTION






GO





/****** 对象: 存储过程 dbo.UpdateNews    脚本日期: 2006-12-1 9:36:23 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateNews]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateNews]
GO




/* 创建过程/函数 UpdateNews;1。                                                                      */
CREATE PROCEDURE [dbo].[UpdateNews]
(
	@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
)
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)





GO





/****** 对象: 存储过程 dbo.DeleteBoard    脚本日期: 2006-12-1 9:36:23 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DeleteBoard]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DeleteBoard]
GO




/* 创建过程/函数 DeleteBoard;1。                                                                     */
CREATE PROCEDURE [dbo].[DeleteBoard]
(
	@pkId int
)
AS
	SET NOCOUNT OFF;
DELETE FROM board WHERE (duplicatedPkId = @pkId) and (duplicateFrom='board')
DELETE FROM board WHERE (pkId = @pkId)





GO





/****** 对象: 存储过程 dbo.DeleteNews    脚本日期: 2006-12-1 9:36:23 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DeleteNews]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DeleteNews]
GO





/* 创建过程/函数 DeleteNews;1。                                                                      */
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)
  COMMIT TRANSACTION






GO





/****** 对象: 存储过程 dbo.GetDuplicateBigClassByUserIdAndTemplateId    脚本日期: 2006-12-1 9:36:23 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetDuplicateBigClassByUserIdAndTemplateId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetDuplicateBigClassByUserIdAndTemplateId]
GO


CREATE PROCEDURE [dbo].[GetDuplicateBigClassByUserIdAndTemplateId]
(
	@userId int,
	@templateId int
)
AS
	select distinct b.pkId, b.name, b.templateId as templateId, t.name as templateName from bigClass b, moduleAssign m, users u, menu e, template t
	where m.usersid = u.pkId and m.menuId=e.pkId and e.bigClassId=b.pkId and b.templateId = t.pkId 
		and b.templateId=@templateId and u.pkId = @userId
	order by b.pkId



GO





/****** 对象: 存储过程 dbo.DeleteBigClass    脚本日期: 2006-12-1 9:36:23 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DeleteBigClass]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DeleteBigClass]
GO



/* 创建过程/函数 DeleteBigClass;1。                                                                  */
CREATE PROCEDURE [dbo].[DeleteBigClass]
(
	@pkId int
)
AS
SET NOCOUNT OFF;
 BEGIN TRANSACTION
delete  from moduleAssign
  where menuId in (select m.pkId from menu m where @pkId=bigClassId)
  delete from menu
   where @pkId=bigClassId

  delete from SignUpBuildField where TableName=(select TableName from SignUpRelation where BigClassId=@pkId)
  delete from SignUpRelation where BigClassId = @pkId
  
  delete from bigclass
   where @pkId=pkId
  
  COMMIT TRANSACTION


GO





/****** 对象: 存储过程 dbo.GetNewsByAll    脚本日期: 2006-12-1 9:36:23 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetNewsByAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetNewsByAll]
GO



CREATE PROCEDURE [dbo].[GetNewsByAll]
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeId int,
	@searchStr varchar(100),
	@gateId int,
	@bigClassId int
	
AS	
	--转换成索引
	SELECT @currentPage = @currentPage - 1
	
	--初始化查询条件(不带where)
	DECLARE @str nvarchar(1000)
	SET @str = ' 1=1 '
	SET @searchStr = '%'+@searchStr+'%'
	IF (@bigClassId <> 0)
		SET @str = @str + ' and bigClassId='+STR(@bigClassId)
	
	SET @str = @str + ' and (content like ''' + @searchStr + ''''
	SET @str = @str + ' or author like ''' + @searchStr + ''''
	SET @str = @str + ' or listTitle like ''' + @searchStr + ''''
	SET @str = @str + ' or intactTitle like ''' + @searchStr + ''''
	SET @str = @str + ' or subheading like ''' + @searchStr + ''''
	SET @str = @str + ') and passed=1 and isDuplicated<>1 and gradeId<='+STR(@gradeId)
	SET @str = @str + ' and gate<>'+STR(@gateId)

	--设置查询表名称
	DECLARE @tableName nvarchar(50)
	SET @tableName = 'news'

	--设置查询排序（不带order by）
	DECLARE @orderTypeA nvarchar(100)
	DECLARE @orderTypeD nvarchar(100)
	SET @orderTypeA = 'isTop ASC, pkId ASC'
	SET @orderTypeD = 'isTop DESC, pkId DESC'
	
	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int, @recordCount int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT @count = Count(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) + ' * 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) + ' *  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 ) + ' * 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 ) + ' * 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





/****** 对象: 存储过程 dbo.GetNewsByAuthor    脚本日期: 2006-12-1 9:36:23 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetNewsByAuthor]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetNewsByAuthor]
GO







CREATE PROCEDURE [dbo].[GetNewsByAuthor]
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeId int,
	@author varchar(100),
	@gateId int,
	@bigClassId int
	
AS	
	--转换成索引
	SELECT @currentPage = @currentPage - 1
	
	--初始化查询条件(不带where)
	DECLARE @str nvarchar(1000)
	SET @str = ' 1=1 '
	SET @author = '%'+@author+'%'
	IF (@bigClassId <> 0)
		SET @str = @str + ' and bigClassId='+STR(@bigClassId)
	
	SET @str = @str + ' and author like ''' + @author + ''''
	SET @str = @str + ' and passed=1 and isDuplicated<>1 and gradeId<='+STR(@gradeId)
	SET @str = @str + ' and gate<>'+STR(@gateId)

	--设置查询表名称
	DECLARE @tableName nvarchar(50)
	SET @tableName = 'news'

	--设置查询排序（不带order by）
	DECLARE @orderTypeA nvarchar(100)
	DECLARE @orderTypeD nvarchar(100)
	SET @orderTypeA = 'isTop ASC, pkId ASC'
	SET @orderTypeD = 'isTop DESC, pkId DESC'
	
	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int,@recordCount int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT @count = Count(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) + ' * 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) + ' *  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 ) + ' * 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 ) + ' * 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





/****** 对象: 存储过程 dbo.GetNewsByContent    脚本日期: 2006-12-1 9:36:23 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetNewsByContent]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetNewsByContent]
GO





CREATE PROCEDURE [dbo].[GetNewsByContent]
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeId int,
	@content varchar(100),
	@gateId int,
	@bigClassId int
	
AS	
	--转换成索引
	SELECT @currentPage = @currentPage - 1
	
	--初始化查询条件(不带where)
	DECLARE @str nvarchar(1000)
	SET  @str = ' 1=1 '
	SET @content = '%'+@content+'%'
	IF (@bigClassId <> 0)
		SET @str = @str + ' and bigClassId='+STR(@bigClassId)
		
	SET @str = @str + ' and content like ''' + @content + ''''
	SET @str = @str + ' and passed=1 and isDuplicated<>1 and gradeId<='+STR(@gradeId)
	SET @str = @str + ' and gate<>'+STR(@gateId)

	--设置查询表名称
	DECLARE @tableName nvarchar(50)
	SET @tableName = 'news'

	--设置查询排序（不带order by）
	DECLARE @orderTypeA nvarchar(100)
	DECLARE @orderTypeD nvarchar(100)
	SET @orderTypeA = 'isTop ASC, pkId ASC'
	SET @orderTypeD = 'isTop DESC, pkId DESC'
	
	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int,@recordCount int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT @count = Count(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) + ' * 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) + ' *  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 ) + ' * 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 ) + ' * 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





/****** 对象: 存储过程 dbo.GetNewsByHot    脚本日期: 2006-12-1 9:36:23 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetNewsByHot]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetNewsByHot]
GO



/* 创建过程/函数 GetNewsByHot;1。                                                                    */
CREATE PROCEDURE [dbo].[GetNewsByHot]
@gradeId int =1,
@gateId int
/*
	(
		@parameter1 datatype = default value,
		@parameter2 datatype OUTPUT
	)
*/
AS
	 SET NOCOUNT ON 
	select top 15 *
	from news
	where passed =1
	  and @gradeId=gradeId
	  and gate<>@gateId and isDuplicated<>1
	order by click desc
	RETURN 




GO





/****** 对象: 存储过程 dbo.GetNewsBySpecialAndpassed    脚本日期: 2006-12-1 9:36:23 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetNewsBySpecialAndpassed]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetNewsBySpecialAndpassed]
GO



/* 创建过程/函数 GetNewsBySpecialAndpassed;1。                                                       */
CREATE PROCEDURE [dbo].[GetNewsBySpecialAndpassed]
@special int=null,
@currentPage int=1,
@pageSize int=5,
@gradeid int=1,
@passed int=1,
@gateId int
/*
	(
		@parameter1 datatype = default value,
		@parameter2 datatype OUTPUT
	)
*/
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  (specialId='+Convert(varchar,@special)
	+') and n.usersId=u.pkId and n.isDuplicated<>1 and n.departmentId=d.pkId 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 (specialId='
	+Convert(varchar,@special)+')  and gradeId<='+convert(varchar,@gradeId)+' and isDuplicated<>1 and passed='
	+convert(varchar,@passed)+' order by istop desc,pkId desc)  order by istop desc,n.pkId desc'
Execute (@vSQL)
	
	RETURN 




GO





/****** 对象: 存储过程 dbo.GetNewsByRecommand    脚本日期: 2006-12-1 9:36:23 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetNewsByRecommand]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetNewsByRecommand]
GO




/* 创建过程/函数 GetNewsByRecommand;1。                                                              */
CREATE PROCEDURE [dbo].[GetNewsByRecommand]
@pageSize int=5,
@gradeid int=1,
@gateId int
/*
	(
		@parameter1 datatype = default value,
		@parameter2 datatype OUTPUT
	)
*/
AS
SET NOCOUNT ON 
	
	declare @vSQL varchar(1000)
select @vSQL ='select top ' + convert(varchar, @pageSize) + ' pkId,listTitle,intactTitle,updateTime,imageName,isImageNews,width,height,istop,click from news where   gate<>'+Convert(varchar,@gateId)+' and  IsImageNews=0 and isDuplicated<>1 and gradeId<='+convert(varchar,@gradeId)+' and passed=1  and isRecommand=1  order by istop desc,pkId desc'
Execute (@vSQL)
RETURN





GO





/****** 对象: 存储过程 dbo.GetNewsByTitle    脚本日期: 2006-12-1 9:36:23 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetNewsByTitle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetNewsByTitle]
GO






CREATE PROCEDURE [dbo].[GetNewsByTitle]
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeId int,
	@title varchar(100),
	@gateId int,
	@bigClassId int
	
AS	
	--转换成索引
	SELECT @currentPage = @currentPage - 1
	
	--初始化查询条件(不带where)
	DECLARE @str nvarchar(1000)
	SET @str = ' 1=1 '
	SET @title = '%'+@title+'%'
	IF (@bigClassId <> 0)
		SET @str = @str + ' and bigClassId='+STR(@bigClassId)
	
	SET @str = @str + ' and (listTitle like ''' + @title + ''''
	SET @str = @str + ' or intactTitle like ''' + @title + ''''
	SET @str = @str + ' or subheading like ''' + @title + ''''
	SET @str = @str + ') and passed=1 and isDuplicated<>1 and gradeId<='+STR(@gradeId)
	SET @str = @str + ' and gate<>'+STR(@gateId)

	--设置查询表名称
	DECLARE @tableName nvarchar(50)
	SET @tableName = 'news'

	--设置查询排序（不带order by）
	DECLARE @orderTypeA nvarchar(100)
	DECLARE @orderTypeD nvarchar(100)
	SET @orderTypeA = 'isTop ASC, pkId ASC'
	SET @orderTypeD = 'isTop DESC, pkId DESC'
	
	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int,@recordCount int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT @count = Count(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) + ' * 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) + ' *  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 ) + ' * 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 ) + ' * 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





/****** 对象: 存储过程 dbo.GetNewsSearchCount    脚本日期: 2006-12-1 9:36:23 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetNewsSearchCount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetNewsSearchCount]
GO




CREATE PROCEDURE [dbo].[GetNewsSearchCount]
	@searchStr varchar(100),
	@bigClassId int,
	@type int,
	@gradeId int,
	@gateId int,
	@recordCount int output
AS	
	
	--初始化查询条件(不带where)
	DECLARE @str nvarchar(1000)
	SET @str = ' 1=1 '
	SET @searchStr = '%'+Convert(varchar,@searchStr)+'%'
	IF (@bigClassId <> 0)
		SET @str = ' and bigClassId='+STR(@bigClassId)
	
	IF (@type=0)
		BEGIN
			SET @str = @str + ' and (listTitle like ''' + @searchStr + ''''
			SET @str = @str + ' or intactTitle like ''' + @searchStr + ''''
			SET @str = @str + ' or subheading like ''' + @searchStr + ''''
			SET @str = @str + ' or content like ''' + @searchStr + ''''
			SET @str = @str + ' or author like ''' + @searchStr + ''')' 
		END
	IF(@type=1)
		BEGIN
			SET @str = @str + ' and (listTitle like ''' + @searchStr + ''''
			SET @str = @str + ' or intactTitle like ''' + @searchStr + ''''
			SET @str = @str + ' or subheading like ''' + @searchStr + ''')'
		END
	IF(@type=2)
		SET @str = @str + ' and author like ''' + @searchStr + ''''
	IF(@type=3)
		SET @str = @str + ' and content like ''' + @searchStr + ''''

	SET @str = @str + ' and passed=1 and isDuplicated<>1 and gradeId<='+Convert(varchar,@gradeId)
	SET @str = @str + ' and gate<>'+Convert(varchar,@gateId)

	--设置查询表名称
	DECLARE @tableName nvarchar(50)
	SET @tableName = 'news'
	
	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT @count = Count(pkId) from '+Convert(nvarchar(50),@tableName)+' where ' + Convert(nvarchar(1000),@str)
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum

	select * from news where 1=0
RETURN




GO





/****** 对象: 存储过程 dbo.GetNewsByClick    脚本日期: 2006-12-1 9:36:23 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetNewsByClick]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetNewsByClick]
GO



/* 创建过程/函数 GetNewsByClick;1。                                                                  */
CREATE PROCEDURE [dbo].[GetNewsByClick]
@pageSize int=5,
@gradeid int=1,
@gateId int
/*
	(
		@parameter1 datatype = default value,
		@parameter2 datatype OUTPUT
	)
*/
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<>1 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





/****** 对象: 存储过程 dbo.InsertArtDesignTemplate    脚本日期: 2006-12-1 9:36:23 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InsertArtDesignTemplate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertArtDesignTemplate]
GO




/* 创建过程/函数 InsertArtDesignTemplate;1。                                                         */
CREATE PROCEDURE [dbo].[InsertArtDesignTemplate]
(
	@pkId int,
	@name varchar(2000),
	@leftTopFrontImageVar varchar(100),
	@leftMiddleFrontImageVar varchar(100),
	@leftMiddleBackImageVar varchar(100),
	@leftVert varchar(100),
	@leftMiddleFrontWidth varchar(100),
	@leftMiddleFrontHeight varchar(100),
	@leftMiddleFrontLink varchar(100),
	@leftBottomFrontImageVar varchar(100),
	@centerTopFrontImageVar varchar(100),
	@centerTopBackImageVar varchar(100),
	@centerTopFrontWidth varchar(100),
	@centerTopFrontHeight varchar(100),
	@centerTopFrontLink varchar(100),
	@centerMiddleBackImageVar varchar(100),
	@itemImageVar varchar(100),
	@centerBottomBackImageVar varchar(100),
	@centerBottomFrontImageVar varchar(100),
	@rightTopFrontImageVar varchar(100),
	@rightMiddleFrontImageVar varchar(100),
	@rightMiddleBackImageVar varchar(100),
	@rightVert varchar(100),
	@rightMiddleFrontWidth varchar(100),
	@rightMiddleFrontHeight varchar(100),
	@rightMiddleFrontLink varchar(100),
	@rightBottomFrontImageVar varchar(100),
	@fontPosition varchar(100),
	@color varchar(100),
	@size varchar(100),
	@space varchar(100),
	@font varchar(100),
	@bold varchar(100),
	@italic varchar(100),
	@topColor varchar(100),
	@middleColor varchar(100),
	@bottomColor varchar(100),
	@border varchar(100),
	@cellPad varchar(100),
	@cellSpace varchar(100),
	@brdrColor varchar(100),
	@centerTopBackHeight  varchar(100),
	@middleTableBorderColor varchar(100),
	@middleTableBorderShow varchar(100),
	@releaseDateVar int,
	@releaseDepartmentVar int,
	@click int,
	@newImageVar varchar(100),
	@topImageVar varchar(100),
	@showTopMoreInfo int,
	@topMoreInfoImageVar varchar(100),
	@showBottomMoreInfo int,
	@bottomMoreInfoImageVar varchar(100),
	@hotImageVar varchar(100)
	
)
AS
	SET NOCOUNT OFF;
INSERT INTO artDesignTemplate(middleTableBorderColor,middleTableBorderShow,centerTopBackHeight,centerBottomFrontImageVar,name, leftTopFrontImageVar, leftMiddleFrontImageVar, leftMiddleBackImageVar, leftVert, leftMiddleFrontWidth, leftMiddleFrontHeight, leftMiddleFrontLink, leftBottomFrontImageVar, centerTopFrontImageVar, centerTopBackImageVar, centerTopFrontWidth, centerTopFrontHeight, centerTopFrontLink, centerMiddleBackImageVar, itemImageVar, centerBottomBackImageVar, rightTopFrontImageVar, rightMiddleFrontImageVar, rightMiddleBackImageVar, rightVert, rightMiddleFrontWidth, rightMiddleFrontHeight, rightMiddleFrontLink, rightBottomFrontImageVar, fontPosition, color, size, space, font, bold, italic, topColor, middleColor, bottomColor, border, cellPad, cellSpace, brdrColor,releaseDateVar,releaseDepartmentVar,click,newImageVar,topImageVar,showTopMoreInfo ,topMoreInfoImageVar,	showBottomMoreInfo,bottomMoreInfoImageVar,hotImageVar) VALUES (@middleTableBorderColor,@middleTableBorderShow,@centerTopBackHeight,@centerBottomFrontImageVar,@name, @leftTopFrontImageVar, @leftMiddleFrontImageVar, @leftMiddleBackImageVar, @leftVert, @leftMiddleFrontWidth, @leftMiddleFrontHeight, @leftMiddleFrontLink, @leftBottomFrontImageVar, @centerTopFrontImageVar, @centerTopBackImageVar, @centerTopFrontWidth, @centerTopFrontHeight, @centerTopFrontLink, @centerMiddleBackImageVar, @itemImageVar, @centerBottomBackImageVar, @rightTopFrontImageVar, @rightMiddleFrontImageVar, @rightMiddleBackImageVar, @rightVert, @rightMiddleFrontWidth, @rightMiddleFrontHeight, @rightMiddleFrontLink, @rightBottomFrontImageVar, @fontPosition, @color, @size, @space, @font, @bold, @italic, @topColor, @middleColor, @bottomColor, @border, @cellPad, @cellSpace, @brdrColor,@releaseDateVar,@releaseDepartmentVar,@click,@newImageVar,@topImageVar,
@showTopMoreInfo ,@topMoreInfoImageVar,	@showBottomMoreInfo,@bottomMoreInfoImageVar,@hotImageVar)





GO





/****** 对象: 存储过程 dbo.UpdateArtDesignTemplate    脚本日期: 2006-12-1 9:36:23 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateArtDesignTemplate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateArtDesignTemplate]
GO




/* 创建过程/函数 UpdateArtDesignTemplate;1。                                                         */
CREATE PROCEDURE [dbo].[UpdateArtDesignTemplate]
(
	@name varchar(2000),
	@leftTopFrontImageVar varchar(100),
	@leftMiddleFrontImageVar varchar(100),
	@leftMiddleBackImageVar varchar(100),
	@leftVert varchar(100),
	@leftMiddleFrontWidth varchar(100),
	@leftMiddleFrontHeight varchar(100),
	@leftMiddleFrontLink varchar(100),
	@leftBottomFrontImageVar varchar(100),
	@centerTopFrontImageVar varchar(100),
	@centerTopBackImageVar varchar(100),
	@centerTopFrontWidth varchar(100),
	@centerTopFrontHeight varchar(100),
	@centerTopFrontLink varchar(100),
	@centerMiddleBackImageVar varchar(100),
	@itemImageVar varchar(100),
	@centerBottomBackImageVar varchar(100),
	@centerBottomFrontImageVar varchar(100),
	@rightTopFrontImageVar varchar(100),
	@rightMiddleFrontImageVar varchar(100),
	@rightMiddleBackImageVar varchar(100),
	@rightVert varchar(100),
	@rightMiddleFrontWidth varchar(100),
	@rightMiddleFrontHeight varchar(100),
	@rightMiddleFrontLink varchar(100),
	@rightBottomFrontImageVar varchar(100),
	@fontPosition varchar(100),
	@color varchar(100),
	@size varchar(100),
	@space varchar(100),
	@font varchar(100),
	@bold varchar(100),
	@italic varchar(100),
	@topColor varchar(100),
	@middleColor varchar(100),
	@bottomColor varchar(100),
	@border varchar(100),
	@cellPad varchar(100),
	@cellSpace varchar(100),
	@brdrColor varchar(100),
	@centerTopBackHeight   varchar(100),
	@pkId int,
	@middleTableBorderColor varchar(100),
	@middleTableBorderShow varchar(100),
    @releaseDateVar int,@releaseDepartmentVar int,
	@click int,
	@newImageVar varchar(100),
	@topImageVar varchar(100),
	@showTopMoreInfo int,
	@topMoreInfoImageVar varchar(100),
	@showBottomMoreInfo int,
	@bottomMoreInfoImageVar varchar(100),
	@hotImageVar varchar(100)
	
)
AS
	SET NOCOUNT OFF;
UPDATE artDesignTemplate SET hotImageVar=@hotImageVar, middleTableBorderColor=@middleTableBorderColor,middleTableBorderShow=@middleTableBorderShow,centerTopBackHeight=@centerTopBackHeight,centerBottomFrontImageVar=@centerBottomFrontImageVar,name = @name, leftTopFrontImageVar = @leftTopFrontImageVar, leftMiddleFrontImageVar = @leftMiddleFrontImageVar, leftMiddleBackImageVar = @leftMiddleBackImageVar, leftVert = @leftVert, leftMiddleFrontWidth = @leftMiddleFrontWidth, leftMiddleFrontHeight = @leftMiddleFrontHeight, leftMiddleFrontLink = @leftMiddleFrontLink, leftBottomFrontImageVar = @leftBottomFrontImageVar, centerTopFrontImageVar = @centerTopFrontImageVar, centerTopBackImageVar = @centerTopBackImageVar, centerTopFrontWidth = @centerTopFrontWidth, centerTopFrontHeight = @centerTopFrontHeight, centerTopFrontLink = @centerTopFrontLink, centerMiddleBackImageVar = @centerMiddleBackImageVar, itemImageVar = @itemImageVar, centerBottomBackImageVar = @centerBottomBackImageVar, rightTopFrontImageVar = @rightTopFrontImageVar, rightMiddleFrontImageVar = @rightMiddleFrontImageVar, rightMiddleBackImageVar = @rightMiddleBackImageVar, rightVert = @rightVert, rightMiddleFrontWidth = @rightMiddleFrontWidth, rightMiddleFrontHeight = @rightMiddleFrontHeight, rightMiddleFrontLink = @rightMiddleFrontLink, rightBottomFrontImageVar = @rightBottomFrontImageVar, fontPosition = @fontPosition, color = @color, size = @size, space = @space, font = @font, bold = @bold, italic = @italic, topColor = @topColor, middleColor = @middleColor, bottomColor = @bottomColor, border = @border, cellPad = @cellPad, cellSpace = @cellSpace, brdrColor = @brdrColor ,releaseDateVar=@releaseDateVar ,releaseDepartmentVar=@releaseDepartmentVar,click=@click,newImageVar=@newImageVar,topImageVar=@topImageVar ,showTopMoreInfo=@showTopMoreInfo ,topMoreInfoImageVar=@topMoreInfoImageVar,	showBottomMoreInfo=@showBottomMoreInfo,bottomMoreInfoImageVar=@bottomMoreInfoImageVar WHERE (pkId = @pkId)





GO





/****** 对象: 存储过程 dbo.GetNewsByBigClassIdCount    脚本日期: 2006-12-1 9:36:23 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetNewsByBigClassIdCount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetNewsByBigClassIdCount]
GO



/* 创建过程/函数 GetNewsByBigClassIdCount;1。                                                        */
CREATE PROCEDURE [dbo].[GetNewsByBigClassIdCount]
@bigClassId int=null,
@gradeId int=1,
@gateId int=-1,
@table varchar(100)=null,
@count int output,
@isForeGround int
/*
	(
		@parameter1 datatype = default value,
		@parameter2 datatype OUTPUT
	)
*/
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 '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 '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





/****** 对象: 存储过程 dbo.GetNewsByRecommand    脚本日期: 2006-12-1 9:36:23 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetNewsByRecommand]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetNewsByRecommand]
GO




/* 创建过程/函数 GetNewsByRecommand;1。                                                              */
CREATE PROCEDURE [dbo].[GetNewsByRecommand]
@pageSize int=5,
@gradeid int=1,
@gateId int
/*
	(
		@parameter1 datatype = default value,
		@parameter2 datatype OUTPUT
	)
*/
AS
SET NOCOUNT ON 
	
	declare @vSQL varchar(1000)
select @vSQL ='select top ' + convert(varchar, @pageSize) + ' pkId,listTitle,intactTitle,updateTime,imageName,isImageNews,width,height,istop,click from news where   gate<>'+Convert(varchar,@gateId)+' and  IsImageNews=0 and isDuplicated<>1 and gradeId<='+convert(varchar,@gradeId)+' and passed=1  and isRecommand=1  order by istop desc,pkId desc'
Execute (@vSQL)
RETURN

GO


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

CREATE PROCEDURE [dbo].[UpdateAspProperty]
	@pkId int,
	@url varchar(100),
	@space int,
	@usedImagesSpace float,
	@usedBackupSpace float,
	@usedSpace float,
	@buildByAsp int
AS

SET NOCOUNT OFF;

UPDATE [dbo].[AspProperty] SET
	url = @url,
	space = @space,
	usedImagesSpace = @usedImagesSpace,
	usedBackupSpace = @usedBackupSpace,
	usedSpace = @usedSpace,
	buildByAsp = @buildByAsp
WHERE
	pkId = @pkId

GO



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


CREATE PROCEDURE [dbo].[InsertAspProperty]
	@url varchar(100),
	@space int,
	@usedImagesSpace float,
	@usedBackupSpace float,
	@usedSpace float,
	@buildByAsp int,
	@pkId int
AS

SET NOCOUNT OFF;

INSERT INTO [dbo].[AspProperty] (
	url,
	space,
	usedImagesSpace,
	usedBackupSpace,
	usedSpace,
	buildByAsp
) VALUES (
	@url,
	@space,
	@usedImagesSpace,
	@usedBackupSpace,
	@usedSpace,
	@buildByAsp
)

GO


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

CREATE PROCEDURE [dbo].[GetSignUpBuildFieldByBigClassId]
   @BigClassId int
AS
	SET NOCOUNT ON;
  select f.* from SignUpBuildField f, SignUpRelation r 
  where f.TableName = r.TableName and r.BigClassId = @BigClassId


GO


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

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

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[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 '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 '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].[GetLinkGangsByGroupId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetLinkGangsByGroupId]
GO

CREATE PROCEDURE [dbo].[GetLinkGangsByGroupId]
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeid int=1,
	@gateId int,
	@groupId int,
	@recordCount int output	-- 修改方法，返回记录数 王敬红20060404
AS	
	--转换成索引
	SELECT @currentPage = @currentPage-1	

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

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

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

	--判断分页大小，查询最后一页用
	SET @size = @recordCount%@pageSize	
	IF(@size=0)
		SET @size = @pageSize	
	
	--查询第一页
	IF (@currentPage = 0) OR (@currentPage > @pageCount-1)	
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' l.*,u.userName "userName",g.name "grade"  FROM linkGang l, users u, grade g '
					+ 'WHERE l.gradeId<=' + STR(@gradeId) + ' AND l.groupId=' + STR(@groupId) + ' AND l.gate<>' + STR(@gateId) 
					+ 'AND l.gradeId=g.pkId and l.userId=u.pkId ORDER BY l.isTop DESC, l.pkId DESC'	
	--查询最后一页
	ELSE IF @currentPage = @pageCount -1			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' l.*,u.userName "userName",g.name "grade"  FROM linkGang l, users u, grade g '
					+ 'WHERE l.gradeId<=' + STR(@gradeId) + ' AND l.groupId=' + STR(@groupId) + ' AND l.gate<>' + STR(@gateId) 
					+ 'AND l.gradeId=g.pkId and l.userId=u.pkId ORDER BY l.isTop ASC, l.pkId ASC) TempTable ORDER BY isTop DESC, pkId DESC'	
	--查询中间页后面的页
	ELSE IF @currentPage > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @currentPage ) + ' l.*,u.userName "userName",g.name "grade"  FROM linkGang l, users u, grade g '
					+ 'WHERE l.gradeId<=' + STR(@gradeId) + ' AND l.groupId=' + STR(@groupId) + ' AND l.gate<>' + STR(@gateId) 
					+ 'AND l.gradeId=g.pkId and l.userId=u.pkId ORDER BY l.isTop ASC, l.pkId ASC) TempTable ORDER BY isTop DESC, pkId DESC'
	--查询中间页前面的页
 	ELSE BEGIN							
		SET @currentPage=@currentPage+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @currentPage ) + ' l.*,u.userName "userName",g.name "grade"  FROM linkGang l, users u, grade g '
					+ 'WHERE l.gradeId<=' + STR(@gradeId) + ' AND l.groupId=' + STR(@groupId) + ' AND l.gate<>' + STR(@gateId) 
					+ 'AND l.gradeId=g.pkId and l.userId=u.pkId ORDER BY l.isTop DESC, l.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].[GetLinkGangsByGroupIdAndUserId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetLinkGangsByGroupIdAndUserId]
GO

CREATE PROCEDURE [dbo].[GetLinkGangsByGroupIdAndUserId]
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeid int=1,
	@gateId int,
	@groupId int,
	@userId int,
	@recordCount int output	-- 修改方法，返回记录数 王敬红20060404
AS	
	--转换成索引
	SELECT @currentPage = @currentPage-1	

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

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

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

	--判断分页大小，查询最后一页用
	SET @size = @recordCount%@pageSize	
	IF(@size=0)
		SET @size = @pageSize	
		
	--查询第一页
	IF (@currentPage = 0) OR (@currentPage > @pageCount-1)	
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' l.*,u.userName "userName",g.name "grade"  FROM linkGang l, users u, grade g '
					+ 'WHERE l.gradeId<=' + STR(@gradeId) + ' AND l.groupId=' + STR(@groupId) + ' AND l.userId=' + STR(@userId) 
					+ ' AND l.gate<>' + STR(@gateId) + 'AND l.gradeId=g.pkId and l.userId=u.pkId ORDER BY l.isTop DESC, l.pkId DESC'	
	--查询最后一页
	ELSE IF @currentPage = @pageCount -1	
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' l.*,u.userName "userName",g.name "grade"  FROM linkGang l, users u, grade g '
					+ 'WHERE l.gradeId<=' + STR(@gradeId) + ' AND l.groupId=' + STR(@groupId) + ' AND l.userId=' + STR(@userId) 
					+ ' AND l.gate<>' + STR(@gateId) + 'AND l.gradeId=g.pkId and l.userId=u.pkId ORDER BY l.isTop ASC, l.pkId ASC) TempTable ORDER BY isTop DESC, pkId DESC'	
	--查询中间页后面的页
	ELSE IF @currentPage > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @currentPage ) + ' l.*,u.userName "userName",g.name "grade"  FROM linkGang l, users u, grade g '
					+ 'WHERE l.gradeId<=' + STR(@gradeId) + ' AND l.groupId=' + STR(@groupId) + ' AND l.userId=' + STR(@userId) 
					+ ' AND l.gate<>' + STR(@gateId) + 'AND l.gradeId=g.pkId and l.userId=u.pkId ORDER BY l.isTop ASC, l.pkId ASC) TempTable ORDER BY isTop DESC, pkId DESC'
	--查询中间页前面的页
 	ELSE BEGIN							
		SET @currentPage=@currentPage+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @currentPage ) + ' l.*,u.userName "userName",g.name "grade"  FROM linkGang l, users u, grade g '
					+ 'WHERE l.gradeId<=' + STR(@gradeId) + ' AND l.groupId=' + STR(@groupId) + ' AND l.userId=' + STR(@userId) 
					+ ' AND l.gate<>' + STR(@gateId) + 'AND l.gradeId=g.pkId and l.userId=u.pkId ORDER BY l.isTop DESC, l.pkId DESC) TempTable1 '
					+ ' ORDER BY isTop ASC, pkId ASC) TempTable2 ORDER BY isTop DESC, pkId DESC'
	END
	--执行查询
	EXEC(@sqlStr)
RETURN

GO

ALTER PROCEDURE [dbo].[GetLinkGroupByBigClassId]
@bigClassId int=null,
@currentPage int=1,
@pageSize int=7,
@gradeid int=1,
@gateId int

AS
	SET NOCOUNT ON
	 
	
	declare @vSQL varchar(1000)
	select @vSQL = 'select top ' + convert(varchar, @pageSize) + 
   		' l.*,u.userName "userName",g.name "grade" from linkGroup l,users u,grade g where  l.gate<>'+Convert(varchar,@gateId) + 
		--' and l.isShow = 1' + 
		' and l.bigClassId=' + Convert(varchar,@bigClassId) +
		' and l.gradeId<=' + convert(varchar,@gradeId) +
		' and l.gradeId=g.pkId and l.userId=u.pkId  and l.pkId <> all (select top ' + Convert(varchar,((@currentPage-1)*@pageSize)) + ' pkId from linkGroup  where bigClassId=' + Convert(varchar,@bigClassId) + 
		' and gradeId<=' + convert(varchar,@gradeId) + ' order by ordering desc,pkId desc)  order by ordering desc,l.pkId desc'
	
	Execute (@vSQL)
	
	RETURN 

GO