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();
         $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 (!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);
     }
 }
Ejemplo n.º 2
0
 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();
         $order = strcasecmp($order, 'DESC') == 0 ? 'DESC' : 'ASC';
         $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);
     }
 }