sdqmupdatge/UpdateSql/5.8/D_Insert.sql

--漏报抽取任务表
if not exists(select * from sysobjects where id=object_id('D_ReportLeak_TaskSettings'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[D_ReportLeak_TaskSettings](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[TaskDay] [int] NOT NULL,
	[TaskHour] [int] NOT NULL,
	[TaskMinute] [int] NOT NULL,
	[TaskEnabled] [bit] NOT NULL,
	[StartDate] [datetime] NOT NULL,
	[CurrentDate] [datetime],
	[ExtractDays] [int] NOT NULL
 CONSTRAINT [PK_D_ReportLeak_TaskSettings] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] 
'
--新漏报任务设置数据迁移
insert into D_ReportLeak_TaskSettings(TaskDay,TaskHour,TaskMinute,TaskEnabled,StartDate,CurrentDate,ExtractDays )
select AutoDbBakEveryDay,AutoDbBakAtHour,AutoDbBakAtMinute,AutoDbBakEnabled,
(select top 1 value from Q_Options where Name='LeakStartDate'),
(select top 1 value from Q_Options where Name='LeakCurrentDate'),
(select top 1 value from Q_Options where Name='LeakLeakDay')
from DataBaseAutoBak where id=5

--删除原有旧漏报任务设置
delete from DataBaseAutoBak where id=5
delete from Q_Options where Name='LeakStartDate'
delete from Q_Options where Name='LeakCurrentDate'
delete from Q_Options where Name='LeakLeakDay'
End

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

--增加房颤和脑梗死，重复病例设置
if not exists(select * from D_RepeatReportSetting where ReportClass =32 and RepeatReportClass=6)
BEGIN
INSERT [dbo].[D_RepeatReportSetting] ([ReportClass], [RepeatReportClass], [Icd9Code], [Icd10Code], [Enabled]) VALUES (32, 6, NULL, NULL, 0)
END

--DVT和CS重复，优先CS
--DVT和EP重复，优先EP
--DVT和SEP重复，优先SEP
--STK和SEP出重复，优先SEP
--ICH和SEP重复，优先SEP
if not exists(select * from D_RepeatReportSetting where ReportClass =12 and RepeatReportClass=10)
BEGIN
INSERT [dbo].[D_RepeatReportSetting] ([ReportClass], [RepeatReportClass], [Icd9Code], [Icd10Code], [Enabled]) VALUES (12, 10, NULL, NULL, 0)
INSERT [dbo].[D_RepeatReportSetting] ([ReportClass], [RepeatReportClass], [Icd9Code], [Icd10Code], [Enabled]) VALUES (12, 20, NULL, NULL, 0)
INSERT [dbo].[D_RepeatReportSetting] ([ReportClass], [RepeatReportClass], [Icd9Code], [Icd10Code], [Enabled]) VALUES (12, 35, NULL, NULL, 0)
INSERT [dbo].[D_RepeatReportSetting] ([ReportClass], [RepeatReportClass], [Icd9Code], [Icd10Code], [Enabled]) VALUES (6, 35, NULL, NULL, 0)
INSERT [dbo].[D_RepeatReportSetting] ([ReportClass], [RepeatReportClass], [Icd9Code], [Icd10Code], [Enabled]) VALUES (26, 35, NULL, NULL, 0)
END

--增加慢阻肺和房颤重复病例除外
if not exists(select * from D_RepeatReportSetting where ReportClass =32 and RepeatReportClass=11)
BEGIN
INSERT [dbo].[D_RepeatReportSetting] ([ReportClass], [RepeatReportClass], [Icd9Code], [Icd10Code], [Enabled]) VALUES (32, 11, NULL, NULL, 0)
END

--是否允许填报人员填报数据时标记无需上报
if not exists(select * from Q_Options where Name='CanReportChangeStatus')
BEGIN
INSERT [dbo].[Q_Options] ([Name], [Value]) VALUES ('CanReportChangeStatus','True')
END

if not exists(select * from  Report_NoReportNote  where [ReportClass]=36 and [Note] = '同一疾病30日内重复入院')
BEGIN
INSERT [dbo].[Report_NoReportNote] ([ReportClass], [Note], [Selected])
VALUES (36, N'同一疾病30日内重复入院', 1)
END

--添加科室关系对应表
if not exists(select * from sysobjects where id=object_id('D_DeptDictMatch'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[D_DeptDictMatch](
	[ID] [int] IDENTITY(1,1)  NOT NULL,
	[UserDeptId] [int] NOT NULL,
	[CYDept] [nvarchar](max) NULL,
 CONSTRAINT [PK_D_DeptDict] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户科室id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'D_DeptDictMatch', @level2type=N'COLUMN',@level2name=N'UserDeptId'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'出院科室' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'D_DeptDictMatch', @level2type=N'COLUMN',@level2name=N'CYDept'

END

--血透腹透如果和房颤重复，优先上报血透腹透
if not exists(select * from D_RepeatReportSetting where ReportClass =32 and RepeatReportClass=44)
BEGIN
INSERT [dbo].[D_RepeatReportSetting] ([ReportClass], [RepeatReportClass], [Icd9Code], [Icd10Code], [Enabled]) VALUES (32, 44, NULL, NULL, 0)
INSERT [dbo].[D_RepeatReportSetting] ([ReportClass], [RepeatReportClass], [Icd9Code], [Icd10Code], [Enabled]) VALUES (32, 45, NULL, NULL, 0)
END

--不根据出院日期判断重复上报 终末期肾病血液透析·HD、终末期肾病腹膜透析·DPD
if not exists(select * from Q_Options where Name='IsRepeatNotByCYDate')
BEGIN
INSERT [dbo].[Q_Options] ([Name], [Value]) VALUES ('IsRepeatNotByCYDate','False')
END
--同一疾病30天内重复入院规则设置
if not exists(select * from Q_Options where Name='IsRepeatRY30Setting')
BEGIN
INSERT [dbo].[Q_Options] ([Name], [Value]) VALUES ('IsRepeatRY30Setting','1')
END
--VTE抽取数据时，在原有的抽取规则下，排除ICD9字段不为空的数据
if not exists(select * from Q_Options where Name='VTEExtractByIcd9Null')
BEGIN
INSERT [dbo].[Q_Options] ([Name], [Value]) VALUES ('VTEExtractByIcd9Null','False')
END

--增加下一次运行时间字段NextRun，缺省值null
if not exists(select * from syscolumns where id=object_id('DataBaseAutoBak') and name='NextRun') 
EXEC dbo.sp_executesql @statement = N'alter table DataBaseAutoBak add [NextRun] DateTime NULL'

if not exists(select * from syscolumns where id=object_id('D_ReportLeak_TaskSettings') and name='NextRun') 
EXEC dbo.sp_executesql @statement = N'alter table D_ReportLeak_TaskSettings add [NextRun] DateTime NULL'

IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[D_View_ReportStatsData]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[D_View_ReportStatsData]
AS
SELECT
       convert(int,ReportClass) as ReportClass
      ,[HNumber] as [Base_HNumber] --住院号
      ,[VisitId]  as  [Base_VisitId] --住院次数
      ,( case when [LYFS] in (''a'',''1'',''A'') then ''a''
              when [LYFS] in (''b'',''2'',''B'') then ''b''
              when [LYFS] in (''c'',''3'') then ''c''
              when [LYFS] in (''d'',''4'') then ''d''
              when [LYFS] in (''e'',''5'') then ''e''
              when [LYFS] in (''f'',''9'',''oth'') then ''oth''
          else [LYFS]  end) [Base_LYFS] --离院方式
      ,[RYDate] as [Base_RYDate]--入院时间
      ,[CYDate] as [Base_CYDate]--出院时间
      ,[BirthDate] as [Base_CSRQ]--出生日期
      ,Report_Leak.CYDept as [CYDept]--出院科室
      ,[ZGDoctor] as [ZGDoctor]--质控医生
	  ,[BGName] --上报医师
	  ,[DealReport]   -- 未报 
    ,[TotalFei] as [ZFY]--总费用   
    ,null [CM_2_1] --手术后并发症
    ,null as Add_TJTime --提交时间
	,[D_DeptDictMatch].UserDeptId as Add_OperDeptId --填报科室
	,null as Add_UserName --填报员
	,null as SBWorkDayCha --上报时间差
	,null as ReportLeak_ID  --数据管理与漏报关联关系
	,null as status --状态
  FROM [Report_Leak]  
   left outer join D_DeptDictMatch on D_DeptDictMatch.CYDept = Report_Leak.CYDept
    where DealReport in(0,2)and (IsDelete is null or IsDelete=0)
  union all
  
SELECT 
       ReportClass
      ,[Base_HNumber]  as [Base_HNumber]--住院号
      ,[Base_VisitId] as  [Base_VisitId]--住院次数
      ,( case when [Base_LYFS] in (''a'',''1'') then ''a''
              when [Base_LYFS] in (''b'',''2'') then ''b''
              when [Base_LYFS] in (''c'',''3'') then ''c''
              when [Base_LYFS] in (''d'',''4'') then ''d''
              when [Base_LYFS] in (''e'',''5'') then ''e''
              when [Base_LYFS] in (''f'',''9'',''oth'') then ''oth''
          else [Base_LYFS]  end) [Base_LYFS] --离院方式--离院方式      
      ,[Base_RYDate]as [Base_RYDate]--入院时间  
      ,(case 
	  when REPORTCLASS in(44,45) and [Base_CYDate] is null 
	   then Add_TJTime   -- HD、DPD提交时间
	   when REPORTCLASS=51 and [Base_CYDate] is not null then 
	   Base_CYDate --OIT 出院时间
	   when REPORTCLASS=51 and [Base_CYDate] is  null and JSON_VALUE(DataInfo,''$."CM-0-2-4-2"'') is  not null 
	   then  JSON_VALUE(DataInfo,''$."CM-0-2-4-2"'')  --OIT 填报页面出院时间
	   when REPORTCLASS=51 and [Base_CYDate] is  null  and (JSON_VALUE(DataInfo,''$."CM-0-2-4-2"'') is null  or JSON_VALUE(DataInfo,''$."CM-0-2-4-2"'') ='''' )
	   then Add_TJTime  --OIT提交时间
	  else  [Base_CYDate] end
	  )
	  as [Base_CYDate]--出院时间
	  ,[Base_CSRQ] as [Base_CSRQ]--出生日期
      ,null as [CYDept] --出院科室     
      ,[Base_ZGYSName] as [ZGDoctor]--质控医生
	  ,[Base_BGName] as  [BGName] --上报医生
      ,1 as [DealReport] --已报
      ,JSON_VALUE(DataInfo,''$."CM-6-1"'')  as [ZFY]--总费用  
      ,case  when ReportClass=10 then JSON_VALUE(DataInfo,''$."CS-6-1-1"'')
       when ReportClass=39 then JSON_VALUE(DataInfo,''$."PACG-7-1-1"'')
       else JSON_VALUE(DataInfo,''$."CM-2-1"'') 
       end as  [CM_2_1] --手术后并发症
      ,Add_TJTime --提交时间
	  ,Add_OperDeptId --填报科室
	  ,Add_UserName --填报员
	  ,SBWorkDayCha as SBWorkDayCha --上报时间差
      ,ReportLeak_ID  --数据管理与漏报关联关系
      ,Status --状态
  FROM [D_MedicalRecord]
  where Status<>0 and (BackUpFlag is null or BackUpFlag=0)
' 

if not exists(select * from syscolumns where id=object_id('D_Disease_Category') and name='DiseaseDifficulty') 
EXEC dbo.sp_executesql @statement = N'alter table D_Disease_Category add DiseaseDifficulty int NULL'

--添加 标记锁定 权限
if not exists (select * from [Q_Quanxian] where ID = 31)
begin
SET IDENTITY_INSERT [dbo].[Q_Quanxian] ON 
INSERT [dbo].[Q_Quanxian] ([Id], [QuanxianName], [Sort]) VALUES (31, N'标记锁定', 31)
SET IDENTITY_INSERT [dbo].[Q_Quanxian] OFF 
end

--D_MedicalRecord 添加锁定标记
if not exists(select * from syscolumns where id=object_id('D_MedicalRecord') and name='IsLock') 
EXEC dbo.sp_executesql @statement = N'alter table D_MedicalRecord add IsLock bit NULL'

--增加 sep和af重复优先上报af
if not exists(select * from D_RepeatReportSetting where ReportClass =35 and RepeatReportClass=32)
BEGIN
INSERT [dbo].[D_RepeatReportSetting] ([ReportClass], [RepeatReportClass], [Icd9Code], [Icd10Code], [Enabled]) VALUES (35, 32, NULL, NULL, 0)
END

--是否开启预抽取
if not exists(select * from Q_Options where Name='ReportLeakProExtract')
BEGIN
INSERT [dbo].[Q_Options] ([Name], [Value]) VALUES ('ReportLeakProExtract','True')
END

--添加疑似病种表
if not exists(select * from sysobjects where id=object_id('D_SuspectRecord'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[D_SuspectRecord](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ReportClass] [int] NOT NULL,
	[HNumber] [nvarchar](50) NOT NULL,
	[VisitId] [int] NULL,
	[PatientName] [nvarchar](50) NULL,
	[Sex] [nvarchar](50) NULL,
	[BirthDate] [datetime] NULL,
	[Age] [nvarchar](50) NULL,
	[RYDate] [datetime] NULL,
	[CYDate] [datetime] NULL,
	[DoctorName] [nvarchar](50) NULL,
	[RYDept] [nvarchar](50) NULL,
	[JudgCondition] [nvarchar](max) NULL,
	[QualityPoint] [nvarchar](max) NULL,
	[GainDate] [datetime] NOT NULL,
 CONSTRAINT [PK_D_SuspectReport] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'
END