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