CREATE OR REPLACE PROCEDURE SP_BQ_BRLDTJ --病人流动统计
(  pStartRq in   Varchar2,
   PStartSj in   Varchar2,
   pEndRq00 in   Varchar2,
   PEndSj00 in   Varchar2,
   PFLAG00 OUT   Varchar2,
   pBQBH00  in   number default 0,  --病区号
   pBQKSBZ  in   varchar2 default '0'  --病区科室标志:0按病区,1按科室
   
)
AS
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- zhr         2008.12.02  修正部门编码表病区被停用后，历史数据无法统计出来的问题
-- dsm         2014.05.06  增加统计请假人数 for BQ-20140505-001
-- dsm         2016.01.07  增加参数pBQBH00,pBQKSBZ for BQ-20151230-001
-- pwx         2021.12.15  截取了游标里的bmmc00为：substrb(bmmc00,1,20) bmmc00 for BQ-20211204-001
   vStartrqsj  Varchar2(20);
   VEndRqsj00  Varchar2(20);
   vprevtrq    Varchar2(8); --开始日期的前一天
   Vcounter    number(5);
   Cursor C_YYBQBH is 
     select bmbh00,substrb(bmmc00,1,20) bmmc00,bh0000 from bm_bmbm00 where bmxz00='9' and flag00='1' and czbz00 in ('0','1','2') and (PBQBH00=0 or BMBH00=pBQBH00)    order by bh0000;
   Cursor C_YYKSBH is select bmbh00,substrb(bmmc00,1,20) bmmc00,bh0000 from bm_bmbm00 where bmxz00='0' and flag00='1' and czbz00 in ('0','1','2')   
      and (PBQBH00=0 or BMBH00 in(select DQKS00 from ZY_BRXXB0 where DQBQ00=pBQBH00 and BRZT00='20') )
       order by bh0000;    
  
BEGIN
  Vcounter:=sf_bq_brldxx;
  commit;
  vStartRqsj:=pStartRq||pStartsj;
  vEndRqsj00:=pEndRq00||pEndSj00;
  vprevtrq:=to_char(to_date(pStartRq,'YYYYMMDD')-1,'YYYYMMDD');
  delete bm_zhtj00_temp00 where Flag00='BQ_BRLDTJ';
  if pEndRq00 is null then
     if pStartRq=to_char(sysdate,'YYYYMMDD')  then 
          SP_BQ_KSLDXX;
     end if;      
     if pBQKSBZ='0' then
       for bm in C_YYBQBH loop
         insert into bm_zhtj00_temp00 (flag00,bmmc00,bmbh00,startd,stopda,startt,stopti,temp11)
                   VALUES ('BQ_BRLDTJ',bm.bmmc00,bm.bmbh00,pStartRq,pEndRq00,pStartSj,pEndSj00,'1');
         --在院人数 qsrq<=endrq and (jsrq is null or jsrq>=endrq)
         Update bm_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;
         --病区收治人数,以住院收费处的为准
         Update bm_zhtj00_temp00 set temp02=(SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b
                                           WHERE  a.BQH000=bm.bmbh00 and qszt00 in ('新入') and
                                                  yxbz00='1' and qsrq00=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;
         --病区出院登记人数
         Update bm_zhtj00_temp00 set temp03=(SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b
                                           WHERE  JSZT00='出院' AND a.BQH000=bm.bmbh00 and
                                                  yxbz00='1' and jsrq00=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;
         --转入人数
         Update bm_zhtj00_temp00 set temp04=(SELECT COUNT ( a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b
                                           WHERE  QSZT00 in ('转入') AND a.BQH000=bm.bmbh00 and
                                                  yxbz00='1' and QSRQ00 =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;
         --转出人数
         Update bm_zhtj00_temp00 set temp05=(SELECT COUNT ( a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b
                                           WHERE  JSZT00='转出' AND a.BQH000=bm.bmbh00 and
                                                  yxbz00='1' AND JSRQ00=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;
         --实际开放床位数
         Update bm_zhtj00_temp00 set temp06=(SELECT SYCW00 from BM_BMBM00 where BMBH00=bm.bmbh00)
           where  flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00;
         --空床数
         Update bm_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));
         --加床数
         Update bm_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));
         --手术人数
         Update bm_zhtj00_temp00 set temp10=(select count(*) from SS_YW0000 where YYSSRQ=pStartrq and SQBQ00=bm.BMBH00)
              where  flag00='BQ_BRLDTJ'  and bmbh00=bm.bmbh00;           
       end loop;       
     elsif pBQKSBZ='1' then   
       for bm in C_YYKSBH loop
         insert into bm_zhtj00_temp00 (flag00,bmmc00,bmbh00,startd,stopda,startt,stopti,temp11)
                   VALUES ('BQ_BRLDTJ',bm.bmmc00,bm.bmbh00,pStartRq,pEndRq00,pStartSj,pEndSj00,'1');
         --在院人数 qsrq<=endrq and (jsrq is null or jsrq>=endrq)
         Update bm_zhtj00_temp00 set temp01=(SELECT COUNT (DISTINCT a.ZYID00) from BQ_KSLDXX a,zy_brxxb0 b
                                           WHERE  a.KSH000=bm.bmbh00  and (BQH000=pBQBH00 or pBQBH00=0) and yxbz00='1' and a.qszt00 in ('1','3') and    --('1','3')
                                                  a.QSRQ00<=pStartRq and JSRQ00>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;
         --病区收治人数,以住院收费处的为准
         Update bm_zhtj00_temp00 set temp02=(SELECT COUNT (DISTINCT a.ZYID00) from BQ_KSLDXX a,zy_brxxb0 b
                                           WHERE  a.KSH000=bm.bmbh00  and (BQH000=pBQBH00 or pBQBH00=0)  and qszt00 in ('1') and
                                                  yxbz00='1' and qsrq00=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;
         --病区出院登记人数
         Update bm_zhtj00_temp00 set temp03=(SELECT COUNT (DISTINCT a.ZYID00) from BQ_KSLDXX a,zy_brxxb0 b
                                           WHERE  a.KSH000=bm.bmbh00 and (BQH000=pBQBH00 or pBQBH00=0)  and JSZT00='2' AND
                                                  yxbz00='1' and jsrq00=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;
         --转入人数
         Update bm_zhtj00_temp00 set temp04=(SELECT COUNT ( a.ZYID00) from BQ_KSLDXX a,zy_brxxb0 b
                                           WHERE  a.KSH000=bm.bmbh00  and (BQH000=pBQBH00 or pBQBH00=0)  and QSZT00 in ('3') AND
                                                  yxbz00='1' and QSRQ00 =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;
         --转出人数
         Update bm_zhtj00_temp00 set temp05=(SELECT COUNT ( a.ZYID00) from BQ_KSLDXX a,zy_brxxb0 b
                                           WHERE  a.KSH000=bm.bmbh00  and (BQH000=pBQBH00 or pBQBH00=0)  and JSZT00='4' AND
                                                  yxbz00='1' AND JSRQ00=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;
         --实际开放床位数
         Update bm_zhtj00_temp00 set temp06=(SELECT SYCW00 from BM_BMBM00 where BMBH00=bm.bmbh00)
            where  flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00;
         --空床数
         Update bm_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));
         --加床数
         Update bm_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));
         --手术人数
         Update bm_zhtj00_temp00 set temp10=(select count(*) from SS_YW0000 where YYSSRQ=pStartrq and SQKS00=bm.BMBH00)
              where  flag00='BQ_BRLDTJ'  and bmbh00=bm.bmbh00;  
       end loop;     
                 
     end if;
     

  else
    if pBQKSBZ='0' then
       for bm in C_YYBQBH loop
         insert into bm_zhtj00_temp00 (flag00,bmmc00,bmbh00,startd,stopda,startt,stopti,temp11)
                   VALUES ('BQ_BRLDTJ',bm.bmmc00,bm.bmbh00,pStartRq,pEndRq00,pStartSj,pEndSj00,'1');
         --期初人数 qsrq<=endrq and (jsrq is null or jsrq>=endrq)
         Update bm_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<=vprevtrq  and BQJSRQ>vprevtrq
                                                  and a.zyid00=b.zyid00  and (upper(substr(b.zyh000,1,1)) not in ('F','M')))
         where  flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00;
         --病区收治人数,以住院收费处的为准
         Update bm_zhtj00_temp00 set temp02=(SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b
                                           WHERE  a.BQH000=bm.bmbh00 and qszt00 in ('新入') and
                                                  yxbz00='1' and qsrq00 between pStartrq and pEndrq00
                                                  and a.zyid00=b.zyid00  and (upper(substr(b.zyh000,1,1)) not in ('F','M')))
         where  flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00;
         --病区出院登记人数
         Update bm_zhtj00_temp00 set temp03=(SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b
                                           WHERE  JSZT00='出院' AND a.BQH000=bm.bmbh00 and
                                                  yxbz00='1' and jsrq00 between pStartrq and pEndrq00
                                                  and a.zyid00=b.zyid00  and (upper(substr(b.zyh000,1,1)) not in ('F','M')))
         where  flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00;
         --转入人数
         Update bm_zhtj00_temp00 set temp04=(SELECT COUNT ( a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b
                                           WHERE  QSZT00='转入' AND a.BQH000=bm.bmbh00 and
                                                  yxbz00='1'AND QSRQ00 between pStartrq and pEndrq00
                                                  and a.zyid00=b.zyid00  and (upper(substr(b.zyh000,1,1)) not in ('F','M')))
         where  flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00;
         --转出人数
         Update bm_zhtj00_temp00 set temp05=(SELECT COUNT ( a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b
                                           WHERE  JSZT00='转出' AND a.BQH000=bm.bmbh00 and
                                                  yxbz00='1' AND JSRQ00 between pStartrq and pEndrq00
                                                  and a.zyid00=b.zyid00  and (upper(substr(b.zyh000,1,1)) not in ('F','M')))
         where  flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00;
         --期末人数 qsrq<=endrq and (jsrq is null or jsrq>=endrq)
         Update bm_zhtj00_temp00 set temp06=(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<=pEndrq00 and BQJSRQ>pEndrq00
                                                  and a.zyid00=b.zyid00  and (upper(substr(b.zyh000,1,1)) not in ('F','M')))
         where  flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00;
         --请假人数,pEndrq00前请假，但是在pEndrq00后续住的
         Update bm_zhtj00_temp00 set temp09=(SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b
                                           WHERE  a.BQH000=bm.bmbh00 and yxbz00='1' and a.jszt00 in ('请假') and
                                                  a.JSRQ00<=pEndrq00 and BQJSRQ>pEndrq00
                                                  and a.zyid00=b.zyid00  and (upper(substr(b.zyh000,1,1)) not in ('F','M'))
                                                  and (exists(select 1 from BQ_BRLDXX where ZYID00=a.ZYID00 and QSZT00 in('续住','入院') and QSRQ00>pEndrq00)  --之后回来续住的
                                                       or not exists(select 1 from BQ_BRLDXX where ZYID00=a.ZYID00  and  QSRQ00>=a.JSRQ00)            --一直都还没有回来的
                                                  )
                                          )
         where  flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00;


       end loop;
    elsif pBQKSBZ= '1' then
     for bm in C_YYKSBH loop
       insert into bm_zhtj00_temp00 (flag00,bmmc00,bmbh00,startd,stopda,startt,stopti,temp11)
                   VALUES ('BQ_BRLDTJ',bm.bmmc00,bm.bmbh00,pStartRq,pEndRq00,pStartSj,pEndSj00,'1');
       --期初人数 qsrq<=endrq and (jsrq is null or jsrq>=endrq)
       Update bm_zhtj00_temp00 set temp01=(SELECT COUNT (DISTINCT a.ZYID00) from BQ_KSLDXX a,zy_brxxb0 b
                                           WHERE  a.KSH000=bm.bmbh00 and (BQH000=pBQBH00 or pBQBH00=0) and yxbz00='1' and a.qszt00 in ('1','3') and
                                                  a.QSRQ00<=vprevtrq  and JSRQ00>vprevtrq
                                                  and a.zyid00=b.zyid00  and (upper(substr(b.zyh000,1,1)) not in ('F','M'))                                                  
                                                  )
       where  flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00;
       --病区收治人数,以住院收费处的为准
       Update bm_zhtj00_temp00 set temp02=(SELECT COUNT (DISTINCT a.ZYID00) from BQ_KSLDXX a,zy_brxxb0 b
                                           WHERE  a.KSH000=bm.bmbh00  and (BQH000=pBQBH00 or pBQBH00=0) and qszt00 in ('1') and
                                                  yxbz00='1' and qsrq00 between pStartrq and pEndrq00
                                                  and a.zyid00=b.zyid00  and (upper(substr(b.zyh000,1,1)) not in ('F','M')))
       where  flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00;
       --病区出院登记人数
       Update bm_zhtj00_temp00 set temp03=(SELECT COUNT (DISTINCT a.ZYID00) from BQ_KSLDXX a,zy_brxxb0 b
                                           WHERE  a.KSH000=bm.bmbh00  and (BQH000=pBQBH00 or pBQBH00=0) and JSZT00='2' AND
                                                  yxbz00='1' and jsrq00 between pStartrq and pEndrq00
                                                  and a.zyid00=b.zyid00  and (upper(substr(b.zyh000,1,1)) not in ('F','M')))
       where  flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00;
       --转入人数
       Update bm_zhtj00_temp00 set temp04=(SELECT COUNT ( a.ZYID00) from BQ_KSLDXX a,zy_brxxb0 b
                                           WHERE  a.KSH000=bm.bmbh00  and (BQH000=pBQBH00 or pBQBH00=0) and QSZT00='3' AND
                                                  yxbz00='1'AND QSRQ00 between pStartrq and pEndrq00
                                                  and a.zyid00=b.zyid00  and (upper(substr(b.zyh000,1,1)) not in ('F','M')))
       where  flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00;
       --转出人数
       Update bm_zhtj00_temp00 set temp05=(SELECT COUNT ( a.ZYID00) from BQ_KSLDXX a,zy_brxxb0 b
                                           WHERE  a.KSH000=bm.bmbh00  and (BQH000=pBQBH00 or pBQBH00=0)  and JSZT00='4' AND
                                                  yxbz00='1' AND JSRQ00 between pStartrq and pEndrq00
                                                  and a.zyid00=b.zyid00  and (upper(substr(b.zyh000,1,1)) not in ('F','M')))
       where  flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00;
       --期末人数 qsrq<=endrq and (jsrq is null or jsrq>=endrq)
       Update bm_zhtj00_temp00 set temp06=(SELECT COUNT (DISTINCT a.ZYID00) from BQ_KSLDXX a,zy_brxxb0 b
                                           WHERE  a.KSH000=bm.bmbh00  and (BQH000=pBQBH00 or pBQBH00=0) and yxbz00='1' and a.qszt00 in ('1','3') and
                                                  a.QSRQ00<=pEndrq00 and JSRQ00>pEndrq00
                                                  and a.zyid00=b.zyid00  and (upper(substr(b.zyh000,1,1)) not in ('F','M')))
       where  flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00;
     end loop;       
     end if;
  end if;
  delete bm_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 temp09=0
         and exists (select 1 from BM_BMBM00 where CZBZ00='2' and BMBH00=bm_zhtj00_temp00.BMBH00);
  PFLAG00:='BQ_BRLDTJ';
  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_BRLDTJ;