sdqmupdatge/UpdateSql/6.0/D_Insert.sql


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

--新增病种EAR
if not exists(select * from D_Disease_Category where DCID=53)
BEGIN
SET IDENTITY_INSERT [dbo].[D_Disease_Category] ON 
INSERT [dbo].[D_Disease_Category] ([DCID], [DiseaseType], [DiseaseType_API], [DiseaseSystem], [DiseaseDescription], [SortID], [Enabled], [Num], [IsOpen], [DiseaseContent], [IsLeakOpen], [ByMonth_StartYear], [ByMonth_StartMonth], [ByMonth_CurrentYear], [ByMonth_CurrentMonth], [ByDay_StartDate], [ByDay_CurrentDate], [ByDay_LeakDay], [LastLeakDate], [HisDeptList], [HisDeptEnabled], [LeakExtractRuleEnabled], [LeakExtractRule], [IsNcisAutoOpen], [SystemDescription], [IsNcisChange], [DiseaseDifficulty]) VALUES (53, N'EAR', N'EAR', 4, N'主动脉腔内修复术', 53, 1, N'form-61', 1, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 1, 1, 0, NULL, NULL, NULL)
SET IDENTITY_INSERT [dbo].[D_Disease_Category] OFF 
END

--公告添加查看用户
if not exists(select * from syscolumns where id=object_id('Report_InterAnnounce') and name='ToUserIds') 
EXEC dbo.sp_executesql @statement = N'alter table Report_InterAnnounce add ToUserIds [nvarchar](max) NULL'

--治疗模式 a:门诊治疗 b:住院治疗
if not exists(select * from syscolumns where id=object_id('Report_Leak') and name='OIT_0_2_1') 
EXEC dbo.sp_executesql @statement = N'alter table Report_Leak add OIT_0_2_1 [nvarchar](max) NULL'

--新增病种
if not exists(select * from D_Disease_Category where DCID=54)
BEGIN
SET IDENTITY_INSERT [dbo].[D_Disease_Category] ON 
INSERT [dbo].[D_Disease_Category] ([DCID], [DiseaseType], [DiseaseType_API], [DiseaseSystem], [DiseaseDescription], [SortID], [Enabled], [Num], [IsOpen], [DiseaseContent], [IsLeakOpen], [ByMonth_StartYear], [ByMonth_StartMonth], [ByMonth_CurrentYear], [ByMonth_CurrentMonth], [ByDay_StartDate], [ByDay_CurrentDate], [ByDay_LeakDay], [LastLeakDate], [HisDeptList], [HisDeptEnabled], [LeakExtractRuleEnabled], [LeakExtractRule], [IsNcisAutoOpen], [SystemDescription], [IsNcisChange], [DiseaseDifficulty]) VALUES (54, N'APTE', N'APTE', 1, N'急性肺血栓栓塞症', 54, 1, N'form-62', 1, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 1, 2, 0, NULL, NULL, NULL)
SET IDENTITY_INSERT [dbo].[D_Disease_Category] OFF 
END

--更新admin病种，
if not exists(select * from Q_UserProfile where UserName='admin'and CID='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,53,54')
update Q_UserProfile
set CID='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,53,54'
where UserName='admin'

--EAR APTE 无需上报理由
if not exists(select * from Report_NoReportNote where ReportClass=53)
BEGIN
INSERT [dbo].[Report_NoReportNote] ( [ReportClass], [Note], [Selected]) VALUES (53, N'参与临床药物与器械试验', 1)
INSERT [dbo].[Report_NoReportNote] ( [ReportClass], [Note], [Selected]) VALUES (53, N'本次住院时间超过90天', 1)
INSERT [dbo].[Report_NoReportNote] ( [ReportClass], [Note], [Selected]) VALUES (53, N'同一疾病30日内重复入院', 1)

INSERT [dbo].[Report_NoReportNote] ( [ReportClass], [Note], [Selected]) VALUES (54, N'由外院住院诊疗后转入本院的病例', 1)
INSERT [dbo].[Report_NoReportNote] ( [ReportClass], [Note], [Selected]) VALUES (54, N'18岁以下的病例', 1)
INSERT [dbo].[Report_NoReportNote] ( [ReportClass], [Note], [Selected]) VALUES (54, N'同一疾病31日内重复入院', 1)
INSERT [dbo].[Report_NoReportNote] ( [ReportClass], [Note], [Selected]) VALUES (54, N'本次住院超过90天', 1)

END