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


create procedure pr_wxasf_freshitem_query(
	@as_branchno	char(6),		--机构
	@as_itemno		varchar(40),	--生鲜码
	@as_cardno		varchar(20)		--会员卡号
)
as
begin
	declare @item_no varchar(40),
			@card_type char(2),				--会员类型
			@sale_way char(1),				--优惠方式：0 折扣/1 会员价
			@discount decimal(5,4),			--折扣
			@discount2 decimal(5,4),		--会员日折扣
			@source_price numeric(16,4),	--原价
			@spec_price numeric(16,4),		--特价
			@sale_price numeric(16,4),		--售价
			@sale_amt numeric(16,4), 		--金额
			@sale_qty numeric(16,4), 		--数量
			@num3 numeric(16,4), 			--计数商品
			@fresh_mark varchar(4),		--生鲜标识
			@fresh_type varchar(4),			--生鲜码类型：0 金额码/1 重量码
			@freshamt_precision varchar(20),--金额精度
			@freshwgt_precision varchar(20),--重量精度
			@pos_plu_freshweight varchar(20),
			@freshqty_newpcs varchar(20),	--计数商品重取重量精度
			@freshqty_precision varchar(20),--计数商品重量精度
			@flag char(1),
			@spec_flag char(2),				--特价标志
			@spec_sheet_no char(20),			--特价单据
			@extend varchar(50)
	
	if ISNULL(@as_itemno,'') = '' return
	If ISNULL(@as_branchno, '') = '' return
	if ISNULL(@as_cardno,'') = '' set @as_cardno = '%'
	select @fresh_mark = ''
	
	if LEN(RTRIM(@as_itemno)) = 13 or LEN(RTRIM(@as_itemno)) = 18
	begin
		select @fresh_mark = isnull(sys_var_value,'22') from t_sys_system where sys_var_id = 'pos_plu_freshno'	--生鲜码标识
		if LEFT(@as_itemno,LEN(@fresh_mark)) = @fresh_mark
		begin
			if LEN(@fresh_mark) = 1
				set @item_no = substring(@as_itemno,2,6)
			else if LEN(@fresh_mark) = 2
				set @item_no = substring(@as_itemno,3,5)
			
			--是否重量码
			select @fresh_type = LEFT(isnull(sys_var_value,'00'),1)
			from t_sys_system
			where sys_var_id = 'pos_plu_id_format'
			
			select @freshamt_precision = (case sys_var_value when '0' then '1'
															when '1' then '10'
															when '2' then '100'
															when '3' then '1000'
															else '100'end)
			from t_sys_system
			where sys_var_id = 'pos_plu_freshamt' --金额精度
			
			if @freshamt_precision is null --未设置电子秤参数时
				set @freshamt_precision ='100'
			
			select @freshwgt_precision = (case sys_var_value when '0' then '1'
															when '1' then '10'
															when '2' then '100'
															when '3' then '1000'
															when '4' then '10000'
															else '100'end)
			from t_sys_system
			where sys_var_id = 'pos_plu_freshqty' --重量精度
			
			If @freshwgt_precision is null	--未设置电子秤参数时
				set @freshwgt_precision = '100'
			
			
			select @freshqty_newpcs = LEFT(sys_var_value,1)
			from t_sys_system
			where sys_var_id = 'pos_plu_freshqty_js' --是否勾选计数商品重取重量精度
			
			If @freshqty_newpcs is null
				set @freshqty_newpcs = '0'
				
			select @freshqty_precision = (case RIGHT(sys_var_value,1) when '0' then '1'
															when '1' then '10'
															when '2' then '100'
															when '3' then '1000'
															when '4' then '10000'
															else '100'end)
			from t_sys_system
			where sys_var_id = 'pos_plu_freshqty_js' --计数商品重量精度
			
			if @freshqty_precision is null --未设置电子秤参数时
				set @freshqty_precision ='100'
			
			select @source_price = sale_price from t_pc_branch_price where branch_no = @as_branchno and item_no = @item_no
			if @source_price is null or @source_price = 0
				select @source_price = sale_price from t_bd_item_info where item_no = @item_no
			set @sale_price = @source_price
			
			if LEN(RTRIM(@as_itemno)) = 13
				begin
					if @fresh_type = '0' --金额码
						begin
							set @flag = '1' --后面反算数量用
							if LEN(@fresh_mark) = 1 --1位标识码
							begin
								set @sale_amt = convert(decimal(16,4),substring(@as_itemno,8,5)) / @freshamt_precision
							end
							else --2位标识码
							begin
								set @sale_amt = convert(decimal(16,4),substring(@as_itemno,8,5)) / @freshamt_precision
							end
						end
					else --重量码
						begin
							set @flag = '2' --后面反算金额用
							if LEN(@fresh_mark) = 1  --1位标识码
								begin
							
		select @num3 = num3 from t_bd_item_info where item_no = @item_no
									if @num3 = 2 --计数商品
									begin
										if @freshqty_newpcs = '1' --勾选了计数商品重取重量精度
											set @freshwgt_precision = @freshqty_precision --计数商品重量精度
										else
											set @freshwgt_precision = '1' --计数，也就是1(个)
									end
									
									set @sale_qty = convert(decimal(16,4),substring(@as_itemno,8,5)) / @freshwgt_precision
								end
							else --2位标识码
								begin
									select @num3 = num3 from t_bd_item_info where item_no = @item_no
									if @num3 = 2 --计数商品
									begin
										if @freshqty_newpcs = '1' --勾选了计数商品重取重量精度
											set @freshwgt_precision = @freshqty_precision --计数商品重量精度
										else
											set @freshwgt_precision = '1' --计数，也就是1(个)
									end
									
									set @sale_qty = convert(decimal(16,4),substring(@as_itemno,8,5)) / @freshwgt_precision
								end
						end
				end
			else --18位：固定 1/2位标识+6/5位PLU+5位金额+5位重量+1位校验
				begin
					select @num3 = num3 from t_bd_item_info where item_no = @item_no
					if @num3 = 2
					begin
						if @freshqty_newpcs = '1' --勾选了计数商品重取重量精度
							set @freshwgt_precision = @freshqty_precision --计数商品重量精度
						else
							set @freshwgt_precision = '1' --计数，也就是1(个)
					end
					
					set @sale_amt = convert(decimal(16,4),substring(@as_itemno,8,5)) / @freshamt_precision
					set @sale_qty = convert(decimal(16,4),substring(@as_itemno,13,5)) / @freshwgt_precision
					set @sale_price = @sale_amt / @sale_qty
				end
		end
	end
	else
		goto error
	
	-- 返回值(单个商品条形码、商品货号、商品原价、商品售价、单位、规格、会员卡ID、特价单据号、特价类型)
	create table #item_info (itembarcode varchar(50), item_no varchar(20), item_name varchar(40), ordernum decimal(18, 4), saleprice decimal(18, 4),
		unit char(4), item_size varchar(20), card_id varchar(20), spec_flag char(3), plan_no varchar(20))
	
	select @extend = '<root><cardid>' + @as_cardno + '</cardid></root>'
	
	insert into #item_info exec pr_wxasf_item_query @item_no, @as_branchno, '', @extend
	select @spec_price = saleprice, @spec_flag = lower(spec_flag), @spec_sheet_no = plan_no from #item_info
	
	--13位金额码反算重量
	if @flag = '1'
	begin
		if @spec_price < @source_price set @sale_price = @spec_price
		set @sale_qty = @sale_amt / (case when @sale_price = 0 then 1 else @sale_price end)
	end
	
	--13位重量码反算金额
	if @flag = '2'
	begin
		if @spec_price < @source_price set @sale_price = @spec_price
		set @sale_amt = @sale_qty * @sale_price
		
		select @pos_plu_freshweight = sys_var_value from t_sys_system where sys_var_id = 'pos_plu_freshweight'
		if left(@pos_plu_freshweight,1) = '1'
		begin
			set @pos_plu_freshweight = SUBSTRING(@pos_plu_freshweight,3,LEN(@pos_plu_freshweight))
			set @sale_amt = ROUND(@sale_amt,cast(@pos_plu_freshweight as int))
		end
	end
	
	if ISNULL(@sale_price,0) = 0 set @sale_price = @source_price
	--线上要求金额为2位小数位 zhangjr 20181018
	set @source_price =ROUND(@source_price,2)
	set @sale_price =ROUND(@sale_price,2)
	set @sale_amt =ROUND(@sale_amt,2)
	
	--返回值：机构、货号、品名、重量(数量)、金额、原价(金额/重量)、特价、单位、规格
	select @as_branchno as branch_no,
			@item_no as item_no,
			(select item_name from t_bd_item_info where item_no = @item_no) as item_name,
			@source_price as source_price,
			@sale_price as sale_price,
			@sale_qty as sale_qty,
			@sale_amt as sale_amt,
			(select unit_no from t_bd_item_info where item_no = @item_no) as unit_no,
			(select item_size from t_bd_item_info where item_no = @item_no) as item_size,
			@spec_sheet_no as plan_no,	--促销单号plan_no
			@spec_flag as spec_flag		--促销类型
	return
	error:
		RAISERROR('当前商品不是生鲜码商品！',16,1)
end
