-- Start of DDL Script for Procedure SD_HOSPITAL.SP_ZS_LJ_WCQKTJ_LJ
-- Generated 13-九月-2018 17:45:50 from SD_HOSPITAL@KFHIS5

CREATE OR REPLACE 
PROCEDURE sp_zs_lj_wcqktj_lj
(
        as_KSRQ00    in    char,       --出院开始日期
        as_JSRQ00    in    char,       --出院结束日期
        ad_LJID00    in    number default 0,
        ad_KSBH00    in    number default 0,
        ad_ID0000    OUT   NUMBER,     --统计报表对应的ID
        as_YHMSG0    OUT   VARCHAR,    --存储过程提示的错误信息
        as_SYSMSG    OUT   VARCHAR,     --系统提示的错误信息,
        as_YYID00    in    char default  '0'  --医院ID
)
as
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- dsm      2013.09.18     create for ZYYS-20130916-001
BEGIN
     select  SQ_LJ_TJ0000_TJID00.nextval  into  ad_ID0000  from  dual;
     --所有路径
     insert into LJ_TJ0000(TJID00,LJID00,KSBH00,ZDM000,ZDMC00)
     select ad_ID0000,a.LJID00,c.KSBH00,b.ICD000,b.ICDMC0 from LJ_MB0000 a,LJ_MBJRTJ b ,LJ_ZDKSFP c
      where a.LJID00=b.LJID00 and a.LJZT00<>'0' and  b.ICD000=c.ICD000
            and (ad_KSBH00=0 or c.KSBH00=ad_KSBH00)
            and (a.LJID00=ad_LJID00 or ad_LJID00=0);

     --出院的所有人
     insert into LJ_TJTEMP(ID0000,ZYID00,LJID01,LJID00,RZKS00,ZDM000)
     select ad_ID0000,ZYID00,LJID01,LJID00,RZKS00,ZDM000 from VW_ZS_RYRJBRMX_LNYY a
         where SJCYRQ>=as_KSRQ00
         and SJCYRQ<=as_JSRQ00
         and ((LJID00=ad_LJID00 or ad_LJID00=0) or (LJID01=ad_LJID00 or ad_LJID00=0))
         and (RZKS00=ad_KSBH00 or ad_KSBH00=0)
         and (as_YYID00='0' or exists(select 1 from BM_BMBM00 where BMBH00=a.RZKS00 and YYID00=as_YYID00));

     update  LJ_TJTEMP a set (JSFS00,MINTS0,MAXTS0,GJTS00,SJTS00,TJ0001)
            =( select JSFS00,MINTS0,MAXTS0,decode(nvl(MAXTS0,0),0,GJTS00,MAXTS0),SJTS00,nvl(BDDYRC,0) from LJ_ZRZC00 b where ZYID00=a.ZYID00 and LJID00=a.LJID00 and rownum<=1 )
      where ID0000=ad_ID0000;

      ---把诊断符合路径和实际路径不符的改掉
     --update LJ_TJTEMP set LJID01=LJID00 where LJID00>0  and LJID00<>LJID01;
       --总入径人次

 --      insert into LJ_TJ0000(TJID00,LJID00,TJ0001,TJ0011)
 --      select ad_ID0000,LJID00,to_char(count(*)),'0'
 --      from  LJ_TJTEMP a where ID0000=ad_ID0000 and LJID00>0
 --        group by LJID00;
       --符合入径条件，但是实际没有入径的病人,为了把病人涉及的路径都插入LJ_TJ0000
 --      insert into LJ_TJ0000(TJID00,LJID00,TJ0001,TJ0011)
 --      select distinct ad_ID0000,LJID01,'0','0' from LJ_TJTEMP a where   LJID00=0 and  not exists(select 1 from lj_tj0000 where LJID00=a.LJID01 and  TJID00=ad_ID0000 );

       --总出院人数RYZS00
       update LJ_TJ0000 a set RYZS00=(select count(*)  from  LJ_TJTEMP c where ID0000=ad_ID0000 and RZKS00=a.KSBH00
                                      and (RZKS00=ad_KSBH00 or ad_KSBH00=0)

                                    )
                             where TJID00=ad_ID0000;


       --第一诊断符合人数TJ0001
       update LJ_TJ0000 a set TJ0001=(select count(*)  from  LJ_TJTEMP c where ID0000=ad_ID0000 and RZKS00=a.KSBH00 and trim(ZDM000)=trim(a.ZDM000)
                                      and (RZKS00=ad_KSBH00 or ad_KSBH00=0)
                                    )
                             where TJID00=ad_ID0000;


       --入径人次      TJ0002
       --诊断条件吻合的
       update LJ_TJ0000 a set TJ0002=(select to_char(count(*))  from  LJ_TJTEMP c where ID0000=ad_ID0000  and trim(ZDM000)=trim(a.ZDM000)
                                       and LJID00=a.LJID00
                                       and (LJID00=ad_LJID00 or ad_LJID00=0)
                                       and (RZKS00=ad_KSBH00 or ad_KSBH00=0)

                                    )
                             where TJID00=ad_ID0000 and LJID00>0;
       ----诊断条件不吻合,但是入径的
       insert into LJ_TJ0000(TJID00,LJID00,ZDM000,RYZS00,TJ0001,TJ0002,TJ0005,KSBH00)
       select ad_ID0000,LJID00,ZDM000,0 RYZS00,0,count(*),0,RZKS00  from LJ_TJTEMP where  ID0000=ad_ID0000  and LJID00>0 and LJID01<>LJID00 group by RZKS00,LJID00,ZDM000;

       --完成数 TJ0005
       update LJ_TJ0000 a set TJ0005=nvl((select to_char(count(*))  from LJ_TJTEMP c where c.ID0000=ad_ID0000
                                           and JSFS00='完成' and LJID00=a.LJID00 and trim(ZDM000)=trim(a.ZDM000)
                                           and (LJID00=ad_LJID00 or ad_LJID00=0)
                                           and (RZKS00=ad_KSBH00 or ad_KSBH00=0)

                                    ) ,'0')
                             where TJID00=ad_ID0000;

     insert into LJ_TJ0000(TJID00,KSMC00,LJMC00,RYZS00,TJ0001,TJ0002,TJ0005)
     select ad_ID0000,'合计','',sum(RYZS00),sum(to_number(TJ0001)),sum(to_number(TJ0002)),sum(to_number(TJ0005))
     from LJ_TJ0000 where TJID00=ad_ID0000 ;
     update LJ_TJ0000 a set RYZS00=(select count(*)  from  LJ_TJTEMP c where ID0000=ad_ID0000  and (RZKS00=ad_KSBH00 or ad_KSBH00=0) )
                                 where   TJID00=ad_ID0000 and KSMC00='合计';
      --修改比例
     update LJ_TJ0000 set TJ0003=decode(to_number(TJ0001),0,'     .00',to_char(to_number(TJ0002)*100/to_number(TJ0001),'9999.99'))||'%',  --入组率
                          TJ0004=decode(to_number(RYZS00),0,'     .00',to_char(to_number(TJ0002)*100/to_number(RYZS00),'9999.99'))||'%',  --入径率
                          TJ0006=decode(to_number(TJ0001),0,'     .00',to_char(to_number(TJ0005)*100/to_number(TJ0001),'9999.99'))||'%' --完成率
            where TJID00=ad_ID0000  ;
   update LJ_TJ0000 a set (LJMC00,LJBM00,VER000)=(select LJMC00 ,LJBM00,VER000 from LJ_MB0000 where LJID00=a.LJID00),
                          ZDMC00=(select ICDMC0 from LJ_MBJRTJ where LJID00=a.LJID00 and ICD000=a.ZDM000 and rownum=1) ,
                          KSMC00=nvl((select BMMC00 from BM_BMBM00 where BMBH00=a.KSBH00 and rownum=1),' ')
                       where TJID00=ad_ID0000 and LJID00>0;
   delete LJ_TJTEMP where ID0000=ad_ID0000;
   commit;
EXCEPTION
      WHEN OTHERS THEN
                as_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！';
                as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_ZS_LJ_WCQKTJ_LJ('||',ad_ID0000,as_YHMSG0,as_SYSMSG)',1,150);
                raise_application_error(-20002,substr(nvl(sqlerrm, '原因不明出错')||'！*'||as_SYSMSG,1,220));
                ROLLBACK;
END;
/



-- End of DDL Script for Procedure SD_HOSPITAL.SP_ZS_LJ_WCQKTJ_LJ