[EXTERNAL EMAIL] [psug-il] Handy Student Course Assignment Page Fragment


Stevens, Willie
 

This is great, Rich. Thanks! Just did a quick test in my sandbox and data pulled exactly as expected. Screenshot below.

image.png

Just shared with my coworker who handles SCA and I'm sure he'll be thrilled. Even without the state grade mapping, this should be a huge time saver. I assume he most likely has the mappings memorized at this point as I somehow even know them from assisting a bit over the years.

Thanks again!

On Fri, Dec 16, 2022 at 11:12 AM Richard Moeller <rmoeller@...> wrote:
I created a page fragment for the All Enrollments page that can be used to make it easier to fill in the student course assignments when you end up having to manually enter the data (such as for a student who has left and returned or has multiple enrollments in the same class and the state reporting report is not working). Here is an example of what it looks like (yes, this student attended two days then withdrew to a private school, then re-enrolled about a month later...). We don't have a high school so I didn't bother with a lot of those fields, you would need to add them. I also haven't gotten around to figuring out how to pull the state grade mappings since we just do Pass/Fail/Withdrawn here. Dropped sections have a red section id, and future exit dates are grayed out since ISBE SIS won't accept a future date. Just in time for the January course assignments due date.


"/admin/students/allenrollments.d63_il_sca.content.footer.txt"
~[if#SEC9.security.inrole=9]
<p>CC.STUDENTID: ~([students]id)</p>
[/if#SEC9]
 
~[if#SEC.security.inrole=1,9]
<!-- start of content area -->
<div class="box-round">
        <h2>IL Student Course Assignment Info - ~(yearname): SID# ~([students]state_studentnumber)</h2>
        <table class="grid">
            <thead>
                <tr>
                    <th>School ID</th>
                    <th>State Course Code</th>
                    <th>Term</th>
                    <th>Local Course Code</th>
                    <th>Local Course Title</th>
                    <th>Credit</th>
                    <th>Section ID</th>
                    <th>Course Level</th>
                    <th>Course Setting</th>
                    <th>Start Date</th>
                    <th>Exit Date</th>
                    <th>Language</th>
                    <th>Grade Override/Stored Grade</th>
 
                </tr>
            </thead>
            <tbody>
                ~[tlist_sql;
                        SELECT 
                                cc.SCHOOLID, 
                                ilcou.STATE_COURSE_CODE, 
                                trm.ABBREVIATION, 
                                CC.COURSE_NUMBER,
                                cou.COURSE_NAME,
                                to_CHAR(cou.CREDIT_HOURS,'FM0.000'),
                                CASE 
                                        WHEN cc.SECTIONID < 0 
                                                THEN 'red'
                                                ELSE ''
                                        END, 
                                ABS(cc.SECTIONID),
                                DECODE(ilcou.COURSE_LEVEL,'01','Remedial','02','General','03','Enriched','04','Honors')L,
                                DECODE(ilcou.COURSE_SETTING,'01','Traditional School Day Program','02','Night or After School','03','Online Learning','04','Distance Learning','05','Alternative Placement','06','Other'),
                                to_char(CC.DateEnrolled,'MM/DD/YYYY'), 
                                CASE 
                                        WHEN CALENDAR_EXITDATE.EXITDATE<=TRUNC(SYSDATE) 
                                                THEN ''
                                                ELSE 'gray'
                                        END,
                                TO_CHAR(CALENDAR_EXITDATE.EXITDATE,'MM/DD/YYYY'),
                                lang.DESCRIPTION,
                                COALESCE(ilcc.COMPLETION_STATUS_CODE,sg.GRADES)
                        FROM 
                                CC
                                LEFT OUTER JOIN S_IL_CC_X ilcc ON CC.DCID = ilcc.CCDCID
                                INNER JOIN COURSES cou ON cc.COURSE_NUMBER = cou.COURSE_NUMBER
                                LEFT OUTER JOIN S_IL_CRS_X ilcou ON cou.DCID = ilcou.COURSESDCID
                                INNER JOIN TERMS trm ON ABS(cc.TERMID) = trm.ID AND cc.SCHOOLID = trm.SCHOOLID
                                LEFT OUTER JOIN SECTIONS sec ON ABS(cc.SECTIONID) = sec.ID
                                LEFT OUTER JOIN S_IL_SEC_X ilsec ON sec.DCID = ilsec.SECTIONSDCID
                                LEFT OUTER JOIN (
                                        SELECT 
                                                STUDENTID, 
                                                SECTIONID, 
                                                LISTAGG(STORECODE || '-' || GRADE,' ') WITHIN GROUP (ORDER BY STORECODE) GRADES
                                        FROM 
                                                STOREDGRADES 
                                        GROUP BY STUDENTID, SECTIONID
                                        ) sg ON CC.STUDENTID = SG.STUDENTID AND ABS(CC.SECTIONID) = SG.SECTIONID
                                LEFT OUTER JOIN PS_COMMON_CODE lang
                                        ON lang.CATEGORY = 'IL_LANGUAGE_CODES'
                                        AND COALESCE(ilsec.LANGUAGE_COURSE_TAUGHT,ilcou.LANGUAGE_COURSE_TAUGHT) = lang.CODE
                                LEFT OUTER JOIN (
                                        SELECT 
                                                CC.DCID CCDCID,
                                                MAX(CALENDAR_DAY.DATE_VALUE) EXITDATE
                                        FROM 
                                                CC
                                                JOIN CALENDAR_DAY 
                                                        ON CC.SCHOOLID = CALENDAR_DAY.SCHOOLID
                                                        AND CALENDAR_DAY.INSESSION=1 
                                                        AND CALENDAR_DAY.MEMBERSHIPVALUE=1
                                                        AND CALENDAR_DAY.DATE_VALUE >= CC.DATEENROLLED
                                                        AND TRUNC(CALENDAR_DAY.DATE_VALUE) < TRUNC(CC.DATELEFT)
                                        GROUP BY 
                                                CC.DCID
                                        ) CALENDAR_EXITDATE ON CC.DCID = CALENDAR_EXITDATE.CCDCID
                        WHERE 
                                CC.STUDENTID = ~(curstudid)
                                AND FLOOR(ABS(CC.TERMID)/100) = ~(curyearid)
                                AND COALESCE(ilcou.STATE_EXCLUDEFROMREPORTING,0) <> 1
                        ORDER BY 
                                cc.TERMID, 
                                cc.dateenrolled, 
                                ilcou.STATE_COURSE_CODE
                        ;nonemessage=<tr><td colspan="100%">No Data Found.</td></tr>;]
                    <tr>
                        <td>~(schoolid)</td>
                        <td>~(ilcode) <button type="button" title='Copy "~(ilcode)" to clipboard.' onclick="d63SCAInfoCopy('~(ilcode;js)')" style="float:right;">&#128203;</button></td>
                        <td>~(term)</td>
                        <td>~(coursenumber) <button type="button" title='Copy "~(coursenumber)" to clipboard.' onclick="d63SCAInfoCopy('~(coursenumber;js)')" style="float:right;">&#128203;</button></td>
                        <td>~(coursename) <button type="button" title='Copy "~(coursename)" to clipboard.' onclick="d63SCAInfoCopy('~(coursename;js)')" style="float:right;">&#128203;</button></td>
                        <td>~(credit) <button type="button" title='Copy "~(credit)" to clipboard.' onclick="d63SCAInfoCopy('~(credit;js)')" style="float:right;">&#128203;</button></td>
                        <td style="color: ~(sectionidcolor);">~(sectionid) <button type="button" title='Copy "~(sectionid)" to clipboard.' onclick="d63SCAInfoCopy('~(sectionid;js)')" style="float:right;">&#128203;</button></td>
                        <td>~(courselevel)</td>
                        <td>~(coursesetting)</td>
                        <td>~(startdate) <button type="button" title='Copy "~(startdate)" to clipboard.' onclick="d63SCAInfoCopy('~(startdate;js)')" style="float:right;">&#128203;</button></td>
                        <td style="color: ~(exitdatecolor);">~(exitdate) <button type="button" title='Copy "~(exitdate)" to clipboard.' onclick="d63SCAInfoCopy('~(exitdate;js)')" style="float:right;">&#128203;</button></td>
                        <td>~(language)</td>
                        <td>~(grade)</td>
                    </tr>
                    [/tlist_sql]
            </tbody>
        </table>
</div>
    <script>
    function d63SCAInfoCopy(copytext) {
        var promise = navigator.clipboard.writeText(copytext);
    }
    </script>
 
[/if#SEC]

--
Richard Moeller
Data Manager
East Maine School District 63
Des Plaines, Illinois

PS 22.10 - Self-hosted
Windows Server 2016
3500 Students, 7 Schools



--
Willie Stevens
PowerSchool Manager
Bloom Township High School District 206