CREATE OR REPLACE PROCEDURE SP_YJ_JZLGRBB ( AS_KSRQ00 in char, --开始日期 AS_JSRQ00 in char, --结束日期 AS_CZY000 in number, --操作员ID AS_YHMSG0 out varchar, --存储过程提示的错误信息 AS_SYSMSG out varchar, --系统提示的错误信息 AS_BMBH00 in number default -1 --科室 ) as ls_TJRQ00 YJ_JZRBB0.RQ0000%TYPE; --统计日期 ls_COUNT0 number(5) ; --计数器 ls_LGSYCW number(5) ; --观察室实有床位数 常量 ls_JZKBMBH BM_BMBM00.BMBH00%TYPE; --急诊科部门编号 E_NotEnoughBL exception; --错误变量不够 E_BLWrong exception; --变量定义错误 E_YZBFCWDJCW exception; --优质病房床位定义错误 -- MODIFICATION HISTORY -- Person Date Comments -- zhangwz 2011.02.11 created; by YJ-20110211-001 -- zhangwz 2011.03.10 根据需求对检索条件进行修改 by YJ-20110302-002. -- zhangyc 2012.03.01 by YJ-20120220-001 数据只提取一天 -- dsm 2012.05.23 如果BM_LGCW00 有维护则从字典表区床位数否则为'5' for YJ-20120508-001 -- jinfl 2013.01.25 入院人次改为由急诊科申请,即为急诊科入院的人次,而非挂急诊号 YJ-20130121-001 -- laijg 2013.03.07 急诊科日报表现在只统计急诊科数据，现增加入参AS_BMBH00，按选择科室统计 by YJ-20130218-001 -- liuj 2013.10.23 急诊科日报表数据改为统计所选科室挂号数据 for YJ-20131008-001 ------------ ----------- --------------------------------------------------------------------- begin ls_TJRQ00:=as_KSRQ00; if AS_BMBH00=-1 then begin select BMBH00 into ls_JZKBMBH from BM_BMBM00 where BMMC00 = '急诊科' and CZBZ00='1' and rownum=1; exception when others then ls_JZKBMBH:=0; end; else ls_JZKBMBH:=AS_BMBH00; end if; select count(*) into ls_LGSYCW from BM_LGCW00 where BQH000=ls_JZKBMBH or BQH000=0; if ls_LGSYCW=0 then ls_LGSYCW:=5; end if; while (ls_TJRQ00<=as_JSRQ00) and (ls_TJRQ00<=to_char(sysdate-1,'YYYYMMDD')) loop select count(BMBH00) into ls_COUNT0 from YJ_JZRBB0 where RQ0000=ls_TJRQ00 and BMBH00=ls_JZKBMBH; if ls_COUNT0=0 then begin insert into YJ_JZRBB0(RQ0000,BMBH00,CZY000,GCSYCS) values (ls_TJRQ00,ls_JZKBMBH,as_CZY000,ls_LGSYCW); --------------------------急诊数据整理-------------------------------- select count(a.GHID00) into ls_COUNT0 from SF_BRXXB0 a,BM_GHLBB0 b where a.GHRQ00=ls_TJRQ00 and a.GHKS00=ls_JZKBMBH and a.GHLB00=b.LBBH00 and b.LBMC00 like '%急诊%' and thrq00 is null; --急诊人次 update YJ_JZRBB0 set JZRC00=ls_COUNT0 where BMBH00=ls_JZKBMBH and RQ0000=ls_TJRQ00; select nvl(sum(sign(b.SL0000)),0) into ls_COUNT0 from SF_BRXXB0 a,YJ_YW0000 b where a.GHRQ00=ls_TJRQ00 and a.GHKS00=ls_JZKBMBH and a.GHID00=b.ZYGHID and b.ZLXMJC like '%救护车%'; --救护车次 update YJ_JZRBB0 set CCCS00=ls_COUNT0 where BMBH00=ls_JZKBMBH and RQ0000=ls_TJRQ00; select nvl(sum(sign(b.SL0000)),0) into ls_COUNT0 from SF_BRXXB0 a,YJ_YW0000 b where a.GHRQ00=ls_TJRQ00 and a.GHKS00=ls_JZKBMBH and a.GHID00=b.ZYGHID and b.ZLXMJC like '%抢救费%'; --抢救人次 update YJ_JZRBB0 set QJRS00=ls_COUNT0 where BMBH00=ls_JZKBMBH and RQ0000=ls_TJRQ00; select count(distinct a.GHID00) into ls_COUNT0 from SF_BRXXB0 a,YS_BRZDXX b where a.GHRQ00=ls_TJRQ00 and a.GHKS00=ls_JZKBMBH and a.GHID00=b.GHID00 and b.ZDMC00 like '%死亡%'; --死亡人次 update YJ_JZRBB0 set SWRS00=ls_COUNT0 where BMBH00=ls_JZKBMBH and RQ0000=ls_TJRQ00; select count(distinct a.GHID00) into ls_COUNT0 from SF_BRXXB0 a,YS_BRZDXX b where a.GHRQ00=ls_TJRQ00 and a.GHID00=b.GHID00 and a.GHKS00=ls_JZKBMBH and ((substr(b.ICD900,1,1)='S') or (substr(b.ICD900,1,3) between 'T01' and 'T07')); --外伤人次 update YJ_JZRBB0 set WSRS00=ls_COUNT0 where BMBH00=ls_JZKBMBH and RQ0000=ls_TJRQ00; select count(distinct a.GHID00) into ls_COUNT0 from SF_BRXXB0 a,YS_BRZDXX b where a.GHRQ00=ls_TJRQ00 and a.GHID00=b.GHID00 and a.GHKS00=ls_JZKBMBH and (substr(b.ICD900,1,3) between 'T36' and 'T65'); --中毒人次 update YJ_JZRBB0 set ZDRS00=ls_COUNT0 where BMBH00=ls_JZKBMBH and RQ0000=ls_TJRQ00; select count(a.GHID00) into ls_COUNT0 from SF_BRXXB0 a,BM_GHLBB0 b where a.GHRQ00=ls_TJRQ00 and a.GHLB00=b.LBBH00 and a.GHKS00=ls_JZKBMBH and b.LBMC00 like '%急诊%' and exists (select 1 from YS_LGBRDJ where GHID00=a.GHID00 and SFYX00='Y'); --留观人次 update YJ_JZRBB0 set RGRS00=ls_COUNT0 where BMBH00=ls_JZKBMBH and RQ0000=ls_TJRQ00; --select count(a.GHID00) into ls_COUNT0 from SF_BRXXB0 a,BM_GHLBB0 b where a.GHRQ00=ls_TJRQ00 and a.GHLB00=b.LBBH00 and b.LBMC00 like '%急诊%' -- and exists (select 1 from ZY_BRXXB0 where BRID00=a.BRID00 and RYRQ00=ls_TJRQ00 and BRZT00<>'0' and BRZT00<>'9'); --住院人次 select count(1) into ls_COUNT0 from YS_BRZYSQ a where SQKS00=ls_JZKBMBH and exists (select 1 from ZY_BRXXB0 where BRID00=a.BRID00 and SZYS00=a.YSGZH0 and SJRYRQ=ls_TJRQ00); update YJ_JZRBB0 set RYRS00=ls_COUNT0 where BMBH00=ls_JZKBMBH and RQ0000=ls_TJRQ00; update YJ_JZRBB0 set CHRS00=greatest(QJRS00-SWRS00,0),LYRS00=greatest(JZRC00-RYRS00-RGRS00,0) where BMBH00=ls_JZKBMBH and RQ0000=ls_TJRQ00; --成活人次 , 离院人次 ------------观察室数据整理---------------------------- -- select count(distinct a.GHID00) into ls_COUNT0 from YS_LGBRDJ a where a.KSRQ00=ls_TJRQ00 or a.JSRQ00='20991231') and a.SFYX00='Y'; --原有人次 ---------留观人数(昨日)=今天实有人数-------------- begin select nvl(GCLGRS,0) into ls_COUNT0 from YJ_JZRBB0 where BMBH00=ls_JZKBMBH and RQ0000=to_char(to_date(ls_TJRQ00,'YYYYMMDD')-1,'YYYYMMDD'); exception when others then ls_COUNT0:='0'; end; update YJ_JZRBB0 set GCYYRS=ls_COUNT0 where BMBH00=ls_JZKBMBH and RQ0000=ls_TJRQ00; select count(distinct a.GHID00) into ls_COUNT0 from YS_LGBRDJ a where a.KSRQ00=ls_TJRQ00 and a.SFYX00='Y' and exists(select 1 from SF_BRXXB0 where ghid00=a.ghid00 and GHKS00=ls_JZKBMBH); --入观人次 update YJ_JZRBB0 set GCRGRS=ls_COUNT0 where BMBH00=ls_JZKBMBH and RQ0000=ls_TJRQ00; select count(distinct a.GHID00) into ls_COUNT0 from YS_LGBRDJ a where a.JSRQ00=ls_TJRQ00 and a.SFYX00='Y' and exists(select 1 from SF_BRXXB0 where ghid00=a.ghid00 and GHKS00=ls_JZKBMBH); --出观人次 含病人入院数据 update YJ_JZRBB0 set GCCYRS=ls_COUNT0 where BMBH00=ls_JZKBMBH and RQ0000=ls_TJRQ00; select count(distinct a.GHID00) into ls_COUNT0 from YS_LGBRDJ a where a.KSRQ00<=ls_TJRQ00 and (a.JSRQ00>ls_TJRQ00 or a.JSRQ00 is null) and a.SFYX00='Y' and exists(select 1 from SF_BRXXB0 where ghid00=a.ghid00 and GHKS00=ls_JZKBMBH); --留观人次 update YJ_JZRBB0 set GCLGRS=ls_COUNT0 where BMBH00=ls_JZKBMBH and RQ0000=ls_TJRQ00; select count(distinct a.ZYID00) into ls_COUNT0 from ZY_BRXXB0 a where a.RYRQ00=ls_TJRQ00 and a.BRZT00<>'0' and a.BRZT00<>'9' and exists (select 1 from YS_LGBRDJ d where d.BRID00=a.BRID00 and d.SFYX00='Y' and d.KSRQ00<=ls_TJRQ00 and exists(select 1 from SF_BRXXB0 where ghid00=d.ghid00 and GHKS00=ls_JZKBMBH) and (d.JSRQ00<=ls_TJRQ00 or d.JSRQ00='20991231' or d.JSRQ00 is null)); --留观入院人次 update YJ_JZRBB0 set GCRYRS=ls_COUNT0 where BMBH00=ls_JZKBMBH and RQ0000=ls_TJRQ00; select count(distinct a.GHID00) into ls_COUNT0 from YS_LGBRDJ a,YS_BRZDXX b where a.KSRQ00<=ls_TJRQ00 and a.JSRQ00>=ls_TJRQ00 and a.SFYX00='Y' and a.GHID00=b.GHID00 and exists(select 1 from SF_BRXXB0 where ghid00=a.ghid00 and GHKS00=ls_JZKBMBH) and b.ZDMC00 like '%死亡%'; --死亡人次 update YJ_JZRBB0 set GCSWRS=ls_COUNT0 where BMBH00=ls_JZKBMBH and RQ0000=ls_TJRQ00; update YJ_JZRBB0 set GCCYRS=GCCYRS-GCRYRS where BMBH00=ls_JZKBMBH and RQ0000=ls_TJRQ00; --出院数 select nvl(sum(to_date(JSRQ00,'YYYYMMDD')-to_date(KSRQ00,'YYYYMMDD')+1),0) into ls_COUNT0 from YS_LGBRDJ a where a.JSRQ00=ls_TJRQ00 and a.SFYX00='Y' and exists(select 1 from SF_BRXXB0 where ghid00=a.ghid00 and GHKS00=ls_JZKBMBH) ; --出观病人占用床日数 update YJ_JZRBB0 set GCZCRS=ls_COUNT0 where BMBH00=ls_JZKBMBH and RQ0000=ls_TJRQ00; end; end if; ----------------------------------------------------------------------------------------------------------------------------------------- ls_TJRQ00:=to_char(to_date(ls_TJRQ00,'YYYYMMDD')+1,'YYYYMMDD'); end loop; --前台来提交事务 --commit; exception when others then as_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！'; as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_YJ_JZLGRBB('||as_KSRQ00||','||as_JSRQ00||','||'ad_ID0000,as_YHMSG0,as_SYSMSG',1,150); rollback; end;