SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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


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


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

CREATE PROCEDURE [dbo].[UserApplyList]
	@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 UserApply'
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @RecordCount = @tempNum

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

	DECLARE @sqlStr varchar(2000),@size int

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

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

CREATE PROCEDURE UserApplyUpdate 
	@PkId int,	
	@UnitName varchar(150),
	@IBE varchar(150),
	@UserName varchar(20),
    @Province varchar(150),
	@City varchar(150),
	@County varchar(150),
    @UnitType int,
    @Phone varchar(50),
    @Email varchar(80),
	@AllowSendSms bit,
	@AllowSmsRemind bit,
	@Memo nText
AS
BEGIN
	SET NOCOUNT OFF;
	BEGIN TRANSACTION
		UPDATE UserApply SET unitName = @UnitName, ibe =@IBE , userName = @UserName, province = @Province, city  = @City,
							 county = @County, unitType = @UnitType, phone = @Phone, email = @Email, 
							 allowSendSms = @AllowSendSms, allowSmsRemind = @AllowSmsRemind, memo = @Memo
	    WHERE pkId = @PkId		
	COMMIT TRANSACTION
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].[UserApplyDelete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UserApplyDelete]
GO

CREATE PROCEDURE UserApplyDelete
	@PkId int
AS
BEGIN
	SET NOCOUNT OFF;
	BEGIN TRANSACTION
		DELETE FROM UserApply WHERE pkId = @PkId
	COMMIT TRANSACTION
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].[UserApplyDetails]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UserApplyDetails]
GO

CREATE PROCEDURE UserApplyDetails 	
	@PkId int = 0	
AS
	select top 1 * from UserApply where pkId = @PkId
	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].[InsertOperationLog]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertOperationLog]
GO





--region [dbo].[InsertOperationLog]

------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Administrator using CodeSmith 3.2.7.955
-- Template:       StoredProcedures.cst
-- Procedure Name: [dbo].[InsertOperationLog]
-- Date Generated: 2008骞?鏈?3鏃?
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[InsertOperationLog]
	@operation varchar(255),
	@operationUserName varchar(100),
	@operationIp varchar(100),
	@operationTime datetime,
	@remark text,
	@type int,
	@operationResult varchar(100),
	@pkId int OUTPUT
AS

SET NOCOUNT ON

INSERT INTO [dbo].[OperationLog] (
	[operation],
	[operationUserName],
	[operationIp],
	[operationTime],
	[remark],
	[type],
	[operationResult]
) VALUES (
	@operation,
	@operationUserName,
	@operationIp,
	@operationTime,
	@remark,
	@type,
	@operationResult
)

SET @pkId = SCOPE_IDENTITY()

--endregion





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



--region [dbo].[GetOperationLogByPage]

------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Administrator using CodeSmith 3.2.7.955
-- Template:       StoredProcedures.cst
-- Procedure Name: [dbo].[GetOperationLogByPage]
-- Date Generated: 2008骞?鏈?3鏃?
------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[GetOperationLogByPage]
	@searchStr varchar(500),
	@pageIndex int,
	@pageSize int,
	@recordCount int output,
	@pageCount int output
AS
	SELECT @pageIndex = @pageIndex - 1
	
	DECLARE @tempNum int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT @count = Count(pkId) FROM OperationLog WHERE ' + @searchStr
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum

	SET @pageCount = (@recordCount + @pageSize-1)/@pageSize

	DECLARE @sqlStr varchar(2000),@size int

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

	IF (@pageIndex = 0) OR (@pageIndex > @pageCount-1)	
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM OperationLog WHERE ' + @searchStr + ' ORDER BY pkId DESC'	
	ELSE IF @pageIndex = @pageCount -1			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' * FROM OperationLog WHERE ' + @searchStr + ' ORDER BY pkId ASC) TempTable ORDER BY pkId DESC'	
	ELSE IF @pageIndex > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @pageIndex ) + ' * FROM OperationLog WHERE ' + @searchStr + ' ORDER BY pkId ASC) TempTable ORDER BY pkId DESC'
	ELSE BEGIN							
		SET @pageIndex=@pageIndex+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @pageIndex ) + ' * FROM OperationLog WHERE ' + @searchStr + ' ORDER BY pkId DESC) TempTable1 '
					+ ' ORDER BY pkId ASC) TempTable2 ORDER BY pkId DESC'
	END
	EXEC(@sqlStr)
	RETURN
--endregion



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





create PROCEDURE [dbo].[GetOperationLogByPkId]
	@pkId int
AS
	
	SELECT *
	FROM OperationLog	
	where pkId = @pkId
	
	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].[DeleteOperationLogByType]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DeleteOperationLogByType]
GO


  
CREATE PROCEDURE [dbo].[DeleteOperationLogByType]
(
	@pkId int
)
AS
	SET NOCOUNT OFF;
       
          DELETE FROM OperationLog WHERE type = @pkId   


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

CREATE PROCEDURE [dbo].[GetWebResearOptionsBypkId]
(@pkId int)
AS
select * from options
where @pkId=options.questionId
order by options.IsTextbox asc,options.pkId asc
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].[UpdateWebResearByTitle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateWebResearByTitle]
GO

CREATE PROCEDURE [dbo].[UpdateWebResearByTitle]
(@questionnaireSpecialName varchar(1000), @bigClassId int, @validDays int, @gate int, @gradeId int, @IsAnonymity int, @restrictObject int, @restrictMode int, @restrictTime int, @IsVerify int, @generateDate datetime, @IsShow 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
 WHERE (bigClassId = @bigClassId)
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].[GetWebResearByPkId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetWebResearByPkId]
GO

CREATE PROCEDURE [dbo].[GetWebResearByPkId]
(@pkId int)
AS
	SET NOCOUNT ON;
SELECT * FROM question
 where @pkId=pkId
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].[InsertWebResear]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertWebResear]
GO

CREATE PROCEDURE [dbo].[InsertWebResear]
(@questionnaireSpecialId int, @questionName varchar(1000), @NotNull int, @optionsType int, @optionsCount int, @pkId int)
AS
	SET NOCOUNT OFF;
INSERT INTO question(questionnaireSpecialId, questionName, NotNull, optionsType, optionsCount) VALUES (@questionnaireSpecialId, @questionName, @NotNull, @optionsType, @optionsCount)
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].[UpdateWebResear]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateWebResear]
GO

CREATE PROCEDURE [dbo].[UpdateWebResear]
(@questionnaireSpecialId int, @questionName varchar(1000), @NotNull int, @optionsType int, @optionsCount int, @pkId int)
AS
	SET NOCOUNT OFF;
UPDATE question SET questionnaireSpecialId = @questionnairespecialId, questionName = @questionName, NotNull = @NotNull, optionsType = @optionsType, optionsCount = @optionsCount WHERE (pkId = @pkId)
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].[DeleteWebResear]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DeleteWebResear]
GO

CREATE PROCEDURE [dbo].[DeleteWebResear]
(@pkId int)
AS
	SET NOCOUNT OFF;
DELETE FROM question WHERE (pkId = @pkId)
DELETE from options where questionId =(@pkId)
Delete from answer where  questionId = (@pkId)
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].[GetWebResearOptionsBybigClassId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetWebResearOptionsBybigClassId]
GO

CREATE PROCEDURE [dbo].[GetWebResearOptionsBybigClassId]
(@bigClassId int)
AS
select * from options
where @bigClassId=options.questionId
order by options.IsTextbox asc,options.pkId asc
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].[DeleteWebResearOptions]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DeleteWebResearOptions]
GO


CREATE PROCEDURE [dbo].[DeleteWebResearOptions]
(@pkId int)
AS
	SET NOCOUNT OFF;
update question 
set optionsCount = optionsCount -1
from question,options
where options.questionId = question.pkId and (options.pkId=@pkId);
DELETE FROM options WHERE (pkId = @pkId);
DELETE FROM answer where (optionsId = @pkId)
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].[InsertWebResearOptions]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertWebResearOptions]
GO

CREATE PROCEDURE [dbo].[InsertWebResearOptions]
(@questionId int, @IsTextbox int, @optionsName varchar(1000), @counter int, @isPic int, @pkId int)
AS
	SET NOCOUNT OFF;
INSERT INTO options(questionId, IsTextbox, optionsName, counter, isPic) VALUES (@questionId, @IsTextbox, @optionsName, @counter, @isPic);
update question
set question.optionsCount = question.optionsCount +1
where question.pkId =(@questionId)
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].[UpdateWebResearOptions]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateWebResearOptions]
GO

CREATE PROCEDURE [dbo].[UpdateWebResearOptions]
(@questionId int, @IsTextbox int, @optionsName varchar(1000), @counter int, @isPic int, @pkId int)
AS
	SET NOCOUNT OFF;
UPDATE options SET questionId = @questionId, IsTextbox = @IsTextbox, optionsName = @optionsName, counter = @counter, isPic = @isPic WHERE (pkId = @pkId)
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].[GetWebResearOptionsByOptionsId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetWebResearOptionsByOptionsId]
GO

CREATE PROCEDURE [dbo].[GetWebResearOptionsByOptionsId]
(@pkId int)
AS
select * from options
where @pkId=pkId
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.bigClassId = (@bigClassId)
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].[InsertWebResearByTitle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertWebResearByTitle]
GO

CREATE PROCEDURE [dbo].[InsertWebResearByTitle]
(@bigClassId int, @questionnaireSpecialName varchar(1000), @validDays int, @gate int, @gradeId int, @IsAnonymity int, @restrictObject int, @restrictMode int, @restrictTime int, @IsVerify int, @generateDate datetime, @IsShow int)
AS
	SET NOCOUNT OFF;
insert into questionnaireSpecial(bigClassId,questionnaireSpecialName,validDays,gate,gradeId,IsAnonymity,restrictObject,restrictMode,restrictTime,IsVerify,generateDate,IsShow) values(@bigClassId,@questionnaireSpecialName,@validDays,@gate,@gradeId,@IsAnonymity,@restrictObject,@restrictMode,@restrictTime,@IsVerify,@generateDate,@IsShow)
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].[GetWebResearAnswerByBigClassId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetWebResearAnswerByBigClassId]
GO

CREATE PROCEDURE [dbo].[GetWebResearAnswerByBigClassId]
(@BigClassId int)
AS
select a.pkId,q.questionName,o.optionsName,a.userName,a.IP,a.Message,a.Date,o.IsTextbox
from question q,options o,answer a
where 
(
     a.questionId = @bigClassId and a.questionId = q.pkId and a.optionsId = o.pkId 
)
order by a.pkId desc
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].[DeleteWebResearAnswerBypkId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DeleteWebResearAnswerBypkId]
GO

CREATE PROCEDURE [dbo].[DeleteWebResearAnswerBypkId]
(@pkId int)
AS
update options
set options.counter = options.counter -1
from options,answer
where answer.pkId = (@pkId) and answer.optionsId = options.pkId
delete from answer 
where @pkId = answer.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].[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)
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 bigClassId = (@bigClassPkId)
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].[InsertWebResearAnswerIP]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertWebResearAnswerIP]
GO

CREATE PROCEDURE [dbo].[InsertWebResearAnswerIP]
(@pkId int, @questionnaireSpecialId int, @IP bigint, @date datetime)
AS
insert into IPInfo(questionnaireSpecialId,IP,date)
values(@questionnaireSpecialId,@IP,@Date)
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].[GetWebResearVoteIPByPkId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetWebResearVoteIPByPkId]
GO

CREATE PROCEDURE [dbo].[GetWebResearVoteIPByPkId]
(@bigClassPkId int, @IP bigint)
AS
select top 1 date
from IPInfo
where questionnaireSpecialId = (@bigclassPkId) and IP = (@IP)
order by date 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].[InsertMenuSet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertMenuSet]
GO




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




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




CREATE PROCEDURE [dbo].[UpdateMenuSet]
(
    @pkId int,
	@name varchar(100),
	@bgPic varchar(100),
	@backGroundPic varchar(100),
	@width int,
	@effect varchar(100),
	@BackgroundColor varchar(100),
	@SelectedColor varchar(100),
	@OuterHighlightColor varchar(100),
	@InnerShadowColor varchar(100),
	@InnerHighlightColor varchar(100),
	@OuterShadowColor varchar(100),
	@SelectedTextColor varchar(100),
	@fontSize varchar(100),
	@fontColor varchar(100),
	@direction varchar(100),
	@menuDirection varchar(100),
	@itemBgPic varchar(100),
	@itemIconWidth int,
	@itemIconHeight int,
	@itemIconRightWidth int,
	@PaddingLeft int,
	@DividerHeight int,
	@DividerColor varchar(100),
	@DividerImageAlignment varchar(100),
	@MenuShow int,
	@adminId int,
    @rowNum int,
    @fontWeight varchar(100)
	
)
AS
	SET NOCOUNT OFF;
UPDATE menuSet SET adminId=@adminId,name = @name, bgPic = @bgPic, backGroundPic = @backGroundPic, width = @width, effect = @effect, BackgroundColor = @BackgroundColor, SelectedColor = @SelectedColor, OuterHighlightColor = @OuterHighlightColor, InnerShadowColor = @InnerShadowColor, InnerHighlightColor = @InnerHighlightColor, OuterShadowColor = @OuterShadowColor, SelectedTextColor = @SelectedTextColor, fontSize = @fontSize, fontColor = @fontColor, direction = @direction  ,menuDirection=@menuDirection,itemBgPic=@itemBgPic,itemIconWidth=@itemIconWidth,itemIconHeight=@itemIconHeight,itemIconRightWidth=@itemIconRightWidth,	paddingLeft=@PaddingLeft,DividerHeight=@DividerHeight,DividerColor=@DividerColor,DividerImageAlignment=@DividerImageAlignment,MenuShow=@MenuShow,rowNum=@rowNum,fontWeight=@fontWeight
 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].[GetSearchAllNewsByUserId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetSearchAllNewsByUserId]
GO

CREATE PROCEDURE GetSearchAllNewsByUserId
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeId int,
	@userId int,
	@gateId int,
	@manageAll varchar(8000),
	@searchStr varchar(100) = '',	 
	@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.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 '
	SET @str = @str + ' and (n.duplicatedPkId=0 or n.duplicatedPkId is null)'
	IF(@searchStr <> '')
		SET @str = @str + @searchStr

	
	--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 (n.isDuplicated is null or n.isDuplicated =0 ) and b.pkId = n.bigClassId and b.templateId = t.pkId ORDER BY '+Convert(nvarchar(100),@orderTypeD)	
	--查询最后一页
	ELSE IF @currentPage = @pageCount -1			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' n.*,u.userName,d.name "department" ,g.name "grade",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].[GetNewsByBigClassId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetNewsByBigClassId]
GO


CREATE PROCEDURE [dbo].[GetNewsByBigClassId]
	@bigClassId int=null,
	@currentPage int=1,
	@pageSize int=5,
	@gradeid int=1,
	@gateId int,
	@searchStr varchar(100) = ''	 
AS
	 SET NOCOUNT ON
	declare @vSQL varchar(1000)
select @vSQL = 'select top ' + convert(varchar, @pageSize) + ' n.*,users.userName,d.name "department" ,	g.name "grade" 
				from news n, users,department d  ,grade g 
				where	n.gate<>'+Convert(varchar,@gateId)+' and   
						n.bigClassId='+Convert(varchar,@bigClassId)+' and 
						n.gradeId<='+convert(varchar,@gradeId)+' and 
						n.departmentId=d.pkId and 
						n.gradeId=g.pkId and 
						n.usersId=users.pkId  ' + 
						@searchStr + ' and
						n.pkId <> all (	select top '+Convert(varchar,((@currentPage-1)*@pageSize))+' pkId 
										from news
										where   gate<>'+Convert(varchar,@gateId)+' and   
												bigClassId='+Convert(varchar,@bigClassId)+' and 
												gradeId<='+convert(varchar,@gradeId)+ @searchStr + '											 
										order by istop desc,pkId desc
									  )
				order by istop desc,n.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].[GetNewsByBigClassIdCount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetNewsByBigClassIdCount]
GO


CREATE PROCEDURE [dbo].[GetNewsByBigClassIdCount]
@bigClassId int=null,
@gradeId int=1,
@gateId int=-1,
@table varchar(100)=null,
@count int output,
@isForeGround int,
@searchStr varchar(100) = ''
AS
	SET NOCOUNT ON
	declare @num int
	declare @vSQL varchar(1000)
	IF (@isForeGround=0)
	BEGIN
		select @vSQL=
			case @table
				when 'users' then 'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from users return'
				when 'popwindow' then  'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from '+convert(varchar,@table)+' where gate<> '+convert(varchar,@gateId)+' and gradeId<='+convert(varchar,@gradeId)+' return'
				when 'newss' then  'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and (specialId1='+convert(varchar,@bigclassId)+' or specialId2='+convert(varchar,@bigclassId)+' )   and  gradeId<='+convert(varchar,@gradeId)+' return'				
				when 'news' then  'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from '+convert(varchar,@table)+' where gate<> '+convert(varchar,@gateId)+' and bigclassId='+convert(varchar,@bigclassId)+' and  gradeId<='+convert(varchar,@gradeId) + @searchStr + ' return'
				when 'gameTitleList' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and bigclassId= '+convert(varchar,@bigclassId)+' and gradeId <= '+convert(varchar,@gradeId)+' return'	--魏兆佳20070727
				when 'gamePicList' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and bigclassId= '+convert(varchar,@bigclassId)+' and gradeId <= '+convert(varchar,@gradeId)+' return'		--魏兆佳20070727
				when 'gameList' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and bigclassId= '+convert(varchar,@bigclassId)+' and gradeId <= '+convert(varchar,@gradeId)+' return'			--魏兆佳20070727
				when 'todayInHistory' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and bigclassId= '+convert(varchar,@bigclassId)+' and gradeId <= '+convert(varchar,@gradeId)+' return'			--魏兆佳20070801
				when 'picNews'then  'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and  bigclassId='+convert(varchar,@bigclassId)+'  and isImageNews=1 and gradeId<='+convert(varchar,@gradeId)+' return'
				when 'picNews1'then  'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and  bigclassId='+convert(varchar,@bigclassId)+'  and isImageNews=1 and gradeId<='+convert(varchar,@gradeId)+' return'--李聪杰20060406
				when 'picNews2'then  'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and  bigclassId='+convert(varchar,@bigclassId)+'  and isImageNews=1 and gradeId<='+convert(varchar,@gradeId)+' return'--李聪杰20060406				
				ELSE  'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from '+convert(varchar,@table)+' where gate<> '+convert(varchar,@gateId)+' and bigclassId='+convert(varchar,@bigclassId)+' and  gradeId<='+convert(varchar,@gradeId)+' return'
			end
	END 
	ELSE IF (@isForeGround=1)
	BEGIN
		select @vSQL=
			case @table
				when 'users' then 'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from users return'
				when 'popwindow' then  'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from '+convert(varchar,@table)+' where gate<> '+convert(varchar,@gateId)+' and gradeId<='+convert(varchar,@gradeId)+' and passed=1 and validDays <> 0 return'
				when 'newss' then  'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and (specialId1='+convert(varchar,@bigclassId)+' or specialId2='+convert(varchar,@bigclassId)+' )   and  gradeId<='+convert(varchar,@gradeId)+' and passed=1 and validDays <> 0 return'				
				when 'news' then 'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from '+convert(varchar,@table)+' where gate<> '+convert(varchar,@gateId)+' and bigclassId='+convert(varchar,@bigclassId)+' and  gradeId<='+convert(varchar,@gradeId)+' and passed=1 and validDays <> 0 return'
				when 'gameTitleList' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and bigclassId= '+convert(varchar,@bigclassId)+' and gradeId <= '+convert(varchar,@gradeId)+' and passed=1 and validDays <> 0 return'	--魏兆佳20070727
				when 'gamePicList' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and bigclassId= '+convert(varchar,@bigclassId)+' and gradeId <= '+convert(varchar,@gradeId)+' and passed=1 and validDays <> 0 return'		--魏兆佳20070727
				when 'gameList' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and bigclassId= '+convert(varchar,@bigclassId)+' and gradeId <= '+convert(varchar,@gradeId)+' and passed=1 and validDays <> 0 return'		--魏兆佳20070727
				when 'todayInHistory' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and bigclassId= '+convert(varchar,@bigclassId)+' and gradeId <= '+convert(varchar,@gradeId)+' and passed=1 and validDays <> 0 return'		--魏兆佳20070801
				when 'picNews'then  'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and  bigclassId='+convert(varchar,@bigclassId)+'  and isImageNews=1 and gradeId<='+convert(varchar,@gradeId)+' and passed=1 and validDays <> 0 return'
				when 'picNews1'then  'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and  bigclassId='+convert(varchar,@bigclassId)+'  and isImageNews=1 and gradeId<='+convert(varchar,@gradeId)+' and passed=1 and validDays <> 0 return'--李聪杰20060406
				when 'picNews2'then  'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and  bigclassId='+convert(varchar,@bigclassId)+'  and isImageNews=1 and gradeId<='+convert(varchar,@gradeId)+' and passed=1 and validDays <> 0 return'--李聪杰20060406					
				ELSE  'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from '+convert(varchar,@table)+' where gate<> '+convert(varchar,@gateId)+' and bigclassId='+convert(varchar,@bigclassId)+' and  gradeId<='+convert(varchar,@gradeId)+' and passed=1 return'
			end
	END	
	Execute (@vSQL)
	execute GetCount @count=@num output
	select @count=@num
RETURN


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


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


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


CREATE PROCEDURE [dbo].[GetNewsByBigClassIdAndusersId]
@bigClassId int=null,
@currentPage int=1,
@pageSize int=5,
@gradeid int=1,
@usersId int,
@gateId int,
@searchStr varchar(100) = ''
AS
	 SET NOCOUNT ON 
	
	declare @vSQL varchar(1000)
select @vSQL = 'select top ' + convert(varchar, @pageSize) + ' n.*,u.userName,d.name "department" ,g.name "grade" 
				from news n,users u,department d,grade g  
				where   n.gate<>'+Convert(varchar,@gateId)+' and
						n.bigClassId='+Convert(varchar,@bigClassId)+' and 
						n.gradeId<='+convert(varchar,@gradeId)+' and 
						n.gradeId=g.pkId and 
						n.departmentId=d.pkId and 
						n.usersId='+convert(varchar,@usersId)+' and 
						n.usersId=u.pkId ' + @searchStr + ' and
						n.pkId <> all 
							(	select top '+Convert(varchar,((@currentPage-1)*@pageSize))+' pkId 
								from news  
								where   n.gate<>'+Convert(varchar,@gateId)+' and  
									bigClassId='+Convert(varchar,@bigClassId)+' and 
									usersId='+convert(varchar,@usersId)+'and 
									gradeId<='+convert(varchar,@gradeId)+ @searchStr + ' 
								order by istop desc,pkId desc
							)  
				order by istop desc,n.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].[GetShowItem]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetShowItem]
GO


CREATE PROCEDURE [dbo].[GetShowItem]
@gradeId int=1,
@parentId int,
@gateId int
AS
	/* SET NOCOUNT ON */
	select m.pkId,m.name,m.itemId,m.bigClassId 'bigClassId',m.gate,m.userIntercept,m.interceptNums,ordering,m.recorderNum,m.menuPic,m.gradeId,t.code,m.layoutTemplateId, m.displayRoot
	  from menu m,bigClass b,template t
	  where m.parentId=@parentId
	    and m.gate<>@gateId
	    and m.gradeId<=@gradeId
	    and m.bigClassId=b.pkId
	    and b.templateId=t.pkId
	order by m.pkId desc,ordering desc
	RETURN

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


CREATE PROCEDURE [dbo].[GetNewsByPkIdAndClick]
@pkId int =null,
@gradeId int=1,
@gateId int 
AS
	 SET NOCOUNT ON 
BEGIN TRANSACTION
	update news
	set click=click+1
	where pkId=@pkId
	select top 1 *
	from news
	where @pkId=pkId
	  and gradeId<=@gradeId
	  and gate<>@gateId
	
	
 COMMIT TRANSACTION
 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产生依赖，避免因栏目移动产生错误
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.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 (n.isDuplicated is null or n.isDuplicated =0 ) and b.pkId = n.bigClassId and b.templateId = t.pkId ORDER BY '+Convert(nvarchar(100),@orderTypeD)	
	--查询最后一页
	ELSE IF @currentPage = @pageCount -1			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' n.*,u.userName,d.name "department" ,g.name "grade",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

