CREATE OR REPLACE FUNCTION SF_YS_GHXZ00(Pghks00 number,Pghys00 number,Pbrid00 number) return varchar2 is ls_YBLB00 BM_BRXXB0.YBLB00%TYPE ; ls_YBZXLB IC_YBBRLB.YBZXLB%TYPE ; ls_FBBH00 BM_BRXXB0.FBBH00%TYPE ; Vbyzglb bm_ghlbb0.lbbh00%type ;--本院职工挂号类别 Vghhbid SF_YSGHHB.GHHBID%type ;--挂号号表ID Vghxhs0 SF_YSGHHB.GHXHS0%type ;--挂号限号数 ls_YGBH00 BM_BRXXB0.YGBH00%TYPE ; vzxzghlb bm_bmbm00.xzghlb%type ;--总限制过类别 vxzbmygghlb bm_bmbm00.xzghlb%type ;--限制部门员工挂号类别 Vxzghlb bm_ybbrlb.xzghlb%type ;--限制挂号类别 Vbmxz00 bm_bmbm00.bmxz00%type ;--挂号科室部门性质 Vbmmc00 bm_bmbm00.bmmc00%type ;--挂号科室名称 Vyxgmfh char(1) ;--允许挂免费号 Vghdjxz char(1) ;--挂号等级限制'Y'限制,允许向下兼容,'M'限制,不允许向下兼容,'N'不作等级限制 Vdqxq00 CHAR(1) ;--当天星期 Vdqrq00 CHAR(8) ;--当前日期 Vdqsj00 CHAR(8) ;--当前时间 Vsfqtjz char(1) ;--是否全天急诊 ls_dtghxz CHAR(1) ; ls_SFYXPTH CHAR(1) ;-- 是否允许挂 普通号 0：不允许 1：允许 ls_JZSJDXZJZH CHAR(1) ;--急诊时间段是否限制号别 Y 限制 N 不限制 默认为 Y ls_KSGHZDCS NUMBER(5) ; V_COUNT0 Number(5) ; V_COUNT1 Number(5) ; Vxs0000 Number(2) ; Vcounter number(10) ;--临时变量 Vxzghdj number(10) ;--挂号等级限制 Vghksxz number(10) ;--对具体科室进行挂号限制 ls_IP varChar2(20) ;--限制IP Vjzlbbh varchar2(10) ;--急诊的挂号类别编号 Vjzlbbh1 varchar2(200); ls_YXGMFH VARCHAR(225) ;--允许挂免费号医生 ls_YXGSYHBYS XT_XTCS00.VALUE0%TYPE ;--允许挂所有号别的医生 Vghlbbh varchar2(255) ;--挂号类别编号 Verrmsg varchar2(255) ;--错误提示变量 Ecustom exception ;--错误变量 LS_GHCS01 number(5); Vbrcsrq bm_brxxb0.brcsrq%type; cursor C_ghlbbh is select lbbh00 from bm_ghlbb0 c where SFYX00='Y' and lbbh00<>Vbyzglb and --挂号等级为-1说明本挂号类别不受等级限制 ( ( ((GHDJ00<=Vxzghdj and Vghdjxz='Y')or (GHDJ00=Vxzghdj and Vghdjxz='M' and LS_GHCS01=0) or (GHDJ00<=-1) or (Vghdjxz='N')) --限制免费号 and ((Vyxgmfh='Y' or LBBH00 in (select LBBH00 from VW_SF_ghlbsf where nvl(ghks00,-1)=-1 or ghks00=Pghks00)) or ((Vyxgmfh='M' and LS_GHCS01=0 ) or (Vyxgmfh='M' and LS_GHCS01>0 and GHDJ00=-2)) ) and not exists(select 1 from xt_xtcs00 where name00='YS_TJKGHLBBH' and c.lbbh00=to_number(trim(value0))) and ( (ls_YXGMFH='N') or ((ls_YXGMFH<>'N') and (instr((select ','||trim(Value0)||',' from XT_XTCS00 where name00='YS_YXGMFHYS'),','||to_char(Pghys00)||',')=0) and (lbbh00 in (select distinct lbbh00 from VW_SF_ghlbsf where sfje00>0)) and LS_GHCS01=0 ) or ((ls_YXGMFH<>'N') and (instr((select ','||trim(Value0)||',' from XT_XTCS00 where name00='YS_YXGMFHYS'),','||to_char(Pghys00)||',')>0)) ) ) or (ls_YXGSYHBYS<>'N' and (instr((select ','||trim(Value0)||',' from XT_XTCS00 where name00='YS_YXGSYHBYS'),','||to_char(Pghys00)||',')>0) ) or (Vyxgmfh='M' and LS_GHCS01>0 and GHDJ00=-2) ) order by xsxh00,ghdj00,lbbh00; cursor C_jzghlbbh is select to_char(lbbh00) LBBH00 from bm_ghlbb0 where SFYX00='Y' AND (LBMC00 LIKE '%急诊%' or SFJZLB in('1')) and ( (GHDJ00<=Vxzghdj and Vghdjxz='Y')or (GHDJ00=Vxzghdj and Vghdjxz='M') or (GHDJ00=-1) or (Vghdjxz='N') or (ls_YXGSYHBYS<>'N' and (instr((select ','||trim(Value0)||',' from XT_XTCS00 where name00='YS_YXGSYHBYS'),','||to_char(Pghys00)||',')>0)) ) ; -- MODIFICATION HISTORY -- Person Date Comments -- YANGY 2007.07.17 老年医院六点以后要求只挂急诊号 -- YANGY 2007.07.19 同一医生同一天只能给同一病人挂一个号，挂第二个号时提示“本日已经挂过号” -- YANGY 2007.07.27 增加一个表可医院自己维护急诊时间段 -- YANGY 2007.08.16 限制一个科室最多只能挂几个号 -- YANGY 2007.08.17 一个医生一天可以挂一个普通号，一个急诊号 -- YANGY 2008.12.14 本院员工挂号设置问题有所改变 -- CSF 2009.04.04 急诊时间段默认急诊，但可选择非急诊号 ，加参数限制，急诊时间段是否限制号别 。 -- CSF 2009.04.15 限制部分医生可以挂免费号 增加参数YS_YXGMFHYS. -- CSF 2009.06.04 免费号=select distinct lbbh00 from VW_SF_ghlbsf where sfje00>0 -- zhr 2010.04.01 特定时间限制特定IP不能接诊，儿科不能接诊15以上的病人 -- zhangwz 2011.06.03 在YS_JZSJDSFXZJZHB='Y'的情况下游标C_jzghlbbh返回的急诊的挂号类别应该不止一个，变更为受参数XT_GHDJXZ影响. -- 游标 C_ghlbbh 判断是否是参数 YS_YXGMFHYS 定义的人员增加前后,处理，要求然等下维护了1100，那么110，100的人员都可以使用. -- zhangwz 2011.06.03 增加参数 YS_YXGSYHBYS 控制可以挂所有号别的医生. by MZYS-20110509-001. -- dsm 2015.11.13 YS_JZSJB0加LB0000的条件 for MZYS-20151031-001 -- dsm 2018.06.08 体检病人不能在HIS就诊 归档 MZYS-20180601-001 -- dsm 2019.07.05 首次挂号限制免费号，二次挂号可以挂免费号 （YS_YXGMFH=M）for MZYS-20190531-003 -- dsm 2019.07.11 急诊挂好只能某些科室用(BM_KSGHDY做对于) for MZYS-20190531-005 begin --如果指定了本院职工的挂号类别，直接返回本院职工类别; --如果该医生限制了挂号等级,并且有排班,直接返回排班定义的号表; --如果该医生限制了挂号等级,则要按医生的等级进行挂号限制 挂号类别等级为-1的说明该类别不受限制 --定义了该类别的病人做了挂号限制BM_YBBRLB --定义了该科室的挂号类别进行限制BM_KSGHDY --是否允许挂免费号 --医技系统简易门诊的默认挂号 ls_SFYXPTH:='1'; vzxzghlb:=' '; vxzbmygghlb:=' '; Vxzghlb:=' '; Vxzghdj:=1000; Vghlbbh:='-1'; Vyxgmfh:='N'; select TO_NUMBER(trim(NVL(max(value0),'0'))) into ls_KSGHZDCS FROM xt_xtcs00 where name00='YS_KSGHXZCS'; select trim(NVL(max(value0),'N')) into ls_dtghxz FROM xt_xtcs00 where name00='YS_TYSDTGHXZ'; select trim(NVL(max(value0),'Y')) into ls_JZSJDXZJZH FROM xt_xtcs00 where name00='YS_JZSJDSFXZJZHB'; select trim(NVL(max(value0),'N')) into ls_YXGMFH FROM xt_xtcs00 where name00='YS_YXGMFHYS'; select trim(NVL(max(value0),'N')) into ls_YXGSYHBYS FROM xt_xtcs00 where name00='YS_YXGSYHBYS'; begin--取部门性质 select bmxz00,bmmc00 into Vbmxz00,Vbmmc00 from bm_bmbm00 where bmbh00=Pghks00; exception when others then return '-1'; end; --20100331 zhr 要求限制某些IP某些时段不能开诊 ls_IP:=trim(sys_context('userenv', 'ip_address')); select count(*) into Vcounter from BM_TYZD00 where ZDMC00='限制开诊IP' and (MC0000 like '%'||ls_IP||'%' or (instrb(MC0000,'*')>0 and MC0000 like '%'||substrb(ls_IP,1,instrb(ls_IP,'.',-1)) ||'%')); if VCounter>0 then select count(*) into Vcounter from BM_TYZD00 where ZDMC00='限制开诊时间' and to_char(sysdate,'HH24:MI') between substrb(MC0000,1,instrb(MC0000,'-')-1) and substrb(MC0000,instrb(MC0000,'-')+1,5); if Vcounter>0 then Verrmsg:='对不起，此时段不允许接诊！'; raise Ecustom; end if; end if; if instrb(Vbmmc00,'儿')>0 then select BRCSRQ into Vghlbbh from bm_brxxb0 where brid00 =Pbrid00; if nvl(floor((months_between(sysdate,to_date(Vghlbbh,'YYYYMMDD')))/12),100)>15 then Verrmsg:='对不起，儿科不能接诊15岁以上的病人！'; raise Ecustom; end if; end if; select count(*) into Vcounter from xt_yyxx00 where YYQC00='福建医科大学附属第二医院'; if Vcounter>0 then --如果有排班，直接取排班的号别 begin select to_char(GHLBBH),GHXHS0,GHHBID into Vghlbbh,Vghxhs0,Vghhbid from SF_YSGHHB where GHKSBH=Pghks00 and GHYSBH=Pghys00 and GHMZSJ=to_char(sysdate,'yyyymmdd') and GHQSSJ<=to_char(sysdate,'hh24:mi:ss') and GHJSSJ>=to_char(sysdate,'hh24:mi:ss'); select count(*) into Vcounter from sf_brxxb0 a,sf_ysghhb b where b.GHHBID=Vghhbid and a.thbz00<>'0' and b.GHKSBH=a.ghks00 and b.GHYSBH=a.JZYS00 and b.GHMZSJ=a.ghrq00 and b.GHQSSJ<=a.ghsj00 and b.GHJSSJ>=a.ghsj00; if Vghxhs0>Vcounter then return Vghlbbh; else return -1; end if; exception when others then Vghlbbh:='-1'; end; end if; --本院职工挂号类别限制 begin select to_number(value0) into Vbyzglb from xt_xtcs00 where name00='SF_BYZGGHLB'; exception when others then Vbyzglb:=-1; end; if Pbrid00>0 then--指定了病人 --病人是否是本院职工，如果是本院职工，只能挂本院职工号并立即返回,其他病人不能挂本院职工号 if Vbyzglb>0 then -- select count(*) into Vcounter from bm_ygbm00 where brid00=Pbrid00; select count(1) into Vcounter from bm_brxxb0 a,BM_YGBM00 b where a.ygbh00=b.ygbh00 and a.brid00=Pbrid00 and a.ygbh00 is not null; if Vcounter>0 then return to_char(Vbyzglb); end if; end if; --限制部门的员工挂号 begin select nvl(xzghlb,' ') into vxzbmygghlb from bm_bmbm00 a,bm_ygbm00 b where a.bmbh00=b.bmbh00 and b.brid00=Pbrid00; exception when others then vxzbmygghlb:=' '; end; --bm_ybbrlb表限制了，该类病人所允许的挂号类别 begin select nvl(xzghlb,' ') into Vxzghlb from bm_brxxb0 a,bm_ybbrlb b,ic_ybbrlb c where a.fbbh00=c.fbbh00 and a.yblb00=c.yblb00 and a.ybbrlb=b.ybbrlb and a.fbbh00=b.fbbh00 and b.ybzxlb=c.ybzxlb and a.brid00=Pbrid00; exception when others then Vxzghlb:=' '; end; select FBBH00 into ls_FBBH00 from BM_BRXXB0 where BRID00=PBRID00; if ls_FBBH00=99 then Verrmsg:='体检病人不能在HIS就诊'; raise Ecustom; end if; IF ls_dtghxz= 'Y' THEN SELECT COUNT(1) INTO V_COUNT1 FROM SF_BRXXB0 WHERE BRID00=Pbrid00 AND JZYS00=Pghys00 AND GHRQ00= TO_CHAR(SYSDATE,'YYYYMMDD') AND THBZ00<>'0' and ghlb00<>-99999; IF V_COUNT1>0 then select YBLB00,FBBH00 INTO ls_YBLB00,ls_FBBH00 FROM BM_BRXXB0 WHERE BRID00=Pbrid00; select ybzxlb INTO ls_YBZXLB from ic_ybbrlb where yblb00=ls_YBLB00 AND FBBH00=ls_FBBH00; select COUNT(1) INTO V_COUNT0 FROM BM_TSBZB0 WHERE YBZXLB=ls_YBZXLB; IF V_COUNT0=0 THEN if V_COUNT1=1 then SELECT COUNT(1) INTO V_COUNT0 FROM SF_BRXXB0 a, bm_ghlbb0 b WHERE a.ghlb00=b.lbbh00 and a.BRID00=Pbrid00 AND a.JZYS00=Pghys00 AND GHRQ00= TO_CHAR(SYSDATE,'YYYYMMDD') AND a.THBZ00<>'0' and a.ghlb00<>-99999 and b.lbmc00 like '%急诊%'; if V_COUNT0>0 then ls_SFYXPTH :='1'; else ls_SFYXPTH := '0'; end if; else Verrmsg:='该病人当天已经在这个医生处挂过号一个普通号和一个急诊号，不能重新挂号'; raise Ecustom; end if; END IF; end if; END IF; IF ls_KSGHZDCS>0 THEN SELECT COUNT(1) INTO V_COUNT0 FROM SF_BRXXB0 WHERE BRID00=Pbrid00 AND GHKS00=Pghks00 AND GHRQ00= TO_CHAR(SYSDATE,'YYYYMMDD') AND THBZ00<>'0' and ghlb00<>-99999; IF V_COUNT0>=ls_KSGHZDCS then Verrmsg:='该病人当天已经在本科室挂过'||to_char(ls_KSGHZDCS)||'次号，不能重新挂号！'; raise Ecustom; end if; END IF; end if; --确定该部门是否允许挂免费的号 begin if Vbmxz00 ='5' then select substrb(Value0,1,1) into Vyxgmfh from xt_xtcs00 where name00='XT_SFYXGH'; else select substrb(Value0,1,1) into Vyxgmfh from xt_xtcs00 where name00='YS_YXGMFH'; end if; exception when others then Vyxgmfh:='N'; end; LS_GHCS01:=0; --是否根据医生的挂号级别来限制挂号等级,Y允许向下兼容M不允许向下兼容N不作限制 begin select substrb(value0,1,1) into Vghdjxz from xt_xtcs00 where name00='XT_GHDJXZ'; if Vghdjxz in ('Y','M') then select ghdj00 into Vxzghdj from bm_ghlbb0 where lbbh00=(select MRGHLB from bm_ygbm00 where YGBH00=Pghys00); --同等级的已经挂号次数 select count(*) ZGHS00 into LS_GHCS01 from SF_BRXXB0 c where c.BRID00=Pbrid00 AND c.GHRQ00= TO_CHAR(SYSDATE,'YYYYMMDD') AND c.THBZ00<>'0' and c.ghlb00<>-99999 and JZKS00=Pghks00 and exists(select 1 from bm_ghlbb0 where LBBH00=c.GHLB00 and GHDJ00=Vxzghdj); end if; exception when others then Vghdjxz:='N'; Vxzghdj:=1000; LS_GHCS01:='0'; end; if vxzbmygghlb<>' ' and Vxzghlb<>' ' then vzxzghlb:= vxzbmygghlb; end if; if vxzbmygghlb<>' ' and Vxzghlb=' ' then vzxzghlb:=vxzbmygghlb; end if; if vxzbmygghlb=' ' and Vxzghlb<>' ' then vzxzghlb:=Vxzghlb; end if; if vxzbmygghlb=' ' and Vxzghlb=' ' then vzxzghlb:=' '; end if; --是否根据科室挂号对应关系表来限制挂号 --select count(*) into Vghksxz from BM_KSGHDY where BMBH00=Pghks00; --marked MZYS-20190531-005 for GH in C_ghlbbh loop --是否根据科室挂号对应关系表来限制挂号 select count(*) into Vghksxz from BM_KSGHDY where LBBH00=gh.lbbh00; --add MZYS-20190531-005 Vcounter:=0; if Vghksxz>0 then select count(*) into Vcounter from BM_KSGHDY where BMBH00=Pghks00 and LBBH00=gh.lbbh00; end if; if Vghksxz=0 or (Vghksxz>0 and Vcounter>0) then --bm_ybbrlb表限制了，该类病人所允许的挂号类别 if (instrb(','||vzxzghlb||',',','||to_char(GH.lbbh00)||',')>0) or (vzxzghlb=' ') then if Vghlbbh='-1' then Vghlbbh:=to_char(GH.lbbh00); else Vghlbbh:=Vghlbbh||','||to_char(GH.lbbh00); end if; end if; end if; end loop; if ls_JZSJDXZJZH='Y' then --是否做急诊时间段限制 Vjzlbbh:= ' '; For JZGH in C_jzghlbbh loop if instrb(','||Vghlbbh||',', ','||JZGH.LBBH00||',')>0 then Vjzlbbh:= JZGH.LBBH00; Vjzlbbh1:=' '; end if; -- end loop; if Vjzlbbh<>' ' then Vsfqtjz := 'N'; select to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS') , to_char(sysdate-1,'D') into Vdqrq00, Vdqsj00, Vdqxq00 from dual; select count(1) into V_COUNT0 from YS_JZSJB0 where rqks00<=Vdqrq00 and rqjz00>=Vdqrq00 and SFYX00='Y' and LB0000='0'; IF V_COUNT0>0 THEN select SFQTJZ into Vsfqtjz from YS_JZSJB0 where rqks00<=Vdqrq00 and rqjz00>=Vdqrq00 and SFYX00='Y' and LB0000='0' and rownum=1; if Vsfqtjz='Y' THEN --Vghlbbh:=Vghlbbh||','|| Vjzlbbh; Vjzlbbh1:=Vjzlbbh; ELSE select count(1) into V_COUNT0 from YS_JZSJB0 where rqks00<=Vdqrq00 and rqjz00>=Vdqrq00 AND sjks00<=Vdqsj00 and sjjz00>=Vdqsj00 and SFYX00='Y' and LB0000='0'; if V_COUNT0>0 then --Vghlbbh:= Vghlbbh||','||Vjzlbbh; Vjzlbbh1:=Vjzlbbh; end if; END IF; if Vjzlbbh1<>' ' then Vghlbbh:=replace(','||Vghlbbh||',', ','||Vjzlbbh1||',',',0,'); Vghlbbh:=substr(Vghlbbh,2,length(Vghlbbh)-2); end if; ELSE select count(1) into V_COUNT0 from YS_JZSJB0 where xqks00<=Vdqxq00 and xqjz00>=Vdqxq00 and SFYX00='Y' and LB0000='0'; if V_COUNT0>0 then select count(1) into V_COUNT0 from YS_JZSJB0 where xqks00<=Vdqxq00 and xqjz00>=Vdqxq00 and SFQTJZ='Y' and SFYX00='Y' and LB0000='0'; IF V_COUNT0>0 THEN --Vghlbbh:= Vghlbbh||','||Vjzlbbh; Vjzlbbh1:=Vjzlbbh; ELSE select count(1) into V_COUNT0 from YS_JZSJB0 where xqks00<=Vdqxq00 and xqjz00>=Vdqxq00 AND sjks00<=Vdqsj00 and sjjz00>=Vdqsj00 and SFYX00='Y' and LB0000='0'; IF V_COUNT0>0 THEN --Vghlbbh:= Vghlbbh||','||Vjzlbbh; Vjzlbbh1:=Vjzlbbh; END IF; END IF; end if; if Vjzlbbh1<>' ' then Vghlbbh:=replace(','||Vghlbbh||',', ','||Vjzlbbh1||',',',0,'); Vghlbbh:=substr(Vghlbbh,2,length(Vghlbbh)-2); end if; END IF; END IF; --END IF; end loop; END IF; if ls_SFYXPTH='0' then Vghlbbh:= Vjzlbbh; IF Vjzlbbh= ' ' THEN Verrmsg:='该病人当天已经在这个医生处挂过号一个普通号并且该医生没有有效的急诊号，不能重新挂号'; RAISE Ecustom; END IF; end if; if Pbrid00<>'0' then select BRCSRQ into Vbrcsrq from bm_brxxb0 where brid00 =Pbrid00; if nvl(floor((months_between(sysdate,to_date(Vbrcsrq,'YYYYMMDD')))/12),100)>5 then if instr(','||Vghlbbh||',',',10017,')>0 then Vghlbbh:=replace(Vghlbbh,',10017',null); end if; if instr(','||Vghlbbh||',',',10018,')>0 then Vghlbbh:=replace(Vghlbbh,',10018',null); end if; if instr(','||Vghlbbh||',',',10019,')>0 then Vghlbbh:=replace(Vghlbbh,',10019',null); end if; if instr(','||Vghlbbh||',',',10020,')>0 then Vghlbbh:=replace(Vghlbbh,',10020',null); end if; if instr(','||Vghlbbh||',',',10022,')>0 then Vghlbbh:=replace(Vghlbbh,',10022',''); end if; if instr(','||Vghlbbh||',',',10023,')>0 then Vghlbbh:=replace(Vghlbbh,',10023',''); end if; if instr(','||Vghlbbh||',',',10024,')>0 then Vghlbbh:=replace(Vghlbbh,',10024',''); end if; if instr(','||Vghlbbh||',',',10026,')>0 then Vghlbbh:=replace(Vghlbbh,',10026',''); end if; else return Vghlbbh; end if; end if; return Vghlbbh; exception when Ecustom then raise_application_error(-20010,Verrmsg||'*'); end;