pruduct.sql

本过程是短信产品开发的一个点播,根据用户发送来的指令,查询该用户早前记录,依据流程的逻辑判断来决定要回复用户的内容.

/***********************
作者：bryan



SMS 点播用户记录****
************************/
CREATE                               proc sp_goldball_proc
	@phone       varchar(32), 
	@port        varchar(32),
	@userdata    varchar(140),
	@serviceid   varchar(32),
	@ServiceCode varchar(32),
	@Receive_id  int,
        @Msg_id      varchar(20),
        @Msg_Fmt     int,
        @Servicetype varchar(6),
        @LinkId      varchar(20),
        @Sequence_ID int
	
as
set @phone=ltrim(rtrim(@phone))
set @port=ltrim(rtrim(@port))
set @userdata=ltrim(rtrim(@userdata))



declare @RUserData varchar(500)

begin
  

declare @step  int,
        @level int,
        @rate  int,
        @score int,
        @total int,
	@v_feemobile varchar(32), --错误处理号码  
     @v_serviceid varchar(32), --错误处理下行收费码
        @ballnum varchar(2)
set @step=0
set @level=0
set @v_feemobile =@phone
set @v_serviceid = @serviceid
if upper(@userdata)  like 'D%'
	begin
if (select count(*) from tb_goldball_user where   datename(mm,intime)=datename(mm,getdate()) and datename(yy,intime)=datename(yy,getdate()) and  mobile=@phone)>35
begin
    select @Ruserdata='你玩本游戏在本月累积超过了35次，系统自动把你当作休息人员。请在下个继续回复D玩我们的游戏。'
    goto tosend
end

select top 1 @step=step,@level=level,@rate=rate,@score=score from tb_goldball_user where mobile=@phone order by UserID desc

if @step=0 or @step=3
begin
   select top 1 @rate=rate from tb_goldball_rate order by newid()
   if @rate<200
      select top 1 @score=rate,@level=@level+1 from tb_goldball_rate where rate>10 and rate<50 order by newid() 
   else
      select top 1 @score=rate,@level=@level+1 from tb_goldball_rate where rate>40 and rate<80 order by newid()  

   if @score <25 set @ballnum='一'
   else if @score<35 set @ballnum='二'
   else if @score<50 set @ballnum='三'
   else  set @ballnum='四'
     
   insert tb_goldball_user(Mobile,step,rate,level,score) values(@phone,1,@rate,@level,@score)
   select @Ruserdata='“晃铛”您击中'+@ballnum+'个球,第一轮得'+convert(varchar(3),@score)+'分.下一轮继续努力'+char(10)+
                     '第二轮开始,您面前有四个金瓶,请回复D将您手中的球滚出,看运气如何.'
end
else
if @step=1
begin
   if @rate<200
      select top 1 @score=rate,@level=@level from tb_goldball_rate where rate>10 and rate<50 order by newid()    
   else
      select top 1 @score=rate,@level=@level from tb_goldball_rate where rate>40 and rate<80 order by newid() 
   insert tb_goldball_user(Mobile,step,rate,level,score) values(@phone,2,@rate,@level,@score)  
   select @total=sum(score) from tb_goldball_user where mobile=@phone and level=@level 

   if @score <25 set @ballnum='一'
   else if @score<35 set @ballnum='二'
   else if @score<50 set @ballnum='三'
   else  set @ballnum='四'

   select @Ruserdata='“晃铛”您击中'+@ballnum+'个球,第二轮得'+convert(varchar(3),@score)+'分.两轮积分是'+convert(varchar(3),@total)+
                   '分,下一轮继续努力.第三轮开始,您面前有四个金瓶,请回复D将您手中球滚出,看运气如何'
end
if @step=2
begin
   if @rate<200
   begin
      declare @sum int
      select @sum=100-sum(score)  from tb_goldball_user where mobile=@phone and level=@level
      select top 1 @score=rate,@level=@level from tb_goldball_rate where rate<@sum order by newid()    
   
   end
   else
      select top 1 @score=rate,@level=@level from tb_goldball_rate where rate>40 and rate<80 order by newid() 
   insert tb_goldball_user(Mobile,step,rate,level,score) values(@phone,3,@rate,@level,@score) 
   select @total=sum(score) from tb_goldball_user where mobile=@phone and level=@level

   if @score <25 set @ballnum='一'
   else if @score<35 set @ballnum='二'
   else if @score<50 set @ballnum='三'
   else  set @ballnum='四'

   if @score>=100 
	begin
      select @Ruserdata='“晃铛”您击中'+@ballnum+'个球,第三轮得'+convert(varchar(3),@score)+'分.三轮积分是'+convert(varchar(3),@total)+
                   '分,您赢得了50元话费,请与02038469983联系'
	insert into tb_app_winner(phone,total,ServiceCode,ServiceCommand,ServiceDesc)
                                values(@phone,@score,'goldball','D','金球撞')
	end
   else
      select @Ruserdata='“晃铛”您击中'+@ballnum+'个球,第三轮得'+convert(varchar(3),@score)+'分.三轮积分是'+convert(varchar(3),@total)+
                   '分,您积分没有过100分,想继续玩吗?请继续回复D'

end
	end
else
	begin 
		select @Ruserdata='D 请正确回复D才能使用本业务,多谢合作!'
    		set @port = '10662216633'
		set @v_feemobile='13560351301'
		 set @v_serviceid='-Sq'
		goto tosend
	end
--set @RUserData=@RUserData+'2元/条' 
tosend:	exec sp_insert_send @FeeMobile =@v_feemobile,
                            @Port=@port,
                            @Phone=@phone,
                            @UserData=@RUserData,
                            @ServiceID=@v_serviceid,
                            @ServiceCode=@ServiceCode,
                            @Receive_id=@Receive_id ,
                            @Msg_id=@Msg_id ,
                            @MsgType=0,
                            @MsgCoding=8,
                            @FeeValue=0000,
                            @FeeType=1,
                            @Msg_Fmt=15,
                            @Servicetype=@Servicetype,
                            @LinkId=@LinkId,
                            @Sequence_ID=@Sequence_ID

end
GO