• Home
  • My Scripts
  • รายงานข้อมูลด้านสุขภาพประชากรต่างด้าว

รายงานข้อมูลด้านสุขภาพประชากรต่างด้าว

Update Delete

ID804
Parent ID58
Table Nameperson_alien
Titleรายงานข้อมูลด้านสุขภาพประชากรต่างด้าว
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;

/*SQL Script สำหรับ JHCIS เริ่มที่นี่ */
SELECT

FROM

WHERE
;
/*SQL Script สำหรับ JHCIS สิ้นสุดที่นี่ */


#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;

/*SQL Script สำหรับ HosXP เริ่มที่นี่ */
SELECT @hoscode as hospcode,
@rep_year as rep_year,check1.*,rate34.*
FROM
(SELECT vstdate,
COUNT(DISTINCT hn) AS 'total',
COUNT(DISTINCT(CASE citizenship WHEN '57' THEN hn ELSE NULL END)) AS 'k57',
COUNT(DISTINCT(CASE citizenship WHEN '48' THEN hn ELSE NULL END)) AS 'p48',
COUNT(DISTINCT(CASE citizenship WHEN '56' THEN hn ELSE NULL END)) AS 'l56',
COUNT(DISTINCT(CASE citizenship WHEN '48' THEN NULL WHEN '56' THEN NULL WHEN '57' THEN NULL ELSE hn END)) AS 'other',
COUNT(DISTINCT(CASE sex WHEN '1' THEN hn ELSE NULL END)) AS 'total_man',
COUNT(DISTINCT(CASE citizenshipsex WHEN '571' THEN hn ELSE NULL END)) AS 'k57man',
COUNT(DISTINCT(CASE citizenshipsex WHEN '481' THEN hn ELSE NULL END)) AS 'p48man',
COUNT(DISTINCT(CASE citizenshipsex WHEN '561' THEN hn ELSE NULL END)) AS 'l56man',
COUNT(DISTINCT(CASE citizenshipother WHEN 'other1' THEN hn ELSE NULL END)) AS 'other_man',
COUNT(DISTINCT(CASE sex WHEN '2' THEN hn ELSE NULL END)) AS 'total_women',
COUNT(DISTINCT(CASE citizenshipsex WHEN '572' THEN hn ELSE NULL END)) AS 'k57women',
COUNT(DISTINCT(CASE citizenshipsex WHEN '482' THEN hn ELSE NULL END)) AS 'p48women',
COUNT(DISTINCT(CASE citizenshipsex WHEN '562' THEN hn ELSE NULL END)) AS 'l56women',
COUNT(DISTINCT(CASE citizenshipother WHEN 'other2' THEN hn ELSE NULL END)) AS 'other_women'
FROM
(SELECT
ovst.pttype,pttype.oldcode,pttype.`name`,
ovst.hn,
ovst.vstdate,
p.citizenship,
p.sex,
CONCAT(p.citizenship,p.sex) AS citizenshipsex,
IF(p.citizenship NOT IN(48,57,56),CONCAT('other',p.sex),'0') AS citizenshipother
from ovst
LEFT JOIN patient p on p.hn=ovst.hn
LEFT JOIN pttype ON ovst.pttype=pttype.pttype
where ovst.vstdate BETWEEN CONCAT(@rep_year-1,'1001') AND CONCAT(@rep_year,'0930') AND p.citizenship!='99' AND pttype.oldcode IN('08')
) AS tt01
GROUP BY vstdate) AS check1
/* --------------------------------------- */
LEFT JOIN
(SELECT vstdaterate,
COUNT(IF(immigration_checkup_result_id=2,vn,NULL)) AS 'total_rate2',
COUNT(IF(immigration_checkup_result_id=2 AND citizenship=57,hn,NULL)) AS 'total_rate2_k57',
COUNT(IF(immigration_checkup_result_id=2 AND citizenship=48,hn,NULL)) AS 'total_rate2_p48',
COUNT(IF(immigration_checkup_result_id=2 AND citizenship=56,hn,NULL)) AS 'total_rate2_l56',

COUNT(IF(immigration_checkup_result_id=2 AND sex=1,vn,NULL)) AS 'total_rate2_man',
COUNT(IF(immigration_checkup_result_id=2 AND citizenship=57 AND sex=1,hn,NULL)) AS 'total_rate2_k57_man',
COUNT(IF(immigration_checkup_result_id=2 AND citizenship=48 AND sex=1,hn,NULL)) AS 'total_rate2_p48_man',
COUNT(IF(immigration_checkup_result_id=2 AND citizenship=56 AND sex=1,hn,NULL)) AS 'total_rate2_l56_man',

COUNT(IF(immigration_checkup_result_id=2 AND sex=2,vn,NULL)) AS 'total_rate2_women',
COUNT(IF(immigration_checkup_result_id=2 AND citizenship=57 AND sex=2,hn,NULL)) AS 'total_rate2_k57_women',
COUNT(IF(immigration_checkup_result_id=2 AND citizenship=48 AND sex=2,hn,NULL)) AS 'total_rate2_p48_women',
COUNT(IF(immigration_checkup_result_id=2 AND citizenship=56 AND sex=2,hn,NULL)) AS 'total_rate2_l56_women',
/**/
COUNT(IF(immigration_followup_id=1,hn,NULL)) AS 'total_A1x', #วัณโรค
COUNT(IF(immigration_followup_id=1 AND citizenship=57,hn,NULL)) AS 'A1x_k57',
COUNT(IF(immigration_followup_id=1 AND citizenship=48,hn,NULL)) AS 'A1x_p48',
COUNT(IF(immigration_followup_id=1 AND citizenship=56,hn,NULL)) AS 'A1x_l56',
COUNT(IF(immigration_followup_id=2,hn,NULL)) AS 'total_B74', #เท้าช้าง
COUNT(IF(immigration_followup_id=2 AND citizenship=57,hn,NULL)) AS 'B74_k57',
COUNT(IF(immigration_followup_id=2 AND citizenship=48,hn,NULL)) AS 'B74_p48',
COUNT(IF(immigration_followup_id=2 AND citizenship=56,hn,NULL)) AS 'B74_l56',
COUNT(IF(immigration_followup_id=3,hn,NULL)) AS 'total_A30', #เรื้อน
COUNT(IF(immigration_followup_id=3 AND citizenship=57,hn,NULL)) AS 'A30_k57',
COUNT(IF(immigration_followup_id=3 AND citizenship=48,hn,NULL)) AS 'A30_p48',
COUNT(IF(immigration_followup_id=3 AND citizenship=56,hn,NULL)) AS 'A30_l56',
COUNT(IF(immigration_followup_id=4,hn,NULL)) AS 'total_A5x', #ซิฟิลลิส
COUNT(IF(immigration_followup_id=4 AND citizenship=57,hn,NULL)) AS 'A5x_k57',
COUNT(IF(immigration_followup_id=4 AND citizenship=48,hn,NULL)) AS 'A5x_p48',
COUNT(IF(immigration_followup_id=4 AND citizenship=56,hn,NULL)) AS 'A5x_l56',
COUNT(IF(immigration_followup_id=5,hn,NULL)) AS 'total_B5x', #มาลาเรีย
COUNT(IF(immigration_followup_id=5 AND citizenship=57,hn,NULL)) AS 'B5x_k57',
COUNT(IF(immigration_followup_id=5 AND citizenship=48,hn,NULL)) AS 'B5x_p48',
COUNT(IF(immigration_followup_id=5 AND citizenship=56,hn,NULL)) AS 'B5x_l56',
COUNT(IF(immigration_followup_id=6,hn,NULL)) AS 'total_rate2othor', #โรคอื่นๆ
COUNT(IF(immigration_followup_id=6 AND citizenship=57,hn,NULL)) AS 'rate2othor_k57',
COUNT(IF(immigration_followup_id=6 AND citizenship=48,hn,NULL)) AS 'rate2othor_p48',
COUNT(IF(immigration_followup_id=6 AND citizenship=56,hn,NULL)) AS 'rate2othor_l56',
/* ------------------------------------*/
COUNT(IF(immigration_checkup_result_id=3,vn,NULL)) AS 'total_rate3',
COUNT(IF(immigration_checkup_result_id=3 AND citizenship=57,hn,NULL)) AS 'total_rate3_k57',
COUNT(IF(immigration_checkup_result_id=3 AND citizenship=48,hn,NULL)) AS 'total_rate3_p48',
COUNT(IF(immigration_checkup_result_id=3 AND citizenship=56,hn,NULL)) AS 'total_rate3_l56',

COUNT(IF(immigration_checkup_result_id=3 AND sex=1,vn,NULL)) AS 'total_rate3_man',
COUNT(IF(immigration_checkup_result_id=3 AND citizenship=57 AND sex=1,hn,NULL)) AS 'total_rate3_k57_man',
COUNT(IF(immigration_checkup_result_id=3 AND citizenship=48 AND sex=1,hn,NULL)) AS 'total_rate3_p48_man',
COUNT(IF(immigration_checkup_result_id=3 AND citizenship=56 AND sex=1,hn,NULL)) AS 'total_rate3_l56_man',

COUNT(IF(immigration_checkup_result_id=3 AND sex=2,vn,NULL)) AS 'total_rate3_women',
COUNT(IF(immigration_checkup_result_id=3 AND citizenship=57 AND sex=2,hn,NULL)) AS 'total_rate3_k57_women',
COUNT(IF(immigration_checkup_result_id=3 AND citizenship=48 AND sex=2,hn,NULL)) AS 'total_rate3_p48_women',
COUNT(IF(immigration_checkup_result_id=3 AND citizenship=56 AND sex=2,hn,NULL)) AS 'total_rate3_l56_women',
/**/
COUNT(IF(immigration_followup_id=7,hn,NULL)) AS 'total_A1x_rate3', #วัณโรค
COUNT(IF(immigration_followup_id=7 AND citizenship=57,hn,NULL)) AS 'A1x_k57_rate3',
COUNT(IF(immigration_followup_id=7 AND citizenship=48,hn,NULL)) AS 'A1x_p48_rate3',
COUNT(IF(immigration_followup_id=7 AND citizenship=56,hn,NULL)) AS 'A1x_l56_rate3',
COUNT(IF(immigration_followup_id=8,hn,NULL)) AS 'total_B74_rate3', #เท้าช้าง
COUNT(IF(immigration_followup_id=8 AND citizenship=57,hn,NULL)) AS 'B74_k57_rate3',
COUNT(IF(immigration_followup_id=8 AND citizenship=48,hn,NULL)) AS 'B74_p48_rate3',
COUNT(IF(immigration_followup_id=8 AND citizenship=56,hn,NULL)) AS 'B74_l56_rate3',
COUNT(IF(immigration_followup_id=9,hn,NULL)) AS 'total_A30_rate3', #เรื้อน
COUNT(IF(immigration_followup_id=9 AND citizenship=57,hn,NULL)) AS 'A30_k57_rate3',
COUNT(IF(immigration_followup_id=9 AND citizenship=48,hn,NULL)) AS 'A30_p48_rate3',
COUNT(IF(immigration_followup_id=9 AND citizenship=56,hn,NULL)) AS 'A30_l56_rate3',
COUNT(IF(immigration_followup_id=10,hn,NULL)) AS 'total_A5x_rate3', #ซิฟิลลิส
COUNT(IF(immigration_followup_id=10 AND citizenship=57,hn,NULL)) AS 'A5x_k57_rate3',
COUNT(IF(immigration_followup_id=10 AND citizenship=48,hn,NULL)) AS 'A5x_p48_rate3',
COUNT(IF(immigration_followup_id=10 AND citizenship=56,hn,NULL)) AS 'A5x_l56_rate3',
COUNT(IF(immigration_followup_id=11,hn,NULL)) AS 'total_F1x', #ติดสารเสพติด
COUNT(IF(immigration_followup_id=11 AND citizenship=57,hn,NULL)) AS 'F1x_k57',
COUNT(IF(immigration_followup_id=11 AND citizenship=48,hn,NULL)) AS 'F1x_p48',
COUNT(IF(immigration_followup_id=11 AND citizenship=56,hn,NULL)) AS 'F1x_l56',
COUNT(IF(immigration_followup_id=12,hn,NULL)) AS 'total_F102', #พิษสุราเรื้อรัง
COUNT(IF(immigration_followup_id=12 AND citizenship=57,hn,NULL)) AS 'F102_k57',
COUNT(IF(immigration_followup_id=12 AND citizenship=48,hn,NULL)) AS 'F102_p48',
COUNT(IF(immigration_followup_id=12 AND citizenship=56,hn,NULL)) AS 'F102_l56',
COUNT(IF(immigration_followup_id=13,hn,NULL)) AS 'total_F2x', #พิษสุราเรื้อรัง
COUNT(IF(immigration_followup_id=13 AND citizenship=57,hn,NULL)) AS 'F2x_k57',
COUNT(IF(immigration_followup_id=13 AND citizenship=48,hn,NULL)) AS 'F2x_p48',
COUNT(IF(immigration_followup_id=13 AND citizenship=56,hn,NULL)) AS 'F2x_l56'
FROM (
SELECT
ic.immigration_checkup_id,
ic.vn,
ic.immigration_checkup_result_id,
pi.immigration_followup_id,
ic.pregnancy,
ic.immigration_checkup_pttype_no,
ic.immigration_checkup_pttype_issueddate,
ic.immigration_checkup_pttype_expirationdate,
ovst.hn,
ovst.vstdate AS 'vstdaterate',
ovst.pttype,
pt.citizenship,
pt.sex
from immigration_checkup AS ic
LEFT JOIN ovst ON ovst.vn=ic.vn
LEFT JOIN patient AS pt ON pt.hn=ovst.hn
LEFT JOIN patient_immigration_followup AS pi ON pi.vn=ovst.vn
WHERE ovst.vstdate BETWEEN CONCAT(@rep_year-1,'1001') AND CONCAT(@rep_year,'0930') AND ic.immigration_checkup_result_id !=1
) AS tt03
GROUP BY vstdaterate) AS rate34
ON check1.vstdate=rate34.vstdaterate
;
/*SQL Script สำหรับ HosXP สิ้นสุดที่นี่ */

#PROVIDER2_SQL#
Script Cron
Force Script Cron0
Active1
Client Office Type
Create Date2019-04-23 02:55:47
Last Update2019-04-23 02:55:47