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