SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

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

CREATE PROCEDURE InsertResourceClick
	-- Add the parameters for the stored procedure here
  (@pkId int)
as begin
    update ResourceClick set clicks = clicks+1 where ResourceId = @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].[GetDownloadFileListBypkId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetDownloadFileListBypkId]
GO

CREATE PROCEDURE GetDownloadFileListBypkId
	@searchStr varchar(500),
	@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(ResourceList.pkId) FROM ResourceList,ResourceClick 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) + ' ResourceList.pkId,ResourceList.FileName,ResourceList.FilePath,ResourceList.FileSize,ResourceList.Grade,ResourceList.Subject,ResourceList.AutoType,ResourceClick.clicks FROM ResourceList,ResourceClick WHERE ' + @searchStr + ' ORDER BY ResourceClick.clicks DESC,ResourceList.pkId asc'	
	ELSE IF @pageIndex = @pageCount -1			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' ResourceList.pkId,ResourceList.FileName,ResourceList.FilePath,ResourceList.FileSize,ResourceList.Grade,ResourceList.Subject,ResourceList.AutoType,ResourceClick.clicks FROM ResourceList,ResourceClick WHERE ' + @searchStr + ' ORDER BY Resourceclick.clicks ASC,ResourceList.pkId DESC) TempTable ORDER BY clicks DESC,pkId asc'	
	ELSE IF @pageIndex > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @pageIndex ) + ' ResourceList.pkId,ResourceList.FileName,ResourceList.FilePath,ResourceList.FileSize,ResourceList.Grade,ResourceList.Subject,ResourceList.AutoType,ResourceClick.clicks FROM ResourceList,ResourceClick WHERE ' + @searchStr + ' ORDER BY Resourceclick.clicks ASC,ResourceList.pkId DESC) TempTable ORDER BY clicks DESC,pkId asc'
	ELSE BEGIN							
		SET @pageIndex=@pageIndex+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @pageIndex ) + ' ResourceList.pkId,ResourceList.FileName,ResourceList.FilePath,ResourceList.FileSize,ResourceList.Grade,ResourceList.Subject,ResourceList.AutoType,ResourceClick.clicks FROM ResourceList,ResourceClick WHERE ' + @searchStr + ' ORDER BY Resourceclick.clicks DESC,ResourceList.pkId ASC) TempTable1 '
					+ ' ORDER BY clicks ASC,pkId DESC) TempTable2 ORDER BY clicks DESC,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].[GetShowItem]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetShowItem]
GO



CREATE PROCEDURE [dbo].[GetShowItem]
@gradeId int=1,
@parentId int,
@gateId int
AS
	select m.pkId,m.name,m.itemId,m.bigClassId 'bigClassId',m.gate,m.userIntercept,m.interceptNums,m.columnNum,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].[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,@IsShowNumber int)
AS
	SET NOCOUNT OFF;
insert into questionnaireSpecial(bigClassId,questionnaireSpecialName,validDays,gate,gradeId,IsAnonymity,restrictObject,restrictMode,restrictTime,IsVerify,generateDate,IsShow,IsShowNumber) values(@bigClassId,@questionnaireSpecialName,@validDays,@gate,@gradeId,@IsAnonymity,@restrictObject,@restrictMode,@restrictTime,@IsVerify,@generateDate,@IsShow,@IsShowNumber)
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), @bigClassId 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 (bigClassId = @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].[InsertMenu]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertMenu]
GO



CREATE PROCEDURE [dbo].[InsertMenu]
(
	@pkId int,
	@parentId int,
	@name varchar(100),
	@remark varchar(100)=null,
	@ordering int=0,
	@address varchar(100)=null,
	@bigClassId int=0,
	@modeId int,
	@gradeId int,
	@gate int=0,
	@recorderNum int=7,
	@menuPic varchar(100),
	@itemId varchar(100),
	@layoutTemplateId int,
	@adminId int,
	@displayRoot int,
	@userIntercept int,
	@interceptNums int,
    @columnNum     int
)
AS
	SET NOCOUNT OFF;
BEGIN TRANSACTION
INSERT INTO menu(parentId, [name], remark, ordering, address, bigClassId, modeId, gradeId,gate,recorderNum,menuPic,itemId,layoutTemplateId,adminId,displayRoot, userIntercept,interceptNums,columnNum) VALUES (@parentId, @name, @remark, @ordering, @address,@bigClassId, @modeId, @gradeId,@gate,@recorderNum,@menuPic,@itemId,@layoutTemplateId,@adminId,@displayRoot,@userIntercept,@interceptNums,@columnNum)
declare @menuId int
select @menuId=@@IDENTITY
declare @n varchar(100)
select @n=[name] from mode where pkId=@modeId
if(@n='频道')
begin
select @address='index.aspx?pkId='+convert(varchar,@menuId)
update menu set address=@address where pkId=@menuId
end
if(@n='栏目')
begin
     insert into artDesign([name],menuId,leftVert,rightVert,fontPosition,color,[size],[space],font,bold,italic,topColor,middleColor,bottomColor,border,cellPad,cellSpace,brdrColor,leftMiddleFrontWidth,leftMiddleFrontHeight,centerTopFrontWidth,centerTopFrontHeight,rightMiddleFrontWidth,rightMiddleFrontHeight,centerTopBackHeight,middleTableBorderColor,bottomMoreInfoImageVar) values(@name,@menuId,'middle','middle','left','#000000',3,2,'宋体',0,0,'','','',0,0,0,'',20,20,20,20,20,20,25,'','more.gif')
     select @layoutTemplateId=pkId 
        from layoutTemplate
        where layoutTemplateType=1 
          and isSystem=1
     update menu set layoutTemplateId=@layoutTemplateId where pkId=@menuId
  end
 COMMIT TRANSACTION
GO
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].[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,@txtName varchar(1000), @pkId int)
AS
	SET NOCOUNT OFF;
INSERT INTO options(questionId, IsTextbox, optionsName, counter, isPic,txtName) VALUES (@questionId, @IsTextbox, @optionsName, @counter, @isPic, @txtName);
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 ON 
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, @txtName varchar(1000), @pkId int)
AS
	SET NOCOUNT OFF;
UPDATE options SET questionId = @questionId, IsTextbox = @IsTextbox, optionsName = @optionsName, counter = @counter, isPic = @isPic, txtName = @txtName 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].[UpdateMenu]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateMenu]
GO




CREATE PROCEDURE [dbo].[UpdateMenu]
(
	@parentId int,
	@name varchar(100),
	@remark varchar(100),
	@ordering int,
	@address varchar(100),
	@bigClassId int,
	@modeId int,
	@gradeId int,
	@pkId int,
	@gate int,
	@recorderNum int,
	@menuPic varchar(100),
	@itemId  varchar(100),
	@layoutTemplateId int,
	@adminId int,
	@displayRoot int,
	@userIntercept int,
	@interceptNums int,
    @columnNum     int
)
AS
	SET NOCOUNT OFF;
 BEGIN TRANSACTION
declare @n varchar(100)
select @n=[name] from mode where pkId=@modeId
if(@n='频道')
begin
select @address='index.aspx?pkId='+convert(varchar,@pkId)
end
UPDATE menu SET columnNum=@columnNum,interceptNums=@interceptNums,userIntercept=@userIntercept, displayRoot=@displayRoot,parentId = @parentId, name = @name, remark = @remark, ordering = @ordering, address = @address, bigClassId = @bigClassId, modeId = @modeId, gradeId = @gradeId,gate=@gate ,recorderNum=@recorderNum,menuPic=@menuPic,itemId=@itemId,layoutTemplateId=@layoutTemplateId ,adminId=@adminId WHERE (pkId = @pkId)
 COMMIT TRANSACTION
GO
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].[GetDownloadFileBypkId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetDownloadFileBypkId]
GO

CREATE PROCEDURE GetDownloadFileBypkId
	-- Add the parameters for the stored procedure here
	@pkId int
as
SELECT *
    from ResourceList
    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].[GetSpecialShowTypeBybigClassId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetSpecialShowTypeBybigClassId]
GO

CREATE PROCEDURE GetSpecialShowTypeBybigClassId
	-- Add the parameters for the stored procedure her
@bigClassId int
as
SELECT * from DownloadfileSpecialTemplate 
where bigClassId = @bigClassId
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].[GetSpecialShowTypeListBybigClassId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetSpecialShowTypeListBybigClassId]
GO

CREATE PROCEDURE GetSpecialShowTypeListBybigClassId
	-- Add the parameters for the stored procedure here
	@bigClassId  int
as
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
   if (@bigClassId <> 0)
BEGIN
    -- Insert statements for procedure here
	SELECT Grade
    from Resourcelist 
    where downloadspecialid = @bigClassId
    Group by Grade
   
    SELECT Subject
    from Resourcelist 
    where downloadspecialid = @bigClassId
    Group by Subject

    SELECT AutoType
    from Resourcelist 
    where downloadspecialid = @bigClassId
    Group by AutoType
END
   ELSE
BEGIN
   SELECT Grade
    from Resourcelist 
    Group by Grade
   
    SELECT Subject
    from Resourcelist 
    Group by Subject

    SELECT AutoType
    from Resourcelist 
    Group by AutoType
END
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].[DeleteDownloadFileBypkId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DeleteDownloadFileBypkId]
GO

CREATE PROCEDURE DeleteDownloadFileBypkId
	-- Add the parameters for the stored procedure here
	@pkId int
as
delete
    from 
    ResourceList
    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].[InsertDownloadFile]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertDownloadFile]
GO

CREATE PROCEDURE InsertDownloadFile
	-- Add the parameters for the stored procedure here
	
(@downloadspecialId int,@FileSize int, @fileName varchar(1000), @filepath varchar(1000), @grade varchar(100), @subject varchar(100), @autotype varchar(100),@createtime Datetime,@updatetime datetime,@pkId int)
as
INSERT INTO resourcelist(downloadspecialId,FileSize, fileName, filepath, grade, subject,autotype,createtime,updatetime) VALUES (@downloadspecialId,@FileSize, @fileName, @filepath, @grade, @subject,@autotype,@createtime,@updatetime)
INSERT INTO resourceclick(ResourceId) 
(
select top 1 pkid 
from ResourceList 
where downloadspecialId=@downloadspecialId and filepath=@filepath and filesize=@FileSize and grade=@grade and subject=@subject and autotype=@autotype
)
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].[UpdateDownloadFile]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateDownloadFile]
GO

CREATE PROCEDURE UpdateDownloadFile
	-- Add the parameters for the stored procedure here
	(@downloadspecialId int,@FileSize int, @fileName varchar(1000), @filepath varchar(1000), @grade varchar(100), @subject varchar(100), @autotype varchar(100),@createtime Datetime,@updatetime datetime,@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
	UPDATE resourcelist 
SET downloadspecialId = @downloadspecialId,
Filesize = @FileSize,
fileName = @fileName,
filepath = @filepath,
grade = @grade,
subject = @subject,
autotype = @autotype,
createtime = @createtime,
updatetime = @updatetime

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

CREATE PROCEDURE InsertResourceClick
	-- Add the parameters for the stored procedure here
  (@pkId int)
as begin
    update ResourceClick set clicks = clicks+1 where ResourceId = @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].[GetTopOneNewsByBigClassId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetTopOneNewsByBigClassId]
GO



create PROCEDURE [dbo].[GetTopOneNewsByBigClassId] 
	@bigClassId int 
AS 
	select	top 1 *
	from	news 
    where bigClassId=@bigClassId and isDuplicated=0
	


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].[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'    沈建昌20080821
		--and t.code<>'SearchHorizontal'   沈建昌20080821
		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].[UpdateSearchOptions]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateSearchOptions]
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[UpdateSearchOptions]
	-- Add the parameters for the stored procedure here
	(@bigClassId int,
     @OptionsName varchar(3500), 
	@OptionsValue varchar(3500))
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  not exists(select   1   from   searchOptions   where   bigClassId=@bigClassId)   
  begin       
  insert into searchOptions(bigClassId,OptionsName,OptionsValue) values(@bigClassId,@OptionsName,@OptionsValue) 
    end   
  else   
  update searchOptions set OptionsName = @OptionsName , OptionsValue=@OptionsValue where bigClassId = @bigClassId
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].[GetsearchOptions]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetsearchOptions]
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE GetsearchOptions
	-- Add the parameters for the stored procedure here
	(@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 * from searchOptions where bigClassId = @bigClassId
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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







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

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

    IF (@type=0)
		BEGIN
			SET @str = @str + ' and (listTitle like ''' + @searchStr + ''''
			SET @str = @str + ' or intactTitle like ''' + @searchStr + ''''
			SET @str = @str + ' or subheading like ''' + @searchStr + ''''
			SET @str = @str + ' or content like ''' + @searchStr + ''''
			SET @str = @str + ' or author like ''' + @searchStr + ''')' 
		END
	IF(@type=1)
		BEGIN
			SET @str = @str + ' and (listTitle like ''' + @searchStr + ''''
			SET @str = @str + ' or intactTitle like ''' + @searchStr + ''''
			SET @str = @str + ' or subheading like ''' + @searchStr + ''')'
		END
	IF(@type=2)
		SET @str = @str + ' and author like ''' + @searchStr + ''''
	IF(@type=3)
		SET @str = @str + ' and content like ''' + @searchStr + ''''

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

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE InsertMedianews 
	-- Add the parameters for the stored procedure here
(
	@specialId int,
	@gradeId int,
	@gate int,
	@departmentId int,
	@listTitle varchar(1000),
	@intactTitle varchar(500),
	@subheading varchar(500),
	@isRecommand int,
	@isTop int,
	@useListTitle int,
	@showReviewLink int,
	@Author varchar(100),
	@AuthorMail varchar(100),
	@CopyFrom varchar(100),
	@copyFromLink varchar(100),
	@about varchar(100),
	@pageType int,
	@autoPageCharNum int,
	@readPoint int,
	@itemType int,
	@content text,
	@descript varchar(1000),
	@usersId int,
	@editor varchar(100),
	@click int,
	@updateTime datetime,
	@passed int,
	@isReview int,
	@isImageNews int,
	@imageName varchar(100),
	@MenuId int,
	@bigClassId int,
	@width int,
	@height int,
	@pkId int output,
	@updateIp varchar(100),
	@useSubHeading int,
	@isDuplicated int=0,
	@duplicateFrom varchar(100),
	@duplicatedPkId int,
	@validDays int,
    @mediaAutostart 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
	 BEGIN TRANSACTION
IF (@validDays is null)
	set @validDays = -1	
IF (@isDuplicated is null)
	set @isDuplicated = 0
IF (@duplicatedPkId is null)
	set @duplicatedPkId = 0
INSERT INTO news(specialId, gradeId, gate, departmentId, listTitle, intactTitle, subheading, isRecommand, isTop, useListTitle, showReviewLink, Author, AuthorMail, CopyFrom, copyFromLink, about, pageType, autoPageCharNum, readPoint, itemType, content, descript, usersId, editor, click, updateTime, passed, isReview, isImageNews, imageName, MenuId, bigClassId, width, height,updateIp,useSubHeading, isDuplicated, duplicateFrom, duplicatedPkId, validDays, mediaAutostart) VALUES (@specialId, @gradeId, @gate, @departmentId, @listTitle, @intactTitle, @subheading, @isRecommand, @isTop, @useListTitle, @showReviewLink, @Author, @AuthorMail, @CopyFrom, @copyFromLink, @about, @pageType, @autoPageCharNum, @readPoint, @itemType, @content, @descript, @usersId, @editor, @click, @updateTime, @passed, @isReview, @isImageNews, @imageName, @MenuId, @bigClassId, @width, @height,@updateIp,@useSubHeading, @isDuplicated, @duplicateFrom, @duplicatedPkId, @validDays, @mediaAutostart)
SET @pkId = @@IDENTITY
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].[UpdateMediaNews]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateMediaNews]
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE UpdateMediaNews
(
	-- Add the parameters for the stored procedure here
	@specialId int,
	@gradeId int,
	@gate int,
	@departmentId int,
	@listTitle varchar(1000),
	@intactTitle varchar(500),
	@subheading varchar(500),
	@isRecommand int,
	@isTop int,
	@useListTitle int,
	@showReviewLink int,
	@Author varchar(100),
	@AuthorMail varchar(100),
	@CopyFrom varchar(100),
	@copyFromLink varchar(100),
	@about varchar(100),
	@pageType int,
	@autoPageCharNum int,
	@readPoint int,
	@itemType int,
	@content text,
	@descript varchar(1000),
	@usersId int,
	@editor varchar(100),
	@click int,
	@updateTime datetime,
	@passed int,
	@isReview int,
	@isImageNews int,
	@imageName varchar(100),
	@MenuId int,
	@bigClassId int,
	@width int,
	@height int,
	@pkId int,
	@updateIp  varchar(100),
	@useSubHeading int,
	@isDuplicated int=0,
	@duplicateFrom varchar(100),
	@duplicatedPkId	int,
	@validDays int,
    @mediaAutostart 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 (@validDays is null)
	set @validDays = -1	
UPDATE news SET useSubHeading=@useSubHeading,updateIp=@updateIp,specialId = @specialId, gradeId = @gradeId, gate = @gate, departmentId = @departmentId, listTitle = @listTitle, intactTitle = @intactTitle, subheading = @subheading, isRecommand = @isRecommand, isTop = @isTop, useListTitle = @useListTitle, showReviewLink = @showReviewLink, Author = @Author, AuthorMail = @AuthorMail, CopyFrom = @CopyFrom, copyFromLink = @copyFromLink, about = @about, pageType = @pageType, autoPageCharNum = @autoPageCharNum, readPoint = @readPoint, itemType = @itemType, content = @content, descript = @descript, usersId = @usersId, editor = @editor, click = @click, updateTime = @updateTime, passed = @passed, isReview = @isReview, isImageNews = @isImageNews, imageName = @imageName, MenuId = @MenuId, bigClassId = @bigClassId, width = @width, height = @height, isDuplicated = @isDuplicated, duplicateFrom = @duplicateFrom, duplicatedPkId = @duplicatedPkId, validDays = @validDays, mediaAutostart=@mediaAutostart WHERE (pkId = @pkId)
UPDATE news SET passed = @passed WHERE duplicatedPkId=@pkId AND duplicateFrom='news'			-- 更新news时，同时更新news抄送的信息
UPDATE board SET passed = @passed WHERE duplicatedPkId=@pkId AND duplicateFrom = 'news'		-- 更新news时，同时更新board抄送的信息
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].[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),
    @subMenuFontSize 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,subMenuFontSize
) 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,@subMenuFontSize)






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





CREATE PROCEDURE [dbo].[GetIncludePageByPkId]
@pkId int,
@gradeid int=1,
@gateId int
AS
	 SET NOCOUNT ON 
	
	select a.*,g.name 'grade' from IncludePage a,grade g
	where @pkId=a.pkId 
	  and a.gradeId=g.pkId 
	  and @gradeid>=a.gradeid
	
	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].[InsertAdmin]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertAdmin]
GO





CREATE PROCEDURE [dbo].[InsertAdmin]
(
	@pkId int,
	@adminName varchar(100),
	@fullName varchar(100),
	@password varchar(100),
	@allowAllAssign int,
	@allowEditOthersAdmin int,
	@isEqualAdmin int,
	@allowLogin int,
	@isSbwAdmin int
)
AS
	SET NOCOUNT OFF;
INSERT INTO Admin(adminName, fullName, password, allowAllAssign, allowEditOthersAdmin, isEqualAdmin,allowLogin, isSbwAdmin) VALUES (@adminName, @fullName, @password, @allowAllAssign, @allowEditOthersAdmin, @isEqualAdmin,@allowLogin,@isSbwAdmin)




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




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




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




CREATE PROCEDURE [dbo].[UpdateAdmin]
(
	@adminName varchar(100),
	@fullName varchar(100),
	@password varchar(100),
	@allowAllAssign int,
	@allowEditOthersAdmin int,
	@isEqualAdmin int,
	@allowLogin int,
	
	@pkId int,
	@isSbwAdmin int
)
AS
	SET NOCOUNT OFF;
UPDATE Admin SET adminName = @adminName, fullName = @fullName, password = @password, allowAllAssign = @allowAllAssign, allowEditOthersAdmin = @allowEditOthersAdmin, isEqualAdmin = @isEqualAdmin ,allowLogin=@allowLogin, isSbwAdmin=@isSbwAdmin 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].[UpdateUsers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateUsers]
GO




CREATE PROCEDURE [dbo].[UpdateUsers]
(
	@userName varchar(100),
	@fullName varchar(100),
	@password varchar(100),
	@question varchar(100),
	@answer varchar(100),
	@sex varchar(10),
	@born datetime,
	@QQ varchar(100),
	@email varchar(100),
	@intro varchar(100),
	@loginIp varchar(100),
	@loginNum int,
	@loginTime datetime,
	@logIp varchar(100),
	@logTime datetime,
	@tel varchar(100),
	@photo varchar(100),
	@examine int,
	@gradeId int,
	@departmentId int,
	@company varchar(100),
	@appear int,
	@networkTest int,
	@Auditing int,
	@manageAll int,
	@usersGroupId int,
	@readInfoType int,
	@topRight int,
	@recommandRight int,
	@pkId int,
	@isSbwUser int
)
AS
	SET NOCOUNT OFF;
UPDATE Users SET userName = @userName, fullName = @fullName, password = @password, question = @question, answer = @answer, sex = @sex, born = @born, QQ = @QQ, email = @email, intro = @intro, loginIp = @loginIp, loginNum = @loginNum, loginTime = @loginTime, logIp = @logIp, logTime = @logTime, tel = @tel, photo = @photo, examine = @examine, gradeId = @gradeId, departmentId = @departmentId, company = @company, appear = @appear, networkTest = @networkTest, Auditing = @Auditing, manageAll = @manageAll, usersGroupId = @usersGroupId, readInfoType = @readInfoType, topRight = @topRight, recommandRight = @recommandRight, isSbwUser=@isSbwUser 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].[GetUsersByPageSize]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetUsersByPageSize]
GO




CREATE PROCEDURE [dbo].[GetUsersByPageSize]
@currentPage int=1,
@pageSize int,
@searchStr varchar(100)
AS
	SET NOCOUNT ON
declare @vSQL varchar(1000)
select @vSQL = 'select top ' + convert(varchar, @pageSize) + ' u.*,g.name "grade",d.name "department" from   users u,grade g,department d where '+@searchStr+' and u.pkId  in  (select top '+Convert(varchar,((@currentPage)*@pageSize))+' pkId from  users order by pkId desc) and u.pkId not in  (select top '+Convert(varchar,((@currentPage-1)*@pageSize))+' pkId from  users order by pkId desc)  and u.gradeId=g.pkId   and u.departmentId=d.pkId order by u.pkId desc'
Execute (@vSQL)	
RETURN 




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
