I am busy changing the world

In many cases, the web engine that generates data tables (html) is close-sourced and you cannot sort those tables. It is not because the engine does not have a mechanism to sort data. It's just that its behaviors does not satisfy your need. For example, the PowerSchool customization engine. In PowerSchool, I can output a table of lunch balance information of a student using this template syntax: ~[tlist;gldetail;studentid=~(curstudid);alternatecolor;sortcmd;date, > ,time, > ;]   <td>~(date)</td>   <td>~(time)</td>   <td>~(neteffect)</td>   <td>~(runninglunchtotalbalance.students)</td>   <td colspan="3">~(transdescription)</td> Did you see the >  (greater than) sign? It says that the table will sort by the date and time columns in ascending order. What I want is to make it sort date and time in descending order. So I tried to replace the > with the < (less than) ho...

You can use this SQL query to get all the students that do not have parent account linked to them in PowerSchool: SELECT s.LASTFIRST, s.STUDENT_NUMBER, s.FAMILY_IDENT, s.FATHER,                                           u.SSIS_FATHER_EMAIL, s.MOTHER, u.SSIS_MOTHER_EMAIL FROM students s INNER JOIN u_studentsuserfields u ON s.DCID = u.STUDENTSDCID WHERE s.DCID not in (SELECT g.STUDENTSDCID FROM guardianstudent g)        AND s.ENROLL_STATUS = 0;

It's quite useful displaying the bell schedule info of the day on the admin and teachers pages's header in PowerSchool. Here is how to do it: 1. Go to PS Administrator >> Custom Pages 2. Add this following code to wildcards/admin_header_css.txt and wildcards/teacher_header_css.txt <span id="ssis-date" style="padding-right:20px;"> ~[tlist_sql; select b.name as name, listagg(p.ABBREVIATION || ',') WITHIN GROUP ( ORDER BY bi.START_TIME) AS periods     from ps.calendar_day c inner join ps.BELL_SCHEDULE b on c.BELL_SCHEDULE_ID = b.ID         inner join ps.BELL_SCHEDULE_ITEMS bi on bi.BELL_SCHEDULE_ID = b.ID         inner join ps.PERIOD p on p.ID = bi.PERIOD_ID     where TO_CHAR(c.DATE_VALUE, 'DD-MM-YYYY') = TO_CHAR(CURRENT_DATE, 'DD-MM-YYYY')         and c.SCHOOLID = ~(curschoolid)     group by b.name; ]     <span style="color:#195f7d">~(name):</span> ~(p...

I was trying to display some custom field data (I will take ssis_student_nickname as an example in this article) in the classattendance.html page in PowerSchool Teachers. But, to be honest, I'm not sure about the customization syntax of PowerSchool. I couldn't make it work with this following syntax: ~([01]ssis_student_nickname) or ~([Students.U_STUDENTSUSERFIELDS]ssis_student_nickname) As fas as I acknowledged, the classattendance page accessing the attendance database using some javascript. So, in the end, I have to do some hack to display the custom field in that page. Here is the details: 1. Add an additional column in the page, and name it: ...              <th align="left" class="cvDemarcation">~[text]psx.html.teachers.classattendance.students[/text]</th>             <th class="cvDemarcation">Student Nick Name</th> ...             <tr class="s...

In Oracle database of PowerSchool, to get a list of students with their siblings who also students, you can use the following query: The important parts of the above query are: * Group the students of a same family based on the FAMILY_IDENT column of the STUDENTS table: SELECT STUDENTS.FAMILY_IDENT AS FAMILY_IDENT, '<ul>' || listagg('<li>' || STUDENTS.LASTFIRST || ' (' || STUDENTS.GRADE_LEVEL || ')' || ',</li>', '')  WITHIN GROUP ( ORDER BY STUDENTS.GRADE_LEVEL DESC, Upper(Trim(STUDENTS.LASTFIRST)) ) || '</ul>' AS SIBLINGS FROM STUDENTS WHERE STUDENTS.ENROLL_STATUS = 0 GROUP BY STUDENTS.FAMILY_IDENT The result will be something like this: ------------------------------------------------------------------------------------------------------- | FAMILY_IDENT | SIBLINGS                                                 ...

To display student's photo on the PowerSchool Parent's Portal: 1. In PS Administrator, create a custom page under the guardian folder, student_photo.html , with the following content: The   ~[studenttitlephoto] will output the student photo's thumbnail which is too small. For example: https://mypowerschool.com/guardian/stpthumb/5712ph_thumb.jpeg?version=1417586219587 I figured out where the source of the real photo is. All I have to do to get the bigger image source is to remove all the ' thumb ' part in the source link of the image: https://mypowerschool/guardian/stp/5712ph.jpeg?version=1417586219587 var src = photo.attr( ' src ' ); src = src. replace ( ' thumb ' , ' ' ); // replace the src link with the bigger photo's src = src. replace ( ' _thumb ' , ' ' ); // replace the src link with the bigger photo's photo.attr( ' src ' , src); Plus, I also have to re...

Part I - Overview, Create or Update PowerSchool Parent accounts using PHP cURL Earlier this year I had a chance to work on a project which is creating a Single Log-In system for Parents using the PowerSchool data. Unlike other kind of users in PowerSchool (Students, Teacher) Parents cannot be bind to an Active Directory server (Yes, my life would be easier if they support LDAP for Parent accounts!), they are stored in a separate place in the database. After searching around for a while, I recognized that the only way for me to build a Single Log-In system for Parents is using the PowerSchool API and implement the SAML 2.0 protocol. The purpose of PowerSchool supporting this protocol is for developers to create a Single Sign-On layer for customized apps to use PowerSchool as an Identity Provider . To learn the PowerSchool API and to implement the SAML protocol which I never heard before is not a big deal to me as a developer, but in the limited amount of time, I was not sure i...

You can use this php snippet to get all the parent accounts from your PowerSchool SIS's Oracle database: $ php get_parent_accounts.php

In the past, It was not easy to get the custom fields data from PowerSchool's Oracle database because of the way it architected and organized custom fields data (they are stored as views in Oracle). With the newer version of PowerSchool (v7.9 and above), you can create custom fields to the extension database table. You can even migrate all the legacy custom fields (students and teachers/ school staffs) to that new location. It makes my life much easier when I want to manipulate those custom fields data because from now on, all the students custom fields data will be stored in 1 place, and the same for teachers's custom fields. * Student custom fields data will be stored in U_STUDENTSUSERFIELDS table * Teacher custom fields data will be stored in U_SCHOOLSTAFFUSERFIELDS table For example, you can use this script (python, SQLAlchemy, cx_Oracle) to export students data from PowerSchool along with any custom fields data you want: PowerSchool DATABASE Connector:

Understand how PowerSchool works is very important if you're working as a developer at a school which is using PowerSchool as the Student Information System. PowerSchool is a big web application with a lot of components working together. It will take a huge amount of time to master all of them, so to get started, I will go through the admin login process of PowerSchool. 1. The first time you access the admin login page (http://mydomain.com/admin/pw.html), PowerSchool will give you a random token, pstoken input field of LoginForm: <input type="hidden" name="pstoken" value=" 20233460462xtw69pPuoIBNJ1RyLBnKsfuykVNlJzd "> 2. The admin user input username and password into the form (in the form of < username;password >), and press submit button. 3. Before the form's data is sent to PowerSchool server, the password is hashed using base64 md5 and then hashed again with the token provided in step (1) If the user is a ldap user, the l...

In order to use the PowerSchool's API(s) or to enable Single Sign-on with 3rd-party app using SAML2, we need to enable HTTPS. To enable HTTPS on this application server, import a certificate and private key into the database. If you have already imported a certificate on this or any other application node, skip this procedure. If you do not yet have a certificate you will have the option to import and enable HTTPS at a later time If you choose to import, all certificates should be standard X509 certificates in standard Privacy Enhanced Mail (PEM) format. Viewed in a text editor, PEM format certificates start with "------BEGIN CERTIFICATE-----" and end with "-----END CERTIFICATE-----". The private key file should also be in PEM format . The private key password is the password you set when you generated the private key/certificate request pair. If your certificate authority provides an intermediate certificate or certificates, include them here. My private ...

PowerSchool is a huge student information system which is used worldwide in International Schools. It's not open-source but It provides us a lot of features. One of them is pages customization. + In a custom page, you can make a SQL query to get the data. For example: to get all the teachers (staffstatus=1) of Elementary School (schoolid=3) which are still active (status=1), and order the result by last name: ~[tlist_sql;     SELECT DISTINCT t.Last_Name, t.First_Name     FROM Users t, SchoolStaff ss     WHERE t.DCID=ss.Users_DCID         AND ss.StaffStatus=1         AND ss.Status=1         AND ss.SchoolID=3     ORDER BY lower(t.Last_Name) ]     <p>~(t.Last_Name), ~(t.First_Name)</p> [/tlist_sql] (PowerSchool v7.8.2) PowerSchool's official website:  http://www.pearsonschoolsystems.com/products/powerschool/