public function constructWhereFromFilterParams($filterParams)
{
$where = new Where();
$table = DbTables::TBL_BACKOFFICE_USERS;
if (isset($filterParams["group"]) && $filterParams["group"] != '0') {
$where->expression($filterParams["group"] . ' IN (SELECT `group_id` FROM ' . DbTables::TBL_BACKOFFICE_USER_GROUPS . ' WHERE `user_id` = ' . $table . '.id ) ', []);
}
if (isset($filterParams["ud"]) && $filterParams["ud"] != '0') {
$where->expression($filterParams["ud"] . ' IN (SELECT `dashboard_id` FROM ' . DbTables::TBL_BACKOFFICE_USER_DASHBOARDS . ' WHERE `user_id` = ' . $table . '.id ) ', []);
}
if (isset($filterParams["team"]) && $filterParams["team"] != '0') {
$where->expression($filterParams["team"] . ' IN (SELECT `team_id` FROM ' . DbTables::TBL_TEAM_STAFF . ' WHERE `user_id` = ' . $table . '.id AND `type` NOT IN (' . TeamService::STAFF_CREATOR . ', ' . TeamService::STAFF_DIRECTOR . ')) ', []);
}
if (isset($filterParams['city']) && $filterParams['city'] > 0) {
$where->EqualTo('city_id', $filterParams['city']);
}
if (isset($filterParams["system-user-status"])) {
if ($filterParams["system-user-status"] == 1) {
$where->and->EqualTo('system', 0);
} elseif ($filterParams["system-user-status"] == 2) {
$where->and->EqualTo('system', 1);
}
}
if (isset($filterParams["external-user-status"])) {
if ($filterParams["external-user-status"] == 1) {
$where->and->EqualTo('external', 0);
} elseif ($filterParams["external-user-status"] == 2) {
$where->and->EqualTo('external', 1);
}
}
if (isset($filterParams["active-user-status"])) {
if ($filterParams["active-user-status"] == 1) {
$where->and->EqualTo('disabled', 0);
} elseif ($filterParams["active-user-status"] == 2) {
$where->and->EqualTo('disabled', 1);
}
}
if (isset($filterParams["sSearch"]) && $filterParams["sSearch"] != '') {
$nestedWhere = new \Zend\Db\Sql\Predicate\Predicate();
$nestedWhere->like($table . '.firstname', '%' . $filterParams["sSearch"] . '%');
$nestedWhere->OR;
$nestedWhere->like($table . '.lastname', '%' . $filterParams["sSearch"] . '%');
$nestedWhere->OR;
$nestedWhere->like($table . '.position', '%' . $filterParams["sSearch"] . '%');
$nestedWhere->OR;
$nestedWhere->like('details' . '.name', '%' . $filterParams["sSearch"] . '%');
$nestedWhere->OR;
$nestedWhere->like('teams' . '.name', '%' . $filterParams["sSearch"] . '%');
$where->addPredicate($nestedWhere);
}
return $where;
}
/**
* Search for records
* @param string $title
* @return array
*/
public function searchData($term)
{
$sql = new Sql($this->tableGateway->getAdapter());
$select = new Select();
$where = new Where();
$where->EqualTo('mobile', $term);
$select->from('contacts')->columns(array('contact_id', 'first_name', 'last_name', 'email', 'mobile'))->where($where);
$select->where($where);
$sql->select();
$statement = $sql->prepareStatementForSqlObject($select);
$result = $statement->execute();
$rows = get_object_vars($result);
// converting two dimensional rows to one dimension
return $rows;
}
