NHSO_PP60-KPI10-Depression

Update Delete

ID785
Parent ID52
Table Name_pp60_kpi10_depression
TitleNHSO_PP60-KPI10-Depression
Description
Script#SQL_OPTIONS#
PROVIDERS=1
PROVIDER1=43STD
PROVIDER1_VALIDATE_TABLES=accident,admission,anc,appointment
SCRIPT_FLOW=SQL
#SQL_OPTIONS#

#PROVIDER1_SQL#
SET @provcode = :provcode;
SET @rep_year = :rep_year;
SET @hoscode = :hoscode;
SET @hosname = :hosname;
SET @hostype = :hostype;
SET @address = :address;
SET @subdistcode = :subdistcode;
SET @distcode = :distcode;
SET @level_service = :level_service;

#SET @prov_c := :provcode;
SET @b_year:=(SELECT yearprocess FROM sys_config LIMIT 1);
SET @start_d:=concat('2017-04-01');
SET @end_d:=concat('2018-03-31');

##STEP1 สร้างตารางเก็บข้อมูล และตารางผลงาน
#เตรียมข้อมูลแฟ้ม specialpp ตามช่วงเวลาที่กำหนด
DROP TABLE IF EXISTS _zone6_tmp_specialpp;
CREATE TABLE IF NOT EXISTS _zone6_tmp_specialpp(
PRIMARY KEY (HOSPCODE,PID,DATE_SERV,PPSPECIAL)
,KEY (cid)
,KEY (hospcode,pid)
,KEY (date_serv)
,KEY (ppspecial)
)ENGINE=MyISAM(
SELECT
s.HOSPCODE,s.PID,s.SEQ,MIN(s.DATE_SERV) AS 'DATE_SERV',s.SERVPLACE,
s.PPSPECIAL,s.PPSPLACE,s.PROVIDER,s.D_UPDATE,p.CID
FROM specialpp s
LEFT JOIN person p ON s.HOSPCODE=p.HOSPCODE AND s.PID=p.PID
WHERE DATE_SERV BETWEEN @start_d AND @end_d
AND s.PPSPECIAL IN ('1B026','1B0282','1B0283','1B0284','1B0285','1B0260','1B0261','1B0263','1B0269','1B130
','1B131','1B140','1B141','1B149','1B150','1B151','1B159')
GROUP BY s.HOSPCODE,s.PID
);

#เตรียมข้อมูลแฟ้ม diag_opd ตามช่วงเวลาที่กำหนด
#GROUP BY ให้เหลือวันที่ล่าสุดในเครื่อง
DROP TABLES IF EXISTS _zone6_tmp_diag_opd;
CREATE TABLE IF NOT EXISTS _zone6_tmp_diag_opd (
KEY(cid),KEY(hospcode),KEY(pid),KEY(seq),KEY(date_serv),KEY(diagcode),KEY(diagtype),KEY(hospcode,pid,seq)
) ENGINE=MyISAM AS(
SELECT SQL_BIG_RESULT
o.HOSPCODE,o.PID,o.SEQ,MAX(o.DATE_SERV) 'DATE_SERV',o.DIAGTYPE,o.DIAGCODE,o.CLINIC,o.PROVIDER,o.D_UPDATE,p.CID
FROM
diagnosis_opd o
LEFT JOIN t_person_db p ON o.HOSPCODE=p.HOSPCODE AND o.PID=p.PID
WHERE DATE_SERV BETWEEN @start_d AND @end_d
AND DIAGCODE = 'Z133'
GROUP BY o.HOSPCODE,o.PID
);

#สร้างตารางเก็บข้อมูลบุคคล
DROP TABLE IF EXISTS _zone6_t_depression;
CREATE TABLE IF NOT EXISTS _zone6_t_depression (
hospcode varchar(5) DEFAULT NULL,
areacode varchar(8) DEFAULT NULL,
cid varchar(13) NOT NULL DEFAULT '',
pid varchar(15) DEFAULT NULL,
age_y int(3) DEFAULT '0',
NATION varchar(3) DEFAULT NULL,
Typearea varchar(1) DEFAULT NULL,
dbpop_Hmain varchar(5) DEFAULT NULL,
dbpop_Hsub varchar(5) DEFAULT NULL,
dbpop_MainInScl varchar(5) DEFAULT NULL,
special_Date date ,
special_Hosp varchar(5) DEFAULT NULL,
special_Seq varchar(16) DEFAULT NULL,
special_PPspecial varchar(7) DEFAULT NULL,
special_PPsplace varchar(5) DEFAULT NULL,
diag_Date date ,
diag_Hosp varchar(5) DEFAULT NULL,
diag_Seq varchar(16) DEFAULT NULL,
diag_Type varchar(1) DEFAULT NULL,
diag_Diagcode varchar(6) DEFAULT NULL,
PRIMARY KEY (hospcode,pid),
KEY (hospcode), KEY (cid), KEY (pid), KEY (areacode), KEY (typearea)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


##STEP2 นำเข้าข้อมุลบุคคลและผลงาน
#นำเข้าข้อมูลบุคคลตามเงื่อนไข
##ไม่ได้ตัดคนซ้ำตัดซ้ำระหว่างสถานบริการ
INSERT IGNORE INTO _zone6_t_depression(
hospcode,areacode,cid,pid,age_y,nation,typearea
)
SELECT pe.check_hosp HOSPCODE
,pe.check_vhid as areacode
,pe.CID
,pe.PID
#,p.age_y
,TIMESTAMPDIFF(YEAR,pe.BIRTH,@start_d) AS age_y
,p.NATION
,pe.check_typearea TYPEAREA
FROM t_person_db pe
LEFT JOIN t_person_cid p ON pe.CID=p.CID /*จอยเอาอายุที่ HDC มาใช้*/
WHERE pe.DISCHARGE=9
#AND pe.nation=99
#AND p.age_y>=60
AND TIMESTAMPDIFF(YEAR,pe.BIRTH,@start_d) >= 30 /*คำนวนอายุในช่วงเวลาที่ประมวลผล*/
#AND pe.check_typearea in(1,3)
GROUP BY CONCAT(pe.HOSPCODE,pe.CID) /*ให้เหลือ CID เลขเดียวใน หน่วยบริการ*/
ORDER BY pe.check_hosp,pe.check_typearea;

#นำเข้าข้อมูลสทิธิรักษาพยาบาลจากฐานข้อมูล DBPOP
UPDATE _zone6_t_depression z ,dbpop d
SET z.dbpop_Hmain=d.HMain , z.dbpop_Hsub=d.Hsub , z.dbpop_MainInScl=d.MainInScl
WHERE z.cid=d.PID;

##นำเข้าข้อมูลคัดกรองจาก specialpp
UPDATE _zone6_t_depression z ,_zone6_tmp_specialpp t ,service s
SET z.special_Date=t.DATE_SERV , z.special_Hosp=t.HOSPCODE ,z.special_PPsplace=t.ppsplace,
z.special_Seq=s.Seq,z.special_PPspecial=t.PPspecial
WHERE
z.hospcode=t.HOSPCODE AND z.cid=t.CID
AND t.hospcode=s.HOSPCODE AND t.pid=s.PID AND t.seq=s.SEQ AND t.date_serv=s.DATE_SERV;
# AND t.PPSPECIAL IN ('1B0282','1B0283','1B0284','1B0285');

#นำเข้าข้อมูลคัดกรองจาก diag_opd รหัส Z133
UPDATE _zone6_t_depression z , _zone6_tmp_diag_opd d ,service s
SET z.diag_Hosp=d.HOSPCODE,z.diag_Date=d.DATE_SERV ,z.diag_SEQ=d.SEQ,
z.diag_Diagcode=d.DIAGCODE, z.diag_Type=d.DIAGTYPE
WHERE
z.hospcode=d.HOSPCODE AND z.cid=d.CID
AND d.hospcode=s.HOSPCODE AND d.pid=s.PID AND d.seq=s.SEQ AND d.date_serv=s.DATE_SERV
AND d.DIAGCODE = 'Z133';

#ลบ Record ที่ไม่ได้คัดกรองออก
DELETE FROM _zone6_t_depression
WHERE special_date IS NULL AND diag_date IS null ;

#STEP3
##นับจำนวนข้อมูล
SELECT hospcode,
COUNT(IF(z.dbpop_MainInScl IN ('WEL','UCS') AND z.NATION = '099' ,CONCAT(z.hospcode,cid),NULL)) AS 'Uc',
COUNT(IF(z.dbpop_MainInScl IN ('BFC','FRG','IOL','L01','L02','L03','L04','L05','L06','L07','L08','LBF','LGO','OBV','OFB','OFC','OFL'
,'PBF','PIL','PLB','PLG','POF','POL','PSB','PSI','PSL','PSO','PSS','PVT','SBF','SIF','SIL','SLB','SLG','SOB','SOF','SOL','SSI','SSS'
,'STP','VBF','VET','VIL','VIO','VLB','VLG','VOF','VOL','VPB','VPL','VPO','VPS','VPT','VSB','VSI','VSL','VSO','VSS') AND z.NATION = '099' ,CONCAT(z.hospcode,cid),NULL)) AS 'Non Uc',
COUNT(IF(z.dbpop_MainInScl IS NULL AND z.NATION = '099',CONCAT(z.hospcode,cid),NULL)) AS 'Other',
COUNT(IF(z.NATION <> '099' OR z.dbpop_MainInScl IN ('NRH','NRD') ,CONCAT(z.hospcode,cid),NULL)) AS 'Non Thai',
COUNT(cid) 'SUM_ALL'
FROM _zone6_t_depression z
GROUP BY z.hospcode

#PROVIDER1_SQL#
Script Cron
Force Script Cron0
Active1
Client Office Type
Create Date2017-12-13 02:38:59
Last Update2018-05-18 11:30:06