if exists(select 1 from sysobjects where name='pr_vip_oct_accinfo')
  drop procedure pr_vip_oct_accinfo
go

create procedure pr_vip_oct_accinfo
(
	@as_type varchar(1) = '0',
	@as_card_id varchar(20) = ''
)
as
begin
	declare @rtn_msg varchar(255),
		@oct_phone_no varchar(2)    -----add by zhangqing 20160920
	set @rtn_msg		   = ''
	if not exists(select top 1 1 from t_rm_vip_info with(nolock) where card_id=@as_card_id)
	begin
		----modify by zhangqing 20160913
					select @oct_phone_no = sys_var_value  from t_sys_system  where sys_var_id = 'oct_phone_no'
					If @oct_phone_no = '1' 
						begin
							if not exists (select top 1 1 from t_rm_vip_info with(nolock) where mobile like '%'+@as_card_id+'%')
								begin
									set @rtn_msg = '会员[' + @as_card_id + ']不存在！'
									goto myerror
								end
							else
								begin
									select @as_card_id = max(card_id) from t_rm_vip_info with(nolock) where  mobile like '%'+@as_card_id+'%'
								end
						end	
					else
						begin
							set @rtn_msg = '会员[' + @as_card_id + ']不存在！'
							goto myerror
						end
				--------end-------------------	
	end
	if @as_type ='1' 
	begin
		if not exists(select top 1 1 from t_rm_vip_type a with(nolock),t_rm_vip_info b with(nolock) where b.card_id=@as_card_id and a.type_id = b.card_type and a.acc_flag = '0')
		begin
			set @rtn_msg = '会员[' + @as_card_id + ']不是积分卡！'
			goto myerror
		end
	end
	
	select  top 1 card_id,vip_name,
			(select type_name from t_rm_vip_type where t_rm_vip_type.type_id = t_rm_vip_info.card_type) as type_name,
			(case card_status when '0' then '正常' 
								   when '1' then '无效'
								   when '2' then '挂失'
								   when '3' then '作废'
								   when '4' then '黑名单'
								   when '5' then '过期'
								   else '正常' end) as card_status,
			birthday,isnull(acc_num,0) acc_num,isnull(dec_num,0) dec_num,card_type,card_flowno,vip_sex,vip_tel,vip_start_date,vip_end_date,use_num,social_id,mobile,bth_flag
	from  t_rm_vip_info
	where card_id=@as_card_id
	return 	
	myerror:
		select  top 1  'error' as card_id,@rtn_msg as vip_name, '' as type_name,
				(case '1' when '0' then '正常' 
								   when '1' then '无效'
								   when '2' then '挂失'
								   when '3' then '作废'
								   when '4' then '黑名单'
								   when '5' then '过期'
								   else '正常' end) as card_status,
			GETDATE() birthday,0 acc_num,0 dec_num,'' card_type,'error' card_flowno,'女' vip_sex,'' vip_tel,GETDATE() vip_start_date,GETDATE() vip_end_date,0 use_num,'' social_id,'' mobile,'公历' bth_flag
		return
	
end
go
