- เพิ่มลายเซ็นใน Hosxp
sql (ตารางเก็บข้อมูลลายเซ็น)
select signature_jpeg_blob from doctor_signature where doctor_code = '0206' limit 10ไปที่ : Tools > Report > Report Designer > ค้นหาฟอร์ม
- ใบรับรองต่างๆ : ค้นหา Cert
- แบบฟอร์ม ค้นหา Report
เข้าไปในแบบฟอร์ม > #อย่าลืมใส่ HN คนไข้ที่ "value" มันจะ error > Design Report > Calc > event
# อย่าลืม ลายเซ็น ต้อง join ตาราง doctor ด้วย Code เสมอ
+' LEFT JOIN doctor_signature ds1 ON ds1.doctor_code = d.code'เสร็จแล้ว ไปที่ select ให้ select เอา ds1.signature_jpeg_blob ด้วย
+' opdscreen_fp_type.opdscreen_fp_type_name,ost.name as ovstost,ds1.signature_jpeg_blob 'ต่อไปเพิ่มลายเซ็นที่หน้า Design
- ใช้ DBimage เพราะไม่ต้อง config code เพิ่ม เหมือนการใช้ image
- เลือกข้อมูลว่าเป็น pipeline ไหนและใช้ data อะไร
- ปรับขนาดโดย คลิกขวาที่ image เลือก stretch ให้ขนาดเล็กลง
- จากนั้นก็ปรับขนาดให้พอดีกับหน้ากระดาษ
- # ห้ามลบ variable อะไรทั้งนั้นเพราะ ทุกอย่างถูกเขียนเชื่อมโยงกันหมด จะเกิด error
# ตัวอย่าง code opd card BACKUP
_vn1 := GetUserParameter(0); _vn := _vn1; _hn := GetSQLStringData('select hn from ovst where vn ="'+_vn+'" '); vn_lists := GetSQLSubQueryData('select vn from ovst where vn in("'+_vn+'") '); if vn_lists = '' then vn_lists := '"A999999999"'; if _vn = '' then _vn := '"A999999999"'; doctor_list := GetSQLSubQueryData('select distinct doctor_code ' +' from( ' +' select doctor_code from patient_history_hpi where vn = "'+_vn+'" ' +' union all select doctor from ovstdiag where vn = "'+_vn+'" ' +' union all select doctor_code from ovst_doctor_diag where vn = "'+_vn+'" ' +' union all select doctor from opitemrece where vn = "'+_vn+'" ' +' union all select doctor from dtmain where vn = "'+_vn+'" ' +' union all select doctor_code from opdscreen_doctor_pe where vn = "'+_vn+'" ' +' ) as cc ' +' where doctor_code is not null '); { repeat showmessage('เลือกวันที่ต้องการพิมพ์ข้อมุล'); vn_lists := GetMultipleList('select to_char(vstdate,"yyyymmdd") from ovst where hn = "'+_hn+'" order by vstdate desc limit 100 '); until vn_lists <> ''; vn_lists := GetSQLSubQueryData('select vn from ovst where hn = "'+_hn+'" and to_char(vstdate,"yyyymmdd") in('+vn_lists+') '); } ChangeReportSQL(' SELECT ' +' ovst.vn, concat("200",ovst.vn) as barvn, ovst.hn, ovst.vstdate, ovst_seq.hospital_department_id, ' +' hospital_department.name AS hospital_department_name, ovst.pttype, ovst.pttypeno, ' +' case when pt.bloodgrp <>"" then pt.bloodgrp ' +' when pt2.bloodgrp <>"" then pt2.bloodgrp ' +' else "ไม่ทราบ" end as bloodgrp, ' +' coalesce(pt.bloodgroup_rh,pt2.bloodgroup_rh) bldgr_rh, ' +' coalesce(pt.mobile_phone_number,pt2.mobile_phone_number) pt_mobile, ' +' coalesce(pt.hometel,pt2.hometel) pt_hometel, ' +' ovst.spclty, coalesce(pt.birthday,pt2.birthday) as birthday, coalesce(pt.cid,pt2.cid) cid, ' +' Concat(pt2.pname, pt2.fname, " ", pt2.lname) AS pt_name, ' // +' case when pt.pname <> "" then Concat(pt.pname, pt.fname, " ", pt.lname) else Concat(pt2.pname, pt2.fname, " ", pt2.lname) end AS pt_name, ' +' opdscreen.bpd,opdscreen.bps,opdscreen.bw, opdscreen.cc,opdscreen.hr, opdscreen.pe, ' +' opdscreen.pulse, opdscreen.temperature,opdscreen.rr,opdscreen.height, opdscreen.fbs, ' +' opdscreen.bmi,opdscreen.tg, opdscreen.pregnancy,opdscreen.tc,opdscreen.ast, ' +' opdscreen.ldl, opdscreen.symptom, opdscreen.alt,opdscreen.walk_id, opdscreen.peak_flow,opdscreen.pe, ' //+'opdscreen.pe_rtf ' +' opdscreen.breast_feeding,opdscreen.cradle_lie,opdscreen.cholesterol,opdscreen.waist, opdscreen.pain_score,opdscreen.lmp_date,opdscreen.o2sat, ' +' opdscreen_fbs.dtx1, opdscreen_fbs.dtx2, opdscreen.opdscreen_patient_type_id, ' +' smoking_type.smoking_type_name, drinking_type.drinking_type_name,pttype1.name AS pttypename, ' +' case when pt.sex = "1" or mct.menstrual_cycle_type_name is null then "N/A" else mct.menstrual_cycle_type_name end AS menstrual_cycle_type_name,' +' vst.vaccine_screen_type_name, dst.development_screen_type_name,' +' spclty.name AS spclty_name,ovst.vsttime, sex2.name AS sexname1, ' +' his.pmh as pmh, ' +' his.fh AS fh, ' +' his.sh AS sh, ' +' his.ros AS ros, ' +' Concat("201", ovst.vn) AS vn2, opdscreen.found_allergy,found_allergy_type.found_allergy_type_name, ' +' d.name AS doctor, h1.name AS hospmain,h2.name AS hospsub, ' +' er_pt_type.name AS pt_type, ed.er_accident_type_id, ' +' ed.gcs_e, ed.gcs_m,ed.gcs_v,ed.pupil_l,ed.pupil_l_text, ' +' ed.pupil_r, ed.pupil_r_text, ' +' pt2.informname, ' +' ' {+' case ' +' when pt.ec_fname <> "" then rt.patient_relation_type_name ' +' when pt2.ec_fname <> "" then rt2.patient_relation_type_name' +' when pt.informname <> "" then pt.informrelation ' +' else pt2.informname end AS patient_relation_type_name, ' +' ' } +' pt2.informrelation AS patient_relation_type_name, ' { +' case ' +' when pt.ec_fname <> "" then Concat(pt.ec_fname," ",pt.ec_lname) ' +' when pt2.ec_fname <> "" then Concat(pt2.ec_fname, " ",pt2.ec_lname) ' +' when pt.informrelation <> "" then pt.informname ' +' else pt2.informrelation end AS contact, ' } +' pt2.informname AS contact, ovst.oqueue, ' +' ' {+' case ' +' when length(t5.full_name) > 0 then Concat(("เลขที่ " || pr5.addrpart), (" หมู่ " || pr5.moopart), t5.full_name) ' +' when length(t4.full_name) > 0 then Concat(("เลขที่ " || pr4.addrpart), (" หมู่ " || pr4.moopart), t4.full_name) ' +' when length(t52.full_name) > 0 then Concat(("เลขที่ " || pr52.addrpart), (" หมู่ " || pr52.moopart), t52.full_name) ' +' when length(t42.full_name) > 0 then Concat(("เลขที่ " || pr42.addrpart), (" หมู่ " || pr42.moopart), t42.full_name) ' +' end AS contact_addr, ' } +' Concat("เลขที่ ",COALESCE(pr4.addrpart,"")," หมู่ " , COALESCE(pr4.moopart,""), COALESCE(t4.full_name,"")) as contact_addr,' +' ' {+' CASE ' +' when coalesce(pr5.phone, "") <> "" or coalesce(pr5.mobile, "") <> "" THEN concat(pr5.phone, " ", pr5.mobile)' +' when coalesce(pr4.phone, "") <> "" or coalesce(pr4.mobile, "") <> "" THEN concat(pr4.phone, " ", pr4.mobile)' +' when coalesce(pr42.phone, "") <> "" or coalesce(pr42.mobile, "") <> "" THEN concat(pr42.phone, " ", pr42.mobile)' +' when coalesce(pr52.phone, "") <> "" or coalesce(pr52.mobile, "") <> "" THEN concat(pr52.phone, " ", pr52.mobile)' } +' COALESCE(pr4.mobile,pr4.phone) AS contact_phone, ' +' opdscreen.pregnancy, ' +' opdscreen.breast_feeding,opdscreen_fp.g,opdscreen_fp.p,opdscreen_fp.a,opdscreen_fp.last_child_age_y,opdscreen_fp.last_child_age_m,opdscreen_fp.last_child_age_d, ' +' opdscreen_fp_type.opdscreen_fp_type_name,ost.name as ovstost,ds1.signature_jpeg_blob ' +' FROM ovst ' +' LEFT JOIN ovst_tx_doctor otd ON otd.vn = ovst.vn ' +' LEFT JOIN doctor dds ON dds.code = otd.doctor_code ' +' LEFT JOIN ovst_seq ON ovst_seq.vn = ovst.vn ' +' LEFT JOIN ovstost ost ON ost.ovstost = ovst.ovstost ' +' LEFT JOIN hospital_department ON hospital_department.id = ovst_seq.hospital_department_id ' +' LEFT JOIN ovst_patient_record pt ON pt.vn = ovst.vn ' +' LEFT JOIN patient pt2 ON pt2.hn = ovst.hn ' +' LEFT JOIN opdscreen ON ovst.vn = opdscreen.vn ' +' LEFT JOIN opdscreen_fbs ON opdscreen_fbs.vn = opdscreen.vn ' +' LEFT JOIN opdscreen_fp ON opdscreen_fp.vn = opdscreen.vn ' +' LEFT JOIN opdscreen_fp_type ON opdscreen_fp_type.opdscreen_fp_type_id = opdscreen_fp.opdscreen_fp_type_id ' +' LEFT JOIN smoking_type ON smoking_type.smoking_type_id = opdscreen.smoking_type_id ' +' LEFT JOIN drinking_type ON drinking_type.drinking_type_id = opdscreen.drinking_type_id ' +' LEFT JOIN pttype pttype1 ON ovst.pttype = pttype1.pttype ' +' LEFT JOIN spclty ON ovst.spclty = spclty.spclty ' +' LEFT JOIN found_allergy_type ON found_allergy_type.found_allergy = opdscreen.found_allergy ' +' ' +' LEFT JOIN sex ON pt.sex = sex.code ' +' LEFT JOIN thaiaddress ta ON ta.chwpart = pt.chwpart AND pt.amppart = ta.amppart AND pt.tmbpart = ta.tmbpart AND ta.codetype = "3" ' +' LEFT JOIN patient_relation_type rt ON pt.ec_relation_type_id = rt.patient_relation_type_id ' +' LEFT JOIN patient_relation_address pr4 ON pt2.hn = pr4.hn AND pr4.patient_relation_type_id = 4 ' +' LEFT JOIN thaiaddress t4 ON t4.chwpart = pr4.chwpart AND pr4.amppart = t4.amppart AND pr4.tmbpart = t4.tmbpart AND t4.codetype = "3" ' +' LEFT JOIN patient_relation_address pr5 ON pt.hn = pr5.hn AND pr5.patient_relation_type_id = 5' +' LEFT JOIN thaiaddress t5 ON t5.chwpart = pr5.chwpart AND pr5.amppart = t5.amppart AND pr5.tmbpart = t5.tmbpart AND t5.codetype = "3" ' +' ' +' LEFT JOIN sex sex2 ON pt2.sex = sex2.code ' +' LEFT JOIN thaiaddress ta2 ON ta2.chwpart = pt2.chwpart AND pt2.amppart = ta2.amppart AND pt2.tmbpart = ta2.tmbpart AND ta2.codetype = "3" ' +' LEFT JOIN patient_relation_type rt2 ON pt2.ec_relation_type_id = rt2.patient_relation_type_id ' +' LEFT JOIN patient_relation_address pr42 ON pt2.hn = pr42.hn AND pr42.patient_relation_type_id = 4 ' +' LEFT JOIN thaiaddress t42 ON t42.chwpart = pr42.chwpart AND pr42.amppart = t42.amppart AND pr42.tmbpart = t42.tmbpart AND t42.codetype = "3" ' +' LEFT JOIN patient_relation_address pr52 ON pt2.hn = pr52.hn AND pr52.patient_relation_type_id = 5' +' LEFT JOIN thaiaddress t52 ON t52.chwpart = pr52.chwpart AND pr52.amppart = t52.amppart AND pr52.tmbpart = t52.tmbpart AND t52.codetype = "3" ' +' ' +' LEFT JOIN patient_history his ON his.hn = ovst.hn ' +' LEFT JOIN ovstdiag odx ON ovst.vn = odx.vn AND odx.diagtype = "1" ' +' LEFT JOIN doctor d ON d.code = odx.doctor AND d.position_id in (1,2) ' +' LEFT JOIN hospcode h1 ON h1.hospcode = ovst.hospmain ' +' LEFT JOIN hospcode h2 ON h2.hospcode = ovst.hospsub ' +' LEFT JOIN er_pt_type ON er_pt_type.er_pt_type = ovst_seq.er_pt_type ' +' LEFT JOIN er_nursing_detail ed ON ed.vn = ovst.vn ' +' LEFT JOIN menstrual_cycle_type mct ON mct.menstrual_cycle_type_id = opdscreen.menstrual_cycle_type_id' +' LEFT JOIN vaccine_screen_type vst ON vst.vaccine_screen_type_id = opdscreen.vaccine_screen_type_id' +' LEFT JOIN development_screen_type dst ON dst.development_screen_type_id = opdscreen.development_screen_type_id' +' LEFT JOIN doctor_signature ds1 ON ds1.doctor_code = d.code' +' WHERE coalesce(ovst.vn,"") in ('+vn_lists+') order by ovst.vn desc LIMIT 1'); hospcode := GetSQLStringData('select hospitalcode from opdconfig'); hospcodeAddr := GetSQLStringData(' select Concat( CASE ' +' WHEN (addrpart IS NULL OR addrpart = "") ' +' THEN "-" ' +' ELSE addrpart ' +' END, " หมู่ ", CASE ' +' WHEN (moopart IS NULL OR moopart = "") ' +' THEN "-" ' +' ELSE moopart ' +' END, " ",tt.full_name, " ", Coalesce(h.po_code, "")) AS FullAddress ' +' from hospcode h' +' left join thaiaddress tt on concat(h.chwpart,h.amppart,h.tmbpart)=tt.addressid' +' where h.hospcode = "'+hospcode+'"' ); LnHospcodeAddr.Text := hospcodeAddr; LnHospcodeAddr1.Text := hospcodeAddr; LnHospcode1.Text := hospitalname; LnHospcode2.Text := hospitalname; if GetSQLintegerData('select count(*) from patient_image where hn = "'+DBPipeline['hn']+'"')>0 then img_hn := DBPipeline['hn'] else img_hn := '999999999'; LoadPPImageFromSQL(Image1,'select app_icon_80x80_png from opdconfig'); LoadPPImageFromSQL(Image2,'select app_icon_80x80_png from opdconfig'); LoadPPImageFromSQL(Image3,'select image from patient_image where hn = "'+img_hn+'"'); LoadPPImageFromSQL(Image4,'select image from patient_image where hn = "'+img_hn+'"'); opd_allergy := GetSQLSubQueryData('select concat(agent,":"||symptom) from opd_allergy where hn="'+_hn+'" '); chk_allergy := GetSQLIntegerData('select count(*) from opd_allergy where hn="'+_hn+'" '); patient_address := GetPatientAddress(_hn); reportname := getReportName+' v.' +GetSQLStringData(' select report_version from reporttemplate where name = "'+GetReportName+'"'); ChangeDBPipeLineLink8SQL(' select p.*,o.name from patient_log p ' +' left outer join opduser o on o.loginname = p.staff ' +' where p.hn = "'+_hn+'" order by p.log_datetime desc limit 1 ' ); if DBPipeLineLink8['name'] <> '' then patient_log := DBPipeLineLink8['name'] +' วันที่ '+FormatThaidate('d mmm yy',DBPipeLineLink8['log_datetime']) else patient_log := '-'; { doctor_name := StringReplaceAll(GetSQLSubQueryData(' select distinct d.name' +' from ovst od, doctor d' +' where od.sign_doctor = d.code and vn = "'+_vn1+'" ' +' and d.position_id in(1,2,45,54,73) ' ),'''', '', True); nurse_screen := GetSQLStringData('select concat("ผู้ซักประวัติ : ",d.name) as doctorname ' +' from ovst_service_time o ' +' left outer join doctor d on d.code=o.doctor ' +' where o.vn = "'+_vn+'" ' +' and o.ovst_service_time_type_code = "OPD-SCREEN" limit 1' ); } chk_diag := GetSQLIntegerData('select count(*) from ovstdiag where vn = "'+DBPipeline['vn']+'" and diagtype = "1" '); doctor_name := GetSQLStringData(' select Concat(d.name," ",d.licenseno)' +' from ovstdiag od, doctor d' +' where od.doctor = d.code and vn = "'+DBPipeline['vn']+'"' +' and od.diagtype = "1" ' ); doctor_dx_name := GetSQLStringData(' SELECT Concat(doctor.name," ",doctor.licenseno) from ovst_doctor_diag , doctor ' +' where ovst_doctor_diag.vn = "'+DBPipeline['vn']+'" and ovst_doctor_diag.doctor_code = doctor.code '); LoadPPImageFromSQL(Image5,'select officer_signature_jpeg_blob from officer_signature os,officer ofi,doctor d ' +' where os.officer_id = ofi.officer_id and ofi.officer_doctor_code = d.code and d.name = "'+doctor_name+'"'); nurse_screen := GetSQLStringData('select concat("ผู้ซักประวัติ : ",d.name) as doctorname ' +' from opdscreen_cc_list o ' +' left join opduser u on o.staff = u.loginname ' +' left join doctor d on d.code=u.doctorcode ' +' where o.vn = "'+_vn+'" ' +' order by o.update_datetime desc limit 1' ); food_allergy := GetSQLStringData('select GROUP_CONCAT(agent) FROM food_allergy fa WHERE hn = "'+DBPipeLine['hn']+'" '); // ------------------------ Region_accident --------------------------------- // Region_accident.Visible := GetSQLStringData('select vn from er_regist where vn = "'+_vn+'" ') <> ''; er_accident_type_name.text := ''; gcs_e.Text := ''; gcs_v.Text := ''; gcs_m.Text := ''; er_accident_type_name.text := GetSQLStringData('select er_accident_type_name from er_accident_type ' +' where er_accident_type_id = "'+DBPipeline['er_accident_type_id']+'"'); if DBPipeline['gcs_e'] <> 0 then gcs_e.Text := GetSQLStringData('select gcs_scale_eye_type_name ' +' from gcs_scale_eye_type where gcs_scale_eye_type_id = "'+DBPipeline['gcs_e']+'" '); if DBPipeline['gcs_v'] <> 0 then gcs_v.Text := GetSQLStringData('select gcs_scale_verbal_type_name ' +' from gcs_scale_verbal_type where gcs_scale_verbal_type_id = "'+DBPipeline['gcs_v']+'" '); if DBPipeline['gcs_m'] <> 0 then gcs_m.Text := GetSQLStringData('select gcs_scale_motor_type_name from gcs_scale_motor_type ' +' where gcs_scale_motor_type_id = "'+DBPipeline['gcs_m']+'"'); // ------------------------------------------------------------------------ // // ------------------------------ BP วัดซ้ำ------------------------------- // ChangeDBPipeLineLink8SQL(' select o1.* ,o2.name as staff_name ,k.department' +' from opdscreen_bp o1 ' +' left outer join opduser o2 on o2.loginname = o1.staff' +' left outer join kskdepartment k on k.depcode = o1.depcode' +' where o1.vn = "'+_vn+'" order by o1.screen_date desc,o1.screen_time desc limit 3' ); // ------------------------------------------------------------------------ // // ---------------------------- Region_PE --------------------------------- // ChangeDBPipeLineLink1SQL(' select oe.doctor_code, concat(d.name,(" ["||d.licenseno||"]")) as doctor_name, ' +' min(case when coalesce(oe.pe_ga,"-")="Y" then "Normal" when coalesce(oe.pe_ga,"-")="N" then "Abnormal" WHEN coalesce(oe.pe_ga,"-") = "-" THEN "ไม่ได้ตรวจ" else "" end) as pe_ga_name, GROUP_CONCAT(oe.pe_ga_text) as pe_ga_text, ' +' min(case when coalesce(oe.pe_heent,"-")="Y" then "Normal" when coalesce(oe.pe_heent,"-")="N" then "Abnormal" WHEN coalesce(oe.pe_heent,"-") = "-" THEN "ไม่ได้ตรวจ" else "" end) as pe_heent_name, GROUP_CONCAT(oe.pe_heent_text) as pe_heent_text, ' +' min(case when coalesce(oe.pe_heart,"-")="Y" then "Normal" when coalesce(oe.pe_heart,"-")="N" then "Abnormal" WHEN coalesce(oe.pe_heart,"-") = "-" THEN "ไม่ได้ตรวจ" else "" end ) as pe_heart_name, GROUP_CONCAT(oe.pe_heart_text) as pe_heart_text, ' +' min(case when coalesce(oe.pe_chest,"-")="Y" then "Normal" when coalesce(oe.pe_chest,"-")="N" then "Abnormal" WHEN coalesce(oe.pe_chest,"-") = "-" THEN "ไม่ได้ตรวจ" else "" end ) as pe_chest_name, GROUP_CONCAT(oe.pe_chest_text) as pe_chest_text, ' +' min(case when coalesce(oe.pe_ab,"-")="Y" then "Normal" when coalesce(oe.pe_ab,"-")="N" then "Abnormal" WHEN coalesce(oe.pe_ab,"-") = "-" THEN "ไม่ได้ตรวจ" else "" end ) as pe_ab_name, GROUP_CONCAT(oe.pe_ab_text) as pe_ab_text, ' +' min(case when coalesce(oe.pe_pv,"-")="Y" then "Normal" when coalesce(oe.pe_pv,"-")="N" then "Abnormal" WHEN coalesce(oe.pe_pv,"-") = "-" THEN "ไม่ได้ตรวจ" else "" end ) as pe_pv_name, GROUP_CONCAT(oe.pe_pv_text) as pe_pv_text, ' +' min(case when coalesce(oe.pe_pr,"-")="Y" then "Normal" when coalesce(oe.pe_pr,"-")="N" then "Abnormal" WHEN coalesce(oe.pe_pr,"-") = "-" THEN "ไม่ได้ตรวจ" else "" end ) as pe_pr_name, GROUP_CONCAT(oe.pe_pr_text) as pe_pr_text, ' +' min(case when coalesce(oe.pe_gen,"-")="Y" then "Normal" when coalesce(oe.pe_gen,"-")="N" then "Abnormal" WHEN coalesce(oe.pe_gen,"-") = "-" THEN "ไม่ได้ตรวจ" else "" end ) as pe_gen_name, GROUP_CONCAT(oe.pe_gen_text) as pe_gen_text, ' +' min(case when coalesce(oe.pe_neuro,"-")="Y" then "Normal" when coalesce(oe.pe_neuro,"-")="N" then "Abnormal" WHEN coalesce(oe.pe_neuro,"-") = "-" THEN "ไม่ได้ตรวจ" else "" end ) as pe_neuro_name, GROUP_CONCAT(oe.pe_neuro_text) as pe_neuro_text, ' +' min(case when coalesce(oe.pe_ext,"-")="Y" then "Normal" when coalesce(oe.pe_ext,"-")="N" then "Abnormal" WHEN coalesce(oe.pe_ext,"-") = "-" THEN "ไม่ได้ตรวจ" else "" end ) as pe_ext_name, GROUP_CONCAT(oe.pe_ext_text) as pe_ext_text, ' //+' string_agg(replace(replace(oe.pe,chr(13)," "),chr(10),""),",") as pe ' +' GROUP_CONCAT(COALESCE(oe.pe,"")) as pe' +' from opdscreen_doctor_pe oe' +' left join doctor d on oe.doctor_code = d.code' +' where oe.vn = "'+_vn+'" ' +' and coalesce(oe.doctor_code,"") in('+doctor_list+') ' +' group by oe.doctor_code, doctor_name' ); chk_pe_img := GetSQLIntegerData('select count(*) from pe_xe_image where vn = "'+_vn+'"'); chk_pe := GetSQLIntegerData(' select count(*) from opdscreen_doctor_pe where vn = "'+_vn+'" and coalesce(doctor_code,"") in('+doctor_list+') ' +' and (pe_ga_text <> "" or pe_heent_text <> "" or pe_heart_text <> "" or pe_chest_text<> "" or pe_ab_text <> "" ' +' or pe_pv_text <> "" or pe_pr_text <> "" or pe_gen_text <>"" or pe_neuro_text <> "" or pe_ext_text <> "" or pe<>"")' ); Region_PE.Visible := (chk_pe>0) or (chk_pe_img>0); { (DBPipeLineLink1['pe_ga_text'] <> '') or (DBPipeLineLink1['pe_heent_text'] <> '') or (DBPipeLineLink1['pe_heart_text'] <> '') or (DBPipeLineLink1['pe_chest_text'] <> '') or (DBPipeLineLink1['pe_ab_text'] <> '') or (DBPipeLineLink1['pe_pv_text'] <> '') or (DBPipeLineLink1['pe_pr_text'] <> '') or (DBPipeLineLink1['pe_gen_text'] <> '') or (DBPipeLineLink1['pe_neuro_text'] <> '') or (DBPipeLineLink1['pe_ext_text'] <> '') or (DBPipeLineLink1['pe'] <> '') or (chk_pe_img>0); } // ------------------------------------------------------------------------ // // ------------------------------ HPI ------------------------------------- // ChangeDBPipeLineLink11SQL(' select concat(dd.name,(" ["||dd.licenseno||"]")) as doctor_name, GROUP_CONCAT(hpi_text) as pi' +' from patient_history_hpi hh, doctor dd ' +' where dd.code=hh.doctor_code and dd.position_id in(1,2) ' +' and hh.vn = "'+_vn+'" ' +' and coalesce(dd.code,"") in('+doctor_list+') ' +' group by doctor_name' ); // ------------------------------------------------------------------------ // // ---------------------------- Region_Dx --------------------------------- // chk_dx := GetSQLIntegerData(' select count(*) from ovstdiag where vn = "'+_vn+'" and diagtype = "1" and doctor in('+doctor_list+') ') + GetSQLIntegerData(' select count(*) from ovst_doctor_diag where vn = ("'+_vn+'") and coalesce(doctor_code,"") in('+doctor_list+') '); if chk_dx > 0 then ChangeDBPipeLineLink2SQL(' SELECT ovstdiag.hn,ovstdiag.vn,ovstdiag.doctor,ovstdiag.diagtype,ovstdiag.icd10,doctor.licenseno, ' +' concat(doctor.name," (",COALESCE(doctor.licenseno,""),") ") AS doctorname,diagtype.name AS DIAGTYPEMANE, ' +' case ' +' when ovstdiag.icd10 in ("Z21","Z206","R75","T742") then "" ' +' when ovstdiag.icd10 between "B200" and "B240" then "" ' +' when ovstdiag.icd10 between "Y0500" and "Y0599" then "" ' +' when LENGTH(ovstdiag.icd10) = 3 then i1.name ' +' when LENGTH(ovstdiag.icd10) = 4 then Concat(i1.name," ",i2.name) ' +' when LENGTH(ovstdiag.icd10) = 5 then Concat(i1.name," ",i2.name," ",i3.name) ' +' end as ICDNAME ' +' FROM ovstdiag ' +' INNER JOIN icd101 i1 ON i1.code = left(ovstdiag.icd10,3) ' +' LEFT JOIN icd101 i2 ON i2.code = left(ovstdiag.icd10,4) ' +' LEFT JOIN icd101 i3 ON i3.code = left(ovstdiag.icd10,5) ' +' LEFT JOIN doctor ON doctor.code = ovstdiag.doctor ' +' INNER JOIN diagtype ON ovstdiag.diagtype = diagtype.diagtype ' +' WHERE 1=1' +' and ovstdiag.vn = "'+_vn+'" ' +' and coalesce(ovstdiag.doctor,"") in('+doctor_list+') ' +' ' +' union ' +' ' +' SELECT ovst.hn,ovst.vn,dx.doctor_code as doctor, "9" as diagtype, "" as icd10, dc.licenseno, ' +' concat(dc.name," (",COALESCE(dc.licenseno,""),") ") AS doctorname, "Diag text" AS DIAGTYPEMANE, ' +' dx.diag_text as ICDNAME ' +' from ovst, ovst_doctor_diag dx, doctor dc ' +' where ovst.vn = dx.vn and dc.code = dx.doctor_code and ovst.vn = "'+_vn+'" ' +' and coalesce(dx.doctor_code,"") in('+doctor_list+') ' +' ORDER BY diagtype ' ) else ChangeDBPipeLineLink2SQL(' SELECT "" as hn,"" as vn,"" as doctor,"" as diagtype,"" as icd10,"" as licenseno,"" as doctorname,"" as DIAGTYPEMANE,"" as ICDNAME ' +' union all SELECT "" as hn,"" as vn,"" as doctor,"" as diagtype,"" as icd10,"" as licenseno,"" as doctorname,"" as DIAGTYPEMANE,"" as ICDNAME ' +' union all SELECT "" as hn,"" as vn,"" as doctor,"" as diagtype,"" as icd10,"" as licenseno,"" as doctorname,"" as DIAGTYPEMANE,"" as ICDNAME ' +' union all SELECT "" as hn,"" as vn,"" as doctor,"" as diagtype,"" as icd10,"" as licenseno,"" as doctorname,"" as DIAGTYPEMANE,"" as ICDNAME ' +' union all SELECT "" as hn,"" as vn,"" as doctor,"" as diagtype,"" as icd10,"" as licenseno,"" as doctorname,"" as DIAGTYPEMANE,"" as ICDNAME '); // ------------------------------------------------------------------------ // // ---------------------------- Region_Rx --------------------------------- // ChangeDBPipeLineLink3SQL(' SELECT concat("[เหตุผล "||(select right(presc_reason,1) from ovst_presc_ned where opi_guid = o.hos_guid limit 1)) as reason,' +' case when d.name_print is null then concat(d.name," ",d.strength," ",d.units) else d.name_print end as name_print, ' +' o.qty, concat(doctor.name,(" ["||doctor.licenseno||"]")) as doctor_name, ' +' case when od.usage_lock = "Y" then ' +' COALESCE(od.shortlist,concat(COALESCE(od.usage_line1,"")," ",COALESCE(od.usage_line2,"")," ",COALESCE(od.usage_line3,""))) ' +' else COALESCE(du.shortlist,concat(COALESCE(du.name1,"")," ",COALESCE(du.name2,"")," ",COALESCE(du.name3,""))) end as drugusagename ' +' FROM opitemrece o ' +' JOIN drugitems d ON d.icode = o.icode ' +' left join drugusage du on o.drugusage = du.drugusage' +' LEFT JOIN opi_dispense od ON od.hos_guid = o.hos_guid' +' LEFT JOIN doctor ON doctor.code = o.doctor ' +' WHERE VN = "'+_vn+'" ' +' and coalesce(o.doctor,"") in('+doctor_list+') ' +' '); // ------------------------------------------------------------------------ // // ---------------------------- Region_serv --------------------------------- // ChangeDBPipeLineLink13SQL(' SELECT case when i.income in("07") then " LAB" when i.income in("08") then " X-Ray" else "ค่าใช้จ่าย" end as income_name, ' +' d.name as serv_name, sum(o.qty) as qty ' +' FROM opitemrece o ' +' join income i on o.income = i.income ' +' left JOIN s_drugitems d ON d.icode = o.icode ' +' WHERE VN = "'+_vn+'" and o.icode not like "1%" ' //+' and o.doctor in('+doctor_list+') ' +' group by i.income, serv_name' +' order by income_name' ); // ------------------------------------------------------------------------ // // ------------------------- Advice --------------------------------------- // ChangeDBPipeLineLink4SQL('select concat(" ",o1.opdscreen_advice_item_name," ",COALESCE(o.advice_note,"")) as opdscreen_advice_item_name, ' +' case when position("," in d.name) > 0 then CONCAT(SUBSTR(d.name,position("," in d.name)+1,LENGTH(d.name)),SUBSTR(d.name,1,position("," in d.name)-1)) else d.name end AS doctor_name ' +' from opdscreen_advice o ' +' inner join opdscreen_advice_item o1 on o1.opdscreen_advice_item_id=o.opdscreen_advice_item_id ' +' left join doctor d on d.code = o.doctor_code ' +' where vn = "'+_vn+'" ' +' order by o.opdscreen_advice_id '); Region_Advice.Visible := DBPipeLineLink4['opdscreen_advice_item_name'] <> ''; Region_advice_fiq.Visible := DBPipeLineLink4['opdscreen_advice_item_name'] = ''; // ------------------------------------------------------------------------ // // ---------------------------- LAB --------------------------------------- // ChangeDBPipeLineLink5SQL(' SELECT lab_head.form_name,lab_head.hn,lab_head.vn,lab_head.lab_order_number, lab_head.report_time,offi.officer_name ' +' FROM lab_head ' +' LEFT OUTER JOIN lab_order ON lab_order.lab_order_number = lab_head.lab_order_number ' +' LEFT OUTER JOIN doctor on doctor.code = lab_head.doctor_code ' +' LEFT OUTER JOIN officer offi ON offi.officer_login_name = lab_head.reporter_name ' +' WHERE lab_head.vn="'+_vn+'" ' +' and lab_order.confirm = "Y" and lab_order_result <> "" ' // +' and lab_order.lab_items_code not in (select lab_items_code from lab_items ' // +' where (lower(lab_items_name) like "%hiv%" or lower(lab_items_name) like "%cd4%" ' // +' or lower(lab_items_name) like "%cd8%" or lower(lab_items_name) like "%vdrl%") ' // +' and active_status = "Y") ' +' GROUP BY lab_head.form_name,lab_head.vn,lab_head.lab_order_number, lab_head.report_time '); // ------------------------------------------------------------------------ // // ---------------------------- X-Ray ------------------------------------- // ChangeDBPipeLineLink6SQL(' SELECT xray_report.confirm_read_film,xray_report.report_doctor, '+ ' xray_report.report_text,xray_report.report_rtf,doctor.name, '+ ' xray_items.xray_items_name '+ ' FROM xray_report '+ ' LEFT OUTER JOIN xray_items ON xray_items.xray_items_code = xray_report.xray_items_code '+ ' LEFT OUTER JOIN doctor ON doctor.code = xray_report.report_doctor '+ ' WHERE xray_report.vn = "'+_vn+'" and confirm_read_film = "Y" '); // ------------------------------------------------------------------------ // // ---------------------------- Appointment ------------------------------- // ChangeDBPipeLineLink7SQL(' select o.clinic,o.oapp_id,o.doctor, u.name as app_username,' +' c.name as clinic_name, d.name as doctor_name, o.hn,o.vstdate,o.nextdate, o.perform_text, ' +' o.nexttime,o.nexttime_end,o.note,o.vn,o.depcode,o.spclty,k.department ' +' from oapp o ' +' left outer join clinic c on c.clinic=o.clinic ' +' left outer join doctor d on d.code=o.doctor' +' left outer join opduser u on o.app_user = u.loginname' +' left outer join kskdepartment k on k.depcode = o.depcode ' +' where o.vn = "'+_vn+'" and o.oapp_status_id = "1" ' +' group by o.clinic,o.oapp_id,o.doctor,c.name,d.name , u.name, ' +' o.hn,o.vstdate,o.nextdate,o.nexttime,o.note,o.vn,o.depcode,o.spclty,k.department ' +' order by o.nextdate,o.nexttime '); // ------------------------------------------------------------------------ // // ------------------------------ Dental ---------------------------------- // ChangeDBPipeLineLink9SQL(' select "ทันตกรรม" as name,d2.name as dttm_name,d1.ttcode ' +' from dtmain d1 ' +' left outer join dttm d2 on d2.code = d1.tmcode ' +' left outer join doctor d3 on d3.code = d1.doctor ' +' left outer join doctor d4 on d4.code = d1.doctor_helper ' +' where d1.vn = "'+_vn+'" ' +' and coalesce(d1.doctor,"") in('+doctor_list+') ' +'' ); // ------------------------------------------------------------------------ // // ----------------------------- Eye Screen ------------------------------- // ChangeDBPipeLineLink10SQL('select * from eye_screen where vn="'+_vn+'" '); // ------------------------------------------------------------------------ // // ----------------------------- Nutrition Consult ------------------------------- // Region_nutri.visible := GetSQLIntegerData(' select count(*)' +' from nutri_consult nc' +' left join doctor dc ON dc.code = nc.doctor_code' +' left join nutri_consult_screen ncs ON ncs.nutri_consult_id = nc.nutri_consult_id' +' left join doctor ds ON ds.code = ncs.doctor_code' +' left join nutri_consult_report nr on nc.nutri_consult_id = nr.nutri_consult_id' +' left join doctor dr ON dr.code = nr.doctor_code' +' where nc.vn = "'+_vn+'" ') > 0; // ------------------------------------------------------------------------ // ChangeDBPipeLineLink15SQL('select * from reporttemplate where name = "'+GetReportName+'"'); LnLogin.Caption := 'ผู้พิมพ์ : '+GetSQLStringData('select officer_name from officer ' +' where officer.officer_login_name = "'+GetCurrentUser+'" '); LnPrinterName.Caption := 'พิมพ์จากเครื่อง '+GetSQLStringData('select computername ' +' from onlineuser where onlineid = "'+GetOnlineID+'" ') +' วันที่พิมพ์ '+FormatThaiDate('dd mmmm yyyy เวลา hh:mm น.',CurrentDateTime); ReportVersion.Caption := GetReportName+' v.'+DBPipeLineLink15['report_version']; ReportUpdate.Caption := 'ปรับปรุงเอกสาร '+FormatThaiDate('d mmm yy',DBPipeLineLink15['last_update']); vaccine := 0; Region_vaccine.visible := GetSQLIntegerData('select count(*) from ovst_vaccine ' +' where vn = "'+_vn+'"') > 0; Memo14.text := ''; Memo14.text := GetSQLStringData('select GROUP_CONCAT(cc.cc_persist_disease) as cc_persist_disease ' +' from (select cc_persist_disease from opd_ill_history where hn = "'+_hn+'" ' +' union all ' +' select clinic.name from clinicmember ' +' join clinic on clinicmember.clinic = clinic.clinic ' +' where clinicmember.hn = "'+_hn+'") as cc '); if trim(Memo14.text) = '' then Memo14.text := ' - '; Memo16.text := ''; Memo16.text := GetSQLStringData('select GROUP_CONCAT(consult_question||"\n"||consult_reply) as consult ' +' from doctor_consult ' +' join er_regist on er_regist.vn = doctor_consult.vn ' +' where doctor_consult.vn = "'+_vn+'" '); if trim(Memo16.Text) = '' THEN Label128.Visible := FALSE; Region_PE_DENTAL.Visible := FALSE; cc_dtmain := GetSQLIntegerData('select count(*) from dtmain where vn = "'+_vn+'" '); if cc_dtmain > 0 THEN Begin Region_PE.Visible := FALSE; Region_PE_DENTAL.Visible := TRUE; Memo19.Text := ''; Memo19.Text := GetSQLStringData('select GROUP_CONCAT(note) FROM dtmain WHERE vn = "'+_vn+'" '); if trim(Memo19.Text) = '' THEN Region_PE_DENTAL.Visible := FALSE; END; end;