-- ==============================
-- 长登科技 www.ppfor.com
-- 唐镇波   2010-04-19
-- ------------------------------
-- 下载文件表
-- ==============================

if not exists (SELECT * FROM  sysobjects WHERE  name = 'PPFOR_TZB_DownloadFile')
create table PPFOR_TZB_DownloadFile
(
 [ID] int identity(1,1) not null primary key,			--自动编号
 FileTitle nvarchar(200) COLLATE Chinese_PRC_CI_AS not null, 					--文件名称
 FileSize int not null, 								--上传文件大小
 FileFormat varchar(8) not null,						--文件格式
 FileDescription text null,								--文件描述
 BelongTypeID int not null,								--所属类型ID
 UploadUser nvarchar(30) null,							--上传文件的管理员名
 UploadDate datetime not null,							--上传文件修改时间
 IsLink bit not null,									--是否外连接下载
 IsShow bit not null,									--是否显示
 DownloadCount int not null,							--下载次数
 Click int not null, 									--浏览次数
 SmallImage nvarchar(255) null,							--小图
 SlideImage nvarchar(255) null,							--幻灯图片
 FileTemplate nvarchar(255) not null,					--详细页模板
 PageKeyWord nvarchar(255) COLLATE Chinese_PRC_CI_AS null,						--Meta关键字
 PageDescription nvarchar(255) COLLATE Chinese_PRC_CI_AS null,					--Meta描述
 FilePath nvarchar(255) not null,						--文件路径
 Sort int not null default(0),							--排序
 IsPublic bit not null,									--是否允许所有人下载
 AllowDegreeIds text null,								--限制允许下载的会员等级
 New bit not null,										--是否最新
 Hot bit not null,										--是否热点
 Slide bit not null,									--是否幻灯片
 Recommended bit not null,								--是否推荐
 FileIntroduction nvarchar(1000) null,					--文件简介
 HtmlName nvarchar(255) not null						--静态页文件名
)
PPFOR_GO;

----添加文件信息------------
if exists (select * from sysobjects where name='PPFOR_TZB_DownloadFile_Add')
drop procedure PPFOR_TZB_DownloadFile_Add
PPFOR_GO;
create procedure PPFOR_TZB_DownloadFile_Add(@ID int out,@FileTitle nvarchar(200),
@FileSize int,@FileFormat varchar(8),@FileDescription text,@BelongTypeID int,@UploadUser nvarchar(30),
@UploadDate datetime,@IsLink bit,@IsShow bit,@DownloadCount int,@Click int,@SmallImage nvarchar(255),
@SlideImage nvarchar(255),@FileTemplate nvarchar(255),@PageKeyWord nvarchar(255),
@PageDescription nvarchar(255),@FilePath nvarchar(255),@Sort int,@IsPublic bit,@AllowDegreeIds text,
@New bit,@Hot bit,@Slide bit,@Recommended bit,@FileIntroduction nvarchar(1000),@HtmlName nvarchar(255))
as
      insert into PPFOR_TZB_DownloadFile values(@FileTitle,@FileSize,@FileFormat,@FileDescription,
		@BelongTypeID,@UploadUser,@UploadDate,@IsLink,@IsShow,@DownloadCount,@Click,@SmallImage,
		@SlideImage,@FileTemplate,@PageKeyWord,@PageDescription,@FilePath,@Sort,@IsPublic,
		@AllowDegreeIds,@New,@Hot,@Slide,@Recommended,@FileIntroduction,@HtmlName)
      set @ID=@@identity
PPFOR_GO;

----修改文件信息------------
if exists (select * from sysobjects where name='PPFOR_TZB_DownloadFile_Update')
drop procedure PPFOR_TZB_DownloadFile_Update
PPFOR_GO;
create procedure PPFOR_TZB_DownloadFile_Update(@ID int,@FileTitle nvarchar(200),
@FileDescription text,@BelongTypeID int,@UploadDate datetime,@IsShow bit,@SmallImage nvarchar(255),
@SlideImage nvarchar(255),@FileTemplate nvarchar(255),@PageKeyWord nvarchar(255),
@PageDescription nvarchar(255),@Sort int,@IsPublic bit,@AllowDegreeIds text,@New bit,@Hot bit,
@Slide bit,@Recommended bit,@FileIntroduction nvarchar(1000),@HtmlName nvarchar(255))
as 
    update PPFOR_TZB_DownloadFile set FileTitle=@FileTitle,FileDescription=@FileDescription,
		BelongTypeID=@BelongTypeID,UploadDate=@UploadDate,IsShow=@IsShow,SmallImage=@SmallImage,
		SlideImage=@SlideImage,FileTemplate=@FileTemplate,PageKeyWord=@PageKeyWord,PageDescription=@PageDescription,
    	Sort=@Sort,IsPublic=@IsPublic,AllowDegreeIds=@AllowDegreeIds,New=@New,Hot=@Hot,
		Slide=@Slide,Recommended=@Recommended,FileIntroduction=@FileIntroduction,HtmlName=@HtmlName
    where [ID]=@ID
PPFOR_GO;

----修改链接文件信息------------
if exists (select * from sysobjects where name='PPFOR_TZB_DownloadFile_UpdateLink')
drop procedure PPFOR_TZB_DownloadFile_UpdateLink
PPFOR_GO;
create procedure PPFOR_TZB_DownloadFile_UpdateLink(@ID int,@FileTitle nvarchar(200),@FileSize int,
@FileFormat varchar(8),@FileDescription text,@BelongTypeID int,@UploadDate datetime,@IsShow bit,
@SmallImage nvarchar(255),@SlideImage nvarchar(255),@FileTemplate nvarchar(255),@PageKeyWord nvarchar(255),
@PageDescription nvarchar(255),@FilePath nvarchar(255),@Sort int,@IsPublic bit,@AllowDegreeIds text,
@New bit,@Hot bit,@Slide bit,@Recommended bit,@FileIntroduction nvarchar(1000),@HtmlName nvarchar(255))
as 
    update PPFOR_TZB_DownloadFile set FileTitle=@FileTitle,FileSize=@FileSize,FileFormat=@FileFormat,
		FileDescription=@FileDescription,BelongTypeID=@BelongTypeID,UploadDate=@UploadDate,IsShow=@IsShow,
		SmallImage=@SmallImage,SlideImage=@SlideImage,FileTemplate=@FileTemplate,PageKeyWord=@PageKeyWord,
		PageDescription=@PageDescription,Sort=@Sort,FilePath=@FilePath,IsPublic=@IsPublic,
		AllowDegreeIds=@AllowDegreeIds,New=@New,Hot=@Hot,Slide=@Slide,Recommended=@Recommended,
		FileIntroduction=@FileIntroduction,HtmlName=@HtmlName
    where [ID]=@ID and [IsLink]=1
PPFOR_GO;

----批量修改文件信息时------------
if exists (select * from sysobjects where name='PPFOR_TZB_DownloadFile_UpdateList')
drop procedure PPFOR_TZB_DownloadFile_UpdateList
PPFOR_GO;
create procedure PPFOR_TZB_DownloadFile_UpdateList(@ID int,@UploadDate datetime,@IsShow bit,
@Sort int,@New bit,@Hot bit,@Slide bit,@Recommended bit)
as 
    update PPFOR_TZB_DownloadFile  set UploadDate=@UploadDate,IsShow=@IsShow,Sort=@Sort,New=@New,Hot=@Hot,
		Slide=@Slide,Recommended=@Recommended where [ID]=@ID 
PPFOR_GO;

----删除文件信息----------
if exists (select * from sysobjects where name='PPFOR_TZB_DownloadFile_Delete')
drop procedure PPFOR_TZB_DownloadFile_Delete
PPFOR_GO;
create procedure PPFOR_TZB_DownloadFile_Delete(@ID int)
as
   delete PPFOR_TZB_DownloadFile  where [ID]=@ID
PPFOR_GO;

----获取文件信息----------
if exists (select * from sysobjects where name='PPFOR_TZB_DownloadFile_GetModel')
drop procedure PPFOR_TZB_DownloadFile_GetModel
PPFOR_GO;
create procedure PPFOR_TZB_DownloadFile_GetModel(@ID int)
as
     select [ID],FileTitle,FileSize,FileFormat,FileDescription,BelongTypeID,UploadUser,UploadDate,
     	IsLink,IsShow,DownloadCount,Click,SmallImage,SlideImage,FileTemplate,PageKeyWord,PageDescription,
		FilePath,Sort,IsPublic,AllowDegreeIds,New,Hot,Slide,Recommended,FileIntroduction,HtmlName
		 from PPFOR_TZB_DownloadFile  where [ID]=@ID
PPFOR_GO;

--根据父类型ID返回所有文件ID--
if exists(select name from sysobjects where name='PPFOR_TZB_DownloadFile_GetShortList' and type='P')
	drop procedure PPFOR_TZB_DownloadFile_GetShortList
PPFOR_GO;
create procedure PPFOR_TZB_DownloadFile_GetShortList(@belongType int)
as
	select [ID],[BelongTypeID],[HtmlName] from [PPFOR_TZB_DownloadFile] where [BelongTypeID]=@belongType
PPFOR_GO;

--获取相关下载文件列表
if exists(select name from sysobjects where name='PPFOR_TZB_DownloadFile_GetRelated' and type='P')
	drop procedure PPFOR_TZB_DownloadFile_GetRelated
PPFOR_GO;
create procedure PPFOR_TZB_DownloadFile_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],[FileTitle],[HtmlName] from [PPFOR_TZB_DownloadFile] where [ID]<>@id and BelongTypeID=@belongType 
				and pageKeyWord like @keyword order by [sort] desc, [ID] desc
		else 
			select [ID],[FileTitle],[HtmlName] from [PPFOR_TZB_DownloadFile] where [ID]<>@id and BelongTypeID=@belongType 
				order by [sort] desc, [ID] desc
	end
PPFOR_GO;

--分页返回文件资料--
if exists(select name from sysobjects where name='PPFOR_TZB_DownloadFile_GetModelListByTypeID' and type='P')
	drop procedure PPFOR_TZB_DownloadFile_GetModelListByTypeID
PPFOR_GO;
create procedure PPFOR_TZB_DownloadFile_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_DownloadFile] where [BelongTypeID]=@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],FileTitle,SmallImage,UploadUser,IsShow,Sort,Recommended,New,Hot,Slide,
			UploadDate,HtmlName from [PPFOR_TZB_DownloadFile] where [BelongTypeID]=' + 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 * from sysobjects where name='PPFOR_TZB_DownloadFile_GetModelListByWhere')
drop procedure PPFOR_TZB_DownloadFile_GetModelListByWhere
PPFOR_GO;
create procedure PPFOR_TZB_DownloadFile_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_DownloadFile where BelongTypeID = any (select [ID] from PPFOR_TZB_DownloadType 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],FileTitle,FileSize,FileFormat,BelongTypeID,UploadDate,IsShow,DownloadCount,Sort,HtmlName
			from  PPFOR_TZB_DownloadFile where BelongTypeID = any (select [ID] from PPFOR_TZB_DownloadType where BelongMenu=' + str(@belongMenu) + ') and ' + @whereStr + ' '
		if @pageIndex <= 1 or @pc <= 1
			set @sqlStr='select top ' + str(@pageSize) + @sqlStr2 + ' order by [Sort] desc,[UploadDate] desc'
		else if @pageIndex = @pc
			set @sqlStr ='select * from (select top ' + str( @rc - @pageSize * (@pageIndex-1) ) 
				+ @sqlStr2 + ' order by [Sort] asc,[UploadDate] asc) Temptable order by [Sort] desc,[UploadDate] desc'
		else  
			set @sqlStr ='select top ' + str(@pageSize) + ' * from (select top '
				+ str( @rc - @pageSize * (@pageIndex-1) ) + @sqlStr2 + ' order by 
				[Sort] asc,UploadDate asc) Temptable order by [Sort] desc,[UploadDate] desc'
		
		exec (@sqlStr)
		select @rc as recordCount,@pc as pageCount
	end
PPFOR_GO;


--分页返回下载文件资料-------
if exists(select name from sysobjects where name='PPFOR_TZB_DownloadFile_GetModelListBySearch' and type='P')
	drop procedure PPFOR_TZB_DownloadFile_GetModelListBySearch
PPFOR_GO;

create procedure PPFOR_TZB_DownloadFile_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_DownloadFile  where IsShow=1 and [BelongTypeID]=any (select [ID] from PPFOR_TZB_DownloadType where IsShow=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],FileTitle,FileSize,FileFormat,FileIntroduction,BelongTypeID,UploadDate,IsLink,DownloadCount,Click,SmallImage,SlideImage,FilePath,Sort,HtmlName
			 from  PPFOR_TZB_DownloadFile   where IsShow=1 and [BelongTypeID]=any (select [ID] from PPFOR_TZB_DownloadType where IsShow=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,[UploadDate] desc'
		else if @pageIndex = @pc
			set @sqlStr ='select * from (select top ' + str( @rc - @pageSize * (@pageIndex-1) ) 
				+ @sqlStr2 + ' order by [Sort] asc,[UploadDate] asc) Temptable order by [Sort] desc,[UploadDate] desc'
		else  
			set @sqlStr ='select top ' + str(@pageSize) + ' * from (select top '
				+ str( @rc - @pageSize * (@pageIndex-1) ) + @sqlStr2 + ' order by 
				[Sort] asc,[UploadDate] asc) Temptable order by [Sort] desc,[UploadDate] desc'
		
		exec (@sqlStr)
		select @rc as recordCount,@pc as pageCount
	end
PPFOR_GO;

if exists(select name from sysobjects where name='PPFOR_TZB_DownloadFile_GetModelListForLabel' and type='P')
	drop procedure PPFOR_TZB_DownloadFile_GetModelListForLabel
PPFOR_GO;
--获取下载文件列表
--未排序
create procedure PPFOR_TZB_DownloadFile_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],FileTitle,FileSize,FileFormat,FileIntroduction,BelongTypeID,UploadDate,IsLink,DownloadCount,Click,SmallImage,SlideImage,FilePath,Sort,HtmlName '
			
			if @belongType>0
				set	@sql= 'select top ' + str(@number) + @sqlStr2 + ' from  PPFOR_TZB_DownloadFile where IsShow=1 and [BelongTypeID]=' + str(@belongType)
			else if @belongMenu>0
				set	@sql= 'select top ' + str(@number) + @sqlStr2 + ' from  PPFOR_TZB_DownloadFile where IsShow=1 and [BelongTypeID]=any (select [ID] from PPFOR_TZB_DownloadType where IsShow=1 and BelongMenu=' + str(@belongMenu) + ')'
			else if @belongLanguage>0
				set	@sql= 'select top ' + str(@number) + @sqlStr2 + ' from  PPFOR_TZB_DownloadFile where IsShow=1 and [BelongTypeID]=any (select [ID] from PPFOR_TZB_DownloadType where IsShow=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, [UploadDate] desc'
				exec (@sql)
			end
		end
	end
PPFOR_GO;

--返回单页下载列表
if exists(select name from sysobjects where name='PPFOR_TZB_DownloadFile_GetModelListByPage' and type='P')
	drop procedure PPFOR_TZB_DownloadFile_GetModelListByPage
PPFOR_GO;
create procedure PPFOR_TZB_DownloadFile_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_DownloadFile   where [IsShow]=1 and [BelongTypeID]=@belongType)
		else if @belongMenu>0
			set @rc=(select count([ID]) from  PPFOR_TZB_DownloadFile   where [IsShow]=1 and [BelongTypeID]=any (select [ID] from [PPFOR_TZB_DownloadType] where [IsShow]=1 and [BelongMenu]=@belongMenu))
		else if @belongLanguage>0
			set	@rc=(select count([ID]) from  PPFOR_TZB_DownloadFile   where [IsShow]=1 and [BelongTypeID]=any (select [ID] from [PPFOR_TZB_DownloadType] where [IsShow]=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_DownloadFile   where [IsShow]=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_DownloadFile where IsShow=1 and [BelongTypeID]=' + str(@belongType) + ' '
		else if @belongMenu>0
			set	@sqlStr2= ' * from  PPFOR_TZB_DownloadFile where IsShow=1 and [BelongTypeID]=any (select [ID] from PPFOR_TZB_DownloadType where IsShow=1 and BelongMenu=' + str(@belongMenu) + ') '
		else if @belongLanguage>0
			set	@sqlStr2= ' * from  PPFOR_TZB_DownloadFile where IsShow=1 and [BelongTypeID]=any (select [ID] from PPFOR_TZB_DownloadType where IsShow=1 and BelongMenu= any(select [ID] from PPFOR_TZB_Menu where show=1 and belongLanguage=' + str(@belongLanguage) + ')) '
		else 
			set	@sqlStr2= ' * from  PPFOR_TZB_DownloadFile where IsShow=1 '
		if @pageIndex <= 1 or @pc <= 1
			set @sqlStr='select top ' + str(@pageSize) + @sqlStr2 + ' order by [sort] desc, [UploadDate] desc'
		else if @pageIndex = @pc
			set @sqlStr = 'select * from (select top ' + str( @rc - @pageSize * (@pageIndex-1) ) 
				+ @sqlStr2 + ' order by [sort] asc, [UploadDate] asc) Temptable order by [sort] desc, [UploadDate] desc'
		else  
			set @sqlStr ='select top ' + str(@pageSize) + ' * from (select top '
				+ str( @rc - @pageSize * (@pageIndex-1) ) + @sqlStr2 + ' order by [sort] asc,
				[UploadDate] asc) Temptable order by [sort] desc, [UploadDate] desc'
		exec (@sqlStr)
		select @rc as recordCount,@pc as pageCount
	end
PPFOR_GO;

--返回单页下载列表
if exists(select name from sysobjects where name='PPFOR_TZB_DownloadFile_GetShortListByPage' and type='P')
	drop procedure PPFOR_TZB_DownloadFile_GetShortListByPage
PPFOR_GO;
create procedure PPFOR_TZB_DownloadFile_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_DownloadFile] where [IsShow]=1'
		if @belongType>0
			set @sqlStr2=@sqlStr2+' and [BelongTypeID]='+str(@belongType)
		else if @belongMenu>0
			set @sqlStr2=@sqlStr2+' and [BelongTypeID]=any (select [ID] from [PPFOR_TZB_DownloadType] where [IsShow]=1 and [BelongMenu]='+str(@belongMenu)+')'
		else if @belongLanguage>0
			set @sqlStr2=@sqlStr2+' and [BelongTypeID]=any (select [ID] from [PPFOR_TZB_DownloadType] where [IsShow]=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],FileTitle,FileSize,FileFormat,FileIntroduction,BelongTypeID,UploadDate,IsLink,
			DownloadCount,Click,SmallImage,SlideImage,FilePath,Sort,HtmlName from [PPFOR_TZB_DownloadFile] where [IsShow]=1 '

		if @belongType>0
			set	@sqlStr2=@sqlStr2 + ' and [BelongTypeID]=' + str(@belongType) + ' '
		else if @belongMenu>0
			set	@sqlStr2=@sqlStr2 + ' and [BelongTypeID]=any (select [ID] from PPFOR_TZB_DownloadType where IsShow=1 and BelongMenu=' + str(@belongMenu) + ') '
		else if @belongLanguage>0
			set	@sqlStr2=@sqlStr2 + ' and [BelongTypeID]=any (select [ID] from PPFOR_TZB_DownloadType where IsShow=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, [UploadDate] desc'
		else if @pageIndex = @pc
			set @sqlStr = 'select * from (select top ' + str( @rc - @pageSize * (@pageIndex-1) ) 
				+ @sqlStr2 + ' order by [sort] asc, [UploadDate] asc) Temptable order by [sort] desc, [UploadDate] desc'
		else  
			set @sqlStr ='select top ' + str(@pageSize) + ' * from (select top '
				+ str( @rc - @pageSize * (@pageIndex-1) ) + @sqlStr2 + ' order by [sort] asc,
				[UploadDate] asc) Temptable order by [sort] desc, [UploadDate] desc'
		exec (@sqlStr)
		select @rc as recordCount,@pc as pageCount
	end
PPFOR_GO;

--返回单页下载列表
if exists(select name from sysobjects where name='PPFOR_TZB_DownloadFile_GetModelListInTypeByPage' and type='P')
	drop procedure PPFOR_TZB_DownloadFile_GetModelListInTypeByPage
PPFOR_GO;
create procedure PPFOR_TZB_DownloadFile_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_DownloadFile] where [IsShow]=1 and [BelongTypeID] 
			in (select [ID] from PPFOR_TZB_DownloadType_GetChildIdsTable('+str(@belongType)+') where [IsShow]=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],FileTitle,FileSize,FileFormat,FileIntroduction,BelongTypeID,UploadDate,IsLink,
			DownloadCount,Click,SmallImage,SlideImage,FilePath,Sort,HtmlName from [PPFOR_TZB_DownloadFile] where [IsShow]=1 and [BelongTypeID] in 
			(select [ID] from PPFOR_TZB_DownloadType_GetChildIdsTable('+str(@belongType)+') where [IsShow]=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, [UploadDate] desc'
		else if @pageIndex = @pc
			set @sqlStr = 'select * from (select top ' + str( @rc - @pageSize * (@pageIndex-1) ) 
				+ @sqlStr2 + ' order by [sort] asc, [UploadDate] asc) Temptable order by [sort] desc, [UploadDate] desc'
		else  
			set @sqlStr ='select top ' + str(@pageSize) + ' * from (select top '
				+ str( @rc - @pageSize * (@pageIndex-1) ) + @sqlStr2 + ' order by [sort] asc,
				[UploadDate] asc) Temptable order by [sort] desc, [UploadDate] desc'
		exec (@sqlStr)
		select @rc as recordCount,@pc as pageCount
	end
PPFOR_GO;

-----获取下载文件点击数----
if exists(select name from sysobjects where name='PPFOR_TZB_DownloadFile_GetClickCount' and type='P')
	drop procedure PPFOR_TZB_DownloadFile_GetClickCount
PPFOR_GO;
create procedure PPFOR_TZB_DownloadFile_GetClickCount(@ID int)
as
	select [Click] from [PPFOR_TZB_DownloadFile] where [ID]=@ID
PPFOR_GO;

-----获取下载文件名称----
if exists(select name from sysobjects where name='PPFOR_TZB_DownloadFile_GetModelName' and type='P')
	drop procedure PPFOR_TZB_DownloadFile_GetModelName
PPFOR_GO;
create procedure PPFOR_TZB_DownloadFile_GetModelName(@ID int)
as
	select [FileTitle] from [PPFOR_TZB_DownloadFile] where [ID]=@ID
PPFOR_GO;

--点击+1，并返回下载文件动态信息--
if exists(select name from sysobjects where name='PPFOR_TZB_DownloadFile_GetVisitInfo' and type='P')
	drop procedure PPFOR_TZB_DownloadFile_GetVisitInfo
PPFOR_GO;
create procedure PPFOR_TZB_DownloadFile_GetVisitInfo(@ID int)
as
	update [PPFOR_TZB_DownloadFile] set [Click]=[Click]+1 where [IsShow]=1 and [ID]=@ID and [IsPublic]=1
	select [PPFOR_TZB_DownloadFile].[ID],[PPFOR_TZB_DownloadType].[ID],
		[PPFOR_TZB_DownloadType].[TypeName],[PPFOR_TZB_DownloadFile].[Click],
		[PPFOR_TZB_DownloadFile].[DownloadCount] from [PPFOR_TZB_DownloadFile],
		[PPFOR_TZB_DownloadType] where [PPFOR_TZB_DownloadFile].[IsShow]=1 
		and [PPFOR_TZB_DownloadFile].[ID]=@ID and [PPFOR_TZB_DownloadFile].[IsPublic]=1
	 	and [PPFOR_TZB_DownloadFile].[BelongTypeID]=[PPFOR_TZB_DownloadType].[ID] 
PPFOR_GO;	

--点击+1，并返回下载文件动态信息--
if exists(select name from sysobjects where name='PPFOR_TZB_DownloadFile_GetVisitContent' and type='P')
	drop procedure PPFOR_TZB_DownloadFile_GetVisitContent
PPFOR_GO;
create procedure PPFOR_TZB_DownloadFile_GetVisitContent(@ID int)
as
	update [PPFOR_TZB_DownloadFile] set [Click]=[Click]+1 where [IsShow]=1 and [ID]=@ID 
	select [PPFOR_TZB_DownloadFile].[ID],[PPFOR_TZB_DownloadType].[ID],
		[PPFOR_TZB_DownloadType].[TypeName],[PPFOR_TZB_DownloadFile].[Click],
		[PPFOR_TZB_DownloadFile].[DownloadCount],[PPFOR_TZB_DownloadFile].[FileDescription],
		[PPFOR_TZB_DownloadFile].[IsPublic],[PPFOR_TZB_DownloadFile].[AllowDegreeIds]
		from [PPFOR_TZB_DownloadFile],[PPFOR_TZB_DownloadType] where 
		[PPFOR_TZB_DownloadFile].[IsShow]=1 and [PPFOR_TZB_DownloadFile].[ID]=@ID 
		and [PPFOR_TZB_DownloadFile].[BelongTypeID]=[PPFOR_TZB_DownloadType].[ID] 
PPFOR_GO;	

---返回第一个下载文件---
if exists(select name from sysobjects where name='PPFOR_TZB_DownloadFile_GetFirstDownloadFile' and type='P')
	drop procedure PPFOR_TZB_DownloadFile_GetFirstDownloadFile
PPFOR_GO;
create procedure PPFOR_TZB_DownloadFile_GetFirstDownloadFile(@BelongTypeID int) 
as
	select top 1 [ID],[FileTitle],[HtmlName] from [PPFOR_TZB_DownloadFile] where [IsShow]=1 
		and [BelongTypeID]=@BelongTypeID order by [sort] desc, [ID] desc
PPFOR_GO;

---返回下一个下载文件---
if exists(select name from sysobjects where name='PPFOR_TZB_DownloadFile_GetNextDownloadFile' and type='P')
	drop procedure PPFOR_TZB_DownloadFile_GetNextDownloadFile
PPFOR_GO;
create procedure PPFOR_TZB_DownloadFile_GetNextDownloadFile(@ID int) 
as
	select top 1 [ID],[FileTitle],[HtmlName] from [PPFOR_TZB_DownloadFile] where [IsShow]=1 and [ID]<@ID and [BelongTypeID]=
		(select [BelongTypeID] from [PPFOR_TZB_DownloadFile] where [IsShow]=1 and [ID]=@ID) order by [ID] desc
PPFOR_GO;

--返回上一个下载文件--
if exists(select name from sysobjects where name='PPFOR_TZB_DownloadFile_GetPrevDownloadFile' and type='P')
	drop procedure PPFOR_TZB_DownloadFile_GetPrevDownloadFile
PPFOR_GO;
create procedure PPFOR_TZB_DownloadFile_GetPrevDownloadFile(@ID int) 
as
	select top 1 [ID],[FileTitle],[HtmlName] from [PPFOR_TZB_DownloadFile] where [IsShow]=1 and [ID]>@ID and [BelongTypeID]=
		(select [BelongTypeID] from [PPFOR_TZB_DownloadFile] where [IsShow]=1 and [ID]=@ID) order by [ID] asc
PPFOR_GO;

--获取相关下载文件列表--
if exists(select name from sysobjects where name='PPFOR_TZB_DownloadFile_GetRelatedList' and type='P')
	drop procedure PPFOR_TZB_DownloadFile_GetRelatedList
PPFOR_GO;
create procedure PPFOR_TZB_DownloadFile_GetRelatedList(@ID int,@number int)
as
	begin
		declare @keyword nvarchar (255)
		declare @belongType int
		select @keyword=PageKeyWord,@belongType=BelongTypeID from [PPFOR_TZB_DownloadFile] where [IsShow]=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],[FileTitle],[HtmlName] from [PPFOR_TZB_DownloadFile] where [ID]<>@ID and [IsShow]=1 and BelongTypeID=@belongType 
					and PageKeyWord like @keyword order by [sort] desc, [ID] desc
			end
		else if @belongType>0
			select [ID],[FileTitle],[HtmlName] from [PPFOR_TZB_DownloadFile] where [ID]<>@ID and [IsShow]=1 and BelongTypeID=@belongType 
				order by [sort] desc, [ID] desc
	end
PPFOR_GO;
--更新下载文件静态页文件名
if exists(select name from sysobjects where name='PPFOR_TZB_DownloadFile_UpdateHtmlName' and type='P')
	drop procedure PPFOR_TZB_DownloadFile_UpdateHtmlName
PPFOR_GO;

CREATE PROCEDURE PPFOR_TZB_DownloadFile_UpdateHtmlName(@ID int,@HtmlName nvarchar(255))
 AS 
	UPDATE [PPFOR_TZB_DownloadFile] SET [HtmlName] = @HtmlName WHERE [ID]=@ID 
PPFOR_GO;

--获取下载文件静态文件名
if exists(select name from sysobjects where name='PPFOR_TZB_DownloadFile_GetHtmlName' and type='P')
	drop procedure PPFOR_TZB_DownloadFile_GetHtmlName
PPFOR_GO;
create procedure PPFOR_TZB_DownloadFile_GetHtmlName(@ID int)
as
	select [HtmlName] from [PPFOR_TZB_DownloadFile] where [ID]=@ID 
PPFOR_GO;