
SELECT q1.pcucode,q1.pid,q1.name as 'ชื่อ-สกุล',q1.Age as อายุ,q1.CID,q1.Address as 'ที่อยู่',q1.chroniccode as รหัสโรคเรื้อรัง,q1.organname as 'ภาวะแทรกซ้อน'
,q1.cup as รักษาที่ ,q1.visitdate,q1.FBS,q1.BPS,q1.BPD
,case when q1.complication is null and q1.fbs <125 then 'Green'
when q1.complication is null and q1.fbs between 125 and 154 then 'Yellow'
when q1.complication is null and q1.fbs between 155 and 182 then 'Orange'
when q1.complication is null and q1.fbs >182 then 'Red'
when q1.complication is not null and q1.fbs <125 then 'Black_G'
when q1.complication is not null and q1.fbs between 125 and 154 then 'Black_Y'
when q1.complication is not null and q1.fbs between 155 and 182 then 'Black_O'
when q1.complication is not null and q1.fbs >=183 then 'Black_R' else null end as Color
from (SELECT person.pcucodeperson as pcucode,person.pid,concat(ctitle.titlename,' ',person.fname,' ',person.lname) AS 'name'
,YEAR(FROM_DAYS(DATEDIFF(CURDATE(),person.birth))) as Age,person.idcard as CID,concat(house.hno,'  ','ม.',village.villno) AS Address
,GROUP_CONCAT(distinct personchronic.chroniccode) as chroniccode,cp.organcomplica as complication,corgan.organname
,personchronic.cup,if(GROUP_CONCAT(distinct personchronic.chroniccode) like'E1%,I1%' or GROUP_CONCAT(DISTINCT personchronic.chroniccode)like'I1%,E1%',1,0) as w1
,v.visitdate,v.fbs,v.bps,v.bpd
FROM person 
INNER JOIN house ON person.hcode = house.hcode AND person.pcucodeperson = house.pcucode
INNER JOIN village ON house.villcode = village.villcode AND house.pcucode = village.pcucode
LEFT JOIN ctitle ON person.prename = ctitle.titlecode
INNER JOIN personchronic ON person.pcucodeperson = personchronic.pcucodeperson AND person.pid = personchronic.pid
LEFT JOIN personchroniccomplica cp on personchronic.pid=cp.pid left join corgan on cp.organcomplica=corgan.organcode
INNER JOIN (SELECT visit.pcucode,visit.pid,visit.visitno,
concat(substr(visit.visitdate,9,2),'/',substr(visit.visitdate,6,2),'/',substr(visit.visitdate,1,4)+543) as visitdate
,visit.symptoms,visitdiag.diagcode
,case when visit.pressure2 IS NULL then substr(visit.pressure,1,INSTR(visit.pressure,'/')-1) else substr(visit.pressure2,1,INSTR(visit.pressure2,'/')-1) end as bps
,case when visit.pressure2 IS NULL then substr(visit.pressure,INSTR(visit.pressure,'/')+1,3) else substr(visit.pressure2,INSTR(visit.pressure2,'/')+1,3) end as bpd
,visitlabsugarblood.sugarnumdigit as fbs
FROM visit INNER JOIN visitdiag ON visit.pcucode = visitdiag.pcucode and visit.visitno = visitdiag.visitno
left JOIN visitlabsugarblood ON visit.pcucode = visitlabsugarblood.pcucode and visit.visitno = visitlabsugarblood.visitno
inner join (select visit.pid,Max(visit.visitno) as visitno from visit
INNER JOIN visitdiag ON visit.pcucode = visitdiag.pcucode and visit.visitno = visitdiag.visitno
where visitdiag.diagcode between 'E10' and 'E14.9' and visit.visitno not in (select vh.visitno from visithomehealthindividual vh)
and visit.visitdate between '2014-05-01' and '2014-05-31' 
group by visit.pid)vmax on visit.pid=vmax.pid and visit.visitno=vmax.visitno)v on personchronic.pid=v.pid
WHERE (personchronic.chroniccode between 'E10' and 'E14.9' or personchronic.chroniccode between 'I10' and 'I15')
and personchronic.typedischart ='03' and personchronic.cup =person.pcucodeperson
and right(village.villcode,2)!= '00' and person.typelive in (0,1,3)and person.dischargetype=9
GROUP BY person.pid order by personchronic.cup,village.villno,house.hno*1 )q1
where q1.chroniccode between 'E10' and 'E14.9' or q1.w1=1 group by q1.pid ;
