SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


CREATE PROCEDURE [dbo].[GuangXiSFW_Add] 
(
	@PKID		int output,
	@Title		nvarchar(50),
	@FileName	nvarchar(50),
	@FileExtension	varchar(4),
	@JoinTime	datetime,
	@UserID		int,
	@BigClassID	int,
	@Memo		ntext,
	@Users		ntext,
	@IsOver		int
)
AS
BEGIN	
	SET NOCOUNT ON;

    insert into [GuangXiSFW] 
		(	[Title],
			[FileName],
			[FileExtension],
			[JoinTime], 
			[UserID], 
			[BigClassID],
			[Memo],
			[Users],
			[IsOver]
		) 
	values 
		(	@Title,
			@FileName,
			@FileExtension,
			@JoinTime,
			@UserID,
			@BigClassID,
			@Memo,
			@Users,
			@IsOver
		)
	select scope_identity()
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


CREATE PROCEDURE [dbo].[GuangXiSFW_Gets]	
	@PageIndex int,
	@PageSize int,
	@Condition varchar(1000),
	@Sequence char(100),
	@SequenceHelp char(100),	
	@RecordCount int output,
	@PageCount int output
AS
	DECLARE @vSQL nvarchar(2000)
	DECLARE @_where nvarchar(1000)
	DECLARE @_order nvarchar(100)
	DECLARE @_orderHelp nvarchar(100)
	DECLARE @tempNum int	
	
	IF @Condition = ''
		SET @_where = ''
	ELSE
		SET @_where = ' WHERE ' + @Condition

	IF @Sequence = ''
		BEGIN
			SET @_order = ' ORDER BY [PKID] DESC'
			SET @_orderHelp = ' ORDER BY [PKID] ASC'
		END
	ELSE
		BEGIN
			SET	@_order = ' ORDER BY ' + @Sequence
			SET @_orderHelp = ' ORDER BY ' + @SequenceHelp
		END
	
	SET @vSQL = N'SELECT @count = Count(*) FROM [GuangXiSFW]' + @_where
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output

	SELECT @RecordCount = @tempNum	
	SELECT @PageIndex = @PageIndex - 1
	SET @PageCount = (@RecordCount + @PageSize-1)/@PageSize

	DECLARE @sqlStr varchar(2000),@size int

	SET @size = @recordCount%@pageSize	
	IF(@size=0)
		SET @size = @pageSize	

	IF (@pageIndex = 0) OR (@pageIndex > @pageCount-1)	
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM [GuangXiSFW] ' + @_where + @_order
	ELSE IF @pageIndex = @pageCount -1			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' * FROM [GuangXiSFW] ' + @_where + @_orderHelp + ') TempTable ' + @_order	
	ELSE IF @pageIndex > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @pageIndex ) + ' * FROM [GuangXiSFW] ' + @_where + @_orderHelp + ') TempTable ' + @_order
	ELSE 
		BEGIN							
			SET @pageIndex=@pageIndex+1
			SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @pageIndex ) + ' * FROM [GuangXiSFW]' + @_where + @_order + ') TempTable1 ' + @_orderHelp + ' ) TempTable2 ' + @_order
		END
	EXEC(@sqlStr)
	RETURN


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


CREATE PROCEDURE [dbo].[GuangXiSFW_Update] 
(
	@PKID		int,
	@Title		nvarchar(50),
	@FileName	nvarchar(50),
	@FileExtension	varchar(4),
	@JoinTime	datetime,
	@UserID		int,
	@BigClassID	int,
	@Memo		ntext,
	@Users		ntext,
	@IsOver		int
)
AS
BEGIN	
	SET NOCOUNT ON;

    update [GuangXiSFW] 
	set	[Title]			= @Title,
		[FileName]		= @FileName,
		[FileExtension] = @FileExtension,
		[JoinTime]		= @JoinTime, 
		[UserID]		= @UserID, 
		[BigClassID]	= @BigClassID,
		[Memo]			= @Memo,
		[Users]			= @Users,
		[IsOver]		= @IsOver
	where	pkId = @PKID
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


CREATE PROCEDURE [dbo].[GetMessageBoardByBigClassIds]
(
	@bigClassIds varchar(100),
	@pageSize int
)
AS
	SET NOCOUNT ON
	DECLARE @str nvarchar(1000)
	SET @str = 'SELECT top ' + Convert(nvarchar(50),@pageSize) + ' messageBoard.*,bigClass.name as bigClassName FROM messageBoard,bigclass WHERE bigclass.pkId = messageBoard.bigClassId and messageBoard.checked=1 and messageBoard.isSecrecy =0 and patindex(''%|''+Convert(Varchar(50),messageBoard.bigClassId)+''|%'','''+Convert(varchar(100),@bigClassIds)+''')>0 order by messageBoard.updatetime desc'
	exec(@str)
	RETURN


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetFlashGameGetRank] 
	-- Add the parameters for the stored procedure here
	@gameId int,
	@topNumber int
AS
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
    DECLARE @sSql nvarchar(200)
    -- Insert statements for procedure here
    if @gameId != 0
    begin
	SET @sSql = N'select top ' + STR(@topNumber) + N' * from FlashGameScores where FlashGameScores.gameId = ' + STR(@gameId) + ' order by TotalScore desc'
	end
	else
	begin
	SET @sSql = N'select top ' + STR(@topNumber) + N' userId,userName, sum(TotalScore) as TotalScore from FlashGameScores where userId in (select userId from FlashGameScores) group by userId,userName order by TotalScore desc'    
	end
	--执行查询
	EXEC(@sSql)
RETURN


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetFlashGameGetScore]
	-- Add the parameters for the stored procedure here
	@gameId int,
	@userName nvarchar(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	select * from FlashGameScores where userName = @userName and gameId = @gameId
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetFlashGameGetRankByUserName]
	-- Add the parameters for the stored procedure here
	@userName nvarchar(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT * from FlashGameScores where TotalScore > (select top 1 TotalScore from FlashGameScores where userName = @userName)
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetFlashGameGetScoreRecordRankByUserName]
	-- Add the parameters for the stored procedure here
	@userName nvarchar(50),
	@gameId int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT * from FlashGameScoreData where gameId = @gameId and score > (select top 1 score from FlashGameScoreData where userName = @userName and gameId = @gameId)
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


CREATE PROCEDURE [dbo].[GetFlashGameGetScoreRecord]
	-- Add the parameters for the stored procedure here
	@gameId int,
	@userName nvarchar(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	select * from FlashGameScoreData where userName = @userName and gameId = @gameId
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[UpdateFlashGameAddScore] 
	-- Add the parameters for the stored procedure here
	@userId nvarchar(50),
	@userName nvarchar(50),
	@gameId int,
	@score int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	

    if(select userId from FlashGameScores where userId = @userId and gameId = @gameId) is null
       insert into FlashGameScores(userId,userName,gameId,TotalScore) values(@userId,@userName,@gameId,@score)
    else
       update FlashGameScores set TotalScore = TotalScore +@score where FlashGameScores.userId =(@userId) and FlashGameScores.userName =(@userName) and FlashGameScores.gameId =(@gameId)
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[UpdateFlashGameScoreRecord] 
	-- Add the parameters for the stored procedure here
	@userId nvarchar(50),
	@userName nvarchar(50),
	@gameId int,
	@score int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	

    if(select userId from FlashGameScoreData where userId = @userId and gameId = @gameId) is null
       insert into FlashGameScoreData(userId,userName,gameId,score) values(@userId,@userName,@gameId,@score)
    else
       update FlashGameScoreData set score = @score where FlashGameScoreData.userId =(@userId) and FlashGameScoreData.userName =(@userName) and FlashGameScoreData.gameId = @gameId and FlashGameScoreData.score <@score
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
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]
    @NewSearchbigClassId int,
    @searchStr varchar(100),
	@bigClassId int,
	@type int,
	@gradeId int,
	@gateId int,
	@recordCount int output
AS	
	--设置查询表名称
	DECLARE @tableName nvarchar(50)
	--初始化查询条件(不带where)
	DECLARE @str nvarchar(1000)
    DECLARE @sCount int,@sNum int
    DECLARE @sSql nvarchar(200)
    SET @sSQL = N'SELECT @count=Count(searchOptions.pkId) from searchOptions where bigClassId = ' + STR(@NewSearchbigClassId)
    EXEC sp_ExecuteSQL @sSQL,N'@count int output',@sNum output
    SELECT @sCount = @sNum

	SET @str = ' validDays <> 0 '
	SET @searchStr = '%'+Convert(varchar,@searchStr)+'%'
	IF (@bigClassId <> 0)
    BEGIN
        SET @tableName = 'news'
		SET @str = @str + ' and news.bigClassId='+STR(@bigClassId)
    END
    ELSE IF(@sCount > 0)
    BEGIN
        SET @tableName = 'news,searchOptions'
        SET @str = @str + ' and patindex(''%|''+' + 'Convert(Varchar(50),news.bigClassId)'+ '+''|%'',searchOptions.OptionsValue)>0 and searchOptions.bigClassId = ' +STR(@NewSearchbigClassId)
	END
    ELSE IF(@sCount = 0)
    BEGIN
        SET @tableName = 'news'
    END

    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 + ''''
	IF(@type=5)
		SET @str = @str + ' and copyFrom like ''' + @searchStr + ''''

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

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


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


Create PROCEDURE [dbo].[GetNewsByCopyFrom]
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeId int,
	@copyFrom varchar(100),
	@gateId int,
	@bigClassId int,
	@NewSearchbigClassId int
AS	
	--转换成索引
	SELECT @currentPage = @currentPage - 1
	--设置查询表名称
	DECLARE @tableName nvarchar(50)
	--初始化查询条件(不带where)
	DECLARE @str nvarchar(1000)
    DECLARE @sCount int,@sNum int
    DECLARE @sSql nvarchar(200)
    SET @sSQL = N'SELECT @count=Count(searchOptions.pkId) from searchOptions where bigClassId = ' + STR(@NewSearchbigClassId)
    EXEC sp_ExecuteSQL @sSQL,N'@count int output',@sNum output
    SELECT @sCount = @sNum

	SET @str = ' validDays <> 0 '
	SET @copyFrom = '%'+@copyFrom+'%'
	IF (@bigClassId <> 0)
    BEGIN    
        SET @tableName = 'news'
		SET @str = @str + ' and news.bigClassId='+STR(@bigClassId)
    END
	ELSE IF(@sCount > 0)
    BEGIN
        SET @tableName = 'news,searchOptions'
        SET @str = @str + ' and patindex(''%|''+' + 'Convert(Varchar(50),news.bigClassId)'+ '+''|%'',searchOptions.OptionsValue)>0 and searchOptions.bigClassId = ' +STR(@NewSearchbigClassId)
	END
    ELSE IF(@sCount = 0)
    BEGIN
        SET @tableName = 'news'
    END
	
	SET @str = @str + ' and copyFrom like ''' + @copyFrom + ''''
	SET @str = @str + ' and passed=1 and (isDuplicated<>1 or isDuplicated is null) and gradeId<='+STR(@gradeId)
	--SET @str = @str + ' and gate<>'+STR(@gateId)


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

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

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

	--判断分页大小，查询最后一页用
	SET @size = @recordCount%@pageSize	
	IF(@size=0)
		SET @size = @pageSize	
	
	--查询第一页
	IF (@currentPage = 0) OR (@currentPage > @pageCount-1)	
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' news.* FROM '+Convert(nvarchar(50),@tableName)+' where '+Convert(nvarchar(1000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeD)
	--查询最后一页
	ELSE IF @currentPage = @pageCount -1			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' news.*  FROM '+Convert(nvarchar(50),@tableName)+' where '+Convert(nvarchar(1000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable ORDER BY '+Convert(nvarchar(100),@orderTypeD_out)	
	--查询中间页后面的页
	ELSE IF @currentPage > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @currentPage ) + ' news.* FROM '+Convert(nvarchar(50),@tableName)+' where '+Convert(nvarchar(1000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable ORDER BY '+Convert(nvarchar(100),@orderTypeD_out)
	--查询中间页前面的页
 	ELSE BEGIN							
		SET @currentPage=@currentPage+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @currentPage ) + ' news.* FROM '+Convert(nvarchar(50),@tableName)+' where '+Convert(nvarchar(1000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeD)+') TempTable1 '
					+ ' ORDER BY '+Convert(nvarchar(100),@orderTypeA_out)+') TempTable2 ORDER BY '+Convert(nvarchar(100),@orderTypeD_out)
	END

	--执行查询
	EXEC(@sqlStr)

RETURN


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


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


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[SensitiveWords_Insert]	
(
	@ID		char(32),
	@Level	int,
	@Word	nvarchar(50)
)
AS
BEGIN
	
	SET NOCOUNT ON;

	INSERT INTO SensitiveWords 
	(
		[ID], [Level], [Word]
	)

	VALUES
	(
		@ID, @Level, @Word
	)

	SELECT SCOPE_IDENTITY()

END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[SensitiveWords_Update]
(
	@pkId	int,
	@ID		char(32),
	@Level	int,
	@Word	nvarchar(50)
)
AS
BEGIN
	
	SET NOCOUNT ON;

    Update SensitiveWords 
	SET [ID] = @ID,
		[Level] = @Level,
		[Word] = @Word
	WHERE pkId = @pkId
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


CREATE PROCEDURE [dbo].[GetLatestNewsByPassed]
	@pageIndex int,
	@pageSize int=5,
	@gradeid int=1,
	@passed int=1,
	@gateId int,	
	@recordCount int ,
	@pageCount int ,
	@menuId int
AS	
	DECLARE @num int
	DECLARE @condition varchar(100)
	select @num=0
	select @num = count(pkId) from BigClassAssociation where menuId = @menuId
	if(@num = 0)
		SET @condition = '1=1'
	else
		SET @condition = 'n.bigClassId in (select bigClassId from BigClassAssociation where menuId='+Convert(varchar,@menuId)+')'
	--转换成索引
	SELECT @pageIndex=@pageIndex-1	

	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int
	DECLARE @vSQL nvarchar(2000)	
	SET @vSQL = N'SELECT @count = Count(*) FROM News n,bigClass b,template t WHERE n.gate<>'+Convert(varchar,@gateId)+' and n.gradeId<='+convert(varchar,@gradeId)+' and n.validDays <> 0  and n.passed='+convert(varchar,@passed)+'  and   n.bigClassId = b.pkId and b.templateId = t.pkId and t.code!=''discuss'' and isDuplicated != 1'
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum

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

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

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

	--执行查询
	EXEC(@sqlStr)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE GetMenuIDByArtDesignTemplateID

	@ArtDesignTemplateID	int

AS
BEGIN
	
	SET NOCOUNT ON;

	SELECT PKID FROM Menu WHERE ArtDesignTemplateID = @ArtDesignTemplateID
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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



CREATE PROCEDURE [dbo].[GetNewsByBigClassIdAndPassed]
@bigClassId int=null,
@currentPage int=1,
@pageSize int=5,
@gradeid int=1,
@passed int=1,
@gateId int
AS
BEGIN

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

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

    -- Insert into our temp table
    INSERT	INTO #PageIndexs (ID)
	SELECT	n.pkId
	FROM	[News] AS n 
			INNER JOIN [Users] AS u ON n.UsersID = u.PKID
			INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
	WHERE	(n.gate > @gateId OR n.gate < @gateId) and   
			--n.usersId =			u.pkId and 
			--n.departmentId =	d.pkId and 
			bigClassId =		@bigClassId and 
			n.gradeId <=		@gradeId and 
			n.passed =			@passed and 
			(n.validDays > 0 OR n.validDays < 0)
	ORDER BY n.istop DESC, n.pkId DESC

	SELECT  n.*, u.UserName, d.name as Department
	FROM	[News] AS n
			INNER JOIN [Users] AS u ON n.UsersID = u.PKID
			INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
			INNER JOIN [#PageIndexs] AS p ON n.PKID = p.ID
	WHERE	p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
	ORDER BY n.istop DESC, n.pkId DESC

	DELETE #PageIndexs
	RETURN 
END





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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



CREATE PROCEDURE [dbo].[GetNewsByBigClassIdAndusersId]
@bigClassId int=null,
@currentPage int=1,
@pageSize int=5,
@gradeid int=1,
@usersId int,
@gateId int,
@searchStr varchar(100) = ''
AS
BEGIN

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

	DECLARE @sql nvarchar(4000)

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

    -- Insert into our temp table
    INSERT	INTO #PageIndexs (ID)
	SELECT	n.pkId
	FROM	[News] AS n 
			INNER JOIN [Users] AS u ON n.UsersID = u.PKID
			INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
			INNER JOIN [grade] AS g ON n.gradeId = g.pkId
			
	WHERE	(n.gate > ' + STR(@gateId) + ' OR n.gate < ' + STR(@gateId) + ') and   
			n.bigClassId = ' + STR(@bigClassId) +' and 
			n.usersId = '+ STR(@usersId) +' and
			n.gradeId <= ' + STR(@gradeId) + ' ' +
			@searchStr + ' ' +
	'ORDER BY n.istop DESC, n.pkId DESC;

	SELECT  n.*, u.UserName, d.name as Department,g.name as grade
	FROM	[News] AS n
			INNER JOIN [Users] AS u ON n.UsersID = u.PKID
			INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
			INNER JOIN [grade] AS g ON n.gradeId = g.pkId
			INNER JOIN [#PageIndexs] AS p ON n.PKID = p.ID
	WHERE	p.IndexId >= ' + STR(@PageLowerBound) + ' AND p.IndexId <= ' + STR(@PageUpperBound) + '
	ORDER BY n.istop DESC, n.pkId DESC

	DELETE #PageIndexs'

	EXEC(@sql)
	
	RETURN 
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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



CREATE PROCEDURE [dbo].[GetNewsByBigClassIdAndisImageNews]
@bigClassId int=null,
@currentPage int=1,
@pageSize int=5,
@gradeid int=1,
@gateId int
AS
BEGIN

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

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

    -- Insert into our temp table
    INSERT	INTO #PageIndexs (ID)
	SELECT	n.pkId
	FROM	[News] AS n 
			INNER JOIN [Users] AS u ON n.UsersID = u.PKID
			INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
			INNER JOIN [grade] AS g ON n.gradeId = g.pkId
	WHERE	(n.gate > @gateId OR n.gate < @gateId) and   
			n.bigClassId =		@bigClassId and 
			isImageNews = 1 and 
			n.gradeId <=		@gradeId 
	ORDER BY n.istop DESC, n.pkId DESC

	SELECT  n.*, u.UserName, d.name as Department,g.name as grade
	FROM	[News] AS n
			INNER JOIN [Users] AS u ON n.UsersID = u.PKID
			INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
			INNER JOIN [grade] AS g ON n.gradeId = g.pkId
			INNER JOIN [#PageIndexs] AS p ON n.PKID = p.ID
	WHERE	p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
	ORDER BY n.istop DESC, n.pkId DESC
	
	DELETE #PageIndexs
	
	RETURN 
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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



CREATE PROCEDURE [dbo].[GetNewsByBigClassIdAndisImageNewsAndPassed]
@bigClassId int=null,
@currentPage int=1,
@pageSize int=5,
@gradeid int=1,
@gateId int
AS
BEGIN

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

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

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

	SELECT  n.*, u.UserName, d.name as Department,g.name as grade,s.name as special
	FROM	[News] AS n
			INNER JOIN [Users] AS u ON n.UsersID = u.PKID
			INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
			INNER JOIN [grade] AS g ON n.gradeId = g.pkId
			INNER JOIN [special] AS s ON n.specialId = s.pkId
			INNER JOIN [#PageIndexs] AS p ON n.PKID = p.ID
	WHERE	p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
	ORDER BY n.istop DESC, n.pkId DESC
	
	DELETE #PageIndexs
	
	RETURN 
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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



CREATE PROCEDURE [dbo].[GetNewsByIsImageNews]
@bigClassId int=null,
@currentPage int=1,
@pageSize int=5,
@gradeid int=1,
@passed int=1,
@gateId int
AS
BEGIN

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

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

    -- Insert into our temp table
    INSERT	INTO #PageIndexs (ID)
	SELECT	n.pkId
	FROM	[News] AS n 

	WHERE	(n.gate > @gateId OR n.gate < @gateId) and   
			n.bigClassId =		@bigClassId and 
			isImageNews = 1 and 
			passed= @passed and 
			n.gradeId <=		@gradeId 
	ORDER BY n.istop DESC, n.pkId DESC

	SELECT  *
	FROM	[News] AS n

			INNER JOIN [#PageIndexs] AS p ON n.PKID = p.ID
	WHERE	p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
	ORDER BY n.istop DESC, n.pkId DESC
	
	DELETE #PageIndexs
	
	RETURN 
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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



CREATE PROCEDURE [dbo].[GetPeopleByBigClassId]
@bigClassId int=null,
@currentPage int=1,
@pageSize int=5,
@gradeid int=1,
@gateId int
AS
BEGIN

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

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

    -- Insert into our temp table
    INSERT	INTO #PageIndexs (ID)
	SELECT	p.pkId
	FROM	[people] AS p 
			INNER JOIN [Users] AS u ON p.userId=u.pkId
			INNER JOIN [grade] AS g ON p.gradeId=g.pkId
	WHERE	(p.gate > @gateId OR p.gate < @gateId) and   
			bigClassId =		@bigClassId and 
			p.gradeId <=		@gradeId 

	ORDER BY p.istop DESC, p.pkId DESC

	SELECT  p.*,u.userName as userName,g.name as grade
	FROM	[people] AS p 
			INNER JOIN [Users] AS u ON p.userId=u.pkId
			INNER JOIN [grade] AS g ON p.gradeId=g.pkId
			INNER JOIN [#PageIndexs] AS pp ON p.PKID = pp.ID
	WHERE	pp.IndexId >= @PageLowerBound AND pp.IndexId <= @PageUpperBound
	ORDER BY p.istop DESC, p.pkId DESC
	
	DELETE #PageIndexs
	
	RETURN 
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

CREATE PROCEDURE [dbo].[GetNewsByBigClassId]
@bigClassId int=null,
@currentPage int=1,
@pageSize int=5,
@gradeid int=1,
@gateId int,
@searchStr varchar(100) = ''
AS
BEGIN

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

	DECLARE @sql nvarchar(4000)

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

    -- Insert into our temp table
    INSERT	INTO #PageIndexs (ID)
	SELECT	n.pkId
	FROM	[News] AS n 
			INNER JOIN [Users] AS u ON n.UsersID = u.PKID
			INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
			inner join [grade] as g on n.gradeId=g.pkId
	WHERE	(n.gate > ' + STR(@gateId) + ' OR n.gate < ' + STR(@gateId) + ') and   
			n.bigClassId = ' + STR(@bigClassId) +' and 
			n.gradeId <= ' + STR(@gradeId) + ' ' +
			@searchStr + ' ' +
	'ORDER BY n.istop DESC, n.pkId DESC;

	SELECT  n.*, u.UserName, d.name as Department,g.name as grade
	FROM	[News] AS n
			INNER JOIN [Users] AS u ON n.UsersID = u.PKID
			INNER JOIN [Department] AS d ON n.DepartmentID = d.PKID
			INNER JOIN [grade] as g on n.gradeId=g.pkId
			INNER JOIN [#PageIndexs] AS p ON n.PKID = p.ID
	WHERE	p.IndexId >= ' + STR(@PageLowerBound) + ' AND p.IndexId <= ' + STR(@PageUpperBound) + '
	ORDER BY n.istop DESC, n.pkId DESC

	DELETE #PageIndexs'

	EXEC(@sql)
	
	RETURN 
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

