PPA1 เด็กกลุ่มเป้าหมาย 6-12 ปี ร้อยละ 70 ได้รับยาเม็ดเสริมธาตุเหล็ก
ID | 837 |
---|---|
Parent ID | 64 |
Table Name | 65_ppa_iron_htc_result_2 |
Title | PPA1 เด็กกลุ่มเป้าหมาย 6-12 ปี ร้อยละ 70 ได้รับยาเม็ดเสริมธาตุเหล็ก |
Description | |
Script | #SQL_OPTIONS# PROVIDERS=1 PROVIDER1=43STD PROVIDER1_VALIDATE_TABLES=student,t_person_cid,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 @prov_c := :provcode; SET @b_year:='2022'; SET @c_year = '2564'; # #SET @start_d:=concat(@b_year-1,'-10-01'); SET @start_d:=concat(@b_year,'-03-01'); SET @end_d:=concat(@b_year,'-07-30'); #---------------------------------- #สร้างตาราง 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, a.NationalityCode AS 'Nation', 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 AND a.NationalityCode = '099' 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 > 12; DELETE FROM tmp_iron_student WHERE Age_y < 6; #----------------------------------- #สร้างตาราง tmp drug DROP TABLE IF EXISTS tmp_iron_drug; CREATE TABLE IF NOT EXISTS tmp_iron_drug(PRIMARY KEY(CID)) ENGINE=MyISAM AS ( SELECT a.HOSPCODE,a.PID,b.CID, a.hospcode AS 'drug_hosp', a.DATE_SERV AS 'drug_date', a.DIDSTD AS 'drug_didstd', 'Y' AS 'drug' FROM drug_opd a /*20/04/64*/ 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', '323296','715616','855623','814105','814122','767400','737856','735615','786261','776508', '1198282','977553','695963','695985','851254','776549','851277') AND a.DATE_SERV BETWEEN @start_d AND @end_d GROUP BY b.cid ORDER BY a.hospcode ); #เอาผลงานมา join กับนักเรียน Student DROP TABLE IF EXISTS 1_iron_student; CREATE TABLE IF NOT EXISTS 1_iron_student ( SELECT a.*,b.drug_hosp,b.drug_date,b.drug FROM tmp_iron_student a LEFT OUTER JOIN tmp_iron_drug b ON a.PersonID=b.CID AND a.School_Hosp=b.drug_hosp GROUP BY a.PersonID,LEFT(a.Gragd,3) ); #ลบข้อมูลนักเรียนประถม 6 ก่อนส่งไปเขต6 DELETE FROM 1_iron_student WHERE Gragd LIKE '216%'; #------------------------------------------------------------------------------ #เพ่ิมเตมจากวันที่ปรุชม #สร้างตาราง tmp เด็กที่อายุ 6 - 12 ปี ที่มี cid ไม่ตรงกับฐานข้อมูลนักเรียน student DROP TABLE IF EXISTS 1_iron_non_student; CREATE TABLE IF NOT EXISTS 1_iron_non_student(PRIMARY KEY(PersonID)) ENGINE=MyISAM AS #----------------------------------- ( SELECT @prov_c AS 'province', b.HOSPCODE AS 'School_Hosp', 'NON' AS 'AcademicYear', 'NON' AS 'Semester', 'NON' AS 'SchoolID', 'NON' AS 'SCHOOLNAME', NULL AS 'StudentID', a.CID AS 'PersonID', a.NATION ,a.`name` AS 'Fname,', a.BIRTH AS 'Birthdate', TIMESTAMPDIFF(YEAR,a.BIRTH,@start_d) AS 'Age_y' , NULL AS 'Gragd', b.drug_hosp,b.drug_date,b.drug FROM t_person_cid a INNER JOIN tmp_iron_drug b ON (a.CID=b.CID) WHERE TIMESTAMPDIFF(YEAR,a.BIRTH,@start_d) BETWEEN 6 AND 12 AND a.DISCHARGE = '9' AND a.NATION = '099' AND a.CID NOT IN (SELECT PersonID FROM tmp_iron_student) ORDER BY b.drug_date ); #เอาข้อมูลนักเรียน+เด็ก6-12ปี INSERT IGNORE INTO 1_iron_student (SELECT * FROM 1_iron_non_student); DROP TABLE IF EXISTS tmp_iron_drug; DROP TABLE IF EXISTS tmp_iron_student; #DROP TABLE IF EXISTS 1_iron_non_student; SELECT * FROM 1_iron_student; #PROVIDER1_SQL# |
Script Cron | |
Force Script Cron | 0 |
Active | 1 |
Client Office Type | |
Create Date | 2022-04-01 01:52:47 |
Last Update | 2022-08-18 20:50:39 |
- Home
- About
- Privacy Policy
- Terms of Use
- Contact Us
© 2015. All Rights Reserved.