CREATE OR REPLACE PROCEDURE SP_BQ_GZLTJ0
( pStartrq 	IN CHAR  , 		--起始日期
  pEndrq00	IN CHAR  ,              --结束日期
  pbqh000 	IN NUMBER,  		--病区号
  pKSBH00   IN NUMBER,       --科室编号
  pID0000   OUT NUMBER 
)
as Vcounter  number(5);
   vprevtrq      char(8);
-- Person      Date            Comments
-- qks         2007-11-21      输液人数改为输液人次
-- qks         2010-03-18      因为可能引起锁表,所以注释sf_bq_brldxx,在user_jobs中SP_XT_SJZL00有包含这个;统计当天的数据可能不准确。
-- dsm         2012-09-06      增加 BQ_GZLMX0.TJFS00和TJFS01 来满足统计时是否要按天累加  for   BQ-20120806-002
-- jinfl       2012-11-26      增加传入科室编号统计  BQ-20121112-003
-- liuj        2103-04-18      由于原来本过程两台不同机器同时操作会引起bq_hlgzl0记录死锁，现修改写法 for BQ-20130402-001
begin
    --Vcounter:=sf_bq_brldxx;
  select  SQ_BQ_HLGZL0_TEMP_ID0000.nextval  into  pID0000  from  dual;
  insert into BQ_HLGZL0_TEMP (ID0000,XH0000,XMID00,XMMC00,SL0000,XMID01,XMMC01,SL0001,PXH000,PXH001) select pID0000,XH0000,XMID00,XMMC00,SL0000,XMID01,XMMC01,SL0001,PXH000,PXH001 from bq_hlgzl0;  
  if pbqh000=0 then  --全院统计
  --入院人数
    update BQ_HLGZL0_TEMP set sl0000=(SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a
                                           WHERE qszt00='新入' and (KSH000=pKSBH00 or pKSBH00=-1) and
                                                  yxbz00='1' and qsrq00 between pStartrq and pEndrq00) where id0000=pID0000 and xmid00=1;
  --出院人数
    update BQ_HLGZL0_TEMP set sl0000=(SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a
                                           WHERE  JSZT00='出院' and (KSH000=pKSBH00 or pKSBH00=-1) AND
                                                  yxbz00='1' and jsrq00 between pStartrq and pEndrq00) where id0000=pID0000 and xmid00=2;
  --死亡人数
    update BQ_HLGZL0_TEMP set sl0000=(SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a,ZY_BRXXB0 b
                                           WHERE  JSZT00='出院' and (KSH000=pKSBH00 or pKSBH00=-1) AND a.ZYID00=b.ZYID00 and b.RCYFH0='死亡' and
                                                  yxbz00='1' and jsrq00 between pStartrq and pEndrq00) where id0000=pID0000 and xmid00=3;
    --计算人头数不累加
    --xmid00=60 是一级护理    for BQ-20120806-002
    update BQ_HLGZL0_TEMP a set sl0000=(SELECT TO_CHAR(COUNT(DISTINCT f.ZYID00)) from ZY_BRFY00 f,zy_fymx00 b,bq_gzlmx0 c
                                 where f.djh000+0=B.djh000 AND c.sfxmid=b.xmbh00+0 and a.xmid00=c.xmid00 and c.xmbz00='0' and
                                       f.czrq00 between pStartrq and pEndrq00 and (b.KDKS00=pKSBH00 or pKSBH00=-1) and  b.xmsl00>0 and  nvl(c.TJFS00,0)=0
                                )WHERE id0000=pID0000 and  (xmid00 BETWEEN 5 AND 9) or xmid00=60 ;

    --计算人头数按天累加
    --TJFS00=1 的不管收多少都按1算,按天累加   for BQ-20120806-002

    update BQ_HLGZL0_TEMP a set sl0000= SF_BQ_CALGZL(a.XMID00,0,pStartrq,pEndrq00,0,pKSBH00)
                                      WHERE id0000=pID0000 and  (xmid00 BETWEEN 5 AND 9)
                                      and exists(select 1 from BQ_GZLMX0 where XMID00=a.XMID00 and nvl(TJFS00,0)=1) ;


    --计算天数 按收费数量,TJFS01=0

    update BQ_HLGZL0_TEMP a set sl0000=(select a.sl0000||'/'||to_char(sum(b.xmsl00)) from zy_fymx00 b,bq_gzlmx0 c
                                 where c.sfxmid=b.xmbh00+0 and a.xmid00=c.xmid00 and c.xmbz00='0' and nvl(c.TJFS01,0)=0 and
                                       B.czrq00 between pStartrq and pEndrq00 and (B.kdks00=pKSBH00 or pKSBH00=-1)
                                )WHERE id0000=pID0000 and  xmid00 BETWEEN 5 AND 7 AND sl0000 IS NOT NULL
                                and exists(select 1 from BQ_GZLMX0 where XMID00=a.XMID00 and nvl(TJFS01,0)=0) ;

     --TJFS01=1 按天每日不管收多少都按1算   for BQ-20120806-002
    update BQ_HLGZL0_TEMP a set sl0000= a.sl0000||'/'||to_char(SF_BQ_CALGZL(a.XMID00,0,pStartrq,pEndrq00,1,pKSBH00))
                           WHERE  id0000=pID0000 and xmid00 BETWEEN 5 AND 7 AND sl0000 IS NOT NULL
                           and exists(select 1 from BQ_GZLMX0 where XMID00=a.XMID00 and nvl(TJFS01,0)=1) ;


    update BQ_HLGZL0_TEMP a set sl0000=(select sum(b.xmsl00) from zy_fymx00 b,bq_gzlmx0 c
                                 where c.sfxmid=b.xmbh00+0 and a.xmid00=c.xmid00 and c.xmbz00='0' and
                                       czrq00 between pStartrq and pEndrq00 and (kdks00=pKSBH00 or pKSBH00=-1)
                                ) where id0000=pID0000 and (xmid00>9 OR xmid00=4)  and xmid00 not in(60);

    update BQ_HLGZL0_TEMP a set sl0001=(select sum(b.xmsl00) from zy_fymx00 b,bq_gzlmx0 c
                                 where c.sfxmid=b.xmbh00+0 and a.xmid01=c.xmid00 and c.xmbz00='0' and
                                       czrq00 between pStartrq and pEndrq00 and (kdks00=pKSBH00 or pKSBH00=-1)
                                ) where id0000=pID0000 and xmid01>9 ;

  else
  --入院人数
    update BQ_HLGZL0_TEMP set sl0000=(SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a
                                           WHERE  BQH000=pbqh000 and qszt00='新入' and (KSH000=pKSBH00 or pKSBH00=-1) and
                                                  yxbz00='1' and qsrq00 between pStartrq and pEndrq00) where id0000=pID0000 and xmid00=1;--
  --出院人数
    update BQ_HLGZL0_TEMP set sl0000=(SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a
                                           WHERE  JSZT00='出院' AND BQH000=pbqh000 and (KSH000=pKSBH00 or pKSBH00=-1) and
                                                  yxbz00='1' and jsrq00 between pStartrq and pEndrq00) where id0000=pID0000 and xmid00=2;
  --死亡人数
    update BQ_HLGZL0_TEMP set sl0000=(SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a,ZY_BRXXB0 b
                                           WHERE  JSZT00='出院' AND BQH000=pbqh000 and (KSH000=pKSBH00 or pKSBH00=-1) and a.ZYID00=b.ZYID00 and b.RCYFH0='死亡' and
                                                  yxbz00='1' and jsrq00 between pStartrq and pEndrq00) where id0000=pID0000 and xmid00=3;

     --计算人头数不累加 
    --xmid00=60 是一级护理    for BQ-20120806-002
    update BQ_HLGZL0_TEMP a set sl0000=(SELECT TO_CHAR(COUNT(DISTINCT f.ZYID00)) from ZY_BRFY00 f,zy_fymx00 b,bq_gzlmx0 c
                                 where f.djh000+0=B.djh000 AND c.sfxmid=b.xmbh00+0 and a.xmid00=c.xmid00 and c.xmbz00='0' and
                                       f.czrq00 between pStartrq and pEndrq00 and B.kdbq00=pbqh000 and (b.kdks00=pKSBH00 or pKSBH00=-1) AND b.xmsl00>0 and  nvl(c.TJFS00,0)=0
                                )WHERE id0000=pID0000 and (xmid00 BETWEEN 5 AND 9)  or xmid00=60;

     --计算人头数按天累加
    --TJFS00=1 的不管收多少都按1算,按天累加   for BQ-20120806-002
    update BQ_HLGZL0_TEMP a set sl0000=SF_BQ_CALGZL(a.XMID00,pbqh000,pStartrq,pEndrq00,0,pKSBH00)  WHERE id0000=pID0000 and (xmid00 BETWEEN 5 AND 9)
                                      and exists(select 1 from BQ_GZLMX0 where XMID00=a.XMID00 and nvl(TJFS00,0)=1) ;

    --计算天数 按收费数量,TJFS01=0
    update BQ_HLGZL0_TEMP a set sl0000=(select a.sl0000||'/'||to_char(sum(b.xmsl00)) from zy_fymx00 b,bq_gzlmx0 c
                                 where c.sfxmid=b.xmbh00+0 and a.xmid00=c.xmid00 and c.xmbz00='0' and  nvl(c.TJFS01,0)=0  and
                                       B.czrq00 between pStartrq and pEndrq00 and B.kdbq00=pbqh000 and (b.kdks00=pKSBH00 or pKSBH00=-1)
                                ) WHERE id0000=pID0000 and xmid00 BETWEEN 5 AND 7 AND sl0000 IS NOT NULL
                                and exists(select 1 from BQ_GZLMX0 where XMID00=a.XMID00 and nvl(TJFS01,0)=0) ;  --8，9 去掉了，统计不准

    --TJFS01=1 按天每日不管收多少都按1算   for BQ-20120806-002
    update BQ_HLGZL0_TEMP a set sl0000= a.sl0000||'/'||to_char(SF_BQ_CALGZL(a.XMID00,pbqh000,pStartrq,pEndrq00,1,pKSBH00))
                                WHERE id0000=pID0000 and xmid00 BETWEEN 5 AND 7 AND sl0000 IS NOT NULL
                                and exists(select 1 from BQ_GZLMX0 where XMID00=a.XMID00 and nvl(TJFS01,0)=1) ;

    update BQ_HLGZL0_TEMP a set sl0000=(select sum(b.xmsl00) from zy_fymx00 b,bq_gzlmx0 c
                                 where c.sfxmid=b.xmbh00+0 and a.xmid00=c.xmid00 and c.xmbz00='0' and
                                       czrq00 between pStartrq and pEndrq00 and kdbq00=pbqh000 and (kdks00=pKSBH00 or pKSBH00=-1)
                                ) where id0000=pID0000 and (xmid00>9 OR xmid00=4) and xmid00 not in(60) ;

    update BQ_HLGZL0_TEMP a set sl0001=(select sum(b.xmsl00) from zy_fymx00 b,bq_gzlmx0 c
                                 where c.sfxmid=b.xmbh00+0 and a.xmid01=c.xmid00 and c.xmbz00='0' and
                                       czrq00 between pStartrq and pEndrq00 and kdbq00=pbqh000 and (kdks00=pKSBH00 or pKSBH00=-1)
                                ) where id0000=pID0000 and xmid01>9 ;

  end if;
  --反应数 去掉，数据不准确
  --update bq_hlgzl0 set xmmc00='输液人数' where xh0000=8 and xmid00=8;
  --update bq_hlgzl0 set xmmc00='输血人数' where xh0000=9 and xmid00=9;
  update BQ_HLGZL0_TEMP set xmmc00='输液人次' where id0000=pID0000 and xh0000=8 and xmid00=8;
  update BQ_HLGZL0_TEMP set xmmc00='输血人次' where id0000=pID0000 and xh0000=9 and xmid00=9;
  update BQ_HLGZL0_TEMP a set xmmc00='输液人数' where id0000=pID0000 and xh0000=8 and xmid00=8 and exists(select 1 from  bq_gzlmx0 where xmid00=a.xmid00 and nvl(TJFS00,0)=1);
  update BQ_HLGZL0_TEMP a  set xmmc00='输血人数' where id0000=pID0000 and xh0000=9 and xmid00=9 and exists(select 1 from  bq_gzlmx0 where xmid00=a.xmid00 and nvl(TJFS00,0)=1);
end;
/