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



CREATE PROCEDURE pr_auto_pos_vip_manage
(
	 @as_type varchar(1) ,      --业务类型 
	 @as_vipNo    varchar(30),	 --会员卡号
	 @oper_id     varchar(10),   --操作人员
	 @branch_no   varchar(6),   --机构编码
	 @as_flow_no varchar(16), --小票号
	 @adec_consume_num   numeric(16,4),  --本单积分值
	 @adec_consume_amt  numeric(16,4),   --本单消费金额
	 @adec_sav_amt numeric(16,4),   --  储值消费金额
	 @as_card_pass varchar(20),     --  卡密码
	 @memo        varchar(100)      --  备注
)
WITH ENCRYPTION
AS
Begin
    declare  @acc_num numeric(16,4)   --累计积分
    declare  @acc_flag varchar(10)    --该类别是否有积分功能
    declare  @acc_num_temp numeric(16,4)
    declare  @li_count int
    declare  @ls_card_status varchar(1) --会员卡状态
    declare  @ls_residual_amt varchar(20)
    declare  @ldec_limit_num int
    declare  @ldec_residual_amt numeric(16,4)
    declare  @ldec_remain_amt numeric(16,4)
    declare  @ls_remain_amt  varchar(20)
    declare  @ldec_sav_min_amt numeric(16,4)
    
     if @as_vipNo is null
        begin
          select 'N' AS [result], '会员卡号为空，请检查！' AS [message]
          return
        end
     if not exists(select top 1 1 from t_rm_vip_info with(nolock) where card_id=@as_vipNo)
		begin
		  select 'N' AS [result], '会员[' + @as_vipNo + ']不存在，请检查！' AS [message]
          return
		end   
        
        
   if @as_type = '1' or @as_type = '4'
    begin
		if @adec_sav_amt = 0
			begin
				 select 'N' AS [result], '储值消费时，储值金额不能为0！' AS [message]
                 return
			end
		if (select top 1 sys_var_value from t_sys_system where sys_var_id='pos_vip_sav')<>'1' 
			begin
				select 'N' AS [result], '后台系统未启用储值消费功能！'  AS [message]
				return
			end

		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_vipNo and a.type_id = b.card_type and a.saving_flag = '1')
			begin
				select 'N' AS [result], '会员[' + @as_vipNo + ']非储值卡，储值消费失败！'AS [message]	
				return
			end 
				
		select @ls_card_status = card_status,@ls_residual_amt = residual_amt,
			   @ldec_limit_num = use_num
		from t_rm_vip_info
		where card_id = @as_vipNo and
			  convert(char(10),vip_start_date,121)<=convert(char(10),getdate(),121) and 
			  convert(char(10),vip_end_date,121)>=convert(char(10),getdate(),121)

		if @ls_residual_amt is null 
			set @ls_residual_amt = ''
		else
			set @ls_residual_amt = ltrim(rtrim(@ls_residual_amt))	
		
		if (@ls_card_status is null) or (@ls_card_status<>'0')
			begin
				select 'N' AS [result], '会员['+@as_vipNo+']卡状态不正常或卡已过期!' AS [message]	
				return
			end
		if @ldec_limit_num<=0
			begin
				select 'N' AS [result], '会员['+@as_vipNo+']已达到限制的消费次数,不能再消费!' AS [message]	
				return
			end
				
		if @ls_residual_amt<>''
		  set @ldec_residual_amt = dbo.f_get_descry(@ls_residual_amt)
		else
		  set @ldec_residual_amt = 0
		
		if @as_type = '1'
			set @adec_sav_amt = @adec_sav_amt *1
		else
			set @adec_sav_amt = @adec_sav_amt *-1
			
		set @ldec_remain_amt = @ldec_residual_amt - @adec_sav_amt
		
		set	@ls_remain_amt = dbo.f_set_descry(@ldec_remain_amt)
		
		
		if @as_type = '1'
		begin
			set @ldec_sav_min_amt = 0
			select @ldec_sav_min_amt = convert(numeric(16,2),sys_var_value) from t_sys_system where sys_var_id='pos_sav_min'
					
			if @ldec_sav_min_amt is null 
				set @ldec_sav_min_amt = 0
			if (@ldec_remain_amt - @ldec_sav_min_amt) < 0     -----modify by yuhy 20141028 除去留底金额意外，一卡通可以消费完全(@ldec_remain_amt - @ldec_sav_min_amt) <= 0 
				begin
					select 'N' AS [result], '会员['+@as_vipNo+']余额不足，当前余额：'+convert(varchar(30),@ldec_residual_amt)+',留底金额：'+convert(varchar(30),@ldec_sav_min_amt) AS [message]	
					return
				end
	    end 
		    
	    insert into t_rm_card_paylist(flow_no, card_type, card_id, amount, pay_time,branch_no,other1,residual_amt,other2)
		values(@as_flow_no,'2',@as_vipNo, @adec_sav_amt, getdate(),left(@branch_no,4),@memo,@ldec_remain_amt,'自助收银系统')
		if @@error<>0
		begin
			 select 'N' AS [result], '会员['+@as_vipNo+']储值消费记录写入失败!,请检查' AS [message]	
			 return
		end
			
		select @li_count =COUNT(*) from t_rm_vip_consume where card_id =@as_vipNo and branch_no =left(@branch_no,4)
	   
		if @li_count = 1
			begin
				if @adec_sav_amt>0 --消费
					UPDATE t_rm_vip_consume SET cons_count = cons_count + 1, cons_amount = cons_amount + @adec_sav_amt,com_flag = 0
					WHERE card_id = @as_vipNo and branch_no = left(@branch_no,4)
				else             --冲正
					UPDATE t_rm_vip_consume SET cons_count = cons_count - 1, cons_amount = cons_amount + @adec_sav_amt,com_flag = 0
					WHERE card_id = @as_vipNo and branch_no = left(@branch_no,4)

					if @@error != 0
						begin
							select 'N' AS [result], '会员['+@as_vipNo+']储值汇总写入失败,请检查!' AS [message]
							return 
						end
			end
		 else
		     begin
		        insert t_rm_vip_consume (branch_no, card_id, cons_count, cons_amount)
				values (left(@branch_no,4), @as_vipNo, 1, @adec_sav_amt)
				if @@error != 0
			       begin
			        select 'N' AS [result], '会员['+@as_vipNo+']储值汇总汇总写入失败,请检查!' AS [message]
			        return 
			      end
		      end
		     
		if @adec_sav_amt >= 0
			update t_rm_vip_info 
			set	residual_amt = @ls_remain_amt,
			consum_amt = isnull(consum_amt,0) + @adec_sav_amt,
			consum_count = isnull(consum_count,0) + 1,
			use_num = isnull(use_num,0) - 1
			where card_id = @as_vipNo
		else
			update t_rm_vip_info 
			set	residual_amt = @ls_remain_amt,
			consum_amt = isnull(consum_amt,0) + @adec_sav_amt,
			consum_count = isnull(consum_count,0) - 1,
			use_num = isnull(use_num,0) + 1
			where card_id = @as_vipNo	
				
		if @@error != 0 
		begin
			 select 'N' AS [result], '会员['+@as_vipNo+']修改余额写入失败,请检查!' AS [message]
			 return
		end
		
		select 'Y' AS [result], '会员['+@as_vipNo+']会员卡储值成功!' AS [message] 
				
	end 	
end
go