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 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 updateWorksheet($vid, $type)
{
global $userid;
if ($type === "DELETE") {
$query = "UPDATE TWORKSHEETVERSION Set `Deleted` = TRUE WHERE `Version ID` = {$vid}";
$errorMsg = "There was an error deleted the worksheet.";
$successMsg = "Worksheet {$vid} succesfully deleted by {$userid}";
$delete = TRUE;
} else {
if ($type === "RESTORE") {
$query = "UPDATE TWORKSHEETVERSION Set `Deleted` = FALSE WHERE `Version ID` = {$vid}";
$errorMsg = "There was an error restoring the worksheet.";
$successMsg = "Worksheet {$vid} succesfully restored by {$userid}";
$delete = FALSE;
} else {
failRequest("There was an error completing your request;");
}
}
try {
db_begin_transaction();
db_query_exception($query);
updateRelatedCompletedQuestions($vid, $delete);
db_commit_transaction();
} catch (Exception $ex) {
db_rollback_transaction();
returnToPageError($ex, $errorMsg);
}
$response = array("success" => TRUE);
echo json_encode($response);
infoLog($successMsg);
exit;
}
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 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 incrementFailedLogins($userid, $attempts)
{
$query = "UPDATE TUSERS SET `Login Attempts` = {$attempts} WHERE `User ID` = {$userid}";
try {
db_query_exception($query);
$msg = "Failed logins for user {$userid} have been incremented.";
infoLog($msg);
} catch (Exception $ex) {
$msg = "There was an error while incrementing the failed logins for the user {$userid}";
error_log($msg);
returnToPageError($ex->getMessage(), null);
}
}
function returnToPageErrorException($message, $ex)
{
if (!isset($message)) {
$message = 'Something has gone wrong';
}
errorLog($message . ": " . $ex->getMessage());
returnToPageError($message);
}
$resultArray1 = db_insert_query_exception($query2);
} catch (Exception $ex) {
if ($ex->getMessage() !== null) {
$desc = $ex->getMessage();
} else {
$desc = "Something went wrong while saving the new user.";
}
$message .= seriousError($desc);
returnToPageError($message);
}
$message = "User '{$fname} {$sname}' successfully added.";
returnToPageSuccess($message);
} else {
$desc = "Something went wrong while saving the new user.";
$message .= seriousError($desc);
returnToPageError($message);
}
function returnToPageError($message)
{
$type = 'ERROR';
if (!isset($message)) {
$message = 'Something has gone wrong';
}
infoLog($message);
$_SESSION['message'] = new Message($type, $message);
header("Location: ../createUser.php");
exit;
}
function returnToPageSuccess($message)
{
$type = 'SUCCESS';
function editWorksheetForGroup($gwid, $level, $type, $setid, $staffid)
{
if ($gwid != null && $gwid > 0) {
header("Location: ../editSetResults.php?gwid={$gwid}");
exit;
} else {
$message = "You have not selected an existing worksheet.";
returnToPageError($message, $level, $type, $setid, $staffid);
}
}
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};";
try {
db_query_exception($query);
} catch (Exception $ex) {
db_rollback_transaction();
$msg = "Something went wrong while updating question {$count} on worksheet {$wname} ({$version}).";
returnToPageError($msg, $version);
}
} else {
$flag = false;
}
$count = $count + 1;
} while ($flag);
$updateString = filter_input(INPUT_POST, 'updateTags', FILTER_SANITIZE_STRING);
if ($updateString) {
$nberror = updateAllTags($updateString, $nberror);
}
$message = "'{$wname}' successfully updated";
if (count($nberror) > 0) {
// Deal with the non=breaking errors
$message .= " with the following errors. <br>";
for ($i = 0; $i < count($nberror); $i++) {
}
$message .= seriousError($desc);
returnToPageError($message, $userid);
}
} else {
//Not enough info to proceed
$message .= "You have not entered all of the required fields.";
returnToPageError($message, $userid);
}
$message = "User '{$fname} {$sname}' successfully updated.";
updateCurrentUser();
returnToPageSuccess($message, $userid);
} else {
$desc = "Something went wrong while saving the users details.";
$message .= seriousError($desc);
returnToPageError($message, $userid);
}
function returnToPageError($message, $userid)
{
$type = 'ERROR';
if (!isset($message)) {
$message = 'Something has gone wrong';
}
infoLog($message);
$_SESSION['message'] = new Message($type, $message);
header("Location: ../editUser.php?userid={$userid}");
exit;
}
function returnToPageSuccess($message, $userid)
{
$type = 'SUCCESS';
