• Home
  • My Scripts
  • 1) 64 ยาเม็ดเสริมธาตุเหล็ก ป.1-6

1) 64 ยาเม็ดเสริมธาตุเหล็ก ป.1-6

Update Delete

ID830
Parent ID62
Table Name64_iron_tablet_result
Title1) 64 ยาเม็ดเสริมธาตุเหล็ก ป.1-6
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 @prov_c := @provcode;
SET @b_year:='2021';
SET @c_year = '2563';
SET @term = '1';
SET @start_d:=concat(@b_year-1,'-10-15');
SET @end_d:=concat(@b_year,'-07-31');

#----------------------------------
#สร้างตาราง tmp นักเรียน ป1-6
DROP TABLE IF EXISTS tmp_iron_student;
CREATE TABLE IF NOT EXISTS tmp_iron_student(INDEX(AcademicYear,Semester,SchoolID,StudentID,PersonID))
ENGINE=MyISAM AS
(
SELECT @prov_c AS 'province',b.hospcode AS 'School_Hosp',a.AcademicYear,a.Semester,a.SchoolID,b.SCHOOLNAME,a.StudentID,a.PersonID,
CONCAT(a.FirstName,' ',a.LastName) AS 'Fname',
a.Birthdate,'00' AS 'Age_y',
CONCAT(a.GradeLevelCode,'-',c.GradeName) AS 'Gragd'
FROM student a
LEFT OUTER JOIN school b ON (a.SchoolID=b.SCHOOLID)
LEFT OUTER JOIN cgrade c ON (a.GradeLevelCode=c.GradeID)
WHERE a.AcademicYear = @c_year
AND c.GradeName LIKE 'ประถม%'
AND b.hospcode IS NOT NULL
GROUP BY PersonID
ORDER BY b.hospcode
);
ALTER TABLE tmp_iron_student ADD PRIMARY KEY (PersonID);
#เอาวันเกิดจาก person มาปรับ เนื่องจาก แฟ้ม strdent ไม่มีวันเกิด
UPDATE tmp_iron_student a ,t_person_cid b SET a.Birthdate = b.BIRTH WHERE a.PersonID=b.CID;
#คำนวนอายุ จากวันปัจจุบัน
UPDATE IGNORE tmp_iron_student a SET a.Age_y = TIMESTAMPDIFF(YEAR,a.Birthdate,@start_d);
#ลบข้อมูลที่อายุเกินออก
DELETE FROM tmp_iron_student WHERE Age_y > 14;
DELETE FROM tmp_iron_student WHERE Age_y < 6;
#-----------------------------------

#สร้างตาราง tmp drug
DROP TABLE IF EXISTS tmp_iron_specialpp_student_drug;
CREATE TABLE IF NOT EXISTS tmp_iron_specialpp_student_drug(PRIMARY KEY(CID))
ENGINE=MyISAM AS
(
SELECT
a.HOSPCODE,a.PID,b.CID,
GROUP_CONCAT(a.hospcode,' ' ORDER BY a.date_serv) AS 'drug_hosp',
GROUP_CONCAT(a.DATE_SERV,' ' ORDER BY a.date_serv) AS 'drug_date',
GROUP_CONCAT(a.DIDSTD,' ' ORDER BY a.date_serv) AS 'drug_didstd',
'Y' AS 'drug'
FROM drug_opd a
INNER JOIN t_person_db b ON (a.HOSPCODE=b.HOSPCODE AND a.PID=b.PID)
WHERE SUBSTR(a.DIDSTD,1,19) IN(
'2020301201378199203','1004880000042031203','1004890000043201218','1004880000042031217',
'1004880000042031218','1004890000041921218','1004890000043201203','1004890000042031203')
AND a.DATE_SERV BETWEEN @start_d AND @end_d
GROUP BY b.cid
ORDER BY a.hospcode
);

#สร้างตาราง tmp specialpp
DROP TABLE IF EXISTS tmp_iron_specialpp_student_1f8;
CREATE TABLE IF NOT EXISTS tmp_iron_specialpp_student_1f8(PRIMARY KEY(CID))
ENGINE=MyISAM AS
(
SELECT
a.HOSPCODE,b.CID,
GROUP_CONCAT(a.HOSPCODE,' ' ORDER BY a.DATE_SERV) AS 'spp_hosp',
GROUP_CONCAT(a.DATE_SERV,' ' ORDER BY a.DATE_SERV) AS 'spp_date',
GROUP_CONCAT(a.PPSPECIAL ORDER BY a.DATE_SERV) AS 'specialpp'
FROM specialpp a
INNER JOIN t_person_db b ON (a.HOSPCODE=b.HOSPCODE AND a.PID=b.PID)
WHERE a.PPSPECIAL = '1F8'
AND a.DATE_SERV BETWEEN @start_d AND @end_d
GROUP BY b.CID
);

#สร้างตาราง specialpp+drug วันเดียวกัน เอาไว้ปรับปรุงตาราง
DROP TABLE IF EXISTS tmp_iron_specialpp_student_1f8drug;
CREATE TABLE IF NOT EXISTS tmp_iron_specialpp_student_1f8Drug(KEY(hospcode,pid,date_serv))
#--------------------------------------------------------------------------------------------------
ENGINE=MyISAM AS
#--------------------------------------------------------------------------------------------------
(
#--------------------------------------------------------------------------------------------------
SELECT a.HOSPCODE,a.PID,a.CID,e.DATE_SERV,e.C AS '1f8Drug'
#-----------------------------------------------------------
FROM t_person_db a
INNER JOIN
(
SELECT b.HOSPCODE,b.PID,b.DATE_SERV,'Y' AS 'C'
#-----------------------------------------------------------
FROM drug_opd b
INNER JOIN specialpp c ON (b.HOSPCODE=c.HOSPCODE AND b.PID=c.PID AND b.DATE_SERV=c.DATE_SERV)
WHERE c.PPSPECIAL = '1F8'
AND SUBSTR(b.DIDSTD,1,19) IN(
'2020301201378199203','1004890000043201218','1004880000042031218','1004890000041921218',
'1004880000042031217','1004880000042031203','1004890000043201203','1004890000042031203')
AND b.DATE_SERV BETWEEN @start_d AND @end_d
GROUP BY b.HOSPCODE,b.PID,b.DATE_SERV
)e ON (a.HOSPCODE=e.HOSPCODE AND a.PID=e.PID)
);

#เอาผลงานมา join กับนักเรียน
DROP TABLE IF EXISTS 1_iron_student;
CREATE TABLE IF NOT EXISTS 1_iron_student
ENGINE=MyISAM AS
#----------------------------------
(
SELECT a.*,b.drug_hosp,b.drug_date,c.spp_hosp,c.spp_date,
b.drug AS 'A', /*คอลัม A*/
d.1f8Drug AS 'C' /*คอลัม C*/
FROM tmp_iron_student a
LEFT OUTER JOIN tmp_iron_specialpp_student_drug b ON a.PersonID=b.CID
LEFT OUTER JOIN tmp_iron_specialpp_student_1f8 c ON a.PersonID=c.CID AND a.School_Hosp=c.hospcode
LEFT OUTER JOIN tmp_iron_specialpp_student_1f8drug d ON a.PersonID=d.CID AND a.School_Hosp=d.hospcode
GROUP BY a.PersonID,LEFT(a.Gragd,3)
);

#------------------------------------------------------------------------------
#เพ่ิมเตมจากวันที่ปรุชม
#สร้างตาราง tmp เด็กที่อายุ 6 - 14 ปี ที่มี cid ไม่ตรงกับฐานข้อมูลนักเรียน student
DROP TABLE IF EXISTS tmp_iron_non_student;
CREATE TABLE IF NOT EXISTS tmp_iron_non_student(PRIMARY KEY(PersonID))
ENGINE=MyISAM AS
#-----------------------------------
(
SELECT
@prov_c AS 'province',
a.HOSPCODE AS 'School_Hosp', NULL AS 'AcademicYear', NULL AS 'Semester',
NULL AS 'SchoolID', NULL AS 'SCHOOLNAME', NULL AS 'StudentID',
a.CID AS 'PersonID', a.`name` AS 'Fname,', a.BIRTH AS 'Birthdate',
TIMESTAMPDIFF(YEAR,a.BIRTH,@start_d) AS 'Age_y' , NULL AS 'Gragd'
FROM t_person_cid a
WHERE TIMESTAMPDIFF(YEAR,a.BIRTH,@start_d) BETWEEN 6 AND 14
AND a.DISCHARGE = '9'
AND a.NATION = '099'
AND a.CID NOT IN (SELECT PersonID FROM tmp_iron_student)
);
#join ผลงานกับ ตารางเด็ก 6-14 ปี
DROP TABLE IF EXISTS 1_iron_non_student;
CREATE TABLE IF NOT EXISTS 1_iron_non_student
ENGINE=MyISAM AS
#-----------------------------------------------------------------
(
SELECT a.*,b.drug_hosp,b.drug_date,c.spp_hosp,c.spp_date,
b.drug AS 'A', /*คอลัม A*/
d.1f8Drug AS 'C' /*คอลัม C*/
FROM tmp_iron_non_student a
INNER JOIN tmp_iron_specialpp_student_drug b ON a.PersonID=b.cid
LEFT OUTER JOIN tmp_iron_specialpp_student_1f8 c ON a.PersonID=c.cid AND a.School_Hosp=c.hospcode
LEFT OUTER JOIN tmp_iron_specialpp_student_1f8drug d ON a.PersonID=d.cid AND a.School_Hosp=d.hospcode
GROUP BY a.PersonID
);
#เอาข้อมูลจากตาราง tmp ไปรวมกับตารางนักเรียนก่อนหน้านี้
INSERT IGNORE INTO 1_iron_student (SELECT * FROM 1_iron_non_student);
#end เด็กอายุ 6-14 ปี
#---------------------------------------------------------------------------

#ลบตาราง tmp ออกทั้งหมด
DROP TABLE IF EXISTS 1_iron_non_student;
DROP TABLE IF EXISTS tmp_iron_specialpp_student_1f8;
DROP TABLE IF EXISTS tmp_iron_specialpp_student_1f8drug;
DROP TABLE IF EXISTS tmp_iron_specialpp_student_drug;


SELECT * FROM 1_iron_student;

#PROVIDER1_SQL#
Script Cron
Force Script Cron0
Active1
Client Office Type
Create Date2020-12-16 11:54:30
Last Update2021-08-05 11:35:44