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 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 addNote($studentId, $staffId, $setId, $note)
{
try {
$query = "INSERT INTO TREPORTNOTES (StudentID, StaffID, GroupID, Note, Date) " . "VALUES ({$studentId}, {$staffId}, {$setId}, '{$note}', NOW())";
db_insert_query_exception($query);
} catch (Exception $ex) {
failRequest($ex->getMessage());
}
succeedRequest();
}
function getTagId($string)
{
$array = explode(':', $string);
$tagid = $array[0];
$type = $array[1];
if ($type == 'NEW') {
//Add a brand new tag
$now = date("Y-m-d H:i:s", time());
$query = "INSERT INTO `TTAGS`(`Name`, `Date Added`) VALUES ('{$tagid}', '{$now}');";
try {
$newtagid = db_insert_query_exception($query);
} catch (Exception $ex) {
$errorMessage = "There was a problem adding the tag ({$tagid})." . $ex->getMessage();
error_log($errorMessage);
return NULL;
}
return $newtagid[1];
} else {
if ($type == 'ADD') {
return $tagid;
} else {
$errorMessage = "There was a problem adding the tag ({$tagid})." . $ex->getMessage();
error_log($errorMessage);
return NULL;
}
}
}
}
} else {
//Not enough info to proceed
$message .= "You have not entered all of the fields required to create a user.";
returnToPageError($message);
}
$userid = $resultArray[1];
if ($role === 'STUDENT') {
//Student user
$query2 = "INSERT INTO TSTUDENTS (`User ID`, `Preferred Name`, `DOB`)\r\n VALUES({$userid}, '{$prefName}', '{$dob}');";
} else {
//Staff user
$query2 = "INSERT INTO TSTAFF (`User ID`, `Title`, `Initials`, `Classroom`, `Phone Number`)\r\n VALUES({$userid}, '{$title}', '{$initials}', '{$classroom}', '{$number}');";
}
try {
$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 updateTag($string, $nberror)
{
$array = explode(':', $string);
$qid = $array[0];
$tagid = $array[1];
$type = $array[2];
if ($type == 'NEW') {
//Add a brand new tag
//Check if the tag is actually new or not, if not then just add the question
try {
try {
$query1 = "SELECT `Tag ID` FROM TTAGS WHERE `Name` = '{$tagid}'";
$newtagid = db_select_single_exception($query1, "Tag ID");
} catch (Exception $ex) {
if ($ex->getCode() === 199) {
$now = date("Y-m-d H:i:s", time());
$query = "INSERT INTO `TTAGS`(`Name`, `Date Added`) VALUES ('{$tagid}','{$now}');";
$resultArray = db_insert_query_exception($query);
$newtagid = $resultArray[1];
} else {
$nberror[] = "There was a problem adding the tag '{$tagid}'.";
return $nberror;
}
}
$query = "INSERT INTO `TQUESTIONTAGS` (`Tag ID`, `Stored Question ID`) VALUES ({$newtagid}, {$qid});";
db_query_exception($query);
} catch (Exception $ex) {
$nberror[] = "There was a problem adding the tag '{$tagid}'.";
return $nberror;
}
} else {
if ($type == 'ADD') {
//Add a new tag for the question
$query = "INSERT INTO `TQUESTIONTAGS` (`Tag ID`, `Stored Question ID`) VALUES ({$tagid}, {$qid});";
try {
db_query_exception($query);
} catch (Exception $ex) {
$nberror[] = "There was a problem adding a tag for an unknown question.";
return $nberror;
}
} else {
if ($type == 'DELETE') {
//Delete a tag
$query = "DELETE FROM `TQUESTIONTAGS` WHERE `Tag ID` = {$tagid} AND `Stored Question ID` = {$qid}";
try {
db_query_exception($query);
} catch (Exception $ex) {
$nberror[] = "There was a problem deleting a tag.";
return $nberror;
}
} else {
$nberror[] = "There was a problem saving a tag.";
return $nberror;
}
}
}
return $nberror;
}
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);
}
