· 8 years ago · Mar 23, 2018, 07:28 AM
1CREATE PROCEDURE [Challenges].[usp_ChallengeDetails_ByChallengeId]
2(
3 @UserGuid UNIQUEIDENTIFIER,
4 @ChallengeInstanceGuid UNIQUEIDENTIFIER
5)
6AS
7/*********************************************************
8 ------------------------------------------------------
9 Description
10
11 This will get the details of an active challenge
12 ------------------------------------------------------
13 ------------------------------------------------------
14
15 -------------------------
16 ------------------------------------------------------
17 exec [Challenges].[usp_ChallengeDetails_ByChallengeId]
18 'CBA4412A-3391-4902-A075-D8C434DA6406' , 'dd31fbdb-d26f-404c-8953-69a998f2f5b7'
19 -----------------------------
20
21 ------------------------------------------------------
22
23 ------------------------------------------------------
24 History
25 ------------------------------------------------------
26 2017-05-23 Sourabh Virdi Created
27 2017-06-13 Sourabh Virdi Modified SP taking too long to return the result, optimised it
28 2017-07-20 Saiprasad Modified INNER JOIN with Tackers table to fetch TrakerGuid
29 2017-08-26 Sourabh Virdi Modified Added JOIN to get member avatar
30 2017-09-06 Shreedhar N Modified Added [Trackers].[usp_ChallengeTrackerData_GET]
31 SP on replacment of ManuallyReportedData table.
32 2017-09-07 Sourabh Virdi Modified Added TrackerSourceType
33 2017-09-22 Shreedhar N Modified Removed the [Trackers].[usp_ChallengeTrackerData_GET]
34 And replaced with the sp whole script as issue with nested insert into so
35 2017-10-12 Reed McLean Added join to SharedFeature to support visibility information
36 2017-10-25 Shreedhar N Modified Changes the IncentiveValue to IncentiveValueWithDenomination
37 with respect to IncentiveTypeId(1=Points,2=Dollars) to show in the challenge card
38 2017-10-26 Sourabh V Modified Changes for Incentive
39 2017-11-09 Sourabh V Modified Considering only date for tracker data
40 2017-11-10 Sourabh Virdi Modified Added support for localization
41 2017-29-11 Lewis Wade Modified Changed LanguageId to get from tbl_Actor instead of parameter
42 2017-29-11 Lewis Wade Modified Added localization for Corporate Challenges
43 2017-21-02 Saiprasad Modified Added Category and Duration for Challenges
44 2017-27-02 Saiprasad Modified Added left join incentives on challenge card
45
46 ------------------------------------------------------
47*********************************************************/
48
49BEGIN
50 SET NOCOUNT ON;
51 DECLARE @Now DateTime = GETDATE()
52 DECLARE @PointsMatrixID INT
53 DECLARE @ChallengeTemplateId INT
54 DECLARE @ActorId INT
55
56
57
58 IF OBJECT_ID('tempdb..#OutDataChallenges') IS NOT NULL
59 BEGIN
60 DROP TABLE #OutDataChallenges;
61 END
62
63 CREATE TABLE #OutDataChallenges
64 (UserID UNIQUEIDENTIFIER,
65 DataValue nvarchar(255),
66 MeasurementId int,
67 DataReportedRangeStartDatetime datetime,
68 DataReportedRangeEndDatetime datetime,
69 SourceName nvarchar(255),
70 MeasurementName nvarchar(255),
71 MeasurementTypeId int,
72 MeasurementTypeName nvarchar(255),
73 MeasurementDescription nvarchar(max),
74 UnitId int,
75 [UnitName] nvarchar(255),
76 UnitDescription nvarchar(255),
77 UnitDisplayName nvarchar(255),
78 Abbreviation nvarchar(255),
79 DisplayOrder int,
80 [IsRequired] int)
81
82
83 ;WITH PointMatrixCTE AS
84 (SELECT sc.PointsMatrixID, sc.ServiceCycleID, ac.ActorId
85 FROM
86 tbl_Actor ac
87 JOIN
88 OnlifeEntity..tbl_EntityGroup eg
89 ON ac.ActorID = eg.ActorId
90 JOIN
91 OnlifeEntity..tbl_Group g
92 ON
93 eg.GroupID = g.GroupID
94 JOIN
95 ServiceCycleGroupMap scgm
96 ON
97 scgm.GroupGuid = g.GroupGuid
98 JOIN
99 tbl_ServiceCycle sc
100 ON
101 sc.ServiceCycleID = scgm.ServiceCycleId
102 AND @Now BETWEEN sc.ServiceCycleStartDate AND sc.ServiceCycleEndDate
103 WHERE
104 ac.SharedMembershipUserId = @UserGuid
105 )
106 SELECT TOP 1 @PointsMatrixID = PointsMatrixID, @ActorId=ActorId FROM PointMatrixCTE ORDER BY ServiceCycleID DESC
107 SELECT @ChallengeTemplateId = ChallengeTemplateId FROM Challenges.ChallengeInstance WHERE ChallengeInstanceGuid = @ChallengeInstanceGuid
108 DECLARE @LanguageId INT = (SELECT LocaleID from Onlife.dbo.tbl_Actor where ActorId = @ActorId)
109
110 DECLARE @trackerGuid UNIQUEIDENTIFIER =
111 (SELECT TOP 1 TrackerGuid FROM Trackers.Tracker t JOIN Challenges.ChallengeTrackerMap ctm
112 ON t.TrackerId = ctm.TrackerId WHERE ctm.ChallengeId = @ChallengeTemplateId)
113
114 DECLARE @VendorInfoID int,@ValidicID nvarchar(255), @TrackerId INT, @DateStart datetime, @DateEnd datetime ,@MeasurementID int
115 DECLARE @HoldSQL nvarchar(max)
116 DECLARE @HoldMaxRN int = 0,@CurrentRN int = 1
117
118 DECLARE @StartDate DATETIME
119 DECLARE @EndDate DATETIME
120 SELECT @StartDate= StartDate, @EndDate = EndDate FROM Challenges.ChallengeInstance
121 WHERE ChallengeInstanceGuid = @ChallengeInstanceGuid
122
123 DECLARE @HoldValidicLookups AS TABLE
124 (
125 RowNumber int IDENTITY(1,1),
126 HoldSQL nvarchar(max),
127 ValidicID nvarchar(255),
128 MeasurementID int,
129 DateStart nvarchar(255),
130 DateEnd nvarchar(255),
131 TrackerID int,
132 RowupdateDate Datetime
133 )
134
135 --A temp table for the raw data to go out, could be thousands of rows, usually dozens to hundreds.
136 CREATE TABLE #OutData
137 (DataReportedRangeStartTime datetime,
138 DataReportedRangeEndTime datetime,
139 DataValue nvarchar(255),
140 SourceName nvarchar(255),
141 UserID nvarchar(255),
142 TrackerID int,
143 MeasurmentID int,
144 RowUpdatedDate datetime,
145 Preference int)
146
147
148 SELECT @VendorInfoID = VendorInfoID
149 FROM tbl_VendorInfo
150 WHERE VendorName = 'Validic'
151
152
153 SELECT
154 t.[TrackerId],
155 t.[TrackerGuid],
156 t.[Name],
157 t.[Title],
158 t.[Description],
159 t.[TrackerTypeId],
160 t.[TrackingFrequencyId],
161 t.[Red],
162 t.[Green],
163 t.[Blue],
164 t.[Alpha],
165 t.[ImagePath],
166 tgtm.[GraphTypeId],
167 tgtm.[LabelXAxis],
168 tgtm.[LabelYAxis],
169 tmm.[MeasurementId],
170 tmm.[DisplayName] [MeasurementName],
171 m.[MeasurementTypeId],
172 mt.[Name] [MeasurementTypeName],
173 tmm.[Description] [MeasurementDescription],
174 [DisplayOrder],
175 m.[MaxRangeValue],
176 m.[MinRangeValue],
177 tmm.[IsRequired],
178 m.[UnitId],
179 u.[Name] [UnitName],
180 u.[Description] [UnitDescription],
181 u.[DisplayName] [UnitDisplayName],
182 u.[Abbreviation],
183 CASE WHEN IsNull(t.CreatedByUserId,@NotMeUserID) <> @UserId THEN 1 ELSE 0 END,
184 t.TrackerSourceTypeId,
185 ISNULL(sf.VisibleToConnectionStatusId, 0) as SharedWithConnectionStatus,
186 tgtm.GraphFrequencyId
187 FROM [Trackers].[Tracker] t
188 JOIN
189 [Trackers].TrackerGraphTypeMap tgtm
190 ON tgtm.[TrackerId] = t.[TrackerId]
191 JOIN
192 [Trackers].[TrackerMeasurementMap] tmm
193 ON tmm.[TrackerId] = t.[TrackerId]
194 JOIN
195 [dbo].[Measurement] m
196 ON m.[MeasurementId] = tmm.[MeasurementId]
197 JOIN
198 [dbo].[MeasurementType] mt
199 ON mt.[MeasurementTypeId] = m.[MeasurementTypeId]
200 JOIN
201 [dbo].[Unit] u
202 ON u.[UnitId] = m.[UnitId]
203 LEFT JOIN Social.SharedFeature sf
204 ON t.TrackerGuid = sf.FeatureGuid And sf.ActorId = @ActorId
205 WHERE t.[TrackerGuid] = @trackerGuid
206
207 --get the source type
208 SELECT
209 @TrackerSourceTypeId = t.TrackerSourceTypeId
210 FROM [Trackers].[Tracker] t
211 WHERE t.[TrackerGuid] = @trackerGuid
212
213
214 ;with
215 TrackerMeasure AS
216 (
217 SELECT MeasurementID,t.TrackerID,tf.Name Frequency
218 FROM Trackers.Tracker t
219 JOIN Trackers.[TrackerMeasurementMap] tmm
220 ON tmm.TrackerID = t.TrackerID
221 JOIN Trackers.TrackingFrequency tf
222 ON tf.TrackingFrequencyId = t.TrackingFrequencyId
223 WHERE t.TrackerGuid = @TrackerGuid
224 )
225 SELECT TrackerID,mmds.MeasurementID,Name,Description,mmds.MeasurementMeasurementDataSourcePreference,MeasureSourceType,TableOrViewName,ColumnName,FilterValue,tm.Frequency
226 INTO #HoldMeasurePullInfo
227 FROM MeasurementMeasurementDataSource mmds
228 JOIN Measurement m
229 ON m.MeasurementId = mmds.MeasurementID
230 JOIN MeasureSourceType mst
231 ON mst.MeasureSourceTypeKey = mmds.MeasureSourceTypeKey
232 JOIN MeasureDataSource mds
233 ON mds.MeasureDataSourceKey = mmds.MeasureDataSourceKey
234 JOIN TrackerMeasure tm
235 ON mmds.MeasurementID = tm.MeasurementID
236
237 GROUP BY TrackerID,mmds.MeasurementID,Name,Description,mmds.MeasurementMeasurementDataSourcePreference,MeasureSourceType,TableOrViewName,ColumnName,FilterValue,tm.Frequency
238 ORDER BY mmds.MeasurementID,MeasurementMeasurementDataSourcePreference
239
240
241
242 --Get all of the manually entered data
243
244 INSERT INTO #OutData
245 SELECT IIF(Frequency = 'Daily', CAST(mrd.DataReportedRangeStartDatetime as DATE), mrd.DataReportedRangeStartDatetime), IIF(Frequency = 'Daily', CAST(mrd.DataReportedRangeEndDatetime as DATE), mrd.DataReportedRangeEndDatetime), DataValue, 'Manual Entry' Source_Name,mrd.userid,TrackerID,mrd.MeasurementId,mrd.RowUpdatedDate,hpi.MeasurementMeasurementDataSourcePreference
246 FROM ManuallyReportedData mrd
247 JOIN #HoldMeasurePullInfo hpi
248 ON hpi.MeasurementID = mrd.MeasurementID
249 AND TableOrViewName = 'dbo.ManuallyReportedData'
250 WHERE mrd.UserID = @UserID
251 AND DataReportedRangeStartDatetime between @startDate and @endDate
252
253 --If the type is not only Manual
254 IF @TrackerSourceTypeId <> 1
255 BEGIN
256 --Get the automatically added data
257 ;with UserValidicID AS
258 (
259 SELECT ta.ActorID,TheirUserToken,@UserID UserID
260 FROM tbl_Actor ta
261 JOIN tbl_ActorVendorTokenMap avtm
262 ON avtm.ActorId = ta.ActorID
263 where SharedMembershipUserID = @UserID
264 )
265 INSERT INTO @HoldValidicLookups
266 (HoldSQL,ValidicID,MeasurementID,DateStart,DateEnd,TrackerID)
267 SELECT 'SELECT ' +
268 IIF(Frequency = 'Daily',' CAST(timestampdatetime as DATE),','timestampdatetime,') +
269 IIF(Frequency = 'Daily',' CAST(timestampdatetime as DATE),','timestampdatetime,')
270 + ColumnName + ',DisplaySource, UserID = @UserID,TrackerID = @TrackerID,' +
271 'MeasurementID = @MeasurementID, '+'timestampdatetime,'+ CAST(MeasurementMeasurementDataSourcePreference as nvarchar(1)) + ' FROM ' + TableOrViewName + '(@ValidicID,@DateStart,@DateEnd)'
272 + IIF(FilterValue is not null,' WHERE ' + ColumnName + ' = ' + CHAR(39) + FilterValue + CHAR(39),'') +
273 IIF(Frequency = 'Daily', ' GROUP BY CAST(timestampdatetime as DATE),timestampdatetime,' + ColumnName + ',DisplaySource ',' '),
274 uvi.TheirUserToken,
275 hmpi.MeasurementID,
276 @startDate,
277 @endDate,
278 TrackerID
279 FROM #HoldMeasurePullInfo hmpi
280 CROSS JOIN UserValidicID uvi
281 WHERE hmpi.MeasureSourceType = 'Automated'
282
283 IF EXISTS (SELECT 1 FROM @HoldValidicLookups)
284 BEGIN
285 SELECT @HoldMaxRN = MAX(RowNumber) FROM @HoldValidicLookups
286
287 WHILE @HoldMaxRN >= @CurrentRN
288 BEGIN
289 SELECT @HoldSQL = HoldSQL,
290 @ValidicID = ValidicID,
291 @MeasurementID = MeasurementID,
292 @DateStart = DateStart,
293 @DateEnd = DateEnd,
294 @TrackerID = TrackerID
295 FROM @HoldValidicLookups
296 WHERE RowNumber = @CurrentRN
297
298
299 --Use a prepared SQL statment to insert data from appropriate tables.
300 INSERT INTO #OutData
301 exec sp_executesql
302 @statement = @HoldSQL,
303 @parameters = N'@UserID nvarchar(255), @ValidicID nvarchar(255),@DateStart datetime,@DateEnd DateTime,@MeasurementID int,@TrackerID int',
304 @UserID = @UserID,
305 @ValidicID = @ValidicID,
306 @DateStart = @DateStart,
307 @DateEnd = @DateEnd,
308 @MeasurementID = @MeasurementID,
309 @TrackerID = @TrackerID
310
311
312 SET @CurrentRN = @CurrentRN + 1
313 END
314 END
315 END
316
317
318 -- If the member shared the tracker then pull back the data from the memebr they shared the tracker with
319 DECLARE @SharedTrackers TABLE ([SharedToUserId] UNIQUEIDENTIFIER);
320 INSERT INTO @SharedTrackers
321 ( [SharedToUserId]
322 )
323 SELECT st.[SharedToUserId]
324 FROM [Trackers].[SharedTracker] st
325 JOIN [Trackers].[Tracker] [tracker] ON st.TrackerId = Tracker.TrackerId
326 WHERE st.[SharedByUserId] = @UserId
327 AND st.ShareUntil > GETDATE()
328 AND [tracker].TrackerGuid = @trackerGuid;
329
330 -- If the member did not share the tracker then pull back their data
331 IF NOT EXISTS (SELECT 1 FROM @SharedTrackers)
332 BEGIN
333 INSERT INTO @SharedTrackers
334 ( [SharedToUserId]
335 )
336 VALUES (@UserId);
337 END
338
339 --Get associated tracker data, and sort out the prefered measures.
340 ;WITH TrackerDef AS ( SELECT tmm.[MeasurementId],
341 m.[Name] [MeasurementName],
342 m.[MeasurementTypeId],
343 m.[Description] AS MeasurementDescription,
344 mt.[Name] [MeasurementTypeName],
345 m.[UnitId],
346 u.[Name] [UnitName],
347 u.[Description] [UnitDescription],
348 u.[DisplayName] [UnitDisplayName],
349 u.[Abbreviation],
350 tmm.[DisplayOrder],
351 tmm.[IsRequired]
352 FROM [Trackers].[TrackerMeasurementMap] tmm
353 JOIN [dbo].[Measurement] m
354 ON m.[MeasurementId] = tmm.[MeasurementId]
355 JOIN [dbo].[MeasurementType] mt
356 ON mt.[MeasurementTypeId] = m.[MeasurementTypeId]
357 JOIN [dbo].[Unit] u
358 ON u.[UnitId] = m.[UnitId]
359 JOIN [Trackers].[Tracker] [tracker]
360 ON [tmm].[TrackerId] = [tracker].[TrackerId]
361 WHERE [tracker].[TrackerGuid] = @trackerGuid
362 ),
363 RawOut AS
364 (
365 SELECT od.[userID],
366 MeasurementID,
367 od.DataReportedRangeStartTime StartDate,
368 od.RowUpdatedDate,
369 od.DataReportedRangeEndTime EndDate,
370 od.DataValue,
371 od.SourceName,
372 od.Preference,
373 [MeasurementName],
374 [MeasurementTypeId],
375 [MeasurementTypeName],
376 [MeasurementDescription],
377 [UnitId],
378 [UnitName],
379 [UnitDescription],
380 [UnitDisplayName],
381 [Abbreviation],
382 [DisplayOrder],
383 [IsRequired],
384 Row_Number() OVER(Partition By userid, measurementid,od.DataReportedRangeStartTime
385 ORDER BY od.RowUpdatedDate desc) RowNumber --Removing the order by od.DataValue since we need to get the Latest value for the day
386 FROM #OutData od
387 JOIN TrackerDef td
388 ON td.MeasurementId = od.MeasurmentID
389 JOIN @SharedTrackers st
390 ON st.[SharedToUserId] = od.[UserId]
391 )
392 SELECT CAST([userID] AS UNIQUEIDENTIFIER) UserID,
393 DataValue,
394 MeasurementId,
395 StartDate [DataReportedRangeStartDatetime],
396 EndDate [DataReportedRangeEndDatetime],
397 SourceName AS Source,
398 [MeasurementName],
399 [MeasurementTypeId],
400 [MeasurementTypeName],
401 [MeasurementDescription],
402 [UnitId],
403 [UnitName],
404 [UnitDescription],
405 [UnitDisplayName],
406 [Abbreviation],
407 [DisplayOrder],
408 [IsRequired],
409 [RowNumber]
410 FROM RawOut
411 ORDER BY StartDate desc,SourceName
412
413
414 SELECT @TrackerID= ISNULL(@TrackerID,MAX(TrackerID))
415 FROM #HoldMeasurePullInfo
416
417 --Insert Tracker view event into the tracker tracker table
418 EXEC [Trackers].[usp_UserTrackerUsage_SaveView] @UserId, @trackerId
419
420 DROP TABLE #OutData
421 DROP TABLE #HoldMeasurePullInfo
422
423 SELECT DISTINCT
424 ci.ChallengeInstanceGuid,
425 ISNULL(cil.Title, ctl.Title) as Title,
426 ctl.[Description],
427 ISNULL(cil.ShortDescription,ctl.ShortDescription) as ShortDescription,
428 ct.HasStandings,
429 cip.IsActive,
430 ISNULL(ci.ImagePath,ct.ImagePath) as ImagePath,
431 ci.ChallengeTypeId,
432 ci.MaxParticipants,
433 ci.EndDate,
434 ci.StartDate,
435 ci.EnrollmentEndDate as EnrollByDate,
436 ct.Duration,
437 cc.Category,
438 CASE WHEN ci.ChallengeTypeId = 1 THEN
439 ( SELECT COUNT(1)
440 FROM [Challenges].[ChallengeTemplate] ct
441 JOIN [Challenges].[ChallengeInstance] ci
442 ON ct.ChallengeTemplateId = ci.ChallengeTemplateId
443 AND ct.ChallengeTemplateId = @ChallengeTemplateId
444 JOIN Challenges.ChallengeInstanceParticipant cip
445 ON ci.ChallengeInstanceId = cip.ChallengeInstanceId AND isActive=1)
446 ELSE
447 ( SELECT COUNT(1)
448 FROM [Challenges].[ChallengeInstanceParticipant]
449 WHERE ChallengeInstanceId = ci.ChallengeInstanceId AND isActive=1) END AS TotalParticipants,
450 CASE WHEN ci.ChallengeTypeId = 1 THEN [Challenges].[GetUserRankPersonalChallenge](@UserGuid, ci.ChallengeInstanceId, 1)
451 ELSE [Challenges].[GetUserRankCompanyChallenge](@UserGuid, ci.ChallengeInstanceId, 1) END AS Standing,
452 ctm.TargetDataValue,
453 CASE WHEN ctm.TargetDataValue IS NOT NULL
454 THEN SUM( CAST(odc.DataValue AS DECIMAL(18, 4))) OVER (PARTITION BY ctrm.TrackerId) END AS DataValue,
455 u.[DisplayName] [UnitDisplayName],
456 crm.Frequency,
457 t.TrackerGuid,
458 t.TrackerSourceTypeId AS TrackerSourceType,
459 AA.[FileName] AS Avatar,
460 VisibleToConnectionStatusId as SharedWithConnectionStatus,
461 ISNULL(ci.ParticipationActionId,ct.ParticipationActionId) as ParticipationActionId,
462 ISNULL(ci.SuccessfulActionId,ct.SuccessfulActionId) as SuccessfulActionId,
463 ISNULL(ci.CompletionActionId,ct.CompletionActionId) as CompletionActionId
464 FROM
465 [Challenges].[ChallengeInstanceParticipant] cip
466 JOIN
467 [Challenges].[ChallengeInstance] ci
468 ON
469 cip.ChallengeInstanceId = ci.ChallengeInstanceId
470 AND ci.ChallengeInstanceGuid = @ChallengeInstanceGuid
471 JOIN
472 [Challenges].[ChallengeTemplate] ct
473 ON
474 ct.ChallengeTemplateId = ci.ChallengeTemplateId
475 JOIN [Challenges].[ChallengeCategory] cc
476 ON cc.ChallengeCategoryId = ct.ChallengeCategoryId
477 JOIN
478 [Challenges].[ChallengeTemplateMeasurementMap] ctm
479 ON
480 ctm.ChallengeTemplateId = ct.ChallengeTemplateId
481 LEFT JOIN
482 [Challenges].[ChallengeRuleMap] crm
483 ON
484 crm.ChallengeTemplateId = ct.ChallengeTemplateId
485 LEFT JOIN
486 [Challenges].[ChallengeTemplate_LOC] ctl
487 ON
488 ctl.ChallengeTemplateId = ct.ChallengeTemplateId
489 AND ctl.LocalizationLanguageID = CASE WHEN EXISTS(SELECT 1 from Challenges.ChallengeTemplate_LOC
490 WHERE ChallengeTemplateId =ct.ChallengeTemplateId AND LocalizationLanguageID = @LanguageId)
491 THEN @LanguageId ELSE 1033 END
492 LEFT JOIN
493 [Challenges].[ChallengeInstance_LOC] cil
494 ON
495 cil.ChallengeInstanceId = ci.ChallengeInstanceId
496 AND cil.LocalizationLanguageID = CASE WHEN EXISTS(SELECT 1 from Challenges.ChallengeTemplate_LOC
497 WHERE ChallengeTemplateId = ct.ChallengeTemplateId AND LocalizationLanguageID = @LanguageId)
498 THEN @LanguageId ELSE 1033 END
499 INNER JOIN
500 [Challenges].[ChallengeTrackerMap] ctrm
501 ON
502 ct.ChallengeTemplateId = ctrm.ChallengeId
503 INNER JOIN [Trackers].Tracker t
504 ON t.TrackerId = ctrm.TrackerId
505 LEFT JOIN
506 #OutDataChallenges odc
507 ON
508 odc.MeasurementId = ctm.MeasurementId
509 AND odc.UserId = @UserGuid
510 AND odc.DataReportedRangeStartDatetime>= CONVERT(date,ci.StartDate) AND odc.DataReportedRangeEndDatetime < CONVERT(date,ci.EndDate)
511 JOIN
512 [dbo].[Measurement] m
513 ON
514 m.[MeasurementId] = ctm.[MeasurementId]
515 JOIN
516 [dbo].[Unit] u
517 ON
518 u.[UnitId] = m.[UnitId]
519 LEFT JOIN
520 tbl_ActorAvatar AA
521 ON AA.ActorId = @ActorId
522 LEFT JOIN
523 Social.SharedFeature sf
524 ON sf.FeatureGuid = ci.ChallengeInstanceGuid AND sf.ActorId = @ActorId
525 WHERE
526 cip.UserId = @UserGuid
527
528 SELECT
529 crem.[Value],
530 crem.[EvaluatedDate],
531 CASE
532 WHEN crm.Frequency =1
533 THEN
534 DATEPART(DAYOFYEAR,EvaluatedDate)
535 WHEN crm.Frequency=2
536 THEN
537 DATEPART(WEEK,EvaluatedDate)
538 WHEN crm.Frequency =4
539 THEN
540 DATEPART(MONTH,EvaluatedDate)
541 END AS [Frequency]
542 FROM
543 [Challenges].[ChallengeRuleEvaluationMap] crem
544 JOIN
545 [Challenges].[ChallengeInstanceParticipant] cip
546 ON cip.ChallengeInstanceParticipantId = crem.ChallengeInstanceParticipantId
547 AND
548 cip.UserId = @UserGuid
549 JOIN
550 [Challenges].[ChallengeInstance] ci
551 ON ci.ChallengeInstanceId = cip.ChallengeInstanceId
552 AND
553 ci.ChallengeInstanceGuid = @ChallengeInstanceGuid
554 JOIN
555 [Challenges].[ChallengeTemplate] ct
556 ON ct.ChallengeTemplateId = ci.ChallengeTemplateId
557 JOIN
558 [Challenges].[ChallengeRuleMap] crm
559 ON crm.ChallengeTemplateId = ct.ChallengeTemplateId
560 WHERE
561 crem.EvaluatedDate BETWEEN CONVERT(date, ci.StartDate) AND CONVERT(date, ci.EndDate)
562 ORDER BY [EvaluatedDate], [Frequency]
563
564 SELECT
565 acm.ActionID, pmacm.InstancePoints AS InstancePoints , it.IncentiveTypeName,it.IncentiveTypeId, bg.Name as BadgeName, bg.BadgeUrl, bg.BadgeClass
566 FROM tbl_ActionCategoryMap acm join tbl_PointsMatrixActionCategoryMap pmacm
567 ON ACM.ActionCategoryID = pmacm.ActionCategoryID
568 AND @Now BETWEEN StartDate AND EndDate AND PointsMatrixID = @PointsMatrixID
569 join [lookups].[IncentiveType] it
570 ON pmacm.IncentiveTypeId = it.IncentiveTypeId
571 left join [dbo].[Badge] bg
572 ON pmacm.BadgeId = bg.BadgeId
573END