= CiviCRM Graphic 2 Patient Export

== Direct Query

=== Requirements

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.  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 */
               )
;

}}}