Examples with QueryStatement com.djrapitops.plan.storage.database.queries.QueryStatement used on opensource projects

Example 36 with QueryStatement

use of com.djrapitops.plan.storage.database.queries.QueryStatement in project Plan by plan-player-analytics.

the class SessionQueries method summaryOfPlayers.

public static Query<Map<String, Long>> summaryOfPlayers(Set<UUID> playerUUIDs, List<ServerUUID> serverUUIDs, long after, long before) {
    String selectAggregates = SELECT + "SUM(" + SessionsTable.SESSION_END + '-' + SessionsTable.SESSION_START + ") as playtime," + "SUM(" + SessionsTable.SESSION_END + '-' + SessionsTable.SESSION_START + '-' + SessionsTable.AFK_TIME + ") as active_playtime," + "COUNT(1) as session_count" + FROM + SessionsTable.TABLE_NAME + WHERE + SessionsTable.SESSION_START + ">?" + AND + SessionsTable.SESSION_END + "<?" + AND + SessionsTable.USER_UUID + " IN ('" + new TextStringBuilder().appendWithSeparators(playerUUIDs, "','").build() + "')" + (serverUUIDs.isEmpty() ? "" : AND + SessionsTable.SERVER_UUID + " IN ('" + new TextStringBuilder().appendWithSeparators(serverUUIDs, "','") + "')");
    return new QueryStatement<Map<String, Long>>(selectAggregates) {

        @Override
        public void prepare(PreparedStatement statement) throws SQLException {
            statement.setLong(1, after);
            statement.setLong(2, before);
        }

        @Override
        public Map<String, Long> processResults(ResultSet set) throws SQLException {
            if (set.next()) {
                long sessionCount = set.getLong("session_count");
                long playtime = set.getLong("playtime");
                long activePlaytime = set.getLong("active_playtime");
                int playerCount = playerUUIDs.size();
                return Maps.builder(String.class, Long.class).put("total_playtime", playtime).put("average_playtime", playerCount != 0 ? playtime / playerCount : -1L).put("total_afk_playtime", playtime - activePlaytime).put("average_afk_playtime", playerCount != 0 ? (playtime - activePlaytime) / playerCount : -1L).put("total_active_playtime", activePlaytime).put("average_active_playtime", playerCount != 0 ? activePlaytime / playerCount : -1L).put("total_sessions", sessionCount).put("average_sessions", playerCount != 0 ? sessionCount / playerCount : -1L).put("average_session_length", sessionCount != 0 ? playtime / sessionCount : -1L).build();
            } else {
                return Collections.emptyMap();
            }
        }
    };
}

Example 37 with QueryStatement

use of com.djrapitops.plan.storage.database.queries.QueryStatement in project Plan by plan-player-analytics.

the class WebUserQueries method fetchUser.

public static Query<Optional<User>> fetchUser(String username) {
    String sql = SELECT + '*' + FROM + SecurityTable.TABLE_NAME + LEFT_JOIN + UsersTable.TABLE_NAME + " on " + SecurityTable.LINKED_TO + "=" + UsersTable.USER_UUID + WHERE + SecurityTable.USERNAME + "=? LIMIT 1";
    return new QueryStatement<Optional<User>>(sql) {

        @Override
        public void prepare(PreparedStatement statement) throws SQLException {
            statement.setString(1, username);
        }

        @Override
        public Optional<User> processResults(ResultSet set) throws SQLException {
            if (set.next()) {
                String linkedTo = set.getString(UsersTable.USER_NAME);
                UUID linkedToUUID = linkedTo != null ? UUID.fromString(set.getString(SecurityTable.LINKED_TO)) : null;
                String passwordHash = set.getString(SecurityTable.SALT_PASSWORD_HASH);
                int permissionLevel = set.getInt(SecurityTable.PERMISSION_LEVEL);
                List<String> permissions = WebUser.getPermissionsForLevel(permissionLevel);
                return Optional.of(new User(username, linkedTo != null ? linkedTo : "console", linkedToUUID, passwordHash, permissionLevel, permissions));
            }
            return Optional.empty();
        }
    };
}

Example 38 with QueryStatement

use of com.djrapitops.plan.storage.database.queries.QueryStatement in project Plan by plan-player-analytics.

the class WebUserQueries method fetchActiveCookies.

public static Query<Map<String, User>> fetchActiveCookies() {
    String sql = SELECT + '*' + FROM + CookieTable.TABLE_NAME + INNER_JOIN + SecurityTable.TABLE_NAME + " on " + CookieTable.TABLE_NAME + '.' + CookieTable.WEB_USERNAME + '=' + SecurityTable.TABLE_NAME + '.' + SecurityTable.USERNAME + LEFT_JOIN + UsersTable.TABLE_NAME + " on " + SecurityTable.LINKED_TO + "=" + UsersTable.USER_UUID + WHERE + CookieTable.EXPIRES + ">?";
    return new QueryStatement<Map<String, User>>(sql) {

        @Override
        public void prepare(PreparedStatement statement) throws SQLException {
            statement.setLong(1, System.currentTimeMillis());
        }

        @Override
        public Map<String, User> processResults(ResultSet set) throws SQLException {
            Map<String, User> usersByCookie = new HashMap<>();
            while (set.next()) {
                String cookie = set.getString(CookieTable.COOKIE);
                User user = extractUser(set);
                usersByCookie.put(cookie, user);
            }
            return usersByCookie;
        }
    };
}

Example 39 with QueryStatement

use of com.djrapitops.plan.storage.database.queries.QueryStatement in project Plan by plan-player-analytics.

the class WebUserQueries method fetchUser.

public static Query<Optional<User>> fetchUser(UUID linkedToUUID) {
    String sql = SELECT + '*' + FROM + SecurityTable.TABLE_NAME + LEFT_JOIN + UsersTable.TABLE_NAME + " on " + SecurityTable.LINKED_TO + "=" + UsersTable.USER_UUID + WHERE + SecurityTable.LINKED_TO + "=? LIMIT 1";
    return new QueryStatement<Optional<User>>(sql) {

        @Override
        public void prepare(PreparedStatement statement) throws SQLException {
            if (linkedToUUID == null) {
                statement.setNull(1, Types.VARCHAR);
            } else {
                statement.setString(1, linkedToUUID.toString());
            }
        }

        @Override
        public Optional<User> processResults(ResultSet set) throws SQLException {
            if (set.next()) {
                String username = set.getString(SecurityTable.USERNAME);
                String linkedTo = set.getString(UsersTable.USER_NAME);
                String passwordHash = set.getString(SecurityTable.SALT_PASSWORD_HASH);
                int permissionLevel = set.getInt(SecurityTable.PERMISSION_LEVEL);
                List<String> permissions = WebUser.getPermissionsForLevel(permissionLevel);
                return Optional.of(new User(username, linkedTo != null ? linkedTo : "console", linkedToUUID, passwordHash, permissionLevel, permissions));
            }
            return Optional.empty();
        }
    };
}

Example 40 with QueryStatement

use of com.djrapitops.plan.storage.database.queries.QueryStatement in project Plan by plan-player-analytics.

the class NetworkActivityIndexQueries method averageActivityIndexForNonRetainedPlayers.

public static Query<ActivityIndex> averageActivityIndexForNonRetainedPlayers(long after, long before, Long threshold) {
    String selectNewUUIDs = SELECT + UsersTable.USER_UUID + FROM + UsersTable.TABLE_NAME + WHERE + UsersTable.REGISTERED + "<=?" + AND + UsersTable.REGISTERED + ">=?";
    String selectUniqueUUIDs = SELECT + "DISTINCT " + SessionsTable.USER_UUID + FROM + SessionsTable.TABLE_NAME + WHERE + SessionsTable.SESSION_START + ">=?" + AND + SessionsTable.SESSION_END + "<=?";
    String sql = SELECT + "AVG(activity_index) as average" + FROM + '(' + selectNewUUIDs + ") n" + LEFT_JOIN + '(' + selectUniqueUUIDs + ") u on n." + SessionsTable.USER_UUID + "=u." + SessionsTable.USER_UUID + INNER_JOIN + '(' + selectActivityIndexSQL() + ") a on n." + SessionsTable.USER_UUID + "=a." + SessionsTable.USER_UUID + WHERE + "n." + SessionsTable.USER_UUID + IS_NULL;
    return new QueryStatement<ActivityIndex>(sql) {

        @Override
        public void prepare(PreparedStatement statement) throws SQLException {
            statement.setLong(1, before);
            statement.setLong(2, after);
            // Have played in the last half of the time frame
            long half = before - (before - after) / 2;
            statement.setLong(3, half);
            statement.setLong(4, before);
            setSelectActivityIndexSQLParameters(statement, 5, threshold, before);
        }

        @Override
        public ActivityIndex processResults(ResultSet set) throws SQLException {
            return set.next() ? new ActivityIndex(set.getDouble("average"), before) : new ActivityIndex(0.0, before);
        }
    };
}