create or replace procedure SP_BQ_KSLDTJ --病人流动统计 ( pStartRq in Varchar2, PStartSj in Varchar2, pEndRq00 in Varchar2, PEndSj00 in Varchar2, PFLAG00 OUT Varchar2, PID0000 OUT number ) -- MODIFICATION HISTORY -- Person Date Comments -- linyx 2020.05.22 create for BQHS9.0-20200513-001 -- linyx 2020.11.06 去掉ryrq00这个条件判断，修改规则和综合查询的界面一样 for BQHS9.0-20201102-003 -- dongxb 2021.09.29 增加参数控制单病区对应多科室时用KSJSRQ来统计 for ZYHS11-20210929-001 AS vStartrqsj Varchar2(20); VEndRqsj00 Varchar2(20); vprevtrq Varchar2(8); --开始日期的前一天 Vcounter number(5); Vid0000 BQ_ZHTJ00_TEMP00.ID0000%TYPE; Vglzkcy varchar2(20); vDayrqsj Varchar2(20);--白班日期时间 vNightrqsj Varchar2(20);--夜班日期时间 vNextDayrqsj Varchar2(20);--下一天白班日期时间 Vsfakstj XT_XTCS00.VALUE0%type;--是否按科室统计 VEndSj00 Varchar2(8); Cursor C_YYBQBH is select bmbh00,bmmc00,bh0000 from bm_bmbm00 where bmxz00='0' and flag00='1' and czbz00 in ('0','1') order by bh0000; BEGIN Vcounter:=sf_bq_brldxx; commit; VEndSj00:=pEndSj00; if (length(VEndSj00)=5) then VEndSj00:=VEndSj00||':59'; end if; vStartRqsj:=pStartRq||pStartsj; --vEndRqsj00:=pEndRq00||pEndSj00; vEndRqsj00:=pEndRq00||VEndSj00; vprevtrq:=to_char(to_date(pStartRq,'YYYYMMDD')-1,'YYYYMMDD'); vDayrqsj:=pStartRq||'08:00:00'; vNightrqsj:=pStartRq||'18:00:00'; vNextDayrqsj:=to_char(to_date(pStartRq,'YYYYMMDD')+1,'YYYYMMDD')||'08:00:00'; --病人流动统计在院病人是否过滤转科和出院的病人 select nvl(max(trim(VALUE0)),'0') into Vglzkcy from XT_XTCS00 where NAME00='BQ_BRLDTJGLZKCY'; --单病区对多科室是否按ksjsrq统计 select trim(nvl(max(VALUE0),'N')) into Vsfakstj from XT_XTCS00 where NAME00='BQ_SFBQDKSAKSTJ'; select SQ_BQ_ZHTJ00_TEMP00_ID0000.Nextval into Vid0000 from dual; --delete BQ_ZHTJ00_TEMP00 where Flag00='BQ_BRLDTJ'; if pEndRq00 is null then for bm in C_YYBQBH loop insert into BQ_ZHTJ00_TEMP00 (id0000,flag00,bmmc00,bmbh00,startd,stopda,startt,stopti) VALUES (Vid0000,'BQ_BRLDTJ',bm.bmmc00,bm.bmbh00,pStartRq,pEndRq00,pStartSj,pEndSj00); --在院人数 qsrq<=endrq and (jsrq is null or jsrq>=endrq) /* Update BQ_ZHTJ00_TEMP00 set temp01=(SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b WHERE a.BQH000=bm.bmbh00 and yxbz00='1' and a.qszt00 in ('新入','转入') and a.QSRQ00<=pStartRq and BQJSRQ>pStartRq and a.zyid00=b.zyid00 and (upper(substr(b.zyh000,1,1)) not in ('F','M')))--泉州人民医院，过滤掉住院号带字母开头‘f’和'm' where flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00;*/ if Vglzkcy='Y' then Update BQ_ZHTJ00_TEMP00 set temp01=(SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b WHERE a.KSH000=bm.bmbh00 and b.dqks00=bm.bmbh00 and b.brzt00<>'4' and yxbz00='1' /*and b.ryrq00<=pStartRq*/ and (qszt00 in ('新入','转入') and QSRQ00<=pStartRq and decode(Vsfakstj,'N',BQJSRQ,KSJSRQ)>pStartRq or qszt00 in ('变更','换入','迁入') and QSRQ00<=pStartRq and nvl(JSRQ00,'99991231')>pStartRq) and a.zyid00=b.zyid00 and (upper(substr(b.zyh000,1,1)) not in ('F','M')))--泉州人民医院，过滤掉住院号带字母开头‘f’和'm' where flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00 and id0000=Vid0000; else Update BQ_ZHTJ00_TEMP00 set temp01=(SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b WHERE a.KSH000=bm.bmbh00 and yxbz00='1' /*and b.ryrq00<=pStartRq*/ and (qszt00 in ('新入','转入') and QSRQ00<=pStartRq and decode(Vsfakstj,'N',BQJSRQ,KSJSRQ)>pStartRq or qszt00 in ('变更','换入','迁入') and QSRQ00<=pStartRq and nvl(JSRQ00,'99991231')>pStartRq) and a.zyid00=b.zyid00 and (upper(substr(b.zyh000,1,1)) not in ('F','M')))--泉州人民医院，过滤掉住院号带字母开头‘f’和'm' where flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00 and id0000=Vid0000; end if; --病区收治人数,以住院收费处的为准 Update BQ_ZHTJ00_TEMP00 set temp02=(SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b WHERE a.KSH000=bm.bmbh00 and qszt00 in ('新入') and yxbz00='1' and qsrq00=pStartRq /*and b.ryrq00<=pStartRq*/ and a.zyid00=b.zyid00 and (upper(substr(b.zyh000,1,1)) not in ('F','M'))) where flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00 and id0000=Vid0000; --病区出院登记人数 Update BQ_ZHTJ00_TEMP00 set temp03=(SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b WHERE JSZT00='出院' and a.KSH000=bm.bmbh00 and yxbz00='1' and jsrq00=pStartrq /*and b.ryrq00<=pStartRq*/ and a.zyid00=b.zyid00 and (upper(substr(b.zyh000,1,1)) not in ('F','M'))) where flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00 and id0000=Vid0000; --转入人数 Update BQ_ZHTJ00_TEMP00 set temp04=(SELECT COUNT ( a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b WHERE QSZT00 in ('转入') and a.KSH000=bm.bmbh00 and yxbz00='1' and QSRQ00 =pStartrq /*and b.ryrq00<=pStartRq*/ and a.zyid00=b.zyid00 and (upper(substr(b.zyh000,1,1)) not in ('F','M'))) where flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00 and id0000=Vid0000; --转出人数 Update BQ_ZHTJ00_TEMP00 set temp05=(SELECT COUNT ( a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b WHERE JSZT00='转出' and a.KSH000=bm.bmbh00 and yxbz00='1' and JSRQ00=pStartrq /*and b.ryrq00<=pStartRq*/ and a.zyid00=b.zyid00 and (upper(substr(b.zyh000,1,1)) not in ('F','M'))) where flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00 and id0000=Vid0000; --实际开放床位数 Update BQ_ZHTJ00_TEMP00 set temp06=(SELECT SYCW00 from BM_BMBM00 where BMBH00=bm.bmbh00) where flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00 and id0000=Vid0000; --空床数 Update BQ_ZHTJ00_TEMP00 set temp07=abs(nvl(temp06,0)-nvl(temp01,0)),temp08=0 where flag00='BQ_BRLDTJ' and abs(nvl(temp06,0))>=abs(nvl(temp01,0)) and id0000=Vid0000; --加床数 Update BQ_ZHTJ00_TEMP00 set temp08=abs(nvl(temp01,0)-nvl(temp06,0)),temp07=0 where flag00='BQ_BRLDTJ' and abs(nvl(temp01,0))>abs(nvl(temp06,0)) and id0000=Vid0000; --白班接班时在院患者数 if Vglzkcy='Y' then Update BQ_ZHTJ00_TEMP00 set temp09=(SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b WHERE a.ksh000=bm.bmbh00 and b.dqks00=bm.bmbh00 and b.brzt00<>'4' and yxbz00='1' and a.qszt00 in ('新入','转入') and a.QSRQ00||a.QSSJ00<=vDayrqsj and decode(Vsfakstj,'N',BQJSRQ,KSJSRQ)||JSSJ00>vDayrqsj and a.zyid00=b.zyid00 and (upper(substr(b.zyh000,1,1)) not in ('F','M'))) where flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00 and ID0000=Vid0000; else Update BQ_ZHTJ00_TEMP00 set temp09=(SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b WHERE a.ksh000=bm.bmbh00 and yxbz00='1' and a.qszt00 in ('新入','转入') and a.QSRQ00||a.QSSJ00<=vDayrqsj and decode(Vsfakstj,'N',BQJSRQ,KSJSRQ)||JSSJ00>vDayrqsj and a.zyid00=b.zyid00 and (upper(substr(b.zyh000,1,1)) not in ('F','M'))) where flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00 and ID0000=Vid0000; end if; --白班时段内新入院患者数 Update BQ_ZHTJ00_TEMP00 set temp10=(SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b WHERE a.ksh000=bm.bmbh00 and qszt00 in ('新入') and yxbz00='1' and qsrq00||substr(QSSJ00,0,5) between vDayrqsj and vNightrqsj and a.zyid00=b.zyid00 and (upper(substr(b.zyh000,1,1)) not in ('F','M'))) where flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00 and ID0000=Vid0000; --夜班接班时在院患者数 if Vglzkcy='Y' then Update BQ_ZHTJ00_TEMP00 set temp11=(SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b WHERE a.ksh000=bm.bmbh00 and b.dqks00=bm.bmbh00 and b.brzt00<>'4' and yxbz00='1' and a.qszt00 in ('新入','转入') and a.QSRQ00||a.QSSJ00<=vNightrqsj and decode(Vsfakstj,'N',BQJSRQ,KSJSRQ)||JSSJ00>vNightrqsj and a.zyid00=b.zyid00 and (upper(substr(b.zyh000,1,1)) not in ('F','M'))) where flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00 and ID0000=Vid0000; else Update BQ_ZHTJ00_TEMP00 set temp11=(SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b WHERE a.ksh000=bm.bmbh00 and yxbz00='1' and a.qszt00 in ('新入','转入') and a.QSRQ00||a.QSSJ00<=vNightrqsj and decode(Vsfakstj,'N',BQJSRQ,KSJSRQ)||JSSJ00>vNightrqsj and a.zyid00=b.zyid00 and (upper(substr(b.zyh000,1,1)) not in ('F','M'))) where flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00 and ID0000=Vid0000; end if; --夜班时段内新入院患者数 Update BQ_ZHTJ00_TEMP00 set temp12=(SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b WHERE a.ksh000=bm.bmbh00 and qszt00 in ('新入') and yxbz00='1' and qsrq00||substr(QSSJ00,0,5) between vNightrqsj and vNextDayrqsj and a.zyid00=b.zyid00 and (upper(substr(b.zyh000,1,1)) not in ('F','M'))) where flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00 and ID0000=Vid0000; --白班收治患者数=（白班接班时在院患者数+白班时段内新入院患者数） Update BQ_ZHTJ00_TEMP00 set temp13 = temp09 + temp10 where flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00 and ID0000=Vid0000; --夜班收治患者数=（夜班接班时在院患者数+夜班时段内新入院患者数） Update BQ_ZHTJ00_TEMP00 set temp14 = temp11 + temp12 where flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00 and ID0000=Vid0000; end loop; else for bm in C_YYBQBH loop insert into BQ_ZHTJ00_TEMP00 (id0000,flag00,bmmc00,bmbh00,startd,stopda,startt,stopti) VALUES (Vid0000,'BQ_BRLDTJ',bm.bmmc00,bm.bmbh00,pStartRq,pEndRq00,pStartSj,pEndSj00); --期初人数 qsrq<=endrq and (jsrq is null or jsrq>=endrq) if Vglzkcy='Y' then Update BQ_ZHTJ00_TEMP00 set temp01=(SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b WHERE a.KSH000=bm.bmbh00 and b.dqks00=bm.bmbh00 and b.brzt00<>'4' and yxbz00='1' and a.qszt00 in ('新入','转入','入院') and a.QSRQ00||a.QSSJ00<=vStartrqsj and decode(Vsfakstj,'N',BQJSRQ,KSJSRQ)||JSSJ00>vStartrqsj /*and b.ryrq00<=pStartRq*/ and a.zyid00=b.zyid00 and (upper(substr(b.zyh000,1,1)) not in ('F','M'))) where flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00 and id0000=Vid0000; else Update BQ_ZHTJ00_TEMP00 set temp01=(SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b WHERE a.KSH000=bm.bmbh00 and yxbz00='1' and a.qszt00 in ('新入','转入','入院') and a.QSRQ00||a.QSSJ00<=vStartrqsj and decode(Vsfakstj,'N',BQJSRQ,KSJSRQ)||JSSJ00>vStartrqsj /*and b.ryrq00<=pStartRq*/ and a.zyid00=b.zyid00 and (upper(substr(b.zyh000,1,1)) not in ('F','M'))) where flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00 and id0000=Vid0000; end if; --病区收治人数,以住院收费处的为准 Update BQ_ZHTJ00_TEMP00 set temp02=(SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b WHERE a.KSH000=bm.bmbh00 and qszt00 in ('新入') /*and b.ryrq00<=pStartRq*/ and yxbz00='1' and qsrq00||qssj00 between vStartrqsj and VEndRqsj00 and a.zyid00=b.zyid00 and (upper(substr(b.zyh000,1,1)) not in ('F','M'))) where flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00 and id0000=Vid0000; --病区出院登记人数 Update BQ_ZHTJ00_TEMP00 set temp03=(SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b WHERE JSZT00='出院' and a.KSH000=bm.bmbh00 /*and b.ryrq00<=pStartRq*/ and yxbz00='1' and jsrq00||jssj00 between vStartrqsj and VEndRqsj00 and a.zyid00=b.zyid00 and (upper(substr(b.zyh000,1,1)) not in ('F','M'))) where flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00 and id0000=Vid0000; --转入人数 Update BQ_ZHTJ00_TEMP00 set temp04=(SELECT COUNT ( a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b WHERE QSZT00='转入' and a.KSH000=bm.bmbh00 /*and b.ryrq00<=pStartRq*/ and yxbz00='1'and QSRQ00||QSSJ00 between vStartrqsj and VEndRqsj00 and a.zyid00=b.zyid00 and (upper(substr(b.zyh000,1,1)) not in ('F','M'))) where flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00 and id0000=Vid0000; --转出人数 Update BQ_ZHTJ00_TEMP00 set temp05=(SELECT COUNT ( a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b WHERE JSZT00='转出' and a.KSH000=bm.bmbh00 /*and b.ryrq00<=pStartRq*/ and yxbz00='1' and JSRQ00||JSSJ00 between vStartrqsj and VEndRqsj00 and a.zyid00=b.zyid00 and (upper(substr(b.zyh000,1,1)) not in ('F','M'))) where flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00 and id0000=Vid0000; --期末人数 qsrq<=endrq and (jsrq is null or jsrq>=endrq) Update BQ_ZHTJ00_TEMP00 set temp06=(SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b WHERE a.KSH000=bm.bmbh00 and yxbz00='1' and a.qszt00 in ('新入','转入','入院') and a.QSRQ00||a.QSSJ00<=VEndRqsj00 and decode(Vsfakstj,'N',BQJSRQ,KSJSRQ)||JSSJ00>VEndRqsj00 /*and b.ryrq00<=pStartRq*/ and a.zyid00=b.zyid00 and (upper(substr(b.zyh000,1,1)) not in ('F','M'))) where flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00 and id0000=Vid0000; end loop; end if; delete BQ_ZHTJ00_TEMP00 where flag00='BQ_BRLDTJ' and temp01=0 and temp02=0 and temp03=0 and temp04=0 and temp05=0 and temp06=0 and exists (select 1 from BM_BMBM00 where CZBZ00='2' and BMBH00=BQ_ZHTJ00_TEMP00.BMBH00) and id0000=Vid0000; PFLAG00:='BQ_BRLDTJ'; PID0000:=Vid0000; COMMIT; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20010, '数据没有找到！*'); WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20020, substrb(NVL(SQLERRM, '原因不明出错')||'！*',1,220)); END SP_BQ_KSLDTJ;