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

create procedure pr_rm_vip_addsav(
	@card_id varchar(20),		--卡id
	@item_clsno varchar(12),	--类别编号，为空就是普通充值，限制死每次只能充一个类别
	@add_amt		numeric(16,2), --充值金额
	@fact_amt		numeric(16,2), --实际付款金额
	@pay_way        varchar(12),	--付款方式
	@sale_man       varchar(10),	--推荐人
	@pos_id			varchar(10),	--pos编号
	@return varchar(100) output)		--返回值
as
--储值卡充值只有普通充值和类别充值
begin 
	declare @residual_amt	varchar(20),	--储值卡余额
			@saving_amt		numeric(16,2),  --储值卡余额数字型
			@ll_count		int ,
			@card_no	varchar(20),
			@card_type	varchar(20),
			@flow_no 	varchar(20),		--小票号
			@branch_no	varchar(6),			--分店编号
			@temp		varchar(20),
			@class_saving_amt		numeric(16,2)  --储值卡余额数字型
	--初始化
	select @ll_count=0
	select @class_saving_amt=0

	if @add_amt=0 
		begin
			select @return='充值金额不可以为0'
			return
		end

	select @ll_count=count(1) from t_rm_vip_info with(nolock) where card_id=@card_id
	if @ll_count=0
		begin
			select @return='储值卡不存在'
			return
		end

	if @sale_man=''
		select @sale_man='9999'

	--得到分店编号
	select @branch_no='000'

	--得到小票流水号
	--首先得到当前单号
	select @pos_id='RPA'
	select @flow_no=@pos_id+convert(varchar(8),getdate(),112) +'%'	
	
	select  @flow_no=max(left(flow_no,15)) from t_rm_payflow where branch_no=@branch_no and flow_no like  @flow_no
	select @flow_no=isnull(@flow_no,'')
	
	if ltrim(isnull(@flow_no,''))='' 
		begin
			select  @temp='0001'
			select @flow_no=@pos_id+convert(varchar(8),getdate(),112)+'0001'+right(convert(varchar(4),round(1000*rand(),0)+1000),3)
		end
	else
		begin
			select  @temp=convert(decimal(16,0),right(@flow_no,4))+1
			select @flow_no=@pos_id+convert(varchar(8),getdate(),112)+ @temp +right(convert(varchar(4),round(1000*rand(),0)+1000),3)
		end


	--取余额
	--查询会员储值信息,得到余额信息
	if @item_clsno='' 
		begin
			select @residual_amt=residual_amt,@card_no=card_no,@card_type=card_type from t_rm_vip_info with(nolock) where card_id=@card_id
			select @saving_amt=dbo.f_get_descry(@residual_amt)
			select @saving_amt=@saving_amt + @add_amt
			--加密
			select @residual_amt=convert(varchar(20),@saving_amt*10000)
			select @residual_amt=dbo.f_get_encrypt(@residual_amt)
			
			--修改储值卡余额
			update t_rm_vip_info  set residual_amt=@residual_amt,save_amt=isnull(save_amt,0) + @saving_amt  where card_id=@card_id

			--插入付款流水
			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,sale_man,memo,voucher_no)
			values(1,@flow_no,@add_amt,@branch_no,@pay_way,'A','',@card_ID,@card_id,'RMB',1,@fact_amt,getdate(),'1001',@sale_man,'充值卡充值','')
			--插入充值流水
			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 , payway ) 
			values ( @card_ID , @card_ID , @card_type , @branch_no , '4' ,'1001' , getdate() , @flow_no , @add_amt , 0 , null , '充值卡充值' , '' , '0' , @fact_amt , @pay_way)  		
			select @return='成功'	

		end
	else
		begin
			--类别充值
			select @ll_count=count(1) from t_rm_vip_sav_other where card_id=@card_ID and isnull(item_clsno,'')=@item_clsno
			if  @ll_count=1 
				begin
					--得到余额
					select @class_saving_amt= sav_amt from t_rm_vip_sav_other where card_id=@card_ID and isnull(item_clsno,'')=@item_clsno

					update t_rm_vip_sav_other
					set sav_amt=isnull(sav_amt,0)+@add_amt,
						num1=isnull(num1,0) +@fact_amt,
						end_date=DATEADD ( year , 50, getdate() ) 
					where card_id=@card_ID and isnull(item_clsno,'')=@item_clsno
				end
			else
				begin
					insert into t_rm_vip_sav_other(card_id,sav_type,item_clsno,sav_amt,com_amt,begin_date,end_date,oper_date,oper_id,branch_no,memo,num1,num2,num3,num4)
					values (@card_ID,'0',@item_clsno,@add_amt,0,getdate(),DATEADD ( year , 50, getdate() ),getdate(),'1001' ,@branch_no  ,'平板充值',@fact_amt,0,0,0 )
				end
			--充值流水
			insert into t_rm_vip_sav_other_log(card_id,sav_type,item_clsno,db_no,com_amt,end_date,oper_date,oper_id,branch_no,memo,num1,num2,num3,num4)
			values (@card_ID,    '0',@item_clsno, '+',@add_amt,DATEADD ( year , 50, getdate() ),getdate(),'1001' ,@branch_no  ,'平板充值',@fact_amt,0,0,@class_saving_amt +@add_amt)
			
			
			--充值插入付款流水 
			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 ,@flow_no ,@add_amt , @branch_no , @pay_way , 'A' , '' , @card_ID , @card_ID , 'RMB' , 1 , @fact_amt , getdate() , 1001 , '' , @sale_man , '1001' , '充值卡充值' , '电子储值' , '' , '0' ) 

			select @return='成功'	
		end

	return 
end
go

