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

--功能:会员消费记录
create proc pr_wxsf_vip_sale_flow 
(  
	@card_id		varchar(20),	--会员卡id
	@begin_date		datetime,		--起始时间
	@end_date		datetime		--结束时间
)
as
begin
	set nocount on 
	--	返回结果集: 
	--flow_no --线下单号 
	--branch_name --门店名称 
	--total_num--合计件数 
	--real_amt --实付金额 
	--discount_amt --优惠金额 
	--sale_price --销售单价 
	--sale_qty --销售数量 
	--item_name --商品名称 
	--sale_date --销售时间 
	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.source_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
	 group by sb.flow_no
	 ) ss on ss.flow_no = s.flow_no
	 inner join
	 (select sp.flow_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 
	 group by sp.flow_no
	 ) pp on pp.flow_no = s.flow_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)
	group by	s.flow_no,s.source_price,i.item_name,s.branch_no,
				ss.total_num,
				pp.real_amt,
				ss.discount_amt,
				s.sell_way
	
	set nocount off 
end
go