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 | R1502-usp_insert_datacall_into_rams.sql | Thu Jun 22 12:36:26 2017 UTC |
2 | RAMS_CIF.zip\RAMS_CIF\RAMS_CIF\scripts\RAMS Sus Release 1701\scripts | R1502-usp_insert_datacall_into_rams.sql | Thu Jun 22 14:30:29 2017 UTC |
Description | Between Files 1 and 2 |
|
---|---|---|
Text Blocks | Lines | |
Unchanged | 2 | 518 |
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 | Í'\x0000\x0000\x0002\x0000R150 2-usp_inse rt_datacal l_into_ram s.sql\x0000C:\U sers\83342 8\Document s\SQL Serv er Managem ent Studio \R1502_v2\ R1502-usp_ insert_dat acall_into _rams.sql\x0000 \x0000\x0000\x0003\x0000K\x0000\x0000\x0000C: \Users\833 428\AppDat a\Local\Te mp\R1502-u sp_insert_ datacall_i nto_rams.s ql\x0000(%\x0000\x0000USE [RAMS] | |
2 | GO | |
3 | ||
4 | /****** Ob ject: Sto redProcedu re [dbo].[ usp_insert _datacall_ into_rams] Script Date: 1/2 5/2016 10: 26:07 AM * *****/ | |
5 | SET ANSI_N ULLS ON | |
6 | GO | |
7 | ||
8 | SET QUOTED _IDENTIFIE R ON | |
9 | GO | |
10 | ||
11 | -- 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 | |
12 | -- 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 | |
13 | ALTER PROC EDURE [dbo ].[usp_ins ert_dataca ll_into_ra ms] | |
14 | @idout big int OUTPUT | |
15 | WITH EXEC AS CALLER | |
16 | AS | |
17 | /********* ********** ********** ********** ********** ********** ********** ********* | |
18 | ** Type : sproc | |
19 | ** File : usp_inse rt_datacal l_into_ram s | |
20 | ** Name : N/A | |
21 | ** Desc : get data from data call view, get new r ams studyi d, insert into the s ection a a nd b | |
22 | ** data int o RAMS tab le: studie s, study_s tatus and questions_ reponses | |
23 | ** Last Modified: 25, Jan, 2016 (SAT ) | |
24 | ** Vers ion: R1502 | |
25 | ** | |
26 | ********** ********** ********** ********** ********** ********** ********** *********/ | |
27 | BEGIN | |
28 | SET TR ANSACTION ISOLATION LEVEL REPE ATABLE REA D | |
29 | SET NO COUNT ON | |
30 | ||
31 | DECLAR E @StudySt atusID INT | |
32 | DECLAR E @StudyID INT | |
33 | DECLAR E @StudyMe mbersID IN T | |
34 | DECLAR E @SiteCod e varchar( 5) | |
35 | DE CLARE @Fac ilitiesID INT | |
36 | DECLAR E @InitStu dyType CHA R(1) | |
37 | DECLAR E @Assigne dID varcha r(15) | |
38 | DECLAR E @StudyTi tle Varcha r(200) | |
39 | DECLAR E @PIVAID varchar(10 0) | |
40 | DECLAR E @COPIVAI D varchar( 100) | |
41 | DECLAR E @StudyEx pDate date time | |
42 | DECLAR E @Server_ ID INT | |
43 | DECLAR E @UserID INT | |
44 | DECLAR E @FormVer sionsID IN T | |
45 | DECLAR E @Questio nsResponse sID int | |
46 | DECLAR E @Questio nsID int | |
47 | DECLAR E @Answers ID int | |
48 | DECLAR E @Checkbo xIndex tin yint | |
49 | DECLAR E @Respons e varchar (500) | |
50 | DECLAR E @mytempc ount int | |
51 | DECLAR E @tempcou nt int | |
52 | DECLAR E @Answers VARCHAR (8) | |
53 | DE CLARE @ran domSeed fl oat | |
54 | DECLAR E @ActionI D bigint | |
55 | ||
56 | set @F ormVersion sID = (sel ect FormVe rsionsID f rom dbo.Fo rm_Version s where Fi leName ='M ainApplica tion.csv') | |
57 | Set @u serID = (s elect User sID from d bo.users w here Login Name = 'SS ISUser') | |
58 | ||
59 | execut e dbo.usp_ pre_treat_ datacall @ UserID | |
60 | ||
61 | set ro wcount 0 | |
62 | ||
63 | SELECT @ActionID = MAX(Act ion_ID) fr om dbo.Dat a_call_CSV _arch | |
64 | if @Ac tionID = N ULL | |
65 | BEGIN | |
66 | SET @A ctionID = 1 | |
67 | END | |
68 | ||
69 | select distinct | |
70 | convert(v archar(200 ),[A-1]) a s study_ti tle, | |
71 | convert(c har(10),[A -6]) as si te_code, | |
72 | convert(v archar(10) ,[A-7]) as study_typ e, | |
73 | convert(v archar(100 ),[A-2-1]) as pi_va_ id, | |
74 | convert(v archar(100 ),[A-4-1]) as copi_v a_id, | |
75 | [A-8] as Study_exp_ date | |
76 | into # mytemp | |
77 | from d bo.Data_ca ll_CSV | |
78 | ||
79 | set ro wcount 1 | |
80 | set @S tudyTitle = ( select top 1 stu dy_title f rom #mytem p) | |
81 | set @S iteCode = ( select t op 1 site_ code from #mytemp) | |
82 | set @I nitStudyTy pe = ( sel ect top 1 substring( study_type ,1,1) from #mytemp) | |
83 | set @P IVAID = ( select top 1 pi_va_i d from #my temp) | |
84 | set @C OPIVAID = ( select t op 1 copi_ va_id from #mytemp) | |
85 | set @S tudyExpDat e = ( sele ct top 1 S tudy_exp_d ate from # mytemp) | |
86 | SE T @mytempc ount = 1 | |
87 | WHILE @my tempcount < ((SELECT COUNT(*) FROM #myte mp)+1) | |
88 | begin | |
89 | set ro wcount 0 | |
90 | ||
91 | set @A ssignedID = NULL; | |
92 | select @studyID = (max(stu diesID)+ 1 ) from dbo .studies; | |
93 | select @studySta tusID = (m ax(studySt atusID) + 1) from St udy_Status ; | |
94 | select @StudyMem bersID = ( MAX(Study_ MembersID) +1) from S tudy_Membe rs; | |
95 | ||
96 | -- We added this in or der to fac ilitate th e 5 digit alphanumer ic researc h center c odes | |
97 | se t @Facilit iesID = (s elect Faci litiesID f rom Facili ties where Code = @S iteCode) | |
98 | se t @randomS eed = rand () | |
99 | ||
100 | select @Assigned ID = rams. dbo.GETSTU DYID(@Site code, @ran domSeed, @ InitStudyT ype); | |
101 | ||
102 | SET ID ENTITY_INS ERT Studie s ON | |
103 | insert into stud ies ( | |
104 | studies ID, | |
105 | Assigne dID, | |
106 | ParentS tudiesID, | |
107 | Title, | |
108 | Facilit iesID, | |
109 | Synchro nizedTimes tamp, | |
110 | FormVer sionsID | |
111 | ) | |
112 | val ues( | |
113 | @StudyI D, | |
114 | @Assign edID, | |
115 | null, | |
116 | @StudyT itle, | |
117 | @Facili tiesID, | |
118 | getdate (), | |
119 | @FormVe rsionsID | |
120 | ) | |
121 | ||
122 | SET ID ENTITY_INS ERT Studie s OFF | |
123 | ||
124 | SET ID ENTITY_INS ERT Study_ status ON | |
125 | ||
126 | insert into stud y_Status ( | |
127 | StudySt atusID, | |
128 | studies ID, | |
129 | StageCo unter, | |
130 | StudySt atesID, | |
131 | Created At, | |
132 | Created By, | |
133 | Updated At, | |
134 | Updated By | |
135 | ) | |
136 | val ues( | |
137 | @studyS tatusID, | |
138 | @StudyI D, | |
139 | 1, | |
140 | 2, | |
141 | getdate (), | |
142 | @UserID , | |
143 | getdate (), | |
144 | @UserID | |
145 | ) | |
146 | ||
147 | SET ID ENTITY_INS ERT Study_ status OFF | |
148 | ||
149 | Insert into [dbo ].[data_ca ll_process _summary] ( | |
150 | [Action _ID], | |
151 | [Assign edID], | |
152 | [Study_ Title], | |
153 | [Study_ Exp_Date], | |
154 | [StudyS tatusID], | |
155 | [Site_C ode], | |
156 | [PI_VA_ ID], | |
157 | [CO_PI_ VA_ID], | |
158 | [Study_ Type], | |
159 | [Action _Type], | |
160 | [loadti me], | |
161 | [loadby ] | |
162 | ) | |
163 | Values ( | |
164 | @Action ID, | |
165 | @Assign edID, | |
166 | @StudyT itle, | |
167 | @StudyE xpDate, | |
168 | @StudyS tatusID, | |
169 | @SiteCo de, | |
170 | @PIVAID , | |
171 | @COPIVA ID, | |
172 | @InitSt udyType, | |
173 | 'Insert ', | |
174 | getdate (), | |
175 | @UserID | |
176 | ) | |
177 | ||
178 | SELECT study_Tit le, Answer s, questio nsID, answ ersID, ans werValue | |
179 | INTO # mytemp2 | |
180 | FROM d bo.V_data_ call_csv_n onflat_map ped | |
181 | WHerE answers no t in (N'A- 6',N'A-7', N'A-8') an d study_ti tle = @Stu dyTitle | |
182 | ||
183 | ||
184 | ||
185 | SET @t empcount = 1 | |
186 | ||
187 | WHILE @ tempcount < ((SELECT COUNT(*) FROM #myte mp2 WHERE study_titl e = @Study Title)+1) -- for sec tion B par t | |
188 | BEGIN | |
189 | ||
190 | SET IDENTITY_I NSERT Ques tions_Resp onses ON | |
191 | ||
192 | sele ct @Questi onsRespons esID = (ma x(Question sResponses ID) + 1) f rom Questi ons_Respon ses | |
193 | ||
194 | SET rowcount 1 | |
195 | sele ct @Questi onsID = Qu estionsID, @Answers = convert( varchar(8) , answers) , @Answers ID = Answe rsID, @Res ponse = an swerValue | |
196 | from #mytemp2 where stud y_title = @StudyTitl e | |
197 | ||
198 | IF @ AnswersID = 0 | |
199 | begi n | |
200 | set @answersID = NULL | |
201 | end | |
202 | ||
203 | SET @CheckboxI ndex = NUL L | |
204 | if e xists ( | |
205 | sele ct answerp osition f rom v_qlis t_datacall | |
206 | wher e formsID in (153,16 0) and con vert(varch ar(10),dat atype) = ' Checkbox' and answer sID = @Ans wersID) | |
207 | BEGI N | |
208 | sele ct @Checkb oxIndex = answerposi tion from v_qlist_da tacall | |
209 | wher e formsID in (153,16 0) and con vert(varch ar(10),dat atype) = ' Checkbox' and answer sID = @Ans wersID | |
210 | END | |
211 | ||
212 | INSERT INTO Ques tions_Resp onses ( | |
213 | Questio nsResponse sID, | |
214 | StudySt atusID, | |
215 | Questio nsID, | |
216 | Checkbo xIndex, | |
217 | Answers ID, | |
218 | Respons e, | |
219 | Updated At | |
220 | ) | |
221 | VALU ES ( | |
222 | @Questi onsRespons esID, | |
223 | @StudyS tatusID, | |
224 | @Questi onsID, | |
225 | @Checkb oxIndex, | |
226 | @Answer sID, | |
227 | @Respon se, | |
228 | Getdate () | |
229 | ) | |
230 | DELE TE #mytemp 2 | |
231 | wher e question sID = @Que stionsID a nd study_t itle = @St udyTitle a nd convert (varchar(8 ),Answers) = @Answer s | |
232 | ||
233 | SET IDENTITY_I NSERT Ques tions_Resp onses OFF | |
234 | ||
235 | END | |
236 | set rowcount 0 | |
237 | ||
238 | ||
239 | drop table #myt emp2 | |
240 | ||
241 | DELET E #mytemp where stud y_title = @StudyTitl e and | |
242 | site_co de = @Site code and | |
243 | substri ng(study_t ype,1,1) = @InitStud yType | |
244 | ||
245 | set rowc ount 1 -- load next row in da ta_call_cv s | |
246 | set @S tudyTitle = ( select top 1 stu dy_title f rom #mytem p) | |
247 | set @S iteCode = ( select t op 1 site_ code from #mytemp) | |
248 | set @I nitStudyTy pe = ( sel ect top 1 substring( study_type ,1,1) from #mytemp) | |
249 | set @P IVAID = ( select top 1 pi_va_i d from #my temp) | |
250 | set @C OPIVAID = ( select t op 1 copi_ va_id from #mytemp) | |
251 | set @S tudyExpDat e = ( sele ct top 1 S tudy_exp_d ate from # mytemp) | |
252 | --select @StudyTit le = study _title,@Si teCode = s ite_code, @InitStudy Type = sub string(stu dy_type,1, 1) from #m ytemp | |
253 | ||
254 | END | |
255 | ||
256 | DROP tabl e #mytemp | |
257 | ||
258 | SET rowco unt 0 | |
259 | ||
260 | BEGIN | |
261 | INS |
Araxis Merge (but not the data content of this report) is Copyright © 1993-2016 Araxis Ltd (www.araxis.com). All rights reserved.