📄 sms2db.sql
字号:
@CMD VarChar(20),
@MSG VarChar(160)
AS
INSERT INTO MobileOrderRelation(Mobile,SPNumber,ServiceID,CMD,MSG) VALUES
(@Mobile,@SPNumber,@ServiceID,@CMD,@MSG)
EXEC SMS2DB_RECEIVEMESSAGE @Mobile,@SPNumber,@ServiceID,@CMD,@MSG,''
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.CMPP3_RECEIVEMESSAGE Script Date: 2004-06-30 16:14:20 ******/
CREATE PROCEDURE CMPP3_RECEIVEMESSAGE
@Msg_ID bigint,
@Dest_Id varchar(21),
@Service_Id varchar(10),
@TP_pid smallint,
@TP_udhi smallint,
@Msg_Fmt smallint,
@Src_terminal_Id varchar(32),
@Src_terminal_type smallint,
@Registered_Delivery smallint,
@Msg_Length smallint,
@Msg_Content varchar(200),
@LinkID varchar(20)
AS
INSERT INTO CMPP_Deliver(Msg_ID,Dest_Id,Service_Id,TP_pid,TP_udhi,Msg_Fmt,Src_terminal_Id,Src_terminal_type,Registered_Delivery,Msg_Length,Msg_Content,LinkID)
VALUES (@Msg_ID,@Dest_Id,@Service_Id,@TP_pid,@TP_udhi,@Msg_Fmt,@Src_terminal_Id,@Src_terminal_type,@Registered_Delivery,@Msg_Length,@Msg_Content,@LinkID)
IF @Msg_Content = 'CMCCTEST'
BEGIN
SET @Msg_Content = '浙江,华海科技,01616,911132,'
SET @Msg_Content = @Msg_Content + DATENAME(yyyy,GetDate()) + '年' + DATENAME(mm,GetDate()) + '月'
SET @Msg_Content = @Msg_Content + DATENAME(dd,GetDate()) + '日' + DATENAME(hh,GetDate()) + '时'
SET @Msg_Content = @Msg_Content + DATENAME(mi,GetDate()) + '分' + DATENAME(ss,GetDate()) + '秒'
SET @Msg_Content = @Msg_Content + DATENAME(weekday,GetDate())
--EXEC CMPP3_SUBMIT 0,1,1,0,7,'CMCCTEST',0,'',0,0,0,15,'911132','02','1',0,0,'01616',@Src_terminal_Id,0,@Msg_Content,@LinkID
RETURN
END
EXEC CMPP3_MobileOrderRelation @Src_terminal_Id,@Dest_Id,@Service_Id,'MSG',@Msg_Content
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/****** Object: Stored Procedure dbo.CMPP3_RECEIVEREPORT Script Date: 2004-06-30 16:14:20 ******/
CREATE PROCEDURE CMPP3_RECEIVEREPORT
@Msg_Id bigint,
@Stat varchar(7),
@Submit_time varchar(10),
@Done_time varchar(10),
@Dest_terminal_Id varchar(32),
@SMSC_sequence int
AS
INSERT INTO CMPP_Report(Msg_Id,Stat,Submit_time,Done_time,Dest_terminal_Id,SMSC_sequence)
VALUES (@Msg_Id,@Stat,@Submit_time,@Done_time,@Dest_terminal_Id,@SMSC_sequence)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROC CMPP3_SUBMITHISTORY
@SequenceID bigint,
@Msg_Id bigint
AS
DECLARE @ID int
DECLARE @MT_SequenceID bigint
DECLARE @MT_SendCount int
DECLARE @MT_BaseFetchFlag int
DECLARE @MT_Date datetime
DECLARE @Pk_total smallint
DECLARE @Pk_number smallint
DECLARE @Registered_Delivery smallint
DECLARE @Msg_level smallint
DECLARE @Service_Id varchar(10)
DECLARE @Fee_UserType smallint
DECLARE @Fee_terminal_Id varchar(32)
DECLARE @Fee_terminal_type smallint
DECLARE @TP_pId smallint
DECLARE @TP_udhi smallint
DECLARE @Msg_Fmt smallint
DECLARE @Msg_src varchar(6)
DECLARE @FeeType varchar(2)
DECLARE @FeeCode varchar(6)
DECLARE @ValId_Time varchar(17)
DECLARE @At_Time varchar(17)
DECLARE @Src_Id varchar (21)
DECLARE @Dest_terminal_Id varchar(32)
DECLARE @Dest_terminal_type smallint
DECLARE @Msg_Content varchar(160)
DECLARE @LinkID varchar(20)
SET @ID = -1
DECLARE SubmitCursor SCROLL CURSOR FOR
SELECT
ID,MT_SequenceID, MT_SendCount, MT_BaseFetchFlag, MT_Date, 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
CMPP_Submit
WHERE
MT_SequenceID = @SequenceID AND
MT_SendCount <> 0
ORDER BY ID DESC
OPEN SubmitCursor
FETCH FIRST FROM SubmitCursor INTO
@ID,@MT_SequenceID,@MT_SendCount, @MT_BaseFetchFlag, @MT_Date, @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
INSERT INTO CMPP_Submit_History
(
MT_SequenceID, MT_SendCount, MT_BaseFetchFlag, 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
)
VALUES
(
@MT_SequenceID, @MT_SendCount, @MT_BaseFetchFlag, @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
)
CLOSE SubmitCursor
DEALLOCATE SubmitCursor
IF @ID <> -1
BEGIN
DELETE FROM CMPP_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 PROCEDURE [dbo].[CMPP3_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
DECLARE @Mobile varchar(32)
DECLARE @SPNumber varchar(32)
DECLARE @ServiceID varchar(10)
DECLARE @CMD varchar(20)
DECLARE @MSG varchar(160)
INSERT INTO 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)
------------------------ 用户手机
IF (@DestUser_ID_UserIDType = 1) --用户手机标识
BEGIN
SET @Mobile = @DestUser_ID_MSISDN
END
IF (@DestUser_ID_UserIDType = 2) --用户伪码标识
BEGIN
---select @Mobile = @DestUser_ID_PseudoCode
SET @Mobile = @DestUser_ID_MSISDN
END
IF (@DestUser_ID_UserIDType = 3) --两者同时标识
BEGIN
SET @Mobile = @DestUser_ID_MSISDN
SET @SPNumber = @DestUser_ID_PseudoCode --规定SP号存伪码
END
-----------------------
-----------------------服务代码
SET @ServiceID = @SPServiceID
-----------------------
-----------------------命令定义
IF (@ActionID = 1)
BEGIN
SET @CMD = 'BStart' --开通服务
END
IF (@ActionID = 2)
BEGIN
SET @CMD = 'BStop' --停止服务
END
IF (@ActionID = 3)
BEGIN
SET @CMD = 'BActive' --激活服务
END
IF (@ActionID = 4)
BEGIN
SET @CMD = 'BPause' --暂停服务
END
-----------------------
INSERT INTO MobileOrderRelation(Mobile,SPNumber,ServiceID,CMD,MSG) VALUES
(@Mobile,@SPNumber,@ServiceID,@CMD,@MSG)
EXEC SMS2DB_RECEIVEMESSAGE @Mobile,@SPNumber,@ServiceID,@CMD,@MSG,@LinkID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROC CMPP3_UPDATESUBMIT
@SubmitID int,
@MT_SequenceID bigint
AS
UPDATE
CMPP_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 SGIP12_GETMESSAGE
@MT_BaseFetchFlag varchar(2)
AS
SELECT TOP 1000 * FROM SGIP_Submit WHERE MT_BaseFetchFlag = @MT_BaseFetchFlag AND MT_SendCount = 0
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROC SGIP12_RECEIVEMESSAGE
@UserNumber VarChar(20),
@SPNumber VarChar(20),
@TP_pid int,
@TP_udhi int,
@MessageCoding int,
@MessageLength int,
@MessageContent VarChar(300),
@LinkID VarChar(8)
AS
INSERT INTO SGIP_Deliver(UserNumber,SPNumber,TP_pid,TP_udhi,MessageCoding,MessageLength,MessageContent,LinkID)
VALUES(@UserNumber,@SPNumber,@TP_pid,@TP_udhi,@MessageCoding,@MessageLength,@MessageContent,@LinkID)
EXEC SMS2DB_RECEIVEMESSAGE @UserNumber,@SPNumber,'','MSG',@MessageContent,@LinkID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -