123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178 |
-
- 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
|