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

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')

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

/*VACCINETYPE รหัสวัคซีน*/

SET @date1= '2015-04-01',@date2='2016-04-30'; /*DATE_SERV  วันที่ให้บริการ */

drop table if EXISTS table2;

CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS

select *  from

(SELECT DISTINCT

p.cid,

p.pname,

concat(p.fname,space(1),p.lname) as person_name,

a.vaccine_date,

GROUP_CONCAT(b.vaccine_code)vaccine,

'list' AS 'table'

FROM

person_vaccine_list a

INNER JOIN

person AS p ON p.person_id = a.person_id

INNER JOIN

person_vaccine AS b ON b.person_vaccine_id = a.person_vaccine_id

WHERE

a.vaccine_date BETWEEN @date1 AND @date2

GROUP BY p.person_id , a.vaccine_date

UNION

SELECT  DISTINCT

pp.cid,

pp.pname,

concat(pp.fname,space(1), pp.lname) as person_name,

e.vaccine_date,

GROUP_CONCAT(bb.vaccine_code)vaccine,

'elsewhere' AS 'table'

FROM

person_vaccine_elsewhere e

INNER JOIN

person AS pp ON pp.person_id = e.person_id

INNER JOIN

person_vaccine AS bb ON bb.person_vaccine_id = e.person_vaccine_id

WHERE

e.vaccine_date BETWEEN @date1 AND @date2

GROUP BY pp.person_id , e.vaccine_date ) bn;

select * from table2 order by cid;

drop table if EXISTS table2;

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

/*วัคซีนรับจากที่อื่น VACCINEPLACE  สถานที่รับวัคซีน ผิดปกติ*/

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

SELECT

'elsewhere' AS `table`,

person_vaccine_elsewhere.vaccine_date,

person_vaccine_elsewhere.vaccine_hospcode,

person.pname,

concat(person.fname,space(1),person.lname)person_name

,TIMESTAMPDIFF(MONTH,

person.birthdate,

person_vaccine_elsewhere.vaccine_date) AS 'age_month',

person_wbc.discharge,

GROUP_CONCAT(person_vaccine.vaccine_code) AS vaccine,

person_vaccine_elsewhere.vaccine_note

FROM

person_vaccine_elsewhere

INNER JOIN

person ON person.person_id = person_vaccine_elsewhere.person_id

INNER JOIN

person_vaccine ON person_vaccine.person_vaccine_id = person_vaccine_elsewhere.person_vaccine_id

INNER JOIN

person_wbc ON person_wbc.person_id = person.person_id

WHERE

person_vaccine_elsewhere.vaccine_date between @date1 and @date2

and vaccine_hospcode IS NULL

GROUP BY person.cid , person_vaccine_elsewhere.vaccine_date

order by person.cid,person_vaccine_elsewhere.vaccine_date;

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

/*แก้ไข เมื่อถูกส่งเข้า Datacenter ข้อมูลจะยังคงมีสถานบริการที่ให้บริการ*/

/*update vaccine_hospcode */

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

#--

UPDATE person_vaccine_elsewhere

set vaccine_hospcode = hospcode

WHERE left(hospcode ,1) BETWEEN '0' and '9' ;

#--

SELECT

'elsewhere' AS `table`,

person_vaccine_elsewhere.vaccine_date,

person_vaccine_elsewhere.vaccine_hospcode,

person.pname,

concat(person.fname,space(1),person.lname)person_name

,TIMESTAMPDIFF(MONTH,

person.birthdate,

person_vaccine_elsewhere.vaccine_date) AS 'age_month',

person_wbc.discharge,

GROUP_CONCAT(person_vaccine.vaccine_code) AS vaccine,

person_vaccine_elsewhere.vaccine_note

FROM

person_vaccine_elsewhere

INNER JOIN

person ON person.person_id = person_vaccine_elsewhere.person_id

INNER JOIN

person_vaccine ON person_vaccine.person_vaccine_id = person_vaccine_elsewhere.person_vaccine_id

INNER JOIN

person_wbc ON person_wbc.person_id = person.person_id

WHERE

person_vaccine_elsewhere.vaccine_date between @date1 and @date2

-- and vaccine_hospcode IS NULL

GROUP BY person.cid , person_vaccine_elsewhere.vaccine_date

order by person.cid,person_vaccine_elsewhere.vaccine_date;

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

/*ค้นรายชื่อ ให้บริการวัคซีน ในฝังบริการ เลือกที่ให้บริการจริงเป็นที่อื่น แต่ไม่บันทึกในฝั่ง บริการที่อื่น*/

set @date1 ='2013-10-01' ,@date2 = '2017-09-30';/*เปลี่ยน วันที่*/

SELECT

person.cid,

person.pname,

CONCAT(person.fname, SPACE(1), person.lname) AS person_name,

person_wbc_service.service_date,

#person_wbc_service.wbc_location_type_id,

wbc_location_type.wbc_location_type_name,

GROUP_CONCAT(person_vaccine.vaccine_code) AS vaccine_name,

person_vaccine_elsewhere.person_vaccine_id

FROM

person_wbc

INNER JOIN person_wbc_service ON person_wbc.person_wbc_id = person_wbc_service.person_wbc_id

INNER JOIN person ON person.person_id = person_wbc.person_id

INNER JOIN person_vaccine_list ON person_wbc.person_id = person_vaccine_list.person_id

INNER JOIN person_vaccine ON person_vaccine.person_vaccine_id = person_vaccine_list.person_vaccine_id

LEFT JOIN person_vaccine_elsewhere ON person.person_id = person_vaccine_elsewhere.person_id

INNER JOIN wbc_location_type ON wbc_location_type.wbc_location_type_id = person_wbc_service.wbc_location_type_id

WHERE

person_wbc_service.service_date BETWEEN @date1 AND @date2

AND person_wbc_service.wbc_location_type_id = 2

AND person_vaccine_elsewhere.person_vaccine_id IS NULL

GROUP BY person.cid , person_wbc_service.service_date

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

/*IPV-P นำเข้าบัญชี 3 เด็ก 0-1 ปี ตาราง   person_vaccine*/

DELETE from person_vaccine WHERE export_vaccine_code = '401';

SET @person_vaccine_id = (select max(person_vaccine_id)+1 from person_vaccine) ;

INSERT IGNORE INTO `person_vaccine` (`person_vaccine_id`, `vaccine_name`, `vaccine_code`, `vaccine_group`, `export_vaccine_code`, `hos_guid`, `combine_vaccine`, `icode`)

VALUES (@person_vaccine_id, '401 : IPV-P ฉีดโปลิโอ 4 เดือน (Z240)', 'IPV-P', 'IPV-P', '401', NULL, 'N', NULL);

/*ตาราง WBC_vaccine*/

DELETE from WBC_vaccine WHERE export_vaccine_code = '401';

SET @wbc_vaccine_id = (select max(wbc_vaccine_id)+1 from wbc_vaccine) ;

INSERT IGNORE INTO `hos`.`wbc_vaccine` (`wbc_vaccine_id`, `wbc_vaccine_name`, `wbc_vaccine_code`, `age_min`, `age_max`, `export_vaccine_code`, `check_code`, `combine_vaccine`)

VALUES (@wbc_vaccine_id, 'ไอพีวี-พี', 'IPV-P', '0', '6', '401', 'IPV-P', 'N');

/*ตาราง   vaccine_combination*/

DELETE FROM vaccine_combination WHERE vaccine_code = 'IPV-P';

SET @vaccine_combination_id = (select max(vaccine_combination_id)+1 from vaccine_combination) ;

INSERT INTO `hos`.`vaccine_combination` (`vaccine_combination_id`, `vaccine_code`, `vaccine_combine_code`, `hos_guid`) VALUES ('86', 'IPV-P', 'IPV-P', null);

/*ตาราง baby_items นำเข้า one STOP service*/

DELETE FROM baby_items WHERE vaccine_code = 'IPV-P';

SET @baby_code = (SELECT MAX(baby_code)+1 FROM baby_items);

INSERT IGNORE INTO `baby_items` (`baby_code`, `baby_group`, `service_code`, `baby_name`, `vaccine_code`, `display_order`, `export_code`, `hos_guid`)

VALUES (@baby_code , '4', NULL, 'ไอพีวี-พี', 'IPV-P', NULL, '401', NULL) ;

/*provis_aptype ข้อมูลการนัดหมาย*/

DELETE FROM provis_aptype WHERE NAME ='IPV-P';

INSERT IGNORE INTO `provis_aptype` (`code`, `name`, `hos_guid`, `hos_guid_ext`)

VALUES ('401', 'IPV-P', NULL, NULL);

/*Provis_vcctype ตารางมาตรฐานส่ง EPI */

DELETE FROM provis_vcctype WHERE CODE ='401';

INSERT IGNORE INTO provis_vcctype (CODE,NAME,hos_guid)

VALUES ('401','IPV-P',NULL);

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

/*หา เด็ก รหัสสถานพยาบาล ว่าง ในวัคซีนที่รับบริการที่อื่น*/

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

SELECT

person_vaccine_elsewhere.vaccine_date,

person.pname,

CONCAT(person.fname,SPACE(1),person.lname ) AS person_name,

person_vaccine_elsewhere.hospcode,

person_vaccine_elsewhere.vaccine_hospcode,

GROUP_CONCAT(person_vaccine.vaccine_code) vaccine

FROM

person_vaccine_elsewhere

INNER JOIN person ON person.person_id = person_vaccine_elsewhere.person_id

INNER JOIN person_vaccine ON person_vaccine.person_vaccine_id

= person_vaccine_elsewhere.person_vaccine_id

WHERE

person_vaccine_elsewhere.vaccine_date between @date1 and @date2

and hospcode IS NULL

AND person.`house_regist_type_id` IN ('1', '3')

GROUP BY person.cid,person_vaccine_elsewhere.vaccine_date

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

/*หา เด็ก รหัสสถานพยาบาล ว่าง ในวัคซีนที่รับบริการที่อื่น*/

SELECT

person.cid,

person.pname,

CONCAT(person.fname, SPACE(1), person.lname) AS person_name,

group_concat(vaccine_code)as vaccine,

person_vaccine_elsewhere.hospcode,

person_vaccine_elsewhere.vaccine_date,

person_vaccine_elsewhere.vaccine_hospcode

FROM person_vaccine_elsewhere

INNER JOIN person ON person.`person_id` = person_vaccine_elsewhere.`person_id`

inner join person_vaccine on person_vaccine.person_vaccine_id = person_vaccine_elsewhere.person_vaccine_id

WHERE hospcode IS NULL

AND person.`house_regist_type_id` IN ('1','3')

and (person_vaccine.vaccine_code = 'BCG'

or person_vaccine.vaccine_code = 'HBV1' )

group by person.cid,person_vaccine_elsewhere.vaccine_date

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

/*labour no relate BCG */

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

SELECT

p.pname,

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

v.vaccine_date ,group_concat(vaccine_code) as 'vaccine'

FROM

person_labour AS pl

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

left join person_vaccine_list as v on v.person_id = pl.person_id

inner join person_vaccine as pv on pv.person_vaccine_id = v.person_vaccine_id

where pv.vaccine_code <> 'bcg'

and p.birthdate between @date1 and @date2

group by p.cid

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

/*

ทำวัคซีนexport_vaccine_codeJ11,J12.ให้แสดงหน้าบัญชี4

*/

SET @vcode1= 'LAJE1';

SET @vcode2 = 'LAJE2';

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

UPDATE person_vaccine AS p

SET p.vaccine_code = @vcode1

WHERE p.export_vaccine_code = 'J11';

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

UPDATE person_vaccine AS p

SET p.vaccine_code = @vcode2

WHERE p.export_vaccine_code = 'J12';

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

UPDATE epi_vaccine AS e

SET e.vaccine_code =@vcode1

WHERE e.export_vaccine_code = 'J11';

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

UPDATE epi_vaccine AS e

SET e.vaccine_code =@vcode2

WHERE e.export_vaccine_code = 'J12';

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

UPDATE vaccine_combination AS b

SET b.vaccine_code =@vcode1,b.vaccine_combine_code=@vcode1

WHERE b.vaccine_code='LAJE1' OR b.vaccine_code ='laje1' OR b.vaccine_code='J11';

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

UPDATE vaccine_combination AS b

SET b.vaccine_code =@vcode2,b.vaccine_combine_code=@vcode2

WHERE b.vaccine_code='LAJE2' OR b.vaccine_code ='laje2' OR b.vaccine_code='J12';

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

UPDATE provis_vcctype AS t

SET t.name = @vcode1

WHERE t.code ='J11' OR t.code = 'j11';

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

UPDATE provis_vcctype AS t

SET t.name = @vcode2

WHERE t.code ='J12' OR t.code = 'j12';

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

UPDATE provis_aptype AS a

SET a.name = @vcode1

WHERE a.code ='J11' OR a.code = 'j11';

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

UPDATE provis_aptype AS a

SET a.name = @vcode2

WHERE a.code ='J12' OR a.code = 'j12';

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