<?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 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."); } }
/** * Retrieve pending appointments for current terms for given tutorId. * Pending appointments start time will be at least 30 minutes away from it's starting time * @param $tutorId * @return array * @throws Exception */ public static function retrievePendingForCurrentTerms($tutorId) { $query = "SELECT `" . self::DB_TABLE . "`.`" . self::DB_COLUMN_ID . "` , `" . self::DB_TABLE . "`.`" . self::DB_COLUMN_START_TIME . "` , `" . self::DB_TABLE . "`.`" . self::DB_COLUMN_END_TIME . "`,\n\t\t\t`" . self::DB_TABLE . "`.`" . self::DB_COLUMN_TERM_ID . "`,\n\t\t\t`" . self::DB_TABLE . "`.`" . self::DB_COLUMN_COURSE_ID . "`, `" . self::DB_TABLE . "`.`" . self::DB_COLUMN_TUTOR_USER_ID . "`, `" . self::DB_TABLE . "`.`" . self::DB_COLUMN_TUTOR_USER_ID . "`,\n\t\t\t`" . self::DB_TABLE . "`.`" . self::DB_COLUMN_LABEL_MESSAGE . "`\n\t\t\tFROM `" . App::getDbName() . "`.`" . self::DB_TABLE . "`\n\t\t\tLEFT JOIN `" . App::getDbName() . "`.`" . TermFetcher::DB_TABLE . "`\n\t\t\tON `" . App::getDbName() . "`.`" . self::DB_TABLE . "`.`" . self::DB_COLUMN_TERM_ID . "`=`" . TermFetcher::DB_TABLE . "`.`" . TermFetcher::DB_COLUMN_ID . "`\n\t\t\tWHERE `" . self::DB_TABLE . "`.`" . self::DB_COLUMN_TUTOR_USER_ID . "` = :tutor_id\n\t\t\tAND `" . self::DB_TABLE . "`.`" . self::DB_COLUMN_LABEL_MESSAGE . "` ='" . Appointment::LABEL_MESSAGE_PENDING . "'\n\t\t\tAND :now BETWEEN `" . TermFetcher::DB_COLUMN_START_DATE . "` AND `" . TermFetcher::DB_COLUMN_END_DATE . "`\n\t\t\tORDER BY `" . self::DB_TABLE . "`.`" . self::DB_COLUMN_START_TIME . "` DESC"; 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_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."); } }
*/ require __DIR__ . "/../public_html/app/config/app.php"; $appInfoFile = ROOT_PATH . "config/dropbox.app"; # Include the Dropbox SDK libraries require_once ROOT_PATH . "plugins/dropbox-sdk/lib/Dropbox/autoload.php"; include_once ROOT_PATH . '/plugins/mysqldump-php-1.4.1/src/Ifsnop/Mysqldump/Mysqldump.php'; use Dropbox as dbx; try { date_default_timezone_set(App::getTimeZone()); // run script only during working hours if (!App::isWorkingDateTimeOn()) { exit; } $filePath = ROOT_PATH . 'storage/backups/'; $fileName = 'sass app db ' . date('m-d-Y Hi') . '.sql'; $zippedFileName = $fileName . '.gz'; $zippedFullFileName = $filePath . $zippedFileName; $curWorkingDateYear = App::getCurWorkingDate()->format('Y'); $dumpSettings = ['compress' => Ifsnop\Mysqldump\Mysqldump::GZIP, 'no-data' => false, 'add-drop-table' => true, 'single-transaction' => false, 'lock-tables' => true, 'add-locks' => true, 'extended-insert' => true, 'disable-foreign-keys-check' => true, 'skip-triggers' => false, 'add-drop-trigger' => true, 'databases' => false, 'add-drop-database' => false, 'hex-blob' => true]; $dump = new Ifsnop\Mysqldump\Mysqldump(App::getDbName(), App::getDbUsername(), App::getDbPassword(), App::getDbHost(), 'mysql', $dumpSettings); $dump->start($filePath . $fileName); $accessToken = DropboxFetcher::retrieveAccessToken(DropboxCon::SERVICE_APP_DATABASE_BACKUP)[DropboxFetcher::DB_COLUMN_ACCESS_TOKEN]; $dbxClient = new dbx\Client($accessToken, App::getVersion()); $adminAccountInfo = $dbxClient->getAccountInfo(); $f = fopen($zippedFullFileName, "rb"); $result = $dbxClient->uploadFile("/storage/backups/{$curWorkingDateYear}/{$zippedFileName}", dbx\WriteMode::add(), $f); fclose($f); } catch (Exception $e) { App::storeError($e->getMessage()); exit; }