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

Example 26 with QueryStatement

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

the class ActivityIndexQueries method averageActivityIndexForRetainedPlayers.

public static Query<ActivityIndex> averageActivityIndexForRetainedPlayers(long after, long before, ServerUUID serverUUID, Long threshold) {
    String selectNewUUIDs = SELECT + UserInfoTable.USER_UUID + FROM + UserInfoTable.TABLE_NAME + WHERE + UserInfoTable.REGISTERED + "<=?" + AND + UserInfoTable.REGISTERED + ">=?" + AND + UserInfoTable.SERVER_UUID + "=?";
    String selectUniqueUUIDs = SELECT + "DISTINCT " + SessionsTable.USER_UUID + FROM + SessionsTable.TABLE_NAME + WHERE + SessionsTable.SESSION_START + ">=?" + AND + SessionsTable.SESSION_END + "<=?" + AND + SessionsTable.SERVER_UUID + "=?";
    String sql = SELECT + "AVG(activity_index) as average" + FROM + '(' + selectNewUUIDs + ") n" + INNER_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;
    return new QueryStatement<ActivityIndex>(sql) {

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

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

Example 27 with QueryStatement

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

the class ActivityIndexQueries method activityIndexForNewPlayers.

public static Query<Collection<ActivityIndex>> activityIndexForNewPlayers(long after, long before, ServerUUID serverUUID, Long threshold) {
    String selectNewUUIDs = SELECT + UserInfoTable.USER_UUID + FROM + UserInfoTable.TABLE_NAME + WHERE + UserInfoTable.REGISTERED + "<=?" + AND + UserInfoTable.REGISTERED + ">=?" + AND + UserInfoTable.SERVER_UUID + "=?";
    String sql = SELECT + "activity_index" + FROM + '(' + selectNewUUIDs + ") n" + INNER_JOIN + '(' + selectActivityIndexSQL() + ") a on n." + SessionsTable.USER_UUID + "=a." + SessionsTable.USER_UUID;
    return new QueryStatement<Collection<ActivityIndex>>(sql) {

        @Override
        public void prepare(PreparedStatement statement) throws SQLException {
            statement.setLong(1, before);
            statement.setLong(2, after);
            statement.setString(3, serverUUID.toString());
            setSelectActivityIndexSQLParameters(statement, 4, threshold, serverUUID, before);
        }

        @Override
        public Collection<ActivityIndex> processResults(ResultSet set) throws SQLException {
            Collection<ActivityIndex> indexes = new ArrayList<>();
            while (set.next()) {
                indexes.add(new ActivityIndex(set.getDouble("activity_index"), before));
            }
            return indexes;
        }
    };
}

Example 28 with QueryStatement

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

the class ActivityIndexQueries method averageActivityIndexForNonRetainedPlayers.

public static Query<ActivityIndex> averageActivityIndexForNonRetainedPlayers(long after, long before, ServerUUID serverUUID, Long threshold) {
    String selectNewUUIDs = SELECT + UserInfoTable.USER_UUID + FROM + UserInfoTable.TABLE_NAME + WHERE + UserInfoTable.REGISTERED + "<=?" + AND + UserInfoTable.REGISTERED + ">=?" + AND + UserInfoTable.SERVER_UUID + "=?";
    String selectUniqueUUIDs = SELECT + "DISTINCT " + SessionsTable.USER_UUID + FROM + SessionsTable.TABLE_NAME + WHERE + SessionsTable.SESSION_START + ">=?" + AND + SessionsTable.SESSION_END + "<=?" + AND + SessionsTable.SERVER_UUID + "=?";
    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);
            statement.setString(3, serverUUID.toString());
            // Have played in the last half of the time frame
            long half = before - (before - after) / 2;
            statement.setLong(4, half);
            statement.setLong(5, before);
            statement.setString(6, serverUUID.toString());
            setSelectActivityIndexSQLParameters(statement, 7, threshold, serverUUID, before);
        }

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

Example 29 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 30 with QueryStatement

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

the class UserIdentifierQueries method fetchPlayerUUIDsOfServer.

/**
 * Query database for all player UUIDs that have joined a server.
 *
 * @param serverUUID UUID of the Plan server.
 * @return Set of UUIDs.
 */
public static Query<Set<UUID>> fetchPlayerUUIDsOfServer(ServerUUID serverUUID) {
    String sql = SELECT + UsersTable.TABLE_NAME + '.' + UsersTable.USER_UUID + ',' + FROM + UsersTable.TABLE_NAME + INNER_JOIN + UserInfoTable.TABLE_NAME + " on " + UsersTable.TABLE_NAME + '.' + UsersTable.USER_UUID + "=" + UserInfoTable.TABLE_NAME + '.' + UserInfoTable.USER_UUID + WHERE + UserInfoTable.SERVER_UUID + "=?";
    return new QueryStatement<Set<UUID>>(sql, 1000) {

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

        @Override
        public Set<UUID> processResults(ResultSet set) throws SQLException {
            Set<UUID> playerUUIDs = new HashSet<>();
            while (set.next()) {
                UUID playerUUID = UUID.fromString(set.getString(UsersTable.USER_UUID));
                playerUUIDs.add(playerUUID);
            }
            return playerUUIDs;
        }
    };
}