procedure GlobalOnCreate;
begin
if GetSQLStringData('select * from opdscreen_bp where vn="'+DBPipeline['vn']+'" order by screen_time')='' then
ChangeDBPipeLineLink1SQL('select bps,bpd from opdscreen where vn="'+DBPipeline['vn']+'"')
else
ChangeDBPipeLineLink1SQL('select * from opdscreen_bp where vn="'+DBPipeline['vn']+'" order by screen_time');
if GetSQLStringData('select vn from opitemrece where vn="'+DBPipeline['vn']+'"')='' then
ChangeDBPipeLineLink3SQL('select concat(if((d.name is null or d.name = ""),"-",d.name))," ", '+
'if(d.strength is null,"",d.strength)," # ",if(o.qty is null,"",o.qty)) '+
'as drug_name,du.shortlist '+
'from opitemrece o '+
'left outer join drugitems d on d.icode = o.icode '+
'left outer join drugusage du on du.drugusage = o.drugusage '+
'where o.an="'+DBPipeline['an']+'" and o.sub_type="1"')
else
ChangeDBPipeLineLink3SQL('ChangeDBPipeLineLink7SQL(sql);elect concat(if((d.name is null or d.name = ""),"-",d.name)," ", '+
'if(d.strength is null,"",d.strength)," # ",if(o.qty is null,"",o.qty)) '+
'as drug_name,du.shortlist '+
'from opitemrece o '+
'left outer join drugitems d on d.icode = o.icode '+
'left outer join drugusage du on du.drugusage = o.drugusage '+
'where o.vn="'+DBPipeline['vn']+'" and o.sub_type="1"');
if GetSQLStringData('select vn from opitemrece where vn="'+DBPipeline['vn']+'"')='' then
ChangeDBPipeLineLink4SQL('select concat("(",lsg.lab_items_sub_group_name,")") as group_name, '+
'lo.lab_items_name_ref,lo.lab_order_result,li.lab_items_unit, '+
'concat(if(lo.lab_items_name_ref is null,"",lo.lab_items_name_ref)," = ", '+
'if((lo.lab_order_result = "" or lo.lab_order_result is null),"รอผล",lo.lab_order_result)," ", '+
'if(li.lab_items_unit is null,"",li.lab_items_unit)) as lab_report '+
'from lab_head lh '+
'left outer join lab_order lo on lo.lab_order_number = lh.lab_order_number '+
'left outer join lab_items li on li.lab_items_code = lo.lab_items_code '+
'left outer join lab_items_sub_group lsg on lsg.lab_items_sub_group_code = lo.lab_items_sub_group_code '+
'where lh.vn="'+DBPipeline['an']+'" order by lo.lab_items_sub_group_code,li.display_order ')
else
ChangeDBPipeLineLink4SQL('select concat("(",lsg.lab_items_sub_group_name,")") as group_name, '+
'lo.lab_items_name_ref,lo.lab_order_result,li.lab_items_unit, '+
'concat(if(lo.lab_items_name_ref is null,"",lo.lab_items_name_ref)," = ", '+
'if((lo.lab_order_result = "" or lo.lab_order_result is null),"รอผล",lo.lab_order_result)," ", '+
'if(li.lab_items_unit is null,"",li.lab_items_unit)) as lab_report '+
'from lab_head lh '+
'left outer join lab_order lo on lo.lab_order_number = lh.lab_order_number '+
'left outer join lab_items li on li.lab_items_code = lo.lab_items_code '+
'left outer join lab_items_sub_group lsg on lsg.lab_items_sub_group_code = lo.lab_items_sub_group_code '+
'where lh.vn="'+DBPipeline['vn']+'" order by lo.lab_items_sub_group_code,li.display_order ');
ChangeDBPipeLineLink2SQL('select a.*,b.name as dt_name,c.name as doc_name,concat(tcount," ซี่")as t_name,concat(scount," ด้าน")as s_name, '+
'concat(rcount," ราก")as r_name,concat(qty_count," ชิ้น")as q_name,a.fee,a.ttcode,a.begin_time,a.end_time '+
'from dtmain a '+
'left outer join dttm b on a.tmcode=b.code '+
'left outer join doctor c on a.doctor=c.code '+
'where vn="'+DBPipeline['vn']+'"');
ChangeDBPipeLineLink5SQL('select * from xray_head where vn="'+DBPipeline['vn']+'"');
ChangeDBPipeLineLink6SQL('select e1.vn as vn,e1.er_oper_code as code,e.name,e.icd9cm,e.price, '+
'concat("[ ER ] ",if((e.name = "" or e.name is null)," - ",e.name)," # ", '+
'if((e1.oper_qty = "" or e1.oper_qty is null),"",e1.oper_qty)," # ", '+
'if((d.name = "" or d.name is null),"",d.name)) as o_name '+
'from er_oper_code e '+
'left outer join er_regist_oper e1 on e1.er_oper_code = e.er_oper_code '+
'left outer join doctor d on d.code = e1.doctor '+
'where e1.vn="'+DBPipeline['vn']+'" '+
'union '+
'select d1.vn as vn,d1.er_oper_code as code,e.name,e.icd9cm,e.price, '+
'concat("[OPD] ",if((e.name = "" or e.name is null)," - ",e.name)," # ", '+
'if((d.name = "" or d.name is null),"",d.name)) as o_name '+
'from er_oper_code e '+
'left outer join doctor_operation d1 on d1.er_oper_code = e.er_oper_code '+
'left outer join doctor d on d.code = d1.doctor '+
'where d1.vn="'+DBPipeline['vn']+'" ');
ChangeDBPipeLineLink7SQL('select a.*,b.name as ward_name,c.name as room_name from opd_observe a '+
' left outer join ward b on a.ward=b.ward '+
' left outer join roomno c on a.roomno=c.roomno '+
' where vn="'+DBPipeline['vn']+'"');
end;
หาเจอแล้วครับแก้เองก็ได้ครับ