/**
* Search Leave Requests.
*
* Valid Search Parameter values
* * 'noOfRecordsPerPage' (int) - Number of records per page. If not available,
* sfConfig::get('app_items_per_page') will be used.
* * 'dateRange' (DateRange) -
* * 'statuses' (array)
* * 'employeeFilter' (array) - Filter by given employees. If an empty array(), does not match any employees.
* * 'leavePeriod'
* * 'leaveType'
* * 'cmbWithTerminated'
* * 'subUnit' - Only return leave requests for employees in given subunit
* (or subunit below that in the org structure).
* * 'locations' (array) - Only return leave requests for employees in given locations.
* * 'employeeName' (string) - Match employee name (Wildcard match against full name).
*
* @param ParameterObject $searchParameters Search Parameters
* @param int $page $status Page Number
* @param bool $isCSVPDFExport If true, returns all results (ignores paging) as an array
* @param bool $isMyLeaveList If true, ignores setting to skip terminated employees.
* @param bool $prefetchComments If true, will prefetch leave comments for faster access.
*
* @return array Returns results and record count in the following format:
* array('list' => results, 'meta' => array('record_count' => count)
*
* If $isCSVPDFExport is true, returns just an array of results.
*/
public function searchLeaveRequests($searchParameters, $page = 1, $isCSVPDFExport = false, $isMyLeaveList = false, $prefetchLeave = false, $prefetchComments = false)
{
$this->_markApprovedLeaveAsTaken();
$limit = !is_null($searchParameters->getParameter('noOfRecordsPerPage')) ? $searchParameters->getParameter('noOfRecordsPerPage') : sfConfig::get('app_items_per_page');
$offset = $page > 0 ? ($page - 1) * $limit : 0;
$list = array();
$select = 'lr.*, em.firstName, em.lastName, em.middleName, em.termination_id, lt.*';
if ($prefetchComments) {
$select .= ', lc.*';
}
if ($prefetchLeave) {
$select .= ', l.*';
}
$q = Doctrine_Query::create()->select($select)->from('LeaveRequest lr')->leftJoin('lr.Leave l')->leftJoin('lr.Employee em')->leftJoin('lr.LeaveType lt');
if ($prefetchComments) {
$q->leftJoin('lr.LeaveRequestComment lc');
}
$dateRange = $searchParameters->getParameter('dateRange', new DateRange());
$statuses = $searchParameters->getParameter('statuses');
$employeeFilter = $searchParameters->getParameter('employeeFilter');
$leavePeriod = $searchParameters->getParameter('leavePeriod');
$leaveType = $searchParameters->getParameter('leaveType');
$leaveTypeId = $searchParameters->getParameter('leaveTypeId');
$includeTerminatedEmployees = $searchParameters->getParameter('cmbWithTerminated');
$subUnit = $searchParameters->getParameter('subUnit');
$locations = $searchParameters->getParameter('locations');
$employeeName = $searchParameters->getParameter('employeeName');
$fromDate = $dateRange->getFromDate();
$toDate = $dateRange->getToDate();
if (!empty($fromDate)) {
$q->andWhere("l.date >= ?", $fromDate);
}
if (!empty($toDate)) {
$q->andWhere("l.date <= ?", $toDate);
}
if (!empty($statuses)) {
$q->whereIn("l.status", $statuses);
}
if (!empty($employeeFilter)) {
if (is_numeric($employeeFilter) && $employeeFilter > 0) {
$q->andWhere('lr.emp_number = ?', (int) $employeeFilter);
} elseif ($employeeFilter instanceof Employee) {
$q->andWhere('lr.emp_number = ?', $employeeFilter->getEmpNumber());
} elseif (is_array($employeeFilter)) {
$empNumbers = array();
foreach ($employeeFilter as $employee) {
$empNumbers[] = $employee instanceof Employee ? $employee->getEmpNumber() : $employee;
}
// Here, ->whereIn() is very slow when employee number count is very high (around 5000).
// this seems to be due to the time taken by Doctrine to replace the 5000 question marks in the query.
// Therefore, replaced with manually built IN clause.
// Note: $empNumbers is not based on user input and therefore is safe to use in the query.
$q->andWhere('lr.emp_number IN (' . implode(',', $empNumbers) . ')');
}
} else {
// empty array does not match any results.
if (is_array($employeeFilter)) {
$q->andWhere('lr.emp_number = ?', -1);
}
}
// if (trim($fromDate) == "" && trim($toDate) == "" && !empty($leavePeriod)) {
// $leavePeriodId = ($leavePeriod instanceof LeavePeriod) ? $leavePeriod->getLeavePeriodId() : $leavePeriod;
// $q->andWhere('lr.leave_period_id = ?', (int) $leavePeriodId);
// }
if (!empty($leaveType)) {
$leaveTypeId = $leaveType instanceof LeaveType ? $leaveType->getLeaveTypeId() : $leaveType;
$q->andWhere('lr.leave_type_id = ?', $leaveTypeId);
}
if (!empty($leaveTypeId)) {
$q->andWhere('lr.leave_type_id = ?', $leaveTypeId);
}
if ($isMyLeaveList) {
$includeTerminatedEmployees = true;
}
// Search by employee name
if (!empty($employeeName)) {
$employeeName = str_replace(' (' . __('Past Employee') . ')', '', $employeeName);
// Replace multiple spaces in string with wildcards
$employeeName = preg_replace('!\\s+!', '%', $employeeName);
// Surround with wildcard character
$employeeName = '%' . $employeeName . '%';
$q->andWhere('CONCAT_WS(\' \', em.emp_firstname, em.emp_middle_name, em.emp_lastname) LIKE ?', $employeeName);
}
if (!empty($subUnit)) {
// Get given subunit's descendents as well.
$subUnitIds = array($subUnit);
$subUnitObj = Doctrine::getTable('Subunit')->find($subUnit);
if (!empty($subUnitObj)) {
$descendents = $subUnitObj->getNode()->getDescendants();
foreach ($descendents as $descendent) {
$subUnitIds[] = $descendent->id;
}
}
$q->andWhereIn('em.work_station', $subUnitIds);
}
if (empty($includeTerminatedEmployees)) {
$q->andWhere("em.termination_id IS NULL");
}
if (!empty($locations)) {
$q->leftJoin('em.locations loc');
$q->andWhereIn('loc.id', $locations);
}
$count = $q->count();
$q->orderBy('l.date DESC, em.emp_lastname ASC, em.emp_firstname ASC');
if ($isCSVPDFExport) {
$limit = $count;
$offset = 0;
}
$q->offset($offset);
$q->limit($limit);
$list = $q->execute();
return $isCSVPDFExport ? $list : array('list' => $list, 'meta' => array('record_count' => $count));
}
/**
* Test funtion to verify searching for leave requests of by
* Employee Name.
*/
public function testSearchLeaveRequestsByEmployeeName()
{
$leaveFixture = $this->fixture['LeaveRequest'];
$ashleyLeave = array($leaveFixture[13], $leaveFixture[12], $leaveFixture[11]);
$tylorLandonJamesLeave = array($leaveFixture[18], $leaveFixture[16], $leaveFixture[15], $leaveFixture[14], $leaveFixture[17]);
$names = array('Ashley Aldis Abel', 'Aldis', 'ldis', 'Aldis', 'Abr');
$expectedArray = array($ashleyLeave, $ashleyLeave, $ashleyLeave, $ashleyLeave, $tylorLandonJamesLeave);
for ($i = 0; $i < count($names); $i++) {
$name = $names[$i];
$expected = $expectedArray[$i];
$searchParameters = new ParameterObject();
$searchParameters->setParameter('employeeName', $name);
$searchResult = $this->leaveRequestDao->searchLeaveRequests($searchParameters);
$requestList = $searchResult['list'];
$requestCount = $searchResult['meta']['record_count'];
/* Checking type */
foreach ($requestList as $request) {
$this->assertTrue($request instanceof LeaveRequest);
}
/* Checking count */
$this->assertEquals(count($expected), count($requestList));
$this->assertEquals(count($expected), $requestCount);
/* Checking values and order */
$this->compareLeaveRequests($expected, $requestList);
}
}