<?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;
}
