Changes between Version 8 and Version 9 of Onyx to CiviCRM

Timestamp:

12/12/13 15:30:59 (11 years ago)

Author:

Richard Bramley

Comment:

--

Onyx to CiviCRM

@@ -65 +65 @@
     , p.enrollment_id AS UhlSystemNumber
     , consent_q1.value AS consent_q1
+    , consent_q1.value AS consent_q2
+    , consent_q1.value AS consent_q3
+    , consent_q1.value AS consent_q4
+    , consent_q1.value AS consent_q5
 FROM participant p
 LEFT JOIN (
@@ -76 +80 @@
     WHERE qa.question_name = 'consent_q1'
     ) consent_q1 ON consent_q1.participant_id = p.id
+LEFT JOIN (
+    SELECT
+          qp.participant_id
+        , ca.category_name AS value
+    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'
+    WHERE qa.question_name = 'consent_q2'
+    ) consent_q2 ON consent_q2.participant_id = p.id
+LEFT JOIN (
+    SELECT
+          qp.participant_id
+        , ca.category_name AS value
+    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'
+    WHERE qa.question_name = 'consent_q3'
+    ) consent_q3 ON consent_q3.participant_id = p.id
+LEFT JOIN (
+    SELECT
+          qp.participant_id
+        , ca.category_name AS value
+    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'
+    WHERE qa.question_name = 'consent_q4'
+    ) consent_q4 ON consent_q4.participant_id = p.id
+LEFT JOIN (
+    SELECT
+          qp.participant_id
+        , ca.category_name AS value
+    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'
+    WHERE qa.question_name = 'consent_q5'
+    ) consent_q5 ON consent_q5.participant_id = p.id
 ;