CREATE OR REPLACE FUNCTION SF_BQ_GETBRXMXSYS (PZYID00 number)
return varchar2 is
  COUNTER NUMBER(5) ;
  count1  NUMBER(5) ;
  count2  NUMBER(5) ;
  count3  NUMBER(5) ;
  count4  NUMBER(5) ;
  count5  NUMBER(5) ;
  count6  NUMBER(5) ;
  count7  NUMBER(5) ;
  VXZWHDYZBRYS  XT_XTCS00.VALUE0%TYPE;
  VYHDWTQYZBRYS  XT_XTCS00.VALUE0%TYPE;
  VYTQYZBRYS  XT_XTCS00.VALUE0%TYPE;
  VYZXYZBRYS  XT_XTCS00.VALUE0%TYPE;
  VYTYZBRYS    XT_XTCS00.VALUE0%TYPE;  --预停止颜色
  VCXYZBRYS    varchar2(10);--撤销医嘱病人当天与新开医嘱一样显示
  VJCYYBRYS    XT_XTCS00.VALUE0%TYPE;
  VJCYYANDXZYZBRYS XT_XTCS00.VALUE0%TYPE;  --检查预约未打印而且有新增医嘱
  VYCXYZBRYS   BM_TYZD00.BH0000%type;--预撤销病人颜色
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- liuj     2014.03.13     create for BQ-20140301-001
-- dsm      2014.04.09     停止未核对的也与新增未核对的同一种颜色 for BQ-20140329-001
-- dsm      2014.11.05     存在预停止的医嘱 也要能自定义头像的颜色 for BQ-20141028-002
-- dsm      2017.08.07     如果有撤销医嘱病人也要显示红色 for BQ-20170720-001
-- dsm      2018.01.05     现要求撤消医嘱核对处理后，病人头像要返回黑色。BQ-20171129-001
-- dsm      2018.03.02     如果病人检查预约成功了显示成亮黄色,预约单打印后恢复颜色 for BQ-20171101-001
-- dsm      2018.04.16     有检查预约还未打印+新医嘱的再定义一个颜色  BQ-20180315-001
-- dsm      2018.05.22     如果有预撤销医嘱时病人颜色可以设置 for BQ-20180509-001
-- dsm      2018.12.20     退费的预约单颜色没有去掉 归档 ZYYS-20181211-001
begin
  select count(1) into COUNTER from XT_XTCS00 where NAME00='BQ_YZLLBRXMSFAYZZTXSYS' and UPPER(TRIM(VALUE0))='Y';
  if COUNTER=0 then
     RETURN '0';
  end if;
  begin
    --撤销医嘱病人与新开病人一样
    select TRIM(VALUE0) into VCXYZBRYS from XT_XTCS00 where NAME00='BQ_CXYZBRYSXSXK';
  exception
    when others THEN
    VCXYZBRYS:='N';
  end;
  VXZWHDYZBRYS:='0';
  VYHDWTQYZBRYS:='0';
  VYTQYZBRYS:='0';
  VYZXYZBRYS:='0';
  VYTYZBRYS:='0';
  VJCYYBRYS:='0';
  VJCYYANDXZYZBRYS:='0';
  for c_brys in (select BH0000,MC0000 from BM_TYZD00 where ZDMC00='病区病人姓名颜色定义' order by XSXH00)
  loop
     if c_brys.BH0000='BQ_XZWHDYZBRYSSZ' then     --新增医嘱或未核对病人姓名颜色
          VXZWHDYZBRYS:=c_brys.MC0000;
     elsif  c_brys.BH0000='BQ_HDWTQYZBRYSSZ' then  --已核对未提取医嘱病人姓名颜色
          VYHDWTQYZBRYS:=c_brys.MC0000;
     elsif  c_brys.BH0000='BQ_YTQYZBRYSSZ' then   --已提取医嘱病人姓名颜色
          VYTQYZBRYS:=c_brys.MC0000;
     elsif  c_brys.BH0000='BQ_YZXYZBRYSSZ' then  --已执行医嘱病人姓名颜色
          VYZXYZBRYS:=c_brys.MC0000;
     elsif  c_brys.BH0000='BQ_YTZYZBRYSSZ' then   --预停止
          VYTYZBRYS:=c_brys.MC0000;
     elsif  c_brys.BH0000='BQ_CXYZBRYSSZ' then   --撤销医嘱病人颜色设置
          VCXYZBRYS:=c_brys.MC0000;
     elsif  c_brys.BH0000='BQ_JCYYBRYS' then      --检查预约成功了未打印
          VJCYYBRYS:=c_brys.MC0000;
     elsif c_brys.BH0000='BQ_JCYYANDXYZBRYS' then  --新医嘱+有医技预约回执单病人头像颜色
          VJCYYANDXZYZBRYS:=c_brys.MC0000;
     elsif c_brys.BH0000='BQ_YCXYZBRYS' then  --预撤销医嘱病人颜色
          VYCXYZBRYS:=c_brys.MC0000;
     end if;
  end loop;
  --新增或未核对
  --and TZRXM0 is null 表示还没有核对
  select count(1) into count1 from (
   select yzid00 from BQ_YPYZ00 where zyid00=PZYID00 and (yzzt00='0' or (yzzt00='3' and TZRXM0 is null)
                                     or (YZZT00='4' and TZRQ00=to_char(sysdate,'YYYYMMDD') and TZRXM0 is null and VCXYZBRYS='Y'  )
                                      )
   union all
   select YZMXID from BQ_YJYZ00 where zyid00=PZYID00 and (yzzt00='0' or (yzzt00='3' and TZRXM0 is null)
                                     or (YZZT00='4' and TZRQ00=to_char(sysdate,'YYYYMMDD') and TZRXM0 is null and VCXYZBRYS='Y')
                                     )

   );
  --已核对或未提取
  select count(1) into count2 from (
   select yzid00 from BQ_YPYZ00 where zyid00=PZYID00 and yzzt00='1'
   union all
   select YZMXID from BQ_YJYZ00 where zyid00=PZYID00 and yzzt00='1');
  --已提取
   select count(1) into count3 from BQ_YPYZ00 where zyid00=PZYID00 and yzzt00='2';
   --已执行
   select count(1) into count4 from BQ_YJYZ00 where zyid00=PZYID00 and yzzt00='2';
   --预停止
   select count(1) into count5 from (
   select yzid00 from BQ_YPYZ00 where zyid00=PZYID00 and yzzt00='5' and TZYS00>0
   union all
   select YZMXID from BQ_YJYZ00 where zyid00=PZYID00 and yzzt00='5' and TZYS00>0
   );
  --预撤销
  count7:=0;
   select count(1) into count7 from (
   select yzid00 from BQ_YPYZ00 where zyid00=PZYID00 and yzzt00 in('0','1') and TZYS00>0 and nvl(YCXBZ0,'0')='1'
   union all
   select YZMXID from BQ_YJYZ00 where zyid00=PZYID00 and yzzt00 in('0','1') and TZYS00>0  and nvl(YCXBZ0,'0')='1'
   );
  count6:=0;
  --BQ-20171101-001检查预约病人颜色
  if VJCYYBRYS<>'0'  then
    select count(*) into count6 from YJ_YYBRB0 a where ZYGHID=PZYID00 and MZZYBZ='1' and YYZT00='0' and nvl(DYCS00,0)=0
    and exists(select 1 from YJ_YW0000 where YJDJH0=a.YJDJH0 and XMZT00 not in('3','4'))
    and not exists(select 1 from YJ_YW0000 where ZYGHID=a.ZYGHID and CXDJH0=a.YJDJH0);
  end if;
  if count5>0 and  VYTYZBRYS='0' and count1=0  then
    count1:=1;
  end if;
  if nvl(VYCXYZBRYS,'0')<>'0' and count7>0 then
    return VYCXYZBRYS;
  end if;
  if (count1=0) and (count2=0) and (count3=0) and (count4=0) and (count5=0) then
    return '0';
  end if;

  if count6>0 and count1>0 then
     return VJCYYANDXZYZBRYS;
  end if;
  if count6>0 then
     return VJCYYBRYS;
  end if;
  if count1>0 then
     return VXZWHDYZBRYS;
  end if;
  if count5>0 then
    return VYTYZBRYS;
  end if;
  if count2>0 then
     return VYHDWTQYZBRYS;
  end if;
  if count3>0 then
     return VYTQYZBRYS;
  end if;
  if count4>0 then
     return VYZXYZBRYS;
  end if;

END SF_BQ_GETBRXMXSYS;