CREATE DATABASE Example
GO
use Example
GO
CREATE TABLE [reply] (
[id] [int] NOT NULL ,
[toCell] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fromCell] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[message] [nvarchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[messageref] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[systemdate] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_reply] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [message] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[mobileno] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[message] [nvarchar] (160) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[messageref] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[status] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[drtry] [int] DEFAULT (0),
[createdate] [datetime] NULL CONSTRAINT [DF_message_createdate] DEFAULT (getdate()),
CONSTRAINT [PK_status] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [trSendSMS] ON dbo.message
FOR INSERT
AS
declare @url as nvarchar(300), @Msg as nvarchar(160),@hr as int,@ID as int
declare @res as nvarchar(300),@Start as int, @End as int
declare @Ref as nvarchar(100),@Mobile as nvarchar(50)
select @Url=''
select @Msg=message , @Mobile=mobileno from message where id=@@Identity
select @Msg=replace(@Msg,'\',' ')
select @Msg=ltrim(rtrim(Replace(Replace(
Replace(Replace(Replace(Replace (Replace(Replace(
Replace(Replace(
Replace(Replace(Replace(
Replace(Replace(Replace(
Replace(@Msg,'%','%25'),'+', '%2B'),'&', '%26')
,'?', '%3F'),';', '%3B'),':',
'%3A'),'}', '%7D'),
'{', '%7B'),'"', '%22')
,' ', '+'),'''', '%27'),'~'
, '%7E'),'`', '%60')
,'!', '%21'),'#'
, '%23'),'$'
, '%24'),'^', '%5E')))
select @Url = 'http://www.logicsms.co.za/postmsg2.aspx?'
select @Url = @Url + 'username=yourusername&password=yourpassword&mobile='
select @Url = @Url + @Mobile +'&Originator=REPLY&message= ' + @Msg
EXEC @hr=sp_OACreate 'MSXML2.XMLHTTP', @ID OUT
select @hr
IF @hr <> 0
BEGIN
print 'Error creating winhttp object'
EXEC sp_OAGetErrorInfo @ID
RETURN
END
EXEC @hr = sp_OAMethod @ID, 'Open',NULL,'POST',@Url,0
IF @hr <> 0
BEGIN
print 'Error opening Url'
EXEC sp_OAGetErrorInfo @ID
RETURN
END
EXEC @hr = sp_OAMethod @ID, 'Send'
IF @hr <> 0
BEGIN
print 'Error Sending Data'
EXEC sp_OAGetErrorInfo @ID
RETURN
END
EXEC @hr = sp_OAGetProperty @ID, 'ResponseText', @Res OUT
IF @hr <> 0
BEGIN
print 'error reading response'
EXEC sp_OAGetErrorInfo @ID
RETURN
END
select @Res
if Charindex(ltrim(rtrim(lower('sent'))),ltrim(rtrim(lower(@Res))))>0
BEGIN
select @Start=Charindex('<Id>',@Res) + 4
select @End=Charindex('</Id>',@Res)
select @Ref=Substring(@Res,@Start,@End-@Start)
Update message set MessageRef=@Ref,status='SENT' where [id]=@@Identity
END ELSE
BEGIN
Update message set status='FAILED' where [id]=@@Identity
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE spGetDrs
AS
declare @Url as nvarchar(4000),@Res as varchar(8000),@mRef as nvarchar(30),@mRefs as nvarchar(3400)
declare @hr as int,@ID as int, @i as int
select @Url=''
select @mRefs=''
DECLARE smscursor CURSOR FOR
SELECT top 120 messageref FROM message
WHERE createdate>getdate()-1 and status='SENT' order by drtry desc
OPEN smscursor
-- Perform the first fetch.
FETCH NEXT FROM smscursor INTO @mRef
select @i=0
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
select @i=@i+1
if not @mRef is null
begin
if @i=1
begin
select @mRefs=@mRefs + ltrim(rtrim(@mRef))
end else
begin
select @mRefs=@mRefs + ',' + ltrim(rtrim(@mRef))
end
end
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM smscursor INTO @mRef
END
CLOSE smscursor
DEALLOCATE smscursor
if @i>0
begin
select @Url = 'http://www.logicsms.net/querysmsbulk.aspx?'
select @Url = @Url + 'username=yourusername&pass=yourpassword&ids=' + @mRefs
print @Url
EXEC @hr=sp_OACreate 'MSXML2.XMLHTTP', @ID OUT
IF @hr <> 0
BEGIN
print 'Error creating winhttp object'
EXEC sp_OAGetErrorInfo @ID
RETURN
END
EXEC @hr = sp_OAMethod @ID, 'Open',NULL,'POST',@Url,0
IF @hr <> 0
BEGIN
print 'Error opening Url'
EXEC sp_OAGetErrorInfo @ID
RETURN
END
EXEC @hr = sp_OAMethod @ID, 'Send'
IF @hr <> 0
BEGIN
print 'Error Sending Data'
EXEC sp_OAGetErrorInfo @ID
RETURN
END
EXEC @hr = sp_OAGetProperty @ID, 'ResponseText', @Res OUT
IF @hr <> 0
BEGIN
print 'error reading response'
EXEC sp_OAGetErrorInfo @ID
RETURN
END
print @Res
declare @iDoc as int
EXEC sp_xml_preparedocument @iDoc OUTPUT, @Res
create table #TempDrs(
[id] varchar(10),
state varchar(20),
drstat varchar(20)
)
insert into #TempDrs ([id],state,drstat)
select [id],state,drstat from
OPENXML (@iDoc, '/drs/dr',2)
WITH ([id] varchar(10),
state varchar(20),drstat varchar(20))
UPDATE message
SET status = upper(drstat), drtry=drtry +1
FROM #TempDrs
INNER JOIN message on
#TempDrs.[id] = message.messageref
Drop Table #tempdrs
end
--select * from message
--update message set status=
--FROM OPENXML (@iDoc, '/drs/dr',2)
-- WITH ([id] varchar(10),
-- state varchar(20),drstat varchar(20))
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE spGetReplies
AS
declare @LastReplyId as int
declare @Url as nvarchar(4000),@Res as varchar(8000)
declare @hr as int,@ID as int, @i as int
declare @Start as datetime
Declare @End as datetime
select @Start=getdate()-(0.06), @End=getdate()+(0.06)
select @Url=''
select @LastReplyId=max(id) from reply
select @LastreplyId=isnull(@LastreplyId,0)
select @Url = 'http://www.logicsms.net/queryreplies.aspx?Type=batch&'
select @Url = @Url + 'username=yourusername&pass=yourpassword&lastid=' + cast(@LastReplyId as varchar(20))
print @Url
EXEC @hr=sp_OACreate 'MSXML2.XMLHTTP', @ID OUT
IF @hr <> 0
BEGIN
print 'Error creating winhttp object'
EXEC sp_OAGetErrorInfo @ID
RETURN
END
EXEC @hr = sp_OAMethod @ID, 'Open',NULL,'POST',@Url,0
IF @hr <> 0
BEGIN
print 'Error opening Url'
EXEC sp_OAGetErrorInfo @ID
RETURN
END
EXEC @hr = sp_OAMethod @ID, 'Send'
IF @hr <> 0
BEGIN
print 'Error Sending Data'
EXEC sp_OAGetErrorInfo @ID
RETURN
END
EXEC @hr = sp_OAGetProperty @ID, 'ResponseText', @Res OUT
IF @hr <> 0
BEGIN
print 'error reading response'
EXEC sp_OAGetErrorInfo @ID
RETURN
END
print @Res
declare @iDoc as int
EXEC sp_xml_preparedocument @iDoc OUTPUT, @Res
insert into Reply ([id],fromCell,toCell, message, systemdate, messageref)
select [id],fromCell,toCell, message, [date], [ref] from
OPENXML (@iDoc, '/replies/reply',2)
WITH ([id] varchar(10),
fromcell varchar(20),
tocell varchar(20),
message varchar(300),
[date] varchar(50),
ref varchar(50)) x
where not exists (select * from Reply r
where r.[id] = x.[id])
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Use msdb
EXEC sp_add_job @job_name = 'LogicSMS-DRS',@owner_login_name ='sa'
EXEC sp_add_jobstep @job_name = 'LogicSMS-DRS',
@step_name = 'Get Delivery Confirmations',
@subsystem = 'TSQL',
@command = 'exec spGetDrs',
@retry_attempts = 5,
@retry_interval = 5,
@database_name = 'Example',
@database_user_name= 'sa'
EXEC sp_add_jobschedule @job_name = 'LogicSMS-DRS',
@name = 'Scheduled Drs',
@freq_type = 4, -- daily
@freq_interval = 1,
@freq_subday_type = 0x4,
@freq_subday_interval=1
EXEC sp_add_jobserver @job_name = 'LogicSMS-DRS'
EXEC sp_add_job @job_name = 'LogicSMS-Replies',@owner_login_name ='sa'
EXEC sp_add_jobstep @job_name = 'LogicSMS-Replies',
@step_name = 'Get Replies',
@subsystem = 'TSQL',
@command = 'exec spGetReplies',
@retry_attempts = 5,
@retry_interval = 5,
@database_name = 'Example',
@database_user_name= 'sa'
EXEC sp_add_jobschedule @job_name = 'LogicSMS-Replies',
@name = 'Scheduled Replies',
@freq_type = 4, -- daily
@freq_interval = 7,
@freq_subday_type = 0x4,
@freq_subday_interval=1
EXEC sp_add_jobserver @job_name = 'LogicSMS-Replies'
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
|