create proc usp_getSend
|
as
|
select * from SMS_SEND where status=0
|
|
go
|
create proc usp_updteSend
|
@batchid int
|
as
|
update SMS_SEND set status=1 where batchid=@batchid
|
go
|
|
create proc usp_getSendNo
|
@batchid int
|
as
|
select mobileNo from SMS_SEND_NO where batchid=@batchid
|
go
|
|
create proc usp_addSmsInfo
|
@GUID varchar(50),
|
@SERV_NO char(8),
|
@LONG_SERV_NO varchar(21),
|
@SERV_CODE varchar(10),
|
@NEED_DOWN_TIME datetime,
|
@MOBILE_NO varchar(21),
|
@MOBILE_NO_TYPE char(1),
|
@SMS_CONTENT varchar(160),
|
@INTO_TIME datetime,
|
@WAPURL varchar(255),
|
@MSGSUM int,
|
@MSGSUB int,
|
@batchid int
|
as
|
INSERT INTO [dbo].[SMS_MT] (GUID,SERV_NO,[LONG_SERV_NO],[SERV_CODE],NEED_DOWN_TIME,MOBILE_NO,MOBILE_NO_TYPE,FEE_TYPE,FEE,SMS_CONTENT,INTO_TIME,WAPURL,MSGSUM,MSGSUB,batchid)
|
values(@GUID,@SERV_NO,@LONG_SERV_NO,@SERV_CODE,@NEED_DOWN_TIME,@MOBILE_NO,@MOBILE_NO_TYPE,'01',0,@SMS_CONTENT,@INTO_TIME,@WAPURL,@MSGSUM,@MSGSUB,@batchid)
|
|
go
|
|
|
create proc usp_updateReportMobile
|
@REPORT_SUCCESS int,
|
@REPORT_TIME datetime,
|
@REPORT_STAT varchar(20),
|
@REPORT_DONWTIME datetime,
|
@MSGID varchar(50),
|
@MOBILE_NO varchar(21)
|
as
|
UPDATE SMS_MT SET REPORT=1,REPORT_SUCCESS=@REPORT_SUCCESS,REPORT_TIME=@REPORT_TIME,REPORT_STAT=@REPORT_STAT,REPORT_DONWTIME=@REPORT_DONWTIME
|
WHERE MSGID=@MSGID AND MOBILE_NO=@MOBILE_NO
|
go
|
|
create proc usp_updateReport
|
@REPORT_SUCCESS int,
|
@REPORT_TIME datetime,
|
@REPORT_STAT varchar(20),
|
@REPORT_DONWTIME datetime,
|
@MSGID varchar(50)
|
as
|
UPDATE SMS_MT SET REPORT=1,REPORT_SUCCESS=@REPORT_SUCCESS,REPORT_TIME=@REPORT_TIME,REPORT_STAT=@REPORT_STAT,
|
REPORT_DONWTIME=@REPORT_DONWTIME WHERE MSGID=@MSGID
|
|
go
|
|
create proc usp_updateMTSubmit
|
@MSGID varchar(50),
|
@FACT_DOWN_TIME datetime,
|
@SUBMIT_RESULT int,
|
@SUBMIT_RESEND int,
|
@GUID varchar(50)
|
as
|
UPDATE SMS_MT SET MSGID=@MSGID,FACT_DOWN_TIME=@FACT_DOWN_TIME,SUBMIT_RESULT=@SUBMIT_RESULT,
|
SUBMIT_RESEND=@SUBMIT_RESEND,STATUS=0
|
where GUID=@GUID
|
go
|
|
create proc usp_updateCMWAIT
|
@OUT_GATEWAY_ID varchar(20),
|
@GUID varchar(50)
|
|
as
|
|
UPDATE SMS_MT_CM_WAIT SET OUT_GATEWAY_ID=@OUT_GATEWAY_ID WHERE GUID=@GUID
|
go
|
|
create proc usp_updateCMWAITReplace
|
@OUT_GATEWAY_ID_DES varchar(20),
|
@OUT_GATEWAY_ID_SRC varchar(20)
|
as
|
UPDATE SMS_MT_CM_WAIT SET OUT_GATEWAY_ID=@OUT_GATEWAY_ID_DES where OUT_GATEWAY_ID=@OUT_GATEWAY_ID_SRC
|
go
|
|
create proc usp_updateCU
|
@OUT_GATEWAY_ID varchar(20),
|
@GUID varchar(50)
|
as
|
UPDATE SMS_MT_CU_WAIT SET OUT_GATEWAY_ID=@OUT_GATEWAY_ID WHERE GUID=@GUID
|
go
|
|
create proc usp_updateCUReplace
|
@OUT_GATEWAY_ID_DES varchar(20),
|
@OUT_GATEWAY_ID_SRC varchar(20)
|
as
|
UPDATE SMS_MT_CU_WAIT SET OUT_GATEWAY_ID=@OUT_GATEWAY_ID_DES where OUT_GATEWAY_ID=@OUT_GATEWAY_ID_SRC
|
|
go
|
|
create proc usp_updateTEL
|
@OUT_GATEWAY_ID varchar(20),
|
@GUID varchar(50)
|
as
|
UPDATE SMS_MT_TEL_WAIT SET OUT_GATEWAY_ID=@OUT_GATEWAY_ID WHERE GUID=@GUID
|
go
|
create proc usp_updateTELReplace
|
@OUT_GATEWAY_ID_DES varchar(20),
|
@OUT_GATEWAY_ID_SRC varchar(20)
|
as
|
UPDATE SMS_MT_TEL_WAIT SET OUT_GATEWAY_ID=@OUT_GATEWAY_ID_DES where OUT_GATEWAY_ID=@OUT_GATEWAY_ID_SRC
|
go
|
|
create proc usp_getMT_CU
|
|
as
|
select top 300 * from SMS_MT_CU_WAIT where (DOWN_TIME<getdate() or DOWN_TIME IS NULL) and (OUT_GATEWAY_ID IS NULL OR len(OUT_GATEWAY_ID)<=0)
|
|
go
|
|
create proc usp_getMT_TEL
|
|
as
|
select top 300 * from SMS_MT_TEL_WAIT where (DOWN_TIME<getdate() or DOWN_TIME IS NULL) and (OUT_GATEWAY_ID IS NULL OR len(OUT_GATEWAY_ID)<=0)
|
|
go
|
|
|
create proc usp_getMT_CM
|
as
|
select top 300 * from SMS_MT_CM_WAIT where (DOWN_TIME<getdate() or DOWN_TIME IS NULL) and (OUT_GATEWAY_ID IS NULL OR len(OUT_GATEWAY_ID)<=0)
|
go
|
|
create proc usp_del_MT_TEL
|
@GUID varchar(50)
|
as
|
DELETE FROM SMS_MT_TEL_WAIT WHERE GUID=@GUID
|
go
|
|
create proc usp_del_MT_CU
|
@GUID varchar(50)
|
as
|
DELETE FROM SMS_MT_CU_WAIT WHERE GUID=@GUID
|
go
|
|
create proc usp_del_MT_CM
|
@GUID varchar(50)
|
as
|
DELETE FROM SMS_MT_CM_WAIT WHERE GUID=@GUID
|
go
|
|
create proc usp_addMTTOWAIT
|
as
|
insert into SMS_MT_CM_WAIT(GUID,DOWN_TIME,LONG_SERV_NO,SERV_CODE,MOBILE_NO,MOBILE_NO_TYPE,FEE_TYPE,FEE,SMS_CONTENT,MSGSUM,MSGSUB)
|
select GUID,NEED_DOWN_TIME,LONG_SERV_NO,SERV_CODE,MOBILE_NO,MOBILE_NO_TYPE,FEE_TYPE,FEE,SMS_CONTENT,MSGSUM,MSGSUB from SMS_MT a
|
where MSGID is null and
|
not exists(select 1 from SMS_MT_CM_WAIT b where a.GUID=b.GUID)
|
go
|
