[IHMP] Move a report to ETL

[nupoorkhandelwal]

Existing report link

https://reporting.avniproject.org/question/535-encounter-type-attendance-and-cancellation-report

Support ticket

https://avni.freshdesk.com/a/tickets/3459

Context

Earlier we had worked on migrating IHMP reports to ETL, but had not moved this particular report as it uses all the program encounters and it was working fine earlier. But seems over time it has become non-responsive. We will have to move to move it to ETL so that client can use it.

The old report still work for a very small date filters

Tech Approach

with agg_encounter1_data as (
    select * from encounter1_ETL_Table
    union all
    select * from encounter1_ETL_Table
),
     agg_encounter2_data as (
         ..................
     )
..........,
complete_agg_data as (
    select '' as program_name,
           address_id,
           addresslevel_name,
           '' encounter_type_name,
           id,
           encounter_date_time,
           max_visit_date_time,
           cancel_date_time,
           "Visit cancel reason"

           from agg_encounter1_data
    where {{filters will come here}}

     union all

     select ..... from agg_encounter2_data
     where {{filters will come here}}
)

Final select query;

[Gojo-Taqi]

Link to the report - https://reporting.avniproject.org/question/2952-encounter-type-attendance-and-cancellation-report-under-testing?start_date=2023-01-01&end_date=2024-01-05&village=Anandpur&slum=mhasobavasti

[Dinesh2019]

• [ ] We have the count mismatch

New Report :

Old Report :

[Gojo-Taqi]

@Dinesh2019 I used the following script to QA, you can verify the same.

with needs_assesment_enc as (
select 
    enc.individual_id           ind_id,
    enc.id,
    enc.program_enrolment_id    enl_id,
    null                        "Visit cancel reason",
    enc.encounter_date_time ,
    enc.cancel_date_time ,
    enc.max_visit_date_time ,
    'Monthly needs assessment'                     encounter_type,
    enc.address_id
    from ihmp.individual_eligible_couple_needs_assessment enc 
    where enc.is_voided is false
     and enc.earliest_visit_date_time between '01-01-2023' and '02-10-2024'
union all
    select 
    enc.individual_id           ind_id,
    enc.id,
    enc.program_enrolment_id    enl_id,
    enc."Visit cancel reason",
    enc.encounter_date_time ,
    enc.cancel_date_time ,
    enc.max_visit_date_time ,
    'Monthly needs assessment'                     encounter_type,
    enc.address_id
    from ihmp.individual_eligible_couple_needs_assessment_cancel enc 
    where enc.is_voided is false
     and enc.earliest_visit_date_time between '01-01-2023' and '02-10-2024'
), eligible_couple_program as (
    select 
    i.id                        ind_id,
    enl.id                      enl_id,
    i.address_id                add_id
    from ihmp.individual i
    inner join ihmp.individual_eligible_couple enl on enl.individual_id = i.id
    where i.is_voided is false
    and enl.is_voided is false
    and enl.program_exit_date_time is null
)
select 
count(distinct enc.id)                              "Scheduled",
count(*) filter (where enc.encounter_date_time::date between '01-01-2023' and '02-10-2024')     "Completed",
count(*) filter (where enc.encounter_date_time isnull and enc.max_visit_date_time < '02-10-2024' and enc.cancel_date_time is null )         "Overdue",
count(*) filter (where enc.cancel_date_time notnull)                                                        "Cancelled",
count(*) filter (where enc.cancel_date_time notnull and enc."Visit cancel reason" = 'Absent' )              "Absent",
count(*) filter (where enc.cancel_date_time notnull and enc."Visit cancel reason" = 'Other' )               "Other",
count(*) filter (where enc.cancel_date_time notnull and enc."Visit cancel reason" = 'Away from village' )   "Away from village"
from needs_assesment_enc enc
join eligible_couple_program enl on enc.enl_id = enl.enl_id
join ihmp.address on address.id = enl.add_id
join virtual_catchment_address_mapping_table cam on cam.addresslevel_id = address.id
join catchment on catchment.id = cam.catchment_id 
where 1=1 
and catchment."name" = 'Akhadwada 1';

[Gojo-Taqi]

@Dinesh2019 you can maybe get this script reviewed by nupoor as well to be double sure.