Wiki Sumber Informasi iSIKHNAS - User contributions [en]

Mapping

2018-06-22T11:27:01Z

Jillhinchliffe: /* Viewing maps */

=Mapping=
The mapping system allows administrators to define new map-based reports rapidly through the interface, and for them to be viewed on-line.

==Defining a new map==
Maps are defined in the same way as other reports, using the Administration | Reports menu. There are two key differences compared to other reports: requirements for the SQL data definition, and style information (Mapfile code)

===SQL===
The SQL is similar to all other reports, and should be a single SELECT query. However, there are several particular requirements

====Required fields====
Every query must return two required fields:
* '''gid''' - this is a 'geographic ID' and is a unique integer for each spatial unit returned. Often, this can just be the id from the locations table, but if there can be multiple reports per location, you need to choose another unique ID value. It doesn't matter which. If nothing else is available, you can also use the row number window function:
select row_number() over () as gid,
* '''the_geom''': this is the spatial data and must be included in every report. It should always come from the projected version of the locations table spatial data. That is:
** For polygon data (administrative units) this should be geom_proj (e.g. select geom_proj as the_geom, )
** For point data (village locations etc) this should be centroid_proj (e.g. select centroid_proj as the_geom, )
Don't use the standard geom and centroid columns, or the data will not be in the right projection and won't appear on the map.

===Field names===
Any fields generated in the query will be available to the user with a map query (where they click on a feature, and further information pops up). So that this feature works nicely, you should include all the data that a user might want to see, and name each field with a user-friendly name. For example
SELECT
sum(total) as "Julah hewan"

===Example===
An example of a simple query to display infrastructure:
SELECT
i.id as gid,
l.centroid_proj as the_geom,
i.shortname as "Nama infrastruktur",
from infrastructure i
join locations l on i.locationid = l.id
where not i.del

===Mapfile code===
The R code box of the Report definition interface is used to include mapfile code, which specifies the appearance of the output.

Most of the mapfile is automatically generated using fixed standards and the SQL previously defined, but you have to specify here how the map should appear.

The contents of the R code box is inserted into the mapfile in a LAYER block. Full documentation of what is permitted can be found in the [http://mapserver.org/mapfile/layer.html Mapserver documentation]

Two sections are required (TYPE and CLASS) and there are some optional sections
=====TYPE (required)=====
This can be POINT, LINE or POLYGON, depending on the map to be produced. For example, for a point map:
TYPE POINT
Other types can be used including CHART, CIRCLE, RASTER but these are uncommon advanced features.

=====CLASS (required) =====
This defines the appearance of the data, as well as the legend. A typical class block for a point map
CLASS
NAME "Kasus"
STYLE
SYMBOL "reddot"
END # STYLE
END # CLASS

This will produce a layer with a single symbol (reddot) for all features, and a legend with 'Kasus'.

For a polygon layer, you can define the colour of the outline and the fill:
CLASS
NAME "Pelatihan"
STYLE
COLOR 255 0 0
OUTLINECOLOR 255 0 0
SIZE 6
END # STYLE
END

The style options are very powerful. See the [http://mapserver.org/mapfile/class.html#class Mapserver CLASS documentation] for more information.

Standard SYMBOLs are pre-defined in a symbol file. For more symbols, this file will have to be manually edited.

It is also possible to define multiple levels of shading, by referring to data from the query. For example:

CLASS
NAME "Belum gunakan"
EXPRESSION ([laporan] = 0)
STYLE
COLOR 237 248 251
END # STYLE
END # CLASS

CLASS
NAME "1 - 5 laporan"
EXPRESSION ([laporan] > 0 AND [laporan] < 6)
STYLE
COLOR 178 226 226
END # STYLE
END # CLASS

CLASS
NAME "6 - 50 laporan"
EXPRESSION ([laporan] > 5 AND [laporan] < 50)
STYLE
COLOR 102 194 164
END # STYLE
END # CLASS

CLASS
NAME "> 50 laporan"
EXPRESSION ([laporan] > 50)
STYLE
COLOR 35 139 69
END # STYLE
END # CLASS

In this case, the query must have a field called "laporan" which has an integer (the number of reports submitted).

The COLOR and OUTLINECOLOR lines require a RGB (red green blue) colour code. The [http://colorbrewer2.org/index.html ColorBrewer] web site is an excellent tool for picking good map colours and finding their RGB code.

=====CLASSITEM (optional) =====
This specifies that one field in the query will be used to determine the style of different features. For example:

CLASSITEM "Hasil"
CLASS
NAME "Positif"
EXPRESSION "Pos"
STYLE
SYMBOL "reddot"
END
END

CLASS
NAME "Negatif"
EXPRESSION "Neg"
STYLE
SYMBOL "bluedot"
END
END

This means that there must be a field in the query called "Hasil" and it will return values of either "Positif" or "Negatif"

==Viewing maps==
Maps must be viewed using the 'root' interface.

* If there are no special parameters (the normal situation)
** Set up a menu entry as usual, and set the URL to: root/index/''reportid''. For example
/root/index/216

* If there are parameters to produce a pre-configured report (different from the default) you need to remove the first '/' and add the parameters. For example:

root/index/216&param[diagnosis]=13

Mapping

2018-06-22T11:25:22Z

Jillhinchliffe: /* Mapfile code */

=Mapping=
The mapping system allows administrators to define new map-based reports rapidly through the interface, and for them to be viewed on-line.

==Defining a new map==
Maps are defined in the same way as other reports, using the Administration | Reports menu. There are two key differences compared to other reports: requirements for the SQL data definition, and style information (Mapfile code)

===SQL===
The SQL is similar to all other reports, and should be a single SELECT query. However, there are several particular requirements

====Required fields====
Every query must return two required fields:
* '''gid''' - this is a 'geographic ID' and is a unique integer for each spatial unit returned. Often, this can just be the id from the locations table, but if there can be multiple reports per location, you need to choose another unique ID value. It doesn't matter which. If nothing else is available, you can also use the row number window function:
select row_number() over () as gid,
* '''the_geom''': this is the spatial data and must be included in every report. It should always come from the projected version of the locations table spatial data. That is:
** For polygon data (administrative units) this should be geom_proj (e.g. select geom_proj as the_geom, )
** For point data (village locations etc) this should be centroid_proj (e.g. select centroid_proj as the_geom, )
Don't use the standard geom and centroid columns, or the data will not be in the right projection and won't appear on the map.

===Field names===
Any fields generated in the query will be available to the user with a map query (where they click on a feature, and further information pops up). So that this feature works nicely, you should include all the data that a user might want to see, and name each field with a user-friendly name. For example
SELECT
sum(total) as "Julah hewan"

===Example===
An example of a simple query to display infrastructure:
SELECT
i.id as gid,
l.centroid_proj as the_geom,
i.shortname as "Nama infrastruktur",
from infrastructure i
join locations l on i.locationid = l.id
where not i.del

===Mapfile code===
The R code box of the Report definition interface is used to include mapfile code, which specifies the appearance of the output.

Most of the mapfile is automatically generated using fixed standards and the SQL previously defined, but you have to specify here how the map should appear.

The contents of the R code box is inserted into the mapfile in a LAYER block. Full documentation of what is permitted can be found in the [http://mapserver.org/mapfile/layer.html Mapserver documentation]

Two sections are required (TYPE and CLASS) and there are some optional sections
=====TYPE (required)=====
This can be POINT, LINE or POLYGON, depending on the map to be produced. For example, for a point map:
TYPE POINT
Other types can be used including CHART, CIRCLE, RASTER but these are uncommon advanced features.

=====CLASS (required) =====
This defines the appearance of the data, as well as the legend. A typical class block for a point map
CLASS
NAME "Kasus"
STYLE
SYMBOL "reddot"
END # STYLE
END # CLASS

This will produce a layer with a single symbol (reddot) for all features, and a legend with 'Kasus'.

For a polygon layer, you can define the colour of the outline and the fill:
CLASS
NAME "Pelatihan"
STYLE
COLOR 255 0 0
OUTLINECOLOR 255 0 0
SIZE 6
END # STYLE
END

The style options are very powerful. See the [http://mapserver.org/mapfile/class.html#class Mapserver CLASS documentation] for more information.

Standard SYMBOLs are pre-defined in a symbol file. For more symbols, this file will have to be manually edited.

It is also possible to define multiple levels of shading, by referring to data from the query. For example:

CLASS
NAME "Belum gunakan"
EXPRESSION ([laporan] = 0)
STYLE
COLOR 237 248 251
END # STYLE
END # CLASS

CLASS
NAME "1 - 5 laporan"
EXPRESSION ([laporan] > 0 AND [laporan] < 6)
STYLE
COLOR 178 226 226
END # STYLE
END # CLASS

CLASS
NAME "6 - 50 laporan"
EXPRESSION ([laporan] > 5 AND [laporan] < 50)
STYLE
COLOR 102 194 164
END # STYLE
END # CLASS

CLASS
NAME "> 50 laporan"
EXPRESSION ([laporan] > 50)
STYLE
COLOR 35 139 69
END # STYLE
END # CLASS

In this case, the query must have a field called "laporan" which has an integer (the number of reports submitted).

The COLOR and OUTLINECOLOR lines require a RGB (red green blue) colour code. The [http://colorbrewer2.org/index.html ColorBrewer] web site is an excellent tool for picking good map colours and finding their RGB code.

=====CLASSITEM (optional) =====
This specifies that one field in the query will be used to determine the style of different features. For example:

CLASSITEM "Hasil"
CLASS
NAME "Positif"
EXPRESSION "Pos"
STYLE
SYMBOL "reddot"
END
END

CLASS
NAME "Negatif"
EXPRESSION "Neg"
STYLE
SYMBOL "bluedot"
END
END

This means that there must be a field in the query called "Hasil" and it will return values of either "Positif" or "Negatif"

==Viewing maps==
Maps must be viewed using the 'root' interface.

* If there are no special parameters (the normal situation)
** Set up a menu entry as usual, and set the URL to: root/index/''reportid''. For example
/root/index/216

* If there are parameters to produced a pre-configured report (different from the default) you need to use remove the first '/' and add the parameters. For example:

root/index/216&param[diagnosis]=13

Advanced permissions

2018-06-22T11:12:19Z

Jillhinchliffe: /* Page that already has a Task created for it */

=Manage advanced permissions=
Permissions on the web site are managed in two ways:
* Menu permissions
** This controls which menu items a user can see and is set in the Administration | Interface | Menu system. If a user does not have permission to see a menu, it won't appear so they cannot select that feature from the interface.
* Advanced permissions
** This controls if a user is actually allowed to access a particular page (whether or not it appears on the menu). If a user does not have page permissions, they will get an error message when they try to access the page.

These permissions are rather more complex. In this example, we will demonstrate giving permission for pages to manage data in program table. If a user gets an access error message, the permissions can be updated as follows:

=====New page with no permission=====
If this is a new interface, then there will be no Operations or Tasks created yet for the page. You have to create these, before you can assign them to a user. If this is an existing page, then the Task should already exist and you can skip this first part.

# Find out the root name of the page that we want to access. On the site, go to the page and look at the URL. For example, if the url is ''www.isikhnas.com/id/trainingCourse/admin'' then the root name of the page is ''TrainingCourse''. Note that the first letter should be made into a capital but the rest should be unchanged
# Go to the interface permissions page: Administration | Permission | Interface Permissions.
# Click on '''Manage Auth Items'''
# Click on '''Create''' under Auth Items
# Create a task to group several operations together:
## Name is ''RootName'''''Administrating''' where ''RootName'' is the root name you found out above
## Type is '''Task'''
## Leave the other fields blank
## Click '''Create'''
# Now create the different operations for that task (not all may be required, depending on what you want people to be able to do)
## Click Create again
## Name is ''RootName'''''Index''', type is Operation, click Create
## Name is ''RootName'''''Create''', type is Operation, click Create
## Name is ''RootName'''''Delete''', type is Operation, click Create
## Name is ''RootName'''''Update''', type is Operation, click Create
## Name is ''RootName'''''Admin''', type is Operation, click Create
## Name is ''RootName'''''View''', type is Operation, click Create
# Now group the four operations under the new task
## At the top, click '''Assign to users'''
## Click on the '''Tasks''' tab
## Select the new task you created (''RootName'''''Administrating''')
## In the third box (on the right) find the four Operations you created and highlight them
## Click the '''<<''' button to move them to the middle box (Assigned operations)

=====Page that already has a Task created for it=====
Once you have created the Task and Operations, or if it was already created:

# Now assign the new permissions for that interface to the right user groups
## Click on '''Roles''' tab at the top
## Find the user group on the left that you want to have permission
## Find the Task in the third box (on the right (''RootName'''''Administrating''')
## Highlight the Task
## Click the '''<<''' button to add it to the group's tasks
# Repeat for any other groups that require permissions

Advanced permissions

2018-06-22T11:11:21Z

Jillhinchliffe: /* New page with no permission */

=Manage advanced permissions=
Permissions on the web site are managed in two ways:
* Menu permissions
** This controls which menu items a user can see and is set in the Administration | Interface | Menu system. If a user does not have permission to see a menu, it won't appear so they cannot select that feature from the interface.
* Advanced permissions
** This controls if a user is actually allowed to access a particular page (whether or not it appears on the menu). If a user does not have page permissions, they will get an error message when they try to access the page.

These permissions are rather more complex. In this example, we will demonstrate giving permission for pages to manage data in program table. If a user gets an access error message, the permissions can be updated as follows:

=====New page with no permission=====
If this is a new interface, then there will be no Operations or Tasks created yet for the page. You have to create these, before you can assign them to a user. If this is an existing page, then the Task should already exist and you can skip this first part.

# Find out the root name of the page that we want to access. On the site, go to the page and look at the URL. For example, if the url is ''www.isikhnas.com/id/trainingCourse/admin'' then the root name of the page is ''TrainingCourse''. Note that the first letter should be made into a capital but the rest should be unchanged
# Go to the interface permissions page: Administration | Permission | Interface Permissions.
# Click on '''Manage Auth Items'''
# Click on '''Create''' under Auth Items
# Create a task to group several operations together:
## Name is ''RootName'''''Administrating''' where ''RootName'' is the root name you found out above
## Type is '''Task'''
## Leave the other fields blank
## Click '''Create'''
# Now create the different operations for that task (not all may be required, depending on what you want people to be able to do)
## Click Create again
## Name is ''RootName'''''Index''', type is Operation, click Create
## Name is ''RootName'''''Create''', type is Operation, click Create
## Name is ''RootName'''''Delete''', type is Operation, click Create
## Name is ''RootName'''''Update''', type is Operation, click Create
## Name is ''RootName'''''Admin''', type is Operation, click Create
## Name is ''RootName'''''View''', type is Operation, click Create
# Now group the four operations under the new task
## At the top, click '''Assign to users'''
## Click on the '''Tasks''' tab
## Select the new task you created (''RootName'''''Administrating''')
## In the third box (on the right) find the four Operations you created and highlight them
## Click the '''<<''' button to move them to the middle box (Assigned operations)

=====Page that already has a Task created for it=====
Once you have created the Task and Operations, or if it was already crated:

# Now assign the new permissions for that interface to the right user groups
## Click on '''Roles''' tab at the top
## Find the user group on the left that you want to have permission
## Find the Task in the third box (on the right (''RootName'''''Administrating''')
## Highlight the Task
## Click the '''<<''' button to add it to the group's tasks
# Repeat for any other groups that require permissions

SMS Handler: Step by step example

2018-06-22T10:58:37Z

Jillhinchliffe: /* 6) Build the message handler */

<languages/>
<translate>
==1) Plan the message== 
* What data are you trying to collect?
* Why?
* Who are the stakeholders?
** Who will provide the data?
*** What information do they have?
*** Can they generate the data reliably?
*** What is the cost of generating and reporting the data?
*** What benefits will they get?
** Who will use the data?
*** What do they need?
*** How long will they need the data to be collected? One off or ongoing?
* Exactly what data items should be collected

==2) Design the database table== 
* Will the data be stored in existing tables or do we need new tables?
* What should the table design be
* Create the tables if necessary
** This currently requires Admin access to the database. Check with Priyono.

==3) Design the SMS message== 
* What will the start code be?
* What fields will be collected?
* What is the type of each field?
* Are there any optional fields?
* Are there any repeating groups?

==4) Set up the SMS message metadata== 
* Go to Edit SMS message, under SMS metadata in the Administration menu
* Create a new SMS message record
** For the reply (and any possible alert messages) put in a temporary placeholder SQL (e.g. "select 'Thank you'")
* Go to Edit SMS Fields
* Add and configure each of the fields

==5) Set up the translation strings ==
* Go to Admin | Interface |translation table
* Add new strings for each of the error messages
* Add new strings for the reply and alert messages (if required)

==6) Build the message handler ==
* Go to Admin | SMS metadata | Create SMS handler
* Select the new message from the list and click submit
* Check any errors carefully. They are usually to do with invalid lookup SQL for specific fields. Proceed if there are no errors, otherwise correct the problems and retry

==7) Test the message ==
* Using IM test the message
* First send just the message start code to check that the format message is correct
* Then send some correct data
* Then send some incorrect data testing each of the validation steps

==8) Finish the reply and alert messages ==
* Now there is some test data, it is easier to compose the SQL for the reply message.
* Build the SQL in PgAdmin first to make sure that it works
* The copy it into the SMS message definition.
** Replace any hard coded user ID values with the predefined variable '''sms_userid'''
** Replace any hard coded message ID values with '''sms_msgid'''
* Save, re-create the handler, and test again

==9) Set up any required custom business rules ==
See the section on Business Rules.

==10) Create output reports==
Create new reports to display, summarise and analyse the data that is now able to be captured.

</translate>

SMS handler setup

2018-06-22T10:52:38Z

Jillhinchliffe:

<languages/>
<translate>
= 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 reply 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 placeholders, 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 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 an 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 required. 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

</translate>

SMS handler setup

2018-06-22T10:39:51Z

Jillhinchliffe: /* Reply SQL */

<languages/>
<translate>
= 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 reply 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 placeholders, 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 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 required. 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

</translate>

New table creation

2018-06-22T10:28:23Z

Jillhinchliffe: /* Reference fields */

=New table creation=
==Technical guide==
New tables are created manually by administrators or iSIKHNAS technical champions using either direct server shell access and psql, or PGAdmin on a remote computer.

In either case you will need access passwords and appropriate permissions for table creation. This example uses PGAdmin.

# Compose your table creation statement using a text editor, based on the standard fields and naming conventions shown below.
# Open PGAdmin and log on to the correct iSIKHNAS database. Possible databases are:
## Crashtest - this is the first one you should use. It is the development server and is reconfigured every night (so all changes are lost). Use this to make sure your table creation script is working
## Training (Sidecar) - the training server is reconfigured every week. Use this for longer term testing, once you think you've got it right
## Live - Once you are sure of the table structure, create it on the live system. it will then be automatically copied to Crashtest (every night) or Sidecar (every Monday).
# Paste your table creation SQL script into an SQL window and run the script
# Set table permissions '''Important'''
## Ensure that isikhnascore and smsd have appropriate permissions on the table, usually including SELECT, UPDATE and INSERT, for any user that needs to access the table:

GRANT SELECT, UPDATE, INSERT ON TABLE ''tablename'' TO isikhnascore;
GRANT SELECT, UPDATE, INSERT ON TABLE ''tablename'' TO smsd;
GRANT SELECT ON TABLE ''tablename'' TO champion;
GRANT SELECT ON TABLE ''tablename'' TO mailer;

# Set sequence permissions '''Very important'''
## Ensure that the sequence has all privileges set for any user that may INSERT records into the data table (mainly isikhnascore and smsd). This is really easy to forget and causes permission errors.

GRANT ALL ON TABLE ''tablename''_id_seq TO isikhnascore;
GRANT ALL ON TABLE ''tablename''_id_seq TO smsd;

# Test the table

==Standard fields==
Every table in the database has a number of standard fields, which are used for consistency and for data auditing purposes. You may only omit or alter these standard fields if you have a very very good reason.

CREATE TABLE ages (
-- record identifier (primary key)
id serial primary key,
...data fields here...
-- if the table is used to store data submitted by SMS
msgid bigint not null,
-- standard system audit fields
createdby integer NOT NULL REFERENCES users,
createdon timestamp NOT NULL DEFAULT now(),
modifiedby integer REFERENCES users,
modifiedon timestamp without time zone,
del boolean NOT NULL DEFAULT false
);

The required standard fields are:
* id - the unique record id and primary key.
* createdby - the id of the user creating the record. This must be explicitly included in any INSERT statements
* createdon - time stamp for creation - automatically inserted
* modifiedby - user id for UPDATE - must be explicitly included
* modifiedon - timestamp for UPDATE - use '''now()'''
* del - the 'deleted' flag. iSIKHNAS never uses DELETE statements, but instead simply flags records as deleted, so they can be referenced for historical purposes or undeleted if required.

If the table is used to store data submitted by SMS or IM, it should also include the '''msgid''' field, which stores the message ID used to link incoming and outgoing SMS data.

==Table and field naming standards==

===Schemas===
There are a number of schemas in the database for different purposes. Most tables should be created in the following schemas:
* data - all routine data tables (i.e. tables containing data submitted by users)
* reference - look up tables
* lab - data tables for the lab system

The other schemas are for specialised purposes
* backoffice - web and database administrative tables
* metadata - data defining table, field, spreadsheet, structures etc
* sms - SMS system management
* reports - output report definitions
* public - not used, except for functions from imported libraries

===Table names===
Tables may be named in English or Indonesian. Core tables developed by the original team have been named in English, but later tables developed by champions can be named in Indonesian.

Tables are named in all lowercase. Multiple words are combined without spaces or underscores. (This rule has been applied inconsistently by some developers less familiar with the standards but should be adhered to for future table creation)

The name of the table represents the elements contained in each record, expressed as a plural (in English). Link tables use the combined names of the two tables they are linking.

Table names should be unique in the entire database (even though tables with the same name are permitted in different schemas).

===Fields===
Fields are named all lowercase with no spaces, with the name reflecting concisely the content.

===Reference fields===
Reference fields are named based on the target table followed by 'id' with no space or underscore. So to reference the id field of the users table, the reference field would be userid.

All reference fields are of type integer except in unusual circumstances where a very large number of records are expected, when a bigint is used.

==Examples==
See the automated list of [[database tables]] for examples. Note the displayed format is not exactly the same as the recommended table definition format in all cases (e.g., the use of 'serial' and references).

====Reference table====
The ageunits table is an example of a simple reference table:

CREATE TABLE ageunits (
id serial PRIMARY KEY,
'''code character varying NOT NULL,'''
'''name character varying[] NOT NULL,'''
createdby integer NOT NULL REFERENCES users,
createdon timestamp NOT NULL DEFAULT now(),
modifiedby integer REFERENCES users,
modifiedon timestamp without time zone,
del boolean NOT NULL DEFAULT false
);

Standard names for reference table fields include:
* name - an array of type character varying. This is used to display the Indonesian name [1] and English name [2]
* code - character varying. A short abbreviation such as may be used in SMS messages.
* hiercode - character varying. Numbers in the form 3.4.2.3 representing a hierarchical coding system. As an example

{{#apGetSQL:
select hiercode, code,
format('%s%s',
repeat(' ',(array_length(regexp_split_to_array(hiercode,'\.'),1)-1)*2),name[2])
from specimentypes order by regexp_split_to_array(hiercode,'\.')|Hiercode,Code, Spesimen}}

====Data tables====
An example of a data table (animals in laboratory submissions) is shown below:

CREATE TABLE animals (
id serial PRIMARY KEY,
'''labsubmissionid integer REFERENCES labsubmissions,'''
'''animalident character varying NOT NULL,'''
'''ownerid integer REFERENCES owners,'''
'''animalid bigint REFERENCES hewan,'''
'''speciesid integer NOT NULL REFERENCES species,'''
'''sexid integer REFERENCES sex,'''
'''age double precision,'''
'''ageunitsid integer REFERENCES ageunits,'''
'''msgid bigint,'''
createdby integer NOT NULL DEFAULT 1 REFERENCES users,
createdon timestamp without time zone NOT NULL DEFAULT now(),
modifiedby integer REFERENCES users,
modifiedon timestamp without time zone,
del boolean NOT NULL DEFAULT false
);

New table creation

2018-06-22T10:24:38Z

Jillhinchliffe: /* Table names */

=New table creation=
==Technical guide==
New tables are created manually by administrators or iSIKHNAS technical champions using either direct server shell access and psql, or PGAdmin on a remote computer.

In either case you will need access passwords and appropriate permissions for table creation. This example uses PGAdmin.

# Compose your table creation statement using a text editor, based on the standard fields and naming conventions shown below.
# Open PGAdmin and log on to the correct iSIKHNAS database. Possible databases are:
## Crashtest - this is the first one you should use. It is the development server and is reconfigured every night (so all changes are lost). Use this to make sure your table creation script is working
## Training (Sidecar) - the training server is reconfigured every week. Use this for longer term testing, once you think you've got it right
## Live - Once you are sure of the table structure, create it on the live system. it will then be automatically copied to Crashtest (every night) or Sidecar (every Monday).
# Paste your table creation SQL script into an SQL window and run the script
# Set table permissions '''Important'''
## Ensure that isikhnascore and smsd have appropriate permissions on the table, usually including SELECT, UPDATE and INSERT, for any user that needs to access the table:

GRANT SELECT, UPDATE, INSERT ON TABLE ''tablename'' TO isikhnascore;
GRANT SELECT, UPDATE, INSERT ON TABLE ''tablename'' TO smsd;
GRANT SELECT ON TABLE ''tablename'' TO champion;
GRANT SELECT ON TABLE ''tablename'' TO mailer;

# Set sequence permissions '''Very important'''
## Ensure that the sequence has all privileges set for any user that may INSERT records into the data table (mainly isikhnascore and smsd). This is really easy to forget and causes permission errors.

GRANT ALL ON TABLE ''tablename''_id_seq TO isikhnascore;
GRANT ALL ON TABLE ''tablename''_id_seq TO smsd;

# Test the table

==Standard fields==
Every table in the database has a number of standard fields, which are used for consistency and for data auditing purposes. You may only omit or alter these standard fields if you have a very very good reason.

CREATE TABLE ages (
-- record identifier (primary key)
id serial primary key,
...data fields here...
-- if the table is used to store data submitted by SMS
msgid bigint not null,
-- standard system audit fields
createdby integer NOT NULL REFERENCES users,
createdon timestamp NOT NULL DEFAULT now(),
modifiedby integer REFERENCES users,
modifiedon timestamp without time zone,
del boolean NOT NULL DEFAULT false
);

The required standard fields are:
* id - the unique record id and primary key.
* createdby - the id of the user creating the record. This must be explicitly included in any INSERT statements
* createdon - time stamp for creation - automatically inserted
* modifiedby - user id for UPDATE - must be explicitly included
* modifiedon - timestamp for UPDATE - use '''now()'''
* del - the 'deleted' flag. iSIKHNAS never uses DELETE statements, but instead simply flags records as deleted, so they can be referenced for historical purposes or undeleted if required.

If the table is used to store data submitted by SMS or IM, it should also include the '''msgid''' field, which stores the message ID used to link incoming and outgoing SMS data.

==Table and field naming standards==

===Schemas===
There are a number of schemas in the database for different purposes. Most tables should be created in the following schemas:
* data - all routine data tables (i.e. tables containing data submitted by users)
* reference - look up tables
* lab - data tables for the lab system

The other schemas are for specialised purposes
* backoffice - web and database administrative tables
* metadata - data defining table, field, spreadsheet, structures etc
* sms - SMS system management
* reports - output report definitions
* public - not used, except for functions from imported libraries

===Table names===
Tables may be named in English or Indonesian. Core tables developed by the original team have been named in English, but later tables developed by champions can be named in Indonesian.

Tables are named in all lowercase. Multiple words are combined without spaces or underscores. (This rule has been applied inconsistently by some developers less familiar with the standards but should be adhered to for future table creation)

The name of the table represents the elements contained in each record, expressed as a plural (in English). Link tables use the combined names of the two tables they are linking.

Table names should be unique in the entire database (even though tables with the same name are permitted in different schemas).

===Fields===
Fields are named all lowercase with no spaces, with the name reflecting concisely the content.

===Reference fields===
Reference fields are named based on the target table followed by 'id' with no space or underscore. So to reference the id field of the user table, the reference field would be userid.

All reference fields are of type integer except in unusual circumstances where a very large number of records are expected, when a bigint is used.

==Examples==
See the automated list of [[database tables]] for examples. Note the displayed format is not exactly the same as the recommended table definition format in all cases (e.g., the use of 'serial' and references).

====Reference table====
The ageunits table is an example of a simple reference table:

CREATE TABLE ageunits (
id serial PRIMARY KEY,
'''code character varying NOT NULL,'''
'''name character varying[] NOT NULL,'''
createdby integer NOT NULL REFERENCES users,
createdon timestamp NOT NULL DEFAULT now(),
modifiedby integer REFERENCES users,
modifiedon timestamp without time zone,
del boolean NOT NULL DEFAULT false
);

Standard names for reference table fields include:
* name - an array of type character varying. This is used to display the Indonesian name [1] and English name [2]
* code - character varying. A short abbreviation such as may be used in SMS messages.
* hiercode - character varying. Numbers in the form 3.4.2.3 representing a hierarchical coding system. As an example

{{#apGetSQL:
select hiercode, code,
format('%s%s',
repeat(' ',(array_length(regexp_split_to_array(hiercode,'\.'),1)-1)*2),name[2])
from specimentypes order by regexp_split_to_array(hiercode,'\.')|Hiercode,Code, Spesimen}}

====Data tables====
An example of a data table (animals in laboratory submissions) is shown below:

CREATE TABLE animals (
id serial PRIMARY KEY,
'''labsubmissionid integer REFERENCES labsubmissions,'''
'''animalident character varying NOT NULL,'''
'''ownerid integer REFERENCES owners,'''
'''animalid bigint REFERENCES hewan,'''
'''speciesid integer NOT NULL REFERENCES species,'''
'''sexid integer REFERENCES sex,'''
'''age double precision,'''
'''ageunitsid integer REFERENCES ageunits,'''
'''msgid bigint,'''
createdby integer NOT NULL DEFAULT 1 REFERENCES users,
createdon timestamp without time zone NOT NULL DEFAULT now(),
modifiedby integer REFERENCES users,
modifiedon timestamp without time zone,
del boolean NOT NULL DEFAULT false
);

New table creation

2018-06-22T10:21:32Z

Jillhinchliffe: /* Table names */

=New table creation=
==Technical guide==
New tables are created manually by administrators or iSIKHNAS technical champions using either direct server shell access and psql, or PGAdmin on a remote computer.

In either case you will need access passwords and appropriate permissions for table creation. This example uses PGAdmin.

# Compose your table creation statement using a text editor, based on the standard fields and naming conventions shown below.
# Open PGAdmin and log on to the correct iSIKHNAS database. Possible databases are:
## Crashtest - this is the first one you should use. It is the development server and is reconfigured every night (so all changes are lost). Use this to make sure your table creation script is working
## Training (Sidecar) - the training server is reconfigured every week. Use this for longer term testing, once you think you've got it right
## Live - Once you are sure of the table structure, create it on the live system. it will then be automatically copied to Crashtest (every night) or Sidecar (every Monday).
# Paste your table creation SQL script into an SQL window and run the script
# Set table permissions '''Important'''
## Ensure that isikhnascore and smsd have appropriate permissions on the table, usually including SELECT, UPDATE and INSERT, for any user that needs to access the table:

GRANT SELECT, UPDATE, INSERT ON TABLE ''tablename'' TO isikhnascore;
GRANT SELECT, UPDATE, INSERT ON TABLE ''tablename'' TO smsd;
GRANT SELECT ON TABLE ''tablename'' TO champion;
GRANT SELECT ON TABLE ''tablename'' TO mailer;

# Set sequence permissions '''Very important'''
## Ensure that the sequence has all privileges set for any user that may INSERT records into the data table (mainly isikhnascore and smsd). This is really easy to forget and causes permission errors.

GRANT ALL ON TABLE ''tablename''_id_seq TO isikhnascore;
GRANT ALL ON TABLE ''tablename''_id_seq TO smsd;

# Test the table

==Standard fields==
Every table in the database has a number of standard fields, which are used for consistency and for data auditing purposes. You may only omit or alter these standard fields if you have a very very good reason.

CREATE TABLE ages (
-- record identifier (primary key)
id serial primary key,
...data fields here...
-- if the table is used to store data submitted by SMS
msgid bigint not null,
-- standard system audit fields
createdby integer NOT NULL REFERENCES users,
createdon timestamp NOT NULL DEFAULT now(),
modifiedby integer REFERENCES users,
modifiedon timestamp without time zone,
del boolean NOT NULL DEFAULT false
);

The required standard fields are:
* id - the unique record id and primary key.
* createdby - the id of the user creating the record. This must be explicitly included in any INSERT statements
* createdon - time stamp for creation - automatically inserted
* modifiedby - user id for UPDATE - must be explicitly included
* modifiedon - timestamp for UPDATE - use '''now()'''
* del - the 'deleted' flag. iSIKHNAS never uses DELETE statements, but instead simply flags records as deleted, so they can be referenced for historical purposes or undeleted if required.

If the table is used to store data submitted by SMS or IM, it should also include the '''msgid''' field, which stores the message ID used to link incoming and outgoing SMS data.

==Table and field naming standards==

===Schemas===
There are a number of schemas in the database for different purposes. Most tables should be created in the following schemas:
* data - all routine data tables (i.e. tables containing data submitted by users)
* reference - look up tables
* lab - data tables for the lab system

The other schemas are for specialised purposes
* backoffice - web and database administrative tables
* metadata - data defining table, field, spreadsheet, structures etc
* sms - SMS system management
* reports - output report definitions
* public - not used, except for functions from imported libraries

===Table names===
Tables may be named in English or Indonesian. Core tables developed by the original team have been named in English, but later tables developed by champions can be named in Indonesian.

Tables are named in all lowercase. Multiple words are combined without spaces or underscores. (This rule has been applied inconsistently by some developers less familiar with the standards but should be adhered to for future table creation)

The name of the table represents the elements contained in each record, expressed as a plural (in English). Link tables use the combined names of the two tables they are linking.

Table names should be unique in the entire database (even though tables with the same name are permitted in different schema).

===Fields===
Fields are named all lowercase with no spaces, with the name reflecting concisely the content.

===Reference fields===
Reference fields are named based on the target table followed by 'id' with no space or underscore. So to reference the id field of the user table, the reference field would be userid.

All reference fields are of type integer except in unusual circumstances where a very large number of records are expected, when a bigint is used.

==Examples==
See the automated list of [[database tables]] for examples. Note the displayed format is not exactly the same as the recommended table definition format in all cases (e.g., the use of 'serial' and references).

====Reference table====
The ageunits table is an example of a simple reference table:

CREATE TABLE ageunits (
id serial PRIMARY KEY,
'''code character varying NOT NULL,'''
'''name character varying[] NOT NULL,'''
createdby integer NOT NULL REFERENCES users,
createdon timestamp NOT NULL DEFAULT now(),
modifiedby integer REFERENCES users,
modifiedon timestamp without time zone,
del boolean NOT NULL DEFAULT false
);

Standard names for reference table fields include:
* name - an array of type character varying. This is used to display the Indonesian name [1] and English name [2]
* code - character varying. A short abbreviation such as may be used in SMS messages.
* hiercode - character varying. Numbers in the form 3.4.2.3 representing a hierarchical coding system. As an example

{{#apGetSQL:
select hiercode, code,
format('%s%s',
repeat(' ',(array_length(regexp_split_to_array(hiercode,'\.'),1)-1)*2),name[2])
from specimentypes order by regexp_split_to_array(hiercode,'\.')|Hiercode,Code, Spesimen}}

====Data tables====
An example of a data table (animals in laboratory submissions) is shown below:

CREATE TABLE animals (
id serial PRIMARY KEY,
'''labsubmissionid integer REFERENCES labsubmissions,'''
'''animalident character varying NOT NULL,'''
'''ownerid integer REFERENCES owners,'''
'''animalid bigint REFERENCES hewan,'''
'''speciesid integer NOT NULL REFERENCES species,'''
'''sexid integer REFERENCES sex,'''
'''age double precision,'''
'''ageunitsid integer REFERENCES ageunits,'''
'''msgid bigint,'''
createdby integer NOT NULL DEFAULT 1 REFERENCES users,
createdon timestamp without time zone NOT NULL DEFAULT now(),
modifiedby integer REFERENCES users,
modifiedon timestamp without time zone,
del boolean NOT NULL DEFAULT false
);

New table creation

2018-06-22T10:20:01Z

Jillhinchliffe: /* Schemas */

=New table creation=
==Technical guide==
New tables are created manually by administrators or iSIKHNAS technical champions using either direct server shell access and psql, or PGAdmin on a remote computer.

In either case you will need access passwords and appropriate permissions for table creation. This example uses PGAdmin.

# Compose your table creation statement using a text editor, based on the standard fields and naming conventions shown below.
# Open PGAdmin and log on to the correct iSIKHNAS database. Possible databases are:
## Crashtest - this is the first one you should use. It is the development server and is reconfigured every night (so all changes are lost). Use this to make sure your table creation script is working
## Training (Sidecar) - the training server is reconfigured every week. Use this for longer term testing, once you think you've got it right
## Live - Once you are sure of the table structure, create it on the live system. it will then be automatically copied to Crashtest (every night) or Sidecar (every Monday).
# Paste your table creation SQL script into an SQL window and run the script
# Set table permissions '''Important'''
## Ensure that isikhnascore and smsd have appropriate permissions on the table, usually including SELECT, UPDATE and INSERT, for any user that needs to access the table:

GRANT SELECT, UPDATE, INSERT ON TABLE ''tablename'' TO isikhnascore;
GRANT SELECT, UPDATE, INSERT ON TABLE ''tablename'' TO smsd;
GRANT SELECT ON TABLE ''tablename'' TO champion;
GRANT SELECT ON TABLE ''tablename'' TO mailer;

# Set sequence permissions '''Very important'''
## Ensure that the sequence has all privileges set for any user that may INSERT records into the data table (mainly isikhnascore and smsd). This is really easy to forget and causes permission errors.

GRANT ALL ON TABLE ''tablename''_id_seq TO isikhnascore;
GRANT ALL ON TABLE ''tablename''_id_seq TO smsd;

# Test the table

==Standard fields==
Every table in the database has a number of standard fields, which are used for consistency and for data auditing purposes. You may only omit or alter these standard fields if you have a very very good reason.

CREATE TABLE ages (
-- record identifier (primary key)
id serial primary key,
...data fields here...
-- if the table is used to store data submitted by SMS
msgid bigint not null,
-- standard system audit fields
createdby integer NOT NULL REFERENCES users,
createdon timestamp NOT NULL DEFAULT now(),
modifiedby integer REFERENCES users,
modifiedon timestamp without time zone,
del boolean NOT NULL DEFAULT false
);

The required standard fields are:
* id - the unique record id and primary key.
* createdby - the id of the user creating the record. This must be explicitly included in any INSERT statements
* createdon - time stamp for creation - automatically inserted
* modifiedby - user id for UPDATE - must be explicitly included
* modifiedon - timestamp for UPDATE - use '''now()'''
* del - the 'deleted' flag. iSIKHNAS never uses DELETE statements, but instead simply flags records as deleted, so they can be referenced for historical purposes or undeleted if required.

If the table is used to store data submitted by SMS or IM, it should also include the '''msgid''' field, which stores the message ID used to link incoming and outgoing SMS data.

==Table and field naming standards==

===Schemas===
There are a number of schemas in the database for different purposes. Most tables should be created in the following schemas:
* data - all routine data tables (i.e. tables containing data submitted by users)
* reference - look up tables
* lab - data tables for the lab system

The other schemas are for specialised purposes
* backoffice - web and database administrative tables
* metadata - data defining table, field, spreadsheet, structures etc
* sms - SMS system management
* reports - output report definitions
* public - not used, except for functions from imported libraries

===Table names===
Tables may be named in English or Indonesian. Core tables developed by the original team have been named in English, but later tables developed by champions can be named in Indonesian.

Tables are named in all lowercase. Multiple words are combined without spaces or underscores. (This rule has been applied inconsistently by some developers less familiar with the standards but should be adhered to for future table creation)

The name of the table represents the elements contained in each record, expressed as a plural (in English). Link tables are use the combined names of the two tables they are linking.

Table names should be unique in the entire database (even though tables with the same name are permitted in different schema.

===Fields===
Fields are named all lowercase with no spaces, with the name reflecting concisely the content.

===Reference fields===
Reference fields are named based on the target table followed by 'id' with no space or underscore. So to reference the id field of the user table, the reference field would be userid.

All reference fields are of type integer except in unusual circumstances where a very large number of records are expected, when a bigint is used.

==Examples==
See the automated list of [[database tables]] for examples. Note the displayed format is not exactly the same as the recommended table definition format in all cases (e.g., the use of 'serial' and references).

====Reference table====
The ageunits table is an example of a simple reference table:

CREATE TABLE ageunits (
id serial PRIMARY KEY,
'''code character varying NOT NULL,'''
'''name character varying[] NOT NULL,'''
createdby integer NOT NULL REFERENCES users,
createdon timestamp NOT NULL DEFAULT now(),
modifiedby integer REFERENCES users,
modifiedon timestamp without time zone,
del boolean NOT NULL DEFAULT false
);

Standard names for reference table fields include:
* name - an array of type character varying. This is used to display the Indonesian name [1] and English name [2]
* code - character varying. A short abbreviation such as may be used in SMS messages.
* hiercode - character varying. Numbers in the form 3.4.2.3 representing a hierarchical coding system. As an example

{{#apGetSQL:
select hiercode, code,
format('%s%s',
repeat(' ',(array_length(regexp_split_to_array(hiercode,'\.'),1)-1)*2),name[2])
from specimentypes order by regexp_split_to_array(hiercode,'\.')|Hiercode,Code, Spesimen}}

====Data tables====
An example of a data table (animals in laboratory submissions) is shown below:

CREATE TABLE animals (
id serial PRIMARY KEY,
'''labsubmissionid integer REFERENCES labsubmissions,'''
'''animalident character varying NOT NULL,'''
'''ownerid integer REFERENCES owners,'''
'''animalid bigint REFERENCES hewan,'''
'''speciesid integer NOT NULL REFERENCES species,'''
'''sexid integer REFERENCES sex,'''
'''age double precision,'''
'''ageunitsid integer REFERENCES ageunits,'''
'''msgid bigint,'''
createdby integer NOT NULL DEFAULT 1 REFERENCES users,
createdon timestamp without time zone NOT NULL DEFAULT now(),
modifiedby integer REFERENCES users,
modifiedon timestamp without time zone,
del boolean NOT NULL DEFAULT false
);

IT:Servers

2018-06-22T09:56:48Z

Jillhinchliffe:

<languages/>
<translate>
=Servers= 
The VPC is divided into two zones, which are hosted in different physical locations. Further, there are two subnets. The public subnet contains servers that (through the NAT) are able to be accessed from outside the VPC (web and messaging servers). The private subnet contains servers that can only be accessed from within the VPC (database, processing and reporting servers).


All iSIKHNAS servers are listed here. They are identified by their location (zone 1 or zone 2, indicating the two physical locations), subnet (private or public), their [http://aws.amazon.com/ec2/instance-types instance type] (indicating memory and CPU capacity) and whether they are autoscaling (elastic) or not. Load balancers are used to distribute requests to autoscaling servers.

==Network Address Translation (NAT) server== 
This is the only point of access to the VPC. It is a single instance (non-elastic) and is located in Zone 1, public subnet. Its sole role is to manage security and direct incoming traffic to the correct server in the VPC.


Type: m1.small

==Messaging server== 
This is in the public subnet of zone 1 and is a single instance. It is responsible for sending and receiving e-mail, managing the SMS system and managing Instant messaging.


Type:m3.medium

=====E-mail processing===== 
In addition to an email server, it uses email aliases to direct incoming email to the email parsing script (Python). This checks that the sender is authorised, and saves any Excel attachments to an S3 bucket (com.isikhnas.queuefiles). It then puts a message on the emailinput queue for processing by the processing server.


Outgoing email is managed by a Python daemon, listening on the emailoutput queue, and getting attachments from the com.isikhnas.emailattachments bucket.

=====SMS processing===== 
SMS messages are managed by SMSTools3. Incoming messages are parsed and submitted to the master database, which is then checked for any response messages.

=====Instant messaging===== 
IM is managed by a Python daemon which listens for incoming messages, submits them to the database, and checks for outgoing responses in the same way as the SMS sytem.
In addition, if an incoming message contains a link to an image, the image is saved to an S3 bucket (static.isikhnas.com/images), and the message is recoded as an IMAGE message to store the file name and attach it to a case record.

==Processing server== 
This is an elastic instance located in the private subnet. The main instance is located in Zone 1, but elastic instances are created so as to balance the numbers between zones 1 and 2.


This server processes incoming email Excel attachments, parsing their contents and inserting them into the database. It gets request from the emailinput queue, and gets attachments from the com.isikhnas.queuefiles bucket. Once processed, it puts request to send email responses on the emailoutput queue, and any attachments for the email in the com.isikhnas.emailattachments bucket.


This server has a Python daemon listening for messages on the emailinput queue.


Type: m3.medium

==Reporting server== 
This is an elastic instance located in the private subnet. The main instance is located in Zone 1, but elastic instances are created so as to balance the numbers between zones 1 and 2.


This server generates output reports, including regular e-mail reports (daily, weekly, monthly), and ad hoc reports based on templates (for example client reports after laboratory submissions).


Regular reports are generated by a Python script triggered by a cron job. Ad hoc reports are produced by a daemon listing on the reportrequest queue. Completed reports are stored in the reports.isikhnas.com S3 bucket and email request placed on the outgoing email queue.


Type: m3.medium

==Web servers== 
These are elastic instance located in the public submit. The main instance is located in Zone 1, but elastic instances are created so as to balance the numbers between zones 1 and 2.


These servers provide the iSIKHNAS web site.


Type: m3.medium

==Database servers== 
=====Master database server=====
There is a single instance of the master database server located in the private subnet of zone 1. This handles all database requests that require writing of data, and is accessible only from within the VPC.


Type: m3.medium

=====Slave (read-replicate) database servers===== 
This is an elastic group of servers in the private subnet, balanced bewteen zones 1 and 2. These servers play a triple role:
* real time backup
* automated promotion to master in case of failure
* read-access
** any reporting or analysis process that requires only read access to the database is directed to one of the read-replicas. This minimises load on the master database to maximise performance. This is necessary as some of the output reports require extensive and complex database processing, which would otherwise slow down the master database for other requests.


Type: m3.large

==Training server== 
The training server (Sidecar) is completely independent from the VPC, and contains a duplicate database, web site and SMS messaging system. The database is automatically updated from the live database every Monday morning, and updates to the user and infrastructure tables (only) on the live server are instantly copied to Sidecar.


Email and Instant messaging requests for the training server are handled by the messaging server, which direct the requests to the Sidecar database.


Type: m3.medium

==Wiki server== 
This is a self-contained documentation server located in the VPC public subnet in zone 1. It runs MediaWiki and a MySQL database. To increase performance during CPU intensive processes (particularly when exporting content to ODT or PDF format), there is a separate Wiki rendering server dedicated to that role.


Type: m3.medium and T2.small (renderer)
</translate>

Overview

2018-06-22T09:49:52Z

Jillhinchliffe:

<languages/>
<translate>

=iSIKHNAS Server Infrastructure= 
iSIKHNAS is hosted in a Virtual Private Cloud (VPC) on Amazon Web Services (AWS) cloud servers. The VPC consists of a number of different components (servers, queues, storage) that expand and contract according to need.


[[File:cloudoverview.png|800px|centre]]

==Security== 
=====Access security=====
The Virtual Private Cloud allows computers within the cloud to communicate between themselves rapidly and easily, while blocking all access from outside the VPC. Access is controlled by a single, highly secure NAT server.


All communications with iSIKHNAS are encrypted using secure industry standard Transport Layer Security (TLS) encryption, preventing interception of any data exchanged between the user and the VPC.


Administrative access through the NAT server is controlled using digital certificates.

=====Data security and redundancy===== 
The iSIKHNAS database runs on a single master database, with multiple slave databases providing real-time replication. Some of the slaves are always in a different physical location to the master. In case of failure of the master, one of the slaves is automatically promoted to be the master, resulting in down-time of only a few seconds.


In addition to database replication, the full system is automatically backed up every 12 hours and stored in replicated off-site storage.


The VPC is spread across two security zones, representing different physical locations, with servers and queues replicated in each zone. This means that should there be a catastrophic failure of an entire data centre, the system will continue to run uninterrupted.

==[[IT:Servers|Servers]]== 
The VPC consists of a number of different servers dedicated to different tasks:
* NAT (for controlling access to the VPC)
* Database (master and read-replica slave servers)
* Web servers (for hosting the web site)
* Messaging server (for managing SMS, IM and email)
* Processing (for processing incoming requests on email)
* Reporting (for analysing data and generating outputs)
* Wiki (for managing this Wiki server)


While some of these servers are single fixed instances, others are Elastic Cloud Computing (EC2) instances. This means that, as demand increases, the server is automatically replicated to increase capacity. As demand decreases, servers no longer in use are shut down.


All servers use the Ubuntu operating system, with open source and custom built software configured to optimise the performance of their particular tasks.


[[IT:Servers|More details...]]

==[[IT:Storage|Storage]]== 
AWS S3 storage buckets are used to provide rapid access to unlimited storage for both temporary and static data. The content of each bucket is automatically replicated and stored in three different physical locations.


[[IT:Storage|More details...]]

==[[IT:Queues|Queues]]== 
The AWS Simple Queue System (SQS) is used to manage the exchange of tasks between the different servers in the VPC. For example, an incoming email is analysed by the messaging server, and then put in the processing queue. The processing server then gets the request from the queue and processes it. Once finished, if there is a response email to send, a request is put on the outgoing email queue, and if there is a report to be generated, a request is put on the reporting queue, to be picked up by the next available report server.
This approach, known as 'loose coupling', allows complex and processor-intensive tasks to be shared between a cluster of different servers, increasing speed and redundancy.


[[IT:Queues|More details...]]

==Daemons== 
In order to respond to various system events, a number of daemons have been created. These are small programs that are running full time in the background, waiting for a job. When an event occurs (such as a new IM message, or a message is available on a queue that the daemon serves), the daemon processes that message, and continues to wait.


iSIKHNAS daemons are written in Python, and controlled with the Linux '''upstart''' tool, which manages starting, stopping, and automatically re-starting the daemon in case of failure.


Daemons include:
* Processor for incoming email
* Report processor
* Instant messaging system
* E-mail sender

==Technologies== 
iSIKHNAS uses a range of AWS technologies to maximise robustness, performance, security and flexibility. These include:
* EC2 (Elastic cloud computing for cost-effective response to demand)
* Route 53 (scalable domain name system)
* VPC (Virtual private cloud, for security)
* S3 (Scalable storage)
* IAM (secure access control)
* SES (e-mail sending service)
* SQS (messaging queue service)
* CloudFront (cached content delivery for increased speed and efficiency)

==Software== 
iSIKHNAS uses a range of open source and custom-built software, on Ubuntu servers.
The main tools used are:


* Apache2 web server
* PostgreSQL database server, with the PostGIS extension
* Python programming language
* PHP and the Yii framework for web page delivery
* MediaWiki for the Wiki
</translate>

Overview

2018-06-22T09:47:21Z

Jillhinchliffe:

Overview of data managed

2018-06-22T09:25:05Z

Jillhinchliffe:

<languages/>
<translate>
==Overview of data managed== 
* [[Data overview: Field disease reporting|Field disease reporting]]
* [[Data overview: Laboratory data|Laboratory data]]
* [[Data overview: Slaughter statistics|Slaughter statistics]]
* [[Data overview: Livestock movement|Livestock movement]]
* [[Data overview: Population data|Population data]]
* [[Data overview: Vaccination programs|Vaccination programs]]
* [[Data overview: Active surveillance|Active surveillance]]
* [[Data overview: Animal identification|Animal identification]]
* [[Data overview: Breeding management|Breeding management]]
* Proposed future functionality
** Feed and feed supplies
** Meat inspections
** Antimicrobial Resistance monitoring
** Milk recording
** Drug registration management
</translate>