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

--创建招聘职位扩展属性表
if not exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_JobParameters]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE [PPFOR_TZB_JobParameters] (
	[paramID] int identity(1,1) not null primary key,
	[belongLanguage] int NOT NULL REFERENCES [PPFOR_TZB_Language]([ID]) ON DELETE CASCADE,  --所属语言
	[paramName] nvarchar (20) COLLATE Chinese_PRC_CS_AS NOT NULL,	--参数名称
	[paramType] int NOT NULL default (0),							--参数值类型
	[orderID] int NOT NULL,											--排序
	[paramValues] text NULL											--参数值
)
PPFOR_GO;

--添加参数
if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_JobParameters_Add]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_JobParameters_Add]
PPFOR_GO;
create procedure PPFOR_TZB_JobParameters_Add(@belongLanguage int,@paramName nvarchar(20),
	@paramType int, @orderID int,@paramValues text, @paramID int out)
as
	begin
		declare @tempID int
		select @tempID=count(1) from [PPFOR_TZB_JobParameters] 
			where belongLanguage=@belongLanguage and paramName=@paramName
		if @tempID=0
			begin
				insert into [PPFOR_TZB_JobParameters] (belongLanguage,paramName,paramType,orderID,
					paramValues) values (@belongLanguage,@paramName,@paramType,@orderID,@paramValues)
				set @paramID=@@identity
			end
		else
			set @paramID=0
	end
PPFOR_GO;

--更新参数
if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_JobParameters_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_JobParameters_Update]
PPFOR_GO;
create procedure PPFOR_TZB_JobParameters_Update(@paramID int,@belongLanguage int,
	@paramName nvarchar(20),@paramType int, @orderID int,@paramValues text)
as
	begin
		declare @tempID int
		select @tempID=count(1) from [PPFOR_TZB_JobParameters] 
			where belongLanguage=@belongLanguage and paramName=@paramName and paramID<>@paramID
		if @tempID=0
			update [PPFOR_TZB_JobParameters] set paramName=@paramName,paramType=@paramType,
				orderID=@orderID,paramValues=@paramValues where paramID=@paramID
	end
PPFOR_GO;

--删除参数
if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_JobParameters_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_JobParameters_Delete]
PPFOR_GO;
create procedure PPFOR_TZB_JobParameters_Delete(@paramID int,@belongLanguage int)
as
	delete from [PPFOR_TZB_JobParameters] where paramID=@paramID 
		and belongLanguage=@belongLanguage
PPFOR_GO;

--获取参数
if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_JobParameters_GetModel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_JobParameters_GetModel]
PPFOR_GO;
create procedure PPFOR_TZB_JobParameters_GetModel(@paramID int,@belongLanguage int)
as
	begin
		select * from [PPFOR_TZB_JobParameters] where paramID=@paramID 
			and belongLanguage=@belongLanguage
	end
PPFOR_GO;

--根据语言获取所有参数
if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_JobParameters_GetModelListByLanguage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_JobParameters_GetModelListByLanguage]
PPFOR_GO;
create procedure PPFOR_TZB_JobParameters_GetModelListByLanguage(@belongLanguage int)
as
	begin
		select * from [PPFOR_TZB_JobParameters] where belongLanguage=@belongLanguage 
			order by orderID asc,paramID asc
	end
PPFOR_GO;

--根据语言获取所有参数部分信息
if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_JobParameters_GetShortListByLanguage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_JobParameters_GetShortListByLanguage]
PPFOR_GO;
create procedure PPFOR_TZB_JobParameters_GetShortListByLanguage(@belongLanguage int)
as
	begin
		if @belongLanguage>0
			select [paramID],[paramName],[paramType] from [PPFOR_TZB_JobParameters] 
				where belongLanguage=@belongLanguage order by orderID asc,paramID asc
		else 
			select [paramID],[paramName],[paramType] from [PPFOR_TZB_JobParameters]
			 	order by belongLanguage asc,paramType asc, orderID asc,paramID asc
	end
PPFOR_GO;

--是否存在参数
if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_JobParameters_IsExists]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_JobParameters_IsExists]
PPFOR_GO;
create procedure PPFOR_TZB_JobParameters_IsExists(@belongLanguage int,
	@paramName nvarchar(20),@retValue bit out)
as
	begin
		declare @tempID int
		select @tempID=count(1) from [PPFOR_TZB_JobParameters] 
			where belongLanguage=@belongLanguage and paramName=@paramName
		if @tempID=0
			set @retValue=0
		else
			set @retValue=1
	end
PPFOR_GO;

--是否允许更新参数
if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_JobParameters_IsEnableUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_JobParameters_IsEnableUpdate]
PPFOR_GO;
create procedure PPFOR_TZB_JobParameters_IsEnableUpdate(@paramID int,
	@belongLanguage int,@paramName nvarchar(20),@retValue bit out)
as
	begin
		declare @tempID int
		select @tempID=count(1) from [PPFOR_TZB_JobParameters] 
			where belongLanguage=@belongLanguage and paramName=@paramName and paramID<>@paramID
		if @tempID=0
			set @retValue=1
		else
			set @retValue=0
	end
PPFOR_GO;
