IF EXISTS (SELECT * FROM dbo.sysobjects  WHERE id =OBJECT_ID(N'[dbo].[pr_wxsf_get_push_msg]') and  OBJECTPROPERTY(id,N'IsProcedure')=1)
DROP procedure [dbo].[pr_wxsf_get_push_msg]
GO

CREATE proc [dbo].[pr_wxsf_get_push_msg]
  --WITH ENCRYPTION
 as
 begin
   --,充值成功的微信消息应该由线上来发
   --card_save
   --1/尊敬的#cust#sex:您本次充值金额为#save_amt,您目前的卡内余额为#save,谢谢您对本店的支持.
   --savecard_pay
   --1/您本次使用储值卡付款金额为#save_this,您的卡内余额为#save,谢谢您对本店的支持,欢迎您下次光临.
   
   declare @company nvarchar(100)
   declare @card_save nvarchar(4000)
   declare @savecard_pay nvarchar(4000)
   declare @tmp_sys_val nvarchar(4000) --参数表里的value
   
   select @company=''
   if exists (select 1 from t_sys_customer)
   begin
	select top 1 @company=[name] from t_sys_customer
   end
   
   select @card_save='尊敬的#cust#sex:您本次充值金额为#save_amt,您目前的卡内余额为#save,谢谢您对本店的支持.'
   select @savecard_pay='您本次使用储值卡付款金额为#save_this,您的卡内余额为#save,谢谢您对本店的支持,欢迎您下次光临.'
   
   if exists (select 1 from mess_t_sys_system where sys_var_id='card_save')
   begin
	 select top 1 @tmp_sys_val=sys_var_value from mess_t_sys_system where sys_var_id='card_save'
	 select @tmp_sys_val=RTRIM(@tmp_sys_val)
	 if LEN(@tmp_sys_val)>0 AND left(@tmp_sys_val,1)='1'
		select @card_save=RIGHT(@tmp_sys_val,LEN(@tmp_sys_val)-2)
   end
   
   if exists (select 1 from mess_t_sys_system where sys_var_id='savecard_pay')
   begin
	 select top 1 @tmp_sys_val=sys_var_value from mess_t_sys_system where sys_var_id='savecard_pay'
	 select @tmp_sys_val=RTRIM(@tmp_sys_val)
	 if LEN(@tmp_sys_val)>0 AND left(@tmp_sys_val,1)='1'
		select @savecard_pay=RIGHT(@tmp_sys_val,LEN(@tmp_sys_val)-2)
   end
   
   --返回内容:flow_id,OPEN ID ,通知内容，通知人，备注
   --列名固定
   
   create table #push_msg
   (
		flow_id numeric(18,0),
		open_id nvarchar(100),
		msg_content nvarchar(4000),
		msg_sender nvarchar(100),
		memo nvarchar(400),
		card_id nvarchar(100),
		sex nvarchar(100),
		vip_name nvarchar(100),
		[first] NVARCHAR(200),
		keyword1 NVARCHAR(100),
		keyword2 nvarchar(100),
		keyword3 nvarchar(100),
		keyword4 nvarchar(100),
		keyword5 nvarchar(100),
		remark nvarchar(1000),		
		smstype nvarchar (2)	
   )
   
   --生成内容
	declare @flow_id numeric(18,0)
	declare @open_id nvarchar(100)
	declare @card_id nvarchar(100)
	declare @sex nvarchar(100)
	declare @vip_name nvarchar(100)
	declare @oper_type nvarchar(100)
	declare	@msg_content nvarchar(4000)
	declare @residual_amt nvarchar(100)--余额
	declare @savelist_amt numeric(16,2)
	declare @first nvarchar(200)
	declare @keyword1 nvarchar(100)
	declare @keyword2 nvarchar(100)
	declare @keyword3 nvarchar(100)
	declare @keyword4 nvarchar(100)
	declare @keyword5 nvarchar(100)
	declare @remark nvarchar(1000)	
	DECLARE @smstype nvarchar (2)	
	
	declare cursor1 cursor for         --定义游标cursor1
		select a.flow_id,a.openid,a.mess
		,b.card_id, b.vip_sex ,isnull(b.vip_name,'') as vip_name
		,a.first,a.keyword1,a.keyword2,a.keyword3,keyword4,a.keyword5,a.remark,a.smstype --add by eric 2021-01-02
		from mess_t_wx_flow a with(nolock) 
		INNER join t_rm_vip_info b with(nolock) 
		ON a.cust_no=b.card_id 
		where a.region='0' and isnull(a.openid,'')<>''
		
		union ALL
		
		select a.flow_id,a.openid,a.mess
		,'' card_id, '' vip_sex ,'' as vip_name
		,a.first,a.keyword1,a.keyword2,a.keyword3,keyword4,a.keyword5,a.remark,a.smstype --add by eric 2021-01-02
		from mess_t_wx_flow a with(nolock)
		where a.region='0' and isnull(a.openid,'')<>'' and isnull(a.cust_no,'')=''
		
	 open cursor1                       --打开游标
	 fetch next from cursor1 into @flow_id,@open_id,@msg_content,@card_id,@sex,@vip_name,@first,@keyword1,@keyword2,@keyword3,@keyword4,@keyword5,@remark,@smstype  --将游标向下移1行，获取的数据放入之前定义的变量@id,@name中
	 
	 while @@fetch_status=0           --判断是否成功获取数据
	 begin
--		 if @sex='男'
--			select @sex='先生'
--		 else
--			select @sex='女士'
--		--oper_type 4 是充值，5 付款
--		if @oper_type='4'		 
--			select @msg_content=@card_save		 
--		 else		 
--			select @msg_content=@savecard_pay
		 
		--替换变量
--		select @msg_content=REPLACE(@msg_content,'#save_amt',@savelist_amt)--充值金额
--		select @msg_content=REPLACE(@msg_content,'#save_this',@savelist_amt)--使用储值卡付款金额
--		select @msg_content=REPLACE(@msg_content,'#cust',@vip_name) --客户名
--		select @msg_content=REPLACE(@msg_content,'#sex',@sex)
--		select @msg_content=REPLACE(@msg_content,'#save',@residual_amt)--余额
		--写入临时表
		insert into #push_msg (flow_id,open_id,msg_content,msg_sender,memo,card_id,sex,vip_name,first,keyword1,keyword2,keyword3,keyword4,keyword5,remark,smstype)
		 values (@flow_id,@open_id,@msg_content,@company,'',@card_id,@sex,@vip_name,@first,@keyword1,@keyword2,@keyword3,@keyword4,@keyword5,@remark,@smstype)
		 
	 fetch next from cursor1 into @flow_id,@open_id,@msg_content,@card_id,@sex,@vip_name,@first,@keyword1,@keyword2,@keyword3,@keyword4,@keyword5,@remark,@smstype --将游标向下移1行
	 end
	 
	 close cursor1                   --关闭游标
	 deallocate cursor1 
    --生成结束
   select * from #push_msg
   
 end
go
