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

 create proc pr_wxsf_member_point_detail(
 @card_id nvarchar(20),
 @pageindex int,
 @pagesize int,
 @total_count int output
 )
 as
 begin
	declare @start int
	declare @end int 

	if @pageindex>1 
		select @start=((@pageindex-1)*@pagesize)+1 
	else 
	   select @start=(@pageindex-1)*@pagesize

	select @end= @pageindex *@pagesize
	
	select identity(int ,1,1) as rownum,t.* into #tmpPagingDataTable from (
		select  t_rm_vip_good_flow.vgf_vip_no as card_id,
			t_rm_vip_good_flow.vgf_vip_no as card_no, 
			t_rm_vip_good_flow.vgf_branch_no as branch_no,   
			'1001' as oper_id,    
			t_rm_vip_good_flow.vgf_date as ope_date,
			(case when len(memo) <= 10 then memo else right(memo,len(memo)-10) end) as flow_no,
			0 as consum_amt,
			(case t_rm_vip_good_flow.vgf_flag when '3' then 1.00 else -1.00 end) *t_rm_vip_good_flow.vgf_vip_num as acc_num,
			(case t_rm_vip_good_flow.vgf_flag when '3' then '½±Àø' else '³å¼õ' end) as memo,
			(case isnull(memo,'') when '' then (case t_rm_vip_good_flow.vgf_flag when '3' then '½±Àø' else '³å¼õ' end) else left(memo,10) end) as oper_des        
		from t_rm_vip_good_flow     
		where t_rm_vip_good_flow.vgf_vip_no=@card_id

		UNION 
		SELECT	card_id,
				card_id as card_no,
				branch_no,
				'1001' as oper_id,
				pay_time as ope_date,
				flow_no,
				amount as consum_amt,
				vip_acc as acc_num,
				'Ïû·Ñ' as memo,
				(case isnull(other2,'') when '' then 'Ïû·Ñ' else other2 end) as oper_des  
		from t_rm_card_paylist 
		where [card_id]=@card_id and card_type = '1' 
	) t order by  ope_date desc
	select @total_count=COUNT(1) from #tmpPagingDataTable
	select * from #tmpPagingDataTable where rownum between @start and @end
 end
go
