create or replace procedure  SP_SST_YYQR_YKT(
  ZDBH00     in varchar2,    --终端编号
  cardno     in varchar2,    --卡号  
  id0000     in  varchar2,   --预约队列标识
  --custname   out varchar2,   --病人姓名  
  --groupname  out varchar2,   --医生姓名
  --qno000  out varchar2,      --预约号
  --czsj00  out varchar2,      --就诊日期
  --yyksmc  out varchar2,      --预约科室
  --hzsj00  out varchar2,      --候诊时间
  outstr  out varchar2,      --错误信息
  p_CURSOR  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_KMH000  IC_YHXX00.KMH000%type;
  LS_YGBH00  BM_YGBM00.YGBH00%type;
  LS_BMBH00  BM_YGBM00.BMBH00%type;
  ls_YYKSBH  SF_YYBRB0.YYKSBH%type;
  ls_YYYSBH  SF_YYBRB0.YYYSBH%type;
  as_YHMSG0  varchar2(255);
  as_SYSMSG  varchar2(255);
  ls_YYZT00  SF_YYBRB0.YYZT00%type;
  ls_YYH000  SF_YYBRB0.YYH000%type;
  Pzxcgbz    number;
  Pid0000    varchar2(255);
  ls_DDRS00  number(5);
  
-- MODIFICATION HISTORY
-- Person      Date         Comments     
-- qks         2013.03.13   增加入参cardno;使用姓名匹配,上午或下午只有一条预约记录情况下，才给予取号。 
-- qks         2013.04.08   modify:出参信息
-- qks         2015.11.16   结果集游标 增加ZSDZ00
begin
    --获取自助机终端编号对应的收费员原编号
  begin
    select a.YGBH00,b.BMBH00 into LS_YGBH00,LS_BMBH00 from VW_SST_YGBMDYXX a,BM_YGBM00 b  
      where a.ZZJBH0=ZDBH00 and a.YGBH00=b.YGBH00;
    exception
      when no_data_found then
      outstr:='终端编号:'||ZDBH00||'在本院SST_YGBHDY表中未对应收费员!';
      return;
  end;  
    
    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,YYZT00,YYKSBH,YYYSBH,YYH000 into ls_BRXM00,ls_YYZT00,ls_YYKSBH,ls_YYYSBH,ls_YYH000 from SF_YYBRB0 where YYID00=id0000 ;
   outstr:=' ';
   if ls_YYZT00='0' then
     --SP_YY_YYZYGH_DGBR(ls_BRID00,to_number(id0000),outstr,as_SYSMSG);
     SP_YY_YYBRDYWH
     (
       id0000      ,           --预约ID
       ls_BRID00   ,           --病人ID
       ls_BRXM00   ,           --病人姓名
       ls_KMH000   ,           --卡面号
       LS_CARDNO   ,           --IC卡号
       LS_YGBH00   ,           --操作员编码         
       LS_BMBH00   ,           --操作员科室 
       'N'         ,           --是否提交
       Pzxcgbz     ,           --执行成功标志 1成功 0失败 
       outstr      ,           --执行结果提示信息，成功的情况下也可能有警告信息
       as_SYSMSG   ,           --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标   
       'N'         ,           --是否扣预约费
       Pid0000                --sf_hzbr00.id0000 
     );     
   end if;  
   if nvl(outstr,' ')=' ' then
    /*
     select 
            BRXM00   Custname,  --病人姓名
            YYYSXM  groupname,  --医生队列
                YYH000   qno000,    --预约号码             
            YYRQ00  visitdatetime,  --就诊时间
            YYKSMC,  
            (select HZQSSJ from YS_HBHZXH where GHHBID=a.GHHBID and HZXH00=a.YYH000) HZSJ00
            
            into Custname,groupname,qno000,czsj00,yyksmc,hzsj00
    from SF_YYBRB0 a where  YYID00=id0000;
    */

    SELECT COUNT(1) into ls_DDRS00 FROM vw_ys_hzbr00 
     where jzks00=ls_YYKSBH
     and jzys00=ls_YYYSBH and dlxh00>0
     and HZXH00<ls_YYH000 and HZZT00='0';
     
    open p_CURSOR for    
     select XM0000 BRXM00,  --病人姓名
            JZYSXM||' '||GHLBMC YSXM00, --医生姓名
            (select m.ckmc00 from bm_ckbm00 m,sf_ysghhb n where n.ghhbid=a.YSPBID and m.ckbh00=n.GHHZCK and rownum=1) ZSMC00, --诊室名称
            ls_DDRS00 DDRS00,--等待人数
            HZXHMC,          --候诊序号名称
            DGJZSJ HZSJ00, --候诊时间
            (select m.ckxxdz from bm_ckbm00 m,sf_ysghhb n where n.ghhbid=a.YSPBID and m.ckbh00=n.GHHZCK and rownum=1) ZSDZ00
       from  vw_ys_hzbr00 a where ID0000=Pid0000;  
    update SF_YYBRB0 set HZDYCS=NVL(HZDYCS,0)+1 where YYID00=id0000 ;
   end if;
 --sp_yy_yyzygh;
 exception    
    when others then  
      outstr:=outstr||','||SqlErrm||as_SYSMSG;  
      RAISE_APPLICATION_ERROR(-20020,'确认失败'||','||SqlErrm||as_SYSMSG);    
end;
/
show error;

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;
--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 
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,nvl(MAX(BRDH00),'1') into LS_BRXM00,ls_BRZJBH,ls_BRLXDH,ls_BRDH01 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';
    LS_YYJSSJ:='12:30:00';
  else
    LS_GHJSSJ:='12:00:00';
    LS_YYJSSJ:='07:30:00';
  end if;
  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  --挂号总费用

 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;
   --二维码信息:BRID00@医生编号内码@导诊台窗口编码

    select nvl(max(c.GHYSBH),0),nvl(max(c.GHHZCK),0) into LS_GHYSBH,LS_GHHZCK
     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,'YYYYMMDD')||LS_YYJSSJ
     and (nvl(a.BRID00,0)=0 or nvl(a.BRID00,0)=ls_brid00)  and a.YYZT00 in('0')
     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 a.GHHBID = C.GHHBID
     and c.ghksbh = d.bmbh00
     and c.GHJSSJ <= LS_GHJSSJ
     and a.BRID00 = ls_BRID00
     and rownum = 1
     order by c.GHHBID desc;

   --P_EWMXX0 := to_char(ls_BRID00)||'@'||to_char(LS_GHYSBH)||'@'||to_char(LS_GHHZCK);
 exception
    when NO_DATA_FOUND then
      RAISE_APPLICATION_ERROR(-20020,'没有查询到预约记录,可能打印时间未到!'||','||SqlErrm);
    when others then
      outstr:=SqlErrm;
      RAISE_APPLICATION_ERROR(-20020,'获取失败'||','||SqlErrm);
end;
/
GRANT EXECUTE ON SP_SST_YYBRLB TO sst
/