-- Start of DDL Script for View SD_HOSPITAL.VW_BA_XNXGJCBB
-- Generated 12-三月-2025 16:01:09 from SD_HOSPITAL@YLZHIS_NEW

CREATE OR REPLACE VIEW vw_ba_xnxgjcbb (
   lsh000,
   bgkdws,
   bgkdws1,
   bgkdwq,
   mjzh00,
   zyh000,
   brxm00,
   zjlx00,
   sfzh00,
   qtjzh0,
   brxb00,
   brcsrq,
   mz0000,
   zy0000,
   hyzk00,
   jtdh00,
   gzdw00,
   lxrxm0,
   lxrdh0,
   jgsf00,
   jgxs00,
   jtdzxs,
   jtdz01,
   jtdz02,
   jtdz03,
   xzzsf0,
   xzzdq0,
   xzzxc0,
   xzzjd0,
   xzzcwh,
   xzzdzh,
   lxjzly,
   fbrq00,
   sfscfb,
   zdrq00,
   zd0000,
   xjtzl0,
   nczfl0,
   xzcsyj,
   xzcslx,
   icd10,
   zdyj00,
   lczztz,
   shbzw0,
   xdt000,
   xgzy00,
   csxdt0,
   ctctas,
   mri000,
   yc0000,
   ss0000,
   sjhbl0,
   bflx00,
   bflxsm,
   qtshzb,
   cyjlxj,
   zddw00,
   zddwjb,
   zg000,
   swsj00,
   gbsyyy,
   gbsybm,
   bgdw00,
   bgks00,
   bgys00,
   bgrq00 )
AS
select A.LSH000,
       '福建省' BGKDWS,--报卡单位省
       '福州市' BGKDWS1,
       '台江区' BGKDWQ,
       '' MJZH00,
       A.ZYH000 ,
       A.BRXM00 ,
       a.brzjlx  ZJLX00,
      decode(a.brzjlx,'身份证',A.SFZH00,'') SFZH00 ,
      decode(a.brzjlx,'身份证','',A.SFZH00) QTJZH0,
       A.BRXB00,
       A.BRCSRQ ,
       A.MZ0000,
       A.ZY0000,
       decode(A.HYZK00,'已婚','已婚/再婚/同居','再婚','已婚/再婚/同居','初婚','已婚/再婚/同居','复婚','已婚/再婚/同居',A.HYZK00) HYZK00,
       A.JTDH00,--本人电话
       A.GZDW00,
       A.LXRXM0,
       A.LXRDH0,
       A.JGSF00,--户籍地址省
       A.JGXS00,
       A.JTDZXS,
       A.JTDZ01,--户籍地址街道（乡）
       '' JTDZ02,--户籍地址居委会（村）
       '' JTDZ03,--户籍地址号
       A.XZZSF0,--现住地址省
       A.XZZDQ0,
       A.XZZXC0,
       '' XZZJD0,--现住地址街道（乡）
       '' XZZCWH,--现住地址居委会（村）
       '' XZZDZH,--现住地址号
        '有' LXJZLY,--在本辖区连续居住6个月以上
       A.RYRQ00 FBRQ00,--发病日期
       (case when (select count(BAH000) as SFSCFB  from BA_BRZYXX where ZYH000 = A.ZYH000) > 1 then '否' else '是' end) SFSCFB,--是否首次发病
       A.RYRQ00 ZDRQ00,--诊断日期
       decode(substr(ZDM000,0,2),'I6','脑卒中','I2','急性心肌梗死','I4','心源性猝死','') ZD0000,--诊断
       '' XJTZL0,--心绞痛治疗措施
       decode(substr(ZDM000,0,3),'I60','蛛网膜下腔出血','I61','脑出血','I63','脑梗死','I64','难分类的完全性卒中','') NCZFL0,--脑卒中分类
       '' XZCSYJ,--心脏性猝死依据
       '' XZCSLX,--心脏性猝死推断类型
       B.ZDM000 ICD10,--门诊患者取门诊主诊断icd10编码，住院患者取出院主诊断icd10编码
 /*插入15列*/
       (select wm_concat ( distinct
case
  when ZLXMJC like '%肌钙蛋白%' then '肌钙蛋白'
  when ZLXMJC like '%心电图%' then '心电图'
  when ZLXMJC like '%CT%' then 'CT/CTA/SPECT'
  when ZLXMJC like '%磁共振%' then 'MRI'
  when ZLXMJC like '%超声心动图%' then '超声心动图'
  else '' end
) from YJ_YW0000 YJ where YJ.ZYGHID=C.ZYID00 and (ZLXMJC like '%CT%' or ZLXMJC like '%心电图%' 
or ZLXMJC like '%肌钙蛋白%' or ZLXMJC like '%磁共振%' or ZLXMJC like '%超声心动图%' )) ZDYJ00,--诊断依据
       (select ZS0000 from ZS_BLWSMX tp where tp.zyid00 = a.lsh000 and ZS0000 is not null and rownum<=1) LCZZTZ,--（病史说明）临床症状体征
/*     (select listagg(JCSJNR,'|') within group (order by  JCSJNR) from YJ_JCBG00_DZBL JY where JY.YJDJH0 in ( select YJDJH0 from YJ_YW0000 YJ where YJ.ZYGHID=C.ZYID00 and ZLXMJC like '%肌钙蛋白%' )) SHBZW0,--检查结果（生化标志物）
     (select listagg(JCSJNR,'|') within group (order by  JCSJNR) from YJ_JCBG00_DZBL JY where JY.YJDJH0 in ( select YJDJH0 from YJ_YW0000 YJ where YJ.ZYGHID=C.ZYID00 and ZLXMJC like '%心电图%'  )) XDT000,--
     '' XGZY00,--
     '' CSXDT0,--
     (select listagg(JCSJNR,'|') within group (order by  JCSJNR) from YJ_JCBG00_DZBL JY where JY.YJDJH0 in ( select YJDJH0 from YJ_YW0000 YJ where YJ.ZYGHID=C.ZYID00 and ZLXMJC like '%CT%'  )) CTCTAS,--
     (select listagg(JCSJNR,'|') within group (order by  JCSJNR) from YJ_JCBG00_DZBL JY where JY.YJDJH0 in ( select YJDJH0 from YJ_YW0000 YJ where YJ.ZYGHID=C.ZYID00 and ZLXMJC like '%磁共振%'  )) MRI000,--*/
     SF_BA_getJCSJNR(C.ZYID00,'肌钙蛋白') SHBZW0,--检查结果（生化标志物）
      SF_BA_getJCSJNR(C.ZYID00,'心电图') XDT000,--
       '' XGZY00,--
       '' CSXDT0,--
      SF_BA_getJCSJNR(C.ZYID00,'CT') CTCTAS,--
      SF_BA_getJCSJNR(C.ZYID00,'磁共振') MRI000,--
       '' YC0000,--
       '' SS0000,--
       '' SJHBL0,--
       '' BFLX00,--
       '' BFLXSM,--
       '' QTSHZB,--其他生化指标
       '' CYJLXJ,--出院记录（小结）

       /*插入15列*/
      '福建省人民医院'   ZDDW00,--诊断单位
       '三级' ZDDWJB,--诊断单位级别
       decode(A.LYFS00, '死亡', '死亡', '存活') ZG000,--转归
       decode(A.LYFS00, '死亡', A.CYRQ00, '') SWSJ00,--死亡时间
       '' GBSYYY,--根本死因
       decode(A.LYFS00, '死亡', b.ZDM000, '') GBSYBM,--根本死因编码 ICD10
       '福建省人民医院' BGDW00,--报告单位
       A.CYKBMC BGKS00,--报告科室
       A.ZYYSMC BGYS00,--报告医师
       A.CYRQ00 BGRQ00--报告日期
  from BA_BRZYXX A, BQ_BRZDXX B, ZY_BRXXB0 C
 where C.ZYID00 = B.ZYID00(+)
   and C.ZYID00(+) = A.LSH000
  -- and A.CYRQ00 between '20211026' and '20211130'
   and ZDLB00 = '3'
   and substr(ZDM000, 0, 3) in  ('I21', 'I22', 'I60', 'I61', 'I63', 'I64', 'I46.1')
union all
--住院（心绞痛）
select A.LSH000,
       '福建省' BGKDWS,--报卡单位省
       '福州市' BGKDWS1,
       '台江区' BGKDWQ,
       '' MJZH00,
       A.ZYH000 ,
       A.BRXM00 ,
      a.brzjlx  ZJLX00,
      decode(a.brzjlx,'身份证',A.SFZH00,'') SFZH00 ,
      decode(a.brzjlx,'身份证','',A.SFZH00) QTJZH0,
       A.BRXB00,
       A.BRCSRQ ,
       A.MZ0000,
       A.ZY0000,
       decode(A.HYZK00,'已婚','已婚/再婚/同居','再婚','已婚/再婚/同居','初婚','已婚/再婚/同居','复婚','已婚/再婚/同居',A.HYZK00) HYZK00,
       A.JTDH00,
       A.GZDW00,
       A.LXRXM0,
       A.LXRDH0,
       A.JGSF00,--户籍地址省
       A.JGXS00,
       A.JTDZXS,
       A.JTDZ01,--户籍地址街道（乡）
       '' JTDZ02,--户籍地址居委会（村）
       '' JTDZ03,--户籍地址号
       A.XZZSF0,--现住地址省
       A.XZZDQ0,
       A.XZZXC0,
       '' XZZJD0,--现住地址街道（乡）
       '' XZZCWH,--现住地址居委会（村）
       '' XZZDZH,--现住地址号
       '有' LXJZLY,--在本辖区连续居住6个月以上
       A.RYRQ00 FBRQ00,--发病日期
       (case when (select count(BAH000) as SFSCFB  from BA_BRZYXX where ZYH000 = A.ZYH000) > 1 then '否' else '是' end) SFSCFB,--是否首次发病
       A.RYRQ00 ZDRQ00,--诊断日期
       '心绞痛' ZD0000,--诊断
      decode(substr(SSM000,0,5),'36.06','支架植入','36.07','支架植入','00.66','PTCA',decode(substr(SSM000,0,4),'36.1','CABG','')) XJTZL0,--心绞痛治疗措施
       '' NCZFL0,--脑卒中分类
       '' XZCSYJ,--心脏性猝死依据
       '' XZCSLX,--心脏性猝死推断类型
       b.ZDM000 ICD10,--门诊患者取门诊主诊断icd10编码，住院患者取出院主诊断icd10编码

       /*插入15列*/
       (select wm_concat ( distinct
case
  when ZLXMJC like '%肌钙蛋白%' then '肌钙蛋白'
  when ZLXMJC like '%心电图%' then '心电图'
  when ZLXMJC like '%CT%' then 'CT/CTA/SPECT'
  when ZLXMJC like '%磁共振%' then 'MRI'
  when ZLXMJC like '%超声心动图%' then '超声心动图'
  else '' end
) from YJ_YW0000 YJ where YJ.ZYGHID=C.ZYID00 and (ZLXMJC like '%CT%' or ZLXMJC like '%心电图%' 
or ZLXMJC like '%肌钙蛋白%' or ZLXMJC like '%磁共振%' or ZLXMJC like '%超声心动图%' ))||
case when substr(SSM000,0,5) in ('36.06','36.07','00.66','39.74','88.41','88.42','00.61','00.62','00.63','00.64','00.65') then ',手术'
     when substr(SSM000,0,4) in ('36.1') then ',手术' else '' end ZDYJ00,--诊断依据
       (select ZS0000 from ZS_BLWSMX tp where tp.zyid00 = a.lsh000 and ZS0000 is not null and rownum<=1) LCZZTZ,--（病史说明）临床症状体征
      SF_BA_getJCSJNR(C.ZYID00,'肌钙蛋白') SHBZW0,--检查结果（生化标志物）
      SF_BA_getJCSJNR(C.ZYID00,'心电图') XDT000,--
       '' XGZY00,--
       '' CSXDT0,--
      SF_BA_getJCSJNR(C.ZYID00,'CT') CTCTAS,--
      SF_BA_getJCSJNR(C.ZYID00,'磁共振') MRI000,--
       '' YC0000,--
       '' SS0000,--
       '' SJHBL0,--
       '' BFLX00,--
       '' BFLXSM,--
       '' QTSHZB,--其他生化指标
       '' CYJLXJ,--出院记录（小结）

       /*插入15列*/
      '福建省人民医院'   ZDDW00,--诊断单位
       '三级' ZDDWJB,--诊断单位级别
       decode(A.LYFS00, '死亡', '死亡', '存活') ZG000,--转归
       decode(A.LYFS00, '死亡', A.CYRQ00, '') SWSJ00,--死亡时间
       '' GBSYYY,--根本死因
       decode(A.LYFS00, '死亡', b.ZDM000, '') GBSYBM,--根本死因编码 ICD10
       '福建省人民医院' BGDW00,--报告单位
       A.CYKBMC BGKS00,--报告科室
       A.ZYYSMC BGYS00,--报告医师
       A.CYRQ00 BGRQ00--报告日期
  from BA_BRZYXX A, BQ_BRZDXX B, ZY_BRXXB0 C, BA_BRSSXX D
 where C.ZYID00 = B.ZYID00(+)
   and C.ZYID00(+) = A.LSH000
   and C.ZYID00 = D.LSH000(+)
   and substr(ZDM000, 0, 3) = 'I20'
   and substr(SSM000, 0, 5) in ('36.07', '36.06', '00.66', '36.10')
  -- and A.CYRQ00 between '20211026' and '20211130'
   and ZDLB00 = '3'
   and D.SSXH00 = 1
   and D.SSMC00 <> '-'
union all
--门诊（脑卒中、急性心肌梗死、心源性猝死）
select B.GHID00 LSH000,
      '福建省' BGKDWS,--报卡单位省
       '福州市' BGKDWS1,
       '台江区' BGKDWQ,
       A.GHH000 MJZH00,
       '' ZYH000,
       B.XM0000 BRXM00,
       '身份证' ZJLX00,
       C.BRZJBH SFZH00,
       '' QTJZH0,
       C.BRXB00,
       C.BRCSRQ,
       C.BRMZ00 MZ0000,
       C.BRZY00 ZY0000,
      decode(C.BRHYZK,'已婚','已婚/再婚/同居','再婚','已婚/再婚/同居','初婚','已婚/再婚/同居','复婚','已婚/再婚/同居',C.BRHYZK)  HYZK00,
       C.BRDH00 JTDH00,
       C.TYDWMC GZDW00,
       C.BRLXXM LXRXM0,
       C.BRLXDH LXRDH0,
       C.BRSZSF JGSF00,--户籍地址省
       C.BRSZDS JGXS00,
       C.BRSZS0 JTDZXS,
       C.BRSZZ0 JTDZ01,--户籍地址街道（乡）
       '' JTDZ02,--户籍地址居委会（村）
       '' JTDZ03,--户籍地址号
       '' XZZSF0,--现住地址省
       '' XZZDQ0,
       C.BRJTDZ XZZXC0,
       '' XZZJD0,--现住地址街道（乡）
       '' XZZCWH,--现住地址居委会（村）
       '' XZZDZH,--现住地址号
       '有' LXJZLY,--在本辖区连续居住6个月以上
       B.GHRQ00 FBRQ00,--发病日期
       (case when (select count(GHID00) as SFSCFB from SF_BRXXB0 where BRID00 = B.BRID00 and GHLB00 <>'1004' and THBZ00 = 2) > 1 then '否' else '是' end) SFSCFB,--是否首次发病
       B.GHRQ00 ZDRQ00,--诊断日期
       decode(substr(ICD900,0,2),'I6','脑卒中','I2','急性心肌梗死','I4','心源性猝死','') ZD0000,--诊断
'' XJTZL0,--心绞痛治疗措施
decode(substr(a.ICD900,0,3),'I60','蛛网膜下腔出血','I61','脑出血','I63','脑梗死（血栓形成性、栓塞性）','I64','难分类的完全性卒中','') NCZFL0,--脑卒中分类
       '' XZCSYJ,--心脏性猝死依据
       '' XZCSLX,--心脏性猝死推断类型
a.ICD900 ICD10,--门诊患者取门诊主诊断icd10编码，住院患者取出院主诊断icd10编码
 /*插入15列*/
       (select wm_concat ( distinct
case
  when ZLXMJC like '%肌钙蛋白%' then '肌钙蛋白'
  when ZLXMJC like '%心电图%' then '心电图'
  when ZLXMJC like '%CT%' then 'CT/CTA/SPECT'
  when ZLXMJC like '%磁共振%' then 'MRI'
  when ZLXMJC like '%超声心动图%' then '超声心动图'
  else '' end
) from YJ_YW0000 YJ where YJ.ZYGHID=B.GHID00 and (ZLXMJC like '%CT%' or ZLXMJC like '%心电图%' 
or ZLXMJC like '%肌钙蛋白%' or ZLXMJC like '%磁共振%' or ZLXMJC like '%超声心动图%' )) ZDYJ00,--诊断依据
    (select ZS0000 from YS_MZBLWS TP where TP.GHID00=B.GHID00 and ZS0000 is not null and rownum<=1) LCZZTZ,--（病史说明）临床症状体征
       /*   (select listagg(JCSJNR,'|') within group (order by  JCSJNR) from YJ_JCBG00_DZBL JY where JY.YJDJH0 in ( select YJDJH0 from YJ_YW0000 YJ where YJ.ZYGHID=B.GHID00 and ZLXMJC like '%肌钙蛋白%' )) SHBZW0,--检查结果（生化标志物）
       (select listagg(JCSJNR,'|') within group (order by  JCSJNR) from YJ_JCBG00_DZBL JY where JY.YJDJH0 in ( select YJDJH0 from YJ_YW0000 YJ where YJ.ZYGHID=B.GHID00 and ZLXMJC like '%心电图%'  )) XDT000,--
       '' XGZY00,--
       '' CSXDT0,--
       (select listagg(JCSJNR,'|') within group (order by  JCSJNR) from YJ_JCBG00_DZBL JY where JY.YJDJH0 in ( select YJDJH0 from YJ_YW0000 YJ where YJ.ZYGHID=B.GHID00 and ZLXMJC like '%CT%'  )) CTCTAS,--
       (select listagg(JCSJNR,'|') within group (order by  JCSJNR) from YJ_JCBG00_DZBL JY where JY.YJDJH0 in ( select YJDJH0 from YJ_YW0000 YJ where YJ.ZYGHID=B.GHID00 and ZLXMJC like '%磁共振%'  )) MRI000,--
       */
      SF_BA_getJCSJNR(B.GHID00,'肌钙蛋白') SHBZW0,--检查结果（生化标志物）
      SF_BA_getJCSJNR(B.GHID00,'心电图') XDT000,--
       '' XGZY00,--
       '' CSXDT0,--
      SF_BA_getJCSJNR(B.GHID00,'CT') CTCTAS,--
      SF_BA_getJCSJNR(B.GHID00,'磁共振') MRI000,--
       
       '' YC0000,--
       '' SS0000,--
       '' SJHBL0,--
       '' BFLX00,--
       '' BFLXSM,--
       '' QTSHZB,--其他生化指标
       '' CYJLXJ,--出院记录（小结）

       /*插入15列*/
      '福建省人民医院'   ZDDW00,--诊断单位
       '三级' ZDDWJB,--诊断单位级别
       '存活' ZG000,--转归
       '' SWSJ00,--死亡时间
       '' GBSYYY,--根本死因
       '' GBSYBM,--根本死因编码 ICD10
       '福建省人民医院' BGDW00,--报告单位
       (select D.BMMC00 from  BM_BMBM00 D where  B.GHKS00 = D.BMBH00 and rownum <=1  )  BGKS00,--报告科室
       B.GHYXM0 BGYS00,--报告医师
       B.GHRQ00 BGRQ00--报告日期
  from YS_BRZDXX A, SF_BRXXB0 B, BM_BRXXB0 C
 where A.GHID00 = B.GHID00(+)
   and C.BRID00(+) = B.BRID00
   and ZZDBZ0 = '3'
   and B.GHLB00 <> '1004'
   and B.THBZ00 = 2
   and substr(ICD900, 0, 3) in ('I21', 'I22', 'I60', 'I61', 'I63', 'I64', 'I46.1')
  -- and b.ghrq00 between '20211026' and '20211130'
/


-- End of DDL Script for View SD_HOSPITAL.VW_BA_XNXGJCBB