Overview odk_planner Documentation¶

Example¶

The main functionality of odk_planner is to show all submitted form data in a large Overview table, sorted by study subject id. Imagine that you have a study with an initial screening form CRF1 that is followed by a second examination form CRF2 and a lab form LRF1. When you look into your data using ODK Aggregate, it would be displayed as three separate tables (the data shown below is taken from the tutorial)

It’s obvious that this kind of data display quickly becomes quite confusing – with thousands of participants and dozens of forms in a real-world scenario... odk_planner extracts the data from the database and displays a neat overview form. For the data above, this would like this

Features¶

In particular, odk_planner has the following features

• cells in the overview table can be highlighted according to the relative timing to other forms: for example, the empty cell 80006/CRF2 in the table above could be colored if 80006/CRF1 has been entered a defined number of days ago
• cells in the overview can also be highlighted depending on the data of values in the submitted forms: for example, the submissions of LRF1 above could be colored if the hemoglobin level is outside a specified range
• the data in the overview table can be used to generate printable tables of missing forms, and these reports of missing forms can even automatically be sent in emails
• participants can be notified by SMS, based on the highlighting of the cells in the overview table; this mechanism can also be configured to send data from the forms to a specified number
• all data in the database can be viewed by clicking on the links in the overview table; contrary to ODK Aggregate, you can specify detailed access permissions for every datapoint
• the schema of the data is read from the same Excel files that are used to generate the .xml forms via XLSForm
• to put the cherry on the cake, this web application comes bundled with some tools that can be used to push data automatically into the ODK database; with this mechanism you can for example integrate integrate X-ray images or data from a MS-SQL database – transparently and fully automatically

Overview¶

The documentation is structured as follows:

• Installation explains how to set up odk_planner; in a typical setting, this part of the documentation would only be read by the system administrator who also installed the Aggregate server
• Configuration describes how to adapt the configuration file config.xls to your needs
• Using describes the features in more detail
• Tools gives an overview of the additional (Python-based) software included in the distribution to automate the integration of data from different sources into your Aggregate database
• Tutorial finally describes a sample setup and comes with some sample data that quickly lets you play around with odk_planner to get an idea of its functionality

Prerequisites¶

• A working ODK Aggregate (tested against v1.3.2) installation. odk_planner can be installed on the same computer that runs ODK Aggregate, or on a different computer that can connect to the MySQL server that is used by ODK Aggregate.
• Web server (tested with Apache) and PHP
• Python2 : Python scripts are used to create new instances and for automated testing
• Python3 : ODK pusher can be used to automatically push data to the ODK Aggregate server and is built with Python3

Please see the following documents on how to install these prerequisites on your local computer:

• On Windows, install MySQL, Apache and PHP bundled in one simple download: XAMPP
• On Linux make sure you installed the packages apache2, mysql-server, and php5
• On OS X you should first install Brew and then download the package mysql – apache/php are installed by default, but must be activated

Once you got these up and running you can continue with the detailed description on how to install ODK Aggregate

Get odk_planner¶

Get odk_planner as a ZIP file or clone the entire repository from GitHub. Then simply move the entire odk_planner directory structure into Apache’s documents root folder.

• on OS X this is /Library/WebServer/Documents/
• on Debian this is /var/www/
• when using XAMPP on Windows this is C:\xampp\xampp\htdocs

When you now open http://localhost/odk_planner in your web browser you should see the following

On Linux/Unix/OS X it is important to configure the file access rights correctly so that the user running apache can modify the per-instance files (it’s enough to set the correct group; write permissions will be set by the script create_instance.py):

cd /path/to/odk_planner
sudo chown :_www instances/ # on OS X
sudo chown :www-data instances/ # on Debian

Setup instance¶

Next thing to do is to set up an instance. odk_planner saves all its configuration, forms, and log data on a per-instance basis. With a single download of odk_planner you can set up any number of instances that are completely independent of each other.

If you installed odk_planner into the root directory of your web server, then an instance named instance_name can be reached under the following two addresses:

http://localhost/odk_planner/index.php?instance=instance_name
http://localhost/odk_planner/instance_name

A new instance is then created by executing the script tools/create_instance.py (to change the proposed default values please modify the file test/demo/config/config-sample.ini):

$ python tools/create_instance.py

this script will create a new odk_planner instance
--------------------------------------------------

instance name: test
MySQL username: ["odk_planner_ro"] aggregate_user
MySQL password: ["0dk pa2sw0rd"] lnb83oSL%.ni
MySQL database: ["odk_planner_test"] main_study
MySQL host: ["127.0.0.1"]

generated new instance:

  - name: test
  - temporary password: xL6NZQfL

make sure that the directory "instances/test/" and all its
contents are writable by the apache user (this should automatically be the
case if the directory "instances/" has the right group
ownership)


press <ENTER> to continue...

The script asks for an instance name (below referenced as instance_name) and MySQL connection parameters. After creating the instance, the temporary password (xL6NZQfL in the example above) is printed. This password can be used for an initial login, before new passwords are set and will be deactivated after the config is changed via the webinterface for a first time.

This part of the configuration is never shown to the user of the webapp and can only be modified by editing the textfile instances/<instance_name>/config/config.ini; the following values can be specified

• login_timeout : after how many minutes a user should be automatically logged out in absence of activity
• proxy : optional setting if a proxy server has to be used to access other websites (used for sending SMS)
• MySQL settings : db_host, db_database, db_user, db_pass : the credentials with which odk_planner will access the MySQL database. See the file create_db_and_user.sql that was created during ODK Aggregate installtion (or get them from the webapp). Note that odk_planner only needs read rights and a new user without write access should be created.
• email settings : smtp_server, smtp_user, smtp_pass : reports are sent via email when automatization is used; these settings specify a valid email account from which the email can be sent (the server running the webapp does not need to be configured to send emails; being able to connect any SMTP server is enough)
• default settings for new instances can be changed by editing the file test/demo/config/config-sample.ini

The initial configuration is the same that is used in the tutorial, but this can also be changed by modifying the file test/demo/config/config-sample.xls

Installing cron job¶

The script cron.php (see automatization) should be called on a daily basis. To install cronjob that executes the script once a day, execute the following command (as root):

crontab -u www-data -e

Then add a new line at the end of the file for every instance that you want to automatize (the following example will run the cron script once a day at 5 AM):

0 5 * * * /usr/bin/php /path/to/odk_planner/cron.php -i instance_name

Confirm that

1. you use the same user that runs the PHP script when called from the web server (i.e. the www-data in the example above)
2. you specify the right path to odk_planner
3. the user specified above actually is permitted to run cron jobs
4. that no error output is generated (check the local email of the user running the cron job)

settings sheet¶

This sheet contains general information on how the data in the ODK database is structered and how it should be accessed as well as the email configuration.

• idfield : Name of field that links forms together (e.g. study participant ID). If a form does not have this field, it will not be displayed in the Overview entered forms
• idfield_start, idfield_length : Which part of the idfield should be used to match different rows into the same column. For example, if IDs include a variable part at the end, such as the visit identifier -V01 in the ID 80001-31-V01, then $idfield_start=0 and $idfield_length=8 would match the IDs 80001-31-V01 and 80001-31-E03
• datefield : Specify which form field should be used to determine when the form was filled in. Normally, such a field would be specified as type today in the .xls form. If a given form does not have the specified field, the submission date is taken (auto generated by ODK).
• title : The title is showed on the login site and in the menu; particularly handy if using multiple instances.
• opentabs : set to yes if you want data and sms related pages to automatically open in a new tab.

users sheet¶

Access to odk_planner is restricted by username/password. Different users can have different access rights. The username/password/access rights are stored in the columns name, password, rights and access.

The following rights are defined (specify any combination separated by commas):

• overview : can dispaly the main overview table of entered data
• data : can see the actual data inside the forms; what fields are visible to what user can be set by different values for access. see Example of access restrictions
• forms : can upload and delete .xls forms (see Uploading form template)
• sms : can send messages
• admin : can change the configuration file config.xls and view the logs

overview sheet¶

This sheet specifies which IDs should be grouped together into one overview table. For example, a project can contain two groups of subjects with a partly overlapping set of forms for each. Instead of generating one big overview containing all subjects and all forms, it would then be more clearly laid out to have an different overview table for every group. The id_rlike field contains an expression that matches all IDs for the given group – a ".*" (without double quotes) is a wildchar that matches any number of characters (read more about MySQL regular expressions; and a relatively easy tutorial can be found here). In addition to the ID-based separation, it is also possible to use arbitrary fields from submitted forms to define what cases should be shown in which overview (by specifying a condition; see below for the format of the conditions). A given ID may appear in any combination of overviews.

Example : show all forms of all IDs in same big overview table

Example : If the IDs of patients start with 80 and the IDs of controls start with 83, then the following table would generate two overview tables, showing the CRFs and the LRFs respectively, each overview split into tables with subheadings for cases and controls.

Example : Split IDs into two overviews showing the same forms, depending on whether the field CRF1\sex is male or female:

colors sheet¶

Every row in this sheet defines a timing constraint that is visually displayed in the overview table. A timing constraint is defined between two forms. Once the form specified by the column form1 is entered, a timer is started. When this timer reaches delay days and the form form2 is not yet entered into the database, then the corresponding table cell will get the CSS style specified in the style column (defaults to background-color property). If more than one row apply to a cell, then its styles are applied in the same order as the rows in the table.

As a special case, the cell of a form can be colored independent of any other form. This way, forms can be highlighted depending on their content (using conditions). In this case, specifying only form2 is enough (see example below).

As a special special case, only form2 is specified and it’s set to a single asterisk * which will allow to color the row header (where the ID is shown).

Optionally, this coloring can be made dependent on any number of conditions that must all be fulfilled in order for this coloring rule to apply to the given forms. These conditions are specified in the column condition.

field1 > 1 & (formid1\field2 = "value with space" | field3 < 5)

Remarks:

• when no formid is specified, the field refers to the form from column form2
• only the operators <, >, = and != are allowed
• if the value contains spaces, it must be enclosed in double quotes
• logical or | as well as logical and & can be used to connect different expressions (and takes precedence over or)
• arbitrarily complex logical expressions can be constructed using grouping parenthesis
• an expression with a non-existing field (e.g. because a form has not been submitted) always evaluates as false; therefore, an expression like FORM\FIELD>"" will therefore evaluate to false if the form is not submitted, the field is not found in the form, or the value of the field is the empty string – in all other cases the expression would evaluate to true (regardless of the datatype of the value)

Example : The following lines will highlight the empty cell for the follow_up form if the briefing form has has specified that a follow up should be done for this subject (field needs_follow_up). The empty cell will first be highlighted green after a week. If it’s still empty after a second week, the cell color will turn orange and then finally red. Additionally, IDs (in the row header) are underlined for these individuals.

field1 > 1 & (formid1\field2 = "value with space" | field3 < 5)

cron sheet¶

This sheet contains settings regarding the automatization:

• notify_email : This address will receive an update every time the script is run autonomously.
• notify_logs : A comma separated list of log files that are sent to the notify_email. The log files are added as .csv files and only new entries since the last email are included. If nothing changed since the last email, the log is not attached.
• profile : If this value is set to yes, performance information is added to the cron log.
• reports_mdays : A comma-separated list of days of the month on which a list of missing forms is attached to the email.

sms sheet¶

Configuration of messaging is done via the following key/value pairs:

• default_country_prefix : if a phone number is specified with one leading zero ("0xxxxxxxx") then the default_country_prefix is automatically added in the beginning of the number (after stripping the single zero)
• url : URL of messaging API (e.g. http://bulksms.vsms.net:5567/eapi/submission/send_sms/2/2.0 when using the bulksms API)
• response_regexp : a regular expression (PCRE, but without the delimiters) that must match the response body sent by the server when a message was successfully delivered
• params : default parameters that are sent with the message; include username and password here (e.g. username=XXX&password=XXX for the bulksms API)
• param_message : name of the parameter that is used for the message (e.g. message for the bulksms API)
• param_number : name of the parameter that is used to specify the receiver’s phone number (e.g. msisdn for the bulksms API) – the number will be formatted with country prefix but without any leading zeroes or plus sign
• phone_numbers defines where phone numbers can be found in the forms. every entry has the form form_id\field_name and multiple entries are separated by spaces. forms that do not exist on the server, that are not found for the given patient, or that do not feature the specified field, are simply ignored.
• template_NAME entries define the messaging templates. When writing a message using odk_planner, the message text can be chosen from these templates. The content of the templates is the only content that is allowed for autogenerated messages.
  • if this template starts with a phone number in the form +1234567890 (including country code) then the message is sent to this number instead of the number defined by phone_numbers
  • every {FORMID\FIELD} is replaced with the content of the specified form for the given patient. only fields that have a access column with sms can be sent via this mechanism

More sheets¶

odk_planner can be extended with additional files in the plugins/ directory that can use settings in the existing sheets or add a new sheet (with the same name as the plugin) to store settings. An example is the aforementioned sms sheets (the main plugin file is plugins/sms.php). Another example is the doughnut sheet that specifies which fields should be used to generate doughnut plots. Please see the comments in the file test/demo/config/confi.xls and in the file plugins/dougnut.php for more information how to configure these extensions.

Uploading form template¶

The very same Excel sheets that are used as input to XLSForm also serve to describe the content of the databases to the odk_planner. The grouping used in the spreadsheet is replicated when content of individual forms is displayed. Note that the xls forms must have the same filename as the ones that were used to generate the xml files (which is not necessarily the same as the form_id in the settings sheet of the xls form).

A simple access control system is provided by using an additional column access in the excel form: If this column is not existing or empty, the string default is assumed to be its content. Whenever a user views a form, a field is only displayed if the user’s acl configuration parameter contains at least one of the comma-separated names that are listed in the access column. Group names can be freely defined apart from the predefined name default – the following example chose the group names sensitive and secretary. If a field has the special access group sms, then its value can be sent via short messages.

Forms can be managed by any user having the “forms” right. The form overview page is displayed by navigating through the forms menu item on top of the page. This page shows an overview of all uploaded forms in a table.

If there was a problem in the matching between database columns and form .xls fields, it is displayed in the “info” column of this overview table. A not matched indicates that the corresponding form could not be matched with any form in the database – make sure the .xml form was uploaded to the aggregate server.

This view also allows to delete forms and upload new ones. Make sure your spreadsheet has the extension .xls and is in Microsoft Excel 97/2000/XP/2003 format.

Overview entered forms¶

The main purpose of odk_planner is to shows an overview of all entered forms (as far as their corresponding .xls form has been uploaded) so far in a structured way. The different forms are linked together using a special field $idfield that must be present in every individual form. The overview table view displays all forms submitted so far, with all forms having the same $idfield arranged in a row. For every form the submission date is displayed. It is possible to have more than one submission of the same form for the same $idfield – this would be displayed with two different submission date/times.

The “colors” sheet in config.xls specifies the temporal interdependance of the different forms. Normally, a follow-up form is expected to be submitted after a given delay. If the time period between two forms is longer than specified (or if the follow-up form has not been submitted yet and the current date is alreday considered “late”), the corresponding field in the overview table is highlighted.

The data of individual submissions can be viewed by clicking on the submission date/time (user needs data right).

Viewing data¶

After selecting a submission for display by clicking on it in the overview table, its contents are arranged following the structure of the corresponding uploaded .xls form. Individual form fields can be selectively (masked) from users by using a access column in the .xls form. See above for more on access restrictions.

Doughnut plot¶

The plugin dougnut generates overview plots that show the distribution of a field of interest over the whole study population. Ordinal data is put into pre-specified bins before plotting. The example below shows the weight distribution in the demo dataset.

Sending SMS¶

If participants save their phone number in a form, this information can be used to send short messages. Refer to sms sheet for proper configuration.

Messages can be sent manually: Click on the small letter symbol next to the participant ID in the overview table to get to a message screen. The phone number can be choosen from a drop down and the message can be either a pre-defined template or a free text.

Automatization¶

The PHP file called cron.php can be executed on the server and will generate an overview. Different components of the odk_planner can register to use this overview to generate output. Log messages are added to the log file cron. At every script invocation, an email is sent to the notify email address.

Currently, the following actions can be performed autonomously

• Automatically sending autogenerated messages to participants (if ! is added to the template name in the additional configuration).
• Creating lists of missing forms and attaching them to the email (on the days specified in the cron configuration).

Log files¶

The directory log/ contains different log files that can be examined through the web interface (in the admin page). Additionally, an update on any of these logs can be sent to the the notify email address.

• user : logins and login attempts
• cron : log of actions that were performed autonomously
• sms : list of SMS that were sent
• mass-sms : list of patient ID / template title of short message

Utility scripts¶

The script xls2xform.py can be used to translate a .xls form into a .xml XForm from the command line:

python xls2xform form1.xml form2.xml

Labeler¶

Another tool bundled with odk_planner is a Python script called labeler.py that generates labels with QR codes and study id numbers. The configuration specifies which labels should be printed, how to align the different parts of the label (size of QR code, how to split text label into sublabels and where to print them, and what paper is to be used (size of labels).

This tool is based on reportlab. To install it from the Python package index simply type the following command:

$ easy_install reportlab

To generate the labels first start with the example configuration labeler_example.xlsx that is also bundled with odk_planner. Either run the script with the configuration as its only parameter (python labeler.py labeler_example.xlsx) or drag the Excel workbook on the script in Windows explorer. The output will be stored in the output directory. The example configuration should work with labels of type Avery 3666.

The configuration Excel workbook has two sheets:

• page_layout : Contains one string for every label on the sheet of labels, starting the the label on the top left in cell A1.
• label_layout : Specifies the label size, as well as the size of the different parts of the label.

The YYYYY part of the participant IDs on the page_layout sheet will be replaced with the actual id (as specified by idrange on sheet label_layout). It is possible to print labels for multiple participants on a single sheet by specifying YYYY1, YYYY2, etc that will be replaced with consecutive numbers. See the config labeler_example.xlsx that makes use of this feature.

The following drawing shows the different parameters to set the label dimensions via the config file:

Other settings on the label_layout page:

• drawrect : Setting 1 in for this config value will print a rectangle around every label. The idea is to use this, then print a page, then adapt the settings to perfectly match the labels. If you cannot match the label boundaries exactly, try to change the “Zoom” settings in the printer dialog.
• singlepage : Setting this to 1 results in a pdf document for every sheet of labels. Setting this to 0 will generate a single pdf with multiple pages.
• textre : Specifies how to split up the ID string into multiple parts for the label. You can specify (.*) if you want to print the whole id as a single part. Use pythex to construct your own expression.

ODK pusher¶

This directory contains different scripts for uploading XForms in a more or less automated way to an ODK Aggregate instance. Note that these scripts need Python 3 and will not run with an older Python version (due to SSL communications problems when connecting to ODK Aggregate from Python 2).

The python module aggregate can be used to push data to an ODK Aggregate instance. It also provides a command line interface for use by scripts, see:

python3 aggregate.py -h

Xray uploader¶

GUI program in tools/odk_pusher that watches a directory for new images and sends compressed copies wrapped into a simple XForm, with optional user provided data.

Usage¶

Step 1 – Scan Xray films : Proceed as usual, but save images under directory “DesktopTBDAR_CXR” so that xray_uploader will find them. Make sure to name the file with a patient ID : No spaces allowed! No additional zeroes allowed!

Step 2 – Start xray uploader & choose images : The images can be chosen in the left pane (press Ctrl-A to select all). The upload is started by clicking on “upload selected” (or press the Return key). If the left pane is empty, all images from the TBDAR_CXR folder have already been uploaded.

Step 3 – Fill in additional information : Some details have to be filled in before the download can be started (your initials and an optional comment).

Final step – Check upload : In the left panel you should see a “uploaded image XXX” for every file that is uploaded. Also, the files disapper in the left pane (and the program will remember this for the next time).

Troubleshooting : If the left pane shows anything in red, this means there was an error somewhere. For example, when the file names are mis-spelled (below is a screenshot where a zero was accidentally replaced with a small letter “o”). Check internet connection and make sure you saved the images in the right directory.

MS-SQL uploader¶

GUI program in tools/odk_pusher that automatically uploads all new results from a MS-SQL database to a ODK Aggregate server. Since this program is based on ODK pusher, it also needs Python 3 to run. Its single dependency is the pure python library pypyodbc.

The MS-SQL database is polled every couple of seconds and new rows are used to fill in .xml forms and then these are sent to the Aggregate server. Once the server has accepted the incoming form, this is noted in a local .sqlite database to prevent sending the same form multiple times.

Demo Project Description¶

The forms used in the demo project are deliberatly kept as simple as possible, while illustrating most of odk_planner‘s features. The demo study “Tuberculosis Cohort” collects data from tuberculosis patients that present at a health facility, as well as from a group of controls that are not infected with tuberculosis.

The demo project consists of five different forms. All forms are generated using XLSForm.

• CRF1 : This is the eligibility form that records patient details and confirms the study’s inclusion criteria.
• CRF1C : The eligibility form for controls, which is different from the eligibility form for patients.
• CRF2 : This clinical request form is filled at the same day as the eligibility form and assesses some clinical signs and symptoms. The same form applies for patients and controls.
• LRF1 : Although a lab sample is taken at the patient’s first visit to the health facility, the lab form is only expected to be entered into the database after some days necessary to perform all the analysis. Again, the same form applies for patients as well as controls.
• CRFX : If patients have a negative lab result, they are expected to get an X-ray in the following week to complete the diagnostic information.

All patients receive a unique study_id that is used to connect the different forms of the same patient. The same applies for the controls, but both groups can be differentiate by looking at their ID : patients have a number like 80XXX and controls have a number like 83XXX. The laboratory adds a suffix -V01 to this ID so it is possible to commit multiple laboratory forms that for the same patient, while still having a unique identifier for every form.

Initial setup of odk_planner and ODK Aggregate¶

First step: Make sure you (or someone else) correctly installed odk_planner as described in the chapter on installation. To proceed with the tutorial you need to be able to access the login screen:

In the following we will always use http://localhost/odk_planner/?instance=test as our base address. Change the URL accordingly to your setting.

Second step: You also need access to the ODK Aggregate so you can upload the .xml forms for the demo project. You can find these forms in the demo/forms/out subdirectory of the files from the demo project.

Third step: The demo project files also contain some prepared data ready for upload. The data is contained in the files demo/data/*.csv. You can either open this data in your favourite spreadsheet program and then use ODK Collect to upload row by row by filling out the forms, or you can simply start the program demo/data/push.py (needs Python3) to upload all the data automatically.

$ python3 demo/data/push.py
upload data from csv files to Aggregate server? (yn) y
Aggregate server url [http://localhost:8080/ODKAggregate]
username []
password []
successfully posted form CRF1, "80001"
successfully posted form CRF1, "80002"
successfully posted form CRF1, "80003"
successfully posted form CRF1, "80004"
successfully posted form CRF1, "80005"
successfully posted form CRF1, "80006"
successfully posted form CRF1C, "83001"
successfully posted form CRF1C, "83002"
successfully posted form CRF1C, "83003"
successfully posted form CRF1C, "83004"
successfully posted form CRF2, "80001"
successfully posted form CRF2, "80002"
successfully posted form CRF2, "80003"
successfully posted form CRF2, "80004"
successfully posted form CRF2, "80005"
successfully posted form CRF2, "83001"
successfully posted form CRF2, "83002"
successfully posted form CRF2, "83003"
successfully posted form CRF2, "83004"
successfully posted form CRFX, "80001"
successfully posted form CRFX, "80002"
successfully posted form CRFX, "80003"
successfully posted form CRFX, "80004"
successfully posted form CRFX, "80005"
successfully posted form CRFX, "80006"
successfully posted form LRF1, "80001-V01"
successfully posted form LRF1, "80002-V01"
successfully posted form LRF1, "80003-V01"
successfully posted form LRF1, "80004-V01"
successfully posted form LRF1, "80005-V01"
successfully posted form LRF1, "80006-V01"
successfully posted form LRF1, "83001-V01"
successfully posted form LRF1, "83002-V01"
successfully posted form LRF1, "83003-V01"

Setting up the Project¶

Download the files from the demo project and unpack them. Now go to http://localhost/odk_planner/?instance=test and use the username admin and the temporary password from the instance creation.

First thing we will do now is to upload the config file from the demo project files demo/config/config-sample.xls. Open this file with Excel (or LibreOffice) and go to the “users” sheet. You will see the following three users pre-defined:

Enter three good passwords for the three different users, click on the admin link in the top navigation and upload the demo/config/config-sample.xls file.

After having uploaded the config file for the first time, the temporary password will not work anymore, but you can now login using the passwords you defined in the config.xls. You can read more about the user configuration settings, in particular about the rights and the access column.

Uploading the forms¶

Once logged in, you should see error messages complaining that “forms are not found” (in the database) and .xls forms not uploaded. So go to the “forms” tab and upload the forms from the demo project (in the demo/forms directory). After uploading the five forms, you should see something like

If you see an error saying not matched this is because you forgot to upload the forms to ODK Aggregate.

Check out the overview¶

You can now see the overview in all its beauty

The overview highlights the following (hover over a highlighted cell to get a popover that explains why the cell is colored)

• case 80006 is missing CRF2 and this field is highlighted in red because CRF1 was already submitted more than one week ago
• case 80006 has a red bar where its LRF1 is, because the sputum smear is negative; and control 83002 has a red bar where its LRF1 is, because the sputum smear is positive – we’ll go into this problem in a minute

For an exercise, try to download, modify, and upload the config.xls in a way to achieve a bold font for all cases/controls with fever (solution). To solve this exercise, read more about coloring the overview.

Viewing data¶

When you now click on a the link 1/1/14 to view the CRF1 from case 80001 you should see the following

At this point you could – and should – be somewhat worried that patient details, such as name and birthdate can be seen on the web...

Luckily, odk_planner lets you control exactly which user can see which data. On one hand, you can defined arbitrary access groups for the different users (above we have defined the groups default, sensitive, and fieldofficer). On the other hand, you can specify for every datapoint which group(s) are permitted to see that data.

Let’s first have a look at form demo/forms/CRF1.xls:

As you can see, this form already defines the datapoints full_name, birthdate, phone_number1 and phone_number2 as sensitive. This means that only users with the access group sensitive are allowed to see this data. To confirm that this indeed works, first have a look at the data when you’re logged in as admin, then logout and login as secretary. You should now see that the corresponding values are masked:

All values that have no access defined automatically get the access group default. This means that an user like the fieldofficer who is not part of the default access group can only see the datapoints that are specifically permitted by adding his access group to the access column. When you log in with the fieldofficer account you should see all datapoints masked:

This is a good moment to read more about privacy.

Exercise: make the first phone number accessible to the field officer. Make sure that the admin is still able to see the phone number, but not the secretary (solution).

Solutions¶

Overview¶

The file index.php contains all the logic and structured into the following sections (VIM can expand/collapse the corresponding code folds).

1. Pre-initialization, includes : Things common to all instances.
2. Set up instance : Find out form cookies or parameters which instance should be used and set up paths accordingly.
3. Load configuration from config.xls into global $user. See file config.php.
4. Login: Show the login box if the user is not currently logged in and compare username and password to configuration options during login action. Then load user information (access rights etc) into global $user.
5. Connect to database, load forms. The excel forms from the directory instances/XXX/forms are loaded and compared with the local ODK database. See file``odk_form.php``.
6. Actions : Do stuff, such as file upload or download. Note that so far no HTML output has been generated (if the user is logged in).
7. Start HTML output
8. Show the menu, depending on user’s access rights.
9. Display : Generate the main output
   1. show=overview : Show the tabular overview over all data contained in the database. See overview.php.
   2. show=form : Show the content of a form
   3. show=forms : List the contents of the instances/XXX/forms directory.
   4. show=admin : Administrator view with upload/download of config.xls
10. Footer : Show some more information if &test is specified as a URL parameter and the current user has test rights.

The file cron.php finally contains code that can be run without any user interaction and is normally executed from the operating system’s job scheduler.

Globals and Utilities¶

The following globals are noteworthy

• $config : An ExcelConfig object – see file config.php. This object is generated from the file instances/XXX/config/config.xls
• $user : An array that is set to one of the values in $config->users after successful login. With keys rights and access that describe this users access permissions.
• $forms : An OdkDirectory object – see file odk_form.php. This object is generated from the files instances/XXX/forms/*.xls. Every object OdkForm in the dictionary $forms->forms contains information about the form as described in its .xls file and can be used to read the data from the database. See Accessing the Database.
• $show : The name of the current view (see above in Overview).
• $hooks : Plugins use this object in order to install hooks.

And some utility functions

• log_add($name, $message) : Adds a message to the specified log file.
• alert($html, $class) : Displays the html snippet inside an alert box with the given class (success, info, danger, error).
• profile_start($name) and profile_stop($name) : Measure time spent for $name (can be called multiple times, e.g. inside a recursive function). The footer displays the total time spent in every $name (if the &test URL parameter is specified).

Accessing the Database¶

The file odk_form.php provides methods to access data in the ODK database using identifiers from .xls files that were used as input to XLSForm. Please use the file’s API doc for reference.

Developing a Plugin¶

odk_planner comes with a simple plugin called doughnut that adds a page with overview plots of data in the database. This section will walk you step by step through the creation of the doughnut plugin that is the file plugins/doughnut.php. The four steps outlined below build successively on top of each other and introduce every time some new API to give a quick an dirty introduction to the internals described above. Note that for every step the whole source code of the plugin is included. Overwrite the file plugins/doughnut.php with the source listing from the different steps to see what changes and play around with the code.

The idea of this plugin is pretty simple : Specify fields and values in the configuration (sheet doughnut) and let the plugin generate doughnut plots that show the distribution of the field of interest over the whole study population.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

<?php

function doughnut_dump_headers($args) {
    echo '<script src="plugins/doughnut_chart.js"></script>';
}

function doughnut_display($args) {
    global $show;
    if ($show !== 'doughnut') return;
?>
    <div class="row">
        <br><br>
        <div class="span12">
            <center>
                <canvas id="doughnut" style="width:100%"></canvas>
            </center>
            <h4 style="text-align:center">doughnut</h4>
            <script type="text/javascript">
                // Data copied from www.chartjs.org/docs/#doughnut-pie-chart
                new Chart(document.getElementById('doughnut').getContext('2d'))
                    .Doughnut([{ value: 300, color:"#F7464A", highlight: "#FF5A5E", label: "Red" }, { value: 50, color: "#46BFBD", highlight: "#5AD3D1", label: "Green" }, { value: 100, color: "#FDB45C", highlight: "#FFC870", label: "Yellow" }]);
            </script>
        </div>
    </div>
<?php
}

function doughnut_augment_views($args) {
    $views =& $args['views'];
    array_push($views, 'doughnut');
}

$hooks->register('dump_headers', 'doughnut_dump_headers');
$hooks->register('augment_views', 'doughnut_augment_views');
$hooks->register('display', 'doughnut_display');

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

<?php

function doughnut_dump_headers($args) {
    echo '<script src="plugins/doughnut_chart.js"></script>';
}

function doughnut_display($args) {
    global $show, $user;
    if (!in_array('data', $user['rights'])) return;
    if ($show !== 'doughnut') return;
?>
    <div class="row">
        <br><br>
        <div class="span12">
            <center>
                <canvas id="doughnut" style="width:100%"></canvas>
            </center>
            <h4 style="text-align:center">doughnut</h4>
            <script type="text/javascript">
                // Data copied from www.chartjs.org/docs/#doughnut-pie-chart
                new Chart(document.getElementById('doughnut').getContext('2d'))
                    .Doughnut([{ value: 300, color:"#F7464A", highlight: "#FF5A5E", label: "Red" }, { value: 50, color: "#46BFBD", highlight: "#5AD3D1", label: "Green" }, { value: 100, color: "#FDB45C", highlight: "#FFC870", label: "Yellow" }]);
            </script>
        </div>
    </div>
<?php
}

function doughnut_augment_views($args) {
    global $user;
    if (!in_array('data', $user['rights'])) return;
    $views =& $args['views'];
    array_push($views, 'doughnut');
}

$hooks->register('dump_headers', 'doughnut_dump_headers');
$hooks->register('augment_views', 'doughnut_augment_views');
$hooks->register('display', 'doughnut_display');

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75

<?php

function doughnut_dump_headers($args) {
    echo '<script src="plugins/doughnut_chart.js"></script>';
}

function doughnut_render($name, $config) {

    $id = 'doughnut_' . $name;
?>
    <div class="row">
        <br><br>
        <div class="span12">
            <center>
                <canvas id="<?php echo $id; ?>" style="width:100%"></canvas>
            </center>
            <h4 style="text-align:center"><?php echo $name; ?></h4>
            <script type="text/javascript">
                // Data copied from www.chartjs.org/docs/#doughnut-pie-chart
                new Chart(document.getElementById('<?php echo $id; ?>').getContext('2d'))
                    .Doughnut([{ value: 300, color:"#F7464A", highlight: "#FF5A5E", label: "Red" }, { value: 50, color: "#46BFBD", highlight: "#5AD3D1", label: "Green" }, { value: 100, color: "#FDB45C", highlight: "#FFC870", label: "Yellow" }]);
            </script>
        </div>
    </div>
<?php
}

function doughnut_config($config) {
    global $forms;
    // We only expect one part but allow more values for later versions.
    $parts = explode(' ', $config);
    if (count($parts) < 1) return 'expected "FORM\\FIELD ..."';
    $form_field = explode('\\', $parts[0]);
    if (count($form_field) !== 2) return 'expected "FORM\\FIELD ..."';

    // $forms->forms is array that maps formid to OdkForm (see odk_form.php).
    $form = @$forms->forms[$form_field[0]];
    if (!$form) return 'unknown form "' . $form_field[0] . '"';
    // OdkForm::mapping maps form field to table/column (see odk_form.php).
    $table_column = @$form->mapping[$form_field[1]];
    if (!$table_column) return 'unknown field "' . implode('\\', $form_field) . '"';

    return array(
        'table' => $table_column[0],
        'column' => $table_column[1],
    );
}

function doughnut_display($args) {
    global $show, $user, $config;
    if (!in_array('data', $user['rights'])) return;
    if ($show !== 'doughnut') return;
    // Parse doughnut config lines.
    foreach($config->plugins['doughnut'] as $name=>$config_string) {
        $config_or_error = doughnut_config($config_string);
        if (gettype($config_or_error) === 'string') {
            // Function returns string in case of parse error.
            alert('Error config doughnut ' . $name . ' : ' .
                  $config_or_error, 'error');
        } else {
            doughnut_render($name, $config_or_error);
        }
    }
}

function doughnut_augment_views($args) {
    global $user;
    if (!in_array('data', $user['rights'])) return;
    $views =& $args['views'];
    array_push($views, 'doughnut');
}

$hooks->register('dump_headers', 'doughnut_dump_headers');
$hooks->register('augment_views', 'doughnut_augment_views');
$hooks->register('display', 'doughnut_display');

SELECT column AS value, COUNT(column) AS count
FROM table GROUP BY column ORDER BY column

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106

<?php

function doughnut_dump_headers($args) {
    echo '<script src="plugins/doughnut_chart.js"></script>';
}

function doughnut_query($config) {
    $query = 'SELECT $column AS value, COUNT($column) AS count ' .
             'FROM $table GROUP BY $column ORDER BY $column';
    $query = str_replace('$table', $config['table'], $query);
    $query = str_replace('$column', $config['column'], $query);
    return $query;
}

function doughnut_json($curs) {
    $data = array();
    $colors = ['red', 'green', 'blue', 'yellow', 'magenta', 'purple', 'orange'];
    $i = 0;
    while($row = mysql_fetch_assoc($curs)) {
        array_push($data, array(
            'value' => $row['count'],
            'label' => trim($row['value']),
            'color' => $colors[$i++ % count($colors)]
        ));
    }
    return json_encode($data);
}

function doughnut_render($name, $config) {
    global $conn;

    $id = 'doughnut_' . $name;

    $query = doughnut_query($config);
    $curs = mysql_query_($query, $conn);
    if ($curs === FALSE) {
        alert('MySQL error : ' . mysql_error(), 'error');
        return;
    }
    $json = doughnut_json($curs);
?>
    <div class="row">
        <br><br>
        <div class="span12">
            <center>
                <canvas id="<?php echo $id; ?>" style="width:100%"></canvas>
            </center>
            <h4 style="text-align:center"><?php echo $name; ?></h4>
            <script type="text/javascript">
                // See www.chartjs.org/docs/#doughnut-pie-chart
                new Chart(document.getElementById('<?php echo $id; ?>').getContext('2d'))
                    .Doughnut(<?php echo $json; ?>);
            </script>
        </div>
    </div>
<?php
}

function doughnut_config($config) {
    global $forms;
    // We only expect one part but allow more values for later versions.
    $parts = explode(' ', $config);
    if (count($parts) < 1) return 'expected "FORM\\FIELD ..."';
    $form_field = explode('\\', $parts[0]);
    if (count($form_field) !== 2) return 'expected "FORM\\FIELD ..."';

    // $forms->forms is array that maps formid to OdkForm (see odk_form.php).
    $form = @$forms->forms[$form_field[0]];
    if (!$form) return 'unknown form "' . $form_field[0] . '"';
    // OdkForm::mapping maps form field to table/column (see odk_form.php).
    $table_column = @$form->mapping[$form_field[1]];
    if (!$table_column) return 'unknown field "' . implode('\\', $form_field) . '"';

    return array(
        'table' => $table_column[0],
        'column' => $table_column[1],
    );
}

function doughnut_display($args) {
    global $show, $user, $config;
    if (!in_array('data', $user['rights'])) return;
    if ($show !== 'doughnut') return;
    // Parse doughnut config lines.
    foreach($config->plugins['doughnut'] as $name=>$config_string) {
        $config_or_error = doughnut_config($config_string);
        if (gettype($config_or_error) === 'string') {
            // Function returns string in case of parse error.
            alert('Error config doughnut ' . $name . ' : ' .
                  $config_or_error, 'error');
        } else {
            doughnut_render($name, $config_or_error);
        }
    }
}

function doughnut_augment_views($args) {
    global $user;
    if (!in_array('data', $user['rights'])) return;
    $views =& $args['views'];
    array_push($views, 'doughnut');
}

$hooks->register('dump_headers', 'doughnut_dump_headers');
$hooks->register('augment_views', 'doughnut_augment_views');
$hooks->register('display', 'doughnut_display');

Digitization¶

Beware the following : It is extremely easy to copy data once it is digitized. This means that you are probably better off not digitizing sensitive data in the first place, instead of trying to prevent everyone who will handle it to make it impossible to copy the data.

Of course, this puts you in a dilemma, since the whole idea of using ODK is to digitize your data for ease of collection and analysis. One possible way out is to digitze all data apart from identifying datapoints, such as name and addresses, which are rarely used for data analysis, and can be kept seperately in a paper register (or on an encrypted hard drive in the principal investor’s office).

Unfortunately, there are other datapoints that will turn up in your final data analysis but can easily be used to identify subjects. Think of coordinates for example. In these cases some more creativity is needed: you could for example add a random offset to locations so you can still use them to analyze distribution but at the same time the spatial resolution would not be sufficient to identify housings (and map them to an address book).

Database segregation¶

If you decide to include sensitive personal data in your database, then you should work out a database design with multiple databases. You can then store the sensitive data in a separate database. This has the advantage that you can define a different backup policy for the. Separating sensitive data in its own database also allows you to create separate database users (and passwords) that can only see part of the data.

Mind that, although you can also define access restrictions for viewing data in okd_planner, it is not possible to restrict the access of an user that has admin rights. And it is always better to use additional safety guards to protect data.

Admin rights¶

It is important to realize that odk_planner has (read-only) access to the entire database that stores the data for your study. If you use the same ODK Aggregate instance for more than one study, then you can set up multiple instances of odk_planner but users with admin rights are free to define how they want to configure odk_planner (and can, for example, create an overview that contains forms from other studies included in the same database).

Therefore, you should restrict admin rights to as few users as possible, ideally only to one person.

Choosing a good password¶

The access protection relies on good passwords. Without good passwords, it will be easy for strangers (or your users’ acquaintances) to guess a valid login and access all the study data!

Since the passwords are stored in plain text (i.e. users with admin rights who can download the config.xls can see them), and because passwords that are used for many different services are a lot easier to intercept somewhere, you really should use an unique password that is solely used to identify users with odk_planner.

The default password 0dk pa2sw0rd used throughout this documentation is actually a really poor password. A word of wisdom from xkcd

ODK Aggregate database settings¶

The ODK Aggregate database settings are stored in the file ODKAggregate-settings.jar that can be found in the subdirectory WEB-INF/lib of the webapps/ODKAggregate directory (e.g. under debian this directory itself is located at /bar/lib/tomcat6). The .jar file (which has the same file format as a .zip file) contains a file called jdbc.properties that stores the MySQL connection settings.

You can open this file to look up the database connection parameters (in case you have lost the original create_db_and_user.sql that was created during the ODK Aggregate installation), or modify it to use the same ODK Aggregate instance to access a different database (e.g. for testing). The following example is for debian:

$ cd /var/lib/tomcat6/webapps/ODKAggregate/WEB-INF/lib/
$ unzip -e ODKAggregate-settings.jar jdbc.properties
Archive:  ODKAggregate-settings.jar
  inflating: jdbc.properties
$ vim jdbc.properties
$ zip -u ODKAggregate-settings.jar jdbc.properties
updating: jdbc.properties (deflated 31%)
$ /etc/init.d/tomcat6 restart
Stopping Tomcat servlet engine: tomcat6.
Starting Tomcat servlet engine: tomcat6.

If you want to change the jdbc.properties file using Windows, just open the ODKAggregate-settings.jar file with 7-zip (or a similar programme), copy the jdbc.properties file to a local directory, apply the necessary changes and then copy it back into the .jar file.

In order to change the super-user name, the server host name or the server ports for your ODK-Aggregate instance simply open the file security.properties and apply the necessary changes. The file can be found under ODKAggregate-settings.jar as well.