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




--微商店获取卡类型变更信息 
create proc pr_wxsf_cardtype_syn_newget
(
	@begin_time datetime, --开始时间（首次同步传空值）
	@end_time datetime,   --结束时间（如果传空值，取系统时间）
	@PerNum int,		  --每页显示数量
	@PageNum int		  --页码
)
as
begin
	declare  @b	numeric(16,0),
			 @e	numeric(16,0),
			 @cnt numeric(16,0)
	
	set @b = (@PageNum - 1) * @PerNum + 1
	set @e = @PageNum * @PerNum
	
	--首次同步（从未同步过），同步表插入所有会员的初始数据
	if @begin_time is null or convert(varchar(10),@begin_time,121) = '1900-01-01'   
	begin
		insert into t_vip_cardtype_syn (card_id, update_date)
		select card_id,getdate() from t_rm_vip_info a 
		where not exists (select 1 from t_vip_cardtype_syn m where m.card_id = a.card_id) 
		and isnull(a.openid, '') <> ''
		
		set @begin_time = '1990-01-01 00:00:00'
	end
	
	--第一次调用，结束时间传空值
	if @end_time is null or convert(varchar(10),@end_time,121) = '1900-01-01' or @end_time < @begin_time
		set @end_time = getdate()
	
	--取变更数据
	declare		@temp_data table (rownumber bigint identity(1, 1) primary key, card_id varchar(20) null,  update_date datetime)
	insert into @temp_data(card_id, update_date)
	select distinct card_id, update_date
	--into #temp_data
	from (select distinct b.card_id,b.update_date from t_vip_cardtype_syn b
		   where b.update_date > @begin_time and b.update_date <= @end_time
		     and not exists (select 1 from t_vip_cardtype_syn m where m.card_id = b.card_id and m.update_date > b.update_date)
		  ) aa
	order by update_date
	
	
	--取总行数
	select @cnt = count(1) from @temp_data

	--返回
	select b.rownumber,b.update_date, 
		   type_name = (select type_name from t_rm_vip_type where type_id = a.card_type),
		   wx_shopid = (select sys_var_value from t_sys_system where lower(rtrim(sys_var_id)) = 'wx_shopid'),
		   @end_time as end_time, @cnt as total_cnt,
		   a.* 
		from @temp_data b, t_rm_vip_info a  
		where b.rownumber between @b and @e and b.card_id = a.card_id
end
go