static function selectGroupBySlug($slug, $id = 0)
{
$group = DBConn::selectOne("SELECT g.id, g.group, g.slug, g.desc, g.created, g.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_groups AS g " . "JOIN " . DBConn::prefix() . "users AS u1 ON u1.id = g.created_user_id " . "JOIN " . DBConn::prefix() . "users AS u2 ON u2.id = g.last_updated_by " . "WHERE g.slug = :slug AND g.id != :id LIMIT 1;", array(':slug' => $slug, ':id' => $id));
if ($group) {
$group->roles = DBConn::selectAll("SELECT r.id, r.role, r.desc " . "FROM " . DBConn::prefix() . "auth_roles AS r " . "JOIN " . DBConn::prefix() . "auth_lookup_group_role AS look ON r.id = look.auth_role_id " . "WHERE look.auth_group_id = :id ORDER BY r.role;", array(':id' => $id));
}
return $group;
}
static function selectUserByIdentifierToken($identifier)
{
$user = DBConn::selectOne("SELECT u.id, name_first AS nameFirst, name_last AS nameLast, email, phone, token AS apiToken, identifier AS apiKey " . "FROM " . DBConn::prefix() . "tokens_auth AS t " . "JOIN " . DBConn::prefix() . "users AS u ON u.id = t.user_id " . "WHERE identifier = :identifier AND t.expires > NOW() " . "AND u.disabled IS NULL;", array(':identifier' => $identifier));
if ($user) {
$user->displayName = $user->nameFirst;
$user->roles = DBConn::selectAll("SELECT DISTINCT(gr.auth_role_id) FROM " . DBConn::prefix() . "auth_lookup_user_group AS ug " . "JOIN " . DBConn::prefix() . "auth_lookup_group_role AS gr ON ug.auth_group_id = gr.auth_group_id " . "WHERE ug.user_id = :id ORDER BY gr.auth_role_id;", array(':id' => $user->id), \PDO::FETCH_COLUMN);
}
return $user;
}
private static function selectUserData($user)
{
if ($user) {
$user->displayName = $user->nameFirst;
$user->roles = DBConn::selectAll("SELECT DISTINCT(gr.auth_role_id) " . "FROM " . DBConn::prefix() . "auth_lookup_user_group AS ug " . "JOIN " . DBConn::prefix() . "auth_lookup_group_role AS gr ON ug.auth_group_id = gr.auth_group_id " . "WHERE ug.user_id = :id;", array(':id' => $user->id), \PDO::FETCH_COLUMN);
$user->teams = DBConn::selectAll("SELECT t.id, t.name, m.joined, IFNULL(g.id, 0) AS gameId, IFNULL(g.name, '') AS game, " . "IFNULL(g.scheduled, 'false') AS gameScheduled, IFNULL(g.game_started, 'false') AS gameStarted, IFNULL(g.game_ended, 'false') AS gameEnded " . "FROM " . DBConn::prefix() . "teams AS t " . "LEFT JOIN " . DBConn::prefix() . "team_members AS m ON m.team_id = t.id " . "LEFT JOIN " . DBConn::prefix() . "games AS g ON t.current_game_id = g.id " . "WHERE m.user_id = :user_id ORDER BY t.name;", array(':user_id' => $user->id));
$user->notices = array('teamInvites' => DBConn::selectAll("SELECT i.token, i.created, i.expires, " . "i.team_id AS teamId, t.name AS teamName, i.user_id AS userId, " . "CONCAT(u.name_first, ' ', u.name_last) AS fromPlayer, u.id AS fromPlayerId " . "FROM " . DBConn::prefix() . "tokens_player_invites AS i " . "LEFT JOIN " . DBConn::prefix() . "teams AS t ON t.id = i.team_id " . "LEFT JOIN " . DBConn::prefix() . "users AS u ON u.id = i.created_user_id " . "WHERE user_id = :id AND response IS NULL AND expires > NOW() " . "AND i.team_id NOT IN (SELECT m.team_id FROM " . DBConn::prefix() . "team_members AS m WHERE i.user_id = m.user_id);", array(':id' => $user->id)));
}
return $user;
}
static function selectStoreTags()
{
return DBConn::selectAll("SELECT t.id, t.tag, t.identifier, t.description, t.created, t.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() . "store_tags AS t " . "JOIN " . DBConn::prefix() . "users AS u1 ON u1.id = t.created_user_id " . "JOIN " . DBConn::prefix() . "users AS u2 ON u2.id = t.last_updated_by;");
}
static function selectVisibilityFields()
{
return DBConn::selectAll("SELECT f.id, CONCAT('(', f.type, ') ', f.identifier) AS label " . "FROM " . DBConn::prefix() . "auth_fields AS f ORDER BY label;");
}
