create or replace function SF_YS_PTZJYY(ad_BRID00  in number,
                                        ad_CZY000  in number,--医生工号
                                        ad_BMBH00  in number,
                                        ad_YGHBZ0  in varchar2 default '0',  --预挂号标志 YYCZLB(预约操作类别) 0正常预约，1预挂号
                                        ad_YYYGBH  in number default 0,    --预约员工编号  CZYXKH操作员胸卡号
                                        as_YYRQ00  in varchar2 default '0'    --预约日期
                                        )
return varchar2 is
  ls_orgCode     varchar2(10);
  ls_passWord    varchar2(50);
  ls_result      varchar2(300);
  ls_result1      varchar2(300);
  ls_YGXM00      BM_YGBM00.ZWXM00%type;
  ls_XKH000      BM_YGBM00.XKH000%type;
  ls_CZYXKH      BM_YGBM00.XKH000%type;
  ls_YGXB00      BM_YGBM00.XB0000%type;
  Ecustom        exception;
  VSQL000        varchar2(300);
  Verrmsg        varchar2(60);
  LS_URL000      varchar2(100);
  ipos           number(5);
  ls_TYPE        varchar2(2);
  ls_BRID00      number(10);
  ls_YYRQ00      varchar2(10);
  ls_sxzzbz      char(1);--20200421 wxx 增加双向转诊预约标志
-- Person      Date        Comments
-- dsm      2013.07.23     创建 for MZYS-20130722-001;
-- dsm      2013.09.25     update for MZYS-20130924-002;
-- dsm      2016.03.21     增加处理YS_SFQYTYYYPT=Y的情况 for MZYS-20160321-001
-- dsm      2016.03.26     增加参数ad_YGHBZ0 for MZYS-20160326-001
-- dsm      2016.04.13     增加DTYPE(诊间预约) for MZYS-20160413-001
-- dsm      2016.04.19     现场修改:传0的都改成传空
-- dsm      2017.09.28     if ls_BRID00=0 then-->if ad_BRID00=0 then for MZYS-20170922-001
-- linshu   2020.03.11     5.0移植到9.0 by MZHS9-20200309-001
begin
   --select nvl(max(ZXYYID),'' '')  into from QYZX_ORGDZ0 a,BM_BMBM00 b where a.YYID00=b.YYID00 and  b.BMBH00=ad_BMBH00 and  rownum=1;
     LS_URL000:=' ';
     begin
       --select trim(VALUE0) into LS_URL000 from XT_XTCS00 where name00='YS_SFQYTYYYPT' and VALUE0<>'N';
       select trim(VALUE0) into LS_URL000 from XT_XTCS00 where name00='MZHS_YSPBXGGHHBURL' and VALUE0<>'N';
       
       --20200421 wxx 扩展MZHS_YSPBXGGHHBURL为Y|0|URL的格式后，赋值给ls_sxzzbz变量作为bz0000的值传给预约平台，下方赋值语句顺序不能变
       ls_sxzzbz:=substr(LS_URL000,3,1);
       LS_URL000:=substr(LS_URL000,5,length(LS_URL000)-4);
       
       if ad_CZY000>0 then
         select XKH000 into  ls_XKH000 from BM_YGBM00 where YGBH00=ad_CZY000;
       else  ls_XKH000:=null;
       end if; 
       if ad_YYYGBH>0 then
         select XKH000 into  ls_CZYXKH from BM_YGBM00 where YGBH00=ad_YYYGBH;
         ls_TYPE:='1';
       else 
         ls_CZYXKH:=null;
         ls_TYPE:=null;
       end if;
       
       --20200421 wxx 通过ls_sxzzbz判断取ls_BRID00值。
       if ls_sxzzbz <> '0' then 
         if ad_BRID00=0 then
            ls_BRID00:='';
         else
            ls_BRID00:=ad_BRID00;
         end if;
       else
         ls_BRID00 := ad_BRID00; 
       end if;
       
       if as_YYRQ00='0' then
          ls_YYRQ00:=null;
       else
          ls_YYRQ00:=as_YYRQ00;
       end if;  
       select '&doctorCode='||ls_XKH000
            ||'&PID='||ls_BRID00
            ||'&DTPYE='||ls_TYPE
            ||'&YYCZLB='||ad_YGHBZ0           
            ||'&CZYXKH='||ls_CZYXKH
            ||'&bz0000='||ls_sxzzbz  --20200421 wxx 跟彭胜协商，增加拼接bz0000入参，用于是否双向转诊的标志，0：双向转诊预约，非0：普通预约
            into ls_result1
            from dual;       
     exception
       when others then
       LS_URL000:=' ';        
     end;   
     if LS_URL000=' ' then     
       begin
         select trim(VALUE0) into LS_URL000 from XT_XTCS00 where name00='YS_PTZJYYURL' and VALUE0<>'N';
       exception
         when others then
         ls_url000:='请设置诊间预约的URL';
          raise Ecustom;
       end;
       if instr(LS_URL000,';')>0 then
          ipos:=instr(LS_URL000,';');
          LS_URL000:=substr(LS_URL000,ipos+1,length(LS_URL000)-ipos);
       end if;   
       VSQL000:='select nvl(max(ZXYYID),'' '')  from QYZX_ORGDZ0 a,BM_BMBM00 b where a.YYID00=b.YYID00 and  b.BMBH00='||ad_BMBH00 ||' and rownum=1 ';
       execute immediate VSQL000 into ls_orgCode;
       if  ls_orgCode =' ' then
         Verrmsg:='没有取到医院代码不能预约';
         raise Ecustom;
       end if;
       ls_passWord:='123';
       select ZWXM00,XKH000,decode(XB0000,'男','1','女','2','9') XB0000 into ls_YGXM00,ls_XKH000,ls_YGXB00 from BM_YGBM00 where YGBH00=ad_CZY000;
       ls_result:='orgCode='||ls_orgCode||'&passWord='||ls_passWord||'&chisZggh='||ls_XKH000||'&chisEmpName='||form_url_encode(ls_YGXM00,'UTF8')||'&chisEmpIdCard='||'&chisEmpSex='||ls_YGXB00;
       select '&brxm='||form_url_encode(a.BRXM00,'UTF8')
          ||'&sex='||decode(a.BRXB00,'男','M','F')
          ||'&idCard='||form_url_encode(a.BRZJBH,'UTF8')
          ||'&mobile='||a.BRDH00
          ||'&gfno='||a.YBKH00
          ||'&homeAddress='||a.BRJTDZ
          ||'&cardId='||decode(b.ICKLB0,'2',b.KMH000,b.ICKH00)
          ||'&province='
          ||'&countycode='
          ||'&town='
          ||'&community='
          into ls_result1
       from BM_BRXXB0 a,IC_YHXX00 b where a.BRID00=ad_BRID00 and a.BRID00=b.BRID00 and b.ZT0000='1';
     end if;
     return LS_URL000||'?'||ls_result||ls_result1;
exception
   when Ecustom then
     return '1:'||Verrmsg;
   when others then
     return '1:错误原因不明！';
end;