= Onyx to CiviCRM

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

1. Load from an Onyx extract.
1. Load directly from the Onyx database.
1. 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 [http://book.civicrm.org/user/current/organising-your-data/custom-fields/ 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

{{{#!sql

SELECT
      p.id AS ParticipantID
    , i.start_date AS interviewDate
    , 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
FROM participant p
JOIN interview i ON i.participant_id = p.id
LEFT JOIN (
    SELECT
          qp.participant_id
        , 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'
    GROUP BY qp.participant_id
    ) consent ON consent.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
;

}}}

== Import Mapping

- Date and Time of Interview ('''New''' against Case - {{{CIVI_FIELD_BRICCS_INTERVIEW_DATETIME}}})
 - [x] Custom Field Added in BRICCS install
 - [ ] Import mapped
- Partiticant Number ('''New''' against Case - {{{CIVI_FIELD_BRICCS_ID}}})
 - [x] Custom Field Added in BRICCS install
 - [ ] Import mapped
- Interview Status ('''New''' against Case - {{{CIVI_FIELD_BRICCS_INTEVIEW_STATUS}}})
 - Values = 'IN_PROGRESS', 'CLOSED', 'CANCELLED', 'COMPLETED'
 - [x] Custom Field Added in BRICCS install
 - [ ] Import mapped
- Title (Contact.Title) ''What to do with titles that don't exist?''
 - [ ] Import Titles from PMI
 - [ ] Convert existing records to new types (i.e., remove full stops, capitalise, etc)
 - [ ] Import mapped
 - [ ] Identify amendments to other imports
 - [ ] Amend other imports
- First Name (Contact.firstName)
 - [ ] Import mapped
- Surname (Contact.lastName)
 - [ ] Import mapped
- Address
 - [ ] Map using Google lookup - ''see Biobank module''
 - [ ] Import mapped
- postcode (Address.postcode)
 - [ ] Import mapped
- Date of Birth (Contact.birthDate)
 - [ ] Import mapped
- Gender (Contact.gender)
 - Values = 'FEMALE', 'MALE'
 - [ ] Import mapped
- NHS Number {Contact.nhsnumber}
 - [ ] Import mapped
- UHL System Number {Contact.nhsNumber}
 - [ ] Import mapped
- Consent Values
 - consent_understandsConsent ('''New''' against Case - {{{CIVI_FIELD_BRICCS_CONSENT_UNDERSTANDS_CONSENT}}})
  - [x] Custom field added in BRICCS install
  - [ ] Import mapped
 - consent_bloodAndUrine ('''New''' against Case - {{{CIVI_FIELD_BRICCS_CONSENT_BLOOD_AND_URINE}}})
  - [x] Custom field added in BRICCS install
  - [ ] Import mapped
 - consent_briccsDatabase ('''New''' against Case - {{{CIVI_FIELD_BRICCS_CONSENT_BRICCS_DATABASE}}})
  - [x] Custom field added in BRICCS install
  - [ ] Import mapped
 - consent_furtherContact ('''New''' against Case - {{{CIVI_FIELD_BRICCS_CONSENT_FURTHER_CONTACT}}})
  - [x] Custom field added in BRICCS install
  - [ ] Import mapped
 - consent_understandsWithdrawal ('''New''' against Case - {{{CIVI_FIELD_BRICCS_CONSENT_UNDERSTANDS_WITHDRAWAL}}})
  - [x] Custom field added in BRICCS install
  - [ ] 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}}})
 - [ ] Import mapped
- Work Telephone  (Add telephone number: {{{Location = work}}}; {{{Type = Phone}}})
 - [ ] Import mapped