/**
* Get Doctrine Query which can be used fetch employee list with the given
* sorting and filtering options
*
* @param array $sortField
* @param $sortOrder
* @param $filters
* @return array
*/
public function getEmployeePerformanceReviewListQuery($sortField = null, $sortOrder = null, array $filters = null)
{
$searchByStatus = false;
/*
* Using RawSQL since it is difficult to use DQL to get an efficient query for the
* employee list search
*/
$query = new Doctrine_RawSql();
$query->select('{e.emp_number}, {e.employee_id}, {e.emp_firstname}, {e.emp_lastname}, ' . '{e.emp_middle_name}, {cs.title}, {j.jobtit_name}, {es.estat_name}, ' . '{s.emp_firstname}, {s.emp_lastname}, rt.erep_reporting_mode')->from("hs_hr_employee e LEFT JOIN hs_hr_compstructtree cs on cs.id = e.work_station " . " LEFT JOIN hs_hr_job_title j on j.jobtit_code = e.job_title_code " . " LEFT JOIN hs_hr_empstat es on e.emp_status = es.estat_code " . " LEFT JOIN hs_hr_emp_reportto rt on e.emp_number = rt.erep_sub_emp_number " . " LEFT JOIN hs_hr_employee s on s.emp_number = rt.erep_sup_emp_number ");
$query->addComponent('e', 'Employee e');
$query->addComponent('cs', 'e.subDivision cs');
$query->addComponent('j', 'e.jobTitle j');
$query->addComponent('es', 'e.employeeStatus es');
$query->addComponent('s', 'e.supervisors s');
/* search filters */
if (!empty($filters)) {
$filterCount = 0;
foreach ($filters as $searchField => $searchBy) {
if (!empty($searchField) && !empty($searchBy) && array_key_exists($searchField, self::$searchMapping)) {
$field = self::$searchMapping[$searchField];
$value = '%' . $searchBy . '%';
if ($searchField == 'subDivision') {
/*
* Not efficient if searching substations by more than one value, but
* we only have the facility to search by one value in the UI.
*/
$query->andwhere('e.work_station IN (SELECT n.id FROM hs_hr_compstructtree n ' . 'INNER JOIN hs_hr_compstructtree p WHERE n.lft >= p.lft ' . 'AND n.rgt <= p.rgt AND p.title LIKE ? )', $value);
} else {
if ($searchField == 'supervisorId') {
$query->andwhere('s.emp_number = ?', $searchBy);
} else {
if ($filterCount == 0) {
$query->where($field . ' LIKE ?', $value);
} else {
$query->andwhere($field . ' LIKE ?', $value);
}
}
}
$filterCount++;
if ($searchField == 'employeeStatus') {
$searchByStatus = true;
}
}
}
}
/* If not searching by employee status, hide terminated employees */
if (!$searchByStatus) {
$query->andwhere('( e.emp_status != ? OR e.emp_status IS NULL )', 'EST000');
}
/* sorting */
if (!empty($sortField) && !empty($sortOrder)) {
if (array_key_exists($sortField, self::$sortMapping)) {
$field = self::$sortMapping[$sortField];
if (is_array($field)) {
foreach ($field as $key => $name) {
$query->addOrderBy($name . ' ' . $sortOrder);
}
} else {
$query->orderBy($field . ' ' . $sortOrder);
}
}
}
/* Default sort by emp_number, makes resulting order predictable, useful for testing */
$query->addOrderBy('e.emp_number', 'asc');
/* Sort subordinates direct first, then indirect, then by supervisor name */
$query->addOrderBy('rt.erep_reporting_mode', 'asc');
if ($sortField != 'supervisor') {
$query->addOrderBy('s.emp_firstname', 'asc');
$query->addOrderBy('s.emp_lastname', 'asc');
}
return $query;
}
