NHSO_PP60-KPI01-ANC

Update Delete

ID763
Parent ID52
Table Name_pp60_kpi01_anc
TitleNHSO_PP60-KPI01-ANC
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 @start_d='2017-04-01';
SET @end_d:='2018-03-31';

DROP TABLE IF EXISTS _zon6_tmp_anc;
CREATE TABLE IF NOT EXISTS _zon6_tmp_anc(
KEY (cid),
KEY (hospcode,pid),
KEY (date_serv),
KEY (ga),
KEY (gravida)
)ENGINE=MyISAM AS(
SELECT SQL_BIG_RESULT
tt.HOSPCODE,tt.PID,tt.SEQ,tt.DATE_SERV,tt.GRAVIDA,tt.ancno,tt.GA,tt.ANCRESULT,tt.ANCPLACE,tt.PROVIDER,tt.D_UPDATE,pe.cid,pe.nation,pe.birth,pe.sex
FROM
anc tt LEFT JOIN person pe ON tt.HOSPCODE=pe.HOSPCODE AND tt.pid=pe.pid
WHERE
tt.date_serv BETWEEN @start_d AND @end_d
and tt.HOSPCODE=tt.ANCPLACE
#check มั๊ย
and mod11(pe.CID)=1
);


DROP TABLE IF EXISTS _zone6_person_anc;

CREATE TABLE IF NOT EXISTS _zone6_person_anc(
hospcode varchar(5) NOT NULL
,pid varchar(15) NOT NULL
,typearea varchar(1) NOT NULL
,cid VARCHAR(13) NOT NULL
,birth date
,sex VARCHAR(1) DEFAULT NULL
,nation VARCHAR(3) DEFAULT NULL
,occupat_new VARCHAR(4) DEFAULT NULL
,gravida VARCHAR(2) DEFAULT NULL

,g1_ga VARCHAR(2) DEFAULT NULL
,g1_date date
,g1_hospcode VARCHAR(5) DEFAULT NULL
,g1_input_hosp VARCHAR(5) DEFAULT NULL

,g2_ga VARCHAR(2) DEFAULT NULL
,g2_date date
,g2_hospcode VARCHAR(5) DEFAULT NULL
,g2_input_hosp VARCHAR(5) DEFAULT NULL

,g3_ga VARCHAR(2) DEFAULT NULL
,g3_date date
,g3_hospcode VARCHAR(5) DEFAULT NULL
,g3_input_hosp VARCHAR(5) DEFAULT NULL

,g4_ga VARCHAR(2) DEFAULT NULL
,g4_date date
,g4_hospcode VARCHAR(5) DEFAULT NULL
,g4_input_hosp VARCHAR(5) DEFAULT NULL

,g5_ga VARCHAR(2) DEFAULT NULL
,g5_date date
,g5_hospcode VARCHAR(5) DEFAULT NULL
,g5_input_hosp VARCHAR(5) DEFAULT NULL

,KEY (hospcode)
,KEY (pid)
,KEY (typearea)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

TRUNCATE TABLE _zone6_person_anc;

/*GROUP BY HOSPCODE,pid,Gravida */
INSERT IGNORE INTO _zone6_person_anc
(
hospcode,pid,typearea,cid,birth,sex,nation,occupat_new,gravida
)
(
SELECT pe.hospcode,pe.pid,pe.check_typearea,pe.cid,pe.BIRTH,pe.SEX,pe.NATION,pe.OCCUPATION_NEW,tt.GRAVIDA
FROM _zon6_tmp_anc as tt
LEFT JOIN t_person_db as pe
ON tt.hospcode=pe.HOSPCODE and tt.pid=pe.PID
WHERE LENGTH(pe.cid) = 13
GROUP BY pe.HOSPCODE,pe.pid,tt.GRAVIDA
ORDER BY pe.HOSPCODE,pe.pid
);
/*ครั้งที่ 1 ga ก่อน 12 wks*/
UPDATE _zone6_person_anc a INNER JOIN
( SELECT hospcode,pid,cid,date_serv ,gravida,ancplace,ga
FROM _zon6_tmp_anc WHERE ga <= 12
GROUP BY hospcode,pid,gravida
) as g ON a.hospcode=g.hospcode AND a.pid=g.pid AND g.gravida=a.gravida
SET a.g1_ga= g.ga ,a.g1_date=g.date_serv ,a.g1_hospcode =g.ancplace,a.g1_input_hosp= g.hospcode;

/*ครั้งที่ 2 ตรวจสอบตามเกณฑ์คุณภาพก่อน*/
UPDATE _zone6_person_anc a INNER JOIN
( SELECT hospcode,pid,cid,date_serv ,gravida,ancplace,ga
FROM _zon6_tmp_anc WHERE ga BETWEEN 16 AND 20
GROUP BY hospcode,pid,gravida
) as g ON a.hospcode=g.hospcode AND a.pid=g.pid AND g.gravida=a.gravida
SET a.g2_ga= g.ga ,a.g2_date=g.date_serv,a.g2_hospcode =g.ancplace,a.g2_input_hosp= g.hospcode;
/*ครั้งที่ 2 หากไม่ผ่านตามเกณฑ์คุณภาพก็ให้ใช้ตามเกณฑ์สิทธิประโยชน์*/
UPDATE _zone6_person_anc a INNER JOIN
( SELECT hospcode,pid,cid,date_serv ,gravida,ancplace,ga
FROM _zon6_tmp_anc WHERE ga BETWEEN 13 AND 20
GROUP BY hospcode,pid,gravida
) as g ON a.hospcode=g.hospcode AND a.pid=g.pid AND g.gravida=a.gravida
SET a.g2_ga= g.ga ,a.g2_date=g.date_serv,a.g2_hospcode =g.ancplace,a.g2_input_hosp= g.hospcode
WHERE ISNULL(a.g2_date) OR ISNULL(a.g2_ga);

/*ครั้งที่ 3 ตรวจสอบตามเกณฑ์คุณภาพก่อน*/
UPDATE _zone6_person_anc a INNER JOIN
(
SELECT hospcode,pid,cid,date_serv ,gravida,ancplace,ga
FROM _zon6_tmp_anc WHERE ga BETWEEN 24 AND 28
GROUP BY hospcode,pid,gravida
) as g ON a.hospcode=g.hospcode AND a.pid=g.pid AND g.gravida=a.gravida
SET a.g3_ga= g.ga ,a.g3_date=g.date_serv ,a.g3_hospcode =g.ancplace ,a.g3_input_hosp= g.hospcode;
/*ครั้งที่ 3 หากไม่ผ่านตามเกณฑ์คุณภาพก็ให้ใช้ตามเกณฑ์สิทธิประโยชน์*/
UPDATE _zone6_person_anc a INNER JOIN
(
SELECT hospcode,pid,cid,date_serv ,gravida,ancplace,ga
FROM _zon6_tmp_anc WHERE ga BETWEEN 26 AND 28
GROUP BY hospcode,pid,gravida
) as g ON a.hospcode=g.hospcode AND a.pid=g.pid AND g.gravida=a.gravida
SET a.g3_ga= g.ga ,a.g3_date=g.date_serv ,a.g3_hospcode =g.ancplace ,a.g3_input_hosp= g.hospcode
WHERE ISNULL(a.g3_date) OR ISNULL(a.g3_ga);

/*ครั้งที่ 4 ตรวจสอบตามเกณฑ์คุณภาพก่อน*/
UPDATE _zone6_person_anc a INNER JOIN
(
SELECT hospcode,pid,cid,date_serv ,gravida,ancplace,ga
FROM _zon6_tmp_anc WHERE ga BETWEEN 30 AND 34
GROUP BY hospcode,pid,gravida
) as g ON a.hospcode=g.hospcode AND a.pid=g.pid AND g.gravida=a.gravida
SET a.g4_ga= g.ga ,a.g4_date=g.date_serv ,a.g4_hospcode =g.ancplace ,a.g4_input_hosp= g.hospcode;
/*ครั้งที่ 4 หากไม่ผ่านตามเกณฑ์คุณภาพก็ให้ใช้ตามเกณฑ์สิทธิประโยชน์*/
UPDATE _zone6_person_anc a INNER JOIN
(
SELECT hospcode,pid,cid,date_serv ,gravida,ancplace,ga
FROM _zon6_tmp_anc WHERE ga BETWEEN 29 AND 34
GROUP BY hospcode,pid,gravida
) as g ON a.hospcode=g.hospcode AND a.pid=g.pid AND g.gravida=a.gravida
SET a.g4_ga= g.ga ,a.g4_date=g.date_serv ,a.g4_hospcode =g.ancplace ,a.g4_input_hosp= g.hospcode
WHERE ISNULL(a.g4_date) OR ISNULL(a.g4_ga);

/*ครั้งที่ 5 */
UPDATE _zone6_person_anc a INNER JOIN
(
SELECT hospcode,pid,cid,date_serv ,gravida,ancplace,ga
FROM _zon6_tmp_anc WHERE ga BETWEEN 36 AND 40
GROUP BY hospcode,pid,gravida
) as g ON a.hospcode=g.hospcode AND a.pid=g.pid AND g.gravida=a.gravida
SET a.g5_ga= g.ga ,a.g5_date=g.date_serv ,a.g5_hospcode =g.ancplace ,a.g5_input_hosp= g.hospcode;


#ปรับปรุงสิทธิ์จาก DBPOP
alter table _zone6_person_anc add maininscl varchar(5) DEFAULT null;
UPDATE _zone6_person_anc,dbpop
set _zone6_person_anc.maininscl=dbpop.MainInScl
WHERE _zone6_person_anc.cid=dbpop.PID;
#ดูว่า ฝากครั้งที่2ถึง5 มากี่ครั้งไว้คิดคะแนน
alter table _zone6_person_anc add anc2 INTEGER(1) DEFAULT '0';
alter table _zone6_person_anc add anc_check varchar(2) DEFAULT null;
update _zone6_person_anc,
(
SELECT
_zone6_person_anc.hospcode,
_zone6_person_anc.pid,
_zone6_person_anc.cid,
_zone6_person_anc.gravida,
sum(case when _zone6_person_anc.g2_date is not null then 1 else 0 end)
+sum(case when _zone6_person_anc.g3_date is not null then 1 else 0 end)
+sum(case when _zone6_person_anc.g4_date is not null then 1 else 0 end)
+sum(case when _zone6_person_anc.g5_date is not null then 1 else 0 end)
as anc2
FROM
_zone6_person_anc
GROUP BY
_zone6_person_anc.hospcode,
_zone6_person_anc.pid,
_zone6_person_anc.cid,
_zone6_person_anc.gravida
) as s
set
_zone6_person_anc.anc2=s.anc2
where
_zone6_person_anc.hospcode=s.hospcode and
_zone6_person_anc.pid=s.pid AND
_zone6_person_anc.cid=s.cid AND
_zone6_person_anc.gravida=s.gravida;

#ตัดคนที่ซ้ำซ้อน cid ออกไปก่อน โดยสร้างตารางพักข้อมูล แล้วลบข้อมูล


DROP TABLE IF EXISTS _zone6_person_anc_dup;
CREATE TABLE IF NOT EXISTS _zone6_person_anc_dup
(
SELECT
_zone6_person_anc.hospcode,
_zone6_person_anc.pid,
_zone6_person_anc.typearea,
_zone6_person_anc.cid,
_zone6_person_anc.birth,
_zone6_person_anc.sex,
_zone6_person_anc.nation,
_zone6_person_anc.occupat_new,
_zone6_person_anc.gravida,
_zone6_person_anc.g1_ga,
_zone6_person_anc.g1_date,
_zone6_person_anc.g1_hospcode,
_zone6_person_anc.g1_input_hosp,
_zone6_person_anc.g2_ga,
_zone6_person_anc.g2_date,
_zone6_person_anc.g2_hospcode,
_zone6_person_anc.g2_input_hosp,
_zone6_person_anc.g3_ga,
_zone6_person_anc.g3_date,
_zone6_person_anc.g3_hospcode,
_zone6_person_anc.g3_input_hosp,
_zone6_person_anc.g4_ga,
_zone6_person_anc.g4_date,
_zone6_person_anc.g4_hospcode,
_zone6_person_anc.g4_input_hosp,
_zone6_person_anc.g5_ga,
_zone6_person_anc.g5_date,
_zone6_person_anc.g5_hospcode,
_zone6_person_anc.g5_input_hosp,
_zone6_person_anc.maininscl,
_zone6_person_anc.anc2,
_zone6_person_anc.anc_check
FROM
_zone6_person_anc
WHERE
_zone6_person_anc.cid in(
SELECT
_zone6_person_anc.cid
FROM
_zone6_person_anc
GROUP BY
_zone6_person_anc.cid
HAVING
count(_zone6_person_anc.pid)>1
)
ORDER BY
cid
);

#ขั้นตอนการลบmarkว่าเรคคอร์ดใหน error ก่อนในfield ชื่อ anc_check ว่า'er'แล้วค่อยลบออก
UPDATE
_zone6_person_anc as a,_zone6_person_anc_dup as b
set a.anc_check='er'
WHERE
a.hospcode=b.hospcode AND
a.pid=b.pid AND
a.cid=b.cid AND
a.gravida=b.gravida;

DELETE
FROM
_zone6_person_anc
WHERE
_zone6_person_anc.anc_check='er';

#ตรวจสอบว่าเรคคอร์ดใหนควรpenddind เอาไปสร้างตารางใหม่ แล้วลบออกจากตารางซ้ำ _zone6_person_anc_dup

DROP TABLE IF EXISTS _zone6_person_anc_pendding;
CREATE TABLE IF NOT EXISTS _zone6_person_anc_pendding
(
SELECT
_zone6_person_anc_dup.hospcode,
_zone6_person_anc_dup.pid,
_zone6_person_anc_dup.typearea,
_zone6_person_anc_dup.cid,
_zone6_person_anc_dup.birth,
_zone6_person_anc_dup.sex,
_zone6_person_anc_dup.nation,
_zone6_person_anc_dup.occupat_new,
_zone6_person_anc_dup.gravida,
_zone6_person_anc_dup.g1_ga,
_zone6_person_anc_dup.g1_date,
_zone6_person_anc_dup.g1_hospcode,
_zone6_person_anc_dup.g1_input_hosp,
_zone6_person_anc_dup.g2_ga,
_zone6_person_anc_dup.g2_date,
_zone6_person_anc_dup.g2_hospcode,
_zone6_person_anc_dup.g2_input_hosp,
_zone6_person_anc_dup.g3_ga,
_zone6_person_anc_dup.g3_date,
_zone6_person_anc_dup.g3_hospcode,
_zone6_person_anc_dup.g3_input_hosp,
_zone6_person_anc_dup.g4_ga,
_zone6_person_anc_dup.g4_date,
_zone6_person_anc_dup.g4_hospcode,
_zone6_person_anc_dup.g4_input_hosp,
_zone6_person_anc_dup.g5_ga,
_zone6_person_anc_dup.g5_date,
_zone6_person_anc_dup.g5_hospcode,
_zone6_person_anc_dup.g5_input_hosp,
_zone6_person_anc_dup.maininscl,
_zone6_person_anc_dup.anc2,
_zone6_person_anc_dup.anc_check
FROM
_zone6_person_anc_dup
LEFT JOIN
(SELECT
_zone6_person_anc_dup.cid,
_zone6_person_anc_dup.hospcode,
count(_zone6_person_anc_dup.gravida) as ga
FROM
_zone6_person_anc_dup
GROUP BY
_zone6_person_anc_dup.cid,
_zone6_person_anc_dup.hospcode) as d
on _zone6_person_anc_dup.hospcode=d.hospcode and _zone6_person_anc_dup.cid=d.cid
WHERE
d.ga>1
);

UPDATE
_zone6_person_anc_dup as a,_zone6_person_anc_pendding as b
SET
a.anc_check='pe'
WHERE
a.hospcode=b.hospcode AND
a.pid=b.pid and
a.cid=b.cid and
a.gravida=b.gravida;


#นำข้อมูลที่สงสัยว่าผิดกลับไปรวมใหม่ตอนทำรายงานค่อยตัดสินใจว่าเอาหรือไม่เอา
INSERT INTO _zone6_person_anc
(
_zone6_person_anc.hospcode,
_zone6_person_anc.pid,
_zone6_person_anc.typearea,
_zone6_person_anc.cid,
_zone6_person_anc.birth,
_zone6_person_anc.sex,
_zone6_person_anc.nation,
_zone6_person_anc.occupat_new,
_zone6_person_anc.gravida,
_zone6_person_anc.g1_ga,
_zone6_person_anc.g1_date,
_zone6_person_anc.g1_hospcode,
_zone6_person_anc.g1_input_hosp,
_zone6_person_anc.g2_ga,
_zone6_person_anc.g2_date,
_zone6_person_anc.g2_hospcode,
_zone6_person_anc.g2_input_hosp,
_zone6_person_anc.g3_ga,
_zone6_person_anc.g3_date,
_zone6_person_anc.g3_hospcode,
_zone6_person_anc.g3_input_hosp,
_zone6_person_anc.g4_ga,
_zone6_person_anc.g4_date,
_zone6_person_anc.g4_hospcode,
_zone6_person_anc.g4_input_hosp,
_zone6_person_anc.g5_ga,
_zone6_person_anc.g5_date,
_zone6_person_anc.g5_hospcode,
_zone6_person_anc.g5_input_hosp,
_zone6_person_anc.maininscl,
_zone6_person_anc.anc2,
_zone6_person_anc.anc_check
)
SELECT
_zone6_person_anc_dup.hospcode,
_zone6_person_anc_dup.pid,
_zone6_person_anc_dup.typearea,
_zone6_person_anc_dup.cid,
_zone6_person_anc_dup.birth,
_zone6_person_anc_dup.sex,
_zone6_person_anc_dup.nation,
_zone6_person_anc_dup.occupat_new,
_zone6_person_anc_dup.gravida,
_zone6_person_anc_dup.g1_ga,
_zone6_person_anc_dup.g1_date,
_zone6_person_anc_dup.g1_hospcode,
_zone6_person_anc_dup.g1_input_hosp,
_zone6_person_anc_dup.g2_ga,
_zone6_person_anc_dup.g2_date,
_zone6_person_anc_dup.g2_hospcode,
_zone6_person_anc_dup.g2_input_hosp,
_zone6_person_anc_dup.g3_ga,
_zone6_person_anc_dup.g3_date,
_zone6_person_anc_dup.g3_hospcode,
_zone6_person_anc_dup.g3_input_hosp,
_zone6_person_anc_dup.g4_ga,
_zone6_person_anc_dup.g4_date,
_zone6_person_anc_dup.g4_hospcode,
_zone6_person_anc_dup.g4_input_hosp,
_zone6_person_anc_dup.g5_ga,
_zone6_person_anc_dup.g5_date,
_zone6_person_anc_dup.g5_hospcode,
_zone6_person_anc_dup.g5_input_hosp,
_zone6_person_anc_dup.maininscl,
_zone6_person_anc_dup.anc2,
_zone6_person_anc_dup.anc_check
FROM
_zone6_person_anc_dup
;
#ลบตารางไม่ใช้
DROP TABLE IF EXISTS _zone6_person_anc_pendding;
DROP TABLE IF EXISTS _zon6_tmp_anc;
DROP TABLE IF EXISTS _zone6_person_anc_dup;


#รายงาน
SELECT
'g1_ga' AS id
,a.hospcode
,sum(case when a.g1_ga is not null then 1 else 0 end) as total
,sum(case when a.nation in('99','099') and a.maininscl IN ('WEL','UCS') and a.g1_ga is not null then 1 else 0 end) as uc
,sum(case when a.nation in('99','099') and a.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 a.g1_ga is not null then 1 else 0 end) as nonuc
,sum(case when a.nation in('99','099') and a.maininscl is null and a.g1_ga is not null then 1 else 0 end) as other
,sum(case when a.nation not in('99','099') and a.g1_ga is not null then 1 else 0 end) as alien
FROM
_zone6_person_anc as a
WHERE
a.anc_check is NULL or a.anc_check<>'pe'
GROUP BY
a.hospcode

UNION

SELECT
'anc2' AS id
,a.hospcode
,sum(case when a.anc2 is not null then 1 else 0 end) as total_person
,sum(case when a.nation in('99','099') and a.maininscl IN ('WEL','UCS') and a.anc2 is not null then a.anc2 else 0 end) as uc
,sum(case when a.nation in('99','099') and a.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 a.anc2 is not null then a.anc2 else 0 end) as nonuc
,sum(case when a.nation in('99','099') and a.maininscl is null and a.anc2 is not null then a.anc2 else 0 end) as other
,sum(case when a.nation not in('99','099') and a.anc2 is not null then a.anc2 else 0 end) as alien
FROM
_zone6_person_anc as a
WHERE
a.anc_check is NULL or a.anc_check<>'pe'
GROUP BY
a.hospcode;

#COMPLETE

#PROVIDER1_SQL#
Script Cron
Force Script Cron0
Active1
Client Office Type
Create Date2017-08-15 04:12:06
Last Update2018-05-18 11:28:32