SELECT
pc.pcucodeperson AS HOSPCODE,
pc.pid AS HN,
p.idcard AS CID,
ctitle.titlename AS `คำนำหน้าชื่อ`,
p.fname AS `ชื่อ`,
p.lname AS `นามสกุล`,
DATE_FORMAT( birth ,"%d-%m-%y") AS `วัน/เดือน/ปี เกิด`,
getAgeYearNum(birth,current_date) AS `อายุ`,
p.typelive,
p.dischargetype,
IF(pc.datefirstdiag IS NULL OR TRIM(pc.datefirstdiag)='' OR pc.datefirstdiag LIKE '0000-00-00%','',DATE_FORMAT(pc.datefirstdiag,'%Y%m%d')) AS DATE_DIAG,
IF(LENGTH(pc.chroniccode)>3,REPLACE(pc.chroniccode,'.',''),pc.chroniccode) AS `รหัสโรค`,
cdisease.diseasenamethai AS `ชื่อโรค`,
'' AS HOSP_DX,
IFNULL(pc.cup,'') AS HOSP_RX,
IF(pc.datedischart IS NULL OR TRIM(pc.datedischart)='' OR pc.datedischart LIKE '0000-00-00%','',DATE_FORMAT(pc.datedischart,'%Y%m%d')) AS DATE_DISCH,
pc.typedischart AS TYPEDISCH,
IF( pc.dateupdate IS NULL OR TRIM(pc.dateupdate)='' ,DATE_FORMAT(pc.datefirstdiag,'%Y%m%d%H%i%s'),DATE_FORMAT(pc.dateupdate,'%Y%m%d%H%i%s')) AS D_UPDATE
FROM
personchronic AS pc
LEFT JOIN person AS p ON pc.pid = p.pid AND pc.pcucodeperson = p.pcucodeperson
INNER JOIN ctitle ON p.prename = ctitle.titlecode
INNER JOIN cdisease ON pc.chroniccode = cdisease.diseasecode;