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

--创建简历表单
if not exists(select name from sysobjects where name='PPFOR_TZB_ResumeForm' and type='U')
	create table PPFOR_TZB_ResumeForm
	(
		[ID] int identity(1,1) not null primary key,				--自动编号		
		[BelongLanguage] int NOT NULL REFERENCES [PPFOR_TZB_Language]([ID]) ON DELETE CASCADE,  --所属语言	
		[TableName] varchar(32) COLLATE Chinese_PRC_CI_AS not null unique,	--表名称
		[Name] nvarchar(20) COLLATE Chinese_PRC_CS_AS not null,		--表单名称
		[FileSaveAddress] nvarchar(255),							--上传文件保存地址
		[FileSize] int not null default(2048),						--上传文件最大KB数
		[State] bit not null default(1),							--表单状态，是否可用		
		[OnlyOnce] bit not null default(0),							--是否只能提交一次	
		[ShowCheckCode] bit not null default(0),					--使用启用验证码
		[SendMail] bit not null default(0),							--提交数据时是否发送邮件 
		[MailAddress] varchar(100),									--邮箱地址
		[Prompt] nvarchar(255),										--表单说明
		[UpdateTime] datetime not null,								--添加的时间
		[GoNextUrl] nvarchar(255) null								--提交成功跳转地址
	)
PPFOR_GO;

--修改简历表单存储过程
if exists(select name from sysobjects where name='PPFOR_TZB_ResumeForm_SetProcedure_GetDataList' and type='P')
	drop procedure PPFOR_TZB_ResumeForm_SetProcedure_GetDataList
PPFOR_GO;
create procedure PPFOR_TZB_ResumeForm_SetProcedure_GetDataList(@TableName varchar(32))
as
	declare @sql varchar(8000)
	if left(@TableName,13)='PPFOR_Resume_' and charindex('''',@TableName)<=0
	begin
		BEGIN TRANSACTION TranStart	
		if object_id('PPFOR_TZB_' + @TableName + '_GetDataList', 'P' ) is not null
		begin
			set @sql='drop procedure [PPFOR_TZB_' + @TableName + '_GetDataList]'
			exec(@sql)
		end
		
		set @sql='create procedure PPFOR_TZB_' + @TableName + '_GetDataList
			(@postId int,@pageSize int,@pageIndex int)
			as
			begin
				declare @rc int
				declare @pc int
				declare @sqlStr nvarchar(1000)
				declare @sqlStr2 nvarchar(1500)				
				if @pageSize<1
					set @pageSize=20		
				if @postId<=0
					select @rc=count([_id]) from [PPFOR_TZB_' + @TableName + ']
				else
					select @rc=count([_id]) from [PPFOR_TZB_' + @TableName + '] where [_postID]=@postId
				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],[_userName],[_postID],[_submitIP],[_submitUrl],[_submitTime]
				 	from [PPFOR_TZB_' + @TableName + '] ''		
				if @postId>0
					set @sqlStr2=@sqlStr2+'' where [_postID]=''+str(@postId)
				if @pageIndex <= 1 or @pc <= 1
					set @sqlStr=''select top '' + str(@pageSize) + @sqlStr2 + '' order by [_id] desc''
				else if @pageIndex = @pc
					set @sqlStr =''select * from (select top '' + str( @rc - @pageSize * (@pageIndex-1) ) 
						+ @sqlStr2 + '' order by [_id] asc) Temptable order by [_id] desc''
				else  
					set @sqlStr =''select top '' + str(@pageSize) + '' * from (select top ''
						+ str( @rc - @pageSize * (@pageIndex-1) ) + @sqlStr2 + '' order by 
						[_id] asc) Temptable order by [_id] desc''
				exec (@sqlStr)
				select @rc as recordCount,@pc as pageCount
			end'
		exec(@sql)	
		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_ResumeForm_Add' and type='P')
	drop procedure PPFOR_TZB_ResumeForm_Add
PPFOR_GO;
create procedure PPFOR_TZB_ResumeForm_Add(@ID int output,@BelongLanguage int,@TableName varchar(32),
	@Name nvarchar(20),@FileSaveAddress nvarchar(255),@FileSize int,@State bit,@OnlyOnce bit,
	@ShowCheckCode bit,@SendMail bit,@MailAddress varchar(100),@Prompt nvarchar(255),
	@UpdateTime datetime,@GoNextUrl nvarchar(255))
as
	declare @sql varchar(1000)
	begin
	set @ID=0	
	BEGIN TRANSACTION TranStart
	insert into PPFOR_TZB_ResumeForm([BelongLanguage],[TableName],[Name],[FileSaveAddress],[FileSize],
		[State],[OnlyOnce],[ShowCheckCode],[SendMail],[MailAddress],[Prompt],[UpdateTime],[GoNextUrl])
		 values (@BelongLanguage,@TableName,@Name,@FileSaveAddress,@FileSize,@State,@OnlyOnce,
		@ShowCheckCode,@SendMail,@MailAddress,@Prompt,@UpdateTime,@GoNextUrl)
	if(@@rowcount>0)
		begin 	
			set @ID=@@identity
			set @sql='create table [PPFOR_TZB_' + @TableName + ']
				([_id] int identity(1,1) not null primary key,
				 [_userName] nvarchar (20) COLLATE Chinese_PRC_CS_AS,
				 [_postID] int not null,	
	             [_submitIP] varchar (15),
			     [_submitUrl] varchar (255),
	             [_submitTime] datetime not null)'
			exec(@sql)

			exec PPFOR_TZB_ResumeForm_SetProcedure_GetDataList @TableName
			
			if object_id('PPFOR_TZB_' + @TableName + '_Add', 'P' ) is not null
			begin
				set @sql='drop procedure [PPFOR_TZB_' + @TableName + '_Add]'
				exec(@sql)
			end
			set @sql='create procedure [PPFOR_TZB_' + @TableName + '_Add]
				(@_userName nvarchar(20),@_postID int,@_submitIP varchar(15),@_submitUrl varchar(255),@_submitTime datetime,
					@_id int output)
				as
					begin
						insert into	[PPFOR_TZB_' + @TableName + '] ([_userName],[_postID],[_submitIP],[_submitUrl],[_submitTime]) 
							values (@_userName,@_postID,@_submitIP,@_submitUrl,@_submitTime)
						if @@rowcount>0
							set @_id=@@identity
						else
							set @_id=0
					end'
			exec(@sql)
			if object_id('PPFOR_TZB_' + @TableName + '_Update', 'P' ) is not null
			begin
				set @sql='drop procedure [PPFOR_TZB_' + @TableName + '_Update]'
				exec(@sql)
			end			
			if object_id('PPFOR_TZB_' + @TableName + '_GetModel', 'P' ) is not null
			begin
				set @sql='drop procedure [PPFOR_TZB_' + @TableName + '_GetModel]'
				exec(@sql)
			end
			set @sql='create procedure [PPFOR_TZB_' + @TableName + '_GetModel](@_id int)
				as
					begin
						select * from [PPFOR_TZB_' + @TableName + '] where [_id]=@_id
					end'
			exec(@sql)
			if object_id('PPFOR_TZB_' + @TableName + '_Delete', 'P' ) is not null
			begin
				set @sql='drop procedure [PPFOR_TZB_' + @TableName + '_Delete]'
				exec(@sql)
			end
			set @sql='create procedure [PPFOR_TZB_' + @TableName + '_Delete](@_id int)
				as
					begin
						delete from [PPFOR_TZB_' + @TableName + '] where [_id]=@_id
					end'
			exec(@sql)			
			if object_id('PPFOR_TZB_' + @TableName + '_IsExistsByIP', 'P' ) is not null
			begin
				set @sql='drop procedure [PPFOR_TZB_' + @TableName + '_IsExistsByIP]'
				exec(@sql)
			end
			set @sql='create procedure [PPFOR_TZB_' + @TableName + '_IsExistsByIP]
				(@_postID int, @_submitIP varchar(15),@retValue bit output)
				as
					begin
						if exists (select [_id] from [PPFOR_TZB_' + @TableName + '] where [_postID]=@_postID and [_submitIP]=@_submitIP)
							set @retValue=1
						else
							set @retValue=0
					end'
			exec(@sql)
			if object_id('PPFOR_TZB_' + @TableName + '_IsExistsByName', 'P' ) is not null
			begin
				set @sql='drop procedure [PPFOR_TZB_' + @TableName + '_IsExistsByName]'
				exec(@sql)
			end
			set @sql='create procedure [PPFOR_TZB_' + @TableName + '_IsExistsByName]
				(@_postID int,@_userName nvarchar(20),@retValue bit output)
				as
					begin
						if exists (select [_id] from [PPFOR_TZB_' + @TableName + '] where [_postID]=@_postID and [_userName]=@_userName)
							set @retValue=1
						else
							set @retValue=0
					end'
			exec(@sql)
		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_ResumeForm_Update' and type='P')
	drop procedure PPFOR_TZB_ResumeForm_Update
PPFOR_GO;
create procedure PPFOR_TZB_ResumeForm_Update(@ID int,@BelongLanguage int,@Name nvarchar(20),
	@FileSaveAddress nvarchar(255),@FileSize int,@State bit,@OnlyOnce bit,@ShowCheckCode bit,
	@SendMail bit,@MailAddress varchar(100),@Prompt nvarchar(255),@UpdateTime datetime,@GoNextUrl nvarchar(255))
as
	update PPFOR_TZB_ResumeForm set [Name]=@Name,[FileSaveAddress]=@FileSaveAddress,
		[FileSize]=@FileSize,[State]=@State,[OnlyOnce]=@OnlyOnce,[ShowCheckCode]=@ShowCheckCode,
		[SendMail]=@SendMail,[MailAddress]=@MailAddress,[Prompt]=@Prompt,[UpdateTime]=UpdateTime,
		[GoNextUrl]=@GoNextUrl where [ID]=@ID and [BelongLanguage]=@BelongLanguage
PPFOR_GO;

--删除简历表单
if exists(select name from sysobjects where name='PPFOR_TZB_ResumeForm_Delete' and type='P')
	drop procedure PPFOR_TZB_ResumeForm_Delete
PPFOR_GO;
create procedure PPFOR_TZB_ResumeForm_Delete(@ID int)
as
	declare @tableName varchar(32)
	declare @sql varchar(1000)
	begin
		BEGIN TRANSACTION TranStart
			select @tableName=[TableName] from PPFOR_TZB_ResumeForm where [ID]=@ID
			if @@rowcount>0 and (@tableName is not null)
				begin
					delete from PPFOR_TZB_ResumeItem where [FormID]=@ID
					set @sql='drop table [PPFOR_TZB_' + @tableName + ']'
					exec(@sql)					
					if object_id('PPFOR_TZB_' + @tableName + '_GetDataList', 'P' ) is not null
					begin
						set @sql='drop procedure [PPFOR_TZB_' + @tableName + '_GetDataList]'
						exec(@sql)
					end
					if object_id('PPFOR_TZB_' + @tableName + '_Add', 'P' ) is not null
					begin
						set @sql='drop procedure [PPFOR_TZB_' + @tableName + '_Add]'
						exec(@sql)
					end
					if object_id('PPFOR_TZB_' + @tableName + '_Update', 'P' ) is not null
					begin
						set @sql='drop procedure [PPFOR_TZB_' + @tableName + '_Update]'
						exec(@sql)
					end
					if object_id('PPFOR_TZB_' + @tableName + '_GetModel', 'P' ) is not null
					begin
						set @sql='drop procedure [PPFOR_TZB_' + @tableName + '_GetModel]'
						exec(@sql)
					end
					if object_id('PPFOR_TZB_' + @tableName + '_Delete', 'P' ) is not null
					begin
						set @sql='drop procedure [PPFOR_TZB_' + @tableName + '_Delete]'
						exec(@sql)
					end
					if object_id('PPFOR_TZB_' + @tableName + '_IsExistsByIP', 'P' ) is not null
					begin
						set @sql='drop procedure [PPFOR_TZB_' + @tableName + '_IsExistsByIP]'
						exec(@sql)
					end
					if object_id('PPFOR_TZB_' + @tableName + '_IsExistsByName', 'P' ) is not null
					begin
						set @sql='drop procedure [PPFOR_TZB_' + @tableName + '_IsExistsByName]'
						exec(@sql)
					end
					delete from PPFOR_TZB_ResumeForm where [ID]=@ID
				end
		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_ResumeForm_GetModel' and type='P')
	drop procedure PPFOR_TZB_ResumeForm_GetModel
PPFOR_GO;
create procedure PPFOR_TZB_ResumeForm_GetModel(@ID int)
as
	select * from PPFOR_TZB_ResumeForm where [ID]=@ID
PPFOR_GO;

--根据表单ID获取简历表单部分信息：表名称，表单名称
if exists(select name from sysobjects where name='PPFOR_TZB_ResumeForm_GetShortModel' and type='P')
	drop procedure PPFOR_TZB_ResumeForm_GetShortModel
PPFOR_GO;
create procedure PPFOR_TZB_ResumeForm_GetShortModel(@ID int)
as
	select [ID],[BelongLanguage],[TableName],[Name] from PPFOR_TZB_ResumeForm where [ID]=@ID
PPFOR_GO;
if exists(select name from sysobjects where name='PPFOR_TZB_ResumeForm_GetModelNewList' and type='P')
	drop procedure PPFOR_TZB_ResumeForm_GetModelNewList
PPFOR_GO;
create procedure PPFOR_TZB_ResumeForm_GetModelNewList(@number int)
as
	if @number>0
		set ROWCOUNT @number
	select [ID],[TableName],[Name] from PPFOR_TZB_ResumeForm order by [ID] desc
PPFOR_GO;

--分页返回所有简历表单资料，只包含ID，名称和附加提示
if exists(select name from sysobjects where name='PPFOR_TZB_ResumeForm_GetModelList' and type='P')
	drop procedure PPFOR_TZB_ResumeForm_GetModelList
PPFOR_GO;
create procedure PPFOR_TZB_ResumeForm_GetModelList(@BelongLanguage int)
as
	select [ID],[TableName],[Name],[Prompt],[UpdateTime] from PPFOR_TZB_ResumeForm where [BelongLanguage]=@BelongLanguage
PPFOR_GO;

--返回是否存在简历表单表名称，是为1，否为0
if exists(select name from sysobjects where name='PPFOR_TZB_ResumeForm_IsExistsTableName' and type='P')
	drop procedure PPFOR_TZB_ResumeForm_IsExistsTableName
PPFOR_GO;
create procedure PPFOR_TZB_ResumeForm_IsExistsTableName(@TableName varchar(32),@retValue bit output)
as
	begin
		if exists(select [ID] from PPFOR_TZB_ResumeForm where TableName=@TableName)
 			set @retValue=1
		else if exists(select name from sysobjects where name=('PPFOR_TZB_' + @TableName) and type='U')
			set @retValue=1
		else
			set @retValue=0
	end
PPFOR_GO;


