$general->loggedOutProtect();
$user->allowDoctorKatsas();
$section = "appointments-terms";
$pageTitle = "All Tutors";
if (empty($_GET['term-id'])) {
$appointments = AppointmentFetcher::retrieveForCurrentTerms();
$allReports = ReportFetcher::retrieveAllOfCurrTerms();
$students = AppointmentHasStudentFetcher::retrieveAllOnCurTerm();
$instructors = AppointmentHasStudentFetcher::retrieveInstructorsOnCurTerm();
$termTitle = 'current terms';
} else {
$termId = $_GET['term-id'];
$appointments = AppointmentFetcher::retrieveForTerm($termId);
$allReports = ReportFetcher::findWithTermId($termId);
$students = AppointmentHasStudentFetcher::retrieveForTerm($termId);
$instructors = AppointmentHasStudentFetcher::retrieveInstructorsForTerm($termId);
$termTitle = $appointments[0][TermFetcher::DB_TABLE . "_" . TermFetcher::DB_COLUMN_NAME];
}
$terms = TermFetcher::retrieveAll();
function getStudentsIds($students, $appointmentId)
{
$studentsIds = "";
foreach ($students as $student) {
if (strcmp($student[AppointmentHasStudentFetcher::DB_COLUMN_APPOINTMENT_ID], $appointmentId) === 0) {
$studentsIds = $student[StudentFetcher::DB_COLUMN_STUDENT_ID] . ", " . $studentsIds;
}
}
return rtrim($studentsIds, ", ");
}
function getInstructorNames($instructors, $appointmentId)
{
$updateDone = Report::updateAllFields($reportUpdate[ReportFetcher::DB_COLUMN_ID], $projectTopicOtherNew, $otherTextArea, $studentsConcernsTextArea, $relevantFeedbackGuidelines, $studentBroughtAlongNew, $studentBroughtAlongOld, $conclusionAdditionalComments, $primaryFocusOfConferenceNew, $primaryFocusOfConferenceOld, $conclusionWrapUpNew, $conclusionWrapUpOld);
// user is tutor requesting fill report
if ($user->isTutor()) {
ReportFetcher::updateLabel($formReportId, Report::LABEL_MESSAGE_PENDING_VALIDATION, Report::LABEL_COLOR_WARNING);
} else {
// user is secretary confirming report
ReportFetcher::updateLabel($formReportId, Report::LABEL_MESSAGE_COMPLETE, Report::LABEL_COLOR_SUCCESS);
}
}
if (!$updateDone) {
throw new Exception("No new data inserted.");
}
}
if (isUrlRqstngManualReportEnable()) {
if ($studentsAppointmentData[0][AppointmentHasStudentFetcher::DB_COLUMN_REPORT_ID] === null && $nowDateTime > $startDateTime) {
$students = AppointmentHasStudentFetcher::retrieveStudentsWithAppointment($appointmentId);
$appointment = Appointment::getSingle($appointmentId);
foreach ($students as $student) {
$reportId = ReportFetcher::insert($student[AppointmentHasStudentFetcher::DB_COLUMN_STUDENT_ID], $student[AppointmentHasStudentFetcher::DB_COLUMN_ID], $student[AppointmentHasStudentFetcher::DB_COLUMN_INSTRUCTOR_ID]);
}
AppointmentFetcher::updateLabel($appointmentId, Appointment::LABEL_MESSAGE_COMPLETE, Appointment::LABEL_COLOR_SUCCESS);
if (!$user->isTutor()) {
Mailer::sendTutorNewReportsCronOnly($appointment);
}
}
} else {
if (isUrlRqstngAppointmentCancelByStudent()) {
AppointmentFetcher::updateLabel($appointmentId, Appointment::LABEL_MESSAGE_STUDENT_CANCELED, Appointment::LABEL_COLOR_CANCELED);
} else {
if (isBtnUpdateAppointmentPrsd() && ($user->isSecretary() && strcmp($studentsAppointmentData[0][AppointmentFetcher::DB_COLUMN_LABEL_MESSAGE], Appointment::LABEL_MESSAGE_COMPLETE) !== 0 || $user->isAdmin())) {
$updateDone = Appointment::updateStudents($appointmentId, $studentsAppointmentData, $_POST['studentsIds']);
/**
* @param $termId
* @return array
* @throws Exception
* @throws PHPExcel_Exception
*/
private static function exportAppointments($termId)
{
Term::validateId($termId);
require_once ROOT_PATH . 'plugins/PHPExcel_1.8.0_doc/Classes/PHPExcel.php';
date_default_timezone_set('Europe/Athens');
$allAppointments = AppointmentFetcher::retrieveForTerm($termId);
$appointmentsHaveStudents = AppointmentHasStudentFetcher::retrieveForTerm($termId);
$primaryFocusOfConferences = PrimaryFocusOfConferenceFetcher::retrieveForTerm($termId);
$termData = TermFetcher::retrieveSingle($termId);
$termStart = new DateTime($termData[TermFetcher::DB_COLUMN_START_DATE]);
$termEnd = new DateTime($termData[TermFetcher::DB_COLUMN_END_DATE]);
$termName = $termData[TermFetcher::DB_COLUMN_NAME];
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator(App::getName())->setLastModifiedBy(App::getName())->setTitle(self::TITLE_VISIT_LOG . " - " . $termName)->setSubject("Appointments")->setDescription("List of appointments for {$termName}.")->setKeywords("office sass appointments php")->setCategory("Appointments");
$sheetIndex = 0;
for ($workingDateTime = $termStart; $workingDateTime <= $termEnd; $workingDateTime->modify('+1 week')) {
$curWeek = $workingDateTime->format('W');
$appointmentsWeekly = self::getAppointments($allAppointments, $curWeek);
$startWeekDate = self::getWorkingDates($workingDateTime->format('Y'), $workingDateTime->format('W'));
$endWeekDate = self::getWorkingDates($workingDateTime->format('Y'), $workingDateTime->format('W'), false);
$periodTime = date("d M", strtotime($startWeekDate)) . "-" . date("d M, o", strtotime('-1 day', strtotime($endWeekDate)));
$objPHPExcel->createSheet($sheetIndex);
$objPHPExcel->setActiveSheetIndex($sheetIndex)->setCellValue('A1', 'Day')->setCellValue('B1', 'Date')->setCellValue('C1', 'Month')->setCellValue('D1', 'Year')->setCellValue('E1', 'Time')->setCellValue('F1', 'LF Lname')->setCellValue('G1', 'Stud Lname')->setCellValue('H1', 'Stud Fname')->setCellValue('I1', 'Stud ID')->setCellValue('J1', 'Major')->setCellValue('K1', 'Mobile')->setCellValue('L1', 'e-mail')->setCellValue('M1', 'Course Code')->setCellValue('N1', 'Course Instructor')->setCellValue('O1', '# of Students')->setCellValue('P1', 'Outcome')->setCellValue('Q1', 'Actual Duration')->setCellValue('R1', 'Purpose');
$sheetIndex++;
$objPHPExcel->getActiveSheet()->getStyle('A1:R1')->applyFromArray(self::$styleVisitLogHeader);
$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(24);
$objPHPExcel->getActiveSheet()->setTitle($periodTime);
$row = 2;
foreach ($appointmentsWeekly as $appointment) {
$appointmentStartDateTime = new DateTime($appointment[AppointmentFetcher::DB_COLUMN_START_TIME]);
$appointmentEndDateTime = new DateTime($appointment[AppointmentFetcher::DB_COLUMN_END_TIME]);
$appointmentId = $appointment[AppointmentFetcher::DB_COLUMN_ID];
$students = self::getStudentsForAppointment($appointmentsHaveStudents, $appointmentId);
$numOfStudents = sizeof($students);
// TODO: REMOVE autosize from loop
$col = 0;
// day
$day = $appointmentStartDateTime->format('l');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $day);
$col++;
// date
$date = $appointmentStartDateTime->format('d');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $date);
$col++;
// month
$month = $appointmentStartDateTime->format('M');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $month);
$col++;
// year
$year = $appointmentStartDateTime->format('Y');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $year);
$col++;
// time
$time = $appointmentStartDateTime->format('H:i');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $time);
$col++;
// tutor last name
$tutorLastName = $appointment[UserFetcher::DB_COLUMN_LAST_NAME];
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $tutorLastName);
// TODO: add student that first requested the appointment.
$col++;
// student last name
$studentLastName = $students[0][StudentFetcher::DB_COLUMN_LAST_NAME];
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $studentLastName);
$col++;
// student first name
$studentFirstName = $students[0][StudentFetcher::DB_COLUMN_FIRST_NAME];
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $studentFirstName);
$col++;
// student ID
$studentId = $students[0][StudentFetcher::DB_COLUMN_STUDENT_ID];
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $studentId);
$col++;
// student major
$majorCode = $students[0][MajorFetcher::DB_COLUMN_CODE];
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $majorCode);
$col++;
// student mobile
$mobile = $students[0][StudentFetcher::DB_COLUMN_MOBILE];
$objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow($col, $row, $mobile);
$col++;
// student email
$email = $students[0][StudentFetcher::DB_COLUMN_EMAIL];
$objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow($col, $row, $email);
$col++;
// course code
$courseCode = $appointment[CourseFetcher::DB_COLUMN_CODE];
$objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow($col, $row, $courseCode);
$col++;
// instructor for first student
$instructorLastName = $students[0][InstructorFetcher::DB_COLUMN_LAST_NAME];
$objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow($col, $row, $instructorLastName);
$col++;
// instructor for first student
$objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow($col, $row, $numOfStudents);
$col++;
// appointment outcome
$appointmentOutcome = $appointment[AppointmentFetcher::DB_COLUMN_LABEL_MESSAGE];
$objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow($col, $row, $appointmentOutcome);
$col++;
// appointment duration
$appointmentDuration = ($appointmentEndDateTime->getTimestamp() - $appointmentStartDateTime->getTimestamp()) / 60;
$objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow($col, $row, $appointmentDuration);
$col++;
// purpose
$primaryFocusOfConferencesString = self::getFocusOfConferencesForAppointment($primaryFocusOfConferences, $appointmentId);
if (!empty($primaryFocusOfConferencesString)) {
$primaryFocusOfConferencesString = Report::convertFocusOfConferenceToString($primaryFocusOfConferencesString[0]);
$objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow($col, $row, $primaryFocusOfConferencesString);
//$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col)->setAutoSize(true);
}
switch ($appointment[AppointmentFetcher::DB_COLUMN_LABEL_COLOR]) {
case Appointment::LABEL_COLOR_PENDING:
$color = '888888';
break;
case Appointment::LABEL_COLOR_CANCELED:
$color = 'e5412d';
break;
case Appointment::LABEL_COLOR_SUCCESS:
$color = '3fa67a';
break;
case Appointment::LABEL_COLOR_WARNING:
$color = 'f0ad4e';
break;
default:
$color = '444';
break;
}
$styleRow = array('font' => array('bold' => false, 'name' => 'Times New Roman', 'size' => 12), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT), 'borders' => array('bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN), 'right' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR, 'rotation' => 90, 'startcolor' => array('argb' => $color), 'endcolor' => array('argb' => $color)));
$objPHPExcel->getActiveSheet()->getStyle("A{$row}:R{$row}")->applyFromArray($styleRow);
$row++;
}
// end foreach
// enable all filters
$objPHPExcel->getActiveSheet()->setAutoFilter($objPHPExcel->getActiveSheet()->calculateWorksheetDimension());
// source: http://stackoverflow.com/a/5578240 - autosize all columns
$lastColumn = $objPHPExcel->getActiveSheet()->getHighestColumn();
$lastColumn++;
for ($column = 'A'; $column != $lastColumn; $column++) {
$objPHPExcel->getActiveSheet()->getColumnDimension($column)->setAutoSize(true);
}
// end for
}
// end foreach
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
return array($termName, $objPHPExcel);
}
$section = "appointments";
if (isset($_SESSION['success'])) {
$successMessage[] = $_SESSION['success'];
unset($_SESSION['success']);
}
if ($user->isTutor()) {
$pageTitle = "" . $user->getFirstName() . " " . $user->getLastName();
$appointments = AppointmentFetcher::retrieveAllOfCurrTermsByTutor($user->getId());
$allReports = ReportFetcher::retrieveAllOfCurrTermsByTutor($user->getId());
// TODO: add sepearate retrieval function for tutor. currently retrieves all students \/
$students = AppointmentHasStudentFetcher::retrieveAllOnCurTerm();
} else {
$pageTitle = "All Tutors";
$appointments = AppointmentFetcher::retrieveAllOfCurrTerms();
$allReports = ReportFetcher::retrieveAllOfCurrTerms();
$students = AppointmentHasStudentFetcher::retrieveAllOnCurTerm();
}
function getStudentsIds($students, $appointmentId)
{
$studentsIds = "";
foreach ($students as $student) {
if (strcmp($student[AppointmentHasStudentFetcher::DB_COLUMN_APPOINTMENT_ID], $appointmentId) === 0) {
$studentsIds = $student[StudentFetcher::DB_COLUMN_STUDENT_ID] . ", " . $studentsIds;
}
}
return rtrim($studentsIds, ", ");
}
function requestRequiresPendingAppointmentsAndReports()
{
if (!isset($_GET['appointments']) || !isset($_GET['reports'])) {
return;
<?php
/**
* Created by PhpStorm.
* User: rdok
* Date: 9/18/2014
* Time: 2:35 PM
*/
try {
require __DIR__ . "/../public_html/app/config/app.php";
date_default_timezone_set('Europe/Athens');
// run script only during working hours every two hours
if (!App::isWorkingDateTimeOn()) {
exit;
}
$appointments = AppointmentFetcher::retrieveCmpltWithoutRptsOnCurTerms();
foreach ($appointments as $appointment) {
$students = AppointmentHasStudentFetcher::retrieveStudentsWithAppointment($appointment[AppointmentFetcher::DB_COLUMN_ID]);
foreach ($students as $student) {
$reportId = ReportFetcher::insert($student[AppointmentHasStudentFetcher::DB_COLUMN_STUDENT_ID], $student[AppointmentHasStudentFetcher::DB_COLUMN_ID], $student[AppointmentHasStudentFetcher::DB_COLUMN_INSTRUCTOR_ID]);
}
AppointmentFetcher::updateLabel($appointment[AppointmentFetcher::DB_COLUMN_ID], Appointment::LABEL_MESSAGE_COMPLETE, Appointment::LABEL_COLOR_SUCCESS);
// Mailer::sendTutorNewReportsCronOnly($appointment);
}
} catch (Exception $e) {
App::storeError($e);
exit;
}
public static function insert($dateStart, $dateEnd, $courseId, $studentsIds, $tutorId, $instructorsIds, $termId)
{
date_default_timezone_set('Europe/Athens');
$dateStart = $dateStart->format(Dates::DATE_FORMAT_IN);
$dateEnd = $dateEnd->format(Dates::DATE_FORMAT_IN);
try {
$queryInsertUser = "******" . App::getDbName() . "`.`" . self::DB_TABLE . "` (`" . self::DB_COLUMN_START_TIME . "`,\t\t\t`" . self::DB_COLUMN_END_TIME . "`, `" . self::DB_COLUMN_COURSE_ID . "`, `" . self::DB_COLUMN_TUTOR_USER_ID . "`, `" . self::DB_COLUMN_TERM_ID . "`)\n\t\t\t\tVALUES(\n\t\t\t\t\t:start_time,\n\t\t\t\t\t:end_time,\n\t\t\t\t\t:course_id,\n\t\t\t\t\t:tutor_user_id,\n\t\t\t\t\t:term_id\n\t\t\t\t)";
$dbConnection = DatabaseManager::getConnection();
$dbConnection->beginTransaction();
$queryInsertUser = $dbConnection->prepare($queryInsertUser);
$queryInsertUser->bindParam(':start_time', $dateStart, PDO::PARAM_STR);
$queryInsertUser->bindParam(':end_time', $dateEnd, PDO::PARAM_STR);
$queryInsertUser->bindParam(':course_id', $courseId, PDO::PARAM_STR);
$queryInsertUser->bindParam(':tutor_user_id', $tutorId, PDO::PARAM_STR);
$queryInsertUser->bindParam(':term_id', $termId, PDO::PARAM_STR);
$queryInsertUser->execute();
// last inserted if of THIS connection
$appointmentId = $dbConnection->lastInsertId();
for ($i = 0; $i < sizeof($studentsIds); $i++) {
AppointmentHasStudentFetcher::insert($appointmentId, $studentsIds[$i], $instructorsIds[$i]);
}
$dbConnection->commit();
return $appointmentId;
} catch (Exception $e) {
if (isset($dbConnection)) {
$dbConnection->rollback();
}
Mailer::sendDevelopers($e->getMessage(), __FILE__);
throw new Exception("Could not insert data into database.");
}
}
public static function insert($studentId, $appointmentId, $instructorId)
{
try {
$query = "INSERT INTO `" . App::getDbName() . "`.`" . self::DB_TABLE . "`\n\t\t\t(`" . self::DB_COLUMN_STUDENT_ID . "`,\t`" . self::DB_COLUMN_INSTRUCTOR_ID . "`)\n\t\t\t\tVALUES\n\t\t\t\t(\n\t\t\t\t\t:student_id,\n\t\t\t\t\t:instructor_id\n\t\t\t\t)";
$dbConnection = DatabaseManager::getConnection();
$dbConnection->beginTransaction();
$query = $dbConnection->prepare($query);
$query->bindParam(':student_id', $studentId, PDO::PARAM_INT);
$query->bindParam(':instructor_id', $instructorId, PDO::PARAM_INT);
$query->execute();
// last inserted if of THIS connection
$reportId = $dbConnection->lastInsertId();
StudentBroughtAlongFetcher::insert($reportId);
PrimaryFocusOfConferenceFetcher::insert($reportId);
ConclusionWrapUpFetcher::insert($reportId);
AppointmentHasStudentFetcher::update($appointmentId, $reportId);
$dbConnection->commit();
return $reportId;
} catch (Exception $e) {
if (isset($dbConnection)) {
$dbConnection->rollback();
}
Mailer::sendDevelopers($e->getMessage(), __FILE__);
throw new Exception("Could not insert data into database.");
}
}
public static function getAppointmentsForCourseAndTutor($tutorId, $courseId, $termId)
{
Tutor::validateId($tutorId);
Term::validateId($termId);
$appointmentHours = AppointmentFetcher::getAppointmentsForTutorAndCourse($tutorId, $courseId, $termId);
$appointmentHoursJSON = [];
foreach ($appointmentHours as $appointmentHour) {
$appointmentTitle = $appointmentHour[CourseFetcher::DB_COLUMN_CODE] . " - " . $appointmentHour[UserFetcher::DB_COLUMN_FIRST_NAME] . " " . $appointmentHour[UserFetcher::DB_COLUMN_LAST_NAME];
$students = AppointmentHasStudentFetcher::retrieveStudentsWithAppointment($appointmentHour[AppointmentFetcher::DB_COLUMN_ID]);
$appointmentTitle .= " - ";
foreach ($students as $student) {
$appointmentTitle .= $student[StudentFetcher::DB_TABLE . "_" . StudentFetcher::DB_COLUMN_FIRST_NAME] . " " . $student[StudentFetcher::DB_TABLE . "_" . StudentFetcher::DB_COLUMN_LAST_NAME] . ", ";
}
$appointmentTitle = rtrim($appointmentTitle, ", ");
$startDate = new DateTime($appointmentHour[AppointmentFetcher::DB_COLUMN_START_TIME]);
$endDate = new DateTime($appointmentHour[AppointmentFetcher::DB_COLUMN_END_TIME]);
$appointmentUrl = App::getDomainName() . "/appointments/" . $appointmentHour[UserFetcher::DB_COLUMN_ID];
switch ($appointmentHour[AppointmentFetcher::DB_COLUMN_LABEL_COLOR]) {
case Appointment::LABEL_COLOR_PENDING:
$color = '#888888';
break;
case Appointment::LABEL_COLOR_CANCELED:
$color = '#e5412d';
break;
case Appointment::LABEL_COLOR_SUCCESS:
$color = '#3fa67a';
break;
case Appointment::LABEL_COLOR_WARNING:
$color = '#f0ad4e';
break;
default:
$color = '#444';
break;
}
$appointmentHoursJSON[] = ['title' => $appointmentTitle, 'start' => $startDate->format('Y-m-d H:i:s'), 'end' => $endDate->format('Y-m-d H:i:s'), 'allDay' => false, 'url' => $appointmentUrl, 'color' => $color];
}
return json_encode($appointmentHoursJSON);
}
