[Support]Move JSS phulwari Dashboard to ETL

[sachsk]

Total 9 reports in dashboard.

• [x] Total number of registered children:- 0.5 hr
• [x] Total number of enrolled children:- 0.5 hr
• [x] Total number of faltering children:- 1hr
• [x] WFA Grade wise phulwari children:- 1hr
• [x] Total number of children exited:- 0.5hr
• [x] Total number of due visits:- 1hr
• [x] List of grade 3 children:- 1hr
• [x] List of Faltering Children: 1hr

Testing 1hr Total dev estimate = 8hr

[BEULAHEVANJALIN]

TEST QUERIES:

• Total number of registered children

  select count(distinct individual.id)
  from individual
       join public.address_level address on individual.address_id = address.id
       join catchment_address_mapping cathment_address
            on cathment_address.addresslevel_id = address.id
       join catchment c on cathment_address.catchment_id = c.id
  where individual.is_voided = false; -- 3494

• Total number of enrolled children

  select count(distinct individual.id)
  from individual
  join program_enrolment on individual.id = program_enrolment.individual_id
       join public.address_level address on individual.address_id = address.id
       join catchment_address_mapping cathment_address
            on cathment_address.addresslevel_id = address.id
       join catchment c on cathment_address.catchment_id = c.id
  where individual.is_voided = false;

• Total number of children exited

  select count(distinct individual.id)
  from individual
       join program_enrolment on individual.id = program_enrolment.individual_id
       join public.address_level address on individual.address_id = address.id
       join catchment_address_mapping cathment_address
            on cathment_address.addresslevel_id = address.id
       join catchment c on cathment_address.catchment_id = c.id
  where individual.is_voided = false
  and program_exit_date_time is not null; -- 1715

[BEULAHEVANJALIN]

On-hold!. Unable to find any observations like "WFA Grade", "Growth Faltering Status" in JSS org. These concepts not used in any forms and unable to find in decision rules as well. And not in ETL table of course. But i could see some observations for these in DB. What could be the reason. Not sure if these concepts got deleted.

[BEULAHEVANJALIN]

• Total number of faltering children
• List of Faltering Children

select count(distinct individual.id)
from individual
join program_encounter on individual.id = program_encounter.individual_id
where program_encounter.observations -> 'a5067bcc-d333-4c80-a0f2-d61df9d210d3' = '["04bb1773-c353-44a1-a68c-9b448e07ff70"]'
and individual.is_voided = false; --1280

[BEULAHEVANJALIN]

• Total number of due visits

  select count(distinct enc.id)
  from program_encounter enc
       join public.individual on enc.individual_id = individual.id
       join public.program_enrolment on individual.id = program_enrolment.individual_id
  where individual.is_voided = false
  and enc.name is not null
  and enc.is_voided = false
  and enc.cancel_date_time is null
  and enc.encounter_date_time is null;

[BEULAHEVANJALIN]

• WFA Grade wise phulwari children
• List of grade 3 children

select count(distinct program_encounter.individual_id)
       filter (where (program_encounter.observations ->> '10d6ef42-2db4-4af9-951d-75b8942690a1')::numeric =
                     1) as "Grade 1",
       count(distinct program_encounter.individual_id)
       filter (where (program_encounter.observations ->> '10d6ef42-2db4-4af9-951d-75b8942690a1')::numeric =
                     2) as "Grade 2",
       count(distinct program_encounter.individual_id)
       filter (where (program_encounter.observations ->> '10d6ef42-2db4-4af9-951d-75b8942690a1')::numeric =
                     3) as "Grade 3"
from individual
         join program_enrolment
              on program_enrolment.program_id = (select id from program where name = 'Phulwari')
                  and individual.id = program_enrolment.individual_id
         join program_encounter on program_enrolment.id = program_encounter.program_enrolment_id
         join address_level address on individual.address_id = address.id
         join catchment_address_mapping catchment_mapping on address.id = catchment_mapping.addresslevel_id
         join catchment on catchment_mapping.catchment_id = catchment.id
where individual.is_voided = false
  and program_enrolment.program_exit_date_time is null
  and program_encounter.encounter_date_time is not null
  and catchment.name <> 'JSS Master Catchment'; -- 2002,1513,749

[BEULAHEVANJALIN]

Link to dashboard : https://reporting.avniproject.org/dashboard/193-jss-phulwari-dashboard-new