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.
Possible value | Description |
---|---|
Alpha |
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. |
Numeric |
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.
Possible value | Description |
---|---|
0 | ErrorMessage: NoError |
1 |
ErrorMessage: ActionValidationError Not a valid Action, no request to SMS supplier |
5 |
ErrorMessage: ErrorMessageFromSupplier SMS supplier returned with error |
6 |
ErrorMessage: ExceptionDuringCommunicationWithSupplier Error during Communication with SMS supplier or when decoding message. |
7 |
ErrorMessage: MessageReferenceNotFound Reference sent to SMS supplier not found at supplier or in Softadmins Admin tables. |
9 |
ErrorMessage: InternalErrorCode Not handled error |
Supplies the result of a "GetOutgoingMessageStatus" action.
Possible value | Description |
---|---|
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. |
Possible value | Description |
---|---|
0 | No error. |
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 SQL parameters before any other SQL is run in the component. This call is only made if 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;