sdqmupdatge/UpdateSql/5.3/D_Insert.sql

--添加病种重复病例除外设置
--pip与各病种重复
if not exists(select * from D_RepeatReportSetting where ReportClass =9 and RepeatReportClass>10)
BEGIN
--甲状腺癌（手术治疗）·TC
INSERT [dbo].[D_RepeatReportSetting] ([ReportClass], [RepeatReportClass], [Icd9Code], [Icd10Code], [Enabled]) 
VALUES (9, 15, N'06.2、06.3、06.4、06.5', NULL, 1)
--房间隔缺损手术·ASD
INSERT [dbo].[D_RepeatReportSetting] ([ReportClass], [RepeatReportClass], [Icd9Code], [Icd10Code], [Enabled]) 
VALUES (9, 30, N'35.61', NULL, 1)
--室间隔缺损手术·VSD
INSERT [dbo].[D_RepeatReportSetting] ([ReportClass], [RepeatReportClass], [Icd9Code], [Icd10Code], [Enabled]) 
VALUES (9, 31, N'35.62', NULL, 1)
--甲状腺结节（手术治疗）·TN
INSERT [dbo].[D_RepeatReportSetting] ([ReportClass], [RepeatReportClass], [Icd9Code], [Icd10Code], [Enabled]) 
VALUES (9,37, N'06.2、06.3、06.4、06.5', NULL, 1)
--复杂性视网膜脱离（手术治疗）·RD
INSERT [dbo].[D_RepeatReportSetting] ([ReportClass], [RepeatReportClass], [Icd9Code], [Icd10Code], [Enabled]) 
VALUES (9, 38, N'13.19、13.3、13.41、13.42、13.43、13.59、13.64、13.65、13.69、13.70、13.71、13.72、13.8、13.90', NULL, 1)
--原发性急性闭角型青光眼（手术治疗）·PACG
INSERT [dbo].[D_RepeatReportSetting] ([ReportClass], [RepeatReportClass], [Icd9Code], [Icd10Code], [Enabled]) 
VALUES (9, 39, N'13.19、13.3、13.41、13.59、13.70、13.71、13.90', NULL, 1)
--发育性髋关节发育不良·DDH
INSERT [dbo].[D_RepeatReportSetting] ([ReportClass], [RepeatReportClass], [Icd9Code], [Icd10Code], [Enabled]) 
VALUES (9, 52, N'76.94、79.8', NULL, 1)
END

--DVT与各病种重复
if not exists(select * from D_RepeatReportSetting where ReportClass =12 and RepeatReportClass>7)
BEGIN
--二尖瓣置换术·MVR
INSERT [dbo].[D_RepeatReportSetting] ([ReportClass], [RepeatReportClass], [Icd9Code], [Icd10Code], [Enabled]) 
VALUES (12, 24, N'35.0、35.2', NULL, 1)
--主动脉瓣置换术·AVR
INSERT [dbo].[D_RepeatReportSetting] ([ReportClass], [RepeatReportClass], [Icd9Code], [Icd10Code], [Enabled]) 
VALUES (12, 29, N'35.0、35.2', NULL, 1)
--胃癌（手术治疗）·GC
INSERT [dbo].[D_RepeatReportSetting] ([ReportClass], [RepeatReportClass], [Icd9Code], [Icd10Code], [Enabled]) 
VALUES (12, 33, N'43.6、43.7、43.8、43.9', NULL, 1)
--子宫肌瘤（手术治疗）·UM
INSERT [dbo].[D_RepeatReportSetting] ([ReportClass], [RepeatReportClass], [Icd9Code], [Icd10Code], [Enabled]) 
VALUES (12, 34, N'68.2', NULL, 1)
--宫颈癌（手术治疗）·CC
INSERT [dbo].[D_RepeatReportSetting] ([ReportClass], [RepeatReportClass], [Icd9Code], [Icd10Code], [Enabled]) 
VALUES (12, 42, N'68.3、68.4、68.5、68.6、68.7', NULL, 1)
--结肠癌（手术治疗）·CoC
INSERT [dbo].[D_RepeatReportSetting] ([ReportClass], [RepeatReportClass], [Icd9Code], [Icd10Code], [Enabled]) 
VALUES (12, 43, N'45.7', NULL, 1)
END

--VTE与各病种重复
if not exists(select * from D_RepeatReportSetting where ReportClass =36 and RepeatReportClass=16)
BEGIN
--肺癌（手术治疗）·LC
INSERT [dbo].[D_RepeatReportSetting] ([ReportClass], [RepeatReportClass], [Icd9Code], [Icd10Code], [Enabled]) 
VALUES (36, 16, NULL, N'C34.0、C34.1、C34.2、C34.3、C34.8、C34.9', 1)
--脑膜瘤（初发手术治疗）·MEN
INSERT [dbo].[D_RepeatReportSetting] ([ReportClass], [RepeatReportClass], [Icd9Code], [Icd10Code], [Enabled]) 
VALUES (36, 25, NULL, N'C70.0、C70.9', 1)
--胶质瘤（初发，手术治疗）·GLI
INSERT [dbo].[D_RepeatReportSetting] ([ReportClass], [RepeatReportClass], [Icd9Code], [Icd10Code], [Enabled]) 
VALUES (36, 27, NULL, N'C71.0、C71.1、C71.2、C71.3、C71.4、C71.5、C71.6、C71.7、C71.8、C71.9', 1)
--垂体腺瘤（初发，手术治疗）·PA
INSERT [dbo].[D_RepeatReportSetting] ([ReportClass], [RepeatReportClass], [Icd9Code], [Icd10Code], [Enabled]) 
VALUES (36, 28, NULL, N'C75.1', 1)
--胃癌（手术治疗）·GC
INSERT [dbo].[D_RepeatReportSetting] ([ReportClass], [RepeatReportClass], [Icd9Code], [Icd10Code], [Enabled]) 
VALUES (36, 33, NULL, N'C16.0、C16.1、C16.2、C16.3、C16.4、C16.5、C16.6、C16.8、C16.9', 1)
--舌鳞状细胞癌（手术治疗）·TSCC
INSERT [dbo].[D_RepeatReportSetting] ([ReportClass], [RepeatReportClass], [Icd9Code], [Icd10Code], [Enabled]) 
VALUES (36, 40, NULL, N'C01.x、C02.0、C02.1、C02.2、C02.3、C02.4、C02.8、C02.9', 1)
--宫颈癌（手术治疗）·CC
INSERT [dbo].[D_RepeatReportSetting] ([ReportClass], [RepeatReportClass], [Icd9Code], [Icd10Code], [Enabled]) 
VALUES (36, 42, NULL, N'C53.0、C53.1、C53.8、C53.9', 1)
--结肠癌（手术治疗）·CoC
INSERT [dbo].[D_RepeatReportSetting] ([ReportClass], [RepeatReportClass], [Icd9Code], [Icd10Code], [Enabled]) 
VALUES (36, 43, NULL, N'C18.0、C18.1、C18.2、C18.3、C18.4、C18.5、C18.6、C18.7、C18.8、C18.9', 1)
--儿童急性淋巴细胞白血病（初始诱导化疗）·ALL
INSERT [dbo].[D_RepeatReportSetting] ([ReportClass], [RepeatReportClass], [Icd9Code], [Icd10Code], [Enabled]) 
VALUES (36, 48, NULL, N'C91.0', 1)
--儿童急性早幼粒细胞白血病（初始化疗）·APL
INSERT [dbo].[D_RepeatReportSetting] ([ReportClass], [RepeatReportClass], [Icd9Code], [Icd10Code], [Enabled]) 
VALUES (36, 49, NULL, N'C92.4', 1)
END

--HF与各病种重复
if not exists(select * from D_RepeatReportSetting where ReportClass =2)
BEGIN
--急性心肌梗死（ST 段抬高型，首次住院）·STEMI
INSERT [dbo].[D_RepeatReportSetting] ([ReportClass], [RepeatReportClass], [Icd9Code], [Icd10Code], [Enabled]) 
VALUES (2, 1, NULL, N'I21.0、I21.1、I21.2、I21.3', 1)
END

--CS与各病种重复
if not exists(select * from D_RepeatReportSetting where ReportClass =10)
BEGIN
--感染分娩母婴阻断·HBV
INSERT [dbo].[D_RepeatReportSetting] ([ReportClass], [RepeatReportClass], [Icd9Code], [Icd10Code], [Enabled]) 
VALUES (10, 50, N'74.0、74.1、74.2、74.4、74.99', N'Z37', 1)
END

--AVR与各病种重复
if not exists(select * from D_RepeatReportSetting where ReportClass =29)
BEGIN
--二尖瓣置换术·MVR
INSERT [dbo].[D_RepeatReportSetting] ([ReportClass], [RepeatReportClass], [Icd9Code], [Icd10Code], [Enabled]) 
VALUES (29, 24, N'35.0、35.2',NULL, 1)
END

--VTE与pip
if not exists(select * from D_RepeatReportSetting where ReportClass =36 and RepeatReportClass=9)
BEGIN
--pip
INSERT [dbo].[D_RepeatReportSetting] ([ReportClass], [RepeatReportClass], [Icd9Code], [Icd10Code], [Enabled]) 
VALUES (36, 9, N'06.2~06.5、80.6、13.0~13.9、17.11~17.13、17.21~17.24、53.00~53.17、85.2~85.4、38.1、81.1、01.24、80.50~80.59、03.53、21.72、76.72~76.79、79.31~79.39、76.94、79.8、78.5~78.6、83.13~83.14、62.0~62.9、64.0~64.4、35.62、35.61、74.0、74.1、74.2、74.4、74.99', NULL, 1)
END

--reportleak 添加RepeatReportClass 
if not exists(select * from syscolumns where id=object_id('Report_Leak') and name='RepeatReportClass') 
BEGIN
EXEC dbo.sp_executesql @statement = N'alter table Report_Leak add RepeatReportClass nvarchar(max) NULL'
END

--[Q_Quanxian]表添加分析图表权限
if not exists (select * from [Q_Quanxian] where ID = 25)
begin
SET IDENTITY_INSERT [dbo].[Q_Quanxian] ON 
INSERT [dbo].[Q_Quanxian] ([Id], [QuanxianName], [Sort]) VALUES (25, N'分析图表', 25)
SET IDENTITY_INSERT [dbo].[Q_Quanxian] OFF 
end

--科室表增加根目录字段
if not exists(select * from syscolumns where id=object_id('Report_UserDept') and name='FullPath') 
BEGIN
EXEC dbo.sp_executesql @statement = N'alter table Report_UserDept add ParentPath nvarchar(max) NULL'
EXEC dbo.sp_executesql @statement = N'alter table Report_UserDept add FullPath nvarchar(max) NULL'
EXEC dbo.sp_executesql @statement = N'alter table Report_UserDept add FullName nvarchar(max) NULL'
END

--pip与BC
if not exists(select * from D_RepeatReportSetting where ReportClass =9 and RepeatReportClass=17)
BEGIN
--乳腺癌（手术治疗）·BC
INSERT [dbo].[D_RepeatReportSetting] ([ReportClass], [RepeatReportClass], [Icd9Code], [Icd10Code], [Enabled]) 
VALUES (9, 17, N'85.2、85.3、85.4', NULL, 1)
END

--系统属性设置：对接失败数据是否自动退回填报员设置
set Identity_insert [dbo].[Report_DistributeDept] ON
if not exists (select * from [Report_DistributeDept] where ID = 27)
INSERT [dbo].[Report_DistributeDept] ([ID] ,[DistributeDeptName] ,[IsStart] ,[HasValue]) VALUES (27, N'对接失败数据是否自动退回填报员', 0, NULL)
set Identity_insert [dbo].[Report_DistributeDept] OFF

--增加 D_MedicalRecord 扩展属性字段
if not exists(select * from syscolumns where id=object_id('D_MedicalRecord') and name='ExtFields') 
BEGIN
	EXEC dbo.sp_executesql @statement = N'alter table D_MedicalRecord add ExtFields nvarchar(max) NULL'
END

--漏报表提取率相关字段
if not exists(select * from syscolumns where id=object_id('Report_Leak') and name='filedNum') 
BEGIN
	EXEC dbo.sp_executesql @statement = N'alter table Report_Leak add FiledNum int NULL'
	EXEC dbo.sp_executesql @statement = N'alter table Report_Leak add NoFilledNum int NULL'
	EXEC dbo.sp_executesql @statement = N'alter table Report_Leak add FiledRate float NULL'
END

--增加 DataBaseAutoBak 分钟字段
if not exists(select * from syscolumns where id=object_id('DataBaseAutoBak') and name='AutoDbBakAtMinute') 
BEGIN
	EXEC dbo.sp_executesql @statement = N'alter table DataBaseAutoBak add AutoDbBakAtMinute int default 0'
END

--添加回收站权限
if not exists (select * from [Q_Quanxian] where ID = 27)
begin
SET IDENTITY_INSERT [dbo].[Q_Quanxian] ON 
INSERT [dbo].[Q_Quanxian] ([Id], [QuanxianName], [Sort]) VALUES (27, N'回收站', 27)
SET IDENTITY_INSERT [dbo].[Q_Quanxian] OFF 
end

--D_MedicalRecord 卫健委对接code
if not exists(select * from syscolumns where id=object_id('D_MedicalRecord') and name='Ncis_JoinCode') 
BEGIN
	EXEC dbo.sp_executesql @statement = N'alter table D_MedicalRecord add Ncis_JoinCode int NULL'
END

--系统属性设置：账号禁用设置
set Identity_insert [dbo].[Report_DistributeDept] ON
if not exists (select * from [Report_DistributeDept] where ID = 28)
INSERT [dbo].[Report_DistributeDept] ([ID] ,[DistributeDeptName] ,[IsStart] ,[HasValue]) VALUES (28, N'账号禁用设置', 0, '5')
set Identity_insert [dbo].[Report_DistributeDept] OFF

--增加 Q_UserProfile LoginDate：登录时间 UserLoginFailNum：登录失败次数
if not exists(select * from syscolumns where id=object_id('Q_UserProfile') and name='LoginDate') 
BEGIN
	EXEC dbo.sp_executesql @statement = N'alter table Q_UserProfile add LoginDate datetime NULL'
	EXEC dbo.sp_executesql @statement = N'alter table Q_UserProfile add UserLoginFailNum int NULL'
END


--增加Report_LeakLog LeakId的索引
if not exists( select * from sysindexes where id=object_id('Report_LeakLog') and name='NonClusteredIndex-LeakID')
begin
CREATE NONCLUSTERED INDEX [NonClusteredIndex-LeakID] ON [dbo].[Report_LeakLog]
(
	[LeakId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
end 

--系统属性设置：是否启用时间粘贴设置
set Identity_insert [dbo].[Report_DistributeDept] ON
if not exists (select * from [Report_DistributeDept] where ID = 29)
INSERT [dbo].[Report_DistributeDept] ([ID] ,[DistributeDeptName] ,[IsStart] ,[HasValue]) VALUES (29, N'时间粘贴设置', 0, NULL)
set Identity_insert [dbo].[Report_DistributeDept] OFF

--系统属性设置：数据抽取是否覆盖已填写项
set Identity_insert [dbo].[Report_DistributeDept] ON
if not exists (select * from [Report_DistributeDept] where ID = 30)
INSERT [dbo].[Report_DistributeDept] ([ID] ,[DistributeDeptName] ,[IsStart] ,[HasValue]) VALUES (30, N'数据抽取是否覆盖已填写项', 0, NULL)
set Identity_insert [dbo].[Report_DistributeDept] OFF

--自动抽取应上报数据任务
if not exists (select * from [DataBaseAutoBak] where ID = 7)
exec dbo.sp_executesql @statement = N'
SET IDENTITY_INSERT [dbo].[DataBaseAutoBak] ON 
INSERT [dbo].[DataBaseAutoBak] ([Id], [AutoDbBakEnabled], [AutoDbBakEveryDay], [AutoDbBakAtHour],[AutoDbBakAtMinute]) VALUES (7, 0, 1, 0, 0) 
SET IDENTITY_INSERT [dbo].[DataBaseAutoBak] OFF
'