static function selectUsers()
{
$qUsers = DBConn::executeQuery("SELECT u.id, u.name_first AS nameFirst, u.name_last AS nameLast, " . "u.email, u.email_verified AS verified, u.phone, u.created, u.last_updated AS lastUpdated, " . "u.disabled, CONCAT(u1.name_first, ' ', u1.name_last) AS updatedBy, t.name AS team, t.id AS teamId " . "FROM " . DBConn::prefix() . "users AS u " . "LEFT JOIN " . DBConn::prefix() . "team_members AS tm ON tm.user_id = u.id " . "LEFT JOIN " . DBConn::prefix() . "teams AS t ON t.id = tm.team_id " . "LEFT JOIN " . DBConn::prefix() . "users AS u1 ON u1.id = u.last_updated_by ORDER BY u.id;");
$qGroups = DBConn::preparedQuery("SELECT grp.id, grp.group, grp.desc, look.created AS assigned " . "FROM " . DBConn::prefix() . "auth_groups AS grp " . "JOIN " . DBConn::prefix() . "auth_lookup_user_group AS look ON grp.id = look.auth_group_id " . "WHERE look.user_id = :id GROUP BY grp.id ORDER BY grp.group;");
$users = array();
while ($user = $qUsers->fetch(\PDO::FETCH_OBJ)) {
$qGroups->execute(array(':id' => $user->id));
$user->groups = $qGroups->fetchAll(\PDO::FETCH_OBJ);
array_push($users, $user);
}
return $users;
}
static function selectVisibilityKey()
{
$qElements = DBConn::executeQuery("SELECT id, identifier, initialized " . "FROM " . DBConn::prefix() . "auth_fields WHERE initialized = 1 ORDER BY identifier;");
$qRoles = DBConn::preparedQuery("SELECT auth_role_id AS id " . "FROM " . DBConn::prefix() . "auth_lookup_role_field " . "WHERE auth_field_id = :auth_field_id " . "GROUP BY auth_role_id ORDER BY auth_role_id;");
$elements = array();
while ($elem = $qElements->fetch(\PDO::FETCH_OBJ)) {
$qRoles->execute(array(':auth_field_id' => $elem->id));
$elem->roles = $qRoles->fetchAll(PDO::FETCH_COLUMN);
array_push($elements, $elem);
}
return $elements;
}
static function selectVisibilityFields()
{
$qFields = DBConn::executeQuery("SELECT e.id, e.identifier, e.type, e.desc, e.initialized, e.created, e.last_updated AS lastUpdated, " . "CONCAT(u1.name_first, ' ', u1.name_last) AS createdBy, CONCAT(u2.name_first, ' ', u2.name_last) AS updatedBy " . "FROM " . DBConn::prefix() . "auth_fields AS e " . "JOIN " . DBConn::prefix() . "users AS u1 ON u1.id = e.created_user_id " . "JOIN " . DBConn::prefix() . "users AS u2 ON u2.id = e.last_updated_by ORDER BY e.identifier;");
$qRoles = DBConn::preparedQuery("SELECT r.id, r.role, r.desc " . "FROM " . DBConn::prefix() . "auth_roles AS r " . "JOIN " . DBConn::prefix() . "auth_lookup_role_field AS look ON r.id = look.auth_role_id " . "WHERE look.auth_field_id = :id GROUP BY r.id ORDER BY r.role;");
$elements = array();
while ($field = $qFields->fetch(\PDO::FETCH_OBJ)) {
$qRoles->execute(array(':id' => $field->id));
$field->roles = $qRoles->fetchAll(\PDO::FETCH_OBJ);
array_push($elements, $field);
}
return $elements;
}
static function deleteExpiredAuthTokens()
{
return DBConn::executeQuery('DELETE FROM ' . DBConn::prefix() . 'tokens_auth WHERE expires < NOW();');
}
