· 7 years ago · Jan 29, 2019, 03:30 PM
1
2ALTER TRIGGER [dbo].[FormData_Identifier_IU]
3ON [dbo].[FormData] AFTER INSERT
4AS
5BEGIN
6SET NOCOUNT ON;
7DECLARE @IdentifierControlTypeId INT
8DECLARE @IdentifierRecordTypeId INT
9DECLARE @CurrentIdentifierValue VARCHAR(200)
10
11
12DECLARE @RecordTypeId INT, @FormId INT, @FormDataId INT
13DECLARE @IdentifierFlexField VARCHAR(100), @FormatString VARCHAR(2000)
14DECLARE @FormTemplateId INT, @LatestFormCreatedDate DATETIME, @TemplateFieldModifiedDate DATETIME
15DECLARE @IsReset BIT = 0
16DECLARE @SeqLength INT
17DECLARE @NextSeqLength INT
18DECLARE @Sql NVARCHAR (MAX)
19DECLARE @IdentifierValue VARCHAR(MAX), @ExistingSeq VARCHAR(100), @CurrentSequence VARCHAR(100)
20
21SELECT @IdentifierControlTypeId =
22 ControlTypeId FROM ControlTypes WHERE [Description] = 'Identifier' AND DateDeleted IS NULL
23
24
25DECLARE FormDataInsertCursor CURSOR FOR
26 SELECT RecordTypeId, FormId, FormDataId FROM Inserted
27
28OPEN FormDataInsertCursor
29FETCH NEXT FROM FormDataInsertCursor INTO @RecordTypeId, @FormId, @FormDataId
30
31WHILE (@@FETCH_STATUS = 0)
32BEGIN
33
34IF NullIF(@FormId, '') IS NOT NULL
35BEGIN
36
37 SELECT @FormTemplateId =
38 FormTemplateId FROM FormTemplateFieldDefinitionRecordTypes WITH (NOLOCK) WHERE RecordTypeId = @RecordTypeId
39
40 SELECT @IdentifierRecordTypeId =
41 RecordTypeId FROM FormtemplateFieldDefinitions WITH (NOLOCK) WHERE RecordtypeId IN(
42 SELECT RecordtypeId FROM FormTemplateFieldDefinitionRecordTypes WHERE FormTemplateId = @FormTemplateId
43 AND DateDeleted IS NULL) AND ControlTypeId = @IdentifierControlTypeId AND DateDeleted IS NULL
44
45
46 IF NullIF(@IdentifierRecordTypeId, '') IS NOT NULL
47 BEGIN
48
49 SELECT @IdentifierFlexField = FieldName, @FormatString = ControlTypeParams
50 FROM FormTemplateFieldDefinitions WITH (NOLOCK)
51 WHERE RecordTypeId = @IdentifierRecordTypeId AND ControlTypeId = @IdentifierControlTypeId AND DateDeleted IS NULL
52
53 SET @Sql = ''
54 SELECT @Sql += 'SELECT @CurrentIdentifierValue = ' + @IdentifierFlexField + ' FROM FormData WITH (NOLOCK)
55 WHERE RecordTypeId = '+ CAST (@IdentifierRecordTypeId AS VARCHAR) + '
56 AND FormId = '+ CAST(@FormId AS VARCHAR) + ' '
57
58
59 EXEC sp_executesql @Sql, N'@CurrentIdentifierValue VARCHAR(MAX) OUT', @CurrentIdentifierValue OUT
60
61
62
63 IF NULLIF(@CurrentIdentifierValue,'') IS NULL OR NULLIF(@CurrentIdentifierValue,'Auto') IS NULL
64 BEGIN
65
66 IF OBJECT_ID('tempdb..#FormatContents') IS NOT NULL
67 DROP TABLE #FormatContents
68
69 CREATE TABLE #FormatContents (
70 ID INT IDENTITY(1,1),
71 Name VARCHAR(150),
72 FormatFragment VARCHAR(150),
73 ReplacedValue VARCHAR(150)
74 )
75
76 SELECT @FormatString = REPLACE( REPLACE (@FormatString, '[{"Format":"','' ),'"}]', '')
77
78 INSERT INTO #FormatContents (FormatFragment, ReplacedValue)
79 SELECT '{' + T2 .X. value('.' , 'VARCHAR(50)' ) + '}' , T2 .X. value('.' , 'VARCHAR(50)' )
80 FROM
81 (
82 SELECT CAST(REPLACE(REPLACE((SELECT @FormatString FOR XML PATH('')), '{', '<X>'), '}', '</X>') AS XML).query('.')
83 ) AS T1( X)
84 CROSS APPLY T1. X.nodes ('/X/text()') as T2(X);
85
86
87 UPDATE #FormatContents SET Name = I.Category FROM #FormatContents F
88 JOIN IdentifierFormats I ON F.ReplacedValue collate database_default = I.[Format] collate database_default
89
90
91 /* Newly added Logic goes here */
92 DECLARE @ReplaceableFlexFieldrecordTypeId VARCHAR(100)
93 DECLARE @ReplaceableFlexField VARCHAR (150)
94
95
96 SELECT
97 @ReplaceableFlexField = XmlString.value('/Root[1]/Field[1]','VARCHAR(100)'),
98 @ReplaceableFlexFieldrecordTypeId= XmlString.value('/Root[1]/Field[2]','VARCHAR(100)')
99 FROM
100 (
101 SELECT CONVERT(XML,'<Root><Field>' + REPLACE(ReplacedValue, '-', '</Field><Field>') + '</Field></Root>') AS XmlString
102 FROM #FormatContents WHERE ReplacedValue LIKE 'FlexField%'
103 )T
104
105
106 IF CHARINDEX('FlexField', NullIF(@FormatString,'')) = 0
107 OR
108 (CHARINDEX('FlexField', NullIF(@FormatString,'')) <> 0 AND EXISTS( SELECT * FROM FormData WHERE FormId = @FormId AND recordTypeId = @ReplaceableFlexFieldrecordTypeId ) )
109 BEGIN
110
111 IF EXISTS( SELECT * FROM FormData WHERE FormId = @FormId AND recordTypeId = @IdentifierRecordTypeId)
112 BEGIN
113
114 SELECT @SeqLength = LEN(ReplacedValue) FROM #FormatContents WHERE LOWER(Name) = LOWER('SequenceNumber')
115
116 SELECT @LatestFormCreatedDate = MAX(DateCreated) FROM Forms WITH (NOLOCK) WHERE FormTemplateId = @FormTemplateId AND FormId <> @FormId AND DateDeleted IS NULL
117
118
119 -- Do not go through following loop if Seqlength is null i.e if no sequence number is needed
120 IF(IsNULL(@SeqLength,0) <> 0 )
121 BEGIN
122
123 SELECT @IsReset =
124 (CASE
125 WHEN ISNULL(MAX(ReplacedValue),'') = 'yyyy' AND YEAR(@LatestFormCreatedDate) <> YEAR(GETDATE()) THEN 1
126 WHEN ISNULL(MAX(ReplacedValue),'') = 'yyyyMM' AND CONVERT(VARCHAR(7), MAX(@LatestFormCreatedDate), 111) <> CONVERT(VARCHAR(7), GETDATE(), 111) THEN 1
127 WHEN ISNULL(MAX(ReplacedValue),'') = 'yyyyMMdd' AND CONVERT(VARCHAR(10), MAX(@LatestFormCreatedDate), 111) <> CONVERT(VARCHAR(10), GETDATE(), 111) THEN 1
128 WHEN ISNULL(MAX(ReplacedValue),'') = 'yyyyMMddHHmmss' AND CONVERT(VARCHAR(26), MAX(@LatestFormCreatedDate), 120) <> CONVERT(VARCHAR(26), GETDATE(), 120) THEN 1
129 WHEN ISNULL(MAX(ReplacedValue),'') = 'yyyyMMddHH' AND CONVERT(VARCHAR(13), MAX(@LatestFormCreatedDate), 120) <> CONVERT(VARCHAR(13), GETDATE(), 120) THEN 1
130 WHEN ISNULL(MAX(ReplacedValue),'') = 'yyyyMMddHHmm' AND CONVERT(VARCHAR(16), MAX(@LatestFormCreatedDate), 120) <> CONVERT(VARCHAR(16), GETDATE(), 120) THEN 1
131 END)
132 FROM #FormatContents
133 WHERE LOWER(Name) = LOWER('DateTime')
134
135 If ISNULL(@IsReset,0) <> 1
136 BEGIN
137
138 SET @Sql = 'SELECT @IdentifierValue = ' + @IdentifierFlexField + ' FROM FormData FD WITH (NOLOCK) JOIN Forms F WITH (NOLOCK) ON F.FormId = FD.FormId
139 WHERE RecordTypeId = '+ CAST (@IdentifierRecordTypeId AS VARCHAR) + '
140 AND F.DateDeleted IS NULL AND FD.DateCreated = (SELECT MAX(DateCreated) FROM FormData
141 WHERE RecordTypeId = ' + CAST(@IdentifierRecordTypeId AS VARCHAR) + ' AND DateDeleted IS NULL AND FormDataId <> ' + CAST(@FormDataId AS VARCHAR) + ' AND FormId <> ' + CAST(@FormId AS VARCHAR) + ' AND FormId IS NOT NULL)'
142
143 EXEC sp_executesql @Sql, N'@IdentifierValue VARCHAR(MAX) OUT', @IdentifierValue OUT
144
145
146 SELECT @ExistingSeq = RIGHT('0000000' + CAST(RIGHT(ISNULL(@IdentifierValue,'0'), @SeqLength) AS VARCHAR), @SeqLength)
147
148
149 IF(ISNUMERIC(@ExistingSeq) <> 0)
150 BEGIN
151 SET @Sql = ''
152 --Set @NextSeqLength to @SeqLength initially
153 SET @NextSeqLength = @SeqLength
154
155 IF( @ExistingSeq = RIGHT('0000000'+ '9999999',@SeqLength) )
156 BEGIN
157 SET @NextSeqLength = @SeqLength + 1
158 SET @Sql = ' UPDATE FormTemplateFieldDefinitions
159 SET ControlTypeParams = REPLACE(ControlTypeParams, ''{''+ RIGHT(''0000000'','+CAST(@SeqLength AS VARCHAR)+') +''}'', ''{''+ RIGHT(''0000000'', '+CAST(@NextSeqLength AS VARCHAR)+') +''}'' )
160 WHERE RecordTypeId = ' + CAST(@IdentifierRecordTypeId AS VARCHAR) + ' AND FieldName = ''' + @IdentifierFlexField + ''' AND DateDeleted IS NULL '
161 Print(@Sql)
162 EXEC sp_executesql @Sql
163 END
164
165 SELECT @CurrentSequence = RIGHT('0000000' + CAST(CAST(RTRIM(LTRIM(@ExistingSeq)) AS INT) + 1 AS VARCHAR), @NextSeqLength)
166 END
167 ELSE
168 BEGIN
169 SET @IsReset = 1;
170 END
171 END
172
173 IF(@IsReset = 1)
174 BEGIN
175 SELECT @CurrentSequence = RIGHT('0000001', @SeqLength)
176 END
177 UPDATE #FormatContents SET ReplacedValue = @CurrentSequence WHERE LOWER(Name) = LOWER('SequenceNumber')
178 END
179
180 --UPDATE #FormatContents SET ReplacedValue = FORMAT(GETDATE(), ReplacedValue) WHERE LOWER(Name) = LOWER('DateTime')
181 UPDATE #FormatContents SET ReplacedValue = (CASE ReplacedValue
182 WHEN 'yyyy' THEN CAST(YEAR(GETDATE()) AS VARCHAR)
183 WHEN 'yyyyMM' THEN CAST(YEAR(GETDATE ()) AS VARCHAR) + RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(MONTH, GETDATE())), 2)
184 WHEN 'yyyyMMdd' THEN CAST(YEAR(GETDATE()) AS VARCHAR) + RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(MONTH, GETDATE())), 2) + RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(DAY, GETDATE())), 2)
185 WHEN 'yyyyMMddHHmmss' THEN CAST(YEAR(GETDATE()) AS VARCHAR) + RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(MONTH, GETDATE())), 2) + RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(DAY, GETDATE())), 2) + Replace( CONVERT(VARCHAR (8), GETDATE(), 108), ':', '')
186 WHEN 'yyyyMMddHHmm' THEN CAST(YEAR(GETDATE()) AS VARCHAR) + RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(MONTH, GETDATE())), 2) + RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(DAY, GETDATE())), 2) + REPLACE(CONVERT( VARCHAR(5 ), GETDATE(), 108 ), ':', '')
187 WHEN 'yyyyMMddHH' THEN CAST(YEAR(GETDATE()) AS VARCHAR) + RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(MONTH, GETDATE())), 2) + RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(DAY, GETDATE())), 2) + REPLACE(CONVERT( VARCHAR(2), GETDATE(), 108 ), ':', '')
188 END)
189 WHERE LOWER(Name) = LOWER('DateTime')
190
191 SET @Sql = ''
192 ;WITH FlexFieldsCTE (ID, XmlString)
193 AS (
194 SELECT ID, CONVERT(XML,'<Root><Field>' + REPLACE(ReplacedValue, '-', '</Field><Field>') + '</Field></Root>') AS XmlString
195 FROM #FormatContents
196 WHERE ReplacedValue LIKE 'FlexField%'
197 )
198 , ParsedFlexFieldsCTE (ID, FlexFieldName, RecordTypeId)
199 AS (
200 SELECT ID,
201 XmlString.value('/Root[1]/Field[1]','VARCHAR(100)') AS FlexFieldName,
202 XmlString.value('/Root[1]/Field[2]','VARCHAR(100)') AS RecordTypeId
203 FROM FlexFieldsCTE
204 )
205
206 SELECT @Sql += 'UPDATE #FormatContents
207 SET ReplacedValue =
208 ISNULL(( SELECT (CASE
209 WHEN fd.DatasetId IS NULL then d.' + p.FlexFieldName + '
210 ELSE (SELECT di.Attribute1 FROM DatasetItems di WITH (NOLOCK) WHERE di.DatasetItemId = d.' + p.FlexFieldName + ' AND di.DateDeleted IS NULL)
211 END )
212 FROM FormData d WITH (NOLOCK)
213 JOIN FormTemplateFieldDefinitions fd WITH (NOLOCK) ON fd.recordTypeId = d.RecordTypeId
214 WHERE d.FormId = ' + CAST (@FormId AS VARCHAR ) + '
215 AND d.RecordTypeId = ' + CAST (p.RecordTypeId AS VARCHAR ) + '
216 AND fd.FieldName = ''' + p.FlexFieldName +'''
217 AND d.DateDeleted IS NULL AND fd.DateDeleted IS NULL ), '''')
218 WHERE ID = ' + CAST (f .ID AS VARCHAR ) + '; '
219 FROM #FormatContents f
220 JOIN ParsedFlexFieldsCTE p ON f.ID = p.ID
221
222 EXEC sp_executesql @Sql
223
224
225 SET @Sql = ''
226 SELECT @Sql += 'SET @FormatString = REPLACE(@FormatString, ''' + FormatFragment + ''', ''' + ReplacedValue + '''); '
227 FROM #FormatContents f
228 EXEC sp_executesql @Sql, N'@FormatString VARCHAR(MAX) OUT', @FormatString OUT
229
230 SET @Sql = 'UPDATE FormData SET ' + @IdentifierFlexField + ' = ''' + @FormatString + ''' WHERE FormId = ' + CAST(@FormId AS VARCHAR) + ' AND RecordTypeId = ' + CAST(@IdentifierRecordTypeId AS VARCHAR) + ';'
231 EXEC sp_executesql @Sql
232
233
234 /*Insert a copy in History table as well for debug purpose*/
235
236 SET @Sql = '';
237 SELECT @Sql= 'INSERT INTO FormDataHistory(ChangedColumns,FormDataId,RecordTypeId, Action, '+ @IdentifierFlexField +',ModuleName)
238 SELECT ''Info from Identifier trigger'', '+ CAST(@FormDataId AS VARCHAR) +', '+ CAST(@IdentifierRecordTypeId AS VARCHAR) +', ''U'', ''' + @FormatString + ''', ''UpdateFrom identifier Trigger'' '
239
240 END
241
242 END
243 END
244 END
245 END
246 FETCH NEXT FROM FormDataInsertCursor INTO @RecordTypeId, @FormId, @FormDataId
247 END
248 CLOSE FormDataInsertCursor
249 DEALLOCATE FormDataInsertCursor
250
251END