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

create procedure pr_rm_vip
  @as_flowno  varchar(20)    
as
begin
declare @ls_branchno char(6),
    @ls_itemno char(20),     
    @ls_vipno varchar(20),  
    @ls_tj varchar(1),                 
    @ln_scale numeric(16,4),           
    @ln_nativeamt numeric(16,4),
    @ln_saleQty numeric(16,4),
    @ln_sourcePrc numeric(16,4),
    @ln_salePrc numeric(16,4),
    @ls_saleWay  varchar(1),
    @li_limit_num int,
    @ls_other1 varchar(20),
    @ls_pos_record_vip varchar(1),  --是否记积分
    @ls_pos_acc_int varchar(1),     --是取整积分
    @ls_consume_type varchar(1), --记积分方式
    @ln_bytiem_limit numeric(16,4),
    @ln_bytiem_score numeric(16,4),
    @ln_total_amount numeric(16,4), --本单合计金额
    @ln_vip_acc_amount numeric(16,4),  --本次记积分
    @ln_vip_amount numeric(16,4),   --本次记积分金额
    @ls_vip_price  varchar(1),           --是否积分
    @ls_isspec		varchar(1),			--当前是否特价
    @ls_acc_flag	varchar(1),			--是否起用“商品是否积分”
    @ls_itemvip_flag    varchar(1),			--当前商品是否积分
    @ld_itemvip_acc	numeric(16,4),			--商品积分值
    @ls_record_acc char(1),		--会员类别是否记录会员积分
    @ls_clsno  char(20),		--商品类别
    @li_clslen int,			--类别长度
    @ls_spec_flag char(10),		--特价类型
    @ls_card_type char(1)

select @ln_vip_amount = 0
select @ln_vip_acc_amount = 0
    
select top 1 @ls_vipNO=b.vip_no from t_rm_saleflow a,t_rm_payflow b where a.flow_no=b.flow_no and a.flow_no=@as_flowno

if (@ls_vipNO is Null) or (@ls_vipNO = '')  return

--会员卡所属类型是否积分 acc_flag 是否积分标志

select @ls_card_type = card_type from t_rm_vip_info where card_no = @ls_vipNO

if exists (select card_id from t_rm_vip_info where card_no = @ls_vipNO and card_type in (select type_id from t_rm_vip_type where acc_flag='0')) 
   select @ls_record_acc = '1' --当前会员记录积分 
else
   select @ls_record_acc = '0' --当前会员不记录积分 

--不记录积分且不是储值卡,返回
if @ls_record_acc = '0' return

select @ls_pos_record_vip = sys_var_value from t_rm_vip_reference where lower(sys_var_id)='pos_record_vip'  and vip_type = @ls_card_type 
select @ls_consume_type = sys_var_value from t_rm_vip_reference where lower(sys_var_id)='pos_consume_type' and vip_type = @ls_card_type
select @ls_acc_flag =  sys_var_value from t_rm_vip_reference where lower(sys_var_id)='pos_vipitem_point' and vip_type = @ls_card_type

select @li_clslen = sys_var_value from t_sys_system where sys_var_id='gl_itm_cls_nolen'


if @ls_acc_flag is null 
   select @ls_acc_flag = '0'

--会员记录消费金额
if @ls_pos_record_vip <> '1' return

--记录会员积分
if (@ls_pos_record_vip='1') and (@ls_record_acc='1') and not ((@ls_vipNO is Null) or (@ls_vipNO = ''))
begin

   --按次数记积分
   if @ls_consume_type = '2'
   begin
     select @ln_bytiem_limit = convert(numeric(16,4), sys_var_value) from t_rm_vip_reference where lower(sys_var_id)='pos_bytiem_limit' and vip_type = @ls_card_type
     select @ln_bytiem_score = convert(numeric(16,4), sys_var_value) from t_rm_vip_reference where lower(sys_var_id)='pos_bytiem_score' and vip_type = @ls_card_type
   
     select top 1 @ls_saleway = sell_way, @ln_total_amount = sale_amount, @ls_branchno = branch_no  from t_rm_payflow where flow_no=@as_flowno
   
     if @ln_total_amount > @ln_bytiem_limit
     begin
        select @ln_vip_amount = @ln_total_amount
        select @ln_vip_acc_amount = @ln_bytiem_score
     end
   end
   --按金额记积分
   else if @ls_consume_type = '1'
   begin
     declare cur_vip cursor for 
         select branch_no,item_no,sale_qnty,source_price,sale_price,sell_way,spec_flag from t_rm_saleflow
         where flow_no=@as_flowno
     
     open cur_vip 
     fetch cur_vip into @ls_branchNO,@ls_ItemNO,@ln_saleQty,@ln_sourcePrc,@ln_salePrc,@ls_saleWay,@ls_spec_flag
     
     while (@@fetch_Status = 0) and @ls_pos_record_vip = '1'
     begin
       if @ls_acc_flag = '1'--增加单品是否积分
       begin
	  select @ls_itemvip_flag = vip_acc_flag  from t_bd_item_info where item_no = @ls_ItemNO
	  if @ls_itemvip_flag='0' or @ls_itemvip_flag is null
	  begin
	     fetch cur_vip into @ls_branchNO,@ls_ItemNO,@ln_saleQty,@ln_sourcePrc,@ln_salePrc,@ls_saleWay,@ls_spec_flag
	     continue
	  end
       end
       
      --小类可以积分，有大类和小类时以小类为准
       select @ls_clsno = item_clsno from t_bd_item_info where item_no=@ls_ItemNO
       while (len(@ls_clsno) >=  @li_clslen)
	begin
	   if exists (select vip_amt from t_rm_vip_set where vip_item_cls = @ls_clsno)
	    begin
	        select @ln_scale = vip_amt from t_rm_vip_set where vip_item_cls = @ls_clsno
	        select @ls_clsno = ''
	    end
	   else
	    begin
		select @ls_clsno = left(@ls_clsno,len(@ls_clsno) - @li_clslen)
	    end	   
	end       

       select @ln_nativeamt = @ln_saleQty*@ln_saleprc

      --特价是否积分 
       select @ls_tj = sys_var_value from t_rm_vip_reference where sys_var_id='pos_vip_policy' and vip_type = @ls_card_type

       --当前商品是否特价   
       if exists(select top 1 * from t_rm_spec_price where branch_no=substring(@ls_branchNO,1,2) and 
                     item_no=@ls_ItemNO and left(sheet_no,2) not in ('PH','PV')  
		     and (substring(week,datepart(weekday,getdate()),1) = '1') and 
		     (((begin_time is null or ltrim(begin_time)='') AND ((convert(char(10),getdate(),121) between convert(char(10),start_date,121) and convert(char(10),end_date,121)))) or 
         (((convert(char(10),getdate(),121) between convert(char(10),start_date,121) and convert(char(10),end_date,121)) and (convert(char(8),getdate(),108) between begin_time and end_time)) and (left(sheet_no,2)='PE') and
         sheet_no=(select max(sheet_no) from t_rm_spec_price where branch_no=substring(@ls_branchNO,1,2) and item_no=@ls_ItemNO and (convert(char(10),getdate(),121) between convert(char(10),start_date,121) and convert(char(10),end_date,121)) and ((convert(char(8),getdate(),108) between begin_time and end_time))) ) ))
      	  select @ls_isspec ='1'
       else
          --判断是否为类别品牌特价
	  if @ls_spec_flag = 'pu' 
		select @ls_isspec ='1'
	  else
      	  	select @ls_isspec ='0'
       
       if @ls_tj='0' and @ls_isspec ='1'
       begin
          fetch cur_vip into @ls_branchNO,@ls_ItemNO,@ln_saleQty,@ln_sourcePrc,@ln_salePrc,@ls_saleWay,@ls_spec_flag
          continue
       end
           
       --start 会员价商品消费记入积分(只对按金额积分有效)
       if @ls_isspec ='0'  --本开关只在无特价时生效 zhangw
       begin
           select @ls_vip_price=sys_var_value from t_rm_vip_reference where sys_var_id='pos_vipprice_point' and vip_type = @ls_card_type

	      --如果后台设为为不积分时还要比较价格是否相等，如果相等则认为没有优惠，还是积分。
           if @ls_vip_price='0' and @ln_sourcePrc > @ln_salePrc 
           begin       
	        fetch cur_vip into @ls_branchNO,@ls_ItemNO,@ln_saleQty,@ln_sourcePrc,@ln_salePrc,@ls_saleWay,@ls_spec_flag
	        continue
           end
       end
      
       if @ln_scale is null
           select @ln_scale = convert(numeric(16,4), sys_var_value) from t_rm_vip_reference where lower(sys_var_id)='pos_consume_score' and vip_type = @ls_card_type
   
       if @ln_scale>0 
       begin
          select @ln_vip_acc_amount = @ln_vip_acc_amount + @ln_nativeamt/@ln_scale
          select @ln_vip_amount = @ln_vip_amount + @ln_nativeamt 
       end
   
       fetch cur_vip into @ls_branchNO,@ls_ItemNO,@ln_saleQty,@ln_sourcePrc,@ln_salePrc,@ls_saleWay,@ls_spec_flag
     end
     close cur_vip
     deallocate cur_vip  
   end
   else --增加单品积分
   begin
       declare cur_vip_item cursor for 
         select branch_no,item_no,sale_qnty,source_price,sale_price,sell_way,spec_flag from t_rm_saleflow
         where flow_no=@as_flowno
     
       open cur_vip_item 
       fetch cur_vip_item into @ls_branchNO,@ls_ItemNO,@ln_saleQty,@ln_sourcePrc,@ln_salePrc,@ls_saleWay,@ls_spec_flag
     
       while (@@fetch_Status = 0) and @ls_pos_record_vip = '1'
       begin
          select @ld_itemvip_acc = vip_acc_num, @ls_itemvip_flag = vip_acc_flag  from t_bd_item_info where item_no = @ls_ItemNO
          if @ls_acc_flag = '1'--单品是否积分
          begin
              if @ls_itemvip_flag='0'  or @ls_itemvip_flag is null
              begin
                fetch cur_vip_item into @ls_branchNO,@ls_ItemNO,@ln_saleQty,@ln_sourcePrc,@ln_salePrc,@ls_saleWay,@ls_spec_flag
                continue
              end
              else if @ld_itemvip_acc = 0 or @ld_itemvip_acc is null
              begin
                  fetch cur_vip_item into @ls_branchNO,@ls_ItemNO,@ln_saleQty,@ln_sourcePrc,@ln_salePrc,@ls_saleWay,@ls_spec_flag
                  continue
              end
          end
      
         
          --add by jincm 小类可以积分，有大类和小类时以小类为准
         select @ls_clsno = item_clsno from t_bd_item_info where item_no=@ls_ItemNO
         while (len(@ls_clsno) >=  @li_clslen)
	  begin
	   if exists (select vip_amt from t_rm_vip_set where vip_item_cls = @ls_clsno)
	    begin
	        select @ln_scale = vip_amt from t_rm_vip_set where vip_item_cls = @ls_clsno
	        select @ls_clsno = ''
	    end
	   else
	    begin
		select @ls_clsno = left(@ls_clsno,len(@ls_clsno) - @li_clslen)
	    end	   
	  end     
	  
          select @ln_nativeamt = @ln_saleQty*@ln_saleprc

	 --特价是否积分 
          select @ls_tj = sys_var_value from t_rm_vip_reference where sys_var_id='pos_vip_policy' and vip_type = @ls_card_type

          --当前商品是否特价   
          if exists(select top 1 * from t_rm_spec_price where branch_no=substring(@ls_branchNO,1,2) and 
                     item_no=@ls_ItemNO and left(sheet_no,2) not in ('PH','PV')  
		     and (substring(week,datepart(weekday,getdate()),1) = '1') and 
		     (((begin_time is null or ltrim(begin_time)='') AND ((convert(char(10),getdate(),121) between convert(char(10),start_date,121) and convert(char(10),end_date,121)))) or 
                     (((convert(char(10),getdate(),121) between convert(char(10),start_date,121) and convert(char(10),end_date,121)) and (convert(char(8),getdate(),108) between begin_time and end_time)) and (left(sheet_no,2)='PE') and
                     sheet_no=(select max(sheet_no) from t_rm_spec_price where branch_no=substring(@ls_branchNO,1,2) and item_no=@ls_ItemNO and (convert(char(10),getdate(),121) between convert(char(10),start_date,121) and convert(char(10),end_date,121)) and ((convert(char(8),getdate(),108) between begin_time and end_time))) ) ))
      	      select @ls_isspec ='1'
          else
	  --判断是否为类别品牌特价
	 	if @ls_spec_flag = 'pu' 
			select @ls_isspec ='1'
	  	else
      	        	select @ls_isspec ='0'

          if @ls_tj='0' and @ls_isspec ='1'
          begin
             fetch cur_vip_item into @ls_branchNO,@ls_ItemNO,@ln_saleQty,@ln_sourcePrc,@ln_salePrc,@ls_saleWay,@ls_spec_flag
             continue
          end
           
          --start 会员价商品消费记入积分(只对按金额积分有效)
          if @ls_isspec ='0'  --本开关只在无特价时生效 zhangw
          begin
              select @ls_vip_price=sys_var_value from t_rm_vip_reference where sys_var_id='pos_vipprice_point' and vip_type = @ls_card_type

	      --如果后台设为为不积分时还要比较价格是否相等，如果相等则认为没有优惠，还是积分。
	      if @ls_vip_price='0' and @ln_sourcePrc > @ln_salePrc 
	      begin       
	        fetch cur_vip_item into @ls_branchNO,@ls_ItemNO,@ln_saleQty,@ln_sourcePrc,@ln_salePrc,@ls_saleWay,@ls_spec_flag
	        continue
	      end
	  end
         
          if @ld_itemvip_acc > 0
          begin
             select @ln_vip_acc_amount = @ln_vip_acc_amount + @ln_saleQty*@ld_itemvip_acc
             select @ln_vip_amount = @ln_vip_amount + @ln_nativeamt 
          end
         
          fetch cur_vip_item into @ls_branchNO,@ls_ItemNO,@ln_saleQty,@ln_sourcePrc,@ln_salePrc,@ls_saleWay,@ls_spec_flag
       end
       close cur_vip_item
       deallocate cur_vip_item  
   end

   --积分更新
   --积分取整
   select @ls_pos_acc_int = '1'
   select @ls_pos_acc_int = sys_var_value from t_rm_vip_reference where lower(sys_var_id)='pos_vip_acc_int' and vip_type = @ls_card_type
   
   if @ls_pos_acc_int = '1'
      select @ln_vip_acc_amount = floor(@ln_vip_acc_amount) 
   
   if @ls_saleWay = 'B' or @ls_saleWay = 'D'
   begin
      select @ln_vip_amount = -@ln_vip_amount
      select @ln_vip_acc_amount = -@ln_vip_acc_amount
   end
   
   if exists(select * from t_rm_vip_consume where card_id=@ls_vipno and branch_no=left(@ls_branchno,2))
   begin
       update t_rm_vip_consume set
          vip_cons_amount = isnull(vip_cons_amount,0) + @ln_vip_amount,
          vip_cur_cons_amount = isnull(vip_cur_cons_amount,0) + @ln_vip_amount,
          vip_acc_amount = isnull(vip_acc_amount,0) + @ln_vip_acc_amount,
          vip_cur_acc_amount = isnull(vip_cur_acc_amount,0) + @ln_vip_acc_amount,
          com_flag='0',
          cons_count = isnull(cons_count, 0) + 1
          where card_id = @ls_vipNO and branch_no=left(@ls_branchno,2)
   
   end
   else
       insert into t_rm_vip_consume(card_id,branch_no,vip_cons_amount,vip_cur_cons_amount,vip_acc_amount,vip_cur_acc_amount,cons_count)
             values(@ls_vipNO, left(@ls_branchno,2),@ln_vip_amount,@ln_vip_amount,@ln_vip_acc_amount,@ln_vip_acc_amount,1)
   
   
   --更新积分
   update t_rm_vip_info set acc_num = isnull(acc_num,0) + @ln_vip_acc_amount where card_id=@ls_vipNO
   
  
   select @li_limit_num = use_num from t_rm_vip_info where card_id=@ls_vipNO
   if @li_limit_num is null return
   
   
   if @li_limit_num >0 
   begin
      --更新使用次数
      select @li_limit_num =@li_limit_num - 1
      update t_rm_vip_info set use_num=convert(varchar(10),@li_limit_num) where card_id=@ls_vipNO
   end
end

return

myerror:
  close cur_vip
  deallocate cur_vip
  close cur_vip_item
  deallocate cur_vip_item
  return
end
go
