CREATE OR REPLACE PROCEDURE SP_SST_YYBRLB_YKT(
  cardno    in   varchar2,    --卡号
  cartype   in   number,      --IC卡类型

  outstr    out  varchar2,   --错误信息
  r_cur1    out  PKG_YY_QRY.MY_CURSOR --结果集游标

  --P_EWMXX0  out  varchar2     --二维码信息:BRID00@医生编号内码@导诊台窗口编码

    )
AS
  ls_BRID00  BM_BRXXB0.BRID00%type;
  LS_CARDNO  IC_YHXX00.ICKH00%type;
  LS_BRXM00  BM_BRXXB0.BRXM00%type;
  LS_DQSD00  number(4);
  LS_GHJSSJ  varchar2(8);
  LS_YYJLS0  number;
  LS_COUNT1  number;
  LS_YYJSSJ  varchar2(8);
  LS_BRYYSJ  varchar2(8);
  ls_YYQHGZSJ number(4);
  ls_YYQHTJ  XT_XTCS00.VALUE0%type;
  ls_BRZJBH  BM_BRXXB0.BRZJBH%type;
  ls_BRLXDH  BM_BRXXB0.BRLXDH%type;
  ls_BRDH01  BM_BRXXB0.BRDH00%type;
  ls_KMH000  IC_YHXX00.KMH000%type;
  LS_GHYSBH  SF_YSGHHB.GHYSBH%type;
  LS_GHHZCK  SF_YSGHHB.GHHZCK%type;
  ls_YZBRXX  XT_XTCS00.VALUE0%type; --验证病人信息
  ls_SFZH00  SF_YYBRB0.SFZH00%type;
  ls_BRDH00  SF_YYBRB0.Brdh00%type;
  ls_OLDBRID  SF_YYBRB0.BRID00%type;
--20120904  只能打印就诊时段的预约

-- MODIFICATION HISTORY
-- Person      Date         Comments
-- qks         2013.03.13   使用姓名匹配,上午或下午只有一条预约记录情况下，才给予显示。

-- qks         2013.03.20   modify
-- qks         2013.04.01   modify:同个卡号多条预约记录，允许取号

-- qks         2016.01.11   超过预约号的截止时间,也要允许取号;
-- qks         2016.11.01   新增参数SST_YYQHGZSJ控制:过了时间30分钟,不允许取号,并给予提示。

-- qks         2017.03.14   新增参数SST_HZQYYHPDTJ控制：患者取预约号判断条件：0按姓名 1按姓名或电话或身份证号。for YYPT-20170307-001
-- qks         2017.04.20   参数SST_HZQYYHPDTJ功能扩展：2按姓名以及身份证号,3按姓名以及电话。 for MZHS9-20170413-001
-- qks         2017.05.25   处理SF_YYBRB0.BRID00不为0问题。

-- qks         2017.06.23   r_cur1增加返回：挂号总费用ghfyze;
-- linshu      2020.05.12   增加出参P_EWMXX0 by ZZJJK-20200430-001
-- linshu      2020.12.30   预约病人现场取号时增加判断:姓名+身份证、姓名+病人联系电话、病人+病人电话 三个条件满足其中一次才允许取号成功 by  ZZJJK-20201221-003
-- qiulf       2021.05.25   病人姓名+电话或身份证一样就更新预约表的brid00 by   ZZJJK9-20210511-001
-- qiulf       2021.07.30   整理现场改动 by   ZZJJK9-20210728-001
begin
  select SF_SST_GETICKH00_ykt(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||'在本院未找到记录,请先【建档注册】!';
        raise NO_DATA_FOUND;
    end;
  select brxm00,BRZJBH,BRLXDH into LS_BRXM00,ls_BRZJBH,ls_BRLXDH from bm_brxxb0 where brid00=ls_brid00;
  --select to_number(substr(to_char(sysdate,'HH24:MI:SS'),1,2)) into LS_DQSD00 from dual;
  --lxl 2021.03.26 取号时段改为12：30分可取下午的号
  select to_number(substr(replace(to_char(sysdate,'HH24:MI:SS'),':',''),1,4)) into LS_DQSD00 from dual;
  if   LS_DQSD00>1230 then
    LS_GHJSSJ:='23:59:59';
    LS_YYJSSJ:='12:30:00';
  else
    LS_GHJSSJ:='12:00:00';
    LS_YYJSSJ:='07:30:00';
  end if;
  update SF_YYBRB0 a set BRID00=LS_BRID00 where BRXM00=lS_BRXM00 and (SFZH00=ls_BRZJBH or BRDH00=LS_BRLXDH) and a.YYRQ00=to_char(sysdate,'YYYYMMDD')
  and a.YYRQ00||a.YYJSSJ>=to_char(sysdate,'YYYYMMDD')||LS_YYJSSJ and BRID00 is not null and BRID00 >0;


  select nvl(max(trim(VALUE0)),'0')  into ls_YYQHTJ from  XT_XTCS00  where  NAME00 = 'SST_HZQYYHPDTJ';

  select count(1),max(a.YYJSSJ) into LS_YYJLS0,LS_BRYYSJ from SF_YYBRB0 a,YS_HBHZXH b,SF_YSGHHB c where a.YYRQ00=to_char(sysdate,'YYYYMMDD')
     and a.YYRQ00||a.YYJSSJ>=to_char(sysdate,'YYYYMMDD')||LS_YYJSSJ
     and (nvl(a.BRID00,0)=0 or nvl(a.BRID00,0)=ls_brid00) and a.YYZT00 in('0')
     --and a.BRXM00=LS_BRXM00  --2017.03.14
     --and (a.BRXM00=LS_BRXM00 or ls_YYQHTJ='1' and (a.SFZH00=ls_BRZJBH or a.BRDH00=ls_BRLXDH or a.KMH000=ls_KMH000)) --2017.04.20
     and (a.BRXM00=LS_BRXM00 and
     (ls_YYQHTJ not in ('2','3') or ls_YYQHTJ='2' and
     (a.SFZH00=ls_BRZJBH or a.SFZH00 is null) or
     ls_YYQHTJ='3' and
     (a.BRDH00=ls_BRLXDH or a.BRDH00 is null))
            or ls_YYQHTJ='1' and (a.SFZH00=ls_BRZJBH or a.BRDH00=ls_BRLXDH or a.KMH000=ls_KMH000))
     and a.GHHBID = b.GHHBID
     and a.YYH000=b.HZXH00
     and a.GHHBID=C.GHHBID
     and c.GHJSSJ<=LS_GHJSSJ;


  --2016.11.01
  begin
    select to_number(nvl(max(trim(VALUE0)),'0'))  into ls_YYQHGZSJ from  XT_XTCS00  where  NAME00 = 'SST_YYQHGZSJ';
  exception
   when others then
    ls_YYQHGZSJ := 0;
  end;
  if ls_YYQHGZSJ>0 and LS_BRYYSJ is not null
     and to_char((sysdate-to_number(ls_YYQHGZSJ)/(60*24)),'HH24:mi:ss')>LS_BRYYSJ then
    outstr:='预约号就诊时间已过,请到现场取排队号就诊！';
    return;
  end if;

  if LS_YYJLS0>1 then
     select count(1) into LS_COUNT1 from SF_YYBRB0 a,YS_HBHZXH b,SF_YSGHHB c where a.YYRQ00=to_char(sysdate,'YYYYMMDD')
        and a.YYRQ00||a.YYJSSJ>=to_char(sysdate,'YYYYMMDD')||LS_YYJSSJ
        and (nvl(a.BRID00,0)=0 or nvl(a.BRID00,0)=ls_brid00)  and a.YYZT00 in('0')
        --and KMH000 = cardno
        and a.KMH000 = ls_KMH000
        --and a.BRXM00=LS_BRXM00 --2017.03.14
        and (a.BRXM00=LS_BRXM00 and (ls_YYQHTJ not in ('2','3') or ls_YYQHTJ='2' and (a.SFZH00=ls_BRZJBH or a.SFZH00 is null) or ls_YYQHTJ='3' and (a.BRDH00=ls_BRLXDH or a.BRDH00 is null))
               or ls_YYQHTJ='1' and (a.SFZH00=ls_BRZJBH or a.BRDH00=ls_BRLXDH or a.KMH000=ls_KMH000))
        and a.GHHBID = b.GHHBID
        and a.YYH000=b.HZXH00
        and a.GHHBID=C.GHHBID
        and c.GHJSSJ<=LS_GHJSSJ;

     --if LS_COUNT0<>LS_COUNT1 then
     if LS_COUNT1 = 0 then --2017.03.20
       outstr:='请到柜台取预约号!';
       return;
     end if;
      --预约病人现场取号时增加判断 ：“姓名+身份证”、“姓名+病人联系电话”、“病人+病人电话”三个条件满足其中一次才允许取号成功 by  ZZJJK-20201221-003
     select nvl(max(trim(VALUE0)),'0')  into ls_YZBRXX from  XT_XTCS00  where  NAME00 = 'MZHS_SFYZBRXX';
     if ls_YZBRXX = '1' then
       select nvl(max(SFZH00),'0'),nvl(max(BRDH00),'0') into ls_SFZH00,ls_BRDH00  from SF_YYBRB0 a  where a.YYRQ00=to_char(sysdate,'YYYYMMDD') and nvl(a.BRID00,0)=0 and  a.YYZT00 in('0')
         and a.brxm00 = LS_BRXM00
         and rownum=1 order by a.yyid00 desc;
       if  not (( ls_BRZJBH = ls_SFZH00 ) or ( ls_BRLXDH = ls_BRDH00 ) or ( ls_BRDH01 <> ls_BRDH00 )) then
        outstr:='请到一楼大厅预约服务台咨询！';
        return;
      end if;
    end if;
  end if;
--  if LS_COUNT0>1 then
--     outstr:='请到柜台取预约号!';
--     raise NO_DATA_FOUND;
--  elsif LS_COUNT0=0 then
--     outstr:='没找到未取号的预约信息!';
--     raise NO_DATA_FOUND;
--  end if;
   open r_cur1 for
     select a.YYID00   id0000, --唯一标识用来表示某个预约
            a.BRXM00   Custname,  --病人姓名
            a.KMH000   Custno,     --门诊卡号
            a.YYYSXM  groupname,  --医生队列
            a.YYH000   qno000,    --预约号码
            a.YYRQ00||' '||substrb(to_char(to_date(c.GHMZSJ||c.GHQSSJ,'YYYYMMDDHH24:MI:SS')+nvl(c.PJZZSJ,5)*(b.HZXH00-1)/1440,'YYYYMMDDHH24:MI:SS'),9,8)  visitdatetime,  --就诊时间
            d.bmmc00 yyksmc,    --预约科室名称
            SF_SST_GETGHF_YKT(c.GHLBBH,ls_brid00,0) ghfyze,  --挂号总费用
            (select ls_brid00||'@'||c.ghysbh||'@'||nvl(dy.sckbh0,0) from bm_ckdy00 dy where dckbh0 = c.ghhzck) EWM001 --增加返回二维码信息
 from SF_YYBRB0 a,YS_HBHZXH b,SF_YSGHHB c,bm_bmbm00 d where a.YYRQ00=to_char(sysdate,'YYYYMMDD')
     --and a.YYRQ00||a.YYJSSJ>=to_char(sysdate,'YYYYMMDDHH24:MI:SS')
     and a.YYRQ00||a.YYJSSJ>=to_char(sysdate,'YYYYMMDD')||LS_YYJSSJ
     and (nvl(a.BRID00,0)=0 or nvl(a.BRID00,0)=ls_brid00)  and a.YYZT00 in('0')
     --and a.BRXM00=LS_BRXM00
     and (LS_YYJLS0>1 and a.KMH000 = ls_KMH000 or LS_YYJLS0<=1)
     and (a.BRXM00=LS_BRXM00 and (ls_YYQHTJ not in ('2','3') or ls_YYQHTJ='2' and (a.SFZH00=ls_BRZJBH or a.SFZH00 is null) or ls_YYQHTJ='3' and (a.BRDH00=ls_BRLXDH or a.BRDH00 is null))
            or ls_YYQHTJ='1' and (a.SFZH00=ls_BRZJBH or a.BRDH00=ls_BRLXDH or a.KMH000=ls_KMH000))
     and a.GHHBID = b.GHHBID
     and a.YYH000=b.HZXH00
     --and b.HZQSSJ||':00'<=LS_GHJSSJ;
     and a.GHHBID=C.GHHBID
     and c.ghksbh=d.bmbh00
     and c.GHJSSJ<=LS_GHJSSJ;


 exception
    when NO_DATA_FOUND then
      RAISE_APPLICATION_ERROR(-20020,'没有查询到预约记录,可能打印时间未到!'||','||SqlErrm);
    when others then
      outstr:=SqlErrm;
      RAISE_APPLICATION_ERROR(-20020,'获取失败'||','||SqlErrm);
end;