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

create  procedure pr_rm_vipol
  @as_flowno  varchar(20),@as_branch_no char(6),@as_birthday_date varchar(10),@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),  --双倍积分设置  wujs 06-03-30
    @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),  --本次记积分
	--只需要两位小数即可，以解决积分清零后结余积分会出现-0.00的积分的情况。add by wangyy  at 2014-03-19
    @ln_vip_acc_amount numeric(16,2),  --本次记积分  
    @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), ---农历生日处理 tansp 20110517
    @ls_bth_flag varchar(10), ---农历生日处理 tansp 20110517
    @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),            --时段积分类别
	@ls_temp varchar(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)    --取最大的会员积分倍数 wangyy 2011-11-10
declare @li_flow int,
	@ls_allret varchar(2),
	@ls_retflowno varchar(20)

/*会员日相关变量*/
declare @vipday_mode varchar(10),
        @vipday_value varchar(80),
        @vipday_acc decimal(10,2),
        @oper_date datetime

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
--@ls_allret 全退不能退所有积分
select @li_flow=CHARINDEX('|',@as_flowno,1)
select @ls_allret = '0'
if @li_flow > 0 
begin
	select @ls_allret = substring( @as_flowno,@li_flow + 1,1)
	select @as_flowno = left(@as_flowno,@li_flow - 1)
end
select top 1 @ls_vipNO='tempvip', @ls_branchno = b.branch_no, @oper_date= a.oper_date 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 top 1 @ls_card_no=null
if ((@ls_vipno is Null) or (@ls_vipno = '')) and ((@ls_card_no is Null) or (@ls_card_no = '')) goto myerror

--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)  goto myerror

--宽广特殊需求，付款方式为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)  goto myerror
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)  goto myerror
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)  goto myerror
end   


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'

select @ls_record_acc = '1'

select @ls_pos_record_vip = sys_var_value from t_sys_system where lower(sys_var_id)='pos_record_vip'

--不记录积分且不是储值卡,返回
if @ls_record_acc = '0' and ((@ls_card_no is Null) or (@ls_card_no = '')) goto myerror

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

-- 记录会员积分
--会员生日N倍积分 liug 2009-09-19
--农历生日处理 tansp 2011-05-17
select @ls_birthflag = '0'
select @ls_birthmonth_flag = '0'
select @ls_bth_flag = '公历'
select @ls_vip_birthday = @as_birthday_date

if @ls_bth_flag = '农历'
begin
	if right(@as_lunar_date, 5) = @ls_vip_birthday
		select @ls_birthflag = '1'
	--判断是不是农历生日的那一个月  add by wangyy at 2011-07-27
	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'
	--判断是不是公历生日的那一个月  add by wangyy at 2011-07-27
	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'
--select @ls_vip_birthday = right(convert(char(10), birthday, 121), 5) from t_rm_vip_info where card_id = @ls_vipno
--if right(convert(char(10), getdate(), 121), 5) = @ls_vip_birthday
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_vip_acc_amount = @ln_vip_acc_amount * @ln_multi_birthday 
		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
		--SELECT @ln_vip_acc_amount = @ln_vip_acc_amount * @ld_birthmonth --N倍积分
		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( substring(sys_var_value,day(getdate())* 2 - 1,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
	        --SELECT @ln_vip_acc_amount = @ln_vip_acc_amount * @ln_multi_month --N倍积分
			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( substring(sys_var_value,DatePart(w,getdate()) * 2 - 1,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
           --SELECT @ln_vip_acc_amount = @ln_vip_acc_amount * @ln_multi_week --N倍积分
	   if @ln_max_mult < @ln_multi_week set @ln_max_mult = @ln_multi_week
	    select @ls_double='1'
      end
   end
 end

--期间积分 如前面N倍积分都无效，则考虑期间积分 add by wangyy at 2011-07-27
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
			--set @ln_vip_acc_amount = @ln_vip_acc_amount * @ld_daterange
	   		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

--Add by Dengzw 2014.09.05会员日积分处理开始
select @vipday_mode = sys_var_value from t_sys_system where sys_var_id='vipday_mode'
select @vipday_value = sys_var_value from t_sys_system where sys_var_id='vipday_value'
Declare @vipday_acc2 varchar(80)
select @vipday_acc2 = sys_var_value from t_sys_system where sys_var_id='vipday_acc'

if (Isnumeric(@vipday_acc2)=0) set @vipday_acc2 = '1'

select @vipday_acc = cast(isnull(@vipday_acc2,1) as decimal(10,2))

/*设置会员日默认值*/
IF (isnull(@vipday_acc,1) <= 1) set @vipday_acc = 1

declare @i int, @isvipday char(1)
/*按日期设置的会员日*/
set @i = 1
set @isvipday = '0'
while (@i<LEN(@vipday_value))
begin
    IF Convert(int,SUBSTRING( @vipday_value, @i, 2 )) = DATEPART(DD,@oper_date) And @vipday_mode='1'
        set @isvipday = '1'
    IF (Convert(int,SUBSTRING( @vipday_value, @i, 2 )) = DATEPART(w,@oper_date) - 1) And @vipday_mode='2'
        set @isvipday = '1'

    set @i = @i + 2
    
end

/*未开启会员日或当天不是会员日，则积分倍数为1倍*/
IF (@isvipday = '0') set @vipday_acc = 1

IF @ln_max_mult < @vipday_acc set @ln_max_mult = @vipday_acc
--会员日积分处理完成


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

			--必须要初始化@ln_multi_sd，否则，当下的面SQL语句查不到值的时候，@ln_multi_sd还是取上一商品的@ln_multi_sd。edit by wangyy at 2014-02-18
			select @ln_multi_sd = 1
			-- end edit wangyy
			--记录时段积分
			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
	
			--时段积分倍数不参与最大倍数优先。故屏蔽之。edit by wangyy at 2014-02-12 
			--if @ln_multi_sd > @ln_max_mult
			-- begin
			--select @ln_vip_acc_amount_time = @ln_vip_acc_amount * @ln_multi_sd
			--  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 --------------------------------
	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'  -- 增加单品是否积分 add by jincm 2005-01-24   
			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 %时不积分add by fufl20100115 
			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/vip_num 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_scale = vip_amt from t_rm_vip_set where vip_item_cls=( select left(item_clsno,((select sys_var_value from t_sys_system where sys_var_id='gl_itm_cls_nolen'))) from t_bd_item_info where item_no=@ls_ItemNO)
			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'
				--如果数据库中不存在此记录，则默认为不记积分 add by wangyy at 2013-01-09
				if @ls_vip_price is null or rtrim(@ls_vip_price) = '' select @ls_vip_price = '0'
				-- 默认为积分
				-- if (@ls_vip_price is null or rtrim(ltrim(@ls_vip_price)) = '' ) select @ls_vip_price = '1' 
				-- if @ls_vip_price<>'1' 
				-- 如果后台设为为不积分时还要比较价格是否相等，如果相等则认为没有优惠，还是积分。 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'
				--如果数据库中不存在此记录，则默认为不记积分 add by wangyy at 2013-01-09
				if @ls_vip_spce_price is null or rtrim(@ls_vip_spce_price) = '' select @ls_vip_spce_price = '0'

				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
			--必须要初始化@ln_multi_sd，否则，当下的面SQL语句查不到值的时候，@ln_multi_sd还是取上一商品的@ln_multi_sd。edit by wangyy at 2014-02-18
			select @ln_multi_sd = 1
			-- end edit wangyy
			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 ((@ls_clsno like rtrim(item_no) + '%') or item_no = 'ALL')order by work_date desc
			if @ln_multi_sd is null
				set @ln_multi_sd = 1
   
			if @ln_scale>0 
			begin
				if @ls_saleWay='B'
				begin
					--select @ln_vip_acc_amount = @ln_vip_acc_amount - @ln_nativeamt/@ln_scale
					--select @ln_vip_amount = @ln_vip_amount - @ln_nativeamt 

					--记录时段积分  add by zhoucc 2012-10-10
					IF @ls_max_mult = '1'
					begin 
						if @ln_multi_sd > @ln_max_mult
						begin 
							select @ln_vip_acc_amount = @ln_vip_acc_amount - @ln_nativeamt/@ln_scale*@ln_multi_sd 
							select @ln_vip_amount = @ln_vip_amount - @ln_nativeamt 
						end 
						else 
						begin 
							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 
					else
					begin
						--如果没有设置时段积分倍数，则取原来的倍数
						--if @ln_multi_sd = 0 select @ln_multi_sd = @ln_max_mult
						--前面已经给@ln_multi_sd赋了初始值1了，所以此处判断有没有时段积分倍数时应该判断@ln_multi_sd的值是否为1才对。edit by wangyy at 2014-07-08
						if @ln_multi_sd = 1 select @ln_multi_sd = @ln_max_mult
						select @ln_vip_acc_amount = @ln_vip_acc_amount - @ln_nativeamt/@ln_scale*@ln_multi_sd 
	       				select @ln_vip_amount = @ln_vip_amount - @ln_nativeamt 
					end
				end
				else
				begin	
					IF @ls_max_mult = '1'
					begin 
						if @ln_multi_sd > @ln_max_mult
						begin 
	 						select @ln_vip_acc_amount = @ln_vip_acc_amount + @ln_nativeamt/@ln_scale*@ln_multi_sd 
	            			select @ln_vip_amount = @ln_vip_amount + @ln_nativeamt 
						end 
						else 
						begin 
	 						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 
					else
					begin
						--如果没有设置时段积分倍数，则取原来的倍数
						--if @ln_multi_sd = 0 select @ln_multi_sd = @ln_max_mult
						--前面已经给@ln_multi_sd赋了初始值1了，所以此处判断有没有时段积分倍数时应该判断@ln_multi_sd的值是否为1才对。edit by wangyy at 2014-07-08
						if @ln_multi_sd = 1 select @ln_multi_sd = @ln_max_mult
						select @ln_vip_acc_amount = @ln_vip_acc_amount + @ln_nativeamt/@ln_scale*@ln_multi_sd 
	       				select @ln_vip_amount = @ln_vip_amount + @ln_nativeamt 
					end
				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 ------------------------------
	end
	else -- 增加单品积分 add by jincm 2005-01-24   
	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 %时不积分add by fufl20100115 
			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_scale = vip_amt from t_rm_vip_set where vip_item_cls=( select left(item_clsno,((select sys_var_value from t_sys_system where sys_var_id='gl_itm_cls_nolen'))) from t_bd_item_info where item_no=@ls_itemno)
			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'
			-- 根据优惠方式是否为空来判断当前销售是否为特价 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' 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 会员价商品消费记入积分(只对按金额积分有效) modified by zenglw 2004-05-10
			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'
				--如果数据库中不存在此记录，则默认为不记积分 add by wangyy at 2013-01-09
				if @ls_vip_price is null or rtrim(@ls_vip_price) = '' select @ls_vip_price = '0'

				-- 如果后台设为为不积分时还要比较价格是否相等，如果相等则认为没有优惠，还是积分。 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'
				--如果数据库中不存在此记录，则默认为不记积分 add by wangyy at 2013-01-09
				if @ls_vip_spce_price is null or rtrim(@ls_vip_spce_price) = '' select @ls_vip_spce_price = '0'
				
				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
			--必须要初始化@ln_multi_sd，否则，当下的面SQL语句查不到值的时候，@ln_multi_sd还是取上一商品的@ln_multi_sd。edit by wangyy at 2014-02-18
			select @ln_multi_sd = 1
			-- end edit wangyy
			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 ((@ls_clsno like rtrim(item_no)  + '%') or (item_no = 'ALL')) 
			order by work_date desc
			if @ln_multi_sd is null
				set @ln_multi_sd = 1

			if @ld_itemvip_acc > 0
			begin
				if @ls_saleWay='B'
				begin
					--select @ln_vip_acc_amount = @ln_vip_acc_amount - @ln_saleQty*@ld_itemvip_acc
					--select @ln_vip_amount = @ln_vip_amount - @ln_nativeamt 

					--记录时段积分    add by zhoucc 2012-10-10
					IF @ls_max_mult = '1'
					begin 
						if @ln_multi_sd > @ln_max_mult
						begin 
							select @ln_vip_acc_amount = @ln_vip_acc_amount - @ln_saleQty*@ld_itemvip_acc*@ln_multi_sd
	            			select @ln_vip_amount = @ln_vip_amount - @ln_nativeamt
						end 
						else 
						begin 
	 						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 
					else
					begin
						--如果没有设置时段积分倍数，则取原来的倍数
						--if @ln_multi_sd = 0 select @ln_multi_sd = @ln_max_mult
						--前面已经给@ln_multi_sd赋了初始值1了，所以此处判断有没有时段积分倍数时应该判断@ln_multi_sd的值是否为1才对。edit by wangyy at 2014-07-08
						if @ln_multi_sd = 1 select @ln_multi_sd = @ln_max_mult
						select @ln_vip_acc_amount = @ln_vip_acc_amount - @ln_saleQty*@ld_itemvip_acc*@ln_multi_sd
	       				select @ln_vip_amount = @ln_vip_amount - @ln_nativeamt 
					end
				end
				else
				begin	
					IF @ls_max_mult = '1'
					begin 
						if @ln_multi_sd > @ln_max_mult
						begin 
							select @ln_vip_acc_amount = @ln_vip_acc_amount +@ln_saleQty*@ld_itemvip_acc*@ln_multi_sd
	            			select @ln_vip_amount = @ln_vip_amount + @ln_nativeamt 
						end 
						else 		
						begin 
	 						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 
					else
					begin	
						--如果没有设置时段积分倍数，则取原来的倍数
						--if @ln_multi_sd = 0 select @ln_multi_sd = @ln_max_mult
						--前面已经给@ln_multi_sd赋了初始值1了，所以此处判断有没有时段积分倍数时应该判断@ln_multi_sd的值是否为1才对。edit by wangyy at 2014-07-08
						if @ln_multi_sd = 1 select @ln_multi_sd = @ln_max_mult
						select @ln_vip_acc_amount = @ln_vip_acc_amount +@ln_saleQty*@ld_itemvip_acc*@ln_multi_sd
	       				select @ln_vip_amount = @ln_vip_amount + @ln_nativeamt 
	    
					end
				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 -------------------
	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
		select @ls_temp = sys_var_value from t_sys_system where lower(sys_var_id) = 'acc_round'
		if @ls_temp is null select @ls_temp = '1' --默认为向下取整
		if @ls_temp = '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 
		else if @ls_temp = '2'
		begin
			--向上取整
			if @ln_vip_acc_amount > 0
				select @ln_vip_acc_amount = ceiling(@ln_vip_acc_amount) 
			else
      			select @ln_vip_acc_amount = -ceiling(abs(@ln_vip_acc_amount)) 
		end
		else
		begin
			--四舍五入
			if @ln_vip_acc_amount > 0
				select @ln_vip_acc_amount = round(@ln_vip_acc_amount,0) 
			else
      			select @ln_vip_acc_amount = -round(abs(@ln_vip_acc_amount),0) 
		end
	end    

	--会员类别积分比例 add by wangyy at 2011-07-20 -----------------------------------------------------------------------------------
	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
	---------------------- end add ---------------------------------------------------------------------------------------------------

	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
	select vip_acc=@ln_vip_acc_amount,amount=@ln_vip_amount
	return
end
select vip_acc=0,amount=0	
return
myerror:
  select vip_acc=0,amount=0	
  return
end
go