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


create procedure pr_bossrpt_taxis_baditem
(
	@as_branchlist  varchar(255),
	@adt_from 		datetime,
	@adt_to 		datetime,
	@orderFlag		char,			--A 金额，Q 数量，D 滞销天数 (按倒序)
	@PerNum			int,
	@PageNum		int,
	@totCnt			int Output
)
as
begin
	declare
		@err_msg varchar(255),
		@b int,
		@e int,
		@as_from char(10),
		@as_to char(10),
		@as_to2 char(10)
	
	if isNull(@PerNum, 0) <= 0 
	begin
		set @err_msg = '每页显示数量必须大于零！'
		goto err
	end

	if isNull(@PageNum, 0) <= 0 
	begin
		set @err_msg = '页码必须大于零！'
		goto err
	end
	
	set @b = (@PageNum - 1) * @PerNum + 1
	set @e = @PageNum * @PerNum
	
	select @as_from = convert(char(10),@adt_from,121),@as_to = convert(char(10),@adt_to,121),@as_to2 = 	convert(char(10),GETDATE(),121)
	select @as_from = DATEADD(DAY,-30,@adt_from) -- 减30天
	
	if @orderFlag = 'A'
		begin
			select	IDENTITY(numeric(18,0),1,1) as rownumber,
					 ItemNo,ItemName,UnusualDay,StockQty,StockAmt
			into	#result_sheet1
			from (	
					select b.item_no as ItemNo,
						a.item_name as ItemName,
						max(UnusualDay) as UnusualDay,
						sum(stock_qty) as StockQty,
						sum(cost_amt) as StockAmt
					from t_bd_item_info a,
					(
						select branch_no,
							   item_no,
							   sum(so_qty + pos_qty) as sale_qty,
							   sum(so_amt + pos_amt) as sale_amt,
							   datediff(dd,max(oper_date),@as_to2) as UnusualDay  --滞销天数=截止日期-最近日期
						from   t_da_jxc_daysum  a
						where  a.oper_date <= @as_to and 
							   a.oper_date >= @as_from and -- 减30天
							--( a.oper_date between convert(char(10),@adt_from,121) and  convert(char(10),@adt_to,121) )  and 
							   charindex(rtrim(a.branch_no),@as_branchlist) > 0 
						group by branch_no,item_no 
						having sum(a.so_qty+a.pos_qty) > 0
					) b,
					t_im_branch_stock c		
					where b.branch_no = left(c.branch_no,2)
					and b.item_no = c.item_no
					and a.item_no = b.item_no
					and UnusualDay > 0
					group by b.item_no,a.item_name	
			) t
			order by StockAmt DESC
			
			set @totCnt = @@rowcount
			
			select ItemNo,ItemName,UnusualDay,StockQty,StockAmt
			from #result_sheet1 
			where rownumber between @b and @e
			
			drop table #result_sheet1 
		end
	else if @orderFlag ='Q'
		begin
			select	IDENTITY(numeric(18,0),1,1) as rownumber,
					 ItemNo,ItemName,UnusualDay,StockQty,StockAmt
			into	#result_sheet2
			from (	
					select b.item_no as ItemNo,
						a.item_name as ItemName,
						max(UnusualDay) as UnusualDay,
						sum(stock_qty) as StockQty,
						sum(cost_amt) as StockAmt
					from t_bd_item_info a,
					(
						select branch_no,
							   item_no,
							   sum(so_qty + pos_qty) as sale_qty,
							   sum(so_amt + pos_amt) as sale_amt,
							   datediff(dd,max(oper_date),@as_to2) as UnusualDay  --滞销天数=截止日期-最近日期
						from   t_da_jxc_daysum  a
						where  a.oper_date <= @as_to and 
							   a.oper_date >= @as_from and -- 减30天
							--( a.oper_date between convert(char(10),@adt_from,121) and  convert(char(10),@adt_to,121) )  and 
							   charindex(rtrim(a.branch_no),@as_branchlist) > 0 
						group by branch_no,item_no 
						having sum(a.so_qty+a.pos_qty) > 0
					) b,
					t_im_branch_stock c		
					where b.branch_no = left(c.branch_no,2)
					and b.item_no = c.item_no
					and a.item_no = b.item_no
					and UnusualDay > 0
					group by b.item_no,a.item_name	
			) t
			order by StockQty DESC
			
			set @totCnt = @@rowcount
			
			select ItemNo,ItemName,UnusualDay,StockQty,StockAmt
			from #result_sheet2
			where rownumber between @b and @e
			
			drop table #result_sheet2
		end
	else
		begin
			select	IDENTITY(numeric(18,0),1,1) as rownumber,
					 ItemNo,ItemName,UnusualDay,StockQty,StockAmt
			into	#result_sheet3
			from (	
					select b.item_no as ItemNo,
						a.item_name as ItemName,
						max(UnusualDay) as UnusualDay,
						sum(stock_qty) as StockQty,
						sum(cost_amt) as StockAmt
					from t_bd_item_info a,
					(
						select branch_no,
							   item_no,
							   sum(so_qty + pos_qty) as sale_qty,
							   sum(so_amt + pos_amt) as sale_amt,
							   datediff(dd,max(oper_date),@as_to2) as UnusualDay  --滞销天数=截止日期-最近日期
						from   t_da_jxc_daysum  a
						where  a.oper_date <= @as_to and 
							   a.oper_date >= @as_from and -- 减30天
							--( a.oper_date between convert(char(10),@adt_from,121) and  convert(char(10),@adt_to,121) )  and 
							   charindex(rtrim(a.branch_no),@as_branchlist) > 0 
						group by branch_no,item_no 
						having sum(a.so_qty+a.pos_qty) > 0
					) b,
					t_im_branch_stock c		
					where b.branch_no = left(c.branch_no,2)
					and b.item_no = c.item_no
					and a.item_no = b.item_no
					and UnusualDay > 0
					group by b.item_no,a.item_name	
			) t
			order by UnusualDay DESC
			
			set @totCnt = @@rowcount
			
			select ItemNo,ItemName,UnusualDay,StockQty,StockAmt
			from #result_sheet3
			where rownumber between @b and @e
			
			drop table #result_sheet3
		end
	
	return
	
err:
	RAISERROR( @err_msg , 16, 1)
	
end
go
