/** * Returns the result of a database query for all records * of persons helped, associated with user name and id * * @return Persons_Model_Person */ public function getAll($params = array('from' => 'v_person_helped', 'appaccount_id' => 0, 'orderby' => 'name', 'paginator' => true)) { $db = $this->getDefaultAdapter(); $sql = $db->select()->from($params['from'])->where('appaccount_id = ?', $params['appaccount_id'])->order($params['orderby']); if (isset($params['filter-keyword'])) { $normalize = new Agana_Filter_Normalize(); $sql->where('lower(unaccented(name)) LIKE ?', $normalize->filter($params['filter-keyword'])); } $db->setFetchMode(Zend_DB::FETCH_ASSOC); if ($params['paginator']) { $adapter = new Zend_Paginator_Adapter_DbSelect($sql); $paginator = new Zend_Paginator($adapter); $page = isset($params['page']) ? $params['page'] : 1; $paginator->setCurrentPageNumber($page); $itemCountPerPage = isset($params['itemCountPerPage']) ? $params['itemCountPerPage'] : 20; $paginator->setItemCountPerPage($itemCountPerPage); $pageRange = isset($params['pageRange']) ? $params['pageRange'] : 7; $paginator->setPageRange($pageRange); return $paginator; } else { return $this->_prepareReturnData($db->fetchAll($sql)); } }
/** * Search persons by similarity in theirs names * * @param string $name * @return array * @throws Exception */ public function searchBySimilarity($appaccount_id, $name) { try { $db = $this->getDefaultAdapter(); $partials = explode(' ', $name); $queryRes = array(); $sqlArray = array(); // build each sql with the first word and the next one in sequence $normalize = new Agana_Filter_Normalize(); $queryRes = array(); if (count($partials) == 1) { $sql = $db->select()->from('person')->where('appaccount_id = ?', $appaccount_id)->where($db->quoteInto('lower(unaccented(name)) LIKE ?', '%' . $normalize->filter(trim($partials[0])) . '%')); //->order('name'); } else { for ($i = 1; $i < count($partials); $i++) { $sql = $db->select()->from('person')->where('appaccount_id = ?', $appaccount_id)->where($db->quoteInto('lower(unaccented(name)) LIKE ?', '%' . $normalize->filter(trim($partials[0])) . '%' . $normalize->filter(trim($partials[$i])) . '%')); //->order('name'); $sqlArray[] = $sql; } // build each sql with the actual word and the next one for ($i = 0; $i < count($partials) - 1; $i++) { $sql = $db->select()->from('person')->where('appaccount_id = ?', $appaccount_id)->where($db->quoteInto('lower(unaccented(name)) LIKE ?', '%' . $normalize->filter(trim($partials[$i])) . '%' . $normalize->filter(trim($partials[$i + 1])) . '%')); //->order('name'); $sqlArray[] = $sql; } $sql = $db->select()->union($sqlArray); } //return $sql->__toString(); $db->setFetchMode(Zend_Db::FETCH_ASSOC); $queryRes = array_merge($queryRes, $this->_prepareReturnData($db->fetchAll($sql))); return $queryRes; } catch (Exception $e) { throw $e; } }