-- Start of DDL Script for Procedure SD_HOSPITAL.SP_BQ_YLFYJYTS
-- Generated 14-六月-2018 15:31:16 from SD_HOSPITAL@fw2

CREATE OR REPLACE 
PROCEDURE sp_bq_ylfyjyts(ad_ZYID00 in number ,
                                               ad_ID0000 out number 
                                             )
as                                             
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- dsm         2013.08.07  create for BQ-20130729-001                                               
        ls_ZYTS00    number(5)            ;    --住院天数
        ls_COUNT0    number(5)            ;    --计数器
        ls_ZYZCXM    XT_XTCS00.VALUE0%TYPE;    --系统定义的统计收费项目  住院诊察费
        ls_ZYHLXM    XT_XTCS00.VALUE0%TYPE;    --系统定义的统计收费项目  住院护理费
        ls_CXXYXM    XT_XTCS00.VALUE0%TYPE;    --系统定义的统计收费项目  持续吸氧项目
        ls_RYRQ00    ZY_BRXXB0.RYRQ00%TYPE;    --入院日期
        ls_CYDJRQ    ZY_BRXXB0.CYDJRQ%TYPE;    --病区出院登记日期
        ls_LSCLC0    varchar2(200)        ;    --临时处理串
        ls_CZRQ00    ZY_FYMX00.CZRQ00%TYPE;    --记账操作日期
        I            number(3)            ;    --计数器
        J            number(3)            ;    --计数器
        ls_SFXMID    BM_YYSFXM.SFXMID%TYPE;    --SFXMID
        ls_HJJE00    ZY_FYMX00.HJJE00%TYPE;    --合计金额
        ls_YZCWDJ    BM_YYSFXM.SFJE00%TYPE;    --优质病房床位单价  参数 BQ_CWXMSFTSBLSQNJWF 定义
        ls_LSCLC1    varchar2(200)        ;    --临时处理串
        ls_CWXMSTR   varchar2(100);            --不收空调费的床位项目 BQ_BLWSQKTFDCWF
        E_NotEnoughBL      	     exception;    --错误变量不够
        E_BLWrong                exception;    --变量定义错误
        E_YZBFCWDJCW             exception;    --优质病房床位定义错误
        ls_FJXDPBF   varchar2(50);             --房间消毒及陪伴费
        LS_CYDJCXDYTS number(3);
       --优质病房床位
        cursor CUR_BQ_YZBFCW is
          select a.CZRQ00,sum(a.HJJE00) from BM_YYSFXM c,ZY_FYMX00 a,ZY_BRFY00 b where b.ZYID00=ad_ZYID00 and b.DJH000=a.DJH000 and a.XMBH00+0=c.SFXMID and c.ZYFPID=5
                 --  and (a.XMBH00=ad_SFXMID or ad_SFXMID=0)
                 group by a.CZRQ00 having sum(a.HJJE00)>=ls_YZCWDJ;
       --层流病房床位
        cursor CUR_BQ_CLBFCW is
          select a.CZRQ00,sum(a.HJJE00) from BM_YYSFXM c,ZY_FYMX00 a,ZY_BRFY00 b where b.ZYID00=ad_ZYID00 and b.DJH000=a.DJH000 and a.XMBH00+0=c.SFXMID and c.ZYFPID=5
           --       and (a.XMBH00=ad_SFXMID or ad_SFXMID=0)
                 and (instr(','||ls_CWXMSTR||',',','||a.XMBH00||',')>0 ) group by a.CZRQ00 ;
                 
                 
        --持续吸氧项目一天内超70
        cursor CUR_BQ_CXXY is
           select b.CZRQ00,sum(b.HJJE00) from ZY_FYMX00 b,ZY_BRFY00 c where c.ZYID00=ad_ZYID00 and  b.DJH000=c.DJH000 and b.XMBH00+0=ls_SFXMID
                  group by b.CZRQ00 having sum(b.HJJE00)>70;
begin
   select count(*) into LS_CYDJCXDYTS  from XT_XTCS00 where NAME00='BQ_CYDJCXDYTS' and VALUE0='Y';
   if LS_CYDJCXDYTS=0 then 
      ad_ID0000:=-1;
      return;
   end if;
   select  SQ_BM_YYSFTJ_ID0000.nextval  into  ad_ID0000  from  dual;
   
   select  RYRQ00,decode(CYDJRQ,null,to_char(sysdate,'YYYYMMDD'),CYDJRQ) into  ls_RYRQ00,ls_CYDJRQ from ZY_BRXXB0 where ZYID00=ad_ZYID00;
    begin
        select trim(VALUE0)  into ls_ZYZCXM   from  XT_XTCS00  where  NAME00 = 'BQ_YLFYJY_ZYZCXM';
    exception
       when others then
         raise E_NotEnoughBL;
         return;
    end;
    begin
        select trim(VALUE0)  into ls_ZYHLXM   from  XT_XTCS00  where  NAME00 = 'BQ_YLFYJY_ZYHLXM';
    exception
       when others then
         raise E_NotEnoughBL;
         return;
    end;
    begin
        select trim(VALUE0)  into ls_CXXYXM   from  XT_XTCS00  where  NAME00 = 'BQ_YLFYJY_CXXYXM';
    exception
       when others then
         raise E_NotEnoughBL;
         return;
    end;
    begin
        select trim(VALUE0)  into ls_CWXMSTR   from  XT_XTCS00  where  NAME00 = 'BQ_YLFYJY_ZYCWXM';
    exception
       when others then
         ls_CWXMSTR:='0';
    end;    
    
    begin
        select trim(VALUE0)  into ls_FJXDPBF   from  XT_XTCS00  where  NAME00 = 'BQ_YLFYJY_XDPBF';
    exception
       when others then
         ls_FJXDPBF:='0';
    end;    
        
    begin
        select trim(VALUE0)  into ls_LSCLC0   from  XT_XTCS00  where  NAME00 = 'BQ_CWXMSFTSBLSQNJWF';
    exception
       when others then
         raise E_YZBFCWDJCW;
         return;
    end;
    
    if substr(ls_LSCLC0,1,1)<>'Y'  then
    begin
      raise E_YZBFCWDJCW;
      return;
    end;
    else begin
    	ls_YZCWDJ:=to_number(trim(substr(ls_LSCLC0,3,10)));
    	exception
       when others then
         raise E_YZBFCWDJCW;
         return;
    end;
    end if;

    J:=0;
    while J<3 loop
      if J=0 then
        select trim(ls_ZYZCXM) into ls_LSCLC0 from dual;
      elsif j=1 then
        select trim(ls_ZYHLXM) into ls_LSCLC0 from dual;
      else
        select trim(ls_CXXYXM) into ls_LSCLC0 from dual;
      end if;
      while length(ls_LSCLC0)>0 loop
        select instr(ls_LSCLC0,',') into I from dual;
        if I>0 then
          begin
             ls_LSCLC1:=substr(ls_LSCLC0,1,I-1);
             ls_LSCLC0:=substr(ls_LSCLC0,I+1,length(ls_LSCLC0)-I);
          end;
          else 
          begin
            ls_LSCLC1:=ls_LSCLC0;
      	    ls_LSCLC0:='';
          end;
        end if;
        begin
          select count(*) into ls_COUNT0 from BM_YYSFXM where SFXMID=to_number(ls_LSCLC1);
        exception
          when others then
      	   raise E_BLWrong;
           return;
        end;
        if ls_COUNT0=0 then
      	   raise E_BLWrong;
           return;
        end if;
      end loop;
     J:=J+1;
    end loop;
        
 ---------------------------以上为参数定义校验------------------------------------

             select trunc(to_date(ls_CYDJRQ,'YYYYMMDD')-to_date(ls_RYRQ00,'YYYYMMDD')) into ls_ZYTS00 from dual;
             select nvl(sum(a.XMSL00),0) into ls_COUNT0 from BM_YYSFXM c,ZY_FYMX00 a,ZY_BRFY00 b where b.ZYID00=ad_ZYID00 and b.DJH000=a.DJH000
                    and a.XMBH00+0=c.SFXMID and c.ZYFPID=5 and c.BH0000 like '01109%'
                    and instr(','||ls_FJXDPBF||',',','||c.SFXMID||',')=0 ;
             if ls_ZYTS00<> ls_COUNT0 then  --床位费数量不等于住院天数
               begin
                 insert into BM_YYSFTJ(ID0000,BMBH00,SFXM01,SFXM02,SFXM03) select ad_ID0000,ad_ZYID00,1,ls_COUNT0,ls_ZYTS00 from dual;   --SFXM01=1床位费不等于住院天数
               end;
             end if;

             J:=0;
             while J<3 loop
               if J=0 then
                 select trim(ls_ZYZCXM) into ls_LSCLC0 from dual;
               elsif j=1 then
                 select trim(ls_ZYHLXM) into ls_LSCLC0 from dual;
               else
                 select trim(ls_CXXYXM) into ls_LSCLC0 from dual;
               end if;
               ls_COUNT0:=0;
               while length(ls_LSCLC0)>0 loop
                  select instr(ls_LSCLC0,',') into I from dual;
                  if I>0 then
                    begin
        	            ls_SFXMID:=to_number(substr(ls_LSCLC0,1,I-1));
                      ls_LSCLC0:=substr(ls_LSCLC0,I+1,length(ls_LSCLC0)-I);
                    end;
                    else begin
        	            ls_SFXMID:=to_number(ls_LSCLC0);
      	              ls_LSCLC0:='';
                   end;
                  end if;

                  if (J=0) or (J=1) then
                    begin
                  	  select nvl(sum(a.XMSL00),0)+ls_COUNT0 into ls_COUNT0 from ZY_FYMX00 a,ZY_BRFY00 b where b.ZYID00=ad_ZYID00 and b.DJH000=a.DJH000 and a.XMBH00+0=ls_SFXMID;
                    end;
                  elsif J=2 then
                    begin
                      open CUR_BQ_CXXY;
                        loop
                          fetch CUR_BQ_CXXY into  ls_CZRQ00,ls_HJJE00;
                          exit when CUR_BQ_CXXY%NOTFOUND;
                               insert into BM_YYSFTJ(ID0000,BMBH00,SFXM01,SFXM02,BMMC00,BZSM01) select ad_ID0000,ad_ZYID00,4,ls_HJJE00,ls_CZRQ00,'持续吸氧1日内超标70元' from dual;   --SFXM01=4开持续吸氧项目一天内收费超过70元
                        end loop;
                      close CUR_BQ_CXXY;

                    end;
                  end if;
               end loop;
               if ((J=0) or (J=1)) and (ls_ZYTS00<> ls_COUNT0) then
                  insert into BM_YYSFTJ(ID0000,BMBH00,SFXM01,SFXM02,SFXM03,BZSM01) select ad_ID0000,ad_ZYID00,J+2,ls_COUNT0,ls_ZYTS00,decode(J,0,'诊察费数量与住院天数不符','护理费数量与住院天数不符')||'(住院天数='||ls_ZYTS00||',实际收费数量='||ls_COUNT0||')' from dual;   --SFXM01=2,3住院诊察，住院护理费不等于住院天数
               end if;
               J:=J+1;
             end loop;
            if ls_CWXMSTR='0' then
             open CUR_BQ_YZBFCW;
               loop
                 fetch CUR_BQ_YZBFCW into  ls_CZRQ00,ls_HJJE00;
                 exit when CUR_BQ_YZBFCW%NOTFOUND;
                    select count(a.DJH000) into ls_COUNT0 from ZY_FYMX00 a,ZY_BRFY00 b,BM_YYSFXM c where b.ZYID00=ad_ZYID00 and b.DJH000=a.DJH000
                           and b.CZRQ00=ls_CZRQ00 and a.XMBH00+0=c.SFXMID and (substr(c.BH0000,1,5)='01107' or substr(c.BH0000,1,5)='01108');
                    if ls_COUNT0>0 then
                       insert into BM_YYSFTJ(ID0000,BMBH00,SFXM01,SFXM02,BMMC00,BZSM01) select ad_ID0000,ad_ZYID00,5,ls_HJJE00,ls_CZRQ00,'住优质病房又收取空调费' from dual;   --SFXM01=5 住优质病房又收取空调费
                    end if;
               end loop;
             close CUR_BQ_YZBFCW;
             end if; 
             open CUR_BQ_CLBFCW;
               loop
                 fetch CUR_BQ_CLBFCW into  ls_CZRQ00,ls_HJJE00;
                 exit when CUR_BQ_CLBFCW%NOTFOUND;
                    select count(a.DJH000) into ls_COUNT0 from ZY_FYMX00 a,ZY_BRFY00 b,BM_YYSFXM c where b.ZYID00=ad_ZYID00 and b.DJH000=a.DJH000
                           and b.CZRQ00=ls_CZRQ00 and a.XMBH00+0=c.SFXMID and (substr(c.BH0000,1,5)='01107' or substr(c.BH0000,1,5)='01108');
                    if ls_COUNT0>0 then
                       insert into BM_YYSFTJ(ID0000,BMBH00,SFXM01,SFXM02,BMMC00,BZSM01) select ad_ID0000,ad_ZYID00,6,ls_HJJE00,ls_CZRQ00,'住层流病房又收取空调费' from dual;   --SFXM01=6 住层流病房又收取空调费
                    end if;
               end loop;
             close CUR_BQ_CLBFCW;

exception
      when E_NotEnoughBL then
             --   as_YHMSG0:='进行查询定义的参数不够,请确认是否存在以下三个参数定义:BQ_YLFYJY_ZYZCXM;BQ_YLFYJY_ZYHLXM;BQ_YLFYJY_CXXYXM！';
             --   as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_BQ_YLFYJYTS('||as_KSRQ00||','||as_JSRQ00||','||
              --      as_DQBQ00||','||'ad_ID0000,as_YHMSG0,as_SYSMSG',1,150);
                RAISE_APPLICATION_ERROR(-20001,'进行查询定义的参数不够,请确认是否存在以下三个参数定义:BQ_YLFYJY_ZYZCXM;BQ_YLFYJY_ZYHLXM;BQ_YLFYJY_CXXYXM！*'||sqlerrm);
                rollback;
      when E_BLWrong then
       --as_YHMSG0:='进行查询定义的参数有误，请核对以下三个参数值在BM_YYSFXM.SFXMID是否存在:BQ_YLFYJY_ZYZCXM;BQ_YLFYJY_ZYHLXM;BQ_YLFYJY_CXXYXM！';
       --         as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_BQ_YLFYJYTS('||as_KSRQ00||','||as_JSRQ00||','||
       --             as_DQBQ00||','||'ad_ID0000,as_YHMSG0,as_SYSMSG',1,150)||ls_LSCLC1;
       RAISE_APPLICATION_ERROR(-20002,'进行查询定义的参数有误，请核对以下三个参数值在BM_YYSFXM.SFXMID是否存在:BQ_YLFYJY_ZYZCXM;BQ_YLFYJY_ZYHLXM;BQ_YLFYJY_CXXYXM！*'||sqlerrm);       
                rollback;
      when E_YZBFCWDJCW then
      --as_YHMSG0:='进行查询定义的参数有误，优质病房床位参数 BQ_CWXMSFTSBLSQNJWF 定义有误，该参数参数值应该为: Y,具体金额 ！';
        --        as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_BQ_YLFYJYTS('||as_KSRQ00||','||as_JSRQ00||','||
        --            as_DQBQ00||','||'ad_ID0000,as_YHMSG0,as_SYSMSG',1,150);
           RAISE_APPLICATION_ERROR(-20003,'进行查询定义的参数有误，优质病房床位参数 BQ_CWXMSFTSBLSQNJWF 定义有误，该参数参数值应该为: Y,具体金额 ！*'||sqlerrm);             
                rollback;
      when others then
           RAISE_APPLICATION_ERROR(-20004,'出错原因不详,请记录此信息并和系统管理员联系！*'||sqlerrm);             
                    
                rollback;
end;
/



-- End of DDL Script for Procedure SD_HOSPITAL.SP_BQ_YLFYJYTS