Recently we found ourselves needing to do an export from PowerSchool into Hapara. There are two files to get up and going actually, a classes file with the usual information like teacher name, class name, etc. and a student file which is actually just their Google username (e-mail address) and the course name that matches the classes file. Sure, you could just export course number, section number and termid but that’s just not very friendly to look at. So I decided that with a little SQL we could export the course name and a term name (S1, S2, Year, etc). Using the very excellent sqlreports4 plugin I put together a select with a few joins (and a pyramid of doom style term naming piece that removes spaces and slashes from course names):
We have some vo-tech classes that are taught at the local community college and the teacher for that course varies by semester and content so the TRIMing is in there to make sure those courses don’t get imported (or any course with a teacher without a valid e-mail address). Removing this AND section is good for debugging just to make sure a class doesn’t get excluded because the teacher info is missing their e-mail address.
This worked fine for the high school (SchoolID=100) but then came the problem of what about adding a class or two at a different school? Well, we could just manually include those but that seems like a hack. A more elegant solution would be having a checkbox on the sections page that you can simply choose to include or exclude that section from the upload. In this vein I created a boolean database extension with a one-to-one relationship to the sections table. Then with a few lines of HTML added the checkbox to the edit sections page.
Of course I had to pre-populate that field for all the high school courses, I just used the data export manager and took all sections at the high school in the current year, exported the SectionsDCID field to a text file, added a column for the include flag (1) in Excel and imported that back into PS into the new DB Extension table.
Now we can use this SQL statement:
replace(replace(replace(replace(Courses.course_name || decode(cc.TermID,2501,’S1′,decode(cc.TermID,2502,’S2′,decode(cc.TermID,2503,’Q1AS’,decode(cc.TermID,2504,’Q2AS’,decode(cc.TermID,2505,’Q3AS’,decode(cc.TermID,2506,’Q4AS’,’Year’)))))), ‘-‘,”) || ‘-‘ || cc.section_number || ‘-1516’, ‘ ‘, ” ), ‘/’,”), ‘,’,”) as Class
CC.Studentid = Students.id
cc.course_number = courses.course_number
Sections.id = cc.SectionID
Sections.DCID = s2.SectionsDCID
CC.TermID >= 2500
CC.TermID < 2599
s2.upload = 1