[JSS Support] Report change

[sachsk]

Support ticket :https://avni.freshdesk.com/a/tickets/3361

Context

Currently we are calculation the grade change on the basis of current encounter and last encounter, but client asked to change the logic to first encounter and current encounter.

So add the "wfa grade", "wfa z-score", "wfa status" of first encounter and update the condition accordingly.

Tech approach

Add the rank in last_monthGrade CTE and join the CTE with main query by adding ( and Row_number=1)condition.

report link :

• [x] https://reporting.avniproject.org/question/1897-children-moved-to-lower-wfa-grade?year=2023&month=12
• [x] https://reporting.avniproject.org/question/1900-children-moved-to-upper-wfa-grade?year=2023&month=11

[vedfordev]

@nupoorkhandelwal I tried with views you suggested but still taking more than 30 minutes. moving back to support ready and unassigned me.

[vedfordev]

query for first report :

WITH concepts AS (
    SELECT hstore((array_agg(concept.uuid))::text[], (array_agg(concept.name))::text[]) AS map
    FROM concept
),
     monthly_grade as (
         select pe.program_enrolment_id                                        as enl_id,
               pe."Weight for age Status" wfaStatus,
                pe."Weight for age Grade" wfaGrade,
                pe."Weight for age z-score" wfazscore,
                pe.encounter_date_time                                         as encounter_date,
                age(pe.encounter_date_time::date, p.enrolment_date_time::date) as timeSinceEnrolment
         from jss.individual_phulwari_growth_monitoring pe
                  cross join concepts
                  join program_enrolment p on pe.program_enrolment_id = p.id
         where pe.encounter_date_time notnull
           and pe.is_voided = false
     ),
     last_monthGrade as (
         select pe.program_enrolment_id                                           as enl_id,
                pe.encounter_date_time                                            as encounter_date,
                pe."Weight for age Status" wfaStatus,
                pe."Weight for age Grade" wfaGrade,
                pe."Weight for age z-score" wfazscore,
                extract(month from (pe.encounter_date_time + interval '1 month')) as a,
                extract(year from (pe.encounter_date_time + interval '1 month'))  as b,
                row_number() over (partition by  program_enrolment_id order by encounter_date_time asc ) rank
         from jss.individual_phulwari_growth_monitoring pe
                  cross join concepts
         where pe.encounter_date_time notnull
           and pe.is_voided = false
     )
        ,
     data as (
         select distinct catchment.name                                                          as "Catchment",
                         address_level.title                                                     as "Village",
                         grp.first_name                                                          as "Phulwari",
                         concat(i.first_name, ' ', i.last_name)                                  as "Full Name",
                         i.date_of_birth                                                         as "DOB",
                         extract(years from (age(i.date_of_birth)))                              as "Age",
                         gender.name                                                             as "Gender",
                         coalesce(i.observations ->> '9e6983b8-06ef-4648-b360-6684100b1be1', '') as "Father's Name",
                         monthly_grade.encounter_date                                            as "encounter date",
                         monthly_grade.wfazscore                                                 as "WfA z score",
                         monthly_grade.wfaStatus                                                 as "WfA Status",
                         monthly_grade.wfaGrade                                                  as "Current WfA Grade",
                         last_monthGrade.wfazscore                                               as "Last Month WfA z score",
                         last_monthGrade.wfaStatus                                               as "Last Month WfA Status",

                         last_monthGrade.wfaGrade                                                as "Last Month WfA Grade",
                         -- last_monthGrade.encounter_date                                          as "last month date",
                         pe.enrolment_date_time                                                  as "Enrolment Date",
                         concat(extract(day from (monthly_grade.timeSinceEnrolment)), ' Days,',
                                extract(month from (monthly_grade.timeSinceEnrolment)), ' Months,',
                                extract(year from (monthly_grade.timeSinceEnrolment)),
                                ' Years')                                                        as "Time since Enrolment"
         from public.individual i
                  cross join concepts
                  join program_enrolment pe on i.id = pe.individual_id
                  join address_level on i.address_id = address_level.id
                  join catchment_address_mapping ON address_level.id = catchment_address_mapping.addresslevel_id
                  join catchment ON catchment_address_mapping.catchment_id = catchment.id
                  join gender on i.gender_id = gender.id
                  left outer join monthly_grade on pe.id = monthly_grade.enl_id
                  left outer join last_monthGrade on pe.id = last_monthGrade.enl_id and rank = 1
                  join group_subject gs on gs.member_subject_id = i.id
                  join public.individual grp on grp.id = gs.group_subject_id
         where extract(year from monthly_grade.encounter_date) = 2023
           and extract (month from monthly_grade.encounter_date) = 12
           and last_monthGrade.a = 12
           and last_monthGrade.b = 2023
           and monthly_grade.wfaGrade
             < last_monthGrade.wfaGrade
           and catchment.name <> 'JSS Master Catchment'
         )
select row_number() over (order by "Full Name") as "Sr.No.", *
from data
;

[vedfordev]

@nupoorkhandelwal

As per card #87

I moved report to etl. Please review once.