USE caris_db;
SET  @start_date = '2019-07-01';
SET	@end_date = '2019-09-30';
set
@start_date_last = '2019-04-01';
set
@end_date_last = '2019-06-30';
SELECT 
    patient_code,
    IF(arv.id_patient IS NOT NULL,
        'yes',
        'no') AS on_arv,
    IF(statut.id_patient IS NOT NULL,
        'pos',
        'neg') AS statut,
    IF(followup_q1 IS NOT NULL
            OR odk_q1 IS NOT NULL
            OR arv_q1 IS NOT NULL
            OR ques_q1 IS NOT NULL
            OR club_q1 IS NOT NULL
            OR mereenfant_q1 is not null
            OR specimen_q1 is not null,
        'yes',
        'no') AS q1,
    IF(followup_q1 IS NOT NULL, 'yes', 'no') AS followup_q1,
    IF(odk_q1 IS NOT NULL, 'yes', 'no') AS odk_q1,
    IF(arv_q1 IS NOT NULL, 'yes', 'no') AS arv_q1,
    IF(ques_q1 IS NOT NULL, 'yes', 'no') AS ques_q1,
    IF(club_q1 IS NOT NULL, 'yes', 'no') AS club_q1,
    if(mereenfant_q1 is not null, 'yes','no') as mereenfant_q1,
    if(specimen_q1 is not null, 'yes','no') as specimen_q1,
    IF(followup_q2 IS NOT NULL
            OR odk_q2 IS NOT NULL
            OR arv_q2 IS NOT NULL
            OR ques_q2 IS NOT NULL
            OR club_q2 IS NOT NULL
            or mereenfant_q2 is not null
            or specimen_q2 is not null,
        'yes',
        'no') AS q2,
    IF(followup_q2 IS NOT NULL, 'yes', 'no') AS followup_q2,
    IF(odk_q2 IS NOT NULL, 'yes', 'no') AS odk_q2,
    IF(arv_q2 IS NOT NULL, 'yes', 'no') AS arv_q2,
    IF(ques_q2 IS NOT NULL, 'yes', 'no') AS ques_q2,
    IF(club_q2 IS NOT NULL, 'yes', 'no') AS club_q2,
    if(mereenfant_q2 is not null, 'yes','no') as mereenfant_q2,
    if(specimen_q2 is not null, 'yes','no') as specimen_q2,
    office
FROM
    (SELECT 
        *
    FROM
        (SELECT 
        id_patient
    FROM
        tracking_followup
    WHERE
        date BETWEEN @start_date_last AND @end_date UNION (SELECT DISTINCT
        patient.id AS id_patient
    FROM
        openfn.odk_child_visit
    LEFT JOIN patient ON patient.patient_code = odk_child_visit.patient_code
    WHERE
        date_of_visit BETWEEN @start_date_last AND @end_date
            AND patient.id IS NOT NULL
            AND is_available_at_time_visit = 1
            AND linked_to_id_patient = 0) UNION SELECT 
        id_patient
    FROM
        tracking_regime
    WHERE
        (start_date BETWEEN @start_date_last AND @end_date or end_date BETWEEN @start_date_last AND @end_date)
            AND category = 'regime_infant_treatment' UNION SELECT 
        id_patient
    FROM
        questionnaire_child
    WHERE
        date BETWEEN @start_date_last AND @end_date UNION SELECT 
        id_patient
    FROM
        testing_mereenfant
    WHERE
        date BETWEEN @start_date_last AND @end_date UNION SELECT 
        id_patient
    FROM
        testing_specimen
    WHERE
        date_blood_taken BETWEEN @start_date_last AND @end_date  UNION SELECT 
        id_patient
    FROM
        questionnaire_proftraining18
    WHERE
        date BETWEEN @start_date_last AND @end_date UNION ((SELECT 
        id_patient
    FROM
        session
    LEFT JOIN club_session ON club_session.id = id_club_session
    LEFT JOIN club ON club_session.id_club = club.id
    WHERE
        is_present = 1
            AND (date BETWEEN @start_date AND @end_date
            OR date BETWEEN @start_date_last AND @end_date_last)
            AND club_type != 1))) o) bot
        LEFT JOIN
    (SELECT DISTINCT
        id_patient AS followup_q1
    FROM
        tracking_followup
    WHERE
        date BETWEEN @start_date_last AND @end_date_last) x ON followup_q1 = bot.id_patient
        left join
(select distinct id_patient as specimen_q1 from testing_specimen WHERE
        date_blood_taken BETWEEN @start_date_last AND @end_date_last) x2 ON specimen_q1 = bot.id_patient
                left join
(select distinct id_patient as specimen_q2 from testing_specimen WHERE
        date_blood_taken BETWEEN @start_date AND @end_date) x3 ON specimen_q2 = bot.id_patient
        LEFT JOIN
    (SELECT DISTINCT
        id_patient AS followup_q2
    FROM
        tracking_followup
    WHERE
        date BETWEEN @start_date AND @end_date) y ON followup_q2 = bot.id_patient
        LEFT JOIN
    patient ON patient.id = bot.id_patient
        LEFT JOIN
    (SELECT DISTINCT
        id_patient AS mereenfant_q1
    FROM
        testing_mereenfant
    WHERE
        date BETWEEN @start_date_last AND @end_date_last) me ON mereenfant_q1 = bot.id_patient
        LEFT JOIN
    (SELECT DISTINCT
        id_patient AS mereenfant_q2
    FROM
        testing_mereenfant
    WHERE
        date BETWEEN @start_date AND @end_date) me2 ON mereenfant_q2 = bot.id_patient
        LEFT JOIN
    (SELECT DISTINCT
        patient.id AS odk_q1
    FROM
        openfn.odk_child_visit
    LEFT JOIN patient ON patient.patient_code = odk_child_visit.patient_code
    WHERE
        date_of_visit BETWEEN @start_date_last AND @end_date_last
            AND patient.id IS NOT NULL
            AND is_available_at_time_visit = 1
            AND linked_to_id_patient = 0) z ON odk_q1 = bot.id_patient
        LEFT JOIN
    (SELECT DISTINCT
        patient.id AS odk_q2
    FROM
        openfn.odk_child_visit
    LEFT JOIN patient ON patient.patient_code = odk_child_visit.patient_code
    WHERE
        date_of_visit BETWEEN @start_date AND @end_date
            AND patient.id IS NOT NULL
            AND is_available_at_time_visit = 1
            AND linked_to_id_patient = 0) zz ON odk_q2 = bot.id_patient
        LEFT JOIN
    (SELECT DISTINCT
        id_patient AS arv_q2
    FROM
        tracking_regime
    WHERE
        start_date BETWEEN @start_date AND @end_date or end_date BETWEEN @start_date AND @end_date) a2 ON arv_q2 = bot.id_patient
        LEFT JOIN
    (SELECT DISTINCT
        id_patient AS arv_q1
    FROM
        tracking_regime
    WHERE
        start_date BETWEEN @start_date_last AND @end_date_last or end_date BETWEEN @start_date_last AND @end_date_last) a1 ON arv_q1 = bot.id_patient
        LEFT JOIN
    (SELECT 
        id_patient AS ques_q1
    FROM
        questionnaire_child
    WHERE
        date BETWEEN @start_date_last AND @end_date_last UNION SELECT 
        id_patient AS ques_q1
    FROM
        questionnaire_proftraining18
    WHERE
        date BETWEEN @start_date_last AND @end_date_last) q1 ON ques_q1 = bot.id_patient
        LEFT JOIN
    (SELECT 
        id_patient AS ques_q2
    FROM
        questionnaire_child
    WHERE
        date BETWEEN @start_date AND @end_date UNION SELECT 
        id_patient AS ques_q2
    FROM
        questionnaire_proftraining18
    WHERE
        date BETWEEN @start_date AND @end_date) q2 ON ques_q2 = bot.id_patient
        LEFT JOIN
    (SELECT DISTINCT
        id_patient AS club_q1
    FROM
        session
    LEFT JOIN club_session ON club_session.id = id_club_session
    LEFT JOIN club ON club_session.id_club = club.id
    WHERE
        is_present = 1
            AND date BETWEEN @start_date_last AND @end_date_last
            AND club_type != 1) c1 ON club_q1 = bot.id_patient
        LEFT JOIN
    (SELECT DISTINCT
        id_patient AS club_q2
    FROM
        session
    LEFT JOIN club_session ON club_session.id = id_club_session
    LEFT JOIN club ON club_session.id_club = club.id
    WHERE
        is_present = 1
            AND date BETWEEN @start_date AND @end_date
            AND club_type != 1) c2 ON club_q2 = bot.id_patient
        LEFT JOIN
    lookup_hospital ON CONCAT(lookup_hospital.city_code,
            '/',
            lookup_hospital.hospital_code) = LEFT(patient_code, 8)
        LEFT JOIN
    (SELECT DISTINCT
        id_patient
    FROM
        tracking_regime
    WHERE
            category = 'regime_infant_treatment') arv ON arv.id_patient = bot.id_patient
        LEFT JOIN
    (SELECT 
        id_patient
    FROM
        tracking_followup UNION SELECT 
        id_patient
    FROM
        tracking_regime
    WHERE
        category = 'regime_infant_treatment' UNION SELECT 
        id_patient
    FROM
        club_patient UNION SELECT 
        id_patient
    FROM
        questionnaire_child UNION SELECT 
        id_patient
    FROM
        questionnaire_proftraining18 UNION SELECT 
        id_patient
    FROM
        testing_specimen
    WHERE
        pcr_result = 1 UNION SELECT 
        id_patient
    FROM
        testing_result
    WHERE
        result = 1 UNION SELECT 
        id_patient
    FROM
        tracking_infant
    WHERE
        positive_pcr_1 != '0000-00-00'
            OR positive_pcr_2 != '0000-00-00') statut ON statut.id_patient = bot.id_patient
WHERE
    linked_to_id_patient = 0