Duplicate concepts in CK

[mahalakshme]

Duplicate concepts form has started to show lot of CK entries. Is it related to CK migration done recently?. Please take a look https://reporting.avniproject.org/question/1536-duplicate-concepts-in-a-form-production

[himeshr]

Root cause

The report sql query did not take into consideration the non_applicable_form_element table in-order to accomodate FormElement over-written in CK to have its own version, whenever it needed a deviation from the OPENCHS Org version.

On using the updated sql query in the report, we see that there are no more duplicate concepts listed.

This same logic is also used in server when returing response to webapp or client, which ensures that the duplicate FormElements are marked as voided and not used.

FormElement.java:

    public boolean isVoided() {
        if (super.isVoided()) {
            return true;
        }
        return getNonApplicable() != null && !getNonApplicable().isVoided();
    }

SQL Before

-- using known_exclusions (
--   select 'JSCS', 'Sickle cell screening', 'Lab test Results', 'HPLC Other result details'
--   union
--   select 'JSCS', 'Sickle cell screening', 'Sickle cell screening history', 'HPLC Other result details'
-- )
-- these are logically cannot be filled in the same flow

select fe.id, o.name "Organisation", f.name "Form", feg.name "Form Element Group", fe.name "Form Element",concept from form f
join form_element_group feg on feg.form_id=f.id
join form_element fe on fe.form_element_group_id=feg.id
join organisation o on o.id=f.organisation_id
join (
    select c.id as concept_id,c.name as concept , f.id as form_id, o.id as org_id,count(*) from form f
    join form_element_group feg on feg.form_id=f.id
    join form_element  fe on fe.form_element_group_id=feg.id
    join organisation o on o.id=f.organisation_id
    join concept c on fe.concept_id=c.id
    where fe.group_id is null and f.is_voided=false and fe.is_voided=false and feg.is_voided=false 
    and fe.id not in (1707, 1713) -- known_exclusions
    and c.name not like 'Placeholder%' and o.id <> 1
    group by 1,2,3,4
    having count(*) > 1) 
x on fe.concept_id = x.concept_id and o.id = x.org_id and f.id = x.form_id
order by 1,2,3;

SQL After

-- using known_exclusions (
--   select 'JSCS', 'Sickle cell screening', 'Lab test Results', 'HPLC Other result details'
--   union
--   select 'JSCS', 'Sickle cell screening', 'Sickle cell screening history', 'HPLC Other result details'
-- )
-- these are logically cannot be filled in the same flow

select fe.id, o.name "Organisation", f.name "Form", feg.name "Form Element Group", fe.name "Form Element",concept from form f
join form_element_group feg on feg.form_id=f.id
join form_element fe on fe.form_element_group_id=feg.id
join organisation o on o.id=f.organisation_id
join (
    select c.id as concept_id,c.name as concept , f.id as form_id, o.id as org_id,count(*) from form f
    join form_element_group feg on feg.form_id=f.id
    join form_element  fe on fe.form_element_group_id=feg.id
    join organisation o on o.id=f.organisation_id
    join concept c on fe.concept_id=c.id
    left join non_applicable_form_element nafe on fe.id = nafe.form_element_id
    where fe.group_id is null and f.is_voided=false and fe.is_voided=false and feg.is_voided=false 
    and fe.id not in (1707, 1713) -- known_exclusions
    and (nafe is null or nafe.is_voided = true)
    and c.name not like 'Placeholder%' and o.id <> 1
    group by 1,2,3,4
    having count(*) > 1) 
x on fe.concept_id = x.concept_id and o.id = x.org_id and f.id = x.form_id
order by 1,2,3;