1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265 | USE [HRCM]
GO
/****** Object: StoredProcedure [dbo].[NLP_InsertObgDxAnnotationByDocument] Script Date: 06/21/2018 10:58:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: ASrinivas
-- Create date: 06/06/2018
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[NLP_InsertObgDxAnnotationByDocument]
@Disorder varchar(100),
@Cause varchar(100),
@Site varchar(100),
@Site1 varchar(100),
@Site2 varchar(100),
@Manifestation varchar(100),
@Type varchar(100),
@AnnotId int,
@DocumentId int,
@Content varchar(5000),
@Stage varchar(20),
@Tconcept1 varchar(50),
@Tconcept2 varchar(50),
@Finding varchar(50),
@History varchar(50),
@StartId int,
@EndId int,
@RuleName varchar(50),
@AccountNumber varchar(20),
@DocumentType varchar(20),
@Neonatal tinyint,
@Gender tinyint,
@SectionHeader varchar(200),
@Pstatus tinyint,
@Pstatus1 tinyint,
@Codable varchar(2)
AS
declare @code1 varchar(20)
declare @code2 varchar(20)
declare @code3 varchar(20)
declare @disorderId int
declare @causeId int
declare @siteId int
declare @site1Id int
declare @site2Id int
declare @manifestationId int
declare @tconcept1Id int
declare @tconcept2Id int
declare @stageId int
declare @typeId int
declare @NCauseId int
declare @dischdate datetime
BEGIN
set @code1 = null
set @code2 = null
set @code3 = null
set @causeId = null
set @siteid = null
set @site1Id = null
set @site2Id = null
set @manifestationId = null
set @tconcept1Id=null
set @tconcept2Id=null
set @stageId=null
set @typeId=null
set @NCauseId=null
set @dischdate=null
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Begin Try
insert into NLP_DxAnnotation (AnnotationId,AccountNumber,DocumentId,Disorder,Cause,Manifestation,Site,Site1,Site2,TConcept1,TConcept2,Type,Stage)
values(@annotId,@AccountNumber,@DocumentId,@disorder,@cause,@manifestation,@site,@site1,@site2,@tconcept1,@tconcept2,@type,@stage)
--Max Combination Query i.e cause and site and manifestation
Select top 1 @disorderId = disorderid from MasterHRCM.dbo.KB_Disorder where disorder=@disorder and Status=0
Select top 1 @typeId = TypeId from MasterHRCM.dbo.KB_Type where Type=@type and Status=0
if (@typeId!=13 and not exists( select 1 from MasterHRCM.dbo.KB_DxCode where DisorderId = @disorderId and Status !=1 and Type=@typeId))
set @typeId=13
select @dischdate = isnull(DischargeDate,getdate()) from HIM_Visit where AccountNumber = @AccountNumber
select ic.* into #DisIcds from MasterHRCM.dbo.KB_DxCode ic
inner join MasterHRCM.dbo.CMS_DxCodeDesc d on ic.Icdcode4 = d.ICD10Code
where DisorderId = @disorderId and ic.Status not in (1) and d.Status = 1
and Neonatal = @Neonatal and Gender in(0, @Gender) and Type=@typeId and history=@history
and @dischdate between d.EffectiveYearFrom and d.EffectiveYearTo
Select top 1 @causeId = i.CauseId from MasterHRCM.dbo.KB_Cause i
inner join #DisIcds d on i.CauseId = d.causeid where cause=ISNULL(@cause,'Blank') and i.Status=0-- and d.status!=1
Select top 1 @siteId = s.SiteId from MasterHRCM.dbo.KB_Site s
inner join #DisIcds d on s.siteid = d.siteid where site=ISNULL(@site,'Blank') and s.Status=0-- and d.status!=1
Select top 1 @site1Id = s1.Site1Id from MasterHRCM.dbo.KB_Site1 s1
inner join #DisIcds d on s1.Site1Id = d.site1id where Site=ISNULL(@site1,'Blank') and s1.Status=0-- and d.status!=1
Select top 1 @site2Id = s2.Site2Id from MasterHRCM.dbo.KB_Site2 s2
inner join #DisIcds d on s2.Site2Id = d.site2id where Site=ISNULL(@site2,'Blank') and s2.Status=0-- and d.status!=1
Select top 1 @manifestationId = ManifestationId from MasterHRCM.dbo.KB_Manifestation where manifestation=ISNULL(@manifestation,'Blank') and Status=0
select @stageId=stage from #DisIcds where stage=@stage
Select top 1 @tconcept1Id = t1.TConcept1Id from MasterHRCM.dbo.KB_TemporalConcept1 t1
inner join #DisIcds d on t1.TConcept1Id = d.temporalconcept1 where tconcept1=@tconcept1 and t1.Status=0-- and d.status!=1
Select top 1 @tconcept2Id = t2.TConcept2Id from MasterHRCM.dbo.KB_TemporalConcept2 t2
inner join #DisIcds d on t2.TConcept2Id = d.temporalconcept2 where TConcept2=ISNULL(@tconcept2,'Blank') and t2.Status=0-- and d.status!=1
-- Need to check gender and nenonatal here
--DisorderId gets NULL what will happen?
if (@causeId is null)
set @causeId = 0
if(@siteId is null)
set @siteId = 0
if(@site1Id is null)
set @site1Id = 0
if(@site2Id is null)
set @site2Id = 0
if(@stageId is null)
set @stage = 9
if(@manifestationId is null)
set @manifestationId = 0
if (@tconcept1Id is null)
set @tconcept1Id = 9
if (@tconcept2Id is null)
set @tconcept2Id = 0
select top 1 @code1=icdcode4,@code2=icdcode5,@code3=icdcode6 from #DisIcds
where Disorderid=@disorderId and causeid=@causeId and siteid=@siteId
and site1id=@site1Id and site2id=@site2Id and
ManifestationId = @manifestationId and stage = @stage and
temporalconcept1 = @tconcept1Id and temporalconcept2 = @tconcept2Id
if(@Code1 is not null)
exec NLP_InsertDxCodeEvidence @AccountNumber,@DocumentId,@code1,@Disorder,@Cause,@Site,@Site1,@Site2,@Manifestation,@Content,@StartId,@EndId
if(@code1 is null and @siteId!=0) --site Only
begin
print 's and s1- and s2'
select top 1 @code1=icdcode4,@code2=icdcode5,@code3=icdcode6 from #DisIcds
where Disorderid=@disorderId and causeid=0 and siteid=ISNULL(@siteId,0)
and site1id=0 and site2id=ISNULL(@site2Id,0) and ManifestationId = ISNULL(@manifestationId,0)
and stage=@stage and TemporalConcept1=@tconcept1Id and temporalconcept2=ISNULL(@tconcept2Id,0)
if(@Code1 is not null)
exec NLP_InsertDxCodeEvidence @AccountNumber,@DocumentId,@code1,@Disorder,@Cause,@Site,@Site1,@Site2,@Manifestation,@Content,@StartId,@EndId
--NLP_insertDxCodeEvidance
if(@code1 is null)
begin
print 's and s1- and s2-'
select top 1 @code1=icdcode4,@code2=icdcode5,@code3=icdcode6 from #DisIcds
where Disorderid=@disorderId and causeid=0 and siteid=ISNULL(@siteId,0) and site1id=0
and site2id=0 and ManifestationId = ISNULL(@manifestationId,0) and stage=@stage
and TemporalConcept1=@tconcept1Id and temporalconcept2=ISNULL(@tconcept2Id,0)
if(@Code1 is not null)
exec NLP_InsertDxCodeEvidence @AccountNumber,@DocumentId,@code1,@Disorder,@Cause,@Site,@Site1,@Site2,@Manifestation,@Content,@StartId,@EndId
end
end
if(@code1 is null) --disorder Only
begin
print 'D'
select top 1 @code1=icdcode4,@code2=icdcode5,@code3=icdcode6 from #DisIcds
where Disorderid=@disorderId and causeid=0 and SiteId=0 and Site1Id=0 and Site2Id=0
and ManifestationId=0 and stage=@stage and TemporalConcept1=@tconcept1Id
and temporalconcept2=0
if(@Code1 is not null)
exec NLP_InsertDxCodeEvidence @AccountNumber,@DocumentId,@code1,@Disorder,@Cause,@Site,@Site1,@Site2,@Manifestation,@Content,@StartId,@EndId
--StartHere
if(@code1 is not null and @manifestationId!=0)
begin
declare @mstartId int
declare @mendId int
declare @mcode1 varchar(50)
declare @mcode2 varchar(50)
declare @mcode3 varchar(50)
declare @manfestDisId int
declare @indexId int
declare @DisIndexId int
Select top 1 @manfestDisId = disorderid from MasterHRCM.dbo.KB_Disorder where disorder=@manifestation and Status=0
select top 1 @mcode1=icdcode4,@mcode2=IcdCode5,@mcode3=IcdCode6 from MasterHRCM.dbo.KB_DxCode ic
inner join MasterHRCM.dbo.CMS_DxCodeDesc d on ic.Icdcode4 = d.ICD10Code
where DisorderId=@manfestDisId and CauseId=0 and SiteId=0 and Site1Id=0 and Site2Id=0 and
ManifestationId=0 and stage=@stage and type=@typeId and temporalconcept1=@tconcept1Id
and temporalconcept2=0 and ic.Status != 1 and d.Status = 1 and history=@history
and neonatal=@Neonatal and @dischdate between d.EffectiveYearFrom and d.EffectiveYearTo
if(@mcode1 is not null)
begin
select @indexId=CHARINDEX(@manifestation, @content)
select @DisIndexId=CHARINDEX(@disorder, @content)
set @mstartId=@startId+@indexId
set @mendId = @mstartId+LEN(@manifestation)
set @endId=@startId+@DisIndexId+LEN(@disorder)
---Insereting Manifestattion as DisorderId in Tempdisease
print 'Manifestation as Disorder'
insert into NLP_DxCodeByDocument (AccountNumber, DocumentId, DxCode, AnnotationString,Poa,SectionHeader,Finding,History,UserName,AnnotationId,StartId, EndId,Rstatus,DxCodeSource,Codable)
values(@AccountNumber,@DocumentId,@mcode1,@manifestation,'Y',@sectionHeader,@finding,@history,'KPAI',@annotId,@mstartId,@mendId,0,@ruleName,@Codable)
exec NLP_InsertDxCodeEvidence @AccountNumber,@DocumentId,@mcode1,@Disorder,@Cause,@Site,@Site1,@Site2,@Manifestation,@Content,@StartId,@EndId
if(@mcode2 is not null and @mcode2!='')
begin
insert into NLP_DxCodeByDocument (AccountNumber, DocumentId, DxCode, AnnotationString,Poa,SectionHeader,Finding,History,UserName,AnnotationId,StartId, EndId,Rstatus,DxCodeSource,Codable)
values(@AccountNumber,@DocumentId,@mcode2,@manifestation,'Y',@sectionHeader,@finding,@history,'KPAI',@annotId,@mstartId,@mendId,0,@ruleName,@Codable)
exec NLP_InsertDxCodeEvidence @AccountNumber,@DocumentId,@mcode2,@Disorder,@Cause,@Site,@Site1,@Site2,@Manifestation,@Content,@StartId,@EndId
end
end
end
end
if (@disorderid is null)
begin
print 'Entry in disorder table only'
select top 1 @disorderId=disorderid from MasterHRCM.dbo.KB_Disorder where disorder=@disorder
insert into NLP_DxCodeByDocument (AccountNumber, DocumentId, DxCode, AnnotationString,Poa,SectionHeader,Finding,History,UserName,AnnotationId,StartId, EndId,Rstatus,DxCodeSource,Codable)
values(@AccountNumber,@DocumentId,'unknown',@content,'Y',@sectionHeader,@finding,@history,'KPAI',@annotId,@startId,@endId,0,@ruleName,@Codable)
end
else
if(@code1 is not null and @code1!='')
begin
print 'Disorder id existed in icdcode1'
insert into NLP_DxCodeByDocument (AccountNumber, DocumentId, DxCode, AnnotationString,Poa,SectionHeader,Finding,History,UserName,AnnotationId,StartId, EndId,Rstatus,DxCodeSource,Codable)
values(@AccountNumber,@DocumentId,@code1,@content,'Y',@sectionHeader,@finding,@history,'KPAI',@annotId,@startId,@endId,0,@ruleName,@Codable)
exec NLP_InsertDxCodeEvidence @AccountNumber,@DocumentId,@code1,@Disorder,@Cause,@Site,@Site1,@Site2,@Manifestation,@Content,@StartId,@EndId
end
if(@code2 is not null and @code2!='')
begin
insert into NLP_DxCodeByDocument (AccountNumber, DocumentId, DxCode, AnnotationString,Poa,SectionHeader,Finding,History,UserName,AnnotationId,StartId, EndId,Rstatus,DxCodeSource,Codable)
values(@AccountNumber,@DocumentId,@code2,@content,'Y',@sectionHeader,@finding,@history,'KPAI',@annotId,@startId,@endId,0,@ruleName,@Codable)
exec NLP_InsertDxCodeEvidence @AccountNumber,@DocumentId,@code2,@Disorder,@Cause,@Site,@Site1,@Site2,@Manifestation,@Content,@StartId,@EndId
end
drop table #DisIcds
if(@finding='positive')
begin
exec NLP_InsertObgComplication @Pstatus,@Pstatus1,@AccountNumber,@DocumentId,@DocumentType,@code1
end
End Try
Begin Catch
Exec usp_GetDBErrorInfo '<Input-Params>','NLP'
End Catch
END
GO
|