CREATE OR REPLACE PROCEDURE SP_YS_CZJZCL_MZDZBL( PID0000 in number , --ID0000 PJZYS00 in number , --接诊医生 PJZKS00 in number , --接诊科室 POPFLAG in number , --操作参数 2:错诊 4:叫诊 1：应诊,3:诊结,5:续诊，6：等待 PCZY000 in number , --操作员id号 PGHHZCK in number default 0, --候诊窗口 PZXJG00 out varchar2 , --执行结果提示 PZXBZ00 out number , --传出参数说明 PCOMMIT in varchar2 default 'N', --是否提交 PECQH00 in varchar2 default '0', PASXJZ0 in varchar2 default 'N', --是否按顺序就诊Y是N否 PBZ0000 in varchar2 default '', --备注 PHZID00 out number ) as -- MODIFICATION HISTORY -- Person Date Comments -- ruanbh 2018.08.07 create 操作叫诊处理 -- ruanbh 2020.03.02 增加参数PSFJZ00 MZYSSJ5-20200228-001 -- ruanbh 2020.03.02 移植SP_YS_CZJZCL内容 MZYSSJ5-20200409-001 -- ruanbh 2020.05.06 排班时间外不限制顺序就诊,叫号时JHCS00+1 MZYSSJ5-20200429-001 -- ruanbh 2020.05.27 增加参数PBZ0000 MZYSSJ5-20200527-002 --huangzw 2021.11.01 1、只能在叫诊状态下应诊、点续诊时直接叫诊 for MZYSSJ5-20211021-001 VTSXX00 varchar2(200); --异常信息 V_ERROR0 VARCHAR2(200); I_YGBH00 BM_YGBM00.YGBH00%TYPE; --员工编号 VID0000 SF_HZBR00.ID0000%TYPE; VMINSXH YS_KSJHSX.SXH000%TYPE; --医生的叫号顺序。 VYSPBID SF_YSGHHB.GHHBID%TYPE; --号表id(科室排班) Vsysdate date; --日期变量 Vcounter number(10); --计数器变量 Vparams varchar2(255); --参数变量 VYcws00 number; --错诊病人往后延迟位数 VLastBR sf_hzbr00%rowtype; --上一个病人队列序号 VsoHzbr sf_hzbr00%rowtype; --源候诊队列 Verrmsg varchar2(255); --错误提示变量 Ecustom exception; --错误变量 Esxbd exception; --科室排班得等所有医生都取过才轮到该医生叫号，顺序不对 EJZJS exception; --排班已经叫诊结束 Vyzbrzjjz xt_xtcs00.value0%type; --已诊病人是否允许直接诊结 V_SFXZASXJZ xt_xtcs00.value0%type; --取出医生候诊就诊时，是否限制按顺序就诊 只限制首诊的HZZT00=1病人 v_pblx00 bm_ckbm00.PBLX00%type; --排班类型 '0'医生排班 '1'医生排班 V_COUNT number; i_YXJHGS number(5,0); --允许叫号个数 YS_JZZTSZ varchar2(1); --医生就诊状态 1就诊 0 离诊 v_SFCYYGYS char(1); --是否参与验光的医生 v_count99 number(5); MZHS_XZSBRZTSFGWHZZT XT_XTCS00.VALUE0%type; ls_JKSQL00 varchar2(1000); YS_SFJZDGBR varchar2(10); YS_KLXGZZDRS varchar2(10); V_HZXH00 varchar2(100); YS_ZJGHBJZXZ varchar2(10); MZHS_XZSFCXSCHZXH varchar2(10); VGHID00 SF_BRXXB0.GHID00%type; LS_SFDCQRCXS varchar2(10); MZHS_XZCRXZDL varchar2(10); v_CDXZXH number(5); CDXZCNT number(5); v_CDDLXH number(10); VexecSQL varchar2(400); VZT0000 varchar2(10); LS_IP0000 varchar2(20); ls_ckbh00 bm_ckbm00.ckmc00%type; Vjzztyz xt_xtcs00.value0%type; --病人处于叫诊状态，才能做应诊操作 YS_SFJYJZBRGZ xt_xtcs00.value0%type;--点击叫诊时，是否将原叫诊病人状态变为候诊 -- v_KSPBID SF_YSGHHB.GHHBID%type; --科室医生排班id --取3个病人 Cursor C_GHBR00 Is select * from SF_HZBR00 where ghys00=Vsohzbr.ghys00 and jzks00=Vsohzbr.jzks00 and HZZT00<>'Z' and hzqsrq=to_char(sysdate,'yyyymmdd')and dlxh00>Vsohzbr.Dlxh00 and RowNum<=VYcws00 order by DLXH00; begin --取参数 begin select to_number(trim(VALUE0)) into i_YXJHGS from XT_XTCS00 where NAME00='YS_YXJHGS'; EXCEPTION WHEN others then i_YXJHGS:=1; END; if i_YXJHGS=0 then i_YXJHGS:=1; end if; begin select nvl(max(trim(VALUE0)),'N') into MZHS_XZSBRZTSFGWHZZT from XT_XTCS00 where NAME00='MZHS_XZSBRZTSFGWHZZT'; exception when others then MZHS_XZSBRZTSFGWHZZT:='N'; end; begin select nvl(max(trim(VALUE0)),'N') into YS_SFJZDGBR from XT_XTCS00 where NAME00='YS_SFJZDGBR'; exception when others then YS_SFJZDGBR:='N'; end; begin select nvl(max(trim(VALUE0)),'N') into YS_SFJYJZBRGZ from XT_XTCS00 where NAME00='YS_SFJYJZBRGZ'; exception when others then YS_SFJYJZBRGZ:='N'; end; begin select to_number(nvl(max(trim(VALUE0)),'0')) into YS_KLXGZZDRS from XT_XTCS00 where NAME00='YS_KLXGZZDRS'; exception when others then YS_KLXGZZDRS:='0'; end; begin select nvl(max(trim(VALUE0)),'N') into YS_ZJGHBJZXZ from XT_XTCS00 where NAME00='YS_ZJGHBJZXZ'; if YS_ZJGHBJZXZ<>'Y' then YS_ZJGHBJZXZ:='N'; end if; exception when others then YS_ZJGHBJZXZ:='N'; end; --续诊是否重新生成HZXH00 begin select nvl(max(trim(VALUE0)),'N') into MZHS_XZSFCXSCHZXH from XT_XTCS00 where NAME00='MZHS_XZSFCXSCHZXH'; exception when others then MZHS_XZSFCXSCHZXH:='N'; end; begin SELECT YGBH00 INTO I_YGBH00 FROM XT_YHZB00 WHERE TRIM(ZBDM00)=(SELECT TRIM(VALUE0) FROM XT_XTCS00 WHERE NAME00='YS_KJYGQXZB') AND YGBH00=pjzys00; EXCEPTION when others then I_YGBH00:=0; END; begin select nvl(trim(VALUE0),'N') into MZHS_XZCRXZDL from XT_XTCS00 where NAME00='MZHS_XZCRXZDL'; exception when others then MZHS_XZCRXZDL:='N'; end; select count(*) into Vcounter from XT_XTCS00 where name00='YS_CYYGYS' and instr(','||trim(VALUE0)||',', ','||to_char(pjzys00)||',' )>0; if Vcounter>0 then v_SFCYYGYS:='Y'; else v_SFCYYGYS:='N'; end if; VYSPBID:=0; --科室排班自动叫诊算法处理 if (pghhzck>0) then begin select GHHBID into VYSPBID from SF_YSGHHB where GHMZSJ=to_char(sysdate,'YYYYMMDD') and GHKSBH=pjzks00 and PBLX00='1' and rownum=1; exception when others then v_SFCYYGYS:='N'; end; end if; begin select nvl(trim(VALUE0),'N') into Vjzztyz from XT_XTCS00 where NAME00='YS_BRJZZTCNZYZCZ'; exception when others then Vjzztyz:='N'; end; --若医生存在自己的队列 /**/ if (pghhzck>0) and (v_SFCYYGYS='Y') then--科室排班医生进行医生就诊状态设置 begin select trim(VALUE0) into YS_JZZTSZ from XT_YHCS00 where NAME00='YS_JZZTSZ' and YGBH00=pjzys00; exception when others then YS_JZZTSZ:='0'; end; if YS_JZZTSZ='0' then update XT_YHCS00 set value0='1' where NAME00='YS_JZZTSZ' and YGBH00=pjzys00; if SQL%NOTFOUND then insert into XT_YHCS00(YGBH00,NAME00,VALUE0,BZ0000) select pjzys00,'YS_JZZTSZ','1','医生就诊状态 1就诊 0离诊' from dual; end if; end if; commit; end if; if (pghhzck>0) and (popflag=4) and (v_SFCYYGYS='Y') then Vcounter:=0; if nvl(pid0000,0)>0 then select count(*) into Vcounter from sf_hzbr00 where ID0000=pid0000 and hzzt00='4'; end if; if Vcounter>0 then --补叫 update sf_hzbr00 set BJBZ00='1' where ID0000=pid0000 and hzzt00='4'; if pcommit='Y' then commit; end if; return; end if; select count(*) into Vcounter from sf_hzbr00 where hzzt00 in ('4','5','1') and ghys00=pjzys00 and jzks00=pjzks00 and hzqsrq=to_char(sysdate,'yyyymmdd') and YSPBID=VYSPBID and (HZXH00>0 or YS_ZJGHBJZXZ='N'); if Vcounter>=i_YXJHGS then Verrmsg:='您已经有科室大队列的'||to_char(Vcounter)||'个病人就诊中,请诊结完,再叫号！'; Raise Ecustom; end if; --如果轮到该医生 begin select id0000 into vid0000 from (select ID0000 from vw_ys_hzbr00 where JZKS00=pjzks00 and ( JZYS00=0 or JZYS00=pJZYS00) and PBLX00='1' and DLXH00>0 and HZZT00 in ('0','2') order by DLXH00) where rownum=1; exception when others then vid0000:=0; end; --没有取到号 则判断是否已经有等待队列，没有插入记录 if vid0000=0 then -- 判断是否已经存在等待记录 select count(*) into V_COUNT from YS_KSJHDL where CZRQ00=to_char(sysdate,'YYYYMMDD') and GHKSBH=pjzks00 and YGBH00=pJZYS00 and SFYX00='1' and BRID00=0; if V_COUNT=0 then insert into YS_KSJHDL(JLID00,CZRQ00,CZSJ00,GHKSBH,GHHBID,YGBH00,HZXH00,BRID00,GHHZCK,SFYX00,ID0000) select SQ_YS_KSJHDL_JLID00.nextVal,to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS'), pjzks00,VYSPBID,pJZYS00,0,0,pghhzck,'1',0 from dual; commit; raise EJZJS; else raise EJZJS; end if; else select * into Vsohzbr from sf_hzbr00 where id0000=vid0000; --删除等待记录 delete from YS_KSJHDL where CZRQ00=to_char(sysdate,'YYYYMMDD') and GHKSBH=pjzks00 and YGBH00=pJZYS00 and SFYX00='1' and BRID00=0; insert into YS_KSJHDL(JLID00,CZRQ00,CZSJ00,GHKSBH,GHHBID,YGBH00,HZXH00,BRID00,GHHZCK,SFYX00,ID0000) select SQ_YS_KSJHDL_JLID00.nextVal,to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS'), pjzks00,Vsohzbr.YSPBID,pJZYS00,Vsohzbr.HZXH00,Vsohzbr.BRID00,pghhzck,'1',vid0000 from dual; commit; end if; if Vsohzbr.SFXZ00='0' then--首诊 if Vsohzbr.HZZT00 not in ('0','2') then Verrmsg:='病人候诊状态已经发生改变,处于"'||Verrmsg||'"状态，请刷新后再试'; Raise Ecustom; end if; end if; --叫号处理 Update SF_HZBR00 set hzzt00='4',JZRQ00=to_char(sysdate,'yyyymmdd'), JZSJ00=to_char(sysdate,'hh24:mi:ss') where id0000=Vid0000; if sql%notfound then Verrmsg:='叫诊时发生错误，请刷新后重试'; Raise Ecustom; end if; update SF_BRXXB0 SET JZYS00=pjzys00,JZYSXM=(Select ZWXM00 FROM BM_YGBM00 WHERE YGBH00=pjzys00) WHERE GHH000=Vsohzbr.GHH000; --修改 update SF_HZBR00 SET ghys00=pjzys00,ghysxm=(select ZWXM00 from BM_YGBM00 WHERE YGBH00=pjzys00 ),GHHZCK=pghhzck where id0000=Vid0000; else --除科室排班叫诊以外的处理 --2004.03.01 Lindj:初诊-〉复诊 VYcws00:=0; Vsysdate:=sysdate;--'0'候诊'1'应诊'2'错诊'3'诊结'4'叫诊'5'续诊 Vparams :='SP_YS_CZJZCL_MZDZBL('||nvl(to_char(Pid0000),'null')||','||nvl(to_char(Pjzys00),'null')||','|| nvl(to_char(Pjzks00),'null')||','||nvl(to_char(popflag),'null')||','||nvl(to_char(Pczy000),'null')||',pzxjg00,pzxbz00,'''||Pcommit||''')'; -- if (pghhzck>0) and (popflag=4) then -- Verrmsg:='请直接应诊，叫诊无效'; -- Raise Ecustom; -- end if; if nvl(pid0000,0)=0 then Verrmsg:='没有指定候诊病人'; Raise Ecustom; end if; --SELECT NVL(MAX(VALUE0),'N') INTO Vyzbrzjjz FROM XT_XTCS00 WHERE NAME00='YS_YZBRZJJZ'; begin SELECT NVL(MAX(VALUE0),'N') INTO Vyzbrzjjz FROM XT_XTCS00 WHERE NAME00='YS_YZBRZJJZ'; EXCEPTION WHEN OTHERS THEN Vyzbrzjjz:='N'; END; if nvl(pid0000,0)=0 then Verrmsg:='没有指定候诊病人'; Raise Ecustom; end if; select nvl(max(trim(VALUE0)),'N') into LS_SFDCQRCXS from XT_XTCS00 where NAME00='YS_SFDCQRCXS'; select * into Vsohzbr from sf_hzbr00 where id0000=pid0000 ; --select count(*) into V_COUNT from BM_TYZD00 where ZDMC00='叫号病人必须按顺序就诊的科室' and instr(','||MC0000||',',','||pjzks00||',')>0; if PASXJZ0='Y' and Vsohzbr.hzzt00 in('0','4') then select count(*) INTO V_COUNT from sf_hzbr00 a where YSPBID=Vsohzbr.YSPBID AND (dlxh000 )) and exists(select 1 from SF_YSGHHB X where X.GHHBID=A.YSPBID and X.GHMZSJ||X.GHQSSJ <= to_char(sysdate,'yyyymmddhh24:mi:ss') and X.GHMZSJ||X.GHJSSJ>= to_char(sysdate,'yyyymmddhh24:mi:ss')) ; --2015.09.07 加叫诊状态 for MZYS-20150821-002 if V_COUNT>0 then Verrmsg:='请按顺序就诊'; Raise Ecustom; end if; end if; --以下这句话会导致oracle9对象死锁 --select decode(Vsohzbr.hzzt00,'0','候诊','1','应诊','2','错诊','3','诊疗结束','4','叫诊') into Verrmsg from dual where rownum=1; --改用 if Vsohzbr.hzzt00='0' then Verrmsg:='候诊'; elsif Vsohzbr.hzzt00='1' then Verrmsg:='应诊'; elsif Vsohzbr.hzzt00='2' then Verrmsg:='错诊'; elsif Vsohzbr.hzzt00='3' then Verrmsg:='诊疗结束'; elsif Vsohzbr.hzzt00='4' then Verrmsg:='叫诊'; elsif Vsohzbr.hzzt00='5' then Verrmsg:='续诊'; elsif Vsohzbr.hzzt00='6' then Verrmsg:='等待'; end if; --if Vsohzbr.ghys00<>pjzys00 then -- Verrmsg:='病人的挂号医生已经发生改变，请刷新后再试'; -- Raise Ecustom; --end if; --if Vsohzbr.jzks00<>pjzks00 then -- Verrmsg:='病人的挂号科室已经发生改变，请刷新后再试'; -- Raise Ecustom; --end if; if popflag=2 then--错诊 if to_number(YS_KLXGZZDRS)>0 then --MZYS-20160118-001 V_COUNT:=0; V_HZXH00:=''; for c_gzbr in (select HZXH00,ID0000,HZZT00,DLXH00 from SF_HZBR00 WHERE YSPBID=Vsohzbr.YSPBID AND (HZXH00 BETWEEN Vsohzbr.HZXH00-10 AND Vsohzbr.HZXH00+10) ORDER BY DLXH00) loop if c_gzbr.HZZT00='2' or c_gzbr.ID0000=Vsohzbr.ID0000 then V_COUNT:=V_COUNT+1 ; V_HZXH00:=V_HZXH00||','||c_gzbr.HZXH00; else V_COUNT:=0; V_HZXH00:=''; end if; if V_COUNT>to_number(YS_KLXGZZDRS) then if instr(V_HZXH00||',',','||Vsohzbr.HZXH00||',')>0 then Verrmsg:='您已经连续过诊超过"'||YS_KLXGZZDRS||'人'; Raise Ecustom; -- else -- V_COUNT:=0; -- V_HZXH00:=''; end if; end if; end loop; end if; if Vsohzbr.sfxz00='0' then--首诊病人 if Vsohzbr.hzzt00 not in ('0','4','2') then Verrmsg:='病人的候诊状态已经发生改变,处于"'||Verrmsg||'"状态请刷新后再试'; Raise Ecustom; end if; if (Vsohzbr.GHYS00<>pjzys00) and (VYSPBID>0) and (Vsohzbr.GHYS00>0) then-- 科室排班不能从叫号直接到诊结 Verrmsg:=Vsohzbr.HZXHMC||'号已经被其他医生叫走，您不能过诊！'; Raise Ecustom; end if; if (VYSPBID>0) and (Vsohzbr.GHYS00=0) then-- 科室排班不能从叫号直接到诊结 Verrmsg:='不能直接过诊，请先叫诊'; Raise Ecustom; end if; VLastBr:=Vsohzbr; For HZBR in C_GHBR00 Loop --VCurrXH:=HZBR.DLXH00; if Vsohzbr.YSPBID=HZBR.YSPBID then Update SF_HZBR00 set DLXH00=VLastBr.DLXH00,HZQSRQ=VLastBr.HZQSRQ, HZQSSJ=VLastBr.HZQSSJ,HZJZRQ=VLastbr.HZJZRQ, HZJZSJ=VLastBr.HZJZSJ where ID0000=HZBR.ID0000; end if; VLastBr:=HZBR; end Loop; Update SF_HZBR00 set DLXH00=VLastBr.DLXH00,HZQSRQ=VLastBr.HZQSRQ, HZQSSJ=VLastBr.HZQSSJ,HZJZRQ=VLastbr.HZJZRQ, HZJZSJ=VLastBr.HZJZSJ,HZZT00='2' where ID0000=Vsohzbr.ID0000; else--续诊病人，只改状态 Update SF_HZBR00 set HZZT00='2' where ID0000=Vsohzbr.ID0000; end if; --科室排班过诊处理 if (pghhzck>0) and (v_SFCYYGYS='Y') then begin select PBLX00 into v_pblx00 from SF_YSGHHB where GHHBID=Vsohzbr.YSPBID;--Vsohzbr.YSPBID; exception when others then v_pblx00:='0'; end; if v_PBLX00='1' then --科室排班过诊时，将改序号改为未指定医生,同时修改医生队列为无效 update SF_BRXXB0 SET JZYS00=0,JZYSXM='' WHERE GHH000=(Select GHH000 FROM SF_HZBR00 WHERE ID0000=pid0000); update SF_HZBR00 SET ghys00=0,ghysxm='',GHHZCK=0 where id0000=pid0000; update YS_KSJHDL set SFYX00='0' where ID0000=pid0000 and SFYX00='1'; end if; end if; elsif popflag=4 then--叫诊 if Vsohzbr.SFXZ00='0' then--首诊 if Vsohzbr.HZZT00 not in ('0','2','6') then Verrmsg:='病人候诊状态已经发生改变,处于"'||Verrmsg||'"状态，请刷新后再试'; Raise Ecustom; end if; --2003.40.04广州不要求约束 --select count(*) into Vcounter from VW_YS_SZHZBR --where hzzt00 in ('0','2') and ghys00=pjzys00 and jzks00=pjzks00 and -- hzqsrq=to_char(sysdate,'yyyymmdd') and dlxh000 then -- Verrmsg:='只能叫诊候诊队列的第一个病人'; -- Raise Ecustom; --end if; end if; if YS_SFJYJZBRGZ='Y' then select count(*) into Vcounter from SF_HZBR00 where hzzt00='4' and ghys00=pjzys00 and jzks00=pjzks00 and hzqsrq=to_char(sysdate,'yyyymmdd'); if Vcounter>0 then update SF_HZBR00 a set hzzt00='0' where hzzt00='4'and YSPBID=Vsohzbr.YSPBID and GHH000<>Vsohzbr.GHH000 and a.ghys00=pjzys00 and a.jzks00=pjzks00 and a.hzqsrq=to_char(sysdate,'yyyymmdd'); end if; end if; if YS_SFJZDGBR<>'Y' then select count(*) into Vcounter from VW_YS_SZHZBR where hzzt00='4' and ghys00=pjzys00 and jzks00=pjzks00 and hzqsrq=to_char(sysdate,'yyyymmdd'); if Vcounter>0 then Verrmsg:='已经有病人处于"叫诊"状态，请刷新后重试'; Raise Ecustom; end if; end if; Update SF_HZBR00 set hzzt00='4',JZRQ00=to_char(sysdate,'yyyymmdd'), JZSJ00=to_char(sysdate,'hh24:mi:ss') where id0000=Pid0000; if sql%notfound then Verrmsg:='叫诊时发生错误，请刷新后重试'; Raise Ecustom; end if; if pghhzck>0 then update SF_BRXXB0 SET JZYS00=pjzys00,JZYSXM=(Select ZWXM00 FROM BM_YGBM00 WHERE YGBH00=pjzys00) WHERE GHH000=Vsohzbr.GHH000;--(Select GHH000 FROM SF_HZBR00 WHERE ID0000=Vsohzbr.ID0000); update SF_HZBR00 SET ghys00=pjzys00,ghysxm=(select ZWXM00 from BM_YGBM00 WHERE YGBH00=pjzys00 ) where id0000=Pid0000; --update SF_HZBR00 set GHHZCK=pghhzck where id0000=Pid0000; end if; select count(*) into v_count99 from SF_HZBR00 where id0000=Pid0000 and exists(select 1 from XT_XTCS00 where NAME00='YS_SFQYZHPDJK' and VALUE0='Y'); if v_count99>0 then ls_JKSQL00:='insert into SD_ZHPD.temp_call_info(visit_no,visit_date,pat_id,pat_name,sex,age,identity,dept_code,visit_dept,clinic_type,' ||'doctor_code,visit_doctor,serial_no,pref_status,pref_name,reg_time,call_time,call_doctor,room_code,' ||'room_name,fztbh0,fztmc0,READ_FLAG,CZY000,CZRQ00)' ||' SELECT GHH000,HZQSRQ,brid00,brxm00,brxb00,brcsrq,brzjbh,jzks00,jzksmc,GHLBMC,ghys00,ghysxm,hzxh00,pref_status,' ||' pref_name,hzqssj,call_time,call_doctor,GHHZCK,ZSMC00,fztbh0,fztmc0,NULL,'||Pczy000||',to_char(sysdate,''yyyymmdd'') ' ||'FROM VW_ZHJK_HZBR00 WHERE id0000='||Pid0000; execute immediate ls_JKSQL00; end if; update SF_HZBR00 set JHCS00=nvl(JHCS00,0)+1 where ID0000=Pid0000; elsif popflag=1 then--应诊 if (Vsohzbr.hzzt00 ='3') and (Vyzbrzjjz='N') then--诊结的病人不能应诊，其余均可 Verrmsg:='病人的候诊状态已经发生改变,处于"'||Verrmsg||'"状态请刷新后再试'; Raise Ecustom; end if; if (Vsohzbr.GHYS00=0) and (VYSPBID>0) then Verrmsg:='请先叫诊后，再进行应诊！'; Raise Ecustom; end if; if (Vsohzbr.Hzzt00<>'4' and Vsohzbr.Hzzt00<>'1') and (Vjzztyz='Y') then Verrmsg:='请先叫诊，然后再进行应诊！'; Raise Ecustom; end if; if (Vsohzbr.GHYS00<>pjzys00) and (VYSPBID>0) and (Vsohzbr.GHYS00<>0) then Verrmsg:='该号源已经被其他医生叫走！'; Raise Ecustom; end if; update SF_BRXXB0 SET JZYS00=pjzys00,JZYSXM=(Select ZWXM00 FROM BM_YGBM00 WHERE YGBH00=pjzys00) WHERE GHH000=Vsohzbr.GHH000;--(Select GHH000 FROM SF_HZBR00 WHERE ID0000=Vsohzbr.ID0000); --变为复诊病历 --Update YS_BRBQB0 set sfcz00='0' where GHH000=Vsohzbr.GHH000; select count(*) into Vcounter from XT_XTCS00 where NAME00='YS_YZSSFZDZJZQYZBR' and VALUE0='Y'; if nvl(Vsohzbr.YSPBID,0)>0 and Vcounter>0 then --MZYS-20151116-002 update SF_HZBR00 set HZZT00='3',ZJRQ00=to_char(sysdate,'yyyymmdd'), ZJSJ00=to_char(sysdate,'hh24:mi:ss') where YSPBID=Vsohzbr.YSPBID and HZZT00='1' and GHH000<>Vsohzbr.GHH000 and ID0000<>Vsohzbr.ID0000; end if; Update SF_HZBR00 set HZZT00='1',YZRQ00=to_char(sysdate,'yyyymmdd'), YZSJ00=to_char(sysdate,'hh24:mi:ss'),GHYS00=pjzys00,JZKS00=pjzks00 where ID0000=Vsohzbr.ID0000; --作废之前过期的预约病人 if nvl(Vsohzbr.YSPBID,0)>0 then begin SP_YY_ZDZFCDBRYY(Vsohzbr.YSPBID,Vsohzbr.HZXH00); exception when others then V_COUNT:=1; end; end if; elsif popflag=3 then--诊结 if Vsohzbr.hzzt00 ='3' then--诊结的病人不能应诊，其余均可 Verrmsg:='病人的候诊状态已经发生改变,处于"'||Verrmsg||'"状态请刷新后再试'; Raise Ecustom; end if; /*wzy if (Vsohzbr.GHYS00=0) and (VYSPBID>0) then-- 科室排班不能从叫号直接到诊结 Verrmsg:=Vsohzbr.HZXHMC||'号,还没有就诊过不能诊结！'; Raise Ecustom; end if; */ if (Vsohzbr.GHYS00<>pjzys00) and (VYSPBID>0) and (Vsohzbr.GHYS00>0) then-- 科室排班不能从叫号直接到诊结 Verrmsg:=Vsohzbr.HZXHMC||'号已经被其他医生叫走，您不能诊结！'; Raise Ecustom; end if; /*if (Vsohzbr.hzzt00='4') and (VYSPBID>0) then-- 科室排班不能从叫号直接到诊结 Verrmsg:=Vsohzbr.HZXHMC||'号,还没有就诊过不能诊结！'; Raise Ecustom; end if; */ Update SF_HZBR00 set HZZT00='3',ZJRQ00=to_char(sysdate,'yyyymmdd'), ZJSJ00=to_char(sysdate,'hh24:mi:ss') where ID0000=Vsohzbr.ID0000; elsif popflag=5 then--续诊 if Vsohzbr.hzzt00 not in ('1','2','3','4') then--至少叫诊过的病人才能续诊 Verrmsg:='病人的候诊状态已经发生改变,处于"'||Verrmsg||'"状态请刷新后再试'; Raise Ecustom; end if; if (Vsohzbr.GHYS00<>pjzys00) and (VYSPBID>0) and (Vsohzbr.GHYS00>0) then-- 科室排班不能从叫号直接到诊结 Verrmsg:=Vsohzbr.HZXHMC||'号已经被其他医生叫走，您不能续诊！'; Raise Ecustom; end if; if (VYSPBID>0) and (Vsohzbr.GHYS00=0) then-- 科室排班不能从叫号直接到诊结 Verrmsg:='不能直接续诊，请先叫诊'; Raise Ecustom; end if; if Vsohzbr.hzzt00 in ('1','4') then Update SF_HZBR00 set HZZT00='3',ZJRQ00=to_char(sysdate,'yyyymmdd'), ZJSJ00=to_char(sysdate,'hh24:mi:ss') where ID0000=Vsohzbr.ID0000; end if; select SQ_SF_HZBR00_ID0000.Nextval into PHZID00 from dual; if MZHS_XZSFCXSCHZXH='Y' and PECQH00='1' then insert into sf_hzbr00 (ID0000,BRID00,GHH000,GHKS00,JZKS00,JZKSMC,GHYS00,GHYSXM,GHLB00, HZLB00,HZLBMC,HZXH00,DLXH00,HZXHMC, DJRQ00,DJSJ00,HZQSRQ,HZQSSJ,HZJZRQ,HZJZSJ,HZZT00,CZY000,SFXZ00,JZZS00,YSPBID,BZ0000) select PHZID00,a.* from (select BRID00,GHH000,GHKS00,pjzks00,JZKSMC,pjzys00,GHYSXM,GHLB00, HZLB00,HZLBMC, nvl((select max(HZXH00) from SF_HZBR00 where YSPBID=Vsohzbr.YSPBID and SFXZ00='1' and BZ0000='二次排队'),2000)+1 HZXH00, null DLXH00, HZXHMC, to_char(sysdate,'yyyymmdd'),to_char(sysdate,'hh24:mi:ss'), HZQSRQ,HZQSSJ,HZJZRQ,HZJZSJ,decode(trim(MZHS_XZSBRZTSFGWHZZT),'Y','0','5'), pczy000,'1',JZZS00,YSPBID,'二次排队' from sf_hzbr00 a where ghh000=Vsohzbr.GHH000 --and sfxz00='0' and HZZT00<>'Z' order by ID0000 desc) a where rownum=1 ; else if MZHS_XZCRXZDL='Y'then begin select min(HZXH00) into v_CDXZXH from SF_HZBR00 where YSPBID=Vsohzbr.YSPBID and HZZT00='0' and SFXZ00<>'1'; exception when others then v_CDXZXH:=99999; end; select count(1) into CDXZCNT from SF_HZBR00 where YSPBID=Vsohzbr.YSPBID and SFXZ00='1' and HZXH00=v_CDXZXH; while (CDXZCNT=1) LOOP begin v_CDXZXH:=v_CDXZXH+1; select count(1) into CDXZCNT from SF_HZBR00 where YSPBID=Vsohzbr.YSPBID and SFXZ00='1' and HZXH00=v_CDXZXH; end; end LOOP; begin select nvl(DLXH00,0)+1 into v_CDDLXH from SF_HZBR00 where YSPBID=Vsohzbr.YSPBID and SFXZ00='0' and HZXH00=v_CDXZXH; exception when others then v_CDDLXH:=99999; end; if v_CDXZXH is null then begin select max(HZXH00)+1,max(DLXH00)+1 into v_CDXZXH,v_CDDLXH from SF_HZBR00 where YSPBID=Vsohzbr.YSPBID; exception when others then v_CDXZXH:=99999;v_CDDLXH:=99999; end; end if ; insert into sf_hzbr00 (ID0000,BRID00,GHH000,GHKS00,JZKS00,JZKSMC,GHYS00,GHYSXM,GHLB00, HZLB00,HZLBMC,HZXH00,DLXH00,HZXHMC, DJRQ00,DJSJ00,HZQSRQ,HZQSSJ,HZJZRQ,HZJZSJ,HZZT00,CZY000,SFXZ00,JZZS00,YSPBID) select PHZID00,a.* from (select BRID00,GHH000,GHKS00,pjzks00,JZKSMC,pjzys00,GHYSXM,GHLB00, HZLB00,HZLBMC, decode(nvl(v_CDXZXH,'99999'),'99999',HZXH00,v_CDXZXH), decode(nvl(v_CDDLXH,'99999'),'99999',DLXH00+1,v_CDDLXH), HZXHMC, to_char(sysdate,'yyyymmdd'),to_char(sysdate,'hh24:mi:ss'), HZQSRQ,HZQSSJ,HZJZRQ,HZJZSJ,decode(trim(MZHS_XZSBRZTSFGWHZZT),'Y','0','5'), pczy000,'1',JZZS00,YSPBID from sf_hzbr00 a where ghh000=Vsohzbr.GHH000 --and sfxz00='0' and HZZT00<>'Z' order by ID0000 desc) a where rownum=1 ; else insert into sf_hzbr00 (ID0000,BRID00,GHH000,GHKS00,JZKS00,JZKSMC,GHYS00,GHYSXM,GHLB00, HZLB00,HZLBMC,HZXH00,DLXH00,HZXHMC, DJRQ00,DJSJ00,HZQSRQ,HZQSSJ,HZJZRQ,HZJZSJ,HZZT00,CZY000,SFXZ00,JZZS00,YSPBID) select PHZID00,a.* from (select BRID00,GHH000,GHKS00,pjzks00,JZKSMC,pjzys00,GHYSXM,GHLB00, HZLB00,HZLBMC, HZXH00, DLXH00, HZXHMC, to_char(sysdate,'yyyymmdd'),to_char(sysdate,'hh24:mi:ss'), HZQSRQ,HZQSSJ,HZJZRQ,HZJZSJ,decode(trim(MZHS_XZSBRZTSFGWHZZT),'Y','0','5'), pczy000,'1',JZZS00,YSPBID from sf_hzbr00 a where ghh000=Vsohzbr.GHH000 --and sfxz00='0' and HZZT00<>'Z' order by ID0000 desc) a where rownum=1 ; end if; end if; --update sf_hzbr00 set HZZT00='Z' where ID0000=Vsohzbr.ID0000; elsif popflag=6 then--等待 if Vsohzbr.hzzt00 not in ('1','2','5','6') then--至少叫诊过的病人才能待诊 Verrmsg:='病人的候诊状态已经发生改变,处于"'||Verrmsg||'"状态请刷新后再试'; Raise Ecustom; end if; Update SF_HZBR00 set HZZT00='6',DDBZ00='1' where ID0000=Vsohzbr.ID0000; end if; end if; if pcommit='Y' then commit; end if; if (popflag=4) then if nvl(Vsohzbr.YSPBID,0)>0 then --20171123 begin execute immediate 'begin SP_HIS_JZDLDXXFS('||Vsohzbr.DLXH00||','||Vsohzbr.YSPBID||'); end;'; if pcommit='Y' then commit; end if; exception when others then Vcounter:=0; end; end if; if nvl(Vsohzbr.YSPBID,0)>0 then --20190114 排队叫号接口 begin select sys_context('userenv','ip_address') into LS_IP0000 from dual; select b.ghhzck into ls_ckbh00 from sf_hzbr00 a,sf_ysghhb b where a.yspbid=b.ghhbid and a.id0000=Vsohzbr.id0000; VexecSQL:= 'begin insert into SD_WLPT.WLPT_JZDATA(ID0000,HZID00,GHID00,BRID00,HZXHMC,HZCKBH,JZKS00,JZYS00,IP0000) values(SQ_WLPT_JZDATA_ID.nextval,:HZID00,:GHID00,:BRID00,:HZXHMC,:HZCKBH,:JZKS00,:JZYS00,:IP0000); end;'; execute immediate VexecSQL using in Vsohzbr.ID0000,in VGHID00,in Vsohzbr.BRID00,in Vsohzbr.HZXHMC,in ls_ckbh00,in Vsohzbr.JZKS00,in Vsohzbr.GHYS00,LS_IP0000; if pcommit='Y' then commit; end if; exception when others then Vcounter:=0; end; end if; end if; if (popflag=3) then begin select GHID00 into VGHID00 from SF_BRXXB0 where GHH000=Vsohzbr.GHH000; execute immediate 'begin SP_FZBMPT_PYDLXX('||Vsohzbr.BRID00||','||VGHID00||',''1''); end;'; if pcommit='Y' then commit; end if; exception when others then Vcounter:=0; end; end if; if (popflag=5) then begin if YS_SFJYJZBRGZ='Y' then select count(*) into Vcounter from SF_HZBR00 where hzzt00='4' and ghys00=pjzys00 and jzks00=pjzks00 and hzqsrq=to_char(sysdate,'yyyymmdd'); if Vcounter>0 then update SF_HZBR00 a set hzzt00='0' where hzzt00='4'and YSPBID=Vsohzbr.YSPBID and GHH000<>Vsohzbr.GHH000 and a.ghys00=pjzys00 and a.jzks00=pjzks00 and a.hzqsrq=to_char(sysdate,'yyyymmdd'); end if; end if; if YS_SFJZDGBR<>'Y' then select count(*) into Vcounter from VW_YS_SZHZBR where hzzt00='4' and ghys00=pjzys00 and jzks00=pjzks00 and hzqsrq=to_char(sysdate,'yyyymmdd'); if Vcounter>0 then Verrmsg:='已经有病人处于"叫诊"状态，请刷新后重试'; Raise Ecustom; end if; end if; Update SF_HZBR00 set hzzt00='4',JZRQ00=to_char(sysdate,'yyyymmdd'), JZSJ00=to_char(sysdate,'hh24:mi:ss') where id0000=PHZID00; if sql%notfound then Verrmsg:='叫诊时发生错误，请刷新后重试'; Raise Ecustom; end if; if pghhzck>0 then update SF_BRXXB0 SET JZYS00=pjzys00,JZYSXM=(Select ZWXM00 FROM BM_YGBM00 WHERE YGBH00=pjzys00) WHERE GHH000=Vsohzbr.GHH000;--(Select GHH000 FROM SF_HZBR00 WHERE ID0000=Vsohzbr.ID0000); update SF_HZBR00 SET ghys00=pjzys00,ghysxm=(select ZWXM00 from BM_YGBM00 WHERE YGBH00=pjzys00 ) where id0000=PHZID00; --update SF_HZBR00 set GHHZCK=pghhzck where id0000=Pid0000; end if; select count(*) into v_count99 from SF_HZBR00 where id0000=PHZID00 and exists(select 1 from XT_XTCS00 where NAME00='YS_SFQYZHPDJK' and VALUE0='Y'); if v_count99>0 then ls_JKSQL00:='insert into SD_ZHPD.temp_call_info(visit_no,visit_date,pat_id,pat_name,sex,age,identity,dept_code,visit_dept,clinic_type,' ||'doctor_code,visit_doctor,serial_no,pref_status,pref_name,reg_time,call_time,call_doctor,room_code,' ||'room_name,fztbh0,fztmc0,READ_FLAG,CZY000,CZRQ00)' ||' SELECT GHH000,HZQSRQ,brid00,brxm00,brxb00,brcsrq,brzjbh,jzks00,jzksmc,GHLBMC,ghys00,ghysxm,hzxh00,pref_status,' ||' pref_name,hzqssj,call_time,call_doctor,GHHZCK,ZSMC00,fztbh0,fztmc0,NULL,'||Pczy000||',to_char(sysdate,''yyyymmdd'') ' ||'FROM VW_ZHJK_HZBR00 WHERE id0000='||PHZID00; execute immediate ls_JKSQL00; end if; update SF_HZBR00 set JHCS00=nvl(JHCS00,0)+1 where ID0000=PHZID00; if nvl(Vsohzbr.YSPBID,0)>0 then --20171123 begin execute immediate 'begin SP_HIS_JZDLDXXFS('||Vsohzbr.DLXH00||','||Vsohzbr.YSPBID||'); end;'; if pcommit='Y' then commit; end if; exception when others then Vcounter:=0; end; end if; if nvl(Vsohzbr.YSPBID,0)>0 then --20190114 排队叫号接口 begin select sys_context('userenv','ip_address') into LS_IP0000 from dual; select b.ghhzck into ls_ckbh00 from sf_hzbr00 a,sf_ysghhb b where a.yspbid=b.ghhbid and a.id0000=PHZID00; VexecSQL:= 'begin insert into SD_WLPT.WLPT_JZDATA(ID0000,HZID00,GHID00,BRID00,HZXHMC,HZCKBH,JZKS00,JZYS00,IP0000) values(SQ_WLPT_JZDATA_ID.nextval,:HZID00,:GHID00,:BRID00,:HZXHMC,:HZCKBH,:JZKS00,:JZYS00,:IP0000); end;'; execute immediate VexecSQL using in PHZID00,in VGHID00,in Vsohzbr.BRID00,in Vsohzbr.HZXHMC,in ls_ckbh00,in Vsohzbr.JZKS00,in Vsohzbr.GHYS00,LS_IP0000; if pcommit='Y' then commit; end if; exception when others then Vcounter:=0; end; end if; select GHID00 into VGHID00 from SF_BRXXB0 where GHH000=Vsohzbr.GHH000; VZT0000:='0'; VexecSQL:='begin SP_YS_DYWBJK_HIS(:v1,:v2,:v3,:v4,:v5); end;'; execute immediate VexecSQL using in Vsohzbr.HZXH00,in Vsohzbr.GHH000,in VGHID00,in Vsohzbr.BRID00,in VZT0000; if pcommit='Y' then commit; end if; exception when others then Vcounter:=0; end; end if; if nvl(PBZ0000,' ')<>' ' then update SF_HZBR00 set BZ0000=PBZ0000 where ID0000=PID0000; end if; exception when no_data_found then raise_application_error(-20001,substrb('没有找到数据！*'||sqlerrm,1,240)); when ECUSTOM then raise_application_error(-20010,substrb(Verrmsg||'！*',1,240)); when Esxbd then rollback; raise_application_error(-20010,substrb('请等待其他医生叫诊一轮结束后，才能继续叫诊'||'！*'||Vparams||sqlerrm,1,126)); when EJZJS then rollback; raise_application_error(-20010,substrb('已没有号源，请稍等后继续叫号'||'！*'||Vparams||sqlerrm,1,126)); when others then rollback; raise_application_error(-20002,substrb(nvl(sqlerrm, '原因不明出错')||'！*'||Vparams||sqlerrm,1,126)); end;