create or replace procedure SP_YY_GQZDSFHY
as
  LS_YCSJ00  number(5);
  LS_KHYYCSJ  number(5);
  LS_NOWSJ0  varchar2(10);
  Vtmpstr     varchar2(30);        --临时变量
  Vmaxhzh     number(5);           --最大候诊序号
  Vcounter    number(10);
  LS_YZSFZFGQYY varchar2(20);
  LS_GQYYSFZDBH varchar2(20);
  LS_YYID00   XT_YYXX00.YYID00%type;
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- dsm         2016.12.13  create for MZYS-20161208-002
-- dsm         2017.03.10  在0点到7点之间任务不做处理 for YYGH-20170310-001
-- dsm         2017.03.20  过期号源，作废原来号源在最后加一条新号源 for YYGH-20170320-001
-- dsm         2017.03.26  加异常处理归档 MZYS-20170424-002
-- yanghq      2018.04.18  修改参数名称YS_YYJSSJYCSJ for MZHS-20180413-001
-- dsm         2018.10.22  过期预约是否自动补号源
-- yanghq      2019.06.18  参数控制预约病人15分钟未取号不增普通号 for MZHS-20190604-001
-- dsm         2020.06.30  医大附二 MZHS_KHYGQYCSJ>0时空号现场号 作废并在最后加一条新号源 for YYGH-20200629-001
-- dsm         2020.07.30  过期预约设置时，要同时调用SP_FZBMPT_PYDLXX做需求处理 for YYGH-20200727-001
-- dsm         2020.11.05  由于"现场过期空号作废"执行比较慢导致(例如23:59，执行了2分钟)，后面的过期语句也会执行 YYGH-20201105-001
begin
  select to_char(sysdate,'HH24:MI:SS') into LS_NOWSJ0 from dual;
  if LS_NOWSJ0>='00:00:00' and LS_NOWSJ0<='07:00:00' then
    return;
  end if;
  begin
    select to_number(nvl(max(trim(VALUE0)),'0')) into LS_YCSJ00 from XT_XTCS00 where NAME00='MZHS_YYJSSJYCSJ';
  exception  
    when others then
    LS_YCSJ00:=0;	 
  end;
  begin
    select to_number(nvl(max(trim(VALUE0)),'0')) into LS_KHYYCSJ from XT_XTCS00 where NAME00='MZHS_KHYGQYCSJ';
  exception
    when others then  
    	LS_KHYYCSJ:=0;
  end;  
  if LS_YCSJ00<=0 and LS_KHYYCSJ<=0 then
    return;
  end if;
  select nvl(max(trim(VALUE0)),'N') into LS_GQYYSFZDBH from XT_XTCS00 where NAME00='MZHS_GQYYSFZDBH' and VALUE0='Y'; --MZHS_GQYYSFZDBH过期预约是否自动补号源	  
  if   (LS_YCSJ00>0 or LS_KHYYCSJ>0) then  --现场过期空号作废，补后面 
    if LS_KHYYCSJ>0 then
      for c_hbmx in (select a.GHHBID,a.HZXH00,  a.ID0000 
         from YS_HBHZXH  a,SF_YSGHHB b where a.BRXM00 is null
            and a.GHHBID=b.GHHBID and b.GHMZSJ=to_char(sysdate,'YYYYMMDD')
            and GHJSSJ>to_char(sysdate-30/1440,'HH24:MI:SS')
            and to_char(sysdate-LS_KHYYCSJ/1440,'HH24:MI:SS')>HZQSSJ||':00' 
           -- and YYBZ00='1' 
		   and YYBZ00 IN ('1','4','6') --ljh 2021.04.13 新增电话预约，网络预约空号作废，补后面
			and YXBZ00='1' --order by GHHBID,ID0000 for update of ZY0000,YXBZ00 nowait            
            )
      loop
         update YS_HBHZXH set  
          ZY0000='过期空号'||to_char(sysdate,'YYYYMMDDHH24:MI:SS')
         ,YXBZ00='0'  where GHHBID=c_hbmx.GHHBID and HZXH00=c_hbmx.HZXH00 and ID0000=c_hbmx.ID0000;
        --update SF_YYBRB0 set YYZT00='4' where YYID00=c_yybr.YYID00;
         select max(HZSJ00),max(HZDXH0),max(HZXH00)  into Vtmpstr,Vcounter,Vmaxhzh
           from YS_HBHZXH where GHHBID=c_hbmx.GHHBID  ;
         insert into YS_HBHZXH (ID0000,GHHBID,HZXH00,YYBZ00,HZSJ00,YYRQSJ,SFZJ00,SJHZXH,HZDXH0,YYID00,ZY0000 )
         select SQ_YS_HBHZXH_ID0000.nextVal,c_hbmx.GHHBID,Vmaxhzh+1,'1',Vtmpstr,null,'0',null,Vcounter,null,'补过期空号:'||c_hbmx.ID0000
            from   dual;
         --begin
         --  update sf_ysghhb set  YYXHS0=YYXHS0-1,YYSL00=YYSL00-1 where ghhbid=c_hbmx.GHHBID;
        -- exception when others then
        --   Vcounter:=1;
        -- end;
      end loop;
      commit;  
    end if; 
    --处理过期预约 
   if LS_NOWSJ0>='00:00:00' and LS_NOWSJ0<='07:00:00' then
     return;
   end if;    
   if LS_YCSJ00>0 then  
      for c_yybr in (select BRID00,GHHBID,YYH000,YYID00
         ,(select ID0000 from YS_HBHZXH where GHHBID=a.GHHBID and HZXH00=a.YYH000) ID0000 from SF_YYBRB0  a where YYRQ00=to_char(sysdate,'YYYYMMDD') and YYZT00='0'
             and  to_char(sysdate-LS_YCSJ00/1440,'YYYYMMDDHH24:MI:SS')>YYRQ00||YYJSSJ)
      loop
         update YS_HBHZXH set BRXM00=null,YYID00=null,BRID00=null
           ,ZY0000='过期'||to_char(sysdate,'YYYYMMDDHH24:MI:SS')
           ,YXBZ00='0'  where GHHBID=c_yybr.GHHBID and HZXH00=c_yybr.YYH000;
         update SF_YYBRB0 set YYZT00='4' where YYID00=c_yybr.YYID00;

         if LS_GQYYSFZDBH='Y' then 
           select max(HZSJ00),max(HZDXH0),max(HZXH00)  into Vtmpstr,Vcounter,Vmaxhzh
             from YS_HBHZXH where GHHBID=c_yybr.GHHBID  ;
           insert into YS_HBHZXH (ID0000,GHHBID,HZXH00,YYBZ00,HZSJ00,YYRQSJ,SFZJ00,SJHZXH,HZDXH0,YYID00,ZY0000 )
            select SQ_YS_HBHZXH_ID0000.nextVal,c_yybr.GHHBID,Vmaxhzh+1,'1',Vtmpstr,null,'0',null,Vcounter,null,'补过期:'||c_yybr.ID0000
             from   dual;
           begin
             update sf_ysghhb set  YYXHS0=YYXHS0-1,YYSL00=YYSL00-1 where ghhbid=c_yybr.GHHBID;
           exception when others then
             Vcounter:=1;
           end;
         end if;  
         --处理消息
         begin
           execute immediate 'begin SP_FZBMPT_PYDLXX('||c_yybr.BRID00||','||c_yybr.YYID00||',''10'','||0||','||0||','||c_yybr.YYH000||'); end;';      
         exception
          when others then
      	   Vcounter:=0;
         end;          
      end loop;
      commit;  
    end if;    
  end if;
end;
/