--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
CREATE OR REPLACE TRIGGER TR_JY_YW0000_AINS_HTDZBL
 AFTER 
 INSERT OR UPDATE
 ON SD_JY.JY_YW0000
 REFERENCING OLD AS OLD NEW AS NEW
 FOR EACH ROW 
declare

-- MODIFICATION HISTORY
-- Person      Date        Comments
-- 猪头      2012.02.21     created;
---------------------------------------------------------   
begin
if(:new.mzzybz='1') then 
	if (  (trim(:new.jgzt00)='6') or(trim(:new.jgzt00)='8') or(trim(:new.jgzt00)='9')) then
    insert into LabReportInfo_ZT (LAB_FLOW,ZT0000,IPID)
     select
         :new.jyywid,
         '0',
         :new.ZYGHID
         from dual where  not exists(select 1 from LabReportInfo_ZT where :new.jyywid=LAB_FLOW);
else
if(trim(:new.jgzt00)='3') then
        DELETE FROM  LabReportInfo_ZT  where :new.jyywid=LAB_FLOW  ;
    end if;
  end if ;
  end if ;
  end;
/

--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%



CREATE OR REPLACE TRIGGER TR_JY_JYJG00_AINS_HTDZBL
 AFTER 
 UPDATE
 ON SD_JY.JY_JYJG00
 REFERENCING OLD AS OLD NEW AS NEW
 FOR EACH ROW 
declare

-- MODIFICATION HISTORY
-- Person      Date        Comments
-- 猪头      2012.02.21     created;
---------------------------------------------------------   
begin
if(:new.jyjg00<>:old.jyjg00) then 
	    update  LabReportInfo_ZT set ZT0000='0' where :new.jyywid=LAB_FLOW ;
    

  end if ;
  end;
/

--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

-- Start of DDL Script for Table SD_HOSPITAL.LABREPORTINFO_ZT
-- Generated 4-五月-2015 16:28:53 from SD_HOSPITAL@lnyy

CREATE TABLE labreportinfo_zt
    (lab_flow                       NUMBER(10,0) NOT NULL,
    zt0000                         CHAR(1) DEFAULT '0' NOT NULL,
    czrq00                         VARCHAR2(8) DEFAULT TO_CHAR(SYSDATE,'YYYYMMDD') NOT NULL,
    czsj00                         VARCHAR2(8) DEFAULT TO_CHAR(SYSDATE,'HH24:MI:SS') NOT NULL,
    ipid                           NUMBER(10,0))
  PCTFREE     10
  INITRANS    1
  MAXTRANS    255
  TABLESPACE  ts_yy
  STORAGE   (
    INITIAL     65536
    MINEXTENTS  1
    MAXEXTENTS  2147483645
  )
  NOCACHE
  MONITORING
/

-- Grants for Table
GRANT INSERT ON labreportinfo_zt TO sd_dzbljk
/
GRANT SELECT ON labreportinfo_zt TO sd_dzbljk
/
GRANT UPDATE ON labreportinfo_zt TO sd_dzbljk
/

--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%


-- Indexes for LABREPORTINFO_ZT

CREATE INDEX index_labreportinfo_lab_flow ON labreportinfo_zt
  (
    lab_flow                        ASC
  )
  PCTFREE     10
  INITRANS    2
  MAXTRANS    255
  TABLESPACE  ts_tj_index
  STORAGE   (
    INITIAL     65536
    MINEXTENTS  1
    MAXEXTENTS  2147483645
  )
/



-- End of DDL Script for Table SD_HOSPITAL.LABREPORTINFO_ZT

--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%


CREATE OR REPLACE VIEW SD_HOSPITAL.LABREPORTITEMINFO
 (
 LAB_ITEM_RCD_FLOW, LAB_FLOW, PID, IPID, LAB_REPITEM_CODE, LAB_REPITEM_NAME, 
LAB_REPITEM_OTHER_NAME, RESULT_TYPE, RESULT_NUM, RESULT_TEXT, LOWER_LIMIT1, 
UPPER_LIMIT1, REFERENCE_TEXT, LAB_REPITEM_UNIT_CODE, LAB_REPITEM_UNIT, 
DANGER_FLAG, CHANGE_FLAG, APPRAISAL_RESULT, DYSX00

 )
 AS 
select 
  to_char(A.jgid00) LAB_ITEM_RCD_FLOW ,
  A.jyywid LAB_FLOW,
  (select to_char(BRID00) from jy_yw0000 where jyywid=A.jyywid and rownum=1) PID  ,
  (select to_char(ZYGHID) from jy_yw0000 where jyywid=A.jyywid and rownum=1) IPID ,
  
  -- (select to_char(decode(A.brid00,'4130816','4130802',A.brid00)) from jy_yw0000 where jyywid=A.jyywid and rownum=1) PID  ,
  --(select to_char(decode(A.zyghid,'751909','751905',A.zyghid) from jy_yw0000 where jyywid=A.jyywid and rownum=1) IPID ,
  to_char(a.bgxmid) LAB_REPITEM_CODE  ,
  (select ZWMC00 from jy_bgxmzd where bgxmid=A.bgxmid and rownum=1) LAB_REPITEM_NAME ,
  ' ' LAB_REPITEM_OTHER_NAME ,
  SF_HT_JYJGTYPE(a.JYJG00,a.BGXMID)  RESULT_TYPE ,
  --(select decode(jglx00,'N','N','T') from jy_bgxmzd where bgxmid=A.bgxmid and rownum=1) RESULT_TYPE ,
--  decode((select decode(jglx00,'N','N','T') from jy_bgxmzd where bgxmid=A.bgxmid),'N',to_number(A.jyjg00),NULL) RESULT_NUM ,  --发现JGLX00=N的值有带>的结构(A.JYJG00)
  --decode((select decode(jglx00,'N','N','T') from jy_bgxmzd where bgxmid=A.bgxmid),'N',A.jyjg00,NULL) RESULT_NUM ,
  decode(SF_HT_JYJGTYPE(a.JYJG00,a.BGXMID),'N',a.JYJG00,null) RESULT_NUM ,
  A.jyjg00 RESULT_TEXT    ,
  A.CKXX00 LOWER_LIMIT1   ,
  A.CKSX00 UPPER_LIMIT1   ,
  A.CKFW00 REFERENCE_TEXT ,
  '' LAB_REPITEM_UNIT_CODE,
  (select dw0000 from jy_bgxmzd where bgxmid=A.bgxmid and rownum=1) LAB_REPITEM_UNIT,
  A.sfcjjz DANGER_FLAG    ,
  A.jgzcbz CHANGE_FLAG    ,
  '' APPRAISAL_RESULT  ,
  B.DYSX00
  from jy_jyjg00 A,JY_BGXMZD B where a.bgxmid=b.bgxmid and  exists(select 1 from jy_bgxmzd where bgxmid=a.bgxmid)
  union all
  select
  distinct
  PYRQ00||YBH000||to_char(XJID00) LAB_ITEM_RCD_FLOW ,
  A.jyywid LAB_FLOW ,
  (select to_char(BRID00) from jy_yw0000 where jyywid=A.jyywid and rownum=1) PID  ,
  (select to_char(ZYGHID) from jy_yw0000 where jyywid=A.jyywid and rownum=1) IPID ,
  to_char(a.XJID00) LAB_REPITEM_CODE  ,
  (select ZWMC00 from JY_XJZD00 where XJID00=A.XJID00 and rownum=1) LAB_REPITEM_NAME ,
   ' ' LAB_REPITEM_OTHER_NAME ,
  'T'  RESULT_TYPE , 
  null RESULT_NUM,
  a.XJJG00 RESULT_TEXT,
  '' LOWER_LIMIT1,
  '' UPPER_LIMIT1,
  '' REFERENCE_TEXT,
  '' LAB_REPITEM_UNIT_CODE,
  '' LAB_REPITEM_UNIT,
  'N' DANGER_FLAG,
  '' CHANGE_FLAG,
  decode(a.XJJG00,'',decode((select count(*)  from jy_yw0000 where a.pyrq00=pyrq00 
       and a.ybh000=ybh000 and nvl(A.KSSID0,'0')<>'0'),'0','阴性','阳性'),a.XJJG00) APPRAISAL_RESULT,
  null
  from 
  JY_XJBGJG A  where exists (select 1 from jy_yw0000 where a.pyrq00=pyrq00 and a.ybh000=ybh000 and jgzt00 in ('6','8','9'))
/

--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

CREATE OR REPLACE VIEW SD_HOSPITAL.LABREPORTINFO1
 (
 LAB_FLOW, PID, IPID, INOUT_FLAG, LAB_REP_CODE, LAB_REP_NAME, LAB_APPLY_TIME, 
LAB_APPLIER_CODE, LAB_APPLIER_NAME, SAMPLE_SUBMIT_TIME, SAMPLE_SUBMITER_CODE, 
SAMPLE_SUBMITER_NAME, APPLIER_DEPT_CODE, APPLIER_DEPT, SAMPLE_CODE, SAMPLE, 
IS_URGENT, LAB_STATUS, LAB_TIME, LAB_RECORDER_ID, LAB_RECORDER, AUDIT_USER_ID, 
AUDIT_USER_NAME, LAB_INSTRUMENT, LAB_DEPT_CODE, LAB_DEPT_NAME, SAMPLE_RECV_TIME, 
SAMPLE_RECEIVER_ID, SAMPLE_RECEIVER, AGE, BED_NO, IS_BACILLI, DANGER_DESCRIBE, 
LAB_OTHER_FLAG, REMARK, IMPFLAG, RETURNDESC

 )
 AS 
select to_char(A.jyywid) LAB_FLOW ,
       to_char(A.brid00) PID ,
        -- to_char(A.zyghid) IPID ,
    (select b.zyid00||'_'||b.brrycs  from zy_brxxb0  b where b.zyid00=a.zyghid) IPID,
      -- to_char(decode(A.brid00,'4130816','4130802',A.brid00)) PID ,
       --to_char(decode(A.zyghid,'751909','751905',A.zyghid)) IPID ,
       decode(A.mzzybz,'0','o','1','i') INOUT_FLAG ,
       A.yqdh00 LAB_REP_CODE      ,
       --A.ybh000 LAB_REP_NAME      ,
--       (select YQJC00 from jy_jyyqzd where YQDH00=A.YQDH00) LAB_REP_NAME,
    --   (select trim(YYJC00) from XT_YYXX00 where ROWNUM=1)||(select YQSM00||'检验报告单'  from jy_jyyqzd where YQDH00=A.YQDH00) LAB_REP_NAME,
      ( select k.ZLXMJC from  bm_zlzd00 k where   a.ZLXMID=k.ZLXMID)LAB_REP_NAME,
       A.sqrq00||substr(A.sqsj00,1,2)||substr(A.sqsj00,4,2)||substr(A.sqsj00,7,2) LAB_APPLY_TIME ,
       to_char(A.sqys00) LAB_APPLIER_CODE  ,
       (select ZWXM00 from BM_YGBM00 where A.sqys00=ygbh00 and rownum=1) LAB_APPLIER_NAME        ,
       A.BBCYRQ||substr(A.BBCYSJ,1,2)||substr(A.BBCYSJ,4,2)||substr(A.BBCYSJ,7,2) SAMPLE_SUBMIT_TIME ,
       to_char(A.BBCJR0) SAMPLE_SUBMITER_CODE ,
       (select ZWXM00 from BM_YGBM00 where A.BBCJR0=ygbh00 and rownum=1) SAMPLE_SUBMITER_NAME ,
       to_char(A.sqks00) APPLIER_DEPT_CODE    ,
       (select BMMC00 from bm_bmbm00 where bmbh00=a.sqks00 and rownum=1) APPLIER_DEPT         ,
       to_char(A.BBID00) SAMPLE_CODE          ,
       (select BBMC00 from bm_BBZD00 where bbid00=A.bbid00 and rownum=1) SAMPLE               ,
       A.JJBZ00 IS_URGENT         ,
       decode(A.jgzt00,'8','Y','6','Y','9','Y','N') LAB_STATUS ,
--       PYRQ00||substr(A.PYSJ00,1,2)||substr(A.PYSJ00,4,2)||substr(A.PYSJ00,7,2) LAB_TIME      ,
       A.CBGRQ0||substr(A.CBGSJ0,1,2)||substr(A.CBGSJ0,4,2)||substr(A.CBGSJ0,7,2) LAB_TIME      ,
       to_char(A.CZY000) LAB_RECORDER_ID  ,
       (select ZWXM00 from BM_YGBM00 where A.CZY000=ygbh00 and rownum=1) LAB_RECORDER         ,
       to_char(BGJYR0) AUDIT_USER_ID      ,
       (select ZWXM00 from BM_YGBM00 where A.BGJYR0=ygbh00 and rownum=1) AUDIT_USER_NAME      ,
       (select yqjc00 from jy_jyyqzd where A.yqdh00=yqdh00 and rownum=1) LAB_INSTRUMENT       ,
       (SELECT to_char(YJKSBH) FROM YJ_YW0000 WHERE YJDJH0=A.YJDJH0 and rownum=1) LAB_DEPT_CODE,
       (SELECT c.bmmc00 FROM YJ_YW0000 b,BM_BMBM00 c WHERE b.yjksbh=c.bmbh00 and  b.YJDJH0=A.YJDJH0 and rownum=1) LAB_DEPT_NAME,
       A.BBSDRQ||substr(A.BBSDSJ,1,2)||substr(A.BBSDSJ,4,2)||substr(A.BBSDSJ,7,2) SAMPLE_RECV_TIME,
       to_char(A.BBSDR0) SAMPLE_RECEIVER_ID  ,
       (select ZWXM00 from BM_YGBM00 where A.BBSDR0=ygbh00 and rownum=1) SAMPLE_RECEIVER       ,
       A.NL0000||decode(A.NLDW00,'Y','岁','M','月','D','天') AGE ,
       A.CH0000 BED_NO     , 
      -- 'N' IS_BACILLI      ,
       (select decode(count(*),'0','N','Y') from jy_jyyqzd where yqmc00 like ('BICLIAISON%') and yqdh00 =a.yqdh00) IS_BACILLI,
       ' ' DANGER_DESCRIBE ,
       ' ' LAB_OTHER_FLAG  ,
       ' ' REMARK1         ,
       ' ' ImpFlag         ,
       ' ' ReturnDesc   
       from jy_yw0000 A,LabReportInfo_ZT B
       where A.jgzt00 in ('6','8','9') and A.mzzybz='1' and b.zt0000='0' and a.jyywid=b.lab_flow
/


--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%