create or replace view vw_zs_hzsq00_zydzbl as
select  A.RYZDMC,A.SZYS00,A.ZZYS00,A.ZRYS00,A.SXYS00,A.ZYID00,A.BRID00,A.MRN000,A.MRN000 ZYH000,A.XM0000,A.XB0000,A.CSRQ00,
        A.BRZT00,A.RYCWH0,A.RYRQ00,A.RYSJ00,A.RYZD00,C.BLZT00,A.DQKS00 DQKSBH,C.YSZBH0,C.YKSBH0,C.YYSZBH,
        C.YSZMC0,C.THYY00,C.GDRQ00,C.GDSJ00 , B.YBMC00,A.RZKS00,D.BMMC00,A.CYDJRQ,
  SF_BQ_CSRQTONL(A.CSRQ00) BRNL00,    --病人年龄
  decode(substr(A.BRZT00,1,1),'2',
    to_char(greatest(to_date(to_char(sysdate,'YYYYMMDD'),'YYYYMMDD')-to_date(A.RYRQ00,'YYYYMMDD'),1))||'天',
    substr(A.SJCYRQ,1,4)||'-'||substr(A.SJCYRQ,5,2)||'-'||substr(A.SJCYRQ,7,2)
    ) AS RYTSMS, --住院天数描述
  (select ' '||X.SSMC00 from VW_SS_YW0000 X where X.ZYGHID(+)=A.ZYID00 and X.MZZYBZ='1' and X.SSZT00 not in ('1','3')
   and X.SSQSRQ=(select trim(max(Y.SSQSRQ)) from SS_YW0000 Y where Y.ZYGHID(+)=X.ZYGHID and Y.MZZYBZ='1'
   and Y.SSZT00 not in ('1','3')) and rownum=1) SSMC00, --手术名称
  (select decode(trim(max(X.SSQSRQ)),NULL,NULL,((to_date(to_char(sysdate,'YYYYMMDD'),'YYYYMMDD')-to_date(trim(max(X.SSQSRQ)),'YYYYMMDD'))))
   from SS_YW0000 X where X.ZYGHID(+)=A.ZYID00 and X.MZZYBZ='1' and X.SSZT00 NOT IN ('1','3')) SSTS00,  --手术天数
  (select Y.ZLXMJC from BM_ZLZD00 Y where Y.ZLXMID(+)=A.HLJBID) HLJB00,   --护理级别
  (select decode(substr(trim(Y.ZLXMJC),1,1),'一','Ⅰ','二','Ⅱ','三','Ⅲ','四','Ⅳ',substr(trim(Y.ZLXMJC),1,1))  from BM_ZLZD00 Y where Y.ZLXMID(+)=A.HLJBID) HLJBJC,   --护理级别简称
  (select ZDMC00 from VW_DZBL_BRZDXX_ONE where ZYID00=A.ZYID00 and rownum=1) ZDMC00,  --诊断名称
  A.DQBQ00,   --当前病区
  G.SQKS00 HZSQKS,    --申请科室
  G.SQYS00 HZSQYS,    --申请医生
  (select ZWXM00 from BM_YGBM00 where YGBH00(+)=G.SQYS00) SQYSXM,    --申请医生姓名
  to_char(to_date(nvl(G.TJRQ00,g.sqrq00),'YYYYMMDD'),'YYYY"年"MM"月"DD"日"') HZSQRQ,    --申请日期
  to_char(to_date(nvl(G.TJSJ00,g.sqsj00),'HH24:MI:SS'),'HH24"时"MI"分"') HZSQSJ,    --申请时间
  to_char(E.SYKSBH) HZKS00,    --会诊科室(多科室用";"隔开)
  decode(g.hzlx00,'3',nvl(to_char(e.syksbh),(select wm_concat(syksmc) from zs_ywhzdj where hzid00=g.id0000)),(select BMMC00 from BM_BMBM00 where BMBH00=E.SYKSBH)) HZKSMC,    --会诊科室名称
  to_char(E.SYYGBH) HZYS00,  --会诊医生(多医生用";"隔开)
  decode(g.hzlx00,'3',nvl(to_char(e.syygbh),(select wm_concat(syysxm) from zs_ywhzdj where hzid00=g.id0000)),(select ZWXM00 from BM_YGBM00 where YGBH00=E.SYYGBH)) HZYSXM,    --会诊医生姓名
  (select yslbmc from bm_yslb00 t,BM_YGBM00 j where t.yslb00=j.yslb00 and j.ygbh00=e.syygbh) SYYSLB,
  (select ZWXM00 from BM_YGBM00 where YGBH00=G.Sjshys) SJSHYS,    --上级审核医生
    E.ZT0000 HZJSBZ,    --会诊结束标志'1'结束 '0'未结束
    F.YSZID0,       --医生组ID
    F.SSKSBH,       --所属科室编号
    (select BMMC00 from bm_bmbm00 b where bmbh00=G.SQKS00) SSKSMC ,--F.SSKSMC,       --所属科室名称
    F.YSZZT0,        --医生组状态
    G.ID0000,         --申请ID
    nvl(E.Id0000,0) HZMXID,  --会诊明细ID
    decode((select count(1) from LJ_ZRZC00 where nvl(JSFS00,' ')=' ' and ZYID00=A.ZYID00),0,'0','1') SFLJBR,--是否路径病人1是0否
    G.HZLX00,---会诊类型
    G.SFJJHZ,--是否紧急会诊
    G.HZLXMC,---会诊类型名称
    G.HZYY00,---会诊医院
    G.HZYYMC,---会诊医院名称
    G.BRHZZY,---病人会诊病史
    G.HZMD00,---全院、院外会诊目的
    G.QYHZYJ,---全院会诊意见
    G.Sfwzbr,
    E.HZMD00 KJHZMD,---科间会诊目的
    E.HZYJ00 KJHZYJ,---科间会诊意见
    E.DZRQ00||' '||E.DZSJ00 DZRQSJ,--急会诊到场时间
    decode(g.hzlx00,'3',g.zt0000,E.Zt0000) Zt0000,--状态
    (select ZWXM00 from BM_YGBM00 where YGBH00=E.SYYGBH) SYYGMC,--会诊医生
    decode(length(e.JSRQ00),8,to_char(to_date(e.JSRQ00,'YYYYMMDD'),'YYYY"年"MM"月"DD"日"'),'')||decode(length(e.JSSJ00),8,to_char(to_date(e.JSSJ00,'HH24:MI:SS'),'HH24"时"MI"分"'),'') JSRQSJ,
    E.LX0000,
    (select IMG000 from ZS_YSTPQM where ygbh00 = e.syygbh) HZYSQM,
    (select IMG000 from ZS_YSTPQM where ygbh00 =g.sqys00) SQYSQM
from ZY_BRXXB0 A,ZS_BLXX01 C, IC_YBBRLB B  ,BM_BMBM00 D, ZS_YSZ000 F,ZS_HZSYMX E right join ZS_HZSQ00 G
on E.HZID00=G.ID0000
 where A.ZYID00=C.ZYID00 and A.ZYID00=G.ZYID00 and A.YBLB00=B.YBLB00 and C.YSZBH0=F.YSZID0 and A.FBBH00=B.FBBH00
   and A.RZKS00=D.BMBH00 and C.BLZT00 in ('1','2','3','5') and G.HZLX00<>'2'
union all
select  A.RYZDMC,A.SZYS00,A.ZZYS00,A.ZRYS00,A.SXYS00,A.ZYID00,A.BRID00,A.MRN000,A.MRN000 ZYH000,A.XM0000,A.XB0000,A.CSRQ00,
        A.BRZT00,A.RYCWH0,A.RYRQ00,A.RYSJ00,A.RYZD00,C.BLZT00,A.DQKS00 DQKSBH,C.YSZBH0,C.YKSBH0,C.YYSZBH,
        C.YSZMC0,C.THYY00,C.GDRQ00,C.GDSJ00 , B.YBMC00,A.RZKS00,D.BMMC00,A.CYDJRQ,
  SF_BQ_CSRQTONL(A.CSRQ00) BRNL00,    --病人年龄
  decode(substr(A.BRZT00,1,1),'2',
    to_char(greatest(to_date(to_char(sysdate,'YYYYMMDD'),'YYYYMMDD')-to_date(A.RYRQ00,'YYYYMMDD'),1))||'天',
    substr(A.SJCYRQ,1,4)||'-'||substr(A.SJCYRQ,5,2)||'-'||substr(A.SJCYRQ,7,2)
    ) AS RYTSMS, --住院天数描述
  (select ' '||X.SSMC00 from VW_SS_YW0000 X where X.ZYGHID(+)=A.ZYID00 and X.MZZYBZ='1' and X.SSZT00 not in ('1','3')
   and X.SSQSRQ=(select trim(max(Y.SSQSRQ)) from SS_YW0000 Y where Y.ZYGHID(+)=X.ZYGHID and Y.MZZYBZ='1'
   and Y.SSZT00 not in ('1','3')) and rownum=1) SSMC00, --手术名称
  (select decode(trim(max(X.SSQSRQ)),NULL,NULL,((to_date(to_char(sysdate,'YYYYMMDD'),'YYYYMMDD')-to_date(trim(max(X.SSQSRQ)),'YYYYMMDD'))))
   from SS_YW0000 X where X.ZYGHID(+)=A.ZYID00 and X.MZZYBZ='1' and X.SSZT00 NOT IN ('1','3')) SSTS00,  --手术天数
  (select Y.ZLXMJC from BM_ZLZD00 Y where Y.ZLXMID(+)=A.HLJBID) HLJB00,   --护理级别
  (select decode(substr(trim(Y.ZLXMJC),1,1),'一','Ⅰ','二','Ⅱ','三','Ⅲ','四','Ⅳ',substr(trim(Y.ZLXMJC),1,1))  from BM_ZLZD00 Y where Y.ZLXMID(+)=A.HLJBID) HLJBJC,   --护理级别简称
  (select ZDMC00 from VW_DZBL_BRZDXX_ONE where ZYID00=A.ZYID00 and rownum=1) ZDMC00,  --诊断名称
  A.DQBQ00,   --当前病区
  G.SQKS00 HZSQKS,    --申请科室
  G.SQYS00 HZSQYS,    --申请医生
  (select ZWXM00 from BM_YGBM00 where YGBH00(+)=G.SQYS00) SQYSXM,    --申请医生姓名
  to_char(to_date(G.TJRQ00,'YYYYMMDD'),'YYYY"年"MM"月"DD"日"') HZSQRQ,    --申请日期
  to_char(to_date(G.TJSJ00,'HH24:MI:SS'),'HH24"时"MI"分"') HZSQSJ,    --申请时间
  '' HZKS00,    --会诊科室(多科室用";"隔开)
  G.HZKSLB HZKSMC,    --会诊科室名称
  '' HZYS00,  --会诊医生(多医生用";"隔开)
   G.HZYSLB HZYSXM,    --会诊医生姓名
    '' SYYSLB,
   (select ZWXM00 from BM_YGBM00 where YGBH00=G.Sjshys) SJSHYS,    --上级审核医生

   (select ZT0000 from ZS_HZSYMX where HZID00=G.ID0000 and rownum=1) HZJSBZ,    --会诊结束标志'1'结束 '0'未结束
    F.YSZID0,       --医生组ID
    F.SSKSBH,       --所属科室编号
    F.SSKSMC,       --所属科室名称
    F.YSZZT0,        --医生组状态
    G.ID0000,         --申请ID
    0 HZMXID,  --会诊明细ID
    decode((select count(1) from LJ_ZRZC00 where nvl(JSFS00,' ')=' ' and ZYID00=A.ZYID00),0,'0','1') SFLJBR,--是否路径病人1是0否
    G.HZLX00,---会诊类型
    G.SFJJHZ,--是否紧急会诊
    G.HZLXMC,---会诊类型名称
    G.HZYY00,---会诊医院
    G.HZYYMC,---会诊医院名称
    G.BRHZZY,---病人会诊病史
    G.HZMD00,---全院、院外会诊目的
    G.QYHZYJ,---全院会诊意见
    G.Sfwzbr,
    (select HZMD00 from ZS_HZSYMX where HZID00=G.ID0000 and rownum=1) KJHZMD,---科间会诊目的
    (select HZYJ00 from ZS_HZSYMX where HZID00=G.ID0000 and rownum=1) KJHZYJ,---科间会诊意见
    (select DZRQ00||' 'DZSJ00 from ZS_HZSYMX where HZID00=G.ID0000 and rownum=1) DZRQSJ,--急会诊到场时间
    (select ZT0000 from ZS_HZSYMX where HZID00=G.ID0000 and rownum=1),--状态
    G.HZYSLB SYYGMC,--会诊医生
    (select decode(length(JSRQ00),8,to_char(to_date(JSRQ00,'YYYYMMDD'),'YYYY"年"MM"月"DD"日"'),'')||decode(length(JSSJ00),8,to_char(to_date(JSSJ00,'HH24:MI:SS'),'HH24"时"MI"分"'),'') from ZS_HZSYMX where HZID00=G.ID0000 and rownum=1) JSRQSJ,--结束日期时间
    (select LX0000 from ZS_HZSYMX where HZID00=G.ID0000 and rownum=1) LX0000,--类型
    (select IMG000 from ZS_YSTPQM t1,ZS_HZSYMX t2 where t2.HZID00=G.ID0000 and t1.ygbh00=t2.syygbh and rownum=1)  HZYSQM,
    (select IMG000 from ZS_YSTPQM  where ygbh00 =g.sqys00) SQYSQM
from ZY_BRXXB0 A,ZS_BLXX01 C, IC_YBBRLB B  ,BM_BMBM00 D, ZS_YSZ000 F, ZS_HZSQ00 G
 where A.ZYID00=C.ZYID00 and A.ZYID00=G.ZYID00 and A.YBLB00=B.YBLB00 and C.YSZBH0=F.YSZID0 and A.FBBH00=B.FBBH00 and A.ZYID00=G.ZYID00
   and A.RZKS00=D.BMBH00 and C.BLZT00 in ('1','2','3','5') and G.HZLX00='2';