select patient_id from(
select * from 
(
	select 	LISTA1.*,
		pid.identifier as NID,
		pn.given_name nome_inicial,
		pn.family_name apelido,
		concat(ifnull(pn.given_name,''),' ',ifnull(pn.middle_name,''),' ',ifnull(pn.family_name,'')) nome,
		pat.value as Telefone,
		pad3.state_province provincia,
		pad3.county_district distrito,
		pad3.address6 as localidade,
		pad3.address5 as bairro,
		pad3.address1 as Referencia,
		per.gender genero,round(datediff(date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)),per.birthdate)/365) idade_actual,
		per.birthdate data_do_nacimento,
		if(LISTA1.ordem in (1,11,12,13),1,0) tarv_elegivel,
		if(LISTA1.ordem in (2,3,4,5,6,7,8,9,10),'TARV','') estado_tarv,
		if(LISTA1.ordem in (1,2,3,4,5,6,7,8),TRUE,FALSE) gravida,
		if(LISTA1.ordem in (12,13),TRUE,FALSE) coinfectado,
		0 as a_faltar,
		levantamento.encounter_datetime ultimo_levantamento,
		levantamento.value_datetime proximo_levantamento,
		seguimento.encounter_datetime ultimo_seguimento,
		seguimento.value_datetime proximo_seguimento,
		if(levantamento.encounter_datetime is not null,levantamento.encounter_datetime,seguimento.encounter_datetime) data_ultima_consulta,
		if(levantamento.value_datetime is not null,levantamento.value_datetime,seguimento.value_datetime) data_proxima_consulta,
		g.gaac_identifier gaac,
		gf.family_identifier family,
		if(tuberculose.patient_id is not null,'Sim','') tb
		
		
from
(
	
	select 	consultaRecepcao.patient_id,			
			'Faltoso' as TIPO_PACIENTE,
			1 as ordem
			
	from 
	(
		
		-- Get Scheduled date on last Fila
		select maxFila.patient_id,maxFila.data_consulta,o.value_datetime  proximo_levantamento
		from
		(
			-- Get max fila for each patient
			Select  p.patient_id,max(e.encounter_datetime) data_consulta
			from    patient p 
					inner join encounter e on p.patient_id=e.patient_id 
			where 	e.voided=0 and p.voided=0 and e.encounter_type=18 and  
					e.encounter_datetime<=date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)) and e.location_id=:location 
			GROUP BY p.patient_id
		) maxFila 
		inner join obs o on o.person_id=maxFila.patient_id
		where 	o.obs_datetime=maxFila.data_consulta and o.concept_id=5096 and o.voided=0	
		
		UNION

		-- Get Max recepcao levantou ARV and max recepcao + 30 days as scheduled date
		Select 	p.patient_id,max(value_datetime) data_consulta,(max(value_datetime) + INTERVAL 30 day) proximo_levantamento
		from 	patient p
				inner join encounter e on p.patient_id=e.patient_id
				inner join obs o on e.encounter_id=o.encounter_id
		where 	p.voided=0 and e.voided=0 and o.voided=0 and e.encounter_type=52 and 
				o.concept_id=23866 and o.value_datetime is not null and 
				o.value_datetime<=date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)) and e.location_id=:location
		group by p.patient_id	
	) consultaRecepcao
	group by patient_id    
	having 	max(proximo_levantamento) between date_add(date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)), interval -21 day) and date_add(date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)), interval -15 day)	
	
	union	
	
	select 	consultaRecepcao.patient_id,			
			'Abandono' as TIPO_PACIENTE,
			4 as ordem
			
	from 
	(
		
		-- Get Scheduled date on last Fila
		select maxFila.patient_id,maxFila.data_consulta,o.value_datetime  proximo_levantamento
		from
		(
			-- Get max fila for each patient
			Select  p.patient_id,max(e.encounter_datetime) data_consulta
			from    patient p 
					inner join encounter e on p.patient_id=e.patient_id 
			where 	e.voided=0 and p.voided=0 and e.encounter_type=18 and  
					e.encounter_datetime<=date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)) and e.location_id=:location 
			GROUP BY p.patient_id
		) maxFila 
		inner join obs o on o.person_id=maxFila.patient_id
		where 	o.obs_datetime=maxFila.data_consulta and o.concept_id=5096 and o.voided=0	
		
		UNION

		-- Get Max recepcao levantou ARV and max recepcao + 30 days as scheduled date
		Select 	p.patient_id,max(value_datetime) data_consulta,(max(value_datetime) + INTERVAL 30 day) proximo_levantamento
		from 	patient p
				inner join encounter e on p.patient_id=e.patient_id
				inner join obs o on e.encounter_id=o.encounter_id
		where 	p.voided=0 and e.voided=0 and o.voided=0 and e.encounter_type=52 and 
				o.concept_id=23866 and o.value_datetime is not null and 
				o.value_datetime<=date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)) and e.location_id=:location
		group by p.patient_id

		union
		
		Select 	ultimavisita.patient_id,ultimavisita.encounter_datetime data_consulta,o.value_datetime proximo_levantamento
		from
			(	
				-- Get max Ficha Clinica for each patient
				select 	p.patient_id,max(encounter_datetime) as encounter_datetime
				from 	encounter e 
						inner join patient p on p.patient_id=e.patient_id 		
				where 	e.voided=0 and p.voided=0 and e.encounter_type in (6,9) and e.location_id=:location and e.encounter_datetime<=date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY))
				group by p.patient_id
			) ultimavisita
			inner join encounter e on e.patient_id=ultimavisita.patient_id
			inner join obs o on o.encounter_id=e.encounter_id and o.concept_id=1410 and o.voided=0			
		where  	e.encounter_datetime=ultimavisita.encounter_datetime and 
				e.encounter_type in (6,9) and e.location_id=:location
		
	) consultaRecepcao
	group by patient_id    
	having 	max(proximo_levantamento) between date_add(date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)), interval -127 day) and date_add(date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)), interval -121  day) or  
	 	max(proximo_levantamento) between date_add(date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)), interval -217 day) and date_add(date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)), interval -211  day) or 
	 	max(proximo_levantamento) between date_add(date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)), interval -307 day) and date_add(date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)), interval -301  day)	
	
	union
	
	select 	inicio_real.patient_id,
			'Abandono Inicio' as TIPO_PACIENTE,					
			if(	data_inicio BETWEEN Date_add(date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)), INTERVAL -99  day) AND Date_add(date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)), INTERVAL -93 day),2,3) as ordem
	from
		(	select patient_id,data_inicio
			from
			(	Select patient_id,min(data_inicio) data_inicio
				from
					(	
						/*Patients on ART who initiated the ARV DRUGS ART Regimen Start Date*/
				
						Select 	p.patient_id,min(e.encounter_datetime) data_inicio
						from 	patient p 
								inner join encounter e on p.patient_id=e.patient_id	
								inner join obs o on o.encounter_id=e.encounter_id
						where 	e.voided=0 and o.voided=0 and p.voided=0 and 
								e.encounter_type in (18,6,9) and o.concept_id=1255 and o.value_coded=1256 and 
								e.encounter_datetime<=date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)) and e.location_id=:location
						group by p.patient_id
				
						union
				
						/*Patients on ART who have art start date ART Start date*/
						Select 	p.patient_id,min(value_datetime) data_inicio
						from 	patient p
								inner join encounter e on p.patient_id=e.patient_id
								inner join obs o on e.encounter_id=o.encounter_id
						where 	p.voided=0 and e.voided=0 and o.voided=0 and e.encounter_type in (18,6,9,53) and 
								o.concept_id=1190 and o.value_datetime is not null and 
								o.value_datetime<=date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)) and e.location_id=:location
						group by p.patient_id

						union

						/*Patients enrolled in ART Program OpenMRS Program*/
						select 	pg.patient_id,min(date_enrolled) data_inicio
						from 	patient p inner join patient_program pg on p.patient_id=pg.patient_id
						where 	pg.voided=0 and p.voided=0 and program_id=2 and date_enrolled<=date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)) and location_id=:location
						group by pg.patient_id
						
						union
						
						
						/*Patients with first drugs pick up date set in Pharmacy First ART Start Date*/
						  SELECT 	e.patient_id, MIN(e.encounter_datetime) AS data_inicio 
						  FROM 		patient p
									inner join encounter e on p.patient_id=e.patient_id
						  WHERE		p.voided=0 and e.encounter_type=18 AND e.voided=0 and e.encounter_datetime<=date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)) and e.location_id=:location
						  GROUP BY 	p.patient_id
					  
						union
						
						/*Patients with first drugs pick up date set Recepcao Levantou ARV*/
						Select 	p.patient_id,min(value_datetime) data_inicio
						from 	patient p
								inner join encounter e on p.patient_id=e.patient_id
								inner join obs o on e.encounter_id=o.encounter_id
						where 	p.voided=0 and e.voided=0 and o.voided=0 and e.encounter_type=52 and 
								o.concept_id=23866 and o.value_datetime is not null and 
								o.value_datetime<=date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)) and e.location_id=:location
						group by p.patient_id					
						
					) inicio
					group by patient_id	
			)inicio1
			where 	data_inicio BETWEEN Date_add(date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)), INTERVAL -99  day) AND Date_add(date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)), INTERVAL -93 day) OR  
					data_inicio BETWEEN Date_add(date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)), INTERVAL -343  day) AND Date_add(date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)), INTERVAL -337 day) 		
				   
		) 	inicio_real 

		inner join 

		(		SELECT 	pg.patient_id 
				FROM   	patient p 
						INNER JOIN patient_program pg ON p.patient_id = pg.patient_id 
						INNER JOIN patient_state ps ON pg.patient_program_id = ps.patient_program_id 
				WHERE  	pg.voided = 0 
						AND ps.voided = 0 
						AND p.voided = 0 
						AND pg.program_id = 2 
						AND ps.state = 9 
						AND ps.end_date IS NULL 
						AND ps.start_date BETWEEN Date_add(date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)), INTERVAL -343  day ) AND date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY))
						AND location_id = :location 
					   
				UNION 
					  
				select 	patient_id
		
				from 
				(
					
					-- Get Scheduled date on last Fila
					select maxFila.patient_id,maxFila.data_consulta,o.value_datetime  proximo_levantamento
					from
					(
						-- Get max fila for each patient
						Select  p.patient_id,max(e.encounter_datetime) data_consulta
						from    patient p 
								inner join encounter e on p.patient_id=e.patient_id 
						where 	e.voided=0 and p.voided=0 and e.encounter_type=18 and  
								e.encounter_datetime<=date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)) and e.location_id=:location 
						GROUP BY p.patient_id
					) maxFila 
					inner join obs o on o.person_id=maxFila.patient_id
					where 	o.obs_datetime=maxFila.data_consulta and o.concept_id=5096 and o.voided=0	
					
					UNION

					-- Get Max recepcao levantou ARV and max recepcao + 30 days as scheduled date
					Select 	p.patient_id,max(value_datetime) data_consulta,(max(value_datetime) + INTERVAL 30 day) proximo_levantamento
					from 	patient p
							inner join encounter e on p.patient_id=e.patient_id
							inner join obs o on e.encounter_id=o.encounter_id
					where 	p.voided=0 and e.voided=0 and o.voided=0 and e.encounter_type=52 and 
							o.concept_id=23866 and o.value_datetime is not null and 
							o.value_datetime<=date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)) and e.location_id=:location
					group by p.patient_id	
				) consultaRecepcao
				group by patient_id    
				having 	(max(proximo_levantamento) + INTERVAL 28 day)<date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY))
				
		) abandono ON inicio_real.patient_id = abandono.patient_id	
	
) LISTA1
left join gaac_member gm on gm.member_id=LISTA1.patient_id and gm.voided=0
left join gaac g on gm.gaac_id=g.gaac_id and g.voided=0
left join gaac_family_member gfm on gfm.member_id=LISTA1.patient_id and gfm.voided=0
left join gaac_family gf on gf.family_id=gfm.family_id and gf.voided=0
left join 
(	

	-- Ficha de Seguimento Data de Inicio de Tratamento de TB
		SELECT 	p.patient_id 
		FROM 	patient p
				INNER JOIN encounter e ON e.patient_id = p.patient_id
				INNER JOIN obs o ON o.encounter_id = e.encounter_id
		WHERE 	p.voided = 0 AND e.voided = 0 AND o.voided = 0
				AND e.encounter_type IN (6,9)
				AND o.concept_id = 1113
				AND e.location_id = :location AND o.value_datetime between date_add(date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)), interval -8 month) and date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)) 
		GROUP BY p.patient_id
		
		
		UNION
		
		-- PATIENTS ON TB PROGRAM
		SELECT 	p.patient_id 
		FROM 	patient p
				INNER JOIN patient_program pg ON pg.patient_id = p.patient_id
		WHERE 	p.voided = 0 AND pg.voided = 0
				AND pg.program_id = 5 
				AND pg.date_enrolled BETWEEN date_add(date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)), interval -8 month) and date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)) AND pg.location_id = :location
		GROUP BY p.patient_id
		
		UNION
		
		-- Ficha Resumo Condicoes medicas importantes
		SELECT 	p.patient_id 
		FROM 	patient p
				INNER JOIN encounter e ON e.patient_id = p.patient_id
				INNER JOIN obs o ON o.encounter_id = e.encounter_id
		WHERE 	p.voided = 0 AND e.voided = 0 AND o.voided = 0
				AND e.encounter_type=53
				AND o.concept_id = 42
				AND o.value_coded=1065
				AND e.location_id = :location AND o.obs_datetime between date_add(date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)), interval -8 month) and date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)) 
		GROUP BY p.patient_id
		
		UNION
		
		-- Ficha Clinica Tratamento TB
		SELECT 	p.patient_id 
		FROM 	patient p
				INNER JOIN encounter e ON e.patient_id = p.patient_id
				INNER JOIN obs o ON o.encounter_id = e.encounter_id
		WHERE 	p.voided = 0 AND e.voided = 0 AND o.voided = 0
				AND e.encounter_type=6
				AND o.concept_id = 1268
				AND o.value_coded=1256
				AND e.location_id = :location AND o.obs_datetime between date_add(date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)), interval -8 month) and date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)) 
		GROUP BY p.patient_id		
			
			
) tuberculose on tuberculose.patient_id=LISTA1.patient_id
left join 
(	select pid1.*
	from patient_identifier pid1
	inner join 
		(
			select patient_id,min(patient_identifier_id) id 
			from patient_identifier
			where voided=0
			group by patient_id
		) pid2
	where pid1.patient_id=pid2.patient_id and pid1.patient_identifier_id=pid2.id
) pid on pid.patient_id=LISTA1.patient_id
left join 
(	select pn1.*
	from person_name pn1
	inner join 
		(
			select person_id,min(person_name_id) id 
			from person_name
			where voided=0
			group by person_id
		) pn2
	where pn1.person_id=pn2.person_id and pn1.person_name_id=pn2.id
) pn on pn.person_id=LISTA1.patient_id
left join 
(	select pad1.*
	from person_address pad1
	inner join 
		(
			select person_id,min(person_address_id) id 
			from person_address
			where voided=0
			group by person_id
		) pad2
	where pad1.person_id=pad2.person_id and pad1.person_address_id=pad2.id
) pad3 on pad3.person_id=LISTA1.patient_id
left join person_attribute pat on pat.person_id=LISTA1.patient_id and pat.person_attribute_type_id=9 and pat.value is not null and pat.value <> '' and pat.voided=0
left join person per on per.person_id=LISTA1.patient_id
left join 
(	
	
	select patient_id,encounter_datetime,max(value_datetime) value_datetime
	from 	
		(
			Select 	ultimavisita.patient_id,ultimavisita.encounter_datetime,o.value_datetime
			from
				(	select 	p.patient_id,max(encounter_datetime) as encounter_datetime
					from 	encounter e 
							inner join patient p on p.patient_id=e.patient_id 		
					where 	e.voided=0 and p.voided=0 and e.encounter_type=18 and e.location_id=:location and e.encounter_datetime<=date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY))
					group by p.patient_id
				) ultimavisita
				inner join encounter e on e.patient_id=ultimavisita.patient_id
				inner join obs o on o.encounter_id=e.encounter_id			
			where o.concept_id=5096 and o.voided=0 and e.encounter_datetime=ultimavisita.encounter_datetime and 
					e.encounter_type=18 and e.location_id=:location
					
			union
			
			Select 	p.patient_id,max(value_datetime) encounter_datetime,(max(value_datetime) + INTERVAL 30 day) value_datetime
			from 	patient p
					inner join encounter e on p.patient_id=e.patient_id
					inner join obs o on e.encounter_id=o.encounter_id
			where 	p.voided=0 and e.voided=0 and o.voided=0 and e.encounter_type=52 and 
					o.concept_id=23866 and o.value_datetime is not null and 
					o.value_datetime<=date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)) and e.location_id=:location
			group by p.patient_id
		) maxVisit
	group by patient_id		
) levantamento on levantamento.patient_id=LISTA1.patient_id
left join 
(	
	Select 	ultimavisita.patient_id,ultimavisita.encounter_datetime,o.value_datetime,e.location_id
	from
		(	select 	p.patient_id,max(encounter_datetime) as encounter_datetime
			from 	encounter e 
					inner join patient p on p.patient_id=e.patient_id 		
			where 	e.voided=0 and p.voided=0 and e.encounter_type in (6,9) and e.location_id=:location and e.encounter_datetime<=date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY))
			group by p.patient_id
		) ultimavisita
		inner join encounter e on e.patient_id=ultimavisita.patient_id
		left join obs o on o.encounter_id=e.encounter_id and o.concept_id=1410 and o.voided=0			
	where  	e.encounter_datetime=ultimavisita.encounter_datetime and 
			e.encounter_type in (6,9) and e.location_id=:location
) seguimento on seguimento.patient_id=LISTA1.patient_id
left join 
(
					
		/*Patients that are female and were marked as “PREGNANT” in the initial consultation or follow-up consultation 
			Patients that are female and have registered as pregnant in Ficha Clinica – Master Card between start and end date
			*/
			Select 	p.patient_id
			from 	patient p 
					inner join encounter e on p.patient_id=e.patient_id
					inner join obs o on e.encounter_id=o.encounter_id
			where 	p.voided=0 and e.voided=0 and o.voided=0 and concept_id=1982 and value_coded=1065 and 
					e.encounter_type in (5,6) and e.encounter_datetime  between date_add(date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)), interval -18 month) AND date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)) and e.location_id=:location

			union		

			/*Patients that are female and have “Number of weeks Pregnant” registered in the initial or follow-up consultation between start and end date*/		
			Select 	p.patient_id
			from 	patient p inner join encounter e on p.patient_id=e.patient_id
					inner join obs o on e.encounter_id=o.encounter_id
			where 	p.voided=0 and e.voided=0 and o.voided=0 and concept_id=1279 and 
					e.encounter_type in (5,6) and e.encounter_datetime between date_add(date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)), interval -18 month) AND date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)) and e.location_id=:location


			union		

			/*Patients that are female and have “Pregnancy Due Date” registered in the initial or follow-up consultation between start and end date*/		
			Select 	p.patient_id
			from 	patient p inner join encounter e on p.patient_id=e.patient_id
					inner join obs o on e.encounter_id=o.encounter_id
			where 	p.voided=0 and e.voided=0 and o.voided=0 and concept_id=1600 and 
					e.encounter_type in (5,6) and e.encounter_datetime between date_add(date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)), interval -18 month) AND date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)) and e.location_id=:location	
			
			union

			/*Patients that started ART for being B+ as specified in “CRITÉRIO PARA INÍCIO DE TARV” in the follow-up consultations (Ficha de Seguimento) between start and end date*/
			Select 	p.patient_id
			from 	patient p 
					inner join encounter e on p.patient_id=e.patient_id
					inner join obs o on e.encounter_id=o.encounter_id
			where 	p.voided=0 and e.voided=0 and o.voided=0 and concept_id=6334 and value_coded=6331 and 
					e.encounter_type in (5,6) and e.encounter_datetime between date_add(date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)), interval -18 month) AND date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)) and e.location_id=:location		
					
			union

			/*Patients that are female and enrolled on PTV/ETC program (patient program id =8) between start date and end date (patient program date_enrolled)*/		
			select 	pp.patient_id
			from 	patient_program pp 
			where 	pp.program_id=8 and pp.voided=0 and 
					pp.date_enrolled between date_add(date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)), interval -18 month) AND date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)) and pp.location_id=:location
					
			union		

			/*Patients that are female and have registered as pregnat in Ficha Resumo – Master Card between start and end date*/		
			Select 	p.patient_id
			from 	patient p 
					inner join encounter e on p.patient_id=e.patient_id
					inner join obs o on e.encounter_id=o.encounter_id
					inner join obs obsART on e.encounter_id=obsART.encounter_id
			where 	p.voided=0 and e.voided=0 and o.voided=0 and o.concept_id=1982 and o.value_coded=1065 and 
					e.encounter_type=53 and obsART.value_datetime between date_add(date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)), interval -18 month) AND date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)) and e.location_id=:location and 
					obsART.concept_id=1190 and obsART.voided=0
					
			union		

			/*Patients with date of last menstrual period between start and end date*/		
			Select 	p.patient_id
			from 	patient p inner join encounter e on p.patient_id=e.patient_id
					inner join obs o on e.encounter_id=o.encounter_id
			where 	p.voided=0 and e.voided=0 and o.voided=0 and o.concept_id=1465 and 
					e.encounter_type=6 and o.value_datetime between date_add(date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)), interval -18 month) AND date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)) and e.location_id=:location
					

			union
				
			/*Patients that are female and have the “Delivery date” (obs concept id 5599) registered in the initial or follow-up consultation where delivery date is between start and end date*/
		
			Select 	p.patient_id
			from 	patient p inner join encounter e on p.patient_id=e.patient_id
					inner join obs o on e.encounter_id=o.encounter_id
			where 	p.voided=0 and e.voided=0 and o.voided=0 and concept_id=5599 and 
					e.encounter_type in (5,6) and o.value_datetime between date_add(date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)), interval -18 month) AND date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)) and e.location_id=:location	
			
			union	
			
			/* 	Patients that are female and have registered as breastfeeding in follow up consultation between start and end date (encounter datetime).			
				Patients that are female and have registered as breastfeeding in Ficha Clinica between start and end date (encounter datetime).
			*/
			Select 	p.patient_id
			from 	patient p 
					inner join encounter e on p.patient_id=e.patient_id
					inner join obs o on e.encounter_id=o.encounter_id
			where 	p.voided=0 and e.voided=0 and o.voided=0 and concept_id=6332 and value_coded=1065 and 
					e.encounter_type=6 and e.encounter_datetime between date_add(date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)), interval -18 month) AND date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)) and e.location_id=:location
			union
			
			/* 	
				Patients that are female and have registered as breastfeeding in Ficha Resumo between start and end date (encounter datetime).
			*/
			Select 	p.patient_id
			from 	patient p 
					inner join encounter e on p.patient_id=e.patient_id
					inner join obs o on e.encounter_id=o.encounter_id
					inner join obs obsART on e.encounter_id=obsART.encounter_id
			where 	p.voided=0 and e.voided=0 and o.voided=0 and o.concept_id=6332 and o.value_coded=1065 and 
					e.encounter_type=53 and e.location_id=:location and 
					obsART.value_datetime between date_add(date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)), interval -18 month) AND date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)) and 
					obsART.concept_id=1190 and obsART.voided=0
			
			union

			/*Patients that are female and have started ART for being breastfeeding as specified in “CRITÉRIO PARA INICIO DE TRATAMENTO ARV” 
			with response equal to “LACTACAO” in the initial or follow-up consultation between start & end date */
			Select 	p.patient_id
			from 	patient p 
					inner join encounter e on p.patient_id=e.patient_id
					inner join obs o on e.encounter_id=o.encounter_id
			where 	p.voided=0 and e.voided=0 and o.voided=0 and concept_id=6334 and value_coded=6332 and 
					e.encounter_type in (5,6) and e.encounter_datetime between date_add(date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)), interval -18 month) AND date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)) and e.location_id=:location
			
					
			/*Patients enrolled in PTV(ETV) Program*/

			union		
					
			select 	pg.patient_id
			from 	patient p 
					inner join patient_program pg on p.patient_id=pg.patient_id
					inner join patient_state ps on pg.patient_program_id=ps.patient_program_id
			where 	pg.voided=0 and ps.voided=0 and p.voided=0 and 
					pg.program_id=8 and ps.state=27 and ps.end_date is null and 
					ps.start_date between date_add(date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)), interval -18 month) AND date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)) and location_id=:location
				
				
		union		
				
		SELECT 	pg.patient_id 
		FROM   	patient p 
				INNER JOIN patient_program pg ON p.patient_id = pg.patient_id 
				INNER JOIN patient_state ps ON pg.patient_program_id = ps.patient_program_id 
		WHERE  	pg.voided = 0 
				AND ps.voided = 0 
				AND p.voided = 0 
				AND pg.program_id = 2 
				AND ps.state IN (7, 8, 10) 
				AND ps.end_date IS NULL 
				AND ps.start_date BETWEEN  Date_add(date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)), INTERVAL -343 day) AND date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)) 
				AND location_id = :location
														 
		UNION 
		
		SELECT person_id 
		FROM   person 
		WHERE  dead = 1 AND voided = 0
		
		union
		
		select transferidopara.patient_id
		from 
		(
			select patient_id,max(data_transferidopara) data_transferidopara
			from 
			(
				/*Programa*/
				select 	maxEstado.patient_id,maxEstado.data_transferidopara 
				from 	( 
							select pg.patient_id,max(ps.start_date) data_transferidopara 
							from 	patient p 
									inner join patient_program pg on p.patient_id=pg.patient_id 
									inner join patient_state ps on pg.patient_program_id=ps.patient_program_id 
							where 	pg.voided=0 and ps.voided=0 and p.voided=0 and 
									pg.program_id=2 and ps.start_date<=date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)) and pg.location_id=:location 
							group by p.patient_id 
						) maxEstado 
						inner join patient_program pg2 on pg2.patient_id=maxEstado.patient_id 
						inner join patient_state ps2 on pg2.patient_program_id=ps2.patient_program_id 
				where 	pg2.voided=0 and ps2.voided=0 and pg2.program_id=2 and 
						ps2.start_date=maxEstado.data_transferidopara and pg2.location_id=:location and ps2.state=7

				union
				/*Ficha Resumo*/
				select maxEstado.patient_id,maxEstado.data_transferidopara
				from 
				(
					select 	p.patient_id,max(o.obs_datetime) data_transferidopara
					from	patient p
							inner join encounter e on p.patient_id=e.patient_id
							inner join obs o on o.encounter_id=e.encounter_id
					where 	e.voided=0 and p.voided=0 and o.obs_datetime<=date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)) and
							o.voided=0 and o.concept_id=6272 and e.encounter_type=53 and  e.location_id=:location
					group by p.patient_id
				) maxEstado
				inner join obs obsTrans on maxEstado.patient_id=obsTrans.person_id
				where 	obsTrans.voided=0 and obsTrans.concept_id=6272 and obsTrans.obs_datetime=maxEstado.data_transferidopara and 
						obsTrans.value_coded=1706 and obsTrans.location_id=:location

				union
				/*Ficha Clinica*/
				select maxEstado.patient_id,maxEstado.data_transferidopara
				from 
				(
					select 	p.patient_id,max(o.obs_datetime) data_transferidopara
					from	patient p
							inner join encounter e on p.patient_id=e.patient_id
							inner join obs o on o.encounter_id=e.encounter_id
					where 	e.voided=0 and p.voided=0 and o.obs_datetime<=date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)) and
							o.voided=0 and o.concept_id=6273 and e.encounter_type=6 and  e.location_id=:location
					group by p.patient_id
				) maxEstado
				inner join obs obsTrans on maxEstado.patient_id=obsTrans.person_id
				where 	obsTrans.voided=0 and obsTrans.concept_id=6273 and obsTrans.obs_datetime=maxEstado.data_transferidopara and 
						obsTrans.value_coded=1706 and obsTrans.location_id=:location	
				
			) transferido
			group by patient_id
		) transferidopara
		inner join 
		(
			select patient_id,max(encounter_datetime) encounter_datetime
			from 
			(
				select p.patient_id,max(e.encounter_datetime) encounter_datetime
				from 	patient p
						inner join encounter e on e.patient_id=p.patient_id
				where 	p.voided=0 and e.voided=0 and e.encounter_datetime<=date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)) and 
						e.location_id=:location and e.encounter_type in (18,6,9)
				group by p.patient_id

				union

				Select 	p.patient_id,max(value_datetime) encounter_datetime
				from 	patient p
						inner join encounter e on p.patient_id=e.patient_id
						inner join obs o on e.encounter_id=o.encounter_id
				where 	p.voided=0 and e.voided=0 and o.voided=0 and e.encounter_type=52 and 
						o.concept_id=23866 and o.value_datetime is not null and 
						o.value_datetime<=date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)) and e.location_id=:location
				group by p.patient_id
			) maxFilaRecepcao
			group by patient_id	
		) consultaOuARV on transferidopara.patient_id=consultaOuARV.patient_id
		where consultaOuARV.encounter_datetime<=transferidopara.data_transferidopara
		
	) gravidaLactante on gravidaLactante.patient_id=LISTA1.patient_id
where round(datediff(date(DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY)),per.birthdate)/365)>=15 and gravidaLactante.patient_id is null
				
) lista
group by patient_id
order by ordem,TIPO_PACIENTE,NID) patientListing