Wiki Sumber Informasi iSIKHNAS

Ikhtisar

Sebuah SMS handler adalah sebuah fungsi yang mengendalikan proses menerima dan menguraikan data dari sebuah SMS masuk, memeriksa data, memasukkannya ke dalam tabel dan memberikan respon apapun yang diperlukan oleh pesan tersebut. Pembuatan fungsi SMS handler terotomatisasi dan dapat dicapai dengan menyediakan metadata melalui interface laman. Proses ini meliputi:

• Mengatur informasi pesan umum (format, perizinan, tujuan, tabel data, pesan eror dll)
• Mengatur detil untuk masing-masing bidang
• Mengisi tabel referensi
• Membuat tabel data
• Membuat serangkaian pesan untuk pesan keluar

Langkah-langkah ini dijelaskan secara terperinci di bawah ini.

Atribut-atribut Pesan

Interface untuk menyunting atribut-atribut pesan tersedia melalui Admin | Membuat SMS Handler | Sunting pilihan menu Pesan SMS. Bidang-bidangnya adalah:

Kode mulai

Setiap pesan memiliki satu kode mulai. Ini hanya berisi huruf saja (tidak ada angka, simbol, spasi atau tanda baca), dan harus ditulis dengan huruf besar. Setidaknya ada satu huruf, namun bisa lebih panjang. Lebih singkat lebih baik, walaupun tetap memungkinkan untuk mudah dimengerti dan unik. Normalnya adalah dua atau tiga huruf.

Nama

Nama untuk pesan tersebut adalah sebuah nama pendek yang mendeskripsikan tujuan dari pesan itu atau tipe data yang sedang dikumpulkan. Hendaknya ditulis dalam bahasa Indonesia dan bahasa Inggris.

Perizinan

Ini mengontrol siapa yang diizinkan untuk mengirimkan tipe pesan ini. Daftar drop down menunjukan seluruh perizinan grup yang terdefinisikan, biasanya dinamai dalam format 'dapat_melakukansesuatu'. Jika ada perizinan yang sesuai dengan tipe laporan ini, yang bisa digunakan, tetapi biasanya akan diminta untuk mendefinisikan perizinan baru. Setelah terdefinisi, dan diasosiasikan dengan pesan SMS, grup pengguna yang berbeda dapat memberikan izin ini atau tidak, tergantung pada tanggung jawab mereka.

Mendefinisikan perizinan baru

Pada menu, kunjungi Admin | Perizinan | Sunting Perizinan.

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.

Simpan izin dan kemudian kembali ke Edit Pesan SMS, pilih izin yang telah Anda buat.

Tujuan dan Teks Bantuan

Tujuan dan teks bantuan saat ini tidak digunakan tetapi akan digunakan dalam dokumentasi dan antarmuka di masa depan.

Nama Tabel

Nama tabel adalah nama dari tabel pangkalan data yang sudah ada di mana data yang diterima akan dimasukkan. Ketik nama tabel.

Pesan kesalahan

Pesan kesalahan adalah kunci untuk string yang akan digunakan sebagai pesan kesalahan jika format umum pesan tidak benar. Biasanya itu adalah dalam bentuk message_type_ kesalahan. Misalnya, untuk pesan OB, namanya adalah OB_error.

Balas SQL

Balas SQL adalah SQL 'pilih' permintaan yang mengontrol apa pesan yang dikembalikan ke pengirim. Ini harus selalu ditentukan.

SQL akan mengirimkan pesan balasan ke pengirim. Pilih sesuatu yang sangat sederhana, seperti: 'Terima kasih. Pesan Anda telah diterima'

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

Order

Type an integer to define the order of fields in the SMS

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).

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

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 menu Admin | Test SMS Message. 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. However no SMS is actually sent (the outgoing messages are displayed on the web interface). Note that any data submitted is inserted into the database so be sure to delete any test data afterwards.

The message is handled as if it were sent from the specified phone number, defaulting to the phone number of the current user. This makes it possible to submit messages on behalf of other users, or to test messages coming from users in different locations and languages.