create or replace procedure SP_YS_CZJZCL --过程说明，根据参数往后挪几位 ( 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' ) as 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 varchar2(20); --取出医生候诊就诊时，是否限制按顺序就诊 只限制首诊的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; YS_PTPBAKSJZZS varchar2(200); V_BRXM00 BM_BRXXB0.BRXM00%type; ls_CHECKWB varchar(10); -- 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; -- MODIFICATION HISTORY -- Person Date Comments -- YANGY 2007.08.21 允许已诊的病人直接就诊 -- CSF 2008.03.28 宁德市医院，首诊的候诊病人必须按顺序就诊 -- CSF 2009.05.18 科室排班，应诊时修改sf_hzbr00.ghys00 sf_brxxb0.jzys00 -- CSF 2009.05.25 科室排班，叫诊时候修改sf_hzbr00.ghys00 还有sf_hzbr00的ghhzck sf_brxxb0.jzys00 -- CSF 2009.08.06 科室排班，自动叫诊算法(1个医生叫完了，就等待其他所有医生叫号，叫完后，才能叫); -- CSF 2009.09.22 东南眼科，医生既处理自己的队列,也处理科室的队列，当叫自己队列号的时候不参与2009.08.06修改的按轮叫号的算法。 -- CSF 2009.10.27 东南眼科, 取消按轮叫号的算法。 -- CSF 2009.11.17 东南眼科, 医生能叫号的限制为2个(即:除了诊结状态的只能是两个候诊号) -- CSF 2009.12.10 东南眼科, 二楼分诊按医生叫诊产生叫诊队列,并按该队列进行叫诊 -- CSF 2009.12.17 东南眼科, 即使没有叫号也可以进行医生就诊状态设置 -- CSF 2010.03.13 东南眼科, 参与大科室抢号的医生用参数维护，抢号产生队列的时候，有判断医生手上就诊状态的病人个数只统计挂科室号源的病人 -- CSF 2010.03.20 东南眼科, 科室排班病人,没有叫诊不允许诊结，没有叫诊也不允许直接诊结 -- CSF 2010.03.22 东南眼科, 科室排班病人,不允许从叫诊状态直接到诊结。 -- CSF 2011.03.21 异常返回的错误字符超过126个字符时候，delphi中的过程执行卡住，所以截取异常为126个字符，另外异常后加回滚处理。 -- daihq 2014.07.07 增加参数控制续诊操作是否把病人候诊状态直接写为候诊状态 for MZHS-20140624-002 -- dsm 2014.07.30 增加popflag=6（等待）的情况 for MZYS-20140725-001 -- dsm 2015.08.04 增加写智慧中间表 for MZYS-20150715-001 -- dsm 2015.09.07 YS_SFXZASXJZ='Y'时要限制包括叫号状态的 for MZYS-20150821-002 -- dsm 2015.10.13 YS_SFJZDGBR=Y时不判断是否已经有病人在叫诊状态了 for MZYS-20150930-003 -- dsm 2016.02.04 增加连续过诊人数的控制 for MZYS-20160118-001 -- dsm 2016.02.25 增加参数YS_YZSSFZDZJZQYZBR控制应诊时是否自动诊结之前的应诊病人 for MZYS-20151116-002 -- dsm 2016.04.26 YS_ZJGHBJZXZ肺科直接挂号的不要列入叫诊限制 for MZYS-20160425-002 -- dsm 2016.11.18 Ecustom返回处改小,泉二回卡死 for MZYS-20161110-001 -- dsm 2016.12.09 YS_SFJZDGBR=Y and YS_SFXZASXJZ='Y'时判断HZZT00=4的必须按顺序纠诊 for MZYS-20161207-001 -- dsm 2017.03.16 增加参数控制续诊时是否重新生成队列号 for MZHS-20170316-001 -- dsm 2017.03.23 YS_SFJZDGBR=Y and YS_SFXZASXJZ='Y'时HZZT00 in(0,4)的都必须按顺序纠正 归档MZYS-20170323-003 -- dsm 2017.06.17 判断按顺序就诊时要考虑YS_SFDCQRCXS参数 归档MZYS-20170518-004 -- dsm 2017.09.21 增加调用SP_YY_ZDZFCDBRYY过程（自动作废过期预约） for MZYS-20170906-001 -- dsm 2017.11.23 增加调用SP_HIS_JZDLDXXFS for MZHS-20171117-001 -- dsm 2017.12.16 增加调用SP_FZBMPT_PYDLXX for MZYS-20171216-001 -- huangjy 2018.01.11 增加MZHS_XZCRXZDL,MZHS_XZCRXZDL='Y'时续诊插入现在候诊队列 for MZHS-20171116-001 -- huangjy 2018.01.19 修正无法取号的问题 for MZHS-20171118-001 -- dsm 2018.10.17 增加调用续诊接口 MZYS-20181017-001 -- dsm 2018.11.15 增加科室补叫功能 for MZYS-20181115-007 -- pwt 2018.11.26 取消叫诊时科室排班的限制功能 for MZYS-20181120-001 -- dsm 2019.01.21 省二检后门诊排队叫号 归档 for MZYS-20190108-001 -- dsm 2020.04.03 泉二下午叫诊时调用sp_pdjh_zdzjbr诊结同诊室的上午候诊号 for MZYS-20200403-001 -- dsm 2021.04.12 popflag=1时，把HZZT00写到HZZTBF for MZYS-20210412-001 -- dsm 2021.07.19 MZHS_XZCRXZDL=Y时，HZXH00不变 for MZYS-20210528-003 -- dsm 2021.11.19 增加YS_PTPBAKSJZZS的控制普通排班转科室叫号 for MZYS-20211027-002 -- linbin 2021.12.29 修正SP_FZBMPT_PYDLXX入参 for MZYS-20211229-001 -- dsm 2024.09.03 增加判断过诊时可以跳过按顺序的判断 for MZYS-20240902-002 -- dsm 2025.05.12 时间段还没有到的不能叫号 for MZYS-20250407-001 -- --------- ------------ ----------------------------------------------------------------------------- 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 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 in('YS_CYYGYS','YS_CYYGYS1') 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;v_SFCYYGYS:='N'; v_SFCYYGYS:='Y'; exception when others then v_SFCYYGYS:='N'; end; --普通排班科室叫诊的诊室 if v_SFCYYGYS='N' then select trim(VALUE0) into YS_PTPBAKSJZZS from XT_XTCS00 where name00='YS_PTPBAKSJZZS' ; select count(*) into Vcounter from BM_CKBM00 where instr(','||YS_PTPBAKSJZZS||',',','||CKBH00||',')>0 and BMBH00=PJZKS00 and Flag00='4'; if Vcounter>0 then v_SFCYYGYS:='Y'; end if; end if; end if; --若医生存在自己的队列 /**/ 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 HZZT00 in ('0','2')) or (instr(','||YS_PTPBAKSJZZS||',',','||GHHZCK||',')>0 and HZZT00 in('0','2') and PBLX00='0' ) ) and DLXH00>0 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('||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; -- 取参数 --取出医生候诊就诊时，是否限制按顺序就诊 只限制首诊的HZZT00=1病人 begin SELECT NVL(MAX(trim(VALUE0)),'NN') INTO V_SFXZASXJZ FROM XT_XTCS00 WHERE NAME00='YS_SFXZASXJZ'; if V_SFXZASXJZ='Y' then V_SFXZASXJZ:='YN'; end if; if V_SFXZASXJZ='N' then V_SFXZASXJZ:='NN'; end if; EXCEPTION WHEN OTHERS THEN V_SFXZASXJZ:='NN'; 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 ; begin select SF_MZHS_CHECKWB(ghqssj,ghjssj) into ls_CHECKWB from SF_YSGHHB where GHHBID=Vsohzbr.YSPBID; EXCEPTION WHEN OTHERS THEN ls_CHECKWB:='1'; end; if ls_CHECKWB='0' then select BRXM00 into V_BRXM00 from BM_BRXXB0 where BRID00=Vsohzbr.BRID00; Verrmsg:='时段还未到('||Vsohzbr.HZXHMC||')'; Raise Ecustom; end if; select count(*) into V_COUNT from BM_TYZD00 where ZDMC00='叫号病人必须按顺序就诊的科室' and instr(','||MC0000||',',','||pjzks00||',')>0; --V_SFXZASXJZ 第2位判断过诊操作是否要跳过按顺序（Y要跳过，N不跳过），默认N if substr(V_SFXZASXJZ,2,1)<>'Y' then if (substr(V_SFXZASXJZ,1,1)='Y') and (YS_SFJZDGBR='Y' and Vsohzbr.hzzt00 in('0','4') ) and V_COUNT>0 then select count(*) INTO V_COUNT from sf_hzbr00 a where YSPBID=Vsohzbr.YSPBID AND (dlxh000 )); --2015.09.07 加叫诊状态 for MZYS-20150821-002 if V_COUNT>0 then Verrmsg:='请按顺序就诊'; Raise Ecustom; end if; elsif (substr(V_SFXZASXJZ,1,1)='Y') and (YS_SFJZDGBR<>'Y') and (Vsohzbr.SFXZ00='0') and (Vsohzbr.hzzt00='0') then select count(*) INTO V_COUNT from sf_hzbr00 a where YSPBID=Vsohzbr.YSPBID AND (dlxh00pid0000) ) AND HZZT00 in('0','4') AND (SFXZ00 in decode(MZHS_XZCRXZDL,'Y','1','0') or SFXZ00 in decode(MZHS_XZCRXZDL,'Y','0','0') ) --SFXZ00='0' and (nvl(YYID00,0)=0 or exists (select 1 from SF_YYBRB0 where BRID00=a.BRID00 and YYID00=a.YYID00 and HZDYCS>0 )) ; --2015.09.07 加叫诊状态 for MZYS-20150821-002 if V_COUNT>0 then Verrmsg:='请按顺序就诊'; Raise Ecustom; end if; 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_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; 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.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); --2021.04.12 多修改HZZTBF for MZYS-20210412-001 Update SF_HZBR00 set HZZTBF=HZZT00,HZZT00='1',YZRQ00=to_char(sysdate,'yyyymmdd'), YZSJ00=to_char(sysdate,'hh24:mi:ss'),GHYS00=pjzys00,JZKS00=pjzks00 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; end if; --作废之前过期的预约病人 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; 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 SQ_SF_HZBR00_ID0000.Nextval,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 SQ_SF_HZBR00_ID0000.Nextval,a.* from (select BRID00,GHH000,GHKS00,pjzks00,JZKSMC,pjzys00,GHYSXM,GHLB00, HZLB00,HZLBMC, -- decode(nvl(v_CDXZXH,'99999'),'99999',HZXH00,v_CDXZXH), a.HZXH00, 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 SQ_SF_HZBR00_ID0000.Nextval,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; begin execute immediate 'begin sp_pdjh_zdzjbr('||Vsohzbr.YSPBID||','||Vsohzbr.JZZS00||'); 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;'; execute immediate 'begin SP_FZBMPT_PYDLXX('||Vsohzbr.BRID00||','''||VGHID00||''',''1'',0,0,''''); end;'; if pcommit='Y' then commit; end if; exception when others then Vcounter:=0; end; end if; if (popflag=5) then begin 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; exception when no_data_found then rollback; raise_application_error(-20001,substrb('数据没有找到！*'||sqlerrm||','||Vparams||sqlerrm,1,126)); when Ecustom then rollback; raise_application_error(-20010,substrb(Verrmsg||'！*'||Vparams||sqlerrm,1,60)); 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 ;