/**
  * 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);
     }
 }