PowerShell Script to Auto-Generate Custom Forms in DHIS2
Problem
- There are three types of forms available in the Data Entry app in DHIS2
- Basic
- Section
- Custom
- Custom Forms allow the greatest flexibilty as the name in implies, in that they can be customized in a WSYSWIG CKEditor or imported as HTML/CSS code
- The CKEditor is unfortunately rather tedious to use, especially for larger forms, so we should automate this process
Solution
I wrote a PowerShell script which will take in a CSV template file, used as an import template for the dataSet itself in DHIS2, and generate a CSS/HTML table that can then be copied and pasted into the CKEditor in DHIS2.
You will need the CSV template file, as the HTML table’s headers come from the description column as well as the order of said headers. Furthermore, this CSV template is handy as it is in the format needed to import metadata in DHIS2, thus automating the creation of the dataSets themselves.
The PowerShell script has six parameters:
- The UID of the dataSet for whom the HTML table should be generated
- The gender’s represented in the dataSet / HTML table
- The path to the CSV template explained above
- The path to output HTML file
- The DHIS2 server’s address
- And finally the API token which you can generate in DHIS2 under your user account menu as an admin
Here is the script and the CSV template file:
<#
.SYNOPSIS
Generates an HTML table to create a Custom Form in DHIS2 (https://dhis2.org/), the open-source public health data tracking system. Requires the input of a CSV template file, a copy of which can be found here: https://grump-it.pro/blog/wp-content/uploads/2024/06/PMTCT_PEP_Data_Element_Import.csv
.EXAMPLE
Create-DHIS2HtmlTable -dataSet "ULsn5Vb8Q8c" -demographic "M and F" -pathToCSVTemplate "C:\Temp\PathToTemplateCSV.csv" -pathToHTMLFile "C:\Temp\PathToOutputHTML.html" -serverAddress "http://192.168.1.10" -apiToken "d2p_HXcMH2GYmSfb1S5OOkxwYq8bag4O2dgiXzHfezX8czke1vV69J"
.EXAMPLE
Create-DHIS2HtmlTable -dataSet "ULsn5Vb8Q8c" -demographic "Females"" -pathToCSVTemplate "C:\Temp\PathToTemplateCSV.csv" -pathToHTMLFile "C:\Temp\PathToOutputHTML.html" -serverAddress "http://192.168.1.10" -apiToken "d2p_HXcMH2GYmSfb1S5OOkxwYq8bag4O2dgiXzHfezX8czke1vV69J"
.INPUTS
[String]$dataSet - The UID of the DHIS2 dataset for whom an HTML table should be generated that will serve as a Custom Form
[String]$demographic - The demographics being measured by the DHIS2 dataset (m, f, or m and f will be recognized)
[String]$pathToCSVTemplate - Path to the CSV Template file which is read to create the names and order of the headers in the HTML table
[String]$pathToHTMLFile - Path to the HTML file to be output
[String]$serverAddress - The http address of the DHIS2 server
[String]$apiToken - #api tokens come from the user account menu -> generate personal access code (remember that these expire every 30 days!!!!)
.OUTPUTS
An .html file with one or more tables depending on the number of demographics chosen
#>
Function Create-DHIS2HtmlTable {
[CmdletBinding()]
Param(
[Parameter(Position= 0, Mandatory= $true, HelpMessage= 'Please provide an UID for a DHIS2 Dataset to be read from the API (i.e. "ULsn5Vb8Q8c")')]
[String]$dataSet,
[Parameter(Position= 1, Mandatory= $true, HelpMessage= 'Please provide the demographics to be record by the dataset (F or M or F and M or F&M or in the case of babies/infants just infants or I):')]
[String]$demographic,
[Parameter(Position= 2, Mandatory= $true, HelpMessage= 'Please input the path to the CSV template file used to create the dataSet in DHIS2')]
[String]$pathToCSVTemplate,
[Parameter(Position= 3, Mandatory= $true, HelpMessage= 'Please input the path to the empty HTML file where the table and HTML will be outputted/generated')]
[String]$pathToHTMLFile,
[Parameter(Position= 4, Mandatory= $true, HelpMessage= 'Please input address of the server, e.g. http://192.168.1.10')]
[String]$serverAddress,
[Parameter(Position= 5, Mandatory= $true, HelpMessage= 'Please input the Api Token with admin rights generated from user account menu -> generate personal access code in DHIS2')]
[String]$apiToken
)
Process
{
Try
{
#MAKE AN API CALL TO GET THE DATASET JSON ENTERED ABOVE
$dataSetURI = $serverAddress+"/api/42/dataSets.json?filter=id:eq:"+$dataSet+"&fields=:all"
$authorization = "ApiToken "+$apiToken
$dataSetsOutput = Invoke-WebRequest -Headers @{ "Authorization" = $authorization} -Uri $dataSetURI
}
Catch
{
Write-Warning "API call failed: Is the dataSet UID correct? Or has the Personal Access Token in DHIS2 expired? Is the server running properly? Exiting script..."
Break
}
#CHECK THAT F OR M OR BABIES OR INFANTS WERE ENTERED CORRECTLY FOR THE demographic
$Males = $null
$Females = $null
$Babies = $null
If(($demographic -imatch "F" -or $demographic -imatch "\bfemale\b" -or $demographic -imatch "\bfemales\b") -and ($demographic -inotmatch "Inf"))
{
$Females = $true
}
If(($demographic -imatch "M" -or $demographic -imatch "\bmale\b" -or $demographic -imatch "\bmales\b"))
{
$Males = $true
}
If($demographic -imatch "Bab" -or $demographic -imatch "Inf" -or $demographic -imatch "b" -or $demographic -imatch "i")
{
$Babies = $true
}
If($demographic -inotmatch "F" -and $demographic -inotmatch "M" -and $demographic -inotmatch "B")
{
Write-Warning "Neither adult males, nor adult females, nor infants or babies selected. Please try again with M, F, or B. Exiting..."
break
}
ElseIf(($Males -eq $true -and $Babies -eq $true) -or ($Females -eq $true -and $Babies -eq $true))
{
Write-Warning "Detected a mix of adults and babies/infants. Please do not mix M, F, with B. Exiting..."
break
}
ElseIf($null -eq $Males -and $null -eq $Females -and $null -eq $Babies)
{
Write-Warning "Neither adult males, nor adult females, nor infants or babies selected. Please try again with M, F, or B. Exiting..."
break
}
#TRY THE PATH TO THE TEMPLATE CSV FILE TO MAKE SURE IT EXISTS AND IF SO IMPORT IT
Try
{
$templateCSV = Import-CSV -Path $pathToCSVTemplate -Delimiter "," -Encoding UTF8
}
Catch
{
Write-Warning "Template CSV File not found: is the path correct? Exiting script..."
Break
}
#TRY THE PATH TO THE TEMPLATE HTML FILE TO MAKE SURE IT EXISTS AND IF SO IMPORT IT
Try
{
Test-Path -Path $pathToHTMLTemplate | Out-Null
Clear-Content -Path $pathToHTMLTemplate
}
Catch
{
Write-Warning "Template HTML file not found: is the path correct? Exiting script..."
Break
}
#DETERMINE THE demographicS BEING MEASURED BY THE HTML TABLE(S) THUS DETERMINING HOW MANY TABLES TO GENERATE
$tableDemographic = @()
$tableDemographicSingular = @()
#A COUNTER FOR TRACKING THE NUMBER OF DEMOGRAPHICS AND THUS THE NUMBER OF TABLES TO GENERATE
$tableDemographicsCount = 0
If($Males -eq $true -and $Females -eq $true) #Both males and females
{
$tableDemographic += "Males"
$tableDemographicSingular += "Male"
$tableDemographic += "Females"
$tableDemographicSingular += "Female"
}
ElseIf($Males -ne $true -and $Females -eq $true) #Females only
{
$tableDemographic += "Females"
$tableDemographicSingular += "Female"
}
ElseIf($Males -eq $true -and $Females -ne $true) #Males only
{
$tableDemographic += "Males"
$tableDemographicSingular += "Male"
}
ElseIf ($Babies -eq $true)
{
$tableDemographic += "Infants"
$tableDemographicSingular += "Infant"
}
#CONVERT THE JSON TO A PSOBJECT
$convertedDataSetsOutput = (ConvertFrom-Json -InputObject $dataSetsOutput.Content).dataSets
#GET ALL THE IDS OF THE DATAELEMENTS FROM THE ABOVE DATASET
$dataElementsFromDataSet = $convertedDataSetsOutput.dataSetElements.dataElement
$allDataElementURI = "$serverAddress/api/42/dataElements?&fields=:all"
$allDataElementsOutput = Invoke-WebRequest -Headers @{ "Authorization" = "ApiToken d2p_HXcMH2GYmSfb1S5OOkxwYq8bag4O2dgiXzHfezX8czke1vV58J"} -Uri $allDataElementURI
$convertedAllDataElements = (ConvertFrom-Json -InputObject $allDataElementsOutput.Content).dataElements
#MAKE AN EMPTY ARRY OF DATAELEMENTS AND FILL IT BY COMPARING AN ARRAY OF ALL DATAELEMENTS AND THE DATAELEMENT IDS FROM THE DATASET ABOVE
$allUsedDataElements = @()
ForEach($convertedAllDataElement in $convertedAllDataElements)
{
ForEach($dataElementFromDataSet in $dataElementsFromDataSet)
{
If($convertedAllDataElement.id -eq $dataElementFromDataSet.id)
{
$allUsedDataElements += $convertedAllDataElement
}
}
}
#GET THE JSON CATEGORYCOMBO ID FROM THE TEMPLATE CSV FILE THEN CALL THE API FOR THAT CATEGORYCOMBO
$categoryComboID = $templateCSV."Category combination UID"[0]
$categoryComboURI = "$serverAddress/api/42/categoryCombos?filter=id:eq:"+$categoryComboID+"&fields=:all"
$categoryComboOutput = Invoke-WebRequest -Headers @{ "Authorization" = "ApiToken d2p_HXcMH2GYmSfb1S5OOkxwYq8bag4O2dgiXzHfezX8czke1vV58J"} -Uri $categoryComboURI
$convertedcategoryComboOutput = ConvertFrom-Json -InputObject $categoryComboOutput.Content
#GET ALL THE CATEGORYOPTIONCOMBOS IDS THAT ARE USED IN THE ABOVE CATEGORYCOMBO
$usedCategoryOptionCombos = $convertedcategoryComboOutput.categoryCombos.categoryOptionCombos
#GET ALL THE CATEGORYCOMBOOPTIONS THAT ARE USED IN THE ABOVE CATEGORYCOMBO IN AN ARRAY
$allCategoryComboURI = "$serverAddress/api/42/categoryOptionCombos?fields=:all"
$allCategoryComboOptions = Invoke-WebRequest -Headers @{ "Authorization" = "ApiToken d2p_HXcMH2GYmSfb1S5OOkxwYq8bag4O2dgiXzHfezX8czke1vV58J"} -Uri $allCategoryComboURI
$convertedcategoryComboOptions = (ConvertFrom-Json -InputObject $allCategoryComboOptions).categoryOptionCombos
$allUsedCategoryOptionCombos = @()
ForEach($convertedcategoryComboOption in $convertedcategoryComboOptions)
{
ForEach($usedCategoryOptionCombo in $usedCategoryOptionCombos)
{
If($convertedcategoryComboOption.id -eq $usedCategoryOptionCombo.id)
{
$allUsedCategoryOptionCombos += $convertedcategoryComboOption
}
}
}
#GET allUsedCategoryOptionCombos AND TRIM OUT THE AGE RANGES THEN SORT THEM PROPERLY IGNORING NON-NUMERIC CHARACTERS
$sortedAgeRanges = @()
ForEach($allUsedCategoryOptionCombo in $allUsedCategoryOptionCombos)
{
#IF YOU ARE WORKING WITH A DATASET THAT HAS THE CATEGORY COMBO OF AGE+SEX GET THE AGE RANGES BY SPLITTING ON THE COMMA (AKA 5-9, Female BECOMES "5-9")
If($allUsedCategoryOptionCombo.name -imatch "Female")
{
$sortedAgeRanges += $allUsedCategoryOptionCombo.name.Split(",")[0]
}
#ELSE IF YOU ARE WORKING WITH INFANTS LOOK FOR WEEKS AND DAYS AND USE THESE AGE RANGES INSTEAD
ElseIf($allUsedCategoryOptionCombo.name -imatch "Weeks" -or $allUsedCategoryOptionCombo.name -imatch "Days")
{
$sortedAgeRanges += $allUsedCategoryOptionCombo.name
}
}
#GOT HELP FROM STACKOVERFLOW: https://stackoverflow.com/questions/78646740/how-to-sort-an-array-of-age-ranges-with-both-numbers-and-characters
$sortedAgeRanges = $sortedAgeRanges | Sort-Object {$_-notlike'<*'},{($_-replace '^.*?(\d+).*$','$1') -as [int]}
#CREATE THE HTML TABLE HEADERS SECTION AND APPEND IT TO THE HTML TEMPLATE FILE
$rowSpan = $sortedAgeRanges.count + 1
#BEGIN CREATING THE HTML TEMPLATE FILE
#START WITH THE HTML HEADER AND CSS
$HTMLCSSBlock = @"
<meta content="text/html; charset=utf-8" http-equiv="Content-Type" />
<title></title>
<style type="text/css">
.table_header {
background-color: #0066CC;
font-family: Arial, Helvetica, sans-serif;
color: #FFFFFF;
text-align:center
}
.column_header {
color: #FFFFFF;
background-color: #000066;
font-family: Arial, Helvetica, sans-serif;
text-align:center
}
.column_header_text {
font-size: 24px !important;
webkit-transform: rotate(270deg); /* Chrome, Opera 15+, Safari 3.1+ */
ms-transform: rotate(270deg); /* IE 9 */
transform: rotate(270deg); /* Firefox 16+, IE 10+, Opera */
}
.header_text {
font-size: 10px !important;
}
.cells {
text-align: center;
font-family: Arial, Helvetica, sans-serif;
font-size: small;
color: #000000;
vertical-align: middle;
}
</style>
"@
#APPEND THE CSS HEADER TO THE HTML TEMPLATE FILE
$HTMLCSSBlock | Out-File -Encoding utf8 -Append -FilePath $pathToHTMLTemplate
#GET THE DESCRIPTION COLUMN FROM THE CSV TEMPLATE FILE
$arrayOfColumnHeaders = $templateCSV.Description #Indicator Descriptions
#LOOP THROUGH THE NUMBER OF demographicS TO DETERMINE HOW MANY TABLES TO GENERATE
Foreach($tableDemographic in $tableDemographic)
{
If($tableDemographic -imatch "\bfemales\b" -or $tableDemographic -imatch "\bmales\b")
{
#CREATE AN ORDERED LIST OF ALL USED CATEGORYOPTIONCOMBOS BY COMPARING THEM WITH THE ORDER IN THE CSV TABLE
$orderedCategoryComboOptionIDs = @()
ForEach($sortedAgeRange in $sortedAgeRanges)
{
$categoryComboOptionName = $sortedAgeRange+", "+$tableDemographicSingular[$tableDemographicsCount]
ForEach($allUsedCategoryOptionCombo in $allUsedCategoryOptionCombos)
{
If($allUsedCategoryOptionCombo.name -eq $categoryComboOptionName)
{
$orderedCategoryComboOptionIDs += $allUsedCategoryOptionCombo.id
}
}
}
}
ElseIf($tableDemographic -imatch "\binfants\b")
{
#CREATE AN ORDERED LIST OF ALL USED CATEGORYOPTIONCOMBOS BY COMPARING THEM WITH THE ORDER IN THE CSV TABLE
$orderedCategoryComboOptionIDs = @()
ForEach($sortedAgeRange in $sortedAgeRanges)
{
ForEach($allUsedCategoryOptionCombo in $allUsedCategoryOptionCombos)
{
If($allUsedCategoryOptionCombo.name -eq $sortedAgeRange)
{
$orderedCategoryComboOptionIDs += $allUsedCategoryOptionCombo.id
}
}
}
}
#CREATE AN ORDERED LIST OF ALL USED DATAELEMENTS BY COMPARING THEM WITH THE ORDER IN THE CSV TABLE
$orderedDataElementIDs = @()
$orderedDateElementShortNames = @()
ForEach($arrayOfColumnHeader in $arrayOfColumnHeaders)
{
ForEach($allUsedDataElement in $allUsedDataElements)
{
#$allUsedDataElement.displayDescription
If($allUsedDataElement.displayDescription -eq $arrayOfColumnHeader.Trim())
{
#$arrayOfColumnHeader
#$allUsedDataElement.displayDescription
$orderedDataElementIDs += $allUsedDataElement.id
$orderedDateElementShortNames += $allUsedDataElement.shortName
}
}
}
#NOW CREATE THE CATEGORYCOMBOOPTIONIDS-DATAELEMENTID PAIRS IN ORDER
$dateElementCategoryComboOptionPairs = @()
ForEach($orderedCategoryComboOptionID in $orderedCategoryComboOptionIDs)
{
ForEach($orderedDataElementID in $orderedDataElementIDs)
{
$dateElementCategoryComboOptionPairs += $orderedDataElementID + "-" + $orderedCategoryComboOptionID
}
}
#BEGING WITH THE TABLE COLUMN HEADER REPRESENTING THE demographicS
$HTMLTemplateHeaderBlock1 = @"
<table style="width: 100%; table-layout: fixed">
<tbody>
<tr>
<td class="column_header" rowspan="$rowSpan">
<p class="column_header_text">$tableDemographic</p>
</td>
<td> </td>
"@
#APPEND THE FIRST HEADER BLOCK TO THE HTML TEMPLATE FILE
$HTMLTemplateHeaderBlock1 | Out-File -Encoding utf8 -Append -FilePath $pathToHTMLTemplate
#LOOP THROUGH THE ARRAY OF COLUMN HEADERS AND INSERT THEM INTO HTML BLOCKS THEN APPEND THEM TO THE HTML TEMPLATE FILE
ForEach($arrayOfColumnHeader in $arrayOfColumnHeaders)
{
$HTMLTemplateHeaderBlock2 = @"
<td class="table_header">
<p class="header_text">$arrayOfColumnHeader</p>
</td>
"@
$HTMLTemplateHeaderBlock2 | Out-File -Encoding utf8 -Append -FilePath $pathToHTMLTemplate
}
#DONT FORGET CLOSE YOUR HTML TABLE ROW
$HTMLTemplateHeaderBlock3 = @"
</tr>
"@
$HTMLTemplateHeaderBlock3 | Out-File -Encoding utf8 -Append -FilePath $pathToHTMLTemplate
#COUNTER TO KEEP TRACK OF ROW NUMBER FOR INSERTION OF $dateElementCategoryComboOptionPairs
$countOfRows = 0
#NOW CREATE THE BODY OF THE HTML TABLE STARTING WITH THE HEADER ROW
ForEach($sortedAgeRange in $sortedAgeRanges)
{
#MAKE <1 HTML COMPLIANT
If($sortedAgeRange -match "<")
{
$sortedAgeRange = $sortedAgeRange.Replace("<","<")
}
$HTMLTemplateBodyBlock1 = @"
<tr>
<td class="table_header">
<p class="header_text">$sortedAgeRange</p>
</td>
"@
$HTMLTemplateBodyBlock1 | Out-File -Encoding utf8 -Append -FilePath $pathToHTMLTemplate
#NOW GENERATE THE TABLE ROWS BY COMBINING $orderedDateElementShortName $sortedAgeRange $tableDemographicSingular
#WHILE ITERATING THROUGH THE $dateElementCategoryComboOptionPairs USING THE countOfRows VARIABLE FROM ABOVE
ForEach($orderedDateElementShortName in $orderedDateElementShortNames)
{
If($tableDemographic -imatch "\bfemales\b" -or $tableDemographic -imatch "\bmales\b")
{
$ValuesToInsert = $orderedDateElementShortName+" "+$sortedAgeRange+", "+$tableDemographicSingular[$tableDemographicsCount]
}
ElseIf($tableDemographic -imatch "\binfants\b")
{
$ValuesToInsert = $orderedDateElementShortName+" "+$sortedAgeRange
}
$dateElementCategoryComboOptionPairsCount = $dateElementCategoryComboOptionPairs[$countOfRows]
$generatedRow = @"
<td class="cells"><input id="$dateElementCategoryComboOptionPairsCount-val" name="entryfield" style="width:70%;" title="$ValuesToInsert" value="[ $ValuesToInsert ]" /></td>
"@
$generatedRow | Out-File -Encoding utf8 -Append -FilePath $pathToHTMLTemplate
$countOfRows++
}
}
#ITERATE THE TABLE COUNTER
$tableDemographicsCount++
#CLOSE THE TABLE HTML TAGS
$HTMLTemplateENDBlock = @"
</tr>
</tbody>
</table>
"@
$HTMLTemplateENDBlock | Out-File -Encoding utf8 -Append -FilePath $pathToHTMLTemplate
}
}
}
Create-DHIS2HtmlTable -dataSet "pyjYrZ2vO1O" -demographic "Babies" `
-pathToCSVTemplate "C:\Users\me\Desktop\Work\CSVs\PMTCT_BT_Data_Element_Import.csv" `
-pathToHTMLFile "C:\Users\me\Desktop\Work\CSVs\PMTCT_BT_Data_Element_Import.html" `
-serverAddress "http://192.168.122.1/" -apiToken "d2p_HXcMH2GYmSfb1S5OOkxwYq8bag4O2dgiXzHfezX8czke1vVxxJ"