-- Start of DDL Script for Trigger SD_HOSPITAL.TR_JY_JYJG00_TEMP_BINS
-- Generated 2013-4-16 9:16:53 from SD_HOSPITAL@NASY

CREATE OR REPLACE TRIGGER tr_jy_jyjg00_temp_bins
 BEFORE
  INSERT OR UPDATE
 ON jy_jyjg00_temp
REFERENCING NEW AS NEW OLD AS OLD
 FOR EACH ROW
DECLARE
  V_COUNT0 	NUMBER;
  V_JYYWID	JY_YW0000.JYYWID%TYPE;
  V_ZLXMID	JY_YW0000.ZLXMID%TYPE;
  V_XB0000	JY_YW0000.XB0000%TYPE;
  V_NL0000	JY_YW0000.NL0000%TYPE;
  V_NLDW00	JY_YW0000.NLDW00%TYPE;
  V_BBID00	JY_YW0000.BBID00%TYPE;
  V_BGR000	JY_YW0000.BGR000%TYPE;
  V_CKZNL0	JY_BGXMZD.CKZNL0%TYPE;
  V_CKZXB0	JY_BGXMZD.CKZXB0%TYPE;
  V_CKZBB0	JY_BGXMZD.CKZBB0%TYPE;
  V_QSZ000	JY_BGXMZD.QSZ000%TYPE;
  V_DW0000	JY_BGXMZD.DW0000%TYPE;
  V_JGLX00	JY_BGXMZD.JGLX00%TYPE;
  V_JD0000	JY_BGXMZD.JD0000%TYPE;
  V_XXTS00	JY_XMCKZ0.XXTS00%TYPE;
  V_SXTS00	JY_XMCKZ0.SXTS00%TYPE;
  V_JYJG00	JY_JYJG00_TEMP.JYJG00%TYPE;
  V_VALUE0	CHAR(1);
  V_JGFS00	VARCHAR2(100);
  V_JYJG01	NUMBER(20,5);
  lsJY_JYJGTSTS number(5);
  E_MODIFY	EXCEPTION;
BEGIN
  V_JYYWID := 0;
  V_ZLXMID := 0;
  :NEW.SFDY00 := 'N';
  begin
    select instr(','||trim(value0)||',',','||trim(:new.yqdh00)||',') into lsJY_JYJGTSTS  from xt_xtcs00 where name00 in ('JY_JYJGTSTS');
  exception
  when others then
     lsJY_JYJGTSTS:=0;
  end;
  IF UPDATING THEN
  	V_BGR000 := 0;
  	IF (:NEW.CZR000 IS NOT NULL) AND (:NEW.JYJG00 <> :OLD.JYJG00) THEN
  		BEGIN
  			SELECT TRIM(VALUE0) INTO V_VALUE0 FROM XT_XTCS00 WHERE NAME00='JY_PYRXGJG';
  			IF V_VALUE0 = 'Y' THEN
  				SELECT BGR000 INTO V_BGR000 FROM JY_YW0000 WHERE PYRQ00=:NEW.PYRQ00 AND YBH000=:NEW.YBH000 AND ROWNUM=1;
  			END IF;
  		EXCEPTION
  			WHEN NO_DATA_FOUND THEN
  				V_BGR000 := 0;
  		END;
  		--IF (V_BGR000 <> 0) AND (:NEW.CZR000 <> V_BGR000) THEN
  			--RAISE E_MODIFY;
  		--END IF;
  	END IF;
  END IF;
  
  :NEW.JYJG00 := TRIM(:NEW.JYJG00);
  IF :NEW.JYJG00 = '' THEN
  	:NEW.JYJG00 := NULL;
  END IF;
  
  IF :NEW.BGXMID < 999992 THEN
  	SELECT JGLX00,JD0000 INTO V_JGLX00,V_JD0000 FROM JY_BGXMZD WHERE BGXMID=:NEW.BGXMID;  
  END IF;
  
  IF ((V_JGLX00 = 'N') OR (V_JGLX00 = 'C')) AND (:NEW.JYJG00 IS NOT NULL) THEN 
  	V_JGFS00 := '9999999999999999999.';
    	V_JGFS00 := RPAD(V_JGFS00,LENGTH(V_JGFS00)+V_JD0000,'9');  	
  	BEGIN
  		V_JYJG01 := ROUND(:NEW.JYJG00,V_JD0000);
  		SELECT TRIM(TO_CHAR(V_JYJG01,V_JGFS00)) INTO :NEW.JYJG00 FROM DUAL;
  		IF INSTR(:NEW.JYJG00,'-.') = 1 THEN
  			:NEW.JYJG00 := '-0.'||SUBSTR(:NEW.JYJG00,3,LENGTH(:NEW.JYJG00));
  		END IF;
  		IF INSTR(:NEW.JYJG00,'.') = 1 THEN
  			:NEW.JYJG00 := '0'||:NEW.JYJG00;
  		END IF;
  		IF INSTR(:NEW.JYJG00,'.') = LENGTH(:NEW.JYJG00) THEN
  			:NEW.JYJG00 := SUBSTR(:NEW.JYJG00,1,LENGTH(:NEW.JYJG00)-1);
  		END IF;  		
  	EXCEPTION
  		WHEN OTHERS THEN
  			:NEW.JYJG00 := :NEW.JYJG00;
  	END;
  END IF; 
  
  SELECT COUNT(*) INTO V_COUNT0 FROM JY_YW0000_CKZ000 WHERE YBH000=:NEW.YBH000 AND PYRQ00=:NEW.PYRQ00;
  IF V_COUNT0 > 0 THEN
  	SELECT JYYWID,ZLXMID,XB0000,NL0000,NLDW00,BBID00 INTO V_JYYWID,V_ZLXMID,V_XB0000,V_NL0000,V_NLDW00,V_BBID00 
  	  FROM JY_YW0000_CKZ000 WHERE YBH000=:NEW.YBH000 AND PYRQ00=:NEW.PYRQ00 AND ROWNUM=1;	  
  ELSE 
  	SELECT COUNT(*) INTO V_COUNT0 FROM JY_YW0000 WHERE YBH000=:NEW.YBH000 AND PYRQ00=:NEW.PYRQ00;
  	IF V_COUNT0 > 0 THEN
        	SELECT JYYWID,ZLXMID,XB0000,NL0000,NLDW00,BBID00 INTO V_JYYWID,V_ZLXMID,V_XB0000,V_NL0000,V_NLDW00,V_BBID00 
        	  FROM JY_YW0000 WHERE YBH000=:NEW.YBH000 AND PYRQ00=:NEW.PYRQ00  AND ROWNUM=1;   
        	  
        	select XB0000,NL0000,NLDW00,BBID00 INTO V_XB0000,V_NL0000,V_NLDW00,V_BBID00 FROM JY_YW0000 WHERE YBH000=:NEW.YBH000 AND PYRQ00=:NEW.PYRQ00 AND ROWNUM=1;
         /*
            --jyywid和zlxmid 另外处理
            begin
               select count(1) into Vcount from JY_YWMX00 a,JY_ZLBGDY b where a.zlxmid=b.zlxmid and b.bgxmid=:new.bgxmid and b.yqdh00=:new.YQDH00 and  a.YBH000=:NEW.YBH000 AND a.PYRQ00=:NEW.PYRQ00;
            exception
            when others then
              Vcount:=0;
            end;
            if Vcount>0 then
               select a.zlxmid,a.jyywid into V_ZLXMID,V_JYYWID from JY_YWMX00 a,JY_ZLBGDY b where a.zlxmid=b.zlxmid and b.bgxmid=:new.bgxmid and b.yqdh00=:new.YQDH00
                   and  a.YBH000=:NEW.YBH000 AND a.PYRQ00=:NEW.PYRQ00 and rownum=1;
            else
               V_ZLXMID:=0;
               V_JYYWID:=0;
            end if; 
          */     	       	  
    	END IF;    
  END IF;   

  IF NVL(:NEW.JYYWID,0) = 0 THEN
       	:NEW.JYYWID := V_JYYWID;
  END IF;
  IF :NEW.JYYWID <> 0 THEN :NEW.SFDY00 := 'Y'; END IF;
  IF NVL(:NEW.ZLXMID,0) = 0 THEN
  	:NEW.ZLXMID := V_ZLXMID;    
  END IF;  
  IF :NEW.ZLXMID=99999999 THEN 
  	:NEW.JYYWID := V_JYYWID;
  	:NEW.ZLXMID := V_ZLXMID;	
  END IF;
    
  IF :NEW.BGXMID > 999993 THEN 
  	RETURN;
  END IF;
  
  IF INSERTING AND (:NEW.JYYWID <> 0) THEN
        INSERT INTO JY_YWMX00(JYMXID,JYYWID,ZLXMID,PYRQ00,YBH000) SELECT SEQ_JY_YWMX00_JYMXID.NEXTVAL,:NEW.JYYWID,:NEW.ZLXMID,:NEW.PYRQ00,:NEW.YBH000
          FROM DUAL WHERE NOT EXISTS(SELECT 1 FROM JY_YWMX00 WHERE YBH000=:NEW.YBH000 AND PYRQ00=:NEW.PYRQ00 AND ZLXMID=:NEW.ZLXMID);
  END IF;    
   
  :NEW.JGZCBZ := NULL;
  :NEW.SFCJJZ := 'N';    
     
--  IF (:NEW.CKFW00 IS NULL) OR (TRIM(:NEW.CKFW00) = '') THEN 	
  BEGIN
  	SELECT CKZNL0,CKZXB0,CKZBB0,QSZ000,DW0000 INTO V_CKZNL0,V_CKZXB0,V_CKZBB0,V_QSZ000,V_DW0000
  	FROM JY_BGXMZD WHERE BGXMID=:NEW.BGXMID;
  	
  	IF V_CKZNL0 = 'Y' THEN
  		SELECT a.ckxx00,a.cksx00,a.ckfw00,a.jjzsx0,a.jjzxx0,XXTS00,SXTS00 INTO :NEW.CKXX00,:NEW.CKSX00,:NEW.CKFW00,:NEW.JJZSX0,:NEW.JJZXX0,V_XXTS00,V_SXTS00 
  		FROM JY_XMCKZ0 A 
  		WHERE BGXMID=:NEW.BGXMID 
  		AND NVL(XBDM00,'W')=NVL(DECODE(V_CKZXB0,'Y',V_XB0000,XBDM00),'W')
  		AND NVL(BBID00,0)=NVL(DECODE(V_CKZBB0,'Y',V_BBID00,BBID00),0)
  		AND decode(V_NLDW00,'M',TO_NUMBER(V_NL0000)*30,'W',TO_NUMBER(V_NL0000)*7,TO_NUMBER(V_NL0000))>=decode(A.NLDW00,'M',NVL(A.NLXX00,0)*30,'W',NVL(A.NLXX00,0)*7,NVL(A.NLXX00,0))
  		AND decode(V_NLDW00,'M',TO_NUMBER(V_NL0000)*30,'W',TO_NUMBER(V_NL0000)*7,TO_NUMBER(V_NL0000))<=decode(A.NLDW00,'M',NVL(A.NLSX00,180)*30,'W',NVL(A.NLSX00,1200)*7,NVL(A.NLSX00,180))
  		AND decode(V_NLDW00,'Y','Y','D')=decode(A.NLDW00,'Y','Y','D') AND ROWNUM=1;
  	ELSE 
  		SELECT a.ckxx00,a.cksx00,a.ckfw00,a.jjzsx0,a.jjzxx0,XXTS00,SXTS00 INTO :NEW.CKXX00,:NEW.CKSX00,:NEW.CKFW00,:NEW.JJZSX0,:NEW.JJZXX0,V_XXTS00,V_SXTS00 
  		FROM JY_XMCKZ0 A 
  		WHERE BGXMID=:NEW.BGXMID 
  		AND NVL(XBDM00,'W')=NVL(DECODE(V_CKZXB0,'Y',V_XB0000,XBDM00),'W')
  		AND NVL(BBID00,0)=NVL(DECODE(V_CKZBB0,'Y',V_BBID00,BBID00),0)
  		AND ROWNUM=1;  		
  	END IF;
  EXCEPTION
  	WHEN NO_DATA_FOUND THEN
  		RETURN;
  END;
--  END IF;
   
  IF (:NEW.CKFW00 IS NULL) OR (TRIM(:NEW.CKFW00) = '') THEN 
  	RETURN;
  END IF;
   
  IF (V_JGLX00 = 'N') OR (V_JGLX00 = 'C') THEN
  	BEGIN
  		IF TO_NUMBER(:NEW.JYJG00) > TO_NUMBER(:NEW.CKSX00) THEN
  			:NEW.JGZCBZ := V_SXTS00;
  		END IF;
  	EXCEPTION
  		WHEN OTHERS THEN	
  			:NEW.JGZCBZ := NULL;
  	END;
  	BEGIN
  		IF TO_NUMBER(:NEW.JYJG00) < TO_NUMBER(:NEW.CKXX00) THEN
  			:NEW.JGZCBZ := V_XXTS00;
  		END IF;
  	EXCEPTION
  		WHEN OTHERS THEN
  			:NEW.JGZCBZ := NULL;
  	END;  	
  	BEGIN
  		IF (TO_NUMBER(:NEW.JYJG00) > TO_NUMBER(:NEW.JJZSX0)) OR (TO_NUMBER(:NEW.JYJG00) < TO_NUMBER(:NEW.JJZXX0)) THEN
  			:NEW.SFCJJZ := 'Y';
  		END IF;
  	EXCEPTION
  		WHEN OTHERS THEN
  			:NEW.SFCJJZ := 'N';
  	END;    	
  	IF INSTR(:NEW.JYJG00,'>') = 1 THEN
  		IF (INSTR(:NEW.JYJG00,'>=') = 1) or (INSTR(:NEW.JYJG00,'>>') = 1) THEN	
  			V_JYJG00 := SUBSTR(:NEW.JYJG00,3,LENGTH(:NEW.JYJG00));
  		ELSE
  			V_JYJG00 := SUBSTR(:NEW.JYJG00,2,LENGTH(:NEW.JYJG00));
  		END IF;
  		BEGIN
  			IF TO_NUMBER(V_JYJG00)+0.0001 > TO_NUMBER(:NEW.CKSX00) THEN
  				:NEW.JGZCBZ := V_SXTS00;
  			END IF;
  		EXCEPTION
  			WHEN OTHERS THEN
  				:NEW.JGZCBZ := NULL;
  		END;  
  		BEGIN
  			IF TO_NUMBER(V_JYJG00)+0.0001 > TO_NUMBER(:NEW.JJZSX0) THEN
  				:NEW.SFCJJZ := 'Y';
  			END IF;
  		EXCEPTION
  			WHEN OTHERS THEN
  				:NEW.SFCJJZ := 'N';
  		END;   				
  	END IF;
  	IF INSTR(:NEW.JYJG00,'<') = 1 THEN
  		IF (INSTR(:NEW.JYJG00,'<=') = 1) or (INSTR(:NEW.JYJG00,'<<') = 1) THEN	
  			V_JYJG00 := SUBSTR(:NEW.JYJG00,3,LENGTH(:NEW.JYJG00));
  		ELSE
  			V_JYJG00 := SUBSTR(:NEW.JYJG00,2,LENGTH(:NEW.JYJG00));
  		END IF;
  		BEGIN
  			IF TO_NUMBER(V_JYJG00)-0.0001 < TO_NUMBER(:NEW.CKXX00) THEN
  				:NEW.JGZCBZ := V_XXTS00;
  			END IF;
  		EXCEPTION
  			WHEN OTHERS THEN
  				:NEW.JGZCBZ := NULL;
  		END; 
  		BEGIN
  			IF TO_NUMBER(V_JYJG00)-0.0001 > TO_NUMBER(:NEW.JJZXX0) THEN
  				:NEW.SFCJJZ := 'Y';
  			END IF;
  		EXCEPTION
  			WHEN OTHERS THEN
  				:NEW.SFCJJZ := 'N';
  		END;   		 		
  	END IF;  	
  END IF;
  
  if lsJY_JYJGTSTS<>0 then
     if nvl(:NEW.JGZCBZ,'阴性')='阴性' then
        :NEW.JGZCBZ:='阴性';
     elsif nvl(:NEW.JGZCBZ,'阴性')='弱阳性' then
        :NEW.JGZCBZ:='弱阳性';
     else 
        :NEW.JGZCBZ:='阳性';
     end if;
  end if;
  
  IF (V_JGLX00 = 'L') OR (V_JGLX00 = 'D') OR (V_JGLX00 = 'T') THEN
  	IF instr(TRIM('|'||:NEW.CKFW00)||'|','|'||:NEW.JYJG00||'|',1) =0 THEN
  		:NEW.JGZCBZ := V_SXTS00;
  	END IF;
  END IF;
    
EXCEPTION
	WHEN E_MODIFY THEN
		RAISE_APPLICATION_ERROR(-20005,'该标本非您排样，不能修改结果!*');
	WHEN OTHERS THEN
		RETURN;
END;
/


-- End of DDL Script for Trigger SD_HOSPITAL.TR_JY_JYJG00_TEMP_BINS