select '' as card_id from sysobjects with(nolock) where 1=2
##
if (#SV = 'kmjxc_pro' or #SV = 'kmjxc_pro')
	exec('insert into t_rm_vip_info (card_id,card_status,card_type,oper_date,oper_id,other1,other2,other3,vip_add,vip_email,vip_end_date,vip_name,
									 vip_sex,vip_start_date,vip_tax,vip_tel,vip_zip)
	      select card_id,card_status,card_type,oper_date,oper_id,other1,other2,other3,vip_add,vip_email,vip_end_date,vip_name,vip_sex,vip_start_date,
				  vip_tax,vip_tel,vip_zip
		  from #SDB..pos_t_vip_info ')
else if #SV = 'bw9kbzpro'
	exec('insert into t_rm_vip_info(card_id,card_flowno,card_type,vip_name,vip_sex,vip_add, vip_zip,mobile,
									vip_tel, vip_email, vip_tax, card_status, oper_id,oper_date, vip_start_date, vip_end_date,
									use_num, birthday, social_id, other1, acc_num, other3,residual_amt,other2) 
			 SELECT card_id, card_id, card_type, vip_name, vip_sex, vip_add, vip_zip,vip_mobile,
					vip_tel, vip_email, vip_tax, card_status, oper_id, oper_date, getdate(),dateadd(yy,2,getdate()),
					999999,birthday, vip_sfz,other1,(case when vip_integral>0 then vip_integral else 0 end),other3,
					(case when spare_cash>0 then spare_cash else 0 end),vip_pass 
			FROM  #SDB..pos_t_vip_info	')
else if #SV = 'tiger'
	exec('insert into t_rm_vip_info(card_id,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, other1, acc_num, other3)
           SELECT card_id, 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,  
			      999999,vip_birthday, vip_idno,other1, cast(other2 as decimal(10,2)), other3
            FROM #SDB..pos_vip_info')
else
	exec('insert into t_rm_vip_info (card_id,cardid_source,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,photo_file,nation,height,weight,blood_type,favor,acc_num,dec_num,memo,num1,num2,num3,other1,other2,other3,modify_date,com_flag)
		  select card_id, cardid_source, 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, photo_file, nation, height, weight, blood_type, favor, acc_num, dec_num, memo, num1, num2, num3, other1, other2, other3, modify_date, com_flag
		  from #sdb..t_rm_vip_info')

GO
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' and not (#SV = 'kmjxc_pro' or #SV = 'kmjxc_std' or #SV = 'bw9kbzpro' or #SV = 'tiger'))
	exec('update t_rm_vip_info set branch_no = tab_a.branch_no
		 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
truncate table t_rm_vip_good_flow
go

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')
go

truncate table t_rm_saving_plus_record
go

create table #tab_vip(id int identity(1,1),card_id varchar(50),residual_amt decimal(18,4))
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),
		@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_pos_sav_amttype = '0'
	begin
		insert into #tab_vip 
		select card_id,dbo.f_str_decrypt(residual_amt) 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)
		while @ll_count > 0
			begin
				select @ls_card_id = card_id,@ldec_residual_amt = residual_amt from #tab_vip where id = @ll_count
				set @ls_residual_amt = cast(@ldec_residual_amt as varchar(30))
				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())')
				
				set @ll_count = @ll_count - 1
			end
	end
	
drop table #tab_vip
go

