--5.3.0版本医院未全部升级因此以下代码网站外部sql脚本重复,暂时保留,后续可删除网站目录的sql脚本内重复代码
--20210326卫健委变化
--住院24小时内出院 PACG 住院时间应大于24小时取消(存在日间手术)
if exists(select * from D_ReportStaticsQuota where id=8 and ReportClassArr<>'1,2,3,4,5,6,7,8,10,11,12,13,14,15,16,17,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,40,41,46,47,48,49,50,52' )
update D_ReportStaticsQuota set ReportClassArr='1,2,3,4,5,6,7,8,10,11,12,13,14,15,16,17,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,40,41,46,47,48,49,50,52'
where id=8 and ReportClassArr<>'1,2,3,4,5,6,7,8,10,11,12,13,14,15,16,17,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,40,41,46,47,48,49,50,52'
--更新自动对接卫健委
if exists(select * from Q_Options where Name='NcisAutoOpenList' and Value<>'' and Value is not null)
update D_Disease_Category set IsNcisAutoOpen=1
where IsNcisChange=1 and
CHARINDEX(','+ cast(DCID as nvarchar(max)) +',',(select top 1 ','+ Value from Q_Options where Name='NcisAutoOpenList')) > 0
--将程序中卫健委变化标志取消
if exists(select * from D_Disease_Category where IsNcisChange=1)
update D_Disease_Category set IsNcisChange=null
where IsNcisChange=1
--更新公告
if exists(select * from Report_InterAnnounce where IsTop=1 and CHARINDEX('因国家单病种平台填报内容发生变化',Title) > 0)
update Report_InterAnnounce set IsTop=0
where IsTop=1 and CHARINDEX('因国家单病种平台填报内容发生变化',Title) > 0
if exists(select * from Q_Options where Name='NcisAutoOpenList' and Value<>'' and Value is not null)
update Q_Options set Value=''
where Name='NcisAutoOpenList' and Value<>'' and Value is not null
--病种重复病除外设置 VTE-BC
if exists(select * from D_RepeatReportSetting where ReportClass=36 and RepeatReportClass=17 and Icd9Code is null)
update D_RepeatReportSetting set Icd9Code='85.2、85.3、85.4'
where ReportClass=36 and RepeatReportClass=17 and Icd9Code is null
--pip 无需上报内容
if exists(select * from D_Disease_Category where SystemDescription like '%18岁以下的病例%' and DCID = 9)
update D_Disease_Category set SystemDescription='
系统特别说明:
1.同一疾病30日内重复入院,无需上报
2.本次住院天数大于90天,无法上报。
'
where SystemDescription like '%18岁以下的病例%' and DCID = 9
--无需上报条件 18岁以下的病例
if exists(select * from D_ReportStaticsQuota where id=1 and ReportClassArr<>'1,2,3,4,5,6,7,10,11,12,13,14,15,16,17,19,20,21,22,23,24,25,26,27,28,29,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,50,51,' )
update D_ReportStaticsQuota set ReportClassArr='1,2,3,4,5,6,7,10,11,12,13,14,15,16,17,19,20,21,22,23,24,25,26,27,28,29,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,50,51,'
where id=1 and ReportClassArr<>'1,2,3,4,5,6,7,10,11,12,13,14,15,16,17,19,20,21,22,23,24,25,26,27,28,29,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,50,51,'
--漏报设置更新科室改为关闭状态
if exists(select * from D_Disease_Category where HisDeptEnabled=1 )
update D_Disease_Category set HisDeptEnabled=0
where HisDeptEnabled=1
--cacc 去除编码 O99.5
if exists(select * from Report_Leak where ReportClass = 47 and DieaseICD like 'O99.5%' and DealReport=0 )
update Report_Leak set DealReport=2
where ReportClass = 47 and DieaseICD like 'O99.5%' and DealReport=0
--tia 去除编码G45.004
if exists(select * from Report_Leak where ReportClass = 13 and DieaseICD like 'G45.004%' and DealReport=0 )
update Report_Leak set DealReport=2
where ReportClass=13 and DieaseICD like 'G45.004%' and DealReport=0
--根据处理记录更新漏报管理状态
update Report_Leak set NoMustReportReason=
(NoMustReportReason+' '+
(select top(1) ChangeNote from Report_LeakLog where Report_Leak.ID=Report_LeakLog.LeakId order by ChangeDate desc)
),DealReport=2
where id in (
select ID from (SELECT (select top(1) ChangeNote from Report_LeakLog where Report_Leak.ID=Report_LeakLog.LeakId order by ChangeDate desc) as note
,(select top(1) Status from Report_LeakLog where Report_Leak.ID=Report_LeakLog.LeakId order by ChangeDate desc) as Status
,*
FROM Report_Leak ) a
where DealReport=0 and Status =2
)
--pip、dvt重复将pip未报数据标记为无需上报
if exists(select * from Report_Leak where ( OpertationICD like '65.2%'or OpertationICD like '65.3%%' or OpertationICD like'65.4%'
or OpertationICD like'65.5%' or OpertationICD like'65.6%' or OpertationICD like'00.7%' or OpertationICD like'81.51%'
or OpertationICD like'81.52%' or OpertationICD like'81.53%' or OpertationICD like'00.80%' or OpertationICD like'00.81%'
or OpertationICD like'00.82%' or OpertationICD like'00.83%' or OpertationICD like'81.54%' or OpertationICD like'81.55%' or OpertationICD like'36.1')
and ReportClass=9 and DealReport=0 )
update Report_Leak set DealReport=2,NoMustReportReason ='PIP与DVT重复的病种'
where ( OpertationICD like '65.2%'or OpertationICD like '65.3%%' or OpertationICD like'65.4%'
or OpertationICD like'65.5%' or OpertationICD like'65.6%' or OpertationICD like'00.7%' or OpertationICD like'81.51%'
or OpertationICD like'81.52%' or OpertationICD like'81.53%' or OpertationICD like'00.80%' or OpertationICD like'00.81%'
or OpertationICD like'00.82%' or OpertationICD like'00.83%' or OpertationICD like'81.54%' or OpertationICD like'81.55%' or OpertationICD like'36.1')
--原“数据统计”权限,改为“指标统计”
update Q_Quanxian set QuanxianName='指标统计' where QuanxianName='数据统计' and id=24
--数据预警将接口自动对接改为 对接失败
if exists(select * from Q_DiseaseGrade where DiseaseGradeID=5 and DiseaseGradeName='接口自动对接')
update Q_DiseaseGrade set DiseaseGradeName='对接失败'
where DiseaseGradeID=5 and DiseaseGradeName='接口自动对接'
--asd vsd 上报说明
--asd
if not exists(select * from D_Disease_Category where DiseaseContent like '%18岁人群%' and DCID = 30)
update D_Disease_Category set DiseaseContent=
'
上报内容:
主要手术 ICD-9-CM-3 编码:35.51、35.52、35.61、35.71 的手术出院患者。
无需上报内容:
1.参与临床药物与器械试验的病例,
2.外院转入手术失敗病例
3.本次住院超过90天
4.年龄<2岁,年龄>18岁人群
数据验证:
1. *是必填项目,其他项未填则按未做统计,★为本病种的核心(重点)质量监控指标。
2. 强制验证项若超过正常值,系统给予提示,不允许填写。
3. 其他项若超出正常值范围,系统给予提示,但允许填写实际数据。
'
where DCID = 30
--vsd
if not exists(select * from D_Disease_Category where DiseaseContent like '%18岁人群%' and DCID = 31)
update D_Disease_Category set DiseaseContent=
'
上报内容:
主要手术 ICD-9-CM-3 编码:35.53、35.55、35.62、35.72 的手术出院患者。
无需上报内容:
1.参与临床药物与器械试验的病例,
2.外院转入手术失敗病例
3.本次住院超过90天
4.年龄<2岁,年龄>18岁人群
数据验证:
1. *是必填项目,其他项未填则按未做统计,★为本病种的核心(重点)质量监控指标。
2. 强制验证项若超过正常值,系统给予提示,不允许填写。
3. 其他项若超出正常值范围,系统给予提示,但允许填写实际数据。
'
where DCID = 31
--无需上报条件 年龄大于等于18岁以及小于2岁的新生儿
if exists(select * from D_ReportStaticsQuota where id=2 and ReportClassArr<>'8,30,31,47,' )
update D_ReportStaticsQuota set ReportClassArr='8,30,31,47,'
where id=2 and ReportClassArr<>'8,30,31,47,'
--修改口腔种植术的说明文字
if exists(select * from D_Disease_Category where DiseaseContent like '%18岁以上%' and DCID = 51)
update D_Disease_Category set DiseaseContent=
'OIT口腔种植术
主要手术 ICD-9-CM-3 编码:23.5,23.6 的门诊患者或 者 76.09,76.91,76.92,22.79 的手术出院患者。
无需上报内容:
1.由外院住院诊疗后转入本院的病例
2.18岁以下的病例
3.同一疾病30日内重复入院的病例
4.本次住院超过90天的病例
数据验证: 1. *是必填项目,其他项未填则按未做统计,★为本病种的核心(重点)质量监控指标。
2. 强制验证项若超过正常值,系统给予提示,不允许填写。
'
where DCID = 51
--修改vte抽取规则
if exists(select * from D_Disease_Category where LeakExtractRule<>0 and DCID =36)
update D_Disease_Category set LeakExtractRule=0
where LeakExtractRule<>0 and DCID =36
--设定 [DataBaseAutoBak] AutoDbBakAtMinute默认值0
if exists(select * from [DataBaseAutoBak] where AutoDbBakAtMinute is null )
update [DataBaseAutoBak] set AutoDbBakAtMinute=0
where AutoDbBakAtMinute is null
--无需上报条件 同一疾病30日内重复入院
if exists(select * from D_ReportStaticsQuota where id=12 and ReportClassArr<>'1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,' )
update D_ReportStaticsQuota set ReportClassArr='1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,'
where id=12 and ReportClassArr<>'1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,'
--对接卫健委code旧数据赋值
if exists(select * from D_MedicalRecord where Ncis_JoinErrorMessage like '%该项为必填项,请进行填写%' and Ncis_IsJoinError=1)
update D_MedicalRecord set Ncis_JoinCode=1000
where Ncis_JoinErrorMessage like '%该项为必填项,请进行填写%' and Ncis_IsJoinError=1
--在1.1版本数据中对应添加2.0数据统计需要字段
--离院方式
if exists(select * from D_MedicalRecord where JSON_VALUE(DataInfo,'$."CM-4-3"') is null and DataVersion='1.1' )
update D_MedicalRecord set DataInfo=
JSON_MODIFY(DataInfo, '$."CM-4-3"',
case when DataVersion='1.1' and ReportClass=1 then JSON_VALUE(DataInfo,'$."STEMI-9-1-2-1"') --STEMI
when DataVersion='1.1' and ReportClass=2 then JSON_VALUE(DataInfo,'$."HF-9-1-2-1"') --HF
when DataVersion='1.1' and ReportClass=3 then JSON_VALUE(DataInfo,'$."Cap-Adult-10-2-1"') --Cap-Adult
when DataVersion='1.1' and ReportClass=4 then JSON_VALUE(DataInfo,'$."Hip-10-2"') --Hip
when DataVersion='1.1' and ReportClass=5 then JSON_VALUE(DataInfo,'$."Knee-10-2"') --Knee
when DataVersion='1.1' and ReportClass=6 then JSON_VALUE(DataInfo,'$."STK-11-2-1"') --STK
when DataVersion='1.1' and ReportClass=7 then JSON_VALUE(DataInfo,'$."CABG-10-2"') --CABG
when DataVersion='1.1' and ReportClass=8 then JSON_VALUE(DataInfo,'$."Cap-9-2-1"') --Cap
when DataVersion='1.1' and ReportClass=9 then JSON_VALUE(DataInfo,'$."PIP-5-2"') --PIP
when DataVersion='1.1' and ReportClass=10 then JSON_VALUE(DataInfo,'$."CS-10-2"') --CS
when DataVersion='1.1' and ReportClass=11 then JSON_VALUE(DataInfo,'$."AECOPD-9-2-1"') --AECOPD
when DataVersion='1.1' and ReportClass=12 then JSON_VALUE(DataInfo,'$."DVT-5-2"') --DVT
when DataVersion='1.1' and ReportClass=13 then JSON_VALUE(DataInfo,'$."TIA-8-2-1"') --TIA
when DataVersion='1.1' and ReportClass=14 then JSON_VALUE(DataInfo,'$."HBIPS-13-2-1"') --HBIPS
when DataVersion='1.1' and ReportClass=15 then JSON_VALUE(DataInfo,'$."TC-9-2-1"') --TC
when DataVersion='1.1' and ReportClass=16 then JSON_VALUE(DataInfo,'$."LC-14-2-1"') --LC
when DataVersion='1.1' and ReportClass=17 then JSON_VALUE(DataInfo,'$."BC-13-2-1"') --BC
end
)where DataVersion='1.1'
--入院日期时间
if exists(select * from D_MedicalRecord where JSON_VALUE(DataInfo,'$."CM-0-2-4-1"') is null and DataVersion='1.1' )
update D_MedicalRecord set DataInfo=
JSON_MODIFY(DataInfo, '$."CM-0-2-4-1"',
case when DataVersion='1.1' and ReportClass=1 then JSON_VALUE(DataInfo,'$."STEMI-0-2-4"') --STEMI
when DataVersion='1.1' and ReportClass=2 then JSON_VALUE(DataInfo,'$."HF-0-2-4"') --HF
when DataVersion='1.1' and ReportClass=3 then JSON_VALUE(DataInfo,'$."Cap-Adult-0-2-4"') --Cap-Adult
when DataVersion='1.1' and ReportClass=4 then JSON_VALUE(DataInfo,'$."Hip-0-2-2-1"') --Hip
when DataVersion='1.1' and ReportClass=5 then JSON_VALUE(DataInfo,'$."Knee-0-2-2"') --Knee
when DataVersion='1.1' and ReportClass=6 then JSON_VALUE(DataInfo,'$."STK-0-2-4"') --STK
when DataVersion='1.1' and ReportClass=7 then JSON_VALUE(DataInfo,'$."CABG-0-2-2"') --CABG
when DataVersion='1.1' and ReportClass=8 then JSON_VALUE(DataInfo,'$."Cap-0-2-4"') --Cap
when DataVersion='1.1' and ReportClass=9 then JSON_VALUE(DataInfo,'$."PIP-0-2-2-1"') --PIP
when DataVersion='1.1' and ReportClass=10 then JSON_VALUE(DataInfo,'$."CS-0-2-4"') --CS
when DataVersion='1.1' and ReportClass=11 then JSON_VALUE(DataInfo,'$."AECOPD-0-2-4"') --AECOPD
when DataVersion='1.1' and ReportClass=12 then JSON_VALUE(DataInfo,'$."DVT-0-2-2-1"') --DVT
when DataVersion='1.1' and ReportClass=13 then JSON_VALUE(DataInfo,'$."TIA-0-2-4"') --TIA
when DataVersion='1.1' and ReportClass=14 then JSON_VALUE(DataInfo,'$."HBIPS-0-3-1"') --HBIPS
when DataVersion='1.1' and ReportClass=15 then JSON_VALUE(DataInfo,'$."TC-0-2-4"') --TC
when DataVersion='1.1' and ReportClass=16 then JSON_VALUE(DataInfo,'$."LC-0-2-4"') --LC
when DataVersion='1.1' and ReportClass=17 then JSON_VALUE(DataInfo,'$."BC-0-2-4-1"') --BC
end
)where DataVersion='1.1'
--出院日期时间
if exists(select * from D_MedicalRecord where JSON_VALUE(DataInfo,'$."CM-0-2-4-2"') is null and DataVersion='1.1' )
update D_MedicalRecord set DataInfo=
JSON_MODIFY(DataInfo, '$."CM-0-2-4-2"',
case when DataVersion='1.1' and ReportClass=1 then JSON_VALUE(DataInfo,'$."STEMI-0-2-5"') --STEMI
when DataVersion='1.1' and ReportClass=2 then JSON_VALUE(DataInfo,'$."HF-0-2-5"') --HF
when DataVersion='1.1' and ReportClass=3 then JSON_VALUE(DataInfo,'$."Cap-Adult-0-2-5"') --Cap-Adult
when DataVersion='1.1' and ReportClass=4 then JSON_VALUE(DataInfo,'$."Hip-0-2-3"') --Hip
when DataVersion='1.1' and ReportClass=5 then JSON_VALUE(DataInfo,'$."Knee-0-2-3"') --Knee
when DataVersion='1.1' and ReportClass=6 then JSON_VALUE(DataInfo,'$."STK-0-2-5"') --STK
when DataVersion='1.1' and ReportClass=7 then JSON_VALUE(DataInfo,'$."CABG-0-2-3"') --CABG
when DataVersion='1.1' and ReportClass=8 then JSON_VALUE(DataInfo,'$."Cap-0-2-5"') --Cap
when DataVersion='1.1' and ReportClass=9 then JSON_VALUE(DataInfo,'$."PIP-0-2-3"') --PIP
when DataVersion='1.1' and ReportClass=10 then JSON_VALUE(DataInfo,'$."CS-0-2-5"') --CS
when DataVersion='1.1' and ReportClass=11 then JSON_VALUE(DataInfo,'$."AECOPD-0-2-5"') --AECOPD
when DataVersion='1.1' and ReportClass=12 then JSON_VALUE(DataInfo,'$."DVT-0-2-3"') --DVT
when DataVersion='1.1' and ReportClass=13 then JSON_VALUE(DataInfo,'$."TIA-0-2-5"') --TIA
when DataVersion='1.1' and ReportClass=14 then JSON_VALUE(DataInfo,'$."HBIPS-0-3-2"') --HBIPS
when DataVersion='1.1' and ReportClass=15 then JSON_VALUE(DataInfo,'$."TC-0-2-5"') --TC
when DataVersion='1.1' and ReportClass=16 then JSON_VALUE(DataInfo,'$."LC-0-2-5"') --LC
when DataVersion='1.1' and ReportClass=17 then JSON_VALUE(DataInfo,'$."BC-0-2-4-2"') --BC
end
) where DataVersion='1.1'
--住院总费用
if exists(select * from D_MedicalRecord where JSON_VALUE(DataInfo,'$."CM-6-1"') is null and DataVersion='1.1' )
update D_MedicalRecord set DataInfo=
JSON_MODIFY(DataInfo, '$."CM-6-1"',
case when DataVersion='1.1' and ReportClass=1 then JSON_VALUE(DataInfo,'$."STEMI-9-1-3-1"') --STEMI
when DataVersion='1.1' and ReportClass=2 then JSON_VALUE(DataInfo,'$."HF-9-1-3-1"') --HF
when DataVersion='1.1' and ReportClass=3 then JSON_VALUE(DataInfo,'$."Cap-Adult-10-3-1"') --Cap-Adult
when DataVersion='1.1' and ReportClass=4 then JSON_VALUE(DataInfo,'$."Hip-10-4-1"') --Hip
when DataVersion='1.1' and ReportClass=5 then JSON_VALUE(DataInfo,'$."Knee-10-4-1"') --Knee
when DataVersion='1.1' and ReportClass=6 then JSON_VALUE(DataInfo,'$."STK-11-3-1"') --STK
when DataVersion='1.1' and ReportClass=7 then JSON_VALUE(DataInfo,'$."CABG-10-4-1"') --CABG
when DataVersion='1.1' and ReportClass=8 then JSON_VALUE(DataInfo,'$."Cap-9-3-1"') --Cap
when DataVersion='1.1' and ReportClass=9 then JSON_VALUE(DataInfo,'$."PIP-5-4-1"') --PIP
when DataVersion='1.1' and ReportClass=10 then JSON_VALUE(DataInfo,'$."CS-10-4-1"') --CS
when DataVersion='1.1' and ReportClass=11 then JSON_VALUE(DataInfo,'$."AECOPD-9-3-1"') --AECOPD
when DataVersion='1.1' and ReportClass=12 then JSON_VALUE(DataInfo,'$."DVT-5-4-1"') --DVT
when DataVersion='1.1' and ReportClass=13 then JSON_VALUE(DataInfo,'$."TIA-8-3-1"') --TIA
when DataVersion='1.1' and ReportClass=14 then JSON_VALUE(DataInfo,'$."HBIPS-13-3-1"') --HBIPS
when DataVersion='1.1' and ReportClass=15 then JSON_VALUE(DataInfo,'$."TC-9-3-1"') --TC
when DataVersion='1.1' and ReportClass=16 then JSON_VALUE(DataInfo,'$."LC-14-3-1"') --LC
when DataVersion='1.1' and ReportClass=17 then JSON_VALUE(DataInfo,'$."BC-13-3-1"') --BC
end
) where DataVersion='1.1'
--手术治疗费
if exists(select * from D_MedicalRecord where JSON_VALUE(DataInfo,'$."CM-6-13"') is null and DataVersion='1.1' )
update D_MedicalRecord set DataInfo=
JSON_MODIFY(DataInfo, '$."CM-6-13"',
case when DataVersion='1.1' and ReportClass=1 then JSON_VALUE(DataInfo,'$."STEMI-9-1-3-5"') --STEMI
when DataVersion='1.1' and ReportClass=2 then JSON_VALUE(DataInfo,'$."HF-9-1-3-5"') --HF
when DataVersion='1.1' and ReportClass=3 then JSON_VALUE(DataInfo,'$."Cap-Adult-10-3-5"') --Cap-Adult
when DataVersion='1.1' and ReportClass=4 then JSON_VALUE(DataInfo,'$."Hip-10-4-5"') --Hip
when DataVersion='1.1' and ReportClass=5 then JSON_VALUE(DataInfo,'$."Knee-10-4-5"') --Knee
when DataVersion='1.1' and ReportClass=6 then JSON_VALUE(DataInfo,'$."STK-11-3-5"') --STK
when DataVersion='1.1' and ReportClass=7 then JSON_VALUE(DataInfo,'$."CABG-10-4-5"') --CABG
when DataVersion='1.1' and ReportClass=8 then JSON_VALUE(DataInfo,'$."Cap-9-3-5"') --Cap
when DataVersion='1.1' and ReportClass=9 then JSON_VALUE(DataInfo,'$."PIP-5-4-5"') --PIP
when DataVersion='1.1' and ReportClass=10 then JSON_VALUE(DataInfo,'$."CS-10-4-5"') --CS
when DataVersion='1.1' and ReportClass=11 then JSON_VALUE(DataInfo,'$."AECOPD-9-3-5"') --AECOPD
when DataVersion='1.1' and ReportClass=12 then JSON_VALUE(DataInfo,'$."DVT-5-4-5"') --DVT
when DataVersion='1.1' and ReportClass=13 then JSON_VALUE(DataInfo,'$."TIA-8-3-5"') --TIA
when DataVersion='1.1' and ReportClass=14 then JSON_VALUE(DataInfo,'$."HBIPS-13-3-5"') --HBIPS
when DataVersion='1.1' and ReportClass=15 then JSON_VALUE(DataInfo,'$."TC-9-3-5"') --TC
when DataVersion='1.1' and ReportClass=16 then JSON_VALUE(DataInfo,'$."LC-14-3-5"') --LC
when DataVersion='1.1' and ReportClass=17 then JSON_VALUE(DataInfo,'$."BC-13-3-5"') --BC
end
) where DataVersion='1.1'
--西药费
if exists(select * from D_MedicalRecord where JSON_VALUE(DataInfo,'$."CM-6-18"') is null and DataVersion='1.1' )
update D_MedicalRecord set DataInfo=
JSON_MODIFY(DataInfo, '$."CM-6-18"',
case when DataVersion='1.1' and ReportClass=1 then JSON_VALUE(DataInfo,'$."STEMI-9-1-3-2"') --STEMI
when DataVersion='1.1' and ReportClass=2 then JSON_VALUE(DataInfo,'$."HF-9-1-3-2"') --HF
when DataVersion='1.1' and ReportClass=3 then JSON_VALUE(DataInfo,'$."Cap-Adult-10-3-2"') --Cap-Adult
when DataVersion='1.1' and ReportClass=4 then JSON_VALUE(DataInfo,'$."Hip-10-4-2"') --Hip
when DataVersion='1.1' and ReportClass=5 then JSON_VALUE(DataInfo,'$."Knee-10-4-2"') --Knee
when DataVersion='1.1' and ReportClass=6 then JSON_VALUE(DataInfo,'$."STK-11-3-2"') --STK
when DataVersion='1.1' and ReportClass=7 then JSON_VALUE(DataInfo,'$."CABG-10-4-2"') --CABG
when DataVersion='1.1' and ReportClass=8 then JSON_VALUE(DataInfo,'$."Cap-9-3-2"') --Cap
when DataVersion='1.1' and ReportClass=9 then JSON_VALUE(DataInfo,'$."PIP-5-4-2"') --PIP
when DataVersion='1.1' and ReportClass=10 then JSON_VALUE(DataInfo,'$."CS-10-4-2"') --CS
when DataVersion='1.1' and ReportClass=11 then JSON_VALUE(DataInfo,'$."AECOPD-9-3-2"') --AECOPD
when DataVersion='1.1' and ReportClass=12 then JSON_VALUE(DataInfo,'$."DVT-5-4-2"') --DVT
when DataVersion='1.1' and ReportClass=13 then JSON_VALUE(DataInfo,'$."TIA-8-3-2"') --TIA
when DataVersion='1.1' and ReportClass=14 then JSON_VALUE(DataInfo,'$."HBIPS-13-3-2"') --HBIPS
when DataVersion='1.1' and ReportClass=15 then JSON_VALUE(DataInfo,'$."TC-9-3-2"') --TC
when DataVersion='1.1' and ReportClass=16 then JSON_VALUE(DataInfo,'$."LC-14-3-2"') --LC
when DataVersion='1.1' and ReportClass=17 then JSON_VALUE(DataInfo,'$."BC-13-3-2"') --BC
end
) where DataVersion='1.1'
--中成药费
if exists(select * from D_MedicalRecord where JSON_VALUE(DataInfo,'$."CM-6-20"') is null and DataVersion='1.1' )
update D_MedicalRecord set DataInfo=
JSON_MODIFY(DataInfo, '$."CM-6-20"',
case when DataVersion='1.1' and ReportClass=1 then JSON_VALUE(DataInfo,'$."STEMI-9-1-3-3"') --STEMI
when DataVersion='1.1' and ReportClass=2 then JSON_VALUE(DataInfo,'$."HF-9-1-3-3"') --HF
when DataVersion='1.1' and ReportClass=3 then JSON_VALUE(DataInfo,'$."Cap-Adult-10-3-3"') --Cap-Adult
when DataVersion='1.1' and ReportClass=4 then JSON_VALUE(DataInfo,'$."Hip-10-4-3"') --Hip
when DataVersion='1.1' and ReportClass=5 then JSON_VALUE(DataInfo,'$."Knee-10-4-3"') --Knee
when DataVersion='1.1' and ReportClass=6 then JSON_VALUE(DataInfo,'$."STK-11-3-3"') --STK
when DataVersion='1.1' and ReportClass=7 then JSON_VALUE(DataInfo,'$."CABG-10-4-3"') --CABG
when DataVersion='1.1' and ReportClass=8 then JSON_VALUE(DataInfo,'$."Cap-9-3-3"') --Cap
when DataVersion='1.1' and ReportClass=9 then JSON_VALUE(DataInfo,'$."PIP-5-4-3"') --PIP
when DataVersion='1.1' and ReportClass=10 then JSON_VALUE(DataInfo,'$."CS-10-4-3"') --CS
when DataVersion='1.1' and ReportClass=11 then JSON_VALUE(DataInfo,'$."AECOPD-9-3-3"') --AECOPD
when DataVersion='1.1' and ReportClass=12 then JSON_VALUE(DataInfo,'$."DVT-5-4-3"') --DVT
when DataVersion='1.1' and ReportClass=13 then JSON_VALUE(DataInfo,'$."TIA-8-3-3"') --TIA
when DataVersion='1.1' and ReportClass=14 then JSON_VALUE(DataInfo,'$."HBIPS-13-3-3"') --HBIPS
when DataVersion='1.1' and ReportClass=15 then JSON_VALUE(DataInfo,'$."TC-9-3-3"') --TC
when DataVersion='1.1' and ReportClass=16 then JSON_VALUE(DataInfo,'$."LC-14-3-3"') --LC
when DataVersion='1.1' and ReportClass=17 then JSON_VALUE(DataInfo,'$."BC-13-3-3"') --BC
end
) where DataVersion='1.1'
--评价
if exists(select * from D_MedicalRecord where JSON_VALUE(DataInfo,'$."CM-5-1"') is null and DataVersion='1.1' )
update D_MedicalRecord set DataInfo=JSON_MODIFY(DataInfo, '$."CM-5-1"', 'y') where DataVersion='1.1'
if exists(select * from D_MedicalRecord where JSON_VALUE(DataInfo,'$."CM-5-2-1"') is null and DataVersion='1.1' )
update D_MedicalRecord set DataInfo=JSON_MODIFY(DataInfo, '$."CM-5-2-1"', 'a') where DataVersion='1.1'
if exists(select * from D_MedicalRecord where JSON_VALUE(DataInfo,'$."CM-5-2-2"') is null and DataVersion='1.1' )
update D_MedicalRecord set DataInfo=JSON_MODIFY(DataInfo, '$."CM-5-2-2"', 'a') where DataVersion='1.1'
if exists(select * from D_MedicalRecord where JSON_VALUE(DataInfo,'$."CM-5-2-3"') is null and DataVersion='1.1' )
update D_MedicalRecord set DataInfo=JSON_MODIFY(DataInfo, '$."CM-5-2-3"', 'a') where DataVersion='1.1'
if exists(select * from D_MedicalRecord where JSON_VALUE(DataInfo,'$."CM-5-2-4"') is null and DataVersion='1.1' )
update D_MedicalRecord set DataInfo=JSON_MODIFY(DataInfo, '$."CM-5-2-4"', 'a') where DataVersion='1.1'
if exists(select * from D_MedicalRecord where JSON_VALUE(DataInfo,'$."CM-5-2-5"') is null and DataVersion='1.1' )
update D_MedicalRecord set DataInfo=JSON_MODIFY(DataInfo, '$."CM-5-2-5"', 'a') where DataVersion='1.1'
if exists(select * from D_MedicalRecord where JSON_VALUE(DataInfo,'$."CM-5-2-6"') is null and DataVersion='1.1' )
update D_MedicalRecord set DataInfo=JSON_MODIFY(DataInfo, '$."CM-5-2-6"', 'a') where DataVersion='1.1'
if exists(select * from D_MedicalRecord where JSON_VALUE(DataInfo,'$."CM-5-2-7"') is null and DataVersion='1.1' )
update D_MedicalRecord set DataInfo=JSON_MODIFY(DataInfo, '$."CM-5-2-7"', 'a') where DataVersion='1.1'
if exists(select * from D_MedicalRecord where JSON_VALUE(DataInfo,'$."CM-5-2-8"') is null and DataVersion='1.1' )
update D_MedicalRecord set DataInfo=JSON_MODIFY(DataInfo, '$."CM-5-2-8"', 'a') where DataVersion='1.1'
if exists(select * from D_MedicalRecord where JSON_VALUE(DataInfo,'$."CM-5-2-9"') is null and DataVersion='1.1' )
update D_MedicalRecord set DataInfo=JSON_MODIFY(DataInfo, '$."CM-5-2-9"', 'a') where DataVersion='1.1'
if exists(select * from D_MedicalRecord where JSON_VALUE(DataInfo,'$."CM-5-2-10"') is null and DataVersion='1.1' )
update D_MedicalRecord set DataInfo=JSON_MODIFY(DataInfo, '$."CM-5-2-10"', 'a') where DataVersion='1.1'
if exists(select * from D_MedicalRecord where JSON_VALUE(DataInfo,'$."CM-5-2-11"') is null and DataVersion='1.1' )
update D_MedicalRecord set DataInfo=JSON_MODIFY(DataInfo, '$."CM-5-2-11"', 'a') where DataVersion='1.1'
--手术并发症
if exists(select * from D_MedicalRecord where JSON_VALUE(DataInfo,'$."CM-2-1"') is null and DataVersion='1.1' and ReportClass in (4,5,7,9,12,15,16,17) )
update D_MedicalRecord set DataInfo=
JSON_MODIFY(DataInfo, '$."CM-2-1"',
case
when DataVersion='1.1' and ReportClass=4 then JSON_VALUE(DataInfo,'$."Hip-7-1"') --Hip
when DataVersion='1.1' and ReportClass=5 then JSON_VALUE(DataInfo,'$."Knee-7-1"') --Knee
when DataVersion='1.1' and ReportClass=7 then JSON_VALUE(DataInfo,'$."CABG-6-2-1"') --CABG
when DataVersion='1.1' and ReportClass=9 then JSON_VALUE(DataInfo,'$."PIP-3-1"') --PIP
when DataVersion='1.1' and ReportClass=12 then JSON_VALUE(DataInfo,'$."DVT-3-1"') --DVT
when DataVersion='1.1' and ReportClass=15 then JSON_VALUE(DataInfo,'$."TC-3-1"') --TC
when DataVersion='1.1' and ReportClass=16 then JSON_VALUE(DataInfo,'$."LC-6-1"') --LC
when DataVersion='1.1' and ReportClass=17 then JSON_VALUE(DataInfo,'$."BC-7-1"') --BC
end
) where DataVersion='1.1' and ReportClass in (4,5,7,9,12,15,16,17)
--sep 去除编码 B73.7、R65.2、R65.3、R65.9
if exists(select * from Report_Leak where ReportClass = 35 and
(DieaseICD like '%B73.7%' or DieaseICD like '%R65.2%' or DieaseICD like '%R65.3%' or DieaseICD like '%R65.9%'
or ICD10_oth like '%B73.7%' or ICD10_oth like '%R65.2%' or ICD10_oth like '%R65.3%' or ICD10_oth like '%R65.9%'
)
and DieaseICD not like '%A0%' and DieaseICD not like '%A2%' and DieaseICD not like '%A3%' and DieaseICD not like '%A4%'
and DieaseICD not like '%A5%' and DieaseICD not like '%B3%'
and ICD10_oth not like '%A0%' and ICD10_oth not like '%A2%' and ICD10_oth not like '%A3%' and ICD10_oth not like '%A4%'
and ICD10_oth not like '%A5%' and ICD10_oth not like '%B3%'
and DealReport=0 )
update Report_Leak set DealReport=2,NoMustReportReason='B73.7、R65.2、R65.3、R65.9 无需上报'
where ReportClass = 35 and (DieaseICD like '%B73.7%' or DieaseICD like '%R65.2%' or DieaseICD like '%R65.3%' or DieaseICD like '%R65.9%'
or ICD10_oth like '%B73.7%' or ICD10_oth like '%R65.2%' or ICD10_oth like '%R65.3%' or ICD10_oth like '%R65.9%'
)
and DieaseICD not like '%A0%' and DieaseICD not like '%A2%' and DieaseICD not like '%A3%' and DieaseICD not like '%A4%'
and DieaseICD not like '%A5%' and DieaseICD not like '%B3%'
and ICD10_oth not like '%A0%' and ICD10_oth not like '%A2%' and ICD10_oth not like '%A3%' and ICD10_oth not like '%A4%'
and ICD10_oth not like '%A5%' and ICD10_oth not like '%B3%'
and DealReport=0
--sep上报说明
if exists(select * from D_Disease_Category where DiseaseContent like '%R65.2%' and DCID = 35)
update D_Disease_Category set DiseaseContent=
'上报内容
主要诊断/其他诊断 ICD-10 编码:A02.1,A22.7,A32.7, A40.1 至 A40.9, A41.0 至 A41.9,A42.7,A54.8,B37.7的出院患者。
无需上报内容
1、参与临床药物与器械试验的病例
2、到达医院前,已经被确认采取安慰治疗或姑息治疗者
3、18岁以下的病例
4、本次住院天数大于90天
5、由外院感染性休克诊疗后转入本院的病例
6、在医师病程记录,有在严重脓毒症出现后3小时内出现被确认采取安慰治疗或姑息治疗的医嘱
7、在医师病程记录,在感染性休克出现后6小时内出现被确认采取安慰治疗或姑息治疗的医嘱
'
where DCID = 35
--HBV上报说明
if exists(select * from D_Disease_Category where DiseaseContent not like '%任意一项诊断%' and DCID = 50)
update D_Disease_Category set DiseaseContent='
上报内容:
出院诊断中任意一项诊断(包含主要诊断和其他诊断)编码为:O98.4,Z22.5 + O80 至 O84+Z37; 且伴1阴道分娩操作 ICD-9-CM-3 编码 72.0 至 72.9,73.0, 73.1,73.21,73.4 至 73.6,73.9;或伴2剖宫产手术 ICD 9-CM-3 编码:74.0,74.1,74.2,74.4,74.99 的出院患者。
无需上报内容:
1.由外院住院诊疗后转入本院的病例
2.参与临床药物与器械试验的病例
3.18岁以下的病例
4.同一疾病30日内重复入院
5.本次住院超过90天!
6.病历记录孕期<28周或不明的
数据验证:
1. *是必填项目,其他项未填则按未做统计,★为本病种的核心(重点)质量监控指标。
2. 强制验证项若超过正常值,系统给予提示,不允许填写。
'
where DiseaseContent not like '%任意一项诊断%' and DCID = 50
--暂时保留 结束--