一个定时发送短信功能的存储过程实现(利用作业的形式)!

 

--编写人:胡乐
--修改时间:2008-07-01
--功能:定时发送

ALTER PROCEDURE DO_Timer_Send
AS
BEGIN 
    DECLARE @U_ID      INT
    DECLARE @GROUP_ID  VARCHAR(250)  --组ID
    DECLARE @SUBJECT   VARCHAR(500)  --发送内容
    DECLARE @CONTENT   VARCHAR(5000) --发送内容
    DECLARE @TIME_TYPE INT           --时间类别
    DECLARE @PER_MONTH INT           --按月发送时的日期
    DECLARE @PER_WEEK  INT           --按每日期几发送
    DECLARE @U_DATE    DATETIME      --定时发送的日期
    DECLARE @U_TIME    VARCHAR(10)   --定时发送的时间
    DECLARE @USERID    VARCHAR(50)   --公司名称
    DECLARE @OWNER     VARCHAR(20)   --公司代码
    DECLARE @SQL       VARCHAR(5000) 
    DECLARE @CATE_ID   INT           --分类1为普通,2为节日,3为生日
    DECLARE @EXEC_INTERVAL INT       --扫描的时间间隔
    SELECT  @EXEC_INTERVAL = 10

    DECLARE @MOBILE_NUM VARCHAR(11)
    DECLARE @NEXT_TIME  DATETIME     --下次运行时间
    DECLARE @LAST_TIME  DATETIME
    DECLARE @SEND_QTY    INT         --已发送数量
    DECLARE @MSG_NUM    INT          --发送信息条数
    DECLARE @GROUP_USER_QTY    INT   --组用户数量
    DECLARE @BALANCE_QTY INT         --剩余发送量
    DECLARE @SHORT_NAME  VARCHAR(50) --企业简称
    DECLARE @MESSAGE    VARCHAR(5000)
    DECLARE @SENDNO INT              --发送批次
    DECLARE @SendNum  VARCHAR(18)  --发送日期+批次
    DECLARE @TMPID INT 
    DECLARE @TMPSQL VARCHAR(2500) 
    DECLARE @SPLIT_STR VARCHAR(200)  --拆分后的字符串
    DECLARE @Idio VARCHAR(30)        --企业签名
    DECLARE @MsgLen INT
    DECLARE @BalanceQty int
    DECLARE @CanExit bit
    DECLARE @N INT
    DECLARE @FACTLEN INT
    DECLARE @USERCOUNT INT  --发送总用户数

    SET @CanExit = 0    

    DECLARE @TDAY VARCHAR(5) --当天日期数
    SET @TDAY=DAY(GETDATE())
    IF LEN(@TDAY)=1 SET @TDAY=@TDAY

    --从Timer_Send表中读数据
    DECLARE SEND_TO_MESSAGE CURSOR LOCAL FORWARD_ONLY  READ_ONLY 
                           FOR SELECT ID,GROUP_ID,CATE_ID,SUBJECT,CONTENT,TIME_TYPE,PER_MONTH,PER_WEEK,U_DATE,U_TIME,USERID,OWNER,NEXT_TIME  
                           FROM timer_send
                           WHERE NEXT_TIME BETWEEN dateadd(mi,-@EXEC_INTERVAL,getdate()) AND GETDATE() and status=0
    OPEN SEND_TO_MESSAGE
         FETCH NEXT FROM SEND_TO_MESSAGE 
         INTO @U_ID,@GROUP_ID,@CATE_ID,@SUBJECT,@CONTENT,@TIME_TYPE,@PER_MONTH,@PER_WEEK,@U_DATE,@U_TIME,@USERID,@OWNER,@NEXT_TIME
         WHILE @@FETCH_STATUS =0
    BEGIN
    BEGIN TRAN --开始事务(1)

         --游标循环后要初始化计费的值
         SELECT @SEND_QTY=0
         --取得剩余的金额@BalanceQty 签名@SHORT_NAME
         select @BalanceQty=BalanceQty,@SHORT_NAME=Idiograph from USER_TYPE_TABLE WHERE OWNER=@OWNER

         --检查一个定时任务里是否有多个用户组
         SET @N=1
         DECLARE SEND_GROUP CURSOR LOCAL FORWARD_ONLY  READ_ONLY 
                           FOR SELECT A  FROM DBO.f_split(@GROUP_ID,';')
         OPEN SEND_GROUP
              FETCH NEXT FROM SEND_GROUP INTO @TMPID
              WHILE @@FETCH_STATUS =0
         BEGIN
              IF @N=1 
                  SET @TMPSQL =  CAST(@TMPID AS VARCHAR)                
              ELSE
                  SET @TMPSQL = @TMPSQL + ' OR GROUP_ID='+CAST(@TMPID AS VARCHAR)     

              --取得组中用户的数量
              IF @CATE_ID=3  --生日  
                  --SELECT @GROUP_USER_QTY=COUNT(*) FROM All_SMS_USERS  WHERE OWNER=@OWNER AND USERID=@USERID AND (isdate(brithday)=1 AND  ( right(rTRIM(CONVERT(CHAR(10),BRITHDAY,120)),2)=@TDAY or right(rTRIM(CONVERT(CHAR(10),BRITHDAY,120)),1)=@TDAY) AND MONTH(BRITHDAY)=MONTH(GETDATE())) and (GROUP_ID=@TMPID) AND MOBILE_NUM NOT IN (SELECT MOBILE FROM BLACKLIST WHERE OWNER=@OWNER)
                  SELECT @GROUP_USER_QTY=COUNT(*) FROM All_SMS_USERS  WHERE OWNER=@OWNER AND USERID=@USERID AND (isdate(brithday)=1 AND  cast(substring(brithday,charindex('-',brithday,charindex('-',brithday)+1)+1,2) as int)=@TDAY AND MONTH(BRITHDAY)=MONTH(GETDATE())) and (GROUP_ID=@TMPID) AND MOBILE_NUM NOT IN (SELECT MOBILE FROM BLACKLIST WHERE OWNER=@OWNER)
              ELSE
                  SELECT @GROUP_USER_QTY=COUNT(*) FROM All_SMS_USERS  WHERE OWNER=@OWNER AND USERID=@USERID AND (GROUP_ID=@TMPID) AND MOBILE_NUM NOT IN (SELECT MOBILE FROM BLACKLIST WHERE OWNER=@OWNER ) 

              --取得企业签名

              SELECT @Idio = '[' + Ltrim(Rtrim(Idiograph)) + ']' FROM User_Type_Table WHERE OWNER=@OWNER
              SET @MsgLen = 70 - LEN(@Idio)

              IF @@ROWCOUNT=0 SELECT @GROUP_USER_QTY=0

              --叠加已发送数量
              SET @SEND_QTY= @SEND_QTY+@GROUP_USER_QTY

              SET @N=@N+1
              FETCH NEXT FROM SEND_GROUP INTO  @TMPID --???
         END
         CLOSE SEND_GROUP
         DEALLOCATE SEND_GROUP
         
         PRINT @SEND_QTY

         --信息字数
         --select @MSG_NUM=COUNT(*) from  dbo.split_msg(@CONTENT, 70, @Idio)
         --SET @SEND_QTY = @SEND_QTY*@MSG_NUM
        --计算短信条数        
         IF len(@CONTENT+@Idio)>70
              BEGIN
                      SET @FACTLEN=67
              END
         ELSE
              BEGIN
                     SET @FACTLEN=70
              END
         IF (len(@CONTENT+@Idio) % @FACTLEN) > 0
              BEGIN
                     SET @MSG_NUM = len(@CONTENT+@Idio) / @FACTLEN + 1
              END
         ELSE
              BEGIN
                    SET @MSG_NUM = len(@CONTENT+@Idio) / @FACTLEN
              END
    SET  @USERCOUNT = @SEND_QTY
    SET @SEND_QTY = @SEND_QTY*@MSG_NUM
         --如果余额足够?发送
         IF @BalanceQty > @SEND_QTY
            BEGIN
                        --检查是否??
                        SELECT @CanExit=status from Timer_Send where id=@u_id
              
                        IF @CanExit = 0
                           BEGIN
                                --得到发送批次
                                select @SENDNO=isnull(max(sendno),0) from req_web
                                select @SENDNO = @SENDNO+1 
                                --发送日期+批次
                                set @SendNum = convert(varchar,Year(getdate())) + convert(varchar,month(getdate())) + convert(varchar,day(getdate()))+cast(@sendno as char(5))
                                SELECT @SQL ='INSERT INTO req_web(mobile_src, mobile_dest, service_id, content,msgsum,SendNum, cur_time,sendno,sendtype,userid,owner,groupsend,group_id) '
                                SELECT @SQL =@SQL+' SELECT MOBILE_NUM AS mobile_src,MOBILE_NUM AS mobile_dest,'''' AS service_id, '+''''+@CONTENT+''' as content,'+''''+CONVERT(varchar,@MSG_NUM)+''' as msgsum, '+''''+@SendNum+''',getdate() as cur_time,'+cast(@sendno as char(5))+' as sendno,1 as sendtype,'+''''+@userid+''' as userid,'+''''+@OWNER+''' as owner,1 as groupsend,0 as group_id  '
                                --SELECT @SQL =@SQL+'   FROM dbo.split_msg('+''''+@CONTENT + ''', 70, '+''''+@Idio + ''') a  cross join All_SMS_USERS  b '
                                SELECT @SQL =@SQL+'   FROM  All_SMS_USERS   '
                                SELECT @SQL =@SQL+' where  OWNER='+''''+@OWNER + ''' and userid='+''''+@userid+''''
                                SELECT @SQL = @SQL + 'AND MOBILE_NUM NOT IN (SELECT MOBILE FROM BLACKLIST WHERE OWNER='''+@OWNER+''')'
                              
                                IF @CATE_ID=3 --生日         
                                   BEGIN
                                        --SELECT @SQL=@SQL +' AND (isdate(brithday)=1 AND  ( right(rTRIM(CONVERT(CHAR(10),BRITHDAY,120)),2)='+''''+@TDAY+''''+' or right(rTRIM(CONVERT(CHAR(10),BRITHDAY,120)),1)='+''''+@TDAY+''''+' ) AND MONTH(BRITHDAY)=MONTH(GETDATE()))'
                                        SELECT @SQL=@SQL +' AND (isdate(brithday)=1 AND  cast(substring(brithday,charindex('+'''-'''+',brithday,charindex('+'''-'''+',brithday)+1)+1,2) as int) = '+''+@TDAY+''+'  AND MONTH(BRITHDAY)=MONTH(GETDATE()))'
                                   END
                                SELECT @SQL=@SQL + ' AND (GROUP_ID= '+@TMPSQL+')'
                                EXEC (@SQL)
                           END

               UPDATE User_Type_Table SET BalanceQty=BalanceQty-@SEND_QTY FROM USER_TYPE_TABLE WHERE OWNER=@OWNER
               UPDATE User_Type_Table SET UsedQty=UsedQty+@SEND_QTY FROM USER_TYPE_TABLE WHERE OWNER=@OWNER
               --记录发送日志
               insert into Send_Log (Content,MsgSum,UserCount,SendNum,Balance,Owner,Userid,UserName,ReMark,IP) values(@CONTENT,@MSG_NUM,@USERCOUNT,@SendNum,@BalanceQty,@OWNER,@userid,@SHORT_NAME,@TMPSQL,'定时ID:'+CONVERT(varchar,@U_ID))
               --更新最后执行时间
               IF @TIME_TYPE=0 SET @LAST_TIME = dateadd(d,1,@NEXT_TIME)  --每天
               IF @TIME_TYPE=1 SET @LAST_TIME = dateadd(m,1,@NEXT_TIME)  --每月
               IF @TIME_TYPE=2 SET @LAST_TIME = dateadd(d,7,@NEXT_TIME)  --每星期
               IF @LAST_TIME <> ''
                  UPDATE Timer_Send SET NEXT_TIME=@LAST_TIME,status=0 WHERE ID=@U_ID
               ELSE
                  UPDATE Timer_Send SET status=1 WHERE ID=@U_ID
                  SET @CanExit = 1
            END
    IF @@ERROR <> 0
       BEGIN
            ROLLBACK TRAN  --事务回滚
            RETURN
       END
    COMMIT TRAN  --事务执行      
       FETCH NEXT FROM SEND_TO_MESSAGE INTO  @U_ID,@GROUP_ID,@CATE_ID,@SUBJECT,@CONTENT,@TIME_TYPE,@PER_MONTH,@PER_WEEK,@U_DATE,@U_TIME,@USERID,@OWNER,@NEXT_TIME
    END
    CLOSE SEND_TO_MESSAGE
    DEALLOCATE SEND_TO_MESSAGE
END

定时器表：

 

1	[ID]	INT	自动编号
2	[Cate_id]	int	定时分类（普通定时，生日定时，节日定时）
3	[Group_id]	varchar	发送组ID
4	[Group_name]	varchar	发送组名
5	[Subject]	varchar	定时工作名称
6	[Content]	text
7	[time_type]	int	发送类型
8	[per_month]	int
9	[per_week]	int
10	[u_date]	datetime	指定日期
11	[u_time]	varchar	指定时间
12	[cur_time]	datetime	创建时间
13	[next_time]	datetime	下次发送时间
14	[status]	int	默认为0 是否有效 触发器已经发送多改为1
15	[userid]	varchar	创建用户ID
16	[owner]	varchar	企业编号