DCIS: Data Investigations

[ageryck]

Client reported data issues as documented here. The issue will track investigation measures taken to prove/disapprove reported claims.

• run queries on submitted SQLite extracts
• run comparative queries on the OpenSRP DB
• compare numbers on dashboard
• write a documented findings document - to be shared to the client
• Apply fix patch and share apk

[ageryck]

sample OpenSRP queries to determine the count of children with birth certificates;

SELECT COUNT(*) FROM core.event --limit 10
WHERE json->>'eventType'='Birth Certification'
AND json @> '{"obs":{"formSubmissionField": "birth_cert"}}'='No'
AND json->>'providerId'='betem7'

SELECT COUNT(DISTINCT json->>'baseEntityId') FROM core.event
WHERE json->>'eventType'='Birth Certification'
AND json->>'providerId'='igbofia2'

[ageryck]

sample SQLite queries to determine the number of events on the extract;

SELECT eventType,COUNT(*) FROM event
--WHERE JSON_EXTRACT(json, '$.providerId')='asc2'--'tgmakagni87'
GROUP BY eventType
ORDER BY eventType ASC

[ageryck]

query to retrieve sqlcipher unlock passphrase

SELECT o.Identifier FROM team.practitioner p 
INNER JOIN  team.practitioner_role pr ON p.id=pr.practitioner_id
INNER JOIN team.organization o ON pr.organization_id=o.id
WHERE username='igbofia6'

[ageryck]

a comparison between the event table and visits table (used by the in-app reporting query) indicates a mismatch in some extracts. Need to determine why we may have more number on visits table compared to corresponding events created i.e. for the following providers;

• betem7
• igbofia6

visits table query used;

select count(distinct ec_child.base_entity_id) from ec_child
                     where date(ec_child.dob) >= date('now', '-60 month')
                     and ifnull(ec_child.dod,'') = '' and ifnull(ec_child.date_removed,'') = ''
                     and ec_child.base_entity_id in (
                     	select v.base_entity_id from visits v
                     	inner join visit_details vd on v.visit_id = vd.visit_id
                     	where vd.visit_key = 'birth_cert' and vd.human_readable_details = 'Yes'
                     );

[ageryck]

processing function was catching a null exception for a column pre_processed. The null value consistently appeared on records for wash check events, some records showed empty strings [not null]. The empty records are set to empty after successful processing.
The root cause of null records is wash_check events and so all visits saved after processing a null record are left unprocessed

[ageryck]

Findings after sharing a beta release with a fixed patch are as follows;
<style type="text/css"></style>

	total unprocessed visits before patch	total unprocessed visits after patch	total events before patch	total events after patch
kanawa5	631	0	73	92
kanawa6	611	0	69	93

There is a 100% match on device vs dashboard figures for the two sampled devices

[allan-on]

processing function was catching a null exception for a column pre_processed. The null value consistently appeared on records for wash check events, some records showed empty strings [not null]. The empty records are set to empty after successful processing. The root cause of null records is wash_check events and so all visits saved after processing a null record are left unprocessed

Fixes applied:

When home visits are processed 24 hours after they're recorded, the pre_processed value which is a replica of the visit_json entry, is used to create the Visit Event.
With the JSON being null for WASH CHECK entries in the visits table, the processing was breaking and so the fix was to use a migration to copy the visit_json as the pre_processed value (where it was null) and ensure Wash Check entries include/add the pre_processed JSON values (in the DB).

This fix might be required for other WCARO flavors since the affected code is shared across multiple flavors