SMS Setup

What You Will Need

To send SMS notifications from a mailQuery scheduled job, you will need to have an account with an SMS Provider who offers an Email-to-SMS feature. Using an Email-to-SMS feature you can send them specially formatted emails which they convert and send on as SMSs. There are a number of providers in the market offering this service, and it could be that your organisation already has an account.

The SMS Provider

Once you have opened an account with the SMS Provider, you need to turn on the Email-to-SMS feature and choose your settings.

Here is an example taken from the settings screen of a provider called SMSGlobal:

Notice the address sendsms@catch-e.com.au in the Allowed sender addresses box. Only emails sent from this address will be accepted by SMSGlobal and converted to SMSs.

To test the service you can send an email from your mail client. You first need to add your email address to the Allowed sender addresses box, then send an email similar to this:

Test Email Format

The fields are populated as follows:

1. The From: address must be included in the Allowed sender addresses box or equivalent for your SMS Provider

2. The To: address must consist of the target mobile phone number in the local part (before the @), and the domain part as specified by your SMS Provider

3. The Subject: is not required and can be left blank

4. The Body: contains the text that will be sent in the SMS notification

A reply to this SMS will be emailed to the From: address in this case, but an alternate address can be configured. Features like this will vary between providers, you will need to refer to their help documentation for further details.

Setting Up a MailQuery Scheduled Job

Once you have completed the above section you will be ready to create a mailQuery scheduled job that can send SMSs. The remainder of this section takes you through a worked example to send driver notifications for vehicle registration fees coming up for renewal.

1) Create Contract Event Type

Start by creating a contract event code called 'REGNSMS'.

The scheduled job will be configured to create a contract event record with this event code for each SMS email sent. This record is then used to stop any further SMS notifications being sent for that vehicle for the current year.

We have used 'REGNSMS' in this example, but you can use any event code you like. You might even have an existing event code that you prefer to use.

2) Create Query Used by the Scheduled Job

Next you need to add the following query to gb_queries (also populate the required gb_template record):

SELECT c.contract_id AS mail_group_by, t.mail_sender_address AS sender_address, CONCAT('61', SUBSTRING(REPLACE(d.mobile, ' ', ''), 2), '@email.smsglobal.com') AS recipient_1_to_address, '' AS subject, t.mail_message AS 'message', -- Placeholders SUBSTRING_INDEX(d.given_name, ' ', 1) AS driver_name, c.reg_no AS reg_no, DATE_FORMAT(c.reg_renewal, '%d/%m/%Y') AS reg_renewal FROM fm_contracts AS c INNER JOIN fm_drivers AS d ON d.driver_id = c.driver_id INNER JOIN gb_templates AS t ON t.sub_name = 'REGNSMS' WHERE c.reg_managed_flag = 'yes' AND DATEDIFF(c.reg_renewal, CURDATE()) BETWEEN 1 AND 30 AND d.mobile != '' AND d.given_name != '' AND NOT EXISTS ( SELECT ce.contract_event_id, ce.contract_id, ce.event_date FROM fm_contract_events AS ce INNER JOIN gl_posting_classes AS pc ON pc.posting_class_id = ce.posting_class_id AND pc.posting_class_code = 'REGNSMS' WHERE ce.contract_id = c.contract_id AND DATEDIFF(CURDATE(), ce.event_date) < 60 AND ce.status_flag = 'active' ) AND (c.suspend_date = '0000-00-00' OR c.suspend_date > DATE_ADD(CURDATE(), INTERVAL 6 MONTH)) ORDER BY mail_group_by 

The text for message can be stored in gb_templates and a join added to that table. This technique is commonly used to store all Mail/SMS messages in the one place. Refer to the notes in mailQuery.

Running this query from a database tool such as MySQL Workbench will give you output similar to this:

Field Purpose Reference

At runtime the mailQuery scheduled job will replace the placeholders embedded in message with values from the placeholder fields. So for the first data row above the actual SMS message sent to mobile 0407 111 178 would be:

Hi Jane, this is a reminder that the registration fee for XX00AA will be due on 17/05/2015 

Note: Word your text carefully as most SMS text is restricted to 160 characters. Factor in the length of any placeholders you use. Refer to http://support.smsglobal.com/customer/en/portal/articles/630697-why-is-my-message-split-into-multiple-parts-1-2-

Query Logic

Also note the following logic from the WHERE clause of the query:

• An SMS email won't be created if a 'REGNSMS' contract event record has been created within the last 60 days

• The registration renewal date must be within the next 30 days

• The driver must have a non-blank name and mobile phone number (no validation of the mobile number is performed)

• The contract cannot be suspended and must have the Reg Managed checkbox checked on

You will need to adjust this query to suit your own requirements.

3) Create the Scheduled Job

Create a mailQuery scheduler job selecting the query created in step 2 above.

Set the following parameters to create the contract event records:

• Create Event: checked

• Event Table: Contract

• Event Type: REGNSMS

That is it, the job can now be activated and scheduled as required.

Additional Notes

You might also want to create a separate exception report to identify issues such as:

• Drivers with managed registration renewals that have an empty or invalid mobile phone number

• Contracts with a registration renewal date that has expired

When viewed from the Mail Queue screen the SMSs will look something like this:

(Email display format similar to regular emails with To, From, Subject, and Body fields)

Contact your Catch-e Account Manager if you require further assistance.