CREATE OR REPLACE FUNCTION SF_YF_BYTJMX
-- modification history
-- Person      Date        Comments
-- qks         2013.01.29  create
-- qks         2013.03.26  modify
-- qks         2013.03.29  modify:代配"非"值要打勾;04.01:修改"补申请摆药单标志"
-- qks         2013.04.27  modify:其他摆药条件不包含: 手术、麻醉医嘱以及出院带药
-- qks         2013.05.23  modify:修改"补申请摆药单标志"
-- qks         2013.07.08  modify:允许同组分开发药(注意:静配不允许分开);
-- qks         2013.09.12  modify:修改"医嘱大类"对应摆药条件
-- qks         2015.12.27  增加“用法大类”摆药条件 by YF9-20151227-001
-- qks         2016.01.18  增加“药品剂型”摆药条件 by YF9-20160118-001
-- xuzw        2015.12.26  增加“摆药类型”摆药条件 口服，外用，针剂 YK9-20160301-002
-- qks         2017.09.07  增加“摆药类型”摆药条件 口服; by YF9-20170907-001
---liwm        2018.01.22  增加摆药条件“计生药品”； by YF9-20180105-001
-- liwm        2019.10.25  增加摆药条件"麻精(口服)""麻精(针剂)" YF9-20191025-002
-- luowf       2020.04.21  工程lxy归档增加摆药条件“大输液药品”    YF9-20200426-001
-- qks         2022.03.19  修改“针剂药品打印类别”摆药条件; for YF9EX-20220319-002
-- linshu      2024.04.15  增加了医嘱同组功能; for YF9-20240325-002
(
  PTABLE0  in  varchar2,           --YF_YZYPSQ别名
  PBYTJID  in  varchar2            --传入摆药条件ID
)
return varchar2
is

  I         NUMBER;
  VZB0000   YF_BYTJMX.ZB0000%type;
  VGLID00   YF_BYTJMX.GLID00%type;
  VZDMC00   varchar2(100);              --字段名称
  VFHYSF0   varchar2(10);               --符号运算符
  VGXYSF0   varchar2(10);               --关系运算符
  Vresult   varchar2(1000);
  VGXFPRI   YF_BYTJMX.GXF000%type;      --前一个关系运算符
  cursor c_bytjmx is select * from YF_BYTJMX where BYTJID=PBYTJID order by zb0000,GLID00,lsh000;
  v_bytjmx c_bytjmx%rowtype;
  VSFYZTZ   XT_XTCS00.VALUE0%type;
begin
  Vresult := '';
  VZB0000 := -1;
  --是否启用医嘱药品是否同组
  select nvl(max(trim(VALUE0)),'0') into VSFYZTZ from XT_XTCS00 where NAME00 = 'YF_YZYPSFTZ';
  open c_bytjmx;
  fetch c_bytjmx into v_bytjmx;
    while c_bytjmx%FOUND loop

      if v_bytjmx.SFFZ00 = 'Y' then
        VFHYSF0 := '<>';
      else
        VFHYSF0 := '=';
      end if;

      --if nvl(v_bytjmx.GXF000,'并且') = '并且' then
      if nvl(VGXFPRI,'并且') = '并且' then
        VGXYSF0 := ' and ';
      else
        VGXYSF0 := ' or ';
      end if;

      if VZB0000=-1 then  --开始
        Vresult := '((';
      elsif VZB0000<>-1 and VZB0000<>v_bytjmx.ZB0000 then --不同组之间一定是and连接
        Vresult := Vresult || ') and (';
      end if;

      if substr(trim(Vresult),-1)='(' then
          VGXYSF0 := '';
      end if;

        if v_bytjmx.GLMC00='医嘱大类' then  --VW_YF_BYTJYZDL
            if (VZB0000=v_bytjmx.ZB0000) and (VGLID00=v_bytjmx.GLID00) then
              Vresult := substr(Vresult,1,length(Vresult)-2)||VGXYSF0||' (kk.lbbh00||decode(kk.lbbh00,''2'',kk.ypczfl,''0'')'||VFHYSF0||'rpad('||v_bytjmx.TJID00||',2,0))))';
          else
            Vresult := Vresult||VGXYSF0||
              ' Exists (select 1 from YF_YZYPSQ jj,BM_YD0000 kk where  '||
              ' jj.YZID00='||PTABLE0||'.yzid00 and jj.ypnm00=kk.ypnm00 and ((kk.lbbh00||decode(kk.lbbh00,''2'',kk.ypczfl,''0'')'||VFHYSF0||'rpad('||v_bytjmx.TJID00||',2,0))))';

            end if;
      elsif v_bytjmx.GLMC00='补申请摆药单标志' then
        --暂时没处理
        --VFHYSF0:=VFHYSF0;
        if VFHYSF0 = '<>' then
            Vresult := Vresult||VGXYSF0||' '||PTABLE0||'.SFBT00<>''1'' ';
        else
          Vresult := Vresult || '1=1';
        end if;
      elsif v_bytjmx.GLMC00='毒理性质' then  --VW_YF_BYTJDLXZ
        if v_bytjmx.TJID00 = '0' then
            if VFHYSF0 = '<>' then
                VZDMC00 := ' KK.DPDJ00<>''0'' or KK.MZDJ00<>''0'' or KK.SJDJ00<>''0'' or KK.FSDJ00<>''0'' ';
          else
            VZDMC00 := ' KK.DPDJ00=''0'' and KK.MZDJ00=''0'' and KK.SJDJ00=''0'' and KK.FSDJ00=''0'' ';
          end if;
        elsif v_bytjmx.TJID00 = '1' then
            if VFHYSF0 = '<>' then
            VZDMC00 := ' KK.DPDJ00=''0'' ';
          else
            VZDMC00 := ' KK.DPDJ00<>''0'' ';
          end if;
        elsif v_bytjmx.TJID00 = '2' then
            if VFHYSF0 = '<>' then
            VZDMC00 := ' KK.MZDJ00=''0'' ';
          else
            VZDMC00 := ' KK.MZDJ00<>''0'' ';
          end if;
        elsif v_bytjmx.TJID00 = '31' then
            if VFHYSF0 = '<>' then
            VZDMC00 := ' KK.SJDJ00<>''1'' ';
          else
            VZDMC00 := ' KK.SJDJ00=''1'' ';
          end if;
        elsif v_bytjmx.TJID00 = '32' then
            if VFHYSF0 = '<>' then
            VZDMC00 := ' KK.SJDJ00<''2'' ';
          else
            VZDMC00 := ' KK.SJDJ00>=''2'' ';
          end if;
        elsif v_bytjmx.TJID00 = '4' then
            if VFHYSF0 = '<>' then
            VZDMC00 := ' KK.FSDJ00=''0'' ';
          else
            VZDMC00 := ' KK.FSDJ00<>''0'' ';
          end if;
        end if;

        if (VZB0000=v_bytjmx.ZB0000) and (VGLID00=v_bytjmx.GLID00) then
          Vresult := substr(Vresult,1,length(Vresult)-2)||VGXYSF0||'('||VZDMC00||')))';
        else
            Vresult := Vresult||VGXYSF0||
              ' Exists (select 1 from YF_YZYPSQ jj,BM_YD0000 kk where  '||
              ' jj.YZID00='||PTABLE0||'.yzid00 and jj.ypnm00=kk.ypnm00 and (('||VZDMC00||')))';
        end if;

      elsif v_bytjmx.GLMC00='医嘱属性' then  --VW_YF_BYTJYZSX(TJID00>=10属于长短嘱)
              if v_bytjmx.TJID00>=10 then
                Vresult := Vresult||VGXYSF0||
                  ' ('||PTABLE0||'.CLBZ00'||VFHYSF0||''''||to_char(to_number(v_bytjmx.TJID00)-10)||''')';
              else
                Vresult := Vresult||VGXYSF0||
                  ' ('||PTABLE0||'.YZXZ00'||VFHYSF0||''''||v_bytjmx.TJID00||''')';
                end if;
      elsif v_bytjmx.GLMC00='打印剂型' then  --不维护情况：非注射剂 VW_YK_YDBYFL
            if (VZB0000=v_bytjmx.ZB0000) and (VGLID00=v_bytjmx.GLID00) then
              Vresult := substr(Vresult,1,length(Vresult)-2)||VGXYSF0||' (kk.BYFL00'||VFHYSF0||''''||v_bytjmx.TJID00||''' or kk.BYFL00 is null and '||v_bytjmx.TJID00||'=1)))';
          else
            Vresult := Vresult||VGXYSF0||
              ' Exists (select 1 from YF_YZYPSQ jj,BM_YD0000 kk where  '||
              ' jj.YZID00='||PTABLE0||'.yzid00 and jj.ypnm00=kk.ypnm00 and ((kk.BYFL00'||VFHYSF0||''''||v_bytjmx.TJID00||''' or kk.BYFL00 is null and '||v_bytjmx.TJID00||'=1))) ';

            end if;
      elsif v_bytjmx.GLMC00='针剂药品打印类别' then  --不维护情况：普通药品 VW_YK_YDZSDYLB

            if (VZB0000=v_bytjmx.ZB0000) and (VGLID00=v_bytjmx.GLID00) then
              Vresult := substr(Vresult,1,length(Vresult)-2)||VGXYSF0||' (nvl(kk.ZSDYLB,0)'||VFHYSF0||v_bytjmx.TJID00||' or kk.ZSDYLB is null and '''||v_bytjmx.TJMC00||'''=''普通药品'')))';
          else
            Vresult := Vresult||VGXYSF0||
              ' Exists (select 1 from YF_YZYPSQ jj,BM_YD0000 kk where  '||
              ' jj.YZID00='||PTABLE0||'.yzid00 and jj.ypnm00=kk.ypnm00 and ((nvl(kk.ZSDYLB,0)'||VFHYSF0||v_bytjmx.TJID00||' or kk.ZSDYLB is null and '''||v_bytjmx.TJMC00||'''=''普通药品''))) ';

            end if;
      elsif v_bytjmx.GLMC00='用药方法' then  --VW_YF_BYTJYPYF

              Vresult := Vresult||VGXYSF0||
                ' ('||PTABLE0||'.YFID00'||VFHYSF0||v_bytjmx.TJID00||')';
      elsif v_bytjmx.GLMC00='用法大类' then  --VW_YF_YFLB00

              Vresult := Vresult||VGXYSF0||
                ' ('||PTABLE0||'.YFLBID'||VFHYSF0||v_bytjmx.TJID00||')';
      elsif v_bytjmx.GLMC00='药品剂型' then  --VW_YF_YPJX00

              Vresult := Vresult||VGXYSF0||
                ' ('||PTABLE0||'.LBJXBH'||VFHYSF0||''''||v_bytjmx.TJID00||''')';

      elsif v_bytjmx.GLMC00='摆药类型' and v_bytjmx.TJMC00 = '口服(短嘱)'then  --VW_YK_YDYPYF

            Vresult := Vresult||VGXYSF0||
            ' Exists (select 1 from BM_YD0000 kk where  '||
            '  '||PTABLE0||'.ypnm00=kk.ypnm00 and kk.YDYPYF = ''0'' and '||PTABLE0||'.YZXZMC = ''短嘱'' ) ';
      elsif v_bytjmx.GLMC00='摆药类型' and v_bytjmx.TJMC00 = '口服(长嘱)'then  --VW_YK_YDYPYF

            Vresult := Vresult||VGXYSF0||
            ' Exists (select 1 from BM_YD0000 kk where  '||
            '  '||PTABLE0||'.ypnm00=kk.ypnm00 and kk.YDYPYF = ''0'' and '||PTABLE0||'.YZXZMC = ''长嘱'' ) ';
      elsif v_bytjmx.GLMC00='摆药类型' and v_bytjmx.TJMC00 = '针剂' then  --VW_YK_YDYPYF

            Vresult := Vresult||VGXYSF0||
            ' Exists (select 1 from BM_YD0000 kk where  '||
            '  '||PTABLE0||'.ypnm00=kk.ypnm00 and kk.YDYPYF = ''2'' ) ';

      elsif v_bytjmx.GLMC00='摆药类型' and v_bytjmx.TJMC00 = '外用' then  --VW_YK_YDYPYF

            Vresult := Vresult||VGXYSF0||
            ' Exists (select 1 from BM_YD0000 kk where  '||
            '  '||PTABLE0||'.ypnm00=kk.ypnm00 and kk.YDYPYF = ''1''  ) ';

      elsif v_bytjmx.GLMC00='摆药类型' and v_bytjmx.TJMC00 = '口服' then

            Vresult := Vresult||VGXYSF0||
            ' Exists (select 1 from BM_YD0000 kk where  '||
            '  '||PTABLE0||'.ypnm00=kk.ypnm00 and kk.YDYPYF = ''0''  ) ';
     elsif v_bytjmx.GLMC00='摆药类型' and v_bytjmx.TJMC00 = '麻精(口服)' then

            Vresult := Vresult||VGXYSF0||
            ' Exists (select 1 from BM_YD0000 kk where  '||
            '  '||PTABLE0||'.ypnm00=kk.ypnm00 and kk.YDYPYF = ''4''  ) ';
       elsif v_bytjmx.GLMC00='摆药类型' and v_bytjmx.TJMC00 = '麻精(针剂)' then

            Vresult := Vresult||VGXYSF0||
            ' Exists (select 1 from BM_YD0000 kk where  '||
            '  '||PTABLE0||'.ypnm00=kk.ypnm00 and kk.YDYPYF = ''3''  ) ';
      elsif v_bytjmx.GLMC00='优先标志' then  --VW_YF_BYTJYXBZ(目前只考虑普通和紧急医嘱)
        if v_bytjmx.TJID00 = '0' then  --普通
            if VFHYSF0 = '<>' then
                VZDMC00 := 'lower('||PTABLE0||'.PCMC00) like ''%s%t%''';
          else
            VZDMC00 := 'lower('||PTABLE0||'.PCMC00) not like ''%s%t%''';
          end if;
        elsif v_bytjmx.TJID00 = '1' then --紧急
            if VFHYSF0 = '<>' then
                VZDMC00 := 'lower('||PTABLE0||'.PCMC00) not like ''%s%t%''';
          else
            VZDMC00 := 'lower('||PTABLE0||'.PCMC00) like ''%s%t%''';
          end if;
        end if;

        Vresult := Vresult||VGXYSF0|| '('||VZDMC00||')';


      elsif v_bytjmx.GLMC00='配送批次' then
        if VFHYSF0 = '<>' then
          Vresult := Vresult||VGXYSF0||
          ' (('||PTABLE0||'.PSPC00 is not null))';
        else
          Vresult := Vresult||VGXYSF0||
          ' (('||PTABLE0||'.PSPC00 is null))';
        end if;
      elsif v_bytjmx.GLMC00='其它' then --新莲花医院计生增加
        begin
          if v_bytjmx.TJMC00 = '计生药品' then  --计生药品
            if VFHYSF0 = '<>' then
              Vresult := Vresult||VGXYSF0||
                 ' Exists (select 1 from BM_YD0000 kk where  '||
                 '  '||PTABLE0||'.ypnm00=kk.ypnm00 and nvl(kk.SFJSYY, ''0'') <> ''1''  ) ';
            else
              Vresult := Vresult||VGXYSF0||
                 ' Exists (select 1 from BM_YD0000 kk where  '||
                 '  '||PTABLE0||'.ypnm00=kk.ypnm00 and nvl(kk.SFJSYY, ''0'') = ''1''  ) ';
            end if;
           end if;
           --lxy 平潭中医院 2020.04.21  增加摆药条件“大输液药品”      YF9-20200426-001
               if v_bytjmx.TJMC00 = '大输液药品' then--大输液
            if VFHYSF0 = '<>' then
              Vresult := Vresult||VGXYSF0||
                 ' Exists (select 1 from BM_YD0000 kk where  '||
                 '  '||PTABLE0||'.ypnm00=kk.ypnm00 and nvl(kk.dxsybz, ''0'') <> ''1''  ) ';
            else
              Vresult := Vresult||VGXYSF0||
                 ' Exists (select 1 from BM_YD0000 kk where  '||
                 '  '||PTABLE0||'.ypnm00=kk.ypnm00 and nvl(kk.dxsybz, ''0'') = ''1''  ) ';
            end if;
           end if;

        end;
      elsif v_bytjmx.GLMC00='项目类别' then  --VW_YK_YDYZSX
            if (VZB0000=v_bytjmx.ZB0000) and (VGLID00=v_bytjmx.GLID00) then
              Vresult := substr(Vresult,1,length(Vresult)-2)||VGXYSF0||' (kk.YPYZSX'||VFHYSF0||v_bytjmx.TJID00||')))';
          else
            Vresult := Vresult||VGXYSF0||
              ' Exists (select 1 from YF_YZYPSQ jj,BM_YD0000 kk where  '||
              ' jj.YZID00='||PTABLE0||'.yzid00 and jj.ypnm00=kk.ypnm00 and ((kk.YPYZSX'||VFHYSF0||v_bytjmx.TJID00||'))) ';
            end if;
       elsif v_bytjmx.GLMC00='材质分类' then


        Vresult := Vresult||VGXYSF0||
            ' Exists (select 1 from BM_YD0000 kk where  '||
            '  '||PTABLE0||'.ypnm00=kk.ypnm00 and kk.YPCZFL = ''3'' ) ';


      end if;

      VZB0000:=v_bytjmx.ZB0000;
      VGLID00:=v_bytjmx.GLID00;
      VGXFPRI:=v_bytjmx.GXF000;
    fetch c_bytjmx into v_bytjmx;
  end loop;
  close c_bytjmx;
  if Vresult is not null then
     if instrb(Vresult,'YZXZ00')<=0 then
          Vresult := Vresult||' and '||PTABLE0||'.YZXZ00=''0'' ';
     end if;
     Vresult := Vresult||'))';
  end if;
  --允许同组分开发药(注意:静配不允许分开)
  if VSFYZTZ <> '1' then
    if ( instr(Vresult,'PSPC00 is not null') = 0 ) or ( VSFYZTZ = '1' ) then
      Vresult := replace(Vresult,PTABLE0||'.yzid00',PTABLE0||'.ypqlpc');
      Vresult := replace(Vresult,'jj.YZID00','jj.ypqlpc');
    end if;
  end if;

  if VSFYZTZ = '1' then
    if instr(Vresult,'KK.DPDJ00<>''0''') > 0 or instr(Vresult,'KK.MZDJ00<>''0''') > 0 then
      Vresult := Vresult||' and a.TZDMDJ=''2'' ';
    elsif instr(Vresult,'KK.SJDJ00<>''0''') > 0 or instr(Vresult,'KK.SJDJ00=''2''') > 0 or instr(Vresult,'KK.SJDJ00=''1''') > 0 or instr(Vresult,'KK.SJDJ00>=''2''') > 0 or instr(Vresult,'KK.SJDJ00>=''1''') > 0 then
      Vresult := Vresult||' and a.TZDMDJ=''1'' ';
    else
      Vresult := Vresult||' and a.TZDMDJ=''0'' ';
    end if;
  end if;
  return Vresult;
end;