· 8 years ago · May 28, 2018, 12:02 AM
1USE [Quest71Dev]
2GO
3
4/****** Object: StoredProcedure [dbo].[spCurriculaGetListByUserIdAndType] Script Date: 01/18/2012 18:22:09 ******/
5SET ANSI_NULLS OFF
6GO
7
8SET QUOTED_IDENTIFIER OFF
9GO
10
11/*
12@searchOption:
13 0 - begins
14 1 - contains
15@type
16 Completed = 1,
17 Pending = 2,
18 Registered = 3,
19 Available = 4,
20*/
21
22
23ALTER PROCEDURE [dbo].[spCurriculaGetListByUserIdAndType] (
24 @userId int,
25 @type int,
26 @keyWord nvarchar(255),
27 @searchOption tinyint,
28 @portalManagerId int = null
29)
30AS
31BEGIN
32SET NOCOUNT ON
33
34DECLARE @b_portal_show_only_level_user_related bit
35 , @currTime datetime
36 , @funcOpt int
37 , @keyWordCriteria nvarchar(255)
38
39SET @keyWord=REPLACE(@keyWord,'_','[_]')
40SELECT @keyWordCriteria = CASE WHEN @searchOption = 0 THEN ISNULL(@keyWord, '') + '%'
41 WHEN @searchOption = 1 THEN '%' + ISNULL(@keyWord, '') + '%'
42 ELSE '%'
43 END
44 ,@currTime = getutcdate()
45 ,@funcOpt = CASE WHEN @type = 4 THEN 2 ELSE 1 END -- avaiable/enrolled
46
47SELECT @b_portal_show_only_level_user_related = CASE WHEN CAST(vch_1033 AS varchar(1) ) = '1' THEN 1 ELSE 0 END
48FROM dbo.system_appvars (NOLOCK)
49WHERE vch_key = 'b_portal_show_only_level_user_related'
50
51create table #events
52 (
53 EventId int,
54 CourseId uniqueidentifier,
55 EvalAvailable bit
56 )
57
58
59create table #currTable (
60 CurriculumId INT
61 ,ModifiedDate DATETIME
62 ,IsCcrActive BIT
63 ,CcrTotalCredits INT
64 ,b_complete BIT
65 ,CourseID UNIQUEIDENTIFIER
66 ,[IOrder] INT
67 ,[CreditType] TINYINT
68 ,[Credits] INT
69 ,[CurriculumName] nvarchar(200)
70 ,[CurriculumDescription] nvarchar(2000)
71)
72
73
74
75
76-- populate curricula list
77INSERT #currTable
78SELECT CurriculumId
79 ,ModifiedDate
80 ,IsCcrActive
81 ,CcrTotalCredits
82 ,b_complete
83 ,CourseID
84 ,[IOrder]
85 ,[CreditType]
86 ,[Credits]
87 ,vch_curriculumname
88 ,vch_description
89FROM dbo.fnGetAllCurriculaBase ( @userId, NULL, @currTime, @funcOpt, @b_portal_show_only_level_user_related ) currBase
90WHERE @funcOpt = 1 -- enrolled
91AND currBase.vch_curriculumname LIKE @keyWordCriteria
92AND
93 ( ( @type = 1 AND b_complete = 1 ) -- Completed
94 OR
95 ( @type = 2 AND b_complete = 0 ) -- Pending
96 OR
97 ( @type = 3 ) -- Registered
98 )
99UNION ALL
100SELECT cc.i_curriculum_id AS CurriculumId
101 ,curr.dt_modified as ModifiedDate
102 ,curr.b_ccr_active AS IsCcrActive
103 ,curr.i_ccr_tot_credits AS CcrTotalCredits
104 ,CAST(0 AS bit) AS b_complete
105 ,cc.vch_course_id AS CourseID
106 ,cc.i_order AS [IOrder]
107 ,cc.ti_credit_type AS [CreditType]
108 ,cc.i_credits AS [Credits]
109 ,curr.vch_curriculumname
110 ,curr.vch_description
111FROM dbo.curriculum_course cc (NOLOCK)
112INNER JOIN dbo.curriculum curr (NOLOCK)
113 ON cc.i_curriculum_id = curr.i_curriculum_id
114LEFT JOIN dbo.curriculum_user cu (NOLOCK)
115 ON cu.i_curriculum_id = curr.i_curriculum_id and cu.i_users_id = @userId
116WHERE @funcOpt = 2 -- avaiable
117AND curr.vch_curriculumname LIKE @keyWordCriteria
118AND cu.i_curriculum_id IS NULL
119AND EXISTS (
120 SELECT 1
121 FROM dbo.curriculum_level cl (NOLOCK)
122 LEFT JOIN dbo.level_user lu (NOLOCK)
123 ON lu.i_levels_id = cl.i_levels_id
124 AND lu.i_users_id = @userID
125 AND lu.i_levels_id <> 0
126 LEFT JOIN dbo.portal_manager pm (NOLOCK)
127 ON pm.i_users_id = @portalManagerId
128 AND pm.i_levels_id = cl.i_levels_id
129 WHERE curr.i_curriculum_id=cl.i_curriculum_id and
130 ( @portalManagerId IS NULL
131 OR
132 ( @portalManagerId IS NOT NULL
133 AND
134 pm.i_levels_id IS NOT NULL
135 )
136 )
137 AND
138 (
139 @b_portal_show_only_level_user_related = 0
140 OR
141 ( lu.i_levels_id IS NOT NULL
142 AND @b_portal_show_only_level_user_related = 1
143 )
144 )
145 )
146
147
148
149
150--- populate all events
151if @funcOpt = 1
152begin
153 INSERT #events
154 SELECT distinct e.i_event_id
155 ,e.vch_course_id
156 ,CASE
157 WHEN e.dt_evaluation_start IS NULL AND e.dt_evaluation_end IS NULL THEN 1
158 WHEN getutcdate() BETWEEN e.dt_evaluation_start AND e.dt_evaluation_end THEN 1
159 ELSE 0
160 END
161 FROM dbo.event_user eu
162 inner join dbo.event e on eu.i_event_id = e.i_event_id
163 inner join #currTable cc on cc.CourseID = e.vch_course_id
164 inner JOIN dbo.course_user cu (NOLOCK)
165 ON cu.vch_course_id = cc.CourseID AND cu.i_users_id = @userId
166 WHERE eu.i_users_id = @userId and
167 e.b_cancelled = 0
168 AND
169 (
170 ISNULL(@b_portal_show_only_level_user_related,0) = 0
171 OR
172 EXISTS ( SELECT 1
173 FROM dbo.event_level el (NOLOCK)
174 INNER JOIN dbo.level_user lu (NOLOCK)
175 ON lu.i_levels_id = el.i_levels_id
176 WHERE el.i_event_id = e.i_event_id
177 AND lu.i_users_id = @userId
178 AND lu.i_levels_id <> 0
179 )
180 )
181end
182else
183begin
184 INSERT #events
185 SELECT evt.i_event_id
186 ,evt.vch_course_id
187 ,0
188 FROM dbo.fnGetAllEventsBase ( @userId, @currTime, @funcOpt, @b_portal_show_only_level_user_related ) evt
189end
190
191
192-- Delete Events if Portal Manager cannot manage them and @portalManagerId is not null
193IF @portalManagerId IS NOT NULL
194BEGIN
195 DELETE #events
196 FROM #events e
197 WHERE NOT EXISTS
198 (
199 SELECT 1
200 FROM dbo.fnPortalManagerGetLevels(@portalManagerId) AS MngrLevels
201 INNER JOIN dbo.event_level(NOLOCK) el ON el.i_levels_id = MngrLevels.LevelId
202 WHERE el.i_levels_id = e.EventId
203 )
204END
205
206
207--------- FIRST RESULTSET ( Curricula ) ------------------
208;WITH _distinctCurr ( CurriculumId, CurriculumName, CurriculumDescription, ModifiedDate, IsCcrActive, CcrTotalCredits )
209AS
210(
211 SELECT CurriculumId
212 ,CurriculumName
213 ,CurriculumDescription
214 ,MAX(ModifiedDate) AS ModifiedDate
215 ,CAST ( MAX( CAST( IsCcrActive AS INT )) AS BIT ) AS IsCcrActive
216 ,MAX(CcrTotalCredits) AS CcrTotalCredits
217 FROM #currTable
218 GROUP BY CurriculumId, CurriculumName, CurriculumDescription
219)
220SELECT curriculum.CurriculumId
221 ,curriculum.CurriculumName
222 ,curriculum.CurriculumDescription
223 ,lcu.b_self_reg AS IsSelfReg
224 ,curriculum.ModifiedDate
225 ,curriculum.IsCcrActive
226 ,curriculum.CcrTotalCredits
227 ,ce.i_status AS CurriculumStatus
228FROM _distinctCurr curriculum
229LEFT JOIN dbo.curriculum_extended ce (NOLOCK)
230 ON ce.i_curriculum_id = curriculum.CurriculumId
231LEFT JOIN fnGetCurriculumSelfRegByUserID (@userId) lcu
232 ON lcu.i_curriculum_id = curriculum.CurriculumId
233ORDER BY curriculum.CurriculumName, curriculum.CurriculumId
234
235--------- SECOND RESULTSET ( Courses ) ------------------
236SELECT cc.CurriculumId
237 ,course.CourseId
238 ,course.CourseName
239 ,course.ShortName
240 ,course.CourseDesc
241 ,course.OnlineUrl
242 ,course.StyleId
243 ,course.Splash
244 ,course.EnvFlags
245 ,course.IsActive
246 ,course.StandardId AS StandardId
247 ,uls.vch_environment_id AS EnvironmentId
248 ,uls.vch_module_id AS ModuleId
249 ,uls.vch_page_id AS PageId
250 ,uls.dt_user_last_visit AS UserLastVisitDate
251 ,cc.IOrder
252 ,cc.CreditType
253 ,cc.Credits
254 ,CAST((CASE
255 WHEN EXISTS(SELECT FavouriteId FROM dbo.vwFavourites WHERE UserId = @userId AND CourseId = course.CourseId) THEN 1
256 ELSE 0
257 END) AS bit) AS IsInFavorites
258 ,course.CourseType
259 ,course.RefreshCoursesPage
260 ,(SELECT
261 COUNT(ev.EvalAvailable)
262 FROM #events ev (NOLOCK)
263 WHERE course.CourseId = ev.CourseId AND ev.EvalAvailable = 1)
264 AS EvaluationAvailableEvents
265FROM #currTable cc
266INNER JOIN dbo.vwCourse course
267 ON course.CourseId = cc.CourseID
268LEFT JOIN dbo.user_lastat uls (NOLOCK)
269 ON uls.vch_course_id = cc.CourseID
270 AND uls.i_users_id = @userId
271ORDER BY cc.iorder, course.CourseName
272
273----------- THIRD RESULTSET ( Events ) ------------------
274SELECT evt.CourseID
275 ,evt.EventID
276 ,e.EventName
277 ,e.Type
278 ,e.VlsStartDate
279 ,e.VlsEndDate
280 ,e.OnlStartDate
281 ,e.OnlEndDate
282 ,e.ClsStartDate
283 ,e.ClsEndDate
284 ,e.IsCancelled
285 ,e.DaysEventAvailable
286 ,eed.EvalDateDate
287FROM #events evt
288LEFT JOIN dbo.vwEvent e ON e.EventId = evt.EventID
289LEFT JOIN dbo.vwEventEvalDates eed ON eed.EventId = evt.EventId
290WHERE EXISTS ( SELECT 1 FROM #currTable cc
291 WHERE cc.CourseID = evt.CourseID
292 )
293ORDER BY e.EventName, evt.CourseID
294
295drop table #currTable
296drop table #events
297
298
299END -- procedure
300
301
302GO