iSAMS Student Photos for Moodle

<?php

///////////////////////////////
// This script will query your iSAMS database and return the location
// and network username of the latest photos for all students
// with a school photo on the system
//////////////////////////////

/////////////////////////////
// Uncomment for debugging
/////////////////////////////

//error_reporting(E_ALL);
//ini_set("display_errors", 1);

////////////////////////////
// Set your iSAMS database connection details here
///////////////////////////

$host="FQDN of your MSSQL Server";
$dbname="iSAMS DB Name";
$dbuser="iSAMS DB User";
$dbuserpass="Really strong password!";

try
{
  $db = new PDO("dblib:host=$host;dbname=$dbname", $dbuser, $dbuserpass);
}
catch(PDOException $e)
{
  echo "Connection failed: " . $e->getMessage();
}

echo "Connected to the database\n";


$query = "SELECT txtEmailAddress, (SELECT TOP (1) txtPath FROM TblPupilManagementPictures WHERE (txtSchoolID = TblPupilManagementPupils.txtSchoolID) ORDER BY intOrder DESC) AS txtPupilPicturePath FROM TblPupilManagementPupils WHERE (intSystemStatus = '1') AND ((SELECT TOP (1) txtPath FROM TblPupilManagementPictures AS TblPupilManagementPictures_1 WHERE (txtSchoolID = TblPupilManagementPupils.txtSchoolID) ORDER BY intOrder DESC) IS NOT NULL)";
$statement = $db->prepare($query);
$statement->execute();

echo "Executing query\n";

$data = fopen('user-picture-locations.csv', 'w');

while ($row = $statement->fetchAll(PDO::FETCH_NUM))
{
    foreach ($row as $rowitem)
    {
        $email = $rowitem[0];
        $split = preg_split("[@]", $email);
        $username = $split[0];

        $filelocation = $rowitem[1];
        $split = preg_split("[/]", $filelocation);
        $year = $split[3];
        $date = $split[4];
        $file = $split[5];

        $bashline = "cd iSAMS\; cd iSAMS.Files\; cd studentimages\; cd " . $year . "\; cd " . $date . "\; prompt; get " . $file . "," . $file . "," . $username . ".jpg," . PHP_EOL;
        echo "Writing " . $username . " line to file\n";
        fwrite($data, $bashline);
    }
}

?>