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