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


create procedure pr_rm_get_item_info_vip(@as_branchNO char(6), @as_ItemNO char(20), @an_saleQty decimal(12,4),@as_vip_no varchar(20))
as
begin
-- 编码的有效性判断
  if not exists(select 1 from t_bd_item_info where item_no=@as_ItemNO) return

  declare @ls_SubName varchar(20),    -- 简称
    @ls_ClassNO char(6),            -- 类别编码
    @ln_salePrice decimal(16,4),      -- 零售价
    @ln_tmpPrice decimal(16,4),     -- 临时价格
    @ln_specPrice decimal(16,4),    -- 前台特价
    @ln_cardPrice decimal(16,4),    -- 会员卡价
    @ln_vipPrice decimal(16,4),     -- 会员卡价
    @ln_discount decimal(5,4),      -- 会员卡价
    @ln_saleQty decimal(16,4),      -- 客单限购数量
    @limited_qty decimal(16,4),     -- 卖场限购数量
    @ln_soldQty decimal(16,4),      -- 当前已经销售的特价销售数量
    @ls_giftitem varchar(1000),     -- 赠送商品编码列，format: 001/002/003
    @ls_giftqty varchar(400),       -- 赠送商品数量列，format: 10/2/4
    @ls_giftamt varchar(400),       -- 赠送商品金额列，format: 12.50/30.0/100.40
    @ldt_startDate datetime,        -- 特价开始日期
    @ldt_endDate datetime,          -- 特价结束日期
    @ldt_startDate_tmp datetime,        -- 特价开始日期
    @ldt_endDate_tmp datetime,          -- 特价结束日期
    @ls_startTime char(8),          -- 特价开始time
    @ls_endTime char(8)   ,         -- 特价结束time
    @ls_startTime_tmp char(8),          -- tmp特价开始time
    @ls_endTime_tmp char(8)   ,         -- tmp特价结束time
    @ls_comb	char(1)	,	 -- 商品类型（组、拆、生...）   
    @ls_trade_type char(1),	 -- 分部类型
    @ls_specflag char(2),         --特价标志
    @ls_spec_sheet_no char(20),         --特价单据
    @ls_spec_sheet_tmp char(20),         --tmp特价单据
    @ls_viptype char(2),      --传入的会员卡类型   '%'全场
    @ls_change  char(1),
    @ls_other1  char(1),
    @ls_BandNO char(6),            -- 品牌编码
    @ls_saleway char(1),           --会员价 or 折扣 （1 会员价，0 折扣）
    @ln_vip_discount decimal(5,4),      --          --会员价 or 折扣 （1 会员价，0 折扣）
    @ln_vip_discount_day decimal(5,4),      --          --会员日折扣
    @flag int, --是否有查询记录	
    @en_dis char(1) --前台是否打折
    -- Modified by huangc  2005-01-10
    select @ls_trade_type = trade_type
       from t_bd_branch_info
        where branch_no = substring(@as_branchNO,1,2)
    select @ls_specflag=''
    select @ls_spec_sheet_no = ''
    -- 从(t_bd_item_info)中获取前台商品的基本信息
    -- @ln_salePrice=sale_price,   @ln_vipPrice=vip_price
    select @ls_SubName=item_subname,	@ls_ClassNO=item_clsno,
        @ls_comb =combine_sta ,
    	@ln_salePrice=sale_price,   @ln_vipPrice=vip_price,@ls_BandNO = isnull(item_brand,'PP'), @en_dis = isnull(en_dis, '')
        from t_bd_item_info where item_no=@as_ItemNO
        
       
    
    ------POS型分部取机构价格表   
    --if @ls_trade_type = '4'
    --begin
    if exists(select 1 from t_pc_branch_price where branch_no=substring(@as_branchNO,1,2) and item_no=@as_ItemNO) 
	begin
			select @ln_salePrice = sale_price, @ln_vipPrice = vip_price
			  from t_pc_branch_price
			 where branch_no=substring(@as_branchNO,1,2) and item_no=@as_ItemNO	
	end
    --end
    
   -- begin 取到当前传入的会员卡的类型，如果会员档案里找不到该会员卡，则会员卡类型为"%"  add by wangjj 2017-09-27
    if isnull(@as_vip_no,'') = '' 
		begin
		   set @ls_viptype = '%'
		end 
    else
		begin
	       select @ls_viptype = card_type from t_rm_vip_info where card_id=@as_vip_no
	         if isnull(@ls_viptype,'%')= '%' set @ls_viptype = '%' ---如果找不到会员卡 或者找不到类型时，类型为'%'
		end
		
		 if @ls_viptype <>'%'
		 begin
		    select @ls_saleway = sale_way,@ln_vip_discount=discount from t_rm_vip_type where type_id = @ls_viptype	--,@ln_vip_discount_day = discount2
		 end 
		
    -- end 取到当前传入的会员卡的类型，如果会员档案里找不到该会员卡，则会员卡类型为"%"  add by wangjj 2017-09-27
    /*************************************************************
    *  special_type
    *   0              促销特价单
    *   0              时段特价单		
    *   2              会员特价单
    *   5              品牌特价			
    *   6              类别特价
    *   4              超量特价单
    *   PW             全场折扣促销（t_pc_price_flow_master表中取数）
    **************************************************************
    *
    * 同一商品取价顺序     以上价格  相同类型 后单压前单,
    *                                不同类型 低价优先，  
    *     
    **************************************************************/

    -- 特价,会员卡价处理....
   select @ln_specPrice = @ln_salePrice
    select @ln_cardPrice = 0
    select @ls_change = '0'
    --Modified by wujs 2004-06-04
    if @ln_vipPrice is null or @ln_vipPrice = 0
       select @ln_vipPrice = @ln_salePrice     
    
    ---促销特价单
    set @flag = 0
	select top 1 @ln_tmpPrice = spe_price,@ln_saleQty = sale_qty,@limited_qty = limited_qty,@ln_soldQty=sold_qty,@ls_spec_sheet_tmp = other3 ,
    @ldt_startDate_tmp = start_date,@ldt_endDate_tmp = end_date,@ls_startTime_tmp= begin_time,@ls_endTime_tmp = end_time, @flag = 1 
    from t_rm_spec_price where (branch_no=substring(@as_branchNO,1,2)) 
	and (item_no=@as_ItemNO)  and special_type = '0'  
	and (substring(week, DATEPART(dw, getdate()), 1) = '1')  
	and (convert(char(10),getdate(),121) between start_date and end_date)
	and (spe_price < @ln_salePrice)  and (substring(other3,1,2) = 'PS')  
	order by flow_no desc
	
  if isnull(@ln_tmpPrice,0) >0 and  isnull(@ln_tmpPrice,0)<@ln_specPrice and @flag = 1 
   begin
		if @limited_qty=0 or @limited_qty is null or @limited_qty>99999 --是否作卖场限量
		 begin
		 --  select @ln_specPrice = @ln_tmpPrice
		   select @ls_change = '1'
		 end
		else
		begin
						--判断卖场限量是否超出
			if @limited_qty>@ln_soldQty  --未超出
				begin
				  -- select @ln_specPrice = @ln_tmpPrice
				   select @ls_change = '1'
			   end
			else 
				begin
				 --select @ln_specPrice = @ln_salePrice
				   select @ls_change = '0'
				end
		end
		
		if @ls_change = '1'
		begin
		--print '0.2:---------------------' + convert(char(10),@ln_tmpPrice)
		   select @ln_specPrice = @ln_tmpPrice
		   select @ls_specflag = LEFT(@ls_spec_sheet_tmp,2)
		   select @ls_spec_sheet_no = @ls_spec_sheet_tmp
		   select @ldt_startDate = @ldt_startDate_tmp
		   select @ldt_endDate  = @ldt_endDate_tmp
		   select @ls_startTime = @ls_startTime_tmp
		   select @ls_endTime   = @ls_endTime_tmp
		end

	end 

     ---时段特价单
    set @flag = 0 
	select top 1 @ln_tmpPrice = spe_price,@ln_saleQty = sale_qty,@limited_qty = limited_qty,@ln_soldQty=sold_qty,@ls_spec_sheet_tmp = other3 ,
    @ldt_startDate_tmp = start_date,@ldt_endDate_tmp = end_date,@ls_startTime_tmp = begin_time,@ls_endTime_tmp = end_time, @flag = 1 
    from t_rm_spec_price where (branch_no=substring(@as_branchNO,1,2)) 
	and (item_no=@as_ItemNO) and special_type = '0'  and (substring(week, DATEPART(dw, getdate()), 1) = '1')  
	and (convert(char(10),getdate(),121) between start_date and end_date)  
	and (convert(char(8),getdate(),114) between begin_time and end_time)  
	and (spe_price < @ln_salePrice)  and (substring(other3,1,2) = 'PE') 
	order by flow_no desc
 
     if  isnull(@ln_tmpPrice,0)>0 and  isnull(@ln_tmpPrice,0)<@ln_specPrice and @flag = 1
   begin
		if @limited_qty=0 or @limited_qty is null or @limited_qty>99999 --是否作卖场限量
		 begin
		 --  select @ln_specPrice = @ln_tmpPrice
		   select @ls_change = '1'
		 end
		else
		begin
						--判断卖场限量是否超出
			if @limited_qty>@ln_soldQty  --未超出
				begin
				  -- select @ln_specPrice = @ln_tmpPrice
				   select @ls_change = '1'
			   end
			else 
				begin
				 --select @ln_specPrice = @ln_salePrice
				   select @ls_change = '0'
				end
		end
			
		if @ls_change = '1'
		begin
		--print '0.3:---------------------' +convert(char(10),@ln_tmpPrice)
		   select @ln_specPrice = @ln_tmpPrice
		   select @ls_specflag = LEFT(@ls_spec_sheet_tmp,2)
		   select @ls_spec_sheet_no = @ls_spec_sheet_tmp
		   select @ldt_startDate = @ldt_startDate_tmp
		   select @ldt_endDate  = @ldt_endDate_tmp
		   select @ls_startTime = @ls_startTime_tmp
		   select @ls_endTime   = @ls_endTime_tmp
		end
		
	end 

	---类别
	set @flag = 0
	select top 1   @ln_discount = discount,@ln_saleQty = sale_qty,@limited_qty = limited_qty,@ln_soldQty=sold_qty,@ls_spec_sheet_tmp = other3 ,
    @ldt_startDate_tmp = start_date,@ldt_endDate_tmp = end_date,@ls_startTime_tmp = begin_time,@ls_endTime_tmp = end_time, @flag = 1 
     from t_rm_spec_price where  (branch_no=substring(@as_branchNO,1,2))  and special_type = '6' 
	 and (substring(week, DATEPART(dw, getdate()), 1) = '1')  
	 and (convert(char(10),getdate(),121) between start_date and end_date)  
	 and  (gift_qty = '%' or gift_qty = @ls_viptype) 
	 and isnull(charindex(RTRIM(@as_ItemNO) + '', gift_item), 0) = 0 
	 and (item_no = (select max(item_no) from t_rm_spec_price  
	  where  (item_no = substring(@ls_ClassNO, 1, len(item_no)))       
		 and (branch_no=substring(@as_branchNO,1,2))  and special_type = '6'   
		 and (convert(char(10),getdate(),121) between start_date and end_date) ) ) 
		 and (convert(char(8),getdate(),114) between begin_time and end_time)  
		 order by flow_no desc
    
      if isnull(@ln_discount,0.0)>0.0 and isnull(@ln_discount,0.0)<1 and @flag = 1
      begin
       select  @ln_tmpPrice = round(@ln_salePrice*@ln_discount,2)
      
       --print '0.4:---------------------' +convert(char(10),@ln_tmpPrice) 
       
		   if @ln_tmpPrice>0 and @ln_tmpPrice < @ln_specPrice
		   begin
			   select @ln_specPrice = @ln_tmpPrice
			   select @ls_specflag = LEFT(@ls_spec_sheet_tmp,2)
			   select @ls_spec_sheet_no = @ls_spec_sheet_tmp
			   select @ldt_startDate = @ldt_startDate_tmp
			   select @ldt_endDate  = @ldt_endDate_tmp
			   select @ls_startTime = @ls_startTime_tmp
			   select @ls_endTime   = @ls_endTime_tmp
		   end 
       end 
               
         
               
---品牌
	set @flag = 0
	select top 1 @ln_discount = discount,@ln_saleQty = sale_qty,@limited_qty = limited_qty,@ln_soldQty=sold_qty,@ls_spec_sheet_tmp = other3 ,
		@ldt_startDate_tmp = start_date,@ldt_endDate_tmp = end_date,@ls_startTime_tmp = begin_time,@ls_endTime_tmp = end_time, @flag = 1 
		 from t_rm_spec_price where  (branch_no=substring(@as_branchNO,1,2))  and item_no = @ls_BandNO and special_type = '5'  
	and (substring(week, DATEPART(dw, getdate()), 1) = '1')  
	and (convert(char(10),getdate(),121) between start_date and end_date) 
	and (convert(char(8),getdate(),114) between begin_time and end_time)  
	 and (gift_qty = @ls_viptype or gift_qty='%') and isnull(charindex(@as_ItemNO + '', gift_item), 0) = 0 
	order by flow_no desc
	
	  if isnull(@ln_discount,0.0)>0.0 and isnull(@ln_discount,0.0)<1 and @flag = 1
      begin
       select  @ln_tmpPrice = round(@ln_salePrice*@ln_discount,2)
       if @ln_tmpPrice>0 and @ln_tmpPrice < @ln_specPrice
		   begin
       --print '0.5:---------------------' +convert(char(10),@ln_tmpPrice) 
			   select @ln_specPrice = @ln_tmpPrice
			   select @ls_specflag = LEFT(@ls_spec_sheet_tmp,2)
			   select @ls_spec_sheet_no = @ls_spec_sheet_tmp
			   select @ldt_startDate = @ldt_startDate_tmp
			   select @ldt_endDate  = @ldt_endDate_tmp
			   select @ls_startTime = @ls_startTime_tmp
			   select @ls_endTime   = @ls_endTime_tmp
			end
       end 
                
   ---会员限量特价单
	set @flag = 0
	select top 1 @ln_tmpPrice = spe_price,@ln_saleQty = sale_qty,@limited_qty = limited_qty,@ln_soldQty=sold_qty,@ls_spec_sheet_tmp = other3 ,
    @ldt_startDate_tmp = start_date,@ldt_endDate_tmp = end_date,@ls_startTime_tmp = begin_time,@ls_endTime_tmp = end_time, @flag = 1 ---,@ls_other1 = other1
    from t_rm_spec_price where (branch_no=substring(@as_branchNO,1,2)) 
	and (item_no=@as_ItemNO) and special_type = '2'  and (substring(week, DATEPART(dw, getdate()), 1) = '1')  and (sale_qty <= 1 ) 
	and (convert(char(10),getdate(),121) between start_date and end_date)  
	and (convert(char(8),getdate(),114) between begin_time and end_time)  
	and (spe_price < @ln_salePrice)  and (substring(other3,1,2) = 'PV')  and (gift_qty = '%' or gift_qty = @ls_viptype) 
	order by flow_no desc
	 
   if isnull(@ln_tmpPrice,0)>0 and  isnull(@ln_tmpPrice,0)< @ln_specPrice and @flag = 1
   begin
		if @limited_qty=0 or @limited_qty is null or @limited_qty>99999 --是否作卖场限量
		 begin
		 --  select @ln_specPrice = @ln_tmpPrice
		   select @ls_change = '1'
		 end
		else
		begin
						--判断卖场限量是否超出
			if @limited_qty>@ln_soldQty  --未超出
				begin
				  -- select @ln_specPrice = @ln_tmpPrice
				   select @ls_change = '1'
			   end
			else 
				begin
				 --select @ln_specPrice = @ln_salePrice
				   select @ls_change = '0'
				end
		end
		
		if @ls_change = '1'
		begin
		 --- print '0.6:---------------------' +convert(char(10),@ln_tmpPrice)
		   select @ln_specPrice = @ln_tmpPrice
		   select @ls_specflag = LEFT(@ls_spec_sheet_tmp,2)
			select @ls_spec_sheet_no = @ls_spec_sheet_tmp
			select @ldt_startDate = @ldt_startDate_tmp
			select @ldt_endDate  = @ldt_endDate_tmp
			select @ls_startTime = @ls_startTime_tmp
			select @ls_endTime   = @ls_endTime_tmp
		end
		
	end 		
	
	
	---全场折扣促销
	declare @other1 char(1)
	set @other1 = '0'
	set @flag = 0
		
	select top 1 @ln_discount = convert(decimal(16,2),other2), @other1 = other1, @ls_spec_sheet_tmp = sheet_no, 
		@ldt_startDate_tmp = convert(datetime,convert(char(10),valid_date,121)), @ldt_endDate_tmp = convert(datetime,convert(char(10),end_date,121)), 
		@flag = 1 		
	from t_pc_price_flow_master 
	where convert(datetime,convert(char(10),getdate(),121)) between convert(datetime,convert(char(10),valid_date,121)) and convert(datetime,convert(char(10),end_date,121))	    
	and trans_no='PW' 
	and approve_flag='1' 
	and (charindex(left(@as_branchNO,2),branchlist) >0 or branch_no=left(@as_branchNO,2) or branch_no='ALL')	
	order by work_date desc;
	
	if @flag = 1
	begin
		if @other1 = '1' and not exists (select * from t_rm_vip_info where card_id = @as_vip_no)	--要求是会员但是传入的为正确的会员卡号时，不取折扣
		begin
			set @flag = 1
		end 
		else
		begin	
			  if isnull(@ln_discount,0.0)>0.0 and isnull(@ln_discount,0.0)<1    
			  begin
			   select  @ln_tmpPrice = round(@ln_salePrice*@ln_discount,2)
			   if @ln_tmpPrice>0 and @ln_tmpPrice < @ln_specPrice
				   begin
			   --print '0.5:---------------------' +convert(char(10),@ln_tmpPrice) 
					   select @ln_specPrice = @ln_tmpPrice
					   select @ls_specflag = LEFT(@ls_spec_sheet_tmp,2)
					   select @ls_spec_sheet_no = @ls_spec_sheet_tmp
					   select @ldt_startDate = @ldt_startDate_tmp
					   select @ldt_endDate  = @ldt_endDate_tmp
					   select @ls_startTime = ''
					   select @ls_endTime   = ''
					end
			   end 
		end
	end	
	      
	  
	---超量特价单	
	set @flag = 0
	select top 1 @ln_tmpPrice = spe_price,@ln_saleQty = sale_qty,@limited_qty = limited_qty,@ln_soldQty=sold_qty,@ls_spec_sheet_tmp = other3 ,
    @ldt_startDate_tmp = start_date,@ldt_endDate_tmp = end_date,@ls_startTime_tmp = begin_time,@ls_endTime_tmp = end_time, @flag = 1 
    from t_rm_spec_price where (branch_no=substring(@as_branchNO,1,2)) 
	and (item_no=@as_ItemNO) and special_type = '4'  and (substring(week, DATEPART(dw, getdate()), 1) = '1')  
	and (convert(char(10),getdate(),121) between start_date and end_date) and (sale_qty <= 1 ) --因自助收银不好汇总数量，因此此起始数量仅支持<=1的情况
	and (spe_price < @ln_salePrice)  and (substring(other3,1,2) = 'PN') 
	order by flow_no desc
	
  if  isnull(@ln_tmpPrice,0)>0 and isnull(@ln_tmpPrice,0) < @ln_specPrice and @flag = 1
      begin
     ---   print '0.7:---------------------'+convert(char(10),@ln_tmpPrice) 
			   select @ln_specPrice = @ln_tmpPrice
			   select @ls_specflag = LEFT(@ls_spec_sheet_tmp,2)
			   select @ls_spec_sheet_no = @ls_spec_sheet_tmp
			   select @ldt_startDate = @ldt_startDate_tmp
			   select @ldt_endDate  = @ldt_endDate_tmp
			   select @ls_startTime = @ls_startTime_tmp
			   select @ls_endTime   = @ls_endTime_tmp
        end 

    -- 没有特价，特价=零售价
    if @ln_specPrice is null or @ln_specPrice = 0
      select @ln_specPrice = @ln_salePrice

    -- 没有会员卡价，会员卡价=会员价
     --如果是会员卡，那么会员卡价就是会员价
     if @ln_cardPrice is null or @ln_cardPrice = 0
    select @ln_cardPrice = @ln_vipPrice

    ----如果是折扣卡，那么会员卡价就是 售价*折扣
    --if @ls_saleway = '0'   select @ln_cardPrice = round(@ln_salePrice * @ln_vip_discount,2)
    
    
    declare @i int, @isvipday char(1),@vipday_mode char(1), @vip_day_ofset varchar(80)
	declare @vipday_value varchar(80), @vip_day_ofset_dis varchar(10), @pos int, @dis_type char(1)
	
	select @vip_day_ofset = sys_var_value from t_sys_system where sys_var_id = 'vip_day_ofset'
		
	if len(@vip_day_ofset) > 2 and @en_dis = '1'
	begin		
		select @vipday_mode = left(@vip_day_ofset, 1)
		select @vipday_value=  substring(@vip_day_ofset, 3, LEN(@vip_day_ofset) - 2)
		
		set @i = 1
		set @isvipday = '0'
		while (@i<LEN(@vipday_value))
		begin
			IF Convert(int,SUBSTRING( @vipday_value, @i, 2 )) = DATEPART(DD,GETDATE()) And @vipday_mode='1'
				set @isvipday = '1'
			IF (Convert(int,SUBSTRING( @vipday_value, @i, 2 )) = DATEPART(w,GETDATE()) - 1) And @vipday_mode='2'
				set @isvipday = '1'

			set @i = @i + 3	    
		end
		--- 没有会员折扣日 但是是折扣卡的
		if @isvipday = '0' and @ls_saleway = '0'  select @ln_cardPrice = round(@ln_salePrice * @ln_vip_discount,2)
		
		if @isvipday = '1'   --有会员日折扣
		begin
		
			select @vip_day_ofset_dis = sys_var_value from t_sys_system where sys_var_id = 'vip_day_ofset_dis'
			
			if @vip_day_ofset_dis > ''
			begin			
				set @pos = CHARINDEX('|', @vip_day_ofset_dis)
				
				set @dis_type = substring(@vip_day_ofset_dis, @pos + 1, 1)
			
				if @dis_type='1' --全场折扣
				begin					
					set @ln_vip_discount_day = CONVERT(decimal(18,2), left( @vip_day_ofset_dis , @pos - 1))
				end
				
				if @dis_type='2' --部分折扣
				begin
					select @ln_vip_discount_day = dis from t_vipday_item where item_no = @as_ItemNO										 
				end
			
			   if @ln_vip_discount >=@ln_vip_discount_day 
				  select @ln_cardPrice = round(@ln_salePrice * @ln_vip_discount_day,2)
			   else
				  select @ln_cardPrice = round(@ln_salePrice * @ln_vip_discount,2)
			end
		end  
	end
	

    -- 处理促销赠品
    select top 1 @ls_giftitem=gift_item, @ls_giftqty=gift_qty,
           @ls_giftamt=gift_amt, @ln_saleQty = sale_qty
    from t_rm_spec_price 
    where ((branch_no = 'ALL' or branch_no = substring(@as_branchNO,1,2)) and 
            item_no = @as_ItemNO and special_type = '3' and sale_qty >0 ) and 
          (convert(char(10), Getdate(), 121) between convert(char(10), gift_startdate,121) and 
	                                             convert(char(10), gift_enddate,121))

    select @ls_giftitem = isnull(@ls_giftitem,'')
    select @ls_giftqty = isnull(@ls_giftqty,'')
    select @ls_giftamt = isnull(@ls_giftamt,'')
    
    if @ln_cardPrice < @ln_specPrice
    begin   
		if @isvipday = '1' 
			set @ls_specflag = 'vd'	 --会员日
		else
			set @ls_specflag = 'pv'	 --会员价
	end 
			
    -- 返回值
    select @ls_SubName as SubName,
      @ls_ClassNO      as ClassNO,
      @ln_salePrice    as salePrice,
      @ln_specPrice    as specPrice,
      @ln_cardPrice    as cardPrice,
      @ln_saleQty      as saleQty,
      @ls_giftitem     as giftitem,
      @ls_giftqty      as giftqty , 
      @ls_giftamt      as giftamt,
      @ls_comb         as comb,
      @ls_specflag     as specflag,
      @ls_spec_sheet_no as spec_sheet_no,
      @ldt_startDate   as startDate,         -- 特价开始日期
	  @ldt_endDate     as endDate,          -- 特价结束日期
	  @ls_startTime    as startTime,          -- 特价开始time
	  @ls_endTime      as endTime     -- 特价结束time
      
end
go