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; }