ประมวลผลข้อมูล ANC
ID | 803 |
---|---|
Parent ID | 57 |
Table Name | anc_daily |
Title | ประมวลผลข้อมูล ANC |
Description | |
Script | #SQL_OPTIONS# PROVIDERS=4 PROVIDER1=JHCIS PROVIDER2=HOSXP PROVIDER3=HOSPITAL_OS PROVIDER4=HOSXP_V4 PROVIDER1_VALIDATE_TABLES=visitanc,visitancpregnancy,visit PROVIDER2_VALIDATE_TABLES=person_anc_service,ovst,ovst_seq PROVIDER3_VALIDATE_TABLES=t_visit,t_order,t_patient PROVIDER4_VALIDATE_TABLES=wny_tsh SCRIPT_FLOW=SQL #SQL_OPTIONS# #PROVIDER1_SQL# SET @provid = :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 @provcode = :provcode; SET @level_service = :level_service; SELECT va.pcucodeperson HOSPCODE, va.pid PID, va.visitno SEQ, IF(va.datecheck IS NULL OR TRIM(va.datecheck)='' OR va.datecheck LIKE '0000-00-00%','',DATE_FORMAT(va.datecheck,'%Y%m%d')) DATE_SERV, va.pregno GRAVIDA, IF(ROUND(DATEDIFF(CAST(va.datecheck AS DATE), CAST(vp.lmp AS DATE))/7) BETWEEN 1 AND 12,1, IF(ROUND(DATEDIFF(CAST(va.datecheck AS DATE), CAST(vp.lmp AS DATE))/7) BETWEEN 16 AND 20,2, IF(ROUND(DATEDIFF(CAST(va.datecheck AS DATE), CAST(vp.lmp AS DATE))/7) BETWEEN 24 AND 28,3, IF(ROUND(DATEDIFF(CAST(va.datecheck AS DATE), CAST(vp.lmp AS DATE))/7) BETWEEN 30 AND 34,4, IF(ROUND(DATEDIFF(CAST(va.datecheck AS DATE), CAST(vp.lmp AS DATE))/7) BETWEEN 36 AND 40,5,'' ) ) ) ) ) ANCNO, ROUND(DATEDIFF(CAST(va.datecheck AS DATE), CAST(vp.lmp AS DATE))/7) GA, IF(va.ancres=0,2,IF(va.ancres<>1,9,1)) ANCRESULT, IF((va.hosservice IS NULL OR va.hosservice=''),va.pcucode,va.hosservice) AS ANCPLACE, v.username PROVIDER, IF( va.dateupdate IS NULL OR TRIM(va.dateupdate)='' OR va.dateupdate LIKE '0000-00-00%',DATE_FORMAT(va.datecheck,'%Y%m%d%H%i%s'),DATE_FORMAT(va.dateupdate,'%Y%m%d%H%i%s') ) AS D_UPDATE ,p.idcard CID FROM visitanc va JOIN visitancpregnancy vp ON vp.pcucodeperson=va.pcucodeperson AND vp.pid=va.pid AND vp.pregno=va.pregno join person p on va.pid=p.pid LEFT JOIN visit v ON v.pcucode=va.pcucode AND va.visitno=v.visitno WHERE DATE(va.datecheck) BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND CURDATE() #PROVIDER1_SQL# #PROVIDER2_SQL# SET @provid = :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 @provcode = :provcode; SET @level_service = :level_service; SELECT @hoscode HOSPCODE, IF(q.pcu_person_id='' OR q.pcu_person_id IS NULL,o.hn, CAST(q.pcu_person_id AS UNSIGNED)) PID, q.seq_id SEQ, DATE_FORMAT(a2.anc_service_date,'%Y%m%d') DATE_SERV, a1.preg_no GRAVIDA, IF(a2.pa_week BETWEEN 1 AND 12,1, IF(a2.pa_week BETWEEN 16 AND 20,2, IF(a2.pa_week BETWEEN 24 AND 28,3, IF(a2.pa_week BETWEEN 30 AND 34,4, IF(a2.pa_week BETWEEN 36 AND 40,5,'' ) ) ) ) ) ANCNO, a2.pa_week GA, IF(a2.service_result='Y',1,IF(a2.service_result='N',2,9)) ANCRESULT, @hoscode ANCPLACE, o.doctor PROVIDER, DATE_FORMAT(a1.last_update,'%Y%m%d%H%i%s') D_UPDATE, p.cid CID FROM person_anc a1 LEFT JOIN person_anc_service a2 ON a1.person_anc_id = a2.person_anc_id LEFT JOIN ovst o ON o.vn = a2.vn LEFT JOIN ovst_seq q ON q.vn = a2.vn LEFT JOIN person p ON p.person_id = a1.person_id WHERE q.pcu_person_id IS NOT NULL AND (a2.anc_service_type_id=1 OR a2.anc_service_type_id IS NULL) AND (a2.anc_location_type_id IN (1,0,2) OR a2.anc_location_type_id IS NULL) AND a2.anc_service_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND CURDATE() #PROVIDER2_SQL# #PROVIDER3_SQL# SELECT distinct b_site.b_visit_office_id AS HOSPCODE ,t_health_family.health_family_hn_hcis as PID ,t_visit.visit_vn AS SEQ , case when t_visit.visit_begin_visit_time is not null then (to_number(substring(t_visit.visit_begin_visit_time,1,5),'9999')-543) || substring(t_visit.visit_begin_visit_time,6,2) || substring(t_visit.visit_begin_visit_time,9,2) when t_health_anc.health_anc_survey is not null and trim(t_health_anc.health_anc_survey) <> '' then (to_number(substring(t_health_anc.health_anc_survey,1,5),'9999')-543) || substring(t_health_anc.health_anc_survey,6,2) || substring(t_health_anc.health_anc_survey,9,2) else (to_number(substring(t_health_anc.modify_date_time,1,5),'9999')-543) || substring(t_health_anc.modify_date_time,6,2) || substring(t_health_anc.modify_date_time,9,2) end AS DATE_SERV , t_health_pregnancy.health_pregnancy_gravida_number AS GRAVIDA , t_health_anc.f_health_anc_section AS ANCNO , t_health_anc.health_anc_gravida_week AS GA , case when t_health_anc.health_anc_exam ='1' then '1' when t_health_anc.health_anc_exam ='2' then '2' else '9' end AS ANCRESULT , t_health_pregnancy.b_visit_office_id AS ANCPLACE ,b_employee.provider as PROVIDER ,(case when length(t_visit.visit_staff_doctor_discharge_date_time) >= 10 then case when length(cast(substring(t_visit.visit_staff_doctor_discharge_date_time,1,4) as numeric) - 543 || replace(replace(replace(substring(t_visit.visit_staff_doctor_discharge_date_time,5),'-',''),',',''),':','')) = 14 then (cast(substring(t_visit.visit_staff_doctor_discharge_date_time,1,4) as numeric) - 543 || replace(replace(replace(substring(t_visit.visit_staff_doctor_discharge_date_time,5),'-',''),',',''),':','')) when length(cast(substring(t_visit.visit_staff_doctor_discharge_date_time,1,4) as numeric) - 543 || replace(replace(replace(substring(t_visit.visit_staff_doctor_discharge_date_time,5),'-',''),',',''),':','')) =12 then (cast(substring(t_visit.visit_staff_doctor_discharge_date_time,1,4) as numeric) - 543 || replace(replace(replace(substring(t_visit.visit_staff_doctor_discharge_date_time,5),'-',''),',',''),':','')) || '00' when length(cast(substring(t_visit.visit_staff_doctor_discharge_date_time,1,4) as numeric) - 543 || replace(replace(replace(substring(t_visit.visit_staff_doctor_discharge_date_time,5),'-',''),',',''),':','')) =10 then (cast(substring(t_visit.visit_staff_doctor_discharge_date_time,1,4) as numeric) - 543 || replace(replace(replace(substring(t_visit.visit_staff_doctor_discharge_date_time,5),'-',''),',',''),':','')) || '0000' when length(cast(substring(t_visit.visit_staff_doctor_discharge_date_time,1,4) as numeric) - 543 || replace(replace(replace(substring(t_visit.visit_staff_doctor_discharge_date_time,5),'-',''),',',''),':','')) = 8 then (cast(substring(t_visit.visit_staff_doctor_discharge_date_time,1,4) as numeric) - 543 || replace(replace(replace(substring(t_visit.visit_staff_doctor_discharge_date_time,5),'-',''),',',''),':','')) || '000000' else '' end when length(t_health_anc.modify_date_time) >= 10 then case when length(cast(substring(t_health_anc.modify_date_time,1,4) as numeric) - 543 || replace(replace(replace(substring(t_health_anc.modify_date_time,5),'-',''),',',''),':','')) = 14 then (cast(substring(t_health_anc.modify_date_time,1,4) as numeric) - 543 || replace(replace(replace(substring(t_health_anc.modify_date_time,5),'-',''),',',''),':','')) when length(cast(substring(t_health_anc.modify_date_time,1,4) as numeric) - 543 || replace(replace(replace(substring(t_health_anc.modify_date_time,5),'-',''),',',''),':','')) =12 then (cast(substring(t_health_anc.modify_date_time,1,4) as numeric) - 543 || replace(replace(replace(substring(t_health_anc.modify_date_time,5),'-',''),',',''),':','')) || '00' when length(cast(substring(t_health_anc.modify_date_time,1,4) as numeric) - 543 || replace(replace(replace(substring(t_health_anc.modify_date_time,5),'-',''),',',''),':','')) =10 then (cast(substring(t_health_anc.modify_date_time,1,4) as numeric) - 543 || replace(replace(replace(substring(t_health_anc.modify_date_time,5),'-',''),',',''),':','')) || '0000' when length(cast(substring(t_health_anc.modify_date_time,1,4) as numeric) - 543 || replace(replace(replace(substring(t_health_anc.modify_date_time,5),'-',''),',',''),':','')) = 8 then (cast(substring(t_health_anc.modify_date_time,1,4) as numeric) - 543 || replace(replace(replace(substring(t_health_anc.modify_date_time,5),'-',''),',',''),':','')) || '000000' else '' end else '' end) as D_UPDATE FROM t_health_anc INNER JOIN t_health_family ON t_health_anc.t_health_family_id = t_health_family.t_health_family_id INNER JOIN t_visit ON t_health_anc.t_visit_id = t_visit.t_visit_id INNER JOIN t_health_pregnancy ON t_health_anc.t_health_pregnancy_id = t_health_pregnancy.t_health_pregnancy_id and t_health_pregnancy.health_pregnancy_active='1' LEFT JOIN b_employee ON t_health_anc.health_anc_staff_record = b_employee.b_employee_id left join t_death on t_health_family.t_health_family_id = t_death.t_health_family_id and t_death.death_active = '1' ,b_site WHERE t_health_anc.health_anc_active = '1' AND t_health_family.health_family_active = '1' AND t_visit.f_visit_type_id <> 'S' AND t_visit.f_visit_status_id ='3' AND t_visit.visit_money_discharge_status='1' AND t_visit.visit_doctor_discharge_status='1' and current_date - date (cast(substr(t_visit.visit_staff_doctor_discharge_date_time,1,4) as numeric) - 543 || substr(t_visit.visit_staff_doctor_discharge_date_time,5,6)) < 7 and (case when t_death.t_death_id is not null then true when t_death.t_death_id is null and t_health_family.f_patient_discharge_status_id <> '1' then true else false end) #PROVIDER3_SQL# #PROVIDER4_SQL# SET @provid = :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 @provcode = :provcode; SET @level_service = :level_service; SELECT @hoscode HOSPCODE, IF(q.pcu_person_id='' OR q.pcu_person_id IS NULL,o.hn, CAST(q.pcu_person_id AS UNSIGNED)) PID, q.seq_id SEQ, DATE_FORMAT(a2.anc_service_date,'%Y%m%d') DATE_SERV, a1.preg_no GRAVIDA, IF(a2.pa_week BETWEEN 1 AND 12,1, IF(a2.pa_week BETWEEN 16 AND 20,2, IF(a2.pa_week BETWEEN 24 AND 28,3, IF(a2.pa_week BETWEEN 30 AND 34,4, IF(a2.pa_week BETWEEN 36 AND 40,5,'' ) ) ) ) ) ANCNO, a2.pa_week GA, IF(a2.service_result='Y',1,IF(a2.service_result='N',2,9)) ANCRESULT, @hoscode ANCPLACE, o.doctor PROVIDER, DATE_FORMAT(a1.last_update,'%Y%m%d%H%i%s') D_UPDATE , p.cid FROM person_anc a1 LEFT JOIN person_anc_service a2 ON a1.person_anc_id = a2.person_anc_id LEFT JOIN ovst o ON o.vn = a2.vn LEFT JOIN ovst_seq q ON q.vn = a2.vn LEFT JOIN person p on p.person_id=a1.person_id WHERE q.pcu_person_id IS NOT NULL AND (a2.anc_service_type_id=1 OR a2.anc_service_type_id IS NULL) AND (a2.anc_location_type_id IN (1,0,2) OR a2.anc_location_type_id IS NULL) AND a2.anc_service_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND CURDATE() #PROVIDER4_SQL# |
Script Cron | |
Force Script Cron | 0 |
Active | 1 |
Client Office Type | |
Create Date | 2019-02-20 09:00:52 |
Last Update | 2019-02-20 09:02:47 |
- Home
- About
- Privacy Policy
- Terms of Use
- Contact Us
© 2015. All Rights Reserved.