1. Psug-Il
  2. Topics

Handy Student Course Assignment Page Fragment

Richard Moeller
 

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

C Albright
 

Very handy – thanks for sharing!!

 

 

Cindy Albright

Data Systems Manager

WARREN TOWNSHIP HIGH SCHOOL

34090 Almond Road

Gurnee, IL 60031

847-548-6775

WTHS signature

 

From: psug-il@groups.io <psug-il@groups.io> On Behalf Of Richard Moeller via groups.io
Sent: Friday, December 16, 2022 11:08 AM
To: psug-il@groups.io
Subject: [psug-il] Handy Student Course Assignment Page Fragment

 

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

Pursuant to Illinois' public records law, this electronic communications to or from Warren Township High School District 121 constitutes a public record. Please be advised that this email may be subject to monitoring and disclosure to third parties.

1 - 2 of 2