Day-2 Message Conversation, SQL Views

Message Conversation

DHIS2 features a mechanism for sending messages for purposes such as user feedback, notifications and general information to users. Messages are grouped into conversations. To interact with message conversations you can send POST and GET request to the messageConversations resource.

Message Conversation API:

 /api/messageConversations.json

● An endpoint for viewing the current logged in user messages.
● The resource we need to interact with when sending and reading messages
● Supports both Object filters and field filters

Example: 
http://103.247.238.86:8081/dhis28/api/messageConversations.json

How to send messages to users and user groups

To send a message, we use post to /api/messageConversations.json with a payload of:

{
"subject":"Hey",
"text":"How are you?",
"users":[{"id":"fOhFTJNFzX2"}],
"userGroups":[{"id":"tdm4F3Xyklc"}],
"organisationUnits":[{"id":"mykF7AaZv9R"}]
}

Result: 
{
    "httpStatus": "Created",
    "httpStatusCode": 201,
    "status": "OK",
    "message": "Message conversation created"
}


See in DHIS2: http://103.247.238.86:8081/dhis28/dhis-web-messaging/index.action

Message Conversation Object filter:

○ ?filter=property-name:operator:value (global)
○ ?type:filter=property-name:operator:value

Message Conversation Field Filtering:

○ ?fields=property-a,property-b[property-ba,property-bb] (global)
○ ?type:fields=property-a,property-b[property-ba,property-bb]

DHIS2 SMS API Integration

SMS Service:

SMS Service is a generic service used for sending/receiving SMS. Any other DHIS2 module can include this service and utilize it to send and receive SMS from users or workers in the field.

Setting up SMS service:

There are few pre-requisites in order to make this service functional. There are two ways to complete these steps. One way is to configure gateway from the GUI in Mobile Configuration Module. The other way is to use SMS Web Api.

Configure SMS Gateway

There are five different types of Gateways supported by SMS Service. SMS can be sent if any one of the gateway is configured. If more than one gateways are present, then they will be used in round-robin fashion for load balancing. There are different parameters for different type of gateway. The Gateway can be configured in GUI in Mobile Configuration Module as shown in the figure.

Configure Generic Http Gateway

Many DHIS2 instances are using GenericHttpGateway to connect to their local gateways. These local gateways provides HTTP APIs for sending SMS. In new GenericHttpGateway it is now possible to configure url parameters provided in their APIs. For example http://gateway.com/sendMessage?username=AA&password=xxxxx&message=testing&msisdn=9999. In this url username, password, message, msisdn are parameters that are required by external gateways. Now in GenericHttp these parameters are configurable which was not possible in previous releases. Making is configurable will help DHIS2 to interact with other gateways which have different url formats.

Gateway Configurations: Below table shows the parameters required for configuring gateway.

SQL Views

SQL views are useful for creating data views which may be more easily constructed with SQL compared combining the multiple objects of the Web API. As an example, lets assume we have been asked to provide a view of all organization units with their names, parent names, organization unit level and name, and the coordinates listed in the database. The view might look something like this:

SELECT ou.name as orgunit, par.name as parent, ou.coordinates, ous.level, oul.name from organisationunit ou
INNER JOIN _orgunitstructure ous ON ou.organisationunitid = ous.organisationunitid
INNER JOIN organisationunit par ON ou.parentid = par.organisationunitid
INNER JOIN orgunitlevel oul ON ous.level = oul.level
WHERE ou.coordinates is not null
ORDER BY oul.level, par.name, ou.name

We will use curl to first execute the view on the DHIS 2 server. This is essentially a materialization process, and ensures that we have the most recent data available through the SQL view when it is retrieved from the server. You can first look up the SQL view from the api/sqlViews resource, then POST using the following command:

curl "https://play.dhis2.org/demo/api/sqlViews/dI68mLkP1wN/execute" -X POST -u admin:district -v

Example # 1: How to create sql view from Postman
SQL: 

SELECT ou.name as orgunit, par.name as parent, ou.coordinates from organisationunit ou
INNER JOIN organisationunit par ON ou.parentid = par.organisationunitid
WHERE ou.coordinates is not null
ORDER BY par.name, ou.name


Step-1: Create the below JSON payload: 
{
    "name": "Test Org- Organization Unit List-Web Apps training",
    "sqlQuery": "SELECT ou.name as orgunit, par.name as parent, ou.coordinates from organisationunit ou INNER JOIN organisationunit par ON ou.parentid = par.organisationunitid WHERE ou.coordinates is not null ORDER BY par.name, ou.name",
    "displayName": "Organization Unit List-Web Apps training"
}

Step-2: Execute it from your server or test it from Postman or curl as: 
URL: http://103.247.238.86:8081/dhis28/api/sqlViews
Method: POST
Authorization: Basic
Body: Insert the above JSON payload in the body of Postman

Step-3: Execute and enjoy the result: 

{
    "httpStatus": "Created",
    "httpStatusCode": 201,
    "status": "OK",
    "response": {
        "responseType": "ObjectReport",
        "uid": "Rv5EhTJeWYq",
        "klass": "org.hisp.dhis.sqlview.SqlView"
    }
}


Example # 2: How to create sql view using CURL

Step-1: Create the below JSON payload and save it as "orgunit.json": 
{
    "name": "Test Org- Organization Unit List-Web Apps training",
    "sqlQuery": "SELECT ou.name as orgunit, par.name as parent, ou.coordinates from organisationunit ou INNER JOIN organisationunit par ON ou.parentid = par.organisationunitid WHERE ou.coordinates is not null ORDER BY par.name, ou.name",
    "displayName": "Organization Unit List-Web Apps training"
}

Step-2: Open your terminal in the same directory where orgunit.json is located and type the below command: 

$ curl -u dhis2appsdev:WebApps@DHIS2 -v "http://...server...../api/sqlViews" -d @orgunit.json -X POST -H "Content-Type:application/json"


Step-3: Execute and enjoy the result: 

{
    "httpStatus": "Created",
    "httpStatusCode": 201,
    "status": "OK",
    "response": {
        "responseType": "ObjectReport",
        "uid": "Rv5EhTJeWYq",
        "klass": "org.hisp.dhis.sqlview.SqlView"
    }
}

Get All SQL Views:

 
Example: 
http://103.247.238.86:8081/dhis28/api/sqlViews

Results: 
{
    "pager": {
        "page": 1,
        "pageCount": 1,
        "total": 29,
        "pageSize": 50
    },
    "sqlViews": [
        {
            "id": "EqdEMRrUmUw",
            "displayName": "COD: Anacod export 2017"
        },
        {
            "id": "fU7XP6fvqRY",
            "displayName": "COD_SOML_Anacod export 2017"
        },
        {
            "id": "G8aBFHyZ6VI",
            "displayName": "mdg4data"
        },
        {
            "id": "zZDq51BA4nH",
            "displayName": "NAM_DE"
        },
        {
            "id": "cz7ma1EHMbJ",
            "displayName": "NAM_DV_onsite-monitoring"
        },
        {
            "id": "Sn3tHXRxpHb",
            "displayName": "OrgUnitCode"
        },
        {
            "id": "kJ2l1NX8WXt",
            "displayName": "OrgunitFilter"
        },
        {
            "id": "Tk9QtyCI4Tv",
            "displayName": "OrgunitFilterName"
        },
        {
            "id": "TA1qSKL8zYG",
            "displayName": "orgunit group member"
        },
        {
            "id": "HafQtonpVEy",
            "displayName": "OrgunitHierarchy"
        },
        {
            "id": "EbZjhdhdakd",
            "displayName": "Orgunitlist"
        },
        {
            "id": "IgnpGusaxsV",
            "displayName": "orgunit_list"
        },
        {
            "id": "r3ruIGts8fR",
            "displayName": "SylhetCheck"
        },
        {
            "id": "TXGSvMKUdXx",
            "displayName": "TB Facility list"
        }...
    ]
}

The next step in the process is the retrieval of the data.The basic structure of the URL is as follows:

http://{server}/api/sqlViews/{id}/data(.csv)

Example: 
http://...server...../api/sqlViews/TXGSvMKUdXx/data

Results: 

{
    "title": "TB Facility list",
    "subtitle": "",
    "headers": [
        {
            "name": "organisationunitid",
            "column": "organisationunitid",
            "type": "java.lang.String",
            "hidden": false,
            "meta": false
        },
        {
            "name": "name",
            "column": "name",
            "type": "java.lang.String",
            "hidden": false,
            "meta": false
        },
        {
            "name": "parentid",
            "column": "parentid",
            "type": "java.lang.String",
            "hidden": false,
            "meta": false
        }....
    ],
    "height": 885,
    "width": 6,
    "rows": [
        [
            "8434",
            "Barisal Chest Clinic, Barisal",
            "2",
            "Barisal Division",
            "HS_27710022000",
            "3"
        ],
        [
            "8421",
            "Barisal Chest Hospital, Barisal",
            "2",
            "Barisal Division",
            "10001976",
            "3"
        ],
        [
            "8397",
            "Sher-e-Bangla Medical College Hospital, Barisal",
            "2",
            "Barisal Division",
            "10001978",
            "3"
        ],
        [
            "10055",
            "Dhaka Shishu Hospital",
            "3",
            "Dhaka Division",
            "",
            "3"
        ]...
}

The {server} parameter should be replaced with your own server. The next part of the URL /api/sqlViews/ should be appended with the specific SQL view identifier. Append either data for XML data or data.csv for comma delimited values. Support response formats are json, xml, csv, xls, html and html+css. As an example, the following command would retrieve XML data for the SQL view defined above.

curl "https://play.dhis2.org/demo/api/sqlViews/dI68mLkP1wN/data.csv" -u admin:district -v

There are three types of SQL views: a. SQL view: Standard SQL views. b. Materialized SQL view: SQL views which are materialized, meaning written to disk. Needs to be updated to reflect changes in underlying tables. Supports criteria to filter result set. c. SQL queries: Plain SQL queries. Support inline variables for customized queries.

The SQL views resource allows you to view and retrieve the result set of SQL views. SQL views are useful for creating data views which may be more easily constructed with SQL compared combining the multiple objects of the Web API


SQl Views API:
 
● To view a specific sql view (GET Method)
	○	http://{server}/api/sqlViews/{id}
● To retrieve data for a specific SQLView (GET Method)
	○	http://{server}/api/sqlViews/{id}/data(.csv,json)
● To execute a specific sql view (POST Method)
	○	http://{server}/api/sqlViews/{id}/execute
How to post a sql view:
http://...server...../api/sqlViews/TXGSvMKUdYy/execute

System and User Settings

 
/api/systemSettings

You can manipulate system settings by interacting with the systemSettings resource. To update the system setting you can post to:

 /api/systemSettings/my-key

With a plain text payload of the value

Example: 

http://103.247.238.86:8081/dhis28/api/systemSettings 

Result: 
{
    "sendMessageScheduled": null,
    "keyDateFormat": "yyyy-MM-dd",
    "googleAnalyticsUA": "",
    "keyRemoteInstanceUsername": "",
    "keySystemMonitoringPassword": "",
    "keyGoogleMapsApiKey": "",
    "keyEmailSender": "no-reply@dhis2.org",
    "keyAnalysisDigitGroupSeparator": "COMMA",
    "ccei-configuraton-json": "{\"parameters\":[{\"key\":\"model-uid\",\"valueType\":\"trackedEntity\",\"value\":\"jsoe1ypx9FC\",\"$$hashKey\":\"object:1719\"},{\"key\":\"equipment-uid\",\"valueType\":\"trackedEntity\",\"value\":\"QAsz8i8dxe4\",\"$$hashKey\":\"object:1720\"},{\"key\":\"program-mapping\",\"valueType\":\"programMapping\",\"programToProgramMapping\":[{\"equipmentProgram\":[{\"UID\":\"SwqLgVQVIFA\",\"name\":\"\"}],\"modelProgram\":[{\"UID\":\"hRxdBTmSUTj\",\"name\":\"Refrigerator Model\"}],\"$$hashKey\":\"object:1737\"}],\"$$hashKey\":\"object:1721\"},{\"key\":\"tracker-associate-attribute-display\",\"valueType\":\"attribute\",\"value\":\"xKB72Ozrm72\",\"$$hashKey\":\"object:1722\"},{\"key\":\"working-status-attribute\",\"valueType\":\"attribute\",\"value\":\"PaLPhugSWzQ\",\"$$hashKey\":\"object:1723\"},{\"key\":\"working-status-data-element\",\"valueType\":\"dataelement\",\"value\":\"FPK42q64JEn\",\"$$hashKey\":\"object:1724\"}]}",
    "dataEntryFormCompleted": null,
    "keyCustomLoginPageLogo": false,
    "keyMapzenSearchApiKey": "search-Se1CFzK",
    "forumIntegration": null,
    "keyUseCustomLogoBanner": true,
    "keyEmailTls": true,
    "omitIndicatorsZeroNumeratorDataMart": false,
    "keyRemoteInstanceUrl": "",
    "keySchedTasks": {
        "0 0 0 * * ?": [
            "analyticsLast3YearsTask",
            "monitoringLastDayTask"
        ],
        "0 0 23 * * ?": [
            "resourceTableTask"
        ]
    },
    "appStoreIndexUrl": "https://s3-eu-west-1.amazonaws.com/dhis2-appstore/appstore.json",
    "keyFlag": "bangladesh",
    "minPasswordLength": 8,
    "analyticsFinancialYearStart": "FINANCIAL_YEAR_JULY",
    "keyApplicationRightFooter": "",
    "keyUseCustomLogoFront": true,
    "flag": null,
    "keySkipDataTypeValidationInAnalyticsTableExport": false,
    "keyCacheAnalyticsDataYearThreshold": 3,
    "scheduleAggregateQueryBuilderTackStrategy": "last12Month",
    "applicationTitlebn": "জাতীয় স্বাস্থ্য তথ্য বাবস্থাপনা পথধতি",
    "appFolderPath": "/home/dhis/apps",
    "startModule": "dhis-web-dashboard-integration",
    "keyAutoSaveDataEntryForm": false,
    "keyScheduledTasks": null,
    "currentStyle": "green/green.css",
    "keyCacheStrategy": "NO_CACHE",
    "keyOpenIdProvider": null,
    "keyDataImportRequireCategoryOptionCombo": false,
    "multiOrganisationUnitForms": false,
    "keyRemoteInstancePassword": "",
    "recaptchaSite": "6LcVwT0UAAAAAAkO_EGPiYOiymIszZUeHfqWIYX5",
    "keyEmailPassword": "",
    "recaptchaSecret": "6LcVwT0UAAAAAAtMWnPoerWwLx_DSwrcEncHCiWu",
    "keyCurrentStyle": "green/green.css",
    "keyDataImportStrictAttributeOptionCombos": false,
    "credentialsExpiryAlert": true,
    "keyCustomTopMenuLogo": false,
    "keyLastMonitoringRun": "2018-05-08T08:20:18.534",
    "keyAnalyticsMaxLimit": 0,
    "keyLockMultipleFailedLogins": false,
    "keyAnalyticsMaintenanceMode": false,
    "lastSuccessfulDataStatistics": "2018-05-09T02:00:06.236",
    "scheduleAggregateQueryBuilder": {
        "aggregateQueryBuilder": "0 0 23 * * ?"
    },
    "keyDatabaseServerCpus": 0,
    "keyDataImportStrictPeriods": false,
    "keyIgnoreAnalyticsApprovalYearThreshold": -1,
    "keyHideUnapprovedDataInAnalytics": false,
    "keySelfRegistrationNoRecaptcha": false,
    "timeSendingMessage": "08:00",
    "applicationTitle": "National Health Information System (DHIS2) - MoHFW",
    "keyAppStoreUrl": "https://play.dhis2.org/appstore/",
    "keyAppStoreApiUrl": "https://play.dhis2.org/appstore/api/apps",
    "aggregationStrategy": null,
    "reportFramework": null,
    "zeroValueSaveMode": null,
    "keySystemMonitoringUrl": "",
    "keyEmailUsername": "hannan",
    "keyAnalysisDisplayProperty": "name",
    "maxPasswordLength": 40,
    "keyMetaDataRepoUrl": "https://raw.githubusercontent.com/dhis2/dhis2-metadata-repo/master/repo/221/index.json",
    "keyApplicationNotification": "Write Your Problems in (HRIS) Support Ticket :- \nhttps://hrm.dghs.gov.bd/supporttickets/create",
    "keyUiLocale": "en",
    "keyLastSuccessfulAnalyticsTablesUpdate": "2018-05-08T00:00:00.000",
    "keyAutoSavetTrackedEntityForm": false,
    "keyCorsWhitelist": [
        .....
    ],
    "keyEmailPort": 587,
    "keyRespectMetaDataStartEndDatesInAnalyticsTableExport": false,
    "keyMessageEmailNotification": true,
    "keyCalendar": "iso8601",
    "keyCacheability": "PUBLIC",
    "keyDataImportRequireAttributeOptionCombo": false,
    "SMS_CONFIG": {},
    "orgUnitGroupSetAggregationLevel": 3,
    "keyScheduledPeriodTypes": [
        "Monthly",
        "Yearly",
        "Quarterly",
        "SixMonthly"
    ],
    "keyLastSuccessfulAnalyticsTablesRuntime": "8 h, 19 m, 10 s",
    "keyStyle": "green/green.css",
    "keyLastSuccessfulResourceTablesUpdate": "2018-05-09T00:00:00.512",
    "factorDeviation": 2,
    "keyVersionEnabled": true,
    "keyAutoSaveCaseEntryForm": false,
    "keyLastSuccessfulScheduledDataSetNotifications": "2018-05-09T02:00:00.430",
    "maxAttempts": null,
    "lockoutTimeframe": null,
    "keyAnalysisRelativePeriod": "LAST_12_MONTHS",
    "keyAccountInvite": false,
    "keyAllowObjectAssignment": true,
    "keyStopMetadataSync": false,
    "appBaseUrl": "http://...server...../dhismohfw/api/apps",
    "keySmsSetting": {
        "gateways": [
            {
                "default": false,
                "uid": "OZA8IS5I1x",
                "name": "generic",
                "messageParameter": "message",
                "recipientParameter": "msisdn",
                "parameters": [
                    {
                        "header": false,
                        "value": "DGHSadmin",
                        "key": "username",
                        "classified": false
                    },
                    {
                        "header": false,
                        "value": "",
                        "key": "password",
                        "classified": true
                    },
                    {
                        "header": false,
                        "value": "1",
                        "key": "apicode",
                        "classified": false
                    },
                    {
                        "header": false,
                        "value": "880",
                        "key": "countrycode",
                        "classified": false
                    },
                    {
                        "header": false,
                        "value": "DHIS-2",
                        "key": "cli",
                        "classified": false
                    },
                    {
                        "header": false,
                        "value": "1",
                        "key": "messagetype",
                        "classified": false
                    },
                    {
                        "header": false,
                        "value": "0",
                        "key": "messageid",
                        "classified": false
                    }
                ],
                "urltemplate": "https://...server....."
            }
        ]
    },
    "keyLastSuccessfulMonitoring": "2018-05-08T08:19:10.109",
    "keyLastSuccessfulDataSynch": "2017-04-24T14:23:00.129",
    "keyApplicationIntro": "Welcome to Ministry of Health & Family Welfare",
    "keyDataImportStrictOrganisationUnits": false,
    "phoneNumberAreaCode": "00880",
    "appStoreUrl": "https://www.dhis2.org/appstore",
    "helpPageLink": "https://hrm.dghs.gov.bd/supporttickets/create",
    "keyDataImportStrictCategoryOptionCombos": false,
    "credentialsExpires": 0,
    "keyAccountRecovery": false,
    "keyCanGrantOwnUserAuthorityGroups": true,
    "keyMessageSmsNotification": true,
    "keyAcceptanceRequiredForApproval": false,
    "keyRequireAddToView": true,
    "keySystemMonitoringUsername": "Linkon",
    "keyOnlyManageWithinUserGroups": false
}

Message Conversation