{"name": "CUSTOM-EHP-REPORT-AI-ทะเบียนผู้ป่วยเบาหวาน ความดัน และผลคัดกรองย้อนหลัง 5 ปี", "title": "ทะเบียนผู้ป่วยเบาหวาน ความดัน และผลคัดกรองย้อนหลัง 5 ปี", "landscape": true, "variables": [{"name": "date1", "prompt": "ตั้งแต่วันที่ (date1)", "sample": "2026-01-01"}, {"name": "date2", "prompt": "ถึงวันที่ (date2)", "sample": "2026-12-31"}], "dataviews": [{"name": "Master", "master": true, "params": ["date1", "date2"], "sql": "SELECT p.hn AS hn, CONCAT(p.pname, p.fname, ' ', p.lname) AS ptname, p.cid AS cid, p.mobile_phone_number AS mobile, pt.name AS pttype_name, \n(SELECT os.fbs FROM opdscreen os WHERE os.hn = p.hn AND os.vstdate BETWEEN :date1 AND :date2 ORDER BY os.vstdate DESC LIMIT 1) as fbs_y0,\n(SELECT os.fbs FROM opdscreen os WHERE os.hn = p.hn AND YEAR(os.vstdate) = YEAR(:date2)-1 ORDER BY os.vstdate DESC LIMIT 1) as fbs_y1,\n(SELECT os.fbs FROM opdscreen os WHERE os.hn = p.hn AND YEAR(os.vstdate) = YEAR(:date2)-2 ORDER BY os.vstdate DESC LIMIT 1) as fbs_y2,\n(SELECT os.fbs FROM opdscreen os WHERE os.hn = p.hn AND YEAR(os.vstdate) = YEAR(:date2)-3 ORDER BY os.vstdate DESC LIMIT 1) as fbs_y3,\n(SELECT os.fbs FROM opdscreen os WHERE os.hn = p.hn AND YEAR(os.vstdate) = YEAR(:date2)-4 ORDER BY os.vstdate DESC LIMIT 1) as fbs_y4,\n(SELECT os.bps FROM opdscreen os WHERE os.hn = p.hn AND os.vstdate BETWEEN :date1 AND :date2 ORDER BY os.vstdate DESC LIMIT 1) as bps_y0,\n(SELECT os.bps FROM opdscreen os WHERE os.hn = p.hn AND YEAR(os.vstdate) = YEAR(:date2)-1 ORDER BY os.vstdate DESC LIMIT 1) as bps_y1,\n(SELECT os.bps FROM opdscreen os WHERE os.hn = p.hn AND YEAR(os.vstdate) = YEAR(:date2)-2 ORDER BY os.vstdate DESC LIMIT 1) as bps_y2,\n(SELECT os.bps FROM opdscreen os WHERE os.hn = p.hn AND YEAR(os.vstdate) = YEAR(:date2)-3 ORDER BY os.vstdate DESC LIMIT 1) as bps_y3,\n(SELECT os.bps FROM opdscreen os WHERE os.hn = p.hn AND YEAR(os.vstdate) = YEAR(:date2)-4 ORDER BY os.vstdate DESC LIMIT 1) as bps_y4\nFROM patient p \nLEFT JOIN pttype pt ON pt.pttype = p.pttype \nLEFT JOIN clinicmember cm ON cm.hn = p.hn \nWHERE cm.clinic IN ('001', '002') \nORDER BY p.hn"}], "columns": [{"field": "hn", "caption": "HN", "align": "taLeftJustified", "memo": false, "width": 20.0}, {"field": "ptname", "caption": "ชื่อ-นามสกุล", "align": "taLeftJustified", "memo": true, "width": 35.0}, {"field": "cid", "caption": "เลขบัตรประชาชน", "align": "taLeftJustified", "memo": false, "width": 25.0}, {"field": "mobile", "caption": "เบอร์โทร", "align": "taLeftJustified", "memo": false, "width": 25.0}, {"field": "pttype_name", "caption": "สิทธิ", "align": "taLeftJustified", "memo": false, "width": 20.0}, {"field": "fbs_y0", "caption": "FBS ปีนี้", "align": "taCentered", "memo": false, "width": 15.0}, {"field": "fbs_y1", "caption": "FBS -1", "align": "taCentered", "memo": false, "width": 15.0}, {"field": "fbs_y2", "caption": "FBS -2", "align": "taCentered", "memo": false, "width": 15.0}, {"field": "fbs_y3", "caption": "FBS -3", "align": "taCentered", "memo": false, "width": 15.0}, {"field": "fbs_y4", "caption": "FBS -4", "align": "taCentered", "memo": false, "width": 15.0}, {"field": "bps_y0", "caption": "BPS ปีนี้", "align": "taCentered", "memo": false, "width": 15.0}, {"field": "bps_y1", "caption": "BPS -1", "align": "taCentered", "memo": false, "width": 15.0}, {"field": "bps_y2", "caption": "BPS -2", "align": "taCentered", "memo": false, "width": 15.0}, {"field": "bps_y3", "caption": "BPS -3", "align": "taCentered", "memo": false, "width": 15.0}, {"field": "bps_y4", "caption": "BPS -4", "align": "taCentered", "memo": false}], "highlight": [], "header_sql": "select h.hospitalcode,concat(o.hosptype,o.name) as hospital_name,\r\nconcat(' ต. ',t.name,' อ. ',t1.name,' จ. ',t2.name) as address\r\nfrom  opdconfig h\r\nleft outer join hospcode o on h.hospitalcode=o.hospcode\r\nleft outer join thaiaddress t on t.tmbpart=o.tmbpart and t.amppart=o.amppart  and t.chwpart=o.chwpart and t.codetype='3'\r\nleft outer join thaiaddress t1 on t1.amppart=o.amppart and t1.chwpart=o.chwpart and t1.codetype='2'\r\nleft outer join thaiaddress t2 on t2.chwpart=o.chwpart and t2.codetype='1'\r\n", "footer": {"page_align": "center"}}

{"name": "CUSTOM-EHP-REPORT-AI-ทะเบียนผู้ป่วยเบาหวาน ความดัน และผลคัดกรองย้อนหลัง 5 ปี", "title": "ทะเบียนผู้ป่วยเบาหวาน ความดัน และผลคัดกรองย้อนหลัง 5 ปี", "landscape": true, "variables": [{"name": "date1", "prompt": "ตั้งแต่วันที่ (date1)", "sample": "2026-01-01"}, {"name": "date2", "prompt": "ถึงวันที่ (date2)", "sample": "2026-12-31"}], "dataviews": [{"name": "Master", "master": true, "params": ["date2"], "sql": "SELECT p.hn AS hn, CONCAT(p.pname, p.fname, ' ', p.lname) AS ptname, p.cid AS cid, p.mobile_phone_number AS mobile, pt.name AS pttype_name, \n(SELECT os.fbs FROM opdscreen os WHERE os.hn = p.hn AND YEAR(os.vstdate) = YEAR(:date2) ORDER BY os.vstdate DESC LIMIT 1) as fbs_y0,\n(SELECT os.fbs FROM opdscreen os WHERE os.hn = p.hn AND YEAR(os.vstdate) = YEAR(:date2)-1 ORDER BY os.vstdate DESC LIMIT 1) as fbs_y1,\n(SELECT os.fbs FROM opdscreen os WHERE os.hn = p.hn AND YEAR(os.vstdate) = YEAR(:date2)-2 ORDER BY os.vstdate DESC LIMIT 1) as fbs_y2,\n(SELECT os.fbs FROM opdscreen os WHERE os.hn = p.hn AND YEAR(os.vstdate) = YEAR(:date2)-3 ORDER BY os.vstdate DESC LIMIT 1) as fbs_y3,\n(SELECT os.fbs FROM opdscreen os WHERE os.hn = p.hn AND YEAR(os.vstdate) = YEAR(:date2)-4 ORDER BY os.vstdate DESC LIMIT 1) as fbs_y4,\n(SELECT os.bps FROM opdscreen os WHERE os.hn = p.hn AND YEAR(os.vstdate) = YEAR(:date2) ORDER BY os.vstdate DESC LIMIT 1) as bps_y0,\n(SELECT os.bps FROM opdscreen os WHERE os.hn = p.hn AND YEAR(os.vstdate) = YEAR(:date2)-1 ORDER BY os.vstdate DESC LIMIT 1) as bps_y1,\n(SELECT os.bps FROM opdscreen os WHERE os.hn = p.hn AND YEAR(os.vstdate) = YEAR(:date2)-2 ORDER BY os.vstdate DESC LIMIT 1) as bps_y2,\n(SELECT os.bps FROM opdscreen os WHERE os.hn = p.hn AND YEAR(os.vstdate) = YEAR(:date2)-3 ORDER BY os.vstdate DESC LIMIT 1) as bps_y3,\n(SELECT os.bps FROM opdscreen os WHERE os.hn = p.hn AND YEAR(os.vstdate) = YEAR(:date2)-4 ORDER BY os.vstdate DESC LIMIT 1) as bps_y4\nFROM patient p \nLEFT JOIN pttype pt ON pt.pttype = p.pttype \nLEFT JOIN clinicmember cm ON cm.hn = p.hn \nWHERE cm.clinic IN ('001', '002') \nORDER BY p.hn"}], "columns": [{"field": "hn", "caption": "HN", "align": "taLeftJustified", "memo": false, "width": 20.0}, {"field": "ptname", "caption": "ชื่อ-นามสกุล", "align": "taLeftJustified", "memo": true, "width": 35.0}, {"field": "cid", "caption": "เลขบัตรประชาชน", "align": "taLeftJustified", "memo": false, "width": 25.0}, {"field": "mobile", "caption": "เบอร์โทร", "align": "taLeftJustified", "memo": false, "width": 25.0}, {"field": "pttype_name", "caption": "สิทธิ", "align": "taLeftJustified", "memo": false, "width": 20.0}, {"field": "fbs_y0", "caption": "FBS ปีนี้", "align": "taCentered", "memo": false, "width": 15.0}, {"field": "fbs_y1", "caption": "FBS -1", "align": "taCentered", "memo": false, "width": 15.0}, {"field": "fbs_y2", "caption": "FBS -2", "align": "taCentered", "memo": false, "width": 15.0}, {"field": "fbs_y3", "caption": "FBS -3", "align": "taCentered", "memo": false, "width": 15.0}, {"field": "fbs_y4", "caption": "FBS -4", "align": "taCentered", "memo": false, "width": 15.0}, {"field": "bps_y0", "caption": "BPS ปีนี้", "align": "taCentered", "memo": false, "width": 15.0}, {"field": "bps_y1", "caption": "BPS -1", "align": "taCentered", "memo": false, "width": 15.0}, {"field": "bps_y2", "caption": "BPS -2", "align": "taCentered", "memo": false, "width": 15.0}, {"field": "bps_y3", "caption": "BPS -3", "align": "taCentered", "memo": false, "width": 15.0}, {"field": "bps_y4", "caption": "BPS -4", "align": "taCentered", "memo": false}], "highlight": [], "header_sql": "select h.hospitalcode,concat(o.hosptype,o.name) as hospital_name,\r\nconcat(' ต. ',t.name,' อ. ',t1.name,' จ. ',t2.name) as address\r\nfrom  opdconfig h\r\nleft outer join hospcode o on h.hospitalcode=o.hospcode\r\nleft outer join thaiaddress t on t.tmbpart=o.tmbpart and t.amppart=o.amppart  and t.chwpart=o.chwpart and t.codetype='3'\r\nleft outer join thaiaddress t1 on t1.amppart=o.amppart and t1.chwpart=o.chwpart and t1.codetype='2'\r\nleft outer join thaiaddress t2 on t2.chwpart=o.chwpart and t2.codetype='1'\r\n", "footer": {"page_align": "center"}}