/**
* Get all users for export method
*
* @param int $typeId
* @param string $sort
* @param string $page
* @return void
*/
public function getExport($typeId, $sort = null, $page = null)
{
$order = $this->getSortOrder($sort, $page);
$sql = Table\Users::getSql();
$order['field'] = $order['field'] == 'id' ? DB_PREFIX . 'users.id' : $order['field'];
// Build the SQL statement to get users
$sql->select(array(DB_PREFIX . 'users.id', DB_PREFIX . 'users.username', DB_PREFIX . 'users.email', DB_PREFIX . 'users.logins'))->orderBy($order['field'], $order['order']);
$sql->select()->where()->equalTo(DB_PREFIX . 'users.type_id', ':type_id');
$params = array('type_id' => $typeId);
if (isset($_GET['search_by'])) {
if ($_GET['search_by'] == 'username') {
$sql->select()->where()->like(DB_PREFIX . 'users.username', ':username');
$searchFor = htmlentities(strip_tags($_GET['search_for']), ENT_QUOTES, 'UTF-8');
$params['username'] = '******' . $searchFor . '%';
} else {
if ($_GET['search_by'] == 'email') {
$sql->select()->where()->like(DB_PREFIX . 'users.email', ':email');
$searchFor = htmlentities(strip_tags($_GET['search_for']), ENT_QUOTES, 'UTF-8');
$params['email'] = '%' . $searchFor . '%';
} else {
if (strpos($_GET['search_by'], 'field_') !== false) {
$id = (int) substr($_GET['search_by'], strrpos($_GET['search_by'], '_') + 1);
$sql->select()->join(DB_PREFIX . 'field_values', array('id', 'model_id'), 'LEFT JOIN');
$sql->select()->where()->equalTo(DB_PREFIX . 'field_values.field_id', ':field_id');
$sql->select()->where()->like(DB_PREFIX . 'field_values.value', ':value');
$searchFor = htmlentities(strip_tags($_GET['search_for']), ENT_QUOTES, 'UTF-8');
$params['field_id'] = $id;
$params['value'] = '%' . $searchFor . '%';
}
}
}
}
// Execute SQL query and get user type
$users = Table\Users::execute($sql->render(true), $params);
$type = Table\UserTypes::findById($typeId);
$userRows = array();
if (isset($users->rows[0])) {
foreach ($users->rows as $row) {
if (null !== $row->logins) {
$logins = unserialize($row->logins);
$row->logins = count($logins);
end($logins);
$row->last_login = date('M j Y g:i A', key($logins));
} else {
$row->logins = 0;
$row->last_login = '******';
}
$values = FieldValue::getAll($row->id, true);
$row = new \ArrayObject(array_merge((array) $row, $values), \ArrayObject::ARRAY_AS_PROPS);
$userRows[] = $row;
}
}
$this->data['userType'] = $type->type;
$this->data['userRows'] = $userRows;
}
/**
* Get count of users
*
* @param int $roleId
* @param array $search
* @param array $deniedRoles
* @return int
*/
public function getCount($roleId = null, array $search = null, array $deniedRoles = [])
{
$params = [];
$sql = Table\Users::sql();
$sql->select();
if (null !== $search) {
$sql->select()->where($search['by'] . ' LIKE :' . $search['by']);
$params[$search['by']] = $search['for'] . '%';
}
if (null !== $roleId) {
$sql->select()->where('role_id = :role_id');
$params['role_id'] = $roleId;
}
if (count($deniedRoles) > 0) {
foreach ($deniedRoles as $key => $denied) {
$sql->select()->where('role_id != :role_id' . ($key + 1));
$params['role_id' . ($key + 1)] = $denied;
}
}
if (count($params) > 0) {
return Table\Users::execute((string) $sql, $params)->count();
} else {
return Table\Users::findAll()->count();
}
}
