Wiki Sumber Informasi iSIKHNAS

Overview

An SMS handler is a function that controls the process of receiving and parsing data from an incoming SMS, checking the data, inserting it into a table and providing any response messages required. The creation of an SMS handler function is automated and can be achieved by providing metadata through the web interface. The process involves:

• Setting up the general message information (format, premissions, purpose, data table, error message etc)
• Setting up the details for each of the fields
• Populating reference tables
• Creating the data table
• Creating message strings for outgoing messages

These steps are described in detail below.

Message attributes

The interface to edit message attributes is available through the Admin | Create SMS Handler | Edit SMS Message menu options. The fields are:

Start code

Every message has a start code. This must contain only letters (no numbers, symbols, spaces or punctuation), and should be written in capital letters. It must be at least one letter, but can be longer. It is best to keep it as short as possible, while still allowing it to be easily understood and unique. Two or three letters is normal.

Name

The name for the message is a short name describing the purpose of the message or the type of data being collected. It should be written in Indonesian and English.

Permission

This controls who is allowed to send this type of message. The drop down list shows all defined group permissions, normally named in the form 'can_dosomething'. If there is an existing permission that is already suitable for this type of report, that can be used, but normally it will be necessary to define a new permission. Once defined, and associated with the SMS message, different user groups can be give this permission or not, depending on their responsibilities.

Defining a new permission

In the menu, go to Admin | Permissions | Edit Permissions.

Name

Enter a new name for the permission in the form 'can_xxx'. For a permission for a particular SMS message, the normal name for the permission is 'can_send_message_type'. For example, for an OB message, the corresponding permission would be 'can_send_ob'.

Enable by default

This sets all groups to have this permission by default. Normally this should be set to 'no' so that groups have to be explicitly give this permission, unless it is sure that almost all users should be able to use this permission.

User permission

Set this to 'no' as we are creating a group permission.

Save the permission and return to Edit SMS Message, selecting the permission that you have created.

Purpose and Help Text

These are currently not used but will be used in documentation and the interface in future.

Table name

This is the name of an existing database table into which the received data will be inserted. Type the name of the table.

Only the first table name is required. The second and third are used in special cases where data is distributed amongst several tables

Error message

This is a key to a string which will be used as the error message if the general format of the message is incorrect. Normally it is in the form message_type_error. For example, for an OB message, the name would be OB_error.

Reply SQL

This is an SQL 'select' query that controls what message is returned to the sender. It must always be defined.

The SQL should return a single varchar value which is the text of the message that will be returned to the sender. At its very simplest, it could be something like:

select 'Thank you. Your message has been received'

However, all reply messages should confirm the contents of the submitted message, converted from the coded version into clear text. The reply SQL is therefore normally more complex, and queries the newly inserted data (filtering by the message id), joins it to references tables, and composes the result. It also normally uses language-specific strings from the translation table to present the message in the correct form.

Pre-defined variables that can be included in the SQL (and almost always are) are:

• sms_userid: the user ID of the person sending the message. This is used to get the right language.
• sms_msgid: the message ID for the current message. This is used to get the submitted data.

Helpful functions that may be included in replay SQL include:

• get_string(key varchar, userid integer): returns a defined string (indexed by the key) in the user's preferred language
• get_user_lang(userid integer): return the language code for the user. This is useful if directly accessing data from a translated array field.
• add_checkdigit(code integer): when returning a numeric code (case ID, program ID etc), a check digit is used to ensure that there are no typographical errors. This function adds a check digit to the raw code. All ID codes should have a check digit added, as if they are used in a message without the check digit, it will be interpreted as an error by the system.
• format(format_string varchar, input_string varchar,...): This is a standard PostgreSQL function to format a string with a list of replaceable variables. The string should contain one or more %s place holders, which are replaced with the value of the variables specified.
• string_agg(string varchar, separator varchar): another standard SQL function that aggregates string from multiple rows (when using a 'group by' clause) into a single concatenated value, with each row's string be separated by the separator. This is useful when the message might insert data into multiple rows, and the reply needs to summarise the data from these rows.

An example of a reply SQL for the OB message:

select format(get_string('OB_reply',sms_userid), dinfo, s.species[1], l.name)
from ( 
select t.caseid, string_agg(format('%s (%s %s)', d.name, dose, units), ', ') as dinfo 
from treatments t
join drugs d on d.id = drug
where t.msgid = sms_msgid group by t.caseid) as dd
left join cadre_reports c on c.id = dd.caseid
left join locations l on l.id = c.locationid
left join species s on s.id = c. speciesid

Alert SQL

The Alert SQL is used to send immediate SMS messages to users other than the original sender, alerting them of the contents of the original SQL or of other information. If no alert message is required, this field should be left blank.

This is a 'select' statement returning one or more records with two fields: the phone number (varchar, from the users.phone field), and the message content (varchar).

The same variables and functions described above are available for use in this message. An example of the Alert SQL for the Q message is:

select u2.phone, 
  format(get_string('Q_alert',2), u.firstname||coalesce(' '||u.surname,), 
  local_phone(u.phone), to_char(report_date,'HH:MM:SS'), question)
from questions q
join users u on u.id = q.userid
join users u2 on (not u2.del) and (u2.phone is not null) and (u2.groupid < 2)
join locations l on l.id = u2.location
join locations l2 on l2.id = u.location
where q.msgid = sms_msgid

Protected

Mark 'yes' to protect this message from being overwritten by the message creation process. Any message function that has had custom modifications made to its code should be marked as protected to avoid being overwritten by the automatic message generator function.

Field attributes

Once the message attributes are defined, the fields for the message need to be defined. This is done from the menu: Admin | Create SMS Handler | Edit SMS Fields

Message

Select the existing message defined in the previous step

Natorder

Natural order. Type an integer to define the order of fields in the SMS. Each field must have a different sequential value.

Name

Enter a name in English and Indonesian. This appears in documentation and is used internally. It may contains spaces. It should briefly describe what the content of the field is.

Data type

The following data types are defined:

Numeric code

This is a number containing a check digit. Its main use is case ID, but it is used in a number of other situations where users need to send and receive numeric codes.

Lookup code

This is a alpha (letters only) code (one or more letters), which is used to look up a value from a reference table. This is the most common way to refer to reference table values.

Integer

This is a simple integer, used for counts in the data submitted (number of animals slaughtered, vaccinated, sick etc.)

Location

This is a location code, which can be a full version (8 digits in the new system, 10 in the old), or short (just the last digits below the users area of responsibility).

Boolean

A single letter text code that can take two values, defaulting to Y (Ya, yes), and T (tidak, no). SQL can be used to define other alternatives.

Text

Free text with any characters. This normally has to be the last field in a message as it is difficult to parse.

Float

A number that may contain a decimal point, for example, drug doses or coordinates.

Integer code

An integer used to lookup a value from a reference table. This is not commonly used (alpha codes are used instead).

Text array

A field containing one or more lookup codes (alpha codes referencing values in a table). These values are parsed and inserted into a single array field in the data table. In this way, multiple values can be handled as a single field type. This is used for signs and differential diagnoses, for example. Care is required to ensure that parsing is unambiguous (last field, or surrounded by numeric fields).

Date

A field which allows the user to submit a date. Dates are submitted in one of the following formats:

• dd/mm/yyyy
• dd/mm/yy (assumes 21st century)
• mm/yyyy (assumes 15th of the month)
• mm/yy (assumes 15th of the month, 21st century)
• dd.mm.yyyy and other variations above
• dd-mm-yyyy and other variations above

Dates are stored in date fields in the database.

Optional

This indicates if the field is optional or not.

Group sequence

SMS messages can contain repeating groups of fields. For example a POP population message can contain multiple pairs of ([species] [number]...). When a field is not part of a repeating group, it should have a group sequence of 0. If it is part of a group, then each element of the group should be numbered sequentially. Only one repeating group is permitted in a message.

Lookup SQL

This is a select statement with a different purpose depending on the field type. When not required, it can be left blank. When used, it should contain a single %s value which is replaced with the value of the field. Return types vary with the field type.

Lookup code

The SQL returns the id from the reference table of the value submitted. The SQL is required in this case. For example:

select id from drugs where upper(code) = upper(trim(%s))

Integer or Date

The SQL is optional. If provided it is used for range checking. It should return a single boolean value. For example:

select %s between 0 and 1000

Text array

The SQL is required. It returns an array of ID values for the codes in the input array. The requirements are rather special:

• two %s parameters
  • First one: select %s from
  • Second one: (select unnest(regexp_matches(%s,'([a-z]+)', 'igx')) as code) as dat
• join to main table: left outer join diseases s on upper(dat.code) = upper(s.code)
  • table must be aliased 's'
• other join clauses and filters

For example:

select %s from
 (select unnest(regexp_matches(%s,'([a-z]+)', 'igx')) as code) as dat
left outer join diseases s on upper(dat.code) = upper(s.code)
AND NOT del
AND (valid_from IS NULL OR valid_from <= CURRENT_DATE)
AND (valid_to IS NULL OR valid_to >= CURRENT_DATE)

Boolean

The SQL is optional. If present, it returns a boolean value for the submitted code. For example:

select case upper(trim(%s)) 
 when 'K' then true
 when 'Y' then true
 when 'T' then false 
 when 'N' then false
else null end

Field name

The field in the database table into which the data received will be inserted.

Error message

A key reference to an string in the translation table that will be used as the error message for this field. The key should contain one replaceable parameter (%s) which is replaced with the (invalid) value submitted.

Reference tables

Reference tables are in the reference schema. Structure varies but most have at least the following fields:

• id: unique record id
• code: an alpha code
• hier_code: a dot separated hierarchical code in the form 1.1.1. This is used to arrange data at different levels of detail, allowing more flexible analysis
• name: varchar[] - a text array field with the name in Indonesian at index 1, and English at index 2
• valid_from and valid_to: dates indicating the period of validity of the item. Valid_to may be null indicating ongoing validity.
• modified_by: user id of the last user to modify the value
• modified_on: timestamp of the last modification
• del: boolean flag for deleted

In addition, there may be classifications referring to other tables (eg species) or flags (eg zoonosis, OIE etc for diseases)

Data tables

Data tables are stored in the 'data' schema. Their structure depends on the data required, but they must have have the following fields which are automatically updated:

• id: unique record id
• userid: integer referencing the user ID of the submitting user (from the 'users' table);
• report_date: timestamp of data submission
• msgid bigint: the unique id of the incoming SMS message
• created_on and modified_on: dates
• created_by and modified_by: user IDs
• del: deleted flag

Message strings

Translated message strings are stored against a key in the translation table, and can be edited using the menu Admin | Message codes and translations | SMS messsage text.

The key or string code is used to access the message. By convention, this code starts with the message start code, followed by an underscore and then an abbreviation of the purpose. For example a message with an error due to an invalid drug code when sending a treatment (OB) message might be OB_invdrug.

The strings are stored in Indonesian and English (and this can be expanded to other languages if required).

Most messages have data inserted into them, so are used with the SQL format() function. For this to work, they need to have place holders for the data to insert, in the form %s. For example:

Laporan tindak lanjut dari %s (ID kasus %s) %s. %s

would have the following data substituted into it: village name, case ID, the numbers of animals and whether the outbreak is resolved or ongoing.

Building the message function

Once the metadata, tables and strings for an SMS handler function have been set up, the function needs to be generated before it can be used. Use the menu Admin | Create SMS handler | Create handler function, and select the function to generate. Once generated, the function will be saved in the SMS schema, and be immediately available for use.

Testing the message function

To test a new function, use the Instant Messaging system. This allows a message to be composed and submitted to the system as if it were being sent by SMS. The message is inserted into the inbox, normal processing follows, and any response messages are inserted into the outbox and returned to the sender via IM. Note that any data submitted is inserted into the database so be sure to delete any test data afterwards if using the live server