CREATE OR REPLACE PROCEDURE SP_YY_TZDXTZ(ad_GHHBID in number,
                                         as_GHMZSJ in char,
                                         as_TZBZ00 in char default '1'  --1停诊，0复诊
                                         )
as
  Vdxnr00    varchar2(600);
  Vdxnrqz    varchar2(600);
  VTZXX00    varchar2(300);
  VTZTZYYCJ  varchar2(300);
  VTZTZDXMB  XT_XTCS00.VALUE0%type;
  VTZBZMC    varchar2(300);
-- MODIFICATION HISTORY
-- Person   Date        Comments
-- dsm      2016.06.07  create for YYGH-20160531-001
-- dsm      2016.08.16  增加参数控制是否发停诊通知的预约短信给病人 for ZHCX-20160804-001
-- dsm      2016.11.03   BRDH00或BRLXDH有都发 for YYGH-20161031-003
-- dsm      2024.08.21   短信用模板参数 for MZHS-20240820-001
-- dsm      2024.08.28   复诊短信模板YY_FZTZDXMB for MZHS-20240822-001
begin
  begin
    select decode(trim(MC0000),'','','《'||MC0000||'》') into Vdxnrqz from BM_TYZD00 where ZDMC00='短信内容开头';
  exception
        when no_data_found then
         Vdxnrqz:=' ' ;
  end;
  select decode(as_TZBZ00,'1','停诊','0','复诊') into VTZBZMC from dual;

  if Vdxnrqz<>' ' then
    Vdxnrqz:=Vdxnrqz||'《'||to_char(to_number(substrb(as_GHMZSJ,5,2)))||'月'||to_char(to_number(substrb(as_GHMZSJ,7,2)))||'日'||VTZBZMC||'通知》';
  else
    Vdxnrqz:='《'||to_char(to_number(substrb(as_GHMZSJ,5,2)))||'月'||to_char(to_number(substrb(as_GHMZSJ,7,2)))||'日'||VTZBZMC||'通知》';
  end if;
  begin
    select ','||trim(VALUE0)||',' into VTZTZYYCJ from XT_XTCS00 where NAME00='YY_TZTZYYCJ';
  exception
    when others then
    VTZTZYYCJ:='N'  ;
  end;
  --有电话的才发
  if as_TZBZ00='1' then
    begin
      select  trim(VALUE0)  into VTZTZDXMB from XT_XTCS00 where NAME00='YY_TZTZDXMB';  --停诊通知短信模板
    exception
      when others then
      VTZTZDXMB:='您预约[YYRQ00]日的[YYKSMC]的[YYYSXM]医生已经停诊，请自行撤销此预约！'  ;
    end;
    --VTZXX00:=VTZTZDXMB;
    for c_yybr in (select BRXM00,YYYSXM,YYKSMC,nvl(BRDH00,BRLXDH) BRDH00,
             substr(YYRQ00,5,2)||'月'||substr(YYRQ00,7,2)||'日' YYRQ00,
             substr(YYJSSJ,1,2)||'时'||substr(YYJSSJ,4,2)||'分' YYSJ00
             from SF_YYBRB0 where GHHBID=ad_GHHBID and YYZT00 in ('0','1')
                        and (BRDH00 is not null  or BRLXDH is not null)
                        and (instr(VTZTZYYCJ,','||WLYYMC||',')>0 or nvl(WLYYMC,' ')=' ')
                        )
    loop
      VTZXX00:=VTZTZDXMB;
      --VTZXX00:='您预约'||c_yybr.YYRQ00||'日的'||c_yybr.YYKSMC||'的'||c_yybr.YYYSXM||'医生已经停诊，请自行撤销此预约！';
      --VTZXX00:='尊敬的'||c_yybr.brxm00||' 您预约'||c_yybr.YYRQ00||' '||c_yybr.YYKSMC||' '||c_yybr.YYYSXM||'医生，由于特殊原因已停诊，请您提早重新预约当日其他医师进行就诊，给您带来不便敬请谅解！';
      VTZXX00:=replace(VTZXX00,'[YYRQ00]',c_yybr.YYRQ00);
      VTZXX00:=replace(VTZXX00,'[YYSJ00]',c_yybr.YYRQ00||c_yybr.YYSJ00);
      VTZXX00:=replace(VTZXX00,'[YYKSMC]',c_yybr.YYKSMC);
      VTZXX00:=replace(VTZXX00,'[YYYSXM]',c_yybr.YYYSXM);
      VTZXX00:=replace(VTZXX00,'[BRXM00]',c_yybr.BRXM00);
      insert into YJ_DXTZ00(DXID00,DXNRFL,DXNR00,YWRQ00,CZRQ00,CZSJ00,DH0000,JSR000,BRID00)
            values(SQ_YJ_DXTZ00_DXID00.nextval,'HIS停诊通知',Vdxnrqz||VTZXX00,to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS'),c_yybr.BRDH00,c_yybr.BRXM00,0);
    end loop;
  elsif  as_TZBZ00='0' then
    begin
      select  trim(VALUE0)  into VTZTZDXMB from XT_XTCS00 where NAME00='YY_FZTZDXMB';  --复诊通知短信模板
    exception
      when others then
      VTZTZDXMB:='您预约[YYRQ00]日的[YYKSMC]的[YYYSXM]医生已经复诊！'  ;
    end;
    VTZXX00:=VTZTZDXMB;
    for c_yybr in (select BRXM00,YYYSXM,YYKSMC,nvl(BRDH00,BRLXDH) BRDH00,
         substr(YYRQ00,5,2)||'月'||substr(YYRQ00,7,2)||'日' YYRQ00,
         substr(YYJSSJ,1,2)||'时'||substr(YYJSSJ,4,2)||'分' YYSJ00
         from SF_YYBRB0 where GHHBID=ad_GHHBID and YYZT00 in ('0','1')
                        and (BRDH00 is not null  or BRLXDH is not null)
                        and (instr(VTZTZYYCJ,','||WLYYMC||',')>0 or nvl(WLYYMC,' ')=' ')
                        )
    loop
      --VTZXX00:='您预约'||c_yybr.YYRQ00||'日的'||c_yybr.YYKSMC||'的'||c_yybr.YYYSXM||'医生已经停诊，请自行撤销此预约！';
      --VTZXX00:='尊敬的'||c_yybr.brxm00||' 您预约'||c_yybr.YYRQ00||' '||c_yybr.YYKSMC||' '||c_yybr.YYYSXM||'医生，由于特殊原因已停诊，请您提早重新预约当日其他医师进行就诊，给您带来不便敬请谅解！';
      VTZXX00:=replace(VTZXX00,'[YYRQ00]',c_yybr.YYRQ00);
      VTZXX00:=replace(VTZXX00,'[YYSJ00]',c_yybr.YYRQ00||c_yybr.YYSJ00);
      VTZXX00:=replace(VTZXX00,'[YYKSMC]',c_yybr.YYKSMC);
      VTZXX00:=replace(VTZXX00,'[YYYSXM]',c_yybr.YYYSXM);
      VTZXX00:=replace(VTZXX00,'[BRXM00]',c_yybr.BRXM00);
      insert into YJ_DXTZ00(DXID00,DXNRFL,DXNR00,YWRQ00,CZRQ00,CZSJ00,DH0000,JSR000,BRID00)
            values(SQ_YJ_DXTZ00_DXID00.nextval,'HIS复诊通知',Vdxnrqz||VTZXX00,to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS'),c_yybr.BRDH00,c_yybr.BRXM00,0);
    end loop;
  end if;
 -- commit; 由于是在触发器中调用，不能加commit;
end;