Changes between Version 11 and Version 12 of Onyx to CiviCRM

Timestamp:

12/13/13 08:40:15 (10 years ago)

Author:

Richard Bramley

Comment:

--

Onyx to CiviCRM

@@ -17 +17 @@
 The following data will be copied from the Onyx database to CiviCRM (the question name within Onyx is shown in brackets):
 
-- Date and Time of Interview {Interview.start_date}
-- Partiticant Number (Participant.barcode)
-- Interview Status (Interview.status) Values of
+- [x] Date and Time of Interview {Interview.start_date}
+- [x] Partiticant Number (Participant.barcode)
+- [ ] Interview Status (Interview.status) Values of
  - IN_PROGRESS
  - CLOSED
  - CANCELLED
  - COMPLETED
-- Title (participant_attribute_value.pat_title)
-- First Name (Participant.firstName)
-- Surname (Participant.lastName)
-- Address
+- [ ] Title (participant_attribute_value.pat_title)
+- [x] First Name (Participant.firstName)
+- [x] Surname (Participant.lastName)
+- [ ] Address
  - participant_attribute_value.pat_address1
  - participant_attribute_value.pat_address2
@@ -33 +33 @@
  - participant_attribute_value.pat_address4
  - participant_attribute_value.pat_postcode
-- Date of Birth (Participant.birthDate)
-- Gender (Participant.gender)
+- [x] Date of Birth (Participant.birthDate)
+- [x] Gender (Participant.gender)
  - FEMALE or MALE
-- Participant NHS Number {participant_attribute_value.pat_nhsnumber}
-- Participant UHL System Number {Participant.enrollmentId}
-- Consent Values
+- [ ] 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")
@@ -58 +58 @@
 SELECT
       p.id AS ParticipantID
+    , i.start_date AS interviewDate
     , p.barcode AS StudyID
     , p.first_name
@@ -64 +65 @@
     , p.gender
     , p.enrollment_id AS UhlSystemNumber
-    , consent.q1
-    , consent.q2
-    , consent.q3
-    , consent.q4
-    , consent.q5
+    , 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
 FROM participant p
+JOIN interview i ON i.participant_id = p.id
 LEFT JOIN (
     SELECT