-- Start of DDL Script for View SD_HOSPITAL.VW_ZLDJHISJK
-- Generated 2020-12-03 10:32:59 from SD_HOSPITAL@fw2

CREATE OR REPLACE VIEW vw_zldjhisjk (
   name,
   sex,
   birthda,
   indno,
   othername,
   otherno,
   telephone,
   contactperson,
   relation,
   contactphone,
   occu,
   trib,
   mari,
   workcom,
   address,
   conservation,
   topo,
   morp,
   icd10,
   beha,
   grad,
   stage,
   t,
   n,
   m,
   basi,
   diagnosisresult,
   detaileddiagnosisresult,
   inciden,
   treainfo,
   hosp,
   caseno,
   dlc,
   state,
   deathda,
   caus,
   deadplace,
   derepdoct,
   reportd,
   repdoct,
   remarkext,
   cyrq00,
   jtdzsf,
   lsh000,
   cykb00 )
AS
select BA.BRXM00 Name, --姓名
case
when instr(BA.BRXB00,'男')>0 then '1'
when instr(BA.BRXB00,'女')>0 then '2'
else '9' end Sex,--性别
substr(BA.BRCSRQ,1,4)||'-'||substr(BA.BRCSRQ,5,2)||'-'||substr(BA.BRCSRQ,7,2) Birthda,--出生日期
--BA.BRCSRQ
BA.SFZH00 Indno,--身份证号
'' OtherName,-- 其他证件类型
'' OtherNo,--证件号
nvl(BA.JTDH00,BA.LXRDH0) Telephone ,
BA.LXRXM0 ContactPerson, --联系人
BA.LXRGX0 Relation,--关系
BA.LXRDH0 ContactPhone,
BA.ZY0000 Occu,--职业
nvl(MZ.DM0000,'99') Trib,--民族
decode(BA.HYZK00,'未婚','1','已婚','2','丧偶','3','离婚','4','不详',9,9) Mari,
BA.GZDW00 WorkCom,
BA.Jtdzsf || BA.JTDZDQ || BA.JTDZXS || BA.JTDZ01 Address,
(BA.XZZSF0||BA.XZZDQ0||BA.XZZXS0||BA.XZZXC0) Conservation,--常住地址
'' Topo,
nvl((select substr(ICD100,2,instr(ICD100,'/')-3) from XT_ZLXTM0 B,bq_baszdb c where trim(b.icd100)=trim(c.fzbm00) and c.zyid00=lsh000 and c.zdlb00='3' and rownum=1),' ') Morp,
--病理类型,只需要4位
(select (case
when  instr(ZDM000,'.x')>0 then substr(ZDM000,0, instr(ZDM000, '.') -1)
when instr(ZDM000,'.')>0 then substr(ZDM000,0, instr(ZDM000, '.') +1)
else ZDM000 end)
 from bq_baszdb B  where LSH000=B.ZYID00 and ZDLB00 IN ('3')  and rownum=1) ICD10,
'' Beha,--行为
'' Grad,--分级
'' Stage,--分期
'' T,
'' N,
'' M,
(select ZLZDYJ from BA_BRZYFY H where H.ZYID00=LSH000 and rownum=1)  Basi,--诊断依据
(select FWMC00||BWMC00||ZDMC00||' '||FZMC00||'' from BQ_BASZDB B where LSH000=B.ZYID00   and ZDLB00 in ('3') and rownum=1 ) DiagnosisResult,--诊断结果
 BA.BLZD00 DetailedDiagnosisResult,
--(select JBMC00 from XT_ZLXTM0 B,BQ_BASZDB C where trim(b.icd100)=trim(c.fzbm00) and C.ZYID00=LSH000 and C.ZDLB00='3' and rownum=1) DetailedDiagnosisResult,--详细诊断结果
--(select substr(ZDRQ00,1,4)||'-'||substr(ZDRQ00,5,2)||'-'||substr(ZDRQ00,7,2) from BQ_BASZDB B where LSH000=B.ZYID00 and ZDLB00 in ('3') and rownum=1) Inciden,--诊断日期
substr(ba.ryrq00,1,4)||'-'||substr(ba.ryrq00,5,2)||'-'||substr(ba.ryrq00,7,2) Inciden,
'' TreaInfo,--治疗信息
'' Hosp,--门诊号
BA.ZYH000 Caseno,--住院号
'' Dlc,--最后接触日期
'' State,--最后接触状态
'' Deathda,--死亡日期
'' Caus,--死因编号
'' DeadPlace,--死亡地点
'' DeRepDoct,--死亡报告医师
--to_char(sysdate,'yyyy-mm-dd') Reportd,--报告日期
substr(ba.cyrq00,1,4)||'-'||substr(ba.cyrq00,5,2)||'-'||substr(ba.cyrq00,7,2) Reportd,
BA.ZYYSMC RepDoct, --报告医师
'' RemarkExt,
BA.CYRQ00,
BA.JTDZSF,
BA.LSH000,
BA.CYKB00
from BA_BRZYXX BA
left join XT_MZ0000 MZ on BA.MZ0000=MZ.MC0000
left join BQ_BRZDXX ZD on BA.LSH000=ZD.ZYID00
left join (select * from XT_XTCZRZ where CZNR00 = '2' and CZRQ00 is not null and CZTABLE = 'BA_BRZYXX' and rownum='1') RZ on BA.LSH000=RZ.DATAID
where ZD.ZDLB00='3'
--and  RZ.CZTABLE = 'BA_BRZYXX' and RZ.CZNR00 = '2' and RZ.CZRQ00 is not null
/


-- End of DDL Script for View SD_HOSPITAL.VW_ZLDJHISJK