student
ID | 50 |
---|---|
Parent ID | 35 |
Table Name | student |
Title | student |
Description | |
Script | #SQL_OPTIONS# PROVIDERS=2 PROVIDER1=JHCIS PROVIDER2=HOSXP PROVIDER1_VALIDATE_TABLES=visitanc,visitancpregnancy,visit PROVIDER2_VALIDATE_TABLES=person_anc_service,ovst,ovst_seq 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; SELECT ps.pcucodeperson AS HOSPCODE, ps.pid AS PID, p.idcard AS CID, h.hcode AS HID, p.prename AS PRENAME, p.fname AS NAME, p.lname AS LNAME, p.pid AS HN, p.sex AS SEX, p.birth AS BIRTH, IF(p.marystatus = 'f','2',p.marystatus) AS MSTATUS, p.occupa AS OCCUPATION_OLD, o.mapoccupa AS OCCUPATION_NEW, p.origin AS RACE, n.mapnation AS NATION, p.religion AS RELIGION, p.educate AS EDUCATION, IF(p.familyposition = '1','1','2') AS FSTATUS, p.fatherid AS FATHER, p.motherid AS MOTHER, p.mateid AS COUPLE, IF(p.datein IS NULL OR TRIM(p.datein)='' OR p.datein LIKE '0000-00-00%','',DATE_FORMAT(p.datein,'%Y%m%d')) AS MOVEIN, p.dischargetype AS DISCHARGE, p.dischargedate AS DDISCHARGE, p.bloodgroup AS ABOGROUP, (SELECT CASE p.bloodrh WHEN 'P' THEN 1 WHEN 'N' THEN 2 ELSE NULL END )AS RHGROUP, p.intercode AS LABOR, p.passpotnumber AS PASSPORT, IF(p.typelive = 0,1,p.typelive) AS TYPEAREA, h.villcode AS VHID, CONCAT(ps.villcode,ps.schoolno) 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 ps.classeducate 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, p.dateupdate AS D_UPDATE FROM personstudent ps INNER JOIN person p ON p.pcucodeperson = ps.pcucodeperson AND p.pid = ps.pid LEFT JOIN house h ON p.hcode=h.hcode AND p.pcucodeperson=h.pcucode LEFT JOIN village v ON p.pcucodeperson=v.pcucode AND v.villcode=h.villcode AND h.pcucode=v.pcucode LEFT JOIN coccupa o ON p.occupa = o.occupacode LEFT JOIN cnation n ON p.nation = n.nationcode WHERE (ps.classeducate >= 0 AND ps.classeducate <= 15) AND ps.pid IS NOT NULL AND RIGHT(ps.villcode,2) <> '00' ; #PROVIDER1_SQL# #PROVIDER2_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; 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' ; #PROVIDER2_SQL# |
Script Cron | |
Force Script Cron | 0 |
Active | 1 |
Client Office Type | |
Create Date | 2015-08-20 10:35:51 |
Last Update | 2015-08-20 10:35:51 |
- Home
- About
- Privacy Policy
- Terms of Use
- Contact Us
© 2015. All Rights Reserved.