/** * Sends faculty members an email for missing exams */ public final function missingExamEmailReminders() { $model = new \Ventus\Faculty\Faculty(); $range = $this->getExamDateRange(time()); $rangeStart = $range['start']; $rangeEnd = $range['end']; // Get all exams that do not have files $sql = "SELECT CONCAT_WS('', r.course_code, course_section) AS course_code_section,\n GROUP_CONCAT(DISTINCT CONCAT(cc.professor_last_name, ', ', cc.professor_first_name) SEPARATOR ' | ') AS prof\n FROM ventus_professor_exam_requests r\n LEFT JOIN org_courses c\n ON (r.course_code = c.code AND r.course_section = c.section AND r.session = c.session)\n LEFT JOIN org_course_classes cc\n ON (c.course_id = cc.course_id AND r.teach_method = cc.teaching_method)\n WHERE r.exam_request_id NOT IN (\n SELECT request_id\n FROM ventus_faculty_files\n )\n AND r.exam_request_id IN (\n SELECT professor_exam_request_id\n FROM ventus_exam_requests\n WHERE is_cancelled = 0\n )\n AND r.exam_date BETWEEN :start AND :end\n AND r.faculty_cron_logged = 0\n AND r.deleted = 0\n AND r.is_confirmed = 1\n GROUP BY r.exam_request_id, c.course_id;"; $exams = $this->db->query($sql, array('start' => $rangeStart->format(DATETIME_MYSQL), 'end' => $rangeEnd->format(DATETIME_MYSQL)))->fetchAll(); foreach ($exams as $e) { // Check permissions $users = $model->fetchUsersWithPermission($e['course_code_section'], true); $emails = array(); foreach ($users as $u) { $emails[] = $u['email']; } if (!empty($emails)) { $html = parent::fetchEmailContent(FS_FACULTY . '/emails/missing-exams-reminder.php', $this->db->pick(array('course_code_section', 'prof'), $e)); $message = \Swift_Message::newInstance('Examens en attente / Pending exams')->setFrom(EMAIL_ALIAS_ACCESS_SERVICE_EXAMS . EMAIL_ORG_STAFF_DOMAIN)->setTo($emails)->setCc(EMAIL_ALIAS_ACCESS_SERVICE_EXAMS . EMAIL_ORG_STAFF_DOMAIN)->setBcc(EMAIL_ALIAS_ACCESS_SERVICE_VENTUS_COMMUNICATION . EMAIL_ORG_STAFF_DOMAIN); parent::sendEmail($message, $html); } } // Update faculty_cron_logged $uSql = "UPDATE ventus_professor_exam_requests\n SET faculty_cron_logged = 1\n WHERE exam_request_id NOT IN (\n SELECT request_id\n FROM ventus_faculty_files\n )\n AND exam_date BETWEEN :start AND :end;"; $this->db->query($uSql, array('start' => $rangeStart->format(DATETIME_MYSQL), 'end' => $rangeEnd->format(DATETIME_MYSQL))); }
/** * Notifies admins of expiring accounts */ public final function notifyAdminsForExpiry() { $startRange = new \DateTime(); $endRange = clone $startRange; $endRange->modify("+14 day"); $sql = "SELECT user_id, user_name, CONCAT(first_name, ' ', last_name) AS account_name, expires_on\n FROM ventus_users\n WHERE DATE(expires_on) BETWEEN :start AND :end\n ORDER BY expires_on ASC;"; $expiryNotify = $this->db->query($sql, array('start' => $startRange->format(DATETIME_MYSQL), 'end' => $endRange->format(DATETIME_MYSQL)))->fetchAll(); if (!empty($expiryNotify)) { $contentArray = array('expiryNotify' => $expiryNotify, 'startRange' => $startRange, 'endRange' => $endRange); $html = parent::fetchEmailContent(FS_PROFILE . '/emails/expiring-account.php', $contentArray); $message = \Swift_Message::newInstance("VENTUS: Rappel d'expiration de compte | Expiring accounts reminder")->setFrom(EMAIL_ALIAS_NO_REPLY . EMAIL_ORG_STAFF_DOMAIN)->setTo(EMAIL_ALIAS_VENTUS_ADMIN . EMAIL_ORG_STAFF_DOMAIN); parent::sendEmail($message, $html); } }
/** * Notifies workshop attendees of upcoming workshops */ public final function notifyWorkshopAttendees() { $sql = 'SELECT w.workshop, w.facilitator, DATE(w.start) AS start_date, s.email, TIME(w.start) AS start_time, TIME(w.end) AS end_time, w.location, CONCAT(s.first_name, " ", s.last_name) AS student_name, a.id AS attendance_id, w.service_id as department FROM ventus_workshops_attendees a JOIN ventus_workshops w ON a.workshop_id = w.workshop_id JOIN org_students s ON a.student_num = s.student_id JOIN ventus_users u ON w.emp_id = u.user_id WHERE a.cron_logged = 0 AND w.is_cancelled = 0 AND w.hidden_workshop = 0 AND DATE(start) = CURRENT_DATE + INTERVAL 1 DAY AND s.email <> "";'; $workshops = $this->db->query($sql)->fetchAll(); $uSql = "UPDATE ventus_workshops_attendees\n SET cron_logged = 1, updated_on = NOW()\n WHERE id = :id;"; $uSqlStmt = $this->db->prepare($uSql); foreach ($workshops as $r) { switch ($r['department']) { case SERVICE_ID_ACCESS: $html = parent::fetchEmailContent(FS_WORKSHOPS . '/emails/workshop-reminder-access-service.php', $r); break; case SERVICE_ID_COUNSELLING: $html = parent::fetchEmailContent(FS_WORKSHOPS . '/emails/workshop-reminder-counselling.php', $r); break; } $message = \Swift_Message::newInstance("Rappel d'atelier / Workshop reminder [{$r['workshop']}]")->setFrom(EMAIL_ALIAS_NO_REPLY . EMAIL_ORG_STAFF_DOMAIN)->setTo($r['email']); //If it's a workshop held by AS, accessia needs to be bcc'd if ($r['department'] === SERVICE_ID_ACCESS) { $message->setBcc(EMAIL_ALIAS_ACCESS_SERVICE_VENTUS_COMMUNICATION . EMAIL_ORG_STAFF_DOMAIN); } parent::sendEmail($message, $html); //Update database so that reminder is not sent out repeatedly $uSqlStmt->bindValue('id', $r['attendance_id'], \PDO::PARAM_INT); $this->db->execute($uSqlStmt); } }
public final function notifyNoShows() { $sql = 'SELECT s.email, CONCAT(s.first_name, " ", s.last_name) AS student_name, s.student_id, p.course_code, p.course_section, p.teach_method, DATE(r.official_evaluation_starttime) AS start_date, TIME(r.official_evaluation_starttime) AS start_time, TIME(r.official_evaluation_endtime) AS end_time FROM ventus_exam_requests r JOIN ventus_professor_exam_requests p ON r.professor_exam_request_id = p.exam_request_id JOIN org_students s ON r.student_num = s.student_id WHERE r.is_cancelled = 0 AND p.deleted = 0 AND r.proctor_cron_logged = 0 AND r.proctor_initiated = 0 AND DATE(r.official_evaluation_starttime) < CURRENT_DATE;'; $no_shows = $this->db->query($sql)->fetchAll(); $uSql = 'UPDATE ventus_exam_requests r JOIN ventus_professor_exam_requests p ON r.professor_exam_request_id = p.exam_request_id JOIN org_students s ON r.student_num = s.student_id SET r.proctor_cron_logged = 1 WHERE r.is_cancelled = 0 AND p.deleted = 0 AND r.proctor_cron_logged = 0 AND r.proctor_initiated = 0 AND DATE(r.official_evaluation_starttime) < DATE(NOW());'; $this->db->query($uSql); foreach ($no_shows as $r) { $subject = "Absence sur place / No show ({$r['student_id']}) ({$r['course_code']} {$r['course_section']} {$r['teach_method']})"; $html = parent::fetchEmailContent(FS_PROCTOR . '/emails/no-show-student.php', $r); $message = \Swift_Message::newInstance($subject)->setFrom(EMAIL_ALIAS_ACCESS_SERVICE_EXAMS . EMAIL_ORG_STAFF_DOMAIN)->setTo($r['email'])->setBcc(EMAIL_ALIAS_ACCESS_SERVICE_VENTUS_COMMUNICATION . EMAIL_ORG_STAFF_DOMAIN); parent::sendEmail($message, $html); } }
/** * Sends an email to Access Service notifying of students with more than one exam in a day * @param array $examDetails The exam details */ private function sendConflictEmail(array $examDetails) { $html = parent::fetchEmailContent(FS_EXAMS . '/emails/exam-conflict-notification.php', $examDetails); $message = \Swift_Message::newInstance('Ventus student with > 1 exam on same day')->setFrom(array('ventus' . EMAIL_ORG_STAFF_DOMAIN => 'ventus' . EMAIL_ORG_STAFF_DOMAIN))->setTo(array(EMAIL_ALIAS_ACCESS_SERVICE_EXAMS . EMAIL_ORG_STAFF_DOMAIN => EMAIL_ALIAS_ACCESS_SERVICE_EXAMS . EMAIL_ORG_STAFF_DOMAIN))->setBcc(EMAIL_ALIAS_ACCESS_SERVICE_VENTUS_COMMUNICATION . EMAIL_ORG_STAFF_DOMAIN); parent::sendEmail($message, $html); }
/** * When an appointment is marked as cancelled, this function is called and sends an email to the student * @param int $id The ID of the event * @param int $empId The current user */ private function sendCancellationEmail($id) { $sql = "SELECT DATE(c.appointment_start) AS start_date, s.email, c.event_id,\n TIME(c.appointment_start) AS start_time, TIME(c.appointment_end) AS end_time,\n CONCAT(s.first_name, ' ', s.last_name) AS student_name,\n CONCAT(u.first_name, ' ', u.last_name) AS emp_name, u.department\n FROM ventus_calendar c\n JOIN org_students s\n ON c.student = s.student_id\n JOIN ventus_users u\n ON c.emp_id = u.user_id\n JOIN ventus_calendar_appointment_types t\n ON c.appointment_type_id = t.appointment_type_id\n WHERE s.email <> ''\n AND c.event_id = :id\n AND t.email_enabled = 1;"; $result = $this->db->query($sql, array('id' => $id))->fetch(); switch ($result['department']) { case SERVICE_ID_ACCESS: $html = parent::fetchEmailContent(FS_CALENDAR . '/emails/appointment-cancelled-access-service.php', $result); break; case SERVICE_ID_COUNSELLING: $html = parent::fetchEmailContent(FS_CALENDAR . '/emails/appointment-cancelled-counselling.php', $result); break; } if (isset($html)) { $message = \Swift_Message::newInstance('Annulation de votre rendez-vous / Notice of appointment cancellation')->setFrom(EMAIL_ALIAS_NO_REPLY . EMAIL_ORG_STAFF_DOMAIN)->setTo($result['email']); //If it's an appointment at AS, accessia needs to be bcc'd if ($result['department'] === SERVICE_ID_ACCESS) { $message->setBcc(EMAIL_ALIAS_ACCESS_SERVICE_VENTUS_COMMUNICATION . EMAIL_ORG_STAFF_DOMAIN); } parent::sendEmail($message, $html); } }
/** * For a given final exam NOE, send a reminder to all the course professors * @param string $confirmation_id The NOE confirmation ID * @param string $course_code The course code * @param string $course_section The course section * @param string $session The session * @param string $teach_method The teach method */ public function sendFinalExamReminder($confirmation_id, $course_code, $course_section, $session, $teach_method) { $course_exceptions = unserialize(CAM_LETTER_EMAIL_EXCEPTIONS); if (!is_string($course_code)) { throw new \InvalidArgumentException("Course code supplied is not a valid string..."); } if (!ctype_digit($session)) { throw new \InvalidArgumentException("Session supplied is not a valid five digit integer..."); } //Get all the NOE details $sql = "SELECT session, course_code, course_section, exam_type, exam_date, exam_alternate_special,\n exam_alternate_special_student, exam_duration, confirmation_key, contact_name, contact_number,\n requestor_email, teach_method\n FROM ventus_professor_exam_requests\n WHERE confirmation_key = :confirmation_key\n AND deleted = 0;"; $noe_details = $this->db->query($sql, array('confirmation_key' => $confirmation_id))->fetch(); //Find all the relevant professor emails for this NOE $params = array($course_code, $course_section, $session, $teach_method); $sql = "SELECT DISTINCT(cc.professor_email)\n FROM org_course_classes cc\n JOIN org_courses c\n ON c.course_id = cc.course_id\n WHERE c.code = ?\n AND c.section = ?\n AND c.session = ?\n AND cc.teaching_method = ?\n AND cc.professor_email <> ''\n AND cc.stpaul = 0"; foreach ($course_exceptions as $key => $prefix) { $params[] = COURSE_CODE_PREFIX_ABBREVIATION_LENGTH; $params[] = $key; $sql .= " AND SUBSTRING(c.code, 1, ?) <> ?"; } $professor_emails = $this->db->query($sql, $params)->fetchAll(); // Initialize professor_email (1D array) $email_to = array(); if (in_array(substr($course_code, 0, COURSE_CODE_PREFIX_ABBREVIATION_LENGTH), array_keys($course_exceptions))) { $email_to = array($course_exceptions[substr($course_code, 0, COURSE_CODE_PREFIX_ABBREVIATION_LENGTH)] => $course_exceptions[substr($course_code, 0, COURSE_CODE_PREFIX_ABBREVIATION_LENGTH)]); } else { // Create a recursive iterator $it = new \RecursiveIteratorIterator(new \RecursiveArrayIterator($professor_emails)); // Push elements of the professor into professor_email foreach ($it as $v) { array_push($email_to, $v); } // Eliminate duplicate emails $email_to = array_unique($email_to); // Eliminate null, 0, '' or false $email_to = array_filter($email_to); } //Generate and send email $html = parent::fetchEmailContent(FS_PROFESSOR . '/emails/final-exam-reminder.php', $noe_details); if (!empty($email_to)) { $message = \Swift_Message::newInstance('Avis d’examen final et rappel/Notice of final exam and reminder')->setFrom(EMAIL_ALIAS_ACCESS_SERVICE_EXAMS . EMAIL_ORG_STAFF_DOMAIN)->setTo($email_to)->setBcc(EMAIL_ALIAS_ACCESS_SERVICE_VENTUS_COMMUNICATION . EMAIL_ORG_STAFF_DOMAIN); parent::sendEmail($message, $html); } }
/** * Send a verification email. Type refers to either reset password or 1st time account * @param string $data The username of the employee * @param string $hash The reset password hash * @return int */ private function sendEmailConfirmation($data, $hash) { $to = $data . EMAIL_ORG_STAFF_DOMAIN; $header = "Compte Ventus / Ventus account"; $html = parent::fetchEmailContent(FS_PROFILE . '/emails/new-account.php', array('hash' => $hash)); $message = \Swift_Message::newInstance($header)->setFrom(array('ventus' . EMAIL_ORG_STAFF_DOMAIN => 'ventus' . EMAIL_ORG_STAFF_DOMAIN))->setTo(array($to => $to))->setBcc(array(EMAIL_ALIAS_VENTUS_ADMIN . EMAIL_ORG_STAFF_DOMAIN => EMAIL_ALIAS_VENTUS_ADMIN . EMAIL_ORG_STAFF_DOMAIN)); return parent::sendEmail($message, $html); }
/** * Send CAM letter to all professors for this semester * @param mixed $student The student * @param array $attachments Email file attachments * @param string $email_cc The address to CC * @param string $email_bcc The address to BCC * @param array $course_details The course details */ public function sendCAMLetterForStudentCourse($student, array $attachments, $email_cc, $email_bcc, array $course_details) { $html = parent::fetchEmailContent(FS_STUDENT . '/emails/cam.php'); $course_code_section = $course_details['code'] . (!empty($course_details['section']) ? ' ' . $course_details['section'] : '') . ' ' . $course_details['teaching_method']; $exceptions = unserialize(CAM_LETTER_EMAIL_EXCEPTIONS); //If the course code is one of the restricted ones, redirect the email to the assigned person if (key_exists(substr($course_details['code'], 0, COURSE_CODE_PREFIX_ABBREVIATION_LENGTH), $exceptions)) { $email_to = array($exceptions[substr($course_details['code'], 0, COURSE_CODE_PREFIX_ABBREVIATION_LENGTH)] => $exceptions[substr($course_details['code'], 0, COURSE_CODE_PREFIX_ABBREVIATION_LENGTH)]); } else { $prof_emails = $course_details['professor_email']; if (!empty($prof_emails)) { $email_to = array(); foreach ($prof_emails as $prof_email) { $email_to[$prof_email] = $prof_email; } } else { $email_to = array(EMAIL_ALIAS_ACCESS_SERVICE_VENTUS_COMMUNICATION . EMAIL_ORG_STAFF_DOMAIN); } } $message = \Swift_Message::newInstance("CMA/CAM - Object/Re: {$course_code_section} - étudiant(e) / student: {$student}")->setFrom(array(EMAIL_ALIAS_ACCESS_SERVICE_COUNSELORS . EMAIL_ORG_STAFF_DOMAIN => EMAIL_ALIAS_ACCESS_SERVICE_COUNSELORS . EMAIL_ORG_STAFF_DOMAIN))->setTo($email_to)->setBcc($email_bcc); //If the course code is NOT one of the restricted ones, only then must the student be cc'd if (!key_exists(substr($course_details['code'], 0, COURSE_CODE_PREFIX_ABBREVIATION_LENGTH), $exceptions)) { $message->setCc($email_cc); } if (!empty($attachments)) { foreach ($attachments as $a) { $attachment = \Swift_Attachment::newInstance($a['content'], $a['name'] . '.pdf', 'application/pdf'); $message->attach($attachment); } } parent::sendEmail($message, $html); }
/** * Send an email to student who just booked appointment * @param array $data appointment information * @param boolean $booked whether the appointment was booked (true) or cancelled (false) * @return int The number of email messages sent */ public function appointmentEmail(array $data, $booked) { $student_data = $this->db->query("SELECT first_name, last_name, email\n FROM org_students\n WHERE student_id = :student", array('student' => $data['student']))->fetch(); $employee_data = $this->db->query("SELECT first_name as emp_first_name, last_name as emp_last_name, department as dep_id\n FROM ventus_users\n WHERE user_id = :emp_id", array('emp_id' => $data['emp_id']))->fetch(); $department_data = $this->db->query("SELECT service_name_en, service_name_fr\n FROM org_services\n WHERE service_id = :dep_id", array('dep_id' => $employee_data['dep_id']))->fetch(); $data = array_merge($data, $student_data, $employee_data, $department_data); if ($booked) { $html = parent::fetchEmailContent(FS_STUDENT . '/emails/appointment-booked.php', $data); $subject = "Booked appointment with {$data['service_name_en']}"; } else { $html = parent::fetchEmailContent(FS_STUDENT . '/emails/appointment-canceled.php', $data); $subject = "Cancelled appointment with {$data['service_name_en']}"; } $message = \Swift_Message::newInstance($subject)->setFrom(array(EMAIL_ALIAS_ACCESS_SERVICE_COUNSELORS . EMAIL_ORG_STAFF_DOMAIN => EMAIL_ALIAS_ACCESS_SERVICE_COUNSELORS . EMAIL_ORG_STAFF_DOMAIN))->setTo($data[email]); return parent::sendEmail($message, $html); }
/** * Sends an email to the user with a confirmation link to reset their password * @param string $email The user's email alias * @param string $key The password reset key */ private function sendPasswordResetConfirmationEmail($email, $key) { $html = parent::fetchEmailContent(FS_FACULTY . '/emails/password-reset.php', array('password_reset_key' => $key, 'email' => $email)); $message = \Swift_Message::newInstance('Réinitialisation du mot de passe du compte employé au Ventus / Ventus staff account password reset')->setFrom(EMAIL_ALIAS_NO_REPLY . EMAIL_ORG_STAFF_DOMAIN)->setTo($email . EMAIL_ORG_STAFF_DOMAIN); parent::sendEmail($message, $html); }
/** * Sends an email reminder to a faculty user * @param string $email The email address */ public function sendReminderEmail($email) { $html = parent::fetchEmailContent(FS_FACULTY . '/emails/missing-exams-reminder-generic.php'); $message = \Swift_Message::newInstance('Examens en attente / Pending exams')->setFrom(EMAIL_ALIAS_ACCESS_SERVICE_EXAMS . EMAIL_ORG_STAFF_DOMAIN)->setTo($email)->setCc(EMAIL_ALIAS_ACCESS_SERVICE_EXAMS . EMAIL_ORG_STAFF_DOMAIN)->setBcc(EMAIL_ALIAS_ACCESS_SERVICE_VENTUS_COMMUNICATION . EMAIL_ORG_STAFF_DOMAIN); parent::sendEmail($message, $html); }
/** * Emails students that have upcoming final exams on the final exam reminder date * @param array $accommodations The accommodations to filter by */ public final function finalExamReminder(array $accommodations) { $month_day_today = date('m-d'); if (in_array($month_day_today, unserialize(STUDENT_FINAL_EXAM_REMINDER_FALL_SEND_ON), true)) { $currentSessionEn = "Fall"; $currentSessionFr = "Autumne"; $deadlineEn = "November 15"; $deadlineFr = "15 Novembre"; } elseif (in_array($month_day_today, unserialize(STUDENT_FINAL_EXAM_REMINDER_WINTER_SEND_ON), true)) { $currentSessionEn = "Winter"; $currentSessionFr = "Hiver"; $deadlineEn = "March 15"; $deadlineFr = "15 Mars"; } else { return; //Today is not either one of the fall or winter reminder dates } $semester = \Ventus\Utilities\Functions::fetchSemester(); //Fetch all students who have unconfirmed final exams $sql = "SELECT DISTINCT(s.access_username) AS email\n FROM ventus_professor_exam_requests p\n JOIN org_courses c\n ON c.code = p.course_code\n AND c.section = p.course_section\n AND c.session = p.session\n JOIN org_course_classes cc\n ON c.course_id = cc.course_id\n JOIN org_student_course_classes sc\n ON cc.class_id = sc.class_id\n JOIN ventus_students s\n ON s.student_num = sc.student_id\n JOIN ventus_student_accommodations vsa\n ON s.student_num = vsa.student_num\n LEFT JOIN ventus_student_accommodations_exceptions vsae\n ON s.student_num = vsae.student_num\n AND vsae.course_code = p.course_code\n AND vsae.course_section = p.course_section\n AND vsae.session = p.session\n LEFT JOIN ventus_students_courses_no_accommodations vscna\n ON s.student_num = vscna.student_num\n AND vscna.course_code = p.course_code\n AND vscna.course_section = p.course_section\n AND vscna.session = p.session \n JOIN ventus_accommodations va \n ON vsa.accommodation_id = va.accommodation_id \n OR vsae.accommodation_id = va.accommodation_id \n JOIN ventus_accommodation_types vat \n ON va.type_id = vat.type_id\n WHERE s.access_profile_activated_by_student = 1\n AND p.is_confirmed = 1\n AND p.session = '{$semester['now_short']}'\n AND p.exam_type = 'final'\n AND p.deleted = 0\n AND vat.category IN (" . implode(',', array_fill(0, sizeof($accommodations), '?')) . ")\n AND (p.exam_request_id, s.student_num) NOT IN (\n SELECT professor_exam_request_id, student_num\n FROM ventus_exam_requests\n )\n AND (p.exam_request_id, s.student_num) NOT IN (\n SELECT professor_exam_request_id, student_num\n FROM ventus_exam_requests_denied\n )\n AND (CURDATE() BETWEEN vsa.effective_on AND vsa.expires_on \n OR CURDATE() BETWEEN vsae.effective_on AND vsae.expires_on)\n AND vscna.opt_out_id IS NULL;"; $eligible = $this->db->query($sql, $accommodations)->fetchAll(); if (!empty($eligible)) { $html = parent::fetchEmailContent(FS_STUDENT . '/emails/final-exam-reminder.php', array('currentSessionFr' => $currentSessionFr, 'currentSessionEn' => $currentSessionEn, 'deadlineFr' => $deadlineFr, 'deadlineEn' => $deadlineEn)); $message = \Swift_Message::newInstance('RAPPEL IMPORTANT REMINDER - Examens finaux au Service d\'accès / Final Exams at Access Service')->setFrom(EMAIL_ALIAS_ACCESS_SERVICE_EXAMS . EMAIL_ORG_STAFF_DOMAIN)->setBcc(EMAIL_ALIAS_ACCESS_SERVICE_VENTUS_COMMUNICATION . EMAIL_ORG_STAFF_DOMAIN); foreach ($eligible as $e) { if (!empty($e['email'])) { $message->setTo($e['email']); parent::sendEmail($message, $html); } } } }
/** * Notifies students to do the survey */ public final function notifyStudentsForSurveys() { $this->addStudentToList(); $sql = "SELECT DATE(sl.survey_sent_on) AS event_semester, s.email, sl.email_link, sl.list_id,\n CONCAT(s.first_name, ' ', s.last_name) AS student_name,\n v.service_name_en, v.service_name_fr\n FROM ventus_survey_student_list sl\n LEFT JOIN org_services v\n ON sl.survey_sent_on_service_id = v.service_id\n LEFT JOIN org_students s\n ON sl.student_number = s.student_id\n WHERE sl.cron_logged = 0\n GROUP BY s.student_id"; $surveys = $this->db->query($sql)->fetchAll(); if (!empty($surveys)) { $sql = "UPDATE ventus_survey_student_list\n SET cron_logged = 1, survey_sent_on = NOW()\n WHERE list_id = :list_id;"; $stmt = $this->db->prepare($sql); foreach ($surveys as $a) { $html = parent::fetchEmailContent(FS_SURVEY . '/emails/survey-invitation.php', $a); $message = \Swift_Message::newInstance()->setSubject("uOttawa, SASS - Sondage / Survey")->setFrom(EMAIL_ALIAS_NO_REPLY . EMAIL_ORG_STAFF_DOMAIN)->setTo($a['email']); parent::sendEmail($message, $html); //Update database so that email is not sent out repeatedly $stmt->bindValue('list_id', $a['list_id'], \PDO::PARAM_INT); $this->db->execute($stmt, $sql); } } }
/** * Inform those responsible for the course that a paper copy has been received * @param int $request_id The exam request ID */ public function notifyDocumentReceived($request_id) { if (!ctype_digit($request_id) && !is_int($request_id)) { throw new \InvalidArgumentException("Invalid exam request ID"); } $sql = "SELECT p.exam_duration, p.course_code, p.course_section, p.teach_method, p.exam_date\n FROM ventus_professor_exam_requests p\n JOIN ventus_exam_requests r\n ON p.exam_request_id = r.professor_exam_request_id\n WHERE r.request_id = :request_id;"; $request_details = $this->db->query($sql, array('request_id' => $request_id))->fetch(); if (!empty($request_details)) { $to = array(); // Check against these factors for faculty user permissions $facultyModel = new \Ventus\Faculty\Faculty(); $users = $facultyModel->fetchUsersWithPermission($request_details['course_code'] . $request_details['course_section'], true); foreach ($users as $u) { $to[] = $u['email']; } if (!empty($to)) { $html = parent::fetchEmailContent(FS_EXAMS . '/emails/documents-received.php', $request_details); $message = \Swift_Message::newInstance("Copie papier reçu / Hard copy received ({$request_details['course_code']} {$request_details['course_code']} {$request_details['course_section']} - " . date('Y-m-d', strtotime($request_details['exam_date'])) . ')')->setFrom(EMAIL_ALIAS_ACCESS_SERVICE_EXAMS . EMAIL_ORG_STAFF_DOMAIN)->setTo($to)->setBcc(EMAIL_ALIAS_ACCESS_SERVICE_VENTUS_COMMUNICATION . EMAIL_ORG_STAFF_DOMAIN); parent::sendEmail($message, $html); } } }
/** * Notifies students for missed appointments */ public final function notifyStudentsForMissedAppointments() { $sql = "SELECT DATE(c.appointment_start) AS start_date, s.email, c.event_id,\n TIME(c.appointment_start) AS start_time, TIME(c.appointment_end) AS end_time,\n CONCAT(s.first_name, ' ', s.last_name) AS student_name,\n CONCAT(u.first_name, ' ', u.last_name) AS emp_name, u.department,\n v.counselling_email_permission, v.student_num, v.hash_link\n FROM ventus_calendar c\n JOIN org_students s\n ON c.student = s.student_id\n JOIN ventus_users u\n ON c.emp_id = u.user_id\n JOIN ventus_calendar_appointment_types t\n ON c.appointment_type_id = t.appointment_type_id\n JOIN ventus_students v\n ON s.student_id = v.student_num \n WHERE c.student_cron_logged = 0\n AND s.email <> ''\n AND t.email_enabled != 0\n AND DATE(c.appointment_start) = CURRENT_DATE() - INTERVAL 1 DAY\n AND (c.appointment_status = 'no_show')\n AND (c.appointment_type_id = 53);"; $updateSql = "UPDATE ventus_calendar\n SET student_cron_logged = 1, updated_on = NOW()\n WHERE event_id = :event_id;"; $preparedUpdateSql = $this->db->prepare($updateSql); $missedAppointments = $this->db->query($sql)->fetchAll(); foreach ($missedAppointments as $a) { switch ($a['department']) { case SERVICE_ID_ACCESS: $html = parent::fetchEmailContent(FS_CALENDAR . '/emails/appointment-missed-access-service.php', $a); break; case SERVICE_ID_COUNSELLING: $html = parent::fetchEmailContent(FS_CALENDAR . '/emails/appointment-missed-counselling.php', $a); break; } if (isset($html)) { $message = \Swift_Message::newInstance('Missed Appointment / Missed Appointment')->setFrom(EMAIL_ALIAS_NO_REPLY . EMAIL_ORG_STAFF_DOMAIN)->setTo($a['email']); //If it's an appointment at AS, accessia needs to be bcc'd if ($a['department'] === SERVICE_ID_ACCESS) { $message->setBcc(EMAIL_ALIAS_ACCESS_SERVICE_VENTUS_COMMUNICATION . EMAIL_ORG_STAFF_DOMAIN); } parent::sendEmail($message, $html); //Update database so that reminder is not sent out repeatedly $preparedUpdateSql->bindValue('event_id', $a['event_id'], \PDO::PARAM_INT); $this->db->execute($preparedUpdateSql); unset($html); } } }
/** * Sends out an email to the student to notify them of opted in * @param string $student_email The student's email address * @param array $opt_in_details The opt in details */ private function sendOptedInEmail($student_email, array $opt_in_details) { // Send an email to the student and AS informing them of this change $html = parent::fetchEmailContent(FS_SPECIALIST . '/emails/opt-in.php', $opt_in_details); $message = \Swift_Message::newInstance("Vous avez rétabli vos mesures adaptées ({$opt_in_details['course']}) / You have opted back in for Adapted measures ({$opt_in_details['course']})")->setFrom(EMAIL_ALIAS_ACCESS_SERVICE_COUNSELORS . EMAIL_ORG_STAFF_DOMAIN)->setTo($student_email)->setBcc(EMAIL_ALIAS_ACCESS_SERVICE_VENTUS_COMMUNICATION . EMAIL_ORG_STAFF_DOMAIN); parent::sendEmail($message, $html); }
/** * Notifies the Exams team of an exam conflict * @param int $student_num The student number * @param \DateTime $start_time The start time of the exam */ private function examConflictNotification($student_num, \DateTime $start_time) { $html = parent::fetchEmailContent(FS_STUDENT . '/emails/exam-conflict-warning.php', array('student_num' => $student_num, 'start_time' => $start_time->format(DATETIME_MYSQL))); $message = \Swift_Message::newInstance('Ventus student exam conflict')->setFrom(array('ventus' . EMAIL_ORG_STAFF_DOMAIN => 'ventus' . EMAIL_ORG_STAFF_DOMAIN))->setTo(array(EMAIL_ALIAS_ACCESS_SERVICE_EXAMS . EMAIL_ORG_STAFF_DOMAIN => EMAIL_ALIAS_ACCESS_SERVICE_EXAMS . EMAIL_ORG_STAFF_DOMAIN))->setBcc(EMAIL_ALIAS_ACCESS_SERVICE_VENTUS_COMMUNICATION . EMAIL_ORG_STAFF_DOMAIN); parent::sendEmail($message, $html); }
/** * For an invalid request, send an email saying that it was invalid * @param array $data The NOE data */ public function sendInvalidInformationEmail(array $data) { $html = parent::fetchEmailContent(FS_PROFESSOR . '/emails/invalid-noe.php', $data); $message = \Swift_Message::newInstance('Demande de mesures adaptées pour un examen / Request for Adapted Exam')->setFrom(array(EMAIL_ALIAS_ACCESS_SERVICE_EXAMS . EMAIL_ORG_STAFF_DOMAIN => EMAIL_ALIAS_ACCESS_SERVICE_EXAMS . EMAIL_ORG_STAFF_DOMAIN))->setTo(array($data['requestor_email'] => $data['requestor_email']))->setBcc(EMAIL_ALIAS_ACCESS_SERVICE_VENTUS_COMMUNICATION . EMAIL_ORG_STAFF_DOMAIN); parent::sendEmail($message, $html); }
/** * Sends an email to a student notifying them of a change to their exam * @param string $name The student name * @param string $email The student email * @param string $course The course * @param string $start The start date/time of the exam * @param string $end The end date/time of the exam * @param string $duration The exam duration */ private function sendExamChangeNotificationToStudent($name, $email, $course, $start, $end, $duration) { $exam_date = \DateTime::createFromFormat(DATETIME_MYSQL, $start)->format('Y-m-d'); $html = parent::fetchEmailContent(FS_EXAMS . '/emails/exam-time-change.php', array('student_name' => $name, 'course' => $course, 'official_evaluation_starttime' => $start, 'official_evaluation_endtime' => $end, 'exam_duration' => $duration)); $message = \Swift_Message::newInstance("Changement à l'heure d'un examen / Changes to examination time ({$course} - {$exam_date})")->setFrom(EMAIL_ALIAS_ACCESS_SERVICE_EXAMS . EMAIL_ORG_STAFF_DOMAIN)->setTo($email)->setBcc(EMAIL_ALIAS_ACCESS_SERVICE_VENTUS_COMMUNICATION . EMAIL_ORG_STAFF_DOMAIN); parent::sendEmail($message, $html); }