if exists(select 1 from sysobjects where name='tr_rm_vip_accflow')
  drop Trigger tr_rm_vip_accflow
go

create trigger tr_rm_vip_accflow on t_rm_vip_acclist 
for insert
as
begin

  declare @ls_cardid char(20),       -- 会员卡号
      @ls_oper_type char(1),        -- 操作类型
      @ln_consum_amt numeric(16,4),    -- 消费金额
      @ln_acc_num numeric(16,4),  -- 积分值
      @ln_sale_num int,	--消费次数
      @vip_update char(1),--是否启用自动升级会员类别
      @vip_uptype char(1),--自动升级规则 0 按积分 1 按消费额
      @card_type  char(2),--原卡类别
      @to_card_type char(2), ----新卡类别
      @l_count numeric(2,0),
      @ls_flow_no char(20),
      @ln_update_acc_now numeric(16,4),----当前卡类别的累计积分
      @ln_update_acc_to numeric(16,4), -----要升级到的卡类别累计积分
      @ln_update_amt_now numeric(16,4),----当前卡类别的累计消费金额
      @ln_update_amt_to numeric(16,4), -----要升级到的卡类别累计消费金额
      @ll_row numeric(2,0),
      @ls_var_value varchar(255),
      @ls_branch_no varchar(10),
      @g_branch_no varchar(10)



	-- 当前销售积分流水数据
	select 	@ls_cardid=card_id,
		@card_type=card_type,
		@ls_oper_type=oper_type,
		@ln_consum_amt=consum_amt,
		@ln_acc_num=acc_num,
                @ls_flow_no=flow_no ,
		@ls_branch_no=branch_no   --add 
		from inserted


	--自动短信，判断是否有短信参数表
	select @ll_row = count(*) from sysobjects where name = 'mess_t_sys_system'
		
	if (@ls_oper_type = '0') 

	begin
	 -- select @l_count=count(card_id)   from t_rm_vip_savelist where card_id=@ls_cardid and flow_no=@ls_flow_no    只有储值卡付款不算会员消费 chenq 20131219
	  if @ln_consum_amt = 0
	     select @ln_sale_num = 0
	  else
	     select @ln_sale_num = 1     
	   /*if @l_count>0 
	   update t_rm_vip_info 
	   set  now_acc_num = isnull(now_acc_num,0) + @ln_acc_num,
		acc_num = isnull(acc_num,0) + @ln_acc_num
	  where card_id = @ls_cardid
          else */
           update t_rm_vip_info 
		   set  consum_amt = isnull(consum_amt,0) + @ln_consum_amt,
			consum_count = isnull(consum_count,0) + @ln_sale_num,
			now_acc_num = isnull(now_acc_num,0) + @ln_acc_num,
			acc_num = isnull(acc_num,0) + @ln_acc_num
		  where card_id = @ls_cardid
		

	---自动短信
		if @ll_row > 0 
			begin
				SELECT @ls_var_value = isnull(sys_var_value,'')  FROM mess_t_sys_system WHERE Upper(LTRIM(RTRIM(sys_var_id))) = Upper(LTRIM(RTRIM('consumer_auto')))
     				if left(@ls_var_value,1) = '1' 
				insert into mess_t_vip_message(mess_type,branch_no,card_id,acc_num,flow_amt,oper_type,send_flag,flow_no) values ('5',@ls_branch_no,@ls_cardid,@ln_acc_num,@ln_consum_amt,'0','0',@ls_flow_no)
			end

	end

	if (@ls_oper_type = 'F') 
	
		update t_rm_vip_info 
	   	set  now_acc_num = isnull(now_acc_num,0) + @ln_acc_num,
		dec_num = isnull(dec_num,0) + ABS(@ln_acc_num)
		where card_id = @ls_cardid


		---自动短信
		if @ll_row > 0 
			begin
				SELECT @ls_var_value = isnull(sys_var_value,'')  FROM mess_t_sys_system WHERE Upper(LTRIM(RTRIM(sys_var_id))) = Upper(LTRIM(RTRIM('vip_givf')))
				select @ln_consum_amt = pay_amount from t_rm_payflow  where flow_no = @ls_flow_no and coin_no = 'VIP'
				if left(@ls_var_value,1) = '1' 
					if @ln_acc_num < 0 --用积分为负时才写此表
						insert into mess_t_vip_message(mess_type,branch_no,card_id,father_card,giv_acc_num,send_flag,oper_type,change_amt) values('2',@ls_branch_no,@ls_cardid,@ls_cardid,@ln_acc_num,'0','F',@ln_consum_amt)
			end
		

	if @ls_oper_type = '3'
		if @ll_row > 0 
			begin
				SELECT @ls_var_value = isnull(sys_var_value,'')  FROM mess_t_sys_system WHERE Upper(LTRIM(RTRIM(sys_var_id))) = Upper(LTRIM(RTRIM('vip_giv3')))
				        if left(@ls_var_value,1) = '1' 
					 --积分兑换商品
					insert into mess_t_vip_message(mess_type,branch_no,card_id,father_card,giv_acc_num,send_flag,oper_type) values('2',@ls_branch_no,@ls_cardid,@ls_cardid,@ln_acc_num,'0','3')
			end
               
	if @ls_oper_type = '6'
		if @ll_row > 0
			begin
				SELECT @ls_var_value = isnull(sys_var_value,'')  FROM mess_t_sys_system WHERE Upper(LTRIM(RTRIM(sys_var_id))) = Upper(LTRIM(RTRIM('vip_giv6')))
				        if left(@ls_var_value,1) = '1' 
					  --积分转储值

					 insert into mess_t_vip_message(mess_type,branch_no,card_id,father_card,giv_acc_num,send_flag,oper_type,change_amt) values('2',@ls_branch_no,@ls_cardid,@ls_cardid,@ln_acc_num,'0','6',@ln_consum_amt)
	
			end
		---自动短信




    -----进行会员自动升级

	select @vip_update=sys_var_value from  t_sys_system where sys_var_id = 'pos_vip_update'
	select @vip_uptype=sys_var_value from  t_sys_system where sys_var_id = 'pos_vip_uptype'
	
	  if @vip_update is null
		select  @vip_update = '0'
		
		if @vip_uptype is null
	  select @vip_uptype='0'
        
 if @vip_update = '1' 
	  begin
		    select @ln_consum_amt = consum_amt , @ln_acc_num = acc_num 
					from t_rm_vip_info where card_id = @ls_cardid
		    if @vip_uptype = '0' ----按积分
	     		begin
							select  @to_card_type = type_id ,@ln_update_acc_to = isnull(update_acc,0) from t_rm_vip_type
							 where isnull(update_acc,0) <= @ln_acc_num 
							  order by update_acc ASC

							select @ln_update_acc_now = isnull(update_acc,0) from t_rm_vip_type where type_id = @card_type   ----addd


							if @to_card_type is not null and @card_type <> @to_card_type
							  update t_rm_vip_info set card_type = @to_card_type   where card_id = @ls_cardid



							--会员卡自动升级
								if @ll_row > 0 
								begin
									SELECT @ls_var_value = isnull(sys_var_value,'')  FROM mess_t_sys_system WHERE Upper(LTRIM(RTRIM(sys_var_id))) = Upper(LTRIM(RTRIM('card_type')))
									        if left(@ls_var_value,1) = '1' 
										 insert into mess_t_vip_message(mess_type,branch_no,card_id,card_type,to_card_type,send_flag) values('1',@ls_branch_no,@ls_cardid,@card_type,@to_card_type,'0')
								end
	     		end
	
		    if @vip_uptype = '1' ----按消费额
		     begin
							select @to_card_type = type_id from t_rm_vip_type
							 where isnull(update_amt,0) <= @ln_consum_amt 
							  order by update_amt ASC

							select @ln_update_amt_now = isnull(update_amt,0) from t_rm_vip_type where type_id = @card_type   --add


							if @to_card_type  is not null and @card_type <> @to_card_type
							  update t_rm_vip_info set card_type = @to_card_type   where card_id = @ls_cardid
							--会员卡自动升级
								if @ll_row > 0 
								begin
									SELECT @ls_var_value = isnull(sys_var_value,'')  FROM mess_t_sys_system WHERE Upper(LTRIM(RTRIM(sys_var_id))) = Upper(LTRIM(RTRIM('card_type')))
									        if left(@ls_var_value,1) = '1' 
										 insert into mess_t_vip_message(mess_type,branch_no,card_id,card_type,to_card_type,send_flag) values('1',@ls_branch_no,@ls_cardid,@card_type,@to_card_type,'0')
								end
		     end	
	  end 
	    
end
go