1) 64 ยาเม็ดเสริมธาตุเหล็ก ป.1-6
ID | 830 |
---|---|
Parent ID | 62 |
Table Name | 64_iron_tablet_result |
Title | 1) 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 Cron | 0 |
Active | 1 |
Client Office Type | |
Create Date | 2020-12-16 11:54:30 |
Last Update | 2021-08-05 11:35:44 |
- Home
- About
- Privacy Policy
- Terms of Use
- Contact Us
© 2015. All Rights Reserved.