Produced by Araxis Merge on 11/9/2018 12:16:39 PM Eastern Standard Time. See www.araxis.com for information about Merge. This report uses XHTML and CSS2, and is best viewed with a modern standards-compliant browser. For optimum results when printing this report, use landscape orientation and enable printing of background images and colours in your browser.
# | Location | File | Last Modified |
---|---|---|---|
1 | RAMS-2.3.0.zip\RAMS-2.3.0\SQL | CreateRAMSStoredProc.sql | Fri Jun 1 05:06:36 2018 UTC |
2 | RAMS-2.3.0.zip\RAMS-2.3.0\SQL | CreateRAMSStoredProc.sql | Fri Nov 9 14:56:00 2018 UTC |
Description | Between Files 1 and 2 |
|
---|---|---|
Text Blocks | Lines | |
Unchanged | 3 | 1280 |
Changed | 2 | 8 |
Inserted | 0 | 0 |
Removed | 0 | 0 |
Whitespace | |
---|---|
Character case | Differences in character case are significant |
Line endings | Differences in line endings (CR and LF characters) are ignored |
CR/LF characters | Not shown in the comparison detail |
No regular expressions were active.
1 | USE [RAMS] | |
2 | GO | |
3 | /****** Ob ject: Sto redProcedu re [dbo].[ usp_pre_tr eat_dataca ll] Scr ipt Date: 5/31/2018 9:25:42 PM ******/ | |
4 | SET ANSI_N ULLS ON | |
5 | GO | |
6 | SET QUOTED _IDENTIFIE R ON | |
7 | GO | |
8 | ALTER PROC EDURE [dbo ].[usp_pre _treat_dat acall] | |
9 | @UserID in t | |
10 | WITH EXEC AS CALLER | |
11 | AS | |
12 | /********* ********** ********** ********** ********** ********** | |
13 | ** 1 delet e duplicat es | |
14 | ** 2. post pond secti on A dupli cates | |
15 | ** 3. chec k existing ones, if found post pond | |
16 | ** 4. arch ive delete d and post ponded rec ords | |
17 | ********** ********** ********** ********** ********** ********** */ | |
18 | ||
19 | DECLARE @A ctionID bi gint | |
20 | DECLARE @S tudyTitle varchar(20 0) | |
21 | DECLARE @S iteCode va rchar(4) | |
22 | DECLARE @S tudyType v archar(1) | |
23 | ||
24 | ||
25 | set @A ctionID = 1 | |
26 | if exi sts (selec t distinct Action_ID from data _call_CSV_ Arch) | |
27 | Begin | |
28 | SELECT @ActionID = (max(Ac tion_ID)+1 ) from Dat a_call_CSV _arch | |
29 | End | |
30 | ||
31 | -- copy al l into arc hive table | |
32 | INSERT INT O Data_cal l_CSV_arch SELECT [A -1],[A-2], [A-2-1],[A -3],[A-4], [A-4-1],[A -5],[A-6], [A-7],[A-8 ], | |
33 | [B- 1],[B-2],[ B-3],[B-4] ,[B-6], | |
34 | [B- 5-1],[B-5- 2],[B-5-3] ,[B-5-4],[ B-5-5],[B- 5-6],[B-5- 7],[B-5-8] ,[B-5-9],[ B-5-10],[B -5-11],[B- 5-12], | |
35 | [B- 7],[B-8],[ B-9],[B-10 ],[B-11],[ B-12],[B-1 3],[B-14], [B-15],[B- 16],[B-17] ,[B-18],ge tdate(),@U serID,@Act ionID,[A-9 ] FROM Dat a_call_CSV ; | |
36 | ||
37 | -- delete absolute d uplicates | |
38 | WITH ALL_ DUP AS( | |
39 | SELECT *, | |
40 | RN = ROW_NUMB ER()OVER(P ARTITION B Y [A-1],[A -2],[A-3], [A-4],[A-6 ],[A-7],[A -9], | |
41 | [B- 1],[B-2],[ B-3],[B-4] , | |
42 | [B- 5-1],[B-5- 2],[B-5-3] ,[B-5-4],[ B-5-5],[B- 5-6],[B-5- 7],[B-5-8] ,[B-5-9],[ B-5-10],[B -5-11],[B- 5-12], | |
43 | [B- 6],[B-7],[ B-8],[B-9] ,[B-10],[B -11],[B-12 ],[B-13],[ B-14],[B-1 5],[B-16], [B-17],[B- 18] | |
44 | ORD ER BY [A-1 ]) | |
45 | FROM db o.Data_cal l_csv | |
46 | ) | |
47 | DELETE FRO M ALL_DUP WHERE RN > 1 ; | |
48 | ||
49 | -- postpon d section A duplicat es | |
50 | WITH A_DU P AS( | |
51 | SELECT *, | |
52 | RN = DENSE_RA NK() over (order by [A-1],[A-2 ],[A-3],[A -4],[A-6], [A-7],[A-9 ]) | |
53 | FROM db o.Data_cal l_csv | |
54 | ) | |
55 | INSERT INT O Data_cal l_CSV_ERR SELECT [A- 1],[A-2],[ A-2-1],[A- 3],[A-4],[ A-4-1],[A- 5],[A-6],[ A-7],[A-8] , | |
56 | [B- 1],[B-2],[ B-3],[B-4] ,[B-6], | |
57 | [B- 5-1],[B-5- 2],[B-5-3] ,[B-5-4],[ B-5-5],[B- 5-6],[B-5- 7],[B-5-8] ,[B-5-9],[ B-5-10],[B -5-11],[B- 5-12], | |
58 | [B- 7],[B-8],[ B-9],[B-10 ],[B-11],[ B-12],[B-1 3],[B-14], [B-15],[B- 16],[B-17] ,[B-18],RN ,getdate() ,@UserID,' Section A duplicates in xls fi le',@Actio nID ,[A-9] FROM A_DUP WHERE RN in | |
59 | (SELECT DI STINCT RN FROM A_DUP group BY [RN] havin g count(RN ) > 1); | |
60 | ||
61 | WITH A_DUP 2 AS( | |
62 | SELECT *, | |
63 | RN = DENSE_RA NK() over (order by [A-1],[A-2 ],[A-3],[A -4],[A-6], [A-7],[A-9 ]) | |
64 | FROM db o.Data_cal l_csv | |
65 | ) | |
66 | DELETE FRO M A_DUP2 WHERE RN i n | |
67 | (SELECT DI STINCT RN FROM A_DUP 2 group BY [RN] havi ng count(R N) > 1); | |
68 | ||
69 | -- If foun d looks sa me study e ntry, post pond | |
70 | ||
71 | IF exists ( | |
72 | select ' --' + subs tring(Assi gnedID, 15 ,1) + subs tring(Assi gnedID,1,4 ) + ltrim( rtrim(Titl e))+ '--' | |
73 | from Stu dies | |
74 | intersec t | |
75 | select ' --'+Substr ing([A-9], 1,1)+Subst ring([A-7] ,1,4) + lt rim(rtrim( [A-1]))+'- -' | |
76 | from dbo .data_call _csv | |
77 | ) | |
78 | BEGIN | |
79 | INSERT I NTO Data_c all_CSV_ER R SELECT [ A-1],[A-2] ,[A-2-1],[ A-3],[A-4] ,[A-4-1],[ A-5],[A-6] ,[A-7], [A -8], | |
80 | [B- 1],[B-2],[ B-3],[B-4] ,[B-6], | |
81 | [B- 5-1],[B-5- 2],[B-5-3] ,[B-5-4],[ B-5-5],[B- 5-6],[B-5- 7],[B-5-8] ,[B-5-9],[ B-5-10],[B -5-11],[B- 5-12], | |
82 | [B- 7],[B-8],[ B-9],[B-10 ],[B-11],[ B-12],[B-1 3],[B-14], [B-15],[B- 16],[B-17] ,[B-18],1, getdate(), @UserID,'f ound exist ing record s',@Action ID ,[A-9] | |
83 | FROM Dat a_call_CSV | |
84 | WHERE ' --'+Substr ing([A-7], 1,1)+Subst ring([A-6] ,1,4) + lt rim(rtrim( [A-1]))+'- -' in ( | |
85 | select '-- ' + substr ing(Assign edID, 15,1 ) + substr ing(Assign edID,1,4) + ltrim(rt rim(Title) )+ '--' as matchstri ng | |
86 | from Studi es | |
87 | intersect | |
88 | select '-- '+Substrin g([A-9],1, 1)+Substri ng([A-7],1 ,4) + ltri m(rtrim([A -1]))+'--' | |
89 | from dbo.d ata_call_c sv | |
90 | ) | |
91 | DELETE F ROM Data_c all_CSV | |
92 | WHERE ' --'+Substr ing([A-9], 1,1)+Subst ring([A-7] ,1,4) + lt rim(rtrim( [A-1]))+'- -' in ( | |
93 | select '-- ' + substr ing(Assign edID, 15,1 ) + substr ing(Assign edID,1,4) + ltrim(rt rim(Title) )+ '--' as matchstri ng | |
94 | from Studi es | |
95 | intersect | |
96 | select '-- '+Substrin g([A-9],1, 1)+Substri ng([A-7],1 ,4) + ltri m(rtrim([A -1]))+'--' | |
97 | from dbo.d ata_call_c sv | |
98 | ) | |
99 | END | |
100 | ||
101 | GO | |
102 | ||
103 | SET ANSI_N ULLS ON | |
104 | GO | |
105 | SET QUOTED _IDENTIFIE R ON | |
106 | GO | |
107 | -- 5.3. us p_update_p i_for_data call | |
108 | ||
109 | ALTER PROC EDURE [dbo ].[usp_upd ate_pi_for _datacall] | |
110 | WITH EXEC AS CALLER | |
111 | AS | |
112 | /********* ********** ********** ********** ********** ********** ********** ********* | |
113 | ** Type : sproc | |
114 | ** File : | |
115 | ** Name : N/A | |
116 | ** Desc : compare ram data c all users, va logon ids, with users tabl e entries, insert in to users t able if fi nd new one s | |
117 | ** Purp ose: | |
118 | ** Usage: | |
119 | ** exec : | |
120 | ** | |
121 | ********** ********** ********** ********** ********** ********** ********** *********/ | |
122 | BEGIN | |
123 | SET TR ANSACTION ISOLATION LEVEL REPE ATABLE REA D | |
124 | SET NO COUNT ON | |
125 | ||
126 | DECLAR E @StudySt atusID INT | |
127 | DECLAR E @Assigne dID varcha r(15) | |
128 | DECLAR E @PIVAID varchar(10 0) | |
129 | DECLAR E @COPIVAI D varchar( 100) | |
130 | DECLAR E @PINAME varchar(50 ) | |
131 | DECLAR E @COPINAM E varchar( 50) | |
132 | DECLAR E @StudyEx pDate date time | |
133 | DECLAR E @UsersID INT | |
134 | ||
135 | DECLAR E @mytempc ount int | |
136 | DECLAR E @tempcou nt int | |
137 | ||
138 | ||
139 | DECLAR E @ActionI D bigint | |
140 | ||
141 | SELECT @ActionID = MAX(Act ion_ID) fr om dbo.Dat a_call_CSV _processed | |
142 | if @Ac tionID IS NULL | |
143 | BEGIN | |
144 | SET @A ctionID = 1 | |
145 | END | |
146 | ||
147 | ||
148 | set ro wcount 0 | |
149 | ||
150 | select dis tinct | |
151 | Studystat usID, | |
152 | PI_VA_ID, | |
153 | b.usersID | |
154 | into # mytemp | |
155 | from d bo.Data_ca ll_process _summary a | |
156 | join d bo.users b on a.PI_V A_ID = b.L oginName | |
157 | where action_id = @ActionI D | |
158 | ||
159 | set @P IVAID = ( select top 1 pi_va_i d from #my temp) | |
160 | set @S tudyStatus ID = ( sel ect top 1 StudyStatu sID from # mytemp) | |
161 | set @U sersID = ( select to p 1 UsersI D from #my temp) | |
162 | ||
163 | SET @myte mpcount = 1 | |
164 | WHILE @my tempcount < ((SELECT COUNT(*) FROM #myte mp)+1) | |
165 | begin | |
166 | ||
167 | update dbo.study _status se t CreatedB y = @Users ID | |
168 | where StudyStatu sID = @Stu dyStatusID | |
169 | ||
170 | update dbo.study_ status set UpdatedBy = @UsersI D | |
171 | where StudyStatu sID = @Stu dyStatusID | |
172 | ||
173 | DELET E #mytemp where PI_V A_ID = @PI VAID and | |
174 | StudySt atusID = @ StudyStatu sID and | |
175 | UsersID = @UsersI D | |
176 | ||
177 | set rowcount 1 | |
178 | ||
179 | set @P IVAID = ( select top 1 pi_va_i d from #my temp) | |
180 | set @S tudyStatus ID = ( sel ect top 1 StudyStatu sID from # mytemp) | |
181 | set @U sersID = ( select to p 1 UsersI D from #my temp) | |
182 | ||
183 | END | |
184 | ||
185 | DROP tabl e #mytemp | |
186 | ||
187 | SET rowco unt 0 | |
188 | ||
189 | END | |
190 | ||
191 | GO | |
192 | ||
193 | ||
194 | /****** Ob ject: Sto redProcedu re [dbo].[ usp_check_ users_for_ datacall] Script Date: 5/31 /2018 9:35 :49 PM *** ***/ | |
195 | SET ANSI_N ULLS ON | |
196 | GO | |
197 | SET QUOTED _IDENTIFIE R ON | |
198 | GO | |
199 | ALTER PROC EDURE [dbo ].[usp_che ck_users_f or_datacal l] | |
200 | WITH EXEC AS CALLER | |
201 | AS | |
202 | /********* ********** ********** ********** ********** ********** ********** ********* | |
203 | ** Type : sproc | |
204 | ** File : | |
205 | ** Name : N/A | |
206 | ** Desc : compare ram data c all users, va logon ids, with users tabl e entries, insert in to users t able if fi nd new one s | |
207 | ** Purp ose: | |
208 | ** Usage: | |
209 | ** exec : | |
210 | ** | |
211 | ********** ********** ********** ********** ********** ********** ********** *********/ | |
212 | BEGIN | |
213 | SET TR ANSACTION ISOLATION LEVEL REPE ATABLE REA D | |
214 | SET NO COUNT ON | |
215 | ||
216 | DECLAR E @StudySt atusID INT | |
217 | DECLAR E @Assigne dID varcha r(15) | |
218 | DECLAR E @PIVAID varchar(10 0) | |
219 | DECLAR E @COPIVAI D varchar( 100) | |
220 | DECLAR E @PINAME varchar(50 ) | |
221 | DECLAR E @COPINAM E varchar( 50) | |
222 | DECLAR E @StudyEx pDate date time | |
223 | DECLAR E @UsersID INT | |
224 | ||
225 | DECLAR E @mytempc ount int | |
226 | DECLAR E @tempcou nt int | |
227 | ||
228 | ||
229 | DECLAR E @ActionI D bigint | |
230 | ||
231 | SELECT @ActionID = MAX(Act ion_ID) fr om dbo.Dat a_call_CSV _arch | |
232 | if @Ac tionID IS NULL | |
233 | BEGIN | |
234 | SET @A ctionID = 1 | |
235 | END | |
236 | ||
237 | ||
238 | set ro wcount 0 | |
239 | ||
240 | select distinct | |
241 | PI_VA_ID | |
242 | into # mytemp | |
243 | from d bo.Data_ca ll_process _summary | |
244 | where action_id = @ActionI D and ( | |
245 | PI_VA_ ID not in ( select L oginname f rom dbo.us ers)) | |
246 | ||
247 | set ro wcount 1 | |
248 | set @P IVAID = ( select top 1 pi_va_i d from #my temp) | |
249 | ||
250 | SET @myte mpcount = 1 | |
251 | WHILE @my tempcount < ((SELECT COUNT(*) FROM #myte mp)+1) | |
252 | begin | |
253 | set ro wcount 0 | |
254 | set @U sersID = ( select (ma x(usersID) + 1) from dbo.users ) | |
255 | ||
256 | SET ID ENTITY_INS ERT dbo.Us ers ON | |
257 | ||
258 | insert into dbo. users ( | |
259 | [UsersI D], | |
260 | [LoginN ame], | |
261 | [Displa yName] | |
262 | ) | |
263 | val ues( | |
264 | @UsersI D, | |
265 | @PIVAID , | |
266 | @PIVAID | |
267 | ) | |
268 | ||
269 | SET ID ENTITY_INS ERT dbo.Us ers OFF | |
270 | ||
271 | ||
272 | ||
273 | DELET E #mytemp where PI_V A_ID = @PI VAID | |
274 | ||
275 | set rowcount 1 | |
276 | ||
277 | set @P IVAID = ( select top 1 pi_va_i d from #my temp) | |
278 | ||
279 | END | |
280 | ||
281 | DROP tabl e #mytemp | |
282 | ||
283 | SET rowco unt 0 | |
284 | ||
285 | select di stinct | |
286 | CO_PI_VA_ ID | |
287 | into # mytemp2 | |
288 | from d bo.Data_ca ll_process _summary | |
289 | where action_id = @ActionI D and ( | |
290 | CO_PI_ VA_ID not in ( selec t loginnam e from dbo .users) an d CO_PI_VA _ID is not null) | |
291 | ||
292 | set ro wcount 1 | |
293 | set @C OPIVAID = ( select t op 1 co_pi _va_id fro m #mytemp2 ) | |
294 | ||
295 | SET @m ytempcount = 1 | |
296 | WHILE @mytempcou nt < ((SEL ECT COUNT( *) FROM #m ytemp2)+1) | |
297 | begin | |
298 | set ro wcount 0 | |
299 | set @U sersID = ( select (ma x(usersID) + 1) from dbo.users ) | |
300 | ||
301 | SET ID ENTITY_INS ERT dbo.Us ers ON | |
302 | insert into dbo. users ( | |
303 | [UsersI D], | |
304 | [LoginN ame], | |
305 | [Displa yName] | |
306 | ) | |
307 | val ues( | |
308 | @UsersI D, | |
309 | @COPIVA ID, | |
310 | @COPIVA ID | |
311 | ) | |
312 | SET ID ENTITY_INS ERT dbo.Us ers OFF | |
313 | ||
314 | ||
315 | ||
316 | DELET E #mytemp2 where CO_ PI_VA_ID = @COPIVAID | |
317 | ||
318 | set rowcount 1 | |
319 | ||
320 | set @C OPIVAID = ( select t op 1 co_pi _va_id fro m #mytemp2 ) | |
321 | ||
322 | END | |
323 | DROP tabl e #mytemp2 | |
324 | ||
325 | SET rowco unt 0 | |
326 | ||
327 | END | |
328 | GO | |
329 | ||
330 | ||
331 | /****** Ob ject: Sto redProcedu re [dbo].[ usp_insert _datacall_ into_rams] Script Date: 5/3 1/2018 9:3 7:12 PM ** ****/ | |
332 | SET ANSI_N ULLS ON | |
333 | GO | |
334 | SET QUOTED _IDENTIFIE R ON | |
335 | GO | |
336 | ||
337 | ||
338 | -- Batch s ubmitted t hrough deb ugger: SQL Query18.sq l|7|0|C:\U sers\ DN S
|
|
339 | -- Batch s ubmitted t hrough deb ugger: SQL Query7.sql |7|0|C:\Us ers\ DN S
|
|
340 | ALTER PROC EDURE [dbo ].[usp_ins ert_dataca ll_into_ra ms] | |
341 | @idout big int OUTPUT | |
342 | WITH EXEC AS CALLER | |
343 | AS | |
344 | /********* ********** ********** ********** ********** ********** ********** ********* | |
345 | ** Type : sproc | |
346 | ** File : usp_inse rt_datacal l_into_ram s | |
347 | ** Name : N/A | |
348 | ** Desc : get data from data call view, get new r ams studyi d, insert into the s ection a a nd b | |
349 | ** data int o RAMS tab le: studie s, study_s tatus and questions_ reponses | |
350 | ** Last Modified: 25, Jan, 2016 (SAT ) | |
351 | ** Vers ion: R1502 | |
352 | ** | |
353 | ********** ********** ********** ********** ********** ********** ********** *********/ | |
354 | BEGIN | |
355 | SET TR ANSACTION ISOLATION LEVEL REPE ATABLE REA D | |
356 | SET NO COUNT ON | |
357 | ||
358 | DECLAR E @StudySt atusID INT | |
359 | DECLAR E @StudyID INT | |
360 | DECLAR E @StudyMe mbersID IN T | |
361 | DECLAR E @SiteCod e varchar( 5) | |
362 | DE CLARE @Fac ilitiesID INT | |
363 | DECLAR E @InitStu dyType CHA R(1) | |
364 | DECLAR E @Assigne dID varcha r(15) | |
365 | DECLAR E @StudyTi tle Varcha r(200) | |
366 | DECLAR E @PIVAID varchar(10 0) | |
367 | DECLAR E @COPIVAI D varchar( 100) | |
368 | DECLAR E @StudyEx pDate date time | |
369 | DECLAR E @Server_ ID INT | |
370 | DECLAR E @UserID INT | |
371 | DECLAR E @FormVer sionsID IN T | |
372 | DECLAR E @Questio nsResponse sID int | |
373 | DECLAR E @Questio nsID int | |
374 | DECLAR E @Answers ID int | |
375 | DECLAR E @Checkbo xIndex tin yint | |
376 | DECLAR E @Respons e varchar (500) | |
377 | DECLAR E @mytempc ount int | |
378 | DECLAR E @tempcou nt int | |
379 | DECLARE @A nswers DNS AR(8) | |
380 | DE CLARE @Fac ilityName DNS AR(100) | |
381 | DE CLARE @ran domSeed fl oat | |
382 | DECLAR E @ActionI D bigint | |
383 | ||
384 | set @F ormVersion sID = (sel ect FormVe rsionsID f rom dbo.Fo rm_Version s where Fo rmVersions ID = 53) | |
385 | Set @u serID = (s elect User sID from d bo.users w here Login Name = 'SS ISUser') | |
386 | ||
387 | execut e dbo.usp_ pre_treat_ datacall @ UserID | |
388 | ||
389 | set ro wcount 0 | |
390 | ||
391 | SELECT @ActionID = MAX(Act ion_ID) fr om dbo.Dat a_call_CSV _arch | |
392 | if @Ac tionID IS NULL | |
393 | BEGIN | |
394 | SET @A ctionID = 1 | |
395 | END | |
396 | ||
397 | select distinct | |
398 | convert(v archar(200 ),[A-1]) a s study_ti tle, | |
399 | convert(c har(10),[A -7]) as si te_code, | |
400 | convert(v archar(10) ,[A-9]) as study_typ e, | |
401 | convert(v archar(100 ),[A-2-1]) as pi_va_ id, | |
402 | convert(v archar(100 ),[A-4-1]) as copi_v a_id, | |
403 | [A-8] as Study_exp_ date | |
404 | into # mytemp | |
405 | from d bo.Data_ca ll_CSV | |
406 | ||
407 | set ro wcount 1 | |
408 | set @S tudyTitle = ( select top 1 stu dy_title f rom #mytem p) | |
409 | set @S iteCode = ( select t op 1 site_ code from #mytemp) | |
410 | set @I nitStudyTy pe = ( sel ect top 1 substring( study_type ,1,1) from #mytemp) | |
411 | set @P IVAID = ( select top 1 pi_va_i d from #my temp) | |
412 | set @C OPIVAID = ( select t op 1 copi_ va_id from #mytemp) | |
413 | set @S tudyExpDat e = ( sele ct top 1 S tudy_exp_d ate from # mytemp) | |
414 | SE T @mytempc ount = 1 | |
415 | WHILE @my tempcount < ((SELECT COUNT(*) FROM #myte mp)+1) | |
416 | begin | |
417 | set ro wcount 0 | |
418 | ||
419 | set @A ssignedID = NULL; | |
420 | select @studyID = (max(stu diesID)+ 1 ) from dbo .studies; | |
421 | select @studySta tusID = (m ax(studySt atusID) + 1) from St udy_Status ; | |
422 | select @StudyMem bersID = ( MAX(Study_ MembersID) +1) from S tudy_Membe rs; | |
423 | ||
424 | -- We added this in or der to fac ilitate th e 5 digit alphanumer ic researc h center c odes | |
425 | se t @Facilit iesID = (s elect Faci litiesID f rom Facili ties where Code = @S iteCode) | |
426 | se t @Facilit yName = (s elect Name from Faci lities whe re Facilit iesID = @F acilitiesI D) | |
427 | se t @randomS eed = rand () | |
428 | ||
429 | select @Assigned ID = rams. dbo.GETSTU DYID(@Site code, @ran domSeed, @ InitStudyT ype); | |
430 | ||
431 | SET ID ENTITY_INS ERT Studie s ON | |
432 | insert into stud ies ( | |
433 | studies ID, | |
434 | Assigne dID, | |
435 | ParentS tudiesID, | |
436 | Title, | |
437 | Facilit iesID, | |
438 | Synchro nizedTimes tamp, | |
439 | FormVer sionsID | |
440 | ) | |
441 | val ues( | |
442 | @StudyI D, | |
443 | @Assign edID, | |
444 | null, | |
445 | @StudyT itle, | |
446 | @Facili tiesID, | |
447 | getdate (), | |
448 | @FormVe rsionsID | |
449 | ) | |
450 | ||
451 | SET ID ENTITY_INS ERT Studie s OFF | |
452 | ||
453 | SET ID ENTITY_INS ERT Study_ status ON | |
454 | ||
455 | insert into stud y_Status ( | |
456 | StudySt atusID, | |
457 | studies ID, | |
458 | StageCo unter, | |
459 | StudySt atesID, | |
460 | Created At, | |
461 | Created By, | |
462 | Updated At, | |
463 | Updated By | |
464 | ) | |
465 | val ues( | |
466 | @studyS tatusID, | |
467 | @StudyI D, | |
468 | 1, | |
469 | 2, | |
470 | getdate (), | |
471 | @UserID , | |
472 | getdate (), | |
473 | @UserID | |
474 | ) | |
475 | ||
476 | SET ID ENTITY_INS ERT Study_ status OFF | |
477 | ||
478 | Insert into [dbo ].[data_ca ll_process _summary] ( | |
479 | [Action _ID], | |
480 | [Assign edID], | |
481 | [Study_ Title], | |
482 | [Study_ Exp_Date], | |
483 | [StudyS tatusID], | |
484 | [Site_C ode], | |
485 | [PI_VA_ ID], | |
486 | [CO_PI_ VA_ID], | |
487 | [Study_ Type], | |
488 | [Action _Type], | |
489 | [loadti me], | |
490 | [loadby ] | |
491 | ) | |
492 | Values ( | |
493 | @Action ID, | |
494 | @Assign edID, | |
495 | @StudyT itle, | |
496 | @StudyE xpDate, | |
497 | @StudyS tatusID, | |
498 | @SiteCo de, | |
499 | @PIVAID , | |
500 | @COPIVA ID, | |
501 | @InitSt udyType, | |
502 | 'Insert ', | |
503 | getdate (), | |
504 | @UserID | |
505 | ) | |
506 | ||
507 | SELECT b.questi onsID, '' AS answers ID | |
508 | in to #studyT itle | |
509 | FR OM v_q list_datac all b | |
510 | WH ERE (char( 64 + b.for mSequence) + '-' + C ONVERT(var char(2), b .QuestionP osition)) = 'A-1' | |
511 | an d b.formS equence IN (1, 2) AN D b.answer sID IS NUL L | |
512 | ||
513 | SE T @tempcou nt =1 | |
514 | ||
515 | WH ILE @tempc ount < ((S ELECT COUN T(*) FROM #studyTitl e)+1) | |
516 | BEGI N | |
517 | SET ID ENTITY_INS ERT Questi ons_Respon ses ON | |
518 | ||
519 | select @Question sResponses ID = (max( QuestionsR esponsesID ) + 1) fro m Question s_Response s | |
520 | select @Question sID = ques tionsID fr om #studyT itle | |
521 | IN SERT INTO Questions_ Responses ( | |
522 | Questio nsResponse sID, | |
523 | StudySt atusID, | |
524 | Questio nsID, | |
525 | Checkbo xIndex, | |
526 | Answers ID, | |
527 | Respons e, | |
528 | Updated At | |
529 | ) | |
530 | VALU ES ( | |
531 | @Questi onsRespons esID, | |
532 | @StudyS tatusID, | |
533 | @Questi onsID, | |
534 | @Checkb oxIndex, | |
535 | @Answer sID, | |
536 | @StudyT itle, | |
537 | Getdate () | |
538 | ) | |
539 | Set @t empcount = @tempcoun t + 1 | |
540 | SET ID ENTITY_INS ERT Questi ons_Respon ses OFF | |
541 | END | |
542 | ||
543 | SE LECT study _Title, An swers, que stionsID, answersID, answerVal ue | |
544 | INTO # mytemp2 | |
545 | FROM d bo.V_data_ call_csv_n onflat_map ped | |
546 | WHerE answers no t in (N'A- 8',N'A-9') and study _title = @ StudyTitle and quest ionsID <> 0 | |
547 | ||
548 | SET @t empcount = 1 | |
549 | ||
550 | WHILE @ tempcount < ((SELECT COUNT(*) FROM #myte mp2 WHERE study_titl e = @Study Title)+1) -- for sec tion B par t | |
551 | BEGIN | |
552 | ||
553 | SET IDENTITY_I NSERT Ques tions_Resp onses ON | |
554 | ||
555 | sele ct @Questi onsRespons esID = (ma x(Question sResponses ID) + 1) f rom Questi ons_Respon ses | |
556 | ||
557 | SET rowcount 1 | |
558 | sele ct @Questi onsID = Qu estionsID, @Answers = convert( varchar(8) , answers) , @Answers ID = Answe rsID, @Res ponse = an swerValue | |
559 | from #mytemp2 where stud y_title = @StudyTitl e | |
560 | ||
561 | IF @Answer s = 'A-7' | |
562 | begi n | |
563 | set @R esponse = @FacilityN ame | |
564 | end | |
565 | ||
566 | IF @ AnswersID = 0 | |
567 | begi n | |
568 | set @answersID = NULL | |
569 | end | |
570 | ||
571 | SET @CheckboxI ndex = NUL L | |
572 | if e xists ( | |
573 | sele ct answerp osition f rom v_qlis t_datacall | |
574 | wher e formsID in (153,16 0) and con vert(varch ar(10),dat atype) = ' Checkbox' and answer sID = @Ans wersID) | |
575 | BEGI N | |
576 | sele ct @Checkb oxIndex = answerposi tion from v_qlist_da tacall | |
577 | wher e formsID in (153,16 0) and con vert(varch ar(10),dat atype) = ' Checkbox' and answer sID = @Ans wersID | |
578 | END | |
579 | ||
580 | INSERT INTO Ques tions_Resp onses ( | |
581 | Questio nsResponse sID, | |
582 | StudySt atusID, | |
583 | Questio nsID, | |
584 | Checkbo xIndex, | |
585 | Answers ID, | |
586 | Respons e, | |
587 | Updated At | |
588 | ) | |
589 | VALU ES ( | |
590 | @Questi onsRespons esID, | |
591 | @StudyS tatusID, | |
592 | @Questi onsID, | |
593 | @Checkb oxIndex, | |
594 | @Answer sID, | |
595 | @Respon se, | |
596 | Getdate () | |
597 | ) | |
598 | DELE TE #mytemp 2 | |
599 | wher e question sID = @Que stionsID a nd study_t itle = @St udyTitle a nd convert (varchar(8 ),Answers) = @Answer s | |
600 | ||
601 | SET IDENTITY_I NSERT Ques tions_Resp onses OFF | |
602 | ||
603 | END | |
604 | set rowcount 0 | |
605 | ||
606 | ||
607 | drop table #myt emp2 | |
608 | d rop table #studyTitl e | |
609 | ||
610 | DELET E #mytemp where stud y_title = @StudyTitl e and | |
611 | site_co de = @Site code and | |
612 | substri ng(study_t ype,1,1) = @InitStud yType | |
613 | ||
614 | set rowc ount 1 -- load next row in da ta_call_cv s | |
615 | set @S tudyTitle = ( select top 1 stu dy_title f rom #mytem p) | |
616 | set @S iteCode = ( select t op 1 site_ code from #mytemp) | |
617 | set @I nitStudyTy pe = ( sel ect top 1 substring( study_type ,1,1) from #mytemp) | |
618 | set @P IVAID = ( select top 1 pi_va_i d from #my temp) | |
619 | set @C OPIVAID = ( select t op 1 copi_ va_id from #mytemp) | |
620 | set @S tudyExpDat e = ( sele ct top 1 S tudy_exp_d ate from # mytemp) | |
621 | --select @StudyTit le = study _title,@Si teCode = s ite_code, @InitStudy Type = sub string(stu dy_type,1, 1) from #m ytemp | |
622 | ||
623 | END | |
624 | ||
625 | DROP tabl e #mytemp | |
626 | ||
627 | SET rowco unt 0 | |
628 | ||
629 | BEGIN | |
630 | INSERT IN TO dbo.Dat a_call_CSV _processed SELECT [A -1],[A-2], [A-2-1],[A -3],[A-4], [A-4-1],[A -5],[A-6], [A-7],[A-8 ], | |
631 | [B- 1],[B-2],[ B-3],[B-4] ,[B-6], | |
632 | [B- 5-1],[B-5- 2],[B-5-3] ,[B-5-4],[ B-5-5],[B- 5-6],[B-5- 7],[B-5-8] ,[B-5-9],[ B-5-10],[B -5-11],[B- 5-12], | |
633 | [B- 7],[B-8],[ B-9],[B-10 ],[B-11],[ B-12],[B-1 3],[B-14], [B-15],[B- 16],[B-17] ,[B-18],ge tdate(),@U serID,@Act ionID, [A- 9] FROM Da ta_call_CS V | |
634 | --DELETE FROM dbo.D ata_call_C SV | |
635 | END | |
636 | ||
637 | execute d bo.usp_che ck_users_f or_datacal l | |
638 | execute d bo.usp_upd ate_pi_for _datacall | |
639 | ||
640 | END | |
641 | ||
642 | GO | |
643 | ||
644 |
Araxis Merge (but not the data content of this report) is Copyright © 1993-2016 Araxis Ltd (www.araxis.com). All rights reserved.