SELECT
cm.hn,
p.cid,
CONCAT(p.pname, p.fname, ' ', p.lname) AS pt_name,
CAST(DATEDIFF(CURDATE(), p.birthday) / 365 AS INTEGER) AS age_y,
c.name AS clinic_name,
cs.name AS clinic_subtype_name,
cm.nap_number,
cm.regdate,
cm.begin_year,
cm.dchdate,
cms.clinic_member_status_name,
h1.name AS refer_hos,
p.addrpart,
p.moopart,
t3.name AS tmb_name,
t2.name AS amp_name,
t1.name AS chw_name,
p.mobile_phone_number,
(SELECT vstdate
FROM ovst
WHERE hn = cm.hn
ORDER BY vn DESC
LIMIT 1) AS last_visit,
(SELECT spclty.name
FROM ovst
LEFT JOIN spclty ON spclty.spclty = ovst.spclty
WHERE hn = cm.hn
ORDER BY vn DESC
LIMIT 1) AS spclty_name,
(SELECT nextdate
FROM oapp
WHERE hn = cm.hn
AND visit_vn IS NULL
AND nextdate > CURDATE()
ORDER BY oapp_id
LIMIT 1) AS oapp_nextdate,
cm.ncd_icd10_list,
' ' AS icd10f43,
(SELECT regdate FROM ipt WHERE hn = cm.hn ORDER BY regdate DESC LIMIT 1) AS regdate_new,
(SELECT an FROM ipt WHERE hn = cm.hn ORDER BY regdate DESC LIMIT 1) AS an_new,
(SELECT spclty.name
FROM ipt
LEFT JOIN spclty ON spclty.spclty = ipt.spclty
WHERE hn = cm.hn
ORDER BY regdate DESC
LIMIT 1) AS s_ipt_new,
(SELECT regdate FROM ipt WHERE hn = cm.hn ORDER BY regdate DESC LIMIT 1,1) AS regdate_long,
(SELECT an FROM ipt WHERE hn = cm.hn ORDER BY regdate DESC LIMIT 1,1) AS an_long,
DATEDIFF(
(SELECT regdate FROM ipt WHERE hn = cm.hn ORDER BY regdate DESC LIMIT 1),
(SELECT regdate FROM ipt WHERE hn = cm.hn ORDER BY regdate DESC LIMIT 1,1)
) AS difdatereg,
(SELECT spclty.name
FROM ipt
LEFT JOIN spclty ON spclty.spclty = ipt.spclty
WHERE hn = cm.hn
ORDER BY regdate DESC
LIMIT 1,1) AS s_ipt_long,
(SELECT cm_vt_eval_type_name
FROM cm_vt_eval_type
WHERE cm_vt_eval_type_id = cm.cm_vt_eval_type_id) AS cm_vt,
(SELECT officer_activity_log_date
FROM officer_activity_log
WHERE log_field_change LIKE '%cm_vt_eval_type_id_new%'
AND officer_activity_log_table = 'clinicmember'
AND officer_activity_log_key_value = cm.clinicmember_id
ORDER BY officer_activity_log_id DESC
LIMIT 1) AS date_cm_vt_eval_type,
-- HbA1c (lab_items_code = 663)
(SELECT order_date
FROM lab_head
LEFT JOIN lab_order ON lab_order.lab_order_number = lab_head.lab_order_number
WHERE lab_head.hn = cm.hn AND lab_items_code = 663
ORDER BY lab_head.lab_order_number DESC
LIMIT 1) AS hba1c_date_new,
(SELECT lab_order_result
FROM lab_head
LEFT JOIN lab_order ON lab_order.lab_order_number = lab_head.lab_order_number
WHERE lab_head.hn = cm.hn AND lab_items_code = 663
ORDER BY lab_head.lab_order_number DESC
LIMIT 1) AS hba1c_result_new,
' ' AS hba1c_result_hospcode_new,
(SELECT order_date
FROM lab_head
LEFT JOIN lab_order ON lab_order.lab_order_number = lab_head.lab_order_number
WHERE lab_head.hn = cm.hn AND lab_items_code = 663
ORDER BY lab_head.lab_order_number DESC
LIMIT 1,1) AS hba1c_date_long,
(SELECT lab_order_result
FROM lab_head
LEFT JOIN lab_order ON lab_order.lab_order_number = lab_head.lab_order_number
WHERE lab_head.hn = cm.hn AND lab_items_code = 663
ORDER BY lab_head.lab_order_number DESC
LIMIT 1,1) AS hba1c_result_long,
' ' AS hba1c_result_hospcode_long,
-- FPG (lab_items_code in 229,136)
(SELECT order_date
FROM lab_head
LEFT JOIN lab_order ON lab_order.lab_order_number = lab_head.lab_order_number
WHERE lab_head.hn = cm.hn AND lab_items_code IN (229,136)
ORDER BY lab_head.lab_order_number DESC
LIMIT 1) AS fpg_date_new,
(SELECT lab_order_result
FROM lab_head
LEFT JOIN lab_order ON lab_order.lab_order_number = lab_head.lab_order_number
WHERE lab_head.hn = cm.hn AND lab_items_code IN (229,136)
ORDER BY lab_head.lab_order_number DESC
LIMIT 1) AS fpg_result_new,
' ' AS fpg_result_hospcode_new,
(SELECT order_date
FROM lab_head
LEFT JOIN lab_order ON lab_order.lab_order_number = lab_head.lab_order_number
WHERE lab_head.hn = cm.hn AND lab_items_code IN (229,136)
ORDER BY lab_head.lab_order_number DESC
LIMIT 1,1) AS fpg_date_long,
(SELECT lab_order_result
FROM lab_head
LEFT JOIN lab_order ON lab_order.lab_order_number = lab_head.lab_order_number
WHERE lab_head.hn = cm.hn AND lab_items_code IN (229,136)
ORDER BY lab_head.lab_order_number DESC
LIMIT 1,1) AS fpg_result_long,
' ' AS fpg_result_hospcode_long,
-- Eye / Foot screen
(SELECT screen_date
FROM cm_dm_cmbty_screen
WHERE hn = cm.hn AND do_eye_screen = 'Y'
ORDER BY cm_dm_cmbty_screen_id DESC
LIMIT 1) AS ratinadate,
(SELECT CONCAT('val: ', va_left_text, ' var: ', va_right_text)
FROM cm_dm_cmbty_eye_screen
WHERE cm_dm_cmbty_screen_id = (
SELECT cm_dm_cmbty_screen_id
FROM cm_dm_cmbty_screen
WHERE hn = cm.hn AND do_eye_screen = 'Y'
ORDER BY cm_dm_cmbty_screen_id DESC
LIMIT 1
)) AS ratinaresult,
' ' AS ratina_hospcode,
(SELECT screen_date
FROM cm_dm_cmbty_screen
WHERE hn = cm.hn AND do_foot_screen = 'Y'
ORDER BY cm_dm_cmbty_screen_id DESC
LIMIT 1) AS footdate,
(SELECT CONCAT('foot left :', r1.dmht_foot_screen_result_name,
' foot right :', r2.dmht_foot_screen_result_name)
FROM cm_dm_cmbty_foot_screen
LEFT JOIN dmht_foot_screen_result r1 ON r1.dmht_foot_screen_result_id = cm_dm_cmbty_foot_screen.dmht_foot_scr_res_left_id
LEFT JOIN dmht_foot_screen_result r2 ON r2.dmht_foot_screen_result_id = cm_dm_cmbty_foot_screen.dmht_foot_scr_res_right_id
WHERE cm_dm_cmbty_screen_id = (
/* NOTE: โค้ดต้นฉบับอ้าง do_eye_screen ที่นี่ อาจตั้งใจหรือเป็นบั๊ก */
SELECT cm_dm_cmbty_screen_id
FROM cm_dm_cmbty_screen
WHERE hn = cm.hn AND do_eye_screen = 'Y'
ORDER BY cm_dm_cmbty_screen_id DESC
LIMIT 1
)) AS foot_result,
' ' AS foot_hospcode,
-- BP ล่าสุดและก่อนหน้า
(SELECT vstdate
FROM opdscreen
WHERE hn = cm.hn AND bpd <> '' AND bps <> ''
ORDER BY vstdate DESC
LIMIT 1) AS bpdate_new,
' ' AS bp_hospcode_new,
(SELECT bps
FROM opdscreen
WHERE hn = cm.hn AND bpd <> '' AND bps <> ''
ORDER BY vstdate DESC
LIMIT 1) AS bps_new,
(SELECT bpd
FROM opdscreen
WHERE hn = cm.hn AND bpd <> '' AND bps <> ''
ORDER BY vstdate DESC
LIMIT 1) AS bpd_new,
(SELECT vstdate
FROM opdscreen
WHERE hn = cm.hn AND bpd <> '' AND bps <> ''
ORDER BY vstdate DESC
LIMIT 1,1) AS bpdate_long,
' ' AS bp_hospcode_long,
(SELECT bps
FROM opdscreen
WHERE hn = cm.hn AND bpd <> '' AND bps <> ''
ORDER BY vstdate DESC
LIMIT 1,1) AS bps_long,
(SELECT bpd
FROM opdscreen
WHERE hn = cm.hn AND bpd <> '' AND bps <> ''
ORDER BY vstdate DESC
LIMIT 1,1) AS bpd_long,
cm.dx_date,
' ' AS note,
-- รายการยา (VN ผู้ป่วยนอกล่าสุดที่ an = '' )
(SELECT GROUP_CONCAT(CONCAT(d.name_print, ' จำนวน ', op.qty) SEPARATOR ', ')
FROM opitemrece op
LEFT JOIN drugitems d ON d.icode = op.icode
WHERE op.vn = (
SELECT vn
FROM ovst
WHERE hn = cm.hn AND an = ''
ORDER BY vstdate DESC
LIMIT 1
)
AND op.icode IN (
'1000235','1001190','1000997','1001159','1000198',
'1001096','1001175','1000173','1001050','1000174','1001808'
)) AS drug_i,
(SELECT vstdate
FROM ovst
WHERE hn = cm.hn AND an = ''
ORDER BY vstdate DESC
LIMIT 1) AS date_ya
FROM clinicmember cm
LEFT JOIN patient p ON p.hn = cm.hn
LEFT JOIN clinic_subtype cs ON cs.clinic_subtype_id = cm.clinic_subtype_id
LEFT JOIN clinic c ON c.clinic = cm.clinic
LEFT JOIN clinic_member_status cms ON cms.clinic_member_status_id = cm.clinic_member_status_id
LEFT JOIN thaiaddress t1 ON t1.chwpart = p.chwpart AND t1.amppart = '00' AND t1.tmbpart = '00'
LEFT JOIN thaiaddress t2 ON t2.chwpart = p.chwpart AND t2.amppart = p.amppart AND t2.tmbpart = '00'
LEFT JOIN thaiaddress t3 ON t3.chwpart = p.chwpart AND t3.amppart = p.amppart AND t3.tmbpart = p.tmbpart
LEFT JOIN hospcode h1 ON h1.hospcode = cm.refer_register_from_hospcode
WHERE
DATE(cm.regdate) BETWEEN '2025-01-01' AND '2025-10-31'