public function fetchRelatedFor(Doctrine_Record $record) { $id = $record->getIncremented(); if (empty($id) || !$this->definition['table']->getAttribute(Doctrine_Core::ATTR_LOAD_REFERENCES)) { return Doctrine_Collection::create($this->getTable()); } else { $q = new Doctrine_RawSql($this->getTable()->getConnection()); $formatter = $q->getConnection()->formatter; $assocTable = $this->getAssociationFactory()->getTableName(); $tableName = $record->getTable()->getTableName(); // Removing schema name from table names (MySQL Nest Relations fix) function removeSchemaFromTable($tableName) { $e = explode('.', $tableName); if (isset($e[1])) { return $e[1]; } return $e[0]; } $assocTable = removeSchemaFromTable($assocTable); $tableName = removeSchemaFromTable($tableName); $identifierColumnNames = $record->getTable()->getIdentifierColumnNames(); $identifier = $formatter->quoteIdentifier(array_pop($identifierColumnNames)); $sub = 'SELECT ' . $formatter->quoteIdentifier($this->getForeignRefColumnName()) . ' FROM ' . $formatter->quoteIdentifier($assocTable) . ' WHERE ' . $formatter->quoteIdentifier($this->getLocalRefColumnName()) . ' = ?'; $condition[] = $formatter->quoteIdentifier($tableName) . '.' . $identifier . ' IN (' . $sub . ')'; $joinCondition[] = $formatter->quoteIdentifier($tableName) . '.' . $identifier . ' = ' . $formatter->quoteIdentifier($assocTable) . '.' . $formatter->quoteIdentifier($this->getForeignRefColumnName()); if ($this->definition['equal']) { $sub2 = 'SELECT ' . $formatter->quoteIdentifier($this->getLocalRefColumnName()) . ' FROM ' . $formatter->quoteIdentifier($assocTable) . ' WHERE ' . $formatter->quoteIdentifier($this->getForeignRefColumnName()) . ' = ?'; $condition[] = $formatter->quoteIdentifier($tableName) . '.' . $identifier . ' IN (' . $sub2 . ')'; $joinCondition[] = $formatter->quoteIdentifier($tableName) . '.' . $identifier . ' = ' . $formatter->quoteIdentifier($assocTable) . '.' . $formatter->quoteIdentifier($this->getLocalRefColumnName()); } $q->select('{' . $tableName . '.*}, {' . $assocTable . '.*}')->from($formatter->quoteIdentifier($tableName) . ' INNER JOIN ' . $formatter->quoteIdentifier($assocTable) . ' ON ' . implode(' OR ', $joinCondition))->where(implode(' OR ', $condition)); if ($orderBy = $this->getOrderByStatement($tableName, true)) { $q->addOrderBy($orderBy); } else { $q->addOrderBy($formatter->quoteIdentifier($tableName) . '.' . $identifier . ' ASC'); } $q->addComponent($tableName, $this->getClass()); $path = $this->getClass() . '.' . $this->getAssociationFactory()->getComponentName(); if ($this->definition['refClassRelationAlias']) { $path = $this->getClass() . '.' . $this->definition['refClassRelationAlias']; } $q->addComponent($assocTable, $path); $params = $this->definition['equal'] ? array($id, $id) : array($id); $res = $q->execute($params); return $res; } }
/** * Get Doctrine Query which can be used fetch employee list with the given * sorting and filtering options * * @param array $sortField * @param $sortOrder * @param $filters * @return array */ public function getEmployeePerformanceReviewListQuery($sortField = null, $sortOrder = null, array $filters = null) { $searchByStatus = false; /* * Using RawSQL since it is difficult to use DQL to get an efficient query for the * employee list search */ $query = new Doctrine_RawSql(); $query->select('{e.emp_number}, {e.employee_id}, {e.emp_firstname}, {e.emp_lastname}, ' . '{e.emp_middle_name}, {cs.title}, {j.jobtit_name}, {es.estat_name}, ' . '{s.emp_firstname}, {s.emp_lastname}, rt.erep_reporting_mode')->from("hs_hr_employee e LEFT JOIN hs_hr_compstructtree cs on cs.id = e.work_station " . " LEFT JOIN hs_hr_job_title j on j.jobtit_code = e.job_title_code " . " LEFT JOIN hs_hr_empstat es on e.emp_status = es.estat_code " . " LEFT JOIN hs_hr_emp_reportto rt on e.emp_number = rt.erep_sub_emp_number " . " LEFT JOIN hs_hr_employee s on s.emp_number = rt.erep_sup_emp_number "); $query->addComponent('e', 'Employee e'); $query->addComponent('cs', 'e.subDivision cs'); $query->addComponent('j', 'e.jobTitle j'); $query->addComponent('es', 'e.employeeStatus es'); $query->addComponent('s', 'e.supervisors s'); /* search filters */ if (!empty($filters)) { $filterCount = 0; foreach ($filters as $searchField => $searchBy) { if (!empty($searchField) && !empty($searchBy) && array_key_exists($searchField, self::$searchMapping)) { $field = self::$searchMapping[$searchField]; $value = '%' . $searchBy . '%'; if ($searchField == 'subDivision') { /* * Not efficient if searching substations by more than one value, but * we only have the facility to search by one value in the UI. */ $query->andwhere('e.work_station IN (SELECT n.id FROM hs_hr_compstructtree n ' . 'INNER JOIN hs_hr_compstructtree p WHERE n.lft >= p.lft ' . 'AND n.rgt <= p.rgt AND p.title LIKE ? )', $value); } else { if ($searchField == 'supervisorId') { $query->andwhere('s.emp_number = ?', $searchBy); } else { if ($filterCount == 0) { $query->where($field . ' LIKE ?', $value); } else { $query->andwhere($field . ' LIKE ?', $value); } } } $filterCount++; if ($searchField == 'employeeStatus') { $searchByStatus = true; } } } } /* If not searching by employee status, hide terminated employees */ if (!$searchByStatus) { $query->andwhere('( e.emp_status != ? OR e.emp_status IS NULL )', 'EST000'); } /* sorting */ if (!empty($sortField) && !empty($sortOrder)) { if (array_key_exists($sortField, self::$sortMapping)) { $field = self::$sortMapping[$sortField]; if (is_array($field)) { foreach ($field as $key => $name) { $query->addOrderBy($name . ' ' . $sortOrder); } } else { $query->orderBy($field . ' ' . $sortOrder); } } } /* Default sort by emp_number, makes resulting order predictable, useful for testing */ $query->addOrderBy('e.emp_number', 'asc'); /* Sort subordinates direct first, then indirect, then by supervisor name */ $query->addOrderBy('rt.erep_reporting_mode', 'asc'); if ($sortField != 'supervisor') { $query->addOrderBy('s.emp_firstname', 'asc'); $query->addOrderBy('s.emp_lastname', 'asc'); } return $query; }