ประมวลผลข้อมูล ANC

Update Delete

ID803
Parent ID57
Table Nameanc_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 Cron0
Active1
Client Office Type
Create Date2019-02-20 09:00:52
Last Update2019-02-20 09:02:47