if exists(select 1 from sysobjects where name = 'pr_wxsf_vip_sale_flow_new')
   drop proc pr_wxsf_vip_sale_flow_new
go

--功能:会员消费记录
create proc [dbo].[pr_wxsf_vip_sale_flow_new] 
(  
	@card_id		varchar(20),	--会员卡id
	@begin_date		datetime,		--起始时间
	@end_date		DATETIME,		--结束时间
	@pageindex int=1, --页码
	@pagesize int=5 --每页行数
)
as
begin
	set nocount on 
	--	返回结果集: 
	--flow_no --线下单号 
	--branch_name --门店名称 
	--total_num--合计件数 
	--real_amt --实付金额 
	--discount_amt --优惠金额 
	--sale_price --销售单价 
	--sale_qty --销售数量 
	--item_name --商品名称 
	--sale_date --销售时间 

	(SELECT IDENTITY(INT,1,1) as row_number,flow_no,oper_date
	into #flow_temp
	from t_rm_payflow WITH (NOLOCK) WHERE vip_no = @card_id and oper_date between @begin_date and @end_date 
    AND exists(select 1 from t_rm_saleflow a where t_rm_payflow.flow_no=a.flow_no)
	and flow_id=1 
	and not exists(select 1 from t_mp_saleflow where t_mp_saleflow.busi_sheet_no = t_rm_payflow.flow_no)
	group by oper_date,flow_no
	)
	ORDER BY oper_date desc
	--创建空表
	select 0 as row_number,flow_no into #flow from t_rm_payflow where 1=2
	--拼接插入 #flow
	declare @sql	varchar(1000)
		set @sql = 'insert into #flow (row_number,flow_no) select top '+ CAST(@pagesize as varchar(3)) + ' row_number,flow_no from #flow_temp r 
			where r.row_number > ( ' + CAST(@pageindex-1 as varchar(3)) + ') * ' + CAST(@pagesize as varchar(3))
	exec(@sql)
	
	--select top (@pagesize) * INTO #flow from (SELECT ROW_NUMBER() OVER (ORDER BY oper_date desc) as row_number,flow_no
	--from t_rm_payflow WITH (NOLOCK) WHERE vip_no = @card_id and oper_date between @begin_date and @end_date 
 --   AND exists(select 1 from t_rm_saleflow a where t_rm_payflow.flow_no=a.flow_no)
	--and flow_id=1 
	--and not exists(select 1 from t_mp_saleflow where t_mp_saleflow.busi_sheet_no = t_rm_payflow.flow_no)
	--)r where r.row_number>(@pageindex-1)*@pagesize

	select		
				s.flow_no as flow_no,
				(select top 1 branch_name from t_bd_branch_info b where b.branch_no = left(s.branch_no,4) )  as branch_name,
				ss.total_num as total_num,
				pp.real_amt as real_amt,
				ss.discount_amt as discount_amt,
				s.sale_price as sale_price,
				round(sum((case when s.sell_way='B' then -1 else 1 end ) * s.sale_qnty),4) as sale_qty,
				i.item_name as item_name,
				max(s.oper_date) as sale_date,
				s.sell_way
	 from t_rm_saleflow s 
	 inner join t_bd_item_info i on i.item_no = s.item_no
	 inner join t_rm_payflow pf on pf.flow_no = s.flow_no and pf.flow_id = 1
	 inner join
	 (select sb.flow_no,
			COUNT(distinct sb.item_no) total_num,
			round(sum((case when sb.sell_way='B' then -1 else 1 end ) *(sb.source_price * sb.sale_qnty - sb.sale_money)),4) discount_amt
	  from t_rm_saleflow sb,t_rm_payflow
	 where t_rm_payflow.vip_no = @card_id 
			and sb.oper_date between @begin_date and @end_date 
			and t_rm_payflow.flow_no = sb.flow_no and t_rm_payflow.flow_id = 1
			and t_rm_payflow.branch_no = sb.branch_no 
	 group by sb.flow_no
	 ) ss on ss.flow_no = s.flow_no
	 inner join
	 (
	 /*select sp.flow_no,
			branch_no,
			round(SUM((case when sp.sell_way='B' then -1 else 1 end )* sp.pay_amount ),4) real_amt
	  from t_rm_payflow sp 
	 where sp.vip_no = @card_id 
			and sp.oper_date between @begin_date and @end_date 
			and not sp.memo like '零钱包存入%'
	 group by sp.flow_no,sp.branch_no 
	 */
	 SELECT
		sp.flow_no,
		sp.branch_no,
		ROUND(SUM(sp.amount),4) real_amt
	 FROM t_rm_card_paylist sp
	 WHERE card_id = @card_id
	 AND sp.pay_time between @begin_date and @end_date 
	 AND sp.card_type = '1'
	 AND sp.flow_no <> '数据库重建'
	 group by sp.flow_no,sp.branch_no 
	 ) pp on pp.flow_no = s.flow_no 
	 and pp.branch_no = LEFT(s.branch_no,2)
	 --and pp.branch_no = s.branch_no
	where pf.vip_no = @card_id and s.oper_date between @begin_date and @end_date
	 and not exists(select 1 from t_mp_saleflow where t_mp_saleflow.busi_sheet_no = s.flow_no and t_mp_saleflow.item_no = s.item_no)
	 and exists (select 1 from #flow f where f.flow_no=s.flow_no) 
	group by	s.flow_no,s.sale_price,i.item_name,s.branch_no,
				ss.total_num,
				pp.real_amt,
				ss.discount_amt,
				s.sell_way
	ORDER BY max(s.oper_date) DESC
	
	DROP TABLE #flow
	drop table #flow_temp			
	set nocount off 
end
go
