Changes between Version 2 and Version 3 of DataIntegration GRAPHIC2 CiviCRM to i2b2

Timestamp:

10/01/13 13:04:57 (11 years ago)

Author:

Richard Bramley

Comment:

--

DataIntegration GRAPHIC2 CiviCRM to i2b2

@@ -21 +21 @@
     , cids.nhs_number_1 nhs_number
     , cids.uhl_s_number_2 uhl_s_number
-    , grd.genvasc_id_10 genvasc_id
-    , grd.genvasc_consent_q1_11 consent_1
-    , grd.genvasc_consent_q2_12 consent_2
-    , grd.genvasc_consent_q3_13 consent_3
-    , grd.genvasc_consent_q4_14 consent_4
-    , grd.genvasc_consent_q5_15 consent_5
-    , grd.genvasc_consent_q6_16 consent_6
-    , grd.genvasc_consent_q7_17 consent_7
+    , g2d.*
     , recruit.birth_date
     , enrollment.start_date enrollment_date
-    , CASE
+/*    , CASE
         WHEN (wstatus.withdrawal_status_24 = 'A') THEN enrollment.end_date
-      END withdrawal_date
+      END withdrawal_date */
 FROM   civicrm_contact recruit
 JOIN   civicrm_case_contact enrollcontact ON enrollcontact.contact_id = recruit.id
 JOIN   civicrm_case enrollment ON enrollment.id = enrollcontact.case_id
-            AND enrollment.start_date < DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
-            AND enrollment.status_id NOT IN (699 /* declined */, 701 /* excluded */)
-            AND enrollment.case_type_id = "3" /* GENVASC */
+            AND enrollment.status_id NOT IN (7 /* declined */, 9 /* excluded */, 4 /* Recruitment Pending */)
+            AND enrollment.case_type_id = "5" /* Graphic 2 */
             AND enrollment.is_deleted = 0
-JOIN   civicrm_value_contact_ids_2 cids ON cids.entity_id = recruit.id
-JOIN   civicrm_value_genvasc_recruitment_data_6 grd ON grd.entity_id = enrollment.id
-LEFT JOIN civicrm_value_genvasc_withdrawal_status_9 wstatus ON wstatus.entity_id = enrollment.id
+JOIN   civicrm_value_contact_ids_2 cids ON cids.entity_id = recruit.id /* Is this the case? */
+JOIN   civicrm_value_graphic2_10 g2d ON g2d.entity_id = enrollment.id /* CHECK THIS */
+/* MAYBE: LEFT JOIN civicrm_value_genvasc_withdrawal_status_9 wstatus ON wstatus.entity_id = enrollment.id */
 WHERE  recruit.is_deleted = 0
-    AND COALESCE(wstatus.withdrawal_status_24, '') <> 'B'
-    AND EXISTS (  SELECT 1
-                  FROM  civicrm_relationship rel
-                  WHERE rel.contact_id_a = recruit.id
-                    AND rel.relationship_type_id = 15 /* with GP surgery */
-               )
+/*    AND COALESCE(wstatus.withdrawal_status_24, '') <> 'B' */
 ;