| 5 | | == Direct Query |
| 6 | | |
| 7 | | === Requirements |
| 8 | | |
| 9 | | 1. Bring across consents and IDs etc from the civicrm_value_graphic2_10 table. |
| 10 | | 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. |
| 11 | | 1. At some point in the furture Graphic 2 will have a withrawal status similar to the one used in Gevasc. I have therefore left in the code, but commented out. I am evil. |
| 12 | | === Query |
| 13 | | |
| 14 | | As usual the queries are different for test and for live. Hurray! |
| 15 | | |
| 16 | | ==== Test |
| 17 | | |
| 18 | | {{{ |
| 19 | | #!sql |
| 20 | | |
| 21 | | SELECT |
| 22 | | recruit.id civiCRM_contact_id |
| 23 | | , cids.nhs_number_1 nhs_number |
| 24 | | , cids.uhl_s_number_2 uhl_s_number |
| 25 | | , g2d.graphic_participant_id_25 graphic_patient_id |
| 26 | | , g2d.graphic_lab_id_26 graphic_lab_id |
| 27 | | , g2d.graphic_family_id_27 graphic_family_id |
| 28 | | , g2d.consent_for_further_studies_28 consent_for_further_studies |
| 29 | | , g2d.g1_blood_consent_29 graphic1_blood_consent |
| 30 | | , g2d.pre_consent_to_graphic_2_30 pre_consent_to_graphic2 |
| 31 | | , recruit.birth_date |
| 32 | | , enrollment.start_date enrollment_date |
| 33 | | /* , CASE |
| 34 | | WHEN (wstatus.withdrawal_status_24 = 'A') THEN enrollment.end_date |
| 35 | | END withdrawal_date */ |
| 36 | | FROM civicrm_contact recruit |
| 37 | | JOIN civicrm_case_contact enrollcontact ON enrollcontact.contact_id = recruit.id |
| 38 | | JOIN civicrm_case enrollment ON enrollment.id = enrollcontact.case_id |
| 39 | | AND enrollment.status_id NOT IN (7 /* declined */, 9 /* excluded */, 4 /* Recruitment Pending */) |
| 40 | | AND enrollment.case_type_id = "5" /* Graphic 2 */ |
| 41 | | AND enrollment.is_deleted = 0 |
| 42 | | JOIN civicrm_value_contact_ids_2 cids ON cids.entity_id = recruit.id /* Is this the case? */ |
| 43 | | JOIN civicrm_value_graphic2_10 g2d ON g2d.entity_id = enrollment.id /* CHECK THIS */ |
| 44 | | /* MAYBE: LEFT JOIN civicrm_value_genvasc_withdrawal_status_9 wstatus ON wstatus.entity_id = enrollment.id */ |
| 45 | | WHERE recruit.is_deleted = 0 |
| 46 | | /* AND COALESCE(wstatus.withdrawal_status_24, '') <> 'B' */ |
| 47 | | ; |
| 48 | | |
| 49 | | }}} |
| | 5 | - **Status**: [[DataIntegration Status Unknown]] |
| | 6 | - **Source**: [[DataIntegration Source GRAPHIC2 CiviCRM]] |
| | 7 | - **Destination**: [[DataIntegration Destination GRAPHIC2 i2b2]] |
| | 8 | - **Technology**: [[i2b2 Mapped Importer]] |
| | 9 | - **Study**: [[BRICCS Study]] |
