public function fetchFaculties(Acad_Model_Class $class)
{
$sql = 'SELECT
`subject_faculty`.`staff_id`,
`subject_department`.`subject_code`,
`subject_faculty`.`subject_mode_id`,
`subject`.`subject_name`,
`subject`.`subject_type_id`
FROM `academics`.`subject_faculty`
INNER JOIN `academics`.`subject_department`
ON (`subject_faculty`.`department_id` = `subject_department`.`department_id`)
INNER JOIN `academics`.`subject`
ON (`subject_department`.`subject_code` = `subject`.`subject_code`)
AND (`subject_faculty`.`subject_code` = `subject`.`subject_code`)
WHERE (`subject_department`.`department_id` = ?
AND `subject_department`.`degree_id` = ?
AND `subject_department`.`semester_id` = ?)';
$bind = array($class->getDepartment(), $class->getDegree(), $class->getSemester());
return $this->getDbTable()->getAdapter()->query($sql, $bind)->fetchAll(Zend_Db::FETCH_GROUP);
}
/**
* Get Faculty Subjects in hand
* @param Acad_Model_Member_Faculty $faculty
* @param Acad_Model_Class|Acad_Model_Department $viewLevel = null
* @param bool $showModes = null
* @return array
*/
public function fetchInHandSubjects(Acad_Model_Member_Faculty $faculty, $viewLevel = NULL, $showModes = NULL)
{
$select = $this->getDbTable()->getAdapter()->select();
$select->distinct()->from('period_attendance2', array('subject_code', 'department_id', 'programme_id', 'semester_id'))->where('faculty_id = ?', $faculty->getMemberId());
if (isset($showModes)) {
$select->columns('subject_mode_id');
}
if ($viewLevel instanceof Acad_Model_Class) {
$select->where('department_id = ?', $viewLevel->getDepartment())->where('programme_id = ?', $viewLevel->getProgramme_id())->where('semester_id = ?', $viewLevel->getSemester());
} elseif ($viewLevel instanceof Acad_Model_Department) {
$select->where('department_id = ?', $viewLevel->getDepartment());
}
return $select->query()->fetchAll(Zend_Db::FETCH_GROUP);
}
/**
* Test conducted in class.
* @param Acad_Model_Class $class
*/
public function getConducted(Acad_Model_Class $class)
{
$sql = 'SELECT
test_id,
(SELECT
test_id
FROM test
WHERE test_type_id = ?
ORDER BY test_id DESC
LIMIT 1) AS max_test_id
FROM test_info
WHERE (department_id = ?
AND degree_id = ?
AND semester_id = ?
AND test_type_id = ?)
ORDER BY test_type_id,test_id DESC
LIMIT 1';
$bind[] = self::getTest_type_id();
$bind[] = $class->getDepartment();
$bind[] = $class->getDegree();
$bind[] = $class->getSemester();
$bind[] = self::getTest_type_id();
return Zend_Db_Table::getDefaultAdapter()->query($sql, $bind)->fetch();
}
/**
* Get Faculty Subjects
* @param Acad_Model_Member_Faculty $faculty
* @return array
*/
public function fetchSubjects(Acad_Model_Member_Faculty $faculty, Acad_Model_Class $class = NULL, $showModes = NULL)
{
$select = $this->getDbTable()->getAdapter()->select();
$select->distinct()->from('subject_faculty', array('subject_code'))->join('subject', 'subject_faculty.subject_code = subject.subject_code', array('subject_name'))->join('subject_department', 'subject_department.subject_code = subject.subject_code', array())->where('subject_faculty.staff_id = ?', $faculty->getMemberId());
if (isset($showModes)) {
$select->columns('subject_mode_id');
}
if (isset($class)) {
$select->where('subject_department.department_id = ?', $class->getDepartment())->where('subject_department.degree_id = ?', $class->getDegree())->where('subject_department.semester_id = ?', $class->getSemester());
}
return $select->query()->fetchAll(Zend_Db::FETCH_GROUP);
}
