student

Update Delete

ID50
Parent ID35
Table Namestudent
Titlestudent
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 Cron0
Active1
Client Office Type
Create Date2015-08-20 10:35:51
Last Update2015-08-20 10:35:51