PROMPT 莆田涵江医院全院病室工作逐日报表 SP_BQ_GZRBB0 create or replace procedure SP_BQ_GZRBB0 ( as_KSRQ00 in char, --开始日期 as_JSRQ00 in char, --结束日期 as_CZY000 in number, --操作员ID as_BQBH00 in number, --病区编码 as_YHMSG0 out varchar, --存储过程提示的错误信息 as_SYSMSG out varchar --系统提示的错误信息 ) as -- MODIFICATION HISTORY -- Person Date Comments -- zhangyc 2011.06.16 create by BQ-20110614-001 -- zhangyc 2011.06.27 修正入院人数统计错误。 -- zhangyc 2011.08.12 BQ-20110809-002 如果前天有数据实有人数==原有人数+入院人数+转入人数-出院-其它出院人数-转入他科人数 -- laijg 2011.08.25 BQ-20110824-003 全院病案工作逐日报表中数据提取出来的数据会出现部分病区‘转入’人数与【查询-病区病人流动查询】窗口中的数据不一致 -- dsm 2014.11.11 实有人数不能再减其他出院人数(出院人数已经减过了) for BQ-20141110-003 -- xgm 2020.08.05 出院者占总床日数算法改成SJCYRQ-SJRYRQ(非RYRQ00) for BQ-20200805-001 ls_TJRQ00 BQ_GZRBB0.RQ0000%TYPE; --统计日期 ls_TJRQ01 BQ_GZRBB0.RQ0000%TYPE; --统计日期 ls_COUNT0 number(5); ls_COUNT1 number(5); --计数器 begin ls_TJRQ00:=as_KSRQ00; ls_TJRQ01:=to_char(to_date(ls_TJRQ00,'YYYYMMDD')-1,'YYYYMMDD'); --while (ls_TJRQ00<=as_JSRQ00) loop select count(BMBH00) into ls_COUNT1 from BQ_GZRBB0 where RQ0000=ls_TJRQ01 and BMBH00=as_BQBH00; -- if ls_COUNT0=0 then begin insert into BQ_GZRBB0(RQ0000,BMBH00,CZY000) values (ls_TJRQ00,as_BQBH00,as_CZY000); --------------------------数据整理-------------------------------- ---------实有病人数(单日)-------------- select count( distinct ZYID00) into ls_COUNT0 from BQ_BRLDXX where QSZT00 in ('新入','转入') and YXBZ00='1' and QSRQ00<=ls_TJRQ00 and BQJSRQ >ls_TJRQ00 and BQH000=as_BQBH00; update BQ_GZRBB0 set SYBRC0=ls_COUNT0 where BMBH00=as_BQBH00 and RQ0000=ls_TJRQ00; ---------原有病人数(昨日)-------------- begin select nvl(SYBRC0,0) into ls_COUNT0 from BQ_GZRBB0 where BMBH00=as_BQBH00 and RQ0000=to_char(to_date(ls_TJRQ00,'YYYYMMDD')-1,'YYYYMMDD'); exception when others then ls_COUNT0:='0'; end; update BQ_GZRBB0 set YYBRC0=ls_COUNT0 where BMBH00=as_BQBH00 and RQ0000=ls_TJRQ00; ---------总入院数-------------- SELECT COUNT (DISTINCT a.ZYID00) into ls_COUNT0 FROM BQ_BRLDXX a,zy_brxxb0 b WHERE qszt00 in ('新入') and a.yxbz00='1' and a.QSRQ00=ls_TJRQ00 and a.BQH000=as_BQBH00 and a.zyid00=b.zyid00 and (upper(substr(b.zyh000,1,1)) not in ('F','M')); update BQ_GZRBB0 set RYS000=ls_COUNT0 where BMBH00=as_BQBH00 and RQ0000=ls_TJRQ00; ---------转入人数-------------- SELECT COUNT ( a.ZYID00) into ls_COUNT0 FROM BQ_BRLDXX a,zy_brxxb0 b WHERE QSZT00 in ('转入') AND a.BQH000=as_BQBH00 and yxbz00='1' and QSRQ00 =ls_TJRQ00 and a.zyid00=b.zyid00 and (upper(substr(b.zyh000,1,1)) not in ('F','M')); update BQ_GZRBB0 set ZRRS00=ls_COUNT0 where BMBH00=as_BQBH00 and RQ0000=ls_TJRQ00; ---------出院人数(合计)-------------- select COUNT (DISTINCT a.ZYID00) into ls_COUNT0 FROM BQ_BRLDXX a,zy_brxxb0 b WHERE JSZT00='出院' AND yxbz00='1' and a.JSRQ00=ls_TJRQ00 and a.BQH000=as_BQBH00 and a.zyid00=b.zyid00 and (upper(substr(b.zyh000,1,1)) not in ('F','M')); update BQ_GZRBB0 set CRRSHJ=ls_COUNT0 where BMBH00=as_BQBH00 and RQ0000=ls_TJRQ00; ---------出院人数(治愈)-------------- select COUNT (DISTINCT a.ZYID00) into ls_COUNT0 FROM BQ_BRLDXX a,zy_brxxb0 b WHERE JSZT00='出院' AND yxbz00='1' and a.JSRQ00=ls_TJRQ00 and a.BQH000=as_BQBH00 and b.RCYFH0 like '%治愈%' and a.zyid00=b.zyid00 and (upper(substr(b.zyh000,1,1)) not in ('F','M')); update BQ_GZRBB0 set CRRSZY=ls_COUNT0 where BMBH00=as_BQBH00 and RQ0000=ls_TJRQ00; ---------出院人数(好转)-------------- select COUNT (DISTINCT a.ZYID00) into ls_COUNT0 FROM BQ_BRLDXX a,zy_brxxb0 b WHERE JSZT00='出院' AND yxbz00='1' and a.JSRQ00=ls_TJRQ00 and a.BQH000=as_BQBH00 and b.RCYFH0 like '%好转%' and a.zyid00=b.zyid00 and (upper(substr(b.zyh000,1,1)) not in ('F','M')); update BQ_GZRBB0 set CRRSHZ=ls_COUNT0 where BMBH00=as_BQBH00 and RQ0000=ls_TJRQ00; ---------出院人数(未愈)-------------- select COUNT (DISTINCT a.ZYID00) into ls_COUNT0 FROM BQ_BRLDXX a,zy_brxxb0 b WHERE JSZT00='出院' AND yxbz00='1' and a.JSRQ00=ls_TJRQ00 and a.BQH000=as_BQBH00 and b.RCYFH0 like '%未愈%' and a.zyid00=b.zyid00 and (upper(substr(b.zyh000,1,1)) not in ('F','M')); update BQ_GZRBB0 set CRRSWY=ls_COUNT0 where BMBH00=as_BQBH00 and RQ0000=ls_TJRQ00; ---------出院人数(死亡)------------- select COUNT (DISTINCT a.ZYID00) into ls_COUNT0 FROM BQ_BRLDXX a,zy_brxxb0 b WHERE JSZT00='出院' AND yxbz00='1' and a.JSRQ00=ls_TJRQ00 and a.BQH000=as_BQBH00 and b.RCYFH0 like '%死亡%' and a.zyid00=b.zyid00 and (upper(substr(b.zyh000,1,1)) not in ('F','M')); update BQ_GZRBB0 set CRRSSW=ls_COUNT0 where BMBH00=as_BQBH00 and RQ0000=ls_TJRQ00; ---------出院人数(24小时内死亡)------------- select COUNT (DISTINCT a.ZYID00) into ls_COUNT0 FROM BQ_BRLDXX a,zy_brxxb0 b WHERE JSZT00='出院' AND yxbz00='1' and a.JSRQ00=ls_TJRQ00 and a.BQH000=as_BQBH00 and b.RCYFH0 like '%24小时死亡%' and a.zyid00=b.zyid00 and (upper(substr(b.zyh000,1,1)) not in ('F','M')); update BQ_GZRBB0 set CRRS24=ls_COUNT0 where BMBH00=as_BQBH00 and RQ0000=ls_TJRQ00; ---------自动出院------------- select COUNT (DISTINCT a.ZYID00) into ls_COUNT0 FROM BQ_BRLDXX a,zy_brxxb0 b WHERE JSZT00='出院' AND yxbz00='1' and a.JSRQ00=ls_TJRQ00 and a.BQH000=as_BQBH00 and b.RCYFH0 like '%自动出院%' and a.zyid00=b.zyid00 and (upper(substr(b.zyh000,1,1)) not in ('F','M')); update BQ_GZRBB0 set ZDCYRS=ls_COUNT0 where BMBH00=as_BQBH00 and RQ0000=ls_TJRQ00; ---------分娩人数------------- select COUNT (DISTINCT a.ZYID00) into ls_COUNT0 FROM BQ_BRLDXX a,zy_brxxb0 b WHERE JSZT00='出院' AND yxbz00='1' and a.JSRQ00=ls_TJRQ00 and a.BQH000=as_BQBH00 and b.RCYFH0 like '%分娩%' and a.zyid00=b.zyid00 and (upper(substr(b.zyh000,1,1)) not in ('F','M')); update BQ_GZRBB0 set FMRS00=ls_COUNT0 where BMBH00=as_BQBH00 and RQ0000=ls_TJRQ00; ---------计划生育------------- update BQ_GZRBB0 set JHSYRS=ls_COUNT0 where BMBH00=as_BQBH00 and RQ0000=ls_TJRQ00; ---------其它出院人数------------- select COUNT (DISTINCT a.ZYID00) into ls_COUNT0 FROM BQ_BRLDXX a,zy_brxxb0 b WHERE JSZT00='出院' AND yxbz00='1' and a.JSRQ00=ls_TJRQ00 and a.BQH000=as_BQBH00 and b.RCYFH0 like '%其他%' and a.zyid00=b.zyid00 and (upper(substr(b.zyh000,1,1)) not in ('F','M')); update BQ_GZRBB0 set CYRSQT=ls_COUNT0 where BMBH00=as_BQBH00 and RQ0000=ls_TJRQ00; ---------转入他科数(转出)------------- SELECT COUNT ( a.ZYID00) into ls_COUNT0 FROM BQ_BRLDXX a,zy_brxxb0 b WHERE JSZT00='转出' AND a.BQH000=as_BQBH00 and yxbz00='1' AND JSRQ00=ls_TJRQ00 and a.zyid00=b.zyid00 and (upper(substr(b.zyh000,1,1)) not in ('F','M')); update BQ_GZRBB0 set ZCRS00=ls_COUNT0 where BMBH00=as_BQBH00 and RQ0000=ls_TJRQ00; ---------出院者占总床日数------------- -- xgm 2020.08.05 for BQ-20200805-001 出院者占总床日数算法改成SJCYRQ-SJRYRQ(非RYRQ00) SELECT sum(greatest(to_date(SJCYRQ,'YYYYMMDD')-to_date(SJRYRQ,'YYYYMMDD'),1))into ls_COUNT0 FROM ZY_BRXXB0 b WHERE ZYID00 in (select ZYID00 from BQ_BRLDXX a where JSZT00='出院' AND YXBZ00='1' and JSRQ00=ls_TJRQ00 and BQH000=as_BQBH00 ) and upper(substr(b.zyh000,1,1)) not in ('F','M'); update BQ_GZRBB0 set CYZZCS=ls_COUNT0 where BMBH00=as_BQBH00 and RQ0000=ls_TJRQ00; ---------总床位数------------- --update BQ_GZRBB0 set ZCWS00=ls_COUNT0 where BMBH00=as_BQBH00 and RQ0000=ls_TJRQ00; ---------陪伴人数------------- select COUNT (DISTINCT a.ZYID00) into ls_COUNT0 FROM BQ_BRLDXX a,zy_brxxb0 b WHERE a.JSRQ00=ls_TJRQ00 and a.BQH000=as_BQBH00 and b.RCYFH0 like '%陪伴%' and a.zyid00=b.zyid00 and (upper(substr(b.zyh000,1,1)) not in ('F','M')); update BQ_GZRBB0 set PBRS00=ls_COUNT0 where BMBH00=as_BQBH00 and RQ0000=ls_TJRQ00; ---------治愈天数------------- -- update BQ_GZRBB0 set ZYTS00=ls_COUNT0 where BMBH00=as_BQBH00 and RQ0000=ls_TJRQ00; ---保存时判断：出院人数合计<>治愈+好转+未愈+死亡+24小时死亡+自动出院+分娩人数+计划生育 ---保存时判断：实有人数<>原有人数+入院人数+由他科转入数-出院合计-其它出院人数-转入他科人数 ---保存时判断：当前原有人数不等于前一天的实有人数应不能保存，并提示操作者‘本日原有人数不等于前一天的实有人数’ ---实有人数=原有人数+入院人数+转入人数-出院-其它出院人数-转入他科人数 --2014.11.11 for BQ-20141110-003 实有人数不能再减其他出院人数(出院人数已经减过了) if ls_COUNT1>0 then ---前天有数据时 --update BQ_GZRBB0 set SYBRC0=nvl(YYBRC0,0)+nvl(RYS000,0)+nvl(ZRRS00,0)-nvl(CRRSHJ,0)-nvl(CYRSQT,0)-nvl(ZCRS00,0) update BQ_GZRBB0 set SYBRC0=nvl(YYBRC0,0)+nvl(RYS000,0)+nvl(ZRRS00,0)-nvl(CRRSHJ,0)-nvl(ZCRS00,0) where BMBH00=as_BQBH00 and RQ0000=ls_TJRQ00; end if; 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; / show error;