# Enfants EID avec followup, sans résultat positif

SELECT * FROM
(
SELECT * FROM view_patient_positive
UNION (SELECT
        `view_patient`.`id` AS `id`,
        `view_patient`.`patient_code` AS `patient_code`,
        `view_patient`.`id_hospital` AS `id_hospital`,
        `view_patient`.`linked_to_id_patient` AS `linked_to_id_patient`
    FROM
        (`view_patient`
        JOIN `tracking_infant` ON ((`view_patient`.`id` = `tracking_infant`.`id_patient`)))
    WHERE comments like '%Added via bulk upload%')
UNION (SELECT
        `view_patient`.`id` AS `id`,
        `view_patient`.`patient_code` AS `patient_code`,
        `view_patient`.`id_hospital` AS `id_hospital`,
        `view_patient`.`linked_to_id_patient` AS `linked_to_id_patient`
    FROM
        (`view_patient`
        JOIN `tracking_regime` ON ((`view_patient`.`id` = `tracking_regime`.`id_patient`)))
    WHERE category='regime_infant_treatment' and id_arv!=0 )

) a
left join (SELECT id_patient,first_name,last_name,dob,eid_pcr_program,is_dead,is_abandoned FROM tracking_infant) b on a.id=b.id_patient
left join (SELECT id_patient, id_arv,start_date from tracking_regime group by id_patient ) c on a.id=c.id_patient

# is infant eid
WHERE a.id in (
SELECT id_patient FROM testing_mereenfant
UNION SELECT id_patient FROM testing_result
UNION SELECT id_patient FROM testing_specimen
UNION SELECT id_patient FROM tracking_infant where eid_pcr_program=1)

and a.id not in (
(
select * from (select id_patient as id from tracking_infant where  eid_pcr_program=1 and (positive_pcr_1!='0000-00-00' or positive_pcr_2!='0000-00-00'  or comments like '%Added via bulk upload%')) a
union
select id_patient as id from testing_specimen where pcr_result=1
union  select id_patient as id from testing_result where result=1
union select id_patient as id from testing_mereenfant where id_patient in (select id_patient from tracking_infant where positive_pcr_1>0 or positive_pcr_2>0)
  )

)