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 
    bot.patient_code,
    IF(club_q1 IS NOT NULL,
        'yes',
        IF(quest_q1 IS NOT NULL,
            'yes',
            IF(arv_1 IS NOT NULL,
                'yes',
                IF(odk_1 IS NOT NULL,
                    'yes',
                    IF(ptme_1 IS NOT NULL,
                        'yes',
                        IF(ptme1_1 IS NOT NULL,
                            'yes',
                            IF(mereenfant_1 IS NOT NULL,
                                'yes',
                                'no'))))))) AS q1,
    IF(club_q2 IS NOT NULL,
        'yes',
        IF(quest_q2 IS NOT NULL,
            'yes',
            IF(arv_2 IS NOT NULL,
                'yes',
                IF(odk_2 IS NOT NULL,
                    'yes',
                    IF(ptme_2 IS NOT NULL,
                        'yes',
                        IF(ptme1_2 IS NOT NULL,
                            'yes',
                            IF(mereenfant_2 IS NOT NULL,
                                'yes',
                                'no'))))))) AS q2,
    IF(club_q1 IS NOT NULL, 'yes', 'no') AS club_q1,
    IF(club_q2 IS NOT NULL, 'yes', 'no') AS club_q2,
    IF(quest_q1 IS NOT NULL, 'yes', 'no') AS quest_q1,
    IF(quest_q2 IS NOT NULL, 'yes', 'no') AS quest_q2,
    IF(arv_1 IS NOT NULL, 'yes', 'no') AS on_arv_q1,
    IF(arv_2 IS NOT NULL, 'yes', 'no') AS on_arv_q2,
    IF(odk_1 IS NOT NULL, 'yes', 'no') AS odk_q1,
    IF(odk_2 IS NOT NULL, 'yes', 'no') AS odk_q2,
    IF(COALESCE(ptme_1, ptme1_1) IS NOT NULL,
        'yes',
        'no') AS ptme_q1,
    IF(COALESCE(ptme_2, ptme1_2) IS NOT NULL,
        'yes',
        'no') AS ptme_q2,
    IF(mereenfant_1 IS NOT NULL,
        'yes',
        'no') AS mereenfant_q1,
    IF(mereenfant_2 IS NOT NULL,
        'yes',
        'no') AS mereenfant_2,
    office
FROM
    (SELECT 
        *
    FROM
        (SELECT 
        patient_code
    FROM
        tracking_motherbasicinfo
    LEFT JOIN patient ON patient.id = tracking_motherbasicinfo.id_patient) o UNION SELECT 
        health_id AS patient_code
    FROM
        openfn.odk_pregnancy_visit
    WHERE
        date_of_visit BETWEEN @start_date_last AND @end_date) bot
        LEFT JOIN
    (SELECT DISTINCT
        patient_code AS club_q2
    FROM
        session
    LEFT JOIN club_session ON club_session.id = session.id_club_session
    LEFT JOIN patient ON patient.id = session.id_patient
    WHERE
        is_present = 1
            AND club_session.date BETWEEN @start_date AND @end_date) c2 ON club_q2 = bot.patient_code
        LEFT JOIN
    patient ON patient.patient_code = bot.patient_code
        LEFT JOIN
    (SELECT DISTINCT
        patient_code AS club_q1
    FROM
        session
    LEFT JOIN club_session ON club_session.id = session.id_club_session
    LEFT JOIN patient ON patient.id = session.id_patient
    WHERE
        is_present = 1
            AND club_session.date BETWEEN @start_date_last AND @end_date_last) c1 ON club_q1 = bot.patient_code
        LEFT JOIN
    (SELECT 
        patient_code AS quest_q1, date
    FROM
        (SELECT 
        id_patient, date
    FROM
        questionnaire_motherhivknowledge UNION SELECT 
        id_patient, date
    FROM
        questionnaire_mothersurvey UNION SELECT 
        id_patient, date
    FROM
        questionnaire_newmotherhivknowledge) x
    LEFT JOIN patient ON patient.id = x.id_patient
    WHERE
        date BETWEEN @start_date_last AND @end_date_last) quest ON quest_q1 = bot.patient_code
        LEFT JOIN
    (SELECT 
        patient_code AS quest_q2
    FROM
        (SELECT 
        id_patient
    FROM
        questionnaire_motherhivknowledge
    WHERE
        date BETWEEN @start_date AND @end_date UNION SELECT 
        id_patient
    FROM
        questionnaire_mothersurvey
    WHERE
        date BETWEEN @start_date AND @end_date UNION SELECT 
        id_patient
    FROM
        questionnaire_newmotherhivknowledge
    WHERE
        date BETWEEN @start_date AND @end_date) y
    LEFT JOIN patient ON patient.id = y.id_patient) ques ON quest_q2 = bot.patient_code
        LEFT JOIN
    lookup_hospital ON CONCAT(lookup_hospital.city_code,
            '/',
            lookup_hospital.hospital_code) = LEFT(patient.patient_code, 8)
        LEFT JOIN
    (SELECT DISTINCT
        patient_code AS arv_1
    FROM
        tracking_regime
    LEFT JOIN patient ON patient.id = id_patient
    WHERE
        (start_date BETWEEN @start_date_last AND @end_date_last
            OR end_date BETWEEN @start_date_last AND @end_date_last)
            AND category = 'regime_mother_treatment') ar ON arv_1 = bot.patient_code
        LEFT JOIN
    (SELECT DISTINCT
        patient_code AS arv_2
    FROM
        tracking_regime
    LEFT JOIN patient ON patient.id = id_patient
    WHERE
        (start_date BETWEEN @start_date AND @end_date
            OR end_date BETWEEN @start_date AND @end_date)
            AND category = 'regime_mother_treatment') arv ON arv_2 = bot.patient_code
        LEFT JOIN
    (SELECT DISTINCT
        health_id AS odk_1
    FROM
        openfn.odk_pregnancy_visit
    WHERE
        date_of_visit BETWEEN @start_date_last AND @end_date_last) odk1 ON odk_1 = bot.patient_code
        LEFT JOIN
    (SELECT DISTINCT
        health_id AS odk_2
    FROM
        openfn.odk_pregnancy_visit
    WHERE
        date_of_visit BETWEEN @start_date AND @end_date) odk2 ON odk_2 = bot.patient_code
        LEFT JOIN
    (SELECT DISTINCT
        patient_code AS ptme_1
    FROM
        tracking_pregnancy
    LEFT JOIN patient ON patient.id = id_patient_mother
    WHERE
        (ptme_enrollment_date BETWEEN @start_date_last AND @end_date_last)
            OR (actual_delivery_date BETWEEN @start_date_last AND @end_date_last)) ptme1 ON ptme_1 = bot.patient_code
        LEFT JOIN
    (SELECT DISTINCT
        patient_code AS ptme_2
    FROM
        tracking_pregnancy
    LEFT JOIN patient ON patient.id = id_patient_mother
    WHERE
        (ptme_enrollment_date BETWEEN @start_date AND @end_date)
            OR (actual_delivery_date BETWEEN @start_date AND @end_date)) ptme2 ON ptme_2 = bot.patient_code
        LEFT JOIN
    (SELECT DISTINCT
        patient_code AS ptme1_1
    FROM
        tracking_motherbasicinfo
    LEFT JOIN patient ON patient.id = id_patient
    WHERE
        PTME_date BETWEEN @start_date_last AND @end_date_last) pt1 ON ptme1_1 = bot.patient_code
        LEFT JOIN
    (SELECT DISTINCT
        patient_code AS ptme1_2
    FROM
        tracking_motherbasicinfo
    LEFT JOIN patient ON patient.id = id_patient
    WHERE
        PTME_date BETWEEN @start_date AND @end_date) pt2 ON ptme1_2 = bot.patient_code
        LEFT JOIN
    (SELECT 
        patient.patient_code AS mereenfant_1
    FROM
        tracking_motherbasicinfo
    LEFT JOIN patient ON patient.id = tracking_motherbasicinfo.id_patient
    LEFT JOIN testing_mereenfant ON CONCAT(testing_mereenfant.mother_city_code, '/', testing_mereenfant.mother_hospital_code, '/', testing_mereenfant.mother_code) = patient_code
    WHERE
        date BETWEEN @start_date_last AND @end_date_last
            AND patient_code IS NOT NULL
    GROUP BY patient_code) me1 ON mereenfant_1 = bot.patient_code
        LEFT JOIN
    (SELECT 
        patient.patient_code AS mereenfant_2
    FROM
        tracking_motherbasicinfo
    LEFT JOIN patient ON patient.id = tracking_motherbasicinfo.id_patient
    LEFT JOIN testing_mereenfant ON CONCAT(testing_mereenfant.mother_city_code, '/', testing_mereenfant.mother_hospital_code, '/', testing_mereenfant.mother_code) = patient_code
    WHERE
        date BETWEEN @start_date AND @end_date
            AND patient_code IS NOT NULL
    GROUP BY patient_code) me2 ON mereenfant_2 = bot.patient_code
WHERE
    (linked_to_id_patient = 0 or linked_to_id_patient is null)
        AND (club_q1 IS NOT NULL
        OR club_q2 IS NOT NULL
        OR quest_q1 IS NOT NULL
        OR quest_q2 IS NOT NULL
        OR arv_1 IS NOT NULL
        OR arv_2 IS NOT NULL
        OR odk_1 IS NOT NULL
        OR odk_2 IS NOT NULL
        OR ptme_1 IS NOT NULL
        OR ptme_2 IS NOT NULL
        OR ptme1_1 IS NOT NULL
        OR ptme1_2 IS NOT NULL
        OR mereenfant_1 IS NOT NULL
        OR mereenfant_2 IS NOT NULL)
GROUP BY bot.patient_code