/*ตรวจสอบ ยา ไม่มีค่าใช้จ่าย*/

select * from drugitems

where istatus <>'N'

and (income not in ('03','17')

or income is null)

/*สมุนไพรที่ยังไม่ Mapping 24 หลัก */

SELECT

thd.icode,

thd.health_med_medication_name,

d.`name`,

d.strength,

d.istatus,

d.did

from health_med_medication as thd

right JOIN drugitems as d on d.icode =thd.icode

where thd.icode is null and left(d.did,1)='4'

and d.istatus <>'N'

#---------------------------

/*ตรวจสอบ กรณี บันทึกหัตถการแผนไทย ไม่่สามารถส่งออกได้*/

SELECT i,v,icd10tm,o.health_med_operation_item_code
,o.health_med_operation_item_name
FROM

(SELECT

SUBSTR(icd10tm FROM 6 FOR 2) AS i ,health_med_operation_code.name AS 'v'

FROM

health_med_operation_code

WHERE NAME LIKE '%ทั่วร่างกาย%'

GROUP BY

SUBSTR(icd10tm FROM 6 FOR 2))AS c

LEFT JOIN health_med_operation_item AS o ON o.health_med_operation_item_code =c.i;

#-----------------

/*การส่งเสริมสุขภาพและการป้องกันโรคด้วยการนวด*/

SELECT

*

FROM

health_med_operation_code

WHERE

`name` LIKE '%นวด%'

AND SUBSTR(CODE, 5, 2) = 79

#--------------------------------

/*หัตถการ เรียงตามประเภทกิจกรรม*/

SELECT

c.code,c.name

, SUBSTR(c.icd10tm, 1, 3)as 'อวัยวะ'

,SUBSTR(c.icd10tm, 4, 2)as'ประเภท'

, SUBSTR(c.icd10tm, 6, 2)as 'กิจกรรม'

FROM

health_med_operation_code c

ORDER BY SUBSTR(c.icd10tm, 4, 2)

, SUBSTR(c.icd10tm, 6, 2)

,SUBSTR(c.icd10tm, 1, 3)

#---------------

/*การบริการการแพทย์แผนไทยแม่หลังคลอด*/

set @date1 = '2016-10-01',@date2 = '2017-09-30' ;/*เปลี่ยน '2015-10-01' ตามที่ต้องการ*/

select f.service_date,

TIMESTAMPDIFF(day,pa.labor_date,f.service_date)
as"หลังคลอดไม่เกิน 45 วัน",

(select timestampdiff(day,min(x.service_date),f.service_date)

from health_med_service x

where x.hn =f.hn)as 'ห่างจากครั้งแรกไม่เกิน 90 วัน',

p.pname,

concat(p.fname,space(1),p.lname)as 'mother_name',

e.icd10tm ,

e.`name`,

concat_ws("-",d.health_med_organ_code,b.health_med_operation_type_code,c.health_med_operation_item_code) as gencode

from health_med_service_operation a

left outer join   health_med_operation_type b on a.health_med_operation_type_id=b.health_med_operation_type_id

left outer join   health_med_operation_item c on a.health_med_operation_item_id=c.health_med_operation_item_id

left outer join   health_med_organ d on a.health_med_organ_id=d.health_med_organ_id

left outer join   health_med_operation_code e on e.code = concat_ws("-",d.health_med_organ_code,b.health_med_operation_type_code,c.health_med_operation_item_code)

left outer join health_med_service f on f.health_med_service_id = a.health_med_service_id

left JOIN person as p on p.patient_hn = f.hn

LEFT JOIN person_anc as pa on pa.person_id = p.person_id

where f.service_date BETWEEN @date1 and @date2

and e.icd10tm in('9007712','9007713','9007714','9007715','9007716','9007730')

and TIMESTAMPDIFF(day,pa.labor_date,f.service_date)<=45   /*หลังคลอดไม่เกิน 45 วัน*/

#group by p.cid /*ถ้านับคนให้เอาเครื่องหมาย # ข้างหน้าออกใส่ตรง group by f.service_date แทน */

group by f.service_date

ORDER BY f.service_date

#-------------------------------

/*การจ่ายยาสมุนไพร นับ service */

set @row= 0;

set @date1='2016-10-01',@date2='2016-10-31';

select @row:=@row+1 as "NO.",o.vstdate ,p.pname ,concat(p.fname,space(1),p.lname)as 'person_name' ,d.`name`,d.strength

from opitemrece as o

inner join drugitems as d on d.icode = o.icode

LEFT JOIN person as p on p.patient_hn = o.hn

where

substring(d.did,1,2)in('41','42')

and o.vstdate between @date1 and @date2

ORDER BY o.vstdate,o.vn

#---------------------

/*เพิ่ม รหัส คลินิก แผนไทย ในหน้า one stop service*/

INSERT IGNORE INTO spclty (spclty, NAME, depcode, ovstostlink, spname, shortname, ill_visit, spclty_guid, no_service_charge, no_service_charge_doctor, oldcode, nhso_code, operation, provis_code, hos_guid) VALUES ('14', 'แพทย์แผนไทย', NULL, '14', 'OPD', NULL, 'N', '{B4C67052-F61D-11DE-BFF4-00215E472B30}', NULL, NULL, NULL, '12', NULL, '01400', NULL);

UPDATE spclty SET provis_code ='01400'

WHERE spclty. NAME LIKE '%ไทย%';

select * from spclty;

#--------------------

/*thaimed serviceตอนเช้าคีย์ข้อมูลตอนเย็น "service_date"       "service_time""vstdate""vsttime"

"2017-01-04" "18:25:43"   "2017-01-04" "12:38:53" พอส่งออก seq ไม่เท่ากัน

*/

SET @date1 := '2017-03-01' ,@date2 := '2017-03-01';

/*เปลี่ยนวันเอาเอง*/

SELECT

o.vstdate AS 'service_date',

o.vsttime AS 'service_time',

s.service_date AS 'Thaimed_sdate',

s.service_time AS 'Thaimed_stime',

p.pname,

CONCAT(p.fname, SPACE(1), p.lname) AS 'pt_name',

t. NAME as 'สิทธิ'

FROM

health_med_service AS s

INNER JOIN ovst AS o ON o.hn = s.hn

AND o.vn = s.vn

INNER JOIN patient AS p ON p.hn = s.hn

INNER JOIN pttype AS t ON t.pttype = o.pttype

WHERE

s.service_date BETWEEN @date1

AND @date2

AND (s.service_date <> o.vstdate

OR s.service_time <> o.vsttime

)

ORDER BY s.vn

#-----------------

/*ตรวจสอบ รหัส U77 ในตาราง icd101 */

SELECT *

FROM icd101

WHERE CODE LIKE 'u77%';

#---------------

/*ตรวจสอบ รหัส U77 ในตาราง icd10_nhso */

SELECT *

FROM icd10_nhso

WHERE CODE LIKE 'u77%';

#---------------

/*ตรวจสอบ รหัส U77 ในตาราง icd10_health_med */

SELECT *

FROM icd10_health_med

WHERE icd10 LIKE 'u77%';

#---------------

/*บริการแผนไทย*/

SELECT p.code,p.name,p.icd10tm

FROM health_med_operation_code p

where p.icd10tm in ('9007900','9007901','9007902' ,'9007904')

#----------------

/*บริการทางเลือก*/

SELECT p.code,p.name,p.icd10tm

FROM health_med_operation_code p

where p.icd10tm in ('9007998','9007999','9007903')

#-----------------

/*กิจกรรมการส่งเสริมสุขภาพและการป้องกันโรคแผนไทย*/

SET @date1 = '2016-10-01',@date2 = '2017-09-30' ;

SELECT f.service_date,p.pname,concat(p.fname,space(1),p.lname)as 'person_name',e.icd10tm,e.name,r.icd10

FROM health_med_service_operation a

LEFT OUTER JOIN   health_med_operation_type b ON a.health_med_operation_type_id=b.health_med_operation_type_id

LEFT OUTER JOIN   health_med_operation_item c ON a.health_med_operation_item_id=c.health_med_operation_item_id

LEFT OUTER JOIN   health_med_organ d ON a.health_med_organ_id=d.health_med_organ_id

LEFT OUTER JOIN   health_med_operation_code e ON e.code = CONCAT_WS("-",d.health_med_organ_code,b.health_med_operation_type_code,c.health_med_operation_item_code)

LEFT OUTER JOIN health_med_service f ON f.health_med_service_id = a.health_med_service_id

INNER JOIN health_med_service_diagnosis r ON r.health_med_service_id = f.health_med_service_id

LEFT JOIN person AS p ON p.patient_hn = f.hn

WHERE f.service_date BETWEEN @date1 AND @date2

AND r.icd10 = 'u77'

AND e.icd10tm IN ('9007998','9007999','9007903','9007900','9007901','9007902' ,'9007904')

#------------------------------------------

/*LMP วันแรกของการมีประจ าเดือน ครั้งสุดท้าย ผิดพลาด*/

SET @date1 = '2012-10-01',@date2 = '2017-09-30';

SELECT

p.pname,

CONCAT(p.fname, SPACE(1), p.lname) AS 'patient_name',

pa.anc_register_date, pa.lmp AS LMP,

pa.pre_labor_service1_date 'วันที่ anc1',

pa.labor_date

FROM

person_anc AS pa

LEFT JOIN person AS p   ON p.person_id = pa.person_id

WHERE pa.lmp > pa.pre_labor_service1_date

AND pa.labor_date BETWEEN @date1   AND @date2

#==================

/*EDC  วันที่กำหนดคลอด ผิดปกติ */

set @date1 = '2009-11-01',@date2 = '2017-09-30';

SELECT

p.pname,concat(p.fname,space(1),p.lname)as 'mother',

pa.anc_register_date,

pa.edc,pa.post_labor_service1_date,pa.post_labor_service2_date,

pa.post_labor_service3_date

FROM

person_anc AS pa

INNER JOIN person AS p ON p.person_id = pa.person_id

INNER JOIN house AS h ON h.house_id = p.house_id

INNER JOIN village AS v ON v.village_id = h.village_id

WHERE

pa.anc_register_date BETWEEN @date1 and @date2

and (pa.edc >pa.post_labor_service1_date OR

pa.edc >pa.post_labor_service2_date OR

pa.edc >pa.post_labor_service3_date)

#======================

/*BDATE วันคลอด / วันสิ้นสุดการตั้งครรภ์ ผิดปกติ*/

SELECT

p.pname,concat(p.fname,space(1),p.lname)as 'mother',

pa.anc_register_date,

pa.labor_date,pa.edc,pa.labor_status_id,

s.labor_status_name

FROM

person_anc AS pa

INNER JOIN person AS p ON p.person_id = pa.person_id

INNER JOIN house AS h ON h.house_id = p.house_id

INNER JOIN village AS v ON v.village_id = h.village_id

INNER JOIN labor_status s on s.labor_status_id =pa.labor_status_id

WHERE pa.labor_date is null and pa.labor_status_id in('2','3')

#==============

/*BRESULT ผลสิ้นสุดการตั้งครรภ์ ผิดปกติ รหัสโรค (ICD - 10 TM )*/

SELECT

p.pname,concat(p.fname,space(1),p.lname)as 'mother',

pa.anc_register_date,s.labor_status_name,

pa.labor_icd10,i.name

FROM

person_anc AS pa

INNER JOIN person AS p ON p.person_id = pa.person_id

left JOIN labor_status s on s.labor_status_id =pa.labor_status_id

left join icd101 as i on i.code = pa.labor_icd10

WHERE pa.labor_status_id in('2','3')

and pa.labor_icd10 is null or pa.labor_icd10 not in ('O000', 'O001', 'O002', 'O008', 'O009', 'O010', 'O011', 'O019', 'O020', 'O021', 'O028', 'O029', 'O030', 'O031', 'O032', 'O033', 'O034', 'O035', 'O036', 'O037', 'O038', 'O039', 'O040', 'O041', 'O042', 'O043', 'O044', 'O045', 'O046', 'O047', 'O048', 'O049', 'O050', 'O051', 'O052', 'O053', 'O054', 'O055', 'O056', 'O057', 'O058', 'O059', 'O060', 'O061', 'O062', 'O063', 'O064', 'O065', 'O066', 'O067', 'O068', 'O069', 'O070', 'O071', 'O072', 'O073', 'O074', 'O075', 'O076', 'O077', 'O078', 'O079', 'O080', 'O081', 'O082', 'O083', 'O084', 'O085', 'O086', 'O087', 'O088', 'O089', 'O800', 'O801', 'O808', 'O809', 'O810', 'O811', 'O812', 'O813', 'O814', 'O815', 'O820', 'O821', 'O822', 'O828', 'O829', 'O830', 'O831', 'O832', 'O833', 'O834', 'O838', 'O839', 'O840', 'O841', 'O842', 'O848', 'O849')

#==================

/*BPLACE สถานที่คลอด */

/*สถานที่คลอด ไม่ระบุ จากการไม่พบในบัญชี 3*/

SELECT DISTINCT CONCAT(p.fname , SPACE(1) , p.lname)AS'person_name',

TIMESTAMPDIFF(YEAR,p.birthdate,pv.vaccine_date)AS'age_year',

GROUP_CONCAT(v.vaccine_code) AS 'vaccine',p.house_regist_type_id AS type_area,p.nationality ,pl.labour_hospcode,p.death

FROM person_vaccine_list AS pv

INNER JOIN person_vaccine AS v ON v.person_vaccine_id = pv.person_vaccine_id

INNER JOIN person AS p ON p.person_id = pv.person_id

LEFT JOIN person_labour AS pl ON pl.person_id = pv.person_id

WHERE pl.person_id IS NULL

AND TIMESTAMPDIFF(YEAR,p.birthdate,pv.vaccine_date)<12

AND p.house_regist_type_id IN(1,3)

AND p.nationality = 99

AND p.discharge_date IS NULL

GROUP BY p.person_id

#======================

/*BTYPE วิธีการคลอด / สิ้นสุดการตั้งครรภ์   1 = NORMAL, 2 = CESAREAN, 3 = VACUUM, 4 = FORCEPS, 5 = ท่าก้น, 6 = ABORTION */

/*BTYPE วิธีการคลอด / สิ้นสุดการตั้งครรภ์ ผิดปกติ*/

set @date1 = '2015-10-01',@date2 = '2016-09-30';

SELECT

pa.labor_date,

p.pname,

CONCAT(p.fname, SPACE(1),p.lname) AS 'mother',

pa.labor_status_id,

ls.labor_status_name,

pa.labour_type_id

FROM

person_anc AS pa

INNER JOIN person AS p ON p.person_id = pa.person_id

LEFT JOIN person_labour AS pl ON pl.person_id = pa.person_id

#LEFT JOIN person_labour_type AS plt ON plt.person_labour_type_id = pa.labour_type_id

INNER join labor_status AS ls ON ls.labor_status_id = pa.labor_status_id

WHERE

pa.labor_status_id IN ('2', '3')

AND pa.labour_type_id IS NULL

and pa.labor_date between @date1 and @date2

/*BDOCTOR   ประเภทของผู้ทำคลอด ว่างเปล่า */

set @date1 ='2009-10-01',@date2 = '2017-09-30';

SELECT

p.pname,

CONCAT(p.fname, SPACE(1),p.lname) AS 'mother',

person_anc.labor_doctor_type_id,

person_anc.preg_no,person_anc.labor_date

from person_anc

LEFT JOIN person_labour_doctor_type as pd on pd.person_labour_doctor_type_id = person_anc.labor_doctor_type_id

INNER JOIN person AS p ON p.person_id = person_anc.person_id

WHERE labor_date BETWEEN @date1 and @date2

and person_anc.labor_doctor_type_id is null

/*LBORN จำนวนเกิดมีชีพ ที่ผิดปกติ */

/*SBORN จำนวนเกิดมีชีพ ที่ผิดปกติ */

set @date1 = '2009-11-01',@date2 = '2017-09-30';

SELECT

v.labor_date ,

p.pname,concat(p.fname,space(1),p.lname)as 'mother',

l.labor_status_name,

v.alive_child_count,

v.dead_child_count

FROM

person_anc as v

INNER JOIN person as p on p.person_id = v.person_id

left join labor_status as l on l.labor_status_id = v.labor_status_id

WHERE v.labor_date BETWEEN @date1 and @date2

and (v.alive_child_count not between 0 and 4

or (v.dead_child_count not between 0 and 4))