• Home
  • My Scripts
  • PPA2 หญิงอายุน้อยกว่า 20 ปีได้รับการคุมกำเนิด

PPA2 หญิงอายุน้อยกว่า 20 ปีได้รับการคุมกำเนิด

Update Delete

ID838
Parent ID64
Table Name65_ppa_young_fp_2
TitlePPA2 หญิงอายุน้อยกว่า 20 ปีได้รับการคุมกำเนิด
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;
SET @start_d:=concat(@b_year,'0301');
SET @end_d:=concat(@b_year,'0630');
SET @end_target:=DATE_ADD(@end_d,INTERVAL -42 day);
SET SESSION group_concat_max_len = 1000000;

##select @end_target as cc;
DROP TABLE IF EXISTS _ppa65_labor_temp;
CREATE TABLE IF NOT EXISTS _ppa65_labor_temp(
lmp date DEFAULT NULL,
edc date DEFAULT NULL,
nation VARCHAR(3) DEFAULT NULL,
bdate date DEFAULT NULL,
teen VARCHAR(1) DEFAULT NULL,
abort VARCHAR(1) DEFAULT NULL,
date_disch date DEFAULT NULL,
fp_date date DEFAULT NULL,
fp VARCHAR(1) DEFAULT NULL,
fptype VARCHAR(1) DEFAULT NULL,
areacode VARCHAR(8) DEFAULT NULL,
fphosp VARCHAR(5) DEFAULT NULL,
instype VARCHAR(5) DEFAULT NULL,##inscl from service
typearea VARCHAR(1) DEFAULT NULL,
check_hosp VARCHAR(5) DEFAULT NULL,
check_typearea VARCHAR(1) DEFAULT NULL,
mainInscl VARCHAR(5) DEFAULT NULL,
hmain VARCHAR(5) DEFAULT NULL,
hsub VARCHAR(5) DEFAULT NULL,
PRIMARY KEY(hospcode,pid),
KEY(hospcode),
KEY(pid),
KEY(cid),
KEY(bdate),
KEY(bhosp),
KEY(bresult),
KEY(lborn),
KEY(age_y)
) ENGINE MyIsam as
SELECT
l.*,p.birth,TIMESTAMPDIFF(year,p.BIRTH,l.BDATE) as age_y,null as nation ,null as teen,null as abort,null date_disch,null fp_date,
null as fp,null as fptype,null as areacode,null as fphosp,null as instype,null as typearea,null as check_hosp,null as check_typearea,
null as maininscl,null as hmain,null as hsub
FROM
labor l left join t_person_db p on l.HOSPCODE=p.hospcode and l.pid=p.pid
WHERE l.BDATE BETWEEN @start_d AND @end_target and p.DISCHARGE='9'
and p.NATION in(99)
GROUP BY hospcode,pid;
###################

UPDATE _ppa65_labor_temp l
INNER JOIN (
SELECT * FROM diagnosis_ipd d FORCE INDEX(hospcode)
WHERE ((SUBSTR(d.diagcode,1,3) BETWEEN 'O03' AND 'O08')
OR (SUBSTR(d.diagcode,1,3) BETWEEN 'O62' AND 'O84')
OR d.diagcode in('O601','O602','O603')) and (d.DATETIME_ADMIT BETWEEN @start_d and @end_d)
GROUP BY d.HOSPCODE,d.PID
) d ON l.hospcode=d.hospcode AND l.pid=d.pid
SET l.BRESULT=d.diagcode
WHERE ((SUBSTR(l.BRESULT,1,3) not BETWEEN 'O03' AND 'O08') OR
(SUBSTR(diagcode,1,3) not BETWEEN 'O62' AND 'O84') OR diagcode not in('O601','O602','O603'));
###################################################################################


UPDATE _ppa65_labor_temp SET LBORN=1
WHERE LBORN=0 AND SBORN=0 AND (SUBSTR(BRESULT,1,3) BETWEEN 'O62' AND 'O84'
OR SUBSTR(BRESULT,1,3) in('O601','O602','O603')) ;
############ส่วนเพิ่ม update labor.cid = t_person_db.cid กรณีที่ labor.cid = null###############
UPDATE _ppa65_labor_temp l INNER JOIN t_person_db p ON p.HOSPCODE=l.HOSPCODE AND p.PID=l.PID
SET l.cid=p.cid where l.cid is null or l.cid='';
############################################################################

UPDATE _ppa65_labor_temp l INNER JOIN t_person_db p ON p.HOSPCODE=l.HOSPCODE AND p.PID=l.PID
SET l.nation=p.NATION;

UPDATE _ppa65_labor_temp SET teen=1 WHERE age_y < 20;

UPDATE _ppa65_labor_temp SET abort=1 WHERE lborn =0
OR SUBSTR(bresult,1,3) BETWEEN 'O03' AND 'O08' ;
#AND SUBSTR(bresult,1,3) BETWEEN 'O03' AND 'O08' ;

DELETE FROM _ppa65_labor_temp WHERE age_y <10 OR age_y is NULL;
##join fp ที่ date_serv อยู่ในช่วง42วัน
UPDATE _ppa65_labor_temp l
LEFT JOIN (select p.cid,SUBSTRING_INDEX(group_concat(distinct fp.date_serv order by fp.date_serv),',',1) as 'date_serv',
SUBSTRING_INDEX(group_concat(fp.fptype order by fp.date_serv),',',1) as 'fptype',
SUBSTRING_INDEX(group_concat(distinct fp.fpplace order by fp.date_serv),',',1) as 'fpplace' from fp
left join t_person_db p on fp.hospcode=p.hospcode and fp.pid=p.pid
where fp.date_serv between @start_d and @end_d group by p.cid) f ##
ON l.cid=f.cid AND f.DATE_SERV BETWEEN l.BDATE AND DATE_ADD(l.BDATE,INTERVAL 42 day) AND f.fptype in(1,2,3,4,5,6,7)
SET fp_date= f.date_serv, l.fptype=f.fptype,l.fphosp = f.fpplace;

#UPDATE ppa65_labor_temp SET fp=1
#WHERE fp_date BETWEEN BDATE and date_disch ;
###update fp
UPDATE _ppa65_labor_temp SET fp=1
WHERE TIMESTAMPDIFF(day,BDATE,fp_date) <= 42;
##update typearea 1,2,3,4
UPDATE _ppa65_labor_temp l INNER JOIN t_person_db p ON p.HOSPCODE=l.HOSPCODE AND p.PID=l.PID
SET l.typearea=p.TYPEAREA;

UPDATE _ppa65_labor_temp l INNER JOIN t_person_db p ON p.cid=l.cid
SET l.check_hosp=p.check_hosp;

UPDATE _ppa65_labor_temp l INNER JOIN t_person_db p ON p.cid=l.cid
SET l.check_typearea=p.check_typearea;
##update areacode
UPDATE _ppa65_labor_temp l LEFT JOIN chospital h ON l.hospcode=h.hoscode
AND h.provcode IN (@prov_c)
SET l.areacode=concat(h.provcode,h.distcode,h.subdistcode,h.mu);
##update สิทธิ์ จาก dbpop
#UPDATE _ppa65_labor_temp l LEFT JOIN dbpop_6303 d on l.cid = d.pid
#set l.hmain=d.HMain,l.hsub=d.Hsub,l.mainInscl=d.MainInScl;
##update inscltype สิทธิ์ที่ใช้รับบริการ
#UPDATE _ppa65_labor_temp l LEFT JOIN service s on l.pid=s.pid and l.hospcode=s.hospcode and l.fp_date=s.date_serv
#set l.instype=s.INSTYPE;

##update inscltype สิทธิ์ที่ใช้รับบริการที่มาในช่วง ปีQOF
#UPDATE _ppa65_labor_temp l LEFT JOIN
#(select l.HOSPCODE,l.pid, SUBSTRING_INDEX( GROUP_CONCAT(DISTINCT s.INSTYPE order by s.date_serv desc),',',1) as instype
#from _ppa65_labor_temp l LEFT JOIN service s on l.pid=s.pid and l.hospcode=s.hospcode
# where s.DATE_SERV BETWEEN @start_d and @end_d group by l.hospcode,l.pid) s2
#on l.pid=s2.pid and l.hospcode=s2.hospcode
#set l.instype=s2.instype where l.instype is null;

##update inscltype ด้วยdbpop_6303
#UPDATE _ppa65_labor_temp l LEFT JOIN dbpop_6303 d on l.cid = d.pid
#set l.instype=d.MainInScl where l.instype is null;

#update hmain hsub ที่ว่างด้วยhospcode
#UPDATE _ppa65_labor_temp set hmain = HOSPCODE where hmain is null or hmain='';
#UPDATE _ppa65_labor_temp set hsub = HOSPCODE where hsub is null or hsub='';

##add field hospcode2##เพิ่มใหม่######################################
ALTER table _ppa65_labor_temp add hospcode2 varchar(5);
update _ppa65_labor_temp l left join chospital h on l.hospcode=h.hoscode
set hospcode2=if( h.hostype in(5,6,7,11,12,15) and l.HOSPCODE=l.BHOSP and l.BHOSP <> null,l.hospcode,l.BHOSP);


select * from _ppa65_labor_temp ;

###########END###############



#PROVIDER1_SQL#
Script Cron
Force Script Cron0
Active1
Client Office Type
Create Date2022-04-01 01:56:54
Last Update2022-08-02 09:46:01