SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

CREATE PROCEDURE [dbo].[TasksInsert]	
	@PKID int output,
	@NO	varchar(20),
	@Title nvarchar(500),	
	@Department           nvarchar(100),
	@JoinTime             datetime,
	@ADUserName1          nvarchar(20),
	@ADUserName1Time      datetime,
	@UserID               int,
	@PassTime             datetime,
	@PassUserID           int,
	@LeadingPassUserID	  int,
	@LeadingPassTime	  datetime,	
	@OverTime             datetime,
	@OverUserID           int,
	@ResponsiblePersons   nvarchar(100),
	@RPSex                int,
	@RPNational           int,
	@RPBirthday           datetime,
	@RPPositions          int,
	@RPTitle              int,
	@RPExpertise          nvarchar(20),
	@RPEducation          int,
	@RPWorkDepartment     nvarchar(100),
	@RPAddress            nvarchar(100),
	@RPPostCode           char(6),
	@RPPhone              varchar(20),
	@RPEmail              varchar(100),
	@Participants         ntext,
	@FinalResultsExpected int,
	@RelatedResearchResults ntext,
	@ResearchUndertakens  ntext,
	@DesignVerificationIssues ntext,
	@CompletedTheFeasibilityAnalysis ntext,
	@MainResults          ntext,
	@FinalResearchResults ntext,
	@TaskApplyDepartmentViews ntext,
	@ADUserName2          nvarchar(20),
	@ADUserName2Time      datetime,
	@TotalGroupViews      ntext,
	@TotalGroupViewsUserName   nvarchar(20),
	@TotalGroupViewsUserNameTime datetime,
	@LeadingGroupViews    ntext,
	@LeadingGroupViewsUserName nvarchar(20),
	@LeadingGroupViewsUserNameTime datetime,
	@Status               int,
	@DocFile              nvarchar(1000),
	@BigClassID           int,
	@Integrity            int
AS
BEGIN
	
	SET NOCOUNT ON;

    INSERT INTO [Tasks]
(
	[NO],
	[Title],
	[Department],
	[JoinTime],
	[ADUserName1],
	[ADUserName1Time],
	[UserID],
	[PassTime],
	[PassUserID],
	[LeadingPassUserID],
	[LeadingPassTime],
	[OverTime],
	[OverUserID],
	[ResponsiblePersons],
	[RPSex],
	[RPNational],
	[RPBirthday],
	[RPPositions],
	[RPTitle],
	[RPExpertise],
	[RPEducation],
	[RPWorkDepartment],
	[RPAddress],
	[RPPostCode],
	[RPPhone],
	[RPEmail],
	[Participants],
	[FinalResultsExpected],
	[RelatedResearchResults],
	[ResearchUndertakens],
	[DesignVerificationIssues],
	[CompletedTheFeasibilityAnalysis],
	[MainResults],
	[FinalResearchResults],
	[TaskApplyDepartmentViews],
	[ADUserName2],
	[ADUserName2Time],
	[TotalGroupViews],
	[TotalGroupViewsUserName],
	[TotalGroupViewsUserNameTime],
	[LeadingGroupViews],
	[LeadingGroupViewsUserName],
	[LeadingGroupViewsUserNameTime],
	[Status],
	[DocFile],
	[BigClassID],
	[Integrity]

)
 VALUES (
   @NO,
   @Title,
   @Department,
   @JoinTime,
   @ADUserName1,
   @ADUserName1Time,
   @UserID,
   @PassTime,
   @PassUserID,
   @LeadingPassUserID,
   @LeadingPassTime,
   @OverTime,
   @OverUserID,
   @ResponsiblePersons,
   @RPSex,
   @RPNational,
   @RPBirthday,
   @RPPositions,
   @RPTitle,
   @RPExpertise,
   @RPEducation,
   @RPWorkDepartment,
   @RPAddress,
   @RPPostCode,
   @RPPhone,
   @RPEmail,
   @Participants,
   @FinalResultsExpected,
   @RelatedResearchResults,
   @ResearchUndertakens,
   @DesignVerificationIssues,
   @CompletedTheFeasibilityAnalysis,
   @MainResults,
   @FinalResearchResults,
   @TaskApplyDepartmentViews,
   @ADUserName2,
   @ADUserName2Time,
   @TotalGroupViews,
   @TotalGroupViewsUserName,
   @TotalGroupViewsUserNameTime,
   @LeadingGroupViews,
   @LeadingGroupViewsUserName,
   @LeadingGroupViewsUserNameTime,
   @Status,
   @DocFile,
   @BigClassID,
   @Integrity
)

SET @PKID = 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].[TasksList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TasksList]
GO



CREATE PROCEDURE [dbo].[TasksList]	
	@PageIndex int,
	@PageSize int,
	@Condition char(200),
	@Sequence char(100),
	@SequenceHelp char(100),	
	@RecordCount int output,
	@PageCount int output
AS
	DECLARE @vSQL nvarchar(2000)
	DECLARE @_where nvarchar(200)
	DECLARE @_order nvarchar(50)
	DECLARE @_orderHelp nvarchar(50)
	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 [Tasks]' + @_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 [Tasks] ' + @_where + @_order
	ELSE IF @pageIndex = @pageCount -1			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' * FROM [Tasks] ' + @_where + @_orderHelp + ') TempTable ' + @_order	
	ELSE IF @pageIndex > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @pageIndex ) + ' * FROM [Tasks] ' + @_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 [Tasks]' + @_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].[TasksUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TasksUpdate]
GO

CREATE PROCEDURE [dbo].[TasksUpdate]	
	@PKID int,
	@NO	varchar(20),
	@Title nvarchar(500),	
	@Department           nvarchar(100),
	@JoinTime             datetime,
	@ADUserName1          nvarchar(20),
	@ADUserName1Time      datetime,
	@UserID               int,
	@PassTime             datetime,
	@PassUserID           int,
	@LeadingPassUserID	  int,
	@LeadingPassTime	  datetime,
	@OverTime             datetime,
	@OverUserID           int,
	@ResponsiblePersons   nvarchar(100),
	@RPSex                int,
	@RPNational           int,
	@RPBirthday           datetime,
	@RPPositions          int,
	@RPTitle              int,
	@RPExpertise          nvarchar(20),
	@RPEducation          int,
	@RPWorkDepartment     nvarchar(100),
	@RPAddress            nvarchar(100),
	@RPPostCode           char(6),
	@RPPhone              varchar(20),
	@RPEmail              varchar(100),
	@Participants         ntext,
	@FinalResultsExpected int,
	@RelatedResearchResults ntext,
	@ResearchUndertakens  ntext,
	@DesignVerificationIssues ntext,
	@CompletedTheFeasibilityAnalysis ntext,
	@MainResults          ntext,
	@FinalResearchResults ntext,
	@TaskApplyDepartmentViews ntext,
	@ADUserName2          nvarchar(20),
	@ADUserName2Time      datetime,
	@TotalGroupViews      ntext,
	@TotalGroupViewsUserName   nvarchar(20),
	@TotalGroupViewsUserNameTime datetime,
	@LeadingGroupViews    ntext,
	@LeadingGroupViewsUserName nvarchar(20),
	@LeadingGroupViewsUserNameTime datetime,
	@Status               int,
	@DocFile              nvarchar(1000),
	@BigClassID           int,
	@Integrity            int
AS
BEGIN
	
	SET NOCOUNT ON;

    UPDATE [Tasks] SET 

	[NO] = @NO,
	[Title] = @Title,
	[Department] = @Department,
	[JoinTime] = @JoinTime,
	[ADUserName1] = @ADUserName1,
	[ADUserName1Time] =   @ADUserName1Time,
	[UserID] =   @UserID,
	[PassTime] =    @PassTime,
	[PassUserID] =   @PassUserID,
	[LeadingPassUserID] =	@LeadingPassUserID,
	[LeadingPassTime] =	@LeadingPassTime,
	[OverTime] =   @OverTime,
	[OverUserID] =    @OverUserID,
	[ResponsiblePersons] =   @ResponsiblePersons,
	[RPSex] =   @RPSex,
	[RPNational] =   @RPNational,
	[RPBirthday] =   @RPBirthday,
	[RPPositions] =   @RPPositions,
	[RPTitle] =    @RPTitle,
	[RPExpertise] = @RPExpertise,
	[RPEducation] = @RPEducation,
	[RPWorkDepartment] =   @RPWorkDepartment,
	[RPAddress]=   @RPAddress,
	[RPPostCode] =   @RPPostCode,
	[RPPhone] =   @RPPhone,
	[RPEmail] =   @RPEmail,
	[Participants] =   @Participants,
	[FinalResultsExpected] =   @FinalResultsExpected,
	[RelatedResearchResults] =   @RelatedResearchResults,
	[ResearchUndertakens] =   @ResearchUndertakens,
	[DesignVerificationIssues] =   @DesignVerificationIssues,
	[CompletedTheFeasibilityAnalysis] =  @CompletedTheFeasibilityAnalysis,
	[MainResults] =   @MainResults,
	[FinalResearchResults] =   @FinalResearchResults,
	[TaskApplyDepartmentViews] =   @TaskApplyDepartmentViews,
	[ADUserName2] =    @ADUserName2,
	[ADUserName2Time] =   @ADUserName2Time,
	[TotalGroupViews] =   @TotalGroupViews,
	[TotalGroupViewsUserName] =  @TotalGroupViewsUserName,
	[TotalGroupViewsUserNameTime] =   @TotalGroupViewsUserNameTime,
	[LeadingGroupViews] =   @LeadingGroupViews,
	[LeadingGroupViewsUserName] =   @LeadingGroupViewsUserName,
	[LeadingGroupViewsUserNameTime] =   @LeadingGroupViewsUserNameTime,
	[Status] =   @Status,
	[DocFile] =   @DocFile,
	[BigClassID] =   @BigClassID,
	[Integrity] =   @Integrity

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].[GetWebResearList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetWebResearList]
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetWebResearList]
	-- Add the parameters for the stored procedure here
	@bigClassPkId int,
@currentPage int=1,
@pageSize int=5,
@gradeid int=1,
@gateId 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
declare @vSQL varchar(1000)
select @vSQL = 'select top ' + convert(varchar, @pageSize) + ' *,(select count(*) from question q where q.questionnaireSpecialId=questionnaireSpecial.pkId ) as QuestionCount,layoutTemplate.name from questionnaireSpecial,layoutTemplate where ItemType = layoutTemplate.pkId and questionnaireSpecial.pkId in (select top '+Convert(varchar,((@currentPage)*@pageSize))+' pkId from questionnaireSpecial where   bigClassId = '+Convert(varchar,@bigClassPkId)+' order by pkId asc)  order by questionnaireSpecial.pkId asc'
Execute (@vSQL)
    

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].[GetWebResearListByPkId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetWebResearListByPkId]
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
create PROCEDURE [dbo].[GetWebResearListByPkId]
	-- Add the parameters for the stored procedure here
	@PkId 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 *,(select count(*)  from question where questionnaireSpecialId = questionnaireSpecial.pkId ) as QuestionCount,layoutTemplate.pkId  from questionnaireSpecial,layoutTemplate where questionnaireSpecial.pkId = @PkId and ItemType = layoutTemplate.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].[GetWebResearListUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetWebResearListUser]
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
create PROCEDURE [dbo].[GetWebResearListUser]
	-- Add the parameters for the stored procedure here
	@bigClassPkId 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  *,(select count(*)  from question where questionnaireSpecialId = questionnaireSpecial.pkId ) as QuestionCount,layoutTemplate.name  from questionnaireSpecial,layoutTemplate where bigClassId = @bigClassPkId and ItemType = layoutTemplate.pkId order by questionnaireSpecial.pkId asc

END







GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

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


CREATE PROCEDURE [dbo].[GetWebResearQuestionByBigClassId]
(@bigClassId int)
AS
SELECT q.pkId, q.questionName, q.optionsType, q.optionsCount, q.NotNull,qS.questionnaireSpecialName,q.questionnaireSpecialId FROM questionnaireSpecial qS,question q
 where @bigClassId=qS.pkId and q.questionnaireSpecialId=qS.pkId
 group by q.pkId , q.questionName , q.optionsType , q.optionsCount , q.NotNull,qS.questionnaireSpecialName,q.questionnaireSpecialId










GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

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


CREATE PROCEDURE [dbo].[GetWebResearSpecialNameByBigClassId]
(@bigClassId int)
AS
SELECT * from questionnaireSpecial
where questionnaireSpecial.pkId = (@bigClassId)









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].[InsertWebResearList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertWebResearList]
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
create PROCEDURE [dbo].[InsertWebResearList]
	-- Add the parameters for the stored procedure here
	@pkId int,
	@bigClassId int,
	@questionnaireSpecialName varchar(1000),
	@ItemType 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
	insert into questionnaireSpecial(bigClassId,questionnaireSpecialName,ItemType,validDays,gate,gradeId,IsAnonymity,restrictObject,restrictMode,restrictTime,IsVerify,generateDate,IsShow,IsShowNumber)
	values(@bigClassId,@questionnaireSpecialName,@ItemType,-1,0,1,1,2,0,1,1,getdate(),1,0)
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].[UpdateWebResearByTitle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateWebResearByTitle]
GO


CREATE PROCEDURE [dbo].[UpdateWebResearByTitle]
(@questionnaireSpecialName varchar(1000), @pkId int, @validDays int, @gate int, @gradeId int, @IsAnonymity int, @restrictObject int, @restrictMode int, @restrictTime int, @IsVerify int, @generateDate datetime, @IsShow int, @IsShowNumber int)
AS
	SET NOCOUNT OFF;
UPDATE questionnaireSpecial 
SET questionnaireSpecialName = @questionnaireSpecialName,
validDays = @validDays,
gate = @gate,
gradeId = @gradeId,
IsAnonymity = @IsAnonymity,
restrictObject = @restrictObject,
restrictMode = @restrictMode,
restrictTime = @restrictTime,
IsVerify = @IsVerify,
generateDate =@generateDate,
IsShow = @IsShow,
IsShowNumber = @IsShowNumber
 WHERE (pkId = @pkId)








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].[UpdateWebResearList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateWebResearList]
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
create PROCEDURE [dbo].[UpdateWebResearList]
	-- Add the parameters for the stored procedure here
	@pkId int,
	@bigClassId int,
	@questionnaireSpecialName varchar(1000),
	@ItemType 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
	update questionnaireSpecial set bigClassId = @bigClassId,questionnaireSpecialName=@questionnaireSpecialName,ItemType=@ItemType 
	where pkId = @pkId

END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

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


CREATE PROCEDURE [dbo].[GetWebResearTitle]
(@bigClassPkId int)
AS
select * from questionnaireSpecial
where pkId = (@bigClassPkId)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

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


CREATE PROCEDURE [dbo].[GetWebResearAnswerByQuestionId]
(@pkId int)
AS
select s.SchoolName,u.userName,u.fullname,o.optionsName,a.Message
from answer a,options o,users u,School s,SchoolUsers su
where 
(
     a.questionId = @pkId and a.optionsId=o.pkId and a.userName = u.userName and su.UserId = u.pkId and su.SchoolId = s.pkId and IsSaved != 1
)
order by u.userName asc,a.optionsId desc

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].[GetWebResearVoteByUserId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetWebResearVoteByUserId]
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetWebResearVoteByUserId]
	-- Add the parameters for the stored procedure here
	@userId int,
	@BigClassId 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 a.questionId,a.optionsId,a.Message,q.optionsType from answer a, question q,users u
    where a.questionId = q.pkId and q.questionnaireSpecialId = @BigClassId and u.userName = a.userName and u.pkId = @userId and a.questionId <> 100307 
 END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

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


CREATE PROCEDURE [dbo].[GetWebResearSummaryFirstResear]



@PageIndex int,
	@PageSize int,
	@PageCount int output,
	@RecordCount int output
AS
	SELECT @PageIndex = @PageIndex - 1
	
	DECLARE @tempNum int	
	DECLARE @vSQL nvarchar(2000)

	SET @vSQL = N'SELECT @count = Count(pkId) FROM SchoolUsers where CanVoteFirst > 0'
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @RecordCount = @tempNum

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

	DECLARE @sqlStr varchar(2000),@size int

	SET @size = @RecordCount%@PageSize	
	IF(@size=0)
		SET @size = @PageSize	

	IF (@PageIndex = 0) OR (@PageIndex > @PageCount-1)	
		SET @sqlStr = 'SELECT TOP ' + STR(@PageSize) + ' SchoolUsers.*,School.SchoolName FROM SchoolUsers,School where School.pkId = SchoolUsers.SchoolId and CanVoteFirst > 0 order by SchoolUsers.pkId asc'	
	ELSE IF @PageIndex = @PageCount -1			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' SchoolUsers.*,School.SchoolName FROM SchoolUsers,School where School.pkId = SchoolUsers.SchoolId and CanVoteFirst > 0 order by SchoolUsers.pkId desc) TempTable ORDER BY pkId ASC'	
	ELSE IF @PageIndex > @PageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@PageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@RecordCount - @PageSize * @PageIndex ) + ' SchoolUsers.*,School.SchoolName FROM SchoolUsers,School where School.pkId = SchoolUsers.SchoolId and CanVoteFirst > 0 order by SchoolUsers.pkId desc) TempTable ORDER BY pkId ASC'
	ELSE BEGIN							
		SET @PageIndex=@PageIndex+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@PageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@PageSize * @PageIndex ) + ' SchoolUsers.*,School.SchoolName FROM SchoolUsers,School where School.pkId = SchoolUsers.SchoolId and CanVoteFirst > 0 order by SchoolUsers.pkId asc) TempTable1 '
					+ ' ORDER BY pkId DESC) TempTable2 ORDER BY pkId ASC'
	END
	EXEC(@sqlStr)
	RETURN


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

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


CREATE PROCEDURE [dbo].[GetWebResearSummarySecondResear]



@PageIndex int,
	@PageSize int,
	@PageCount int output,
	@RecordCount int output
AS
	SELECT @PageIndex = @PageIndex - 1
	
	DECLARE @tempNum int	
	DECLARE @vSQL nvarchar(2000)

	SET @vSQL = N'SELECT @count = Count(pkId) FROM SchoolUsers where CanVoteSecond > 0'
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @RecordCount = @tempNum

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

	DECLARE @sqlStr varchar(2000),@size int

	SET @size = @RecordCount%@PageSize	
	IF(@size=0)
		SET @size = @PageSize	

	IF (@PageIndex = 0) OR (@PageIndex > @PageCount-1)	
		SET @sqlStr = 'SELECT TOP ' + STR(@PageSize) + ' SchoolUsers.*,School.SchoolName FROM SchoolUsers,School where School.pkId = SchoolUsers.SchoolId and CanVoteSecond > 0 order by SchoolUsers.pkId asc'	
	ELSE IF @PageIndex = @PageCount -1			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' SchoolUsers.*,School.SchoolName FROM SchoolUsers,School where School.pkId = SchoolUsers.SchoolId and CanVoteSecond > 0 order by SchoolUsers.pkId desc) TempTable ORDER BY pkId ASC'	
	ELSE IF @PageIndex > @PageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@PageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@RecordCount - @PageSize * @PageIndex ) + ' SchoolUsers.*,School.SchoolName FROM SchoolUsers,School where School.pkId = SchoolUsers.SchoolId and CanVoteSecond > 0 order by SchoolUsers.pkId desc) TempTable ORDER BY pkId ASC'
	ELSE BEGIN							
		SET @PageIndex=@PageIndex+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@PageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@PageSize * @PageIndex ) + ' SchoolUsers.*,School.SchoolName FROM SchoolUsers,School where School.pkId = SchoolUsers.SchoolId and CanVoteSecond > 0 order by SchoolUsers.pkId asc) TempTable1 '
					+ ' ORDER BY pkId DESC) TempTable2 ORDER BY pkId ASC'
	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].[GetWebResearVoteSummaryNum]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetWebResearVoteSummaryNum]
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetWebResearVoteSummaryNum]
	-- Add the parameters for the stored procedure here
	@BigClassId int
	--@NumOptions nvarchar(4000)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
    select optionsId,sum(convert(float,convert(nvarchar(8),Message))) as sum,sum(convert(float,convert(nvarchar(8),Message)))/count(*) as average 
    from (select answer.* from answer,question,options where answer.optionsId = options.pkId and options.questionId = question.pkId and question.questionnaireSpecialId = @BigClassId and ISNUMERIC(convert(nvarchar(8),Message)) = 1 and answer.IsSaved = 0 and question.pkId <> 53 and question.pkId <> 355 and question.pkId <> 358 and question.pkId <> 360 and question.pkId <> 100307 and question.pkId <> 100310 and question.pkId <> 100314 and question.pkId <> 100315 and question.pkId <> 100317 and question.pkId <> 100319 and question.pkId <> 100321 and question.pkId <> 100324 and options.pkId <> 1001350 and options.pkId <> 1001356) as b group by optionsId 

    -- Insert statements for procedure here
 
 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].[GetWebResearVoteSummaryCheck]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetWebResearVoteSummaryCheck]
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetWebResearVoteSummaryCheck]
	-- Add the parameters for the stored procedure here
	@BigClassId int
	--@CheckOptions nvarchar(4000)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
    
select convert(float,oc)/convert(float,qc) as per,optionsId,a.questionId 
from ((select count(*) as oc,answer.optionsId,answer.questionId 
from answer,options,question 
where answer.optionsId = options.pkId and options.questionId = question.pkId and question.questionnaireSpecialId = @BigClassId and answer.IsSaved = 0 
group by answer.optionsId,answer.questionId ) as a
   inner join
( select count(*) as qc,answer.questionId 
from answer,options,question 
where answer.optionsId = options.pkId and options.questionId = question.pkId and question.questionnaireSpecialId = @BigClassId and answer.IsSaved = 0
group by answer.questionId ) as b
on a.questionId = b.questionId)
    -- Insert statements for procedure here
 
 
 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].[GetWebResearSchoolUserDetails]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetWebResearSchoolUserDetails]
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetWebResearSchoolUserDetails]
	-- Add the parameters for the stored procedure here
	@UserId 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 SchoolUsers.*, School.SchoolName from SchoolUsers,School where School.pkId = SchoolUsers.SchoolId and SchoolUsers.UserId = @UserId
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].[InsertWebResearAnswerForSave]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertWebResearAnswerForSave]
GO

CREATE PROCEDURE [dbo].[InsertWebResearAnswerForSave]
(@pkId int, @questionId int, @optionsId int, @userId int, @IP bigint, @date datetime, @Message text, @userName varchar(100))
AS
insert into answer(questionId,optionsId,userId,IP,date,Message,userName,IsSaved)
values(@questionId,@optionsId,@userId,@IP,@date,@Message,@userName,1)
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].[UpdateWebResearState]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateWebResearState]
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE UpdateWebResearState
	-- Add the parameters for the stored procedure here
	@ResearName nvarchar(20),
	@UserName varchar(100)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DECLARE @sSql nvarchar(200)
	SET @sSQL = N'update SchoolUsers set ' + @ResearName + ' = 1 where UserId in (select pkId from users where userName = ''' + @UserName + ''')'
    EXEC(@sSQL)
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].[DeleteWebResearList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DeleteWebResearList]
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
create PROCEDURE [dbo].[DeleteWebResearList]
	-- Add the parameters for the stored procedure here
	@pkId 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
	delete from answer  
	where answer.questionId in (select pkId from question where questionnaireSpecialId = @pkId)
	delete from options
	where options.questionId in (select pkId from question where questionnaireSpecialId = @pkId)
	delete from question 
	where question.questionnaireSpecialId = @pkId
	delete from questionnaireSpecial
	where questionnaireSpecial.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].[GetWebResearVoteSchoolInfoByUserId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetWebResearVoteSchoolInfoByUserId]
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
create PROCEDURE [dbo].[GetWebResearVoteSchoolInfoByUserId]
	-- Add the parameters for the stored procedure here
	@userId int,
	@BigClassId 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 a.questionId,a.optionsId,a.Message,q.optionsType from answer a, question q,users u
    where a.questionId = q.pkId and q.questionnaireSpecialId = @BigClassId and u.userName = a.userName and u.pkId = @userId and a.questionId = 100307 
 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].[DeleteWebResearAnswerByResearIdAndUserName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DeleteWebResearAnswerByResearIdAndUserName]
GO


CREATE PROCEDURE [dbo].[DeleteWebResearAnswerByResearIdAndUserName]
	-- Add the parameters for the stored procedure here
	@ResearId int ,
	@UserName varchar(100)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    update options set counter = counter -1 where options.pkId in (select answer.optionsId from answer,question where answer.IsSaved=0 and answer.questionId = question.pkId and question.questionnaireSpecialId = @ResearId and answer.UserName = @UserName)
    update options set FirstResearcounter = FirstResearcounter -1 where options.pkId in (select answer.optionsId from answer,question,users,SchoolUsers where answer.IsSaved=0 and answer.questionId = question.pkId and question.questionnaireSpecialId = @ResearId and answer.UserName = users.username and users.username = @UserName and SchoolUsers.userId = users.pkId and SchoolUsers.Department = 1)
    update options set SecondResearcounter = SecondResearcounter -1 where options.pkId in (select answer.optionsId from answer,question,users,SchoolUsers where answer.IsSaved=0 and answer.questionId = question.pkId and question.questionnaireSpecialId = @ResearId and answer.UserName = users.username and users.username = @UserName and SchoolUsers.userId = users.pkId and SchoolUsers.Department = 2)
	delete from answer where UserName = @UserName and questionId in (select pkId from question where questionnaireSpecialId = @ResearId)
	
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].[InsertWebResearAnswer]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertWebResearAnswer]
GO


CREATE PROCEDURE [dbo].[InsertWebResearAnswer]
(@pkId int, @questionId int, @optionsId int, @userId int, @IP bigint, @date datetime, @Message text, @userName varchar(100))
AS
insert into answer(questionId,optionsId,userId,IP,date,Message,userName)
values(@questionId,@optionsId,@userId,@IP,@date,@Message,@userName)
update options
set counter = counter +1
where options.pkId =(@optionsId)


update options
set FirstResearCounter = FirstResearCounter +1
where options.pkId =(@optionsId) and options.questionId in (select pkId from question where questionnaireSpecialId=6) and @userName in ( select UserName from users,schoolusers where users.pkId = SchoolUsers.UserId and SchoolUsers.department = 1)
update options
set SecondResearCounter = SecondResearCounter +1
where options.pkId =(@optionsId) and options.questionId in (select pkId from question where questionnaireSpecialId=6) and @userName in ( select UserName from users,schoolusers where users.pkId = SchoolUsers.UserId and SchoolUsers.department = 2)

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].[GetAuditingLinkByUserId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetAuditingLinkByUserId]
GO











CREATE   PROCEDURE [dbo].[GetAuditingLinkByUserId]
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeId int,
	@userId int,
	@gateId int,
	@manageAll varchar(8000),
	@recordCount int output
	
AS	
	--转换成索引
	SELECT @currentPage = @currentPage - 1
	
	--初始化查询条件(不带where)
	DECLARE @str nvarchar(4000)
--用临时表先读取授权的栏目和MenuName，然后在读取授权的新闻，
--这样设计不会对news表中的menuId产生依赖，避免因栏目移动产生错误
select b.pkId bigclassId, m.name menuName,m.pkId sourceMenuId into #test from bigClass b, menu m, moduleAssign a where m.bigClassId=b.pkId and m.pkId=a.menuId and usersId=@userId

	SET @str = ' 1=1 '
	SET @str = @str + ' and n.bigclassId = z.bigclassId'
	SET @str = @str + ' and z.bigclassId = b.pkid'
	SET @str = @str + ' and b.templateId = t.pkId'
	SET @str = @str + ' and n.passed=0 and n.gradeId<='+STR(@gradeId)
	SET @str = @str + ' and n.gate<>'+STR(@gateId)
	SET @str = @str + '  and n.userId=u.pkId and n.gradeId=g.pkId '

--SET @str = @str + ' and ' + @manageAll
print @str
--SET @str = @str + ' and m.pkId in (1795,1796,1803)'
	--设置查询表名称
	DECLARE @tableName nvarchar(100)

	SET @tableName = '#test z, link n,users u,grade g, template t, bigClass b '
print @tableName
	--设置查询排序（不带order by）
	DECLARE @orderTypeA nvarchar(100)
	DECLARE @orderTypeD nvarchar(100)
	SET @orderTypeA = 'sourceMenuId ASC, newsId ASC'
	SET @orderTypeD = 'sourceMenuId DESC, newsId DESC'
	
	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(4000)



	SET @vSQL = N'SELECT @count = Count(n.pkId) from '+Convert(nvarchar(100),@tableName)+' where ' + Convert(nvarchar(4000),@str)
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum

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

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

	--判断分页大小，查询最后一页用
	SET @size = @recordCount%@pageSize	
	IF(@size=0)
		SET @size = @pageSize	
	
	--查询第一页
	IF (@currentPage = 0) OR (@currentPage > @pageCount-1)	
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' n.*,u.userName,g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId , t.code FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(4000),@str)+' and b.pkId = n.bigClassId and b.templateId = t.pkId ORDER BY '+Convert(nvarchar(100),@orderTypeD)	
	--查询最后一页
	ELSE IF @currentPage = @pageCount -1			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' n.*,u.userName,g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId , t.code FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(4000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable ORDER BY '+Convert(nvarchar(100),@orderTypeD)	
	--查询中间页后面的页
	ELSE IF @currentPage > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @currentPage ) + ' n.*,u.userName,g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId, t.code FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(4000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable ORDER BY '+Convert(nvarchar(100),@orderTypeD)
	--查询中间页前面的页
 	ELSE BEGIN							
		SET @currentPage=@currentPage+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @currentPage ) + ' n.*,u.userName,g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId, t.code FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(4000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeD)+') TempTable1 '
					+ ' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable2 ORDER BY '+Convert(nvarchar(100),@orderTypeD)
	END

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


GO
SET QUOTED_IDENTIFIER 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].[GetAuditingCount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetAuditingCount]
GO






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

	--友情链接
	SET @str4 = ' 1=1 '
	SET @str4 = @str4 + '  and n.bigClassId in (select b.pkId from bigClass b, menu m, moduleAssign a where m.bigClassId=b.pkId and m.pkId=a.menuId and a.usersId='+STR(@userId)+')'
	SET @str4 = @str4 + ' and ' + @manageAll
	SET @str4 = @str4 + ' and n.passed=0 and n.gradeId<='+STR(@gradeId)
	SET @str4 = @str4 + ' and n.gate<>'+STR(@gateId)
	SET @str4 = @str4 + ' and n.userId=u.pkId'

	--人物事物
	SET @str5 = ' 1=1 '
	SET @str5 = @str5 + '  and n.bigClassId in (select b.pkId from bigClass b, menu m, moduleAssign a where m.bigClassId=b.pkId and m.pkId=a.menuId and a.usersId='+STR(@userId)+')'
	SET @str5 = @str5 + ' and ' + @manageAll
	SET @str5 = @str5 + ' and n.passed=0 and n.gradeId<='+STR(@gradeId)
	SET @str5 = @str5 + ' and n.gate<>'+STR(@gateId)
	SET @str5 = @str5 + ' and n.userId=u.pkId'

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

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

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

	SET @vSQL = N'SELECT @count4 = Count(n.pkId) from link n,users u where ' + Convert(nvarchar(4000),@str4)
	EXEC sp_ExecuteSQL @vSQL, N'@count4 int output', @tempNum output
	SELECT @linkCount = @tempNum	

	SET @vSQL = N'SELECT @count5 = Count(n.pkId) from people n,users u where ' + Convert(nvarchar(4000),@str4)
	EXEC sp_ExecuteSQL @vSQL, N'@count5 int output', @tempNum output
	SELECT @peopleCount = @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].[GetAuditingPeopleByUserId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetAuditingPeopleByUserId]
GO












create   PROCEDURE [dbo].[GetAuditingPeopleByUserId]
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeId int,
	@userId int,
	@gateId int,
	@manageAll varchar(8000),
	@recordCount int output
	
AS	
	--转换成索引
	SELECT @currentPage = @currentPage - 1
	
	--初始化查询条件(不带where)
	DECLARE @str nvarchar(4000)
--用临时表先读取授权的栏目和MenuName，然后在读取授权的新闻，
--这样设计不会对news表中的menuId产生依赖，避免因栏目移动产生错误
select b.pkId bigclassId, m.name menuName,m.pkId sourceMenuId into #test from bigClass b, menu m, moduleAssign a where m.bigClassId=b.pkId and m.pkId=a.menuId and usersId=@userId

	SET @str = ' 1=1 '
	SET @str = @str + ' and n.bigclassId = z.bigclassId'
	SET @str = @str + ' and z.bigclassId = b.pkid'
	SET @str = @str + ' and b.templateId = t.pkId'
	SET @str = @str + ' and n.passed=0 and n.gradeId<='+STR(@gradeId)
	SET @str = @str + ' and n.gate<>'+STR(@gateId)
	SET @str = @str + '  and n.userId=u.pkId and n.gradeId=g.pkId '

--SET @str = @str + ' and ' + @manageAll
print @str
--SET @str = @str + ' and m.pkId in (1795,1796,1803)'
	--设置查询表名称
	DECLARE @tableName nvarchar(100)

	SET @tableName = '#test z, people n,users u,grade g, template t, bigClass b '
print @tableName
	--设置查询排序（不带order by）
	DECLARE @orderTypeA nvarchar(100)
	DECLARE @orderTypeD nvarchar(100)
	SET @orderTypeA = 'sourceMenuId ASC, newsId ASC'
	SET @orderTypeD = 'sourceMenuId DESC, newsId DESC'
	
	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(4000)



	SET @vSQL = N'SELECT @count = Count(n.pkId) from '+Convert(nvarchar(100),@tableName)+' where ' + Convert(nvarchar(4000),@str)
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum

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

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

	--判断分页大小，查询最后一页用
	SET @size = @recordCount%@pageSize	
	IF(@size=0)
		SET @size = @pageSize	
	
	--查询第一页
	IF (@currentPage = 0) OR (@currentPage > @pageCount-1)	
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' n.*,u.userName,g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId , t.code FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(4000),@str)+' and b.pkId = n.bigClassId and b.templateId = t.pkId ORDER BY '+Convert(nvarchar(100),@orderTypeD)	
	--查询最后一页
	ELSE IF @currentPage = @pageCount -1			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' n.*,u.userName,g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId , t.code FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(4000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable ORDER BY '+Convert(nvarchar(100),@orderTypeD)	
	--查询中间页后面的页
	ELSE IF @currentPage > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @currentPage ) + ' n.*,u.userName,g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId, t.code FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(4000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable ORDER BY '+Convert(nvarchar(100),@orderTypeD)
	--查询中间页前面的页
 	ELSE BEGIN							
		SET @currentPage=@currentPage+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @currentPage ) + ' n.*,u.userName,g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId, t.code FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(4000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeD)+') TempTable1 '
					+ ' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable2 ORDER BY '+Convert(nvarchar(100),@orderTypeD)
	END

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



GO
SET QUOTED_IDENTIFIER 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].[GetAuditingBoardByUserId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetAuditingBoardByUserId]
GO












create   PROCEDURE [dbo].[GetAuditingBoardByUserId]
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeId int,
	@userId int,
	@gateId int,
	@manageAll varchar(8000),
	@recordCount int output
	
AS	
	--转换成索引
	SELECT @currentPage = @currentPage - 1
	
	--初始化查询条件(不带where)
	DECLARE @str nvarchar(4000)
--用临时表先读取授权的栏目和MenuName，然后在读取授权的新闻，
--这样设计不会对news表中的menuId产生依赖，避免因栏目移动产生错误
select b.pkId bigclassId, m.name menuName,m.pkId sourceMenuId into #test from bigClass b, menu m, moduleAssign a where m.bigClassId=b.pkId and m.pkId=a.menuId and usersId=@userId

	SET @str = ' 1=1 '
	SET @str = @str + ' and n.bigclassId = z.bigclassId'
	SET @str = @str + ' and z.bigclassId = b.pkid'
	SET @str = @str + ' and b.templateId = t.pkId'
	SET @str = @str + ' and n.passed=0 and n.gradeId<='+STR(@gradeId)
	SET @str = @str + ' and n.gate<>'+STR(@gateId)
	SET @str = @str + '  and n.userId=u.pkId and n.gradeId=g.pkId '

--SET @str = @str + ' and ' + @manageAll
print @str
--SET @str = @str + ' and m.pkId in (1795,1796,1803)'
	--设置查询表名称
	DECLARE @tableName nvarchar(100)

	SET @tableName = '#test z, board n,users u,grade g, template t, bigClass b '
print @tableName
	--设置查询排序（不带order by）
	DECLARE @orderTypeA nvarchar(100)
	DECLARE @orderTypeD nvarchar(100)
	SET @orderTypeA = 'sourceMenuId ASC, newsId ASC'
	SET @orderTypeD = 'sourceMenuId DESC, newsId DESC'
	
	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(4000)



	SET @vSQL = N'SELECT @count = Count(n.pkId) from '+Convert(nvarchar(100),@tableName)+' where ' + Convert(nvarchar(4000),@str)
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum

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

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

	--判断分页大小，查询最后一页用
	SET @size = @recordCount%@pageSize	
	IF(@size=0)
		SET @size = @pageSize	
	
	--查询第一页
	IF (@currentPage = 0) OR (@currentPage > @pageCount-1)	
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' n.*,u.userName,g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId , t.code FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(4000),@str)+' and b.pkId = n.bigClassId and b.templateId = t.pkId ORDER BY '+Convert(nvarchar(100),@orderTypeD)	
	--查询最后一页
	ELSE IF @currentPage = @pageCount -1			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' n.*,u.userName,g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId , t.code FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(4000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable ORDER BY '+Convert(nvarchar(100),@orderTypeD)	
	--查询中间页后面的页
	ELSE IF @currentPage > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @currentPage ) + ' n.*,u.userName,g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId, t.code FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(4000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable ORDER BY '+Convert(nvarchar(100),@orderTypeD)
	--查询中间页前面的页
 	ELSE BEGIN							
		SET @currentPage=@currentPage+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @currentPage ) + ' n.*,u.userName,g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId, t.code FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(4000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeD)+') TempTable1 '
					+ ' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable2 ORDER BY '+Convert(nvarchar(100),@orderTypeD)
	END

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



GO
SET QUOTED_IDENTIFIER 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].[GetAuditingLinkGangByUserId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetAuditingLinkGangByUserId]
GO












CREATE   PROCEDURE [dbo].[GetAuditingLinkGangByUserId]
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeId int,
	@userId int,
	@gateId int,
	@manageAll varchar(8000),
	@recordCount int output
	
AS	
	--转换成索引
	SELECT @currentPage = @currentPage - 1
	
	--初始化查询条件(不带where)
	DECLARE @str nvarchar(4000)
--用临时表先读取授权的栏目和MenuName，然后在读取授权的新闻，
--这样设计不会对news表中的menuId产生依赖，避免因栏目移动产生错误
select b.pkId bigclassId, m.name menuName,m.pkId sourceMenuId into #test from bigClass b, menu m, moduleAssign a where m.bigClassId=b.pkId and m.pkId=a.menuId and usersId=@userId

	SET @str = ' 1=1 '
	SET @str = @str + ' and l.bigClassId = z.bigclassId'
	SET @str = @str + ' and z.bigclassId = b.pkid'
	SET @str = @str + ' and b.templateId = t.pkId'
	SET @str = @str + ' and n.passed=0 and n.gradeId<='+STR(@gradeId)
	SET @str = @str + ' and n.gate<>'+STR(@gateId)
	SET @str = @str + '  and n.userId=u.pkId and n.gradeId=g.pkId and n.groupId=l.pkId'

--SET @str = @str + ' and ' + @manageAll
print @str
--SET @str = @str + ' and m.pkId in (1795,1796,1803)'
	--设置查询表名称
	DECLARE @tableName nvarchar(100)

	SET @tableName = '#test z, linkGang n,users u,grade g, template t, bigClass b, linkGroup l '
print @tableName
	--设置查询排序（不带order by）
	DECLARE @orderTypeA nvarchar(100)
	DECLARE @orderTypeD nvarchar(100)
	SET @orderTypeA = 'sourceMenuId ASC, newsId ASC'
	SET @orderTypeD = 'sourceMenuId DESC, newsId DESC'
	
	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(4000)



	SET @vSQL = N'SELECT @count = Count(n.pkId) from '+Convert(nvarchar(100),@tableName)+' where ' + Convert(nvarchar(4000),@str)
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum

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

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

	--判断分页大小，查询最后一页用
	SET @size = @recordCount%@pageSize	
	IF(@size=0)
		SET @size = @pageSize	
	
	--查询第一页
	IF (@currentPage = 0) OR (@currentPage > @pageCount-1)	
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' n.*,u.userName,g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId , t.code, l.bigClassId FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(4000),@str)+' and b.pkId = l.bigClassId and b.templateId = t.pkId ORDER BY '+Convert(nvarchar(100),@orderTypeD)	
	--查询最后一页
	ELSE IF @currentPage = @pageCount -1			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' n.*,u.userName,g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId , t.code, l.bigClassId FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(4000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable ORDER BY '+Convert(nvarchar(100),@orderTypeD)	
	--查询中间页后面的页
	ELSE IF @currentPage > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @currentPage ) + ' n.*,u.userName,g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId, t.code, l.bigClassId FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(4000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable ORDER BY '+Convert(nvarchar(100),@orderTypeD)
	--查询中间页前面的页
 	ELSE BEGIN							
		SET @currentPage=@currentPage+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @currentPage ) + ' n.*,u.userName,g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId, t.code, l.bigClassId FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(4000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeD)+') TempTable1 '
					+ ' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable2 ORDER BY '+Convert(nvarchar(100),@orderTypeD)
	END

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



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

	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].[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,
    @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 @searchStr = '%'+@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
    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 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].[GetNewsByAuthor]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetNewsByAuthor]
GO









CREATE PROCEDURE [dbo].[GetNewsByAuthor]
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeId int,
	@author varchar(100),
	@gateId int,
	@bigClassId int,
	@NewSearchbigClassId int
AS	
	--转换成索引
	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 @author = '%'+@author+'%'
	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 author like ''' + @author + ''''
	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].[GetNewsByContent]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetNewsByContent]
GO









CREATE PROCEDURE [dbo].[GetNewsByContent]
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeId int,
	@content varchar(100),
	@gateId int,
	@bigClassId int,
	@NewSearchbigClassId int
AS	
	--转换成索引
	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 @content = '%'+@content+'%'
	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 content like ''' + @content + ''''
	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].[GetNewsByTitle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetNewsByTitle]
GO











CREATE PROCEDURE [dbo].[GetNewsByTitle]
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeId int,
	@title varchar(100),
	@gateId int,
	@bigClassId int,
	@NewSearchbigClassId int
AS	
    
	--转换成索引
	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 @title = '%'+@title+'%'
	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 (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 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 OFF 
GO
SET ANSI_NULLS ON 
GO

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


CREATE PROCEDURE [dbo].[GetNewsCalendarSearchCount]
(
	@NewSearchbigClassId int,
	@bigClassId int,
	@gradeId int,
	@gateId int,
	@searchTimeBegin DateTime,
	@searchTimeEnd DateTime,
	@recordCount int output
)
AS
	--设置查询表名称
	DECLARE @tableName nvarchar(50)
	--初始化查询条件(不带where)
	DECLARE @str nvarchar(1000)
    DECLARE @sCount int,@sNum int
    DECLARE @sSql nvarchar(200)
    DECLARE @searchStrBegin nvarchar(100)
    DECLARE @searchStrEnd nvarchar(100)
    SET @sSQL = N'SELECT @count=Count(searchOptions.pkId) from searchOptions where bigClassId = ' + STR(@NewSearchbigClassId)
    EXEC sp_ExecuteSQL @sSQL,N'@count int output',@sNum output
    SELECT @sCount = @sNum

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


	SET @str = @str + ' and news.updateTime >''' + @searchStrBegin + ''' and news.updateTime <''' + @searchStrEnd + '''' + ' and news.bigClassId = bigClass.pkId '

	SET @str = @str + ' and news.passed=1 and (news.isDuplicated<>1 or news.isDuplicated is null) and news.gradeId<='+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].[GetNewsCalendarSearch]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetNewsCalendarSearch]
GO











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

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

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

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

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

	--执行查询
	EXEC(@sqlStr)

RETURN







GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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


CREATE PROCEDURE [dbo].[GetNewsCalendarByDay]
(
	@NewSearchbigClassId int,
	@bigClassId int,
	@gradeId int,
	@gateId int,
	@searchTimeBegin DateTime,
	@searchTimeEnd DateTime
)
AS
	--设置查询表名称
	DECLARE @tableName nvarchar(50)
	--初始化查询条件(不带where)
	DECLARE @str nvarchar(1000)
    DECLARE @sCount int,@sNum int
    DECLARE @sSql nvarchar(200)
    DECLARE @searchStrBegin nvarchar(100)
    DECLARE @searchStrEnd nvarchar(100)
    SET @sSQL = N'SELECT @count=Count(searchOptions.pkId) from searchOptions where bigClassId = ' + STR(@NewSearchbigClassId)
    EXEC sp_ExecuteSQL @sSQL,N'@count int output',@sNum output
    SELECT @sCount = @sNum

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


	SET @str = @str + ' and news.updateTime >''' + @searchStrBegin + ''' and news.updateTime <''' + @searchStrEnd + '''' + ' and news.bigClassId = bigClass.pkId '

	SET @str = @str + ' and news.passed=1 and (news.isDuplicated<>1 or news.isDuplicated is null) and news.gradeId<='+Convert(varchar,@gradeId) + 'group by CONVERT(varchar(10),news.updatetime,120)'
	--SET @str = @str + ' and gate<>'+Convert(varchar,@gateId)

	
	
	
	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT CONVERT(varchar(10),news.updatetime,120) ''日期'',count(*) ''新闻数'' from '+Convert(nvarchar(50),@tableName)+' where ' + Convert(nvarchar(1000),@str)
	EXEC sp_ExecuteSQL @vSQL
RETURN
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
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
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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



CREATE PROCEDURE [dbo].[GetNewsByBigClassIds]
(
	@bigClassIds varchar(100),
	@pageSize int
)
AS
	SET NOCOUNT ON
	DECLARE @str nvarchar(1000)
	SET @str = 'SELECT top ' + Convert(nvarchar(50),@pageSize) + ' * FROM news WHERE passed=1 and patindex(''%|''+Convert(Varchar(50),news.bigClassId)+''|%'','''+Convert(varchar(100),@bigClassIds)+''')>0 order by 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].[InsertMessageForward]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertMessageForward]
GO

CREATE PROCEDURE [dbo].[InsertMessageForward]
	-- Add the parameters for the stored procedure here
	@messageId int,
	@ForwardToUserName nvarchar(50),
	@Status 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 count(*) from messageForward where messageId = @messageId and ForwardToUserName = @ForwardToUserName) = 0
	INSERT INTO messageForward(messageId,ForwardToUserName,Status) VALUES(@messageId,@ForwardToUserName,@Status)
	
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].[GetMessageForwardByUserName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetMessageForwardByUserName]
GO

CREATE PROCEDURE [dbo].[GetMessageForwardByUserName]
	-- Add the parameters for the stored procedure here
	@ForwardToUserName nvarchar(250),
	@Status int,
	@recordCount int output
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(2000)
	DECLARE @tableName nvarchar(50)
	DECLARE @str nvarchar(1000)
	SET @tableName = 'messageForward'
	SET @str = ' ForwardToUserName = ''' + @ForwardToUserName + ''''
	
	SET @vSQL = N'SELECT @count = Count(*) from '+Convert(nvarchar(50),@tableName)+' where ' + @str
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum
	
	
	
	if @Status = 0
	   begin
	   SET @vSQL = N'SELECT messageBoard.MenuId as menuId,messageBoard.bigClassId as bigClassId,bigClass.name,bigClass.templateId,count(messageBoard.MenuId) as count from '+Convert(nvarchar(50),@tableName) + ',bigClass,messageBoard' +' where ' + @str + ' and messageForward.messageId = messageBoard.pkId and messageBoard.bigClassId = bigClass.pkId and messageboard.reContent is null group by messageBoard.bigClassId,bigClass.name,bigClass.templateId,messageBoard.MenuId'
	   end
	else
	   begin
	   SET @vSQL = N'SELECT messageBoard.MenuId as menuId,messageBoard.bigClassId as bigClassId,bigClass.name,bigClass.templateId,count(messageBoard.MenuId) as count from '+Convert(nvarchar(50),@tableName) + ',bigClass,messageBoard' +' where ' + @str + ' and messageForward.messageId = messageBoard.pkId and messageBoard.bigClassId = bigClass.pkId and messageBoard.reContent is not null group by messageBoard.bigClassId,bigClass.name,bigClass.templateId,messageBoard.MenuId'
	   end
	
	
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	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].[GetMessageBoardByBigClassIdAndForwardUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetMessageBoardByBigClassIdAndForwardUser]
GO

CREATE PROCEDURE [dbo].[GetMessageBoardByBigClassIdAndForwardUser]
@bigClassId int=null,
@currentPage int=1,
@pageSize int=5,
@gradeid int=1,
@gateId int,
@userName varchar(100)
AS
	 SET NOCOUNT ON
	declare @vSQL varchar(1000)
select @vSQL = 'select top ' + convert(varchar, @pageSize) + ' m.*,g.name "grade" from messageBoard m,messageForward mf, grade g where mf.messageId = m.pkId and mf.ForwardToUserName =''' + @userName + ''' and m.gate<>'+Convert(varchar,@gateId)+' and   m.bigClassId='+Convert(varchar,@bigClassId)+' and m.gradeId<='+convert(varchar,@gradeId)+' and m.gradeId=g.pkId and m.pkId <> all (select top '+Convert(varchar,((@currentPage-1)*@pageSize))+' pkId from messageBoard where   gate<>'+Convert(varchar,@gateId)+' and   bigClassId='+Convert(varchar,@bigClassId)+' and gradeId<='+convert(varchar,@gradeId)+' order by istop desc,pkId desc)  order by istop desc,m.pkId desc'
Execute (@vSQL)
	
	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].[GetAuditingMessageBoardByUserId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetAuditingMessageBoardByUserId]
GO




CREATE PROCEDURE [dbo].[GetAuditingMessageBoardByUserId]
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeId int,
	@userId int,
	@gateId int,
	@manageAll varchar(1000),
	@recordCount int output
	
AS	
	--转换成索引
	SELECT @currentPage = @currentPage - 1
	
	--初始化查询条件(不带where)
	DECLARE @str nvarchar(4000)

--用临时表先读取授权的栏目和MenuName，然后在读取授权的新闻，
--这样设计不会对news表中的menuId产生依赖，避免因栏目移动产生错误
--根据栏目Id进行分组，以消除不用菜单位置的相同栏目产生的重复记录，20091010
select b.pkId bigclassId, m.name menuName,max(m.pkId) sourceMenuId into #MessageBoardTemp from bigClass b, menu m, moduleAssign a where m.bigClassId=b.pkId and m.pkId=a.menuId and usersId=@userId group by b.pkId,m.name


	SET @str = ' 1=1 '
	SET @str = @str + ' and m.bigclassId = z.bigclassId'
	SET @str = @str + ' and z.bigclassId = b.pkid'
	SET @str = @str + ' and m.checked=0 and m.gradeId<='+STR(@gradeId)
	SET @str = @str + ' and m.gate<>'+STR(@gateId)
	SET @str = @str + ' and m.gradeId=g.pkId '

	--设置查询表名称
	DECLARE @tableName nvarchar(150)
	SET @tableName = '#MessageBoardTemp z, messageBoard m, grade g , bigClass b '

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

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

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

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

	--执行查询
	EXEC(@sqlStr)

RETURN




GO
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].[GetAuditingNewsByUserId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetAuditingNewsByUserId]
GO














CREATE   PROCEDURE [dbo].[GetAuditingNewsByUserId]
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeId int,
	@userId int,
	@gateId int,
	@manageAll varchar(8000),
	@recordCount int output
	
AS	
	--转换成索引
	SELECT @currentPage = @currentPage - 1
	
	--初始化查询条件(不带where)
	DECLARE @str nvarchar(4000)
--用临时表先读取授权的栏目和MenuName，然后在读取授权的新闻，
--这样设计不会对news表中的menuId产生依赖，避免因栏目移动产生错误
--根据栏目Id进行分组，以消除不用菜单位置的相同栏目产生的重复记录，20091010
select b.pkId bigclassId, m.name menuName,max(m.pkId) sourceMenuId into #test from bigClass b, menu m, moduleAssign a where m.bigClassId=b.pkId and m.pkId=a.menuId and usersId=@userId group by b.pkId,m.name

	SET @str = ' 1=1 '
	SET @str = @str + ' and n.bigclassId = z.bigclassId'
	SET @str = @str + ' and z.bigclassId = b.pkid'
	SET @str = @str + ' and b.templateId = t.pkId'
	SET @str = @str + ' and n.passed=0 and n.gradeId<='+STR(@gradeId)
	SET @str = @str + ' and n.gate<>'+STR(@gateId)
	SET @str = @str + '  and n.usersId=u.pkId and n.gradeId=g.pkId and n.departmentId=d.pkId ' --and n.MenuId=m.pkId 去掉了 fengyb 2008年-1-9日and n.MenuId=m.pkId 
	--SET @str = @str + ' and (n.duplicatedPkId=0 or n.duplicatedPkId is null)'

--SET @str = @str + ' and ' + @manageAll
print @str
--SET @str = @str + ' and m.pkId in (1795,1796,1803)'
	--设置查询表名称
	DECLARE @tableName nvarchar(100)

	SET @tableName = '#test z, news n,users u,department d,grade g, template t, bigClass b '
print @tableName
	--设置查询排序（不带order by）
	DECLARE @orderTypeA nvarchar(100)
	DECLARE @orderTypeD nvarchar(100)
	SET @orderTypeA = 'sourceMenuId ASC, newsId ASC'
	SET @orderTypeD = 'sourceMenuId DESC, newsId DESC'
	
	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(4000)



	SET @vSQL = N'SELECT @count = Count(n.pkId) from '+Convert(nvarchar(100),@tableName)+' where ' + Convert(nvarchar(4000),@str)
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum

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

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

	--判断分页大小，查询最后一页用
	SET @size = @recordCount%@pageSize	
	IF(@size=0)
		SET @size = @pageSize	
	
	--查询第一页
	IF (@currentPage = 0) OR (@currentPage > @pageCount-1)	
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' n.*,u.userName,d.name "department" ,g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId , t.code FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(4000),@str)+' and b.pkId = n.bigClassId and b.templateId = t.pkId ORDER BY '+Convert(nvarchar(100),@orderTypeD)	
	--查询最后一页
	ELSE IF @currentPage = @pageCount -1			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' n.*,u.userName,d.name "department" ,g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId , t.code FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(4000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable ORDER BY '+Convert(nvarchar(100),@orderTypeD)	
	--查询中间页后面的页
	ELSE IF @currentPage > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @currentPage ) + ' n.*,u.userName,d.name "department" ,g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId, t.code FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(4000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable ORDER BY '+Convert(nvarchar(100),@orderTypeD)
	--查询中间页前面的页
 	ELSE BEGIN							
		SET @currentPage=@currentPage+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @currentPage ) + ' n.*,u.userName,d.name "department" ,g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId, t.code FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(4000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeD)+') TempTable1 '
					+ ' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable2 ORDER BY '+Convert(nvarchar(100),@orderTypeD)
	END

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





GO
SET QUOTED_IDENTIFIER 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.isImageNews=0 and n.bigClassId = b.pkId and b.templateId = t.pkId and t.code!=''discuss'''
	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 n.isImageNews=0 and t.pkId = b.templateId and t.code!=''discuss'' 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 n.isImageNews=0 and t.pkId = b.templateId and t.code!=''discuss'' 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 n.isImageNews=0 and t.pkId = b.templateId and t.code!=''discuss'' 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 n.isImageNews=0 and t.pkId = b.templateId and t.code!=''discuss'' 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].[GetMessageForwardUserCount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetMessageForwardUserCount]
GO

CREATE PROCEDURE [dbo].[GetMessageForwardUserCount]
@bigClassId int=null,
@gradeId int=1,
@gateId int=-1,
@count int output,
@isForeGround int,
@searchStr varchar(100) = '',
@userName nvarchar(250)
AS
	SET NOCOUNT ON
	declare @num int
	declare @vSQL varchar(1000)
	IF (@isForeGround=0)
	BEGIN
		select @vSQL=
		     'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from messageBoard,messageForward where gate<> '+convert(varchar,@gateId)+' and bigclassId='+convert(varchar,@bigclassId)+' and  gradeId<='+convert(varchar,@gradeId)+' and messageBoard.pkId = messageForward.messageId and ForwardToUserName = ''' + @userName + ''' return'
	END 
	ELSE IF (@isForeGround=1)
	BEGIN
		select @vSQL=
			'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from messageBoard,messageForward where gate<> '+convert(varchar,@gateId)+' and bigclassId='+convert(varchar,@bigclassId)+' and  gradeId<='+convert(varchar,@gradeId)+' and messageBoard.reContent is not null and messageBoard.pkId = messageForward.messageId and ForwardToUserName = ''' + @userName + ''' return'
	END	
	Execute (@vSQL)
	execute GetCount @count=@num output
	select @count=@num
RETURN
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

