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