-- ==============================
-- 长登科技 www.ppfor.com
-- 唐镇波   2010-04-19
-- ------------------------------
-- 招聘职位表
-- ==============================

if not exists (SELECT * FROM  sysobjects WHERE  name = 'PPFOR_TZB_JobPost')
create table PPFOR_TZB_JobPost
(
 [ID] int identity(1,1) not null primary key,			--自动编号
 BelongTypeID int not null,								--所属类型ID
 PostTitle nvarchar(200) COLLATE Chinese_PRC_CI_AS not null, 					--职位名称
 PostDescription text null,								--职位描述  
 PostIntroduction nvarchar(1000) null,					--职位简介
 UpdateDate datetime not null,							--职位修改时间
 PublishDate datetime not null,							--职位发布时间
 IsTimeLimit bit not null default(0),					--是否开启时间限制
 BeginTime datetime not null,							--职位招聘起始时间
 EndTime datetime not null,								--职位招聘结束时间
 IsShow bit not null,									--是否显示
 Click int not null, 									--浏览次数
 SmallImage nvarchar(255) null,							--小图 
 PostTemplate nvarchar(255) not null,					--详细页模板  
 PageKeyWord nvarchar(255) COLLATE Chinese_PRC_CI_AS null,						--Meta关键字
 PageDescription nvarchar(255) COLLATE Chinese_PRC_CI_AS null,					--Meta描述
 Sort int not null default(0),							--排序
 IsPublic bit not null,									--是否允许所有人浏览
 AllowDegreeIds text null,								--限制允许浏览的会员等级   
 HtmlName nvarchar(255) not null,						--静态页文件名
 ResumeName nvarchar(255) not null						--简历页文件名			
)
PPFOR_GO;

----添加职位信息------------
if exists (select * from sysobjects where name='PPFOR_TZB_JobPost_Add')
drop procedure PPFOR_TZB_JobPost_Add
PPFOR_GO;
create procedure PPFOR_TZB_JobPost_Add(@ID int out,@BelongTypeID int,@PostTitle nvarchar(200),
@PostDescription text,@PostIntroduction nvarchar(1000),@UpdateDate datetime,@PublishDate datetime,
@IsTimeLimit bit,@BeginTime datetime,@EndTime datetime,@IsShow bit,@Click int,@SmallImage nvarchar(255),
@PostTemplate nvarchar(255),@PageKeyWord nvarchar(255),@PageDescription nvarchar(255),@Sort int,
@IsPublic bit,@AllowDegreeIds text,@HtmlName nvarchar(255),@ResumeName nvarchar(255))
as
      insert into PPFOR_TZB_JobPost values(@BelongTypeID,@PostTitle,@PostDescription,@PostIntroduction,
		@UpdateDate,@PublishDate,@IsTimeLimit,@BeginTime,@EndTime,@IsShow,@Click,@SmallImage,
		@PostTemplate,@PageKeyWord,@PageDescription,@Sort,@IsPublic,@AllowDegreeIds,@HtmlName,@ResumeName)
      set @ID=@@identity
PPFOR_GO;
 
----修改职位信息------------
if exists (select * from sysobjects where name='PPFOR_TZB_JobPost_Update')
drop procedure PPFOR_TZB_JobPost_Update
PPFOR_GO;
create procedure PPFOR_TZB_JobPost_Update(@ID int out,@BelongTypeID int,@PostTitle nvarchar(200),
@PostDescription text,@PostIntroduction nvarchar(1000),@UpdateDate datetime,@PublishDate datetime,
@IsTimeLimit bit,@BeginTime datetime,@EndTime datetime,@IsShow bit,@Click int,@SmallImage nvarchar(255),
@PostTemplate nvarchar(255),@PageKeyWord nvarchar(255),@PageDescription nvarchar(255),@Sort int,
@IsPublic bit,@AllowDegreeIds text,@HtmlName nvarchar(255),@ResumeName nvarchar(255))
as 
    update PPFOR_TZB_JobPost set BelongTypeID=@BelongTypeID,PostTitle=@PostTitle,
		PostDescription=@PostDescription,PostIntroduction=@PostIntroduction,UpdateDate=@UpdateDate,
		PublishDate=@PublishDate,IsTimeLimit=@IsTimeLimit,BeginTime=@BeginTime,EndTime=@EndTime,
		IsShow=@IsShow,SmallImage=@SmallImage,PostTemplate=@PostTemplate,PageKeyWord=@PageKeyWord,
		PageDescription=@PageDescription,Sort=@Sort,IsPublic=@IsPublic,AllowDegreeIds=@AllowDegreeIds,
		HtmlName=@HtmlName,[ResumeName]=@ResumeName where [ID]=@ID
PPFOR_GO;

----批量修改职位信息时------------
if exists (select * from sysobjects where name='PPFOR_TZB_JobPost_UpdateList')
drop procedure PPFOR_TZB_JobPost_UpdateList
PPFOR_GO;
create procedure PPFOR_TZB_JobPost_UpdateList(@ID int,@UpdateDate datetime,@IsShow bit,
@Sort int)
as 
    update PPFOR_TZB_JobPost  set UpdateDate=@UpdateDate,IsShow=@IsShow,Sort=@Sort where [ID]=@ID 
PPFOR_GO;

----删除职位信息----------
if exists (select * from sysobjects where name='PPFOR_TZB_JobPost_Delete')
drop procedure PPFOR_TZB_JobPost_Delete
PPFOR_GO;
create procedure PPFOR_TZB_JobPost_Delete(@ID int)
as
   delete PPFOR_TZB_JobPost  where [ID]=@ID
PPFOR_GO;

----获取职位信息----------
if exists (select * from sysobjects where name='PPFOR_TZB_JobPost_GetModel')
drop procedure PPFOR_TZB_JobPost_GetModel
PPFOR_GO;
create procedure PPFOR_TZB_JobPost_GetModel(@ID int)
as
     select * from PPFOR_TZB_JobPost  where [ID]=@ID
PPFOR_GO;

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

--获取相关招聘职位列表
if exists(select name from sysobjects where name='PPFOR_TZB_JobPost_GetRelated' and type='P')
	drop procedure PPFOR_TZB_JobPost_GetRelated
PPFOR_GO;
create procedure PPFOR_TZB_JobPost_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],[PostTitle],[HtmlName],[ResumeName] from [PPFOR_TZB_JobPost] where [ID]<>@id 
				and [BelongTypeID]=@belongType and PageKeyWord like @keyword order by [Sort] desc, [PublishDate] desc
		else 
			select [ID],[PostTitle],[HtmlName],[ResumeName] from [PPFOR_TZB_JobPost] where [ID]<>@id 
				and [BelongTypeID]=@belongType order by [Sort] desc, [PublishDate] desc
	end
PPFOR_GO;

--分页返回职位资料--
if exists(select name from sysobjects where name='PPFOR_TZB_JobPost_GetModelListByTypeID' and type='P')
	drop procedure PPFOR_TZB_JobPost_GetModelListByTypeID
PPFOR_GO;
create procedure PPFOR_TZB_JobPost_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_JobPost] 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],PostTitle,SmallImage,IsShow,Sort,UpdateDate,PublishDate,HtmlName,ResumeName
			from [PPFOR_TZB_JobPost] where [BelongTypeID]=' + 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_JobPost_GetModelListForLabel' and type='P')
	drop procedure PPFOR_TZB_JobPost_GetModelListForLabel
PPFOR_GO;
--获取招聘职位列表
--未排序
create procedure PPFOR_TZB_JobPost_GetModelListForLabel(@belongLanguage int,@belongMenu int,@belongType int,@memberDegree int,@number int)
as
	begin
		declare @sql varchar(1000)
		declare @sqlStr2 varchar(255)
		if @number>=0			
		begin
			set @sqlStr2=' [ID],BelongTypeID,PostTitle,PostIntroduction,UpdateDate,PublishDate,Click,SmallImage,Sort,HtmlName,ResumeName '
			
			if @belongType>0
				set	@sql= 'select top ' + str(@number) + @sqlStr2 + ' from  PPFOR_TZB_JobPost where IsShow=1 and [BelongTypeID]=' + str(@belongType)
			else if @belongMenu>0
				set	@sql= 'select top ' + str(@number) + @sqlStr2 + ' from  PPFOR_TZB_JobPost where IsShow=1 and [BelongTypeID]=any (select [ID] from PPFOR_TZB_JobType where IsShow=1 and BelongMenu=' + str(@belongMenu) + ')'
			else if @belongLanguage>0
				set	@sql= 'select top ' + str(@number) + @sqlStr2 + ' from  PPFOR_TZB_JobPost where IsShow=1 and [BelongTypeID]=any (select [ID] from PPFOR_TZB_JobType 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)))+',%'')'
							 	
				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_JobPost_GetModelListByPage' and type='P')
	drop procedure PPFOR_TZB_JobPost_GetModelListByPage
PPFOR_GO;
create procedure PPFOR_TZB_JobPost_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_JobPost where [IsShow]=1 and [BelongTypeID]=@belongType)
		else if @belongMenu>0
			set @rc=(select count([ID]) from  PPFOR_TZB_JobPost where [IsShow]=1 and [BelongTypeID]=any (select [ID] from [PPFOR_TZB_JobType] where [IsShow]=1 and [BelongMenu]=@belongMenu))
		else if @belongLanguage>0
			set	@rc=(select count([ID]) from  PPFOR_TZB_JobPost where [IsShow]=1 and [BelongTypeID]=any (select [ID] from [PPFOR_TZB_JobType] 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_JobPost 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_JobPost where IsShow=1 and [BelongTypeID]=' + str(@belongType) + ' '
		else if @belongMenu>0
			set	@sqlStr2= ' * from  PPFOR_TZB_JobPost where IsShow=1 and [BelongTypeID]=any (select [ID] from PPFOR_TZB_JobType where IsShow=1 and BelongMenu=' + str(@belongMenu) + ') '
		else if @belongLanguage>0
			set	@sqlStr2= ' * from  PPFOR_TZB_JobPost where IsShow=1 and [BelongTypeID]=any (select [ID] from PPFOR_TZB_JobType 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_JobPost where IsShow=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_JobPost_GetShortListByPage' and type='P')
	drop procedure PPFOR_TZB_JobPost_GetShortListByPage
PPFOR_GO;
create procedure PPFOR_TZB_JobPost_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_JobPost] 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_JobType] where [IsShow]=1 and [BelongMenu]='+str(@belongMenu)+')'
		else if @belongLanguage>0
			set @sqlStr2=@sqlStr2+' and [BelongTypeID]=any (select [ID] from [PPFOR_TZB_JobType] 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],BelongTypeID,PostTitle,PostIntroduction,UpdateDate,PublishDate,
			Click,SmallImage,Sort,HtmlName,ResumeName from [PPFOR_TZB_JobPost] 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_JobType where IsShow=1 and BelongMenu=' + str(@belongMenu) + ') '
		else if @belongLanguage>0
			set	@sqlStr2=@sqlStr2 + ' and [BelongTypeID]=any (select [ID] from PPFOR_TZB_JobType 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, [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_JobPost_GetModelListInTypeByPage' and type='P')
	drop procedure PPFOR_TZB_JobPost_GetModelListInTypeByPage
PPFOR_GO;
create procedure PPFOR_TZB_JobPost_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_JobPost] where [IsShow]=1 and [BelongTypeID]='+str(@belongType)
		
		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],BelongTypeID,PostTitle,PostIntroduction,UpdateDate,PublishDate,
			Click,SmallImage,Sort,HtmlName,ResumeName from [PPFOR_TZB_JobPost] where [IsShow]=1 and [BelongTypeID]='+str(@belongType)	

		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_JobPost_GetClickCount' and type='P')
	drop procedure PPFOR_TZB_JobPost_GetClickCount
PPFOR_GO;
create procedure PPFOR_TZB_JobPost_GetClickCount(@ID int)
as
	select [Click] from [PPFOR_TZB_JobPost] where [ID]=@ID
PPFOR_GO;

-----获取招聘职位名称----
if exists(select name from sysobjects where name='PPFOR_TZB_JobPost_GetModelName' and type='P')
	drop procedure PPFOR_TZB_JobPost_GetModelName
PPFOR_GO;
create procedure PPFOR_TZB_JobPost_GetModelName(@ID int)
as
	select [PostTitle] from [PPFOR_TZB_JobPost] where [ID]=@ID
PPFOR_GO;

--点击+1，并返回招聘职位动态信息--
if exists(select name from sysobjects where name='PPFOR_TZB_JobPost_GetVisitInfo' and type='P')
	drop procedure PPFOR_TZB_JobPost_GetVisitInfo
PPFOR_GO;
create procedure PPFOR_TZB_JobPost_GetVisitInfo(@ID int)
as
	update [PPFOR_TZB_JobPost] set [Click]=[Click]+1 where [IsShow]=1 and [ID]=@ID and [IsPublic]=1
	select [PPFOR_TZB_JobPost].[ID],[PPFOR_TZB_JobType].[ID],[PPFOR_TZB_JobType].[Name],
		[PPFOR_TZB_JobPost].[Click] from [PPFOR_TZB_JobPost],[PPFOR_TZB_JobType] 
		where [PPFOR_TZB_JobPost].[IsShow]=1 and [PPFOR_TZB_JobPost].[ID]=@ID 
		and [PPFOR_TZB_JobPost].[IsPublic]=1 and [PPFOR_TZB_JobPost].[BelongTypeID]=[PPFOR_TZB_JobType].[ID] 
PPFOR_GO;	

--点击+1，并返回招聘职位动态信息--
if exists(select name from sysobjects where name='PPFOR_TZB_JobPost_GetVisitContent' and type='P')
	drop procedure PPFOR_TZB_JobPost_GetVisitContent
PPFOR_GO;
create procedure PPFOR_TZB_JobPost_GetVisitContent(@ID int)
as
	update [PPFOR_TZB_JobPost] set [Click]=[Click]+1 where [IsShow]=1 and [ID]=@ID 
	select [PPFOR_TZB_JobPost].[ID],[PPFOR_TZB_JobType].[ID],[PPFOR_TZB_JobType].[Name],
		[PPFOR_TZB_JobPost].[Click],[PPFOR_TZB_JobPost].[PostDescription],[PPFOR_TZB_JobPost].[IsPublic],
		[PPFOR_TZB_JobPost].[AllowDegreeIds] from [PPFOR_TZB_JobPost],[PPFOR_TZB_JobType]
		where [PPFOR_TZB_JobPost].[IsShow]=1 and [PPFOR_TZB_JobPost].[ID]=@ID 
		and [PPFOR_TZB_JobPost].[BelongTypeID]=[PPFOR_TZB_JobType].[ID] 
PPFOR_GO;	

---返回第一个招聘职位---
if exists(select name from sysobjects where name='PPFOR_TZB_JobPost_GetFirstJobPost' and type='P')
	drop procedure PPFOR_TZB_JobPost_GetFirstJobPost
PPFOR_GO;
create procedure PPFOR_TZB_JobPost_GetFirstJobPost(@BelongTypeID int) 
as
	select top 1 [ID],[PostTitle],[HtmlName],[ResumeName] from [PPFOR_TZB_JobPost] where [IsShow]=1 
		and [BelongTypeID]=@BelongTypeID order by [Sort] desc, [PublishDate] desc
PPFOR_GO;

---返回下一个招聘职位---
if exists(select name from sysobjects where name='PPFOR_TZB_JobPost_GetNextJobPost' and type='P')
	drop procedure PPFOR_TZB_JobPost_GetNextJobPost
PPFOR_GO;
create procedure PPFOR_TZB_JobPost_GetNextJobPost(@ID int) 
as
	select top 1 [ID],[PostTitle],[HtmlName],[ResumeName] from [PPFOR_TZB_JobPost] where [IsShow]=1 and [ID]<@ID and [BelongTypeID]=
		(select [BelongTypeID] from [PPFOR_TZB_JobPost] where [IsShow]=1 and [ID]=@ID) order by [ID] desc
PPFOR_GO;

--返回上一个招聘职位--
if exists(select name from sysobjects where name='PPFOR_TZB_JobPost_GetPrevJobPost' and type='P')
	drop procedure PPFOR_TZB_JobPost_GetPrevJobPost
PPFOR_GO;
create procedure PPFOR_TZB_JobPost_GetPrevJobPost(@ID int) 
as
	select top 1 [ID],[PostTitle],[HtmlName],[ResumeName] from [PPFOR_TZB_JobPost] where [IsShow]=1 and [ID]>@ID and [BelongTypeID]=
		(select [BelongTypeID] from [PPFOR_TZB_JobPost] where [IsShow]=1 and [ID]=@ID) order by [ID] asc
PPFOR_GO;

--获取相关招聘职位列表--
if exists(select name from sysobjects where name='PPFOR_TZB_JobPost_GetRelatedList' and type='P')
	drop procedure PPFOR_TZB_JobPost_GetRelatedList
PPFOR_GO;
create procedure PPFOR_TZB_JobPost_GetRelatedList(@ID int,@number int)
as
	begin
		declare @keyword nvarchar (255)
		declare @belongType int
		select @keyword=PageKeyWord,@belongType=BelongTypeID from [PPFOR_TZB_JobPost] 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],[PostTitle],[HtmlName],[ResumeName] from [PPFOR_TZB_JobPost] where [ID]<>@ID and [IsShow]=1 and BelongTypeID=@belongType 
					and PageKeyWord like @keyword order by [Sort] desc, [PublishDate] desc
			end
		else if @belongType>0
			select [ID],[PostTitle],[HtmlName],[ResumeName] from [PPFOR_TZB_JobPost] where [ID]<>@ID and [IsShow]=1 and BelongTypeID=@belongType 
				order by [Sort] desc, [PublishDate] desc
	end
PPFOR_GO;
--更新招聘职位静态页文件名
if exists(select name from sysobjects where name='PPFOR_TZB_JobPost_UpdateHtmlName' and type='P')
	drop procedure PPFOR_TZB_JobPost_UpdateHtmlName
PPFOR_GO;

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

--获取招聘职位静态文件名
if exists(select name from sysobjects where name='PPFOR_TZB_JobPost_GetHtmlName' and type='P')
	drop procedure PPFOR_TZB_JobPost_GetHtmlName
PPFOR_GO;
create procedure PPFOR_TZB_JobPost_GetHtmlName(@ID int)
as
	select [HtmlName] from [PPFOR_TZB_JobPost] where [ID]=@ID 
PPFOR_GO;

--更新职位简历静态文件名
if exists(select name from sysobjects where name='PPFOR_TZB_JobPost_UpdateResumeName' and type='P')
	drop procedure PPFOR_TZB_JobPost_UpdateResumeName
PPFOR_GO;

CREATE PROCEDURE PPFOR_TZB_JobPost_UpdateResumeName(@ID int,@ResumeName nvarchar(255))
 AS 
	UPDATE [PPFOR_TZB_JobPost] SET [ResumeName] = @ResumeName WHERE [ID]=@ID 
PPFOR_GO;

--获取职位简历静态文件名
if exists(select name from sysobjects where name='PPFOR_TZB_JobPost_GetResumeName' and type='P')
	drop procedure PPFOR_TZB_JobPost_GetResumeName
PPFOR_GO;
create procedure PPFOR_TZB_JobPost_GetResumeName(@ID int)
as
	select [ResumeName] from [PPFOR_TZB_JobPost] where [ID]=@ID 
PPFOR_GO;