function createNewGroupWorksheet($staff, $setid, $versionid, $datedue)
{
$query = "insert into TGROUPWORKSHEETS (\n `Group ID`,\n `Primary Staff ID`,\n `Additional Staff ID`,\n `Additional Staff ID 2`,\n `Version ID`,\n `Date Due`,\n `Date Last Modified`)\n values(\n {$setid},";
foreach ($staff as $staffMember) {
if ($staffMember != null) {
$query .= " " . $staffMember . ",";
} else {
$query .= " null,";
}
}
$query .= " " . $versionid . ",";
if (isset($datedue)) {
$query .= " STR_TO_DATE('{$datedue}', '%d/%m/%Y'),";
} else {
$query .= " NOW(),";
}
$query .= " NOW());";
try {
db_begin_transaction();
$result = db_insert_query_exception($query);
$gwid = $result[1];
db_commit_transaction();
} catch (Exception $ex) {
db_rollback_transaction();
errorLog("Error creating a new group worksheet link: " . $ex->getMessage());
$resultArray = array("result" => FALSE);
echo json_encode($resultArray);
}
$resultArray = array("result" => TRUE, "gwid" => $gwid);
echo json_encode($resultArray);
}
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 removeFromGroup($studentid, $groupid)
{
$query = "UPDATE `TUSERGROUPS` SET `Archived`= 1 WHERE `User ID` = {$studentid} AND `Group ID` = {$groupid};";
try {
db_begin_transaction();
db_query_exception($query);
db_commit_transaction();
} catch (Exception $ex) {
db_rollback_transaction();
failRequest($ex->getMessage());
}
succeedRequest();
}
header("Location: ../unauthorisedAccess.php");
exit;
}
}
$user = $_SESSION["user"];
$wname = filter_input(INPUT_POST, 'worksheetname', FILTER_SANITIZE_STRING);
$vname = filter_input(INPUT_POST, 'versionname', FILTER_SANITIZE_STRING);
$author = filter_input(INPUT_POST, 'author', FILTER_SANITIZE_NUMBER_INT);
$date = filter_input(INPUT_POST, 'date', FILTER_SANITIZE_STRING);
$number = filter_input(INPUT_POST, 'questions', FILTER_SANITIZE_STRING);
$link = filter_input(INPUT_POST, 'link', FILTER_SANITIZE_URL);
$rawTags = filter_input(INPUT_POST, 'tags', FILTER_SANITIZE_STRING);
$wid = filter_input(INPUT_POST, 'wid', FILTER_SANITIZE_NUMBER_INT);
$informationArray = array($wname, $vname, $author, $date, $number, $rawTags, $link);
if (validation($wname, $author, $date, $number)) {
db_begin_transaction();
$newdate = date('Y-d-m h:m:s', strtotime(str_replace('/', '-', $date)));
$query = "INSERT INTO TWORKSHEETVERSION (`WName`, `VName`, `Link`, `Author ID`) VALUES ('{$wname}', '{$vname}', '{$link}', {$author});";
try {
$resultArray = db_insert_query_exception($query);
$vid = $resultArray[1];
} catch (Exception $ex) {
db_rollback_transaction();
$message = "Something went wrong adding the worksheet, please try again.";
returnToPageError($message, $ex);
}
if (!isset($wid)) {
$wid = $vid;
}
$query1 = "UPDATE TWORKSHEETVERSION SET `Worksheet ID` = {$wid} WHERE `Version ID` = {$vid};";
try {
function add_keyword_mappings($ref, $string, $resource_type_field, $partial_index = false, $is_date = false, $optional_column = '', $optional_value = '', $is_html = false)
{
# For each instance of a keyword in $string, add a keyword->resource mapping.
# Create keywords that do not yet exist.
# Increase the hit count of each keyword that matches.
# Store the position and field the string was entered against for advanced searching.
if (trim($string) == "") {
return false;
}
$keywords = split_keywords($string, true, $partial_index, $is_date, $is_html);
add_verbatim_keywords($keywords, $string, $resource_type_field);
// add in any verbatim keywords (found using regex).
db_begin_transaction();
for ($n = 0; $n < count($keywords); $n++) {
unset($kwpos);
if (is_array($keywords[$n])) {
$kwpos = $keywords[$n]['position'];
$keywords[$n] = $keywords[$n]['keyword'];
}
$kw = $keywords[$n];
if (!isset($kwpos)) {
$kwpos = $n;
}
add_keyword_to_resource($ref, $kw, $resource_type_field, $kwpos, $optional_column, $optional_value, false);
}
db_end_transaction();
}
function delete_challenge_cascading($id)
{
if (!is_valid_id($id)) {
message_error('Invalid ID.');
}
try {
db_begin_transaction();
db_delete('challenges', array('id' => $id));
db_delete('submissions', array('challenge' => $id));
db_delete('hints', array('challenge' => $id));
$files = db_select_all('files', array('id'), array('challenge' => $id));
foreach ($files as $file) {
delete_file($file['id']);
}
db_end_transaction();
} catch (PDOException $e) {
db_rollback_transaction();
log_exception($e);
}
}
function updateGroupWorksheet($worksheetDetails, $newResults, $completedWorksheets)
{
db_begin_transaction();
// Update the details for the group worksheet
try {
$gwid = $worksheetDetails["gwid"];
$staff1 = $worksheetDetails["staff1"];
$staff2 = !$worksheetDetails["staff2"] || $worksheetDetails["staff2"] == "0" ? "null" : $worksheetDetails["staff2"];
$staff3 = !$worksheetDetails["staff3"] || $worksheetDetails["staff3"] == "0" ? "null" : $worksheetDetails["staff3"];
$datedue = $worksheetDetails["dateDueMain"];
$stuNotes = mysql_real_escape_string($worksheetDetails["studentNotes"]);
$staffNotes = mysql_real_escape_string($worksheetDetails["staffNotes"]);
$hidden = $worksheetDetails["hidden"] ? "0" : "1";
$query = "UPDATE TGROUPWORKSHEETS SET `Primary Staff ID` = {$staff1}, `Additional Staff ID` = {$staff2}, `Additional Staff ID 2` = {$staff3}, " . "`Date Due` = STR_TO_DATE('{$datedue}', '%d/%m/%Y'), `Additional Notes Student` = '{$stuNotes}', `Additional Notes Staff` = '{$staffNotes}' " . ",`Hidden` = {$hidden}, `Date Last Modified` = NOW() " . "WHERE `Group Worksheet ID` = {$gwid};";
db_query_exception($query);
} catch (Exception $ex) {
db_rollback_transaction();
$message = "There was an error saving the details for the worksheet.";
errorLog($message . " Exception: " . $ex->getMessage());
$array = array("result" => FALSE, "message" => $message);
echo json_encode($array);
exit;
}
try {
foreach ($newResults as $key => $newResult) {
$array = explode("-", $key);
$stuId = $array[0];
$sqid = $array[1];
$cqid = $array[2];
$originalResult = $array[3];
if ($newResult != $originalResult) {
//The result needs to updated
//Write query and update
if ($cqid == 0) {
//Add a new question
$query = "INSERT INTO TCOMPLETEDQUESTIONS (`Stored Question ID`, `Mark`, `Student ID`, `Deleted`, `Group Worksheet ID`)\n VALUES ({$sqid}, {$newResult}, {$stuId}, 0, {$gwid});";
db_query_exception($query);
} else {
if ($newResult != "") {
//Update question
$query = "UPDATE TCOMPLETEDQUESTIONS SET `Mark` = {$newResult} WHERE `Completed Question ID` = {$cqid};";
db_query_exception($query);
} else {
//Delete question
$query = "DELETE FROM TCOMPLETEDQUESTIONS WHERE `Completed Question ID` = {$cqid};";
db_query_exception($query);
}
}
}
}
} catch (Exception $ex) {
db_rollback_transaction();
$message = "There was an error saving the results for the worksheet.";
errorLog($message . " Exception: " . $ex->getMessage());
$array = array("result" => FALSE, "message" => $message);
echo json_encode($array);
exit;
}
//Save all completed worksheet information
try {
$notes = $completedWorksheets["notes"];
$daysLate = $completedWorksheets["dates"];
$cwids = $completedWorksheets["cwid"];
$completionStatus = $completedWorksheets["completion"];
foreach ($completionStatus as $stuId => $compStatus) {
$cwid = array_key_exists($stuId, $cwids) ? $cwids[$stuId] : null;
$late = array_key_exists($stuId, $daysLate) ? $daysLate[$stuId] : null;
if ($late == "") {
$late = 'null';
}
$note = array_key_exists($stuId, $notes) ? mysql_real_escape_string($notes[$stuId]) : null;
if ($compStatus == "Not Required" && $note == null) {
// Not required so no CW
if ($cwid != "") {
// CW already exists so delete it
$query = "DELETE FROM TCOMPLETEDWORKSHEETS WHERE `Completed Worksheet ID` = {$cwid};";
db_query_exception($query);
}
} else {
if ($cwid != "") {
// CW already exists so update it
$cwid = $cwids[$stuId];
$query = "UPDATE TCOMPLETEDWORKSHEETS SET " . "`Completion Status` = '{$compStatus}', " . "`Date Status` = {$late}, " . "`Notes` = '{$note}', " . "`Student ID` = {$stuId}, " . "`Group Worksheet ID` = {$gwid} " . "WHERE `Completed Worksheet ID` = {$cwid}";
db_query_exception($query);
} else {
// CW doesn't exist so make a new one
$query = "INSERT INTO TCOMPLETEDWORKSHEETS " . "(`Group Worksheet ID`, `Student ID`, `Notes`, `Completion Status`, `Date Status`) " . "VALUES ({$gwid}, {$stuId}, '{$note}', '{$compStatus}', {$late});";
db_insert_query_exception($query);
}
}
// Calculate the date the student handed the work in
if ($late == null || $late == 'null') {
$late = 0;
}
$dateHandedIn = date_format(date_add(date_create_from_format('d/m/Y', $datedue), date_interval_create_from_date_string("{$late} days")), 'd/m/Y');
// Update the completed questions for that student
$query = "UPDATE TCOMPLETEDQUESTIONS " . "SET `Date Added` = STR_TO_DATE('{$dateHandedIn}', '%d/%m/%Y') " . "WHERE `Student ID` = {$stuId} AND `Group Worksheet ID` = {$gwid};";
db_query_exception($query);
}
} catch (Exception $ex) {
db_rollback_transaction();
$message = "There was an error saving the status of the worksheet.";
errorLog($message . " Exception: " . $ex->getMessage());
$array = array("result" => FALSE, "message" => $message);
echo json_encode($array);
exit;
}
db_commit_transaction();
$test = array("result" => TRUE);
echo json_encode($test);
}
