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.");
     }
 }
Esempio n. 3
0
 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;
 }
Esempio n. 4
0
 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.");
     }
 }
Esempio n. 6
0
 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;
 }
Esempio n. 9
0
 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.");
     }
 }
Esempio n. 10
0
 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.");
     }
 }
Esempio n. 13
0
 /**
  * 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;
 }
Esempio n. 14
0
 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.");
     }
 }