SELECT concat(ctitle.titlename,person.fname,' ',person.lname,' ','(',convert(person.pid using utf8),')')as pname,
person.birth AS pbirth,
GetAgeYearNum(person.birth,'2014-10-01')as age,
person.idcard AS pcid,
person.hnomoi AS hno,
person.mumoi AS mu,
person.typelive
FROM person INNER JOIN ctitle ON person.prename = ctitle.titlecode
INNER JOIN house ON person.pcucodeperson = house.pcucode AND person.hcode = house.hcode
INNER JOIN village ON house.pcucode = village.pcucode AND village.villcode = house.villcode
WHERE person.typelive IN ('1','3')
and GetAgeYearNum(person.birth,CURRENT_DATE) >= '15'
and SUBSTRING(house.villcode,7,2)<>'00'
and person.pid NOT IN (select ncd_person_ncd_screen.pid
FROM ncd_person_ncd_screen where ncd_person_ncd_screen.screen_date between '2014-10-01' and '2015-09-30' )
and CONCAT(person.pid,person.pcucodeperson) not in (SELECT CONCAT(persondeath.pid,persondeath.pcucodeperson)
FROM persondeath WHERE persondeath.deaddate <= CURRENT_DATE)
and concat(person.pid,person.pcucodeperson)NOT IN(select concat(personchronic.pid,personchronic.pcucodeperson)
from personchronic where personchronic.chroniccode BETWEEN 'E10' AND 'E15' or(personchronic.chroniccode BETWEEN 'I10' AND 'I15'))
order BY village.villno;