public function searchLeaveEntitlements(LeaveEntitlementSearchParameterHolder $searchParameters)
{
try {
$q = Doctrine_Query::create()->from('LeaveEntitlement le');
$deletedFlag = $searchParameters->getDeletedFlag();
$leaveTypeId = $searchParameters->getLeaveTypeId();
$empNumber = $searchParameters->getEmpNumber();
$fromDate = $searchParameters->getFromDate();
$toDate = $searchParameters->getToDate();
$idList = $searchParameters->getIdList();
$validDate = $searchParameters->getValidDate();
$empIdList = $searchParameters->getEmpIdList();
$hydrationMode = $searchParameters->getHydrationMode();
$orderField = $searchParameters->getOrderField();
$order = $searchParameters->getOrderBy();
$entitlementTypes = $searchParameters->getEntitlementTypes();
$params = array();
if ($deletedFlag === true) {
$q->addWhere('le.deleted = 1');
} else {
if ($deletedFlag === false) {
$q->addWhere('le.deleted = 0');
}
}
if (!empty($leaveTypeId)) {
$q->addWhere('le.leave_type_id = :leaveTypeId');
$params[':leaveTypeId'] = $leaveTypeId;
}
if (!is_null($empNumber)) {
$q->addWhere('le.emp_number = :empNumber');
$params[':empNumber'] = $empNumber;
}
if (!empty($fromDate) && !empty($toDate)) {
$q->addWhere('(le.from_date BETWEEN :fromDate AND :toDate)');
$params[':fromDate'] = $fromDate;
$params[':toDate'] = $toDate;
}
if (!empty($validDate)) {
$q->addWhere('(:validDate BETWEEN le.from_date AND le.to_date)');
$params[':validDate'] = $validDate;
}
if (!empty($idList)) {
$q->andWhereIn('le.id', $idList);
}
if (is_array($entitlementTypes) && count($entitlementTypes) > 0) {
// We are not using andWhereIn(), because it causes the error:
// 'Invalid parameter number: mixed named and positional parameters'.
// This is because andWhereIn() adds positional parameters (? marks),
// while we are using named parameters for the other parts of the query.
// Therefore, we are building the whereIn() part using named parameters here.
$count = 0;
$namedParams = array();
foreach ($entitlementTypes as $entitlementType) {
$count++;
$namedParam = ':et' . $count;
$namedParams[] = $namedParam;
$params[$namedParam] = $entitlementType;
}
$q->andWhere('le.entitlement_type IN (' . implode(',', $namedParams) . ')');
}
if (!empty($empIdList)) {
// filter empIdList and create comma separated string.
$filteredIds = array_map(function ($item) {
return filter_var($item, FILTER_VALIDATE_INT);
}, $empIdList);
// Remove items which did not validate as int (they will be set to false)
// NOTE: This doesn't cause SQL injection issues because we filter the array
// and only allow integers in the array.
$filteredIds = array_filter($filteredIds);
$q->andWhere('le.emp_number IN (' . implode(',', $filteredIds) . ')');
}
// We need leave type name
$q->leftJoin('le.LeaveType l');
$orderClause = '';
switch ($orderField) {
case 'leave_type':
$orderClause = 'l.name ' . $order;
break;
case 'employee_name':
$q->leftJoin('le.Employee e');
$orderClause = 'e.emp_lastname ' . $order . ', e.emp_firstname ' . $order;
break;
default:
$orderClause = $orderField . ' ' . $order;
$orderClause = trim($orderClause);
break;
}
// get predictable sorting
if (!empty($orderClause)) {
$orderClause .= ', le.id ASC';
}
$q->addOrderBy($orderClause);
if (!empty($hydrationMode)) {
$q->setHydrationMode($hydrationMode);
}
$results = $q->execute($params);
return $results;
} catch (Exception $e) {
throw new DaoException($e->getMessage(), 0, $e);
}
}