CREATE OR REPLACE TRIGGER tr_yf_mzcf00_cfzt00_del
AFTER  DELETE  OR UPDATE
ON "SD_YF"."YF_MZCF00"
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
declare
  MZPY_JZHSFZJFY XT_XTCS00.VALUE0%type;--取号机是否直接发药
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- csf         2009.03.25  删除队列转移到任务
-- chenyw      2010.07.29  在处方状态不相等的情况触发 
-- csf         2010.09.17  如果有用自组取号机进行发药处理，那么在触发器中不改变yf_cfdl00的zt0000
-- --------    --------    ----------------------------
Begin
    select nvl(max(trim(VALUE0)),'N') into MZPY_JZHSFZJFY from XT_XTCS00 where NAME00='MZPY_JZHSFZJFY';
    if MZPY_JZHSFZJFY='N' then  
    if (UPDATing and (:NEW.CFZT00='2' or :NEW.CFZT00='3') and (:OLD.CFZT00<>:NEW.CFZT00)) OR DELETing then
      update YF_CFDL00 set ZT0000='3' WHERE CFLSH0=:OLD.CFLSH0;
    end if;
  end if;
EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20131, '删除处方队列出错！*返回信息:'||SQLERRM);
End;
/

CREATE OR REPLACE FUNCTION SF_YF_GETZD0(
 p_GHH000    IN  CHAR         --输入挂号号
 )RETURN CHAR IS           --疾病诊断

CURSOR  C_YS_BRZDXX  IS                                          --定义药品盘盈数据集游标
  SELECT Trim(ZDMC00)  FROM  YS_BRZDXX
   WHERE GHH000=p_GHH000 AND ZZDBZ0<>'3';

v_Return      VARCHAR2(2000);
V_ZDMC00      VARCHAR2(2000);
v_ZZDBZ0      VARCHAR2(2000);
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- CSF      2007.05.29     取出所有诊断
-- YANGY    2007.07.16     如果没有主要诊断，取其他诊断
BEGIN

  --先查询主诊断
  begin
    SELECT nvl(Trim(ZDMC00),'') INTO v_Return
    FROM YS_BRZDXX  WHERE GHH000=p_GHH000 AND ZZDBZ0='3' and rownum=1;
  exception
    when others then
    v_Return := ' ';
  end;
  
  --查询其他诊断
  OPEN  C_YS_BRZDXX;                                        --打开游标
  FETCH  C_YS_BRZDXX  INTO  V_ZDMC00;
  WHILE  C_YS_BRZDXX%FOUND  LOOP
    if v_Return<>' ' then
      v_Return:=v_Return||', '||V_ZDMC00;
    else
      v_Return:=V_ZDMC00;    
    end if;
    FETCH  C_YS_BRZDXX  INTO  V_ZDMC00;
  END  LOOP;      ---FOR  WHILE  ...  LOOP  OUTER
  CLOSE  C_YS_BRZDXX;

  RETURN v_Return;
END;
/

CREATE OR REPLACE FUNCTION SF_YF_GETXYZYZD(
 p_GHH000    IN  CHAR,         --输入挂号号
 p_SFXYZD    IN  CHAR,          --输入诊断类别 ‘1’西药诊断 ‘0’中药诊断
 p_CFLSH0    IN  VARCHAR2 Default '0'  --处方流水号
 )RETURN CHAR IS           --疾病诊断

CURSOR C_GETZD IS
  Select ZZDBZ0,REPLACE(REPLACE(TRIM(ZDMC00),'[','【'),']','】') ZDMC00 FROM YS_BRZDXX WHERE GHH000=p_GHH000 and SFXYZD=p_SFXYZD;
v_Return      VARCHAR2(1000);
V_XYZDMC      VARCHAR2(1000);--YS_BRZDXX.ZDMC00%TYPE;
V_ZYZDMC      VARCHAR2(1000);--YS_BRZDXX.ZDMC00%TYPE;

v_ZZDBZ0      VARCHAR2(1000);
V_ZDMC00      VARCHAR2(1000);
V_XYZYZD      VARCHAR2(800);--YS_BRZDXX.ZDMC00%TYPE;--西医主要诊断
V_XYQTZD      VARCHAR2(800);--YS_BRZDXX.ZDMC00%TYPE;--西医其他诊   
V_XYCSZD      VARCHAR2(800);--YS_BRZDXX.ZDMC00%TYPE;--西医从属诊
V_ZYJBZD      VARCHAR2(800);--YS_BRZDXX.ZDMC00%TYPE;--中医疾病
V_ZYHZZD      VARCHAR2(800);--YS_BRZDXX.ZDMC00%TYPE;--中医症候 
V_SFXYZD      YS_BRZDXX.SFXYZD%TYPE;--
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- CSF      2007.05.29     取出所有诊断，并分西医和中医
-- YANGH    2007.07.04     扩大返回的诊断名称的长度
-- YANGH    2007.08.01     删除诊断名称中的中括号“[]”?
-- YANGY    2009.02.26     每个处方的诊断可能不一样。
-- CSF      2010.03.24     西医主要诊断等的长度应该比诊断名称长
-- huangjy  2018.09.14     修改多个同类型的诊断时，只能获取其中一个，显示不全的问题  for YF-20180903-001
-- ---------   ----------  --------------------------------------------------
BEGIN
  

  V_ZYZDMC:=' ';
  V_XYZDMC:=' ';
  V_XYZYZD:=' ';
  V_XYQTZD:=' ';
  V_XYCSZD:=' ';
  V_ZYJBZD:=' ';
  V_ZYHZZD:=' ';
  if p_CFLSH0<>'0' then
      select NVL(TRIM(MAX(a.zdmc00)),''), NVL(TRIM(MAX(a.SFXYZD)),'1') INTO V_ZDMC00,V_SFXYZD from ys_brzdxx a, yf_mzcf00 b where a.zdxxid=b.zdxxid and b.cflsh0=p_CFLSH0;
  else
      V_ZDMC00 := '';
  end if;
  
  if nvl(V_ZDMC00,' ')=' ' then
      open C_GETZD;
      FETCH C_GETZD INTO v_ZZDBZ0,V_ZDMC00;
      WHILE C_GETZD%FOUND LOOP
        IF v_ZZDBZ0='3' then   --西医主要诊断
          V_XYZYZD:=V_ZDMC00;
        elsif v_ZZDBZ0='4' then  --4其他诊
          V_XYQTZD:=V_XYQTZD||' '||V_ZDMC00;
        elsif v_ZZDBZ0='5' then  --5从属诊
          V_XYCSZD:=V_XYCSZD||' '||V_ZDMC00;
        elsif v_ZZDBZ0='1' then  --中医疾病
          V_ZYJBZD:=V_ZYJBZD||' '||V_ZDMC00;
        elsif v_ZZDBZ0='2' then  --2中医症候 
          V_ZYHZZD:=V_ZYHZZD||' '||V_ZDMC00;
        END IF;
      FETCH C_GETZD INTO v_ZZDBZ0,V_ZDMC00;
      END LOOP;
      CLOSE C_GETZD;
      
      
      V_XYZDMC:=V_XYZYZD||' '||V_XYQTZD||' '||V_XYCSZD;
      
      V_ZYZDMC:=V_ZYJBZD||' '||V_ZYHZZD;
      if p_sfxyzd='1' then
        v_Return:=V_XYZDMC;
      else
        v_Return:=V_ZYZDMC;  
      end if;    
  else
      if V_SFXYZD=p_sfxyzd then   --要取的诊断类型 与该处方所对应的诊断类型一致时。
        v_Return := V_ZDMC00;
      else
        v_Return := ' ';
      end if;
  
  end if;  

  RETURN v_Return;
END;
/