ข้อมูลคนไข้ เบาหวาน ความดัน BMI 3 ครั้งหลังสุด
select o.hn,c.clinic,(group_concat(DISTINCT ‘(‘,o.bmi,’/’,o.vstdate,’)’))as AA,
(select concat(‘(‘,o2.bmi,’)(‘,o2.vstdate,’)’) from opdscreen o2
where o2.hn=o.hn and o2.vstdate between min(o.vstdate) and max(o.vstdate)
order by concat(year(o2.vstdate),month(o2.vstdate),day(o2.vstdate)) desc limit 0,1)as “หลังสุด”,
(select concat(‘(‘,o3.bmi,’)(‘,o3.vstdate,’)’) from opdscreen o3
where o3.hn=o.hn and o3.vstdate between min(o.vstdate) and max(o.vstdate)
order by concat(year(o3.vstdate),month(o3.vstdate),day(o3.vstdate)) desc limit 1,1)as “ก่อนหลังสุด”,
(select concat(‘(‘,o4.bmi,’)(‘,o4.vstdate,’)’) from opdscreen o4
where o4.hn=o.hn and o4.vstdate between min(o.vstdate) and max(o.vstdate)
order by concat(year(o4.vstdate),month(o4.vstdate),day(o4.vstdate)) desc limit 2,1)as “ก่อนก่อนครั้งหลังสุด”
from opdscreen o
left outer join clinicmember c on c.hn=o.hn
where o.vstdate between “2015-10-01” and “2016-09-31” and c.clinic in (“001″,”002”)
and c.clinic is not null
group by o.hn
order by year(o.vstdate),month(o.vstdate),date(o.vstdate) desc
Centos7+Hosxp_pcu
เปิด port 22 : vi /etc/ssh/sshd_config เอา # ออกหน้า Port 22
ปิดการใช้งาน SELINUX : vi /etc/selinux/config ส่วนของ SELINUX=disabled
เปลี่ยน hostname : vi /etc/hostname
ตรวจสอบ network Card : nmclid
ตรวจสอบ Mysql : etc/init.d/mysql status
สร้างตาราง เพื่อส่งออก 43 แฟ้มในกรณีที่ Error
CREATE table f43_repo_list (table_name VARCHAR(200) not null PRIMARY key);
ตารางใน hosxp เกี่ยวกับ Risk Management
หน่วยงาน = select * from hospital_department
ความรุนแรง = select * from risk_level
เชื่อมโยงโปรแกรม = select * from risk_relation_program
รายงาน = select * from risk_report
GFR แยก 5 state
select p.sex,p.age_y,o.hba1c,o.bw,o.bmi,round(o.creatinine_kidney_percent,2),o.creatinine,
round(
(case when p.sex=1 then (((140-p.age_y)*o.bw)/(72*o.creatinine))
else (((140-p.age_y)*o.bw*0.85)/(72*o.creatinine)) end),2)as GFR_CKD_EPI
,
round(if(p.sex=1,(((140-p.age_y)*o.bw)/(72*o.creatinine)),(((140-p.age_y)*o.bw*0.85)/(72*o.creatinine))),2)as K,
(case when if(p.sex=1,(((140-p.age_y)*o.bw)/(72*o.creatinine)),
((140-p.age_y)*o.bw*0.85)/(72*o.creatinine))>89 then ‘ STATE 1’
when if(p.sex=1,(((140-p.age_y)*o.bw)/(72*o.creatinine)),
((140-p.age_y)*o.bw*0.85)/(72*o.creatinine)) between ‘60.00’ and ‘89.999’ then ‘ STATE 2’
when if(p.sex=1,(((140-p.age_y)*o.bw)/(72*o.creatinine)),
((140-p.age_y)*o.bw*0.85)/(72*o.creatinine)) between ‘30.00’ and ‘59.999’ then ‘ STATE 3’
when if(p.sex=1,(((140-p.age_y)*o.bw)/(72*o.creatinine)) ,
((140-p.age_y)*o.bw*0.85)/(72*o.creatinine)) between ‘15.00’ and ‘29.99’ then ‘ STATE 4’
when if(p.sex=1,(((140-p.age_y)*o.bw)/(72*o.creatinine)),
((140-p.age_y)*o.bw*0.85)/(72*o.creatinine)) between ‘0.01’ and ‘14.999’ then ‘ STATE 5’
else ‘error massage’ end)as KK,
c.clinic,concat(v.pdx,’ ‘,v.dx0,’ ‘,v.dx1,’ ‘,v.dx2,’ ‘,v.dx3,’ ‘,v.dx4,’ ‘,v.dx5)as diag,ROUND(o.bmi,2)as BMI,
(case when o.creatinine_kidney_percent>=90 then ‘1’
when o.creatinine_kidney_percent between ‘60.00’ and ‘89.999’ then ‘2’
when o.creatinine_kidney_percent between ‘30.00’ and ‘59.999’ then ‘3’
when o.creatinine_kidney_percent between ‘15.00’ and ‘29.999’ then ‘4’
when o.creatinine_kidney_percent<=15 then ‘5’
else ‘error massage’ end)as GFR,
o.creatinine_kidney_percent,o.vn,o.hn,o.vstdate,o.vsttime,o.cc,o.symptom,
p.cid,concat(p.pname,p.fname,’ ‘,p.lname)as name,(case when p.sex=’1’ then ‘ชาย’ else ‘หญิง’ end) as sex,
p.age_y
from opdscreen o
left join person p on p.patient_hn=o.hn
left join vn_stat v on v.vn=o.vn
left join clinicmember c on c.hn=o.hn
where (((o.vstdate between ‘2015-10-01’ and ‘2015-12-31’)
and (o.creatinine_kidney_percent is not null and o.creatinine_kidney_percent<>”0.00″) and o.creatinine<>0.00)
and ((v.pdx between”e100″ and “e149″
or v.dx0 between”e100” and “e149″
or v.dx1 between”e100” and “e149″
or v.dx2 between”e100” and “e149″
or v.dx3 between”e100” and “e149″
or v.dx4 between”e100” and “e149″
or v.dx5 between”e100” and “e149″) or
(v.pdx between”i10” and “i152″
or v.dx0 between”i10” and “i152″
or v.dx1 between”i10” and “i152″
or v.dx2 between”i10” and “i152″
or v.dx3 between”i10” and “i152″
or v.dx4 between”i10” and “i152″
or v.dx5 between”i10” and “i152”)))
and o.hba1c is not null
group by o.hn desc
order by KK,o.hba1c asc
คนไข้ความดัน ควบคุมระดับ ได้ดี 2 ครั้งหลังสุด คัดกรองจากคลิคนิค
set @d1 = ‘2015-05-31’;
set @d2 = ‘2016-10-01’;
SELECT * FROM
(select p.patient_hn as hn, p.cid, concat(p.pname, p.fname ,” “, p.lname) as pt_name
, TIMESTAMPDIFF(year,p.birthdate,now()) as age,
p.nationality,p.person_discharge_id,p.death,p.death_date,p.house_regist_type_id,m.clinic_member_status_id
,if(p.sex =1,”ชาย”,”หญิง”) as sex ,h.address, v1.village_moo, v1.village_name
, “NOT PASS” as group1,p.patient_hn,ps2.icd10
,(select if(bps between ‘1’ and ‘140’ and bpd between ‘1’ and ’90’,1,0) as poin
from opdscreen o1 WHERE o1.hn = v.hn and vstdate between @d1 and @d2
ORDER BY vstdate DESC limit 0,1) as dtx1
,(select vstdate
from opdscreen o1d WHERE o1d.hn = v.hn and vstdate between @d1 and @d2
ORDER BY vstdate DESC limit 0,1) as dtx1d
,(select bps
from opdscreen o1a WHERE o1a.hn = v.hn and vstdate between @d1 and @d2
ORDER BY vstdate DESC limit 0,1) as dtx1a
,(select bpd
from opdscreen o1b WHERE o1b.hn = v.hn and vstdate between @d1 and @d2
ORDER BY vstdate DESC limit 0,1) as dtx1b
,(select if(bps between ‘1’ and ‘140’ and bpd between ‘1’ and ’90’,1,0) as poin
from opdscreen o2 WHERE o2.hn = v.hn and vstdate between @d1 and @d2
ORDER BY vstdate DESC limit 1,1) as dtx2
,(select vstdate
from opdscreen o2d WHERE o2d.hn = v.hn and vstdate between @d1 and @d2
ORDER BY vstdate DESC limit 1,1) as dtx2d
,(select bps
from opdscreen o2a WHERE o2a.hn = v.hn and vstdate between @d1 and @d2
ORDER BY vstdate DESC limit 1,1) as dtx2a
,(select bpd
from opdscreen o2b WHERE o2b.hn = v.hn and vstdate between @d1 and @d2
ORDER BY vstdate DESC limit 1,1) as dtx2b
from vn_stat v
left join person p on p.patient_hn = v.hn
LEFT JOIN house h on h.house_id = p.house_id
LEFT JOIN village v1 on v1.village_id = p.village_id
left join clinic_persist_icd ps on ps.hn=v.hn
left join clinic_persist_icd ps2 on ps2.hn=ps.hn and ps2.icd10 between ‘e10′ and’e149’
left join clinicmember m on m.hn=ps.hn
where (v.vstdate between @d1 and @d2)
and /* v.hn in (select hn from clinicmember where clinic=”002″) */
((ps.icd10 between ‘i10’ and ‘i159′ or ps.icd10=’i674′ or ps.icd10=’H350′)
and (p.death=’N’) and (p.house_regist_type_id in (‘1′,’3′))and (m.clinic_member_status_id =’3’))
and (ps2.icd10 is null)
ORDER BY v.hn ) t1
WHERE t1.dtx1 =1 and t1.dtx2= 1
GROUP BY t1.hn
ดึงรายงาน REFER
**************REFER*******************
————–OUT———–ส่งออกจาก รพ.—————-
++++HOSXP++++++++++++
select month(refer_date),YEAR(refer_date),count(*) from referout
where refer_date between ‘2015-12-01’ and ‘2017-09-31’
group by month(refer_date),YEAR(refer_date)
ORDER BY year(refer_date),month(refer_date)
++++THAI REFER+++++++++
select month(refer_date),YEAR(refer_date),count(*) from referout
where refer_date between ‘2015-12-01’ and ‘2017-09-31’
group by month(refer_date),YEAR(refer_date)
ORDER BY year(refer_date),month(refer_date)
————–IN————–รับเข้าจาก รพ.สต.——————
++++HOSXP++++++++++++
select month(refer_date),YEAR(refer_date),count(*) from referin
where (refer_date between ‘2015-12-01’ and ‘2017-09-31’)
and (refer_hospcode in (‘00277′,’03139′,’03140′,’03141′,’03142′,’03144′,’03145′,’03146′,’03147′,’03148′,’13852’) )
group by month(refer_date),YEAR(refer_date)
ORDER BY year(refer_date),month(refer_date)
+++++++++THAI REFER+++++++++
select month(refer_date),YEAR(refer_date),count(*) from referback
where refer_hospcode in (‘00277′,’03139′,’03140′,’03141′,’03142′,’03144′,’03145′,’03146′,’03147′,’03148′,’13852’)
and (refer_date between ‘2015-12-01’ and ‘2017-09-31’)
group by month(refer_date),YEAR(refer_date)
ORDER BY year(refer_date),month(refer_date)
————-BACK———–รับเข้าจาก รพ.อื่นๆ ที่ไม่ใช่ รพ.สต.——–
++++HOSXP++++++++++++
select month(refer_date),YEAR(refer_date),count(*) from referin
where (refer_date between ‘2015-12-01’ and ‘2017-09-31’)
and (refer_hospcode not in (‘00277′,’03139′,’03140′,’03141′,’03142′,’03144′,’03145′,’03146′,’03147′,’03148′,’13852’) )
group by month(refer_date),YEAR(refer_date)
ORDER BY year(refer_date),month(refer_date)
++++THAI REFER+++++++++
select month(refer_date),YEAR(refer_date),count(*) from referback
where refer_hospcode not in (‘00277′,’03139′,’03140′,’03141′,’03142′,’03144′,’03145′,’03146′,’03147′,’03148′,’13852’)
and (refer_date between ‘2015-12-01’ and ‘2017-09-31’)
group by month(refer_date),YEAR(refer_date)
ORDER BY year(refer_date),month(refer_date)
พระสงฆ์
select GROUP_CONCAT(DISTINCT d.DIAGCODE)as dxx,d.DIAGTYPE,p.TYPEAREA,p.PRENAME,p.`NAME`,p.LNAME,s.*
from service s
left join person p on (s.pid=p.pid) and (p.HOSPCODE=’10917′)
left join diagnosis_opd d on s.pid=d.pid and (d.HOSPCODE=’10917′) and (d.DIAGCODE BETWEEN ‘z00’ and ‘z108′)
where s.HOSPCODE=’10917’ and (CHIEFCOMP like ‘%ภาพพระ%’) /* and (p.TYPEAREA in (‘1′,’3’)) */
group by s.PID
order by p.PRENAME
ความเห็นล่าสุด