CiviCRM Graphic 2 Patient Export

Direct Query

Requirements

1. Bring across consents and IDs etc from the civicrm_value_graphic2_10 table.
2. 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.
3. 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.

Query

As usual the queries are different for test and for live. Hurray!

Test

SELECT
      recruit.id civiCRM_contact_id
    , cids.nhs_number_1 nhs_number
    , cids.uhl_s_number_2 uhl_s_number
    , g2d.graphic_participant_id_25 graphic_patient_id
    , g2d.graphic_lab_id_26 [graphic_lab_id]
    , g2d.graphic_family_id_27 [graphic_family_id]
    , g2d.consent_for_further_studies_28 [consent_for_further_studies]
    , g2d.g1_blood_consent_29 [graphic1_blood_consent]
    , g2d.pre_consent_to_graphic_2_30 [pre_consent_to_graphic2]
    , 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.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 /* 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' */
;