-- ==============================
-- 长登科技 www.ppfor.com
-- 唐镇波   2010-04-21
-- ------------------------------
-- 新闻表
-- ==============================

--创建新闻表
if not exists(select name from sysobjects where name='PPFOR_TZB_News' and type='U')
CREATE TABLE [PPFOR_TZB_News] (
	[ID] int IDENTITY (1, 1) NOT NULL PRIMARY KEY,
	[belongType] int NOT NULL ,					--所属类型
	[newsTitle] nvarchar(200) COLLATE Chinese_PRC_CI_AS NOT NULL ,		--新闻标题
	[newsReview] nvarchar(1000) NULL ,			--新闻导读
	[newsContent] text NULL ,					--新闻内容
	[smallImage] nvarchar(255) NULL ,			--小图
	[recommended] bit NOT NULL ,				--是否推荐
	[new] bit NOT NULL ,						--是否最新
	[hot] bit NOT NULL ,						--是否热点
	[slide] bit NOT NULL ,						--是否幻灯片
	[newsTemplate] nvarchar(255) NOT NULL ,		--详细页模板
	[author] nvarchar(30) COLLATE Chinese_PRC_CI_AS NULL ,	--作者
	[source] nvarchar(255) NULL ,				--来源
	[updateDate] datetime NOT NULL ,			--更新时间
	[sort] int NOT NULL DEFAULT (0),			--顺序
	[show] bit NOT NULL DEFAULT (1),			--是否显示
	[pageKeyWord] nvarchar (255) COLLATE Chinese_PRC_CI_AS NULL ,		--META关键字
	[pageDescription] nvarchar (255) COLLATE Chinese_PRC_CI_AS NULL ,	--META描述
	[click] int NOT NULL DEFAULT (0),			--点击	
	[publishDate] datetime NOT NULL ,			--发布时间
	[slideImage] nvarchar (255) NULL,			--幻灯图片
	[isPublic] bit not null,					--是否允许所有人浏览
	[allowDegreeIds] text null,					--限制允许浏览的会员等级
	[htmlName] nvarchar(255) not null			--静态文件名
)
PPFOR_GO;

--添加新闻
if exists(select name from sysobjects where name='PPFOR_TZB_News_Add' and type='P')
	drop procedure PPFOR_TZB_News_Add
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_News_Add(@ID int output,@belongType int,@newsTitle nvarchar(200),
	@newsReview nvarchar(1000),@newsContent text,@smallImage nvarchar(255),@recommended bit,
	@new bit,@hot bit,@slide bit,@newsTemplate nvarchar(255),@author nvarchar(30),
	@source nvarchar(255),@updateDate datetime,@sort int,@show bit,@pageKeyWord nvarchar(255),
	@pageDescription nvarchar(255),@click int,@publishDate datetime,
	@slideImage nvarchar(255),@isPublic bit,@allowDegreeIds text,@htmlName nvarchar(255))
 AS 
	begin
		INSERT INTO [PPFOR_TZB_News](
			[belongType],[newsTitle],[newsReview],[newsContent],[smallImage],[recommended],[new],
			[hot],[slide],[newsTemplate],[author],[source],[updateDate],[sort],[show],[pageKeyWord],
			[pageDescription],[click],[publishDate],[slideImage],isPublic,allowDegreeIds,htmlName)
			VALUES(@belongType,@newsTitle,@newsReview,@newsContent,@smallImage,@recommended,@new,
			@hot,@slide,@newsTemplate,@author,@source,@updateDate,@sort,@show,@pageKeyWord,
			@pageDescription,@click,@publishDate,@slideImage,@isPublic,@allowDegreeIds,@htmlName)
		SET @ID = @@IDENTITY
	end
PPFOR_GO;

--删除新闻
if exists(select name from sysobjects where name='PPFOR_TZB_News_Delete' and type='P')
	drop procedure PPFOR_TZB_News_Delete
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_News_Delete(@ID int)
 AS 
	DELETE [PPFOR_TZB_News] WHERE [ID]=@ID 
PPFOR_GO;

--获取新闻ID列表
if exists(select name from sysobjects where name='PPFOR_TZB_News_GetShortList' and type='P')
	drop procedure PPFOR_TZB_News_GetShortList
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_News_GetShortList(@belongType int)
 AS 
	SELECT [ID],[belongType],[htmlName] FROM [PPFOR_TZB_News] WHERE belongType=@belongType  order by sort,[ID]
PPFOR_GO;

--获取新闻
if exists(select name from sysobjects where name='PPFOR_TZB_News_GetModel' and type='P')
	drop procedure PPFOR_TZB_News_GetModel
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_News_GetModel(@ID int)
 AS 
	SELECT * FROM [PPFOR_TZB_News] WHERE [ID]=@ID 
PPFOR_GO;

--更新新闻
if exists(select name from sysobjects where name='PPFOR_TZB_News_Update' and type='P')
	drop procedure PPFOR_TZB_News_Update
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_News_Update(@ID int,@belongType int,@newsTitle nvarchar(200),
	@newsReview nvarchar(1000),@newsContent text,@smallImage nvarchar(255),@recommended bit,
	@new bit,@hot bit,@slide bit,@newsTemplate nvarchar(255),@author nvarchar(30),
	@source nvarchar(255),@updateDate datetime,@sort int,@show bit,@pageKeyWord nvarchar(255),
	@pageDescription nvarchar(255),@click int,@publishDate datetime,
	@slideImage nvarchar(255),@isPublic bit,@allowDegreeIds text,@htmlName nvarchar(255))
 AS 
	UPDATE [PPFOR_TZB_News] SET [belongType] = @belongType,[newsTitle] = @newsTitle,
		[newsReview] = @newsReview,[newsContent] = @newsContent,[smallImage] = @smallImage,
		[recommended] = @recommended,[new] = @new,[hot] = @hot,[slide] = @slide,
		[newsTemplate] = @newsTemplate,[author] = @author,[source] = @source,
		[updateDate] = @updateDate,[sort] = @sort,[show] = @show,[pageKeyWord] = @pageKeyWord,
		[pageDescription] = @pageDescription,[click] = @click,
		[publishDate]=@publishDate,[slideImage]=@slideImage,isPublic=@isPublic,
		allowDegreeIds=@allowDegreeIds,[htmlName]=@htmlName WHERE [ID]=@ID 
PPFOR_GO;

--更新新闻列表信息
if exists(select name from sysobjects where name='PPFOR_TZB_News_UpdateList' and type='P')
	drop procedure PPFOR_TZB_News_UpdateList
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_News_UpdateList(@ID int,@updateDate datetime,@sort int,@show bit)
 AS 
	UPDATE [PPFOR_TZB_News] SET [updateDate] = @updateDate,[sort] = @sort,[show] = @show
		WHERE [ID]=@ID 
PPFOR_GO;

--分页返回新闻资料
if exists(select name from sysobjects where name='PPFOR_TZB_News_GetModelListByTypeID' and type='P')
	drop procedure PPFOR_TZB_News_GetModelListByTypeID
PPFOR_GO;
create procedure PPFOR_TZB_News_GetModelListByTypeID(@belongType int,@pageSize int,@pageIndex int)
as
	begin
		declare @rc int
		declare @pc int
		declare @sqlStr nvarchar(2000)
		declare @sqlStr2 nvarchar(1000)
		
		if @pageSize<1
			set @pageSize=20

		select @rc=count([ID]) from [PPFOR_TZB_News] where [belongType]=@belongType
		if @rc=0
			set @pc=0
		else
			set @pc = ceiling(@rc * 1.0 / @pageSize)

		if @pageIndex>@pc
			set @pageIndex=@pc
		else if @pageIndex<1
			set @pageIndex=1
		
		set @sqlStr2=' [ID],newsTitle,newsReview,smallImage,author,show,sort,recommended,new,hot,slide,
			publishDate,updateDate,htmlName from [PPFOR_TZB_News] where [belongType]=' + str(@belongType) + ' '
	
		if @pageIndex <= 1 or @pc <= 1
			set @sqlStr='select top ' + str(@pageSize) + @sqlStr2 + ' order by [sort] desc,[publishDate] desc'
		else if @pageIndex = @pc
			set @sqlStr ='select * from (select top ' + str( @rc - @pageSize * (@pageIndex-1) ) 
				+ @sqlStr2 + ' order by [sort] asc,[publishDate] asc) Temptable order by [sort] desc,[publishDate] desc'
		else  
			set @sqlStr ='select top ' + str(@pageSize) + ' * from (select top '
				+ str( @rc - @pageSize * (@pageIndex-1) ) + @sqlStr2 + ' order by 
				[sort] asc,[publishDate] asc) Temptable order by [sort] desc,[publishDate] desc'
		
		exec (@sqlStr)
		select @rc as recordCount,@pc as pageCount
	end
PPFOR_GO;

--分页返回搜索结果
if exists(select name from sysobjects where name='PPFOR_TZB_News_GetModelListByWhere' and type='P')
	drop procedure PPFOR_TZB_News_GetModelListByWhere
PPFOR_GO;
create procedure PPFOR_TZB_News_GetModelListByWhere(@belongMenu int,@whereStr nvarchar(255),@pageSize int,@pageIndex int)
as
	begin
		declare @rc int
		declare @pc int
		declare @TmpSelect nvarchar(1000)
		declare @sqlStr nvarchar(2000)
		declare @sqlStr2 nvarchar(1000)
		
		if @pageSize<1
			set @pageSize=20

		set @TmpSelect = 'select @rc = count([ID]) from PPFOR_TZB_News where belongType = any (select [ID] from PPFOR_TZB_NewsType where belongMenu=' + str(@belongMenu) + ') and ' + @whereStr		
		execute sp_executesql @TmpSelect, N'@rc int output', @rc output

		if @rc=0
			set @pc=0
		else
			set @pc = ceiling(@rc * 1.0 / @pageSize)

		if @pageIndex>@pc
			set @pageIndex=@pc
		else if @pageIndex<1
			set @pageIndex=1
		
		set @sqlStr2=' [ID],belongType,newsTitle,newsReview,smallImage,author,show,sort,publishDate,
			updateDate,htmlName from [PPFOR_TZB_News] where belongType = any (select [ID] from PPFOR_TZB_NewsType where belongMenu=' + str(@belongMenu) + ') and ' + @whereStr + ' '
	
		if @pageIndex <= 1 or @pc <= 1
			set @sqlStr='select top ' + str(@pageSize) + @sqlStr2 + ' order by [sort] desc,[publishDate] desc'
		else if @pageIndex = @pc
			set @sqlStr ='select * from (select top ' + str( @rc - @pageSize * (@pageIndex-1) ) 
				+ @sqlStr2 + ' order by [sort] asc,[publishDate] asc) Temptable order by [sort] desc,[publishDate] desc'
		else  
			set @sqlStr ='select top ' + str(@pageSize) + ' * from (select top '
				+ str( @rc - @pageSize * (@pageIndex-1) ) + @sqlStr2 + ' order by 
				[sort] asc,[publishDate] asc) Temptable order by [sort] desc,[publishDate] desc'
		
		exec (@sqlStr)
		select @rc as recordCount,@pc as pageCount
	end
PPFOR_GO;

--分页返回搜索结果
if exists(select name from sysobjects where name='PPFOR_TZB_News_GetModelListBySearch' and type='P')
	drop procedure PPFOR_TZB_News_GetModelListBySearch
PPFOR_GO;
create procedure PPFOR_TZB_News_GetModelListBySearch(@belongLanguage int,@belongMenu int,@memberDegree int, 
	@whereStr nvarchar(255),@pageSize int,@pageIndex int)
as
	begin
		declare @rc int
		declare @pc int
		declare @TmpSelect nvarchar(1000)
		declare @sqlStr nvarchar(2000)
		declare @sqlStr2 nvarchar(1000)
		
		if @pageSize<1
			set @pageSize=20

		set @TmpSelect = 'select @rc = count([ID]) from PPFOR_TZB_News where show=1 and [belongType]=any (select [ID] from PPFOR_TZB_NewsType where show=1 '
		if @belongMenu>0
			set @TmpSelect=@TmpSelect+' and belongMenu='+str(@belongMenu)+') '
		else
			set @TmpSelect=@TmpSelect+' and belongMenu= any(select [ID] from PPFOR_TZB_Menu where show=1 and belongLanguage=' + str(@belongLanguage) + ')) '
		set @TmpSelect=@TmpSelect+' and ' + @whereStr + ' '	
		
		if @memberDegree=0 
			set	@TmpSelect=@TmpSelect + ' and isPublic=1'
		else if @memberDegree>0
			set	@TmpSelect=@TmpSelect + ' and (isPublic=1 or allowDegreeIds like ''%,'+ rtrim(ltrim(str(@memberDegree)))+',%'')'

		execute sp_executesql @TmpSelect, N'@rc int output', @rc output

		if @rc=0
			set @pc=0
		else
			set @pc = ceiling(@rc * 1.0 / @pageSize)

		if @pageIndex>@pc
			set @pageIndex=@pc
		else if @pageIndex<1
			set @pageIndex=1
		
		set @sqlStr2=' [ID],belongType,newsTitle,newsReview,slideImage,smallImage,author,source,updateDate,sort,click,publishDate,htmlName 
			 from [PPFOR_TZB_News] where show=1 and [belongType]=any (select [ID] from PPFOR_TZB_NewsType where show=1 '
		if @belongMenu>0 
			set @sqlStr2=@sqlStr2+' and belongMenu=' + str(@belongMenu)+') '
		else
			set @sqlStr2=@sqlStr2+' and belongMenu= any(select [ID] from PPFOR_TZB_Menu where show=1 and belongLanguage=' + str(@belongLanguage) + ')) '
		set @sqlStr2=@sqlStr2 + ' and '+ @whereStr + ' '

		if @memberDegree=0 
			set	@sqlStr2=@sqlStr2 + ' and isPublic=1'
		else if @memberDegree>0
			set	@sqlStr2=@sqlStr2 + ' and (isPublic=1 or allowDegreeIds like ''%,'+ rtrim(ltrim(str(@memberDegree)))+',%'')'

		if @pageIndex <= 1 or @pc <= 1
			set @sqlStr='select top ' + str(@pageSize) + @sqlStr2 + ' order by [sort] desc,[publishDate] desc'
		else if @pageIndex = @pc
			set @sqlStr ='select * from (select top ' + str( @rc - @pageSize * (@pageIndex-1) ) 
				+ @sqlStr2 + ' order by [sort] asc,[publishDate] asc) Temptable order by [sort] desc,[publishDate] desc'
		else  
			set @sqlStr ='select top ' + str(@pageSize) + ' * from (select top '
				+ str( @rc - @pageSize * (@pageIndex-1) ) + @sqlStr2 + ' order by 
				[sort] asc,[publishDate] asc) Temptable order by [sort] desc,[publishDate] desc'
		
		exec (@sqlStr)
		select @rc as recordCount,@pc as pageCount
	end
PPFOR_GO;

--获取相关新闻列表
if exists(select name from sysobjects where name='PPFOR_TZB_News_GetRelated' and type='P')
	drop procedure PPFOR_TZB_News_GetRelated
PPFOR_GO;
create procedure PPFOR_TZB_News_GetRelated(@id int,@belongType int,@keyword nvarchar(255),@number int)
as
	begin
		if @number>0
			set ROWCOUNT @number
	 	if @keyword is not null and len(@keyword)>0
			select [ID],[newsTitle],[htmlName] from [PPFOR_TZB_News] where [ID]<>@id and belongType=@belongType 
				and pageKeyWord like @keyword order by [sort] desc, [publishDate] desc
		else 
			select [ID],[newsTitle],[htmlName] from [PPFOR_TZB_News] where [ID]<>@id and belongType=@belongType 
				order by [sort] desc, [publishDate] desc
	end
PPFOR_GO;

--获取新闻列表
if exists(select name from sysobjects where name='PPFOR_TZB_News_GetModelListForLabel' and type='P')
	drop procedure PPFOR_TZB_News_GetModelListForLabel
PPFOR_GO;
create procedure PPFOR_TZB_News_GetModelListForLabel(@belongLanguage int,@belongMenu int,@belongType int,@memberDegree int,@new bit,@recommended bit,@hot bit,@slide bit,@number int)
as
	begin
		declare @sql varchar(1000)
		declare @sqlStr2 varchar(255)
		begin
			set @sqlStr2=' [ID],belongType,newsTitle,newsReview,slideImage,smallImage,author,source,updateDate,sort,click,publishDate,htmlName '
			
			if @belongType>0
				set	@sql= 'select top ' + str(@number) + @sqlStr2 + ' from [PPFOR_TZB_News] where show=1 and [belongType]=' + str(@belongType)
			else if @belongMenu>0
				set	@sql= 'select top ' + str(@number) + @sqlStr2 + ' from [PPFOR_TZB_News] where show=1 and [belongType]=any (select [ID] from [PPFOR_TZB_NewsType] where show=1 and belongMenu=' + str(@belongMenu) + ')'
			else if @belongLanguage>0
				set	@sql= 'select top ' + str(@number) + @sqlStr2 + ' from [PPFOR_TZB_News] where show=1 and [belongType]=any (select [ID] from [PPFOR_TZB_NewsType] where show=1 and belongMenu= any(select [ID] from [PPFOR_TZB_Menu] where show=1 and belongLanguage=' + str(@belongLanguage) + '))'
			
			if @sql is not null
			begin
				if @memberDegree=0 
					set	@sql=@sql + ' and isPublic=1'
				else if @memberDegree>0
					set	@sql=@sql + ' and (isPublic=1 or allowDegreeIds like ''%,'+ rtrim(ltrim(str(@memberDegree)))+',%'')'
				
			 	if @recommended=1 
					set @sql=@sql + ' and [recommended]=1'					
				else if @new=1
					set @sql=@sql + ' and [new]=1'					
				else if @hot=1
					set @sql=@sql + ' and [hot]=1'		
				else if @slide=1
					set @sql=@sql + ' and [slide]=1'				
				set @sql=@sql + ' order by [sort] desc, [publishDate] desc'
				exec (@sql)
			end
		end
	end
PPFOR_GO;

--返回单页新闻列表
if exists(select name from sysobjects where name='PPFOR_TZB_News_GetModelListByPage' and type='P')
	drop procedure PPFOR_TZB_News_GetModelListByPage
PPFOR_GO;
create procedure PPFOR_TZB_News_GetModelListByPage(@belongLanguage int,@belongMenu int,@belongType int,@pageSize int,@pageIndex int) 
as
	begin
		declare @rc int
		declare @pc int
		declare @sqlStr nvarchar(2000)
		declare @sqlStr2 nvarchar(1000)

		if @pageSize<1
			set @pageSize=20
		
		if @belongType>0
			set @rc=(select count([ID]) from [PPFOR_TZB_News] where [show]=1 and [belongType]=@belongType)
		else if @belongMenu>0
			set @rc=(select count([ID]) from [PPFOR_TZB_News] where [show]=1 and [belongType]=any (select [ID] from [PPFOR_TZB_NewsType] where [show]=1 and [belongMenu]=@belongMenu))
		else if @belongLanguage>0
			set	@rc=(select count([ID]) from [PPFOR_TZB_News] where [show]=1 and [belongType]=any (select [ID] from [PPFOR_TZB_NewsType] where [show]=1 and [belongMenu]= any(select [ID] from [PPFOR_TZB_Menu] where [show]=1 and [belongLanguage]=@belongLanguage)))
		else
			set @rc=(select count([ID]) from [PPFOR_TZB_News] where [show]=1)
		
		if @rc=0
			set @pc=0
		else
			set @pc = ceiling(@rc * 1.0 / @pageSize)

		if @pageIndex>@pc
			set @pageIndex=@pc
		else if @pageIndex<1
			set @pageIndex=1
		
		if @belongType>0
			set	@sqlStr2= ' * from [PPFOR_TZB_News] where show=1 and [belongType]=' + str(@belongType) + ' '
		else if @belongMenu>0
			set	@sqlStr2= ' * from [PPFOR_TZB_News] where show=1 and [belongType]=any (select [ID] from [PPFOR_TZB_NewsType] where show=1 and belongMenu=' + str(@belongMenu) + ') '
		else if @belongLanguage>0
			set	@sqlStr2= ' * from [PPFOR_TZB_News] where show=1 and [belongType]=any (select [ID] from [PPFOR_TZB_NewsType] where show=1 and belongMenu= any(select [ID] from [PPFOR_TZB_Menu] where show=1 and belongLanguage=' + str(@belongLanguage) + ')) '
		else 
			set	@sqlStr2= ' * from [PPFOR_TZB_News] where show=1 '
		if @pageIndex <= 1 or @pc <= 1
			set @sqlStr='select top ' + str(@pageSize) + @sqlStr2 + ' order by [sort] desc, [publishDate] desc'
		else if @pageIndex = @pc
			set @sqlStr = 'select * from (select top ' + str( @rc - @pageSize * (@pageIndex-1) ) 
				+ @sqlStr2 + ' order by [sort] asc, [publishDate] asc) Temptable order by [sort] desc, [publishDate] desc'
		else  
			set @sqlStr ='select top ' + str(@pageSize) + ' * from (select top '
				+ str( @rc - @pageSize * (@pageIndex-1) ) + @sqlStr2 + ' order by [sort] asc,
				[publishDate] asc) Temptable order by [sort] desc, [publishDate] desc'
		exec (@sqlStr)
		select @rc as recordCount,@pc as pageCount
	end
PPFOR_GO;

--返回单页新闻列表
if exists(select name from sysobjects where name='PPFOR_TZB_News_GetShortListByPage' and type='P')
	drop procedure PPFOR_TZB_News_GetShortListByPage
PPFOR_GO;
create procedure PPFOR_TZB_News_GetShortListByPage(@belongLanguage int,@belongMenu int,@belongType int,@memberDegree int,@pageSize int,@pageIndex int) 
as
	begin
		declare @rc int
		declare @pc int
		declare @sqlStr nvarchar(2000)
		declare @sqlStr2 nvarchar(1000)

		if @pageSize<1
			set @pageSize=20
		
		set @sqlStr2='select @rc = count([ID]) from [PPFOR_TZB_News] where [show]=1'
		if @belongType>0
			set @sqlStr2=@sqlStr2+' and [belongType]='+str(@belongType)
		else if @belongMenu>0
			set @sqlStr2=@sqlStr2+' and [belongType]=any (select [ID] from [PPFOR_TZB_NewsType] where [show]=1 and [belongMenu]='+str(@belongMenu)+')'
		else if @belongLanguage>0
			set @sqlStr2=@sqlStr2+' and [belongType]=any (select [ID] from [PPFOR_TZB_NewsType] where [show]=1 and [belongMenu]= any(select [ID] from [PPFOR_TZB_Menu] where [show]=1 and [belongLanguage]='+str(@belongLanguage)+'))'

		if @memberDegree=0 
			set	@sqlStr2=@sqlStr2 + ' and isPublic=1'
		else if @memberDegree>0
			set	@sqlStr2=@sqlStr2 + ' and (isPublic=1 or allowDegreeIds like ''%,'+ rtrim(ltrim(str(@memberDegree)))+',%'')'
			
		execute sp_executesql @sqlStr2, N'@rc int output', @rc output

		if @rc=0
			set @pc=0
		else
			set @pc = ceiling(@rc * 1.0 / @pageSize)

		if @pageIndex>@pc
			set @pageIndex=@pc
		else if @pageIndex<1
			set @pageIndex=1
		
		set	@sqlStr2= ' [ID],belongType,newsTitle,newsReview,slideImage,smallImage,author,source,updateDate,
			sort,click,publishDate,htmlName from [PPFOR_TZB_News] where show=1 '

		if @belongType>0
			set	@sqlStr2=@sqlStr2 +  ' and [belongType]=' + str(@belongType) + ' '
		else if @belongMenu>0
			set	@sqlStr2=@sqlStr2 +  ' and [belongType]=any (select [ID] from [PPFOR_TZB_NewsType] where show=1 and belongMenu=' + str(@belongMenu) + ') '
		else if @belongLanguage>0
			set	@sqlStr2=@sqlStr2 +  ' and [belongType]=any (select [ID] from [PPFOR_TZB_NewsType] where show=1 and belongMenu= any(select [ID] from [PPFOR_TZB_Menu] where show=1 and belongLanguage=' + str(@belongLanguage) + ')) '
		
		if @memberDegree=0 
			set	@sqlStr2=@sqlStr2 + ' and isPublic=1'
		else if @memberDegree>0
			set	@sqlStr2=@sqlStr2 + ' and (isPublic=1 or allowDegreeIds like ''%,'+ rtrim(ltrim(str(@memberDegree)))+',%'')'
			
		if @pageIndex <= 1 or @pc <= 1
			set @sqlStr='select top ' + str(@pageSize) + @sqlStr2 + ' order by [sort] desc, [publishDate] desc'
		else if @pageIndex = @pc
			set @sqlStr = 'select * from (select top ' + str( @rc - @pageSize * (@pageIndex-1) ) 
				+ @sqlStr2 + ' order by [sort] asc, [publishDate] asc) Temptable order by [sort] desc, [publishDate] desc'
		else  
			set @sqlStr ='select top ' + str(@pageSize) + ' * from (select top '
				+ str( @rc - @pageSize * (@pageIndex-1) ) + @sqlStr2 + ' order by [sort] asc,
				[publishDate] asc) Temptable order by [sort] desc, [publishDate] desc'
		exec (@sqlStr)
		select @rc as recordCount,@pc as pageCount
	end
PPFOR_GO;

--返回单页新闻列表
if exists(select name from sysobjects where name='PPFOR_TZB_News_GetModelListInTypeByPage' and type='P')
	drop procedure PPFOR_TZB_News_GetModelListInTypeByPage
PPFOR_GO;
create procedure PPFOR_TZB_News_GetModelListInTypeByPage(@belongType int,@memberDegree int,@pageSize int,@pageIndex int) 
as
	begin
		declare @rc int
		declare @pc int
		declare @sqlStr varchar(2000)
		declare @sqlStr2 varchar(1000)

		if @pageSize<1
			set @pageSize=20		
		
		set @sqlStr2='select @rc = count([ID]) from [PPFOR_TZB_News] where [show]=1 and [belongType] 
			in (select [ID] from PPFOR_TZB_NewsType_GetChildIdsTable('+str(@belongType)+') where [show]=1)'
		
		if @memberDegree=0 
			set	@sqlStr2=@sqlStr2 + ' and isPublic=1'
		else if @memberDegree>0
			set	@sqlStr2=@sqlStr2 + ' and (isPublic=1 or allowDegreeIds like ''%,'+ rtrim(ltrim(str(@memberDegree)))+',%'')'
			
		execute sp_executesql @sqlStr2, N'@rc int output', @rc output

		if @rc=0
			set @pc=0
		else
			set @pc = ceiling(@rc * 1.0 / @pageSize)

		if @pageIndex>@pc
			set @pageIndex=@pc
		else if @pageIndex<1
			set @pageIndex=1		
		
		set	@sqlStr2= ' [ID],belongType,newsTitle,newsReview,slideImage,smallImage,author,source,updateDate,
				sort,click,publishDate,htmlName  from [PPFOR_TZB_News] where show=1 and [belongType] in 
			(select [ID] from PPFOR_TZB_NewsType_GetChildIdsTable('+str(@belongType)+') where [show]=1) '		
		
		if @memberDegree=0 
			set	@sqlStr2=@sqlStr2 + ' and isPublic=1'
		else if @memberDegree>0
			set	@sqlStr2=@sqlStr2 + ' and (isPublic=1 or allowDegreeIds like ''%,'+ rtrim(ltrim(str(@memberDegree)))+',%'')'
			
		if @pageIndex <= 1 or @pc <= 1
			set @sqlStr='select top ' + str(@pageSize) + @sqlStr2 + ' order by [sort] desc, [publishDate] desc'
		else if @pageIndex = @pc
			set @sqlStr = 'select * from (select top ' + str( @rc - @pageSize * (@pageIndex-1) ) 
				+ @sqlStr2 + ' order by [sort] asc, [publishDate] asc) Temptable order by [sort] desc, [publishDate] desc'
		else  
			set @sqlStr ='select top ' + str(@pageSize) + ' * from (select top '
				+ str( @rc - @pageSize * (@pageIndex-1) ) + @sqlStr2 + ' order by [sort] asc,
				[publishDate] asc) Temptable order by [sort] desc, [publishDate] desc'
		exec (@sqlStr)
		select @rc as recordCount,@pc as pageCount
	end
PPFOR_GO;

--获取新闻点击数
if exists(select name from sysobjects where name='PPFOR_TZB_News_GetClickCount' and type='P')
	drop procedure PPFOR_TZB_News_GetClickCount
PPFOR_GO;
create procedure PPFOR_TZB_News_GetClickCount(@ID int)
as
	select [click] from [PPFOR_TZB_News] where [ID]=@ID
PPFOR_GO;

--获取新闻名称
if exists(select name from sysobjects where name='PPFOR_TZB_News_GetModelName' and type='P')
	drop procedure PPFOR_TZB_News_GetModelName
PPFOR_GO;
create procedure PPFOR_TZB_News_GetModelName(@ID int)
as
	select [newsTitle] from [PPFOR_TZB_News] where [ID]=@ID
PPFOR_GO;

--点击+1，并返回新闻动态信息
if exists(select name from sysobjects where name='PPFOR_TZB_News_GetVisitInfo' and type='P')
	drop procedure PPFOR_TZB_News_GetVisitInfo
PPFOR_GO;
create procedure PPFOR_TZB_News_GetVisitInfo(@ID int)
as
	update [PPFOR_TZB_News] set [click]=[click]+1 where [show]=1 and [ID]=@ID and isPublic=1
	select [PPFOR_TZB_News].[ID],[PPFOR_TZB_NewsType].[ID],[PPFOR_TZB_NewsType].[typeName],
		[PPFOR_TZB_News].[click] from [PPFOR_TZB_News],[PPFOR_TZB_NewsType] 
		where [PPFOR_TZB_News].[show]=1 and [PPFOR_TZB_News].[ID]=@ID 
		and [PPFOR_TZB_News].isPublic=1	
		and [PPFOR_TZB_News].[belongType]=[PPFOR_TZB_NewsType].[ID] 
PPFOR_GO;	

--点击+1，并返回新闻动态信息，权限
if exists(select name from sysobjects where name='PPFOR_TZB_News_GetVisitContent' and type='P')
	drop procedure PPFOR_TZB_News_GetVisitContent
PPFOR_GO;
create procedure PPFOR_TZB_News_GetVisitContent(@ID int)
as	
	update [PPFOR_TZB_News] set [click]=[click]+1 where [show]=1 and [ID]=@ID
	select [PPFOR_TZB_News].[ID],[PPFOR_TZB_NewsType].[ID],[PPFOR_TZB_NewsType].[typeName],
		[PPFOR_TZB_News].[click],[PPFOR_TZB_News].[newsContent],[PPFOR_TZB_News].[isPublic],
		[PPFOR_TZB_News].[allowDegreeIds] from [PPFOR_TZB_News],[PPFOR_TZB_NewsType] 
		where [PPFOR_TZB_News].[show]=1 and [PPFOR_TZB_News].[ID]=@ID 
		and [PPFOR_TZB_News].[belongType]=[PPFOR_TZB_NewsType].[ID]
PPFOR_GO;	

--返回第一条新闻
if exists(select name from sysobjects where name='PPFOR_TZB_News_GetFirstNews' and type='P')
	drop procedure PPFOR_TZB_News_GetFirstNews
PPFOR_GO;
create procedure PPFOR_TZB_News_GetFirstNews(@belongType int) 
as
	select top 1 [ID],[newsTitle],[htmlName] from [PPFOR_TZB_News] where [show]=1 
		and [belongType]=@belongType order by [sort] desc, [publishDate] desc
PPFOR_GO;	

--返回上一条新闻
if exists(select name from sysobjects where name='PPFOR_TZB_News_GetPrevNews' and type='P')
	drop procedure PPFOR_TZB_News_GetPrevNews
PPFOR_GO;
create procedure PPFOR_TZB_News_GetPrevNews(@ID int) 
as
	select top 1 [ID],[newsTitle],[htmlName] from [PPFOR_TZB_News] where [show]=1 and [ID]>@ID 
		and [belongType]=(select [belongType] from [PPFOR_TZB_News] where [show]=1 and [ID]=@ID)
		order by [ID] asc
PPFOR_GO;

--返回下一条新闻
if exists(select name from sysobjects where name='PPFOR_TZB_News_GetNextNews' and type='P')
	drop procedure PPFOR_TZB_News_GetNextNews
PPFOR_GO;
create procedure PPFOR_TZB_News_GetNextNews(@ID int) 
as
	select top 1 [ID],[newsTitle],[htmlName] from [PPFOR_TZB_News] where [show]=1 and [ID]<@ID 
		and [belongType]=(select [belongType] from [PPFOR_TZB_News] where [show]=1 and [ID]=@ID) 
		order by [ID] desc
PPFOR_GO;	

--获取相关新闻列表
if exists(select name from sysobjects where name='PPFOR_TZB_News_GetRelatedList' and type='P')
	drop procedure PPFOR_TZB_News_GetRelatedList
PPFOR_GO;
create procedure PPFOR_TZB_News_GetRelatedList(@ID int,@number int)
as
	begin
		declare @keyword nvarchar (255)
		declare @belongType int
		select @keyword=pageKeyWord,@belongType=belongType from [PPFOR_TZB_News] where [show]=1 and [ID]=@ID 
		if @number>0
			set ROWCOUNT @number
	 	if @keyword is not null and len(@keyword)>0
			begin
				exec PPFOR_TZB_Meta_FormatKeyWord @keyword,@keyword out	
				select [ID],[newsTitle],[htmlName] from [PPFOR_TZB_News] where [ID]<>@ID and [show]=1 and belongType=@belongType 
					and pageKeyWord like @keyword order by [sort] desc, [publishDate] desc
			end
		else if @belongType>0
			select [ID],[newsTitle],[htmlName] from [PPFOR_TZB_News] where [ID]<>@ID and [show]=1 and belongType=@belongType 
				order by [sort] desc, [publishDate] desc
	end
PPFOR_GO;

--更新新闻静态页文件名
if exists(select name from sysobjects where name='PPFOR_TZB_News_UpdateHtmlName' and type='P')
	drop procedure PPFOR_TZB_News_UpdateHtmlName
PPFOR_GO;

CREATE PROCEDURE PPFOR_TZB_News_UpdateHtmlName(@ID int,@htmlName nvarchar(255))
 AS 
	UPDATE [PPFOR_TZB_News] SET [htmlName] = @htmlName WHERE [ID]=@ID 
PPFOR_GO;

--获取新闻静态文件名
if exists(select name from sysobjects where name='PPFOR_TZB_News_GetHtmlName' and type='P')
	drop procedure PPFOR_TZB_News_GetHtmlName
PPFOR_GO;
create procedure PPFOR_TZB_News_GetHtmlName(@ID int)
as
	select [htmlName] from [PPFOR_TZB_News] where [ID]=@ID 
PPFOR_GO;

