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


--功能:线上会员评价源数据
create proc pr_wxsf_vip_sale_evaluate
(  
	@begin_date	 datetime,	--开始时间
	@end_date	 datetime,	--结束时间
	@flow_no	 varchar(30),--销售流水号
	@card_id	 varchar(20) --会员卡ID
)
as
begin
	set nocount on -- 
	
	set @card_id = LTRIM(RTRIM(@card_id))
	set @flow_no = LTRIM(RTRIM(@flow_no)) 

	declare	@li_row		int,
			@li_count	int,
			@plan_no	varchar(20),
			@card_type	varchar(2),
			@gift_info	varchar(100)

	--存放卡类型赠送信息
	Create table #gift_info(
		id		Numeric(18,0)	IDENTITY(1,1) NOT NULL,	
		card_type	varchar(2)	not null,
		gift_info	varchar(100)	null
		primary key(id) 
	)	

	insert into #gift_info(card_type,gift_info)
	select type_id,'' from t_rm_vip_type

	select @li_count = count(1) from #gift_info
	set @li_row = 1 
	while @li_row <= @li_count
	begin
		select @card_type = card_type from #gift_info where id = @li_row

		set @plan_no = ''
		if isnull(@card_type,'')<>''
			select top 1 @plan_no = plan_no from t_vip_mk_plain_master where begin_date <= CONVERT(varchar(10),getdate(),20) and end_date >= CONVERT(varchar(10),getdate(),20) 
					and approve_flag = '1' and sheet_type = '4' and (card_type = @card_type or card_type = 'A')
		
		if isnull(@plan_no,'') = '' 
		begin
			set @li_row = @li_row + 1
			continue
		end

		set @gift_info = ''
		if exists(select 1  from t_vip_mk_plain_detail where plan_no = @plan_no and send_type = '1')
			set @gift_info = '完成评价可获得礼券'

		if exists(select 1  from t_vip_mk_plain_detail where plan_no = @plan_no and send_type = '2')
		begin
			if isnull(@gift_info,'')<>'' and len(@gift_info)>0
				set @gift_info = @gift_info+'/金额'
			else
				set @gift_info = '完成评价可获得金额'
		end

		if exists(select 1  from t_vip_mk_plain_detail where plan_no = @plan_no and send_type = '3')
		begin
			if isnull(@gift_info,'')<>'' and len(@gift_info)>0
				set @gift_info = @gift_info+'/积分'
			else
				set @gift_info = '完成评价可获得积分'
		end

		update #gift_info set gift_info = @gift_info where card_type = @card_type
		
		set @li_row = @li_row + 1
	end

	select s.flow_no,	--销售单号
		   s.oper_date,	--销售时间
		   s.branch_no,	--销售门店
		   branch_name,	--门店名称
		   s.sale_man,	--营业员
		   sale_name,	--营业员名称
		   s.item_no,	--商品货号
		   item_name,	--商品名称
		   s.sale_qnty,	--销售数量
		   (case s.sell_way when 'A' then '销售' when 'B' then '退货' when 'C' then '赠送' else '' end) as sell_way, --销售方式	 	
		   (select gift_info from #gift_info where card_type = v.card_type) as gift_info, --评价赠送信息	
		    (case when CHARINDEX('微订单号',s.memo) > 0 then '线上' else '线下' end) as flow_flag, --订单标识线上线下
  			(case when len(rtrim(s.wx_no)) > 0 then s.wx_no else '' end) as order_no  --线上订单号
	from t_rm_saleflow s with(nolock) left join t_rm_saleman m with(nolock) on s.sale_man = m.sale_id,
		 t_bd_branch_info d with(nolock),
		 t_rm_vip_info v with(nolock),	
		 t_bd_item_info b with(nolock)
	where s.branch_no = d.branch_no and
		  s.card_id = v.card_id and	
		  s.item_no = b.item_no and
		  s.flow_no not in(select distinct orderno from wxsf_vip_sale_evaluate) and
		  s.oper_date between @begin_date and @end_date and 
		  s.card_id is not null and
		  s.card_id like @card_id and
		  s.flow_no like @flow_no
		  		
	set nocount off --
end
go