USE caris_db;
SET @start_date= '2019-01-01';
SET @end_date='2019-03-31';
SELECT 
    patient.patient_code,
    infant_dob,
    date as date_de_prelevement,
    lookup_testing_birth_location.name AS accouchement,
    lookup_testing_specimen_result.name as result,
    testing_specimen.pcr_result_date,
    infant_birth_location_describe,
    lookup_testing_nutrition.name as nutrition,
    response.name as arv_before_after,
    cotrim.name as on_cotrim,
    infant_on_cotrimoxazole_date,
    infant_why_cotrimoxazole_not_given_describe,
    lookup_testing_infant_arv_prophylaxis.name as arv_prophylaxis,
    infant_prophylaxis_arv_regime_describe,
    concat(mother_city_code,'/',mother_hospital_code,'/',mother_code) as code_mere,
    lookup_testing_response.name as 'Est-ce que la femme a été placée sous ARV?',
    mother_on_arv_start_date,
    if(x.patient_code is not null, 'yes','no') as 'on hivhaiti?',
    if(club_patient.id_patient is not null,'yes','no')as 'enrolled in club',
    date_de_creation_caris,
    is_PTME,
    PTME_date
FROM
    testing_mereenfant
        LEFT JOIN
    patient ON patient.id = testing_mereenfant.id_patient
        LEFT JOIN
    lookup_testing_birth_location ON lookup_testing_birth_location.id = infant_birth_location
        LEFT JOIN
    lookup_testing_nutrition ON lookup_testing_nutrition.id = infant_nutrition
    left join
    lookup_testing_response on lookup_testing_response.id=mother_on_arv
    left join lookup_response cotrim on cotrim.id=infant_on_cotrimoxazole
    left join lookup_testing_infant_arv_prophylaxis on lookup_testing_infant_arv_prophylaxis.id=infant_prophylaxis_arv_regime
	left join (
    select patient_code, patient.id, is_PTME, tracking_motherbasicinfo.created_at as date_de_creation_caris, PTME_date from tracking_motherbasicinfo
    left join patient on patient.id=id_patient
    )x on x.patient_code = concat(mother_city_code,'/',mother_hospital_code,'/',mother_code)
    left join
    club_patient on club_patient.id_patient=x.id
    left join testing_specimen on testing_specimen.id_patient=testing_mereenfant.id_patient
    left join lookup_testing_specimen_result on testing_specimen.pcr_result=lookup_testing_specimen_result.id
    left join lookup_response as response on response.id = testing_mereenfant.infant_prophylaxis_arv_regime_before_or_after
    WHERE
    date BETWEEN @start_date AND @end_date