· 8 years ago · May 17, 2018, 08:56 PM
1USE [SDS_DevSchoolDistrict]
2GO
3
4
5SELECT dbo.WidgetDataForStudentAnnualScheduleAsXml(432, 12, DEFAULT, DEFAULT, 1, 1)
6GO
7
8-- =============================================
9-- Author: School Data Solutions
10-- Create date: <Create Date, ,>
11-- Description: <Description, ,>
12-- Copyright 2011-2012 School Data Solutions, All Rights Reserved
13-- =============================================
14ALTER FUNCTION [dbo].[WidgetDataForStudentAnnualScheduleAsXml]
15( ---------------------------------------
16 @EntityId int,
17 @SchoolYearId int,
18 ---------------------------------------
19 @DataViewMode varchar(200) = 'Standard',
20 @EncryptIds bit = 0,
21 @ApplicationUserId int,
22 @ApplicationUserPersonRoleId int = 1
23) ---------------------------------------
24RETURNS Xml
25AS
26BEGIN
27 /*---------------------------------------------
28 -- Debugging
29 ---------------------------------------------
30 SELECT DISTINCT S.Student
31 FROM StudentSchoolYears SSY
32 JOIN StudentEntityEnrollments SEE
33 ON SSY.StudentId = SEE.StudentId
34 AND SSY.GradeLevelId = 12
35 JOIN Classes C
36 ON SEE.EntityId = C.ClassId
37 AND C.SchoolYearId = SSY.SchoolYearId
38 AND SSY.SchoolYearId = 12
39 JOIN Students S
40 ON SSY.StudentId = S.StudentId
41 ORDER BY S.Student
42
43 DECLARE @EntityId int = 432
44 DECLARE @SchoolYearId int = 12
45 DECLARE @DataViewMode varchar(200) = 'Standard'
46 DECLARE @EncryptIds bit = 0
47 DECLARE @ApplicationUserId int = 1
48 DECLARE @ApplicationUserPersonRoleId int = 1
49 SELECT dbo.WidgetDataForStudentAnnualScheduleAsXml(@EntityId, @SchoolYearId, @DataViewMode, @EncryptIds, @ApplicationUserId, @ApplicationUserPersonRoleId)
50 ---------------------------------------------*/
51
52
53
54 ---------------------------------------------
55 -- Prepare for performance metrics
56 ---------------------------------------------
57 DECLARE @XmlAssemblyStartTime time
58 DECLARE @XmlAssemblyEndTime time
59 DECLARE @XmlAssemblyTimeInMilliseconds int
60 SET @XmlAssemblyStartTime = GETDATE()
61 ---------------------------------------------
62
63
64 ---------------------------------------------
65 -- Declare the return variable here
66 ---------------------------------------------
67 DECLARE @Xml XML
68 SET @Xml = '<Xml xmlAssemblyTimeInMilliseconds=""></Xml>'
69
70
71
72
73 -- Set default of current year if a year was nor provided
74 SET @SchoolYearId = ISNULL(@SchoolYearId,dbo.SchoolYearIdValue(NULL))
75
76
77 -- Get the last day of the school year (this is for convenience)
78 DECLARE @SchoolYearEndDate Date
79 SELECT @SchoolYearEndDate = SchoolYearEndDate
80 FROM SchoolYears
81 WHERE SchoolYearId = @SchoolYearId
82
83
84
85
86 -- The intention is to establish an XML shape of a grid displaying
87 -- the students schedules; so it's a long way down from here...
88
89
90
91
92 ---------------------------------------------
93 -- Detemrine how many columns we will need in the grid
94 ---------------------------------------------
95
96
97 -- Columns are based on the Least Common Multiple (LCM)
98 -- of the distinct smallest divisions utilized by
99 -- the classes in a student's schedule.
100 --
101 -- So, first thing we do is get a set of all the term types
102 -- being used (we have to make sure to insert them in ascending
103 -- order)
104 DECLARE @Terms TABLE (
105 RecordNumber int IDENTITY(1,1),
106 Divisions int,
107 OriginalDivisions int --we need to preserve the original value for later
108 )
109
110 DECLARE @TermColumnCount int
111
112 INSERT @Terms (Divisions,OriginalDivisions)
113 SELECT *
114 FROM (
115 -- Perform the initial selection in a subquery so we can order the results
116 SELECT DISTINCT
117 ISNULL(RTType.ClassTermCountPerYear,
118 ISNULL(CETType.ClassTermCountPerYear,
119 ISNULL(CTType.ClassTermCountPerYear,1))) as Divisions,
120 ISNULL(RTType.ClassTermCountPerYear,
121 ISNULL(CETType.ClassTermCountPerYear,
122 ISNULL(CTType.ClassTermCountPerYear,1))) as OriginalDivisions
123 FROM dbo.StudentClassEnrollmentsForYearOnDate(@EntityId,@SchoolYearId,
124 CASE
125 WHEN @SchoolYearEndDate < GETDATE()
126 THEN NULL
127 ELSE GETDATE()
128 END ) SCE
129 JOIN Classes C
130 ON SCE.EntityId = C.ClassId
131 LEFT
132 JOIN ClassTerms CT
133 ON C.ClassTermId = CT.ClassTermId
134 LEFT
135 JOIN ClassTermTypes CTType
136 ON CT.ClassTermTypeId = CTType.ClassTermTypeId
137 LEFT
138 JOIN ClassTermTypes CETType
139 ON C.CreditEarningTermTypeId = CETType.ClassTermTypeId
140 LEFT
141 JOIN ClassTermTypes RTType
142 ON C.ReportingTermTypeId = RTType.ClassTermTypeId
143 ) DivisionTable
144 ORDER
145 BY Divisions ASC
146
147
148
149
150 -- Next, determine the LCM of those, based somewhat on a
151 -- Sieve of Erastothenes principle, where we go through each
152 -- term, and divide out any lesser term that is a factor;
153 -- for example if we have (2,3,6) this becomes (2,3,1) since
154 -- 2 and 3 are factors of 6
155 --
156 -- After this is done, we are (effectively) left with a
157 -- prime factorization of the LCM
158 --
159 -- We need a loop to do this, as we have to complete the reduction
160 -- of each term before moving on.
161 -- For example, if we have (2,3,6,18)
162 -- Pass 1: (2,3,3,9)
163 -- Pass 2: (2,3,1,3)
164 -- Pass 3: (2,3,1,3)
165 -- Pass 4: (2,3,1,3)
166 -- LCM: 2 * 3 * 1 * 3 = 18
167 --
168 -- If we did this with a single Update statement without a loop,
169 -- we would end up with:
170 -- (2,3,6,18) => (2,3,1,<1)
171 -- because the 18 would be divided by the 2, 3, and the 6
172 -- and LCM: 2 * 3 * 1 * <1 = ... not good
173 --
174 DECLARE @RecordNumber int
175 DECLARE @MaxRecordNumber int
176
177 SET @RecordNumber = 0
178 SET @MaxRecordNumber = 0
179 SELECT @MaxRecordNumber = MAX(RecordNumber) FROM @Terms
180
181 WHILE @RecordNumber < @MaxRecordNumber
182 BEGIN
183 SET @RecordNumber = @RecordNumber + 1
184 UPDATE @Terms
185 SET Divisions = Divisions /
186 ISNULL( ( SELECT POWER(10.,SUM(LOG10(T2.Divisions)))
187 FROM @Terms T2
188 WHERE T.Divisions > T2.Divisions
189 AND T.Divisions % T2.Divisions = 0 ),1)
190 FROM @Terms T
191 WHERE RecordNumber = @RecordNumber
192 END
193
194
195 -- Now we multiply the terms to get the LCM (which we will call @TermColumnCount)
196 SELECT @TermColumnCount = POWER(10.,SUM(LOG10(Divisions)))
197 FROM @Terms
198
199 --PRINT @TermColumnCount
200 --SELECT * FROM @Terms
201
202
203
204
205
206
207 ---------------------------------------------
208 -- Determine the Headers
209 ---------------------------------------------
210
211
212 -- Ultimately we will want appropriate column headers in our
213 -- grid. We'll grab all possible headers right now and filter out what is
214 -- not actually used later.
215 DECLARE @ColumnHeaders TABLE (
216 Header varchar(100),
217 ColumnStart int,
218 ColumnStop int,
219 RelativeRow int,
220 AbsoluteRow int
221 )
222
223 INSERT @ColumnHeaders (Header,ColumnStart,ColumnStop,RelativeRow)
224 SELECT DISTINCT
225 CT.ClassTermAbbreviation,
226 (@TermColumnCount / CTT.ClassTermCountPerYear) * (CT.ClassTermOrdinalByType - 1) + 1,
227 (@TermColumnCount / CTT.ClassTermCountPerYear) * CT.ClassTermOrdinalByType,
228 CTT.ClassTermCountPerYear
229 FROM ClassTerms CT
230 JOIN ClassTermTypes CTT
231 ON CT.ClassTermTypeId = CTT.ClassTermTypeId
232 JOIN (SELECT DISTINCT
233 OriginalDivisions as ClassTermCountPerYear
234 FROM @Terms) CG
235 ON CTT.ClassTermCountPerYear <= CG.ClassTermCountPerYear
236
237
238
239
240
241
242
243 ---------------------------------------------
244 -- Build the Class Grid
245 ---------------------------------------------
246
247 -- Determining the start and stop terms of a class can be derived from the
248 -- ClassTermId, but keep in mind some people have odd subsets out there
249 -- (such as 2 of 3 trimesters). In such a case it would be better to do a date
250 -- analysis based on the start and stop dates of the class.
251 --
252 -- This bit here figures out the maximum and minimum class start dates in the
253 -- scehdule, establishing a date range that the grid represents.
254 --
255 -- This is not fully utilized at this point, but when we switch to more of a date
256 -- centered approach (rather than a numerical term approach) this will be needed.
257 --
258 -- As it is now, these are still used when adjusting for a student that took less
259 -- than a full class.
260
261 DECLARE @MinStartDate date
262 DECLARE @MaxEndDate date
263 DECLARE @DaysPerColumn decimal(18,4)
264
265 SELECT @MinStartDate = MIN(C.ClassStartDate),
266 @MaxEndDate = MAX(C.ClassEndDate)
267 FROM dbo.StudentClassEnrollmentsForYearOnDate(@EntityId,@SchoolYearId,
268 CASE
269 WHEN @SchoolYearEndDate < GETDATE()
270 THEN NULL
271 ELSE GETDATE()
272 END) SCE
273 JOIN Classes C
274 ON SCE.EntityId = C.ClassId
275
276 SET @DaysPerColumn = DATEDIFF(day,@MinStartDate,@MaxEndDate) / @TermColumnCount
277
278
279
280 --
281 -- Now we can build a table to hold each of the students classes and
282 -- what the start and stop columns should be for the class
283 --
284 -- First we need all the date information for the class: both when
285 -- the class starts and stops as well as when the student's enrollment
286 -- starts and stops
287 --
288 --
289 -- Since this is a schedule, only those classes that are associated with a
290 -- period will be considered.
291 --
292 DECLARE @ClassGrid TABLE (
293 ClassId int,
294 ClassStartDate date,
295 ClassEndDate date,
296 ClassColumnStart int,
297 ClassColumnStop int,
298 EffectiveStartDate date,
299 EffectiveEndDate date,
300 EffectiveColumnStart int,
301 EffectiveColumnStop int,
302 Divisions int,
303 Term int,
304 ClassDateDiff int,
305 EffectiveDateDiff int
306 )
307
308 INSERT @ClassGrid
309 SELECT DISTINCT
310 C.ClassId,
311 C.ClassStartDate,
312 C.ClassEndDate,
313 (@TermColumnCount / CTT.ClassTermCountPerYear) * (CT.ClassTermOrdinalByType - 1) + 1,
314 (@TermColumnCount / CTT.ClassTermCountPerYear) * CT.ClassTermOrdinalByType,
315 CASE
316 WHEN SCE.StudentEntityEnrollmentStartDate < C.ClassStartDate
317 THEN C.ClassStartDate
318 ELSE SCE.StudentEntityEnrollmentStartDate
319 END,
320 CASE
321 WHEN ISNULL(SCE.StudentEntityEnrollmentStopDate,C.ClassEndDate) >= C.ClassEndDate
322 THEN C.ClassEndDate
323 ELSE SCE.StudentEntityEnrollmentStopDate
324 END,
325 NULL,
326 NULL,
327 ISNULL(CTT.ClassTermCountPerYear,1),
328 ISNULL(CT.ClassTermOrdinalByType,1),
329 NULL,
330 NULL
331 FROM dbo.StudentClassEnrollmentsForYearOnDate(@EntityId,@SchoolYearId,
332 CASE
333 WHEN @SchoolYearEndDate < GETDATE()
334 THEN NULL
335 ELSE GETDATE()
336 END) SCE
337 JOIN Classes C
338 ON SCE.EntityId = C.ClassId
339 LEFT
340 JOIN ClassTerms CT
341 ON C.ClassTermId = CT.ClassTermId
342 LEFT
343 JOIN ClassTermTypes CTT
344 ON CT.ClassTermTypeId = CTT.ClassTermTypeId
345 JOIN Classes_ClassPeriods CCP
346 ON C.ClassId = CCP.ClassId
347
348
349 -- With the dates, we can determine the effective start and stop columns if the student
350 -- had start and stop dates different than the class
351 UPDATE @ClassGrid
352 SET EffectiveColumnStart =
353 CASE
354 WHEN EffectiveStartDate <= ClassStartDate
355 THEN ClassColumnStart
356 ELSE ClassColumnStart + ROUND(DATEDIFF(day,ClassStartDate,EffectiveStartDate) / @DaysPerColumn,1)
357 END,
358 EffectiveColumnStop =
359 CASE
360 WHEN EffectiveEndDate >= ClassEndDate
361 THEN ClassColumnStop
362 ELSE ClassColumnStop - ROUND(DATEDIFF(day,EffectiveEndDate,ClassEndDate) / @DaysPerColumn,1)
363 END
364
365 UPDATE @ClassGrid
366 SET EffectiveColumnStart = 1
367 WHERE EffectiveColumnStart = 0
368
369 UPDATE @ClassGrid
370 SET EffectiveColumnStart = @TermColumnCount
371 WHERE EffectiveColumnStart > @TermColumnCount
372
373
374 ---------------------------------------------
375 --SELECT * FROM @ClassGrid
376 ---------------------------------------------
377
378
379
380
381 ---------------------------------------------
382 -- Build the Grade Grid
383 ---------------------------------------------
384
385 -- Next, we need to do something very similar for the grades,
386 -- but we don't have to worry about start and stop dates as they
387 -- are based solely on term numbers
388 --
389 -- (again, only classes that are scheduled into a period)
390 --
391 DECLARE @GradeGrid TABLE (
392 StudentClassGradeId int,
393 DivisionNumber int,
394 Divisions int,
395 ColumnStart int,
396 ColumnStop int
397 )
398
399 INSERT @GradeGrid
400 SELECT SCG.StudentClassGradeId,
401 CT.ClassTermOrdinalByType,
402 CTT.ClassTermCountPerYear,
403 (@TermColumnCount / CTT.ClassTermCountPerYear) * (CT.ClassTermOrdinalByType - 1) + 1,
404 (@TermColumnCount / CTT.ClassTermCountPerYear) * CT.ClassTermOrdinalByType
405 FROM dbo.StudentClassEnrollmentsForYearOnDate(@EntityId,@SchoolYearId,
406 CASE
407 WHEN @SchoolYearEndDate < GETDATE()
408 THEN NULL
409 ELSE GETDATE()
410 END) SCE
411 JOIN Classes C
412 ON SCE.EntityId = C.ClassId
413 JOIN StudentClassGrades SCG
414 ON SCE.StudentId = SCG.StudentId
415 AND C.ClassId = SCG.ClassId
416 JOIN ClassTerms CT
417 ON SCG.ClassTermId = CT.ClassTermId
418 JOIN ClassTermTypes CTT
419 ON CT.ClassTermTypeId = CTT.ClassTermTypeId
420 JOIN Classes_ClassPeriods CCP
421 ON C.ClassId = CCP.ClassId
422
423
424 --SELECT * FROM @GradeGrid
425
426
427 -- Let's come back to @ColumnHeaders at this point and remove any row that is not
428 -- being used
429 DELETE @ColumnHeaders
430 FROM @ColumnHeaders CH
431 LEFT
432 JOIN @ClassGrid CG
433 ON CH.RelativeRow = CG.Divisions
434 LEFT
435 JOIN @GradeGrid GG
436 ON CH.RelativeRow = GG.Divisions
437 WHERE CG.Divisions IS NULL
438 AND GG.Divisions IS NULL
439
440
441 -- If we use a row with more than 1 divisions, we don't want the 1 division row
442 IF EXISTS ( SELECT *
443 FROM @ColumnHeaders
444 WHERE RelativeRow > 1 )
445 DELETE @ColumnHeaders
446 WHERE RelativeRow = 1
447
448
449 -- Now, let's set the absolute row; this defines the order in which class grade data appears
450 UPDATE @ColumnHeaders
451 SET AbsoluteRow = A.AbsoluteRow
452 FROM @ColumnHeaders CH
453 JOIN (SELECT ROW_NUMBER() OVER(ORDER BY R.RelativeRow) as AbsoluteRow,
454 R.RelativeRow as RelativeRow
455 FROM (
456 SELECT DISTINCT
457 CH1.RelativeRow as RelativeRow
458 FROM @ColumnHeaders CH1) R
459 ) A
460 ON CH.RelativeRow = A.RelativeRow
461
462 --SELECT * FROM @ColumnHeaders
463
464
465
466
467 ---------------------------------------------
468 -- Build the data
469 ---------------------------------------------
470
471 -- At this point, we should be ready to assemble the data
472 --
473 -- Structure will be:
474 -- <XML>
475 -- <AnnualScheduleGrid NumAbsoluteRows="" NumAbsoluteColumns="">
476 -- <AnnualScheduleGridRow Period="" RowName="" >
477 -- <AnnualScheduleGridColumn Period="" RowName="" Column="" ColumnSpan="" ...>
478 --
479 -- Where the ... for each column will contain appropriate data if it is a header,
480 -- class data, or grade data
481
482
483
484 -- We have to retain an association for each Period.TermType.Term.SubRow
485 -- that will be used to link all the data together in the XML document,
486 -- so we'll store that association in a table for quick reference
487 DECLARE @ClassPeriodRowReference TABLE (
488 ClassPeriod varchar(100),
489 ClassId int,
490 StudentClassGradeId int,
491 TermTypeDivisions int,
492 TermDivision int,
493 PeriodClassTermTypeTermRow int,
494 SubRow int,
495 RowName varchar(100)
496 )
497
498 -- set up the class periods;
499 -- keep in mind we want classes to build across a row by
500 -- term
501 INSERT @ClassPeriodRowReference
502 SELECT CP.ClassPeriod,
503 C.ClassId,
504 NULL,
505 ISNULL(CTT.ClassTermCountPerYear,1),
506 ISNULL(CT.ClassTermOrdinalByType,1),
507 ROW_NUMBER() OVER(PARTITION BY CP.ClassPeriod,
508 ISNULL(CTT.ClassTermCountPerYear,1),
509 ISNULL(CT.ClassTermOrdinalByType,1)
510 ORDER BY C.ClassName),
511 0,
512 NULL
513 FROM @ClassGrid CG
514 JOIN Classes C
515 ON CG.ClassId = C.ClassId
516 JOIN Classes_ClassPeriods CCP
517 ON C.ClassId = CCP.ClassId
518 JOIN ClassPeriods CP
519 ON CCP.ClassPeriodId = CP.ClassPeriodId
520 LEFT
521 JOIN ClassTerms CT
522 ON C.ClassTermId = CT.ClassTermId
523 LEFT
524 JOIN ClassTermTypes CTT
525 ON CT.ClassTermTypeId = CTT.ClassTermTypeId
526
527
528 --SELECT * FROM @ClassPeriodRowReference
529
530
531
532 -- set up the class grades
533 -- Class grades will only show up in the first period if a class
534 -- has more than one period; again grades need to progress accurately
535 -- across a single row
536 INSERT @ClassPeriodRowReference
537 SELECT (SELECT MIN(ClassPeriod) FROM @ClassPeriodRowReference WHERE ClassId = C.ClassId),
538 C.ClassId,
539 GG.StudentClassGradeId,
540 ISNULL(CTT.ClassTermCountPerYear,1),
541 ISNULL(CT.ClassTermOrdinalByType,1),
542 (SELECT MIN(PeriodClassTermTypeTermRow) FROM @ClassPeriodRowReference WHERE ClassId = C.ClassId),
543 ROW_NUMBER() OVER(PARTITION BY C.ClassId,
544 ISNULL(CTT.ClassTermCountPerYear,1),
545 ISNULL(CT.ClassTermOrdinalByType,1)
546 ORDER BY C.ClassName)
547 -- this acts like an offset, so the credit earning rows
548 -- don't match up with the report earning rows
549 --
550 -- It is imperitive that a class have only one
551 -- CreditEarningGradeType and
552 -- ReportingGradeType as we have defined
553 + CH.AbsoluteRow - 1,
554 NULL
555 FROM @GradeGrid GG
556 LEFT
557 JOIN (SELECT DISTINCT
558 RelativeRow,
559 AbsoluteRow
560 FROM @ColumnHeaders) CH
561 ON ( GG.Divisions = 1
562 AND CH.RelativeRow = ( SELECT MIN(RelativeRow) FROM @ColumnHeaders ))
563 OR GG.Divisions = CH.RelativeRow
564 JOIN StudentClassGrades SCG
565 ON GG.StudentClassGradeId = SCG.StudentClassGradeId
566 JOIN Classes C
567 ON SCG.ClassId = C.ClassId
568 JOIN ClassTerms CT
569 ON SCG.ClassTermId = CT.ClassTermId
570 JOIN ClassTermTypes CTT
571 ON CT.ClassTermTypeId = CTT.ClassTermTypeId
572
573
574 -- Put the fields together to create the row identifier
575 UPDATE @ClassPeriodRowReference
576 SET RowName = ClassPeriod +
577 '.' +
578 CAST(PeriodClassTermTypeTermRow as varchar(10))+
579 '.' +
580 CAST(SubRow as varchar(10))
581
582 --SELECT * FROM @ClassPeriodRowReference
583
584
585
586
587
588
589
590 ---------------------------------------------
591 -- Start building the XML
592 ---------------------------------------------
593
594
595 ---------------------------------------------
596 -- Build the header rows
597 --
598 -- There is some interest in only showing the major
599 -- divisions of the headers rather than showing both
600 -- the major and minor divisions (i.e. semester only
601 -- instead of semester and quarter).
602 --
603 -- So being, we will omit any header row that has number
604 -- of elements divisable by the number of elements in a
605 -- prior row
606 ---------------------------------------------
607 DECLARE @AnnualScheduleGridHeaderRows XML
608
609 SET @AnnualScheduleGridHeaderRows = (
610 SELECT DISTINCT
611 'H' AS '@Period',
612 'H.' +
613 '1.' +
614 CAST(CH.AbsoluteRow as varchar(10)) AS '@RowName'
615 FROM @ColumnHeaders CH
616
617 -- omit any row that is represented by an equivalent, smaller number of divisions
618 WHERE NOT EXISTS (SELECT *
619 FROM @ColumnHeaders CH1
620 WHERE CH1.RelativeRow <> CH.RelativeRow
621 AND CH.RelativeRow % CH1.RelativeRow = 0)
622 FOR XML PATH('AnnualScheduleGridRow'),ROOT('AnnualScheduleGridRows')
623 )
624
625
626
627
628 ---------------------------------------------
629 -- Build the Class Period rows
630 ---------------------------------------------
631 DECLARE @AnnualScheduleGridRows XML
632
633 SET @AnnualScheduleGridRows = (
634 SELECT DISTINCT
635 RowRef.ClassPeriod AS '@Period',
636 RowRef.RowName AS '@RowName'
637 FROM @ClassPeriodRowReference RowRef
638 FOR XML PATH('AnnualScheduleGridRow'),ROOT('AnnualScheduleGridRows')
639 )
640
641
642
643 ---------------------------------------------
644 -- Build the Header Columns
645 -- Including the Row Title, with an appropriate row span so that table
646 -- element will expand across all header rows
647 ---------------------------------------------
648 DECLARE @AnnualScheduleGridHeaderRowSpanColumn XML
649
650 SET @AnnualScheduleGridHeaderRowSpanColumn = (
651 SELECT 'H' AS '@Period',
652 'H.1.1' AS '@RowName',
653 '1' AS '@Column',
654 '1' AS '@ColumnSpan',
655 'ColumnHeader' AS '@ColumnType',
656 (SELECT COUNT(DISTINCT CH.AbsoluteRow)
657 FROM @ColumnHeaders CH
658
659 -- omit any row that is represented by an equivalent, smaller number of divisions
660 WHERE NOT EXISTS (SELECT *
661 FROM @ColumnHeaders CH1
662 WHERE CH1.RelativeRow <> CH.RelativeRow
663 AND CH.RelativeRow % CH1.RelativeRow = 0)
664 ) AS '@RowSpan',
665 '' AS '@Text'
666 FOR XML PATH('AnnualScheduleGridColumn'),ROOT('AnnualScheduleGridColumns')
667 )
668
669
670 DECLARE @AnnualScheduleGridHeaderColumns XML
671
672 SET @AnnualScheduleGridHeaderColumns = (
673 SELECT DISTINCT
674 'H' AS '@Period',
675 'H.' +
676 '1.' +
677 CAST( (SELECT DISTINCT AbsoluteRow
678 FROM @ColumnHEaders CH1
679 WHERE CH1.RelativeRow = CH.RelativeRow) as varchar(10)) AS '@RowName',
680 CH.ColumnStart + 1 as '@Column',
681 CH.ColumnStop -
682 CH.ColumnStart +
683 1 AS '@ColumnSpan',
684 'Header' AS '@ColumnType',
685 CH.Header as '@Heading',
686 CH.Header AS '@Text'
687 FROM @ColumnHeaders CH
688
689 -- omit any row that is represented by an equivalent, smaller number of divisions
690 WHERE NOT EXISTS (SELECT *
691 FROM @ColumnHeaders CH1
692 WHERE CH1.RelativeRow <> CH.RelativeRow
693 AND CH.RelativeRow % CH1.RelativeRow = 0)
694 FOR XML PATH('AnnualScheduleGridColumn'),ROOT('AnnualScheduleGridColumns')
695 )
696
697
698
699
700
701
702 ---------------------------------------------
703 -- Build the Row Title title columns for each period
704 -- so they will rowspan across all the rows in the period
705 ---------------------------------------------
706 DECLARE @AnnualScheduleGridPeriodRowSpanColumn XML
707
708 SET @AnnualScheduleGridPeriodRowSpanColumn = (
709 SELECT P.ClassPeriod AS '@Period',
710 --RowName AS '@RowName',
711 P.ClassPeriod + '.1.0' AS '@RowName',
712 '1' AS '@Column',
713 '1' AS '@ColumnSpan',
714 'ColumnHeader' AS '@ColumnType',
715 (SELECT COUNT(DISTINCT RowName)
716 FROM @ClassPeriodRowReference
717 WHERE RowName like P.ClassPeriod + '.%'
718 ) AS '@RowSpan',
719 P.ClassPeriod AS '@Text'
720 FROM (SELECT DISTINCT
721 ClassPeriod--,
722 --RowName
723 FROM @ClassPeriodRowReference
724 WHERE RowName like '%0') P
725 FOR XML PATH('AnnualScheduleGridColumn'),ROOT('AnnualScheduleGridColumns')
726 )
727
728
729
730
731
732 ---------------------------------------------
733 -- Build the Class Data Columns
734 ---------------------------------------------
735
736 DECLARE @AnnualScheduleGridClassColumns XML
737
738 SET @AnnualScheduleGridClassColumns = (
739 SELECT
740 RowRef.ClassPeriod AS '@Period',
741 RowRef.RowName AS '@RowName',
742 CG.EffectiveColumnStart + 1 AS '@Column',
743 CG.EffectiveColumnStop -
744 CG.EffectiveColumnStart +
745 1 AS '@ColumnSpan',
746 'ClassTitle' AS '@ColumnType',
747 CASE @EncryptIds
748 WHEN 1
749 THEN CAST(StudentEncryptedIds.EncryptedId as varchar(200))
750 ELSE CAST(SCE.StudentId as varchar(200))
751 END AS '@EntityId',
752 --SCE.StudentEntityEnrollmentStartDate AS '@StudentEntityEnrollmentStartDate',
753 --SCE.CompletionDate AS '@CompletionDate',
754 --SCE.DropDate AS '@DropDate',
755 CASE @EncryptIds
756 WHEN 1
757 THEN CAST(ClassEncryptedIds.EncryptedId as varchar(200))
758 ELSE CAST(C.ClassId as varchar(200))
759 END AS '@ClassId',
760 C.ClassName AS '@ClassName',
761 C.ClassCode AS '@ClassCode',
762 C.ClassName + ' (' + C.ClassCode + ')' AS '@Text'
763 FROM @ClassGrid CG
764 JOIN @ClassPeriodRowReference RowRef
765 ON CG.ClassId = RowRef.ClassId
766 AND RowRef.StudentClassGradeId IS NULL
767 JOIN StudentEntityEnrollments SCE
768 ON CG.ClassId = SCE.EntityId
769 AND SCE.StudentId = @EntityId
770 JOIN EncryptedIds StudentEncryptedIds
771 ON SCE.StudentId = StudentEncryptedIds.Id
772 JOIN dbo.DataViewModeClasses(NULL,@DataViewMode) C
773 ON CG.ClassId = C.ClassId
774 JOIN EncryptedIds ClassEncryptedIds
775 ON C.ClassId = ClassEncryptedIds.Id
776 FOR XML PATH('AnnualScheduleGridColumn'),ROOT('AnnualScheduleGridColumns')
777 )
778
779 --SELECT @AnnualScheduleGridClassColumns
780
781
782
783
784
785 ---------------------------------------------
786 -- Build the Class Grade Columns
787 ---------------------------------------------
788 DECLARE @AnnualScheduleGridGradeColumns XML
789
790 SET @AnnualScheduleGridGradeColumns = (
791 SELECT
792 RowRef.ClassPeriod AS '@Period',
793 RowRef.RowName AS '@RowName',
794 GG.ColumnStart + 1 AS '@Column',
795 GG.ColumnStop -
796 GG.ColumnStart +
797 1 AS '@ColumnSpan',
798 'ClassGrade' AS '@ColumnType',
799 CASE @EncryptIds
800 WHEN 1
801 THEN CAST(StudentEncryptedIds.EncryptedId as varchar(200))
802 ELSE CAST(SCG.StudentId as varchar(200))
803 END AS '@EntityId',
804 CASE @EncryptIds
805 WHEN 1
806 THEN CAST(ClassEncryptedIds.EncryptedId as varchar(200))
807 ELSE CAST(SCG.ClassId as varchar(200))
808 END AS '@ClassId',
809 CG.ClassGrade AS '@ClassGrade',
810 CG.ClassGrade AS '@Text'
811 FROM @GradeGrid GG
812 JOIN @ClassPeriodRowReference RowRef
813 ON GG.StudentClassGradeId = RowRef.StudentClassGradeId
814 JOIN StudentClassGrades SCG
815 ON GG.StudentClassGradeId = SCG.StudentClassGradeId
816 JOIN EncryptedIds StudentEncryptedIds
817 ON SCG.StudentId = StudentEncryptedIds.Id
818 JOIN EncryptedIds ClassEncryptedIds
819 ON SCG.ClassId = ClassEncryptedIds.Id
820 JOIN ClassGrades CG
821 ON SCG.ClassGradeId = CG.ClassGradeId
822 FOR XML PATH('AnnualScheduleGridColumn'),ROOT('AnnualScheduleGridColumns')
823 )
824
825 --SELECT @AnnualScheduleGridGradeColumns
826
827
828 ---------------------------------------------
829 -- Build the Place Holder Columns
830 --
831 -- First, we'll build the set of all possible place holders
832 -- and then take out the ones we don't need
833 ---------------------------------------------
834 DECLARE @ColumnNumbers TABLE (
835 ColumnNumber int
836 )
837 DECLARE @Column int
838 SET @Column = 1
839 WHILE @Column <= @TermColumnCount
840 BEGIN
841 INSERT @ColumnNumbers VALUES(@Column)
842 SET @Column = @Column + 1
843 END
844
845 DECLARE @PlaceHolders TABLE (
846 ClassPeriod varchar(100),
847 RowName varchar(200),
848 ColumnNumber int
849 )
850
851 INSERT @PlaceHolders
852 SELECT
853 R.ClassPeriod AS '@Period',
854 R.RowName AS '@RowName',
855 CN.ColumnNumber AS '@Column'
856 FROM (SELECT DISTINCT
857 ClassPeriod,
858 RowName
859 FROM @ClassPeriodRowReference) R
860 , @ColumnNumbers CN
861
862
863 -- Take out what we are already using
864 DELETE @PlaceHolders
865 FROM @PlaceHolders PH
866 JOIN @ClassPeriodRowReference RowRef
867 ON PH.RowName = RowRef.RowName
868 JOIN @ClassGrid CG
869 ON RowRef.ClassId = CG.ClassId
870 LEFT
871 JOIN @GradeGrid GG
872 ON RowRef.StudentClassGradeId = GG.StudentClassGradeId
873 WHERE PH.ColumnNumber BETWEEN
874 ISNULL(GG.ColumnStart,CG.EffectiveColumnStart)
875 AND
876 ISNULL(GG.ColumnStop,CG.EffectiveColumnStop)
877
878 --SELECT * FROM @PlaceHolders
879
880 DECLARE @AnnualScheduleGridPlaceHolderColumns XML
881
882 SET @AnnualScheduleGridPlaceHolderColumns = (
883 SELECT
884 PH.ClassPeriod AS '@Period',
885 PH.RowName AS '@RowName',
886 PH.ColumnNumber + 1 AS '@Column',
887 1 AS '@ColumnSpan',
888 'PlaceHolder' AS '@ColumnType',
889 '' AS '@Text'
890 FROM @PlaceHolders PH
891 FOR XML PATH('AnnualScheduleGridColumn'),ROOT('AnnualScheduleGridColumns')
892 )
893
894 --SELECT @AnnualScheduleGridPlaceHolderColumns
895
896
897 -- This is the root document in which everything will be assembled
898 DECLARE @AnnualScheduleGrid XML
899
900 SET @AnnualScheduleGrid =
901'<AnnualScheduleGrid NumAbsoluteColumns = "' +
902CAST(@TermColumnCount + 1 as varchar(10)) + -- +1 becuase of the row titles (Period indicators)
903'" NumAbsoluteRows="' +
904CAST((SELECT ISNULL((SELECT COUNT(DISTINCT AbsoluteRow) FROM @ColumnHeaders),0)) +
905 (SELECT ISNULL((SELECT COUNT(DISTINCT RowName) FROM @ClassPeriodRowReference),0))
906 as varchar(10)) +
907'" >
908</AnnualScheduleGrid>'
909
910 SET @AnnualScheduleGrid = dbo.XmlAdopt(@AnnualScheduleGrid,@AnnualScheduleGridHeaderRows,'AnnualScheduleGrid','AnnualScheduleGridRow')
911 SET @AnnualScheduleGrid = dbo.XmlAdopt(@AnnualScheduleGrid,@AnnualScheduleGridRows,'AnnualScheduleGrid','AnnualScheduleGridRow')
912
913
914 SET @AnnualScheduleGrid = dbo.XmlJoin(@AnnualScheduleGrid,@AnnualScheduleGridHeaderRowSpanColumn,'AnnualScheduleGridRow.RowName = AnnualScheduleGridColumn.RowName')
915 SET @AnnualScheduleGrid = dbo.XmlJoin(@AnnualScheduleGrid,@AnnualScheduleGridHeaderColumns,'AnnualScheduleGridRow.RowName = AnnualScheduleGridColumn.RowName')
916 SET @AnnualScheduleGrid = dbo.XmlJoin(@AnnualScheduleGrid,@AnnualScheduleGridPeriodRowSpanColumn,'AnnualScheduleGridRow.RowName = AnnualScheduleGridColumn.RowName')
917 SET @AnnualScheduleGrid = dbo.XmlJoin(@AnnualScheduleGrid,@AnnualScheduleGridClassColumns,'AnnualScheduleGridRow.RowName = AnnualScheduleGridColumn.RowName')
918 SET @AnnualScheduleGrid = dbo.XmlJoin(@AnnualScheduleGrid,@AnnualScheduleGridGradeColumns,'AnnualScheduleGridRow.RowName = AnnualScheduleGridColumn.RowName')
919 SET @AnnualScheduleGrid = dbo.XmlJoin(@AnnualScheduleGrid,@AnnualScheduleGridPlaceHolderColumns,'AnnualScheduleGridRow.RowName = AnnualScheduleGridColumn.RowName')
920 SET @Xml = dbo.XmlAdopt(@Xml,@AnnualScheduleGrid,'Xml','AnnualScheduleGrid')
921
922
923
924
925 ---------------------------------------------
926 DECLARE @SchoolYears Xml
927 ---------------------------------------------
928 SET @SchoolYears = dbo.SchoolYearsByStudentAsXml(@EntityId, @DataViewMode, @EncryptIds, @ApplicationUserId, @ApplicationUserPersonRoleId)
929 ---------------------------------------------
930 --SELECT @SchoolYears AS '@SchoolYears'
931 ---------------------------------------------
932 SET @Xml = dbo.XmlAdopt(@Xml,@SchoolYears,'Xml','SchoolYear')
933
934
935
936
937 ---------------------------------------------
938 -- Track Assembly time
939 ---------------------------------------------
940 SET @XmlAssemblyEndTime = GETDATE()
941 SET @XmlAssemblyTimeInMilliseconds = DATEDIFF(ms, @XmlAssemblyStartTime, @XmlAssemblyEndTime)
942 SET @Xml = dbo.XmlAssignAttributeValues(@Xml, '//Xml', 'xmlAssemblyTimeInMilliseconds', @XmlAssemblyTimeInMilliseconds)
943 ---------------------------------------------
944
945
946 --SELECT @Xml AS '@Xml'
947 ---------------------------------------------
948 RETURN @Xml
949END