select '' as  card_id
from sysobjects with(nolock) where 1=2
##
if (#TV = 'isszmv6' or #TV = 'isszmv7' or #TV = 'isszmv8' or #TV = 'isszmv9')
	exec('insert into t_rm_vip_info (card_id,card_no,card_type, vip_name, vip_sex, vip_add, vip_zip, vip_tel, vip_email, vip_tax, card_status, oper_id, oper_date, vip_start_date, vip_end_date, use_num, birthday, social_id, residual_amt, consum_amt, consum_count, company, duty, degree, mobile, homeplace, reg_place, live_place, marriage, salary_level, nation, height, weight, blood_type, favor, acc_num, dec_num, now_acc_num,memo,modify_date, com_flag)
		  select card_id,card_id as card_no,card_type, vip_name, vip_sex, vip_add, vip_zip, vip_tel, vip_email, vip_tax, card_status, oper_id, oper_date, vip_start_date, vip_end_date, use_num, birthday, social_id, residual_amt, consum_amt, consum_count, company, duty, degree, mobile, homeplace, reg_place, live_place, marriage, salary_level, nation, height, weight, blood_type, favor, acc_num, dec_num, (case when acc_num > dec_num then acc_num - dec_num else 0 end),memo,modify_date, com_flag from #SDB..t_rm_vip_info')
else
	exec('insert into t_rm_vip_info (card_id,card_flowno,card_type, vip_name, vip_sex, vip_add, vip_zip, vip_tel, vip_email, vip_tax, card_status, oper_id, oper_date, vip_start_date, vip_end_date, use_num, birthday, social_id, residual_amt, consum_amt, consum_count, company, duty, degree, mobile, homeplace, reg_place, live_place, marriage, salary_level, nation, height, weight, blood_type, favor, acc_num, dec_num, memo,modify_date, com_flag)
		  select card_id,card_id as card_flowno,card_type, vip_name, vip_sex, vip_add, vip_zip, vip_tel, vip_email, vip_tax, card_status, oper_id, oper_date, vip_start_date, vip_end_date, use_num, birthday, social_id, residual_amt, consum_amt, consum_count, company, duty, degree, mobile, homeplace, reg_place, live_place, marriage, salary_level, nation, height, weight, blood_type, favor, acc_num, dec_num, memo,modify_date, com_flag from #SDB..t_rm_vip_info')	

if (#SV = 'hbyjtv8' or #SV = 'hbposv7' or #SV = 'hbposv8' or #SV = 'hbposev8' or #SV = 'hbposev9') and
   (#TV = 'hbyjtv8' or #TV = 'hbposv7' or #TV = 'hbposv8' or #TV = 'hbposev8' or #TV = 'hbposev9')
   exec('update t_rm_vip_info set bth_flag = tab_a.bth_flag
		 from #SDB..t_rm_vip_info as tab_a
		 where t_rm_vip_info.card_id = tab_a.card_id ')
GO
if (#TV = 'hbposv8')
	exec('update t_rm_vip_info set branch_no = (case when len(ltrimn(rtrim(tab_a.branch_no))) = 3 then right(ltrimn(rtrim(tab_a.branch_no)),2) else tab_a.branch_no end)
		 from (select top 1 branch_no from #tdb..t_bd_branch_info where property = ''1'') as tab_a 
		 where isnull(t_rm_vip_info.branch_no,'''') = '''' ')
GO
if not (#SV = 'isszmv6' or #SV = 'isszmv7' or #SV = 'isszmv8' or #SV = 'isszmv9' or #TV = 'isszmv6' or #TV = 'isszmv7' or #TV = 'isszmv8' or #TV = 'isszmv9')
	exec('update t_rm_vip_info set cardid_source = tab_a.cardid_source,
									photo_file = tab_a.photo_file,
									num1 = tab_a.num1,
									num2 = tab_a.num2,
									num3 = tab_a.num3,
									other1 = tab_a.other1,
									other2 = tab_a.other2,
									other3 = tab_a.other3
			from #SDB..t_rm_vip_info as tab_a
			where t_rm_vip_info.card_id = tab_a.card_id')
go
if (not (#SV = 'isszmv6' or #SV = 'isszmv7' or #SV = 'isszmv8' or #SV = 'isszmv9')) and (#TV = 'isszmv6' or #TV = 'isszmv7' or #TV = 'isszmv8' or #TV = 'isszmv9')
	exec('update t_rm_vip_info set source_card_id = tab_a.cardid_source,
									pass = tab_a.other2
			from #SDB..t_rm_vip_info as tab_a
			where t_rm_vip_info.card_id = tab_a.card_id')
go
if (#SV = 'isszmv6' or #SV = 'isszmv7' or #SV = 'isszmv8' or #SV = 'isszmv9') and (not (#TV = 'isszmv6' or #TV = 'isszmv7' or #TV = 'isszmv8' or #TV = 'isszmv9'))
	exec('update t_rm_vip_info set cardid_source = tab_a.source_card_id,
								   other2 = tab_a.pass
			from #SDB..t_rm_vip_info as tab_a
			where t_rm_vip_info.card_id = tab_a.card_id')
go
if not (#TV = 'isszmv6' or #TV = 'isszmv7' or #TV = 'isszmv8' or #TV = 'isszmv9')
	exec('truncate table t_rm_vip_good_flow')
else
	exec('truncate table t_rm_vip_acclist')
go
if not (#TV = 'isszmv6' or #TV = 'isszmv7' or #TV = 'isszmv8' or #TV = 'isszmv9')
	exec('insert into t_rm_vip_good_flow(vgf_branch_no,vgf_flag,vgf_vip_no,vgf_item_num,vgf_vip_num,vgf_oper,vgf_date)
			select ''00'',''3'',card_id,''0'',(acc_num-dec_num),''1001'',getdate() 
						  from t_rm_vip_info 
						  where (acc_num-dec_num)<>0 and
									  exists(select top 1 1 from t_bd_branch_info where property = ''1'' and branch_no = ''00'' or branch_no = ''000'')')
else
	begin
		declare @ls_oper_des varchar(50),@ls_ver varchar(10)
		if #TV = 'isszmv6' 
			set @ls_ver = 'V6'
		else if #TV = 'isszmv7'
			set @ls_ver = 'V7'
		else if #TV = 'isszmv8'
			set @ls_ver = 'V8'
		else if #TV = 'isszmv9'
			set @ls_ver = 'V9'
		else
			set @ls_ver = ''

		if #SV = 'isszmv6'
			set @ls_oper_des = 'V6转' + @ls_ver + '剩余积分初始化'
		else if #SV = 'isszmv7'
			set @ls_oper_des = 'V6转' + @ls_ver + '剩余积分初始化'
		else if #SV = 'isszmv8'
			set @ls_oper_des = 'V6转' + @ls_ver + '剩余积分初始化'
		else if #SV = 'isszmv9'
			set @ls_oper_des = 'V6转' + @ls_ver + '剩余积分初始化'
		else
			set @ls_oper_des = '剩余积分初始化'

		if @ls_ver = ''
			set @ls_oper_des = '剩余积分初始化'
		
		if (#SV = 'isszmv6' or #SV = 'isszmv7' or #SV = 'isszmv8' or #SV = 'isszmv9')
			exec('INSERT INTO t_rm_vip_acclist ( card_id, card_no, card_type, branch_no, oper_type, 
					oper_id, ope_date,flow_no, consum_amt, acc_num, new_cardno, oper_des, memo, com_flag )  
				  select card_id,card_no,card_type,branch_no,''2'',''1001'',getdate(),'''',0,now_acc_num,'''',
						 ''' + @ls_oper_des + ''','''',''1''
				  from t_rm_vip_info where now_acc_num > 0  ')
		else
			exec('INSERT INTO t_rm_vip_acclist ( card_id, card_no, card_type, branch_no, oper_type, 
					oper_id, ope_date,flow_no, consum_amt, acc_num, new_cardno, oper_des, memo, com_flag )  
				  select card_id,card_id,card_type,branch_no,''2'',''1001'',getdate(),'''',0,acc_num - dec_num,'''',
						 ''' + @ls_oper_des + ''','''',''1''
				  from t_rm_vip_info 
				  where (acc_num-dec_num)<>0 and
									  exists(select top 1 1 from t_bd_branch_info where property = ''1'' and branch_no = ''00'' or branch_no = ''000'')')
		
	end
go

if not (#TV = 'isszmv6' or #TV = 'isszmv7' or #TV = 'isszmv8' or #TV = 'isszmv9')
	exec('truncate table t_rm_saving_plus_record')
else
	exec('truncate table t_rm_vip_savelist')
go

create table #tab_vip(id int identity(1,1),card_id varchar(50),residual_amt decimal(18,4),card_type varchar(10))
declare @ls_branch_no varchar(10),
		@ls_pos_sav_amttype varchar(1),
		@ls_card_id varchar(50),
		@ldec_residual_amt decimal(18,4),
		@ls_residual_amt varchar(30),
		@ls_card_type varchar(10),
		@ls_oper_des varchar(50),
		@ls_ver varchar(50),
		@ll_count int,
		@ll_exist int

set @ls_branch_no = ''
select top 1 @ls_branch_no = branch_no from t_bd_branch_info where property = '1'
select @ls_branch_no = isnull(@ls_branch_no,'')

set @ls_pos_sav_amttype = '0'
select top 1 @ls_pos_sav_amttype = sys_var_value from t_sys_system where sys_var_id = 'pos_sav_amttype'
select @ls_pos_sav_amttype = isnull(@ls_pos_sav_amttype,'0')

if @ls_branch_no = '00' or @ls_branch_no = '000' or @ls_pos_sav_amttype = '0'
	begin
		insert into #tab_vip 
		select card_id,dbo.f_str_decrypt(residual_amt),card_type from t_rm_vip_info 
		where card_type in(select  type_id from t_rm_vip_type where saving_flag='1')
		
		set @ll_count = 0
		select @ll_count = count(*) from #tab_vip
		
		set @ll_exist = 0
		select top 1 @ll_exist = 1 from sysobjects as tab_a with(nolock),syscolumns as tab_b with(nolock)
		where tab_a.id = tab_b.id and 
			  tab_a.name = 't_rm_saving_plus_record' and
			  tab_a.xtype = 'U' and
			  tab_b.name = 'plus_real_count'		
		set @ll_exist = isnull(@ll_exist,0)

		if #TV = 'isszmv6' 
			set @ls_ver = 'V6'
		else if #TV = 'isszmv7'
			set @ls_ver = 'V7'
		else if #TV = 'isszmv8'
			set @ls_ver = 'V8'
		else if #TV = 'isszmv9'
			set @ls_ver = 'V9'
		else
			set @ls_ver = ''

		if #SV = 'isszmv6'
			set @ls_oper_des = 'V6转' + @ls_ver + '剩余金额充值'
		else if #SV = 'isszmv7'
			set @ls_oper_des = 'V6转' + @ls_ver + '剩余金额充值'
		else if #SV = 'isszmv8'
			set @ls_oper_des = 'V6转' + @ls_ver + '剩余金额充值'
		else if #SV = 'isszmv9'
			set @ls_oper_des = 'V6转' + @ls_ver + '剩余金额充值'
		else
			set @ls_oper_des = '剩余金额充值'

		if @ls_ver = ''
			set @ls_oper_des = '剩余金额充值'

		while @ll_count > 0
			begin
				select @ls_card_id = card_id,@ldec_residual_amt = residual_amt,@ls_card_type = card_type from #tab_vip where id = @ll_count
				set @ls_residual_amt = cast(@ldec_residual_amt as varchar(30))
				if not (#TV = 'isszmv6' or #TV = 'isszmv7' or #TV = 'isszmv8' or #TV = 'isszmv9')
					begin
						if @ll_exist = 1
							exec('insert into t_rm_saving_plus_record (branch_no,card_id,plus_count,oper_id,oper_date,plus_real_count)	
								  values(''' + @ls_branch_no + ''',''' + @ls_card_id + ''',' + @ls_residual_amt + ',''1001'',getdate(),' + @ls_residual_amt + ')')
						else
							exec('insert into t_rm_saving_plus_record (branch_no,card_id,plus_count,oper_id,oper_date)	
								  values(''' + @ls_branch_no + ''',''' + @ls_card_id + ''',' + @ls_residual_amt + ',''1001'',getdate())')
					end
				else
					begin
						 exec('insert into t_rm_vip_savelist  
								( card_id, card_no, card_type, branch_no, oper_type,   
								  oper_id, ope_date, flow_no, consum_amt,  acc_num,   
								  new_cardno, oper_des,memo, com_flag,	real_amt )
							values (''' + @ls_card_id + ''',''' + @ls_card_id + ''',''' + @ls_card_type + ''',''' + @ls_branch_no + ''',''4'',
								  ''1001'', getdate(), '''',''' + @ls_residual_amt + ''',''0'',
								  null,''' + @ls_oper_des + ''', '''',''0'',''' + @ls_residual_amt + '''')
						exec('insert into t_rm_payflow 
							( flow_id, flow_no, sale_amount, branch_no, pay_way, sell_way, 
							  card_no, card_id, vip_no, coin_no, coin_rate,  pay_amount,  
							  oper_date,oper_id, counter_no, sale_man, cashier_no, memo, voucher_no, shift_no, com_flag ) 
						values ( 1,'''','''+ @ls_residual_amt +''',''' + @ls_branch_no + ''',''RMB'',''A'','''+@ls_card_id+''',
						 ''' + @ls_card_id + ''',''' + @ls_card_id + ''',''RMB'',1,'''+@ls_residual_amt+''',getdate(),''1001'',
						  '''', ''1001'', ''111'' ,''' + @ls_oper_des + ''','''','''',''0''') 
					end
				
				set @ll_count = @ll_count - 1
			end
	end
	
drop table #tab_vip
go

