------------------------------------------------------
-- Export file for user SD_HOSPITAL                 --
-- Created by Administrator on 2017-07-31, 14:50:02 --
------------------------------------------------------

spool SP_JY_SCPHDL.log

prompt
prompt Creating procedure SP_JY_SCPHDL
prompt ===============================
prompt
create or replace procedure sd_hospital.SP_JY_SCPHDL(
  pBRID00 in  number,   --入参: 病人ID
  pDDRS00 out number,   --出参：等待人数
  pDLLXID out number,   --出参：队列类型ID
  pERRMSG out varchar2  --出参：错误信息，空表示正确
)
as
    V_xmyxts    number(5);    
    V_counter   number(5); 
    V_brxm00    bm_brxxb0.brxm00%type;
    V_brxb00    bm_brxxb0.brxb00%type;    
    V_brnl00    varchar2(20);    
    V_yjdjh0    varchar2(2000); 
    V_brqfxx    varchar2(2000); 
    V_fbbh00    bm_brxxb0.fbbh00%type;
    V_ybsfjs    varchar2(200);
    V_hmscfs    varchar2(200);
    V_dllxid    JY_DLLX00.dllxid%type;  
    V_jzms00    varchar2(200); 
    V_hmyxjb    varchar2(200); 
    V_ickh00    ic_yhxx00.ickh00%type;    
    V_ddrs00    number(5);
    V_phh000    jy_PDJHB0.DLH000%type;    
      V_SYSMSG    varchar2(2000); 
      ls_error    varchar2(2000);   
    V_pdjzfs    varchar2(200);
    JY_PDJHJZBRPDFS     varchar2(200);
    vJZBZSL     number(5);
    JY_PDJHJZXM varchar2(200); 
    vJZXMSL     number(5);   
    JY_PDJHJZPDXM       varchar2(200);  
    vJZBZYXMSL  number(5);  
    vJZBZHXMSL  number(5);  
    vPDJHDLTSCLXM       varchar2(200);  
    V_sfwp00 char(1);
    V_hmqz00 JY_DLLX00.dllxbh%type;
    V_phxh00 number(5);    
    V_KZCLSJ number(5); 
    JY_JYQHYECKZLXM XT_XTCS00.VALUE0%type;
    V_yedllx   JY_DLLX00.dllxid%type; 
    V_yenl00   JY_YW0000.NL0000%type;
    V_yexm00   JY_YW0000.YEXM00%type;
    V_yezlxm   varchar2(200);
    V_count0   number(5);   
    V_count1   number(5);  
    JY_JYPDQHSFBQYYXDL XT_XTCS00.VALUE0%type;
    cursor cur_yjdjh is 
        select YJDJH0 
          from jy_yw0000 
         where sqrq00 between to_char(sysdate-V_xmyxts,'YYYYMMDD') and to_char(sysdate,'YYYYMMDD') 
           and sfbz00 in ('0','1') and brid00=pBRID00 ;
--Person              Date            Comments
--daihq               2016.12.21      create for LIS9-20161216-001    
--daihq               2017.04.10      增加参数JY_JYPDQHSFBQYYXDL控制是否不启用优先队列 for LIS9-20170410-001       
begin
      --取参数值：项目有效天数JY_JYQHXMYXTS
    begin
        select to_number(value0) into V_xmyxts from xt_xtcs00 where name00='JY_JYQHXMYXTS';
    exception
    when others then    
        V_xmyxts:=10;
    end;
    --检验取号指定婴儿窗口的诊疗项目和婴儿窗口类型,维护诊疗项目ID(BM_ZLZD00,ZLXMID,多个用逗号隔开);婴儿窗口类型ID(JY_DLLX00.DLLXID)，例如：19391;2，N 不开展婴儿窗口
    begin
        select nvl(max(trim(VALUE0)),'N') into JY_JYQHYECKZLXM from xt_xtcs00 where name00='JY_JYQHYECKZLXM';
    exception
    when others then    
        JY_JYQHYECKZLXM:='N';
    end;
    --检验排队取号是否不启用优先队列（优先队列会以单独的数据开头（例如8开头）），Y 是，N 否，默认N
    begin
        select nvl(max(trim(VALUE0)),'N') into JY_JYPDQHSFBQYYXDL from xt_xtcs00 where name00='JY_JYPDQHSFBQYYXDL';
    exception
    when others then    
        JY_JYPDQHSFBQYYXDL:='N';
    end;
    --获取病人基本信息
    begin
        select brxm00,brxb00,sf_bq_csrqtonl(brcsrq),fbbh00 into V_brxm00,V_brxb00,V_brnl00,V_fbbh00 from bm_brxxb0 where brid00=pBRID00;
    exception
    when others then
        pERRMSG:='获取病人基本信息发生错误！'; 
        return;
    end;
    --判断是否存在未取号的项目信息
    select count(1) into V_counter 
      from jy_yw0000 
     where mzzybz='0' and sqrq00 between to_char(sysdate-V_xmyxts,'YYYYMMDD') and to_char(sysdate,'YYYYMMDD') 
       and BRID00=pBRID00 and zlxmid not in (1076) AND JGZT00 IN ('0');
       --1076 附加费项目
    if V_counter=0 then
        pERRMSG:=V_brxm00||'您没有检验项目,无法打印号码....！'; 
        return; 
    end if;
    --获取项目信息
    V_yjdjh0:='0';
    for yw in cur_yjdjh loop
        V_yjdjh0:=V_yjdjh0||','||yw.yjdjh0;
    end loop;
    --if V_yjdjh0<>'' then
    --   V_yjdjh0:=substr(V_yjdjh0,1,length(V_yjdjh0)-1) ;
    --end if; 
    
    --判断欠费信息        
    begin
        select SF_JY_HQQFXX(pBRID00,'',V_yjdjh0,'',0,0,0) into V_brqfxx from dual;
    exception
    when others then
        pERRMSG:=V_brxm00||'获取账户信息失败！'; 
        return; 
    end;                
    V_brqfxx:=trim(substr(V_brqfxx,instr(V_brqfxx,'|',1,2)+1,length(V_brqfxx)));
    if V_brqfxx<>'0' then
        pERRMSG:= V_brxm00||'病人金额不够，请先到收费处补交：' || V_brqfxx|| ' 元后再操作！';    
        return;
    end if;   
    --判断医保病人是否结算
    select SF_JY_PDYBBRSFJS(pBRID00,V_yjdjh0) into V_ybsfjs from dual;
    if V_ybsfjs='N' then    
        pERRMSG:= V_brxm00||'您还没有结算，请先到收费处结算后再取号！';    
        return;
    end if;
    --判断是否已取过号，且号还是有效状态
    select count(1) into V_counter 
      from jy_pdjhb0 
     where brid00=pBRID00
       and jhbz00 in ('0','1','3') --and sfdy00='Y' 
       and PHRQ00=to_char(sysdate,'YYYYMMDD');
    if V_counter>0 then   
        pERRMSG:= V_brxm00||'您已经取过号,并且你的号还有效,不需要再取！';    
        return;
    end if; 
    --取参数值：判断取号方式JY_JYQHFMSCFS 
    begin
        select trim(value0) into V_hmscfs from xt_xtcs00 where name00='JY_JYQHFMSCFS';
    exception
    when others then    
        V_hmscfs:='0';
    end; 
    if V_hmscfs='0' then  --南平市医院 只取一个号
        --获取队列类型
        begin   
            select DLLXID into V_DLLXID from 
                  (select C.* from jy_yw0000 a, JY_DLBBDY b,JY_DLLX00 c where a.bbid00=b.bbid00  and 
                  a.sqrq00 between to_char(sysdate-V_xmyxts,'YYYYMMDD') and to_char(sysdate,'YYYYMMDD') 
                  and b.DLLXID=c.DLLXID and a.zlxmid not in (1076) and a.brid00=Pbrid00 and jgzt00='0' 
                  order by c.YXZT00 desc ) where rownum=1;  
        exception
        when no_data_found then
             V_DLLXID:=2;
        end;
        --判断优先级别，即是否急诊      
        select count(1) into V_counter from jy_yw0000 where brid00=Pbrid00 and  zlxmid not in (1076) and 
              sqrq00 between to_char(sysdate-V_xmyxts,'YYYYMMDD') and to_char(sysdate,'YYYYMMDD')
              and  YXBZ00='Y' and JGZT00='0';     
        if V_counter>0 then
           V_hmyxjb:='1';
        else
           V_hmyxjb:='0';           
        end if;            
    elsif V_hmscfs='1' then     
        --获取队列类型
        begin   
            select DLLXID into V_DLLXID from 
            (select  DLLXID from  vw_jy_yw0000_DLXS00 where sqrq00 between to_char(sysdate-V_xmyxts,'YYYYMMDD') and to_char(sysdate,'YYYYMMDD') 
               and  brid00=Pbrid00 order by YXZT00 desc,DLLXID ) where rownum=1;
        exception
        when no_data_found then
             V_DLLXID:=2;
        end;             
        --判断优先级别，即是否急诊  
        --取判断急诊方式参数    
            begin
                select trim(value0) into V_pdjzfs from xt_xtcs00 where name00='JY_JYQHPDJZFS';
            exception
            when others then    
                V_pdjzfs:='1';
            end;
                                        
            if V_pdjzfs='1' then
                V_hmyxjb:='0';
        --排队叫号急诊病人判断方式:0:急诊标志；1:项目;2:急诊标志和项目；3:急诊标志或项目;4:急诊标志+项目，急症项目;默认:0                
                begin 
                    select trim(value0) into JY_PDJHJZBRPDFS from xt_xtcs00 where name00 in ('JY_PDJHJZBRPDFS');
                exception
                when no_data_found then                    
                   JY_PDJHJZBRPDFS:='0';
                end;   
        --急诊标志
                select count(1) into vJZBZSL 
                  from jy_yw0000 
                 where brid00=Pbrid00 and zlxmid not in (1076) 
                   and sqrq00 between to_char(sysdate-V_xmyxts,'YYYYMMDD') and to_char(sysdate,'YYYYMMDD') 
                   and YXBZ00='Y' and JGZT00='0' and (bbid00 in (select BBID00 from JY_DLBBDY where dllxid in (V_dllxid)) or V_dllxid=0);
        --急诊项目
                begin 
                    select trim(decode(trim(value0),'N','0',value0)) into JY_PDJHJZXM  from xt_xtcs00 where name00 in ('JY_PDJHJZXM');
                exception
                when no_data_found then                    
                   JY_PDJHJZXM:='0';
                end;                
                JY_PDJHJZXM:=','||JY_PDJHJZXM||',';
                select count(1) into vJZXMSL 
                  from jy_yw0000 
                 where brid00=Pbrid00 and zlxmid not in (1076) 
                   and sqrq00 between to_char(sysdate-V_xmyxts,'YYYYMMDD') and to_char(sysdate,'YYYYMMDD') 
                   and JGZT00='0' 
--                   and zlxmid in (JY_PDJHJZXM) 
                   and instr(JY_PDJHJZXM,','||zlxmid||',')>0 
                   and (bbid00 in (select BBID00 from JY_DLBBDY where dllxid in (V_dllxid)) or V_dllxid=0);

        --急症标志和项目
                begin 
                    select trim(decode(trim(value0),'N','0',value0)) into JY_PDJHJZPDXM from xt_xtcs00 where name00 in ('JY_PDJHJZPDXM');
                exception
                when no_data_found then                    
                   JY_PDJHJZPDXM:='0';
                end;                 
                JY_PDJHJZPDXM:=','||JY_PDJHJZPDXM||',';
                select count(1) into vJZBZYXMSL 
                  from jy_yw0000 
                 where brid00=Pbrid00 and zlxmid not in (1076) 
                   and sqrq00 between to_char(sysdate-V_xmyxts,'YYYYMMDD') and to_char(sysdate,'YYYYMMDD') 
                   and JGZT00='0' 
                   --and zlxmid in (JY_PDJHJZPDXM) 
                   and instr(JY_PDJHJZPDXM,','||zlxmid||',')>0
                   and yxbz00='Y'  and (bbid00 in (select BBID00 from JY_DLBBDY where dllxid in (V_dllxid)) or V_dllxid=0);
                   

        --急症标志或者项目
                select count(1) into vJZBZHXMSL 
                  from jy_yw0000 
                 where brid00=Pbrid00 and zlxmid not in (1076) 
                   and sqrq00 between to_char(sysdate-V_xmyxts,'YYYYMMDD') and to_char(sysdate,'YYYYMMDD')  
                   and JGZT00='0' 
--                   and (zlxmid in (JY_PDJHJZPDXM) or yxbz00='Y') 
                   and (instr(JY_PDJHJZPDXM,','||zlxmid||',')>0  or yxbz00='Y')                   
                   and (bbid00 in (select BBID00 from JY_DLBBDY where dllxid in (V_dllxid)) or V_dllxid=0);

                if (JY_PDJHJZBRPDFS='0') and (vJZBZSL>0) then
                    V_hmyxjb:='1'; 
                elsif (JY_PDJHJZBRPDFS='1') and (vJZXMSL>0)  then
                    V_hmyxjb:='1';
                elsif (JY_PDJHJZBRPDFS='2') and (vJZBZYXMSL>0)  then
                    V_hmyxjb:='1';
                elsif (JY_PDJHJZBRPDFS='3') and  (vJZBZHXMSL>0)  then
                    V_hmyxjb:='1';
                elsif (JY_PDJHJZBRPDFS='4') and  ((vJZXMSL>0) or (vJZBZYXMSL>0))  then
                    V_hmyxjb:='1' ;
                end if;   
                
          elsif V_pdjzfs='0' then  --全部是急诊项目才是急诊病人
                V_hmyxjb:='1';
                begin 
                    select trim(decode(trim(value0),'N','0',value0)) into JY_PDJHJZPDXM from xt_xtcs00 where name00 in ('JY_PDJHJZPDXM');
                exception
                when no_data_found then                    
                   JY_PDJHJZPDXM:='0';
                end;     
                JY_PDJHJZPDXM:=','||JY_PDJHJZPDXM||',';
                begin 
                    select trim(decode(trim(value0),'N','0',value0)) into JY_PDJHJZXM  from xt_xtcs00 where name00 in ('JY_PDJHJZXM');
                exception
                when no_data_found then                    
                   JY_PDJHJZXM:='0';
                end; 
                JY_PDJHJZXM:=','||JY_PDJHJZXM||',';
                begin 
                    select trim(decode(trim(value0),'N','0',value0)) into vPDJHDLTSCLXM  from xt_xtcs00 where name00 in ('JY_PDJHDLTSCLXM');
                exception
                when no_data_found then                    
                   vPDJHDLTSCLXM:='0';
                end;                
                vPDJHDLTSCLXM:=','||vPDJHDLTSCLXM||',';
                select count(1) into vJZBZSL 
                  from jy_yw0000 
                 where brid00=Pbrid00 and zlxmid not in (1076) 
                   and sqrq00 between to_char(sysdate-V_xmyxts,'YYYYMMDD') and to_char(sysdate,'YYYYMMDD')
                   and jgzt00='0' 
                   and (bbid00 in (select BBID00 from JY_DLBBDY where dllxid in (V_dllxid)) or (V_dllxid=0)
                        or ((2=V_dllxid) 
--                        and zlxmid in (vPDJHDLTSCLXM)
                        and (instr(vPDJHDLTSCLXM,','||zlxmid||',')>0 )
                        ) ) 
--                        and ((zlxmid not in (JY_PDJHJZXM) ) and  ((yxbz00='N') or  (zlxmid not in (JY_PDJHJZPDXM))));
                        and ((instr(JY_PDJHJZXM,','||zlxmid||',')=0 ) and  ((yxbz00='N') or  (instr(JY_PDJHJZPDXM,','||zlxmid||',')=0)));
                        
               if (vJZBZSL<>'0') and (vJZBZSL<>'-1') then
                  V_hmyxjb:='0' ;
               end if;
        end if; 
                        
    end if;
    
    --开启婴儿窗口的处理
    if trim(JY_JYQHYECKZLXM)<>'N' then      
      if instrb(trim(JY_JYQHYECKZLXM),';')>0 then
         V_yedllx := to_number(substrb(trim(JY_JYQHYECKZLXM),instrb(trim(JY_JYQHYECKZLXM),';')+1,1));
         V_yezlxm := substrb(trim(JY_JYQHYECKZLXM),1,instrb(trim(JY_JYQHYECKZLXM),';')-1);
      else
         V_yezlxm := trim(JY_JYQHYECKZLXM);
         V_yedllx := 2;
      end if;
      select distinct nl0000,yexm00 into V_yenl00,V_yexm00 from JY_YW0000 where brid00=Pbrid00 and  zlxmid not in (1076) and 
              sqrq00 between to_char(sysdate-V_xmyxts,'YYYYMMDD') and to_char(sysdate,'YYYYMMDD') and JGZT00='0' and rownum=1; 
      if (V_yenl00<=5) or (trim(V_yexm00)||'A'<>'A') then
        select count(1) into V_counter from BM_CKBM00 where FLAG00='J' and CKLXID=V_yedllx and ZT0000='0';
        if trim(V_yezlxm)='Y' then
          if (V_counter>0) then
              V_dllxid := V_yedllx; 
          else
            V_hmyxjb:='1' ;  
          end if;
        else 
          select count(1) into V_count0 from JY_YW0000 where brid00=Pbrid00 and  zlxmid not in (1076) and 
                sqrq00 between to_char(sysdate-V_xmyxts,'YYYYMMDD') and to_char(sysdate,'YYYYMMDD') 
                and JGZT00='0' and instrb(','||trim(V_yezlxm)||',',','||to_char(zlxmid)||',')>0; 
          select count(1) into V_count1 from JY_YW0000 where brid00=Pbrid00 and  zlxmid not in (1076) and 
                sqrq00 between to_char(sysdate-V_xmyxts,'YYYYMMDD') and to_char(sysdate,'YYYYMMDD') 
                and JGZT00='0' and instrb(','||trim(V_yezlxm)||',',','||to_char(zlxmid)||',')=0; 
          if (V_counter>0) then
            if (V_count0>0) and (V_count1=0) then --只开一项‘血常规’项目
              V_dllxid := V_yedllx; 
            end if;
          elsif (V_count0>0) and (V_count1=0) then --只开一项‘血常规’项目且婴儿窗口关闭
            V_hmyxjb:='1' ;  
          end if;   
        end if;         
      end if;
    end if;  
    
    --生成队列号表  
    delete from JY_PDJHB0 where PHRQ00<>to_char(sysdate,'YYYYMMDD');     
    select count(DLH000) into V_counter from JY_PDJHB0 where PHRQ00=to_char(sysdate,'YYYYMMDD') and JHBZ00='0' and brid00=Pbrid00 and DLLXID=V_dllxid; 
    if V_counter=0 then
        V_sfwp00:='1';
    else
        V_sfwp00:='0';        
    end if;              
    select DLLXBH into V_hmqz00 from JY_DLLX00 where DLLXID=V_dllxid;
    V_KZCLSJ:=0;
    select count(1) into V_counter from xt_xtcs00 where name00='JY_DLCRBZQK' and value0='0';
    while V_counter<>1 loop
    
        V_KZCLSJ:=V_KZCLSJ+1;
        if V_KZCLSJ>=40 then    
            update xt_xtcs00 set value0='0'  where name00='JY_DLCRBZQK';                  
        end if;
        select count(1) into V_counter from xt_xtcs00 where name00='JY_DLCRBZQK' and value0='0';
    end loop;
   
    update xt_xtcs00 set value0='1' where name00='JY_DLCRBZQK';
    
    --2017.04.10 daihq add
    if trim(JY_JYPDQHSFBQYYXDL)='Y' then
      V_hmyxjb:='0';
    end if;
       
    if V_hmyxjb='0' then
        if V_sfwp00='1' then             
            select nvl(max(to_number(substr(DLH000,length(DLH000)-2,3))),0)+1 into V_phxh00 
              from JY_PDJHB0  where DLHMYX='0' and DLLXID=V_dllxid and  PHRQ00=to_char(sysdate,'YYYYMMDD');
            V_phh000:=V_hmqz00||lpad(V_phxh00,3,'0');
        end if;    
    elsif V_hmyxjb='1' then
    
        if V_sfwp00='1' then        
            select nvl(max(to_number(substr(DLH000,length(DLH000)-2,3))),0)+1 into V_phxh00 
              from JY_PDJHB0  where DLHMYX='1' and  PHRQ00=to_char(sysdate,'YYYYMMDD');
            V_phh000:='8'||lpad(V_phxh00,3,'0');
        end if;  
                    
    end if;    
    select count(1) into V_ddrs00 from jy_PDJHB0 where DLLXID=V_dllxid and jhbz00 not in ('2'); 
                     
    pDDRS00 := V_ddrs00;
    pDLLXID := V_dllxid;
    --记录jy_pdjhb0
    insert into jy_PDJHB0(DLID00,DLH000,BRID00,brxm00,CYCKBH,CKMC00,PHRQ00,JHCS00,JHBZ00,DLLXID,DLHMYX,QHSJ00)
    values
    (SQ_JY_PDJHB0_DLID00.nextval,V_phh000,pBRID00,V_brxm00,null,null,to_char(SYSdate,'YYYYMMDD'),0,'0',V_dllxid,V_hmyxjb,to_char(sysdate,'HH24:MI:SS'));   
    --占用标志释放
    update xt_xtcs00 set value0='0' where name00='JY_DLCRBZQK';
exception
    when others then
        pERRMSG:=ls_error||substr(SQLERRM||'执行过程SP_JY_SCPHDL',1,150);
        raise_application_error(-20011,substrb(pERRMSG,1,220));
end;
/


spool off