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

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

--设置参数值
if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_JobParamValues_SetValue]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_JobParamValues_SetValue]
PPFOR_GO;
create procedure PPFOR_TZB_JobParamValues_SetValue(@postID int,@paramID int,
	@customValue nvarchar(255),@jvID int out)
as
	begin
		declare @tempID int
		select @tempID=jvID from [PPFOR_TZB_JobParamValues] where postID=@postID 
			and paramID=@paramID
		if @tempID is null
			begin
				insert into [PPFOR_TZB_JobParamValues] (postID,paramID,customValue)
					values (@postID,@paramID,@customValue)
				set @jvID=@@identity
			end
		else
			begin
				update [PPFOR_TZB_JobParamValues] set customValue=@customValue 
					where jvID=@tempID
				set @jvID=@tempID
			end
	end
PPFOR_GO;

--删除参数值
if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_JobParamValues_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_JobParamValues_Delete]
PPFOR_GO;
create procedure PPFOR_TZB_JobParamValues_Delete(@jvID int)
as
	begin
		delete from [PPFOR_TZB_JobParamValues] where jvID=@jvID
	end
PPFOR_GO;

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

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