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