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

/*
EXEC pr_wxasf_item_query '69211656','000002'
EXEC pr_wxasf_item_query '9256','000002'
EXEC pr_wxasf_item_query '06101002','000002'
EXEC pr_wxasf_item_query '69211656','000002','13800138003','<?xml version="1.0" ?><root><cardid></cardid></root>'
EXEC pr_wxasf_item_query '69211656','000002','13800138000','<?xml version="1.0" ?><root><cardid>hy001</cardid></root>'
*/


CREATE procedure pr_wxasf_item_query
(
	@itembarcode nvarchar(50), 
	@branch_no nvarchar(6),
	@phone NVARCHAR(20) = '',
	@extend VARCHAR(4000) = ''
)
WITH ENCRYPTION
as
begin
  declare @ls_itemname varchar(40),    --商品名称
	@ln_basePrice decimal(16,4),      -- 原价
    @ln_salePrice decimal(16,4),      -- 零售价
    @ls_specType char(1),         -- 特价类型
    @ln_tmpPrice decimal(16,4),
    @ln_specPrice decimal(16,4),    -- 前台特价
    @ln_cardPrice decimal(16,4),    -- 会员价
    @ln_vipPrice decimal(16,4),     -- 会员价
    @ln_vipPrice2 decimal(16,4),    --会员价2
    @ln_vipPrice3 decimal(16,4),    --会员价3
    @ln_vipPrice4 decimal(16,4),    --会员价4
    @ln_vipPrice5 decimal(16,4),    --会员价5
    @ln_discount decimal(6,2),      -- 折扣率
    @ln_buyQty 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
    @ls_suptel varchar(30),         -- 维修电话
    @ls_supaddr varchar(100),       -- 维修地址
    @ls_supno varchar(30),          -- 维修编码
    @ldt_startDate datetime,        -- 特价开始日期
    @ldt_endDate datetime,          -- 特价结束日期
    @ls_startTime char(8),          -- 特价开始time
    @ls_endTime char(8)   ,         -- 特价结束time
    @ldt_starttime datetime,        -- 特价开始日期
    @ldt_endtime datetime,          -- 特价结束日期
    @ldt_now datetime    ,
    @ls_comb	char(1)	,	 -- 商品类型（组、拆、生...）   
    @ln_flow decimal(16,0),     -- 流水号
    @ls_trade_type char(1),	 -- 分部类型
    @ls_specflag  char(3),	 --增加特价标志，以区别PS\PE
    @ls_itemno	varchar(50),	--商品条形码(现直接货号)
	@ls_unit_no	varchar(4),	--单位
	@ls_item_size varchar(20), --规格
	@msg varchar(100),
	@vip_type CHAR(1),
	@sale_way CHAR(1),
	@price_type INT,
	@discount NUMERIC(10,2),
	@week_day  INT, --星期数,星期日是7，星期一是1，以此类推
	@card_id NVARCHAR(50),
	@count INT,
	@item_brand NVARCHAR(10),
	@item_cls_no NVARCHAR(10),
	@rule_no VARCHAR(10), --促销类型
	@plan_no_show VARCHAR(50), --输出促销单号
	@plan_no_spec VARCHAR(50),--特价单
	@rule_no_spec CHAR(10)
	
	select @week_day =  datepart(WEEKDAY,GETDATE()) - 1
    if @week_day = 0 
       select @week_day = 7
       
	-- 编码的有效性判断
	if not exists(select 1 from t_bd_item_info where item_no=@itembarcode)
		BEGIN
			--附加条码
			SELECT @ls_itemno = ISNULL(item_no,'') FROM t_bd_item_barcode WHERE item_barcode =@itembarcode 
			IF @ls_itemno = '' OR @ls_itemno IS NULL
				BEGIN
					--自编码
					SELECT @ls_itemno = max(ISNULL(item_no,'')),@count = COUNT(*)
					 FROM t_bd_item_info WHERE item_subno = @itembarcode
					if @count > 1
					BEGIN
					   SELECT @msg = '存在多个自编码为' +@itembarcode + '的商品，无法销售！'
					   RAISERROR(@msg,16,1)
					   RETURN	
					end		
					IF @ls_itemno = '' OR @ls_itemno IS NULL
						BEGIN
							 SELECT @msg = '商品编码' +@itembarcode + '不存在'
							 RAISERROR(@msg,16,1)
							 RETURN	
						END
				END
		ENd
	ELSE
	BEGIN
		--直接是货号
		SET @ls_itemno = @itembarcode
		
    end
	
	select @ls_specflag=''
    select @branch_no = LEFT(@branch_no,6) 
    
    select @ls_trade_type = trade_type
       from t_bd_branch_info
        where branch_no = @branch_no

    -- 从(t_bd_item_info)中获取前台商品的基本信息
   select @ls_itemname=item_name, 
		@ls_comb =combine_sta ,
		@ln_basePrice=sale_price, 
    	@ln_salePrice=sale_price, 
    	@ln_vipPrice=vip_price, 
    	@ln_vipPrice2=vip_price2, 
    	@ln_vipPrice3=vip_price3, 
    	@ln_vipPrice4=vip_price4,
 
    	@ln_vipPrice5=vip_price5,
    	@ls_unit_no=unit_no,
    	@ls_item_size=item_size,
    	@item_brand = item_brand,
    	@item_cls_no = item_clsno
        from t_bd_item_info where item_no=@ls_itemno
    
	--取机构价格表信息
	if exists(select 1 from t_pc_branch_price where branch_no=Left(@branch_no, 6) and item_no=@ls_itemno) 
	begin
		select @ln_salePrice = sale_price, @ln_vipPrice = vip_price,@ln_vipPrice2 = vip_price2,
		@ln_vipPrice3 = vip_price3,@ln_vipPrice4 = vip_price4,@ln_vipPrice5 = vip_price5
		  from t_pc_branch_price
		 where branch_no=Left(@branch_no, 6) and item_no=@ls_itemno	
	end    
    
     --传入会员判断
	DECLARE @idoc int
	exec sp_xml_preparedocument @idoc output,@extend
	select @card_id = LTRIM(RTRIM(cardid))  from openxml(@idoc,'root',1)
	with (cardid varchar(50) 'cardid')
	IF @card_id is NULL
		SET @card_id = ''

    -- 特价,会员卡价处理....
    select @ln_specPrice = 0
    select @ln_cardPrice = 0
    select @vip_type =''
    IF @phone <> '' OR @card_id <> ''
	BEGIN
		IF @card_id = ''
			SELECT TOP 1 @vip_type = card_type,@card_id = card_id
			FROM t_rm_vip_info WHERE (card_status='0' OR card_status IS null)
			and mobile = @phone ORDER BY card_id ASC
		else
			SELECT TOP 1 @vip_type = card_type
			FROM t_rm_vip_info WHERE (card_status='0' OR card_status IS null) AND card_id = @card_id
			
		select @vip_type = isnull(LTRIM(rtrim(@vip_type)),'')
		if len(@vip_type) > 0
		BEGIN
			SELECT	@sale_way = ltrim(rtrim(sale_way)),
					@price_type =ltrim(rtrim(price_type)),
					@discount = discount
			FROM t_rm_vip_type
			WHERE [type_id] = @vip_type
			IF @discount IS NULL 
			   SELECT @discount = 1
			IF @sale_way = '0'
				SET @ln_vipPrice = @ln_salePrice * @discount
			IF @sale_way = '1'
			BEGIN
					IF @price_type = '1'
						SELECT @ln_vipPrice = @ln_vipPrice
					IF @price_type = '2'
						SELECT @ln_vipPrice = @ln_vipPrice2
					IF @price_type = '3'
						SELECT @ln_vipPrice = @ln_vipPrice3
					IF @price_type = '4'
						SELECT @ln_vipPrice = @ln_vipPrice4
					IF @price_type = '5'
						SELECT @ln_vipPrice = @ln_vipPrice5
				select @ln_cardPrice = @ln_vipPrice
			END
			IF @sale_way = '2'
			BEGIN
					IF @price_type = '1'
						SELECT @ln_vipPrice = @ln_vipPrice * @discount
					IF @price_type = '2'
						SELECT @ln_vipPrice = @ln_vipPrice2 * @discount
					IF @price_type = '3'
						SELECT @ln_vipPrice = @ln_vipPrice3 * @discount
					IF @price_type = '4'
						SELECT @ln_vipPrice = @ln_vipPrice4 * @discount
					IF @price_type = '5'
						SELECT @ln_vipPrice = @ln_vipPrice5 * @discount
			END
			
			--如果会员价为0，则跟前台一样，取销售价***2018-07-31**************--
			if @ln_vipPrice = 0 OR @ln_vipPrice IS NULL
				SELECT @ln_vipPrice = @ln_salePrice
		END
	END
	    
    --促销特价  
    --modify by panjq***2018-06-05******暂时只支持促销特价单、时段特价单，2种促销单
    if LEN(@vip_type) > 0 --会员  
		SELECT top 1 @ln_specPrice = ISNULL(spec_price,0),
					 @plan_no_spec =  ISNULL(sheet_no,''),
					 @rule_no_spec = ISNULL(trans_no,'')
		from(
			--促销特价单  
			select sheet_no,spec_price,trans_no 
			from (
				select top 1 a.sheet_no,b.new_price as spec_price,a.trans_no trans_no 
				from t_pc_price_flow_master a, t_pc_price_flow_detail b
				  where a.sheet_no = b.sheet_no
				  and a.approve_flag = '1' 
				  and  a.trans_no = 'PS' 
				  and  getdate() between a.valid_date and (convert(char(10),a.end_date,120)+' 23:59:59')
				  and ((DATEPART(dw, getdate())>1 and substring(a.week, DATEPART(dw, getdate()), 1)= '1') OR (DATEPART(dw, getdate())=1 and substring(a.week, 1, 1) = '1'))
				  and (branch_no = 'ALL' or charindex(left(@branch_no,3),branch_list) > 0)
				  and (vip_type is null or vip_type='' or vip_type = '无' or vip_type = 'Y' or vip_type = @vip_type)
				  and b.item_no = @ls_itemno
				  and exists(select 1 from t_rm_spec_price where sheet_no = a.sheet_no and branch_no = left(@branch_no,3))
				  order by new_price
			) t1
			--时段特价单
			union 
			select sheet_no,spec_price,trans_no 
			from (
				select top 1 a.sheet_no,b.new_price as spec_price,a.trans_no trans_no 
				from t_pc_price_flow_master a, t_pc_price_flow_detail b
				  where a.sheet_no = b.sheet_no
				  and a.approve_flag = '1' 
				  and  a.trans_no = 'PE' 
				  and  getdate() between a.valid_date and (convert(char(10),a.end_date,120)+' 23:59:59')
				  and ((DATEPART(dw, getdate())>1 and substring(a.week, DATEPART(dw, getdate()), 1)= '1') OR (DATEPART(dw, getdate())=1 and substring(a.week, 1, 1) = '1'))
				  and convert(char(8),getdate(),108) > = b.begin_time and convert(char(8),getdate(),108)<=b.end_time 
				  and (branch_no = 'ALL' or charindex(left(@branch_no,3),branch_list) > 0)
				  and (vip_type is null or vip_type='' or vip_type = '无' or vip_type = 'Y' or vip_type = @vip_type)
				  and b.item_no = @ls_itemno
				  and exists(select 1 from t_rm_spec_price where sheet_no = a.sheet_no and branch_no = left(@branch_no,3))
				  order by new_price
			) t2
			/*
			--类别品牌折扣单
			union 
			select sheet_no,spec_price from (
				select top 1 a.sheet_no,@ln_salePrice * b.discount as spec_price from t_pc_price_flow_master a, t_pc_price_flow_detail b
				  where a.sheet_no = b.sheet_no
				  and a.approve_flag = '1' 
				  and a.trans_no = 'PU' 
				  and ((b.price_type = '5' and charindex(@item_brand,b.item_no)>0)  or (b.price_type = '6' and charindex(@item_cls_no,b.item_no)>0))
				  and  getdate() between a.valid_date and (convert(char(10),a.end_date,120)+' 23:59:59')
				  and ((DATEPART(dw, getdate())>1 and substring(a.week, DATEPART(dw, getdate()), 1)= '1') OR (DATEPART(dw, getdate())=1 and substring(a.week, 1, 1) = '1'))
				  and (branch_no = 'ALL' or charindex(left(@branch_no,3),branch_list) > 0)
				  and (vip_type is null or ltrim(rtrim(vip_type))='' or vip_type = '无' or vip_type = 'Y' or vip_type = @vip_type)
				  and exists(select 1 from t_rm_spec_price where sheet_no = a.sheet_no and branch_no = left(@branch_no,3))
				  order by discount	
			  )	t3		
			  */
		  )	x order by spec_price
		  
    else --非会员
		select top 1 @ln_specPrice = ISNULL(spec_price,0),
					 @plan_no_show =  ISNULL(sheet_no,''),
					 @rule_no = ISNULL(trans_no,'') 
		from(
			--促销特价单  
			select sheet_no,spec_price,trans_no 
			from (
				select top 1 a.sheet_no,b.new_price as spec_price,a.trans_no trans_no
				from t_pc_price_flow_master a, t_pc_price_flow_detail b
				  where a.sheet_no = b.sheet_no
				  and a.approve_flag = '1' 
				  and  a.trans_no = 'PS' 
				  and  getdate() between a.valid_date and (convert(char(10),a.end_date,120)+' 23:59:59')
				  and ((DATEPART(dw, getdate())>1 and substring(a.week, DATEPART(dw, getdate()), 1)= '1') OR (DATEPART(dw, getdate())=1 and substring(a.week, 1, 1) = '1'))
				  and (branch_no = 'ALL' or charindex(left(@branch_no,3),branch_list) > 0)
				  and (vip_type is null or vip_type='' or vip_type = '无' or vip_type = 'N')
				  and b.item_no = @ls_itemno
				  and exists(select 1 from t_rm_spec_price where sheet_no = a.sheet_no and branch_no = left(@branch_no,3))
				  order by new_price
			) t1
			--时段特价单
			union 
			select sheet_no,spec_price,trans_no 
			from (
				select top 1 a.sheet_no,b.new_price as spec_price,a.trans_no trans_no 
				from t_pc_price_flow_master a, t_pc_price_flow_detail b
				  where a.sheet_no = b.sheet_no
				  and a.approve_flag = '1' 
				  and  a.trans_no = 'PE' 
				  and  getdate() between a.valid_date and (convert(char(10),a.end_date,120)+' 23:59:59')
				  and ((DATEPART(dw, getdate())>1 and substring(a.week, DATEPART(dw, getdate()), 1)= '1') OR (DATEPART(dw, getdate())=1 and substring(a.week, 1, 1) = '1'))
				  and convert(char(8),getdate(),108) > = b.begin_time and convert(char(8),getdate(),108)<=b.end_time 
				  and (branch_no = 'ALL' or charindex(left(@branch_no,3),branch_list) > 0)
				  and (vip_type is null or vip_type='' or vip_type = '无' or vip_type = 'N')
				  and b.item_no = @ls_itemno
				  and exists(select 1 from t_rm_spec_price where sheet_no = a.sheet_no and branch_no = left(@branch_no,3))
				  order by new_price
			) t2
			--类别品牌折扣单
			/*
			union 
			select sheet_no,spec_price from (
				select top 1 a.sheet_no,@ln_salePrice * b.discount as spec_price from t_pc_price_flow_master a, t_pc_price_flow_detail b
				  where a.sheet_no = b.sheet_no
				  and a.approve_flag = '1' 
				  and a.trans_no = 'PU' 
				  and ((b.price_type = '5' and charindex(@item_brand,b.item_no)>0)  or (b.price_type = '6' and charindex(@item_cls_no,b.item_no)>0))
				  and  getdate() between a.valid_date and (convert(char(10),a.end_date,120)+' 23:59:59')
				  and ((DATEPART(dw, getdate())>1 and substring(a.week, DATEPART(dw, getdate()), 1)= '1') OR (DATEPART(dw, getdate())=1 and substring(a.week, 1, 1) = '1'))
				  and (branch_no = 'ALL' or charindex(left(@branch_no,3),branch_list) > 0)
				  and (vip_type is null or ltrim(rtrim(vip_type))='' or vip_type = '无' or vip_type = 'N')
				  and exists(select 1 from t_rm_spec_price where sheet_no = a.sheet_no and branch_no = left(@branch_no,3))
				  order by discount	
			  )	t3	*/	
		  )	x where spec_price > 0 order by spec_price
    
    
	--移到促销前面
    if len(@vip_type)> 0 and (not @ln_vipPrice is null) and  @ln_vipPrice < @ln_salePrice
    begin
       select @ln_salePrice = @ln_vipPrice
       SET @rule_no = 'PV'
       SET @plan_no_show = ''
    END
       
    if @ln_specPrice > 0  and (not @ln_specPrice is null) and @ln_specPrice < @ln_salePrice
    begin
       select @ln_salePrice = @ln_specPrice
       SELECT @plan_no_show = @plan_no_spec
       SELECT @rule_no = @rule_no_spec
    END
	
	IF @rule_no IS NULL OR LEN(@rule_no) <=0 
		SET @rule_no = ''

    IF @plan_no_show IS NULL OR LEN(@plan_no_show) <=0 
		SET @plan_no_show = ''
		
    -- 返回值(单个商品信息、价格、商品条形码、商品名称、商品原价、商品售价、单位、规格)
   select @itembarcode as itembarcode,
	  @ls_itemno as item_no,
      @ls_itemname as item_name,
      @ln_basePrice as ordernum,
      @ln_salePrice as saleprice,
      @ls_unit_no	as unit,
      @ls_item_size as standard,
	  @card_id as	card_id,
	  @plan_no_show AS plan_no,
	  @rule_no AS spec_flag
end
go
