public static function getTestCases($qId)
{
$db = DatabaseManager::getConnection();
$query = 'SELECT inputCase,outputCase,isSample FROM TestCases WHERE qid=:qid';
$bindings = array('qid' => $qId);
return $db->select($query, $bindings);
}
public static function viewScoreboard($questionId)
{
$db = DatabaseManager::getConnection();
$query = 'SELECT Scoreboard.status as Status,UserDetails.Name as Name
FROM Scoreboard join UserDetails
ON Scoreboard.UserId = UserDetails.UserId
where Scoreboard.questionId=:qid';
$bindings = array('qid' => $questionId);
return $db->select($query, $bindings);
}
public static function disconnectServiceType($serviceType)
{
try {
$query = "DELETE\n\t\t\tFROM `" . App::getDbName() . "`.`" . self::DB_TABLE . "`\n\t\t\tWHERE `" . self::DB_COLUMN_SERVICE_TYPE . "` = :service_type";
$dbConnection = DatabaseManager::getConnection();
$query = $dbConnection->prepare($query);
$query->bindParam(':service_type', $serviceType, PDO::PARAM_STR);
$query->execute();
return true;
} catch (Exception $e) {
Mailer::sendDevelopers($e->getMessage(), __FILE__);
throw new Exception("Could not access database. <br/>Please try again.");
}
}
/**
* @param $db
* @throws Exception
*/
public static function retrieve()
{
$query = "SELECT id, email, f_name, l_name, mobile, ci, credits\n\t\t FROM `" . App::getDbName() . "`.student";
try {
$dbConnection = DatabaseManager::getConnection();
$query = $dbConnection->prepare($query);
$query->execute();
$rows = $query->fetchAll(PDO::FETCH_ASSOC);
return $rows;
} catch (PDOException $e) {
throw new Exception("Something terrible happened. Could not retrieve students data from database.: ");
}
// end catch
}
public static function login($useremail, $password)
{
$db = DatabaseManager::getConnection();
$queryString = 'SELECT * FROM UserDetails WHERE EmailId = :useremail AND Password = :password';
$bindings = array('useremail' => $useremail, 'password' => $password);
$result = $db->select($queryString, $bindings);
if ($result != false) {
$_SESSION['username'] = $result[0]['Name'];
$_SESSION['emailid'] = $result[0]['EmailId'];
$_SESSION['department'] = $result[0]['Department'];
$_SESSION['userid'] = $result[0]['UserId'];
self::setUserType($result[0]['Type']);
return isset($_SESSION['username']);
}
return false;
}
public static function updateMailSent()
{
date_default_timezone_set('Europe/Athens');
$dateNow = new DateTime();
$dateNow = $dateNow->format(Dates::DATE_FORMAT_IN);
try {
$query = "INSERT INTO `" . App::getDbName() . "`.`" . self::DB_TABLE . "`\n\t\t\t\tVALUES(\n\t\t\t\t\t:now\n\t\t\t\t)";
$dbConnection = DatabaseManager::getConnection();
$query = $dbConnection->prepare($query);
$query->bindParam(':now', $dateNow, PDO::PARAM_STR);
$query->execute();
return true;
} catch (Exception $e) {
throw new Exception("Could not data into database.");
}
}
public static function retrieveCurrTermAllTeachingCourses()
{
$query = "SELECT `" . UserFetcher::DB_TABLE . "`.`" . UserFetcher::DB_COLUMN_FIRST_NAME . "`,\n\t\t\t\t\t\t `" . UserFetcher::DB_TABLE . "`.`" . UserFetcher::DB_COLUMN_LAST_NAME . "`,\n\t\t\t\t\t\t `" . CourseFetcher::DB_TABLE . "`.`" . CourseFetcher::DB_COLUMN_CODE . "`,\n\t\t\t\t\t\t `" . CourseFetcher::DB_TABLE . "`.`" . CourseFetcher::DB_COLUMN_NAME . "`,\n\t\t\t\t\t\t `" . TermFetcher::DB_TABLE . "`.`" . TermFetcher::DB_COLUMN_NAME . "` AS\n\t\t\t\t\t\t" . TermFetcher::DB_TABLE . "_" . TermFetcher::DB_COLUMN_NAME . "\n\t\t\tFROM `" . App::getDbName() . "`.`" . self::DB_TABLE . "`\n\t\t\tINNER JOIN `" . App::getDbName() . "`.`" . UserFetcher::DB_TABLE . "`\n\t\t\t\tON `" . Tutor_has_course_has_termFetcher::DB_TABLE . "`.`" . Tutor_has_course_has_termFetcher::DB_COLUMN_TUTOR_USER_ID . "` = `" . UserFetcher::DB_TABLE . "`.`" . UserFetcher::DB_COLUMN_ID . "`\n\t\t\tINNER JOIN `" . TermFetcher::DB_TABLE . "`\n\t\t\t\tON `" . Tutor_has_course_has_termFetcher::DB_TABLE . "`.`" . Tutor_has_course_has_termFetcher::DB_COLUMN_TERM_ID . "` = `" . TermFetcher::DB_TABLE . "`.`" . TermFetcher::DB_COLUMN_ID . "`\n\t\t\tINNER JOIN `" . CourseFetcher::DB_TABLE . "`\n\t\t\t\tON `" . Tutor_has_course_has_termFetcher::DB_TABLE . "`.`" . Tutor_has_course_has_termFetcher::DB_COLUMN_COURSE_ID . "` = `" . CourseFetcher::DB_TABLE . "`.`" . CourseFetcher::DB_COLUMN_ID . "`\n\t\t\tWHERE (:now BETWEEN `" . TermFetcher::DB_TABLE . "`.`" . TermFetcher::DB_COLUMN_START_DATE . "` AND `" . TermFetcher::DB_TABLE . "`.`" . TermFetcher::DB_COLUMN_END_DATE . "`)";
try {
date_default_timezone_set('Europe/Athens');
$now = new DateTime();
$now = $now->format(Dates::DATE_FORMAT_IN);
$dbConnection = DatabaseManager::getConnection();
$query = $dbConnection->prepare($query);
$query->bindParam(':now', $now, PDO::PARAM_STR);
$query->execute();
return $query->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
Mailer::sendDevelopers($e->getMessage(), __FILE__);
throw new Exception("Could not retrieve teaching courses from current terms from database.");
}
}
/**
* Returns a single column from the next row of a result set or FALSE if there are no more rows.
*
* @param $what
* @param $field
* @param $value
* @param $id
* @return mixed
* @throws Exception
*/
public function updateInfo($what, $field, $value, $id)
{
// I have only added few, but you can add more. However do not add 'password' even though the parameters will only be given by you and not the user, in our system.
$allowed = ['id', 'username', 'f_name', 'l_name', 'email', 'COUNT(mobile)', 'mobile', 'user', 'gen_string', 'COUNT(gen_string)', 'COUNT(id)', 'img_loc'];
if (!in_array($what, $allowed, true) || !in_array($field, $allowed, true)) {
throw new InvalidArgumentException();
} else {
try {
$query = "UPDATE `" . App::getDbName() . "`.`" . $field . "` SET `{$what}` = ? WHERE `id`= ?";
$dbConnection = DatabaseManager::getConnection();
$query = $dbConnection->prepare($query);
$query->bindValue(1, $value, PDO::PARAM_STR);
$query->bindValue(2, $id, PDO::PARAM_INT);
$query->execute();
return true;
} catch (Exception $e) {
throw new Exception($e->getMessage());
}
}
}
/**
* NEEDS TESTING
* @param $dateStart
* @param $dateEnd
* @param $tutorId
* @throws Exception
* @internal param $db
* @return bool
*/
public static function existDatesBetween($dateStart, $dateEnd, $tutorId)
{
date_default_timezone_set('Europe/Athens');
$dateStart = $dateStart->format(Dates::DATE_FORMAT_IN);
$dateEnd = $dateEnd->format(Dates::DATE_FORMAT_IN);
$query = "SELECT COUNT(`" . self::DB_TABLE . "`.`" . self::DB_COLUMN_ID . "`),`" . CourseFetcher::DB_TABLE . "`\n\t\t\tFROM `" . App::getDbName() . "`.`" . self::DB_TABLE . "`\n\t\t\tWHERE `" . self::DB_COLUMN_TUTOR_USER_ID . "` = :tutor_id\n\t\t\tAND(`" . self::DB_TABLE . "`.`" . self::DB_COLUMN_START_TIME . "` BETWEEN {$dateStart} AND {$dateEnd})";
try {
$dbConnection = DatabaseManager::getConnection();
$query = $dbConnection->prepare($query);
$query->bindParam(':tutor_id', $tutorId, PDO::PARAM_INT);
$query->execute();
if ($query->fetchColumn() === '0') {
return false;
}
return $query->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
Mailer::sendDevelopers($e->getMessage(), __FILE__);
throw new Exception("Could not retrieve teaching courses data from database.");
}
return true;
}
public static function existsUserId($id)
{
try {
$sql = "SELECT COUNT(" . self::DB_COLUMN_USER_ID . ") FROM `" . App::getDbName() . "`.`" . self::DB_TABLE . "` WHERE `" . self::DB_COLUMN_USER_ID . "` = :user_id";
$dbConnection = DatabaseManager::getConnection();
$dbConnection = $dbConnection->prepare($sql);
$dbConnection->bindParam(':user_id', $id, PDO::PARAM_INT);
$dbConnection->execute();
if ($dbConnection->fetchColumn() === '0') {
return false;
}
} catch (Exception $e) {
Mailer::sendDevelopers($e->getMessage(), __FILE__);
throw new Exception("Could not check if tutor id already exists on database.");
}
return true;
}
public static function delete($id)
{
try {
$query = "DELETE FROM `" . App::getDbName() . "`.`" . self::DB_TABLE . "` WHERE `" . self::DB_COLUMN_ID . "` = :id";
$dbConnection = DatabaseManager::getConnection();
$query = $dbConnection->prepare($query);
$query->bindParam(':id', $id, PDO::PARAM_INT);
$query->execute();
return true;
} catch (Exception $e) {
Mailer::sendDevelopers($e->getMessage(), __FILE__);
throw new Exception("Could not delete instructor from database.");
}
}
public static function countForTermIds($termIds, $labels = [])
{
if (empty($labels)) {
$labels = self::$labels;
}
foreach ($termIds as $key => $termId) {
$termBindParams[] = ':term_id_' . $key;
}
$termBindParams = implode(', ', $termBindParams);
$labelBindParams = "'" . implode("', '", $labels) . "'";
$query = "SELECT COUNT(" . self::DB_COLUMN_ID . ") AS total\n\t\t\tFROM `" . App::getDbName() . "`.`" . self::DB_TABLE . "`\n WHERE `" . self::DB_TABLE . "`.`" . self::DB_COLUMN_TERM_ID . "` in ({$termBindParams})\n AND `" . self::DB_TABLE . "`.`" . self::DB_COLUMN_LABEL_MESSAGE . "` in ({$labelBindParams})";
try {
$dbConnection = DatabaseManager::getConnection();
$query = $dbConnection->prepare($query);
foreach ($termIds as $key => $termId) {
$query->bindValue(":term_id_{$key}", $termId, PDO::PARAM_INT);
}
$query->execute();
return $query->fetch(PDO::FETCH_ASSOC)['total'];
} catch (PDOException $e) {
Mailer::sendDevelopers($e->getMessage(), __FILE__);
throw new Exception("Could not retrieve data from database.");
}
}
public function deleteTeachingCourse($courseId)
{
if (!preg_match('/^[0-9]+$/', $courseId)) {
throw new Exception("Data tempering detected.\n\t\t\t<br/>You're trying to hack this app.<br/>Developers are being notified about this.<br/>Expect Us.");
}
$tutorId = $this->getId();
try {
$query = "DELETE FROM `" . App::getDbName() . "`.`" . self::DB_TABLE_TUTOR_HAS_COURSE_HAS_TERM . "` WHERE `tutor_user_id`=:id AND`course_id`=:courseId;";
$dbConnection = DatabaseManager::getConnection();
$query = $dbConnection->prepare($query);
$query->bindParam(':id', $tutorId, PDO::PARAM_INT);
$query->bindParam(':courseId', $courseId, PDO::PARAM_INT);
$query->execute();
return true;
} catch (Exception $e) {
throw new Exception("Could not delete course from database.");
}
}
/**
*
* @param string $group The database group to connect to
*/
function __construct($connection = 'default')
{
Console::debugEx(LOG_DEBUG1, __CLASS__, "Initializing connection for %s.", $connection);
$this->conn = DatabaseManager::getConnection($connection);
}
public static function existsName($name)
{
try {
$query = "SELECT COUNT(" . self::DB_COLUMN_NAME . ") FROM `" . App::getDbName() . "`.`" . self::DB_TABLE . "` WHERE `" . self::DB_COLUMN_NAME . "` = :name";
$dbConnection = DatabaseManager::getConnection();
$query = $dbConnection->prepare($query);
$query->bindParam(':name', $name, PDO::PARAM_STR);
$query->execute();
if ($query->fetchColumn() === '0') {
return false;
}
} catch (Exception $e) {
Mailer::sendDevelopers($e->getMessage(), __FILE__);
throw new Exception("Could not check if term name already exists on database. <br/> Aborting process.");
}
return true;
}
<?php
namespace Application;
include_once "src/Application/DatabaseManager.class.php";
$db_connector = new DatabaseManager("./config.ini");
$connection = $db_connector->getConnection();
//Create new table if not exists
$storage_links = 'CREATE TABLE IF NOT EXISTS `storage_links` (
`link_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT \'Link ID\',
`creation_date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
`is_limited` INT(1) DEFAULT 0,
`link_hash` VARCHAR(32) COMMENT \'Link hash\',
`link_url` TEXT COMMENT \'Link address\',
PRIMARY KEY (`link_id`))
ENGINE=MyISAM;';
$connection->query($storage_links);
$redirect_statistic = 'CREATE TABLE IF NOT EXISTS `redirect_statistic` (
`link_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT \'Link ID\',
`redirect_date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
`user_agent` VARCHAR(128) COMMENT \'User agent\',
`redirect_link` VARCHAR(128) COMMENT \'Redirect link\',
PRIMARY KEY (`link_id`))
ENGINE=MyISAM;';
$connection->query($redirect_statistic);
$test_storage_links = 'CREATE TABLE IF NOT EXISTS `test_storage_links` LIKE `storage_links`';
$connection->query($test_storage_links);
$test_redirect_statistic = 'CREATE TABLE IF NOT EXISTS `test_redirect_statistic` LIKE `redirect_statistic`';
$connection->query($test_redirect_statistic);
public static function UpdateChallengeInfo($challengeId, $challengeName, $challengeStatement, $startDate, $endDate, $type)
{
$db = DatabaseManager::getConnection();
$queryString = 'UPDATE Challenge SET cName=:cName,cDesc=:cDesc,startDate=:startDate,endDate=:endDate,Type=:type WHERE cId=:challengeId';
$bindings = array('cName' => $challengeName, 'cDesc' => $challengeStatement, 'startDate' => $startDate, 'endDate' => $endDate, 'challengeId' => $challengeId, 'type' => $type);
$isUpdateSuccessful = $db->insert($queryString, $bindings);
return $isUpdateSuccessful;
}
public static function retrieveStudentsWithPendingAppointment($appointmentId)
{
$query = "SELECT `" . UserFetcher::DB_TABLE . "`.`" . UserFetcher::DB_COLUMN_FIRST_NAME . "` AS\n " . UserFetcher::DB_TABLE . "_" . UserFetcher::DB_COLUMN_FIRST_NAME . ",\n `" . UserFetcher::DB_TABLE . "`.`" . UserFetcher::DB_COLUMN_ID . "` AS\n " . UserFetcher::DB_TABLE . "_" . UserFetcher::DB_COLUMN_ID . ",\n `" . AppointmentFetcher::DB_TABLE . "`.`" . AppointmentFetcher::DB_COLUMN_START_TIME . "`,\n `" . AppointmentFetcher::DB_TABLE . "`.`" . AppointmentFetcher::DB_COLUMN_END_TIME . "`,\n `" . UserFetcher::DB_TABLE . "`.`" . UserFetcher::DB_COLUMN_LAST_NAME . "` AS\n " . UserFetcher::DB_TABLE . "_" . UserFetcher::DB_COLUMN_LAST_NAME . ",\n `" . InstructorFetcher::DB_TABLE . "`.`" . InstructorFetcher::DB_COLUMN_ID . "` AS\n " . InstructorFetcher::DB_TABLE . "_" . InstructorFetcher::DB_COLUMN_ID . ",\n `" . InstructorFetcher::DB_TABLE . "`.`" . InstructorFetcher::DB_COLUMN_FIRST_NAME . "` AS\n " . InstructorFetcher::DB_TABLE . "_" . InstructorFetcher::DB_COLUMN_FIRST_NAME . ",\n `" . InstructorFetcher::DB_TABLE . "`.`" . InstructorFetcher::DB_COLUMN_LAST_NAME . "` AS\n " . InstructorFetcher::DB_TABLE . "_" . InstructorFetcher::DB_COLUMN_LAST_NAME . ",\n `" . AppointmentFetcher::DB_TABLE . "`.`" . AppointmentFetcher::DB_COLUMN_COURSE_ID . "`,\n `" . AppointmentFetcher::DB_TABLE . "`.`" . AppointmentFetcher::DB_COLUMN_TERM_ID . "`,\n `" . self::DB_TABLE . "`.`" . self::DB_COLUMN_ID . "` , `" . self::DB_TABLE . "`.`" . self::DB_COLUMN_APPOINTMENT_ID . "` , `" . self::DB_TABLE . "`.`" . self::DB_COLUMN_STUDENT_ID . "`,\n `" . self::DB_TABLE . "`.`" . self::DB_COLUMN_REPORT_ID . "`, `" . self::DB_TABLE . "`.`" . self::DB_COLUMN_INSTRUCTOR_ID . "`, `" . StudentFetcher::DB_TABLE . "`.`" . StudentFetcher::DB_COLUMN_FIRST_NAME . "` AS " . StudentFetcher::DB_TABLE . "_" . StudentFetcher::DB_COLUMN_FIRST_NAME . ", `" . StudentFetcher::DB_TABLE . "`.`" . StudentFetcher::DB_COLUMN_LAST_NAME . "` AS " . StudentFetcher::DB_TABLE . "_" . StudentFetcher::DB_COLUMN_LAST_NAME . ", `" . AppointmentFetcher::DB_TABLE . "`.`" . AppointmentFetcher::DB_COLUMN_LABEL_MESSAGE . "`,\n\t\t\t`" . AppointmentFetcher::DB_TABLE . "`.`" . AppointmentFetcher::DB_COLUMN_ID . "` AS\n\t\t\t" . AppointmentFetcher::DB_TABLE . "_" . AppointmentFetcher::DB_COLUMN_ID . "\n\t\t\t, `" . AppointmentFetcher::DB_TABLE . "`.`" . AppointmentFetcher::DB_COLUMN_LABEL_COLOR . "`\n\t\t\tFROM `" . App::getDbName() . "`.`" . self::DB_TABLE . "`\n\t\t\tINNER JOIN `" . App::getDbName() . "`.`" . StudentFetcher::DB_TABLE . "`\n\t\t\tON `" . App::getDbName() . "`.`" . StudentFetcher::DB_TABLE . "`.`" . StudentFetcher::DB_COLUMN_ID . "` = `" . self::DB_TABLE . "`.`" . self::DB_COLUMN_STUDENT_ID . "`\n INNER JOIN `" . App::getDbName() . "`.`" . AppointmentFetcher::DB_TABLE . "`\n\t\t\tON `" . App::getDbName() . "`.`" . AppointmentFetcher::DB_TABLE . "`.`" . AppointmentFetcher::DB_COLUMN_ID . "` = `" . self::DB_TABLE . "`.`" . self::DB_COLUMN_APPOINTMENT_ID . "`\n INNER JOIN `" . App::getDbName() . "`.`" . UserFetcher::DB_TABLE . "`\n\t\t\tON `" . App::getDbName() . "`.`" . AppointmentFetcher::DB_TABLE . "`.`" . AppointmentFetcher::DB_COLUMN_TUTOR_USER_ID . "` = `" . UserFetcher::DB_TABLE . "`.`" . UserFetcher::DB_COLUMN_ID . "`\n INNER JOIN `" . App::getDbName() . "`.`" . InstructorFetcher::DB_TABLE . "`\n\t\t\tON `" . App::getDbName() . "`.`" . InstructorFetcher::DB_TABLE . "`.`" . InstructorFetcher::DB_COLUMN_ID . "` = `" . self::DB_TABLE . "`.`" . self::DB_COLUMN_INSTRUCTOR_ID . "`\n\t\t\tWHERE `" . self::DB_TABLE . "`.`" . self::DB_COLUMN_APPOINTMENT_ID . "`=:appointment_id\n\t\t\tAND `" . AppointmentFetcher::DB_TABLE . "`.`" . AppointmentFetcher::DB_COLUMN_LABEL_MESSAGE . "`=" . Appointment::LABEL_MESSAGE_PENDING;
try {
$dbConnection = DatabaseManager::getConnection();
$query = $dbConnection->prepare($query);
$query->bindParam(':appointment_id', $appointmentId, PDO::PARAM_INT);
$query->execute();
return $query->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
App::storeError($e->getMessage());
throw new Exception("Could not retrieve data from database.");
}
}
public static function updateCorrectSubmissionTime($userId, $questionId, $solvedTime)
{
$db = DatabaseManager::getConnection();
$queryString = 'UPDATE Scoreboard SET endTime=:solvedTime WHERE questionID=:qid and UserId=:userId';
$bindings = array('questionId' => $questionId, 'userId' => $userId, 'solvedTime' => $solvedTime);
return $db->insert($queryString, $bindings);
}
/**
* Verifies a user with given email exists. returns true if found; else false
*
* @param $email
* @param $table
* @throws Exception
* @internal param $db
*/
public static function emailExists($email, $table)
{
$email = trim($email);
$query = "SELECT COUNT(id) FROM `" . App::getDbName() . "`.`" . $table . "` WHERE email = :email";
$dbConnection = DatabaseManager::getConnection();
$dbConnection = $dbConnection->prepare($query);
$dbConnection->bindParam(':email', $email, PDO::PARAM_STR);
try {
$dbConnection->execute();
$rows = $dbConnection->fetchColumn();
if ($rows == 1) {
return true;
} else {
return false;
}
// end else if
} catch (PDOException $e) {
throw new Exception("Something terrible happened. Could not access database.");
}
// end catch
}
public function updateAvatarImg($avatar_img_loc)
{
$id = $this->getId();
try {
$query = "UPDATE `" . App::getDbName() . "`.user SET `img_loc`= :avatar_img WHERE `id`= :user_id";
$dbConnection = DatabaseManager::getConnection();
$query = $dbConnection->prepare($query);
$query->bindParam(':avatar_img', $avatar_img_loc, PDO::PARAM_STR);
$query->bindParam(':user_id', $id, PDO::PARAM_INT);
$query->execute();
return true;
} catch (PDOException $e) {
throw new Exception("Something terrible happened. Could not update database.");
}
// end try catch
}
public static function insert($reportId)
{
try {
$query = "INSERT INTO `" . App::getDbName() . "`.`" . self::DB_TABLE . "`\n\t\t\t(`" . self::DB_COLUMN_REPORT_ID . "`)\n\t\t\tVALUES(:report_id)";
$dbConnection = DatabaseManager::getConnection();
$query = $dbConnection->prepare($query);
$query->bindParam(':report_id', $reportId, PDO::PARAM_INT);
$query->execute();
} catch (Exception $e) {
Mailer::sendDevelopers($e->getMessage(), __FILE__);
throw new Exception("Could not insert report data into database.");
}
return false;
}
public static function insert($reportId)
{
try {
$queryInsertUser = "******" . App::getDbName() . "`.`" . self::DB_TABLE . "`\n\t\t\t(`" . self::DB_COLUMN_REPORT_ID . "`)\n\t\t\tVALUES(:report_id)";
$dbConnection = DatabaseManager::getConnection();
$queryInsertUser = $dbConnection->prepare($queryInsertUser);
$queryInsertUser->bindParam(':report_id', $reportId, PDO::PARAM_INT);
$queryInsertUser->execute();
} catch (Exception $e) {
throw new Exception("Could not insert report data into database.");
}
}
public static function getSourceCode($userId, $questionId)
{
$db = DatabaseManager::getConnection();
$query = 'SELECT SourceCode
FROM Scoreboard
WHERE questionId=:questionId AND UserId=:userId
';
$bindings = array('questionId' => $questionId, 'userId' => $userId);
return $db->select($query, $bindings);
}
/**
* Retrieve pending report give a tutor id for current terms.
* @param $tutorId
* @return array
* @throws Exception
*/
public function retrievePendingForCurrentTerms($tutorId)
{
$query = "SELECT `" . self::DB_TABLE . "`.`" . self::DB_COLUMN_ID . "`, `" . self::DB_TABLE . "`.`" . self::DB_COLUMN_LABEL_MESSAGE . "`,\n\t\t\t`" . self::DB_TABLE . "`.`" . self::DB_COLUMN_LABEL_COLOR . "`, `" . AppointmentFetcher::DB_TABLE . "`.`" . self::DB_COLUMN_ID . "`\n\t\t\tAS " . AppointmentFetcher::DB_TABLE . "_" . AppointmentFetcher::DB_COLUMN_ID . "\n\t\t\tFROM `" . App::getDbName() . "`.`" . self::DB_TABLE . "`\n\t\t\tINNER JOIN `" . App::getDbName() . "`.`" . AppointmentHasStudentFetcher::DB_TABLE . "`\n\t\t\t\tON `" . AppointmentHasStudentFetcher::DB_TABLE . "`.`" . AppointmentHasStudentFetcher::DB_COLUMN_REPORT_ID . "` =\n\t\t\t\t\t`" . self::DB_TABLE . "`.`" . self::DB_COLUMN_ID . "`\n\t\t\tINNER JOIN `" . App::getDbName() . "`.`" . AppointmentFetcher::DB_TABLE . "`\n\t\t\t\tON `" . AppointmentFetcher::DB_TABLE . "`.`" . AppointmentFetcher::DB_COLUMN_ID . "` =\n\t\t\t\t\t`" . AppointmentHasStudentFetcher::DB_TABLE . "`.`" . AppointmentHasStudentFetcher::DB_COLUMN_APPOINTMENT_ID . "`\n\t\t\tINNER JOIN `" . TermFetcher::DB_TABLE . "`\n\t\t\t\tON `" . TermFetcher::DB_TABLE . "`.`" . TermFetcher::DB_COLUMN_ID . "` =\n\t\t\t\t\t`" . AppointmentFetcher::DB_TABLE . "`.`" . AppointmentFetcher::DB_COLUMN_TERM_ID . "`\n\n\t\t\tWHERE (:now BETWEEN `" . TermFetcher::DB_COLUMN_START_DATE . "` AND `" . TermFetcher::DB_COLUMN_END_DATE . "`)\n\t\t\tAND `" . AppointmentFetcher::DB_TABLE . "`.`" . AppointmentFetcher::DB_COLUMN_TUTOR_USER_ID . "`=:tutor_id\n\t\t\tAND `" . self::DB_TABLE . "`.`" . self::DB_COLUMN_LABEL_MESSAGE . "`='" . Report::LABEL_MESSAGE_PENDING_FILL . "'\n\t\t\tORDER BY `" . self::DB_TABLE . "`.`" . self::DB_COLUMN_ID . "` ASC";
try {
$now = App::getCurrentTime();
$dbConnection = DatabaseManager::getConnection();
$query = $dbConnection->prepare($query);
$query->bindParam(':now', $now, PDO::PARAM_STR);
$query->bindParam(':tutor_id', $tutorId, PDO::PARAM_STR);
$query->execute();
return $query->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
App::storeError($e->getMessage());
throw new Exception("Could not retrieve data from database.");
}
}
public static function existsStudentId($studentId)
{
try {
$query = "SELECT COUNT(" . self::DB_COLUMN_STUDENT_ID . ") FROM `" . App::getDbName() . "`.`" . self::DB_TABLE . "` WHERE `" . self::DB_COLUMN_STUDENT_ID . "` = :studentId";
$dbConnection = DatabaseManager::getConnection();
$query = $dbConnection->prepare($query);
$query->bindParam(':studentId', $studentId, PDO::PARAM_INT);
$query->execute();
if ($query->fetchColumn() === '0') {
return false;
}
} catch (Exception $e) {
Mailer::sendDevelopers($e->getMessage(), __FILE__);
throw new Exception("Could not check if stuent id already exists on database.");
}
return true;
}
public static function updateGenString($id, $generatedString)
{
try {
$query = "UPDATE `" . App::getDbName() . "`.`user` SET `gen_string` = :gen_string WHERE `id` = :id";
$dbConnection = DatabaseManager::getConnection();
$query = $dbConnection->prepare($query);
$query->bindParam(':gen_string', $generatedString, PDO::PARAM_STR);
$query->bindParam(':id', $id, PDO::PARAM_INT);
$query->execute();
} catch (Exception $e) {
Mailer::sendDevelopers($e->getMessage(), __FILE__);
throw new Exception("Could not update generated string. Please re-send password link to user that was created.");
}
}
