ข้อมูลนักเรียน

Update Delete

ID735
Parent ID46
Table Namestudent_trat
Titleข้อมูลนักเรียน
Description
ScriptSET @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 Cron0
Active1
Client Office Type
Create Date2017-01-11 10:33:07
Last Update2017-01-11 10:33:07