14.3.23. crate_anon.crateweb.consent.lookup_rio

crate_anon/crateweb/consent/lookup_rio.py

Copyright (C) 2015, University of Cambridge, Department of Psychiatry. Created by Rudolf Cardinal (rnc1001@cam.ac.uk).

This file is part of CRATE.

CRATE is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.

CRATE is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with CRATE. If not, see <https://www.gnu.org/licenses/>.

Functions to look up patient details from various versions of a Servelec RiO clinical database.

crate_anon.crateweb.consent.lookup_rio.gen_opt_out_pids_mpids_rio_cpft_datamart(source_db: str) → Generator[Tuple[str, str], None, None][source]

Generates PIDs/MPIDs from all patients opting out, from a RiO database that is the version in the CPFT data warehouse.

Parameters:

source_db – the type of the source database; see crate_anon.crateweb.config.constants.ClinicalDatabaseType

Yields:

tuple – rio_number, nhs_number for each patient opting out; both are in string format

crate_anon.crateweb.consent.lookup_rio.gen_opt_out_pids_mpids_rio_generic(source_db: str, raw_rio: bool = False, cpft_datamart: bool = False) → Generator[Tuple[str, str], None, None][source]

Generates PIDs/MPIDs from all patients opting out, from a RiO database of some sort.

Parameters:
Yields:

tuple – rio_number, nhs_number for each patient opting out; both are in string format

crate_anon.crateweb.consent.lookup_rio.gen_opt_out_pids_mpids_rio_raw(source_db: str) → Generator[Tuple[str, str], None, None][source]

Generates PIDs/MPIDs from all patients opting out, from a raw RiO database.

Parameters:

source_db – the type of the source database; see crate_anon.crateweb.config.constants.ClinicalDatabaseType

Yields:

tuple – rio_number, nhs_number for each patient opting out; both are in string format

Returns the latest consent mode for a patient from the copy of RiO in the CPFT data warehouse.

Parameters:
Returns:

a crate_anon.crateweb.consent.models.ConsentMode, or None

Returns the latest consent mode for a patient, from some style of RiO database.

Parameters:

  • nhs_number – NHS number

  • source_db – the type of the source database; see crate_anon.crateweb.config.constants.ClinicalDatabaseType

  • decisions – list of human-readable decisions; will be modified

  • raw_rio – is the source database a raw copy of RiO?

  • cpft_datamart – is the source database the version from the CPFT data warehouse?

Returns:

a crate_anon.crateweb.consent.models.ConsentMode, or None

Shared function as very similar for the various copies of RiO data.

In raw RiO at CPFT, the traffic-light table is UserAssessConsentrd. This is processed regularly into the CPFT Data Warehouse, so that contains very fresh data and is a good choice.

For CPFT’s custom consent mode, built into RiO v6, the table copy in the Data Warehouse is:

SELECT
    [ClientID]  -- VARCHAR(15) NOT NULL; RiO number as text
    ,[AssessmentDate]  -- DATETIME
    ,[ReferralNumber]  -- NVARCHAR(20); a small integer as text
    ,[ResearchContact]  -- NVARCHAR(20); 'RED', 'YELLOW', 'GREEN'
    ,[OptOut]
        -- BIT; 1 for opt out; NULL (or potentially 0?) for not
        -- opted out
    ,[OptOutFromMedicalResearch_AfterDetailsRemoved]
        -- VARCHAR(1); 'Y' for opt out; 9 for not opted out
        -- identical information to OptOut; OptOut is the simpler
    ,[PersonActingonBehalf_of_Patient]
        -- NTEXT, e.g. 'Mr Smith' or NULL
    ,[PersonActingonBehalf_of_Patient_Relation]
        -- NTEXT, e.g. 'Husband' or NULL or 'N/A' or 'n/a'
    ,[Personactingonbehalf_address]
        -- NTEXT, e.g. an address or NULL or 'N/A' or 'n/a'
    ,[WhoMakesDecisionFor_Patient]
        -- NVARCHAR(40), e.g. one of:
        --  '16 or over, has capacity'
        --  '16 or over, lacks capacity'
        --  'Under 16, “Gillick competent”'
            -- note left/right double quotes
        --  'Under 16, parent/guardian consent'
FROM [CPFT_DATAMART].[dbo].[ConsentToResearch]
    -- NB this is on a different CPFT server; see databases.txt

Note also: the CPFT_DATAMART database does not provide patient- identifiable information, except PatientOverview_RiO:

SELECT
    [NHSNumber]     -- VARCHAR(15); NHS number as text
    ,[ClientID]     -- VARCHAR(15) NOT NULL; RiO number as text
    ,[PatientName]  -- VARCHAR(202); e.g. 'Smith, John'
    ,[Surname]      -- VARCHAR(100); e.g. 'Smith'
    ,[DOB]          -- DATETIME; time part is zero
    ,[TeamStartDate]    -- DATETIME
    ,[TeamName]         -- NVARCHAR(50)
    ,[ReferralNumber]   -- INT NOT NULL; a small integer
    ,[LastAttendedApptDate_WithTeam]    -- DATE
    ,[FutureAppts_WithTeam]     -- DATE; just one despite the name
    ,[HCPs]
        -- NVARCHAR(MAX); semicolon-delimited list; e.g.
        -- '; Alice Smith'
        -- '; Alice Smith; Bob Jones'
        -- '; Unknown Consultant'
FROM [CPFT_DATAMART].[dbo].[PatientOverviewRiO]

In raw RiO at CPFT, the traffic-light table is UserAssessConsentrd.

SELECT
    [ClientID]  -- VARCHAR(15); RiO number as text
    ,[AssessmentDate]  -- DATETIME
    ,[system_ValidationData]  -- NTEXT; XML style
    ,[ResearchContact]  -- NVARCHAR(20); 'RED', 'YELLOW', 'GREEN', NULL
    ,[useemail]  -- BIT (1, 0, NULL)
    ,[optout]  -- BIT
    ,[capname]  -- NTEXT
    ,[capaddress]  -- NTEXT
    ,[caprelation]  -- NTEXT
    ,[capacity]  -- VARCHAR(20); e.g. 'a', 'b'
    ,[type12_NoteID]  -- INT NOT NULL; small integer
    ,[type12_OriginalNoteID]  -- INT; usually NULL
    ,[type12_DeletedDate]  -- DATETIME; NULL if not deleted
    ,[type12_UpdatedBy]  -- NVARCHAR(20); username
    ,[type12_UpdatedDate]  -- DATETIME
    ,[formref]  -- NVARCHAR(20); typically a small integer as text

    -- If it's been preprocessed, also these:
    ,[crate_pk]         -- in CRATE preprocessed version only; BIGINT
    ,[crate_rio_number] -- in CRATE preprocessed version only; BIGINT
FROM [dbo].[UserAssessconsentrd]

The NHS number table is:

SELECT
    [ClientID]  -- VARCHAR(15); RiO number as text
    ,[NNN]  -- CHAR(10); NHS number as text
    -- and lots more
FROM [dbo].ClientIndex]

Returns the latest consent mode for a patient from a raw copy of RiO.

Parameters:
Returns:

a crate_anon.crateweb.consent.models.ConsentMode, or None

crate_anon.crateweb.consent.lookup_rio.lookup_cpft_rio_crate_preprocessed(lookup: PatientLookup, decisions: List[str], secret_decisions: List[str]) → None[source]

Look up patient details from a CRATE-preprocessed RiO database.

Parameters:

  • lookup – a crate_anon.crateweb.consent.models.PatientLookup

  • decisions – list of human-readable decisions; will be modified

  • secret_decisions – list of human-readable decisions containing secret (identifiable) information; will be modified

Here, we use the version of RiO preprocessed by the CRATE preprocessor. This is almost identical to the RCEP version, saving us some thought and lots of repetition of complex JOIN code to deal with the raw RiO database.

However, the CRATE preprocessor does this with views. We would need to index the underlying tables; however, the CRATE processor has also done this for us for the lookup tables, so we don’t need so many.

USE my_database_name;

CREATE INDEX _idx_cdd_nhs ON ClientIndex (NNN);  -- already in RiO source

CREATE INDEX _idx_cnh_id ON ClientName (ClientID);  -- already in RiO source  # noqa
CREATE INDEX _idx_cnh_eff ON ClientName (EffectiveDate);  -- ignored
CREATE INDEX _idx_cnh_end ON ClientName (EndDate);  -- ignored

CREATE INDEX _idx_cah_id ON ClientAddress (ClientID);  -- already in RiO source as part of composite index  # noqa
CREATE INDEX _idx_cah_from ON ClientAddress (FromDate);  -- ignored
CREATE INDEX _idx_cah_to ON ClientAddress (ToDate);  -- ignored

CREATE INDEX _idx_cch_id ON ClientTelecom (ClientID);  -- already in RiO source as part of composite index  # noqa

CREATE INDEX _idx_cgh_id ON ClientHealthCareProvider (ClientID);  -- already in RiO source  # noqa
CREATE INDEX _idx_cgh_from ON ClientHealthCareProvider (FromDate);  -- ignored  # noqa
CREATE INDEX _idx_cgh_to ON ClientHealthCareProvider (ToDate);  -- ignored

CREATE INDEX _idx_cc_id ON CPACareCoordinator (ClientID);  -- preprocessor adds this  # noqa
CREATE INDEX _idx_cc_start ON CPACareCoordinator (StartDate);  -- ignored
CREATE INDEX _idx_cc_end ON CPACareCoordinator (EndDate);  -- ignored

CREATE INDEX _idx_ref_id ON AmsReferral (ClientID);  -- already in RiO source as part of composite index  # noqa
CREATE INDEX _idx_ref_recv ON AmsReferral (ReferralReceivedDate);  -- ignored  # noqa
CREATE INDEX _idx_ref_removal ON AmsReferral (RemovalDateTime);  -- ignored

CREATE INDEX _idx_rsh_id ON AmsReferralAllocation (ClientID);  -- already in RiO source as part of composite index  # noqa
CREATE INDEX _idx_rsh_start ON AmsReferralAllocation (StartDate);  -- ignored
CREATE INDEX _idx_rsh_end ON AmsReferralAllocation (EndDate);  -- ignored

CREATE INDEX _idx_rth_id ON AmsReferralTeam (ClientID);  -- already in RiO source as part of composite index  # noqa
CREATE INDEX _idx_rth_start ON AmsReferralTeam (StartDate);  -- ignored
CREATE INDEX _idx_rth_end ON AmsReferralTeam (EndDate);  -- ignored

… or alternative RiO number indexes on CRATE_COL_RIO_NUMBER field.

Then, the only field name differences from RCEP are:

Client_Name_History.End_Date  -- not End_Date_
crate_anon.crateweb.consent.lookup_rio.lookup_cpft_rio_generic(lookup: PatientLookup, decisions: List[str], secret_decisions: List[str], as_crate_not_rcep: bool) → None[source]

Look up patient details from a RiO database, either as a CRATE-processed or an RCEP-processed version. (They are very similar.)

Parameters:

  • lookup – a crate_anon.crateweb.consent.models.PatientLookup

  • decisions – list of human-readable decisions; will be modified

  • secret_decisions – list of human-readable decisions containing secret (identifiable) information; will be modified

  • as_crate_not_rcep – is it a CRATE-preprocessed, rather than an RCEP-preprocessed, database?

Main:

Client_Demographic_Details
    Client_ID -- PK; RiO number; integer in VARCHAR(15) field
    Date_of_Birth -- DATETIME
    Date_of_Death -- DATETIME; NULL if not dead
    Death_Flag -- INT; 0 for alive, 1 for dead
    Deleted_Flag -- INT; 0 normally; 1 for deleted
    NHS_Number -- CHAR(10)
    Gender_Code -- 'F', 'M', 'U', 'X'
    Gender_Description -- 'Male', 'Female', ...

Then, linked to it:

Client_Name_History
    Client_ID -- integer in VARCHAR(15)
    Effective_Date -- DATETIME
    End_Date_  -- DATETIME, typically NULL
          -- in the CRATE version, this is End_Date instead
    Name_Type_Code  -- '1' for 'usual name', '2' for 'Alias', '3'
        for 'Preferred name', '4' for 'Birth name', '5' for
        'Maiden name', '7' for 'Other', 'CM' for 'Client Merge';
        NVARCHAR(10)
    Name_Type_Description  -- e.g. 'Usual name', 'Alias'
    Deleted_Flag -- INT

    title
    Given_Name_1  -- through to Given_Name_5
    Family_Name
    suffix
    ...

Client_Address_History
    Client_ID -- integer in VARCHAR(15)
    Address_Type_Code -- e.g. 'PRIMARY' but also 'CA', 'FCH'...
    Address_Type_Description
    Address_From_Date -- DATETIME
    Address_To_Date -- DATETIME; NULL for active ones

    Address_Line_1
    Address_Line_2
    Address_Line_3
    Address_Line_4
    Address_Line_5
    Post_Code
    ... -- no e-mail address field

Client_GP_History
    Client_ID -- integer in VARCHAR(15)
    GP_From_Date -- DATETIME
    GP_To_Date -- DATETIME; NULL for active ones

    GP_Name -- e.g. 'Smith JT'
    GP_Practice_Address_Line1
    GP_Practice_Address_Line2
    GP_Practice_Address_Line3
    GP_Practice_Address_Line4
    GP_Practice_Address_Line5
    GP_Practice_Post_code
    ...

CPFT clinician details/?discharged info appear to be here:

CPA_CareCoordinator
    Client_ID -- integer in VARCHAR(15)
    Start_Date -- DATETIME
    End_Date -- DATETIME
    End_Reason_Code
    End_Reason_Description
    End_Reason_National_Code

    Care_Coordinator_User_title
    Care_Coordinator_User_first_name
    Care_Coordinator_User_surname
    Care_Coordinator_User_email
    Care_Coordinator_User_Consultant_Flag -- INT; 0 or 1 (or NULL?)

Main_Referral_Data
    Client_ID -- integer in VARCHAR(15)
    Referral_Received_Date -- DATETIME
    Removal_DateTime -- DATETIME
    # Care_Spell_Start_Date
    # Care_Spell_End_Date -- never non-NULL in our data set
    # Discharge_HCP -- ??user closing the referral

    Referred_Consultant_User_title
    Referred_Consultant_User_first_name
    Referred_Consultant_User_surname
    Referred_Consultant_User_email
    Referred_Consultant_User_Consultant_Flag  -- 0, 1, NULL

Referral_Staff_History
    Client_ID -- integer in VARCHAR(15)
    Start_Date -- DATETIME
    End_Date -- DATETIME
    Current_At_Discharge -- INT -- ? -- 1 or NULL

    HCP_User_title
    HCP_User_first_name
    HCP_User_surname
    HCP_User_email
    HCP_User_Consultant_Flag  -- 0, 1, NULL

Referral_Team_History
        -- similar, but for teams; no individual info
    Client_ID -- integer in VARCHAR(15)
    Start_Date -- DATETIME
    End_Date -- DATETIME
    Current_At_Discharge -- INT -- ? -- 1 or NULL

    Team_Code -- NVARCHAR -- e.g. 'TCGMH712'
    Team_Description -- NVARCHAR -- e.g. 'George Mackenzie'
    Team_Classification_Group_Code -- NVARCHAR -- e.g. 'FS'
    Team_Classification_Group_Description -- NVARCHAR -- e.g.
                                                    'Forensic Service'

Not obviously relevant:

Client_CPA -- records CPA start/end, etc.
Client_Professional_Contacts -- empty table!

Added 2017-02-27:

Client_Communications_History -- email/phone
    Client_ID -- integer in VARCHAR(15)
    Method_Code -- NVARCHAR(10); '1' for 'Telephone number', '3'
        for 'Email address', '4' for 'Minicom/textphone number'
    Method_Description
    Context_Code -- e.g. '1' for 'Communication address at home',
        other codes for 'Vacation home...', etc.
    Context_Description
    Contact_Details -- NVARCHAR(80)
crate_anon.crateweb.consent.lookup_rio.lookup_cpft_rio_rcep(lookup: PatientLookup, decisions: List[str], secret_decisions: List[str]) → None[source]

Look up patient details from a RiO database that’s been preprocessed through Servelec’s RCEP (RiO CRIS Extraction Program) tool.

Parameters:

  • lookup – a crate_anon.crateweb.consent.models.PatientLookup

  • decisions – list of human-readable decisions; will be modified

  • secret_decisions – list of human-readable decisions containing secret (identifiable) information; will be modified

RiO notes, 2015-05-19

… ADDENDUM 2017-02-27: this is the RiO database as modified by Servelec’s RiO CRIS Extraction Program (RCEP). See also lookup_cpft_rio_raw().

For speed, RiO-RCEP needs these indexes:

USE my_database_name;

CREATE INDEX _idx_cdd_nhs ON Client_Demographic_Details (NHS_Number);

CREATE INDEX _idx_cnh_id ON Client_Name_History (Client_ID);
CREATE INDEX _idx_cnh_eff ON Client_Name_History (Effective_Date);
CREATE INDEX _idx_cnh_end ON Client_Name_History (End_Date_);

CREATE INDEX _idx_cah_id ON Client_Address_History (Client_ID);
CREATE INDEX _idx_cah_from ON Client_Address_History (Address_From_Date);
CREATE INDEX _idx_cah_to ON Client_Address_History (Address_To_Date);

CREATE INDEX _idx_cch_id ON Client_Communications_History (Client_ID);

CREATE INDEX _idx_cgh_id ON Client_GP_History (Client_ID);
CREATE INDEX _idx_cgh_from ON Client_GP_History (GP_From_Date);
CREATE INDEX _idx_cgh_to ON Client_GP_History (GP_To_Date);

CREATE INDEX _idx_cc_id ON CPA_CareCoordinator (Client_ID);
CREATE INDEX _idx_cc_start ON CPA_CareCoordinator (Start_Date);
CREATE INDEX _idx_cc_end ON CPA_CareCoordinator (End_Date);

CREATE INDEX _idx_ref_id ON Main_Referral_Data (Client_ID);
CREATE INDEX _idx_ref_recv ON Main_Referral_Data (Referral_Received_Date);
CREATE INDEX _idx_ref_removal ON Main_Referral_Data (Removal_DateTime);

CREATE INDEX _idx_rsh_id ON Referral_Staff_History (Client_ID);
CREATE INDEX _idx_rsh_start ON Referral_Staff_History (Start_Date);
CREATE INDEX _idx_rsh_end ON Referral_Staff_History (End_Date);

CREATE INDEX _idx_rth_id ON Referral_Team_History (Client_ID);
CREATE INDEX _idx_rth_start ON Referral_Team_History (Start_Date);
CREATE INDEX _idx_rth_end ON Referral_Team_History (End_Date);

-- CREATE INDEX _idx_rth_teamdesc ON Referral_Team_History (Team_Description);  # noqa