CREATE OR REPLACE TRIGGER TR_SF_YSGHHB_BEFINSAPDEL BEFORE INSERT OR DELETE OR UPDATE OF FZYYS0, DHYYMX, YYXHS0, GHXHS0, DHMX02, WLYYS0, FZYYMX, PJZZSJ, YYXHMX, HZXHJS, DHYY02, DHYYS0, WLYYMX ON SD_SF.SF_YSGHHB REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW declare ErrStr varchar2(200); Cs_Ys_Xzygh0 CHAR(1); E_Ghxhs0 EXCEPTION; E_Yyxhs0 EXCEPTION; V_Ghxhs0 NUMBER(6); V_Count0 NUMBER(5); V_Yyxhs0 NUMBER(6); Vyzdycd number(5); Vhzdcd0 number(5); Vhzdnum number(5); Vhzdrs0 number(5); i number(5); Vhzqssj date; Vhzjzsj date; Vmessage varchar2(20); V_HZQSXH NUMBER(5);--候诊起始序号 v_FZYYMX sf_yspbb0.FZYYMX%type; v_DHYYMX sf_yspbb0.DHYYMX%type; v_WLYYMX sf_YSPBB0.DHMX02%type; v_YNBLMX sf_YSPBB0.YNBLMX%type; ls_hzxh00 ys_hbhzxh.hzxh00%type; v_PJZZSJ sf_YSPBB0.PJZZSJ%type; v_COUNT1 number(5); --记录YS_YSPBXH记录数 ls_XQ0000 char(1); --存放星期几 ls_YSPBID SF_YSPBB0.YSPBID%type; v_COUNT2 number(5); --记录已挂号人数 ls_YSPBSFHYWH XT_XTCS00.VALUE0%type; MZHS_YYXHSKGBZHS varchar2(10); LS_SFJYBXTPP varchar2(10); -- MODIFICATION HISTORY -- Person Date Comments -- YANGY 2007.08.15 门诊护士产生挂号表时，也同时产生所有的候诊序号 -- 叶新燕 2008.12.30 候诊序号不能从指定的序号开始 给与纠正 -- CSF 2008.12.31 限号数增加，候诊序号没有正常增加 -- CSF 2009.09.06 排班维护，增加分诊台预约、电话预约、网络预约的维护 -- LML 2010.05.05 纠正bug,分诊台预约、电话预约、网络预约的维护,只有一个数，且数据后面没有","则存在问题。 -- zhr 2010.10.30 广东省中医，按时段产生预约号 -- zhr 2011.01.18 生成YS_HBHZXH时,HZXH00+候诊序号基数HZXHJS -- qks 2011.01.19 修改广东中医院的条件。 -- qks 2011.02.21 广东中医院:增加条件and YYBZ00<>'7' -- qks 2011.03.05 广东中医院:修改“修正预约优待标志”条件 -- qks 2011.06.19 广东中医院:增加条件and nvl(:new.YYSL00,0)=0,修改BRID00=NULL, -- csf 2011.08.18 省二模式下YS_HSGHXZHZXH=Y,YS_HBHZXH只要没被约，那么将YYBZ00先还原成1，再进行修改。 -- csf 2012.01.11 仅仅HZXHJS变化时候Cs_Ys_Xzygh0='Y'的模式无需处理 MZHS-20120112-001 -- qks 2012.04.26 省二模式下YS_HSGHXZHZXH=Y,启用DHYY02 MZHS-20120427-001 -- qks 2012.06.07 YS_HBHZXH增加HZQSSJ;只修改排班表中的"网络预约数3",重新生成挂号号表时,要求YS_HBHZXH.HZQSSJ也要写入值 by YYGH-20120608-002 -- daihq 2012.11.06 增加从模板表YS_YSPBXH读取号源添加到YS_HBHZXH,增加系统参数MZHS_YSPBSFHYWH MZHS-20121010-001 -- daihq 2012.12.13 号源未使用时，YS_HBHZXH中的YYBZ00根据模板表来修改 MZHS-20121213-003 -- dsm 2013.10.12 YS_HBHZXH.ZZGHBZ写入数据 for YYGH-20130924-002 -- dsm 2015.01.22 YS_HSGHXZHZXH='M'时也要和Y一样处理 for MZYS-20150109-003 -- dsm 2015.05.22 YS_HBHZXH.ZSYYBZ写入数据 for YYGH-20150327-001 -- dsm 2015.07.16 增加参数MZHS_YYXHSKGBZHS控制是否生成YYBZ00=3的数据 for MZHS-20150713-001 -- dsm 2016.03.22 当MZHS_SFJYBXTPP参数起用时,屏蔽此触发器 for MZHS-20160321-002 -- dsm 2016.05.04 BM_MZSJB0的条件改成 in for MZSF-20160405-003 -- daihq 2016.05.26 v_WLYYMX改为取DHMX02的长度 for YYGH-20160526-001 -- dsm 2016.06.30 超过排班结束时间的号源时间都写成结束时间 YYGH-20160630-001 -- dsm 2016.11.01 MZHS_YYXHSKGBZHS=Y时去掉E_Yyxhs0错误限制 for ZZJJK-20161027-001 -- dsm 2016.11.07 当YS_YSPBXH有记录,修改号表的GHXHS0时YS_HBHZXH的记录不会变化 for YYGH-20161107-001 -- chenhn 2016.12.13 修改增加号源的bug问题 for YYGH-20161209-001 -- dsm 2017.06.07 SCSBYY='删除'并且号源减少时特殊处理 for YYGH-20170523-001 Begin --==============================================其他医院做法============================================== select nvl(max(substr(VALUE0,1,1)),'N') into LS_SFJYBXTPP from XT_XTCS00 where NAME00='MZHS_SFJYBXTPP'; if LS_SFJYBXTPP='Y' then return; end if; --取系统参数MZHS_YSPBSFHYWH begin select upper(trim(VALUE0)) into ls_YSPBSFHYWH from XT_XTCS00 where NAME00='MZHS_YSPBSFHYWH'; exception when others then ls_YSPBSFHYWH:='N'; end; --取YSPBID ls_XQ0000 := to_char(to_Date(:new.GHMZSJ,'YYYY.MM.DD'),'D'); select nvl(max(YSPBID),0) into ls_YSPBID from SF_YSPBB0 where YSPBXQ = ls_XQ0000 and tzbz00='0' and YSKSBH=:new.GHKSBH and PBLX00='0' and YSYGBH=:new.GHYSBH and YSPBSJ in (select MZSJ00 from BM_MZSJB0 where KSSJ00=:new.GHQSSJ and JSSJ00=:new.GHJSSJ); v_COUNT1:=0; if ls_YSPBSFHYWH='Y' then select count(*) into v_COUNT1 from YS_YSPBXH where YSPBID=ls_YSPBID and ls_YSPBID<>0; end if; select upper(trim(nvl(max(VALUE0),'N'))) into Cs_Ys_Xzygh0 from XT_XTCS00 where NAME00='YS_HSGHXZHZXH'; if Cs_Ys_Xzygh0 in ('Y','M') then --2012.12.13 add by daihq for MZHS-20121213-003 if v_COUNT1>0 then update YS_HBHZXH a set a.YYBZ00=(select b.YYBZ00 from YS_YSPBXH b where b.YSPBID=ls_YSPBID and b.PBXH00=a.HZXH00) where a.BRID00 is null and GHHBID=:new.GHHBID and exists(select 1 from YS_YSPBXH c where c.YSPBID=ls_YSPBID and c.PBXH00=a.HZXH00); end if; if inserting then--增加号表记录 :NEW.MRXHS0 := :NEW.GHXHS0; V_HZQSXH:=:NEW.HZQSXH; V_Ghxhs0 := 1; --取平均诊治时间 v_PJZZSJ := nvl(trim(:NEW.PJZZSJ),Floor((to_date(:NEW.ghmzsj||:NEW.ghjssj,'yyyymmddhh24:mi:ss')-to_date(:NEW.ghmzsj||:NEW.ghqssj,'yyyymmddhh24:mi:ss'))*24*60/GREATEST(:NEW.GHXHS0,1))); if v_COUNT1>0 then --删除未使用的旧数据 delete from YS_HBHZXH a where GHHBID=:new.GHHBID and BRID00 is null and not exists(select 1 from YS_YSPBXH where PBXH00=a.HZXH00 and YSPBID=ls_YSPBID); select count(*) into v_COUNT2 from YS_HBHZXH a where GHHBID=:new.GHHBID; for YS_YSPBXHDATA in (select PBXH00,YYBZ00,ZZGHBZ,ZSYYBZ from YS_YSPBXH a where YSPBID=ls_YSPBID and not exists(select 1 from YS_HBHZXH where HZXH00=a.PBXH00 and GHHBID=:new.GHHBID)) loop if V_Ghxhs0<=(:new.GHXHS0-v_COUNT2) then Vmessage:= SF_SF_HBHZSJ(:new.GHQSSJ,:new.GHJSSJ,least(:new.mrxhs0,:new.ghxhs0),V_Ghxhs0); insert into YS_HBHZXH (ID0000,GHHBID,HZXH00,YYBZ00,HZDXH0,HZSJ00,HZQSSJ,ZZGHBZ,ZSYYBZ) Values (SQ_YS_HBHZXH_ID0000.nextval,:new.GHHBID,YS_YSPBXHDATA.PBXH00,YS_YSPBXHDATA.YYBZ00,substrb(Vmessage,1,instrb(Vmessage,',')-1),substrb(Vmessage,instrb(Vmessage,',')+1,11), to_char(to_date(substr(:new.GHQSSJ,1,5),'hh24:mi')+(V_Ghxhs0-1)*to_number(v_PJZZSJ)/(24*60),'hh24:mi'), YS_YSPBXHDATA.ZZGHBZ,YS_YSPBXHDATA.ZSYYBZ); V_Ghxhs0:= V_Ghxhs0+1; end if; end loop; else while V_Ghxhs0<=:NEW.GHXHS0 loop Vmessage:= SF_SF_HBHZSJ(:new.GHQSSJ,:new.GHJSSJ,least(:new.mrxhs0,:new.ghxhs0),V_Ghxhs0); Insert into YS_HBHZXH (ID0000, GHHBID, HZXH00,HZDXH0,HZSJ00,HZQSSJ,ZZGHBZ,ZSYYBZ) Values (SQ_YS_HBHZXH_ID0000.nextval, :new.GHHBID, V_Ghxhs0+V_HZQSXH-1,substrb(Vmessage,1,instrb(Vmessage,',')-1),substrb(Vmessage,instrb(Vmessage,',')+1,11), to_char(to_date(substr(:new.GHQSSJ,1,5),'hh24:mi')+(V_Ghxhs0-1)*to_number(v_PJZZSJ)/(24*60),'hh24:mi'),'0' ,'0'); V_Ghxhs0:= V_Ghxhs0+1; end loop; end if; elsif Updating then--修改挂号限号数 V_HZQSXH := :NEW.HZQSXH; V_Ghxhs0 := :OLD.GHXHS0; --取平均诊治时间 v_PJZZSJ := nvl(trim(:NEW.PJZZSJ),Floor((to_date(:NEW.ghmzsj||:NEW.ghjssj,'yyyymmddhh24:mi:ss')-to_date(:NEW.ghmzsj||:NEW.ghqssj,'yyyymmddhh24:mi:ss'))*24*60/GREATEST(:NEW.GHXHS0,1))); if V_Ghxhs0<:NEW.GHXHS0 then--号改多 V_Ghxhs0 := :OLD.GHXHS0+1; if v_COUNT1>0 then delete from YS_HBHZXH a where GHHBID=:new.GHHBID and BRID00 is null and not exists(select 1 from YS_YSPBXH where PBXH00=a.HZXH00 and YSPBID=ls_YSPBID); select count(*) into v_COUNT2 from YS_HBHZXH where GHHBID=:new.GHHBID; V_Ghxhs0 :=v_COUNT2; for YSPBXHDATA in (select PBXH00,YYBZ00,ZZGHBZ,ZSYYBZ from YS_YSPBXH a where YSPBID=ls_YSPBID and not exists(select 1 from YS_HBHZXH where HZXH00=a.PBXH00 and GHHBID=:new.GHHBID)) loop if v_COUNT2<=:NEW.GHXHS0 then Vmessage:= SF_SF_HBHZSJ(:new.GHQSSJ,:new.GHJSSJ,least(:new.mrxhs0,:new.ghxhs0),:new.GHXHS0); insert into YS_HBHZXH (ID0000,GHHBID,HZXH00,YYBZ00,HZDXH0,HZSJ00,HZQSSJ,ZZGHBZ,ZSYYBZ) Values (SQ_YS_HBHZXH_ID0000.nextval,:new.GHHBID,YSPBXHDATA.PBXH00,YSPBXHDATA.YYBZ00,substrb(Vmessage,1,instrb(Vmessage,',')-1),substrb(Vmessage,instrb(Vmessage,',')+1,11), to_char(to_date(substr(:new.GHQSSJ,1,5),'hh24:mi')+(V_Ghxhs0-1)*to_number(v_PJZZSJ)/(24*60),'hh24:mi'),YSPBXHDATA.ZZGHBZ,YSPBXHDATA.ZSYYBZ ); V_Ghxhs0:= V_Ghxhs0+1; v_COUNT2:= v_COUNT2+1; end if; end loop; while V_Ghxhs0<=:NEW.GHXHS0 loop Vmessage:= SF_SF_HBHZSJ(:new.GHQSSJ,:new.GHJSSJ,least(:new.mrxhs0,:new.ghxhs0),:new.GHXHS0); Insert into YS_HBHZXH (ID0000, GHHBID, HZXH00,HZDXH0,HZSJ00,HZQSSJ,ZZGHBZ,ZSYYBZ) Values (SQ_YS_HBHZXH_ID0000.nextval, :new.GHHBID, V_Ghxhs0+V_HZQSXH-1,substrb(Vmessage,1,instrb(Vmessage,',')-1),substrb(Vmessage,instrb(Vmessage,',')+1,11), to_char(to_date(substr(:new.GHQSSJ,1,5),'hh24:mi')+(V_Ghxhs0-1)*to_number(v_PJZZSJ)/(24*60),'hh24:mi') ,'0','0'); V_Ghxhs0:= V_Ghxhs0+1; end loop; else while V_Ghxhs0<=:NEW.GHXHS0 loop Vmessage:= SF_SF_HBHZSJ(:new.GHQSSJ,:new.GHJSSJ,least(:new.mrxhs0,:new.ghxhs0),:new.GHXHS0); Insert into YS_HBHZXH (ID0000, GHHBID, HZXH00,HZDXH0,HZSJ00,HZQSSJ,ZZGHBZ,ZSYYBZ) Values (SQ_YS_HBHZXH_ID0000.nextval, :new.GHHBID, V_Ghxhs0+V_HZQSXH-1,substrb(Vmessage,1,instrb(Vmessage,',')-1),substrb(Vmessage,instrb(Vmessage,',')+1,11), to_char(to_date(substr(:new.GHQSSJ,1,5),'hh24:mi')+(V_Ghxhs0-1)*to_number(v_PJZZSJ)/(24*60),'hh24:mi') ,'0','0'); V_Ghxhs0:= V_Ghxhs0+1; end loop; end if; elsif V_Ghxhs0>:NEW.GHXHS0 then--号改少 -- while (V_Ghxhs0+1)>=:NEW.GHXHS0 loop if :new.SCSBYY='删除' then --泉二直接删除号源明细不能按下面的规则删除2017.06.07 return; end if; select count(1) into V_Count0 from YS_HBHZXH where GHHBID=:NEW.GHHBID and HZXH00>=V_HZQSXH+(:NEW.GHXHS0) and BRID00 is not null; if V_Count0>0 then raise E_Ghxhs0; end if; if v_COUNT1>0 then delete from YS_HBHZXH a where GHHBID=:new.GHHBID and BRID00 is null and not exists(select 1 from YS_YSPBXH where PBXH00=a.HZXH00 and YSPBID=ls_YSPBID); select count(*) into v_COUNT2 from YS_HBHZXH where GHHBID=:new.GHHBID; if v_COUNT2>:new.GHXHS0 then delete from YS_HBHZXH where GHHBID=:NEW.GHHBID and HZXH00 in (select HZXH00 from (select HZXH00,rownum from YS_HBHZXH where GHHBID=:NEW.GHHBID and BRID00 is null and rownum<=(v_COUNT2-:new.GHXHS0) order by HZXH00 desc)); elsif v_COUNT2<:new.GHXHS0 then v_COUNT2:=v_COUNT2+1; for YSPBXHDATA in (select PBXH00,YYBZ00,ZZGHBZ,ZSYYBZ from YS_YSPBXH a where YSPBID=ls_YSPBID and not exists(select 1 from YS_HBHZXH where HZXH00=a.PBXH00 and GHHBID=:new.GHHBID)) loop if v_COUNT2<=:NEW.GHXHS0 then Vmessage:= SF_SF_HBHZSJ(:new.GHQSSJ,:new.GHJSSJ,least(:new.mrxhs0,:new.ghxhs0),:new.GHXHS0); insert into YS_HBHZXH (ID0000,GHHBID,HZXH00,YYBZ00,HZDXH0,HZSJ00,HZQSSJ,ZZGHBZ,ZSYYBZ) Values (SQ_YS_HBHZXH_ID0000.nextval,:new.GHHBID,YSPBXHDATA.PBXH00,YSPBXHDATA.YYBZ00,substrb(Vmessage,1,instrb(Vmessage,',')-1),substrb(Vmessage,instrb(Vmessage,',')+1,11), to_char(to_date(substr(:new.GHQSSJ,1,5),'hh24:mi')+(V_Ghxhs0-1)*to_number(v_PJZZSJ)/(24*60),'hh24:mi'),YSPBXHDATA.ZZGHBZ,YSPBXHDATA.ZSYYBZ ); V_Ghxhs0:= V_Ghxhs0+1; v_COUNT2:=v_COUNT2+1; end if; end loop; end if; else Delete from YS_HBHZXH where GHHBID=:NEW.GHHBID and HZXH00>=V_HZQSXH+(:NEW.GHXHS0) and BRID00 is null; end if; --V_Ghxhs0:= V_Ghxhs0-1; -- end loop; end if; --修改平均诊治时间 if nvl(:NEW.PJZZSJ,' ')<>nvl(:OLD.PJZZSJ,' ') or nvl(:NEW.DHYY02,0)<>nvl(:OLD.DHYY02,0) then update YS_HBHZXH set HZQSSJ=to_char(to_date(substr(:new.GHQSSJ,1,5),'hh24:mi')+(HZXH00-nvl(V_HZQSXH,1))*to_number(v_PJZZSJ)/(24*60),'hh24:mi') where GHHBID=:NEW.GHHBID and BRID00 is null; end if; else--删除号表记录 Delete from YS_HBHZXH where GHHBID=:OLD.GHHBID; end if; end if; --省二等医院要求预约号数不能多于总号数的一半 if Cs_Ys_Xzygh0='Y' or ls_YSPBSFHYWH='Y' then --加or ls_YSPBSFHYWH='Y'是因为泉二后面才启用参数但是有的排班YS_YSPBXH没有生成导致YS_HBHZXH.YYBZ00不对所以加YYGH-20160630-001 select nvl(max(trim(VALUE0)),'N') into MZHS_YYXHSKGBZHS from XT_XTCS00 where name00='MZHS_YYXHSKGBZHS' and VALUE0='Y'; -- if updating OR inserting then if (updating and (:new.GHXHS0<>:old.GHXHS0 or :new.YYXHS0<>:old.YYXHS0 or :new.DHYYS0<>:old.DHYYS0 or :new.FZYYS0<>:old.FZYYS0 or :new.WLYYS0<>:old.WLYYS0 or :new.DHYY02<>:old.DHYY02 or :new.YYXHMX<>:old.YYXHMX or :new.DHYYMX<>:old.DHYYMX or :new.FZYYMX<>:old.FZYYMX or :new.WLYYMX<>:old.WLYYMX or :new.DHMX02<>:old.DHMX02) ) OR inserting then V_Yyxhs0 := :NEW.YYXHS0; if MZHS_YYXHSKGBZHS='N' and (V_Yyxhs0*2)> :NEW.GHXHS0 then Raise E_Yyxhs0; end if; --if MZHS_YYXHSKGBZHS='Y' and (V_Yyxhs0)> :NEW.GHXHS0 then -- Raise E_Yyxhs0; --end if; if v_COUNT1=0 then update YS_HBHZXH set YYBZ00='1' where GHHBID=:new.GHHBID and BRXM00 is null;-- and YYBZ00='3'; if MZHS_YYXHSKGBZHS='N' then --不限过半时,不生成YYBZ00=3的数据 while V_Yyxhs0>0 loop Update YS_HBHZXH set YYBZ00='3' where GHHBID=:new.GHHBID and HZXH00=(:NEW.HZQSXH-1)+(V_Yyxhs0*2) and BRXM00 is null; V_Yyxhs0:= V_Yyxhs0-1; end loop; end if; --处理分诊预约数 if :NEW.FZYYS0>0 and substr(:new.FZYYMX,-1)=','then --v_FZYYMX:=:new.FZYYMX; v_FZYYMX:=substr(:new.FZYYMX,1,length(:new.FZYYMX)-1); SP_EXECUTE_SQL('update YS_HBHZXH SET YYBZ00=''5'' where GHHBID='||:new.GHHBID||' and HZXH00 in ('||v_FZYYMX||')'); elsif :NEW.FZYYS0>0 and substr(:new.FZYYMX,-1)<>','then v_FZYYMX:=substr(:new.FZYYMX,1,length(:new.FZYYMX)); SP_EXECUTE_SQL('update YS_HBHZXH SET YYBZ00=''5'' where GHHBID='||:new.GHHBID||' and HZXH00 in ('||v_FZYYMX||')'); end if; --处理电话预约数 if :NEW.DHYYS0>0 and substr(:new.DHYYMX,-1)=',' then v_DHYYMX:=substr(:new.DHYYMX,1,length(:new.DHYYMX)-1); SP_EXECUTE_SQL('update YS_HBHZXH SET YYBZ00=''4'' where GHHBID='||:new.GHHBID||' and HZXH00 in ('||v_DHYYMX||')'); elsif :NEW.DHYYS0>0 and substr(:new.DHYYMX,-1) <>',' then v_DHYYMX:=substr(:new.DHYYMX,1,length(:new.DHYYMX)); SP_EXECUTE_SQL('update YS_HBHZXH SET YYBZ00=''4'' where GHHBID='||:new.GHHBID||' and HZXH00 in ('||v_DHYYMX||')'); end if; --处理网络预约数 if :NEW.WLYYS0>0 and substr(:new.WLYYMX,-1)=',' then v_WLYYMX:=substr(:new.WLYYMX,1,length(:new.WLYYMX)-1); SP_EXECUTE_SQL('update YS_HBHZXH SET YYBZ00=''6'' where GHHBID='||:new.GHHBID||' and HZXH00 in ('||v_WLYYMX||')'); elsif :NEW.WLYYS0>0 and substr(:new.WLYYMX,-1)<>',' then v_WLYYMX:=substr(:new.WLYYMX,1,length(:new.WLYYMX)); SP_EXECUTE_SQL('update YS_HBHZXH SET YYBZ00=''6'' where GHHBID='||:new.GHHBID||' and HZXH00 in ('||v_WLYYMX||')'); end if; --处理网络预约数(12320) if :NEW.DHYY02>0 and substr(:new.DHMX02,-1)=',' then v_WLYYMX:=substr(:new.DHMX02,1,length(:new.DHMX02)-1); SP_EXECUTE_SQL('update YS_HBHZXH SET YYBZ00=''6'' where GHHBID='||:new.GHHBID||' and HZXH00 in ('||v_WLYYMX||')'); elsif :NEW.DHYY02>0 and substr(:new.DHMX02,-1)<>',' then v_WLYYMX:=substr(:new.DHMX02,1,length(:new.DHMX02)); SP_EXECUTE_SQL('update YS_HBHZXH SET YYBZ00=''6'' where GHHBID='||:new.GHHBID||' and HZXH00 in ('||v_WLYYMX||')'); end if; --增加保留号处理 if substr(:new.YNBLMX,-1)=',' then v_YNBLMX:=substr(:new.YNBLMX,1,length(:new.YNBLMX)-1); SP_EXECUTE_SQL('update YS_HBHZXH SET YYBZ00=''10'' where GHHBID='||:new.GHHBID||' and HZXH00 in ('||v_YNBLMX||')'); elsif substr(:new.YNBLMX,-1)<>',' then v_YNBLMX:=substr(:new.YNBLMX,1,length(:new.YNBLMX)); SP_EXECUTE_SQL('update YS_HBHZXH SET YYBZ00=''10'' where GHHBID='||:new.GHHBID||' and HZXH00 in ('||v_YNBLMX||')'); end if; --如果 end if; end if; end if; --=====================以下是广东省中院要求按时间段进行挂号的处理===============================-- --生成号表时，填写SF_HBHZXH的数据。 --未开始挂号，修改限号数的，重新填写SF_HBHZXH的数据 --未开始挂号，修改预约限号数或限号的，重新修正SF_HBHZXH的预约信息(开始挂号的，不让修改预约限号数) --正式挂号后，进行加号的 select upper(trim(nvl(max(VALUE0),'N'))) into Cs_Ys_Xzygh0 from XT_XTCS00 where NAME00='YS_SFYXPTBRTQGH'; if Cs_Ys_Xzygh0='M' then --广东中院 --if (updating and :new.GHYGHS=0 and (:new.GHXHS0<>:old.GHXHS0 or nvl(:old.HZXHJS,-1)<>:new.HZXHJS)) then if inserting or (updating and :new.GHYGHS=0 and nvl(:new.YYSL00,0)=0 and (:new.GHXHS0<>:old.GHXHS0)) then delete from YS_HBHZXH where GHHBID=:new.GHHBID; --取候诊序号基数 Vyzdycd:=Floor((to_date(:new.ghmzsj||:new.ghjssj,'yyyymmddhh24:mi:ss')-to_date(:new.ghmzsj||:new.ghqssj,'yyyymmddhh24:mi:ss'))*24*60); Vhzdcd0:=60; Vhzdnum:=ceil(round(Vyzdycd/Vhzdcd0,2));--候诊段个数 Vhzdrs0:=ceil(round(:new.ghxhs0/Vhzdnum,2));--每个候诊段默认挂号人数 for i in 1..:new.GHXHS0 loop V_Count0:=ceil(i/Vhzdrs0); --候诊段序号 --取候诊段的起始和截至时间 Vhzqssj:=to_date(:new.ghmzsj||:new.ghqssj,'YYYYMMDDHH24:MI:SS')+Vhzdcd0*(V_Count0-1)/(60*24); Vhzjzsj:=to_date(:new.ghmzsj||:new.ghqssj,'YYYYMMDDHH24:MI:SS')+Vhzdcd0*V_Count0/(60*24); Vhzjzsj:=least(Vhzjzsj,to_date(:new.ghmzsj||:new.ghjssj,'YYYYMMDDHH24:MI:SS')); insert into YS_HBHZXH (ID0000,GHHBID,HZXH00,YYBZ00,HZSJ00,YYRQSJ,SFZJ00,SJHZXH,HZDXH0,YYID00) Values (SQ_YS_HBHZXH_ID0000.nextVal,:new.GHHBID,i+nvl(:new.HZXHJS,0),'1',to_char(Vhzqssj,'HH24:MI')||'-'||to_char(Vhzjzsj,'HH24:MI'),null,'0',null,V_Count0,null); end loop; end if; if updating and :new.GHYGHS=0 and nvl(:old.HZXHJS,0)<>nvl(:new.HZXHJS,0) then update YS_HBHZXH set HZXH00=HZXH00+nvl(:new.HZXHJS,0)-nvl(:old.HZXHJS,0) where GHHBID=:new.GHHBID; end if; if inserting or (updating and :new.GHYGHS=0 and nvl(:new.YYSL00,0)=0 and ((:new.GHXHS0<>:old.GHXHS0) or (:new.YYXHMX<>:old.YYXHMX) or (:new.YYXHS0<>:old.YYXHS0) or (:old.DHYYMX<>:new.DHYYMX) or (:old.FZYYMX<>:new.FZYYMX))) then Vyzdycd:=Floor((to_date(:new.ghmzsj||:new.ghjssj,'yyyymmddhh24:mi:ss')-to_date(:new.ghmzsj||:new.ghqssj,'yyyymmddhh24:mi:ss'))*24*60); Vhzdcd0:=60; Vhzdnum:=ceil(round(Vyzdycd/Vhzdcd0,2));--候诊段个数 Vhzdrs0:=ceil(round(:new.ghxhs0/Vhzdnum,2));--每个候诊段默认挂号人数 update YS_HBHZXH set YYBZ00='1',BRID00=NULL,SJHZXH=NULL where GHHBID=:new.GHHBID and YYBZ00<>'7'; SP_EXECUTE_SQL('update YS_HBHZXH set YYBZ00=''3'' where GHHBID='||to_char(:new.GHHBID)||' and ID0000 in (select ID0000 from '|| '(select ID0000,HZDXH0,rank()over(partition by HZDXH0 order by HZXH00) Rank00 '|| 'from YS_HBHZXH a where GHHBID='||to_char(:new.GHHBID)||' ) where RANK00<=nvl(SF_YS_HZDYYS(HZDXH0,'''||:new.YYXHMX||'''),0))'); --update YS_HBHZXH a set SJHZXH=(select count(*) from YS_HBHZXH where GHHBID=:new.GHHBID and YYBZ00='3' and HZXH00<=a.HZXH00) where GHHBID=:new.GHHBID and YYBZ00='3'; --修正预约优待标志'1'普通'2'优待'3'院外预约'4'院内电话预约'5'分诊预约,'6' 网络预约'7'当天预约 if :new.DHYYS0>0 then SP_EXECUTE_SQL('update YS_HBHZXH set YYBZ00=''4'' where GHHBID='||to_char(:new.GHHBID)||' and ID0000 in (select ID0000 from '|| '(select ID0000,HZDXH0,rank()over(partition by HZDXH0 order by HZXH00) Rank00 '|| 'from YS_HBHZXH a where GHHBID='||to_char(:new.GHHBID)||' and YYBZ00=''3'') where RANK00<=nvl(SF_YS_HZDYYS(HZDXH0,'''||:new.DHYYMX||'''),0))'); end if; if :new.FZYYS0>0 then SP_EXECUTE_SQL('update YS_HBHZXH set YYBZ00=''5'' where GHHBID='||to_char(:new.GHHBID)||' and ID0000 in (select ID0000 from '|| '(select ID0000,HZDXH0,rank()over(partition by HZDXH0 order by HZXH00) Rank00 '|| 'from YS_HBHZXH a where GHHBID='||to_char(:new.GHHBID)||' and YYBZ00=''3'') where RANK00<=nvl(SF_YS_HZDYYS(HZDXH0,'''||:new.FZYYMX||'''),0))'); end if; if :new.WLYYS0>0 then SP_EXECUTE_SQL('update YS_HBHZXH set YYBZ00=''6'' where GHHBID='||to_char(:new.GHHBID)||' and ID0000 in (select ID0000 from '|| '(select ID0000,HZDXH0,rank()over(partition by HZDXH0 order by HZXH00) Rank00 '|| 'from YS_HBHZXH a where GHHBID='||to_char(:new.GHHBID)||' and YYBZ00=''3'') where RANK00<=nvl(SF_YS_HZDYYS(HZDXH0,'''||:new.WLYYMX||'''),0))'); end if; end if; end if; if inserting or updating then update YS_HBHZXH set HZQSSJ=substr(:new.GHJSSJ,1,5) where GHHBID=:new.GHHBID and :new.GHMZSJ||HZQSSJ>:new.GHMZSJ||substr(:new.GHJSSJ,1,5); end if; exception when E_Ghxhs0 then raise_application_error(-20281, '修改挂号号表ID'||:NEW.GHHBID||'的挂号号表失败,原因:挂号限号数已经超过了已经挂的病人数!*'); rollback; when E_Yyxhs0 then raise_application_error(-20281, '修改或增加挂号号表ID'||:NEW.GHHBID||'的挂号号表失败,原因:挂号限号数至少应该是预约限号数的两倍!*'); rollback; When no_data_found then raise_application_error(-20010,'数据没有找到,'||errStr||'！*'); when others then raise_application_error(-20020, nvl(sqlerrm, '原因不明出错！*')); End TR_SF_YSGHHB_BEFINSAPDEL; /