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

create proc pr_wxsf_bossrpt
(
	@oper_type nvarchar(20),
	@by_type nvarchar(20),		 --h按小时，d按天,asc 升序,desc 降序,num_desc 数量降序，amt_desc 金额降序
	@time_begin datetime,
	@time_end datetime,	
	@item_no nvarchar(50),
	@branch_no nvarchar(50)
)
as
begin 
	 --当前SQL是专卖9的表结构，其它产品按自己表结构更改
	 
	 --@oper_type:1 时段新增会员数量、2 会员储值消费金额、3 会员充值金额、4 前台每种付款方式销售额。5 商品销量排行(不统计退货),6 门店销售额排行(不统计退货),7 商品查询
	 --8 大类销售排行(不统计退货，一级类别) 
	 
	 --返回列：time_quantum 时间段 (格式:yyyy-MM-dd或H),num 数量/金额,pay_name 支付方式名称,amt 金额
	 --或其它列名
	 
	 if @oper_type='1'
	 begin
		 --有些产品oper_date只到日期部分，所以无法统计每小时新增会员数量。只能按天
		if @by_type='d'
		begin			
			select COUNT(1) as num,CONVERT(varchar(10),oper_date,121) as time_quantum  from t_rm_vip_info with(nolock) where oper_date between @time_begin and @time_end
			 group by   CONVERT(varchar(10),oper_date,121)
			return
		end
	 end
	 --会员储值消费金额
	 if @oper_type='2'
	 begin
		if @by_type='h'
		begin			
			select sum(amount) as num,datepart(HH,pay_time) as time_quantum  
			from t_rm_card_paylist with(nolock) 
			where pay_time between @time_begin and @time_end
			and card_type = '2'
			group by   datepart(HH,pay_time)
			return
		end
		if @by_type='d'
		begin			
			select sum(amount) as num,CONVERT(varchar(10),pay_time,121) as time_quantum  
			from t_rm_card_paylist with(nolock) 
			where pay_time between @time_begin and @time_end
			and card_type = '2'
			group by   CONVERT(varchar(10),pay_time,121)
			return
		end
	 end
	 --会员充值金额
	 if @oper_type='3'
	 begin
		if @by_type='h'
		begin			
			select sum(plus_count) as num,datepart(HH,oper_date) as time_quantum  
			from t_rm_saving_plus_record with(nolock) 
			where oper_date between @time_begin and @time_end
			group by   datepart(HH,oper_date)
			return
		end
		if @by_type='d'
		begin			
			select sum(plus_count) as num,CONVERT(varchar(10),oper_date,121) as time_quantum  
			from t_rm_saving_plus_record with(nolock) 
			where oper_date between @time_begin and @time_end
			group by   CONVERT(varchar(10),oper_date,121)
			return
		end
	 end
	 --前台每种付款方式销售额(不统计退货)
	 if @oper_type='4'
	 --20170619 tansh 改为统计退货，并且不统计前台充值
	 begin		
		select sum(case when sell_way='B' then -pay_amount else pay_amount end) as num,b.pay_name,a.pay_way  from t_rm_payflow a with(nolock)
		inner join t_bd_payment_info b with(nolock) on b.pay_flag='0' and a.pay_way=b.pay_way
		where oper_date between @time_begin and @time_end and isnull(trans_type,'') <> 'TTP' and isnull(memo,'') not like '前台充值%' 
		group by   b.pay_name,a.pay_way
		return
	 end
	 
	 --商品销量排行(不统计退货)
	 --20170619 tansh 改为统计退货
	 if @oper_type='5'
	 begin
		if @by_type='asc'
		begin			
			select top 20 isnull(sum(case when sell_way='B' then -sale_qnty else sale_qnty end),0) as num,a.item_no,b.item_name  from t_rm_saleflow a with(nolock)
			inner join t_bd_item_info b with(nolock) on a.item_no=b.item_no
			where oper_date between @time_begin and @time_end
			group by   a.item_no,b.item_name  order by num asc
			 return
		end
		if @by_type='desc'
		begin			
			select top 20 isnull(sum(case when sell_way='B' then -sale_qnty else sale_qnty end),0) as num,a.item_no,b.item_name  from t_rm_saleflow a with(nolock)
				inner join t_bd_item_info b with(nolock) on a.item_no=b.item_no
				 where oper_date between @time_begin and @time_end
			 group by   a.item_no,b.item_name  order by num desc
			 return
		end
	 end
	 
	 --门店销售额排行(不统计退货)
	 --20170619 tansh 改为统计退货，并且不统计前台充值
	 if @oper_type='6'
	 begin
		if @by_type='asc'
		begin			
			select top 20 isnull(sum(case when sell_way='B' then -pay_amount else pay_amount end),0) as num,a.branch_no,b.branch_name  from t_rm_payflow a with(nolock) 
				inner join t_bd_branch_info b with(nolock)  on a.branch_no=b.branch_no
			where oper_date between @time_begin and @time_end and isnull(trans_type,'') <> 'TTP' and isnull(memo,'') not like '前台充值%' 
			 group by   a.branch_no,b.branch_name  order by num 
asc
			 return
		end
		if @by_type='desc'
		begin			
			select top 20 isnull(sum(case when sell_way='B' then -pay_amount else pay_amount end),0) as num,a.branch_no,b.branch_name  from t_rm_payflow a with(nolock) 
				inner join t_bd_branch_info b with(nolock)  on a.branch_no=b.branch_no
			where oper_date between @time_begin and @time_end and isnull(trans_type,'') <> 'TTP' and isnull(memo,'') not like '前台充值%'
			 group by   a.branch_no,b.branch_name  order by num desc
			 return
		end
	 end
	 
	 --商品查询.输入:货号（自编码）.返回：品名、规格、库存（默认总部，可选择门店）、当日销量、7天销量、30天销量
	 if @oper_type='7'
	 begin
		 if @branch_no is null or @branch_no=''
			select @branch_no='0001'
		declare @stock_qty numeric(18,4),@day_saleqty numeric(18,4),@week_saleqty numeric(18,4),@month_saleqty numeric(18,4)		 
		 
		 --把自编码转为商品编码
		if not exists (select 1 from t_bd_item_info with(nolock) where item_no=@item_no)
		begin
			select @item_no=item_no from t_bd_item_info with(nolock) where item_subno=@item_no 
		end
		
		select top 1 @stock_qty=stock_qty from t_im_branch_stock with(nolock) where item_no=@item_no and branch_no like left(@branch_no, 2) + '%'
		select @day_saleqty=isnull(SUM(sale_qnty),0) from t_rm_saleflow with(nolock) where sell_way='A' and item_no=@item_no and branch_no like left(@branch_no, 2)  + '%' and oper_date between CONVERT(char(10),GETDATE(),121) and GETDATE()	 
		select @week_saleqty=isnull(SUM(sale_qnty),0) from t_rm_saleflow with(nolock) where sell_way='A' and item_no=@item_no and branch_no like left(@branch_no, 2)  + '%' and oper_date between CONVERT(char(10),dateadd(d,-6,GETDATE()),121) and GETDATE()
		select @month_saleqty=isnull(SUM(sale_qnty),0) from t_rm_saleflow with(nolock) where sell_way='A' and item_no=@item_no and branch_no like left(@branch_no, 2)  + '%' and oper_date between CONVERT(char(10),dateadd(d,-29,GETDATE()),121) and GETDATE()		
		select 	item_no,item_name,item_size, isnull(@stock_qty,0) as stock_qty,@day_saleqty as day_saleqty,@week_saleqty as week_saleqty,@month_saleqty as month_saleqty from t_bd_item_info with(nolock) where item_no=@item_no
	
		return
	 end
	 
	 --大类销量排行(不统计退货，一级类别)
	 --20170619 tansh 改为统计退货
	 if @oper_type='8'
	 begin
		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	
		
		if @by_type='amt_desc'
		begin			
			select c.item_clsno,d.item_clsname,amt,num from 
			(	select top 20 
				left(b.item_clsno,@gl_itm_cls_nolen) as item_clsno,isnull(sum(case when sell_way='B' then -a.sale_money else a.sale_money end),0) as amt   
					,isnull(sum(case when sell_way='B' then -a.sale_qnty else a.sale_qnty end),0) as num
				from t_rm_saleflow a with(nolock) 
				inner join t_bd_item_info b with(nolock) on a.item_no=b.item_no	
				where oper_date between @time_begin and @time_end	
				group by left(b.item_clsno,@gl_itm_cls_nolen)		
				order by amt desc
			) c
			 inner join t_bd_item_cls d with(nolock) on c.item_clsno=d.item_clsno
			order by c.amt desc

			 return
		end
		if @by_type='num_desc'
		begin			
			 select c.item_clsno,d.item_clsname,amt,num from 
			(	select top 20 
				left(b.item_clsno,@gl_itm_cls_nolen) as item_clsno,isnull(sum(case when sell_way='B' then -a.sale_money else a.sale_money end),0) as amt   
					,isnull(sum(case when sell_way='B' then -a.sale_qnty else a.sale_qnty end),0) as num
				from t_rm_saleflow a with(nolock) 
				inner join t_bd_item_info b with(nolock) on a.item_no=b.item_no	
				where oper_date between @time_begin and @time_end	
				group by left(b.item_clsno,@gl_itm_cls_nolen)		
				order by num desc
			) c
			 inner join t_bd_item_cls d with(nolock) on c.item_clsno=d.item_clsno
			order by c.num desc
			 
			 return
		end
	 end
	 
	 --默认0
	 select '0'
end
go