public function buildSearchQuery(CandidateSearchParameters $paramObject, $countQuery = false)
{
try {
$query = $countQuery ? "SELECT COUNT(*)" : "SELECT jc.id, jc.first_name, jc.middle_name, jc.last_name, jc.date_of_application, jcv.status, jv.name, e.emp_firstname, e.emp_middle_name, e.emp_lastname, e.termination_id, ref.emp_firstname AS ref_firstname, ref.emp_middle_name AS ref_middle_name, ref.emp_lastname AS ref_lastname, jv.status as vacancyStatus, jv.id as vacancyId, ca.id as attachmentId, jc.status as candidateStatus";
$query .= " FROM ohrm_job_candidate jc";
$query .= " LEFT JOIN ohrm_job_candidate_vacancy jcv ON jc.id = jcv.candidate_id";
$query .= " LEFT JOIN ohrm_job_vacancy jv ON jcv.vacancy_id = jv.id";
$query .= " LEFT JOIN hs_hr_employee e ON jv.hiring_manager_id = e.emp_number";
$query .= " LEFT JOIN hs_hr_employee ref ON jc.added_person = ref.emp_number";
$query .= " LEFT JOIN ohrm_job_candidate_attachment ca ON jc.id = ca.candidate_id";
$query .= ' WHERE jc.date_of_application BETWEEN ' . "'{$paramObject->getFromDate()}'" . ' AND ' . "'{$paramObject->getToDate()}'";
$candidateStatuses = $paramObject->getCandidateStatus();
if (!empty($candidateStatuses)) {
$query .= " AND jc.status IN (" . implode(",", $candidateStatuses) . ")";
}
$query .= $this->_buildKeywordsQueryClause($paramObject->getKeywords());
$query .= $this->_buildAdditionalWhereClauses($paramObject);
$query .= " ORDER BY " . $this->_buildSortQueryClause($paramObject->getSortField(), $paramObject->getSortOrder());
if (!$countQuery) {
$query .= " LIMIT " . $paramObject->getOffset() . ", " . $paramObject->getLimit();
}
return $query;
} catch (Exception $e) {
throw new DaoException($e->getMessage());
}
}
