select
person.pcucodeperson AS รพสต,
concat(ctitle.titlename,person.fname,' ',person.lname)as ชื่อ,
person.idcard as บัตรประชาชน,
GetAgeYearNum(person.birth,CURRENT_DATE)as อายุ,
concat(person.hnomoi,' ','หมู่',' ',person.mumoi) as บ้านเลขที่,
village.villname AS หมู่บ้าน,
max(case when personchronic.chroniccode = 'I10' then 'HT' else null end) as HT,
max(case when personchronic.chroniccode between 'E10' and 'E15' then 'DM' else null end) as DM,
max(case when visitlabchcyhembmsse.labcode = 'CH99' then visitlabchcyhembmsse.labresultdigit else null end) as 'HbA1C',
max(case when visitlabchcyhembmsse.labcode = 'CH07' then visitlabchcyhembmsse.labresultdigit else null end) as 'Chol',
max(case when visitlabchcyhembmsse.labcode = 'CH25' then visitlabchcyhembmsse.labresultdigit else null end) as 'TG',
max(case when visitlabchcyhembmsse.labcode = 'CH17' then visitlabchcyhembmsse.labresultdigit else null end) as 'LDL',
max(case when visitlabchcyhembmsse.labcode = 'CH14' then visitlabchcyhembmsse.labresultdigit else null end) as 'HDL',
max(case when visitlabchcyhembmsse.labcode = 'CH04' then visitlabchcyhembmsse.labresultdigit else null end) as 'BUN',
max(case when visitlabchcyhembmsse.labcode = 'CH09' then visitlabchcyhembmsse.labresultdigit else null end) as 'Cre',
max(case when visitlabchcyhembmsse.labcode = 'CHeGFR' then visitlabchcyhembmsse.labresultdigit else null end) as 'eGFR',
max(case when visitlabchcyhembmsse.labcode = 'CH22' then visitlabchcyhembmsse.labresultdigit else null end) as 'Sodium',
max(case when visitlabchcyhembmsse.labcode = 'CH19' then visitlabchcyhembmsse.labresultdigit else null end) as 'Potusium',
max(case when visitlabchcyhembmsse.labcode = 'CH06' then visitlabchcyhembmsse.labresultdigit else null end) as 'Chloride',
max(case when visitlabchcyhembmsse.labcode = 'CH27' then visitlabchcyhembmsse.labresultdigit else null end) as 'Uric acid',
max(case when visitlabchcyhembmsse.labcode = 'MS01020201' then visitlabchcyhembmsse.labresultdigit else null end) as 'Micro alb',
max(case when visitlabsugarblood.typetesting = '2' then visitlabsugarblood.sugarnumdigit else null end) as 'FBS'
,MAX(concat(left(ncdservice.date_serv,4)+543,mid(ncdservice.date_serv,5,2),right(ncdservice.date_serv,2))) as 'วันที่คัดกรอง'
,case when ncdservice.examfoot = 0 then 'Normal'
when ncdservice.examfoot = 1 then 'Low Risk'
when ncdservice.examfoot = 2 then 'Modurate Risk'
when ncdservice.examfoot = 3 then 'Hight Rist'
when ncdservice.examfoot = 4 then 'Foot Ulcer'
when ncdservice.examfoot = 5 then 'Amputation' else null end as ผลการตรวจเท้า
,case when ncdservice.exameyel=1 then 'NO DR'
when ncdservice.exameyel=2 then 'Mild NPDR'
when ncdservice.exameyel=3 then 'Moderate NPDR'
when ncdservice.exameyel=4 then 'Severe NPDR'
when ncdservice.exameyel=5 then 'CSME' when ncdservice.exameyel=6 then 'PDR' else null end as ผลการตรวจจอประสาทตาด้านซ้าย
,case when ncdservice.exameyer=1 then 'NO DR'
when ncdservice.exameyer=2 then 'Mild NPDR'
when ncdservice.exameyer=3 then 'Moderate NPDR'
when ncdservice.exameyer=4 then 'Severe NPDR'
when ncdservice.exameyer=5 then 'CSME'
when ncdservice.exameyer=6 then 'PDR' else null end as ผลการตรวจจอประสาทตาด้านขวา
,case when ncdservice.eyeopthalmoscope in (1,3) then 'opthalmoscope'
when ncdservice.eyeopthalmoscope in (2,4) then 'fundus camera' else null end as วิธีตรวจจอประสาทตา
,case when ncdservice.eyeopthalmoscope in (1,2) then 'ปกติ'
when ncdservice.eyeopthalmoscope in (3,4) then 'ผิดปกติ' else null end as ผลการตรวจจอประสาทตา
from person
inner join ctitle on person.prename = ctitle.titlecode
inner join personchronic on person.pid=personchronic.pid
inner join cdisease on personchronic.chroniccode=cdisease.diseasecode
inner join house on person.hcode = house.hcode and person.pcucodeperson = house.pcucode
inner join visit ON person.pcucodeperson = visit.pcucodeperson
and person.pid = visit.pid
left outer JOIN ncdservice ON personchronic.pid = ncdservice.pid and ncdservice.date_serv between '20141001' and '20150930'
left join visitlabsugarblood ON visit.pcucode = visitlabsugarblood.pcucode
and visit.visitno = visitlabsugarblood.visitno
left outer join visitlabchcyhembmsse ON visit.pcucode = visitlabchcyhembmsse.pcucode
and visit.visitno = visitlabchcyhembmsse.visitno
inner join village on house.villcode = village.villcode and house.pcucode = village.pcucode
WHERE ((person.dischargetype is null) OR (person.dischargetype != '1'))
and SUBSTRING(house.villcode,7,2)<> '00' and (personchronic.chroniccode = 'I10' or personchronic.chroniccode between 'E10' and 'E15' )
GROUP BY person.pid
order by village.villno;