-- ==============================
-- 长登科技 www.ppfor.com
-- 唐镇波   2010-04-21
-- ------------------------------
-- 简历表单项表
-- ==============================

--创建简历表单项表
if not exists(select name from sysobjects where name='PPFOR_TZB_ResumeItem' and type='U')
	create table PPFOR_TZB_ResumeItem
	(
		[ID] int identity(1,1) not null primary key,				--自动编号		
		[FormId] int not null REFERENCES [PPFOR_TZB_ResumeForm]([ID]) ON DELETE CASCADE,	--所属表单ID
		[Name] nvarchar(20) COLLATE Chinese_PRC_CS_AS not null,		--表单项名称
		[FieldName] varchar(32) COLLATE Chinese_PRC_CI_AS not null,	--字段名
		[Order] int not null default(0),							--排序序号
		[State] bit not null default(1),							--是否启用
		[NotNull] bit not null default(1),							--是否必填
		[Type] tinyint not null,									--表单项类型
		[Default] nvarchar(255),									--默认值
		[Args1] int not null default(0),							--整型参数1	
		[Args2] int not null default(0),							--整型参数2
		[Args3] int not null default(0),							--整型参数3
		[Args4] nvarchar(255),										--参数4
		[Args5] ntext,												--参数5	
		[Prompt] nvarchar(255),										--附加提示
		[UpdateTime] datetime not null								--添加时间
	)
PPFOR_GO;

--创建添加数据的存储过程
if exists(select name from sysobjects where name='PPFOR_TZB_ResumeForm_SetProcedure_Add' and type='P')
	drop procedure PPFOR_TZB_ResumeForm_SetProcedure_Add
PPFOR_GO;
create procedure PPFOR_TZB_ResumeForm_SetProcedure_Add(@tableName varchar(32))
as
declare @colName varchar(32)
declare @colType varchar(32)
declare @s char(1)
declare @c char(1)
declare @index int
declare @names varchar(8000)
declare @paraNames varchar(8000)
declare @paraNames2 varchar(8000)
declare @params varchar(8000)
declare @sql varchar(8000) 
	begin
	if left(@tableName,13)='PPFOR_Resume_'
		begin
			declare pcurr cursor for
				SELECT [name],type_name([xtype]) FROM syscolumns WHERE ([id] = (SELECT MAX(id)
	     		FROM sysobjects WHERE xtype = 'u' AND [name] = ('PPFOR_TZB_' + @tableName)))
	
			set @s=','
			set @c='@'
			set @index=0
			open pcurr
			fetch next from pcurr into @colName,@colType
			while (@@fetch_status = 0)
			begin			
			 	if @colName is not null
				begin
					set @index=@index +1
					if(@index>6 and @index<=106)
					begin
						if @colType='int' or @colType='datetime' or @colType='ntext' 
							or @colType='nvarchar' or @colType='numeric'
						begin	
							if @paraNames is not null
								set @paraNames=@paraNames + @s + @c + @colName
							else
								set @paraNames= @s + @c + @colName
							if @paraNames2 is not null
								set @paraNames2=@paraNames2 + @s + '[' + @colName + ']=' + @c + @colName
							else
								set @paraNames2= 'set [' + @colName + ']=' + @c + @colName
							if @names is null
								set @names= @s + '[' + @colName + ']'
							else
								set @names=@names + @s + '[' + @colName + ']'
							if @params is null
								set @params= @s + @c + @colName
							else
								set @params=@params + @s + @c + @colName
							if @colType='int' or @colType='datetime' or @colType='ntext'
								set @paraNames=@paraNames + ' ' + @colType
							else if @colType='nvarchar'
								set @paraNames=@paraNames + ' nvarchar(255)'
							else
								set @paraNames=@paraNames + ' numeric(14,4)'
						end		
					end		
				end		
			 	fetch next from pcurr into @colName,@colType
			end
			close pcurr
			deallocate pcurr 
			BEGIN TRANSACTION TranStart
			if object_id('PPFOR_TZB_' + @tableName + '_Add', 'P' ) is not null
				set @sql='alter procedure [PPFOR_TZB_' + @tableName + '_Add]' 
			else
				set @sql='create procedure [PPFOR_TZB_' + @tableName + '_Add]' 
			set @sql=@sql + '(@_userName nvarchar(20),@_postID int,@_submitIP varchar(15),@_submitUrl varchar(255),@_submitTime datetime'
			set @sql=@sql + @paraNames + ',@_id int output) as '
			set @sql=@sql + 'begin   insert into [PPFOR_TZB_' + @tableName + ']([_userName],[_postID],[_submitIP],[_submitUrl],
				[_submitTime]' + @names + ') values ('
			set @sql=@sql + '@_userName,@_postID,@_submitIP,@_submitUrl,@_submitTime' + @params + ')'
			set @sql=@sql + '	if(@@rowcount>0) set @_id=@@identity else set @_id=0  end'  
			exec(@sql)

			if object_id('PPFOR_TZB_' + @tableName + '_Update', 'P' ) is not null
				set @sql='alter procedure [PPFOR_TZB_' + @tableName + '_Update]' 
			else
				set @sql='create procedure [PPFOR_TZB_' + @tableName + '_Update]' 
			set @sql=@sql + '(@_id int'
			set @sql=@sql + @paraNames + ') as '
			set @sql=@sql + 'update [PPFOR_TZB_' + @tableName + '] ' + @paraNames2
			set @sql=@sql + ' where [_id]=@_id'
			exec(@sql)
			if @@error<>0 and @@trancount>0
				ROLLBACK TRANSACTION TranStart
			COMMIT TRANSACTION TranStart
		end
	end
PPFOR_GO;

--添加简历表单项
if exists(select name from sysobjects where name='PPFOR_TZB_ResumeItem_Add' and type='P')
	drop procedure PPFOR_TZB_ResumeItem_Add
PPFOR_GO;
create procedure PPFOR_TZB_ResumeItem_Add(@ID int output,@FormId int,@TableName varchar(32),
	@Name nvarchar(20),@FieldName varchar(32),@Order int,@State bit,
	@NotNull bit,@Type tinyint,@FieldType varchar(20),@Default nvarchar(255),
	@Args1 int,@Args2 int,@Args3 int,@Args4 nvarchar(255),
	@Args5 ntext,@Prompt nvarchar(255),@UpdateTime datetime)
as
	declare @maxOrder int
	declare @sql varchar(1000)
	begin
	set @ID=0

	BEGIN TRANSACTION TranStart
	select @maxOrder=max([Order])+1 from PPFOR_TZB_ResumeItem where [FormId]=@formId
	if(@maxOrder is not null)
		set @Order=@maxOrder
	insert into PPFOR_TZB_ResumeItem([FormId],[Name],[FieldName],[Order],[State],[NotNull],
		[Type],[Default],[Args1],[Args2],[Args3],[Args4],[Args5],[Prompt],[UpdateTime]) 
		values (@FormId,@Name,@FieldName,@Order,@State,@NotNull,@Type,@Default,@Args1,
		@Args2,@Args3,@Args4,@Args5,@Prompt,@UpdateTime)
	if(@@rowcount>0)
		begin 	
		set @ID=@@identity
		set @sql='alter table [PPFOR_TZB_' + @TableName + '] add [' + @FieldName + '] '
			 + @FieldType + ' null'
		exec(@sql)
		exec PPFOR_TZB_ResumeForm_SetProcedure_Add @TableName
		end
	if @@error<>0 and @@trancount>0 
		ROLLBACK TRANSACTION TranStart
	else 
		COMMIT TRANSACTION TranStart
	end
PPFOR_GO;

--更新简历表单项
if exists(select name from sysobjects where name='PPFOR_TZB_ResumeItem_Update' and type='P')
	drop procedure PPFOR_TZB_ResumeItem_Update
PPFOR_GO;
create procedure PPFOR_TZB_ResumeItem_Update(@ID int,@FormId int,@Name nvarchar(20),@Order int,
	@State bit,@NotNull bit,@Default nvarchar(255),@Args1 int,@Args2 int,@Args3 int,
	@Args4 nvarchar(255),@Args5 ntext,@Prompt nvarchar(255),@UpdateTime datetime)
as
	update PPFOR_TZB_ResumeItem set [Name]=@Name,[Order]=@Order,[State]=@State,[NotNull]=@NotNull,
		[Default]=@Default,[Args1]=@Args1,[Args2]=@Args2,[Args3]=@Args3,[Args4]=@Args4,
		[Args5]=@Args5,[Prompt]=@Prompt,[UpdateTime]=@UpdateTime where [ID]=@ID and [FormId]=@FormId
PPFOR_GO;

--更新简历表单项排序
if exists(select name from sysobjects where name='PPFOR_TZB_ResumeItem_UpdateOrder' and type='P')
	drop procedure PPFOR_TZB_ResumeItem_UpdateOrder
PPFOR_GO;
create procedure PPFOR_TZB_ResumeItem_UpdateOrder(@ID int,@Order int,@UpdateTime datetime)
as
	update PPFOR_TZB_ResumeItem set [Order]=@Order,[UpdateTime]=@UpdateTime where [ID]=@id
PPFOR_GO;

--删除简历表单项
if exists(select name from sysobjects where name='PPFOR_TZB_ResumeItem_Delete' and type='P')
	drop procedure PPFOR_TZB_ResumeItem_Delete
PPFOR_GO;
create procedure PPFOR_TZB_ResumeItem_Delete(@ID int)
as
	declare @sql varchar(1000)
	declare @FieldName varchar(32)
	declare @formId int
	declare @TableName varchar(32)
	begin
	BEGIN TRANSACTION TranStart
		select @FieldName=[FieldName],@formId=[FormId] from PPFOR_TZB_ResumeItem where [ID]=@ID
		if(@FieldName is not null) and (@formId is not null)
		begin
			select @TableName=[TableName] from PPFOR_TZB_ResumeForm where [ID]=@formId
			if(@TableName is not null)
			begin
				set @sql='alter table [PPFOR_TZB_' + @TableName + '] drop column [' + @FieldName + ']'
				exec(@sql)
				exec PPFOR_TZB_ResumeForm_SetProcedure_Add @TableName
			end
		end
		delete from PPFOR_TZB_ResumeItem where [ID]=@ID
	if @@error<>0 and @@trancount>0
		ROLLBACK TRANSACTION TranStart
	else
		COMMIT TRANSACTION TranStart
	end
PPFOR_GO; 

--根据表单项ID获取简历表单项
if exists(select name from sysobjects where name='PPFOR_TZB_ResumeItem_GetModel' and type='P')
	drop procedure PPFOR_TZB_ResumeItem_GetModel
PPFOR_GO;
create procedure PPFOR_TZB_ResumeItem_GetModel(@ID int)
as
	select * from PPFOR_TZB_ResumeItem where [ID]=@ID
PPFOR_GO;

--根据表单ID和状态限定获取所有表单项
if exists(select name from sysobjects where name='PPFOR_TZB_ResumeItem_GetModelList' and type='P')
	drop procedure PPFOR_TZB_ResumeItem_GetModelList
PPFOR_GO;
create procedure PPFOR_TZB_ResumeItem_GetModelList(@formId int,@isCheckState bit)
as
	if @isCheckState=1
		select * from PPFOR_TZB_ResumeItem where [FormId]=@formId and [State]=1
			 order by [Order] asc,[ID] asc
	else
		select * from PPFOR_TZB_ResumeItem where [FormId]=@formId 
			order by [Order] asc,[ID] asc
PPFOR_GO;

--分页返回所有简历表单项资料，只包含ID，表单项名称，字段名，是否必填，字段类型
if exists(select name from sysobjects where name='PPFOR_TZB_ResumeItem_GetShortList' and type='P')
	drop procedure PPFOR_TZB_ResumeItem_GetShortList
PPFOR_GO;
create procedure PPFOR_TZB_ResumeItem_GetShortList(@formId int)
as
	select [ID],[FormId],[Name],[FieldName],[Order],[NotNull],[Type],[UpdateTime] from PPFOR_TZB_ResumeItem where [FormId]=@formId
PPFOR_GO;

--返回是否允许添加简历表单项，是为1，否为0
if exists(select name from sysobjects where name='PPFOR_TZB_ResumeItem_IsEnableAdd' and type='P')
	drop procedure PPFOR_TZB_ResumeItem_IsEnableAdd
PPFOR_GO;
create procedure PPFOR_TZB_ResumeItem_IsEnableAdd(@formId int,@Name nvarchar(20),
	@FieldName varchar(32),@retValue bit output)
as
	declare @count int
	begin
		if exists(select [ID] from PPFOR_TZB_ResumeForm where [ID]=@formId)
		begin
			select @count=count([ID]) from PPFOR_TZB_ResumeItem where [FormId]=@formId
			if @count is null or (@count<100)
			begin			
				if exists(select [ID] from PPFOR_TZB_ResumeItem where [FormId]=@formId 
					and ([Name]=@Name or [FieldName]=@FieldName))
		 			set @retValue=0
				else
					set @retValue=1
			end
			else
				set @retValue=0
		end
		else
			set @retValue=0
	end
PPFOR_GO;

