CREATE OR REPLACE PROCEDURE SP_SST_YYBRLB(
  cardno  in varchar2,   --卡号
  cartype in number,     --IC卡类型
  outstr  out varchar2,  --错误信息
  r_cur1  out PKG_YY_QRY.MY_CURSOR --结果集游标
  )
AS
  ls_BRID00  BM_BRXXB0.BRID00%type;
  LS_BRXM00  BM_BRXXB0.BRXM00%type;
  LS_CARDNO  IC_YHXX00.ICKH00%type;
  LS_DQSD00  number(4);
  LS_GHJSSJ  varchar2(8);
  LS_BRDH00  BM_BRXXB0.BRDH00%type;
  LS_BRZJBH  BM_BRXXB0.BRZJBH%type;
  LS_COUNT   number(5);
  AS_YY_YYQHYCSJ number(4);
  AS_SYSDATE_YC  varchar2(16);
  --20120904  只能打印就诊时段的预约
  --20170914  增加自动对应功能
  --20180308  yanghq 增加对结果集的日期和时间的排序
  --20180316  yanghq 增加预约取号延迟时间控制，参数YY_YYQHYCSJ
  --20180604  yanghq 自动比对规则修改为 姓名+电话或者姓名+身份证
begin
  select SF_SST_GETICKH00(cardno,cartype) into LS_CARDNO from dual;
    begin
        select BRID00 into ls_brid00 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(VALUE0,0) into AS_YY_YYQHYCSJ
  from (select (select VALUE0 from SD_HOSPITAL.XT_XTCS00 where NAME00='YY_YYQHYCSJ') VALUE0 from dual);
  select to_char(sysdate-AS_YY_YYQHYCSJ/24/60,'YYYYMMDDHH24:MI:SS') into AS_SYSDATE_YC from dual;

  select BRXM00,nvl(BRDH00,BRLXDH),BRZJBH into LS_BRXM00,LS_BRDH00,LS_BRZJBH from bm_brxxb0 where brid00=ls_brid00;
  select to_number(substr(to_char(sysdate,'HH24:MI:SS'),1,2)) into LS_DQSD00 from dual;
  if   LS_DQSD00>12 then LS_GHJSSJ:='23:59:59';
  else LS_GHJSSJ:='12:00:00';
  end if;
  select count(*) into LS_COUNT from SF_YYBRB0 where YYRQ00=to_char(sysdate,'YYYYMMDD')
       and (BRXM00=LS_BRXM00  or   nvl(WLYYMC,'0')='智能电话' )
       and (SFZH00=LS_BRZJBH or BRDH00=LS_BRDH00) and YYRQ00||YYJSSJ>=AS_SYSDATE_YC and nvl(BRID00,0)=0;
  if LS_COUNT>0 then
     update SF_YYBRB0 set BRID00=LS_BRID00,BZ0000=BZ0000||',自动对应1' where  YYRQ00=to_char(sysdate,'YYYYMMDD')
       and BRXM00=LS_BRXM00  and YYZT00 in('0','1')
        --and SFZH00=LS_BRZJBH and BRDH00=LS_BRDH00 and YYRQ00||YYJSSJ>=AS_SYSDATE_YC and nvl(BRID00,0)=0;
        and (SFZH00=LS_BRZJBH or BRDH00=LS_BRDH00) and YYRQ00||YYJSSJ>=AS_SYSDATE_YC and nvl(BRID00,0)=0
        and nvl(WLYYMC,'0')<>'智能电话';
     update SF_YYBRB0 set BRID00=LS_BRID00,BZ0000=BZ0000||',自动对应2',brxm00=LS_BRXM00 where  YYRQ00=to_char(sysdate,'YYYYMMDD')
       --and BRXM00=LS_BRXM00
       and YYZT00 in('0','1')
        --and SFZH00=LS_BRZJBH and BRDH00=LS_BRDH00 and YYRQ00||YYJSSJ>=AS_SYSDATE_YC and nvl(BRID00,0)=0;
        and (SFZH00=LS_BRZJBH ) and YYRQ00||YYJSSJ>=AS_SYSDATE_YC and nvl(BRID00,0)=0
        and nvl(WLYYMC,'0')='智能电话';
  end if;
 -- if cardno='H50464261' then
  --  raise NO_DATA_FOUND;
 -- end if;
 --or a.BRXM00=LS_BRXM00
  open r_cur1 for
     select YYID00   YYID00, --唯一标识用来表示某个预约
            BRXM00   BRXM00,  --病人姓名
            KMH000   CARDNO,  --门诊卡号
            YYYSXM   YYYSXM,  --医生队列
            YYH000   YYH000,  --预约号码
            YYRQ00   YYRQ00,  --就诊时间
            YYKSMC   YYKSMC,  --
            YYJSSJ   YYSJ00,
            SF_SST_DDRS00(a.YYKSBH,a.YYYSBH,a.YYH000) ddrs00,
            (select CKMC00 from SF_YSGHHB b,BM_CKBM00 c where b.GHHBID=a.GHHBID and b.GHHZCK=c.ckbh00 and rownum=1) ZSCKMC
 from vw_yy_bryyxxcx a where  (nvl(BRID00,0)=ls_BRID00 )
 --from vw_yy_bryyxxcx a where  (nvl(BRID00,0)=ls_BRID00 or (a.BRXM00=LS_BRXM00 and a.BRDH00=LS_BRDH00) or (a.BRXM00=LS_BRXM00 and a.SFZH00=LS_BRZJBH))
 and YYRQ00>=to_char(sysdate,'YYYYMMDD') and YYRQ00||YYJSSJ>=AS_SYSDATE_YC
 and YYZT00 in('0','1')
 and (NVL(HZDYCS,0)=0 or  DJRQ00=to_char(sysdate,'YYYYMMDD'))
 and YYLB00 in('2','0','1')
 order by yyrq00,yysj00;
 exception
    when NO_DATA_FOUND then
      RAISE_APPLICATION_ERROR(-20020,LS_BRZJBH||'@'||LS_BRDH00||'@'||LS_COUNT||'@'||AS_SYSDATE_YC||'没有查询到预约记录,可能打印时间未到!'||','||SqlErrm);
    when others then
      outstr:=SqlErrm;
      RAISE_APPLICATION_ERROR(-20020,'获取失败'||','||SqlErrm);
end;