SELECT a.VISIT_ID as seq,
DATE_FORMAT(date(a.REG_DATETIME),'%Y%m%d') as date_serv,
time(a.REG_DATETIME) as time_serv,
c.DRUG_NAME as drug_name,
b.RX_AMOUNT as qty,
replace(replace(replace(c.INFO_PATIENT,char(13),''),char(10),''),'""','') as usage_line1 ,
replace(replace(replace(c.DRUG_INFO,char(13),''),char(10),''),'""','') as usage_line2,
replace(replace(replace(c.ATTENTION,char(13),''),char(10),''),'""','') as usage_line3
FROM opd_visits a
INNER JOIN prescriptions b ON a.VISIT_ID = b.VISIT_ID AND b.IS_CANCEL =0
INNER JOIN drugs c ON b.DRUG_ID = c.DRUG_ID
INNER JOIN usage_units d ON c.PACKAGE = d.UUNIT_ID
WHERE a.VISIT_ID = '${seq}'
UNION
SELECT i.VISIT_ID as seq,
DATE_FORMAT(date(i.adm_dt),'%Y-%m-%d') as date_serv,
time(i.adm_dt) as time_serv,
c.DRUG_NAME as drug_name,
b.rx_dose as qty,
CONCAT(c.prepack,' ',d.UUNIT_NAME) as unit ,
replace(replace(replace(c.INFO_PATIENT,char(13),''),char(10),''),'""','') as usage_line1 ,
replace(replace(replace(c.DRUG_INFO,char(13),''),char(10),''),'""','') as usage_line2,
replace(replace(replace(c.ATTENTION,char(13),''),char(10),''),'""','') as usage_line3
FROM ipd_reg i
INNER JOIN ipd_oneday b ON i.visit_id = b.visit_id AND b.is_cancel = 0
INNER JOIN drugs c ON b.DRUG_ID = c.DRUG_ID
INNER JOIN usage_units d ON c.PACKAGE = d.UUNIT_ID
WHERE i.VISIT_ID = '${seq}'
UNION
SELECT i.VISIT_ID as seq,
DATE_FORMAT(date(i.adm_dt),'%Y-%m-%d') as date_serv,
time(i.adm_dt) as time_serv,
c.DRUG_NAME as drug_name,
b.rx_dose as qty,
CONCAT(c.prepack,' ',d.UUNIT_NAME) as unit ,
replace(replace(replace(c.INFO_PATIENT,char(13),''),char(10),''),'""','') as usage_line1 ,
replace(replace(replace(c.DRUG_INFO,char(13),''),char(10),''),'""','') as usage_line2,
replace(replace(replace(c.ATTENTION,char(13),''),char(10),''),'""','') as usage_line3
FROM ipd_reg i
INNER JOIN ipd_cont b ON i.visit_id = b.visit_id AND b.is_cancel = 0
INNER JOIN drugs c ON b.DRUG_ID = c.DRUG_ID
INNER JOIN usage_units d ON c.PACKAGE = d.UUNIT_ID
ไม่มีความคิดเห็น:
แสดงความคิดเห็น