• Home
  • My Scripts
  • PPA4 ผู้ป่วยโรคเรื้อรัง ได้รับการคัดกรองภาวะซึมเศร้า

PPA4 ผู้ป่วยโรคเรื้อรัง ได้รับการคัดกรองภาวะซึมเศร้า

Update Delete

ID840
Parent ID64
Table Name65_ppa_depression_chronic_2
TitlePPA4 ผู้ป่วยโรคเรื้อรัง ได้รับการคัดกรองภาวะซึมเศร้า
Description
Script#SQL_OPTIONS#
PROVIDERS=1
PROVIDER1=43STD
PROVIDER1_VALIDATE_TABLES=student,t_person_cid,anc,labor
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:='2022';
#งวดที่ 1

SET @start_d1:=concat(@b_year-1,'1001');
SET @end_d1:=concat(@b_year,'0228');

#งวดที่ 2
SET @start_d:=concat(@b_year,'0301');
SET @end_d:=concat(@b_year,'0630');

SET SESSION group_concat_max_len = 1000000;


#------------------------------------------------#
#Stap1
#2Q One Record in Hospcode
DROP TABLE IF EXISTS tmp_ppa65_specialpp2q;
CREATE TABLE IF NOT EXISTS tmp_ppa65_specialpp2q(
PRIMARY KEY (HOSPCODE,PID),KEY (cid),KEY (hospcode,pid))
(#depress 60year
SELECT
s.HOSPCODE,s.PID,p.CID,
GROUP_CONCAT(s.DATE_SERV ORDER BY s.date_serv) '2Q_DATE',
GROUP_CONCAT(s.PPSPECIAL ORDER BY s.date_serv) '2Q',
GROUP_CONCAT(s.PPSPLACE ORDER BY s.date_serv) '2Q_PLACE',s.PROVIDER,s.D_UPDATE
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 ('1B0280','1B0281')
GROUP BY p.CID ORDER BY s.date_serv
);

#Stap1-งวดที่1
#2Q One Record in Hospcode
DROP TABLE IF EXISTS tmp_ppa65_specialpp2q_A1;
CREATE TABLE IF NOT EXISTS tmp_ppa65_specialpp2q_A1(
PRIMARY KEY (HOSPCODE,PID),KEY (cid),KEY (hospcode,pid))
(#depress 60year
SELECT
s.HOSPCODE,s.PID,p.CID,
GROUP_CONCAT(s.DATE_SERV ORDER BY s.date_serv) '2Q_DATE',
'A1' AS '2Q',
GROUP_CONCAT(s.PPSPLACE ORDER BY s.date_serv) '2Q_PLACE',s.PROVIDER,s.D_UPDATE
FROM specialpp s
LEFT JOIN person p ON s.HOSPCODE=p.HOSPCODE AND s.PID=p.PID
WHERE DATE_SERV BETWEEN @start_d1 AND @end_d1 AND s.PPSPECIAL IN ('1B0280','1B0281')
GROUP BY p.CID ORDER BY s.date_serv
);

INSERT IGNORE INTO tmp_ppa65_specialpp2q (SELECT * FROM tmp_ppa65_specialpp2q_A1);
DROP TABLE IF EXISTS tmp_ppa65_specialpp2q_A1;


#9Q One Record in Hospcode
DROP TABLE IF EXISTS tmp_ppa65_specialpp9q;
CREATE TABLE IF NOT EXISTS tmp_ppa65_specialpp9q(
PRIMARY KEY (HOSPCODE,PID),KEY (cid),KEY (hospcode,pid))
(
SELECT
a.HOSPCODE,a.PID,p.CID,
GROUP_CONCAT(a.DATE_SERV ORDER BY a.DATE_SERV) '9Q_DATE',
GROUP_CONCAT(b.PPSPECIAL ORDER BY a.DATE_SERV) '9Q',
GROUP_CONCAT(a.PPSPLACE ORDER BY a.DATE_SERV) '9Q_PLACE',a.PROVIDER,b.D_UPDATE
FROM specialpp a
INNER JOIN specialpp b ON b.HOSPCODE=a.HOSPCODE AND b.PID=a.PID AND a.DATE_SERV=b.DATE_SERV
INNER JOIN t_person_cid p ON p.HOSPCODE=a.HOSPCODE AND p.PID=a.PID
WHERE a.DATE_SERV BETWEEN @start_d AND @end_d AND a.PPSPECIAL = '1B0281' AND b.PPSPECIAL IN ('1B0282','1B0283','1B0284','1B0285')
GROUP BY p.CID ORDER BY a.date_serv
);

#งวดที่ 1
#9Q One Record in Hospcode
DROP TABLE IF EXISTS tmp_ppa65_specialpp9q_B1;
CREATE TABLE IF NOT EXISTS tmp_ppa65_specialpp9q_B1(
PRIMARY KEY (HOSPCODE,PID),KEY (cid),KEY (hospcode,pid))
(
SELECT
a.HOSPCODE,a.PID,p.CID,
GROUP_CONCAT(a.DATE_SERV ORDER BY a.DATE_SERV) '9Q_DATE',
'B1' AS '9Q',
GROUP_CONCAT(a.PPSPLACE ORDER BY a.DATE_SERV) '9Q_PLACE',a.PROVIDER,b.D_UPDATE
FROM specialpp a
INNER JOIN specialpp b ON b.HOSPCODE=a.HOSPCODE AND b.PID=a.PID AND a.DATE_SERV=b.DATE_SERV
INNER JOIN t_person_cid p ON p.HOSPCODE=a.HOSPCODE AND p.PID=a.PID
WHERE a.DATE_SERV BETWEEN @start_d1 AND @end_d1 AND a.PPSPECIAL = '1B0281' AND b.PPSPECIAL IN ('1B0282','1B0283','1B0284','1B0285')
GROUP BY p.CID ORDER BY a.date_serv
);

INSERT IGNORE INTO tmp_ppa65_specialpp9q (SELECT * FROM tmp_ppa65_specialpp9q_B1);
DROP TABLE IF EXISTS tmp_ppa65_specialpp9q_B1;


#merge 2Q,9Q to One Table (Service 2Q,9Q One Hospcode)
DROP TABLE IF EXISTS tmp_ppa65_ppspecial_2q9q;
CREATE TABLE IF NOT EXISTS tmp_ppa65_ppspecial_2q9q(
PRIMARY KEY (HOSPCODE,PID),KEY (cid),KEY (hospcode,pid))
(#Depress 60y
SELECT
a.HOSPCODE,a.PID,a.CID, a.2Q_PLACE,a.2Q_DATE,a.2Q, b.9Q,b.9Q_DATE,b.9Q_PLACE
FROM tmp_ppa65_specialpp2q a
LEFT JOIN tmp_ppa65_specialpp9q b ON (b.HOSPCODE=a.HOSPCODE AND b.PID=a.PID)
);

DROP TABLE IF EXISTS tmp_ppa65_specialpp2q;
DROP TABLE IF EXISTS tmp_ppa65_specialpp9q;



DROP TABLE IF EXISTS tmp1_ppa65_specialpp2q;
CREATE TABLE IF NOT EXISTS tmp1_ppa65_specialpp2q(
PRIMARY KEY (HOSPCODE,PID),KEY (cid),KEY (hospcode,pid))
(#depress Chronic
SELECT
s.HOSPCODE,s.PID,p.CID,
GROUP_CONCAT(s.DATE_SERV ORDER BY s.date_serv) '2Q_DATE',
GROUP_CONCAT(s.PPSPECIAL ORDER BY s.date_serv) '2Q',
GROUP_CONCAT(s.PPSPLACE ORDER BY s.date_serv) '2Q_PLACE',s.PROVIDER,s.D_UPDATE
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 ('1B130','1B131')
GROUP BY p.CID ORDER BY s.date_serv
);
# งวดที่ 1
DROP TABLE IF EXISTS tmp1_ppa65_specialpp2q_C1;
CREATE TABLE IF NOT EXISTS tmp1_ppa65_specialpp2q_C1(
PRIMARY KEY (HOSPCODE,PID),KEY (cid),KEY (hospcode,pid))
(#depress Chronic
SELECT
s.HOSPCODE,s.PID,p.CID,
GROUP_CONCAT(s.DATE_SERV ORDER BY s.date_serv) '2Q_DATE',
'C1' AS '2Q',
GROUP_CONCAT(s.PPSPLACE ORDER BY s.date_serv) '2Q_PLACE',s.PROVIDER,s.D_UPDATE
FROM specialpp s
LEFT JOIN person p ON s.HOSPCODE=p.HOSPCODE AND s.PID=p.PID
WHERE DATE_SERV BETWEEN @start_d1 AND @end_d1 AND s.PPSPECIAL IN ('1B130','1B131')
GROUP BY p.CID ORDER BY s.date_serv
);

INSERT IGNORE INTO tmp1_ppa65_specialpp2q (SELECT * FROM tmp1_ppa65_specialpp2q_C1);
DROP TABLE IF EXISTS tmp1_ppa65_specialpp2q_C1;

DROP TABLE IF EXISTS tmp1_ppa65_specialpp9q;
CREATE TABLE IF NOT EXISTS tmp1_ppa65_specialpp9q(
PRIMARY KEY (HOSPCODE,PID),KEY (cid),KEY (hospcode,pid))
(
SELECT
a.HOSPCODE,a.PID,p.CID,
GROUP_CONCAT(a.DATE_SERV ORDER BY a.DATE_SERV) '9Q_DATE',
GROUP_CONCAT(b.PPSPECIAL ORDER BY a.DATE_SERV) '9Q',
GROUP_CONCAT(a.PPSPLACE ORDER BY a.DATE_SERV) '9Q_PLACE',a.PROVIDER,b.D_UPDATE
FROM specialpp a
INNER JOIN specialpp b ON b.HOSPCODE=a.HOSPCODE AND b.PID=a.PID AND a.DATE_SERV=b.DATE_SERV
INNER JOIN t_person_cid p ON p.HOSPCODE=a.HOSPCODE AND p.PID=a.PID
WHERE a.DATE_SERV BETWEEN @start_d AND @end_d AND a.PPSPECIAL = '1B131' AND b.PPSPECIAL IN ('1B0260','1B0261','1B0262','1B0263')
GROUP BY p.CID ORDER BY a.date_serv
);

DROP TABLE IF EXISTS tmp1_ppa65_specialpp9q_D1;
CREATE TABLE IF NOT EXISTS tmp1_ppa65_specialpp9q_D1(
PRIMARY KEY (HOSPCODE,PID),KEY (cid),KEY (hospcode,pid))
(
SELECT
a.HOSPCODE,a.PID,p.CID,
GROUP_CONCAT(a.DATE_SERV ORDER BY a.DATE_SERV) '9Q_DATE',
'D1' AS '9Q',
GROUP_CONCAT(a.PPSPLACE ORDER BY a.DATE_SERV) '9Q_PLACE',a.PROVIDER,b.D_UPDATE
FROM specialpp a
INNER JOIN specialpp b ON b.HOSPCODE=a.HOSPCODE AND b.PID=a.PID AND a.DATE_SERV=b.DATE_SERV
INNER JOIN t_person_cid p ON p.HOSPCODE=a.HOSPCODE AND p.PID=a.PID
WHERE a.DATE_SERV BETWEEN @start_d1 AND @end_d1 AND a.PPSPECIAL = '1B131' AND b.PPSPECIAL IN ('1B0260','1B0261','1B0262','1B0263')
GROUP BY p.CID ORDER BY a.date_serv
);

INSERT IGNORE INTO tmp1_ppa65_specialpp9q (SELECT * FROM tmp1_ppa65_specialpp9q_D1);
DROP TABLE IF EXISTS tmp1_ppa65_specialpp9q_D1;


#
#merge 2Q,9Q to One Table (Service 2Q,9Q One Hospcode)
#
DROP TABLE IF EXISTS tmp1_ppa65_ppspecial_2q9q;
CREATE TABLE IF NOT EXISTS tmp1_ppa65_ppspecial_2q9q(
PRIMARY KEY (HOSPCODE,PID),KEY (cid),KEY (hospcode,pid))
(#Depress Chronic
SELECT
a.HOSPCODE,a.PID,a.CID, a.2Q_PLACE,a.2Q_DATE,a.2Q, b.9Q,b.9Q_DATE,b.9Q_PLACE
FROM tmp1_ppa65_specialpp2q a
LEFT JOIN tmp1_ppa65_specialpp9q b ON (b.HOSPCODE=a.HOSPCODE AND b.PID=a.PID)
);
#
DROP TABLE IF EXISTS tmp1_ppa65_specialpp2q;
DROP TABLE IF EXISTS tmp1_ppa65_specialpp9q;
#
#-------------------------------------------------------------
#Stap3 Crate person Depression Chronic
DROP TABLE IF EXISTS tmp_ppa65_depress_chronic;
CREATE TABLE IF NOT EXISTS tmp_ppa65_depress_chronic(
PRIMARY KEY (HOSPCODE,PID,CID),KEY(HOSPCODE,PID))
(
SELECT
a.HOSPCODE,a.PID,a.CID,CONCAT(a.`NAME`,' ',a.LNAME) 'PNAME',a.SEX,a.NATION,a.age_y,a.DISCHARGE,a.TYPEAREA,a.vhid,b.g_chronic,'0' AS 'check_chronic'
FROM t_person_cid a
INNER JOIN(SELECT cid,GROUP_CONCAT(DIAGCODE ORDER BY DIAGCODE) AS 'g_chronic'
FROM t_chronic GROUP BY cid)b ON a.cid=b.cid
WHERE a.NATION = '099'
AND a.DISCHARGE = '9'
AND a.typearea IN (1,3)
ORDER BY a.HOSPCODE,a.PID
);
/*โรคจิต*/
UPDATE tmp_ppa65_depress_chronic SET check_chronic = '1' WHERE INSTR(g_chronic,'F');
/*เบาหวาน*/
UPDATE tmp_ppa65_depress_chronic SET check_chronic = '1'
WHERE INSTR(g_chronic,'E10') OR INSTR(g_chronic,'E11') OR INSTR(g_chronic,'E12') OR INSTR(g_chronic,'E14');
/*ความดัน*/
UPDATE tmp_ppa65_depress_chronic SET check_chronic = '1'
WHERE INSTR(g_chronic,'I10') OR INSTR(g_chronic,'I11') OR INSTR(g_chronic,'I12') OR INSTR(g_chronic,'I13') OR INSTR(g_chronic,'I14') OR INSTR(g_chronic,'I15');
/*มะเร็ง*/
UPDATE tmp_ppa65_depress_chronic SET check_chronic = '1'
WHERE INSTR(g_chronic,'C') OR INSTR(g_chronic,'D0') OR INSTR(g_chronic,'D1') OR INSTR(g_chronic,'D2') OR INSTR(g_chronic,'D3') OR INSTR(g_chronic,'D4') ;
/*โรคหัวใจและหลอดเลือด*/
UPDATE tmp_ppa65_depress_chronic SET check_chronic = '1'
WHERE INSTR(g_chronic,'I2') OR INSTR(g_chronic,'I3') OR INSTR(g_chronic,'I4') OR INSTR(g_chronic,'I50') OR INSTR(g_chronic,'I51') OR INSTR(g_chronic,'I52') ;
/*หลอดเลือดสมอง*/
UPDATE tmp_ppa65_depress_chronic SET check_chronic = '1' WHERE INSTR(g_chronic,'I6');
/*ปอดอุดกลั้นเรื้อรัง*/
UPDATE tmp_ppa65_depress_chronic SET check_chronic = '1'
WHERE INSTR(g_chronic,'J40') OR INSTR(g_chronic,'J41') OR INSTR(g_chronic,'J42') OR INSTR(g_chronic,'J43') OR INSTR(g_chronic,'J44')
OR INSTR(g_chronic,'J45') OR INSTR(g_chronic,'J46') OR INSTR(g_chronic,'J40');
#
#Merge person + special
DROP TABLE IF EXISTS ppa65_1_depress_chronic;
CREATE TABLE IF NOT EXISTS ppa65_1_depress_chronic(
SELECT a.*,b.2Q_PLACE,b.2Q_Date,b.2Q,b.9Q,b.9Q_Date,b.9Q_PLACE
FROM tmp_ppa65_depress_chronic a
LEFT JOIN tmp1_ppa65_ppspecial_2q9q b ON b.CID=a.CID
);
#2020-09-02
UPDATE ppa65_1_depress_chronic SET 2Q_PLACE=null ,2Q_Date = NULL,2Q =NULL ,9Q = NULL,9Q_Date = NULL , 9Q_PLACE = NULL
WHERE age_y >= 60;
#
UPDATE ppa65_1_depress_chronic a
INNER JOIN tmp_ppa65_ppspecial_2q9q b ON a.CID=b.CID AND a.age_y >=60 AND a.check_chronic = 1
SET a.2Q_PLACE=b.2Q_PLACE ,a.2Q_Date=b.2Q_DATE ,a.2Q=b.2Q, a.9Q_PLACE=b.9Q_PLACE ,a.9Q_Date=b.9Q_DATE ,a.9Q=b.9Q;
#
DROP TABLE IF EXISTS tmp_ppa65_depress_chronic;
DROP TABLE IF EXISTS tmp_ppa65_ppspecial_2q9q;
DROP TABLE IF EXISTS tmp1_ppa65_ppspecial_2q9q;
#
ALTER TABLE ppa65_1_depress_chronic ADD COLUMN Maininscl VARCHAR(3);
ALTER TABLE ppa65_1_depress_chronic ADD COLUMN Hmain VARCHAR(5);
ALTER TABLE ppa65_1_depress_chronic ADD COLUMN Hsub VARCHAR(5);
ALTER TABLE ppa65_1_depress_chronic ADD COLUMN Pass VARCHAR(1);

#ปรับสิทธิรักษาพยาบาล
UPDATE ppa65_1_depress_chronic a,dbpop b
SET a.Maininscl=b.MainInScl, a.Hmain=b.HMain, a.Hsub=b.Hsub
WHERE a.cid=b.PID;

#ลบคนที่มีผลงานในงวดที่ 1 ออก
DELETE FROM ppa65_1_depress_chronic WHERE 2Q in('A1','B1','C1','D1');

SELECT * FROM ppa65_1_depress_chronic;


#PROVIDER1_SQL#
Script Cron
Force Script Cron0
Active1
Client Office Type
Create Date2022-04-01 02:05:48
Last Update2022-08-18 20:52:45