-- ==============================
-- 长登科技 www.ppfor.com
-- 唐镇波   2010-04-21
-- ------------------------------
-- 自定义表单
-- ==============================

--创建自定义表单
if not exists(select name from sysobjects where name='PPFOR_TZB_CustomForm' and type='U')
	create table PPFOR_TZB_CustomForm
	(
		[id] int identity(1,1) not null primary key,				--自动编号			
		formTableName varchar(32) COLLATE Chinese_PRC_CI_AS not null unique,	--表名称
		formName nvarchar(20) COLLATE Chinese_PRC_CS_AS not null,	--表单名称
		formFileSaveAddress nvarchar(255),							--上传文件保存地址
		formFileSize int not null default(2048),					--上传文件最大KB数
		formState bit not null default(1),							--表单状态，是否可用
		formTimeLimit bit not null default(0),						--是否限制使用时间
		formTimeStart datetime,										--开始时间
		formTimeEnd	datetime,										--结束时间
		formOnlyOnce bit not null default(0),						--是否只能提交一次
		formLogined bit not null default(0),						--是否需要登录
		formMemberGroup varchar(255),								--会员组限制
		formShowCheckCode bit not null default(0),					--使用启用验证码
		formSendMail bit not null default(0),						--提交数据时是否发送邮件 
		formMailAddress	nvarchar(50),								--邮箱地址
		formPrompt nvarchar(255),									--表单说明
		formAddTime datetime not null,								--添加的时间
		formGoNextUrl nvarchar(255)									--提交成功跳转地址
	)
PPFOR_GO;

--修改自定义表单存储过程
if exists(select name from sysobjects where name='PPFOR_TZB_CustomForm_SetProcedure_GetDataList' and type='P')
	drop procedure PPFOR_TZB_CustomForm_SetProcedure_GetDataList
PPFOR_GO;
create procedure PPFOR_TZB_CustomForm_SetProcedure_GetDataList(@formTableName varchar(32))
as
	declare @sql varchar(8000)
	if left(@formTableName,11)='PPFOR_Form_' and charindex('''',@formTableName)<=0
	begin
		BEGIN TRANSACTION TranStart	
		if object_id('PPFOR_TZB_' + @formTableName + '_GetDataList', 'P' ) is not null
		begin
			set @sql='drop procedure [PPFOR_TZB_' + @formTableName + '_GetDataList]'
			exec(@sql)
		end
		
		set @sql='create procedure PPFOR_TZB_' + @formTableName + '_GetDataList
			(@pageSize int,@pageIndex int)
			as
			begin
				declare @rc int
				declare @pc int
				declare @sqlStr nvarchar(1000)
				declare @sqlStr2 nvarchar(1000)				
				if @pageSize<1
					set @pageSize=20		
				select @rc=count([_id]) from [PPFOR_TZB_' + @formTableName + ']
				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],[_submitIP],[_submitUrl],[_submitTime]
				 	from [PPFOR_TZB_' + @formTableName + '] ''			
				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_CustomForm_Add' and type='P')
	drop procedure PPFOR_TZB_CustomForm_Add
PPFOR_GO;
create procedure PPFOR_TZB_CustomForm_Add(@formTableName varchar(32),@formName nvarchar(20),
	@formFileSaveAddress nvarchar(255),@formFileSize int,@formState bit,@formTimeLimit bit,
	@formTimeStart datetime,@formTimeEnd datetime,@formOnlyOnce bit,@formLogined bit,
	@formMemberGroup varchar(255),@formShowCheckCode bit,@formSendMail bit,
	@formMailAddress nvarchar(50),@formPrompt nvarchar(255),
	@formAddTime datetime,@formGoNextUrl nvarchar(255),@id int output)
as
	declare @sql varchar(1000)
	begin
	set @id=0	
	BEGIN TRANSACTION TranStart
	insert into PPFOR_TZB_CustomForm(formTableName,formName,formFileSaveAddress,formFileSize,
		formState,formTimeLimit,formTimeStart,formTimeEnd,formOnlyOnce,formLogined,formMemberGroup,
		formShowCheckCode,formSendMail,formMailAddress,formPrompt,formAddTime,formGoNextUrl) values 
		(@formTableName,@formName,@formFileSaveAddress,@formFileSize,@formState,@formTimeLimit,
		@formTimeStart,@formTimeEnd,@formOnlyOnce,@formLogined,@formMemberGroup,
		@formShowCheckCode,@formSendMail,@formMailAddress,@formPrompt,@formAddTime,@formGoNextUrl)
	if(@@rowcount>0)
		begin 	
			set @id=@@identity
			set @sql='create table [PPFOR_TZB_' + @formTableName + ']
				([_id] int identity(1,1) not null primary key,
				 [_userName] nvarchar (20) COLLATE Chinese_PRC_CS_AS,
	             [_submitIP] varchar (15),
			     [_submitUrl] varchar (255),
	             [_submitTime] datetime not null)'
			exec(@sql)

			exec PPFOR_TZB_CustomForm_SetProcedure_GetDataList @formTableName
			
			if object_id('PPFOR_TZB_' + @formTableName + '_Add', 'P' ) is not null
			begin
				set @sql='drop procedure [PPFOR_TZB_' + @formTableName + '_Add]'
				exec(@sql)
			end
			set @sql='create procedure [PPFOR_TZB_' + @formTableName + '_Add]
				(@_userName nvarchar(20),@_submitIP varchar(15),@_submitUrl varchar(255),@_submitTime datetime,
					@_id int output)
				as
					begin
						insert into	[PPFOR_TZB_' + @formTableName + '] ([_userName],[_submitIP],[_submitUrl],[_submitTime]) 
							values (@_userName,@_submitIP,@_submitUrl,@_submitTime)
						if @@rowcount>0
							set @_id=@@identity
						else
							set @_id=0
					end'
			exec(@sql)
			if object_id('PPFOR_TZB_' + @formTableName + '_Update', 'P' ) is not null
			begin
				set @sql='drop procedure [PPFOR_TZB_' + @formTableName + '_Update]'
				exec(@sql)
			end			
			if object_id('PPFOR_TZB_' + @formTableName + '_GetModel', 'P' ) is not null
			begin
				set @sql='drop procedure [PPFOR_TZB_' + @formTableName + '_GetModel]'
				exec(@sql)
			end
			set @sql='create procedure [PPFOR_TZB_' + @formTableName + '_GetModel](@_id int)
				as
					begin
						select * from [PPFOR_TZB_' + @formTableName + '] where [_id]=@_id
					end'
			exec(@sql)
			if object_id('PPFOR_TZB_' + @formTableName + '_Delete', 'P' ) is not null
			begin
				set @sql='drop procedure [PPFOR_TZB_' + @formTableName + '_Delete]'
				exec(@sql)
			end
			set @sql='create procedure [PPFOR_TZB_' + @formTableName + '_Delete](@_id int)
				as
					begin
						delete from [PPFOR_TZB_' + @formTableName + '] where [_id]=@_id
					end'
			exec(@sql)
			if object_id('PPFOR_TZB_' + @formTableName + '_IsExistsByIP', 'P' ) is not null
			begin
				set @sql='drop procedure [PPFOR_TZB_' + @formTableName + '_IsExistsByIP]'
				exec(@sql)
			end
			set @sql='create procedure [PPFOR_TZB_' + @formTableName + '_IsExistsByIP]
				(@_submitIP varchar(15),@retValue bit output)
				as
					begin
						if exists (select [_id] from [PPFOR_TZB_' + @formTableName + '] where [_submitIP]=@_submitIP)
							set @retValue=1
						else
							set @retValue=0
					end'
			exec(@sql)
			if object_id('PPFOR_TZB_' + @formTableName + '_IsExistsByName', 'P' ) is not null
			begin
				set @sql='drop procedure [PPFOR_TZB_' + @formTableName + '_IsExistsByName]'
				exec(@sql)
			end
			set @sql='create procedure [PPFOR_TZB_' + @formTableName + '_IsExistsByName]
				(@_userName nvarchar(20),@retValue bit output)
				as
					begin
						if exists (select [_id] from [PPFOR_TZB_' + @formTableName + '] where [_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_CustomForm_Update' and type='P')
	drop procedure PPFOR_TZB_CustomForm_Update
PPFOR_GO;
create procedure PPFOR_TZB_CustomForm_Update(@id int,@formName nvarchar(20),
	@formFileSaveAddress nvarchar(255),@formFileSize int,@formState bit,@formTimeLimit bit,
	@formTimeStart datetime,@formTimeEnd datetime,@formOnlyOnce bit,@formLogined bit,
	@formMemberGroup varchar(255),@formShowCheckCode bit,@formSendMail bit,
	@formMailAddress nvarchar(50),@formPrompt nvarchar(255),@formGoNextUrl nvarchar(255))
as
	update PPFOR_TZB_CustomForm set formName=@formName,formFileSaveAddress=@formFileSaveAddress,
		formFileSize=@formFileSize,formState=@formState,formTimeLimit=@formTimeLimit,
		formTimeStart=@formTimeStart,formTimeEnd=@formTimeEnd,formOnlyOnce=@formOnlyOnce,
		formLogined=@formLogined,formMemberGroup=@formMemberGroup,
		formShowCheckCode=@formShowCheckCode,formSendMail=@formSendMail,
		formMailAddress=@formMailAddress,formPrompt=@formPrompt,formGoNextUrl=@formGoNextUrl where [id]=@id
PPFOR_GO;

--删除自定义表单
if exists(select name from sysobjects where name='PPFOR_TZB_CustomForm_Delete' and type='P')
	drop procedure PPFOR_TZB_CustomForm_Delete
PPFOR_GO;
create procedure PPFOR_TZB_CustomForm_Delete(@id int)
as
	declare @tableName varchar(32)
	declare @sql varchar(1000)
	begin
		BEGIN TRANSACTION TranStart
			select @tableName=formTableName from PPFOR_TZB_CustomForm where [id]=@id
			if @@rowcount>0 and (@tableName is not null)
				begin
					delete from PPFOR_TZB_CustomItem 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_CustomForm 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_CustomForm_GetModel' and type='P')
	drop procedure PPFOR_TZB_CustomForm_GetModel
PPFOR_GO;
create procedure PPFOR_TZB_CustomForm_GetModel(@id int)
as
	select formTableName,formName,formFileSaveAddress,formFileSize,formState,formTimeLimit,
		formTimeStart,formTimeEnd,formOnlyOnce,formLogined,formMemberGroup,
		formShowCheckCode,formSendMail,formMailAddress,formPrompt,formAddTime,formGoNextUrl
		from PPFOR_TZB_CustomForm where [id]=@id
PPFOR_GO;

--根据表单ID获取自定义表单部分信息：表名称，表单名称
if exists(select name from sysobjects where name='PPFOR_TZB_CustomForm_GetShortModel' and type='P')
	drop procedure PPFOR_TZB_CustomForm_GetShortModel
PPFOR_GO;
create procedure PPFOR_TZB_CustomForm_GetShortModel(@id int)
as
	select formTableName,formName from PPFOR_TZB_CustomForm where [id]=@id
PPFOR_GO;
if exists(select name from sysobjects where name='PPFOR_TZB_CustomForm_GetModelNewList' and type='P')
	drop procedure PPFOR_TZB_CustomForm_GetModelNewList
PPFOR_GO;
create procedure PPFOR_TZB_CustomForm_GetModelNewList(@number int)
as
	if @number>0
		set ROWCOUNT @number
	select [id],formTableName,formName from PPFOR_TZB_CustomForm order by [id] desc
PPFOR_GO;

--分页返回所有自定义表单资料，只包含ID，名称和附加提示
if exists(select name from sysobjects where name='PPFOR_TZB_CustomForm_GetModelListByPage' and type='P')
	drop procedure PPFOR_TZB_CustomForm_GetModelListByPage
PPFOR_GO;
create procedure PPFOR_TZB_CustomForm_GetModelListByPage(@pageSize int,@pageIndex int)
as
	begin
		declare @rc int
		declare @pc int
		declare @sqlStr nvarchar(1000)
		declare @sqlStr2 nvarchar(1000)
		
		if @pageSize<1
			set @pageSize=20

		select @rc=count([id]) from PPFOR_TZB_CustomForm
		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],formName,formPrompt from PPFOR_TZB_CustomForm '
	
		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
PPFOR_GO;

--返回是否存在自定义表单表名称，是为1，否为0
if exists(select name from sysobjects where name='PPFOR_TZB_CustomForm_IsExistsTableName' and type='P')
	drop procedure PPFOR_TZB_CustomForm_IsExistsTableName
PPFOR_GO;
create procedure PPFOR_TZB_CustomForm_IsExistsTableName(@formTableName varchar(32),@retValue bit output)
as
	begin
		if exists(select [id] from PPFOR_TZB_CustomForm where formTableName=@formTableName)
 			set @retValue=1
		else if exists(select name from sysobjects where name=('PPFOR_TZB_' + @formTableName) and type='U')
			set @retValue=1
		else
			set @retValue=0
	end
PPFOR_GO;


