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


create procedure pr_bossrpt_sale_analyze
(
	@as_branchlist  varchar(255),
	@adt_from 		datetime,
	@adt_to 		datetime,
	@as_type 		char(1), --0 按销量 1 按销售额
	@PerNum			int,
	@PageNum		int,
	@totCnt			int Output
)
as
begin
	--declare as_type char(1) --0 按销量 1 按销售额
	declare 
		@err_msg varchar(255),
		@b int,
		@e int
	
	if isnull(@as_type,'')=''
		set @as_type = '0'
	
	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
	
	if 	@as_type = '0'  --按销量
		begin
			SELECT   IDENTITY(numeric(18,0),1,1) as rownumber,
					 a.item_no as ItemNo,
					 b.item_name as ItemName,			
					 sum(so_qty + pos_qty) as Qty,   
					 sum(so_amt + pos_amt) as Amt
			INTO #result_sheet1
			FROM     t_da_jxc_daysum a,
					 t_bd_item_info b
			WHERE 	( a.item_no = b.item_no) and
					( a.oper_date between convert(char(10),@adt_from,121) and convert(char(10),@adt_to,121) )  and 
					(so_qty <>0 or pos_qty <>0  or  so_amt <>0 or pos_amt <>0  or  a.avg_profit_amt<>0)  and
					charindex(rtrim(a.branch_no),@as_branchlist) > 0																		
			GROUP BY a.item_no,   
					 b.item_name
			ORDER BY sum(so_qty + pos_qty) desc,
					 sum(so_amt + pos_amt) desc	
			
			set @totCnt = @@rowcount

			select ItemNo,ItemName,Qty,Amt
			from #result_sheet1 
			where rownumber between @b and @e
			
			drop table #result_sheet1
		end
	else --按销售额
		begin
			SELECT   IDENTITY(numeric(18,0),1,1) as rownumber,
					 a.item_no as ItemNo,
					 b.item_name as ItemName,			
					 sum(so_qty + pos_qty) as Qty,   
					 sum(so_amt + pos_amt) as Amt
			INTO #result_sheet2
			FROM     t_da_jxc_daysum a,
					 t_bd_item_info b
			WHERE 	( a.item_no = b.item_no) and
					( a.oper_date between convert(char(10),@adt_from,121) and convert(char(10),@adt_to,121) ) and 
					(so_qty <>0 or pos_qty <>0  or  so_amt <>0 or pos_amt <>0 or  a.avg_profit_amt<>0) and
					charindex(rtrim(a.branch_no),@as_branchlist) > 0																		
			GROUP BY a.item_no,   
					 b.item_name
			ORDER BY sum(so_amt + pos_amt) desc,	
					 sum(so_qty + pos_qty) desc
					 
			set @totCnt = @@rowcount
			
			select ItemNo,ItemName,Qty,Amt
			from #result_sheet2 
			where rownumber between @b and @e
			
			drop table #result_sheet2
		end
	
	return 

err:
	RAISERROR( @err_msg , 16, 1)

end
go