📄 sms2db.sql

📁 简单的cmpp3.0网关实现代码简单的cmpp3.0网关实现代码
💻 SQL
📖 第 1 页 / 共 4 页
字号:
上一页 1 2 34






CREATE    PROC SGIP12_RECEIVEREPORT
  @SequenceNumber1 int, 
  @SequenceNumber2 int, 
  @SequenceNumber3 int,
  @ReportType int,
  @UserNumber VarChar(20),
  @State int,
  @ErrorCode int,
  @LinkID VarChar(8)
AS

  INSERT INTO SGIP_Report(SequenceNumber1,SequenceNumber2,SequenceNumber3,ReportType,UserNumber,State,ErrorCode,LinkID)
                   VALUES(@SequenceNumber1,@SequenceNumber2,@SequenceNumber3,@ReportType,@UserNumber,@State,@ErrorCode,@LinkID)









GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO




CREATE  PROC SGIP12_SUBMITERROR
  @SubmitID		int,
  @SequenceNumber1	int,
  @SequenceNumber2	int,
  @SequenceNumber3	int,
  @ResponseResult	int,
  @MT_SendCount		int,
  @MT_BaseFetchFlag	varchar(2),
  @SPNumber		varchar(21),
  @ChargeNumber		varchar(21),
  @UserNumber		varchar(21),
  @ServiceType		varchar(1),
  @FeeType		int,
  @FeeValue		varchar(6),
  @GivenValue		varchar(6),
  @AgentFlag		int,
  @MorelatetoMTFlag	int,
  @Priority		int,
  @ExpireTime		varchar(16),
  @ScheduleTime		varchar(16),
  @ReportFlag		int,
  @TP_pid		int,
  @TP_udhi		int,
  @MessageCoding	int,
  @MessageType		int,
  @MessageContent	varchar(30),
  @LinkID		varchar(8),
  @ErrorCode		int,
  @ErrorInfo 		varchar(300)
AS

  INSERT INTO SGIP_Submit_Error
              (SequenceNumber1,SequenceNumber2,SequenceNumber3,ResponseResult,MT_SendCount,
               MT_BaseFetchFlag, SPNumber, ChargeNumber, UserNumber, ServiceType, FeeType, 
               FeeValue, GivenValue, AgentFlag, MorelatetoMTFlag, Priority, ExpireTime, 
               ScheduleTime, ReportFlag, TP_pid, TP_udhi, MessageCoding, MessageType, 
               MessageContent, LinkID,ErrorCode,ErrorInfo)
  VALUES
              (@SequenceNumber1,@SequenceNumber2,@SequenceNumber3,@ResponseResult, @MT_SendCount, 
               @MT_BaseFetchFlag, @SPNumber, @ChargeNumber, @UserNumber, @ServiceType, @FeeType, 
               @FeeValue, @GivenValue, @AgentFlag, @MorelatetoMTFlag, @Priority, @ExpireTime, 
               @ScheduleTime, @ReportFlag, @TP_pid, @TP_udhi, @MessageCoding, @MessageType,
               @MessageContent, @LinkID,@ErrorCode,@ErrorInfo)


  UPDATE SGIP_Submit SET MT_SendCount = @MT_SendCount + 1 WHERE ID = @SubmitID






GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO










CREATE     PROC SGIP12_SUBMITHISTORY
  @SubmitID		int, 
  @SequenceNumber1	int,
  @SequenceNumber2	int,
  @SequenceNumber3	int,
  @ResponseResult	int,
  @MT_SendCount		int,
  @MT_BaseFetchFlag	varchar(2),
  @SPNumber	varchar(21),
  @ChargeNumber	varchar(21),
  @UserNumber	varchar(21),
  @ServiceType	varchar(1),
  @FeeType	int,
  @FeeValue	varchar(6),
  @GivenValue	varchar(6),
  @AgentFlag	int,
  @MorelatetoMTFlag	int,
  @Priority	int,
  @ExpireTime	varchar(16),
  @ScheduleTime	varchar(16),
  @ReportFlag	int,
  @TP_pid	int,
  @TP_udhi	int,
  @MessageCoding	int,
  @MessageType	int,
  @MessageContent	varchar(30),
  @LinkID	varchar(8)
AS

  INSERT INTO SGIP_Submit_History
              (SequenceNumber1,SequenceNumber2,SequenceNumber3,ResponseResult,MT_SendCount, 
               MT_BaseFetchFlag, SPNumber, ChargeNumber, UserNumber, ServiceType, FeeType, 
               FeeValue, GivenValue, AgentFlag, MorelatetoMTFlag, Priority, ExpireTime, 
               ScheduleTime, ReportFlag, TP_pid, TP_udhi, MessageCoding, MessageType, 
               MessageContent, LinkID)
  VALUES
              (@SequenceNumber1,@SequenceNumber2,@SequenceNumber3,@ResponseResult, @MT_SendCount,
               @MT_BaseFetchFlag, @SPNumber, @ChargeNumber, @UserNumber, @ServiceType, @FeeType, 
               @FeeValue, @GivenValue, @AgentFlag, @MorelatetoMTFlag, @Priority, @ExpireTime, 
               @ScheduleTime, @ReportFlag, @TP_pid, @TP_udhi, @MessageCoding, @MessageType, 
               @MessageContent, @LinkID)


  DELETE FROM SGIP_Submit WHERE ID = @SubmitID








GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


CREATE   PROC SMGP13_GETMESSAGE
  @MT_BaseFetchFlag  varchar(2)
AS


  SELECT TOP 1000 * FROM SMGP_Submit WHERE MT_BaseFetchFlag = @MT_BaseFetchFlag  AND MT_SendCount = 0








GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO









CREATE     PROC SMGP13_RECEIVEMESSAGE
  @MsgID varchar(21),
  @IsReport smallint,
  @MsgFormat smallint,
  @RecvTime varchar(14),
  @SrcTermID varchar(21),
  @DestTermID varchar(21),
  @MsgLength smallint,
  @MsgContent varchar(300),
  @Reserve varchar(8)
AS
  INSERT INTO SMGP_Deliver(MsgID,IsReport,MsgFormat,RecvTime,SrcTermID,DestTermID,MsgLength,MsgContent,Reserve) 
               VALUES(@MsgID,@IsReport,@MsgFormat,@RecvTime,@SrcTermID,@DestTermID,@MsgLength,@MsgContent,@Reserve)

 

  --加入接收短信的入口过程







GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO






CREATE     PROC SMGP13_RECEIVEREPORT
  @MsgID varchar(21),
  @sub varchar(3),
  @dlvrd varchar(3),
  @Submit_date varchar(10),
  @done_date varchar(10),
  @Stat varchar(7),
  @Err varchar(3),
  @Txt varchar(20),
  @MsgContent varchar(300)
AS
  INSERT INTO SMGP_Report(MsgID,sub,dlvrd,Submit_date,done_date,Stat,Err,Txt,MsgContent)
  VALUES (@MsgID,@sub,@dlvrd,@Submit_date,@done_date,@Stat,@Err,@Txt,@MsgContent)






GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




CREATE    PROC SMGP13_SUBMITHISTORY
  @SequenceID 	bigint,
  @Msg_Id 	varchar(10),
  @RespMsgID    varchar(10),
  @RespStatus   int
AS
  DECLARE @ID	         int
  DECLARE @MT_SequenceID bigint
  DECLARE @MT_SendCount	 int
  DECLARE @MT_Date	 datetime
  DECLARE @MsgType	 smallint
  DECLARE @NeedReport	 smallint
  DECLARE @Priority	 smallint
  DECLARE @ServiceID	 varchar(10)
  DECLARE @FeeType	 varchar(2)
  DECLARE @FeeCode 	 varchar(6)
  DECLARE @FixedFee	 varchar(6)
  DECLARE @MsgFormat	 smallint
  DECLARE @ValidTime	 varchar(17)
  DECLARE @AtTime	 varchar(17)
  DECLARE @SrcTermID	 varchar(21)
  DECLARE @ChargeTermID	 varchar(21)
  DECLARE @DestTermID	 varchar(21)
  DECLARE @MsgContent	 varchar(300)

  SET @ID = -1
  SELECT 
    @ID             = ID,
    @MT_SendCount   = MT_SendCount,
    @MT_Date        = MT_Date,
    @MsgType        = MsgType,
    @NeedReport     = NeedReport,
    @Priority       = Priority,
    @ServiceID      = ServiceID,
    @FeeType        = FeeType,
    @FeeCode        = FeeCode,
    @FixedFee       = FixedFee,
    @MsgFormat      = MsgFormat,
    @ValidTime      = ValidTime,
    @AtTime         = AtTime,
    @SrcTermID      = SrcTermID,
    @ChargeTermID   = ChargeTermID,
    @DestTermID     = DestTermID,
    @MsgContent     = MsgContent
  FROM
    SMGP_Submit
  WHERE
    MT_SequenceID = @SequenceID AND 
    MT_SendCount <> 0 
  ORDER BY ID DESC
  
  IF @ID <> -1 
  BEGIN
    INSERT INTO SMGP_Submit_History(MT_SequenceID, MT_SendCount, MT_Date, MsgType, NeedReport, Priority, 
                                    ServiceID, FeeType, FeeCode, FixedFee, MsgFormat, ValidTime, AtTime, 
                                    SrcTermID, ChargeTermID, DestTermID, MsgContent,RespMsgID,RespStatus) 
                            VALUES (@SequenceID, @MT_SendCount, @MT_Date, @MsgType, @NeedReport, @Priority, 
                                    @ServiceID, @FeeType, @FeeCode, @FixedFee, @MsgFormat, @ValidTime, @AtTime, 
                                    @SrcTermID, @ChargeTermID, @DestTermID, @MsgContent,@RespMsgID,@RespStatus)
    DELETE FROM SMGP_Submit WHERE ID = @ID
  END
             





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE  PROC SMGP13_UPDATESUBMIT
  @SubmitID int,
  @MT_SequenceID bigint
AS

  UPDATE 
    SMGP_Submit 
  SET 
    MT_SequenceID = @MT_SequenceID,
    MT_SendCount = MT_SendCount + 1 
  WHERE 
    ID = @SubmitID
      






GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




CREATE     PROC SMS2DB_RECEIVEMESSAGE
  @Mobile varchar(32),
  @SPNumber varchar(32),
  @ServiceID varchar(10),
  @CMD varchar(20),
  @MSG varchar(160),
  @LinkID varchar(10)
AS
  DECLARE @MobileType Char(2)

  IF SubString(@Mobile,3,1) in ('4','5','6','7','8','9') 
     SET @MobileType = '59'
  ELSE 
     SET @MobileType = '14'
  


  IF @MobileType = '59'
  BEGIN
    IF (@CMD = 'BStart') OR (@CMD = 'BStop')
    BEGIN
      EXECUTE Jxt2DB..JXT2_ReceviceMsg_EXT '085859',@SPNumber,@ServiceID,@Mobile,@CMD,@MSG,@MobileType,'' 
      RETURN
    END

    IF (LEN(@SPNumber) >= 6) AND (SUBSTRING(@SPNumber,1,6) = '085859') 
    BEGIN
      SET @SPNumber = SubString(@SPNumber,7,Len(@SPNumber)) 
      EXECUTE Jxt2DB..JXT2_ReceviceMsg_EXT '085859',@SPNumber,@ServiceID,@Mobile,@CMD,@MSG,@MobileType,''
    END 

    RETURN
  END


  IF @MobileType = '14'
  BEGIN   
    IF LEN(@SPNumber) > 5
    BEGIN
      DECLARE @SPNumber_User varchar(21)
      SET @SPNumber_User = SubString(@SPNumber,6,Len(@SPNumber)  - 5 )
    END

    EXECUTE Jxt2DB..JXT2_ReceviceMsg_EXT @SPNumber,@SPNumber_User,@ServiceID,@Mobile,@CMD,@MSG,@MobileType,@LinkID
    RETURN
  END





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
上一页 1 2 34
💿 文件大小 8769 K
👤 上传用户 yxm_325
📂 所属分类其他
🏷️ 相关标签

#cmpp #3.0 #网关 #代码
⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -