sms3ex.sql

CMPP和SMPP协议比较

      @Msg_Fmt, @Msg_src, @FeeType, @FeeCode, @ValId_Time, @At_Time, @Src_Id, @Dest_terminal_Id, 
      @Dest_terminal_type, @Msg_Content, @LinkID
     )

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




CREATE     PROC SP_CMPP30_SUBMITERROR
  @MT_SendCount		Int,
  @MT_AutoClearError	Char(1),
  @MT_SequenceID	bigint,
  @Msg_Id		varchar(20),
  @ErrorCode		int,
  @ErrorInfo		varchar(100)
AS
  
  INSERT INTO CMPP30_SUBMIT_ERROR
     (
       MT_SequenceID, MT_SendCount,  MT_SubmitType, MT_Date, Msg_Id, Pk_total,Pk_number, 
       Registered_Delivery, Msg_level, Service_Id, Fee_UserType, Fee_terminal_Id,Fee_terminal_type, 
       TP_pId, TP_udhi, Msg_Fmt, Msg_src, FeeType, FeeCode, ValId_Time, At_Time,Src_Id,Dest_terminal_Id,   
       Dest_terminal_type, Msg_Content, LinkID, ErrorCode, ErrorInfo, ErrorDate
     )
  SELECT
       MT_SequenceID, MT_SendCount,  MT_SubmitType, MT_Date, @Msg_Id, Pk_total,Pk_number, 
       Registered_Delivery, Msg_level, Service_Id, Fee_UserType, Fee_terminal_Id,Fee_terminal_type, 
       TP_pId, TP_udhi, Msg_Fmt, Msg_src, FeeType, FeeCode, ValId_Time, At_Time,Src_Id,Dest_terminal_Id,   
       Dest_terminal_type, Msg_Content, LinkID, @ErrorCode, @ErrorInfo, GetDate()
  FROM  CMPP30_SUBMIT WHERE
     MT_SequenceID = @MT_SequenceID AND 
     MT_SendCount <> 0
  ORDER BY  ID DESC


  /*自动清理发送失败的记录*/
  IF @MT_AutoClearError = '1'
  BEGIN
    DELETE FROM CMPP30_SUBMIT WHERE MT_SendCount >= @MT_SendCount
  END
 

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO





















CREATE                PROC SP_CMPP30_SUBMITHISTORY
  @SequenceID 	bigint,
  @Msg_Id 	varchar(20)
AS

  INSERT INTO CMPP30_SUBMIT_HISTORY
     (
       MT_SequenceID, MT_SendCount,  MT_SubmitType, MT_Date, Msg_Id, Pk_total,Pk_number, 
       Registered_Delivery, Msg_level, Service_Id, Fee_UserType, Fee_terminal_Id,Fee_terminal_type, 
       TP_pId, TP_udhi, Msg_Fmt, Msg_src, FeeType, FeeCode, ValId_Time, At_Time,Src_Id,Dest_terminal_Id,   
       Dest_terminal_type, Msg_Content, LinkID
     )
  SELECT TOP 1
       MT_SequenceID, MT_SendCount,  MT_SubmitType, MT_Date, @Msg_Id, Pk_total,Pk_number, 
       Registered_Delivery, Msg_level, Service_Id, Fee_UserType, Fee_terminal_Id,Fee_terminal_type, 
       TP_pId, TP_udhi, Msg_Fmt, Msg_src, FeeType, FeeCode, ValId_Time, At_Time,Src_Id,Dest_terminal_Id,   
       Dest_terminal_type, Msg_Content, LinkID
  FROM  CMPP30_SUBMIT WHERE
     MT_SequenceID = @SequenceID --AND 
     --MT_SendCount <> 0
  ORDER BY  ID DESC


  DELETE FROM CMPP30_SUBMIT WHERE MT_SequenceID = @SequenceID
 
 







GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO








/****** Object:  Stored Procedure dbo.SP_CMPP30_SyncOrderRelationReq    Script Date: 2007-4-11 20:20:44 ******/


















CREATE                PROCEDURE [dbo].[SP_CMPP30_SyncOrderRelationReq] 
(	
      @MsgType 			 varchar(30),
      @TransactionID 		 varchar(30),
      @Version  		 varchar(10),
      @Send_Address_DeviceType   int,
      @Send_Address_DeviceID     varchar(30),
      @Dest_Address_DeviceType   int,
      @Dest_Address_DeviceID     varchar(30),
      @FeeUser_ID_UserIDType     int,
      @FeeUser_ID_MSISDN  	 varchar(32),
      @FeeUser_ID_PseudoCode 	 varchar(100),
      @DestUser_ID_UserIDType 	 int,
      @DestUser_ID_MSISDN        varchar(32),
      @DestUser_ID_PseudoCode 	 varchar(100),
      @LinkID 			 varchar(20),
      @ActionID 		 int,
      @ActionReasonID 		 int,
      @SPID 			 varchar(10),
      @SPServiceID		 varchar(10),
      @AccessMode 		 int,
      @FeatureStr 		 varchar(100)

)
AS
  --@FeatureStr   特服号 短信内容

  INSERT INTO MISC16_SyncOrderRelationReq
             (MsgType,TransactionID,Version,Send_Address_DeviceType,Send_Address_DeviceID,
              Dest_Address_DeviceType,Dest_Address_DeviceID,FeeUser_ID_UserIDType,
              FeeUser_ID_MSISDN,FeeUser_ID_PseudoCode,DestUser_ID_UserIDType,DestUser_ID_MSISDN,
              DestUser_ID_PseudoCode,LinkID,ActionID,ActionReasonID,SPID,SPServiceID,
              AccessMode,FeatureStr) 
       VALUES 
             (@MsgType,@TransactionID,@Version,@Send_Address_DeviceType,@Send_Address_DeviceID,
              @Dest_Address_DeviceType,@Dest_Address_DeviceID,@FeeUser_ID_UserIDType,
              @FeeUser_ID_MSISDN,@FeeUser_ID_PseudoCode,@DestUser_ID_UserIDType,@DestUser_ID_MSISDN,
              @DestUser_ID_PseudoCode,@LinkID,@ActionID,@ActionReasonID,@SPID,@SPServiceID,
              @AccessMode,@FeatureStr)
 
  DECLARE @Mobile varchar(21)
  --用户手机标识                          
  IF (@DestUser_ID_UserIDType = 1) SET @Mobile = @DestUser_ID_MSISDN
  --用户伪码标识 
  IF (@DestUser_ID_UserIDType = 2) SET @Mobile = @DestUser_ID_MSISDN                          
  --两者同时标识 
  IF (@DestUser_ID_UserIDType = 3) SET @Mobile = @DestUser_ID_MSISDN                      
  /*
  1： 开通服务；
  2： 停止服务；
  3： 激活服务；
  4： 暂停服务；
  */

  IF (@ActionID = 1) OR (@ActionID = 2)
  BEGIN
    INSERT INTO Ext5DB..WaitForMiscOrder(ServiceID,Mobile,ActionID)
                                  VALUES(@SPServiceID,@Mobile,@ActionID)  
  END

 

/*加入业务语句或存贮过程实现业务订购或取消等*/






GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO














CREATE        PROC SP_CMPP30_UPDATESUBMIT
  @SubmitID int,
  @MT_SequenceID bigint
AS

  UPDATE  CMPP30_SUBMIT SET 
    MT_SequenceID = @MT_SequenceID,
    MT_SendCount = MT_SendCount + 1,
    MT_Date = GetDate()
  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 SP_SYSTEM_DATAPOOL
  @Protocol	varchar(6),
  @Index	int
AS
 
  /*过滤字管理*/ 
  IF @Index = 1
  BEGIN
    SELECT ID,Protocol,BadWord AS fValue FROM SYSTEM_BADWORD WHERE Protocol = @Protocol
  END
  /*黑名单管理*/ 
  IF @Index = 2
  BEGIN
    SELECT ID,Protocol,Mobile AS fValue FROM SYSTEM_BLACKLIST WHERE Protocol = @Protocol
  END
 
  
 /*以上为统计部分*/ 
  IF @Index = 3 --SMPP3x
  BEGIN
    SELECT COUNT(*) AS ICount FROM SMPP3x_SUBMIT
  END

  IF @Index = 4 --CMPP20
  BEGIN
    SELECT COUNT(*) AS ICount FROM CMPP20_SUBMIT
  END
 
 
  IF @Index = 5 --SGIP12
  BEGIN
    SELECT COUNT(*) AS ICount FROM SGIP12_SUBMIT
  END
 

  IF @Index = 6 --CMPP30
  BEGIN
    SELECT COUNT(*) AS ICount FROM CMPP30_SUBMIT
  END






GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO