CREATE OR REPLACE FUNCTION SF_XSPXS( 
p_XSPBH0    IN    NUMBER    --显示屏编号
)RETURN VARCHAR2 IS         --返回该显示屏的显示内容 用'@','%','#'隔开
                            --如'0@123%  张三  普01请进#'
                            --表示向地址123的八字显示屏发送"  张三  普01请进"
                            --'A@BBB%CCCCCCCCC#'其中A为屏类型
                            --'0'八字(首屏闪烁)'1'八字'2'单行13字'3'双行13字'Z'不用发送
                            --'4'药房液晶显示器 '5'分诊显示器
                            --BBB为显示屏地址，CCCCCCCCC为发送的内容
  v_return  BM_XSPDM0.BZ0000%TYPE;
  v_FLAG00  BM_XSPDZ0.XSPLB0%TYPE;
  v_XSPDZ0  BM_XSPDZ0.XSPDZ0%TYPE;
  v_CKBH00  BM_CKBM00.CKBH00%TYPE;
  v_BRXM00  VARCHAR2(20);
  v_CFDLSJ  NUMBER(5);
  v_XSPYSL  NUMBER(2);
  i         NUMBER(3);
  v_GHYSXM  VARCHAR2(20);
  v_BMBH00  BM_BMBM00.BMBH00%TYPE;
  v_TYBZ00  Char(1);
  v_FYPYBZ  BM_XSPDM0.BZ0000%TYPE;
  v_XSBRZT  varchar2(20);
  v_CFLSH0  YF_MZCF00.CFLSH0%type;
  v_ZYTS00  YF_MZCF00.ZYTS00%type;
  v_YPDLBH  YF_MZCF00.YPDLBH%type;
  v_WS0000  number(3);
  v_XCF000  varchar2(20);
  v_XZJL00  varchar2(10);
  v_XZWS00  varchar2(10);
  MZPY_ZDCKPYFY XT_XTCS00.VALUE0%type;
--daihq   2014.01.27  医院导回,根据参数MZPY_ZDCKPYFY判断是否是小处方，若是在病人姓名后面加“优先”两个字 by YFPY-20140108-001
begin
  v_XCF000 := ' ';
  --取参数MZPY_ZDCKPYFY 是否限制剂量或者味数少于维护的value0值;value0值第一位判断剂量,第二位维护味数之间用分号隔开
  begin
  	select nvl(trim(max(VALUE0)),'N') into MZPY_ZDCKPYFY from XT_XTCS00 where NAME00='MZPY_ZDCKPYFY';
  	if instr(MZPY_ZDCKPYFY,';') > 0 then
  	  v_XZJL00 := substrb(MZPY_ZDCKPYFY,1,instr(MZPY_ZDCKPYFY,';')-1);
      v_XZWS00 := trim(substrb(MZPY_ZDCKPYFY,instr(MZPY_ZDCKPYFY,';') + 1,lengthb(MZPY_ZDCKPYFY)));
    end if;
  	exception
      when others then
        MZPY_ZDCKPYFY:='N';

  end;
  select XSPLB0,XSPDZ0 into v_FLAG00,v_XSPDZ0 from BM_XSPDZ0
    where XSPBH0=p_XSPBH0 and XSPXH0='0' and rownum=1;
  if v_FLAG00='0' then       --医生诊室候诊显示屏(1个诊室1个显示屏)
    v_return:='1@'||trim(to_char(v_XSPDZ0))||'%';
    begin
    select CKBH00 into v_CKBH00 from BM_CKBM00
      where DQXSP0=p_XSPBH0 and FLAG00='4' and ZT0000='0' and rownum=1;
    EXCEPTION WHEN NO_DATA_FOUND THEN
      v_CKBH00:=-1;
      v_return:=v_return||'省中医院祝您健康#';
      return v_return;
    end;
    begin
      select trim(substrb(ZWXM00,1,8)) into v_GHYSXM from BM_YGBM00 where YGBH00 in
      (select GHYSBH from SF_YSGHHB where GHMZSJ=to_char(SYSDATE,'YYYYMMDD') and TZBZ00='0' and
        to_char(SYSDATE,'HH24:MI:SS') between GHQSSJ and GHJSSJ and GHHZCK=v_CKBH00) and rownum=1;
    EXCEPTION WHEN NO_DATA_FOUND THEN
      v_GHYSXM:='省中医院';
    end;
    v_GHYSXM:=replace(rpad(lpad(v_GHYSXM,(8+lengthb(trim(v_GHYSXM)))/2,' '),8,' '),' ','  ');
    declare cursor c_hzbr is
    select BRID00,HZZT00,HZXHMC from (select BRID00,decode(HZZT00,'4','4','5','0','0') HZZT00,HZXHMC from SF_HZBR00
      where exists (select 1 from BM_CKBM00 where DQXSP0=p_XSPBH0 and FLAG00='4' and ZT0000='0' and CKBH00=SF_HZBR00.JZZS00)
       and HZZT00 in ('4') and HZQSRQ=to_char(SYSDATE,'YYYYMMDD')
       and exists (select 1 from SF_YSGHHB where GHMZSJ=to_char(SYSDATE,'YYYYMMDD') and TZBZ00='0'
       and to_char(SYSDATE,'HH24:MI:SS') between GHQSSJ and GHJSSJ and GHHZCK=SF_HZBR00.JZZS00 and GHYSBH=SF_HZBR00.GHYS00)
      order by decode(HZZT00,'4','4','5','0','0') desc,DLXH00) where rownum<5;
    begin
    for c_field in c_hzbr loop
      begin
        select trim(substrb(BRXM00,1,8)) into v_BRXM00 from BM_BRXXB0 where BRID00=c_field.BRID00;
      exception when NO_DATA_FOUND then
        v_BRXM00:='';
      end;
      v_BRXM00:=nvl(v_BRXM00,'');
      v_BRXM00:=rpad(lpad(v_BRXM00,(8+lengthb(trim(v_BRXM00)))/2,' '),8,' ');
      if c_field.HZZT00='4' then
        v_return:='0'||substr(v_return,2,length(v_return)-1)||v_BRXM00||substrb(nvl(c_field.HZXHMC,''),1,4)||'请进';
      else
        v_return:=v_return||v_BRXM00||substrb(nvl(c_field.HZXHMC,''),1,4)||'稍侯';
      end if;
    end loop;
    end;
--    begin
    v_return:=replace(v_return,' ','  ')||'#';
    v_return:=replace(v_return,'%#','%'||v_GHYSXM||'祝您健康#');
  elsif v_FLAG00='1'  then     --药房配药发药显示屏
    begin
    select to_number(VALUE0) into v_CFDLSJ from XT_XTCS00 where NAME00='XT_CFDLSJ' and rownum=1;
    EXCEPTION WHEN NO_DATA_FOUND THEN
      v_CFDLSJ:=30;
    end;
    if v_CFDLSJ<30 then
      v_CFDLSJ:=30;
    end if;
    v_return:='2@'||trim(to_char(v_XSPDZ0))||'%';
    begin
    select CKBH00 into v_CKBH00 from BM_CKBM00
      where DQXSP0=p_XSPBH0 and FLAG00='3' and ZT0000='0' and rownum=1;
    EXCEPTION WHEN NO_DATA_FOUND THEN
      v_CKBH00:=-1;
    end;
    if v_CKBH00<>-1 then
      declare cursor c_fydl is
      select rownum XH0000,NO0000,BRXM00 from (select trim(to_char(rownum)) NO0000,BRXM00 from (select BRXM00,max(PYSJ00) from YF_CFDL00
        where PYRQ00=to_char(SYSDATE,'YYYYMMDD') and FYCKBH=v_CKBH00 and ZT0000='2' and PYSJ00>=to_char(SYSDATE-v_CFDLSJ/1440,'HH24:MI:SS') group by BRXM00 order by max(PYSJ00)) where rownum<3
        union all
        select ' ',' ' from BM_BMBM00 where rownum<3) where rownum<3;
      begin
      for v_fydl in c_fydl loop
        if mod(v_fydl.XH0000,2)=0 then
          v_return:=v_return||nvl(trim(v_fydl.NO0000),' ')||rpad(nvl(v_fydl.BRXM00,' '),8,' ')||'  请取药';
        else
          v_return:=v_return||nvl(trim(v_fydl.NO0000),' ')||rpad(nvl(v_fydl.BRXM00,' '),8,' ');
        end if;
      end loop;
      end;
    end if;
    begin
    select XSPDZ0,XSPLB0 into v_XSPDZ0,v_FLAG00 from BM_XSPDZ0
      where XSPBH0=p_XSPBH0 and XSPXH0='1' and rownum=1;
    exception when NO_DATA_FOUND then
      return v_return;
    end;
    if v_FLAG00='1' then
      v_return:=v_return||'#2@'||trim(to_char(v_XSPDZ0))||'%';
      v_XSPYSL:=2;
    elsif v_FLAG00='2' then
      v_return:=v_return||'#3@'||trim(to_char(v_XSPDZ0))||'%';
      v_XSPYSL:=5;
    else
      return v_return;
    end if;
    if v_CKBH00=-1 then
      v_return:=v_return||'#';
      return v_return;
    end if;
    declare cursor c_pydl is
    select rownum XH0000,NO0000,BRXM00 from (select trim(to_char(rownum)) NO0000, BRXM00 from (select BRXM00,max(DYSJ00) from YF_CFDL00
      where DYRQ00=to_char(SYSDATE,'YYYYMMDD') and FYCKBH=v_CKBH00 and ZT0000='1' and DYSJ00>=to_char(SYSDATE-v_CFDLSJ/1440,'HH24:MI:SS') group by BRXM00 order by max(DYSJ00))
          where rownum<=v_XSPYSL
    union all
    select ' ',' ' from BM_BMBM00 where rownum<=v_XSPYSL) where rownum<=v_XSPYSL;
    begin
    for v_pydl in c_pydl loop
      if mod(v_pydl.XH0000,v_XSPYSL)=0 then
        v_return:=v_return||nvl(trim(v_pydl.NO0000),' ')||rpad(nvl(v_pydl.BRXM00,' '),8,' ')||'  请稍侯';
      elsif mod(v_pydl.XH0000,v_XSPYSL)=3 then
        v_return:=v_return||' '||nvl(trim(v_pydl.NO0000),' ')||rpad(nvl(v_pydl.BRXM00,' '),6,' ');
      else
        v_return:=v_return||nvl(trim(v_pydl.NO0000),' ')||rpad(nvl(v_pydl.BRXM00,' '),8,' ');
      end if;
    end loop;
    end;
    v_return:=replace(v_return,' ','  ')||'#';  --,' 请拿药','请拿药'),' 请稍侯','请稍侯');
--      v_return:=replace(replace(replace(v_return,' ','  ')||'#',' 请拿药','请拿药'),' 请稍侯','请稍侯');
  elsif v_FLAG00='4'  then     --药房配药发药液晶显示屏
    begin
    select to_number(VALUE0) into v_CFDLSJ from XT_XTCS00 where NAME00='XT_CFDLSJ' and rownum=1;
    EXCEPTION WHEN NO_DATA_FOUND THEN
      v_CFDLSJ:=15;
    end;
    if v_CFDLSJ<15 and sf_sf_getyyid00()<>'H00000' then
      v_CFDLSJ:=15;
    end if;
    v_return:='';
    begin
    select CKBH00,decode(FLAG00,'9','1','0'),BMBH00 into v_CKBH00,v_TYBZ00,v_BMBH00 from BM_CKBM00
      where DQXSP0=p_XSPBH0 and FLAG00 in('3','9') and ZT0000='0' and rownum=1;
    EXCEPTION WHEN NO_DATA_FOUND THEN
      v_CKBH00:=-1;
      v_TYBZ00:='0';
      v_BMBH00:=-1;
    end;
    if v_CKBH00<>-1 then
--      begin
--        select decode(trim(BZ0000),'1','1','0') into v_FYPYBZ from BM_XSPDM0 where XSPBH0=p_XSPBH0;
--      exception when NO_DATA_FOUND then
        v_FYPYBZ:='0';
--      end;
      if v_TYBZ00='0' then
--       if v_CKBH00=250 or v_CKBH00=156 then
--        declare cursor c_fydl is
--       select rownum XH0000,trim(to_char(FYDLXH)) NO0000,BRXM00 from (select BRXM00,min(FYDLXH) FYDLXH from YF_CFDL00
--          where PYRQ00=to_char(SYSDATE,'YYYYMMDD') and FYCKBH=v_CKBH00 and ZT0000='2' and THBZ00='0' and JHCS00=0 --and PYSJ00>=to_char(SYSDATE-v_CFDLSJ/1440,'HH24:MI:SS')
--          group by BRXM00 order by FYDLXH)
--          -- where rownum<=60;
--          -- 珠海医院只显示三个病人  linzy
--          where rownum<=3;
--        begin
--        i:=1;
--        for v_fydl in c_fydl loop
--          i:=v_fydl.XH0000;
--          if i=1 then
--         v_XSBRZT:='取药';
--          else
--          v_XSBRZT:='稍候';
--          end if;
--          v_return:=v_return||lpad(nvl(trim(v_fydl.NO0000),' '),4,' ')||lpad(nvl(v_fydl.BRXM00,' '),8,' ')||' '||v_XSBRZT||CHR(13);
--          if mod(v_fydl.XH0000,3)=0 and v_fydl.XH0000<=60 then
--            v_return:=v_return||'%';
--          end if;
--        end loop;
--        end;
--       else
         declare cursor c_fydl is
        select rownum XH0000,trim(to_char(FYDLXH)) NO0000,BRXM00 from (select BRXM00,min(FYDLXH) FYDLXH from YF_CFDL00
          where PYRQ00=to_char(SYSDATE,'YYYYMMDD') and FYCKBH=v_CKBH00 and ZT0000='2' and THBZ00='0' and PYSJ00>=to_char(SYSDATE-v_CFDLSJ/1440,'HH24:MI:SS') group by BRXM00 order by FYDLXH)
          where rownum<=60;
        begin
        i:=1;
        for v_fydl in c_fydl loop
          i:=v_fydl.XH0000;
          if MZPY_ZDCKPYFY <> 'N' then
            select CFLSH0 into v_CFLSH0 from YF_CFDL00
            where PYRQ00=to_char(SYSDATE,'YYYYMMDD') and FYCKBH=v_CKBH00 and ZT0000='2' and THBZ00='0' and PYSJ00>=to_char(SYSDATE-v_CFDLSJ/1440,'HH24:MI:SS')
              and FYDLXH=to_number(v_fydl.NO0000) and rownum=1;
            select nvl(ZYTS00,0),YPDLBH into v_ZYTS00,v_YPDLBH from YF_MZCF00 where CFLSH0=v_CFLSH0 and rownum=1;
            select count(distinct YPNM00) into v_WS0000  from yf_mzcfmx where cflsh0=v_CFLSH0;
            if ((v_ZYTS00<=to_number(v_XZJL00)) or (v_WS0000<=to_number(v_XZWS00))) and (v_YPDLBH='2')  then
              v_XCF000 := 'Y';
            else
              v_XCF000 := ' ';
            end if;
          end if;
          v_return:=v_return||lpad(nvl(trim(v_XCF000||v_fydl.NO0000),' '),4,' ')||lpad(nvl(v_fydl.BRXM00,' '),8,' ')||CHR(13);
          if mod(v_fydl.XH0000,3)=0 and v_fydl.XH0000<=60 then
            v_return:=v_return||'%';
          end if;
        end loop;
        end;
--        end if;
        if mod(i,3)<>0 then
          v_return:=v_return||'%';
        end if;
      else
        declare cursor c_tydl is
        select rownum XH0000,trim(to_char(FYDLXH)) NO0000,BRXM00 from (select BRXM00,min(FYDLXH) FYDLXH from YF_CFDL00
          where DYRQ00=to_char(SYSDATE,'YYYYMMDD') and FYCKBH in (select CKBH00 from BM_CKBM00 where BMBH00 in
                (select BMBH00 from BM_CKBM00 where DQXSP0=p_XSPBH0 and FLAG00='9' and ZT0000='0') and FLAG00='3') and THBZ00='1' and DYSJ00>=to_char(SYSDATE-60/1440,'HH24:MI:SS')
           group by BRXM00 order by FYDLXH)
           where rownum<=60;
        begin
        i:=1;
        for v_tydl in c_tydl loop
          i:=v_tydl.XH0000;
          if MZPY_ZDCKPYFY <> 'N' then
            select CFLSH0 into v_CFLSH0 from YF_CFDL00 where DYRQ00=to_char(SYSDATE,'YYYYMMDD') and FYCKBH in (select CKBH00 from BM_CKBM00 where BMBH00 in
                  (select BMBH00 from BM_CKBM00 where DQXSP0=p_XSPBH0 and FLAG00='9' and ZT0000='0') and FLAG00='3') and THBZ00='1' and DYSJ00>=to_char(SYSDATE-60/1440,'HH24:MI:SS')
                  and FYDLXH=to_number(v_tydl.NO0000) and rownum=1;
            select nvl(ZYTS00,0),YPDLBH into v_ZYTS00,v_YPDLBH from YF_MZCF00 where CFLSH0=v_CFLSH0 and rownum=1;
            select count(distinct YPNM00) into v_WS0000  from yf_mzcfmx where cflsh0=v_CFLSH0;
            if ((v_ZYTS00<=to_number(v_XZJL00)) or (v_WS0000<=to_number(v_XZWS00))) and (v_YPDLBH='2') then
              v_XCF000 := 'Y';
            else
              v_XCF000 := ' ';
            end if;
          end if;
          v_return:=v_return||lpad(nvl(trim(v_XCF000||v_tydl.NO0000),' '),4,' ')||lpad(nvl(v_tydl.BRXM00,' '),8,' ')||CHR(13);
          if mod(v_tydl.XH0000,3)=0 and v_tydl.XH0000<=60 then
            v_return:=v_return||'%';
          end if;
        end loop;
        if mod(i,3)<>0 then
          v_return:=v_return||'%';
        end if;
        end;
      end if;
    end if;
  end if;
  return v_return;
end;
/