Non-numeric entry in numeric field

[mahalakshme]

https://avni.freshdesk.com/a/tickets/3360 -

Guess:

Looks like some observation entry for numeric concept type has non-numeric entry - this has happened before and was fixed in avni-client. May be it has happened from webapp - not sure.

Acceptance criteria:

• Fix the data so that ETL continues running
• Identify the rootcause of failure and based on priority and code it touches we can decide if needed to fix as part of 6.1.0.

[mahalakshme]

@himeshr are you working on this? asking since this is in Ready lane.

[himeshr]

Issue was with a single program_encounter, where Concept "Number of calcium tablets consumed since last last visit" had a single space(" ") as the value.
As per sync_telemetry, the sync was last done at '2023-12-18 05:26:17.4510', by the user, but the lastModifiedDateTime was 2023-12-18 08:04:45.925000 +00:00, on the program_encounter.

User used Avni APK '5.1.3' on an 7.0 android version phone.

select * from public.program_encounter where id = 2678218; --//Last modified id => 60; dateTime was 2023-12-18 08:04:45.925000 +00:00

select * from sync_telemetry where user_id = 60 order by last_modified_date_time desc; /*7.0,5.1.3*/ -- Last sync end time is 2023-12-18 05:26:17.451000 +00:00

[himeshr]

Fixed the data and unblocked ETL.

[himeshr]

Issue was not reproducible using avni-webapp.
Therefore, it seems to be the same root-cause as the one mentioned here

[himeshr]

Related SQL commands

    (with individual_pregnancy_anc_home_visit_concept_maps as (SELECT public.hstore((array_agg(c2.uuid)) :: text [], (array_agg(c2.name)) :: text []) AS map
                                                               FROM public.concept
                                                                        join public.concept_answer a on concept.id = a.concept_id
                                                                        join public.concept c2 on a.answer_concept_id = c2.id)


   SELECT entity.program_enrolment_id                                                  "program_enrolment_id",
       entity.id                                                                    "id",
       entity.individual_id                                                         "individual_id",
       entity.earliest_visit_date_time                                              "earliest_visit_date_time",
       entity.encounter_date_time                                                   "encounter_date_time",
       entity.uuid                                                                  "uuid",
       entity.name                                                                  "name",
       entity.address_id                                                            "address_id",
       entity.max_visit_date_time                                                   "max_visit_date_time",
       entity.is_voided                                                             "is_voided",
       entity.encounter_location                                                    "encounter_location",
       entity.legacy_id                                                             "legacy_id",
       entity.cancel_date_time                                                      "cancel_date_time",
       entity.cancel_location                                                       "cancel_location",
       entity.created_by_id                                                         "created_by_id",
       entity.last_modified_by_id                                                   "last_modified_by_id",
       entity.created_date_time                                                     "created_date_time",
       entity.last_modified_date_time                                               "last_modified_date_time",
       entity.organisation_id                                                       "organisation_id"
       ,public.get_coded_string_value(entity.observations-> 'f90bb5df-c7fa-4266-8141-3534f3e47e6c', individual_pregnancy_anc_home_visit_concept_maps.map)::TEXT as "Foetal movements",
public.get_coded_string_value(entity.observations-> '0adc4170-9ebb-4feb-8b81-450fbf9a04dc', individual_pregnancy_anc_home_visit_concept_maps.map)::TEXT as "Pregnancy complications",
public.get_coded_string_value(entity.observations-> '1d3d368a-3180-4bda-bf42-bee1351844f3', individual_pregnancy_anc_home_visit_concept_maps.map)::TEXT as "Registered for institutional delivery",
public.get_coded_string_value(entity.observations-> '74eb771a-f9e8-4b51-a078-3a13e2044b0d', individual_pregnancy_anc_home_visit_concept_maps.map)::TEXT as "Reason for not saving money",
(entity.observations->> 'dda37984-052d-41e2-b255-0cb9a67d580f')::TEXT as "Other reason for resting less than 2 hours",
(entity.observations->> '1a28299c-de6e-4c4d-9afd-1c8129b40c77')::NUMERIC as "Number of FA tablets consumed since last visit",
(entity.observations->> 'ea7cd073-704c-4551-a251-5c656f56bc24')::TEXT as "Reason for not eating yesterday",
public.get_coded_string_value(entity.observations-> '955f355d-f741-4246c-952e-f539296764f8', individual_pregnancy_anc_home_visit_concept_maps.map)::TEXT as "Reason for resting less than 2 hours",
public.get_coded_string_value(entity.observations-> 'c6053dc3-593b-4f24-91f9-3d0e33b4af36', individual_pregnancy_anc_home_visit_concept_maps.map)::TEXT as "FA tablets received from",
public.get_coded_string_value(entity.observations-> 'c210a202-78e4-493f-bcf6-11ed6e79d61c', individual_pregnancy_anc_home_visit_concept_maps.map)::TEXT as "Immunization Counselling (advice)",
public.get_coded_string_value(entity.observations-> '22d263c5-033a-487f-b237-2ebf6c8698e4', individual_pregnancy_anc_home_visit_concept_maps.map)::TEXT as "Coitus/Sex Counselling (advice)",
public.get_coded_string_value(entity.observations-> '1eb2875c-8956-4c50-96dd-d53815397cd9', individual_pregnancy_anc_home_visit_concept_maps.map)::TEXT as "Whether attended last community meetup",
((entity.observations->> '599190b5-1875-48e4-961b-b653177b8aa5')::timestamptz AT time zone 'asia/kolkata')::date as "Date of decrease in foetal movements",
(entity.observations->> 'cd5a83e3-b5f0-4179-8c9e-d00c0af1602a')::TEXT as "Other reason for manual labour",
(entity.observations->> 'c2b21d7d-447a-4f97-9775-199c316921e4')::NUMERIC as "Number of calcium tablets consumed since last last visit",
((entity.observations->> '57f66ff5-e050-4a72-ad03-94d99dad4630')::timestamptz AT time zone 'asia/kolkata')::date as "TT2 Date",
((entity.observations->> 'e638d96b-ad09-4fec-9e01-2e4b1a243c6d')::timestamptz AT time zone 'asia/kolkata')::date as "Date of deworming",
public.get_coded_string_value(entity.observations-> '1307241d-92c2-4660-8ed9-c5da899aa06e', individual_pregnancy_anc_home_visit_concept_maps.map)::TEXT as "Supplementary nutritional therapy (advice)",
public.get_coded_string_value(entity.observations-> '98d5f572-2aff-4973-941e-5459fce82f45', individual_pregnancy_anc_home_visit_concept_maps.map)::TEXT as "Diet Advice Do's",
(entity.observations->> 'dc9d9866-a797-4c3f-b26b-e34d628825d8')::TEXT as "Other pregnancy complaints",
(entity.observations->> '13c1458f-b4fe-442a-9b0b-b5ec6e62da11')::TEXT as "Name of institution",
public.get_coded_string_value(entity.observations-> '12638203-bf5f-45ce-a419-d51bb94ec0e1', individual_pregnancy_anc_home_visit_concept_maps.map)::TEXT as "Services received outside or at Calcutta Kids",
public.get_coded_string_value(entity.observations-> 'bc8a4300-6a51-411f-a81b-198135fdf236', individual_pregnancy_anc_home_visit_concept_maps.map)::TEXT as "Reason for eating less than pre-pregnancy",
((entity.observations->> 'f91604a8-89ac-4a99-a3cb-9edd764c8b0e')::timestamptz AT time zone 'asia/kolkata')::date as "TT1 Date",
public.get_coded_string_value(entity.observations-> 'd5372690-d699-4bd0-ad6e-6d8f0ba559a0', individual_pregnancy_anc_home_visit_concept_maps.map)::TEXT as "Mother referred to",
public.get_coded_string_value(entity.observations-> '24c0eb2b-d6bf-4099-a8d5-0ac2217e98b7', individual_pregnancy_anc_home_visit_concept_maps.map)::TEXT as "Consider a special case",
public.get_coded_string_value(entity.observations-> '0dddfa30-74ee-47a4-b212-1918499f4c92', individual_pregnancy_anc_home_visit_concept_maps.map)::TEXT as "Finance management (advice)",
public.get_coded_string_value(entity.observations-> 'b5639c14-30a2-43d3-b818-21cd0cc0a2ff', individual_pregnancy_anc_home_visit_concept_maps.map)::TEXT as "Illness management (advice)",
public.get_coded_string_value(entity.observations-> 'fd8fcbd8-278f-4417-9696-176ff46c3bcc', individual_pregnancy_anc_home_visit_concept_maps.map)::TEXT as "Government scheme information (advice)",
(entity.observations->> '4362911d-ac02-447c-9812-dfc5271eb499')::NUMERIC as "Money saved so far",
(entity.observations->> '8543f637-c896-4082-8fc9-6fa3f8263c0a')::NUMERIC as "Hours of rest yesterday",
public.get_coded_string_value(entity.observations-> '5a96f634-8107-46f6-8981-c356415ab654', individual_pregnancy_anc_home_visit_concept_maps.map)::TEXT as "Reason for same or more manual labour",
public.get_coded_string_value(entity.observations-> '2a5f655e-3351-42c4-87fd-e3a447eeedaa', individual_pregnancy_anc_home_visit_concept_maps.map)::TEXT as "Food eaten yesterday",
(entity.observations->> 'af4d58ae-7233-4c68-9caa-7ba53a6c5f94')::TEXT as "Other services received outside",
(entity.observations->> '642685db-63dc-4abd-94bf-333bda7a96a8')::TEXT as "Other reason for not saving money",
public.get_coded_string_value(entity.observations-> 'bd925c49-1315-405a-999b-cd6509668281', individual_pregnancy_anc_home_visit_concept_maps.map)::TEXT as "Eating compared to your pre-pregnancy food intake",
public.get_coded_string_value(entity.observations-> '370721b6-7d28-4993-b408-9266e408b5a1', individual_pregnancy_anc_home_visit_concept_maps.map)::TEXT as "Calcium tablets received from",
(entity.observations->> '70cad8f7-722e-4186-aa89-4acc7acb2ed7')::TEXT as "Other reason for eating less than pre-pregnancy",
public.get_coded_string_value(entity.observations-> 'cac85c5d-d0eb-46a7-b30a-0a01836f8c0c', individual_pregnancy_anc_home_visit_concept_maps.map)::TEXT as "Manual labour being done compared to pre-pregnancy",
public.get_coded_string_value(entity.observations-> '65b19077-0b47-4ce7-a608-9efcf0cd8f27', individual_pregnancy_anc_home_visit_concept_maps.map)::TEXT as "Saving money for delivery",
((entity.observations->> 'e1b7ce95-8c73-46fa-8354-19a14f5ca17f')::timestamptz AT time zone 'asia/kolkata')::date as "TT Booster Date",
public.get_coded_string_value(entity.observations-> '3f0de760-cb91-428f-928d-164b9c8b0e6c', individual_pregnancy_anc_home_visit_concept_maps.map)::TEXT as "Diet Advice Don'ts",
public.get_coded_string_value(entity.observations-> 'd070cf73-16f3-4fb7-bf4b-94b95ce753a1', individual_pregnancy_anc_home_visit_concept_maps.map)::TEXT as "Rest and sleep (advice)",
public.get_coded_string_value(entity.observations-> '8b01c973-206a-42a7-9ecb-2471ee51be88', individual_pregnancy_anc_home_visit_concept_maps.map)::TEXT as "Ambulance services information (advice)",
(entity.observations->> '7467e767-b0be-4228-bf8b-911b2a774787')::TEXT as "Other reason for not attending last community meetup"
                                                               FROM public.program_encounter entity
                                                                   cross join individual_pregnancy_anc_home_visit_concept_maps
                                                                   LEFT OUTER JOIN public.program_enrolment programEnrolment
                                                               ON entity.program_enrolment_id = programEnrolment.id
                                                                   LEFT OUTER JOIN public.individual ind on programEnrolment.individual_id = ind.id
                                                                   LEFT OUTER JOIN public.encounter_type et on entity.encounter_type_id = et.id
                                                                   LEFT OUTER JOIN public.subject_type st on st.id = ind.subject_type_id
                                                                   LEFT OUTER JOIN public.program p on p.id = programEnrolment.program_id
                                                               WHERE p.uuid = '076ddb2d-a499-4314-af95-4178553d279b'
                                                                 AND et.uuid = 'a6f8c24e-f56f-456b-aa41-7aada038390c'
                                                                 AND st.uuid = '9f2af1f9-e150-4f8e-aad3-40bb7eb05aa3'
                                                                 AND entity.cancel_date_time isnull
                                                                 and entity.last_modified_date_time > '2023-12-18T07:27:19.704'
                                                                 and entity.last_modified_date_time <= '2023-12-30T11:42:28.865');

select entity.id, (entity.observations->> 'c2b21d7d-447a-4f97-9775-199c316921e4') as "Number of calcium tablets consumed since last last visit"
FROM public.program_encounter entity
                  LEFT OUTER JOIN public.program_enrolment programEnrolment
                                  ON entity.program_enrolment_id = programEnrolment.id
                  LEFT OUTER JOIN public.individual ind on programEnrolment.individual_id = ind.id
                  LEFT OUTER JOIN public.encounter_type et on entity.encounter_type_id = et.id
                  LEFT OUTER JOIN public.subject_type st on st.id = ind.subject_type_id
                  LEFT OUTER JOIN public.program p on p.id = programEnrolment.program_id
WHERE p.uuid = '076ddb2d-a499-4314-af95-4178553d279b'
  AND et.uuid = 'a6f8c24e-f56f-456b-aa41-7aada038390c'
  AND st.uuid = '9f2af1f9-e150-4f8e-aad3-40bb7eb05aa3'
   AND entity.cancel_date_time isnull
                                                                 and entity.last_modified_date_time > '2023-12-18T07:27:19.704'
                                                                 and entity.last_modified_date_time <= '2023-12-29T11:42:28.865';

-- 2678218
-- "Number of calcium tablets consumed since last last visit"

-- https://app.avniproject.org/#/app/subject?uuid=0e5adaeb-b9b3-45b4-81e9-e426a4999cd3
-- https://app.avniproject.org/#/app/subject/editProgramEncounter?uuid=844a9667-839c-4e47-acb1-34a007de8ed4
select * from individual where id = 64206;
select * from public.program_encounter where id = 2678218; --//Last modified id => 60; dateTime was 2023-12-18 08:04:45.925000 +00:00
select * from sync_telemetry where user_id = 60 order by last_modified_date_time desc; /*7.0,5.1.3*/ -- Last sync end time is 2023-12-18 05:26:17.451000 +00:00