• Home
  • My Scripts
  • QOF 08 การตรวจพัฒนาการเด็ก DSPM

QOF 08 การตรวจพัฒนาการเด็ก DSPM

Update Delete

ID797
Parent ID50
Table Nameqof62_kpi08_dspm
TitleQOF 08 การตรวจพัฒนาการเด็ก DSPM
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='2020';
SET @start_d=concat(@b_year-1,'0401'); #แก้ไขวันที่ตามเงื่อนไข QOF
SET @end_d=concat(@b_year,'0430');
SET @begin_d=DATE_ADD(@start_d,INTERVAL -43 month);
SET @begin_d1=DATE_ADD(@start_d,INTERVAL -1 month);
SET @end_d1=concat(@b_year,'0430');

#/*เตรียมข้อมูล*/
SET @table1='specialpp';
SET @filed1= 't.HOSPCODE,
t.PID,
t.SEQ,
t.DATE_SERV,
t.SERVPLACE,
t.PPSPECIAL,
t.PPSPLACE,
t.PROVIDER,
t.D_UPDATE,
t.HDC_DATE';

DROP TABLE IF EXISTS _qof61_8_tmp_specialpp;
SET @s:=CONCAT('CREATE TABLE IF NOT EXISTS _qof61_8_tmp_specialpp (
PRIMARY KEY (HOSPCODE,PID,DATE_SERV,PPSPECIAL)
,KEY (cid),KEY (hospcode,pid),KEY (date_serv),KEY (ppspecial)
) ENGINE=MyISAM AS SELECT SQL_BIG_RESULT ', @filed1, '
,p.CID
FROM ', @table1,' t LEFT JOIN t_person_db p ON t.HOSPCODE=p.HOSPCODE AND t.pid=p.pid
WHERE DATE_SERV BETWEEN @begin_d1 AND @end_d1');
PREPARE stmt FROM @s;
EXECUTE stmt;

#/*หาเด็กกลุ่มเป้าหมาย*/
DROP TABLE IF EXISTS _qof61_8_t_childdev;
CREATE TABLE IF NOT EXISTS _qof61_8_t_childdev(
`HOSPCODE` varchar(5) NOT NULL DEFAULT '',
`PID` varchar(15) NOT NULL DEFAULT '',
`CID` varchar(13) NOT NULL,
`BIRTH` date DEFAULT NULL,
`SEX` varchar(1) NOT NULL DEFAULT '',
`AREACODE` varchar(8) DEFAULT NULL,
`TYPEAREA` varchar(1) NOT NULL DEFAULT '',
`mhospcode` varchar(5) DEFAULT NULL,
`mpid` varchar(15) DEFAULT NULL,
`mcid` varchar(13) DEFAULT NULL,
`AGE_9` int(1) NOT NULL DEFAULT '0',
`AGE_18` int(1) NOT NULL DEFAULT '0',
`AGE_30` int(1) NOT NULL DEFAULT '0',
`AGE_42` int(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`HOSPCODE`,`PID`),
KEY (`HOSPCODE`,`PID`,`CID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT IGNORE INTO _qof61_8_t_childdev (SELECT
p.HOSPCODE,p.PID,p.CID,p.BIRTH,p.SEX,p.check_vhid AREACODE,p.check_typearea TYPEAREA,null,null
,IF(mother = '0000000000000' AND ISNULL(mother) ,null,mother )
,0,0,0,0
FROM t_person_cid p
WHERE BIRTH BETWEEN @begin_d AND @end_d AND
check_typearea in('1','3') AND p.DISCHARGE='9' AND LENGTH(trim(p.CID))=13 AND NATION in(99)
);

UPDATE _qof61_8_t_childdev c INNER JOIN tmp_newborn n ON c.cid=n.cid ######tmp_newborn
SET c.mhospcode=n.hospcode,c.mpid=n.mpid;
UPDATE _qof61_8_t_childdev c INNER JOIN t_person_db p ON c.mhospcode=p.hospcode AND c.mpid=p.pid
SET c.mcid=p.cid;
UPDATE _qof61_8_t_childdev SET AGE_9=1 WHERE (DATE_ADD(BIRTH,INTERVAL 9 month) BETWEEN @start_d AND @end_d)
OR (DATE_ADD(BIRTH,INTERVAL 10 month) BETWEEN @start_d AND @end_d);
UPDATE _qof61_8_t_childdev SET AGE_18=1 WHERE (DATE_ADD(BIRTH,INTERVAL 18 month) BETWEEN @start_d AND @end_d)
OR (DATE_ADD(BIRTH,INTERVAL 19 month) BETWEEN @start_d AND @end_d);
UPDATE _qof61_8_t_childdev SET AGE_30=1 WHERE (DATE_ADD(BIRTH,INTERVAL 30 month) BETWEEN @start_d AND @end_d)
OR (DATE_ADD(BIRTH,INTERVAL 31 month) BETWEEN @start_d AND @end_d);
UPDATE _qof61_8_t_childdev SET AGE_42=1 WHERE (DATE_ADD(BIRTH,INTERVAL 42 month) BETWEEN @start_d AND @end_d)
OR (DATE_ADD(BIRTH,INTERVAL 43 month) BETWEEN @start_d AND @end_d);

#/*นำเด็กกลุ่มเป้าหมายเข้าตารางคำนวน*/
DROP TABLE IF EXISTS _qof61_8_t_childdev_specialpp;
CREATE TABLE IF NOT EXISTS _qof61_8_t_childdev_specialpp(
hospcode VARCHAR(5) NOT NULL,
pid VARCHAR(15) NOT NULL,
cid VARCHAR(15) NOT NULL DEFAULT '',
birth date,
sex VARCHAR(1) NOT NULL,
areacode VARCHAR(8) NOT NULL,
typearea VARCHAR(1) DEFAULT NULL,
agemonth VARCHAR(3) NOT NULL DEFAULT '',
mhospcode varchar(5) DEFAULT NULL,
mpid varchar(15) DEFAULT NULL,
mcid varchar(13) DEFAULT NULL,
date_start date,
date_end date,
date_serv_first date,
status1 VARCHAR(1) DEFAULT NULL,
date_serv2 date,
sp_first text,
sp_last text,
date_serv_last date,
status2 VARCHAR(1) DEFAULT NULL,
status21 VARCHAR(1) DEFAULT NULL,
status22 VARCHAR(1) DEFAULT NULL,
status23 VARCHAR(1) DEFAULT NULL,
status24 VARCHAR(1) DEFAULT NULL,
status25 VARCHAR(1) DEFAULT NULL,
PRIMARY KEY(hospcode,pid,cid,agemonth)
,KEY (hospcode,pid,cid)
) ENGINE=MyISAM ;

INSERT IGNORE INTO _qof61_8_t_childdev_specialpp (hospcode,pid,cid,birth,sex,areacode,typearea,mhospcode,mpid,mcid,agemonth)
(
SELECT
p.hospcode,p.pid,p.cid,p.birth,p.sex,p.areacode,p.typearea,mhospcode,mpid,mcid,'42'
FROM _qof61_8_t_childdev p
WHERE AGE_42=1
);
INSERT IGNORE INTO _qof61_8_t_childdev_specialpp (hospcode,pid,cid,birth,sex,areacode,typearea,mhospcode,mpid,mcid,agemonth)
(
SELECT
p.hospcode,p.pid,p.cid,p.birth,p.sex,p.areacode,p.typearea,mhospcode,mpid,mcid,'30'
FROM _qof61_8_t_childdev p
WHERE AGE_30=1
);
INSERT IGNORE INTO _qof61_8_t_childdev_specialpp (hospcode,pid,cid,birth,sex,areacode,typearea,mhospcode,mpid,mcid,agemonth)
(
SELECT
p.hospcode,p.pid,p.cid,p.birth,p.sex,p.areacode,p.typearea,mhospcode,mpid,mcid,'18'
FROM _qof61_8_t_childdev p
WHERE AGE_18=1
);
INSERT IGNORE INTO _qof61_8_t_childdev_specialpp (hospcode,pid,cid,birth,sex,areacode,typearea,mhospcode,mpid,mcid,agemonth)
(
SELECT
p.hospcode,p.pid,p.cid,p.birth,p.sex,p.areacode,p.typearea,mhospcode,mpid,mcid,'9'
FROM _qof61_8_t_childdev p
WHERE AGE_9=1
);

INSERT IGNORE INTO _qof61_8_t_childdev_specialpp (hospcode,pid,cid,birth,sex,areacode,typearea,mhospcode,mpid,mcid,agemonth)
(SELECT
p.hospcode,p.pid,p.cid,p.birth,p.sex,p.areacode,p.typearea,mhospcode,mpid,mcid
,IF(age_18=1 AND age_9=1 , 18 ,0)
FROM _qof61_8_t_childdev p
);
INSERT IGNORE INTO _qof61_8_t_childdev_specialpp (hospcode,pid,cid,birth,sex,areacode,typearea,mhospcode,mpid,mcid,agemonth)
(SELECT
p.hospcode,p.pid,p.cid,p.birth,p.sex,p.areacode,p.typearea,mhospcode,mpid,mcid
,IF(age_18=1 AND age_9=1 , 9 ,0)
FROM _qof61_8_t_childdev p
);
DELETE FROM _qof61_8_t_childdev_specialpp WHERE agemonth=0;

#/*อัพเดทช่วงวันที่ที่ทำแล้วถือว่าได้รับการคัดกรอง*/
UPDATE _qof61_8_t_childdev_specialpp SET date_start=DATE_ADD(BIRTH,INTERVAL 9 month) ,date_end=DATE_ADD(DATE_ADD(BIRTH,INTERVAL 10 month),INTERVAL -1 DAY) WHERE agemonth=9;
UPDATE _qof61_8_t_childdev_specialpp SET date_start=DATE_ADD(BIRTH,INTERVAL 18 month) ,date_end=DATE_ADD(DATE_ADD(BIRTH,INTERVAL 19 month),INTERVAL -1 DAY) WHERE agemonth=18;
UPDATE _qof61_8_t_childdev_specialpp SET date_start=DATE_ADD(BIRTH,INTERVAL 30 month) ,date_end=DATE_ADD(DATE_ADD(BIRTH,INTERVAL 31 month),INTERVAL -1 DAY) WHERE agemonth=30;
UPDATE _qof61_8_t_childdev_specialpp SET date_start=DATE_ADD(BIRTH,INTERVAL 42 month) ,date_end=DATE_ADD(DATE_ADD(BIRTH,INTERVAL 43 month),INTERVAL -1 DAY) WHERE agemonth=42;

#/*หาวันที่วันแรกที่ได้รับการตรวจเอาวันน้อยที่สุดจากวันเกิดที่มีรหัสที่กำหนด*/
UPDATE _qof61_8_t_childdev_specialpp s INNER JOIN
(
SELECT s1.cid,s1.date_start, s1.date_end ,MIN(t1.date_serv) min_date_serv
FROM _qof61_8_tmp_specialpp t1 INNER JOIN _qof61_8_t_childdev_specialpp s1 ON t1.cid=s1.cid
WHERE t1.date_serv BETWEEN s1.date_start AND s1.date_end
AND PPSPECIAL in('1B260','1B261','1B262' ,'1B200'
,'1B201','1B202','1B209','1B210','1B211','1B212'
,'1B219','1B220','1B221','1B222','1B229','1B230'
,'1B231','1B232','1B239','1B240','1B241','1B242','1B249')
GROUP BY t1.cid,s1.date_start
)
t ON s.cid=t.cid AND s.date_start=t.date_start
SET s.date_serv_first= t.min_date_serv
WHERE t.min_date_serv BETWEEN s.date_start AND s.date_end ;

#/*อัพเดททgroup รหัสที่ทำวันแรกทั้งหมด*/
UPDATE _qof61_8_t_childdev_specialpp s INNER JOIN
(
SELECT s1.cid,s1.date_serv_first ,GROUP_CONCAT(t1.PPSPECIAL ORDER BY t1.PPSPECIAL) gr_sp
FROM _qof61_8_tmp_specialpp t1 INNER JOIN _qof61_8_t_childdev_specialpp s1 ON t1.cid=s1.cid
WHERE t1.date_serv in(s1.date_serv_first)
GROUP BY t1.cid,s1.date_serv_first
)
t ON s.cid=t.cid AND s.date_serv_first=t.date_serv_first
SET s.sp_first= t.gr_sp ;

#/*สรุปผลครั้งแรกตามเงื่อนไข*/
UPDATE _qof61_8_t_childdev_specialpp SET status1=IF(INSTR(sp_first,'1B260'),1
,IF(INSTR(sp_first,'1B261'),2
,IF(INSTR(sp_first,'1B262'),3
,NULL
)))
WHERE date_serv_first IS NOT NULL;

#/*สรุปผลครั้งแรกถ้าลงรหัสแบบที่ละด้านแบบเดิม*/
UPDATE _qof61_8_t_childdev_specialpp SET status1=IF(
INSTR(sp_first,'1B200')
AND INSTR(sp_first,'1B210')
AND INSTR(sp_first,'1B220')
AND INSTR(sp_first,'1B230')
AND INSTR(sp_first,'1B240')
,1,NULL)
WHERE date_serv_first IS NOT NULL AND status1 IS NULL;

UPDATE _qof61_8_t_childdev_specialpp SET status1=IF(
instr(sp_first,'1B201')
OR instr(sp_first,'1B211')
OR instr(sp_first,'1B221')
OR instr(sp_first,'1B231')
OR instr(sp_first,'1B241')
,2,NULL)
WHERE date_serv_first IS NOT NULL AND status1 IS NULL;

UPDATE _qof61_8_t_childdev_specialpp SET status1=IF(
instr(sp_first,'1B202')
OR instr(sp_first,'1B212')
OR instr(sp_first,'1B222')
OR instr(sp_first,'1B232')
OR instr(sp_first,'1B242')
,3,NULL)
WHERE date_serv_first IS NOT NULL AND status1 IS NULL;


#/*อัพเดทวันที่ติดตามนับจากวันครั้งแรกถึงวันสุดท้ายที่ไม่เกิน 30 วันที่เป็นไปได้ กรณีผิดปกติ*/
UPDATE _qof61_8_t_childdev_specialpp SET date_serv2=DATE_ADD(date_serv_first,INTERVAL 30 day)
WHERE date_serv_first IS NOT NULL AND status1 in(2) ;


#/*หาวันที่วันสุดท้ายที่มาตรวจ ตามช่วง 30 วันที่กำหนด เปลี่ยนได้ตลอดจนกว่าจะเลย 30 วัน*/
UPDATE _qof61_8_t_childdev_specialpp s INNER JOIN
(
SELECT s1.cid,s1.date_serv_first, s1.date_serv2 ,MAX(t1.date_serv) max_date_serv
FROM _qof61_8_tmp_specialpp t1 INNER JOIN _qof61_8_t_childdev_specialpp s1 ON t1.cid=s1.cid
WHERE t1.date_serv BETWEEN s1.date_serv_first AND s1.date_serv2
AND PPSPECIAL in('1B260'
,'1B200','1B202','1B210','1B212'
,'1B220','1B222','1B230','1B232'
,'1B240','1B242')
GROUP BY t1.cid,s1.date_serv2
)
t ON s.cid=t.cid AND s.date_serv2=t.date_serv2
SET s.date_serv_last= t.max_date_serv
WHERE t.max_date_serv BETWEEN s.date_serv_first AND s.date_serv2
AND t.max_date_serv > s.date_serv_first AND s.date_serv2 is not null;


#/*อัพเดททgroup รหัสวันสุดท้ายกรณีที่ติดตาม จะเปลี่ยนแปลงได้ตลอดจนกว่าจะเลย 30 วันจึงจะสรุปได้จริง*/
UPDATE _qof61_8_t_childdev_specialpp s INNER JOIN
(
SELECT s1.cid,s1.date_serv_last ,GROUP_CONCAT(t1.PPSPECIAL) gr_sp
FROM _qof61_8_tmp_specialpp t1 INNER JOIN _qof61_8_t_childdev_specialpp s1 ON t1.cid=s1.cid
WHERE t1.date_serv in(s1.date_serv_last)
GROUP BY t1.cid,s1.date_serv_last
)
t ON s.cid=t.cid AND s.date_serv_last=t.date_serv_last
SET s.sp_last= t.gr_sp
WHERE s.date_serv2 is not null ;
#/*สรุปผลครั้งสุดท้าย*/
UPDATE _qof61_8_t_childdev_specialpp SET status21=IF(INSTR(sp_last,'1B202'),1,null),
status22=IF(INSTR(sp_last,'1B212'),1,null),
status23=IF(INSTR(sp_last,'1B222'),1,null),
status24=IF(INSTR(sp_last,'1B232'),1,null),
status25= IF(INSTR(sp_last,'1B242'),1,null)
WHERE date_serv_last IS NOT NULL AND INSTR(sp_last,'1B260')=0
AND date_serv_last > date_serv_first
AND NOW() > date_serv2
AND date_serv2 is not null;

UPDATE _qof61_8_t_childdev_specialpp SET status2=IF(INSTR(sp_last,'1B260'),1,NULL)
WHERE date_serv_last IS NOT NULL AND date_serv_last > date_serv_first AND status2 IS NULL
AND date_serv2 is not null;

#/*สรุปผลครั้งสุดท้ายถ้าลงรหัสแบบที่ละด้านแบบเดิม*/
UPDATE _qof61_8_t_childdev_specialpp SET status2=IF(
INSTR(sp_last,'1B200')
AND INSTR(sp_last,'1B210')
AND INSTR(sp_last,'1B220')
AND INSTR(sp_last,'1B230')
AND INSTR(sp_last,'1B240')
,1,NULL)
WHERE date_serv_last IS NOT NULL AND date_serv_last > date_serv_first AND status2 IS NULL
AND date_serv2 is not null;




SELECT
p.check_hosp hospcode
,IFNULL(p.check_vhid,concat(h.provcode,h.distcode,h.subdistcode,SUBSTR(CONCAT('00',h.mu),-2))) AS 'areacode'
,DATE_FORMAT(now(),'%Y%m%d%H%i') as d_com
,IF(DATE_FORMAT(s.date_start,'%m') > 3,YEAR(s.date_start)+544,YEAR(s.date_start)+543) as 'b_year'
#,s.date_start
#,@b_year+543 as b_year
,DATE_FORMAT(s.date_start,'%m') AS 'date_start'
,s.agemonth,s.cid,s.status1 #result9_2
,s.status2
,s.status21,s.status22,s.status23,s.status24,s.status25
FROM
_qof61_8_t_childdev_specialpp s
INNER JOIN t_person_cid p ON s.cid=p.cid
INNER JOIN chospital h ON p.HOSPCODE=h.hoscode
WHERE p.check_typearea in(1,3) AND p.NATION in(99) AND p.DISCHARGE in(9)
AND s.date_start BETWEEN @start_d AND @end_d AND substr(p.check_vhid ,1,2) = @prov_c
AND h.provcode = @prov_c;



#PROVIDER1_SQL#
Script Cron
Force Script Cron0
Active1
Client Office Type
Create Date2018-06-12 01:28:45
Last Update2020-01-13 10:55:25