[Deepak Foundation] Change decision logic for Anemia

gistfile1.txt

set role dfad_trial;


select gender.name                                                 Gender,
       EXTRACT(YEAR FROM AGE(ind.date_of_birth))                   Age,
       enc.observations ->> '0f283bcc-fa4c-4d98-93fd-cbf1729b770d' Hemoglobin,
       enc.observations ->> 'd304f306-deca-4418-a9a5-27b04e083623' "Anaemic status",
       case
           when EXTRACT(YEAR FROM AGE(ind.date_of_birth)) < 12
               AND (enc.observations ->> '0f283bcc-fa4c-4d98-93fd-cbf1729b770d')::numeric >= 11
               AND (enc.observations ->> '0f283bcc-fa4c-4d98-93fd-cbf1729b770d')::numeric < 11.5
               then 'Mild anaemia'
           when (enc.observations ->> '0f283bcc-fa4c-4d98-93fd-cbf1729b770d')::numeric >= 8
               AND (enc.observations ->> '0f283bcc-fa4c-4d98-93fd-cbf1729b770d')::numeric < 11
               then 'Moderate anaemia'
           when (enc.observations ->> '0f283bcc-fa4c-4d98-93fd-cbf1729b770d')::numeric < 8
               then 'Severe anaemia'
           when EXTRACT(YEAR FROM AGE(ind.date_of_birth)) >= 12
               AND EXTRACT(YEAR FROM AGE(ind.date_of_birth)) < 15
               AND (enc.observations ->> '0f283bcc-fa4c-4d98-93fd-cbf1729b770d')::numeric >= 11
               AND (enc.observations ->> '0f283bcc-fa4c-4d98-93fd-cbf1729b770d')::numeric < 12
               then 'Mild anaemia'
           when ind.gender_id = (select id from gender where name = 'Male')
               AND EXTRACT(YEAR FROM AGE(ind.date_of_birth)) >= 15
               AND (enc.observations ->> '0f283bcc-fa4c-4d98-93fd-cbf1729b770d')::numeric >= 11
               AND (enc.observations ->> '0f283bcc-fa4c-4d98-93fd-cbf1729b770d')::numeric < 13
               then 'Mild anaemia'
           when ind.gender_id = (select id from gender where name = 'Female')
               AND EXTRACT(YEAR FROM AGE(ind.date_of_birth)) >= 15
               AND (enc.observations ->> '0f283bcc-fa4c-4d98-93fd-cbf1729b770d')::numeric >= 11
               AND (enc.observations ->> '0f283bcc-fa4c-4d98-93fd-cbf1729b770d')::numeric < 12
               then 'Mild anaemia'
           when enc.observations ->> '0f283bcc-fa4c-4d98-93fd-cbf1729b770d' is not null
               then 'Non anaemic'
           else null
           end                                                     whatToBE
from public.program_encounter enc
         right join public.individual ind on enc.individual_id = ind.id
         join gender on ind.gender_id = gender.id
where ind.is_voided = false
  and enc.is_voided = false
  and ind.organisation_id = (select id from organisation where name = 'Deepak foundation - Adolescent tracking trial');



update program_encounter enc
set observations            = case
                                  when EXTRACT(YEAR FROM AGE(ind.date_of_birth)) < 12
                                      AND (enc.observations ->> '0f283bcc-fa4c-4d98-93fd-cbf1729b770d')::numeric >= 11
                                      AND (enc.observations ->> '0f283bcc-fa4c-4d98-93fd-cbf1729b770d')::numeric < 11.5
                                      then enc.observations ||
                                           '{"d304f306-deca-4418-a9a5-27b04e083623":["f4e6b83a-162c-4571-9379-1fd9a60670b6"]}'
                                  when (enc.observations ->> '0f283bcc-fa4c-4d98-93fd-cbf1729b770d')::numeric >= 8
                                      AND (enc.observations ->> '0f283bcc-fa4c-4d98-93fd-cbf1729b770d')::numeric < 11
                                      then enc.observations ||
                                           '{"d304f306-deca-4418-a9a5-27b04e083623":["830319d4-b616-4253-88ff-00ada2b3ff03"]}'
                                  when (enc.observations ->> '0f283bcc-fa4c-4d98-93fd-cbf1729b770d')::numeric < 8
                                      then enc.observations ||
                                           '{"d304f306-deca-4418-a9a5-27b04e083623":["1f92d18a-2c91-4421-8c41-2cc56f799c6d"]}'
                                  when EXTRACT(YEAR FROM AGE(ind.date_of_birth)) >= 12
                                      AND EXTRACT(YEAR FROM AGE(ind.date_of_birth)) < 15
                                      AND (enc.observations ->> '0f283bcc-fa4c-4d98-93fd-cbf1729b770d')::numeric >= 11
                                      AND (enc.observations ->> '0f283bcc-fa4c-4d98-93fd-cbf1729b770d')::numeric < 12
                                      then enc.observations ||
                                           '{"d304f306-deca-4418-a9a5-27b04e083623":["f4e6b83a-162c-4571-9379-1fd9a60670b6"]}'
                                  when ind.gender_id = (select id from gender where name = 'Male')
                                      AND EXTRACT(YEAR FROM AGE(ind.date_of_birth)) >= 15
                                      AND (enc.observations ->> '0f283bcc-fa4c-4d98-93fd-cbf1729b770d')::numeric >= 11
                                      AND (enc.observations ->> '0f283bcc-fa4c-4d98-93fd-cbf1729b770d')::numeric < 13
                                      then enc.observations ||
                                           '{"d304f306-deca-4418-a9a5-27b04e083623":["f4e6b83a-162c-4571-9379-1fd9a60670b6"]}'
                                  when ind.gender_id = (select id from gender where name = 'Female')
                                      AND EXTRACT(YEAR FROM AGE(ind.date_of_birth)) >= 15
                                      AND (enc.observations ->> '0f283bcc-fa4c-4d98-93fd-cbf1729b770d')::numeric >= 11
                                      AND (enc.observations ->> '0f283bcc-fa4c-4d98-93fd-cbf1729b770d')::numeric < 12
                                      then enc.observations ||
                                           '{"d304f306-deca-4418-a9a5-27b04e083623":["f4e6b83a-162c-4571-9379-1fd9a60670b6"]}'
                                  when enc.observations ->> '0f283bcc-fa4c-4d98-93fd-cbf1729b770d' is not null
                                      then enc.observations ||
                                           '{"d304f306-deca-4418-a9a5-27b04e083623":["e9e15ea9-15b3-4fdd-9a21-e3f06fb4c8bb"]}'
                                  else enc.observations
    end,
    manual_update_history   = 'Change in the decision logic for Anemia',
    last_modified_by_id     = (select id from users where username = 'beulah@dfad_trial'),
    last_modified_date_time = current_timestamp + interval '1 millisecond'
from public.individual ind
where ind.id = enc.individual_id
  and ind.organisation_id = (select id from organisation where name = 'Deepak foundation - Adolescent tracking trial');