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