📄 sms3ex.sql
字号:
CONSTRAINT [DF_Submit_Error_TP_udhi] DEFAULT (0) FOR [TP_udhi],
CONSTRAINT [DF_Submit_Error_Msg_Fmt] DEFAULT (15) FOR [Msg_Fmt],
CONSTRAINT [DF_Submit_Error_FeeCode] DEFAULT (0) FOR [FeeCode],
CONSTRAINT [DF_Submit_Error_ValId_Time] DEFAULT (0) FOR [ValId_Time],
CONSTRAINT [DF_Submit_Error_At_Time] DEFAULT (0) FOR [At_Time],
CONSTRAINT [DF_Submit_Error_Dest_terminal_type] DEFAULT (0) FOR [Dest_terminal_type],
CONSTRAINT [DF_Submit_Error_ErrorDate] DEFAULT (getdate()) FOR [ErrorDate]
GO
ALTER TABLE [dbo].[CMPP30_SUBMIT_HISTORY] ADD
CONSTRAINT [DF_Submit_History_MT_SequenceID] DEFAULT ((-1)) FOR [MT_SequenceID],
CONSTRAINT [DF_Submit_History_MT_SendCount] DEFAULT (0) FOR [MT_SendCount],
CONSTRAINT [DF_Submit_History_MT_Date] DEFAULT (getdate()) FOR [MT_Date],
CONSTRAINT [DF_Submit_History_Msg_Id] DEFAULT (0) FOR [Msg_Id],
CONSTRAINT [DF_Submit_History_Pk_total] DEFAULT (1) FOR [Pk_total],
CONSTRAINT [DF_Submit_History_Pk_number] DEFAULT (1) FOR [Pk_number],
CONSTRAINT [DF_Submit_History_Registered_Delivery] DEFAULT (0) FOR [Registered_Delivery],
CONSTRAINT [DF_Submit_History_Msg_level] DEFAULT (7) FOR [Msg_level],
CONSTRAINT [DF_Submit_History_Fee_UserType] DEFAULT (0) FOR [Fee_UserType],
CONSTRAINT [DF_Submit_History_Fee_terminal_type] DEFAULT (0) FOR [Fee_terminal_type],
CONSTRAINT [DF_Submit_History_TP_pId] DEFAULT (0) FOR [TP_pId],
CONSTRAINT [DF_Submit_History_TP_udhi] DEFAULT (0) FOR [TP_udhi],
CONSTRAINT [DF_Submit_History_Msg_Fmt] DEFAULT (15) FOR [Msg_Fmt],
CONSTRAINT [DF_Submit_History_FeeCode] DEFAULT (0) FOR [FeeCode],
CONSTRAINT [DF_Submit_History_ValId_Time] DEFAULT (0) FOR [ValId_Time],
CONSTRAINT [DF_Submit_History_At_Time] DEFAULT (0) FOR [At_Time],
CONSTRAINT [DF_Submit_History_Dest_terminal_type] DEFAULT (0) FOR [Dest_terminal_type]
GO
ALTER TABLE [dbo].[MISC16_SyncOrderRelationReq] ADD
CONSTRAINT [DF_SyncOrderRelationReq_MO_Date] DEFAULT (getdate()) FOR [MO_Date]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.VIEW_CMPP30_SUBMITREPORT
AS
SELECT dbo.CMPP30_SUBMIT_HISTORY.MT_SequenceID,
dbo.CMPP30_SUBMIT_HISTORY.MT_SendCount,
dbo.CMPP30_SUBMIT_HISTORY.MT_SubmitType,
dbo.CMPP30_SUBMIT_HISTORY.MT_Date, dbo.CMPP30_SUBMIT_HISTORY.Msg_Id,
dbo.CMPP30_SUBMIT_HISTORY.Pk_total,
dbo.CMPP30_SUBMIT_HISTORY.Pk_number,
dbo.CMPP30_SUBMIT_HISTORY.Registered_Delivery,
dbo.CMPP30_SUBMIT_HISTORY.Msg_level,
dbo.CMPP30_SUBMIT_HISTORY.Service_Id,
dbo.CMPP30_SUBMIT_HISTORY.Fee_UserType,
dbo.CMPP30_SUBMIT_HISTORY.Fee_terminal_Id,
dbo.CMPP30_SUBMIT_HISTORY.Fee_terminal_type,
dbo.CMPP30_SUBMIT_HISTORY.TP_pId, dbo.CMPP30_SUBMIT_HISTORY.TP_udhi,
dbo.CMPP30_SUBMIT_HISTORY.Msg_Fmt,
dbo.CMPP30_SUBMIT_HISTORY.Msg_src, dbo.CMPP30_SUBMIT_HISTORY.FeeType,
dbo.CMPP30_SUBMIT_HISTORY.FeeCode,
dbo.CMPP30_SUBMIT_HISTORY.ValId_Time,
dbo.CMPP30_SUBMIT_HISTORY.At_Time, dbo.CMPP30_SUBMIT_HISTORY.Src_Id,
dbo.CMPP30_SUBMIT_HISTORY.Dest_terminal_Id,
dbo.CMPP30_SUBMIT_HISTORY.Dest_terminal_type,
dbo.CMPP30_SUBMIT_HISTORY.Msg_Content,
dbo.CMPP30_SUBMIT_HISTORY.LinkID, dbo.CMPP30_REPORT.Stat,
dbo.CMPP30_REPORT.Submit_time, dbo.CMPP30_REPORT.Done_time,
dbo.CMPP30_REPORT.Dest_terminal_Id AS Expr1,
dbo.CMPP30_REPORT.SMSC_sequence, dbo.CMPP30_REPORT.MO_Date
FROM dbo.CMPP30_SUBMIT_HISTORY INNER JOIN
dbo.CMPP30_REPORT ON
dbo.CMPP30_SUBMIT_HISTORY.Msg_Id = dbo.CMPP30_REPORT.Msg_Id
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_GETMESSAGE
@MT_SendCount Int,
@MT_ProtocolID Varchar(10),
@MT_BadwordFilter Char(1),
@MT_BadwordReplace Varchar(10),
@MT_BlackListFilter Char(1),
@MT_RetrySendTime Int
AS
/*
@MT_SendCount //重发次数
@MT_ProtocolID //协议标识(扩展字段)
@MT_BadwordFilter //是否过滤短信内容 1 过滤 0 不过滤
@MT_BadwordReplace //过滤短信内容替换为
@MT_BlackListFilter //是否过滤黑名单 1 过滤 0 不过滤
@MT_AutoClearError //是否自动清理发送失败的记录 1 清理 0 不清理
@MT_RetrySendTime //重发时间
*/
/*黑名单过虑*/
IF @MT_BlackListFilter = '1'
BEGIN
DELETE FROM CMPP30_SUBMIT WHERE Dest_terminal_Id IN
(SELECT Mobile FROM SYSTEM_BLACKLIST WHERE Protocol = @MT_ProtocolID)
END
/*短信内容过滤*/
IF @MT_BadwordFilter = '1'
BEGIN
DECLARE @BadWord VarChar(20)
DECLARE FilterCursor SCROLL CURSOR FOR
SELECT BadWord FROM SYSTEM_BADWORD WHERE Protocol = @MT_ProtocolID
OPEN FilterCursor
FETCH FIRST FROM FilterCursor INTO @BadWord
WHILE @@fetch_status = 0
BEGIN
UPDATE CMPP30_SUBMIT SET Msg_Content = REPLACE(Msg_Content,@BadWord,@MT_BadwordReplace) WHERE MT_SendCount = 0
FETCH NEXT FROM FilterCursor INTO @BadWord
END
CLOSE FilterCursor
DEALLOCATE FilterCursor
END
/*取发信数据*/
IF @MT_SendCount < 1 SET @MT_SendCount = 1
SELECT TOP 1000 * FROM CMPP30_SUBMIT WHERE (MT_SendCount < 1) OR
(MT_SendCount < @MT_SendCount AND MT_Date < DATEADD(minute,-@MT_RetrySendTime,GetDate()))
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_RECEIVEMESSAGE Script Date: 2004-06-30 16:14:20 ******/
CREATE PROCEDURE SP_CMPP30_RECEIVEMESSAGE
@Msg_ID varchar(20), --信息标识
@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(1000), --短信内容
@LinkID varchar(20), --LinkID
@Pk_longId smallint, --超长短信 组合ID
@Pk_total smallint, --超长短信 共几条
@Pk_number smallint --超长短信 第几条
AS
INSERT INTO CMPP30_DELIVER
(Msg_ID,Dest_Id,Service_Id,TP_pid,TP_udhi,Pk_longId,Pk_total,Pk_number,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,@Pk_longId,@Pk_total,@Pk_number,@Msg_Fmt,
@Src_terminal_Id,@Src_terminal_type, @Registered_Delivery,@Msg_Length,@Msg_Content,@LinkID)
/*加入业务语句或存贮过程实现业务*/
IF @Msg_Content = 'CMCCTEST'
BEGIN
--修改企业代码、接入号...
INSERT INTO CMPP30_SUBMIT( 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 (1,1,1,1,'CMCCTEST',0,@Src_terminal_Id,0,0,0,15,'929024','01','000000','','','05550124',@Src_terminal_Id,0,'免费信息发送ABC',@LinkID)
RETURN
END
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 SP_CMPP30_RECEIVEREPORT
@Msg_Id varchar(20),
@Stat varchar(7),
@Submit_time varchar(10),
@Done_time varchar(10),
@Dest_terminal_Id varchar(32),
@SMSC_sequence bigint
AS
INSERT INTO CMPP30_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 SP_CMPP30_SUBMIT
@MT_SubmitType char(1),
@Msg_Id bigint,
@Pk_total smallint,
@Pk_number smallint,
@Registered_Delivery smallint,
@Msg_level smallint,
@Service_Id varchar(10),
@Fee_UserType smallint,
@Fee_terminal_Id varchar(32),
@Fee_terminal_type smallint,
@TP_pId smallint,
@TP_udhi smallint,
@Msg_Fmt smallint,
@Msg_src varchar(6),
@FeeType varchar(2),
@FeeCode varchar(6),
@ValId_Time varchar(17),
@At_Time varchar(17),
@Src_Id varchar(21),
@Dest_terminal_Id varchar(32),
@Dest_terminal_type smallint,
@Msg_Content varchar(1000),
@LinkID varchar(20)
AS
INSERT INTO CMPP30_SUBMIT
(
MT_SubmitType, 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_SubmitType, @Pk_total, @Pk_number, @Registered_Delivery,
@Msg_level, @Service_Id, @Fee_UserType, @Fee_terminal_Id, @Fee_terminal_type, @TP_pId, @TP_udhi,
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -