Automating Exports from PowerSchool

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):

 SELECT
replace(replace(replace(replace(Courses.course_name || decode(Sections.TermID,2501,’S1′,decode(Sections.TermID,2502,’S2′,’Year’)), ‘-‘,”) || ‘-‘ || Sections.section_number || ‘-1516’, ‘ ‘, ” ), ‘/’,”), ‘,’,”) as mailbox, replace(replace(replace(replace(Courses.course_name || decode(Sections.TermID,2501,’S1′,decode(Sections.TermID,2502,’S2′,’Year’)), ‘-‘,”) || ‘-‘ || Sections.section_number || ‘-1516’, ‘ ‘, ” ), ‘/’,”), ‘,’,”) as calendar, Teachers.email_addr, Courses.course_name, ‘any’, replace(replace(replace(replace(Courses.course_name || decode(Sections.TermID,2501,’S1′,decode(Sections.TermID,2502,’S2′,’Year’)), ‘-‘,”) || ‘-‘ || Sections.section_number || ‘-1516’, ‘ ‘, ” ), ‘/’,”), ‘,’,”) as calendar,  ‘any’, Sections.SchoolID
FROM
 Sections
JOIN
 Courses
ON
 Sections.course_number = courses.course_number
JOIN
 Teachers
ON
 Teachers.id = Sections.teacher
WHERE
 Sections.termid >= 2500
AND
 Sections.termid < 2599
AND
 LENGTH(TRIM (Teachers.email_addr)) > 0
AND
 Sections.SchoolID = 100

 

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.

Screen Shot 2015-08-30 at 4.51.02 PM

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:

 SELECT
replace(replace(replace(replace(Courses.course_name || decode(Sections.TermID,2501,’S1′,decode(Sections.TermID,2502,’S2′,’Year’)), ‘-‘,”) || ‘-‘ || Sections.section_number || ‘-1516’, ‘ ‘, ” ), ‘/’,”), ‘,’,”) as mailbox, replace(replace(replace(replace(Courses.course_name || decode(Sections.TermID,2501,’S1′,decode(Sections.TermID,2502,’S2′,’Year’)), ‘-‘,”) || ‘-‘ || Sections.section_number || ‘-1516’, ‘ ‘, ” ), ‘/’,”), ‘,’,”) as calendar, Teachers.email_addr, Courses.course_name, ‘any’, replace(replace(replace(replace(Courses.course_name || decode(Sections.TermID,2501,’S1′,decode(Sections.TermID,2502,’S2′,’Year’)), ‘-‘,”) || ‘-‘ || Sections.section_number || ‘-1516’, ‘ ‘, ” ), ‘/’,”), ‘,’,”) as calendar,  ‘any’, Sections.SchoolID
FROM
 Sections
JOIN
 Courses
ON
 Sections.course_number = courses.course_number
JOIN
 Teachers
ON
 Teachers.id = Sections.teacher
LEFT JOIN 
 U_DEF_EXT_SECTIONS s2 
ON 
 Sections.DCID = s2.SectionsDCID
WHERE
 Sections.termid >= 2500
AND
 Sections.termid < 2599
AND
 LENGTH(TRIM (Teachers.email_addr)) > 0
AND
 s2.upload = 1
Screen Shot 2015-08-30 at 4.48.50 PM
The report nicely exports as a CSV file in the required format! The real beauty of sqlReports though is the ability to use sqlExports to automate a query to run at a set time each day and upload to an FTP server.  Which is exactly what is done with the student data:
SELECT
ps_customfields.getstudentscf(Students.id,’student_email’),
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
FROM
CC
JOIN
Students
ON
CC.Studentid = Students.id
JOIN
Courses
ON
cc.course_number = courses.course_number
JOIN
Sections
ON
Sections.id = cc.SectionID
LEFT JOIN
U_DEF_EXT_SECTIONS s2
ON
Sections.DCID = s2.SectionsDCID
WHERE
CC.TermID >= 2500
AND
CC.TermID < 2599
AND
s2.upload = 1
You get a nice two column export that looks like this:
Screen Shot 2015-08-30 at 4.46.24 PM
This file is now ready to be picked up by a cURL script and uploaded to Hapara.  The best part is all you have to do is change a few termID’s in the SQL code from year to year to maintain it.  And you can always add a course and students later by simply checking a box.
Advertisements
Automating Exports from PowerSchool

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s