function getMarkbookForSetAndTeacher($setid, $staffid)
{
$query1 = "SELECT U.`User ID` ID, CONCAT(S.`Preferred Name`,' ',U.Surname) Name FROM TUSERGROUPS G \n JOIN TUSERS U ON G.`User ID` = U.`User ID` JOIN TSTUDENTS S ON U.`User ID` = S.`User ID` \n WHERE G.`Group ID` = {$setid}\n AND G.`Archived` <> 1\n ORDER BY U.Surname;";
$query2 = "SELECT WV.`Version ID` VID, GW.`Group Worksheet ID` GWID, WV.`WName` WName, WV.`VName` VName, DATE_FORMAT(GW.`Date Due`, '%d/%m/%Y') Date, DATE_FORMAT(GW.`Date Due`, '%d/%m') ShortDate, SUM(SQ.`Marks`) Marks \n FROM TGROUPWORKSHEETS GW\n JOIN TWORKSHEETVERSION WV ON WV.`Version ID` = GW.`Version ID`\n JOIN TSTOREDQUESTIONS SQ on SQ.`Version ID` = WV.`Version ID` \n WHERE GW.`Primary Staff ID` = {$staffid} AND GW.`Group ID` = {$setid} AND WV.`Deleted` = 0 \n AND (GW.`Deleted` IS NULL OR GW.`Deleted` <> 1) AND (GW.`Hidden` IS NULL OR GW.`Hidden` <> 1)\n GROUP BY GW.`Group Worksheet ID` \n ORDER BY GW.`Date Due`, WV.`WName`;";
try {
$students = db_select_exception($query1);
$worksheets = db_select_exception($query2);
} catch (Exception $ex) {
$message = "There was an error retrieving the markbook";
returnToPageError($ex, $message);
}
$resultsArray = array();
foreach ($worksheets as $worksheet) {
$GWID = $worksheet["GWID"];
$query = "select SQ.`Version ID` VID, `Group Worksheet ID` GWID, CQ.`Student ID` StuID, SUM(Mark) Mark, SUM(Marks) Marks from TCOMPLETEDQUESTIONS CQ\n join TSTOREDQUESTIONS SQ ON CQ.`Stored Question ID` = SQ.`Stored Question ID`\n WHERE `Group Worksheet ID` = {$GWID}\n group by CQ.`Student ID`;";
try {
$results = db_select_exception($query);
} catch (Exception $ex) {
$message = "There was an error retrieving the markbook";
returnToPageError($ex, $message);
}
$newArray = array();
foreach ($results as $result) {
$id = $result["StuID"];
$newArray[$id] = $result;
}
$vid = $worksheet["VID"];
$resultsArray[$GWID] = $newArray;
}
$response = array("success" => TRUE, "students" => $students, "worksheets" => $worksheets, "results" => $resultsArray);
echo json_encode($response);
}
function addToGroup($studentid, $groupid)
{
$query1 = "SELECT `Link ID` FROM TUSERGROUPS WHERE `User ID` = {$studentid} AND `Group ID` = {$groupid};";
try {
db_begin_transaction();
$links = db_select_exception($query1);
if (count($links) == 0) {
$query2 = "INSERT INTO `TUSERGROUPS`(`User ID`, `Group ID`, `Archived`) VALUES ({$studentid},{$groupid},0)";
db_insert_query_exception($query2);
} else {
$query3 = "UPDATE `TUSERGROUPS` SET `Archived` = 0 WHERE ";
foreach ($links as $link) {
$id = $link["Link ID"];
$query3 .= "`Link ID` = {$id} AND ";
}
$query3 = substr($query3, 0, -5);
db_query_exception($query3);
}
db_commit_transaction();
} catch (Exception $ex) {
db_rollback_transaction();
failRequest($ex->getMessage());
}
succeedRequest();
}
function searchWorksheets($searchTerms)
{
$searchArray = convertSearchTerms($searchTerms);
if (count($searchArray) === 0) {
returnToPageNoResults();
}
$query = "SELECT `Version ID`, `WName` Name FROM `TWORKSHEETVERSION` WHERE ";
foreach ($searchArray as $key => $searchTerm) {
if ($key != 0) {
$query .= " OR ";
}
$query .= "`WName` LIKE '%{$searchTerm}%' ";
}
$query .= "ORDER BY `WName`";
try {
$worksheets = db_select_exception($query);
if (count($worksheets) === 0) {
returnToPageNoResults();
}
} catch (Exception $ex) {
returnToPageError($ex, "There was an error running the search query");
}
$fullSearchArray = getFullSearchArray($searchArray);
// Score the worksheets
foreach ($worksheets as $key => $worksheet) {
$worksheets[$key] = scoreWorksheet($worksheet, $fullSearchArray);
}
$sorted = array_orderby($worksheets, 'Score', SORT_DESC, 'Name', SORT_ASC);
$response = array("success" => TRUE, "vids" => $sorted);
echo json_encode($response);
exit;
}
function getNotesForStaff($staffId)
{
try {
$query = "SELECT R.ID, S.`Preferred Name`, U.`First Name`, U.`Surname`, G.`Name`, R.`Note`, R.`Date`, DATE_FORMAT(R.`Date`, '%b %D %Y %k:%i') date_format FROM `TREPORTNOTES` R\n LEFT JOIN TUSERS U ON U.`User ID` = R.`StudentID`\n LEFT JOIN TSTUDENTS S ON S.`User ID` = R.`StudentID`\n LEFT JOIN TGROUPS G ON G.`Group ID` = R.GroupID\n WHERE StaffID = {$staffId} ";
$query .= "ORDER BY G.Name, U.Surname, R.Date DESC;";
succeedRequest(db_select_exception($query));
} catch (Exception $ex) {
failRequest($ex->getMessage());
}
}
function getAllStudents($orderby, $desc)
{
$query = "SELECT U.`User ID` ID, U.`First Name` FName, U.`Surname` SName FROM TUSERS U " . "JOIN TSTUDENTS S ON S.`User ID` = U.`User ID` ";
$query .= orderBy([$orderby], [$desc]);
try {
$users = db_select_exception($query);
} catch (Exception $ex) {
$message = "There was an error loading the students";
returnToPageError($ex, $message);
}
$response = array("success" => TRUE, "users" => $users);
echo json_encode($response);
}
function getSetsForStaffMember($staffid, $orderby, $desc)
{
$query = "select G.`Group ID` ID, G.`Name` Name from TGROUPS G\n join TUSERGROUPS UG on G.`Group ID` = UG.`Group ID`";
$query .= filterBy(["UG.`User ID`", "G.`Type ID`", "UG.`Archived`"], [$staffid, 3, 0]);
$query .= orderBy([$orderby], [$desc]);
try {
$sets = db_select_exception($query);
} catch (Exception $ex) {
errorLog("Error loading the worksheets: " . $ex->getMessage());
$response = array("success" => TRUE);
echo json_encode($response);
}
$response = array("success" => TRUE, "sets" => $sets);
echo json_encode($response);
}
function getAllCompletedWorksheetsForGroup($groupid, $staffid, $orderby, $desc)
{
$query = "SELECT GW.`Group Worksheet ID` ID, WV.`WName` WName, DATE_FORMAT(GW.`Date Due`, '%d/%m/%Y') DueDate FROM TGROUPWORKSHEETS GW \n JOIN TWORKSHEETVERSION WV ON GW.`Version ID` = WV.`Version ID` ";
$query .= filterBy(["GW.`Group ID`", "GW.`Primary Staff ID`", "WV.`Deleted`"], [$groupid, $staffid, "0"]);
$query .= "AND (GW.`Deleted` IS NULL OR GW.`Deleted` = 0) ";
$query .= orderBy([$orderby], [$desc]);
try {
$worksheets = db_select_exception($query);
} catch (Exception $ex) {
$message = "There was an error retrieving the worksheets";
returnToPageError($ex, $message);
}
$response = array("success" => TRUE, "worksheets" => $worksheets);
echo json_encode($response);
exit;
}
function convertTagNamesToArray($tag_name_array)
{
$tag_array = array();
foreach ($tag_name_array as $tag_name) {
if (strlen($tag_name) > 0 && $tag_name !== " ") {
$name = trim($tag_name);
$query = "SELECT `Tag ID` ID, `Name` From TTAGS WHERE Name = '{$name}'";
try {
$tag = db_select_exception($query);
} catch (Exception $ex) {
failRequestWithException("Error getting tags", $ex);
}
if (count($tag) > 0) {
array_push($tag_array, $tag[0]);
}
}
}
return $tag_array;
}
$fullName = $user->getFirstName() . ' ' . $user->getSurname();
$userid = $user->getUserId();
$userRole = $user->getRole();
$userval = base64_encode($user->getValidation());
} else {
header($resultArray[1]);
exit;
}
if (!authoriseUserRoles($userRole, ["SUPER_USER", "STAFF"])) {
header("Location: unauthorisedAccess.php");
exit;
}
$tagId = filter_input(INPUT_GET, 'tagid', FILTER_SANITIZE_NUMBER_INT);
$query = "select `Tag ID`, `Name` from TTAGS order by `Name`;";
try {
$tags = db_select_exception($query);
} catch (Exception $ex) {
}
if (isset($_SESSION['message'])) {
$Message = $_SESSION['message'];
$message = $Message->getMessage();
$type = $Message->getType();
unset($_SESSION['message']);
}
?>
<!DOCTYPE html>
<html>
<head lang="en">
<?php
pageHeader("Tags");
$worksheet = db_select_exception($query1);
} catch (Exception $ex) {
$msg = $ex->getMessage();
failWithMessage("Something went wrong selecting the worksheet with Version ID ({$vid}) - {$msg}");
}
$queries = array("SELECT V.`Worksheet ID` WID, V.`WName` WName, V.`VName` VName, V.`Author ID` AuthorID, S.`Initials` Author, V.`Date Added` Date, V.`Link` Link FROM TWORKSHEETVERSION V JOIN TSTAFF S ON V.`Author ID` = S.`Staff ID` WHERE V.`Version ID` = {$vid};", "SELECT S.`Stored Question ID` ID, S.`Number` Number, S.`Marks` Marks FROM TSTOREDQUESTIONS S WHERE S.`Version ID` = {$vid} ORDER BY S.`Question Order`;", "SELECT S.`Stored Question ID` ID, T.`Name` Name FROM TSTOREDQUESTIONS S JOIN TQUESTIONTAGS Q ON S.`Stored Question ID` = Q.`Stored Question ID` JOIN TTAGS T ON Q.`Tag ID` = T.`Tag ID` WHERE S.`Version ID` = {$vid} ORDER BY T.`Name`;", "SELECT T.`Name` Name, T.`Tag ID` ID FROM TSTOREDQUESTIONS S JOIN TQUESTIONTAGS Q ON S.`Stored Question ID` = Q.`Stored Question ID` JOIN TTAGS T ON Q.`Tag ID` = T.`Tag ID` GROUP BY T.`Name` ORDER BY COUNT(T.`Name`) DESC, T.`Name`; ", "SELECT S.`Initials` Initials, S.`User ID` ID FROM TSTAFF S ORDER BY S.`Initials`;");
$errors = array("Something went wrong retrieving the worksheet with Version ID ({$vid})", "Something went wrong retrieving questions for the worksheet with Version ID ({$vid})", "Something went wrong retrieving tags for the worksheet with Version ID ({$vid})", "Something went wrong loading all of the tags, please try again.", "Something went wrong loading all of the staff, please try again");
$variables = array("worksheet", "questions", "tags", "alltags", "staff");
for ($i = 0; $i < count($queries); $i++) {
if (!$stopFlag) {
$query = $queries[$i];
$error = $errors[$i];
$variable = $variables[$i];
if (isset($query, $variable, $error)) {
try {
${$variable} = db_select_exception($query);
} catch (Exception $ex) {
$stopFlag = true;
failWithMessage($error, $ex);
}
} else {
$stopFlag = true;
}
}
}
if (isset($_SESSION['message'])) {
$Message = $_SESSION['message'];
$message = $Message->getMessage();
$type = $Message->getType();
unset($_SESSION['message']);
}
$query = "UPDATE TWORKSHEETVERSION V \r\n SET V.`WName` = '{$wname}', V.`VName` = '{$vname}', V.`Link` = '{$link}', V.`Date Added` = '{$newdate}', V.`Author ID` = {$author}\r\n WHERE V.`Version ID` = {$version};";
try {
db_query_exception($query);
} catch (Exception $ex) {
db_rollback_transaction();
$msg = "Something went wrong updating the worksheet";
returnToPageError($msg, $version);
}
//Check each question
$count = 1;
$flag = true;
$query2 = "SELECT T.`Tag ID` ID FROM TTAGS T ORDER BY T.`Tag ID`;";
$query3 = "SELECT T.`Name` Name FROM TTAGS T ORDER BY T.`Tag ID`;";
try {
$alltagids = db_select_exception($query2);
$alltagnames = db_select_exception($query3);
} catch (Exception $ex) {
db_rollback_transaction();
$msg = "Something went setting up the tags.";
returnToPageError($msg, $version);
}
do {
$name = $count . 'a';
$qid = filter_input(INPUT_POST, $name, FILTER_SANITIZE_STRING);
if ($qid > 0) {
//Update number and marks
$name1 = $count . 'num';
$name2 = $count . 'mark';
$number = filter_input(INPUT_POST, $name1, FILTER_SANITIZE_STRING);
$marks = filter_input(INPUT_POST, $name2, FILTER_SANITIZE_STRING);
$query = "UPDATE TSTOREDQUESTIONS\r\n SET `Number` = '{$number}', `Marks` = {$marks}\r\n WHERE `Stored Question ID` = {$qid};";
$fullName = $user->getFirstName() . ' ' . $user->getSurname();
$userid = $user->getUserId();
$userRole = $user->getRole();
$userval = base64_encode($user->getValidation());
} else {
header($resultArray[1]);
exit;
}
if (!authoriseUserRoles($userRole, ["SUPER_USER", "STAFF"])) {
header("Location: unauthorisedAccess.php");
exit;
}
$gwid = filter_input(INPUT_GET, 'gwid', FILTER_SANITIZE_NUMBER_INT);
$query1 = "SELECT S.`User ID`, S.`Initials` FROM TSTAFF S \r\n JOIN TUSERS U ON S.`User ID` = U.`User ID`\r\n ORDER BY U.`Surname`;";
try {
$staff = db_select_exception($query1);
} catch (Exception $ex) {
$staff = array();
}
$postData = array("gwid" => $gwid, "type" => "WORKSHEETFORGWID", "userid" => $userid, "userval" => $userval);
$resp = sendCURLRequest("/requests/getWorksheet.php", $postData);
$respArray = json_decode($resp[1], TRUE);
$success = $respArray["success"];
if ($success) {
$details = $respArray["details"];
$worksheet = $respArray["worksheet"];
$results = $respArray["results"];
$completedWorksheets = $respArray["completedWorksheets"];
$notes = $respArray["notes"];
$students = $respArray["students"];
} else {
function setStudentWorksheetStatus()
{
global $studentWorksheets, $returns;
$query = "SELECT CW.`Group Worksheet ID` GWID, CW.`Notes` Notes, CW.`Completion Status` Comp, CW.`Date Status` Days\n FROM TCOMPLETEDWORKSHEETS CW\n WHERE ";
$inputs = $returns["inputs"];
if (array_key_exists("student", $inputs)) {
$student = $inputs["student"];
$query .= "CW.`Student ID` = {$student} AND ";
}
$query .= "CW.`Group Worksheet ID` IN (";
foreach ($studentWorksheets as $worksheet) {
$query .= $worksheet["GWID"] . ", ";
}
$query = substr($query, 0, -2);
$query .= ")";
try {
$results = db_select_exception($query);
foreach ($results as $result) {
$studentWorksheets[$result["GWID"]]["Notes"] = $result["Notes"];
$studentWorksheets[$result["GWID"]]["Comp"] = $result["Comp"];
$studentWorksheets[$result["GWID"]]["Days"] = $result["Days"];
}
} catch (Exception $ex) {
$message = "There was an error generating the report.";
failRequestWithException($message, $ex);
}
}
$body = "<html>\r\n <body>\r\n <p>Hi {$firstName},</p>\r\n <p>You have recently reported a forgotten password.</p>\r\n <p>To reset your password please go to the following <a href='{$url}'>link</a>.</p>\r\n <p>If you did not report a forgotten password then please contact us by replying to this email.</p>\r\n <p>Thanks,<br>Smarkbook</p>\r\n </body>\r\n </html>\r\n ";
try {
sendMailFromContact($email, $name, $body, $subject);
$message = "An email has been sent to {$email} containing a link to reset your password.";
returnToPageSuccess($message);
} catch (Exception $ex) {
$desc = "Something went wrong while sending the forgot password email.";
returnToPageError($desc);
}
} else {
if ($type === "RESET") {
if (isset($pwd, $code)) {
//Check the details match up for the right time
$query2 = "SELECT `User ID`, `Reset Code`, `Reset Time` FROM TUSERS WHERE `Email` = '{$email}'";
try {
$result2 = db_select_exception($query2);
if (count($result2) == 0) {
throw new Exception();
} else {
$userid = $result2[0]["User ID"];
$resetCode = $result2[0]["Reset Code"];
$resetTime = $result2[0]["Reset Time"];
}
} catch (Exception $ex) {
$desc = "Something went wrong while resetting your password. You may have entered the wrong email address. Please refresh and try again.";
$type = 'ERROR';
infoLog($desc);
$_SESSION['message'] = new Message($type, $desc);
header("Location: ../forgottenPassword.php?code={$code}");
exit;
}
function getWorksheetInformationFor($array)
{
$query = "SELECT SQ.`Stored Question ID` SQID, SQ.`Version ID` VID, SQ.`Number` Number, SQ.`Marks` Marks, WV.`WName` WName, WV.`VName` VName, WV.`Link` Link FROM TSTOREDQUESTIONS SQ\n JOIN TQUESTIONS Q ON Q.`Question ID` = SQ.`Question ID`\n JOIN TWORKSHEETVERSION WV ON WV.`Version ID` = SQ.`Version ID`\n WHERE SQ.`Stored Question ID` IN (";
foreach ($array as $row) {
$query .= $row["sqid"] . ", ";
}
$query = substr($query, 0, -2);
$query .= ");";
try {
$results = db_select_exception($query);
} catch (Exception $ex) {
failRequestWithException("Something went wrong loading all of the worksheet details", $ex);
}
foreach ($array as $rowKey => $row) {
foreach ($results as $result) {
if ($row["sqid"] === $result["SQID"]) {
$array[$rowKey]["details"] = $result;
// TODO Add a proper break function in here to break the for loop
break;
}
}
}
return $array;
}
}
if (isset($_SESSION['message'])) {
$message = $_SESSION['message'];
}
clearAllTemporaryVariables();
$level = getInput("GET", "level", "INT");
//$type = getInput("GET", "type", "INT");
$type = 1;
$groupid = getInput("GET", "groupid", "INT");
$staffid = getInput("GET", "staffid", "INT");
$vid = getInput("GET", "vid", "INT");
$query1 = "SELECT S.`Initials` Initials, S.`User ID` ID FROM TSTAFF S ORDER BY S.`Initials`;";
$query2 = "SELECT * FROM TGROUPS G JOIN TUSERGROUPS UG ON G.`Group ID` = UG.`Group ID` WHERE UG.`User ID` = 2 AND `Type ID` = 3 ORDER BY `Name`";
try {
$staff = db_select_exception($query1);
$groups = db_select_exception($query2);
} catch (Exception $ex) {
errorLog($ex->getMessage());
}
function getInput($method, $name, $type)
{
if ($method === "GET") {
$method = INPUT_GET;
} else {
if ($method === "POST") {
$method = INPUT_POST;
} else {
return 0;
}
}
if ($type === "STRING") {
function getNotesForGWID($gwid)
{
$query = "SELECT `Student ID`, `Notes` FROM TCOMPLETEDWORKSHEETS WHERE `Group Worksheet ID` = {$gwid};";
try {
$notes = optimiseArray(db_select_exception($query), "Student ID");
} catch (Exception $ex) {
errorLog("Something went wrong loading the notes for the worksheet: " . $ex->getMessage());
$test = array("success" => FALSE);
echo json_encode($test);
}
$test = array("success" => TRUE, "notes" => $notes);
echo json_encode($test);
}
$userid = $user->getUserId();
$userRole = $user->getRole();
$userval = base64_encode($user->getValidation());
} else {
header($resultArray[1]);
exit;
}
if (!authoriseUserRoles($userRole, ["SUPER_USER", "STAFF"])) {
header("Location: unauthorisedAccess.php");
exit;
}
$setId = isset($_GET['setid']) ? filter_input(INPUT_GET, 'setid', FILTER_SANITIZE_STRING) : 0;
$staffId = isset($_GET['staffid']) ? filter_input(INPUT_GET, 'staffid', FILTER_SANITIZE_STRING) : $userid;
$query = "SELECT G.`Group ID` ID, G.Name Name " . "FROM TUSERGROUPS U JOIN TGROUPS G ON U.`Group ID` = G.`Group ID` " . "WHERE `User ID` = {$staffId} AND G.`Type ID` = 3 AND U.`Archived` <> 1 ORDER BY G.Name;";
try {
$sets = db_select_exception($query);
if ($setId == 0 && count($sets) > 0) {
$setId = $sets[0]['ID'];
}
$message = filter_input(INPUT_GET, 'msg', FILTER_SANITIZE_STRING);
$type = filter_input(INPUT_GET, 'err', FILTER_SANITIZE_STRING);
} catch (Exception $ex) {
errorLog($ex->getMessage());
$success = FALSE;
$message = "There was an error loading the markbook.";
$type = "ERROR";
}
try {
$set = db_select_single_exception("SELECT Name FROM TGROUPS WHERE `Group ID` = {$setId};", "Name");
} catch (Exception $ex) {
$set = "";
function validateRequest($userid, $userval, $external)
{
if ($external === 'JIs7r') {
return 'EXTERNAL';
}
$query = "SELECT `Validation`, `Role` FROM TUSERS U WHERE `User ID` = {$userid}";
try {
$result = db_select_exception($query);
if (count($result) > 0 && $result[0]['Validation'] === $userval) {
return $result[0]['Role'];
} else {
errorLog("Request failed due to invalid credentials");
return false;
}
} catch (Exception $ex) {
//Request invalidated
errorLog("Request failed with exception: " . $ex->getMessage());
return false;
}
}
function findRelatedCompletedQuestions($vid)
{
$query = "SELECT CQ.`Completed Question ID` CQID FROM TCOMPLETEDQUESTIONS CQ\n JOIN TSTOREDQUESTIONS SQ ON CQ.`Stored Question ID` = SQ.`Stored Question ID`\n WHERE `Version ID` = {$vid};";
$cqids = db_select_exception($query);
return $cqids;
}
