[CK Support] Migrate Child Due Visit List to ETL

[nupoorkhandelwal]

Context

Reference Support ticket - https://avni.freshdesk.com/a/tickets/3271

Currently, this report - https://reporting.avniproject.org/question/207-child-visit-due-list is taking longer time to load. In order to improve the performance need to update it to use ETL table available.

Tech Approach

1. Update the new report to use ETL table and save it with as a new report - [DO NOT USE ETL] Child Visit Due List
2. Couple of columns are not available in ETL table, for now we can put them in comment
3. Add same set of filters
4. Once QA is done, replace the original report with the new report.

[vedfordev]

Notes:

Report Link

https://reporting.avniproject.org/question/2843-do-not-use-etl-child-visit-due-list

Points

@sachsk Need to conform, we see that in old report we see row related to "Doctor Visit Followup at Home". which is not related to child program. Currently I not included it in new etl report.

[vinayvenu]

@sachsk ping

[vedfordev]

Dev Test Scenario :

1. In some data we find out that schedule date is diffrent in old and new report. To check used below query and findout old report has some problem.

set role calcutta_kids;

select *
from encounter_type where is_voided = false and name = 'Child Home Visit';
-- 33

select *
from program_encounter where encounter_type_id = 33 and individual_id = 891601 and encounter_date_time isnull ;

select *
from program_enrolment where id = 189979;

2. For missing data of individual 1335874, new report doesn't have value. To check that used below query and find out that old report shows visit which program was exited.

set role calcutta_kids;

select *
from encounter_type where is_voided = false and name = 'Child Home Visit';
-- 33

select *
from program_encounter where encounter_type_id = 33 and individual_id = 1335874 and encounter_date_time isnull ;

select *
from calcutta_kids.individual_child_child_home_visit where individual_id = 1335874 and encounter_date_time isnull ;

select *
from program_enrolment where individual_id = 1335874;

[vedfordev]

currently facing issue in Metabase .Raised ticket : https://avni.freshdesk.com/a/tickets/3354

[vedfordev]

moved report to prerelease : https://reporting.avniproject.org/question/2867-do-not-use-etl-child-visit-due-list-prerelease

[nupoorkhandelwal]

@vedfordev please remove commented code from the sql.

[vedfordev]

@nupoorkhandelwal @sachsk moving card to support ready and unassigned me

1. some part are commented because we have to add decision concept.
2. filter work is decide by tech lead
3. old report has some problem mentioned some scenario in above card.

[Dinesh2019]

• [x] When we cancel the visit the cancel date is not updated on the cancel row in the report.

• [x] Voided check needs to be handled the individual Test child din01 Jan0201 is voided still shows in the report but voided the db

  

• [x] When applying the filter getting the error message

  

• [x] For age group filter we want the customer to know we have to give the option as a or b instead of number may be we can have the drop down

[Gojo-Taqi]

@Dinesh2019 added A,B,C,D as options in age group filter.

[Dinesh2019]

Report showing error

[Gojo-Taqi]

Machi read the error message. It says the date filter is a required parameter. Unless you don't give a date, it won't run.

[Gojo-Taqi]

I am not able to add an encounter_name filter in this report. Need to pair on this with someone, to move the card forward. Hence moving the card in QA failed.

[Gojo-Taqi]

After Nupoor's help, the issue has been fixed. Moving it to QA ready.

[Dinesh2019]

Created the data for visit child pnc but data is not reflected in the metabase report

Individual name : Testdin Feb0212

Screenshot from metabase:

Screenshot for app :

[Gojo-Taqi]

Since the encounter is completed, the individuals details are not coming in the report. If the encounter is scheduled and not completed, only those encounters will come in the report. Moving this back to QA ready.