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

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

--创建图片
if exists(select name from sysobjects where name='PPFOR_TZB_Picture_Add' and type='P')
	drop procedure PPFOR_TZB_Picture_Add
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_Picture_Add(@ID int output,@picTitle nvarchar(200),@picDescription text,
	@picAddress text,@smallImage nvarchar(255),@recommended bit,@new bit,@hot bit,@slide bit,
	@author nvarchar(30),@source nvarchar(255),@updateDate datetime,@sort int,@show bit,
	@pageKeyWord nvarchar(255),@pageDescription nvarchar(255),
	@belongType int,@picTemplate nvarchar(255),@click int,@slideImage nvarchar(255),
	@isPublic bit,@allowDegreeIds text,@picIntroduction nvarchar(1000),@htmlName nvarchar(255))
 AS
	begin 
		INSERT INTO [PPFOR_TZB_Picture]([picTitle],[picDescription],[picAddress],[smallImage],
			[recommended],[new],[hot],[slide],[author],[source],[updateDate],[sort],[show],
			[pageKeyWord],[pageDescription],[belongType],[picTemplate],[click],[slideImage],
			[isPublic],[allowDegreeIds],[picIntroduction],[htmlName])VALUES(@picTitle,@picDescription,
			@picAddress,@smallImage,@recommended,@new,@hot,@slide,@author,@source,@updateDate,
			@sort,@show,@pageKeyWord,@pageDescription,@belongType,@picTemplate,
			@click,@slideImage,@isPublic,@allowDegreeIds,@picIntroduction,@htmlName)
		SET @ID = @@IDENTITY
	end
PPFOR_GO;

--删除图片
if exists(select name from sysobjects where name='PPFOR_TZB_Picture_Delete' and type='P')
	drop procedure PPFOR_TZB_Picture_Delete
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_Picture_Delete(@ID int)
 AS 
	DELETE [PPFOR_TZB_Picture] WHERE [ID]=@ID 
PPFOR_GO;

--获取图片列表
if exists(select name from sysobjects where name='PPFOR_TZB_Picture_GetShortList' and type='P')
	drop procedure PPFOR_TZB_Picture_GetShortList
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_Picture_GetShortList(@picParentID int)
 AS 
	SELECT [ID],[belongType],[htmlName] FROM [PPFOR_TZB_Picture] where belongType=@picParentID
PPFOR_GO;

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

--更新图片
if exists(select name from sysobjects where name='PPFOR_TZB_Picture_Update' and type='P')
	drop procedure PPFOR_TZB_Picture_Update
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_Picture_Update(@ID int,@belongType int,@picTitle nvarchar(200),
	@picDescription text,@picAddress text,@smallImage nvarchar(255),@recommended bit,
	@new bit,@hot bit,@slide bit,@author nvarchar(30),@source nvarchar(255),@updateDate datetime,
	@sort int,@show bit,@pageKeyWord nvarchar(255),@pageDescription nvarchar(255),
	@picTemplate nvarchar(255),@click int,@slideImage nvarchar(255),
	@isPublic bit,@allowDegreeIds text,@picIntroduction nvarchar(1000),@htmlName nvarchar(255))
 AS 
	UPDATE [PPFOR_TZB_Picture] SET [belongType]=@belongType,[picTitle] = @picTitle,
		[picDescription] = @picDescription,[picAddress] = @picAddress,[smallImage] = @smallImage,
		[recommended] = @recommended,[new]=@new,[hot]=@hot,[slide]=@slide,[author] = @author,
		[source] = @source,[updateDate] = @updateDate,[sort] = @sort,[show] = @show,
		[pageKeyWord] = @pageKeyWord,[pageDescription] = @pageDescription,
		[picTemplate]=@picTemplate,[click]=@click,[slideImage]=@slideImage,[isPublic]=@isPublic, 
		[allowDegreeIds]=@allowDegreeIds,[picIntroduction]=@picIntroduction,[htmlName]=@htmlName	 WHERE [ID]=@ID 
PPFOR_GO;

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

--分页返回图片资料
if exists(select name from sysobjects where name='PPFOR_TZB_Picture_GetModelListByTypeID' and type='P')
	drop procedure PPFOR_TZB_Picture_GetModelListByTypeID
PPFOR_GO;
create procedure PPFOR_TZB_Picture_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_Picture] 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],picTitle,smallImage,author,show,sort,recommended,new,hot,slide,
			updateDate,htmlName from [PPFOR_TZB_Picture] where [belongType]=' + str(@belongType) + ' '
	
		if @pageIndex <= 1 or @pc <= 1
			set @sqlStr='select top ' + str(@pageSize) + @sqlStr2 + ' order by [sort] desc,[ID] desc'
		else if @pageIndex = @pc
			set @sqlStr ='select * from (select top ' + str( @rc - @pageSize * (@pageIndex-1) ) 
				+ @sqlStr2 + ' order by [sort] asc,[ID] asc) Temptable order by [sort] desc,[ID] desc'
		else  
			set @sqlStr ='select top ' + str(@pageSize) + ' * from (select top '
				+ str( @rc - @pageSize * (@pageIndex-1) ) + @sqlStr2 + ' order by 
				[sort] asc,[ID] asc) Temptable order by [sort] desc,[ID] desc'
		
		exec (@sqlStr)
		select @rc as recordCount,@pc as pageCount
	end
PPFOR_GO;

--分页返回搜索结果
if exists(select name from sysobjects where name='PPFOR_TZB_Picture_GetModelListByWhere' and type='P')
	drop procedure PPFOR_TZB_Picture_GetModelListByWhere
PPFOR_GO;
create procedure PPFOR_TZB_Picture_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_Picture where belongType = any (select [ID] from PPFOR_TZB_PictureType 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,picTitle,smallImage,author,show,sort,updateDate,htmlName 
			from [PPFOR_TZB_Picture] where belongType = any (select [ID] from PPFOR_TZB_PictureType where belongMenu=' + str(@belongMenu) + ') and ' + @whereStr + ' '
	
		if @pageIndex <= 1 or @pc <= 1
			set @sqlStr='select top ' + str(@pageSize) + @sqlStr2 + ' order by [sort] desc,[ID] desc'
		else if @pageIndex = @pc
			set @sqlStr ='select * from (select top ' + str( @rc - @pageSize * (@pageIndex-1) ) 
				+ @sqlStr2 + ' order by [sort] asc,[ID] asc) Temptable order by [sort] desc,[ID] desc'
		else  
			set @sqlStr ='select top ' + str(@pageSize) + ' * from (select top '
				+ str( @rc - @pageSize * (@pageIndex-1) ) + @sqlStr2 + ' order by 
				[sort] asc,[ID] asc) Temptable order by [sort] desc,[ID] desc'
		
		exec (@sqlStr)
		select @rc as recordCount,@pc as pageCount
	end
PPFOR_GO;

--分页返回搜索结果
if exists(select name from sysobjects where name='PPFOR_TZB_Picture_GetModelListBySearch' and type='P')
	drop procedure PPFOR_TZB_Picture_GetModelListBySearch
PPFOR_GO;
create procedure PPFOR_TZB_Picture_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_Picture where show=1 and [belongType]=any (select [ID] from PPFOR_TZB_PictureType 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,picTitle,picIntroduction,smallImage,author,source,updateDate,sort,click,slideImage,htmlName
			from [PPFOR_TZB_Picture] where show=1 and [belongType]=any (select [ID] from PPFOR_TZB_PictureType 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,[ID] desc'
		else if @pageIndex = @pc
			set @sqlStr ='select * from (select top ' + str( @rc - @pageSize * (@pageIndex-1) ) 
				+ @sqlStr2 + ' order by [sort] asc,[ID] asc) Temptable order by [sort] desc,[ID] desc'
		else  
			set @sqlStr ='select top ' + str(@pageSize) + ' * from (select top '
				+ str( @rc - @pageSize * (@pageIndex-1) ) + @sqlStr2 + ' order by 
				[sort] asc,[ID] asc) Temptable order by [sort] desc,[ID] desc'
		
		exec (@sqlStr)
		select @rc as recordCount,@pc as pageCount
	end
PPFOR_GO;

--获取相关图片列表
if exists(select name from sysobjects where name='PPFOR_TZB_Picture_GetRelated' and type='P')
	drop procedure PPFOR_TZB_Picture_GetRelated
PPFOR_GO;
create procedure PPFOR_TZB_Picture_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],[picTitle],[smallImage],[htmlName] from [PPFOR_TZB_Picture] where [ID]<>@id 
				and belongType=@belongType and pageKeyWord like @keyword order by [sort] desc, [ID] desc
		else 
			select [ID],[picTitle],[smallImage],[htmlName] from [PPFOR_TZB_Picture] where [ID]<>@id 
				and belongType=@belongType order by [sort] desc, [ID] desc
	end
PPFOR_GO;

--获取图片列表
if exists(select name from sysobjects where name='PPFOR_TZB_Picture_GetModelListForLabel' and type='P')
	drop procedure PPFOR_TZB_Picture_GetModelListForLabel
PPFOR_GO;
create procedure PPFOR_TZB_Picture_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)
		if @number>=0			
		begin
			set @sqlStr2=' [ID],belongType,picTitle,picIntroduction,smallImage,author,source,updateDate,sort,click,slideImage,htmlName '
			
			if @belongType>0
				set	@sql= 'select top ' + str(@number) + @sqlStr2 + ' from [PPFOR_TZB_Picture] where show=1 and [belongType]=' + str(@belongType)
			else if @belongMenu>0
				set	@sql= 'select top ' + str(@number) + @sqlStr2 + ' from [PPFOR_TZB_Picture] where show=1 and [belongType]=any (select [ID] from PPFOR_TZB_PictureType where show=1 and belongMenu=' + str(@belongMenu) + ')'
			else if @belongLanguage>0
				set	@sql= 'select top ' + str(@number) + @sqlStr2 + ' from [PPFOR_TZB_Picture] where show=1 and [belongType]=any (select [ID] from PPFOR_TZB_PictureType 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, [ID] desc'
				exec (@sql)
			end
		end
	end
PPFOR_GO;

--返回单页图片列表
if exists(select name from sysobjects where name='PPFOR_TZB_Picture_GetModelListByPage' and type='P')
	drop procedure PPFOR_TZB_Picture_GetModelListByPage
PPFOR_GO;
create procedure PPFOR_TZB_Picture_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_Picture] where [show]=1 and [belongType]=@belongType)
		else if @belongMenu>0
			set @rc=(select count([ID]) from [PPFOR_TZB_Picture] where [show]=1 and [belongType]=any (select [ID] from [PPFOR_TZB_PictureType] where [show]=1 and [belongMenu]=@belongMenu))
		else if @belongLanguage>0
			set	@rc=(select count([ID]) from [PPFOR_TZB_Picture] where [show]=1 and [belongType]=any (select [ID] from [PPFOR_TZB_PictureType] 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_Picture] 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_Picture] where show=1 and [belongType]=' + str(@belongType) + ' '
		else if @belongMenu>0
			set	@sqlStr2=' * from [PPFOR_TZB_Picture] where show=1 and [belongType]=any (select [ID] from PPFOR_TZB_PictureType where show=1 and belongMenu=' + str(@belongMenu) + ') '
		else if @belongLanguage>0
			set	@sqlStr2=' * from [PPFOR_TZB_Picture] where show=1 and [belongType]=any (select [ID] from PPFOR_TZB_PictureType 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_Picture] where show=1 '
		if @pageIndex <= 1 or @pc <= 1
			set @sqlStr='select top ' + str(@pageSize) + @sqlStr2 + ' order by [sort] desc,[ID] desc'
		else if @pageIndex = @pc
			set @sqlStr = 'select * from (select top ' + str( @rc - @pageSize * (@pageIndex-1) ) 
				+ @sqlStr2 + ' order by [sort] asc, [ID] asc) Temptable order by [sort] desc,[ID] desc'
		else  
			set @sqlStr ='select top ' + str(@pageSize) + ' * from (select top '
				+ str( @rc - @pageSize * (@pageIndex-1) ) + @sqlStr2 + ' order by [sort] asc,
				[ID] asc) Temptable order by [sort] desc,[ID] desc'
		exec (@sqlStr)
		select @rc as recordCount,@pc as pageCount
	end
PPFOR_GO;

--返回单页图片列表
if exists(select name from sysobjects where name='PPFOR_TZB_Picture_GetShortListByPage' and type='P')
	drop procedure PPFOR_TZB_Picture_GetShortListByPage
PPFOR_GO;
create procedure PPFOR_TZB_Picture_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_Picture] 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_PictureType] where [show]=1 and [belongMenu]='+str(@belongMenu)+')'
		else if @belongLanguage>0
			set @sqlStr2=@sqlStr2+' and [belongType]=any (select [ID] from [PPFOR_TZB_PictureType] 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,picTitle,picIntroduction,smallImage,author,source,updateDate,sort,
			click,slideImage,htmlName from [PPFOR_TZB_Picture] 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_PictureType where show=1 and belongMenu=' + str(@belongMenu) + ') '
		else if @belongLanguage>0
			set	@sqlStr2= @sqlStr2 + ' and [belongType]=any (select [ID] from PPFOR_TZB_PictureType 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,[ID] desc'
		else if @pageIndex = @pc
			set @sqlStr = 'select * from (select top ' + str( @rc - @pageSize * (@pageIndex-1) ) 
				+ @sqlStr2 + ' order by [sort] asc, [ID] asc) Temptable order by [sort] desc,[ID] desc'
		else  
			set @sqlStr ='select top ' + str(@pageSize) + ' * from (select top '
				+ str( @rc - @pageSize * (@pageIndex-1) ) + @sqlStr2 + ' order by [sort] asc,
				[ID] asc) Temptable order by [sort] desc,[ID] desc'
		exec (@sqlStr)
		select @rc as recordCount,@pc as pageCount
	end
PPFOR_GO;

--返回单页图片列表
if exists(select name from sysobjects where name='PPFOR_TZB_Picture_GetModelListInTypeByPage' and type='P')
	drop procedure PPFOR_TZB_Picture_GetModelListInTypeByPage
PPFOR_GO;
create procedure PPFOR_TZB_Picture_GetModelListInTypeByPage(@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_Picture] where [show]=1 and [belongType] 
			in (select [ID] from PPFOR_TZB_PictureType_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,picTitle,picIntroduction,smallImage,author,source,updateDate,sort,
			click,slideImage,htmlName from [PPFOR_TZB_Picture] where show=1 and [belongType] in 
			(select [ID] from PPFOR_TZB_PictureType_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,[ID] desc'
		else if @pageIndex = @pc
			set @sqlStr = 'select * from (select top ' + str( @rc - @pageSize * (@pageIndex-1) ) 
				+ @sqlStr2 + ' order by [sort] asc, [ID] asc) Temptable order by [sort] desc,[ID] desc'
		else  
			set @sqlStr ='select top ' + str(@pageSize) + ' * from (select top '
				+ str( @rc - @pageSize * (@pageIndex-1) ) + @sqlStr2 + ' order by [sort] asc,
				[ID] asc) Temptable order by [sort] desc,[ID] desc'
		exec (@sqlStr)
		select @rc as recordCount,@pc as pageCount
	end
PPFOR_GO;

--获取图片点击数
if exists(select name from sysobjects where name='PPFOR_TZB_Picture_GetClickCount' and type='P')
	drop procedure PPFOR_TZB_Picture_GetClickCount
PPFOR_GO;
create procedure PPFOR_TZB_Picture_GetClickCount(@ID int)
as
	select [click] from [PPFOR_TZB_Picture] where [ID]=@ID
PPFOR_GO;
--获取图片名称
if exists(select name from sysobjects where name='PPFOR_TZB_Picture_GetModelName' and type='P')
	drop procedure PPFOR_TZB_Picture_GetModelName
PPFOR_GO;
create procedure PPFOR_TZB_Picture_GetModelName(@ID int)
as
	select [picTitle] from [PPFOR_TZB_Picture] where [ID]=@ID
PPFOR_GO;
--点击+1，并返回图片动态信息
if exists(select name from sysobjects where name='PPFOR_TZB_Picture_GetVisitInfo' and type='P')
	drop procedure PPFOR_TZB_Picture_GetVisitInfo
PPFOR_GO;
create procedure PPFOR_TZB_Picture_GetVisitInfo(@ID int)
as
	update [PPFOR_TZB_Picture] set [click]=[click]+1 where [show]=1 and [ID]=@ID and isPublic=1
	select [PPFOR_TZB_Picture].[ID],[PPFOR_TZB_PictureType].[ID],
		[PPFOR_TZB_PictureType].[picTypeName],[PPFOR_TZB_Picture].[click] from [PPFOR_TZB_Picture],
		[PPFOR_TZB_PictureType] where [PPFOR_TZB_Picture].[show]=1 and [PPFOR_TZB_Picture].[ID]=@ID 
		and [PPFOR_TZB_Picture].[isPublic]=1
		and [PPFOR_TZB_Picture].[belongType]=[PPFOR_TZB_PictureType].[ID]
PPFOR_GO;	

--点击+1，并返回图片动态信息,权限
if exists(select name from sysobjects where name='PPFOR_TZB_Picture_GetVisitContent' and type='P')
	drop procedure PPFOR_TZB_Picture_GetVisitContent
PPFOR_GO;
create procedure PPFOR_TZB_Picture_GetVisitContent(@ID int)
as
	update [PPFOR_TZB_Picture] set [click]=[click]+1 where [show]=1 and [ID]=@ID
	select [PPFOR_TZB_Picture].[ID],[PPFOR_TZB_PictureType].[ID],
		[PPFOR_TZB_PictureType].[picTypeName],[PPFOR_TZB_Picture].[click],
		[PPFOR_TZB_Picture].[picDescription],[PPFOR_TZB_Picture].[isPublic],
		[PPFOR_TZB_Picture].[allowDegreeIds] from [PPFOR_TZB_Picture],
		[PPFOR_TZB_PictureType] where [PPFOR_TZB_Picture].[show]=1 and [PPFOR_TZB_Picture].[ID]=@ID 
		and [PPFOR_TZB_Picture].[belongType]=[PPFOR_TZB_PictureType].[ID]
PPFOR_GO;	

--返回第一条图片
if exists(select name from sysobjects where name='PPFOR_TZB_Picture_GetFirstPicture' and type='P')
	drop procedure PPFOR_TZB_Picture_GetFirstPicture
PPFOR_GO;
create procedure PPFOR_TZB_Picture_GetFirstPicture(@belongType int) 
as
	select top 1 [ID],[picTitle],[htmlName] from [PPFOR_TZB_Picture] where [show]=1 
		and [belongType]=@belongType order by [sort] desc, [ID] desc
PPFOR_GO;	

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

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

--获取相关图片列表
if exists(select name from sysobjects where name='PPFOR_TZB_Picture_GetRelatedList' and type='P')
	drop procedure PPFOR_TZB_Picture_GetRelatedList
PPFOR_GO;
create procedure PPFOR_TZB_Picture_GetRelatedList(@ID int,@number int)
as
	begin
		declare @keyword nvarchar (255)
		declare @belongType int
		select @keyword=pageKeyWord,@belongType=belongType from [PPFOR_TZB_Picture] 
			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],[picTitle],[smallImage],[htmlName] from [PPFOR_TZB_Picture] where [ID]<>@ID 
					and [show]=1 and belongType=@belongType and pageKeyWord like @keyword 
					order by [sort] desc, [ID] desc
			end
		else if @belongType>0
			select [ID],[picTitle],[smallImage],[htmlName] from [PPFOR_TZB_Picture] where [ID]<>@ID 
				and [show]=1 and belongType=@belongType order by [sort] desc, [ID] desc
	end
PPFOR_GO;
--更新图片静态页文件名
if exists(select name from sysobjects where name='PPFOR_TZB_Picture_UpdateHtmlName' and type='P')
	drop procedure PPFOR_TZB_Picture_UpdateHtmlName
PPFOR_GO;

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

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