-- ==============================
-- 长登科技 www.ppfor.com
-- 唐镇波   2010-04-20
-- ------------------------------
-- 招聘职位扩展参数内容值表
-- ==============================

--创建招聘职位扩展参数内容值表
if not exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_JobContentValues]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE [PPFOR_TZB_JobContentValues] (
	[jcID] [int] identity(1,1) NOT NULL primary key,
	[postID] [int] NOT NULL ,
	[paramID] [int] NOT NULL REFERENCES [PPFOR_TZB_JobParameters](paramID) ON DELETE CASCADE,
	[customContent] [text] NULL
)
PPFOR_GO;

--设置参数内容值
if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_JobContentValues_SetValue]') and OBJECTPROPERTY(id, N'Isprocedure') = 1)
drop procedure [PPFOR_TZB_JobContentValues_SetValue]
PPFOR_GO;
create procedure PPFOR_TZB_JobContentValues_SetValue(@postID int,@paramID int,
	@customContent text,@jcID int out)
as
	begin
		declare @tempID int
		select @tempID=jcID from [PPFOR_TZB_JobContentValues] where postID=@postID 
			and paramID=@paramID
		if @tempID is null
			begin
				insert into [PPFOR_TZB_JobContentValues] (postID,paramID,customContent)
					values (@postID,@paramID,@customContent)
				set @jcID=@@identity
			end
		else
			begin
				update [PPFOR_TZB_JobContentValues] set customContent=@customContent
				 	where jcID=@tempID
				set @jcID=@tempID
			end
	end
PPFOR_GO;

--删除参数内容值
if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_JobContentValues_Delete]') and OBJECTPROPERTY(id, N'Isprocedure') = 1)
drop procedure [PPFOR_TZB_JobContentValues_Delete]
PPFOR_GO;
create procedure PPFOR_TZB_JobContentValues_Delete(@jcID int)
as
	begin
		delete from [JobContentValues] where jcID=@jcID
	end
PPFOR_GO;

--根据招聘职位ID删除参数内容值
if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_JobContentValues_DeleteByJobID]') and OBJECTPROPERTY(id, N'Isprocedure') = 1)
drop procedure [PPFOR_TZB_JobContentValues_DeleteByJobID]
PPFOR_GO;
create procedure PPFOR_TZB_JobContentValues_DeleteByJobID(@postID int)
as
	begin
		delete from [PPFOR_TZB_JobContentValues] where postID=@postID
	end
PPFOR_GO;

--根据招聘职位ID获取所有参数内容值
if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_JobContentValues_GetModelList]') and OBJECTPROPERTY(id, N'Isprocedure') = 1)
drop procedure [PPFOR_TZB_JobContentValues_GetModelList]
PPFOR_GO;
create procedure PPFOR_TZB_JobContentValues_GetModelList(@postID int)
as
	begin
		select * from [PPFOR_TZB_JobContentValues] where postID=@postID and paramID= any 
			(select paramID from [PPFOR_TZB_JobParameters] where paramType=1)
	end
PPFOR_GO;

--根据招聘职位ID获取所有参数内容值
if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_JobContentValues_GetModel]') and OBJECTPROPERTY(id, N'Isprocedure') = 1)
drop procedure [PPFOR_TZB_JobContentValues_GetModel]
PPFOR_GO;
create procedure PPFOR_TZB_JobContentValues_GetModel(@postID int,@paramID int)
as
	begin
		select * from [PPFOR_TZB_JobContentValues] where postID=@postID and paramID=@paramID 
	end
PPFOR_GO;