Onyx to CiviCRM

There are three options available to get BRICCS data from Onyx into CiviCRM:

1. Load from an Onyx extract.
2. Load directly from the Onyx database.
3. Load from the data already loaded into i2b2.

The 2nd option is the most desirable, if possible.

BRICCS Study Enrollment (Case) Type in CiviCRM

I have created a new briccs Drupal module to contain code specific for the BRICCS study. The module creates the BRICCS case type and all custom values specific for the study when it is enabled, and deletes them when it is disabled. The module WILL also contain the cron hook that loads the participants and their data from the Onyx database.

Data Requirements

The following data will be copied from the Onyx database to CiviCRM (the question name within Onyx is shown in brackets):

• [x] Date and Time of Interview {Interview.start_date}
• [x] Partiticant Number (Participant.barcode)
• [x] Interview Status (Interview.status) Values of
  • IN_PROGRESS
  • CLOSED
  • CANCELLED
  • COMPLETED
• [x] Title (participant_attribute_value.pat_title)
• [x] First Name (Participant.firstName)
• [x] Surname (Participant.lastName)
• [x] Address
  • participant_attribute_value.pat_address1
  • participant_attribute_value.pat_address2
  • participant_attribute_value.pat_address3
  • participant_attribute_value.pat_address4
  • participant_attribute_value.pat_postcode
• [x] Date of Birth (Participant.birthDate)
• [x] Gender (Participant.gender)
  • FEMALE or MALE
• [x] Participant NHS Number {participant_attribute_value.pat_nhsnumber}
• [x] Participant UHL System Number {Participant.enrollmentId}
• [x] Consent Values
  • Understands the request for consent (question_answer.question_name="consent_q1")
  • Consents to donate blood and urine (question_answer.question_name="consent_q2")
  • Consents to entry in the BRICCS database (question_answer.question_name="consent_q3")
  • Consents to possible further contact from BRU (question_answer.question_name="consent_q4")
  • Understands the rules for withdrawal (question_answer.question_name="consent_q5")
• [x] GP
• [x] Telephone
• [x] Work Telephone

Additional Requirements

• All interviews will be extracted, regardless of their status.
• Since interviews can be updated if they have not been completed, the procedure will have to cope with updates as well as inserts.
• Since the cron jobs are run in a user's request, make sure that only a few participants are processed for each request.
  • It is possible to have hidden custom fields within CiviCRM by ​marking the Custom Group as inactive. This could be used to store a date last updated or a hash value to cut down the number of interviews that need to be processed every time.

Query

SELECT
      p.id AS ParticipantID
    , DATE_FORMAT(i.start_date, '%Y%m%d%H%i%s') AS interviewDate
    , consent.interviewer_name
    , p.barcode AS StudyID
    , i.status AS interviewStatus
    , pa.title
    , p.first_name
    , p.last_name
    , pa.address_1
    , pa.address_2
    , pa.address_3
    , pa.address_4
    , pa.postcode
    , p.birth_date
    , p.gender
    , pa.nhsNumber
    , p.enrollment_id AS UhlSystemNumber
    , consent.q1 AS consent_understandsConsent
    , consent.q2 AS consent_bloodAndUrine
    , consent.q3 AS consent_briccsDatabase
    , consent.q4 AS consent_furtherContact
    , consent.q5 AS consent_understandsWithdrawal
    , pa.gp
    , pa.telephone
    , pa.workphone
    , mra.most_recent_action
    , i.start_date
    , CASE
        WHEN recruitment_context.episodeType = 'Study_specific' THEN 
          CASE WHEN recruitment_context.studyCode = 'Study_Reference1' THEN 'GeneFast AS study' END
        ELSE recruitment_context.episodeType
      END recruitmentContext
FROM participant p
JOIN interview i ON i.participant_id = p.id
LEFT JOIN (
    SELECT
          qp.participant_id
        , CONCAT(u.first_name, ' ', u.last_name) AS interviewer_name
        , GROUP_CONCAT(if(qa.question_name = 'consent_q1', category_name, NULL)) AS q1 
        , GROUP_CONCAT(if(qa.question_name = 'consent_q2', category_name, NULL)) AS q2
        , GROUP_CONCAT(if(qa.question_name = 'consent_q3', category_name, NULL)) AS q3
        , GROUP_CONCAT(if(qa.question_name = 'consent_q4', category_name, NULL)) AS q4
        , GROUP_CONCAT(if(qa.question_name = 'consent_q5', category_name, NULL)) AS q5
    FROM question_answer qa
    JOIN category_answer ca ON ca.question_answer_id = qa.id
    JOIN questionnaire_participant qp ON qp.id = qa.questionnaire_participant_id
         AND qp.questionnaire_name = 'ManualConsentQuestionnaire'
    JOIN user u ON u.id = qp.user_id
    GROUP BY qp.participant_id
    ) consent ON consent.participant_id = p.id
LEFT JOIN (
    SELECT
          qp.participant_id
        , GROUP_CONCAT(if(qa.question_name = 'epi_type', category_name, NULL)) AS episodeType
        , GROUP_CONCAT(if(qa.question_name = 'epi_studycode', category_name, NULL)) AS studyCode
    FROM question_answer qa
    JOIN category_answer ca ON ca.question_answer_id = qa.id
    JOIN questionnaire_participant qp ON qp.id = qa.questionnaire_participant_id
         AND qp.questionnaire_name = 'RecruitmentContextQuestionnaire'
    GROUP BY qp.participant_id
    ) recruitment_context ON recruitment_context.participant_id = p.id
LEFT JOIN (
    SELECT
        pa.participant_id
      , GROUP_CONCAT(if(pa.attribute_name = 'pat_title', pa.text_value, NULL)) AS title
      , GROUP_CONCAT(if(pa.attribute_name = 'pat_address1', pa.text_value, NULL)) AS address_1
      , GROUP_CONCAT(if(pa.attribute_name = 'pat_address2', pa.text_value, NULL)) AS address_2
      , GROUP_CONCAT(if(pa.attribute_name = 'pat_address3', pa.text_value, NULL)) AS address_3
      , GROUP_CONCAT(if(pa.attribute_name = 'pat_address4', pa.text_value, NULL)) AS address_4
      , GROUP_CONCAT(if(pa.attribute_name = 'pat_postcode', pa.text_value, NULL)) AS postcode
      , GROUP_CONCAT(if(pa.attribute_name = 'pat_ethnicity', pa.text_value, NULL)) AS ethnicity
      , GROUP_CONCAT(if(pa.attribute_name = 'pat_gp', pa.text_value, NULL)) AS gp
      , GROUP_CONCAT(if(pa.attribute_name = 'pat_nhsnumber', pa.text_value, NULL)) AS nhsNumber
      , GROUP_CONCAT(if(pa.attribute_name = 'pat_telephone', pa.text_value, NULL)) AS telephone
      , GROUP_CONCAT(if(pa.attribute_name = 'pat_workphone', pa.text_value, NULL)) AS workphone
    FROM participant_attribute_value pa
    GROUP BY pa.participant_id
    ) pa ON pa.participant_id = p.id
JOIN (
    SELECT
          interview_id
        , MAX(date_time) most_recent_action
    FROM action
    GROUP BY interview_id
    ) mra ON mra.interview_id = i.id
WHERE
    /*
        LIMIT the query to select only the first x records
        ordered by most_recent_action, but include all the
        ties for the last place.

        This allows us to filter the query using
        most_recent_action > last_most_recent_action
        as opposed to most_recent_action >= last_most_recent_action,
        which would start to fail if there were more than x
        records with a most_recent_action of last_most_recent_action.
    */
    mra.most_recent_action IN (
        SELECT DISTINCT most_recent_action
        FROM (
            SELECT
                  interview_id
                , MAX(date_time) most_recent_action
            FROM action
            WHERE date_time > '01-jan-2000' /* last_most_recent_action */
            GROUP BY interview_id
            ORDER BY MAX(date_time) ASC
            LIMIT 10
        ) adf
    )
ORDER BY mra.most_recent_action ASC
;

Import Mapping

• Existing Participant mapping
  • [x] First check that the patient does not already exist.
    • Match based on UHL System Number, NHS Number and date of birth.
  • [x] Produce an error report for patients that partially match - email?
• Date and Time of Interview (New against Case - CIVI_FIELD_BRICCS_INTERVIEW_DATETIME)
  • [x] Custom Field Added in BRICCS install
  • [x] Import mapped
  • [x] Validation checks
• Partiticant Number (New against Case - CIVI_FIELD_BRICCS_ID)
  • [x] Custom Field Added in BRICCS install
  • [x] Import mapped
  • [x] Validation checks
• Interview Status (New against Case - CIVI_FIELD_BRICCS_INTEVIEW_STATUS)
  • Values = 'IN_PROGRESS', 'CLOSED', 'CANCELLED', 'COMPLETED'
  • [x] Custom Field Added in BRICCS install
  • [x] Import mapped
• Case Status
  • [ ] Where Interview Status = 'CANCELLED' then 'Cancelled'
  • [ ] Otherwise 'Recruited'.
• Title (Contact.Title) What to do with titles that don't exist?
  • [x] Import Titles from PMI
  • [x] Convert existing records to new types (i.e., remove full stops, capitalise, etc)
  • [x] Import mapped
  • [x] Identify amendments to other imports
  • [x] Amend other imports
• First Name (Contact.firstName)
  • [x] Import mapped
• Surname (Contact.lastName)
  • [x] Import mapped
• Address
  • [x] Map using Google lookup - see Biobank module
  • [x] Import mapped
• postcode (Address.postcode)
  • [x] Import mapped
• Date of Birth (Contact.birthDate)
  • [x] Import mapped
  • [x] Validations checks
• Gender (Contact.gender)
  • Values = 'FEMALE', 'MALE'
  • [x] Import mapped
  • [x] Validation
• NHS Number {Contact.nhsnumber}
  • [x] Import mapped
  • [x] Formatting
  • [x] Validation checks
• UHL System Number {Contact.nhsNumber}
  • [x] Import mapped
  • [x] Formatting
  • [x] Validation checks
• Recruitment Type
  • [x] Custom field added to BRICCS install / uninstall
  • [x] Import mapped
• Consent Values
  • consent_understandsConsent (New against Case - CIVI_FIELD_BRICCS_CONSENT_UNDERSTANDS_CONSENT)
    • [x] Custom field added in BRICCS install
    • [x] Import mapped
  • consent_bloodAndUrine (New against Case - CIVI_FIELD_BRICCS_CONSENT_BLOOD_AND_URINE)
    • [x] Custom field added in BRICCS install
    • [x] Import mapped
  • consent_briccsDatabase (New against Case - CIVI_FIELD_BRICCS_CONSENT_BRICCS_DATABASE)
    • [x] Custom field added in BRICCS install
    • [x] Import mapped
  • consent_furtherContact (New against Case - CIVI_FIELD_BRICCS_CONSENT_FURTHER_CONTACT)
    • [x] Custom field added in BRICCS install
    • [x] Import mapped
  • consent_understandsWithdrawal (New against Case - CIVI_FIELD_BRICCS_CONSENT_UNDERSTANDS_WITHDRAWAL)
    • [x] Custom field added in BRICCS install
    • [x] Import mapped
• GP (Add relationship to GP)
  • This must fail silently as most GPs will not be in CiviCRM yet
  • [ ] Mapped import to relationship to GP
  • [ ] Mapped import to relationship to GP practice
• Telephone (Add telephone number: Location = home; Type = Phone)
  • [x] Import mapped
• Work Telephone (Add telephone number: Location = work; Type = Phone)
  • [x] Import mapped
• Case Start Date <= Interview start date?
  • [x] Import Mapped

Participant Validation

The validation of participant details will be different depending on whether the participant already exists within CiviCRM or not.

Identification that the participant already exists

• [x] Find participants with the same:
  • [x] NHS Number
  • [x] UHL System Number
• [x] If participants exist for more than one of these, check that they are the same participant.
• [x] For matching participants, check that the following match with the imported data:
  • [x] NHS Number
  • [x] UHL System Number
  • [x] Date of Birth
  • [x] Gender
• [x] If any mismatches in the above are identified:
  • [x] Do not import the participant
  • [ ] Email the details of the participant to be processed manually

Multiple Studies

• [x] A participant may have more than one study enrollment, so multiple Study IDs are possible.

Additional Set Up

• [ ] Add the import error email recipients to the LCBRU config screen. Value of Me, Nick and Sue. Default to me if it is not set.

Participants that do not exists

• Validate that the following fields are valid:
  • [x] NHS Number
  • [x] BRICCS study ID
  • [x] UHL System Number
  • [x] Gender
  • [x] Date of Birth
    • [x] Is valid date
    • [x] Not in future
    • [x] Not over 130 years old
• If any validation checks fail
  • [x] Do import the participant
  • [ ] Email the participant details and validation error

Title Import

• Title is actually called prefix and the option group with a name of 'individual_prefix'.
• I think the thing to do is to disable the current ones, if possible, and give the existing contacts pointers to the new ones.
• If possible this should be implemented as a new button on the LCBRU module configuration dialog, with an undo option.

Processing Considerations

• When processing using cron, the request that processes the cron will be much slower. Therefore, we need to process as little as possible in the cron job and break up the large imports into smaller imports.
• Queued jobs are run from within a cron job, so that doesn't really gain us anything.
• The onyx table action contains a list of start and stop editing actions against different interviews. The participant id can be obtained by looking in the interview table with the interview id from the action table.
• Therefore, it should be possible to identify which participants have changed since the last date processed. Or rather since the date of the last action processed.

The process can therefore look something like this:

1. Run a query in Onyx that returns a list of participants details.
2. The details should contain the date/time of the most-recent action related to an interview for that participant, participant_most_recent_action_date.
3. The query should be filtered to not return participant details whose most recent action occurred before the most recent action processed by the system. NB: Not the date that the processing occurred, but the date of the participant's participant_most_recent_action_date
4. The query should be filtered to return only as many participant details as can be processed in a time that will not unduly affect the performance of the request in which the processing occurs.
5. The query results should be ordered by participant_most_recent_action_date ascending.
6. The details should be processed.
7. The participant_most_recent_action_date for the final participant should be recorded in a variable. Are Drupal Variables persistent when the server is restarted?