CREATE OR REPLACE PROCEDURE SP_SST_JZHKQH
(
    --入参
    P_ZDBH00    in varchar2,   --终端编号
    P_CARDNO    in varchar2, --IC卡号
    P_ERRMSG    out varchar2,
  r_cur1      out PKG_YY_QRY.MY_CURSOR --结果集游标
)
as
   VYSPBID   SF_YSGHHB.GHHBID%TYPE;      --号表id(科室排班)
   Vsysdate  date;                       --日期变量
   Vcounter  number(10);                 --计数器变量
   Vparams   varchar2(255);              --参数变量
   Verrmsg   varchar2(255);              --错误提示变量
   Ecustom   exception;                  --错误变量
   V_COUNT   number;
   V_HZXH00  varchar2(100);
   v_CDXZXH  number(5);
   CDXZCNT   number(5);
   v_CDDLXH  SF_HZBR00.DLXH00%type;
   ls_error  varchar2(220);
   V_ECPDQH  number(5);
   v_MAXHZH  number(10);
   V_DLXH00  SF_HZBR00.DLXH00%type;
   ls_BRID00 SF_BRXXB0.BRID00%type;
   LS_CARDNO IC_YHXX00.ICKH00%type;
   v_CDXZDL  SF_HZBR00.DLXH00%type;
   LS_YGBH00 BM_YGBM00.YGBH00%type;
   LS_YGXM00 BM_YGBM00.ZWXM00%type;
   LS_NUMBER number(10);
   LS_SQL_BLJK  varchar2(1000);
   V_GHJSSJ  SF_YSGHHB.GHJSSJ%type;
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- dsm         2021.04.07  允许已出报告的病人取回看号 for ZZJJK-20210407-002
-- dsm         2021.11.03  上午的2点后不能取，下午的5点半后不能取
begin
    --获取自助机终端编号对应的收费员原编号
     begin
         select YGBH00,ZWXM00 into LS_YGBH00,LS_YGXM00 from VW_SST_YGBMDYXX where ZZJBH0=P_ZDBH00;
       exception
        when no_data_found then
        Verrmsg:='终端编号:'||P_ZDBH00||'在本院SST_YGBHDY表中未对应收费员!';
        raise Ecustom;
     end;
     select SF_SST_GETICKH00(P_CARDNO) into LS_CARDNO from dual;
     begin
       select b.BRID00 into LS_BRID00
         from  IC_YHXX00 b where  ICKH00 =LS_CARDNO and ZT0000=1;
       exception
           when no_data_found then
         Verrmsg:='IC卡号:'||LS_CARDNO||'在本院IC_YHXX00表中未找到!';
         raise Ecustom;
       end;
       begin
         select to_number(MC0000) into LS_NUMBER from BM_TYZD00 where ZDMC00='二次排队科室' and BH0000='DJGHZBR' and rownum=1;
       exception
         when others then
            LS_NUMBER:=1;
       end;
       V_ECPDQH:=0;
     for c_hzmx in(
          select ID0000,JZKS00,YSPBID,GHH000 from SF_HZBR00 a where BRID00=ls_BRID00 and HZZT00<>'Z' and GHLB00>0
          --and YSPBID>0
          and HZQSRQ=to_char(sysdate,'YYYYMMDD')
          and not exists(select 1 from SF_HZBR00 where GHH000=a.GHH000 and nvl(BZ0000,' ')='二次排队' )
          and exists(select 1 from BM_TYZD00 where ZDMC00='二次排队科室' and BH0000='JZKSPZ' and instr(','||MC0000||',',','||a.JZKS00||',')>0)
          )
     loop
       select count(*) into Vcounter from YJ_YW0000 a where ZYHGHH=c_hzmx.GHH000 and MZZYBZ='0' and (jkzt00 in('2')  or nvl(BBZT00,'0')='9')
         and  exists(select 1 from BM_ZLZD00 where ZLXMID=a.ZLXMID and LBBH00 in(3,4));
       if Vcounter>0 then
          select count(*) into V_COUNT from SF_YSGHHB where GHHBID=c_hzmx.YSPBID and GHJSSJ>=to_char(sysdate-1/24,'HH24:MI:SS');
          begin
            select GHJSSJ into V_GHJSSJ  from SF_YSGHHB where GHHBID=c_hzmx.YSPBID and GHJSSJ>=to_char(sysdate,'HH24:MI:SS');
            if substr(V_GHJSSJ,1,2)<'12' and to_char(sysdate,'HH24:MI')>'14' then
                V_COUNT:=0;
            elsif  substr(V_GHJSSJ,1,2)>'15' and  to_char(sysdate,'HH24:MI:SS')>'17:30' then
                V_COUNT:=0;
            end if;
          exception
            when others then
             V_COUNT:=0;
          end;
          if V_COUNT=0 then
            Verrmsg:='医生已经下班了!';
            raise Ecustom;
          end if;
          V_ECPDQH:=V_ECPDQH+1;
          --插入当前就诊病人的后两个
          select nvl(max(HZXH00),-1) into v_MAXHZH from SF_HZBR00 where YSPBID=c_hzmx.YSPBID and HZZT00='0'
               and SFXZ00<>'1' and nvl(BZ0000,' ')<>'二次排队' ;

          select nvl(max(HZXH00),-1),nvl(max(DLXH00),-1) into v_CDXZXH,v_CDXZDL  from (
          select HZXH00,DLXH00 from SF_HZBR00 where YSPBID=c_hzmx.YSPBID and HZZT00='0'
               and SFXZ00<>'1' and nvl(BZ0000,' ')<>'二次排队'  order by DLXH00) where rownum<=LS_NUMBER;

         -- select nvl(max(DLXH00),-1) into v_CDXZDL from SF_HZBR00 where YSPBID=c_hzmx.YSPBID and HZZT00='0' and HZXH00=v_CDXZXH;
          CDXZCNT:=1;
          while CDXZCNT=1 and v_CDXZXH<v_MAXHZH loop
            select nvl(max(DLXH00),1) into V_DLXH00 from SF_HZBR00 where YSPBID=c_hzmx.YSPBID and HZZT00='0' and HZXH00=v_CDXZXH+1;
            select count(*) into CDXZCNT from SF_HZBR00 where YSPBID=c_hzmx.YSPBID and DLXH00>v_CDXZDL and DLXH00<V_DLXH00 and nvl(BZ0000,' ')='二次排队' and HZZT00='0';
            v_CDXZXH:=v_CDXZXH+1;
            v_CDXZDL:=V_DLXH00;
          end loop;
          select nvl(max(ZDDLXH),0)+1 into V_HZXH00   from SF_HZWBXH a where GHHBID=c_hzmx.YSPBID and HZLB00=0;
          if V_HZXH00=1 then
            insert into SF_HZWBXH(GHHBID,HZLB00,ZDDLXH)
            values( c_hzmx.YSPBID,0,V_HZXH00);
          else
            update SF_HZWBXH set ZDDLXH=ZDDLXH+1 where GHHBID=c_hzmx.YSPBID and HZLB00=0;
          end if;
          select nvl(max(DLXH00),0) into v_CDDLXH from SF_HZBR00 where YSPBID=c_hzmx.YSPBID
               and DLXH00>v_CDXZDL and DLXH00<V_DLXH00 and nvl(BZ0000,' ')='二次排队' ;
          if v_CDDLXH>0 then
            v_CDDLXH:=v_CDDLXH+1;
          else
            v_CDDLXH:=v_CDXZDL+1;
          end if;
          insert into sf_hzbr00
                 (ID0000,BRID00,GHH000,GHKS00,JZKS00,JZKSMC,GHYS00,GHYSXM,GHLB00,
                  HZLB00,HZLBMC,HZXH00,DLXH00,HZXHMC,
                  DJRQ00,DJSJ00,HZQSRQ,HZQSSJ,HZJZRQ,HZJZSJ,HZZT00,CZY000,SFXZ00,JZZS00,YSPBID,BZ0000)
          select      SQ_SF_HZBR00_ID0000.Nextval,a.* from (select BRID00,GHH000,GHKS00,JZKS00,JZKSMC,GHYS00,GHYSXM,GHLB00,
                  1,'普通',
                  V_HZXH00 HZXH00,
                  v_CDDLXH DLXH00,
                  'H'||Lpad(to_char(V_HZXH00),3,'0'),
                  to_char(sysdate,'yyyymmdd'),to_char(sysdate,'hh24:mi:ss'),
                  HZQSRQ,HZQSSJ,HZJZRQ,HZJZSJ,'0',
                  LS_YGBH00,'1',JZZS00,YSPBID,'二次排队'
          from        sf_hzbr00 a where ghh000=c_hzmx.GHH000 --and sfxz00='0'
                  and HZZT00<>'Z' order by ID0000 desc) a where rownum=1 ;
         begin --2021.08.23
           LS_SQL_BLJK:='begin SP_BLJK_BRECQH(:v1,:v2); end;';
             execute immediate LS_SQL_BLJK
             using in ls_BRID00,
                in c_hzmx.GHH000;
           exception
             when others then
              Vcounter:=0;
           end;
       end if;
     end loop  ;
     if V_ECPDQH=0 then
       Verrmsg:='"回看"取号失败,可能不满足取号条件!';
       raise Ecustom;
     else
       --commit;
       open r_cur1 for
         select a.HZXH00,
                a.HZXHMC,
                a.JZKSMC,
                (select ZWXM00 from BM_YGBM00 where BMBH00=a.GHYS00) JZYSXM,
                (select c.CKXXDZ from sf_ysghhb b,bm_ckbm00 c where b.ghhbid=a.YSPBID and b.ghhzck=c.ckbh00 and c.flag00='4') CKXXDZ  --诊室位置
           from SF_HZBR00 a where BRID00=ls_BRID00 and  nvl(BZ0000,' ')='二次排队' and HZZT00='0'      ;
     end if;
exception
  when Ecustom         then
    rollback;
    P_ERRMSG:=dbms_utility.format_error_backtrace||substrb(Verrmsg||'！*'||sqlerrm,1,60);
   -- raise_application_error(-20010,substrb(Verrmsg||'！*'||sqlerrm,1,60));
  when others        then
    rollback;
    P_ERRMSG:=dbms_utility.format_error_backtrace||substrb(nvl(sqlerrm, '原因不明出错')||'！*'||sqlerrm,1,110);
   -- raise_application_error(-20002,substrb(nvl(sqlerrm, '原因不明出错')||'！*'||sqlerrm,1,126));
end ;