CREATE OR REPLACE PROCEDURE SP_WLPT_SST_YYQR(
  id0000     in  varchar2,   --预约队列标识
  cardno     in varchar2,   --卡号
  cartype    in number, --IC卡类型
  custname  out varchar2,    --病人姓名
  groupname  out varchar2,   --医生姓名
  qno000  out varchar2,      --预约号
  czsj00  out varchar2,     --就诊日期
  yyksmc  out varchar2,     --预约科室
  hzsj00  out varchar2,     --候诊时间
  outstr  out varchar2   --错误信息
    )
as
-- MODIFICATION HISTORY
-- Person      Date         Comments
-- yanghq      2019.07.31   省二自助机确认取号，从sp_sst_yyqr复制而来
-- dsm         2023.04.23   提前2小时签到
  LS_BRID00  BM_BRXXB0.BRID00%type;
  LS_BRID01  BM_BRXXB0.BRID00%type;
  as_YHMSG0  varchar2(2550);
  as_SYSMSG  varchar2(2550);
  ls_YYZT00  SF_YYBRB0.YYZT00%type;
  LS_CARDNO  IC_YHXX00.ICKH00%type;
  LS_KMH000  IC_YHXX00.KMH000%type;
  LS_BRXM00  SF_YYBRB0.BRXM00%type;
  LS_YYBZ00  YS_HBHZXH.YYBZ00%type;--预约标志 '1'普通'2'优待 '3'预约 '4'电话 '5'分诊 '6'网络
  LS_GHID00  SF_YYBRB0.GHID00%type;
  LS_YYH000  SF_YYBRB0.YYH000%type;
  LS_BRDH00  SF_YYBRB0.BRDH00%type;
  ls_YYRQ00  SF_YYBRB0.YYRQ00%type;
  ls_GHHBID  SF_YYBRB0.GHHBID%type;
  ls_YYJSSJ  SF_YYBRB0.YYJSSJ%type;
  ls_TZBZ00  SF_YSGHHB.TZBZ00%type;
  ls_GHQSSJ  SF_YSGHHB.GHQSSJ%type;
  ls_GHJSSJ  SF_YSGHHB.GHJSSJ%type;
  ls_COUNT   number(5);
  Ecustom     exception;
begin
   select SF_SST_GETICKH00(cardno) into LS_CARDNO from dual;
   begin
        select BRID00,KMH000 into LS_BRID00,LS_KMH000 from IC_YHXX00 where  ICKH00 =LS_CARDNO and ZT0000=1;
   exception
          WHEN NO_DATA_FOUND THEN
          outstr:='IC卡号:'||LS_CARDNO||'在本院IC_YHXX00表中未找到,请联系管理员!';
          raise NO_DATA_FOUND;
   end;

   select nvl(BRID00,0),YYZT00,GHID00,YYH000,BRDH00,BRXM00,YYRQ00,GHHBID,
       --YYJSSJ,
       (select GHQSSJ from SF_YSGHHB where GHHBID=a.GHHBID) YYJSSJ,
       (select TZBZ00 from SF_YSGHHB where GHHBID=a.GHHBID) TZBZ00
       into LS_BRID01,ls_YYZT00,LS_GHID00,LS_YYH000,LS_BRDH00,LS_BRXM00,LS_YYRQ00,ls_GHHBID,ls_YYJSSJ,ls_TZBZ00
   from SF_YYBRB0 a where YYID00=id0000;
   if ls_YYRQ00>to_char(sysdate,'YYYYMMDD') --or (ls_YYRQ00=to_char(sysdate,'YYYYMMDD') and ls_YYJSSJ>to_char(sysdate+2/24,'HH24:MI:SS'))
     then
     as_YHMSG0:='不能提前签到！';
     Custname:='';
     raise Ecustom;
   end if;
   if ls_YYZT00 in ('4') then
     as_YHMSG0:='已经过期！';
     Custname:='';
     raise Ecustom;
   end if;
   if ls_YYZT00 in ('2') then
     as_YHMSG0:='已经取消！';
     Custname:='';
     raise Ecustom;
   end if;
   if ls_TZBZ00='1' then
     as_YHMSG0:='医生排班已停止！';
     Custname:='';
     raise Ecustom;
   end if;
  if  LS_BRID01=0 then
    ----SP_SST_MZYY_ZJYYDJ
     --更新预约病人信息
     update SF_YYBRB0 set brid00=LS_BRID00,KMH000=LS_KMH000,ICKH00=LS_CARDNO where YYID00=id0000 ;

     --更新医生号表侯诊序号
     update YS_HBHZXH set BRID00=LS_BRID00,YYID00=id0000,BRXM00=LS_BRXM00  where GHHBID=LS_GHID00 and HZXH00=LS_YYH000; --YYBZ00

   --update SF_YSGHHB set YYXHS0=YYXHS0+1,TQYYS0=TQYYS0+1 where GHHBID = P_PBLSH;

   --更新病人电话号码 20180702重新启用
      update BM_BRXXB0 set BRDH00=nvl(LS_BRDH00,BRDH00) where BRID00=LS_BRID00;
  end if;
  outstr:=' ';
  if ls_YYZT00='0' then
     SP_YY_YYZYGH_DGBR(ls_BRID00,to_number(id0000),outstr,as_SYSMSG);
     select count(*) into ls_COUNT from sf_hzbr00 where DJRQ00=to_char(sysdate,'YYYYMMDD') and BRID00=ls_BRID00 and HZXH00=LS_YYH000 and YSPBID=ls_GHHBID;
     if ls_COUNT=0 then
       Custname:='';
       as_YHMSG0:=ls_BRID00||'@'||id0000||'@签到失败！';
       raise Ecustom;
     end if;
  end if;
  if nvl(outstr,' ')=' ' then
     select a.BRXM00 Custname,a.YYYSXM groupname,a.YYH000 qno000,a.YYRQ00 visitdatetime,
        --a.YYKSMC,
        a.YYKSMC||','||(select h.CKXXDZ||h.CKMC00 from bm_ckbm00 h where c.GHHZCK=h.ckbh00 and rownum=1) yyksmc,
        decode(a.YYYSBH,'163',least(to_char(to_date(a.YYRQ00||a.YYJSSJ,'YYYYMMDDHH24:MI:SS'),'HH24:MI:SS'),
        to_char(to_date(C.GHMZSJ||C.GHJSSJ,'YYYYMMDDHH24:MI:SS')-1/32,'HH24:MI:SS') ),
        least(to_char(to_date(a.YYRQ00||a.YYJSSJ,'YYYYMMDDHH24:MI:SS'),'HH24:MI:SS'),
        to_char(to_date(C.GHMZSJ||C.GHJSSJ,'YYYYMMDDHH24:MI:SS')-1/96,'HH24:MI:SS') ) )  HZSJ00
        into Custname,groupname,qno000,czsj00,yyksmc,hzsj00
     from sf_yybrb0 a,SF_YSGHHB c
      where a.GHHBID=c.GHHBID and a.YYID00=id0000;
     update SF_YYBRB0 set HZDYCS=NVL(HZDYCS,0)+1 where YYID00=id0000 ;
  end if;
 exception
    when Ecustom then
      outstr:=outstr||','||as_YHMSG0;
      RAISE_APPLICATION_ERROR(-20021,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE||'#1#确认失败'||','||as_YHMSG0);
    when others then
      outstr:=outstr||','||SqlErrm||as_SYSMSG;
      RAISE_APPLICATION_ERROR(-20020,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE||'#2#确认失败'||','||SqlErrm||as_SYSMSG);
end;