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

--创建自定义表单项表
if not exists(select name from sysobjects where name='PPFOR_TZB_CustomItem' and type='U')
	create table PPFOR_TZB_CustomItem
	(
		[id] int identity(1,1) not null primary key,				--自动编号		
		formId int not null REFERENCES [PPFOR_TZB_CustomForm]([id]) ON DELETE CASCADE,	--所属表单ID
		itemName nvarchar(20) COLLATE Chinese_PRC_CS_AS not null,						--表单项名称
		itemFieldName varchar(32) COLLATE Chinese_PRC_CI_AS not null,					--字段名
		itemOrder int not null default(0),							--排序序号
		itemState bit not null default(1),							--是否启用
		itemNotNull bit not null default(1),						--是否必填
		itemType tinyint not null,									--表单项类型
		itemDefault nvarchar(255),									--默认值
		itemArgs1 int not null default(0),							--整型参数1	
		itemArgs2 int not null default(0),							--整型参数2
		itemArgs3 int not null default(0),							--整型参数3
		itemArgs4 nvarchar(255),									--参数4
		itemArgs5 ntext,											--参数5	
		itemPrompt nvarchar(255),									--附加提示
		itemAddTime datetime not null								--添加时间
	)
PPFOR_GO;

--创建添加数据的存储过程
if exists(select name from sysobjects where name='PPFOR_TZB_CustomForm_SetProcedure_Add' and type='P')
	drop procedure PPFOR_TZB_CustomForm_SetProcedure_Add
PPFOR_GO;
create procedure PPFOR_TZB_CustomForm_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,11)='PPFOR_Form_'
		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>5 and @index<=105)
					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),@_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],[_submitIP],[_submitUrl],
				[_submitTime]' + @names + ') values ('
			set @sql=@sql + '@_userName,@_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_CustomItem_Add' and type='P')
	drop procedure PPFOR_TZB_CustomItem_Add
PPFOR_GO;
create procedure PPFOR_TZB_CustomItem_Add(@formId int,@formTableName varchar(32),
	@itemName nvarchar(20),@itemFieldName varchar(32),@itemOrder int,@itemState bit,
	@itemNotNull bit,@itemType tinyint,@itemFieldType varchar(20),@itemDefault nvarchar(255),
	@itemArgs1 int,@itemArgs2 int,@itemArgs3 int,@itemArgs4 nvarchar(255),
	@itemArgs5 ntext,@itemPrompt nvarchar(255),@itemAddTime datetime,@id int output)
as
	declare @order int
	declare @sql varchar(1000)
	begin
	set @id=0

	BEGIN TRANSACTION TranStart
	select @order=max(itemOrder)+1 from PPFOR_TZB_CustomItem where formId=@formId
	if(@order is not null)
		set @itemOrder=@order
	insert into PPFOR_TZB_CustomItem(formId,itemName,itemFieldName,itemOrder,itemState,itemNotNull,
		itemType,itemDefault,itemArgs1,itemArgs2,itemArgs3,itemArgs4,
		itemArgs5,itemPrompt,itemAddTime) values (@formId,@itemName,@itemFieldName,@itemOrder,
		@itemState,@itemNotNull,@itemType,@itemDefault,@itemArgs1,
		@itemArgs2,@itemArgs3,@itemArgs4,@itemArgs5,@itemPrompt,@itemAddTime)
	if(@@rowcount>0)
		begin 	
		set @id=@@identity
		set @sql='alter table [PPFOR_TZB_' + @formTableName + '] add [' + @itemFieldName + '] '
			 + @itemFieldType + ' null'
		exec(@sql)
		exec PPFOR_TZB_CustomForm_SetProcedure_Add @formTableName
		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_CustomItem_Update' and type='P')
	drop procedure PPFOR_TZB_CustomItem_Update
PPFOR_GO;
create procedure PPFOR_TZB_CustomItem_Update(@id int,@itemName nvarchar(20),@itemOrder int,
	@itemState bit,@itemNotNull bit,@itemDefault nvarchar(255),
	@itemArgs1 int,@itemArgs2 int,@itemArgs3 int,@itemArgs4 nvarchar(255),
	@itemArgs5 ntext,@itemPrompt nvarchar(255))
as
	update PPFOR_TZB_CustomItem set itemName=@itemName,itemOrder=@itemOrder,itemState=@itemState,
		itemNotNull=@itemNotNull,itemDefault=@itemDefault,
		itemArgs1=@itemArgs1,itemArgs2=@itemArgs2,itemArgs3=@itemArgs3,itemArgs4=@itemArgs4,
		itemArgs5=@itemArgs5,itemPrompt=@itemPrompt where [id]=@id
PPFOR_GO;

--更新自定义表单项排序
if exists(select name from sysobjects where name='PPFOR_TZB_CustomItem_UpdateOrder' and type='P')
	drop procedure PPFOR_TZB_CustomItem_UpdateOrder
PPFOR_GO;
create procedure PPFOR_TZB_CustomItem_UpdateOrder(@id int,@itemOrder int)
as
	update PPFOR_TZB_CustomItem set itemOrder=@itemOrder where [id]=@id
PPFOR_GO;

--删除自定义表单项
if exists(select name from sysobjects where name='PPFOR_TZB_CustomItem_Delete' and type='P')
	drop procedure PPFOR_TZB_CustomItem_Delete
PPFOR_GO;
create procedure PPFOR_TZB_CustomItem_Delete(@id int)
as
	declare @sql varchar(1000)
	declare @itemFieldName varchar(32)
	declare @formId int
	declare @formTableName varchar(32)
	begin
	BEGIN TRANSACTION TranStart
		select @itemFieldName=itemFieldName,@formId=formId from PPFOR_TZB_CustomItem where [id]=@id
		if(@itemFieldName is not null) and (@formId is not null)
		begin
			select @formTableName=formTableName from PPFOR_TZB_CustomForm where [id]=@formId
			if(@formTableName is not null)
			begin
				set @sql='alter table [PPFOR_TZB_' + @formTableName + '] drop column [' + @itemFieldName + ']'
				exec(@sql)
				exec PPFOR_TZB_CustomForm_SetProcedure_Add @formTableName
			end
		end
		delete from PPFOR_TZB_CustomItem 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_CustomItem_GetModel' and type='P')
	drop procedure PPFOR_TZB_CustomItem_GetModel
PPFOR_GO;
create procedure PPFOR_TZB_CustomItem_GetModel(@id int)
as
	select formId,itemName,itemFieldName,itemOrder,itemState,itemNotNull,itemType,
		itemDefault,itemArgs1,itemArgs2,itemArgs3,itemArgs4,
		itemArgs5,itemPrompt,itemAddTime from PPFOR_TZB_CustomItem where [id]=@id
PPFOR_GO;

--根据表单ID和状态限定获取所有表单项
if exists(select name from sysobjects where name='PPFOR_TZB_CustomItem_GetModelList' and type='P')
	drop procedure PPFOR_TZB_CustomItem_GetModelList
PPFOR_GO;
create procedure PPFOR_TZB_CustomItem_GetModelList(@formId int,@isCheckState bit)
as
	if @isCheckState=1
		select [id],itemName,itemFieldName,itemOrder,itemState,itemNotNull,itemType,
			itemDefault,itemArgs1,itemArgs2,itemArgs3,itemArgs4,itemArgs5,itemPrompt,
			itemAddTime from PPFOR_TZB_CustomItem where formId=@formId and itemState=1
			 order by itemOrder asc,id asc
	else
		select [id],itemName,itemFieldName,itemOrder,itemState,itemNotNull,itemType,
			itemDefault,itemArgs1,itemArgs2,itemArgs3,itemArgs4,itemArgs5,itemPrompt,
			itemAddTime from PPFOR_TZB_CustomItem where formId=@formId order by itemOrder asc,id asc
PPFOR_GO;

--分页返回所有自定义表单项资料，只包含ID，表单项名称，字段名，是否必填，字段类型
if exists(select name from sysobjects where name='PPFOR_TZB_CustomItem_GetModelListByPage' and type='P')
	drop procedure PPFOR_TZB_CustomItem_GetModelListByPage
PPFOR_GO;
create procedure PPFOR_TZB_CustomItem_GetModelListByPage(@formId int,@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_CustomItem where formId=@formId
		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],itemName,itemFieldName,itemOrder,itemNotNull,itemType
			 from PPFOR_TZB_CustomItem where formId=' + str(@formId) + ' '
	
		if @pageIndex <= 1 or @pc <= 1
			set @sqlStr='select top ' + str(@pageSize) + @sqlStr2 + ' order by itemOrder asc,[id] asc'
		else if @pageIndex = @pc
			set @sqlStr ='select * from (select top ' + str( @rc - @pageSize * (@pageIndex-1) ) 
				+ @sqlStr2 + ' order by itemOrder desc,[id] desc) Temptable order by itemOrder asc,[id] asc'
		else  
			set @sqlStr ='select top ' + str(@pageSize) + ' * from (select top '
				+ str( @rc - @pageSize * (@pageIndex-1) ) + @sqlStr2 + ' order by 
				itemOrder desc,[id] desc) Temptable order by itemOrder asc,[id] asc'
		
		exec (@sqlStr)
		select @rc as recordCount,@pc as pageCount
	end
PPFOR_GO;

--返回是否允许添加自定义表单项，是为1，否为0
if exists(select name from sysobjects where name='PPFOR_TZB_CustomItem_IsEnableAdd' and type='P')
	drop procedure PPFOR_TZB_CustomItem_IsEnableAdd
PPFOR_GO;
create procedure PPFOR_TZB_CustomItem_IsEnableAdd(@formId int,@itemName nvarchar(20),
	@itemFieldName varchar(32),@retValue bit output)
as
	declare @count int
	begin
		if exists(select [id] from PPFOR_TZB_CustomForm where [id]=@formId)
		begin
			select @count=count([id]) from PPFOR_TZB_CustomItem where formId=@formId
			if @count is null or (@count<100)
			begin			
				if exists(select [id] from PPFOR_TZB_CustomItem where formId=@formId 
					and (itemName=@itemName or itemFieldName=@itemFieldName))
		 			set @retValue=0
				else
					set @retValue=1
			end
			else
				set @retValue=0
		end
		else
			set @retValue=0
	end
PPFOR_GO;

