• Home
  • My Scripts
  • 5. คัดกรองทันตกรรมในเรือนจำ

5. คัดกรองทันตกรรมในเรือนจำ

Update Delete

ID811
Parent ID59
Table Nameprison64_05_dental_screen
Title5. คัดกรองทันตกรรมในเรือนจำ
Description
Script#SQL_OPTIONS#
PROVIDERS=1
PROVIDER1=43STD
PROVIDER1_VALIDATE_TABLES=ncdscreen,t_person_db
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 @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 @datestart:='2020-10-15';
SET @dateend:='2021-07-31';

DROP TABLE IF EXISTS prison64_05_dental_screen;
CREATE TABLE IF NOT EXISTS prison64_05_dental_screen(
`HOSPCODE` varchar(5) NOT NULL,
`PID` varchar(15) NOT NULL,
`CID` varchar(13) DEFAULT NULL,
`BIRTH` date DEFAULT NULL,
`AGE` varchar(3) DEFAULT NULL,
`D_SERV_DENTAL` date DEFAULT NULL,
`H_SERV_DENTAL` varchar(5) DEFAULT NULL,
`RESULT_DENTAL` varchar(50) DEFAULT NULL, ###เพิ่มผลการตรวจฟันจากแฟ้มdental
`D_SERV_PROCED` date DEFAULT NULL,
`H_SERV_PROCED` varchar(5) DEFAULT NULL,
`D_SERV_INSTRUC` date DEFAULT NULL,
`H_SERV_INSTRUC` varchar(5) DEFAULT NULL,

PRIMARY KEY (HOSPCODE,PID),
KEY (cid)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 IGNORE AS (
SELECT HOSPCODE,PID,CID,BIRTH,NULL`AGE` ,
NULL`D_SERV_DENTAL` ,NULL`H_SERV_DENTAL` ,
NULL`D_SERV_PROCED` ,NULL`H_SERV_PROCED` ,
NULL`D_SERV_INSTRUC` ,NULL`H_SERV_INSTRUC`

FROM prison64_00_prisonerDB
);

#สร้างtemp table specialpp & diagnosis_opd
DROP TABLE IF EXISTS `_tmp_dental`;
CREATE TEMPORARY TABLE `_tmp_dental` (
`hospcode` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '',
`pid` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '',
`seq` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`date_serv` date NULL DEFAULT '0000-00-00',
`file` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '',
`cid` char(13) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`result` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, ###เพิ่มผลการตรวจฟันจากแฟ้มdental
INDEX `cid`(`cid`) USING BTREE
)
SELECT s.HOSPCODE,s.PID,s.SEQ,s.DATE_SERV,'DENTAL' as 'file',p.CID ,
IF(s.PCARIES=0 and s.NEED_FLUORIDE<>1 and s.NEED_SCALING<>1,'ปกติ'
,CONCAT(IF(s.PCARIES<>0,CONCAT('ฟันผุ ',s.PCARIES,' ซี่ '),''),IF(s.NEED_FLUORIDE=1,'ต้องเคลือบฟลูออไรด์ ',''),IF(s.NEED_SCALING=1,'ต้องขูดหินน้ำลาย ',''
) ))as 'result'###เพิ่มผลการตรวจฟันจากแฟ้มdental
FROM dental s INNER JOIN person p ON s.HOSPCODE=p.HOSPCODE AND s.PID=p.PID
WHERE s.DATE_SERV BETWEEN @datestart AND @dateend
ORDER BY DATE_SERV DESC ; #เพื่อได้บริการล่าสุด

DROP TABLE IF EXISTS `_tmp_procedure_opd`;
CREATE TEMPORARY TABLE `_tmp_procedure_opd` (
`hospcode` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '',
`pid` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '',
`seq` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`date_serv` date NULL DEFAULT '0000-00-00',
`PROCEDCODE` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '',
`file` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '',
`cid` char(13) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
INDEX `cid`(`cid`) USING BTREE
)
SELECT s.HOSPCODE,s.PID,s.SEQ,s.DATE_SERV,s.PROCEDCODE ,'procedure_opd' as 'file',p.CID
FROM procedure_opd s INNER JOIN person p ON s.HOSPCODE=p.HOSPCODE AND s.PID=p.PID
WHERE s.DATE_SERV BETWEEN @datestart AND @dateend
ORDER BY DATE_SERV DESC #เพื่อได้บริการล่าสุด
;

#หยอดผลงาน จากแฟ้ม DENTAL
UPDATE prison64_05_dental_screen p,_tmp_dental s
SET p.AGE= AGE(s.DATE_SERV,p.BIRTH,'y')
,p.D_SERV_DENTAL=s.DATE_SERV,p.H_SERV_DENTAL=s.HOSPCODE
,p.RESULT_DENTAL=s.result ###เพิ่มผลการตรวจฟันจากแฟ้มdental
WHERE p.CID=s.CID
;
#หยอดผลงาน จากแฟ้ม procedure_opd ตรวจฟัน
UPDATE prison64_05_dental_screen p,_tmp_procedure_opd s
SET p.AGE= AGE(s.DATE_SERV,p.BIRTH,'y')
,p.D_SERV_PROCED=s.DATE_SERV ,p.H_SERV_PROCED=s.HOSPCODE
WHERE p.CID=s.CID AND s.PROCEDCODE='2330011'
;
#หยอดผลงาน จากแฟ้ม procedure_opd ให้คำแนะนำ
UPDATE prison64_05_dental_screen p,_tmp_procedure_opd s
SET p.AGE= AGE(s.DATE_SERV,p.BIRTH,'y')
,p.D_SERV_INSTRUC=s.DATE_SERV,p.H_SERV_INSTRUC=s.HOSPCODE
WHERE p.CID=s.CID AND (s.PROCEDCODE='2338610' OR s.PROCEDCODE= '2338611')
;

# INDIVIDUAL
SELECT h.hospcode,h.hosname ,p.cid ,p.AGE as age
,GROUP_CONCAT('[',p.H_SERV_DENTAL,'] ',p.D_SERV_DENTAL,' ผล : ',p.result_dental) as date_serv_dental ###เพิ่มผลการตรวจฟันจากแฟ้มdental
,GROUP_CONCAT('[',p.H_SERV_PROCED,'] ',p.D_SERV_PROCED) as date_serv_procedure
,GROUP_CONCAT('[',p.H_SERV_INSTRUC,'] ',p.D_SERV_INSTRUC) as date_serv_instruc
FROM prison64_05_dental_screen p LEFT JOIN prison64_00_prisonerDB h ON p.CID=h.CID
WHERE p.AGE BETWEEN 15 AND 120 AND H_SERV_INSTRUC <>'' AND (H_SERV_DENTAL <>'' OR D_SERV_PROCED <>'')
GROUP BY p.CID
;




#PROVIDER1_SQL#
Script Cron
Force Script Cron0
Active1
Client Office Type
Create Date2020-05-14 11:42:09
Last Update2021-03-25 01:28:40