Standards Assignment Scores

This year our school district went with standards based grading for grades K-6 and one of the requests was that parents/students wanted to be able to view individual assignments and their standards score for a class.  After a lot of looking around I was able to put together a query which seems to work, it JOINs on several tables but it is still quite performant in practice and with the eventual transition to PowerTeacher Pro and its new tables optimization is not high on the list.

We are still using traditional grades for our junior high and high school so I added a conditional to the scores.html page, if the schoolid is 150 or less (our high school is 100 and the jr high is 150 and all the elementary schools and middle school are higher) it shows as usual:

~[if#schoolcheck.~(curschoolid)<=150]
….<!—normal scores page—>
Otherwise it shows the results of the query in a table, which uses the current studentid, sectionid, and schoolid:
[else#schoolcheck]
<table border=”0″ cellpadding=”0″ cellspacing=”0″ align=”center” width=”99%”  class=”tableToGrid”>
                <tr>
                <th data-sortorder=”asc” width=”10%”>Date</th>
                <th width=”25%”>Assignment</th>
                <th width=”40%”>Standard Description</th>
                <th width=”20%”>Standard ID</th>
                <th width=”10%” data-sorttype=”attrInt”>Score</th>
                </tr>
                ~[tlist_sql;
                SELECT
                 To_Char(sa.dateassignmentdue, ‘MM/DD/YYYY’),
                 PSM_Assignment.name,
                 st.name,
                 st.identifier,
                 ss.actualscoreentered
                FROM
                 PSM_AssignmentStandardScore ss
                JOIN
                 PSM_AssignmentStandard ast
                ON
                 ss.assignmentstandardid = ast.id
                JOIN
                 PSM_Standard st
                ON
                 ast.standardid = st.id
                JOIN
                 PSM_SectionEnrollment se
                ON
                 ss.sectionenrollmentid = se.id
                JOIN
                 PSM_Section sct
                ON
                 se.sectionid = sct.id
                JOIN
                 PSM_School sch
                ON
                 sct.schoolid = sch.id
                JOIN
                 PSM_SchoolCourse schcrs
                ON
                 sct.SchoolCourseID = schcrs.id
                JOIN
                 PSM_SectionAssignment sa
                ON
                 ast.sectionassignmentid = sa.id
                JOIN
                 PSM_Assignment
                ON
                 sa.assignmentid = PSM_Assignment.id
                JOIN
                 PSM_Student
                ON
                 PSM_Student.id = se.StudentID
                JOIN
                 Students
                ON
                 Students.Student_Number = PSM_Student.StudentIdentifier
                JOIN
                 PSM_Term term
                ON
                 sct.TermID = term.ID
                WHERE
                 sch.schoolnumber = ~(curschoolid)
                AND
                 Students.id = ~(curstudid)
                AND
                 se.DateEnrolled < To_Date(To_Char(SYSDATE, ‘MM/DD/YYYY’), ‘MM/DD/YYYY’)
                AND
                 se.DateLeft >  To_Date(To_Char(SYSDATE, ‘MM/DD/YYYY’), ‘MM/DD/YYYY’)
                AND
                 sct.id = (SELECT SectionID FROM SYNC_SectionMap WHERE SectionsDCID = ~(sectionid))
                ORDER BY
                 sa.dateassignmentdue, schcrs.SchoolCourseTitle, st.identifier]
                <tr>
                <td><center>~(dueDate)</center></td><td>~(assignmentName)</td><td>~(standardName)</td><td><center>~(identifier)</center></td><td><center>~(scoreEntered)</center></td>
                </tr>
                [/tlist_sql]
                </table>
[/if#schoolcheck]
And produces this output, which provides parents and students the info we were looking for in a sortable table:
ss-sbg-0
One thing that bothered me was that it’s not very intuitive to click the double dash ‘–‘ to view the scores so I customized the /wildcards/att_quicklookmeet_guardian.txt with a conditional to show at link to the scores.html page for SBG assignment detail:
~[if#schoolcheck.~(curschoolid)>150]<br><a href=”/guardian/scores.html?frn=004[ccid]”>View SBG Assignments</a>[/if#schoolcheck]
ss-sbg-1
Maybe there’s a completely better way of doing this but with the conditionals I didn’t have to actually remove or modify the original page other than adding to it.  If you know of a better way please let me know.  And if you know of a way to get something like this working with PTP tables please share that too!
Advertisements
Standards Assignment Scores

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s