We are trying to sync event data to DHIS using an automated program and we want to ensure that the data is getting synced correctly. We would like to use some queries in the DHIS db to make sure that the counts of events synced are matching with the data we have. Currently, we are checking the programstageinstance table for this. One example query we are using to get the count of all events in HTS program stage is as follows :
select count(*) from programstageinstance pi inner join programstage ps on pi.programstageid = ps.programstageid inner join organisationunit o on pi.organisationunitid = o.organisationunitid where o.uid = 'KP8boYIVyI7' and ps.name = 'HTS' and pi.eventdatavalues -> 'RQXv5WqAQGX' is not null;
But this query is not returning the expected results. Is this the correct way to get the count of event data having value for the element with id RQXv5WqAQGX ? We were unable to find proper documentation for DHIS database model. Any help would be much appreciated.
I think these are the main things that must be included in the request or in the query but maybe it’s not necessary to mention the period when querying directly from the DB?