Changes between Version 38 and Version 39 of Onyx to CiviCRM

Timestamp:

01/13/14 09:58:03 (11 years ago)

Author:

Richard Bramley

Comment:

--

Onyx to CiviCRM

@@ -86 +86 @@
     , mra.most_recent_action
     , i.start_date
+    , CASE
+        WHEN recruitment_context.episodeType = 'Study_specific' THEN recruitment_context.studyCode
+        ELSE recruitment_context.episodeType
+      END recruitmentContext
 FROM participant p
 JOIN interview i ON i.participant_id = p.id
@@ -102 +106 @@
     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