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

create proc pr_wxsf_item_query_new
 (
    @starttime varchar(50), --创建开始时间
    @endtime varchar(50),--创建结束时间
    @bigcategory varchar(20),--大类
    @smallcategory varchar(20),--小类
    @brandno  varchar(20), --品牌
    @pageindex  int,--第几页
    @pagesize  int, --每页条数
    @item_no varchar(40),--编号
    @totalcount  int output,  --总条数
    @returntotal varchar(1)='1', --‘1’返回总条数
    @zbm varchar(1)='0'
 )
  WITH ENCRYPTION
 as
 begin
        declare 
        @exec_sql    nvarchar(3000),
        @total_sql        nvarchar(3000),
        @beginPosition int, --当前开始 
        @endPosition int, --当前结束 
        @sSql_with        nvarchar(3000), --查询商品 
        @pageCount        int, --页数 
        @total            int --商品总数 

		if @bigcategory is null
			set @bigcategory = '%'
		if @smallcategory is null
			set @smallcategory = '%'
		if @item_no is null
			set @item_no = '%'
		if @brandno is null
			set @brandno = '%'
		
		if @starttime is null or @starttime = ''
			set @starttime = '1900-01-01'	
		if @endtime is null or @endtime = ''
			set @endtime = convert(varchar(10),getdate(),121)	

		declare @gl_itm_cls_nolen_str varchar(20)
		select @gl_itm_cls_nolen_str='2'  
		if exists (select 1 from t_sys_system where sys_var_id='gl_itm_cls_nolen')
		begin
			select @gl_itm_cls_nolen_str=sys_var_value from t_sys_system where sys_var_id='gl_itm_cls_nolen'
		end	
		select @gl_itm_cls_nolen_str = rtrim(@gl_itm_cls_nolen_str)

		SELECT identity(int,1,1) as row_id,  
			a.[item_no],a.[item_name],LEFT(a.item_clsno,@gl_itm_cls_nolen_str) as [type_no],b.item_clsname as [type_name],a.sale_price as [item_price],
			isnull(item_size,'') as [item_spec],isnull(unit_no,'') as [item_unit],a.item_brand as brandno,vip_price as vip_price,vip_price1 as vip_price1,
			vip_price2 as vip_price2,0 as vip_price3,0 as vip_price4,0 as vip_price5,base_price as base_price,wholesale_price1 = base_price1, wholesale_price2 = base_price2,
			item_rem  as item_spell,'' as item_desc,vip_price as item_mprice,sale_price as item_oldprice,'00' as branch_no,a.item_subno,
			'0' as isCertBag  
		into #base      
	        from t_bd_item_info a with(nolock)
	            left join t_bd_item_cls b with(nolock) on a.item_clsno=b.item_clsno     
	        where
		( 
			@item_no = '%'
			or 
			(@item_no <> '%' and ((a.item_no = @item_no and @zbm = '0') or (a.item_subno=@item_no and @zbm = '1')))
		)
		and
		(
			@starttime = '%'
			or
			(@starttime <> '%' and a.build_date>= @starttime)
		) 
		and 
		(
			@endtime = '%'
			or
			(@endtime <> '%' and a.build_date <= @endtime)
		)
		and
		(
			@bigcategory = '%'
			or 
			(@bigcategory <> '%' and a.item_clsno like @bigcategory + '%')
		)
		and
		(
			@smallcategory = '%'
			or 
			(@smallcategory <> '%' and a.item_clsno like @smallcategory + '%')
		)
		and 
		(
			@brandno = '%'
			or
			(@brandno <> '%' and a.item_brand like @brandno + '%')			
		)
		and ISNULL(a.status,'0') = '1'
		        
		if @returntotal='1'
        begin
			
			select @total = count(1) from #base
			if @total is null
				set @total = 0

			set @totalcount = @total
        end
        
		 select @pageCount = ceiling(case when @total > 0 then convert(numeric(12,2),@total) / @pageSize else 0 end);
        select @pageIndex = case when @pageIndex > @pageCount then @pageCount when @pageIndex<1 then 1 else @pageIndex end;
		select @beginPosition = ( (@pageIndex-1) * @pageSize ) +1, @endPosition = (@pageIndex * @pageSize);

		select * from #base
		where row_id between convert(varchar(20),@beginPosition)  and convert(varchar(20),@endPosition)

		drop table #base
 end
go