· 8 years ago · Aug 28, 2018, 11:00 PM
1USE [SDS_DevSchoolDistrict]
2GO
3
4-- =============================================
5-- Author: School Data Solutions
6-- Create date: <Create Date, ,>
7-- Description: <Description, ,>
8-- Copyright 2011-2012 School Data Solutions, All Rights Reserved
9-- ============================================
10ALTER FUNCTION [dbo].[AssessmentTestGroupScoresByStudentGroupAsXml]
11(
12 @StudentGroupId int,
13 @AssessmentTestGroupId int,
14
15 @AssessmentTestIdList varchar(1000) = NULL,
16 @OrderingAssessmentTestId int = NULL,
17 @OrderingDirection varchar(4) = NULL,
18
19 @DefaultAssessmentCount int = 5,
20 @StudentPageIndex int = 1,
21 @StudentPageSize int = 100,
22
23 @DataViewMode varchar(200) = 'Standard',
24 @EncryptIds bit = 0,
25 @ApplicationUserId int = NULL,
26 @ApplicationUserPersonRoleId int = 1
27)
28RETURNS Xml
29AS
30BEGIN /*--------------------------------------------
31 -- Debugging
32 ---------------------------------------------
33 DECLARE @StudentGroupId int = 51315 --
34 DECLARE @AssessmentTestGroupId int = 12 -- 14
35 ---------------------------------------------
36 DECLARE @AssessmentTestIdList varchar(1000) = '66, 67, 7, 68, 14'
37 DECLARE @OrderingAssessmentTestId int = 67
38 DECLARE @OrderingDirection varchar(4) = 'ASC'
39 DECLARE @DefaultAssessmentCount int = 5
40 ---------------------------------------------
41 DECLARE @StudentPageIndex int = 1
42 DECLARE @StudentPageSize int = 20 -- 200: 480ms; 500
43 ---------------------------------------------
44 DECLARE @DataViewMode varchar(200) = 'Standard'
45 DECLARE @EncryptIds bit = 0
46 DECLARE @ApplicationUserId int = 357
47 DECLARE @ApplicationUserPersonRoleId int = 1
48 -- SELECT dbo.AssessmentTestGroupScoresByStudentGroupAsXml(51315, 12, '66, 67, 7, 68, 14', 66, 'DESC', DEFAULT, DEFAULT, DEFAULT, DEFAULT, 0, 357, 1)
49 -- SELECT dbo.AssessmentTestGroupScoresByStudentGroupAsXml(51315, 12, '66, 67, 7, 68, 14', NULL, 'DESC', DEFAULT, DEFAULT, DEFAULT, DEFAULT, 0, 357, 1)
50 --SELECT dbo.AssessmentTestGroupScoresByStudentGroupAsXml(51315, 12, '66, 67, 7, 68, 14', 66, NULL, DEFAULT, DEFAULT, DEFAULT, DEFAULT, 0, 357, 1)
51 --SELECT dbo.AssessmentTestGroupScoresByStudentGroupAsXml(51315, 12, '66, 67, 7, 68, 14', NULL, NULL, DEFAULT, DEFAULT, DEFAULT, DEFAULT, 0, 357, 1)
52 ---------------------------------------------*/
53
54--SELECT dbo.AssessmentTestGroupScoresByStudentGroupAsXml(51315, 12, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, 0, 357, 1)
55
56 ---------------------------------------------
57 -- Declare the return variable here
58 -- Prepare for performance metrics
59 ---------------------------------------------
60 DECLARE @Xml XML = '<Xml/>'
61 DECLARE @StartTime time = CAST(GETDATE() as time)
62 ---------------------------------------------
63
64
65
66
67
68
69 ---------------------------------------------
70 -- If no @OrderingDirection passed
71 -- default order names ASC (ALphabetical) and score DESC (high-to-low)
72 ---------------------------------------------
73 IF(@OrderingDirection IS NULL)
74 BEGIN
75 IF(@OrderingAssessmentTestId IS NULL)
76 SET @OrderingDirection = 'ASC' -- for names
77 ELSE
78 SET @OrderingDirection = 'DESC' -- for scores
79 END
80 ---------------------------------------------
81
82
83 ---------------------------------------------
84 -- Assessment-Test-Ids
85 -- We include alternate tests using ISNULL(AT.UseAsAlternateForAssessmentTestId, AT.AssessmentTestId)
86 ---------------------------------------------
87 DECLARE @AssessmentTestIds TABLE (
88 AssessmentTestGroupId int,
89 AssessmentTestId int,
90 AssessmentTestOrdinal int,
91 PossiblyEncryptedAssessmentTestId varchar(500),
92 -- IsAlternate bit,
93 ScoreCount int DEFAULT(0),
94 SendScores bit DEFAULT(0),
95 -----------------------------------
96 PRIMARY KEY (AssessmentTestId)
97 )
98 ---------------------------------------------
99 INSERT @AssessmentTestIds(
100 AssessmentTestGroupId,
101 AssessmentTestId,
102 AssessmentTestOrdinal,
103 PossiblyEncryptedAssessmentTestId
104 -- IsAlternate
105 )
106 SELECT AT_G.AssessmentTestGroupId,
107 AT.AssessmentTestId,
108 AT_G.AssessmentTestOrdinalByGroup,
109 AssessmentTestEncryptedIds.EncryptedId
110 -- CASE WHEN(AT.UseAsAlternateForAssessmentTestId IS NOT NULL) THEN 1 ELSE 0 END
111 FROM AssessmentTests_Groups AT_G
112 JOIN AssessmentTests AT
113 ON AT.AssessmentTestId = AT_G.AssessmentTestId
114 --ON ISNULL(AT.UseAsAlternateForAssessmentTestId, AT.AssessmentTestId) = AT_G.AssessmentTestId
115 JOIN EncryptedIds AssessmentTestEncryptedIds
116 ON AT.AssessmentTestId = AssessmentTestEncryptedIds.Id
117 WHERE AT_G.AssessmentTestGroupId = @AssessmentTestGroupId
118 --AND AT_G.AssessmentTestId IN (7, 75, 76)
119 ---------------------------------------------
120 --SELECT * FROM @AssessmentTestIds ORDER BY AssessmentTestOrdinalByGroup
121 ---------------------------------------------
122
123
124
125
126
127
128
129
130
131 ---------------------------------------------
132 -- Student Maximum Assessment Test Scores
133 ---------------------------------------------
134 DECLARE @StudentMaximumAssessmentTestScores TABLE (
135 StudentId int,
136 AssessmentTestId int,
137 StudentAssessmentTestScoreId int,
138 StudentAssessmentTestScore money,
139 AssessmentTestPerformanceLevelId int,
140 StudentAssessmentTestScoreRank int,
141 AssessmentTestOrdinalByGroup int,
142 -----------------------------------
143 PRIMARY KEY (AssessmentTestId, StudentId, StudentAssessmentTestScoreRank)
144 )
145 INSERT @StudentMaximumAssessmentTestScores(
146 StudentId,
147 AssessmentTestId,
148 StudentAssessmentTestScoreId,
149 StudentAssessmentTestScore,
150 AssessmentTestPerformanceLevelId,
151 StudentAssessmentTestScoreRank,
152 AssessmentTestOrdinalByGroup
153 )
154 SELECT SATS.StudentId,
155 -- ISNULL(AT.UseAsAlternateForAssessmentTestId, AT.AssessmentTestId),
156 AT.AssessmentTestId,
157 SATS.StudentAssessmentTestScoreId,
158 SATS.StudentAssessmentTestScore,
159 SATS.AssessmentTestPerformanceLevelId,
160 ROW_NUMBER() OVER(
161 -- PARTITION BY ISNULL(AT.UseAsAlternateForAssessmentTestId, AT.AssessmentTestId), SATS.StudentId
162 PARTITION BY AT.AssessmentTestId, SATS.StudentId
163 ORDER BY SATS.StudentAssessmentTestScore DESC,
164 SATS.StudentAssessmentTestScoreId DESC) AS ScoreRank,
165 ATI.AssessmentTestOrdinal
166 FROM @AssessmentTestIds ATI
167 JOIN StudentAssessmentTestScores SATS
168 ON SATS.AssessmentTestId = ATI.AssessmentTestId
169 JOIN AssessmentTests AT
170 ON AT.AssessmentTestId = ATI.AssessmentTestId
171 JOIN Students_Groups S_G
172 ON SATS.StudentId = S_G.StudentId
173 AND S_G.StudentGroupId = @StudentGroupId
174
175 -- Ensure the Application User has access to view assessment data for each students
176 JOIN ApplicationUserPermissions AUP
177 ON S_G.StudentId = AUP.EntityId
178 AND AUP.PersonRoleId = 8 --student
179 AND AUP.ApplicationUserId = @ApplicationUserId
180 AND AUP.ApplicationUserPersonRoleId = @ApplicationUserPersonRoleId
181 JOIN SecurableItems SI
182 ON SI.SecurableItem = 'Student Assessments'
183 AND AUP.ApplicationSecurityPermissionId >=
184 SI.MinimumApplicationSecurityPermissionId
185 ---------------------------------------------
186 --SELECT * FROM @StudentMaximumAssessmentTestScores SMATS
187 ---------------------------------------------
188
189
190 ---------------------------------------------
191 -- Student Assessment Test Scores
192 ---------------------------------------------
193 DECLARE @StudentAssessmentTestScores TABLE (
194 StudentId int,
195 AssessmentTestId int,
196 StudentAssessmentTestScore money,
197 AssessmentTestOrdinal int,
198 AssessmentTestPerformanceLevelId int,
199 AssessmentTestPerformanceLevel varchar(100),
200 AssessmentTestPerformanceLevelColor varchar(6),
201 IsPassFail bit,
202 -----------------------------------
203 PRIMARY KEY (AssessmentTestId, StudentId)
204 )
205 ---------------------------------------------
206 INSERT @StudentAssessmentTestScores (
207 StudentId,
208 AssessmentTestId,
209 StudentAssessmentTestScore,
210 AssessmentTestOrdinal,
211 AssessmentTestPerformanceLevelId,
212 AssessmentTestPerformanceLevel,
213 AssessmentTestPerformanceLevelColor,
214 IsPassFail
215 )
216 SELECT SATS.StudentId,
217 SATS.AssessmentTestId,
218 SATS.StudentAssessmentTestScore,
219 ATI.AssessmentTestOrdinal,
220 SATS.AssessmentTestPerformanceLevelId,
221 ATPL.AssessmentTestPerformanceLevel,
222 ATPL.AssessmentTestPerformanceLevelColor,
223 AT.AssessmentTestIsPassFail
224 FROM @StudentMaximumAssessmentTestScores SATS
225 JOIN @AssessmentTestIds ATI
226 ON SATS.AssessmentTestId = ATI.AssessmentTestId
227 AND SATS.StudentAssessmentTestScoreRank = 1
228 JOIN AssessmentTests AT
229 ON ATI.AssessmentTestId = AT.AssessmentTestId
230 LEFT
231 JOIN AssessmentTestPerformanceLevels ATPL
232 ON SATS.AssessmentTestPerformanceLevelId = ATPL.AssessmentTestPerformanceLevelId
233 ---------------------------------------------
234 --SELECT * FROM @StudentAssessmentTestScores ORDER BY StudentId, AssessmentTestId
235 ---------------------------------------------
236
237
238 ---------------------------------------------
239 -- Assessment-Test-Score-Counts
240 ---------------------------------------------
241 DECLARE @AssessmentTestScoreCounts TABLE (
242 AssessmentTestId int,
243 ScoreCount int,
244 -----------------------------------
245 PRIMARY KEY (AssessmentTestId)
246 )
247 ---------------------------------------------
248 INSERT @AssessmentTestScoreCounts(
249 AssessmentTestId,
250 ScoreCount
251 )
252 SELECT SATS.AssessmentTestId, COUNT(SATS.StudentId) AS ScoreCount
253 FROM @StudentAssessmentTestScores SATS
254 GROUP
255 BY SATS.AssessmentTestId
256 ---------------------------------------------
257 --SELECT * FROM @AssessmentTestScoreCounts
258 ---------------------------------------------
259
260
261 ---------------------------------------------
262 -- Update Score Counts per Test
263 ---------------------------------------------
264 UPDATE T
265 SET ScoreCount = C.ScoreCount
266 --SELECT T.AssessmentTestId, C.ScoreCount
267 FROM @AssessmentTestIds T
268 JOIN @AssessmentTestScoreCounts C
269 ON T.AssessmentTestId = C.AssessmentTestId
270 ---------------------------------------------
271 --SELECT * FROM @AssessmentTestIds
272 ---------------------------------------------
273
274
275 ---------------------------------------------
276 -- Delete Assessment test id, where no scores exist for students in passed Student-Group
277 ---------------------------------------------
278 --DELETE @AssessmentTestIds
279 --WHERE ScoreCount = 0
280 ---------------------------------------------
281 --SELECT * FROM @AssessmentTestIds
282 ---------------------------------------------
283
284
285
286 ---------------------------------------------
287 -- If a list of tests was passed in, use it
288 ---------------------------------------------
289 IF(@AssessmentTestIdList IS NOT NULL AND @AssessmentTestIdList <> '')
290 BEGIN -------------------------------------------------
291 DECLARE @SplitValues TABLE ( AssessmentTestId varchar(500), Ordinal int IDENTITY(1, 1) )
292 -------------------------------------------------
293 INSERT @SplitValues(AssessmentTestId)
294 SELECT CAST(Value as int)
295 FROM dbo.SplitList(@AssessmentTestIdList, ',')
296 --SELECT * FROM @SplitValues SV
297 -------------------------------------------------
298 UPDATE T
299 SET SendScores = O.SendScores,
300 AssessmentTestOrdinal = O.Ordinal
301 --SELECT T.AssessmentTestOrdinal, O.Ordinal,
302 -- T.SendScores, O.SendScores
303 FROM @AssessmentTestIds T
304 JOIN (
305 SELECT ATI.AssessmentTestId,
306 CASE WHEN(SV.Ordinal IS NOT NULL) THEN 1
307 ELSE 0
308 END AS 'SendScores',
309 ROW_NUMBER() OVER(ORDER BY ISNULL(SV.Ordinal, 99999), ATI.AssessmentTestOrdinal) AS 'Ordinal'
310 FROM @AssessmentTestIds ATI
311 LEFT
312 JOIN @SplitValues SV
313 ON ATI.AssessmentTestId = SV.AssessmentTestId
314 --ORDER
315 -- BY ISNULL(SV.Ordinal, 99999), ATI.AssessmentTestOrdinal
316 ) O
317 ON T.AssessmentTestId = O.AssessmentTestId
318 END -------------------------------------------------
319 ELSE
320 BEGIN -------------------------------------------------
321 UPDATE T
322 SET SendScores = CASE WHEN(O.Ordinal <= @DefaultAssessmentCount) THEN 1 ELSE 0 END,
323 AssessmentTestOrdinal = O.Ordinal
324 --SELECT T.AssessmentTestOrdinal, O.Ordinal,
325 -- T.SendScores, CASE WHEN(O.Ordinal <= @DefaultAssessmentCount) THEN 1 ELSE 0 END AS SendScores
326 FROM @AssessmentTestIds T
327 JOIN (
328 SELECT ATI.AssessmentTestId,
329 ROW_NUMBER() OVER(ORDER BY ATI.ScoreCount DESC, ATI.AssessmentTestOrdinal) AS 'Ordinal'
330 FROM @AssessmentTestIds ATI
331 --ORDER
332 -- BY ATI.AssessmentTestOrdinal
333 ) O
334 ON T.AssessmentTestId = O.AssessmentTestId
335 END -------------------------------------------------
336 ---------------------------------------------
337 --SELECT 'line 341', T.AssessmentTestId, T.SendScores, T.ScoreCount, T.AssessmentTestOrdinal FROM @AssessmentTestIds T ORDER BY T.SendScores DESC, T.ScoreCount DESC,T.AssessmentTestOrdinal
338 ---------------------------------------------
339
340
341 ---------------------------------------------
342 -- Delete student Scores where Score.TestID not in Tests where SendScores=1
343 ---------------------------------------------
344 --SELECT COUNT(*) AS ScoreCount FROM @StudentAssessmentTestScores
345 ---------------------------------------------
346 DELETE @StudentAssessmentTestScores
347 WHERE AssessmentTestId NOT IN (
348 SELECT T.AssessmentTestId
349 FROM @AssessmentTestIds T
350 WHERE T.SendScores = 1
351 )
352 ---------------------------------------------
353 --SELECT COUNT(*) AS ScoreCount FROM @StudentAssessmentTestScores
354 ---------------------------------------------
355
356
357
358
359
360 ---------------------------------------------
361 -- StudentIds
362 ---------------------------------------------
363 DECLARE @StudentIds TABLE (
364 StudentId int,
365 Ordinal int,
366 -----------------------------------
367 PRIMARY KEY (StudentId)
368 )
369 ---------------------------------------------
370 INSERT @StudentIds(StudentId)
371 SELECT DISTINCT S.StudentId
372 FROM @StudentAssessmentTestScores S
373 ---------------------------------------------
374 --SELECT COUNT(*) AS 'StudentCount' FROM @StudentIds
375 ---------------------------------------------
376
377
378
379
380 ---------------------------------------------
381 -- Fetch Student-Count
382 ---------------------------------------------
383 DECLARE @StudentCount int
384 SELECT @StudentCount = COUNT(StudentId) FROM @StudentIds
385 ---------------------------------------------
386 --SELECT @StudentCount AS '@StudentCount'
387 ---------------------------------------------
388
389
390 ---------------------------------------------
391 -- If @Order-By-Assessment-Test-Id is passed in,
392 -- and we are sending scores for that test
393 -- assign student-ordinals accordingly, for ordering and paging
394 -- Gotcha: when ordering by score... must account for NULL scores
395 ---------------------------------------------
396 IF(EXISTS( SELECT 1
397 FROM @AssessmentTestIds AT
398 WHERE AT.AssessmentTestId = @OrderingAssessmentTestId
399 AND AT.SendScores = 1
400 ))
401 BEGIN ---------------------------------
402 DECLARE @StudentOrderingScores TABLE (
403 StudentId int,
404 Score decimal(18, 2),
405 -----------------------------------
406 PRIMARY KEY (StudentId)
407 )
408 ---------------------------------------------
409 INSERT @StudentOrderingScores(StudentId, Score)
410 SELECT SATS.StudentId, SATS.StudentAssessmentTestScore
411 FROM @StudentAssessmentTestScores SATS
412 WHERE SATS.AssessmentTestId = @OrderingAssessmentTestId
413 ---------------------------------------------
414 INSERT @StudentOrderingScores(StudentId, Score)
415 SELECT SI.StudentId, NULL
416 FROM @StudentIds SI
417 WHERE NOT EXISTS ( SELECT 1
418 FROM @StudentOrderingScores SOS
419 WHERE SOS.StudentId = SI.StudentId)
420 ---------------------------------------------
421 --SELECT * FROM @StudentOrderingScores
422 ---------------------------------------------
423 DECLARE @StudentScoreOrdinals TABLE (
424 StudentId int,
425 Score decimal(18, 2),
426 ScoreOrdinal int,
427 -----------------------------------
428 PRIMARY KEY (StudentId)
429 )
430 ---------------------------------------------
431 INSERT @StudentScoreOrdinals(
432 StudentId,
433 Score,
434 ScoreOrdinal)
435 SELECT SOS.StudentId,
436 SOS.Score,
437 ROW_NUMBER() OVER(ORDER BY
438 CASE WHEN(@OrderingDirection = 'DESC')
439 THEN @StudentCount - ISNULL(SOS.Score, 0)
440 ELSE ISNULL(SOS.Score, 99999) -- place NULLs at the end of ASC ordered scores
441 END,
442 ST.StudentId) AS NewOrdinal
443 FROM @StudentIds ST
444 JOIN @StudentOrderingScores SOS
445 ON ST.StudentId = SOS.StudentId
446 ---------------------------------------------
447 --SELECT 'before', S.StudentId, S.LastName, S.FirstName, S.Ordinal, O.Score, O.NewOrdinal FROM @Students S LEFT JOIN @NewStudentOrdinals O ON S.StudentId = O.StudentId ORDER BY S.Ordinal
448 ---------------------------------------------
449 UPDATE S
450 SET Ordinal = SSO.ScoreOrdinal
451 FROM @StudentIds S
452 JOIN @StudentScoreOrdinals SSO
453 ON S.StudentId = SSO.StudentId
454 ---------------------------------------------
455 --SELECT 'after', S.StudentId, S.LastName, S.FirstName, S.Ordinal, O.Score, O.NewOrdinal FROM @Students S LEFT JOIN @NewStudentOrdinals O ON S.StudentId = O.StudentId ORDER BY S.Ordinal
456 END ---------------------------------
457 ELSE
458 BEGIN ---------------------------------------------
459 DECLARE @StudentNameOrdinals TABLE (
460 StudentId int,
461 LastName varchar(100),
462 FirstName varchar(100),
463 NameOrdinal int,
464 -----------------------------------
465 PRIMARY KEY (StudentId)
466 )
467 ---------------------------------------------
468 INSERT @StudentNameOrdinals(
469 StudentId,
470 LastName,
471 FirstName,
472 NameOrdinal)
473
474 SELECT S.StudentId,
475 P.FirstName,
476 P.LastName,
477 ROW_NUMBER() OVER(ORDER BY P.LastName, P.FirstName, P.PersonId)
478 FROM @StudentIds S
479 JOIN dbo.DataViewModePersons(NULL,@DataViewMode) P
480 ON S.StudentId = P.PersonId
481 ---------------------------------------------
482 --SELECT * FROM @StudentNameOrdinals ORDER BY NameOrdinal
483 ---------------------------------------------
484 UPDATE S
485 SET Ordinal = CASE WHEN(@OrderingDirection = 'DESC')
486 THEN @StudentCount - SNO.NameOrdinal
487 ELSE SNO.NameOrdinal
488 END
489 FROM @StudentIds S
490 JOIN @StudentNameOrdinals SNO
491 ON S.StudentId = SNO.StudentId
492 ---------------------------------------------
493 --SELECT * FROM @StudentIds ORDER BY Ordinal
494 END ---------------------------------------------
495 ---------------------------------------------
496
497
498
499
500 ---------------------------------------------
501 -- Delete Students not on current Page
502 ---------------------------------------------
503 DECLARE @FirstStudentIndex int = ((@StudentPageIndex - 1) * @StudentPageSize) + 1
504 DECLARE @LastStudentIndex int = @StudentPageIndex * @StudentPageSize
505 --SELECT @FirstStudentIndex AS '@FirstStudentIndex', @LastStudentIndex AS '@LastStudentIndex'
506 ---------------------------------------------
507 DELETE @StudentIds
508 WHERE Ordinal < @FirstStudentIndex
509 OR Ordinal > @LastStudentIndex
510 ---------------------------------------------
511
512
513
514
515 ---------------------------------------------
516 -- Compose DataPages
517 ---------------------------------------------
518 DECLARE @DataPages TABLE
519 (
520 DataPageIndex int,
521 FirstRecordIndex int,
522 LastRecordIndex int,
523 DataPageRecordCount int
524 )
525 INSERT @DataPages(DataPageIndex, FirstRecordIndex, LastRecordIndex, DataPageRecordCount)
526 SELECT DP.DataPageIndex, FirstRecordIndex, LastRecordIndex, DataPageRecordCount
527 FROM dbo.DataPages(@StudentPageSize, @StudentCount) DP
528 ---------------------------------------------
529 --SELECT * FROM @DataPages
530 ---------------------------------------------
531
532
533
534
535 ---------------------------------------------
536 -- Delete Student Scores for students not on current Page
537 ---------------------------------------------
538 DELETE @StudentAssessmentTestScores
539 WHERE StudentId NOT IN (
540 SELECT S.StudentId
541 FROM @StudentIds S
542 )
543 ---------------------------------------------
544
545
546
547
548 ---------------------------------------------
549 -- Student CROSS JOIN Tests
550 ---------------------------------------------
551 DECLARE @StudentsAndTests TABLE (
552 StudentId int,
553 AssessmentTestId int,
554 PossiblyEncryptedAssessmentTestId varchar(500),
555 AssessmentTestOrdinalByGroup int,
556 -----------------------------------
557 PRIMARY KEY (StudentId, AssessmentTestId)
558 )
559 ---------------------------------------------
560 INSERT @StudentsAndTests
561 SELECT S.StudentId,
562 T.AssessmentTestId,
563 CASE @EncryptIds
564 WHEN 1 THEN T.PossiblyEncryptedAssessmentTestId
565 ELSE CAST(T.AssessmentTestId as varchar(200))
566 END,
567 T.AssessmentTestOrdinal
568 FROM @StudentIds S
569 CROSS
570 JOIN @AssessmentTestIds T
571 WHERE T.SendScores = 1
572 ---------------------------------------------
573 --SELECT * FROM @StudentsAndTests ORDER BY StudentId, AssessmentTestOrdinalByGroup
574 ---------------------------------------------
575
576
577
578
579
580 ---------------------------------------------
581 -- Student Assessment Test Scores with blanks for un-taken tests
582 ---------------------------------------------
583 DECLARE @StudentAssessmentTestScoresWithBlanks TABLE (
584 StudentId int,
585 AssessmentTestId int,
586 PossiblyEncryptedAssessmentTestId varchar(500),
587 AssessmentTestOrdinalByGroup int,
588 StudentAssessmentTestScore money,
589 AssessmentTestPerformanceLevelId int,
590 AssessmentTestPerformanceLevel varchar(100),
591 AssessmentTestPerformanceLevelColor varchar(6),
592 IsPassFail bit,
593 -----------------------------------
594 PRIMARY KEY (StudentId, AssessmentTestId)
595 )
596 ---------------------------------------------
597 INSERT @StudentAssessmentTestScoresWithBlanks(
598 StudentId,
599 AssessmentTestId,
600 PossiblyEncryptedAssessmentTestId,
601 AssessmentTestOrdinalByGroup,
602 StudentAssessmentTestScore,
603 AssessmentTestPerformanceLevelId,
604 AssessmentTestPerformanceLevel,
605 AssessmentTestPerformanceLevelColor,
606 IsPassFail)
607 SELECT SAT.StudentId,
608 SAT.AssessmentTestId,
609 SAT.PossiblyEncryptedAssessmentTestId,
610 SAT.AssessmentTestOrdinalByGroup,
611 SATS.StudentAssessmentTestScore,
612 SATS.AssessmentTestPerformanceLevelId,
613 SATS.AssessmentTestPerformanceLevel,
614 SATS.AssessmentTestPerformanceLevelColor,
615 SATS.IsPassFail
616 FROM @StudentsAndTests SAT
617 LEFT
618 JOIN @StudentAssessmentTestScores SATS
619 ON SAT.StudentId = SATS.StudentId
620 AND SAT.AssessmentTestId = SATS.AssessmentTestId
621 ---------------------------------------------
622
623
624
625
626
627 ---------------------------------------------
628 -- Assessment Test Performance Level Score Counts
629 ---------------------------------------------
630 DECLARE @AssessmentTestPerformanceLevelScoreCounts TABLE (
631 AssessmentTestPerformanceLevelId int,
632 ScoreCount int ,
633 -----------------------------------
634 PRIMARY KEY (AssessmentTestPerformanceLevelId)
635 )
636 ---------------------------------------------
637 INSERT @AssessmentTestPerformanceLevelScoreCounts
638 SELECT SATS.AssessmentTestPerformanceLevelId,
639 COUNT(SATS.StudentId)
640 FROM @StudentAssessmentTestScores SATS
641 GROUP
642 BY SATS.AssessmentTestPerformanceLevelId
643 ---------------------------------------------
644 --SELECT * FROM @AssessmentTestPerformanceLevelScoreCounts
645 ---------------------------------------------
646
647
648
649
650
651
652
653
654
655 ---------------------------------------------
656 -- Students
657 ---------------------------------------------
658 DECLARE @Students TABLE (
659 StudentId int,
660 FirstName varchar(100),
661 LastName varchar(100),
662 DisplayName varchar(300),
663 Ordinal int,
664 PossiblyEncryptedStudentId varchar(500),
665 -----------------------------------
666 PRIMARY KEY (StudentId)
667 )
668 ---------------------------------------------
669 INSERT @Students(
670 StudentId,
671 FirstName,
672 LastName,
673 DisplayName,
674 Ordinal,
675 PossiblyEncryptedStudentId)
676 SELECT P.PersonId,
677 P.FirstName,
678 P.LastName,
679 P.FirstName + ' ' + P.LastName,
680 S.Ordinal,
681 CASE @EncryptIds
682 WHEN 1 THEN StudentEncryptedIds.EncryptedId
683 ELSE CAST(P.PersonId as varchar(200))
684 END
685 FROM @StudentIds S
686 JOIN dbo.DataViewModePersons(NULL,@DataViewMode) P
687 ON S.StudentId = P.PersonId
688 JOIN EncryptedIds StudentEncryptedIds
689 ON S.StudentId = StudentEncryptedIds.Id
690 ---------------------------------------------
691 --SELECT * FROM @Students
692 ---------------------------------------------
693
694
695
696
697
698
699 ---------------------------------------------
700 --
701 ---------------------------------------------
702 DECLARE @AssessmentTestPerformanceLevels TABLE
703 ( -------------------------------------------
704 AssessmentTestPerformanceLevelId int,
705 PossiblyEncryptedAssessmentTestPerformanceLevelId varchar(500),
706 AssessmentTestPerformanceLevel varchar(50),
707 AssessmentTestPerformanceLevelMinimumScore int,
708 AssessmentTestPerformanceLevelMaximumScore int,
709 AssessmentTestPerformanceLevelColor varchar(20),
710 AssessmentTestPerformanceLevelOrdinalByTest int,
711 AssessmentTestPerformanceLevelMeetsStandard bit,
712 AssessmentTestId int,
713 HighestPossibleScore int,
714 ScoreCount int
715 --IsAlternate bit
716 -------------------------------------------
717 PRIMARY KEY (AssessmentTestPerformanceLevelId)
718 )
719 ---------------------------------------------
720 INSERT @AssessmentTestPerformanceLevels (
721 AssessmentTestPerformanceLevelId,
722 PossiblyEncryptedAssessmentTestPerformanceLevelId,
723 AssessmentTestPerformanceLevel,
724 AssessmentTestPerformanceLevelMinimumScore,
725 AssessmentTestPerformanceLevelMaximumScore,
726 AssessmentTestPerformanceLevelColor,
727 AssessmentTestPerformanceLevelOrdinalByTest,
728 AssessmentTestPerformanceLevelMeetsStandard,
729 AssessmentTestId,
730 HighestPossibleScore,
731 ScoreCount
732 -- IsAlternate
733 ) -------------------------------------------
734 SELECT PL.AssessmentTestPerformanceLevelId,
735 CASE @EncryptIds
736 WHEN 1 THEN EPL.EncryptedId
737 ELSE CAST(PL.AssessmentTestPerformanceLevelId as varchar(200))
738 END,
739 AssessmentTestPerformanceLevel,
740 AssessmentTestPerformanceLevelMinimumScore,
741 AssessmentTestPerformanceLevelMaximumScore,
742 AssessmentTestPerformanceLevelColor,
743 AssessmentTestPerformanceLevelOrdinalByTest,
744 CASE
745 WHEN(PL.AssessmentTestPerformanceLevelMinimumScore >= AT.MinimumPassingScore)
746 THEN CAST(1 AS bit)
747 ELSE CAST(0 AS bit)
748 END, --PL.AssessmentTestPerformanceLevelMeetsStandard
749 ATI.AssessmentTestId,
750 AT.HighestPossibleScore,
751 ISNULL(ATPLSC.ScoreCount, 0)
752 -- CASE WHEN(AT.UseAsAlternateForAssessmentTestId IS NOT NULL) THEN 1 ELSE 0 END AS 'IsAlternate'
753 FROM @AssessmentTestIds ATI
754 JOIN AssessmentTests AT
755 ON AT.AssessmentTestId = ATI.AssessmentTestId
756 --ON ISNULL(AT.UseAsAlternateForAssessmentTestId, AT.AssessmentTestId) = ATI.AssessmentTestId
757 AND ATI.SendScores = 1
758 JOIN AssessmentTestPerformanceLevels PL
759 ON AT.AssessmentTestId = PL.AssessmentTestId
760 JOIN EncryptedIds EPL
761 ON PL.AssessmentTestPerformanceLevelId = EPL.Id
762 LEFT
763 JOIN @AssessmentTestPerformanceLevelScoreCounts ATPLSC
764 ON ATPLSC.AssessmentTestPerformanceLevelId
765 = PL.AssessmentTestPerformanceLevelId
766 WHERE ATPLSC.ScoreCount > 0
767 --OR AT.UseAsAlternateForAssessmentTestId IS NULL
768 ---------------------------------------------
769 --SELECT 'Line 678', * FROM @AssessmentTestPerformanceLevels
770 ---------------------------------------------
771
772
773
774
775 ---------------------------------------------
776 --
777 ---------------------------------------------
778 DECLARE @AssessmentTests TABLE
779 ( -------------------------------------------
780 AssessmentTestId int,
781 PossiblyEncryptedAssessmentTestId varchar(500),
782 AssessmentTestLabel varchar(200),
783 ScoreCount int,
784 SendScores bit,
785 AssessmentTestIsPassFail bit,
786 HighestPossibleScore int,
787 LowestPossibleScore int,
788 MinimumPassingScore int,
789 AssessmentTestOrdinalByGroup int,
790 CorrespondingAssessmentTestGroupId int,
791 EncryptedCorrespondingAssessmentTestGroupId varchar(500),
792 CorrespondingAssessmentTestGroup varchar(200),
793 -------------------------------------------
794 PRIMARY KEY (AssessmentTestId)
795 )
796 ---------------------------------------------
797 INSERT @AssessmentTests (
798 AssessmentTestId,
799 PossiblyEncryptedAssessmentTestId,
800 AssessmentTestLabel,
801 ScoreCount,
802 SendScores,
803 AssessmentTestIsPassFail,
804 HighestPossibleScore,
805 LowestPossibleScore,
806 MinimumPassingScore,
807 AssessmentTestOrdinalByGroup,
808 CorrespondingAssessmentTestGroupId,
809 EncryptedCorrespondingAssessmentTestGroupId,
810 CorrespondingAssessmentTestGroup)
811 SELECT AT.AssessmentTestId,
812 CASE @EncryptIds
813 WHEN 1 THEN ATI.POssiblyEncryptedAssessmentTestId
814 ELSE CAST(AT.AssessmentTestId as varchar(200))
815 END,
816 AssessmentTestLabel,
817 ATI.ScoreCount,
818 ATI.SendScores,
819 AssessmentTestIsPassFail,
820 HighestPossibleScore,
821 LowestPossibleScore,
822 MinimumPassingScore,
823 ATI.AssessmentTestOrdinal,
824 ATG.AssessmentTestGroupId,
825 AssessmentTestGroupEncryptedIds.EncryptedId,
826 ATG.AssessmentTestGroup -- AS 'CorrespondingAssessmentTestGroup',
827 FROM @AssessmentTestIds ATI
828 JOIN AssessmentTests AT
829 ON ATI.AssessmentTestId = AT.AssessmentTestId
830 LEFT
831 JOIN AssessmentTestGroups ATG
832 ON AT.CorrespondingAssessmentTestGroupId = ATG.AssessmentTestGroupId
833 AND AT.CorrespondingAssessmentTestGroupId <> @AssessmentTestGroupId
834 LEFT
835 JOIN EncryptedIds AssessmentTestGroupEncryptedIds
836 ON ATG.AssessmentTestGroupId = AssessmentTestGroupEncryptedIds.Id
837 ---------------------------------------------
838 --SELECT 'Line 842', * FROM @AssessmentTests ORDER BY SendScores DESC, AssessmentTestOrdinalByGroup
839 ---------------------------------------------
840
841
842 ---------------------------------------------
843 --
844 ---------------------------------------------
845 DECLARE @AssessmentTestGroups TABLE
846 ( -------------------------------------------
847 AssessmentTestGroupId int,
848 PossiblyEncryptedAssessmentTestGroupId varchar(500),
849 AssessmentTestGroup varchar(500)
850 -------------------------------------------
851 PRIMARY KEY (AssessmentTestGroupId)
852 )
853 ---------------------------------------------
854 INSERT @AssessmentTestGroups (
855 AssessmentTestGroupId,
856 PossiblyEncryptedAssessmentTestGroupId,
857 AssessmentTestGroup)
858 SELECT ATG.AssessmentTestGroupId,
859 CASE @EncryptIds
860 WHEN 1 THEN EID.EncryptedId
861 ELSE CAST(ATG.AssessmentTestGroupId as varchar(200))
862 END,
863 ATG.AssessmentTestGroup
864 FROM AssessmentTestGroups ATG
865 JOIN EncryptedIds EID
866 ON ATG.AssessmentTestGroupId = EID.Id
867 WHERE ATG.AssessmentTestGroupId = @AssessmentTestGroupId
868 ---------------------------------------------
869
870
871
872 ---------------------------------------------
873 -- Assessment-Test-Groups / Assessment-Tests / Performance-Levels
874 ---------------------------------------------
875 DECLARE @AssessmentTestGroup Xml -- varchar(max)
876 ---------------------------------------------
877 SET @AssessmentTestGroup = (
878 SELECT AssessmentTestGroup.PossiblyEncryptedAssessmentTestGroupId AS 'AssessmentTestGroupId',
879 AssessmentTestGroup.AssessmentTestGroup,
880 --AssessmentTestGroup.AssessmentTestGroupIsAutomatic,
881 --AssessmentTestGroup.AssessmentTestSubjectAreaId,
882 --AssessmentTestGroup.AssessmentTestSubjectArea,
883 ---------------------------------------
884 AssessmentTest.PossiblyEncryptedAssessmentTestId AS 'Id',
885 AssessmentTest.AssessmentTestLabel AS 'Label',
886 AssessmentTest.ScoreCount AS 'ScoreCount',
887 dbo.JavascriptBoolean(AssessmentTest.AssessmentTestIsPassFail) AS 'IsPassFail',
888 AssessmentTest.HighestPossibleScore,
889 AssessmentTest.LowestPossibleScore,
890 AssessmentTest.MinimumPassingScore,
891 AssessmentTest.AssessmentTestOrdinalByGroup AS 'Ordinal',
892 AssessmentTest.CorrespondingAssessmentTestGroupId AS 'GroupId',
893 ---------------------------------------
894 AssessmentTestPerformanceLevel.PossiblyEncryptedAssessmentTestPerformanceLevelId AS 'Id',
895 AssessmentTestPerformanceLevel.AssessmentTestPerformanceLevel AS 'PerformanceLevel',
896 AssessmentTestPerformanceLevel.AssessmentTestPerformanceLevelColor AS 'Color',
897 AssessmentTestPerformanceLevel.AssessmentTestPerformanceLevelMaximumScore AS 'MaximumScore',
898 AssessmentTestPerformanceLevel.AssessmentTestPerformanceLevelMinimumScore AS 'MinimumScore',
899 AssessmentTestPerformanceLevel.AssessmentTestPerformanceLevelOrdinalByTest AS 'Ordinal'
900 FROM @AssessmentTestGroups AssessmentTestGroup
901 CROSS
902 JOIN @AssessmentTests AssessmentTest
903 LEFT
904 JOIN @AssessmentTestPerformanceLevels AssessmentTestPerformanceLevel
905 ON AssessmentTest.AssessmentTestId = AssessmentTestPerformanceLevel.AssessmentTestId
906 WHERE AssessmentTest.SendScores = 1
907 --AND AssessmentTest.ScoreCount > 0
908 ORDER
909 BY AssessmentTest.AssessmentTestOrdinalByGroup,
910 AssessmentTestPerformanceLevel.AssessmentTestPerformanceLevelOrdinalByTest,
911 AssessmentTestPerformanceLevel.AssessmentTestPerformanceLevel
912 FOR XML AUTO, ROOT('AssessmentTestGroups')
913 )
914 ---------------------------------------------
915 --SELECT @AssessmentTestGroup AS '@AssessmentTestGroup'
916 ---------------------------------------------
917
918
919
920
921
922 ---------------------------------------------
923 -- Assessment-Tests
924 ---------------------------------------------
925 DECLARE @AssessmentTestsXml Xml -- varchar(max)
926 ---------------------------------------------
927 SET @AssessmentTestsXml = (
928 SELECT AssessmentTest.PossiblyEncryptedAssessmentTestId AS 'Id',
929 AssessmentTest.AssessmentTestLabel AS 'Label',
930 AssessmentTest.ScoreCount AS 'ScoreCount',
931 dbo.JavascriptBoolean(AssessmentTest.SendScores) AS 'Active',
932 --AssessmentTest.AssessmentTestIsPassFail AS 'IsPassFail',
933 --AssessmentTest.HighestPossibleScore,
934 --AssessmentTest.LowestPossibleScore,
935 --AssessmentTest.MinimumPassingScore,
936 AssessmentTest.AssessmentTestOrdinalByGroup AS 'Ordinal'
937
938 FROM @AssessmentTests AssessmentTest
939 WHERE AssessmentTest.SendScores = 1 OR AssessmentTest.ScoreCount > 0
940 ORDER
941 BY AssessmentTest.AssessmentTestOrdinalByGroup
942 FOR XML AUTO, ROOT('AssessmentTests')
943 )
944 ---------------------------------------------
945 --SELECT @AssessmentTestsXml AS '@AssessmentTestsXml'
946 ---------------------------------------------
947
948
949
950
951 ---------------------------------------------
952 -- StudentAssessmentTestScores-Levels
953 ---------------------------------------------
954 DECLARE @StudentsWithAssessmentTestScoresXml Xml -- varchar(max)
955 ---------------------------------------------
956 SET @StudentsWithAssessmentTestScoresXml = (
957 SELECT Student.PossiblyEncryptedStudentId AS 'StudentId',
958 Student.FirstName,
959 Student.LastName,
960 Student.Ordinal,
961 @StudentPageIndex AS 'PageIndex',
962 ----------------
963 StudentAssessmentTestScore.PossiblyEncryptedAssessmentTestId AS 'Id',
964 StudentAssessmentTestScore.AssessmentTestOrdinalByGroup AS 'Ordinal',
965 dbo.TrimZeros(StudentAssessmentTestScore.StudentAssessmentTestScore) AS 'Score',
966 -- StudentAssessmentTestScore.Passed AS 'Passed',
967 dbo.JavascriptBoolean(StudentAssessmentTestScore.IsPassFail) AS 'IsPassFail',
968 StudentAssessmentTestScore.AssessmentTestPerformanceLevel AS 'PerformanceLevel',
969 StudentAssessmentTestScore.AssessmentTestPerformanceLevelId AS 'PerformanceLevelId',
970 StudentAssessmentTestScore.AssessmentTestPerformanceLevelColor AS 'Color'
971 ------------------
972 FROM @Students Student
973 JOIN @StudentAssessmentTestScoresWithBlanks StudentAssessmentTestScore
974 ON Student.StudentId = StudentAssessmentTestScore.StudentId
975 ORDER
976 BY Student.Ordinal,
977 StudentAssessmentTestScore.AssessmentTestOrdinalByGroup
978 FOR XML AUTO,ROOT('StudentsWithAssessmentTestScoresXml')
979 )
980 ---------------------------------------------
981 --SELECT @StudentsWithAssessmentTestScoresXml AS '@StudentsWithAssessmentTestScoresXml'
982 ---------------------------------------------
983
984
985
986 /*---------------------------------------------
987 -- Students
988 ---------------------------------------------
989 DECLARE @StudentsXml Xml -- varchar(max)
990 ---------------------------------------------
991 SET @StudentsXml = (
992 SELECT Student.PossiblyEncryptedStudentId AS 'StudentId',
993 Student.FirstName,
994 Student.LastName,
995 Student.Ordinal,
996 @StudentPageIndex AS 'PageIndex'
997 FROM @Students Student
998 ORDER
999 BY Student.Ordinal
1000 FOR XML AUTO,ROOT('Students')
1001 )
1002 ---------------------------------------------
1003 --SELECT @StudentsXml AS '@StudentsXml'
1004 ---------------------------------------------*/
1005
1006
1007
1008
1009
1010 ---------------------------------------------
1011 DECLARE @StudentPages Xml
1012 ---------------------------------------------
1013 SET @StudentPages = (
1014 SELECT DataPageIndex AS '@PageIndex',
1015 FirstRecordIndex AS '@FirstRecordIndex',
1016 LastRecordIndex AS '@LastRecordIndex',
1017 DataPageRecordCount AS '@PageRecordCount'
1018 FROM @DataPages
1019 FOR XML PATH('StudentPage'), ROOT('StudentPages')
1020 )
1021 ---------------------------------------------
1022 --SELECT @StudentPages AS '@StudentPages'
1023 ---------------------------------------------
1024
1025
1026
1027
1028 /*---------------------------------------------
1029 -- StudentAssessmentTestScores-Levels
1030 ---------------------------------------------
1031 DECLARE @AssessmentTestScoresXml Xml -- varchar(max)
1032 ---------------------------------------------
1033 SET @AssessmentTestScoresXml = (
1034 SELECT Student.Ordinal AS '@Ordinal',
1035 StudentAssessmentTestScore.PossiblyEncryptedAssessmentTestId AS '@Id',
1036 --StudentAssessmentTestScore.AssessmentTestOrdinalByGroup AS '@Ordinal',
1037 dbo.TrimZeros(StudentAssessmentTestScore.StudentAssessmentTestScore) AS '@Score',
1038 -- StudentAssessmentTestScore.Passed AS 'Passed',
1039 StudentAssessmentTestScore.IsPassFail AS '@IsPassFail',
1040 StudentAssessmentTestScore.AssessmentTestPerformanceLevel AS '@PerformanceLevel',
1041 StudentAssessmentTestScore.AssessmentTestPerformanceLevelId AS '@PerformanceLevelId',
1042 StudentAssessmentTestScore.AssessmentTestPerformanceLevelColor AS '@Color'
1043 ------------------
1044 FROM @Students Student
1045 JOIN @StudentAssessmentTestScoresWithBlanks StudentAssessmentTestScore
1046 ON Student.StudentId = StudentAssessmentTestScore.StudentId
1047 ORDER
1048 BY StudentAssessmentTestScore.AssessmentTestOrdinalByGroup,
1049 Student.Ordinal
1050 FOR XML PATH('StudentAssessmentTestScore'),ROOT('StudentAssessmentTestScores')
1051 )
1052 ---------------------------------------------
1053 --SELECT @AssessmentTestScoresXml AS '@AssessmentTestScoresXml'
1054 */---------------------------------------------
1055
1056
1057
1058 ---------------------------------------------
1059 -- Assemble output Xml
1060 ---------------------------------------------
1061 SET @Xml = dbo.XmlAdopt(@Xml,@AssessmentTestGroup,'Xml','AssessmentTestGroup')
1062 SET @StudentPages = dbo.XmlJoin(@StudentPages, @StudentsWithAssessmentTestScoresXml, 'StudentPage.PageIndex=Student.PageIndex')
1063 SET @Xml = dbo.XmlAdopt(@Xml,@StudentPages,'Xml','StudentPages')
1064 SET @Xml = dbo.XmlAdopt(@Xml,@AssessmentTestsXml,'Xml','AssessmentTests')
1065 ---------------------------------------------
1066 --SET @Xml = dbo.XmlAdopt(@Xml,@StudentAssessmentTestScoresXml,'Xml','StudentAssessmentTestScores')
1067 --SET @Xml = dbo.XmlJoin(@Xml,@AssessmentTestScoresXml,'AssessmentTest.Id=StudentAssessmentTestScore.Id')
1068 ---------------------------------------------
1069
1070
1071
1072
1073 ---------------------------------------------
1074 -- Track Assembly time
1075 ---------------------------------------------
1076 DECLARE @XmlAssemblyTimeInMilliseconds int = DATEDIFF(ms, @StartTime, CAST(GETDATE() as time))
1077 SET @Xml = dbo.XmlAssignAttributeValues(@Xml, '//Xml', 'xmlAssemblyTimeInMilliseconds', @XmlAssemblyTimeInMilliseconds)
1078 ---------------------------------------------
1079
1080
1081 --SELECT @Xml AS '@Xml'
1082 ---------------------------------------------
1083 RETURN @Xml
1084END