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();
}
returnToPageError($message, $ex);
}
$tagId = $newtagid[1];
} else {
if ($type !== "CURRENT" && $type !== "NULL") {
db_rollback_transaction();
$message = "There was an error creating the tags for the worksheet, please try again.";
returnToPageError($message);
}
}
if ($type !== "NULL" && $tagId !== "") {
$query5 = "INSERT INTO TQUESTIONTAGS (`Tag ID`, `Stored Question ID`) VALUES ({$tagId}, {$sqid});";
try {
db_query_exception($query5);
} catch (Exception $ex) {
db_rollback_transaction();
$message = "There was a problem adding a tag to the worksheet, please try again.";
returnToPageErrorException($message, $ex);
}
}
}
}
db_commit_transaction();
$message = "Worksheet ({$wname}) added successfully.";
returnToPageSuccess($message, $vid);
} else {
$message = "Something went wrong adding the worksheet, please try again.";
returnToPageError($message);
}
function updateAllTags($string)
{
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);
}
