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

 create proc pr_wxsf_item_query
 (
 @oper_type nvarchar(20),
 @item_no nvarchar(20),
 @branch_no nvarchar(20),
 @item_clsno nvarchar(20)
 )
  WITH ENCRYPTION
 as
  begin
	--2016-2-25，LIUW,查出二级类别
	declare @gl_itm_cls_nolen int   
	select @gl_itm_cls_nolen=2  
	if exists (select 1 from t_sys_system where sys_var_id='gl_itm_cls_nolen')
	begin
		select @gl_itm_cls_nolen=sys_var_value from t_sys_system where sys_var_id='gl_itm_cls_nolen'
	end	
	select @gl_itm_cls_nolen=@gl_itm_cls_nolen*2  	
		
	-- 0 单品查询，1 商品导出,2 按类别查询
	if @oper_type='0' 
		SELECT   
		a.[item_no],a.[item_name],LEFT(a.item_clsno,@gl_itm_cls_nolen) as [type_no],b.item_clsname as [type_name],a.sale_price as [item_price],
		item_size as [item_spec],unit_no as [item_unit],
		item_rem  as item_spell, other1 as item_desc,vip_price as item_mprice,sale_price as item_oldprice,'00' as branch_no,a.item_subno,isnull(c.stock_qty,0) as stock  , 
				ISNULL(a.vip_price1, 0) AS vip_price1 ,
				ISNULL(a.vip_price2, 0) AS vip_price2 ,
				0.00 AS vip_price3 ,
				0.00 AS vip_price4 ,
				0.00 AS vip_price5 ,
				ISNULL(a.base_price1, 0) AS wholesale_price1 ,
				ISNULL(a.base_price2, 0) AS wholesale_price2
		from t_bd_item_info a 	
				left join t_bd_item_cls b on a.item_clsno=b.item_clsno			 
				left join (select item_no,SUM(stock_qty)as stock_qty from t_im_branch_stock where item_no=@item_no group by item_no) c
					on a.item_no = c.item_no
		where a.item_no=@item_no  
		and a.status<>'2'
	else if @oper_type='1' 
		SELECT   a.[item_no],[item_name],LEFT(a.item_clsno,@gl_itm_cls_nolen) as [type_no],b.item_clsname as [type_name],sale_price as [item_price],
		item_size as [item_spec],unit_no as [item_unit],
		item_rem  as item_spell, other1 as item_desc, vip_price as item_mprice, sale_price as item_oldprice,'00' as branch_no,a.item_subno ,isnull(c.stock_qty,0) as stock   , 
				ISNULL(a.vip_price1, 0) AS vip_price1 ,
				ISNULL(a.vip_price2, 0) AS vip_price2 ,
				0.00 AS vip_price3 ,
				0.00 AS vip_price4 ,
				0.00 AS vip_price5 ,
				ISNULL(a.base_price1, 0) AS wholesale_price1 ,
				ISNULL(a.base_price2, 0) AS wholesale_price2
		from t_bd_item_info a 	
				left join t_bd_item_cls b on a.item_clsno=b.item_clsno
				left join (select item_no,SUM(stock_qty)as stock_qty from t_im_branch_stock group by item_no) c
					on a.item_no = c.item_no
		where a.status<>'2'
	else if @oper_type='2' 
		SELECT   
		a.[item_no],a.[item_name],LEFT(a.item_clsno,@gl_itm_cls_nolen) as [type_no],b.item_clsname as [type_name],a.sale_price as [item_price],
		item_size as [item_spec],unit_no as [item_unit],
		item_rem  as item_spell, other1 as item_desc, vip_price as item_mprice, sale_price as item_oldprice,'000' as branch_no,a.item_subno ,isnull(c.stock_qty,0) as stock 	 , 
				ISNULL(a.vip_price1, 0) AS vip_price1 ,
				ISNULL(a.vip_price2, 0) AS vip_price2 ,
				0.00 AS vip_price3 ,
				0.00 AS vip_price4 ,
				0.00 AS vip_price5 ,
				ISNULL(a.base_price1, 0) AS wholesale_price1 ,
				ISNULL(a.base_price2, 0) AS wholesale_price2  
		from t_bd_item_info  a 	
				inner join t_bd_item_cls b on a.item_clsno=b.item_clsno	  
				left join (select item_no,SUM(stock_qty)as stock_qty from t_im_branch_stock group by item_no) c
					on a.item_no = c.item_no		  
		where ISNULL(a.item_clsno,'zz') like @item_clsno+'%' 
		and a.status<>'2'
	else if @oper_type='4' --@item_no传入调价单号
		SELECT b.item_no,item_name,LEFT(b.item_clsno,@gl_itm_cls_nolen) as [type_no],c.item_clsname as [type_name],b.sale_price as [item_price],
		item_size as [item_spec],unit_no as [item_unit],
		item_rem  as item_spell, b.other1 as item_desc, 
		b.vip_price as item_mprice,a.old_price3 as item_oldprice,'00' as branch_no,b.item_subno,
		case b.combine_sta when '7' then '1' else '0' end as isCertBag ,
		b.item_brand as brandno,
		0 as stock , 
				ISNULL(b.vip_price1, 0) AS vip_price1 ,
				ISNULL(b.vip_price2, 0) AS vip_price2 ,
				0.00 AS vip_price3 ,
				0.00 AS vip_price4 ,
				0.00 AS vip_price5 ,
				ISNULL(b.base_price1, 0) AS wholesale_price1 ,
				ISNULL(b.base_price2, 0) AS wholesale_price2
		FROM t_pc_price_flow_detail a 
		left join t_bd_item_info b on a.item_no = b.item_no
		left join t_bd_item_cls c on b.item_clsno=c.item_clsno
	    WHERE sheet_no = @item_no
	    AND ISNULL(b.status, '0')<>'2' AND b.combine_sta='0'
 end
go