Skip to main content

SQL script for transferring TB patient data

Executive Summary​

The aim of this project was to enable patients in Uganda to access their TB medication at any clinic across the country. This required a SQL script to transfer medical events to the correct organizational unit within a DHIS2 instance, ensuring correct tracking of a TB treatment.

Walk-In Refill Transfer Script​

The script is divided into two main sections, each with several parts. It deals with transferring eligible events to a matching tracked entity instance (TEI) and handling events set for transfer where a matching TEI is not found.

Section Zero: Function Declarations​

  • Function send_message: This function is defined to send messages to specified recipients. It involves creating a temporary table, looping through the user group members, and inserting relevant message data into various tables.

Section One: Transfer of Eligible Events to TEI​

  1. Transfer of Event Data Values: The script transfers event data values from the TB-refill event program to treatment and lab program stages of the TB-refill tracker program. It consists of several sub-parts:

    • Transfer of Treatment: Inserts treatment data into the programstageinstance table.

    • Transfer of Lab: Inserts lab data into the programstageinstance table.

    • Status Update Query: Updates the status of events based on whether lab results are available.

    • Lab Result Transfer: Transfers lab results for events where treatment has already transferred.

    • Update TEI Ownership on Permanent Transfer: Updates the ownership of the tracked entity instance on permanent transfer.

  2. Section Two: Flagging Events for Review: This section handles cases where a matching TEI is not found. It flags events by setting their transfer status to one of the specified values:

    • Check Name: Flags events with matching unit TB number but no matching full name.

    • No Match: Flags events with no matching unit TB number and no matching full name.

    • Not Yet Transferred TB Number Not Found: Flags events with matching full name but not matching unit TB number.

Detailed breakdown​

Here's a more detailed breakdown of the main sections:

Section Zero: Function Declarations​

  • send_message Function:

    • Parameters: recipient_orgunitid, message_messagetype, message_messagesubject, message_messagetext.

    • Functionality: Creates and sends messages to recipients.

    • Tables involved: temptransfermessage, messageconversation, message, usermessage, messageconversation_usermessages, etc.

Section One: Transfer of Eligible Events to TEI​

  1. Transfer of Treatment and Lab: Uses a Common Table Expression (CTE) named transfer to gather data to be inserted into treatment and lab stages.

    • Tables involved: programstageinstance, trackedentityattribute, trackedentityattributevalue, trackedentityinstance, programinstance, program, etc.

    • Conditions: Only events with specific statuses and values are selected for transfer.

    • Message Notifications: Sends messages to both target and source organizations.

  2. Status Update Query:

    • Tables involved: programstageinstance, trackedentityattribute, etc.

    • Functionality: Updates the event status as 'completed' if lab results exist; else sets to 'active'.

  3. Lab Result Transfer:

    • Tables involved: programstageinstance, trackedentityattribute, etc.

    • Functionality: Transfers lab results for events with status 'active'.

  4. Update TEI Ownership on Permanent Transfer:

    • Tables involved: trackedentityprogramowner, programstageinstance, trackedentityinstance, etc.

    • Functionality: Updates the ownership of TEI on permanent transfer.

Section Two: Flagging Events for Review​

  • Conditions and Updates: Contains three update queries that set the transfer status of events based on different conditions.

    • Tables involved: programstageinstance, trackedentityattribute, trackedentityattributevalue, etc.

    • Status Values: 'check name', 'no match', 'not yet transferred tb no not found'.

Script​

The SQL script developed for the walk-in refill project is publicly available and can be found on our GitHub repository:

https://github.com/dev-otta/walkin-refill

Should you have any questions or need further assistance with the script, please do not hesitate to get in touch with us