Changes between Version 10 and Version 11 of Onyx to CiviCRM


Ignore:
Timestamp:
12/12/13 15:52:53 (10 years ago)
Author:
Richard Bramley
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • Onyx to CiviCRM

    v10 v11  
    6464    , p.gender
    6565    , p.enrollment_id AS UhlSystemNumber
    66     , consent_q1.value AS consent_q1
    67     , consent_q1.value AS consent_q2
    68     , consent_q1.value AS consent_q3
    69     , consent_q1.value AS consent_q4
    70     , consent_q1.value AS consent_q5
    71 FROM participant p
    72 LEFT JOIN (
    73     SELECT
    74           qp.participant_id
    75         , ca.category_name AS value
    76     FROM question_answer qa
    77     JOIN category_answer ca ON ca.question_answer_id = qa.id
    78     JOIN questionnaire_participant qp ON qp.id = qa.questionnaire_participant_id
    79          AND qp.questionnaire_name = 'ManualConsentQuestionnaire'
    80     WHERE qa.question_name = 'consent_q1'
    81     ) consent_q1 ON consent_q1.participant_id = p.id
    82 LEFT JOIN (
    83     SELECT
    84           qp.participant_id
    85         , ca.category_name AS value
    86     FROM question_answer qa
    87     JOIN category_answer ca ON ca.question_answer_id = qa.id
    88     JOIN questionnaire_participant qp ON qp.id = qa.questionnaire_participant_id
    89          AND qp.questionnaire_name = 'ManualConsentQuestionnaire'
    90     WHERE qa.question_name = 'consent_q2'
    91     ) consent_q2 ON consent_q2.participant_id = p.id
    92 LEFT JOIN (
    93     SELECT
    94           qp.participant_id
    95         , ca.category_name AS value
    96     FROM question_answer qa
    97     JOIN category_answer ca ON ca.question_answer_id = qa.id
    98     JOIN questionnaire_participant qp ON qp.id = qa.questionnaire_participant_id
    99          AND qp.questionnaire_name = 'ManualConsentQuestionnaire'
    100     WHERE qa.question_name = 'consent_q3'
    101     ) consent_q3 ON consent_q3.participant_id = p.id
    102 LEFT JOIN (
    103     SELECT
    104           qp.participant_id
    105         , ca.category_name AS value
    106     FROM question_answer qa
    107     JOIN category_answer ca ON ca.question_answer_id = qa.id
    108     JOIN questionnaire_participant qp ON qp.id = qa.questionnaire_participant_id
    109          AND qp.questionnaire_name = 'ManualConsentQuestionnaire'
    110     WHERE qa.question_name = 'consent_q4'
    111     ) consent_q4 ON consent_q4.participant_id = p.id
    112 LEFT JOIN (
    113     SELECT
    114           qp.participant_id
    115         , ca.category_name AS value
    116     FROM question_answer qa
    117     JOIN category_answer ca ON ca.question_answer_id = qa.id
    118     JOIN questionnaire_participant qp ON qp.id = qa.questionnaire_participant_id
    119          AND qp.questionnaire_name = 'ManualConsentQuestionnaire'
    120     WHERE qa.question_name = 'consent_q5'
    121     ) consent_q5 ON consent_q5.participant_id = p.id
    122 ;
    123 
    124 }}}
    125 
    126 Try a different approach:
    127 
    128 {{{#!sql
    129 
    130 SELECT
    131       p.id AS ParticipantID
    132     , p.barcode AS StudyID
    133     , p.first_name
    134     , p.last_name
    135     , p.birth_date
    136     , p.gender
    137     , p.enrollment_id AS UhlSystemNumber
    13866    , consent.q1
    13967    , consent.q2
    … …  
    14674          qp.participant_id
    14775        , GROUP_CONCAT(if(qa.question_name = 'consent_q1', category_name, NULL)) AS q1
    148         , GROUP_CONCAT(if(qa.question_name = 'consent_q1', category_name, NULL)) AS q2
    149         , GROUP_CONCAT(if(qa.question_name = 'consent_q1', category_name, NULL)) AS q3
    150         , GROUP_CONCAT(if(qa.question_name = 'consent_q1', category_name, NULL)) AS q4
    151         , GROUP_CONCAT(if(qa.question_name = 'consent_q1', category_name, NULL)) AS q5
     76        , GROUP_CONCAT(if(qa.question_name = 'consent_q2', category_name, NULL)) AS q2
     77        , GROUP_CONCAT(if(qa.question_name = 'consent_q3', category_name, NULL)) AS q3
     78        , GROUP_CONCAT(if(qa.question_name = 'consent_q4', category_name, NULL)) AS q4
     79        , GROUP_CONCAT(if(qa.question_name = 'consent_q5', category_name, NULL)) AS q5
    15280    FROM question_answer qa
    15381    JOIN category_answer ca ON ca.question_answer_id = qa.id