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.");
}
}
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.");
}
}
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;
}
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 function getMajors()
{
$query = "SELECT major.code AS 'Code', major.name AS 'Name', major.id\n\t\t\t\tFROM `" . App::getDbName() . "`.major";
try {
$query = $this->db->prepare($query);
$query->execute();
return $query->fetchAll(PDO::FETCH_ASSOC);
} catch (Exception $e) {
Mailer::sendDevelopers($e->getMessage(), __FILE__);
throw new Exception("Could not retrieve majors data 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 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 insert($code, $name)
{
try {
$query = "INSERT INTO `" . App::getDbName() . "`.`" . self::DB_TABLE . "` (`" . self::DB_COLUMN_CODE . "`, `" . self::DB_COLUMN_NAME . "`)\n\t\t\t\tVALUES(\n\t\t\t\t\t:code,\n\t\t\t\t\t:name\n\t\t\t\t)";
$dbConnection = DatabaseManager::getConnection();
$query = $dbConnection->prepare($query);
$query->bindParam(':code', $code, PDO::PARAM_STR);
$query->bindParam(':name', $name, PDO::PARAM_STR);
$query->execute();
return true;
} catch (Exception $e) {
Mailer::sendDevelopers($e->getMessage(), __FILE__);
throw new Exception("Could not insert course into database.");
}
}
public static function retrieveSingle($id)
{
$query = "SELECT `" . self::DB_COLUMN_ID . "`, `" . self::DB_COLUMN_INSTRUCTOR_ID . "`, `" . self::DB_COLUMN_STUDENT_ID . "`, `" . self::DB_COLUMN_STUDENT_CONCERNS . "`, `" . self::DB_COLUMN_PROJECT_TOPIC_OTHER . "`, `" . self::DB_COLUMN_RELEVANT_FEEDBACK_OR_GUIDELINES . ",`" . self::DB_COLUMN_ADDITIONAL_COMMENTS . "`\n\t\t\tFROM `" . App::getDbName() . "`.`" . self::DB_TABLE . "`\n\t\t\tWHERE `" . self::DB_COLUMN_ID . "`=:id";
try {
$dbConnection = DatabaseManager::getConnection();
$query = $dbConnection->prepare($query);
$query->bindParam(':id', $id, PDO::PARAM_INT);
$query->execute();
return $query->fetch(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
Mailer::sendDevelopers($e->getMessage(), __FILE__);
throw new Exception("Could not retrieve data from database.");
}
// end 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 retrieveStudentsWithAppointment($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";
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) {
Mailer::sendDevelopers($e->getMessage(), __FILE__);
throw new Exception("Could not retrieve data from database.");
}
}
/**
* 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 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.");
}
}
