-- ==============================
-- 长登科技 www.ppfor.com
-- 唐镇波   2010-04-19
-- ------------------------------
-- 会员自定义注册项表
-- ==============================

--创建会员自定义注册项表
if not exists(select name from sysobjects where name='PPFOR_TZB_MemberField' and type='U')
begin
	create table PPFOR_TZB_MemberField
	(
		[id] int identity(1,1) not null primary key,							--自动编号		
		fieldName nvarchar(20) COLLATE Chinese_PRC_CS_AS not null unique,		--注册项名称
		fieldItemName varchar(32) COLLATE Chinese_PRC_CI_AS not null unique,	--字段名
		fieldOrder int not null default(0),							--排序序号
		fieldState bit not null default(1),							--是否启用
		fieldNotNull bit not null default(1),						--是否必填
		fieldType tinyint not null,									--注册项类型
		fieldDefault nvarchar(255),									--默认值
		fieldArgs1 int not null default(0),							--整型参数1	
		fieldArgs2 int not null default(0),							--整型参数2
		fieldArgs3 int not null default(0),							--整型参数3
		fieldArgs4 nvarchar(255),									--参数4
		fieldArgs5 ntext,											--参数5	
		fieldPrompt nvarchar(255),									--附加提示
		fieldAddTime datetime not null,								--添加时间
		fieldIsSystem bit not null default(0)
	)
end
PPFOR_GO;

--添加自定义注册项
if exists(select name from sysobjects where name='PPFOR_TZB_MemberField_Add' and type='P')
	drop procedure PPFOR_TZB_MemberField_Add
PPFOR_GO;
create procedure PPFOR_TZB_MemberField_Add(@fieldName nvarchar(20),
	@fieldItemName varchar(32),@fieldOrder int,@fieldState bit,@fieldNotNull bit,
	@fieldType tinyint,@fieldItemType varchar(20),@fieldDefault nvarchar(255),
	@fieldArgs1 int,@fieldArgs2 int,@fieldArgs3 int,@fieldArgs4 nvarchar(255),
	@fieldArgs5 ntext,@fieldPrompt nvarchar(255),@fieldAddTime datetime,@id int output)
as
	declare @order int
	declare @sql varchar(1000)
	begin
	set @id=0

	BEGIN TRANSACTION TranStart
	select @order=max(fieldOrder)+1 from PPFOR_TZB_MemberField
	if(@order is not null)
		set @fieldOrder=@order
	insert into PPFOR_TZB_MemberField(fieldName,fieldItemName,fieldOrder,fieldState,fieldNotNull,
		fieldType,fieldDefault,fieldArgs1,fieldArgs2,fieldArgs3,fieldArgs4,
		fieldArgs5,fieldPrompt,fieldAddTime,fieldIsSystem) values (@fieldName,@fieldItemName,
		@fieldOrder,@fieldState,@fieldNotNull,@fieldType,@fieldDefault,@fieldArgs1,
		@fieldArgs2,@fieldArgs3,@fieldArgs4,@fieldArgs5,@fieldPrompt,@fieldAddTime,0)
	if(@@rowcount>0)
		begin 	
		set @id=@@identity
		set @sql='alter table [PPFOR_TZB_CustomMember] add [' + @fieldItemName + '] '
			 + @fieldItemType + ' null'
		exec(@sql)
		exec PPFOR_TZB_CustomMember_SetProcedure
		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_MemberField_Update' and type='P')
	drop procedure PPFOR_TZB_MemberField_Update
PPFOR_GO;
create procedure PPFOR_TZB_MemberField_Update(@id int,@fieldName nvarchar(20),@fieldOrder int,
	@fieldState bit,@fieldNotNull bit,@fieldDefault nvarchar(255),
	@fieldArgs1 int,@fieldArgs2 int,@fieldArgs3 int,@fieldArgs4 nvarchar(255),
	@fieldArgs5 ntext,@fieldPrompt nvarchar(255))
as
	update PPFOR_TZB_MemberField set fieldName=@fieldName,fieldOrder=@fieldOrder,
		fieldState=@fieldState,	fieldNotNull=@fieldNotNull,fieldDefault=@fieldDefault,
		fieldArgs1=@fieldArgs1,fieldArgs2=@fieldArgs2,fieldArgs3=@fieldArgs3,fieldArgs4=@fieldArgs4,
		fieldArgs5=@fieldArgs5,fieldPrompt=@fieldPrompt where [id]=@id
PPFOR_GO;

--更新自定义注册项排序
if exists(select name from sysobjects where name='PPFOR_TZB_MemberField_UpdateOrder' and type='P')
	drop procedure PPFOR_TZB_MemberField_UpdateOrder
PPFOR_GO;
create procedure PPFOR_TZB_MemberField_UpdateOrder(@id int,@fieldOrder int)
as
	update PPFOR_TZB_MemberField set fieldOrder=@fieldOrder where [id]=@id
PPFOR_GO;

--删除自定义注册项
if exists(select name from sysobjects where name='PPFOR_TZB_MemberField_Delete' and type='P')
	drop procedure PPFOR_TZB_MemberField_Delete
PPFOR_GO;
create procedure PPFOR_TZB_MemberField_Delete(@id int)
as
	declare @sql varchar(1000)
	declare @fieldItemName varchar(32)
	begin
	BEGIN TRANSACTION TranStart
		select @fieldItemName=fieldItemName from PPFOR_TZB_MemberField where [id]=@id 
			and fieldIsSystem=0
		if @fieldItemName is not null
		begin
			set @sql='alter table [PPFOR_TZB_CustomMember] drop column [' + @fieldItemName + ']'
			exec(@sql)
			exec PPFOR_TZB_CustomMember_SetProcedure
		end
		delete from PPFOR_TZB_MemberField where [id]=@id and fieldIsSystem=0
	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_MemberField_GetModel' and type='P')
	drop procedure PPFOR_TZB_MemberField_GetModel
PPFOR_GO;
create procedure PPFOR_TZB_MemberField_GetModel(@id int)
as
	select [id],fieldName,fieldItemName,fieldOrder,fieldState,fieldNotNull,fieldType,
		fieldDefault,fieldArgs1,fieldArgs2,fieldArgs3,fieldArgs4,
		fieldArgs5,fieldPrompt,fieldAddTime,fieldIsSystem from PPFOR_TZB_MemberField where [id]=@id
PPFOR_GO;

--根据注册ID和状态限定获取所有注册项
if exists(select name from sysobjects where name='PPFOR_TZB_MemberField_GetModelList' and type='P')
	drop procedure PPFOR_TZB_MemberField_GetModelList
PPFOR_GO;
create procedure PPFOR_TZB_MemberField_GetModelList(@isCheckState bit)
as
	if @isCheckState=1
		select [id],fieldName,fieldItemName,fieldOrder,fieldState,fieldNotNull,fieldType,
			fieldDefault,fieldArgs1,fieldArgs2,fieldArgs3,fieldArgs4,fieldArgs5,fieldPrompt,
			fieldAddTime,fieldIsSystem from PPFOR_TZB_MemberField where fieldState=1
			 order by fieldOrder asc,[id] asc
	else
		select [id],fieldName,fieldItemName,fieldOrder,fieldState,fieldNotNull,fieldType,
			fieldDefault,fieldArgs1,fieldArgs2,fieldArgs3,fieldArgs4,fieldArgs5,fieldPrompt,
			fieldAddTime,fieldIsSystem from PPFOR_TZB_MemberField order by fieldOrder asc,[id] asc
PPFOR_GO;

--返回是否允许添加自定义注册项，是为1，否为0
if exists(select name from sysobjects where name='PPFOR_TZB_MemberField_IsEnableAdd' and type='P')
	drop procedure PPFOR_TZB_MemberField_IsEnableAdd
PPFOR_GO;
create procedure PPFOR_TZB_MemberField_IsEnableAdd(@fieldName nvarchar(20),
	@fieldItemName varchar(32),@retValue bit output)
as
	begin
		if exists(select [id] from PPFOR_TZB_MemberField where [fieldName]=@fieldName or [fieldItemName]=@fieldItemName)
 			set @retValue=0
		else
			set @retValue=1
	end
PPFOR_GO;

--返回是否允许更新自定义注册项，是为1，否为0
if exists(select name from sysobjects where name='PPFOR_TZB_MemberField_IsEnableUpdate' and type='P')
	drop procedure PPFOR_TZB_MemberField_IsEnableUpdate
PPFOR_GO;
create procedure PPFOR_TZB_MemberField_IsEnableUpdate(@id int ,@fieldName nvarchar(20),@retValue bit output)
as
	begin
		if exists(select [id] from PPFOR_TZB_MemberField where [fieldName]=@fieldName and [id]<>@id)
 			set @retValue=0
		else
			set @retValue=1
	end
PPFOR_GO;



