PowerSchool Scheduled data export, minimal install

There are several reasons we need to export data from PowerSchool and many times the built-in AutoSend feature will not do.  The AutoSend feature can only export data on active students for either the district or the school, sometimes we need a more specific set of kids as well as the fields that we can pull.

This post will focus on a method that should be able to be run on any Windows 2008 r2 server and above without an extra installs.  It uses PowerShell, .NET 4 and ODP.NET.  Also note that this is what you use once you have your sql query and you just want to set it to run on a schedule to get a .csv or some other delimited file.

I chose this method instead of others because PowerSchool has to be installed on Windows Servers and quite frankly we're a Windows Shop when it comes to servers.  You can run this directly on the server that runs PowerSchool but I chose to use a server we use to warehouse, scrub and analyze a lot of our data.  We also have lots of other servers that run specific applications for transportation/routing, food service, library systems and etc. and all of these are Windows boxes that have the base requirements.

Once I decided this would be my best method I used this post on the Hey, Scripting Guy! Blog to build my basic .ps1 scripts using ODP.NET.  I wasn't as concerned with speed but more the 'simplify' part.  So in summary of what that post talks about is:

1. ODP.NET  from Oracle 
2. Make sure you have .Net 4
3. the ability to run .Net 4 in PowerShell
• Configure PowerShell to use newer .net.  To do this, you need to create a config file, named PowerShell.Exe.Config according to (http://tfl09.blogspot.com/2010/08/using-newer-versions-of-net-with.html)
4. bypassing the block (run PowerShell as Admin):  Set-ExecutionPolicy RemoteSigned
5. run this script by:  .\query1.ps1 

So get ODP.NET:

1. create a folder called c:/autoexport or somewhere else on your Windows 7+ or 2008r2+ box.
2. download the proper ODP.NET from Oracle 
3. extract that in your new folder under another folder called c:/autoexport/odp

Check if you have .NET 4

	gci 'HKLM:\SOFTWARE\Microsoft\NET Framework Setup\NDP' | sort pschildname -des | foreach-object {$_.name; $_.GetValue("Version");}
	<# credit http://stackoverflow.com/questions/3487265/powershell-script-to-return-versions-of-net-framework-on-a-machine#comment3650817_3491193#>

view raw checkdotNET.ps1 hosted with ❤ by GitHub

Configure PowerShell to use newer .NET.  To do this, you need to create a config file, named PowerShell.Exe.Config according to (http://tfl09.blogspot.com/2010/08/using-newer-versions-of-net-with.html),  then put this in the same folder as powershell.exe (%SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe)

	<?xml version="1.0"?>
	<configuration>
	<startup useLegacyV2RuntimeActivationPolicy="true">
	<supportedRuntime version="v4.0.30319"/>
	<supportedRuntime version="v2.0.50727"/>
	</startup>
	</configuration>

view raw PowerShell.Exe.Config hosted with ❤ by GitHub

then bypassing the block (run PowerShell as Admin):
 Set-ExecutionPolicy RemoteSigned

Heres the sample script for Naviance Student import:

	<# credit to http://blogs.technet.com/b/heyscriptingguy/archive/2012/12/04/use-oracle-odp-net-and-powershell-to-simplify-data-access.aspx#>
	#Just modify the Connection String here
	$conString="User Id=<your db query username>;Password=<your password>;Data Source=<your powershool db IP>/<db sid>"
	#Just modify the SQL here:
	$sqlString=@"
	/*
	Product: student auto import
	Data Type: SIS
	Entity: STUDENT
	Author:
	Modified:
	*/
	SELECT
	students.student_number as Student_ID,
	students.SCHED_YEAROFGRADUATION as Class_Year,
	students.Last_Name as Last_Name,
	students.SchoolID as Campus_ID,
	students.First_Name as First_Name,
	students.Gender as Gender,
	students.Ethnicity as Ethnicity,
	TO_CHAR(students.DOB, 'MM/DD/YYYY') as DOB,
	('FC' || students.student_number) as FC_User_Name, /* some method to create unique usernames for each student */
	'<some default password>' as FC_Password
	FROM
	PS.STUDENTS
	WHERE
	ps.STUDENTS.grade_level > '05' and ps.students.enroll_status = '0' and ps.students.SCHOOLID != '99'
	"@
	#location of your ODP.NET
	Add-Type -Path "F:\autoExport\odp.net\managed\common\Oracle.ManagedDataAccess.dll"
	function Get-OracleResultDa
	{
	param (
	[Parameter(Mandatory=$true)]
	[ValidateScript({$_ -match '\bdata source\b'})]
	[string]$conString,
	[ValidateScript({$_ -match '\bselect\b'})]
	[Parameter(Mandatory=$true)]
	[string]$sqlString
	)
	$resultSet=@()
	try {
	$con = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($conString)
	$cmd=$con.CreateCommand()
	$cmd.CommandText= $sqlString
	$da=New-Object Oracle.ManagedDataAccess.Client.OracleDataAdapter($cmd);
	$resultSet=New-Object System.Data.DataTable
	[void]$da.fill($resultSet)
	} catch {
	Write-Error ($_.Exception.ToString())
	} finally {
	if ($con.State -eq 'Open') { $con.close() }
	}
	$resultSet
	}
	#this is the line where specify the location for the export and you have to specify the objects you want after the 1st pipe
	Get-OracleResultDa $conString $sqlString | Select-Object Student_ID,Class_Year,Last_Name,Campus_ID,First_Name,Gender,Ethnicity,DOB,FC_User_Name,FC_Password | Export-Csv -NoTypeInformation F:\autoExport\exports\naviance_students.csv

view raw queryFor_NavianceStudents.ps1 hosted with ❤ by GitHub

To run this in PowerShell you need to do the ol' 'dot slash' in front of the actual script:  ./queryFor_NavianceStudents.ps1

now that you have your delimited export file, you can use PowerShell and WinSCP to upload it.  I'll probably write that part up too soon.

Wrap those up in a .bat file then schedule that with task scheduler!

Custom Alert Icon on Class Attendance page of PowerTeacher

The main goal was to add custom alerts to the class attendance page on PowerTeacher, this page is visited by teachers daily and shows all students in the chosen class in one screen.

Alternatives require a teacher to click through each student on the /teachers/studentpages and also requires customizations be made for each of those pages.

The target page for this post is /teachers/classattendance.html but before we get to that we have first add a custom field for the alert, I just added a boolean field in an advanced DB extension called U_Info and added the column/field named Alert_customalertfield with default value of 0.

Then we need an alert icon, I used a .gif that was basically the favicon.ico of the alert type's website.  But any 16x16 .gif will do, add this to /images so that https://yourPSserver/images/alert_customalert.gif will show.

Next we have to add a place in PowerSchool to check this box, for this we will only allow folks with permission on the admin side to check.  I chose the "Other Information" page for a student on the PowerSchool Admin side.

just add a custom insertion point by adding this code just above the ending /table> tag on /admin/students/misc.html:

	<!-- custom insertion point added by someone on mm/dd/yyyy -->
	~[cust.insertion_point:custom.studentalerts]

view raw cust.insertion_point:custom.studentalerts hosted with ❤ by GitHub

Then add this 'page fragment' called "misc.alert_customalert.custom.studentalerts.txt"
with this in it in the same directory as misc.html:

	<tr bgcolor="#f6f6f6">
	<td colspan="4" class="bold">custom Alert</td>
	</tr>
	<tr bgcolor="#edf3fe">
	<td class="bold">custom Alert</td>
	<td><input type="checkbox" name="[Students.U_Info]customalert" value="0"></td></tr>

view raw misc.alert_customalert.custom.studentalerts.txt hosted with ❤ by GitHub

To add this alert icon to the admin side its a simple page fragment called:"title_student_end_css.alert_customalert.student.alert.txt"inserting into: "~[cust.insertion_point:student.alert]"
to do this create a file named "title_student_end_css.alert_customalert.student.alert.txt" in this directory  /wildcards/ with this in it

	<!-- Start of custom alert -->
	&nbsp&nbsp~[if.~([Students.U_Info]Alertcustomalert)="1"]<a class="dialogDivC" title="custom alert Student" href="#hiddenDivDialog"><img src="/images/alert_customalertfield.gif"></a>
	<div id="hiddenDivDialog" class="hide">
	<<div class="studentAlert" id="alert-customalert~(studentfrn)">
	<img class="alertImage" src="/images/alert_customalert.gif" />
	<div class="alertContent">
	<p class="alertHeader">~(studentname)</p>
	<p>~([Students.U_Info]Alertcustomalert;if.fieldvalue.1.then=CustomAlert)<br>&nbsp;<br><a href="/admin/students/misc.html?frn=~(studentfrn)"> Other information</a></p>
	</div>
	</div>
	[/if]

view raw title_student_end_css.alert_customalert.student.alert.txt × hosted with ❤ by GitHub

So to get back to the main goal of this post, adding this to the Class Attendance 'Single Entry' tab, is to add the code below to /teachers/classattendance.html directly after '[alerts]' tag, should be line 245 of the original page. It's not elegant but it does work:

	<!-- Start of custom alert-->
	~[tlist_sql;select CASE customalert WHEN 1 THEN '<a class="dialogDivC" title="customalert Student" href="#hiddenDivDialog"><img src="/images/alert_customalert.gif"></a>' ELSE '' END from ps.students s join ps.U_customalertDBextension i on s.DCID = i.STUDENTSDCID join ps.cc cc on cc.STUDENTID = s.ID where cc.id = '[ccid]';]~(studentid;)[/tlist_sql]
	<div id="hiddenDivDialog" class="hide"><div class="studentAlert" id="alert-customalert~(studentfrn)">
	<img class="alertImage" src="/images/alert_customalert.gif" />
	<div class="alertContent"><p class="alertHeader">customalert Student</p></div></div>
	<!-- End of custom alert -->

view raw customalert_teachers_classattendance hosted with ❤ by GitHub

Like I said, it's not elegant and I have not load tested it but it does work in a environment where ~450 teachers login on a daily basis.  It works by using a stored procedure variable called [ccid] that is only available on that page and actually iterates through the listing of students in that class, unfortunately it does not work with a page fragment, for some reason that variable/stored procedure doesn't carry through to the page fragment.  Also other commonly used DATs like ~(studentname) or ~(studentfrn)   don't work.  So I just use a tlist_sql query joining the CC, Students and U_info tables and finding if the student with that [ccid] has a 1 in the customfieldalert, then do a case statement that replaces the value with the html code i want.  Definitely some hackery but like I said it works and teachers are happy.