Transfers don't affect KPI download calculations

[mbarton]

1. Start from a totally fresh instance
2. Seed a single patient at Kings (s/seed --cohort 7 --cases 1 --full_year --organisations RJZ01)
3. Transfer that patient to Frimley Park
4. Download the KPI spreadsheet

Expected

• The HBT_level sheet only has data for RDU (Frimley Park)
• The ICB_level sheet only has data for NHS Frimley Integrated Care Board

Actual

• The HBT_level sheet only has data for RJZ (Kings)
• The ICB_level sheet only has data for NHS South East London Integrated Care Board

This happens because our KPI model has a link to the lead organisation for a patient but that link is not updated as part of a transfer. The rest of the app is unaffected as it uses the Site bridging model to dynamically calculate the lead organisation at request time.

The Frimley Park dashboard ends up in an odd state:

I'm not sure how much of this is because I only have one case and how much of it comes from this bug.

Thanks to @anjaleesyangbo who helped us remember about the organisation field on KPI as part of the preparations for cohort 6 analysis. This issue does not block that analysis as the individual patient level KPI models are correct (pending #1161) and we will bridge the analysis through the Site model.

[mbarton]

This query returns all the affected patients along with their incorrect current kpi.organisation and what it should be based on the site table:

select
  epilepsy12_registration.cohort,
  epilepsy12_registration.case_id,
  org_by_kpi.ods_code as org_before,
  org_by_site.ods_code as org_after

from epilepsy12_kpi
  inner join epilepsy12_registration
    on epilepsy12_registration.kpi_id = epilepsy12_kpi.id
  
  inner join epilepsy12_site
    on epilepsy12_registration.case_id = epilepsy12_site.case_id

  inner join epilepsy12_organisation org_by_kpi
    on org_by_kpi.id = epilepsy12_kpi.organisation_id
  
  inner join epilepsy12_organisation org_by_site
    on org_by_site.id = epilepsy12_site.organisation_id
  
  where epilepsy12_site.site_is_actively_involved_in_epilepsy_care 
    and epilepsy12_site.site_is_primary_centre_of_epilepsy_care
    and epilepsy12_site.organisation_id != epilepsy12_kpi.organisation_id;

[eatyourpeas]

#1180 fixes this:
Using the same example

Before transfer

After transfer

Just as an aside - The KPIs of the child in transfer but before accepted are still include in the live dashboard for the receiving organisation. I think this is fine, since the dashboard is live, but possibly we need some messaging to say the numbers include children in transfer.

[eatyourpeas]

#1180 has merged but leaving this open pending update of the database in the platform of the mismatches

[eatyourpeas]

In follow up - using the ORM in the shell:

>>> from django.db.models import Q, F
>>> from epilepsy12.models import *   
>>> mismatched_cases = (
...     Case.objects.filter(
...         site__site_is_actively_involved_in_epilepsy_care=True,
...         site__site_is_primary_centre_of_epilepsy_care=True,
...         registration__isnull=False,
...     )
...     .exclude(site__organisation=F("registration__kpi__organisation"))
...     .distinct()
... )
>>> mismatched_cases.count()
0

I am not totally sure my query here is correct but there are apparently no organisation mismatches that I can see

[mbarton]

I can have a look at your query tomorrow but this is the SQL I wrote last week when investigating originally. It shows the affected KPI tables with what they are set to and what they should be (calculated via the Site table).

select
  epilepsy12_registration.case_id as case_id,
  epilepsy12_kpi.id as kpid_id,
  org_by_kpi.id as org_in_kpi_table_id,
  org_by_kpi.ods_code as org_in_kpi_table_ods_code,
  org_by_site.id as org_via_site_table_id,
  org_by_site.ods_code as org_via_site_table_ods_code

  from epilepsy12_kpi

  inner join epilepsy12_registration
    on epilepsy12_registration.kpi_id = epilepsy12_kpi.id
  
  inner join epilepsy12_site
    on epilepsy12_registration.case_id = epilepsy12_site.case_id

  inner join epilepsy12_organisation org_by_kpi
    on org_by_kpi.id = epilepsy12_kpi.organisation_id
  
  inner join epilepsy12_organisation org_by_site
    on org_by_site.id = epilepsy12_site.organisation_id
  
  where epilepsy12_site.site_is_actively_involved_in_epilepsy_care 
    and epilepsy12_site.site_is_primary_centre_of_epilepsy_care
    and epilepsy12_site.organisation_id != epilepsy12_kpi.organisation_id

  order by org_by_kpi.ods_code;

[eatyourpeas]

This is the equivalent (I think) in the ORM:

from django.db.models import OuterRef, Subquery
from epilepsy12.models import Case, Site

# Subquery to get the organisation from the Site table where the conditions are met
site_organisation_subquery = Site.objects.filter(
    case=OuterRef('pk'),
    site_is_actively_involved_in_epilepsy_care=True,
    site_is_primary_centre_of_epilepsy_care=True
).values('organisation')[:1]

# Query to list all Case objects where the organisation in the Site table does not match the organisation in the related KPI
cases_with_mismatched_organisations = Case.objects.filter(
    registration__isnull=False,
    registration__kpi__organisation__isnull=False
    registration__cohort=6,
    registration__audit_progress__registration_complete=True,
    registration__audit_progress__first_paediatric_assessment_complete=True,
    registration__audit_progress__assessment_complete=True,
    registration__audit_progress__epilepsy_context_complete=True,
    registration__audit_progress__multiaxial_diagnosis_complete=True,
    registration__audit_progress__investigations_complete=True,
    registration__audit_progress__management_complete=True,
).exclude(
    registration__kpi__organisation=Subquery(site_organisation_subquery)
)

[mbarton]

@eatyourpeas and I are planning to update the remaining patients manually on Thursday 23rd Jan

[eatyourpeas]

We paired on this and there are no outstanding cases in live where the organisation in the KPI table does not match that in Site.