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

Timestamp:

10/01/13 12:51:56 (11 years ago)

Author:

Richard Bramley

Comment:

--

DataIntegration GRAPHIC2 CiviCRM to i2b2

@@ -6 +6 @@
 
 1. Bring across consents and IDs etc from the civicrm_value_graphic2_10 table.
-1. Check that the patient has one of the required statuses in the civicrm_case.status_id column.  (Define what these are ~ recruited, completed, etc)
+1. Check that the patient has one of the required statuses in the civicrm_case.status_id column.  These are: 6: Available for cohort, 10: Completed, 1: Open, 5: Recruited.
+
+=== Query
+
+As usual the queries are different for test and for live.  Hurray!
+
+==== Test
+
+{{{
+#!sql
+
+SELECT
+      recruit.id civiCRM_contact_id
+    , 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
+    , recruit.birth_date
+    , enrollment.start_date enrollment_date
+    , CASE
+        WHEN (wstatus.withdrawal_status_24 = 'A') THEN enrollment.end_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.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
+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 */
+               )
+;
+
+}}}