Fuze Card Saga Pt. 1: Unboxing & Trying to Add Cards

November 8, 2017: The Fuze Card finally arrives.

First impressions

It’s thin. Like, credit-card thin.  No idea what wizardry was used to fit a battery inside the thing, but they pulled it off.

It’s soft(?).  The matte plastic feels soft – at least softer than a rigid credit card.  It doesn’t seem bendier or in any other way more deformable than a regular card, but it somehow feels like it should be.  Go figure.

The buttons are raised, which does create worries about wallet misfires.  I keep my wallet in my back pocket, where it’s often sat upon by a profoundly bony backside. The buttons do require a decent amount of pressure to push, though, so this may not prove to be a problem.

Getting it Connected

Cards are added via the companion app, which loaded up with no issue.  The card connects automatically via Bluetooth Low Energy, though you can also force it into pairing mode by holding the Power and Multi buttons simultaneously.

Once connected, the app instructs you to set up a passcode, which is just a 6-button sequence created by the three hardware buttons.  At \(3^6=729\) possible combinations, the passcode isn’t exactly guess-proof, but then again neither is a stolen credit card.  And the Fuze card has other security measures as well.

Adding Cards

This was a pain in the ass.

Cards have to be added using an off-brand Square reader which connects via the headphone jack.  First problem: I bought an Essential phone yesterday, and it doesn’t have a headphone jack.

The card reader (right)
and the Fuze charger.

The Essential does, though, have a USB-C to 3.5mm adapter, which is nice.  Problem is, I can’t get it to scan for anything.  Worse, I don’t actually own any wired headphones, so I can’t diagnose whether the culprit is the app, the reader, the adapter, or the phone.

Fortunately, I still have my old Nexus 5X, which does have a headphone jack.  This would be an opportune time to troubleshoot the USB-C adapter, but no. I just want to load my blasted credit cards.

Edit: The USB-C adapter also failed on the Nexus, so either it's defective or I'm doing it wrong.

Speaking of loading those cards:

Why can’t I just photograph the card, or manually enter the information into the app, a’la Android Pay?  Maybe requiring the reader is a form of fraud prevention, forcing you to have the actual card?  But even then, I could just use a card writer to make a physical copy of a stolen card – and I’m sure emulators exist as well.

Mercifully, the Nexus can read the cards and add them to the app, which syncs them to the Fuze card.  At least that’s out of the way.

With the cards added, the next step is to test basic usability.  Hilarity ensues.

Posted by Adam Labay, 0 comments

#DumpDeVos Caller Guide

Step 1: Call Your Senators

Use Call My Congress (opens in a new window) to look up your senators.  Use the links to call them.  (Note: confirmation of presidential appointees is a Senate process, so there’s little your other representatives can do here.)

Script #1:

My name is _____ and I am calling to let the Senator know that I would like him/her to oppose the appointment of Betsy DeVos for Secretary of Education.

I believe in my community’s public schools. Betsy DeVos believes in school privatization and vouchers. She has worked to undermine efforts to regulate Michigan charters, even when they clearly fail. The “marketplace” solution of DeVos will destroy our democratically governed community schools. Her hostility towards public schools disqualifies her. I am asking the Senator to vote against the confirmation of Betsy DeVos.

Script #2:

My name is _____ and I am calling to let the Senator know that I would like him/her to oppose the appointment of Betsy DeVos for Secretary of Education.

DeVos and her family heavily lobbied the Michigan legislature to shield the charter industry from greater oversight. She pushes for-profit charter schools and online schools, which consistently fail the students that they are supposed to serve.

I want my tax dollars to stay in my community to support my public schools. I don’t want my money going to private schools and profit making scams. Betsy DeVos is bad for American education. I am asking that the Senator oppose DeVos.


Step 1b: Send a Video to Your Senators

Use Countable (embedded at right) to send a video message to your Senators as well. Personal story > telephone script.


Step 2: Help #DumpDeVos Gather Steam

Step 2a: Retweet a Badass Teachers Meme


Cruise through the Twitter history of #NotMySOE, pick a meme, and retweet it with the hashtags #DumpDeVos and #NotMySOE as well as #TBATs.  Consider tagging the aforementioned Senators as well.

Step 2b: Remind People that Public Schools are a Success Story


If you haven’t already, create a #PublicSchoolSuccess picture and tweet it out using said hashtag.  (Soon to be added) are lists of people who have either a) allied with Betsy DeVos, or b) have claimed public schools to be a failed experiment.  Tag as many as you reasonably can.


Step 3: Acknowledge That She’ll Probably Still Get Confirmed.

Stick with me, here.

Let’s just face the facts.  There is basically 100% likelihood that Betsy DeVos will become our new Secretary of Education.  Reasons:

  • In the history of the United States, just 9 nominees were rejected by the Senate, and only 13 withdrawn before confirmation.
  • The major way to knock someone out of contention is with flagrant conflicts of interest, and that’s far more likely to be a Rex Tillerson fight.
  • Jeff Sessions and Michael Flynn represent far greater threats to the rights of the oppressed, so resources will go to those fights.
  • School-choice ideology aside, this is a partisan nomination and a partisan confirmation.  And have you seen the Senate make-up?  Related:
  • Harry Reid basically made it impossible for the minority party to stop cabinet appointments.  Whoops.
  • Whatever GOP senators might be persuaded by reasoned arguments against DeVos have probably been bought.
But that doesn’t mean it’s over.

In 2018, all of the House is up for re-election.
As are 36 states’ Governors.
As are hundreds of state legislators.
And thousands of school board members.

The privatizers aren’t stopping after the DeVos hearing, and neither should we.


Step 4: Gear Up for the Long Haul

Step 4a: Join the Badass Teachers Association

Both the national organization and the one for your state.  This is where grassroots activism starts.  Read their blogs. Link up on Twitter.

Step 4b: Look Up When Your Senator is in Town

Go back to Call My Congress and go to your Senator’s page.  Find his/her Town Hall schedule (they all go by ridiculous cutesy names so you’ll have to search a bit).  Go there and hold them accountable, not just for the DeVos hearings, but for their commitment to public education.

Step 4c: Read the Diane Ravitch Canon

Or at the very least, The Death and Life of the Great American School System.  And once you’ve read that, memorize every data point from Reign of Error.

Use these talking points often.


Step 6: Have a Lovely Beverage

You’ve earned it.

Posted by Adam Labay, 0 comments

#PublicSchoolSuccess App – The Details

The #PublicSchoolSuccess Image Generator app is located here.

This page is just the code behind it.

If that’s what you’re after, read on.

Credit Where It’s Due

This is all based off of existing code by Shane Chism, namely his Facebook Picture Overlay script.  Shane’s script is designed to do those icon overlays that you see on Facebook every time there’s a new social cause.  It overlays a small transparent .png in the lower-right of an image, and makes sure the whole thing fits in a 200×600 frame.

Shane’s code still comprises >95% of the code in use, so major props to him for making something so easy for me to modify, and for being a mensch who gives it away for free.

The Modifications

The original script works like this:

  1. Resize and crop the source image until it fits in a 200×600 boundary box.
  2. Create a canvas the same size as the source image.
  3. Insert the source image into the canvas.
  4. Overlay the icon in the lower-right corner (a programmable offset is also available).
  5. Convert to JPG, timestamp, and save.

The #PublicSchoolSuccess version works similarly, but with a couple changes in workflow:

  1. Resize and crop the source image until it fits in a 300×450 boundary box.
    • If the image is portrait, set the width to 300 and crop the bottom to make the height 450.
    • If the image is landscape, set the height to 450 and crop the right to make the width 300.
  2. Rotate the source image to match the angle of the frame.
  3. Create a canvas the size of the overlay (which is the size of the finished image).
  4. Place the rotated/resized portrait on the canvas.
  5. Place the overlay on the canvas.
  6. Convert to JPG, timestamp, and save.

The Code

After this project, I am up to all of 8 hours of php programming (if you include time spent scuzzing with the image to get the right angles and locations).  As such, I make no promises about the quality or reliability of this code.  I did my best to emulate Shane’s methodology for the sake of consistency.  Still, I’m sure I flouted plenty of conventions and eschewed plenty of efficiencies.  Meh.

The package comes in 4 parts:

  • uploader.html – what users actually visit
  • uploader.php – uploads the user’s file and triggers processing
  • YearbookPicOverlay.php – does the actual processing
  • success.html – displays the result to the user

I also modified the .html files to make them look better, but since said modifications are entirely dependent on my site’s css, there’s really no point to including them.

uploader.html
<html xmlns="http://www.w3.org/1999/xhtml">

<head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Sample Uploader Page</title>
</head>

<body>

	<h2>Facebook Tagger</h2>

	<!-- Enctype MUST be multipart/form-data for file upload -->
	<form action="uploader.php" method="post" enctype="multipart/form-data">
    
    	<!-- For ease-of-use sake I like to put error messages on the same page as uploads -->
        <!-- Alternatively, you can code your website to display errors wherever you like -->
    	<?php @print( $fbtOutput ); ?>
    
    	Please upload your picture (JPEG or JPG format):<br />
    	<input type="file" name="picture_upload" />
        
        <br /><br />
        <input type="submit" name="submit" value="Tag me!" />
    
    </form>
	
	<br /><hr />
    <i>For help and further explanations, see the <a href="http://shanechism.com/code/static/16">documentation</a>.</i>

</body>

</html>
success.html
<html xmlns="http://www.w3.org/1999/xhtml">

<head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Congratulations, you're tagged! (Sample Uploader Success Page)</title>
</head>

<body>

	<h2>Tagging Success!</h2>

	<div style="margin: 0px auto; text-align: center;">
        
        <b>Here's your freshly tagged image:</b><br /><br />
        To save, right click the image and select "Save Image As," "Save Link As," or "Save Picture As." Then, save it to your desktop!<br /><br />
        
        <!-- As we saw in uploader.php, $image is the path to the image beginning at the resources directory. -->
        <!-- In order to show our image I will need to create the first part of the link. In this case it just starts at the domain name. -->
       	<img src="<?php echo( 'http://' . $_SERVER['SERVER_NAME'] . '/path_to_resources_folder/' . $image ); ?>" alt="Facebook Picture" /><br /><br />
        
        <!-- $_SERVER['PHP_SELF'] is a PHP Superglobal basically pointing the link back to this PHP page -->
        Why not <a href="<?php echo( $_SERVER['PHP_SELF'] ); ?>">tag another</a>?
    
    </div>
    
    <br /><hr />
    <i>For help and further explanations, see the <a href="http://shanechism.com/code/static/16">documentation</a>.</i>

</body>

</html>
uploader.php
<?php

# Check to see if the form has been submitted or not:
if( !isset( $_POST['submit'] ) ){
 
 # Form has not been submitted, show our uploader form and stop the script
 require_once( "uploader.html" );
 exit();
 
}else{
 
 # Form has been submitted, begin processing data
 
 # Include the function file then call it with the uploaded picture:
 # TIP: The "../../ portion is a relative path. You will need to change this
 # path to fit your website's directory structure.
 require_once( 'YearbookPicOverlay.php' );
 
 # Create the FacebookTagger object using our upload value given to us by uploader.html
 $fbt = new FacebookPicOverlay();
 
 # Let's say we're using this script to do an image overlay. Let's invoke the
 # overlay method, which will then return the image file relative to the resources
 # folder (ex: will return resources/processed/imagename.jpg).
 try {
 $image = $fbt->overlay( $_FILES['picture_upload'] );
 }catch( Exception $e ){
 print( "<b>Oops!</b> " . $e->getMessage() );
 print( "<br /><br /><a href=\"javascript:history.go(-1)\">Please go back and try again</a>" );
 exit();
 }
 
 # This will delete all images created more than two days ago (by default).
 # This is helpful in keeping our processed folder at a reasonable file size.
 $fbt->maintenance();
 
 require_once( "success.html" );
 
} 

# That's all, folks!
?>
YearbookPicOverlay.php
<?php
/*************************************************
 * Facebook Picture Overlay Script
 * Version: 2.1.0
 * Coded by: Shane Chism <http://shanechism.com>
 * Updates: http://shanechism.com/code/static/16
 * Distributed under the GNU Public License
 * Modified by Adam Labay
 * Description & Attribution: http://adamlabay.net
 *************************************************/
 
/** \brief Facebook Picture Overlay Script
 * Allows for image overlay to uploaded files based on Facebook's standards
 * @author Shane Chism <schism@acm.org>
**/

/** \brief #PublicSchoolSuccess Overlay Script
 * Creates #PublicSchoolSuccess image using user photo
 * @author Adam Labay <adam@adamlabay.net>
**/
class FacebookPicOverlay {
 
 // --------------------------------------------------
 // CONFIGURATION SECTION - MANDATORY
 // --------------------------------------------------
 // Modify the values in this section according to
 // your own needs. Pay close attention to your
 // directory structure.
 
 # Path to the directory containing the resources and processed folders:
 var $rootPath = "./";
 
 # Resources folder name:
 # (default: "resources/")
 var $resourcesFolder = "resources/"; 
 
 # Folder you would like the processed images saved to:
 # (default: "resources/processed/")
 var $processedFolder = "resources/processed/";
 
 // YEARBOOK PIC CONFIGURATION
 # These variables now represent the width and height of the portrait rather than those of
 # the finished picture. Also included are angle of rotation and position (upper-left).
 var $fbWidth = 330;
 var $fbHeight = 450;
 var $rotAngle = 12;
 var $offsetx = 551;
 var $offsety = 143;
 
 // ** OVERLAY MODE CONFIGURATION
 
 # Overlay image filename and extension (must be placed in the resources folder):
 # (default: "overlay.png")
 # Image will be resized to the dimensions given for export.
 var $overlay = "yearbook_overlay.png";
 var $exportWidth = 600;
 var $exportHeight = 440;
 
 
 # Throw Exceptions?
 # true = User errors will generate an Exception() error
 # false = User errors will return overlay as false, and save error to $this->error
 var $throwExceptions = true;
 
 // --------------------------------------------------
 
 // --------------------------------------------------
 // CONFIGURATION SECTION - OPTIONAL
 // --------------------------------------------------
 // You can fine tune the tagger to your needs,
 // though these options can remain the same and your
 // tagging should still work.
 
 # Maximum image size allowed for upload (in MB):
 # (default: 20)
 var $maxFileSize = 20;
 
 # Save images at this quality (percentage):
 # (smaller quality has a smaller file size but looks worse)
 # (default: 100)
 var $quality = 100;
 
 # Save images in this file format:
 # (options: "jpg", "jpeg", "JPG", "JPEG")
 # (default: "jpg")
 var $extension = "jpg";
 
 // --------------------------------------------------
 
 var $uploaded, $uploadedInfo, $error;
 
 function __construct(){
 
 $this->checkConfig();
 
 }
 
 private function checkConfig(){
 
 if( substr( $this->resourcesFolder, 1 ) == '/' )
 $this->resourcesFolder = substr( $this->resourcesFolder, 1, ( strlen( $this->resourcesFolder ) - 1 ) );
 if( substr( $this->resourcesFolder, -1 ) != '/' )
 $this->resourcesFolder .= "/";
 
 if( substr( $this->processedFolder, 1 ) == '/' )
 $this->processedFolder = substr( $this->processedFolder, 1, ( strlen( $this->processedFolder ) - 1 ) );
 if( substr( $this->processedFolder, -1 ) != '/' )
 $this->processedFolder .= "/";
 
 if( !file_exists( $this->rootPath . $this->resourcesFolder ) )
 $this->printErr( "The resources folder path you have specified is invalid. Please check it and try again (configuration section: <code>\$rootPath</code> and <code>\$resourcesFolder</code>)." );
 if( !file_exists( $this->rootPath . $this->processedFolder ) )
 $this->printErr( "The processed folder path you have specified is invalid. Please check it and try again (configuration section: <code>\$rootPath</code> and <code>\$processedFolder</code>)." );
 
 $overlay = $this->rootPath . $this->resourcesFolder . $this->overlay;
 if( !file_exists( $overlay ) )
 $this->printErr( "The \"overlay\" image you specified in the configuration section (<code>\$overlay</code>) does not exist. Please correct and try again." );
 
 }
 
 private function printErr( $text ){
 die( "<h3>FBT Error:</h3> " . $text );
 }
 
 private function throwErr( $err ){
 $this->error = $err;
 if( $this->throwExceptions )
 throw new Exception( $err );
 }

 # Places your overlay image on the picture and returns the hyperlink
 public function overlay( $uploaded ){
 
 $this->checkConfig();
 $this->uploaded = $uploaded;
 
 $overlay = $this->rootPath . $this->resourcesFolder . $this->overlay;
 $overlaySize = getimagesize( $overlay );
 
 $canvasWidth = $overlaySize[0];
 $canvasHeight = $overlaySize[1];
 
 if( empty( $this->uploaded ) || $uploaded['size'] < 1 ){
 $this->throwErr( "You have not chosen an image to upload!" );
 return false;
 }
 
 $this->uploadedInfo = getimagesize( $this->uploaded['tmp_name'] );
 
 if( $this->uploaded['size'] > ( $this->maxFileSize * 1000000 ) || filesize( $this->uploaded['tmp_name'] ) > ( $this->maxFileSize * 1000000 ) ){
 $this->throwErr( "The file you have chosen to upload is too big." );
 return false;
 }
 
 if( $this->uploadedInfo['mime'] != "image/jpeg" && $this->uploadedInfo['mime'] != "image/jpg" ){
 $this->throwErr( "The file you have chosen to upload is the wrong file type. Please choose a JPG or JPEG file only." );
 return false;
 }
 
 $portrait = array();
 
 $portrait[0] = $this->uploadedInfo[0]; 
 if( ( $portrait[0] * $this->fbHeight ) / $this->fbWidth > $this->uploadedInfo[1] ){
 $portrait[1] = $this->uploadedInfo[1];
 $portrait[0] = ( $portrait[1] * $this->fbWidth ) / $this->fbHeight;
 }
 else
 $portrait[1] = ( $portrait[0] * $this->fbHeight ) / $this->fbWidth;
 
 $src = imagecreatefromjpeg( $this->uploaded['tmp_name'] );
 $srcscaled = imagecreatetruecolor( $this->fbWidth, $this->fbHeight );
 imagecopyresampled( $srcscaled, $src, 0, 0, 0, 0, $this->fbWidth, $this->fbHeight, $portrait[0], $portrait[1] );
 $tmp = imagerotate( $srcscaled, $this->rotAngle, 0 );
 $tmpsize = array();
 $tmpsize[0] = imagesx( $tmp );
 $tmpsize[1] = imagesy( $tmp );
 
 imagealphablending( $tmp, true );
 $overlayRes = imagecreatefrompng( $overlay );
 
 do{
 $filename = time() . "-processed.jpg";
 $file = $this->rootPath . $this->processedFolder . $filename;
 }while( file_exists( $file ) );
 
 $canvas = imagecreatetruecolor( $canvasWidth, $canvasHeight );
 imagecopy( $canvas, $tmp, $this->offsetx, $this->offsety, 0, 0, $tmpsize[0], $tmpsize[1] );
 imagecopy( $canvas, $overlayRes, 0, 0, 0, 0, $canvasWidth, $canvasHeight);
 $canvasout = imagecreatetruecolor ($this->exportWidth, $this->exportHeight);
 imagecopyresampled( $canvasout, $canvas, 0, 0, 0, 0, $this->exportWidth, $this->exportHeight, $canvasWidth, $canvasHeight);
 imagejpeg( $canvasout, $file, $this->quality );
 
 if( !file_exists( $file ) )
 $file = $this->rootPath . $this->resourcesFolder . $this->oops;
 
 imagedestroy( $src );
 imagedestroy( $srcscaled );
 imagedestroy( $tmp );
 imagedestroy( $overlayRes );
 imagedestroy( $canvas );
 imagedestroy( $canvasout );
 
 return ( $this->processedFolder . $filename );
 
 }
 
 # Deletes all files in the processed folder that were created before $timestamp
 # Defaults to 2 days ago
 public function maintenance( $timestamp = NULL ){
 
 $this->checkConfig();
 
 if( $timestamp == NULL )
 # Defaults to 2 days ago
 $timestamp = strtotime( "-2 days" );
 
 if( $timestamp > time() )
 $this->printErr( "You are trying to perform maintenance on files created in the future. This is beyond the script's abilities, please install a time machine to continue." );
 
 if( $handle = opendir( $this->rootPath . $this->processedFolder ) ){
 
 while( false !== ( $filename = readdir( $handle ) ) ){
 
 if( substr( $filename, ( -1 * ( 1 + strlen( $this->extension ) ) ) ) == ( "." . $this->extension ) ){

 $file = $this->rootPath . $this->processedFolder . $filename;
 
 if( filectime( $file ) < $timestamp )
 @unlink( $file );
 }
 
 }
 
 closedir( $handle );
 
 }else{
 $this->printErr( "Unable to access the processed folder. Check your <code>\$rootPath</code> and <code>\$processedFolder</code> settings in the configuration section." );
 }
 
 }
 
}

?>

Posted by Adam Labay, 0 comments

#PublicSchoolSuccess Image Generator

The amazing people at Badass Teachers Association put together an excellent Twitter campaign featuring notable public school graduates.  Some examples:

 

To make something like this go viral, though, will take thousands more contributions.

So, I threw together an app that will let people add their face to the image and post it to their favorite social media.

The #PublicSchoolSuccess App

Step 1: Go to adamlabay.net/fbtag/uploader.html
Step 2: Upload a picture of your face.
Step 3: Save the resulting image.
Step 4: Tweet it to @BetsyDeVos and make sure to use the hashtag #PublicSchoolSuccess. If you have the space, copy @BadassTeachersA as well.
Step 5: Spread the gospel.

The Code

To be clear, I did pitifully little to create this.  All the grunt work was done by Shane Chism and I just tweaked the code.  That said, if you want the gory details, I’ll post them shortly.

Posted by Adam Labay, 1 comment

PowerSchool ISA Report for DC Schools

If you’re a DC school that uses PowerSchool as your SIS, then you’ve probably faced the situation where none of the attendance reports properly match the In-Seat Attendance (ISA) reports in Qlik.

I still can’t speak to the exact mechanics of why the two reports differ, but to bridge the gap I put together a report for the SQLReports4 package that calculates ISA using the same method as Qlik.

A few notes:

  1. All students need to have a Full-Time Equivalency (FTE) assigned.  Generally, this happens by default when a student is added to PowerSchool, but sometimes it doesn’t register.
  2. The report’s first parameter lets you decide whether to report Year-to-Date or over a date range.
    • Both cases will return the ISA% by month and grade.
    • If the date range is specified, grade and schoolwide values for the range will be returned.
    • If the date range is not specified, grade and schoolwide values YTD will be returned.

The code:

<ReportName>In-Seat Attendance</ReportName>
<ReportTitle>In-Seat Attendance</ReportTitle>
<AfterTitle></AfterTitle>
<ReportGroup>Attendance</ReportGroup>
<ReportDescription>%ISA [(Students Present * Days Present)/(Students Enrolled * Days Enrolled)] per grade, per month.</ReportDescription>
<SQLQuery><textarea name="UF-0060051129">WITH mem_days AS
(
SELECT to_char(cd.Date_Value, 'MM/YYYY') m, s.Grade_Level gl, COUNT(cd.Date_Value) dm

FROM Students s
INNER JOIN Terms t ON t.YearID = ~(curyearid) AND s.SchoolID = t.SchoolID
INNER JOIN Calendar_Day cd ON s.EntryDate &lt;= cd.Date_Value AND s.ExitDate &gt;= cd.Date_Value AND s.SchoolID = cd.SchoolID AND t.FirstDay &lt;= cd.Date_Value

WHERE
cd.Date_Value &lt;= (CASE WHEN '%param1%' = 'Yes' THEN to_date('%param3%', 'MM/DD/YYYY') ELSE SYSDATE END)
AND cd.Date_Value &gt;= (CASE WHEN '%param1%' = 'Yes' THEN to_date('%param2%', 'MM/DD/YYYY') ELSE to_date('1/1/1900', 'MM/DD/YYYY') END)
AND cd.MembershipValue = 1

GROUP BY to_char(cd.Date_Value, 'MM/YYYY'), s.Grade_Level
)
, att_days AS
(
SELECT to_char(cd.Date_Value, 'MM/YYYY') m, s.Grade_Level gl, COUNT(att.Att_Date) da

FROM Students s
INNER JOIN Terms t ON t.YearID = ~(curyearid) AND s.SchoolID = t.SchoolID
INNER JOIN Calendar_Day cd ON s.EntryDate &lt;= cd.Date_Value AND s.ExitDate &gt;= cd.Date_Value AND s.SchoolID = cd.SchoolID AND t.FirstDay &lt;= cd.Date_Value
INNER JOIN Attendance att ON s.ID = att.StudentID AND cd.Date_Value = att.Att_Date
INNER JOIN Attendance_Code attc ON att.Attendance_CodeID = attc.ID

WHERE
cd.Date_Value &lt;= (CASE WHEN '%param1%' = 'Yes' THEN to_date('%param3%', 'MM/DD/YYYY') ELSE SYSDATE END)
AND cd.Date_Value &gt;= (CASE WHEN '%param1%' = 'Yes' THEN to_date('%param2%', 'MM/DD/YYYY') ELSE to_date('1/1/1900', 'MM/DD/YYYY') END)
AND cd.MembershipValue = 1
AND att.Att_Mode_Code = 'ATT_ModeDaily'
AND attc.Presence_Status_CD = 'Present'

GROUP BY to_char(cd.Date_Value, 'MM/YYYY'), s.Grade_Level
)
,isa AS
(
SELECT to_char(to_date(mem_days.m, 'MM/YYYY'), 'YYYY') y, to_char(to_date(mem_days.m, 'MM/YYYY'), 'Month') m, mem_days.gl gl, ROUND(att_days.da/mem_days.dm*100, 2) pct
FROM mem_days
INNER JOIN att_days ON mem_days.m = att_days.m AND mem_days.gl = att_days.gl
ORDER BY mem_days.m, gl
)
, isa_sch AS
(
SELECT mem_days.m md, to_char(to_date(mem_days.m, 'MM/YYYY'), 'YYYY') y, to_char(to_date(mem_days.m, 'MM/YYYY'), 'Month') m, 'All' gl, ROUND(SUM(att_days.da)/SUM(mem_days.dm)*100, 2) pct
FROM mem_days
INNER JOIN att_days ON mem_days.m = att_days.m AND mem_days.gl = att_days.gl
GROUP BY mem_days.m, to_char(to_date(mem_days.m, 'MM/YYYY'), 'YYYY'), to_char(to_date(mem_days.m, 'MM/YYYY'), 'Month')
ORDER BY mem_days.m
)
,isa_ytd AS
(
SELECT (CASE WHEN '%param1%' = 'Yes' THEN 'Range' ELSE 'YTD' END) y, (CASE WHEN '%param1%' = 'Yes' THEN '%param2%' || ' - ' || '%param3%' ELSE 'YTD' END) m, mem_days.gl gl, ROUND(SUM(att_days.da)/SUM(mem_days.dm)*100, 2) pct
FROM mem_days
INNER JOIN att_days ON mem_days.m = att_days.m AND mem_days.gl = att_days.gl
GROUP BY mem_days.gl
ORDER BY mem_days.gl
)
, isa_sch_ytd AS
(
SELECT (CASE WHEN '%param1%' = 'Yes' THEN 'Range' ELSE 'YTD' END) y, (CASE WHEN '%param1%' = 'Yes' THEN '%param2%' || ' - ' || '%param3%' ELSE 'YTD' END) m, 'All' gl, ROUND(SUM(att_days.da)/SUM(mem_days.dm)*100, 2) pct
FROM mem_days
INNER JOIN att_days ON mem_days.m = att_days.m AND mem_days.gl = att_days.gl
)

SELECT y, m, to_char(gl), pct
FROM isa
UNION ALL
SELECT y, m, gl, pct
FROM isa_sch
UNION ALL
SELECT y, m, to_char(gl), pct
FROM isa_ytd
UNION ALL
SELECT y, m, gl, pct
FROM isa_sch_ytd</textarea></SQLQuery>
<ReportHeader><th>Year</th><th>Month</th><th>Grade Level</th><th>ISA %</th></ReportHeader>
<CreateStudentSelection>0</CreateStudentSelection>
<StudentSelectionQuery></StudentSelectionQuery>
<CreateStudentSelectionB></CreateStudentSelectionB>
<IncludeRowNumber>0</IncludeRowNumber>
<OpeninNewWindow>0</OpeninNewWindow>
<HideCopyButton></HideCopyButton>
<HideCSVButton></HideCSVButton>
<HideTabButton></HideTabButton>
<HidePDFButton></HidePDFButton>
<ShowSearchBox></ShowSearchBox>
<ShowResultsInfo></ShowResultsInfo>
<ParameterName1>Custom Date Range</ParameterName1>
<ParameterVal1>Yes</ParameterVal1>
<ParameterCal1></ParameterCal1>
<ParameterOpt1>yesorno</ParameterOpt1>
<ParameterName2>Start Date</ParameterName2>
<ParameterVal2>8/22/2016</ParameterVal2>
<ParameterCal2>1</ParameterCal2>
<ParameterOpt2></ParameterOpt2>
<ParameterName3>End Date</ParameterName3>
<ParameterVal3>12/5/2016</ParameterVal3>
<ParameterCal3>1</ParameterCal3>
<ParameterOpt3></ParameterOpt3>
<ParameterName4></ParameterName4>
<ParameterVal4></ParameterVal4>
<ParameterCal4></ParameterCal4>
<ParameterOpt4></ParameterOpt4>
<ParameterName5></ParameterName5>
<ParameterVal5></ParameterVal5>
<ParameterCal5></ParameterCal5>
<ParameterOpt5></ParameterOpt5>
<PdfOrientation></PdfOrientation>
<NoCSVQuotes></NoCSVQuotes>
<NoRowSel></NoRowSel>
<ShowSHC></ShowSHC>
<UseColRe></UseColRe>
<UseFixHdr></UseFixHdr>
<HidePrint></HidePrint>
<ReportNotes></ReportNotes>
<UseColFilt></UseColFilt>
<UseRowGroups></UseRowGroups>
<UseRowGroupsOptions>Opt1</UseRowGroupsOptions>
<DisplayRowGroupsCol></DisplayRowGroupsCol>
<UseRowGroupsCounts></UseRowGroupsCounts>
<sqlChartsReport></sqlChartsReport>
<InitialsqlChart></InitialsqlChart>
<InitialChartName></InitialChartName>
<RowGroupsDesc></RowGroupsDesc>
<FLSMapField></FLSMapField>
<ExcFrmList></ExcFrmList>
<ExecGrp></ExecGrp>
<ValueLi3>0</ValueLi3>
<NoHeaderRow></NoHeaderRow>
<ReportDirections></ReportDirections>
Posted by Adam Labay, 0 comments

For My PGCPS Friends: The What-If Report

There’s ongoing and increasing talk of the Growth Mindset going around.

And, more often than not, all it amounts to is bulletin boards chock-full of motivational sayings.  And little else.

Instead, wouldn’t it be nice to do something for your students that actually helps them connect effort and achievement?

Enter the What-If report.
whatifsample

Say you’re like me, and have that kid (or kids, or all of your kids) who never completes more than 60% of an assignment.  Frequently-cited reasons include:

  • I don’t know what I’m doing, so my score’s not going to improve by doing more work anyway.
  • 60% is passing, isn’t it?
  • It’s just not worth my time.

To start proving to them that the work would have paid off, I started placing three grades at the top of each assignment:

  1. Your grade in my class right now
  2. Your score on this assignment
  3. What your class grade would have been if you had received full credit on this assignment.

The kids loved it and started putting forth more effort.  This is even more effective if you do make-up assignments or have a robust system in place for revision.

Here’s how to do it:

Step 1: Pull a grade report from SchoolMax.

Go into SchoolMax, click on Reports >> Grades, and pull a “Student Assignments and Grade Listing” Report.

Make sure to export to CSV.

export

Step 2: Make a copy of this Google Spreadsheet.

savereport

Click the screenshot, go to File >> Make a Copy, and it’s yours to work with.

Or, open the file directly from the link: https://docs.google.com/spreadsheets/d/1DqFpyRWkPW8_ARiSechFnw0Nj6lUNtxaiP1tdC57QUY/edit#gid=0

Step 3: Insert the grade report into the What-If Spreadsheet.

Open the tab named GR101.CSV, delete all the contents, and paste the contents of your grade report.

Step 4: Pick the assignment.

Return to the first sheet, and pick an assignment.

Nota Bene: For this to work, all of your assignments in SchoolMax must:

  1. have unique names, i.e. “Homework 9/16/16” instead of just “Homework” each week.
  2. be unique within 40 characters, i.e. “Revised first draft of analytical essay on <<topic>>” will get chopped to “Revised first draft of analytical essay” and you’re back to problem (a).

Step 5: Read off the appropriate values.

Note: This was written for 3 reporting categories, as tends to be the case in PGCPS.  If there are more than 3 categories, you’re screwed.  Let me know and we’ll work something out.

Step 6: Enjoy a lovely beverage.

Cheers

Posted by Adam Labay, 0 comments

PowerSchool SQLReport: Assignment List and Projected GPA

A vice principal wanted a report that can be given weekly to Homeroom teachers, which provides them with each of their students’:

  • Current grade in each class
  • Average by category (Homework, Classwork, Assessment) in each class
  • Projected GPA

The code is below if you want to use it for yourself.  This is intended to be used with SQLReports4.  If you don’t have it on your PS server, I strongly encourage its addition.

A few notes:

  1. You need to specify the GPA Calculation method.  Mine are named “Projected GPA %%” where %% is the term name.  Change to suit your needs.
  2. If a category has no assignments for a student (possibly from exemptions), it is not included in the report at all.
  3. If a category has a denominator of zero (say, the only assignment was extra credit), then the percent is blank.

The code:

Continue reading →

Posted by Adam Labay, 0 comments

Migrating Google Apps: Copy Folder Utility

All things being equal, migrating your Google Drive from one account to another should be easy:

  1. Transfer ownership of all files to the new account
  2. Profit

Unfortunately, if your account is on a Google Apps Domain, your administrator may have disabled ownership outside the domain.  If it’s Google Apps for Education, they didn’t get a say in the matter at all. (Fair enough – FERPA is a cruel mistress, after all.)

In principle, this doesn’t pose too much of a barrier, expanding the previous process to:

  1. Account A shares all files with Account B
  2. Account B creates local copies of shared files
  3. Account B trashes the original, shared files
  4. Enjoy a lovely beverage

And if all you have is a pile of files lying around, this isn’t a problem.  But heaven help you if your files are in folders, since Google Drive lacks folder-copying (also known as xcopy) functionality.

Enter a guy named Eric, whose Copy Folder extension basically saved my behind.  Steps 1-4 above now apply, with the added bonus that the extension automatically removes the irritating “Copy of…” prefix from all the new files.  Brilliant!

It does seem to have the odd issue with enormous collections (>500 files in a folder), but all told I was able to save an estimated 20,000 files in a week, impossible by any other method.

Posted by Adam Labay, 0 comments