A component for sending and receiving SMS messages.
A valid Customer Account must be registered with the SMS service provider.
Statement to extract actions to perform.
A maximum of 11 alphanumeric characters. Only the characters A-Z, a-z, 0-9 and åäöÅÄÖ are supported.
The recipient can not respond to a message sent using this originator type.
A numeric value representing a phone number on international format without leading "00".
Example: The swedish phone number 0700-123123 should be written as 46700123123.
Supplies the result of a "SendMessage" action.
Not a valid Action, no request to SMS supplier
SMS supplier returned with error
Error during Communication with SMS supplier or when decoding message.
Reference sent to SMS supplier not found at supplier or in Softadmins Admin tables.
Not handled error
Supplies the result of a "GetOutgoingMessageStatus" action.
|Buffered||The message has been delivered to the recipient's phone operator.|
|Delivered||The message has been delivered to the destination terminal. Most delivered messages never reach this status (because of operator limitations) and remain as "Buffered".|
|Failed||Message delivery to the phone operator failed.|
|null||Unable to fetch status. See ErrorCode and ErrorMessage for more details.|
|Queued||The message has been received by the SMS supplier and is awaiting transfer to the recipient's phone operator. You should ask again later to get its final status.|
|Unknown||The message was sent but it is unknown if it was delivered.|
|1||The action was rejected by the component.|
|5||Error from supplier.|
|6||Communication error. Check the system's error log.|
|7||Invalid message reference.|
|9||Internal error. Check the system's error log.|
Allows you to validate the parameters supplied by the user before any other SQL is run in the component. This call is only made if the component has visible parameters, the SQL is a stored procedure, and 'Validate parameters' is checked.
Use this call to restrict which entries a user is allowed to view and edit, and to log which entries a user views.
Access to a menu item is normally controlled through functions and roles alone but some entities need more fine grained control. For example, a user may have access to the View Member menu item for normal members but not for members with a protected identity.
The menu items a user visits are always logged (in ADMINLogMenuItem) but for sensitive data you may need to log exactly what entries are viewed. Do the logging in this call as the common ways of viewing data (grid and InfoSQL) are not allowed to modify the database.
If you bind a scalar function instead of a stored procedure to this call then its name must end with '_GrantAccess'.
CREATE SCHEMA SmsOutgoing AUTHORIZATION dbo; GO CREATE TABLE SmsOutgoing.SmsQueue ( SmsQueueId BIGINT IDENTITY CONSTRAINT PK_SmsQueue PRIMARY KEY, Destination varchar(30) not null, Content nvarchar(1600) not null, QueueDatetimeUtc datetime2(2) not null, SentDatetimeUtc datetime2(2) null, SentMessageReference varbinary(1000) null, DeliveryStatus varchar(30) null, IsFinalStatus bit not null CONSTRAINT DF_SmsQueue_IsFinalStatus DEFAULT(0), ErrorCode int null, ErrorMessage varchar(max) null ); GO CREATE OR ALTER PROCEDURE SmsOutgoing.SmsQueue_Send @Action varchar(50) = NULL AS BEGIN DECLARE @Originator varchar(11) = 'Multisoft'; -- Originator should be hardcoded or fetched from a setting. IF @Action IS NULL BEGIN SELECT TOP (5000) 'SendMessage' AS Action, Q.SmsQueueId AS OutgoingMessageId, 'Alpha' AS OriginatorType, @Originator AS Originator, Q.Destination AS Destination, Q.Content AS Content FROM SmsOutgoing.SmsQueue Q WHERE Q.SentDatetimeUtc IS NULL ORDER BY Q.SmsQueueId; RETURN; END; IF @Action = 'SendMessage' BEGIN UPDATE SmsOutgoing.SmsQueue SET SentDatetimeUtc = SYSUTCDATETIME(), SentMessageReference = R.SentMessageReference, ErrorCode = R.ErrorCode, ErrorMessage = R.ErrorMessage, IsFinalStatus = IIF(R.ErrorCode <> 0, 1, 0) FROM SmsOutgoing.SmsQueue Q JOIN #OutgoingMessageResult R ON R.OutgoingMessageId = Q.SmsQueueId; RETURN; END; RAISERROR('Unknown action %s', 16, 1, @Action); END;
Uses the queue from the previous example (Sending messages from a queue) to fetch the status of sent messages. While sending should be done continuously you should only fetch delivery reports every few minutes.
CREATE OR ALTER PROCEDURE SmsOutgoing.SmsQueue_GetDeliveryStatus @Action varchar(50) = NULL AS BEGIN IF @Action IS NULL BEGIN SELECT 'GetOutgoingMessageStatus' AS Action, Q.SmsQueueId AS OutgoingMessageId, Q.SentMessageReference AS SentMessageReference FROM SmsOutgoing.SmsQueue Q WHERE Q.SentDatetimeUtc IS NOT NULL AND Q.IsFinalStatus = 0 AND Q.SentMessageReference IS NOT NULL; RETURN; END; IF @Action = 'GetOutgoingMessageStatus' BEGIN DECLARE @StopPollingAfter datetime2(2) = DATEADD(day, 3, SYSUTCDATETIME()); UPDATE SmsOutgoing.SmsQueue SET DeliveryStatus = R.Status, ErrorCode = R.ErrorCode, ErrorMessage = R.ErrorMessage, IsFinalStatus = CASE WHEN R.Status IN ('Buffered', 'Queued') AND Q.SentDatetimeUtc > @StopPollingAfter THEN 0 ELSE 1 END FROM SmsOutgoing.SmsQueue Q JOIN #OutgoingMessageStatusResult R ON R.OutgoingMessageId = Q.SmsQueueId; RETURN; END; RAISERROR('Unknown action %s', 16, 1, @Action); END;