Creative and Strange: PowerSchool Transportation Reports

File: ssis_transportation.html

<!DOCTYPE html>
<html>

<head>
<title>Transportation</title>
~[wc:UI_js_includes]
~[wc:commonscripts]
<link href="/images/css/screen.css" rel="stylesheet" media="screen">
<link href="/images/css/print.css" rel="stylesheet" media="print">

</head>
<body>
~[wc:admin_header_frame_css]


<a href="/admin/home.html" target="_top">~[text:psx.html.admin_students.unlimitedcontacts.start_page]</a> >
<a href="home.html?selectstudent=nosearch" target="_top">~[text:psx.html.admin_students.unlimitedcontacts.student_selection]</a> >
Transportation Roster


~[wc:admin_navigation_frame_css]



<table width="100%">
<tr>
<td background="/images/transparent.gif">


<h1>Transportation Roster</h1>


<form name="searchform" action="ssis_transportation.html" method="GET" style="float:left;width:100%">
<table>
<tr>
<td style="background-color:white;">Filter by Bus: </td>
<td style="background-color:white;">
<a href="ssis_transportation.html?busnumber=Bus 1">Bus 1</a> | 
<a href="ssis_transportation.html?busnumber=Bus 2">Bus 2</a> | 
<a href="ssis_transportation.html?busnumber=Bus 3">Bus 3</a> | 
<a href="ssis_transportation.html?busnumber=Bus 4">Bus 4</a> | 
<a href="ssis_transportation.html?busnumber=Bus 5">Bus 5</a> | 
<a href="ssis_transportation.html?busnumber=Bus 6">Bus 6</a> | 
<a href="ssis_transportation.html?busnumber=Bus 7">Bus 7</a> | 
<a href="ssis_transportation.html?busnumber=Bus 8">Bus 8</a> | 
<a href="ssis_transportation.html?busnumber=Van 1">Van 1</a> | 
<a href="ssis_transportation.html?busnumber=Van 2">Van 2</a> | 
<a href="ssis_transportation.html?busnumber=Van 3">Van 3</a> | 
<a href="ssis_transportation.html?busnumber=Van 4">Van 4</a> | 
<a href="ssis_transportation.html?busnumber=Van 5">Van 5</a> | 
<a href="ssis_transportation.html?busnumber=Van 6">Van 6</a> | 
<a href="ssis_transportation.html?busnumber=Van 7">Van 7</a> | 
<a href="ssis_transportation.html?busnumber=ALL">ALL</a>
</td>

</tr>
</table>
</form>

<br><br>
<p id="demo" style="font-size: 1.5em;">Driver and Monitor Information Replaced Here.</p>
<script type="text/javascript">

if ('~(gpv.busnumber)'=='Bus 1') {
document.getElementById("demo").innerHTML = "Driver: Dang Cong Minh (84) 93-356-9755 | Monitor: Dang Thi Thu Hong (84) 93-267-8310";
} else if ('~(gpv.busnumber)'=='Bus 2') {
document.getElementById("demo").innerHTML = "Driver: Mr. Lam (84) 90-690-6030 | Monitor: Le Thi Hoang (84) 93-240-1677";
} else if ('~(gpv.busnumber)'=='Bus 3') {
document.getElementById("demo").innerHTML = "Driver: Duong Hoang Hai (84) 93-262-3161 | Monitor: Nguyen Thi Huong Lan (84) 93-839-3678";
} else if ('~(gpv.busnumber)'=='Bus 4') {
document.getElementById("demo").innerHTML = "Driver: Nguyen Thanh Long (Minh) (84) 90-909-5221 | Monitor: Le Thi My Ngoc (84) 96-183-0728";
} else if ('~(gpv.busnumber)'=='Bus 5') {
document.getElementById("demo").innerHTML = "Driver: Nguyen Huu Duc (84) 90-217-4558 | Monitor: Tran Thi Thu Thao (84) 90-286-0135";
} else if ('~(gpv.busnumber)'=='Bus 6') {
document.getElementById("demo").innerHTML = "Driver: Pham Anh Tuan (84) 93-867-7970 | Monitor: Nguyen Le Kim Thuy (84) 126-711-5315";
} else if ('~(gpv.busnumber)'=='Bus 7') {
document.getElementById("demo").innerHTML = "Driver: Tran Chi Linh (84) 98-853-0258 | Monitor: Ho Thi Thu Trang (84) 90-648-3441";
} else if ('~(gpv.busnumber)'=='Bus 8') {
document.getElementById("demo").innerHTML = "Driver: Mr. Binh (84) 92-444-6698 | Monitor: Lu Vi Thuy (84) 93-760-3832";
} else if ('~(gpv.busnumber)'=='Van 1') {
document.getElementById("demo").innerHTML = "Driver: Duong Quang Hieu (84) 90-316-2641 | Monitor: Nguyen Ngoc (84) 93-805-0819";
} else if ('~(gpv.busnumber)'=='Van 2') {
document.getElementById("demo").innerHTML = "Driver: Mr. Phong (84) 91-914-7306 | Monitor: Phuong Thi My Dung (84) 93-803-2507";
} else if ('~(gpv.busnumber)'=='Van 3') {
document.getElementById("demo").innerHTML = "Driver: Pham Phuc Dai (84) 90-939-3472 | Monitor: Tran Thi Han Hoan (84) 91-553-3467";
} else if ('~(gpv.busnumber)'=='Van 4') {
document.getElementById("demo").innerHTML = "Driver: Vu Thai Hong (Long) (84) 91-360-1602 | Monitor: Duong Thi Lanh (84) 90-314-9296";
} else if ('~(gpv.busnumber)'=='Van 5') {
document.getElementById("demo").innerHTML = "Driver: Nguyen Hoang Thich (84) 93-402-6073 | Monitor: Duong Thi Hien (84) 93-785-4299";
} else if ('~(gpv.busnumber)'=='Van 6') {
document.getElementById("demo").innerHTML = "Driver: Le Hoang An (84) 91-803-1819 | Monitor: Luong Thi Thu Yen (84) 90-253-2862";
} else if ('~(gpv.busnumber)'=='Van 7') {
document.getElementById("demo").innerHTML = "Driver: Nguyen Tan Buu (84) 90-589-6220 | Monitor: Phan Thi Kim Cuc (84) 93-834-2083";
} else {
document.getElementById("demo").innerHTML = ".";
}
</script>

<table id="transportationExport" class="grid" style="width:100%">
<tr>
<th width='300px'></th>
<th>StudentID</th>
<th>Students</th>
<th>Grade/Gender<br>HR Teacher</th>
<th>Address</th>
<th colspan=2>Contact Information</th>
<th>Special Instructions</th>
</tr>

~[tlist_sql;
WITH ROUTE AS (
SELECT t.BUSNUMBER AS BUSNUMBER, s.ID AS STUDENTDCID,
TO_CHAR(TRUNC(t.departuretime/3600),'FM9900') || chr(58) ||
TO_CHAR(TRUNC(MOD(t.departuretime,3600)/60),'FM00') ||
' ' || TO_CHAR(t.address) AS T_ADDRESS,
s.STUDENT_NUMBER as STUDENT_NUMBER,
s.LASTFIRST AS STUDENTLASTFIRST,
DECODE(s.grade_level,0,'K',-2,'EC3',-1,'EC4',TRIM(TO_CHAR(s.grade_level))) AS GRADE,
s.gender AS GENDER,
u.SSIS_HR_TEACHER AS HR_TEACHER,
s.street || '<br>' || u.ssis_student_ward || ', ' || u.ssis_student_district || '<br>' || s.city AS ADDRESS,
TO_CHAR(t.SPECIALINSTRUCTIONS) AS SPECIALINSTRUCTIONS,
ROW_NUMBER() OVER (PARTITION BY t.BUSNUMBER ORDER BY t.BUSNUMBER)AS RN
FROM TRANSPORTATION t
INNER JOIN STUDENTS s ON s.id=t.STUDENTID
INNER JOIN U_StudentsUserFields u ON s.DCID = u.StudentsDCID
WHERE s.ENROLL_STATUS =0
AND t.fromto = 'To'
~[if#1.~(gpv.busnumber)=]
/* NO bus number provided. */
AND t.busnumber = 'zzz'
[else#1]
~[if#2.~(gpv.busnumber)=ALL]
AND t.busnumber LIKE '%'
[else#2]
/* Bus number provided. . */
AND t.busnumber = ('~[gpv:busnumber]')
[/if#2]
[/if#1]
),
StudentsParents AS (
SELECT DISTINCT
DECODE(co.ContactRelationship,'Mother','M','Father','F','Step-Mother','M','Step-Father','F','X') ContactRelationship,
co.StudentDCID AS STUDENTDCID,
co.ContactLastFirst,
pp.PhoneNumberasentered,
e.EmailAddress
FROM
PSSIS_STU_Contact_Act_Emerg co
LEFT JOIN PSSIS_Person_Phone pp on pp.PersonID = co.ContactPersonID AND pp.isPreferred = 1
LEFT JOIN PSSIS_Person_Email e on e.PersonID = co.ContactPersonID AND e.isPrimaryEmailAddress = 1
WHERE co.ContactRelationship in ('Mother','Step-Mother','Father','Step-Father')
)
SELECT ID, BUSNUMBER,
MONITORDETAIL,
STUDENT_NUMBER, STUDENTLASTFIRST, GRADE, GENDER,
HR_TEACHER,
ADDRESS,
Mother,
Father,
SPECIALINSTRUCTIONS
FROM(
SELECT RN AS ID, BUSNUMBER,
'<font size=4><strong>' || BUSNUMBER || '</strong></font>' ||
'<br> .' as MONITORDETAIL,
0 AS STUDENT_NUMBER, '' AS STUDENTLASTFIRST, '' AS GRADE, '' AS GENDER,
'' AS HR_TEACHER,
'' AS ADDRESS,
'' AS SPECIALINSTRUCTIONS,
'' AS Mother,
'' AS Father,
'Group' As RowType
FROM ROUTE r
LEFT JOIN StudentsParents m ON r.StudentDCID = m.StudentDCID AND m.ContactRelationship = 'M'
LEFT JOIN StudentsParents f ON r.StudentDCID = f.StudentDCID AND f.ContactRelationship = 'F'
WHERE RN = 1
UNION ALL
SELECT RN AS ID, BUSNUMBER,
T_ADDRESS AS MONITORDETAIL,
STUDENT_NUMBER, STUDENTLASTFIRST, GRADE, GENDER,
HR_TEACHER,
ADDRESS,
SPECIALINSTRUCTIONS,
m.ContactLastFirst || '<br>' || m.PhoneNumberasentered || '<br>' || m.EmailAddress AS Mother,
f.ContactLastFirst || '<br>' || f.PhoneNumberasentered || '<br>' || f.EmailAddress AS Father,
'Item' As RowType
FROM ROUTE r
LEFT JOIN StudentsParents m ON r.StudentDCID = m.StudentDCID AND m.ContactRelationship = 'M'
LEFT JOIN StudentsParents f ON r.StudentDCID = f.StudentDCID AND f.ContactRelationship = 'F'
)X
ORDER BY BUSNUMBER ASC
, CASE WHEN RowType='Group' THEN 0 ELSE 1 END ASC
, MONITORDETAIL ASC
, STUDENTLASTFIRST ASC
;]
<tr>
<td style="display:none"> ~(ID) </td>
<td style="display:none"> ~(BUSNUMBER) </td>
<td>~(MONITORDETAIL)</td>
<td> ~(STUDENT_NUMBER)</td>
<td> ~(STUDENTLASTFIRST)</td>
<td>~(GRADE) / ~(GENDER) <br> ~(HR_TEACHER)</td>
<td> ~(ADDRESS)</td>
<td> ~(Mother)</td>
<td> ~(Father)</td>
<td> ~(SPECIALINSTRUCTIONS)</td>
</tr>

[/tlist_sql]

</table>


</body>
</html>

File: ssis_transportation_attendance.html

<!DOCTYPE html>
<html>

<head>
<title>Transportation Attendance</title>
~[wc:UI_js_includes]
~[wc:commonscripts]
<link href="/images/css/screen.css" rel="stylesheet" media="screen">
<link href="/images/css/print.css" rel="stylesheet" media="print">

</head>
<body>
~[wc:admin_header_frame_css]


<a href="/admin/home.html" target="_top">~[text:psx.html.admin_students.unlimitedcontacts.start_page]</a> >
<a href="home.html?selectstudent=nosearch" target="_top">~[text:psx.html.admin_students.unlimitedcontacts.student_selection]</a> >
Transportation Attendance


~[wc:admin_navigation_frame_css]



<table width="100%">
<tr>
<td background="/images/transparent.gif">


<h1>Transportation Attendance</h1>
<h3>Period Coverage: ____________________________</h3>


<form name="searchform" action="ssis_transportation_attendance.html" method="GET" style="float:left;width:100%">
<table>
<tr>
<td style="background-color:white;">Filter by Bus: </td>
<td style="background-color:white;">
<a href="ssis_transportation_attendance.html?busnumber=Bus 1">Bus 1</a> | 
<a href="ssis_transportation_attendance.html?busnumber=Bus 2">Bus 2</a> | 
<a href="ssis_transportation_attendance.html?busnumber=Bus 3">Bus 3</a> | 
<a href="ssis_transportation_attendance.html?busnumber=Bus 4">Bus 4</a> | 
<a href="ssis_transportation_attendance.html?busnumber=Bus 5">Bus 5</a> | 
<a href="ssis_transportation_attendance.html?busnumber=Bus 6">Bus 6</a> | 
<a href="ssis_transportation_attendance.html?busnumber=Bus 7">Bus 7</a> | 
<a href="ssis_transportation_attendance.html?busnumber=Bus 8">Bus 8</a> | 
<a href="ssis_transportation_attendance.html?busnumber=Van 1">Van 1</a> | 
<a href="ssis_transportation_attendance.html?busnumber=Van 2">Van 2</a> | 
<a href="ssis_transportation_attendance.html?busnumber=Van 3">Van 3</a> | 
<a href="ssis_transportation_attendance.html?busnumber=Van 4">Van 4</a> | 
<a href="ssis_transportation_attendance.html?busnumber=Van 5">Van 5</a> | 
<a href="ssis_transportation_attendance.html?busnumber=Van 6">Van 6</a> | 
<a href="ssis_transportation_attendance.html?busnumber=Van 7">Van 7</a> | 
<a href="ssis_transportation_attendance.html?busnumber=ALL">ALL</a>
</td>

</tr>
</table>
</form>

<br><br>
<p id="demo" style="font-size: 1.5em;">Driver and Monitor Information Replaced Here.</p>
<script type="text/javascript">

if ('~(gpv.busnumber)'=='Bus 1') {
document.getElementById("demo").innerHTML = "Driver: Dang Cong Minh (84) 93-356-9755 | Monitor: Dang Thi Thu Hong (84) 93-267-8310";
} else if ('~(gpv.busnumber)'=='Bus 2') {
document.getElementById("demo").innerHTML = "Driver: Mr. Lam (84) 90-690-6030 | Monitor: Le Thi Hoang (84) 93-240-1677";
} else if ('~(gpv.busnumber)'=='Bus 3') {
document.getElementById("demo").innerHTML = "Driver: Duong Hoang Hai (84) 93-262-3161 | Monitor: Nguyen Thi Huong Lan (84) 93-839-3678";
} else if ('~(gpv.busnumber)'=='Bus 4') {
document.getElementById("demo").innerHTML = "Driver: Nguyen Thanh Long (Minh) (84) 90-909-5221 | Monitor: Le Thi My Ngoc (84) 96-183-0728";
} else if ('~(gpv.busnumber)'=='Bus 5') {
document.getElementById("demo").innerHTML = "Driver: Nguyen Huu Duc (84) 90-217-4558 | Monitor: Tran Thi Thu Thao (84) 90-286-0135";
} else if ('~(gpv.busnumber)'=='Bus 6') {
document.getElementById("demo").innerHTML = "Driver: Pham Anh Tuan (84) 93-867-7970 | Monitor: Nguyen Le Kim Thuy (84) 126-711-5315";
} else if ('~(gpv.busnumber)'=='Bus 7') {
document.getElementById("demo").innerHTML = "Driver: Tran Chi Linh (84) 98-853-0258 | Monitor: Ho Thi Thu Trang (84) 90-648-3441";
} else if ('~(gpv.busnumber)'=='Bus 8') {
document.getElementById("demo").innerHTML = "Driver: Mr. Binh (84) 92-444-6698 | Monitor: Lu Vi Thuy (84) 93-760-3832";
} else if ('~(gpv.busnumber)'=='Van 1') {
document.getElementById("demo").innerHTML = "Driver: Duong Quang Hieu (84) 90-316-2641 | Monitor: Nguyen Ngoc (84) 93-805-0819";
} else if ('~(gpv.busnumber)'=='Van 2') {
document.getElementById("demo").innerHTML = "Driver: Mr. Phong (84) 91-914-7306 | Monitor: Phuong Thi My Dung (84) 93-803-2507";
} else if ('~(gpv.busnumber)'=='Van 3') {
document.getElementById("demo").innerHTML = "Driver: Pham Phuc Dai (84) 90-939-3472 | Monitor: Tran Thi Han Hoan (84) 91-553-3467";
} else if ('~(gpv.busnumber)'=='Van 4') {
document.getElementById("demo").innerHTML = "Driver: Vu Thai Hong (Long) (84) 91-360-1602 | Monitor: Duong Thi Lanh (84) 90-314-9296";
} else if ('~(gpv.busnumber)'=='Van 5') {
document.getElementById("demo").innerHTML = "Driver: Nguyen Hoang Thich (84) 93-402-6073 | Monitor: Duong Thi Hien (84) 93-785-4299";
} else if ('~(gpv.busnumber)'=='Van 6') {
document.getElementById("demo").innerHTML = "Driver: Le Hoang An (84) 91-803-1819 | Monitor: Luong Thi Thu Yen (84) 90-253-2862";
} else if ('~(gpv.busnumber)'=='Van 7') {
document.getElementById("demo").innerHTML = "Driver: Nguyen Tan Buu (84) 90-589-6220 | Monitor: Phan Thi Kim Cuc (84) 93-834-2083";
} else {
document.getElementById("demo").innerHTML = ".";
}
</script>

<table id="transportationExport" class="grid" style="width:100%">
<tr>
<th>Monitor</th>
<th>Students</th>
<th>Grade</th>
<th>Gender</th>
<th>HR Teacher</th>
<th colspan=2>MON</th>
<th colspan=2>TUE</th>
<th colspan=2>WED</th>
<th colspan=2>THU</th>
<th colspan=2>FRI</th>
<th>Special Instruction</th>
</tr>
<tr>
<td colspan=5></td>
<td>AM</td>
<td>PM</td>
<td>AM</td>
<td>PM</td>
<td>AM</td>
<td>PM</td>
<td>AM</td>
<td>PM</td>
<td>AM</td>
<td>PM</td>
</tr>

~[tlist_sql;
WITH ROUTE AS (
SELECT t.BUSNUMBER AS BUSNUMBER,
'.' AS MONITOR,
s.LASTFIRST AS STUDENTLASTFIRST,
DECODE(s.grade_level,0,'K',-2,'EC3',-1,'EC4',TRIM(TO_CHAR(s.grade_level))) AS GRADE,
s.gender AS GENDER,
u.SSIS_HR_TEACHER AS HR_TEACHER,
TO_CHAR(t.SPECIALINSTRUCTIONS) AS SPECIALINSTRUCTIONS,
ROW_NUMBER() OVER (PARTITION BY t.BUSNUMBER Order By s.LASTFIRST)AS RN
FROM TRANSPORTATION t
INNER JOIN STUDENTS s ON s.id=t.STUDENTID
INNER JOIN U_StudentsUserFields u ON s.DCID = u.StudentsDCID
INNER JOIN U_DEF_EXT_TRANSPORTATION x ON x.TRANSPORTATIONDCID=t.DCID
WHERE s.ENROLL_STATUS =0
AND t.fromto = 'To'
~[if#1.~(gpv.busnumber)=]
/* NO bus number provided. */
AND t.busnumber = 'zzz'
[else#1]
~[if#2.~(gpv.busnumber)=ALL]
AND t.busnumber LIKE '%'
[else#2]
/* Bus number provided. . */
AND t.busnumber = ('~[gpv:busnumber]')
[/if#2]
[/if#1]
)
SELECT ID, BUSNUMBER,
MONITORDETAIL, STUDENTLASTFIRST, GRADE, GENDER,
HR_TEACHER,
SPECIALINSTRUCTIONS
FROM(
SELECT RN AS ID, BUSNUMBER,
'<font size=4>' || BUSNUMBER || '</font><br>' || MONITOR as MONITORDETAIL,
'' AS STUDENTLASTFIRST, '' AS GRADE, '' AS GENDER,
'' AS HR_TEACHER,
'' AS SPECIALINSTRUCTIONS,
'Group' As RowType
FROM ROUTE WHERE RN = 1
UNION ALL
SELECT RN AS ID, BUSNUMBER,
RN || '.' AS MONITORDETAIL,
STUDENTLASTFIRST, GRADE, GENDER,
HR_TEACHER,
SPECIALINSTRUCTIONS,
'Item' As RowType
FROM ROUTE
)X
ORDER BY BUSNUMBER ASC
, CASE WHEN RowType='Group' THEN 0 ELSE 1 END ASC
, STUDENTLASTFIRST ASC
;]

<tr>
<td style="display:none"> ~(ID) </td>
<td style="display:none"> ~(BUSNUMBER) </td>
<td><strong> ~(MONITORDETAIL) </strong></td>
<td> ~(STUDENTLASTFIRST)</td>
<td> ~(GRADE)</td>
<td> ~(GENDER)</td>
<td> ~(HR_TEACHER)</td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td> ~(SPECIALINSTRUCTIONS)</td>
</tr>

[/tlist_sql]

</table>


</body>
</html>

Monday, February 11, 2019

PowerSchool Transportation Reports