/**
* @return string
*/
public function getQuery()
{
$colNames = QueryPartsBuilder::buildCSVString($this->colNames);
$values = QueryPartsBuilder::buildCSVString($this->values);
$query = "INSERT INTO {$this->tableName} ({$colNames}) VALUES ({$values})";
return $query;
}
/**
* @param int $seasonID
* @param int $subjectID
* @param int $groupID
* @param int $professorID
* @param bool $isImprovement
*/
function __construct($seasonID, $subjectID, $groupID, $professorID, $isImprovement)
{
$this->db = new DefaultDatabase();
$seasonID = intval($seasonID);
$subjectID = intval($subjectID);
$groupID = intval($groupID);
$professorID = intval($professorID);
$isImprovement = (bool) $isImprovement;
$colNames = array(QBuilder::buildColName(Student::TABLE_NAME, Student::PROP_ID . ' AS ' . ExamResult::PROP_STUDENT_ID), QBuilder::buildColName(Student::TABLE_NAME, Student::PROP_FIRST_NAME), QBuilder::buildColName(Student::TABLE_NAME, Student::PROP_LAST_NAME), QBuilder::buildColName(Result::TABLE_NAME, Result::PROP_DATE), QBuilder::buildColName(Result::TABLE_NAME, Result::PROP_MARK), QBuilder::buildColName(Group::TABLE_NAME, Group::PROP_NAME), QBuilder::buildColName(Department::TABLE_NAME, Department::PROP_NAME), QBuilder::buildColName(AcademicYear::TABLE_NAME, AcademicYear::PROP_YEAR), QBuilder::buildColName(Subject::TABLE_NAME, Subject::PROP_NAME), QBuilder::buildColName(Exam::TABLE_NAME, Exam::PROP_ID . ' AS ' . ExamResult::PROP_EXAM_ID), QBuilder::buildColName(Exam::TABLE_NAME, Exam::PROP_HEAD_ID), QBuilder::buildColName(Exam::TABLE_NAME, Exam::PROP_MEMBER1_ID), QBuilder::buildColName(Exam::TABLE_NAME, Exam::PROP_MEMBER2_ID));
$this->projection = QBuilder::buildCSVString($colNames);
$tableNames = array(AcademicYear::TABLE_NAME);
$this->tableNames = QBuilder::buildCSVString($tableNames);
$this->joinTableWith(Group::TABLE_NAME, QBuilder::buildColEq(AcademicYear::TABLE_NAME, AcademicYear::PROP_ID, Group::TABLE_NAME, Group::PROP_START_AY_ID));
$this->joinTableWith(Student::TABLE_NAME, QBuilder::buildColEq(Group::TABLE_NAME, Group::PROP_ID, Student::TABLE_NAME, Student::PROP_GROUP_ID));
$this->joinTableWith(Department::TABLE_NAME, QBuilder::buildColEq(Department::TABLE_NAME, Department::PROP_ID, Group::TABLE_NAME, Group::PROP_DEPARTMENT_ID));
$this->joinTableWith(Exam::TABLE_NAME, QBuilder::buildColEq(Department::TABLE_NAME, Department::PROP_ID, Exam::TABLE_NAME, Exam::PROP_DEPARTMENT_ID));
$this->joinTableWith(Season::TABLE_NAME, QBuilder::buildColEq(Season::TABLE_NAME, Season::PROP_ID, Exam::TABLE_NAME, Exam::PROP_SEASON_ID));
$this->joinTableWith(Subject::TABLE_NAME, QBuilder::buildColEq(Subject::TABLE_NAME, Subject::PROP_ID, Exam::TABLE_NAME, Exam::PROP_SUBJECT_ID));
$attendanceSubjectFilter = QBuilder::buildColEq(Subject::TABLE_NAME, Subject::PROP_ID, Attendance::TABLE_NAME, Attendance::PROP_SUBJECT_ID);
$attendanceStudentFilter = QBuilder::buildColEq(Student::TABLE_NAME, Student::PROP_ID, Attendance::TABLE_NAME, Attendance::PROP_STUDENT_ID);
$this->joinTableWith(Attendance::TABLE_NAME, $attendanceStudentFilter . ' AND ' . $attendanceSubjectFilter);
$resultStudentFilter = QBuilder::buildColEq(Student::TABLE_NAME, Student::PROP_ID, Result::TABLE_NAME, Result::PROP_STUDENT_ID);
$resultExamFilter = QBuilder::buildColEq(Exam::TABLE_NAME, Exam::PROP_ID, Result::TABLE_NAME, Result::PROP_EXAM_ID);
$this->joinTableWith(Result::TABLE_NAME, $resultExamFilter . ' AND ' . $resultStudentFilter, 'LEFT JOIN');
$filters = array(QBuilder::buildColName(Group::TABLE_NAME, Group::PROP_ID) => $groupID);
$this->buildConjunctionWhereClause($filters);
$this->appendAndFilter(QBuilder::buildColName(Season::TABLE_NAME, Season::PROP_ID), $seasonID);
$this->appendAndFilter(QBuilder::buildColName(Subject::TABLE_NAME, Subject::PROP_ID), $subjectID);
$this->appendAndFilter(QBuilder::buildColName(Attendance::TABLE_NAME, Attendance::PROP_ASSIGNMENT), '1');
$this->appendAndFilter(QBuilder::buildColName(Attendance::TABLE_NAME, Attendance::PROP_LAB), '1');
$this->appendAndFilter(QBuilder::buildColName(Attendance::TABLE_NAME, Attendance::PROP_SEMINARIES), '1');
$this->appendAndFilter(QBuilder::buildColName(Attendance::TABLE_NAME, Attendance::PROP_STATUS), $isImprovement ? '1' : '0');
$this->appendAndFilter(QBuilder::buildColName(Exam::TABLE_NAME, Exam::PROP_HEAD_ID), $professorID);
}
/**
* @param string[] $colNames
* @param string[] $tableNames
* @param string[] $filters
*/
public function __construct($colNames, $tableNames, $filters = array())
{
$this->db = new DefaultDatabase();
$colNames = $this->db->sanitizeNameValuePairs($colNames);
$tableNames = $this->db->sanitizeNameValuePairs($tableNames);
$filters = $this->db->sanitizeNameValuePairs($filters);
$this->projection = empty($colNames) ? '*' : QueryPartsBuilder::buildCSVString($colNames);
$this->tableNames = QueryPartsBuilder::buildCSVString($tableNames);
$this->buildConjunctionWhereClause($filters);
}
/**
* @return string
*/
public function getQuery()
{
$setQueryPart = QueryPartsBuilder::buildNameValuePairStrings($this->nameValuePairs);
$setQueryPart = QueryPartsBuilder::buildCSVString($setQueryPart);
$selection = trim($this->selection);
if (empty($selection)) {
$query = "UPDATE {$this->tableName} SET {$setQueryPart}";
} else {
$query = "UPDATE {$this->tableName} SET {$setQueryPart} WHERE {$this->selection}";
}
return $query;
}
/**
* @param int $professorID
*/
function __construct($professorID)
{
$this->db = new DefaultDatabase();
$professorID = intval($professorID);
$colNames = array(QBuilder::buildColName(Attendance::TABLE_NAME, '*'));
$this->projection = QBuilder::buildCSVString($colNames);
$tableNames = array(Attendance::TABLE_NAME);
$this->tableNames = QBuilder::buildCSVString($tableNames);
$this->joinTableWith(Subject::TABLE_NAME, QBuilder::buildColEq(Attendance::TABLE_NAME, Attendance::PROP_SUBJECT_ID, Subject::TABLE_NAME, Subject::PROP_ID));
$this->joinTableWith(Exam::TABLE_NAME, QBuilder::buildColEq(Subject::TABLE_NAME, Subject::PROP_ID, Exam::TABLE_NAME, Exam::PROP_SUBJECT_ID));
$filters = array(QBuilder::buildColName(Exam::TABLE_NAME, Exam::PROP_HEAD_ID) => $professorID);
$this->buildConjunctionWhereClause($filters);
}
/**
* @param int $studentID
*/
function __construct($studentID)
{
$this->db = new DefaultDatabase();
$studentID = intval($this->db->escape($studentID));
$this->studentID = $studentID;
$this->lastResultDateAlias = 'LastResultDate';
$resultDateFullyQualifiedColName = Result::TABLE_NAME . '.' . Result::PROP_DATE;
$colNames = array(Subject::TABLE_NAME . '.' . Subject::PROP_ID, "MAX({$resultDateFullyQualifiedColName}) AS {$this->lastResultDateAlias}");
$this->projection = QueryPartsBuilder::buildCSVString($colNames);
$tableNames = array(Subject::TABLE_NAME, Exam::TABLE_NAME, Result::TABLE_NAME, Student::TABLE_NAME, Group::TABLE_NAME, Department::TABLE_NAME);
$this->tableNames = QueryPartsBuilder::buildCSVString($tableNames);
$filters = array(Result::TABLE_NAME . '.' . Result::PROP_STUDENT_ID => $studentID);
$this->buildConjunctionWhereClause($filters);
$this->appendAndFilter(Exam::TABLE_NAME . '.' . Exam::PROP_SUBJECT_ID, Subject::TABLE_NAME . '.' . Subject::PROP_ID, true);
$this->appendAndFilter(Result::TABLE_NAME . '.' . Result::PROP_EXAM_ID, Exam::TABLE_NAME . '.' . Exam::PROP_ID, true);
$this->appendAndFilter(Student::TABLE_NAME . '.' . Student::PROP_GROUP_ID, Group::TABLE_NAME . '.' . Group::PROP_ID, true);
$this->appendAndFilter(Group::TABLE_NAME . '.' . Group::PROP_DEPARTMENT_ID, Exam::TABLE_NAME . '.' . Exam::PROP_DEPARTMENT_ID, true);
}
/**
* @param int $studentID
*/
function __construct($studentID)
{
$this->db = new DefaultDatabase();
$studentID = intval($this->db->escape($studentID));
$this->studentID = $studentID;
$colNames = array(Subject::TABLE_NAME . '.' . Subject::PROP_NAME, Result::TABLE_NAME . '.' . Result::PROP_MARK);
$this->projection = QueryPartsBuilder::buildCSVString($colNames);
$this->latestExamTableAlias = 'LatestExam';
$latestExamQuery = new LatestExamQuery($studentID);
$latestExamTableQuery = $latestExamQuery->getQuery();
$latestExamTableQuery = "({$latestExamTableQuery}) AS {$this->latestExamTableAlias}";
$tableNames = array(Subject::TABLE_NAME, Exam::TABLE_NAME, Result::TABLE_NAME, Student::TABLE_NAME, Group::TABLE_NAME, Department::TABLE_NAME, $latestExamTableQuery);
$this->tableNames = QueryPartsBuilder::buildCSVString($tableNames);
$filters = array(Result::TABLE_NAME . '.' . Result::PROP_STUDENT_ID => $studentID);
$this->buildConjunctionWhereClause($filters);
$this->appendAndFilter(Exam::TABLE_NAME . '.' . Exam::PROP_SUBJECT_ID, Subject::TABLE_NAME . '.' . Subject::PROP_ID, true);
$this->appendAndFilter(Result::TABLE_NAME . '.' . Result::PROP_EXAM_ID, Exam::TABLE_NAME . '.' . Exam::PROP_ID, true);
$this->appendAndFilter(Student::TABLE_NAME . '.' . Student::PROP_GROUP_ID, Group::TABLE_NAME . '.' . Group::PROP_ID, true);
$this->appendAndFilter(Group::TABLE_NAME . '.' . Group::PROP_DEPARTMENT_ID, Exam::TABLE_NAME . '.' . Exam::PROP_DEPARTMENT_ID, true);
$this->appendAndFilter(Subject::TABLE_NAME . '.' . Subject::PROP_ID, $this->latestExamTableAlias . '.' . Subject::PROP_ID, true);
$this->appendAndFilter($this->latestExamTableAlias . '.' . $latestExamQuery->getLastResultDateAlias(), Result::TABLE_NAME . '.' . Result::PROP_DATE, true);
}
