ข้อมูลนักเรียน
ID | 735 |
---|---|
Parent ID | 46 |
Table Name | student_trat |
Title | ข้อมูลนักเรียน |
Description | |
Script | 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; SELECT (SELECT hospitalcode FROM opdconfig) AS HOSPCODE, vs.person_id AS PID, IF(p.cid = '' OR p.cid = NULL,pt.cid,p.cid) AS CID, p.house_id AS HID, IF(pn.provis_code IN(SELECT provis_pname_code FROM provis_pname),pn.provis_code,pn2.provis_pname_code) AS PRENAME, p.fname AS NAME, p.lname AS LNAME, IF(p.patient_hn = '' OR p.patient_hn = NULL,pt.hn,p.patient_hn) AS HN, p.sex AS SEX, p.birthdate AS BIRTH, p.marrystatus AS MSTATUS, p.occupation AS OCCUPATION_OLD, (SELECT oc.nhso_code FROM occupation oc WHERE oc.occupation=p.occupation) AS OCCUPATION_NEW, (SELECT nhso_code FROM nationality n WHERE n.nationality=p.citizenship) AS RACE, (SELECT nhso_code FROM nationality n WHERE n.nationality=p.nationality) AS NATION, p.religion AS RELIGION, IF(p.education IS NULL OR p.education = '',9,p.education) AS EDUCATION, p.person_house_position_id AS FSTATUS, p.father_cid AS FATHER, p.mother_cid AS MOTHER, p.sps_cid AS COUPLE, p.movein_date AS MOVEIN, IF(p.death <> 'N',1,p.person_discharge_id) AS DISCHARGE, IF(p.death <> 'N',d.death_date,p.discharge_date) AS DDISCHARGE, (SELECT CASE (pb.`code`) WHEN '01' THEN '1' WHEN '05' THEN '1' WHEN '09' THEN '1' WHEN '03' THEN '3' WHEN '07' THEN '3' WHEN '11' THEN '3' WHEN '02' THEN '2' WHEN '06' THEN '2' WHEN '10' THEN '2' WHEN '04' THEN '4' WHEN '08' THEN '4' WHEN '12' THEN '4' ELSE NULL END) AS ABOGROUP, (SELECT CASE (pb.`code`) WHEN '01' THEN NULL WHEN '05' THEN '2' WHEN '09' THEN '1' WHEN '03' THEN NULL WHEN '07' THEN '2' WHEN '11' THEN '1' WHEN '02' THEN NULL WHEN '06' THEN '2' WHEN '10' THEN '1' WHEN '04' THEN NULL WHEN '08' THEN '2' WHEN '12' THEN '1' ELSE NULL END) AS RHGROUP, (SELECT nhso_code FROM person_labor_type pl WHERE pl.person_labor_type_id=p.person_labor_type_id) AS LABOR, IF(pt.passport_no = '' OR pt.passport_no = NULL,NULL,pt.passport_no) AS PASSPORT, IF(p.house_regist_type_id IN ('0',' ') OR p.house_regist_type_id IS NULL,5,p.house_regist_type_id) AS TYPEAREA, (SELECT v.village_code FROM village v WHERE v.village_id=p.village_id) AS VHID, CONCAT(v.village_code,vs.village_school_id) AS SCHOOLCODE, CASE MONTH(CURDATE()) WHEN '1' THEN (LEFT(NOW(),4) + 542) WHEN '2' THEN (LEFT(NOW(),4) + 542) WHEN '3' THEN (LEFT(NOW(),4) + 542) WHEN '4' THEN (LEFT(NOW(),4) + 542) ELSE (LEFT(CURDATE(),4)+ 543) END AS EDUCATIONYEAR, CASE vs.village_school_class_id WHEN '0' THEN '00' WHEN '1' THEN '01' WHEN '2' THEN '02' WHEN '3' THEN '03' WHEN '4' THEN '11' WHEN '5' THEN '12' WHEN '6' THEN '13' WHEN '7' THEN '21' WHEN '8' THEN '22' WHEN '9' THEN '23' WHEN '10' THEN '31' WHEN '11' THEN '32' WHEN '12' THEN '33' WHEN '13' THEN '41' WHEN '14' THEN '42' WHEN '15' THEN '43' ELSE NULL END AS CLASS, vs.last_update AS D_UPDATE FROM village_student vs INNER JOIN village_school vsc ON vsc.village_school_id = vs.village_school_id INNER JOIN village v ON v.village_id = vsc.village_id INNER JOIN village_school_class vc ON vc.village_school_class_id = vs.village_school_class_id INNER JOIN person p ON p.person_id = vs.person_id LEFT JOIN provis_bgroup pb ON p.blood_group = pb.name LEFT JOIN pname pn ON p.pname = pn.name LEFT JOIN provis_pname pn2 ON p.pname = pn2.provis_pname_short_name LEFT JOIN person_labor_type pl ON p.person_labor_type_id = pl.person_labor_type_id LEFT JOIN person_death d ON p.person_id = d.person_id LEFT JOIN village_organization_member vm ON p.person_id=vm.person_id LEFT JOIN patient pt ON pt.hn = p.patient_hn WHERE (vs.discharge = 'N' OR vs.discharge IS NULL) AND RIGHT(v.village_code,2) <> '00' ; |
Script Cron | |
Force Script Cron | 0 |
Active | 1 |
Client Office Type | |
Create Date | 2017-01-11 10:33:07 |
Last Update | 2017-01-11 10:33:07 |
- Home
- About
- Privacy Policy
- Terms of Use
- Contact Us
© 2015. All Rights Reserved.