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

create  procedure pr_rm_vip
  @as_flowno  varchar(20),@as_branch_no char(6),@as_lunar_date varchar(10) 
as
begin
declare @ls_branchno char(6),
    @ls_itemno char(20),     
    @ls_vipno varchar(20),  
    @ls_card_no 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),    --记积分方式
    @pos_vip_multiple varchar(10),  --双倍积分设置  
    @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,                 --类别长度
    @ll_cnt    int,
    @ls_vip_spce_price varchar(1),  --会员特价是否积分
    @ls_spec_flag char(10),          --特价类型
    @pos_vip_weekn_double varchar(10), ---每星期几双倍积分 
    @ls_double varchar(1),
    @ln_multi_month int, --第几天N倍分
    @ln_multi_week int,   --每星期几N倍积分 
    @ls_multi_birthday varchar(10), --会员生日N倍积分
    @ln_multi_birthday int,
    @ls_vip_birthday varchar(5),
    @ls_lessrate_flag varchar(10),
    @ln_percent numeric(6,2),
    @ln_profitrate numeric(16,2),
    @ln_multi_sd numeric(16,4), -- 时段N倍积分
    @ls_cs varchar(1),
    @ld_acc_rate decimal(10,2), --会员积分比例
    @ls_birthflag char(1), ---农历生日处理  
    @ls_bth_flag varchar(10), ---农历生日处理  
    @pos_vip_auto_int	char(1),	--记会员积分自动抹零
    @pos_vip_auto_int_dec int,	--积分四舍五入几位小数之后抹零
    @ln_vip_acc_amount_time numeric(16,4),  --时段记积分
    @ls_clsno_time  char(20)            --时段积分类别

declare @ld_birthmonth decimal(10,2),  --会员生日月积分倍数
	@ld_daterange decimal(10,2),   --期间积分倍数
	@ls_daterange_s varchar(10),  --期间开始日期
	@ls_daterange_e varchar(10),   --期间结束日期
	@ls_birthmonth_flag char(1),    --会员生日月标志
	@ln_max_mult decimal(10,2),    --会员生日月标志
	@ls_max_mult char(1)    --取最大的会员积分倍数  

select @ls_double='0'
select @ln_vip_amount = 0
select @ln_vip_acc_amount = 0
select @ln_multi_birthday = 0
select @ln_max_mult = 0
select @ln_vip_acc_amount_time = 0

--zhangyf付款方式为GIF的小票不积分，返回（只有是赠送商品产生的付款单不参与积分，如果是促销导致的GIF还是需要积分的）
if exists(select 1 from t_rm_saleflow a,t_rm_payflow b where (b.pay_way='GIF' and b.sell_way = 'C') and a.flow_no=b.flow_no and a.flow_no=@as_flowno and b.branch_no = @as_branch_no) return

--宽广特殊需求，付款方式为LGK或SAV的小票不积分，返回
if  (select sys_var_value from t_sys_system where sys_var_id='pos_use_bank_card')='1' 
begin
    if exists(select 1 from t_rm_saleflow a,t_rm_payflow b where (b.pay_way='LGK' or b.pay_way='SAV') and a.flow_no=b.flow_no and a.flow_no=@as_flowno and b.branch_no = @as_branch_no) return
end 
--有储值卡付款的小票不参与积分
if  (select sys_var_value from t_sys_system where sys_var_id='pos_use_sav_noacc')='1' 
begin
    if exists(select 1 from t_rm_saleflow a,t_rm_payflow b where  b.pay_way='SAV' and a.flow_no=b.flow_no and a.flow_no=@as_flowno and b.branch_no = @as_branch_no) return
end   

--使用代金券付款的小票不参与积分
if  (select sys_var_value from t_sys_system where sys_var_id='pos_use_shp_noacc')='1' 
begin
    if exists(select 1 from t_rm_saleflow a,t_rm_payflow b where  b.pay_way='SHP' and a.flow_no=b.flow_no and a.flow_no=@as_flowno and b.branch_no = @as_branch_no) return
end   

select top 1 @ls_vipNO=b.vip_no, @ls_branchno = b.branch_no from t_rm_saleflow
 a,t_rm_payflow b where a.flow_no=b.flow_no and a.flow_no=@as_flowno and b.branch_no = @as_branch_no
select top 1 @ls_card_no=card_no from t_rm_payflow where flow_no=@as_flowno and pay_way ='SAV' and branch_no = @as_branch_no

select	@pos_vip_auto_int=sys_var_value from t_sys_system where sys_var_id='pos_vip_auto_int'
select	@pos_vip_auto_int_dec=convert(int,sys_var_value) from t_sys_system where sys_var_id='pos_vip_auto_int_dec'

if ((@ls_vipNO is Null) or (@ls_vipNO = '')) and ((@ls_card_no is Null) or (@ls_card_no = '')) return
-- 会员卡所属类型是否积分 acc_flag 是否积分标志
if exists (select card_id from t_rm_vip_info where card_id = @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' --当前会员不记录积分 

select @ls_pos_record_vip = sys_var_value from t_sys_system where lower(sys_var_id)='pos_record_vip'
--mahw 不积分也要减使用次数
if (@ls_pos_record_vip='1') and (@ls_record_acc <> '1') and not ((@ls_vipNO is Null) or (@ls_vipNO = ''))
begin
	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 cons_count = isnull(cons_count, 0) + 1, com_flag='0',cons_date=getdate(),modify_date=getdate()
          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,cons_date,modify_date)
          values(@ls_vipNO, left(@ls_branchno,2),0,0,0,0,1,getdate(),getdate())

	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

--不记录积分且不是储值卡,返回
if @ls_record_acc = '0' and ((@ls_card_no is Null) or (@ls_card_no = '')) return
select @ls_consume_type = sys_var_value from t_sys_system where lower(sys_var_id)='pos_consume_type'
select @ls_acc_flag =  sys_var_value from t_sys_system where lower(sys_var_id)='pos_vipitem_point'
select @li_clslen = sys_var_value from t_sys_system where sys_var_id='gl_itm_cls_nolen'
select @ls_max_mult = sys_var_value from t_sys_system where sys_var_id='pos_vip_max_mult'
if @ls_max_mult is null set @ls_max_mult = '0'
if @ls_acc_flag is null 
   select @ls_acc_flag = '0'

--增加当商品档案毛利率<X%时不积分  按消费金额积分和按单品的积分值积分时有效  add by fufl20100115 
select @ls_lessrate_flag = rtrim(sys_var_value) from t_sys_system where sys_var_id='noacc_less_itemrate'
if @ls_lessrate_flag is null or @ls_lessrate_flag=''
	select @ls_lessrate_flag='0'  
else
	begin	  
           select @ln_percent=cast(substring(@ls_lessrate_flag,3,len(@ls_lessrate_flag) - 2 ) as  numeric(6,2))
           select @ls_lessrate_flag=left(@ls_lessrate_flag,1)
	end

-- 会员记录消费金额

if @ls_pos_record_vip <> '1' and ((@ls_card_no is Null) or (@ls_card_no = ''))
begin
   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 cons_count = isnull(cons_count, 0) + 1, com_flag='0',cons_date=getdate(),modify_date=getdate()
          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,cons_date,modify_date)
          values(@ls_vipNO, left(@ls_branchno,2),0,0,0,0,1,getdate(),getdate())
   
   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
   -- 记录了消费次数后， 直接返回.  wujs 2005-05-16
   return
end
-- 记录会员积分
--会员生日N倍积分  
   --农历生日处理  
	select @ls_birthflag = '0'
	select @ls_birthmonth_flag = '0'
	select @ls_bth_flag = bth_flag, @ls_vip_birthday = right(convert(char(10), birthday, 121), 5) from t_rm_vip_info where card_id = @ls_vipno
	if @ls_bth_flag = '农历'
	begin
	   if right(@as_lunar_date, 5) = @ls_vip_birthday
	      select @ls_birthflag = '1'
	   --判断是不是农历生日的那一个月   
	   if left(right(@as_lunar_date, 5),2) = left(@ls_vip_birthday,2) and exists ( select 1 from t_sys_system where sys_var_id = 'pos_vip_birthmonth' and sys_var_value = '1')
		set @ls_birthmonth_flag = '1'
	end
	else
	begin
	   if right(convert(char(10), getdate(), 121), 5) = @ls_vip_birthday
	      select @ls_birthflag = '1'
	   --判断是不是公历生日的那一个月   
	   if left(right(convert(char(10), getdate(), 121), 5),2) = left(@ls_vip_birthday,2) and exists ( select 1 from t_sys_system where sys_var_id = 'pos_vip_birthmonth' and sys_var_value = '1')
		set @ls_birthmonth_flag = '1'
	end
		
   if @ls_birthflag = '1'
   begin
     select @ls_multi_birthday = sys_var_value FROM t_sys_system WHERE lower(sys_var_id) = 'pos_vip_birth_double'
     select @ls_multi_birthday = isnull(@ls_multi_birthday, '0/2')
     if substring(@ls_multi_birthday, 1, 1) = '1' 
     begin
       select @ln_multi_birthday = convert(int, substring(@ls_multi_birthday,3,len(@ls_multi_birthday) - 2))
       if @ln_multi_birthday is null
          select @ln_multi_birthday = 2				
          --N倍积分
           
	  select @ln_max_mult = @ln_multi_birthday
          select @ls_double='1'
     end
   end
   

   --如果没有生日N倍积分，则考虑生日当月N倍积分
   if @ls_double <> '1' or @ls_max_mult = '1'
   begin 
	if @ls_birthmonth_flag = '1'
	begin
		select @ld_birthmonth = cast(isnull(sys_var_value,'2') as int) FROM t_sys_system WHERE lower(sys_var_id) = 'pos_vip_birthmonth_v'
		if @ld_birthmonth is null 
			set @ld_birthmonth = 2
		 
		if @ln_max_mult < @ld_birthmonth set @ln_max_mult = @ld_birthmonth
	        select @ls_double='1'
	end 
   end 

   --是否N倍积分日 fufl 09-09-22
   --当天是生日N倍积分时，就不再做每月第几日N倍积分操作
   if  @ls_double<>'1'  or @ls_max_mult = '1'
   begin
	SELECT @pos_vip_multiple = sys_var_value FROM t_sys_system WHERE lower(sys_var_id) = 'pos_vip_multiple'
	SELECT @pos_vip_multiple = ISNULL(@pos_vip_multiple, '0/1')
	--N倍积分 liug20090919,N[2,20]
	select @ln_multi_month = cast(isnull(sys_var_value,'2') as int) FROM t_sys_system WHERE lower(sys_var_id) = 'pos_vip_multi_month'
	if @ln_multi_month is null 
	   select @ln_multi_month=2
	   IF (substring(@pos_vip_multiple, 1, 1) = '1' AND len(@pos_vip_multiple) > 2)
	   BEGIN
	     IF (DATEPART(dd, getdate()) = convert(int,substring(@pos_vip_multiple, 3, len(@pos_vip_multiple) - 2) ))
	     begin
	         
		if @ln_max_mult < @ln_multi_month set @ln_max_mult = @ln_multi_month
	        select @ls_double='1'
	     end
	   END
    end

  --每星期几N倍积分日 fufl 09-09-22
  --当天是生日N倍积分或每月第几日N倍积分日时，就不再做每星期几双倍积分操作
  if @ls_double<>'1'  or @ls_max_mult = '1'
  begin
   select @pos_vip_weekn_double = sys_var_value FROM t_sys_system WHERE lower(sys_var_id) = 'pos_vip_weekn_double'
   select @pos_vip_weekn_double = isnull(@pos_vip_weekn_double,'0/')
   --N倍积分 liug20090919,N[2,20]
   select @ln_multi_week = cast(isnull(sys_var_value,'2') as int) FROM t_sys_system WHERE lower(sys_var_id) = 'pos_vip_multi_week'
   if @ln_multi_week is null 
      select @ln_multi_week=2
   if (substring(@pos_vip_weekn_double,1,1)='1' and len(@pos_vip_weekn_double)>2)
   begin
      if (datename(dw,getdate()) = substring(@pos_vip_weekn_double,3,len(@pos_vip_weekn_double) - 2) )
      begin
            
	   if @ln_max_mult < @ln_multi_week set @ln_max_mult = @ln_multi_week
	    select @ls_double='1'
      end
   end
  end

  --期间积分 如前面N倍积分都无效，则考虑期间积分  
  if @ls_double<>'1'  or @ls_max_mult = '1'
  begin
	if exists (select 1 from t_sys_system where sys_var_id = 'pos_vip_date_range' and sys_var_value = '1')
	begin
		select @ls_daterange_s = sys_var_value from t_sys_system where sys_var_id = 'pos_vip_date_range_s'
		select @ls_daterange_e = sys_var_value from t_sys_system where sys_var_id = 'pos_vip_date_range_e'
		if convert(char(10),getdate(),120) between @ls_daterange_s and @ls_daterange_e
		begin
			select @ld_daterange = cast(sys_var_value as int) from t_sys_system where sys_var_id = 'pos_vip_date_range_v'
			if @ld_daterange is null or @ld_daterange <= 0 
				set @ld_daterange = 2
			
	   		if @ln_max_mult < @ld_daterange set @ln_max_mult = @ld_daterange
			set @ls_double = '1' 
		end 
	end
  end

if @ln_max_mult is null or @ln_max_mult = 0 set @ln_max_mult = 1

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_sys_system where lower(sys_var_id)='pos_bytiem_limit'
     select @ln_bytiem_score = convert(numeric(16,4), sys_var_value) from t_sys_system where lower(sys_var_id)='pos_bytiem_score'
   
     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 and pay_way<>'CHG' and branch_no = @as_branch_no
   
     if @ln_total_amount > @ln_bytiem_limit
     begin

	--记录时段积分
	select top 1  @ln_multi_sd = d.num1 from t_pc_price_flow_master m ,t_pc_price_flow_detail d 
	where m.trans_no = 'JF' and d.sheet_no= m.sheet_no and  m.valid_flag='0' and 
	m.approve_flag = '1' and substring(m.week,DATEPART ( dw , getdate() )  ,1) = '1'  and convert(char(10), getdate(), 121) between m.valid_date and m.end_date
	and right(convert(char(19), getdate(), 121),8) between d.begin_time and d.end_time and CHARINDEX(left(@ls_branchNO,2),m.branchlist)>0  and
	(d.item_no = 'JF000'or d.item_no = 'ALL')  order by work_date desc
	if @ln_multi_sd is null
	   set @ln_multi_sd = 1

	if @ln_multi_sd > @ln_max_mult
	 begin
	   
	   set @ln_max_mult = @ln_multi_sd
	 end

	if @ls_saleway='B'
	begin
	   select @ln_vip_amount = -@ln_total_amount
	   select @ln_vip_acc_amount = -@ln_bytiem_score*@ln_max_mult
	end
	else
	begin
       	   select @ln_vip_amount = @ln_total_amount
           select @ln_vip_acc_amount = @ln_bytiem_score*@ln_max_mult
	end

     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 and branch_no = @as_branch_no
     
     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
	   --
	   --select @ln_scale = 0
       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  

       if @ls_lessrate_flag = '1' --当商品档案毛利率<X %时不积分 
       begin
       		select @ln_profitrate = round((case sale_price when 0 then 0 else (sale_price - price)/sale_price end),4) * 100 
                from t_bd_item_info where item_no = @ls_ItemNO  
                if @ln_profitrate < @ln_percent 
                 begin
		     fetch cur_vip 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
       select @ln_scale = null
       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 = round(@ln_saleQty*@ln_saleprc,2)
      -- 特价是否积分 
       select @ls_tj = sys_var_value from t_sys_system where sys_var_id='pos_vip_policy'
       select @ls_cs = sys_var_value from t_sys_system where sys_var_id='pos_vip_cs_set'
	if @ls_cs is null 
	select @ls_cs = '0'
       -- 根据优惠方式是否为空来判断当前销售是否为特价 modified by wujs 2005-09-19
       -- 即省了查询特价时间，又能防止超量特价不能判断是否需要积分
	if @ls_spec_flag='ps' or @ls_spec_flag='pn' or @ls_spec_flag='pu' or @ls_spec_flag='pe' or
	     @ls_spec_flag='pc' or @ls_spec_flag='pcb' or @ls_spec_flag='pd' or @ls_spec_flag='pdb' or
	     @ls_spec_flag='dm' or @ls_spec_flag='py' or @ls_spec_flag='pg' or @ls_spec_flag='pm' or
	     @ls_spec_flag='lf' or @ls_spec_flag='ph' or @ls_spec_flag = 'ep' 
          select @ls_isspec = '1'
       else
          select @ls_isspec = '0'
       
       if (@ls_tj='0' and @ls_isspec ='1') or  ( @ls_cs ='0' and  @ls_spec_flag='cs')
       begin
          fetch cur_vip into @ls_branchNO,@ls_ItemNO,@ln_saleQty,@ln_sourcePrc,@ln_salePrc,@ls_saleWay,@ls_spec_flag
          continue
       end
       -- start 会员价商品消费记入积分(只对按金额积分有效) modified by zenglw 2004-05-10
       if @ls_isspec ='0' and ( @ls_spec_flag='pv' or @ls_spec_flag='cs')  -- 本开关只在无特价时生效 zhangw
       begin
           select @ls_vip_price=sys_var_value from t_sys_system where sys_var_id='pos_vipprice_point'
	   -- 默认为积分
	   
	   -- 如果后台设为为不积分时还要比较价格是否相等，如果相等则认为没有优惠，还是积分。 zhangw
           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	
		-- 会员特价商品是否记积分(一定要是会员价记积分才能是特价是否记积分) add by lihg 2006-06-14
			-- 取会员特价
			select @ll_cnt = count(item_no) from t_rm_spec_price
			where branch_no=(select sys_var_value from t_sys_system where sys_var_id = 'g_branch_no')
			and item_no = @ls_ItemNO and special_type = '2' 
			and ( convert(char(10),getdate(),121) between convert(char(10),start_date,121) and convert(char(10),end_date,121))
			and ( substring(week, DATEPART(dw, getdate()), 1) = '1')
			and ( left(other3,2)='PV') 
			and ( other3=( select max(other3) 
							 from t_rm_spec_price 
							where branch_no=(select sys_var_value from t_sys_system where sys_var_id = 'g_branch_no')
							  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))));

			select @ls_vip_spce_price=sys_var_value from t_sys_system where sys_var_id='pos_vip_spec_price'
			if @ls_vip_price = '1' and @ls_vip_spce_price = '0' and @ll_cnt > 0
			begin
				fetch cur_vip into @ls_branchNO,@ls_ItemNO,@ln_saleQty,@ln_sourcePrc,@ln_salePrc,@ls_saleWay,@ls_spec_flag
	        	continue
			end
		-- end add by lihg 2006-06-14

       end
       -- ended moddified 
       if @ln_scale is null  ----or @ln_scale = 0  为0时表示该类别不积分
           select @ln_scale = convert(numeric(16,4), sys_var_value) from t_sys_system where lower(sys_var_id)='pos_consume_score'

	select @ls_clsno = item_clsno from t_bd_item_info where item_no=@ls_ItemNO
	select top 1  @ln_multi_sd = d.num1 from t_pc_price_flow_master m ,t_pc_price_flow_detail d 
	where m.trans_no = 'JF' and d.sheet_no= m.sheet_no and  m.valid_flag='0' and 
	m.approve_flag = '1' and substring(m.week,DATEPART ( dw , getdate() )  ,1) = '1'  and convert(char(10), getdate(), 121) between m.valid_date and m.end_date
	and right(convert(char(19), getdate(), 121),8) between d.begin_time and d.end_time and CHARINDEX(left(@ls_branchNO,2),m.branchlist)>0  and
	((left(item_no,2) = left(@ls_clsno,2)) or item_no = 'ALL')order by work_date desc
	if @ln_multi_sd is null
	  set @ln_multi_sd = 0
   
       if @ln_scale>0 
       begin
          if @ls_saleWay='B'
          begin
            
	    --记录时段积分
	     if @ln_multi_sd > @ln_max_mult
	       begin
		  set @ln_max_mult = @ln_multi_sd
	       end
	     
	     select @ln_vip_acc_amount = @ln_vip_acc_amount - @ln_nativeamt/@ln_scale*@ln_max_mult
             select @ln_vip_amount = @ln_vip_amount - @ln_nativeamt 
          end
	  else
	  begin	
	     
	    --记录时段积分
	     if @ln_multi_sd > @ln_max_mult 
	       begin		  
		  set @ln_max_mult = @ln_multi_sd
	       end
	     
	     select @ln_vip_acc_amount = @ln_vip_acc_amount + @ln_nativeamt/@ln_scale*@ln_max_mult
             select @ln_vip_amount = @ln_vip_amount + @ln_nativeamt 
	
  	  end 
       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 and branch_no = @as_branch_no
     
       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

          if @ls_lessrate_flag = '1' --当商品档案毛利率<X %时不积分
          begin
       		select @ln_profitrate = round((case sale_price when 0 then 0 else (sale_price - price)/sale_price end),4) * 100 
                from t_bd_item_info where item_no = @ls_ItemNO  
                if @ln_profitrate < @ln_percent 
                 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 =round( @ln_saleQty*@ln_saleprc ,2)
	
	  
	  -- 特价是否积分 
          select @ls_tj = sys_var_value from t_sys_system where sys_var_id='pos_vip_policy'
          -- 根据优惠方式是否为空来判断当前销售是否为特价 
          -- 即省了查询特价时间，又能防止超量特价不能判断是否需要积分
	  if @ls_spec_flag='ps' or @ls_spec_flag='pn' or @ls_spec_flag='pu' or @ls_spec_flag='pe' or
	     @ls_spec_flag='pc' or @ls_spec_flag='pcb' or @ls_spec_flag='pd' or @ls_spec_flag='pdb' or
	     @ls_spec_flag='dm' or @ls_spec_flag='py' or @ls_spec_flag='pg' or @ls_spec_flag='pm' or
	     @ls_spec_flag='lf' or @ls_spec_flag='ph' or @ls_spec_flag = 'ep' or @ls_spec_flag='cs'
             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' and @ls_spec_flag='pv' -- 本开关只在无特价时生效 zhangw
          begin
           select @ls_vip_price=sys_var_value from t_sys_system where sys_var_id='pos_vipprice_point'

		      -- 如果后台设为为不积分时还要比较价格是否相等，如果相等则认为没有优惠，还是积分。 zhangw
		      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
			-- 会员特价商品是否记积分(一定要是会员价记积分才能是特价是否记积分) add by lihg 2006-06-14
			-- 取会员特价
			select @ll_cnt = count(item_no) from t_rm_spec_price
			where branch_no=(select sys_var_value from t_sys_system where sys_var_id = 'g_branch_no')
			and item_no = @ls_ItemNO and special_type = '2' 
			and ( convert(char(10),getdate(),121) between convert(char(10),start_date,121) and convert(char(10),end_date,121))
			and ( substring(week, DATEPART(dw, getdate()), 1) = '1')
			and ( left(other3,2)='PV') 
			and ( other3=( select max(other3) 
							 from t_rm_spec_price 
							where branch_no=(select sys_var_value from t_sys_system where sys_var_id = 'g_branch_no')
							  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))));

			select @ls_vip_spce_price=sys_var_value from t_sys_system where sys_var_id='pos_vip_spec_price'
			if @ls_vip_price = '1' and @ls_vip_spce_price = '0' and @ll_cnt > 0
			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 lihg 2006-06-14
	  	end

		select @ls_clsno = item_clsno from t_bd_item_info where item_no=@ls_ItemNO
		select top 1  @ln_multi_sd = d.num1 from t_pc_price_flow_master m ,t_pc_price_flow_detail d 
		where m.trans_no = 'JF' and d.sheet_no= m.sheet_no and  m.valid_flag='0' and 
		m.approve_flag = '1' and substring(m.week,DATEPART ( dw , getdate() )  ,1) = '1'  and convert(char(10), getdate(), 121) between m.valid_date and m.end_date
		and right(convert(char(19), getdate(), 121),8) between d.begin_time and d.end_time and CHARINDEX(left(@ls_branchNO,2),m.branchlist)>0  and
		((left(item_no,2) = left(@ls_clsno,2)) or (item_no = 'ALL')) order by work_date desc
		if @ln_multi_sd is null
		   set @ln_multi_sd = 0

          if @ld_itemvip_acc > 0
          begin
	     if @ls_saleWay='B'
	     begin
             	
		 --记录时段积分
		if @ln_multi_sd > @ln_max_mult 
	       	 begin		   
		   set @ln_max_mult = @ln_multi_sd
	       	 end
		
		select @ln_vip_acc_amount = @ln_vip_acc_amount - @ln_saleQty*@ld_itemvip_acc*@ln_max_mult
             	select @ln_vip_amount = @ln_vip_amount - @ln_nativeamt
		
	     end
             else
	     begin
	        
		 --记录时段积分
		if @ln_multi_sd > @ln_max_mult 
	       	 begin		  
		  set @ln_max_mult = @ln_multi_sd
	       	 end
		
		select @ln_vip_acc_amount = @ln_vip_acc_amount + @ln_saleQty*@ld_itemvip_acc*@ln_max_mult
	        select @ln_vip_amount = @ln_vip_amount + @ln_nativeamt 		

	     end 
          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_sys_system where lower(sys_var_id)='pos_vip_acc_int' 
   if @ls_pos_acc_int = '1'
   begin
     if @ln_vip_acc_amount > 0
        select @ln_vip_acc_amount = floor(@ln_vip_acc_amount) 
     else
      	select @ln_vip_acc_amount = -floor(abs(@ln_vip_acc_amount)) 
   end    

  --会员类别积分比例 
  select @ld_acc_rate = acc_rate from t_rm_vip_type where type_id = (select card_type from t_rm_vip_info where card_id = @ls_vipNO)
  if @ld_acc_rate is null or @ld_acc_rate <= 0 
	set @ld_acc_rate = 1
  select @ln_vip_acc_amount = @ln_vip_acc_amount*@ld_acc_rate
 

 if @ls_saleWay = 'D'
   begin
      select @ln_vip_amount = -@ln_vip_amount
      select @ln_vip_acc_amount = -@ln_vip_acc_amount
   end
   
 if	@pos_vip_auto_int='1' 	
    begin
        set @ln_vip_amount=round(@ln_vip_amount,isnull(@pos_vip_auto_int_dec,0))
	set @ln_vip_acc_amount=round(@ln_vip_acc_amount,isnull(@pos_vip_auto_int_dec,0))
    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_date=getdate(),modify_date=getdate(),
          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,cons_date,modify_date)
             values(@ls_vipNO, left(@ls_branchno,2),@ln_vip_amount,@ln_vip_amount,@ln_vip_acc_amount,@ln_vip_acc_amount,1,getdate(),getdate())
    
   --更新积分
   update t_rm_vip_info set acc_num = isnull(acc_num,0) + @ln_vip_acc_amount where card_id=@ls_vipNO 
   --记录第笔小票积分  
   update t_rm_card_paylist set vip_acc = @ln_vip_acc_amount 
	where branch_no = substring(@ls_branchno,1,2) and card_type='1'
	  and card_id = @ls_vipNO and flow_no = @as_flowno

   update mess_t_vip_message set acc_num = @ln_vip_acc_amount
	where branch_no = substring(@ls_branchno,1,2) and card_type='1'
	  and card_id = @ls_vipNO and flow_no = @as_flowno and send_flag = '0'

    
   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

--记录充值卡消费金额
if  not ((@ls_card_no is Null) or (@ls_card_no = ''))
begin
   
   --如果有多笔付款(多张充值卡,现金混合)
   declare ldc_card_pay cursor for 
       select card_no, (case sell_way when 'B' then -pay_amount else pay_amount end), branch_no from t_rm_payflow
            where flow_no=@as_flowno and pay_way = 'SAV'

   open ldc_card_pay

   fetch ldc_card_pay into @ls_card_no, @ln_total_amount, @ls_branchno
   while @@fetch_status=0
   begin
        if exists(select * from t_rm_vip_consume where card_id=@ls_card_no and branch_no=left(@ls_branchno,2))
        begin
             update t_rm_vip_consume set com_flag='0',cons_date=getdate(),modify_date=getdate(),
                                     cons_count = isnull(cons_count, 0) + 1,
                                     cons_amount = isnull(cons_amount,0) + @ln_total_amount
                               where card_id = @ls_card_no 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,cons_amount,cons_date,modify_date)
             values(@ls_card_no, left(@ls_branchno,2),0,0,0,0,1,@ln_total_amount,getdate(),getdate())
       
        if @@error <> 0
        begin
           close ldc_card_pay
           deallocate ldc_card_pay
           goto myerror
       end
        
       fetch ldc_card_pay into @ls_card_no, @ln_total_amount, @ls_branchno
   end
   close ldc_card_pay
   deallocate ldc_card_pay
end 
return
myerror:
  return
end
go