diff --git a/src/main/kotlin/net/taehui/twilight/system/DB.kt b/src/main/kotlin/net/taehui/twilight/system/DB.kt index 2ced76d..574fc60 100644 --- a/src/main/kotlin/net/taehui/twilight/system/DB.kt +++ b/src/main/kotlin/net/taehui/twilight/system/DB.kt @@ -2665,259 +2665,283 @@ } } - fun getWow(): CompletableFuture { - val totalAvatars = mutableListOf() - val totalAvatarsAt = mutableListOf() - val highestAvatars = mutableListOf() - val highestAvatarsAt = mutableListOf() - val standAvatars = mutableListOf() - val standAvatarsAt = mutableListOf() - val bandAvatars = mutableListOf() - val bandAvatarsAt = mutableListOf() - val ability5KAvatars = mutableListOf() - val ability7KAvatars = mutableListOf() - val ability9KAvatars = mutableListOf() - val levelAvatars = mutableListOf() - val levyingDate = yyyyMMDDFormat.format(LocalDate.now().withDayOfMonth(1)) - val defaultFilter = "tw_comment.Avatar = tn_avatar.Avatar_ID" - val dateFilter = "'$levyingDate' <= tw_comment.Date" - fun getTotalAvatarsStatement(filter: String): String { - return """ - SELECT Avatar_ID, Avatar_Name, COUNT(tw_comment.Note_ID) AS Value - FROM tw_comment, tn_avatar - WHERE $filter - GROUP BY Avatar_ID - ORDER BY Value DESC - LIMIT 10 - """.trimIndent() - } - - fun getHighestAvatarsStatement(filter: String): String { - return """ - SELECT Avatar_ID, Avatar_Name, COUNT(DISTINCT tw_comment.Note_ID) AS Value - FROM tw_comment, tn_avatar - WHERE $filter - GROUP BY Avatar_ID - ORDER BY Value DESC - LIMIT 10 - """.trimIndent() - } - - fun getStandAvatarsStatement(filter: String): String { - return """ - SELECT Avatar_ID, Avatar_Name, SUM(Stand) AS Value - FROM tw_comment, tn_avatar - WHERE $filter AND Is_Max = true - GROUP BY Avatar_ID - ORDER BY Value DESC - LIMIT 10 - """.trimIndent() - } - - fun getBandAvatarsStatement(filter: String): String { - return """ - SELECT Avatar_ID, Avatar_Name, MAX(Band) AS Value - FROM tw_comment, tn_avatar - WHERE $filter - GROUP BY Avatar_ID - ORDER BY Value DESC - LIMIT 10 - """.trimIndent() - } - - return CompletableFuture.allOf( - logValueFuture { - pool.connection.use { - it.prepareStatement(getTotalAvatarsStatement(defaultFilter)).use { dbStatement -> - dbStatement.executeQuery().use { rows -> - while (rows.next()) { - totalAvatars.add(object { - val avatarID = rows.getString("Avatar_ID") - val avatarName = rows.getString("Avatar_Name") - val value = rows.getLong("Value") - }) - } + fun getHOFTotalTotal(): CompletableFuture { + return logValueFuture { + pool.connection.use { + it.prepareStatement(""" + SELECT Avatar_ID, Avatar_Name, COUNT(tw_comment.Note_ID) AS Value + FROM tw_comment, tn_avatar + WHERE tw_comment.Avatar = tn_avatar.Avatar_ID + GROUP BY Avatar_ID + ORDER BY Value DESC + LIMIT 10 + """.trimIndent()).use { dbStatement -> + dbStatement.executeQuery().use { rows -> + val data = mutableListOf() + while (rows.next()) { + data.add(object { + val avatarID = rows.getString("Avatar_ID") + val avatarName = rows.getString("Avatar_Name") + val value = rows.getLong("Value") + }) } - } - } - }, - logValueFuture { - pool.connection.use { - it.prepareStatement(getTotalAvatarsStatement("$dateFilter AND $defaultFilter ")) - .use { dbStatement -> - dbStatement.executeQuery().use { rows -> - while (rows.next()) { - totalAvatarsAt.add(object { - val avatarID = rows.getString("Avatar_ID") - val avatarName = rows.getString("Avatar_Name") - val value = rows.getLong("Value") - }) - } - } - } - } - }, - logValueFuture { - pool.connection.use { - it.prepareStatement(getHighestAvatarsStatement(defaultFilter)).use { dbStatement -> - dbStatement.executeQuery().use { rows -> - while (rows.next()) { - highestAvatars.add(object { - val avatarID = rows.getString("Avatar_ID") - val avatarName = rows.getString("Avatar_Name") - val value = rows.getLong("Value") - }) - } - } - } - } - }, - logValueFuture { - pool.connection.use { - it.prepareStatement( - getHighestAvatarsStatement( - "$dateFilter AND $defaultFilter AND Is_Max = true" - ) - ).use { dbStatement -> - dbStatement.executeQuery().use { rows -> - while (rows.next()) { - highestAvatarsAt.add(object { - val avatarID = rows.getString("Avatar_ID") - val avatarName = rows.getString("Avatar_Name") - val value = rows.getLong("Value") - }) - } - } - } - } - }, - logValueFuture { - pool.connection.use { - it.prepareStatement(getStandAvatarsStatement(defaultFilter)).use { dbStatement -> - dbStatement.executeQuery().use { rows -> - while (rows.next()) { - standAvatars.add(object { - val avatarID = rows.getString("Avatar_ID") - val avatarName = rows.getString("Avatar_Name") - val value = rows.getLong("Value") - }) - } - } - } - } - }, - logValueFuture { - pool.connection.use { - it.prepareStatement(getStandAvatarsStatement("$dateFilter AND $defaultFilter")) - .use { dbStatement -> - dbStatement.executeQuery().use { rows -> - while (rows.next()) { - standAvatarsAt.add(object { - val avatarID = rows.getString("Avatar_ID") - val avatarName = rows.getString("Avatar_Name") - val value = rows.getLong("Value") - }) - } - } - } - } - }, - logValueFuture { - pool.connection.use { - it.prepareStatement(getBandAvatarsStatement(defaultFilter)).use { dbStatement -> - dbStatement.executeQuery().use { rows -> - while (rows.next()) { - bandAvatars.add(object { - val avatarID = rows.getString("Avatar_ID") - val avatarName = rows.getString("Avatar_Name") - val value = rows.getLong("Value") - }) - } - } - } - } - }, - logValueFuture { - pool.connection.use { - it.prepareStatement(getBandAvatarsStatement("$dateFilter AND $defaultFilter")) - .use { dbStatement -> - dbStatement.executeQuery().use { rows -> - while (rows.next()) { - bandAvatarsAt.add(object { - val avatarID = rows.getString("Avatar_ID") - val avatarName = rows.getString("Avatar_Name") - val value = rows.getLong("Value") - }) - } - } - } - } - }, - logValueFuture { - ability5KAvatars.addAll(getAbilities(getAbilities5K()).limit(50).map { (avatar, abilities) -> - object { - val avatarID = avatar.first - val avatarName = avatar.second - val value = abilities.first - } - }.toList()) - }, - logValueFuture { - ability7KAvatars.addAll(getAbilities(getAbilities7K()).limit(50).map { (avatar, abilities) -> - object { - val avatarID = avatar.first - val avatarName = avatar.second - val value = abilities.first - } - }.toList()) - }, - logValueFuture { - ability9KAvatars.addAll(getAbilities(getAbilities9K()).limit(50).map { (avatar, abilities) -> - object { - val avatarID = avatar.first - val avatarName = avatar.second - val value = abilities.first - } - }.toList()) - }, - logValueFuture { - pool.connection.use { - it.prepareStatement( - """ - SELECT Avatar_ID, Avatar_Name, 1 + SUM(Total_Notes * Stand / 1000000 / 10000) AS Value - FROM tw_note, tw_comment, tn_avatar - WHERE tw_note.Note_ID = tw_comment.Note_ID AND tw_comment.Avatar = tn_avatar.Avatar_ID AND Is_Salt = false - GROUP BY Avatar_ID - ORDER BY Value DESC - LIMIT 50 - """.trimIndent() - ).use { dbStatement -> - dbStatement.executeQuery().use { rows -> - while (rows.next()) { - levelAvatars.add(object { - val avatarID = rows.getString("Avatar_ID") - val avatarName = rows.getString("Avatar_Name") - val value = rows.getLong("Value") - }) - } - } + data } } } - ).thenApply { - object { - val totalAvatars = totalAvatars - val totalAvatarsAt = totalAvatarsAt - val highestAvatars = highestAvatars - val highestAvatarsAt = highestAvatarsAt - val standAvatars = standAvatars - val standAvatarsAt = standAvatarsAt - val bandAvatars = bandAvatars - val bandAvatarsAt = bandAvatarsAt - val ability5KAvatars = ability5KAvatars - val ability7KAvatars = ability7KAvatars - val ability9KAvatars = ability9KAvatars - val levelAvatars = levelAvatars + } + } + + fun getHOFAtTotal(): CompletableFuture { + return logValueFuture { + pool.connection.use { + it.prepareStatement(""" + SELECT Avatar_ID, Avatar_Name, COUNT(tw_comment.Note_ID) AS Value + FROM tw_comment, tn_avatar + WHERE ${yyyyMMDDFormat.format(LocalDate.now().withDayOfMonth(1))}' <= tw_comment.Date AND tw_comment.Avatar = tn_avatar.Avatar_ID + GROUP BY Avatar_ID + ORDER BY Value DESC + LIMIT 10 + """.trimIndent()).use { dbStatement -> + dbStatement.executeQuery().use { rows -> + val data = mutableListOf() + while (rows.next()) { + data.add(object { + val avatarID = rows.getString("Avatar_ID") + val avatarName = rows.getString("Avatar_Name") + val value = rows.getLong("Value") + }) + } + data + } + } + } + } + } + + fun getHOFTotalHighest(): CompletableFuture { + return logValueFuture { + pool.connection.use { + it.prepareStatement(""" + SELECT Avatar_ID, Avatar_Name, COUNT(DISTINCT tw_comment.Note_ID) AS Value + FROM tw_comment, tn_avatar + WHERE tw_comment.Avatar = tn_avatar.Avatar_ID AND Is_Max = true + GROUP BY Avatar_ID + ORDER BY Value DESC + LIMIT 10 + """.trimIndent()).use { dbStatement -> + dbStatement.executeQuery().use { rows -> + val data = mutableListOf() + while (rows.next()) { + data.add(object { + val avatarID = rows.getString("Avatar_ID") + val avatarName = rows.getString("Avatar_Name") + val value = rows.getLong("Value") + }) + } + data + } + } + } + } + } + + fun getHOFAtHighest(): CompletableFuture { + return logValueFuture { + pool.connection.use { + it.prepareStatement(""" + SELECT Avatar_ID, Avatar_Name, COUNT(DISTINCT tw_comment.Note_ID) AS Value + FROM tw_comment, tn_avatar + WHERE ${yyyyMMDDFormat.format(LocalDate.now().withDayOfMonth(1))}' <= tw_comment.Date AND tw_comment.Avatar = tn_avatar.Avatar_ID AND Is_Max = true + GROUP BY Avatar_ID + ORDER BY Value DESC + LIMIT 10 + """.trimIndent()).use { dbStatement -> + dbStatement.executeQuery().use { rows -> + val data = mutableListOf() + while (rows.next()) { + data.add(object { + val avatarID = rows.getString("Avatar_ID") + val avatarName = rows.getString("Avatar_Name") + val value = rows.getLong("Value") + }) + } + data + } + } + } + } + } + + fun getHOFTotalStand(): CompletableFuture { + return logValueFuture { + pool.connection.use { + it.prepareStatement(""" + SELECT Avatar_ID, Avatar_Name, SUM(Stand) AS Value + FROM tw_comment, tn_avatar + WHERE tw_comment.Avatar = tn_avatar.Avatar_ID AND Is_Max = true + GROUP BY Avatar_ID + ORDER BY Value DESC + LIMIT 10 + """.trimIndent()).use { dbStatement -> + dbStatement.executeQuery().use { rows -> + val data = mutableListOf() + while (rows.next()) { + data.add(object { + val avatarID = rows.getString("Avatar_ID") + val avatarName = rows.getString("Avatar_Name") + val value = rows.getLong("Value") + }) + } + data + } + } + } + } + } + + fun getHOFAtStand(): CompletableFuture { + return logValueFuture { + pool.connection.use { + it.prepareStatement(""" + SELECT Avatar_ID, Avatar_Name, SUM(Stand) AS Value + FROM tw_comment, tn_avatar + WHERE ${yyyyMMDDFormat.format(LocalDate.now().withDayOfMonth(1))}' <= tw_comment.Date AND tw_comment.Avatar = tn_avatar.Avatar_ID AND Is_Max = true + GROUP BY Avatar_ID + ORDER BY Value DESC + LIMIT 10 + """.trimIndent()).use { dbStatement -> + dbStatement.executeQuery().use { rows -> + val data = mutableListOf() + while (rows.next()) { + data.add(object { + val avatarID = rows.getString("Avatar_ID") + val avatarName = rows.getString("Avatar_Name") + val value = rows.getLong("Value") + }) + } + data + } + } + } + } + } + + fun getHOFTotalBand(): CompletableFuture { + return logValueFuture { + pool.connection.use { + it.prepareStatement(""" + SELECT Avatar_ID, Avatar_Name, MAX(Band) AS Value + FROM tw_comment, tn_avatar + WHERE tw_comment.Avatar = tn_avatar.Avatar_ID AND Is_Max = true + GROUP BY Avatar_ID + ORDER BY Value DESC + LIMIT 10 + """.trimIndent()).use { dbStatement -> + dbStatement.executeQuery().use { rows -> + val data = mutableListOf() + while (rows.next()) { + data.add(object { + val avatarID = rows.getString("Avatar_ID") + val avatarName = rows.getString("Avatar_Name") + val value = rows.getLong("Value") + }) + } + data + } + } + } + } + } + + fun getHOFAtBand(): CompletableFuture { + return logValueFuture { + pool.connection.use { + it.prepareStatement(""" + SELECT Avatar_ID, Avatar_Name, MAX(Band) AS Value + FROM tw_comment, tn_avatar + WHERE ${yyyyMMDDFormat.format(LocalDate.now().withDayOfMonth(1))}' <= tw_comment.Date AND tw_comment.Avatar = tn_avatar.Avatar_ID AND Is_Max = true + GROUP BY Avatar_ID + ORDER BY Value DESC + LIMIT 10 + """.trimIndent()).use { dbStatement -> + dbStatement.executeQuery().use { rows -> + val data = mutableListOf() + while (rows.next()) { + data.add(object { + val avatarID = rows.getString("Avatar_ID") + val avatarName = rows.getString("Avatar_Name") + val value = rows.getLong("Value") + }) + } + data + } + } + } + } + } + + fun getHOFAbility5K(): CompletableFuture { + return logValueFuture { + getAbilities(getAbilities5K()).limit(50).map { (avatar, abilities) -> + object { + val avatarID = avatar.first + val avatarName = avatar.second + val value = abilities.first + } + } + } + } + + fun getHOFAbility7K(): CompletableFuture { + return logValueFuture { + getAbilities(getAbilities7K()).limit(50).map { (avatar, abilities) -> + object { + val avatarID = avatar.first + val avatarName = avatar.second + val value = abilities.first + } + } + } + } + + fun getHOFAbility9K(): CompletableFuture { + return logValueFuture { + getAbilities(getAbilities9K()).limit(50).map { (avatar, abilities) -> + object { + val avatarID = avatar.first + val avatarName = avatar.second + val value = abilities.first + } + } + } + } + + fun getHOFLevel(): CompletableFuture { + return logValueFuture { + pool.connection.use { + it.prepareStatement( + """ + SELECT Avatar_ID, Avatar_Name, 1 + SUM(Total_Notes * Stand / 1000000 / 10000) AS Value + FROM tw_note, tw_comment, tn_avatar + WHERE tw_note.Note_ID = tw_comment.Note_ID AND tw_comment.Avatar = tn_avatar.Avatar_ID AND Is_Salt = false + GROUP BY Avatar_ID + ORDER BY Value DESC + LIMIT 50 + """.trimIndent() + ).use { dbStatement -> + dbStatement.executeQuery().use { rows -> + val data = mutableListOf() + while (rows.next()) { + data.add(object { + val avatarID = rows.getString("Avatar_ID") + val avatarName = rows.getString("Avatar_Name") + val value = rows.getLong("Value") + }) + } + data + } + } } } } diff --git a/src/main/kotlin/net/taehui/twilight/system/PlatformSystem.kt b/src/main/kotlin/net/taehui/twilight/system/PlatformSystem.kt index 2116ac4..5cd0950 100644 --- a/src/main/kotlin/net/taehui/twilight/system/PlatformSystem.kt +++ b/src/main/kotlin/net/taehui/twilight/system/PlatformSystem.kt @@ -6,6 +6,7 @@ import net.dv8tion.jda.api.JDA import net.dv8tion.jda.api.JDABuilder import net.dv8tion.jda.api.audio.AudioReceiveHandler +import net.dv8tion.jda.api.audio.AudioSendHandler import net.dv8tion.jda.api.audio.CombinedAudio import net.dv8tion.jda.api.entities.Guild import net.dv8tion.jda.api.entities.Member @@ -19,17 +20,16 @@ import net.dv8tion.jda.api.events.message.MessageUpdateEvent import net.dv8tion.jda.api.hooks.ListenerAdapter import net.dv8tion.jda.api.requests.GatewayIntent -import net.taehui.twilight.HCDataHandler -import net.taehui.twilight.JSON -import net.taehui.twilight.Logger -import net.taehui.twilight.Twilight +import net.taehui.twilight.* import org.apache.hc.client5.http.classic.methods.HttpGet import org.apache.hc.client5.http.impl.classic.HttpClients +import java.nio.ByteBuffer import java.text.NumberFormat import java.util.* import java.util.concurrent.CompletableFuture import java.util.concurrent.ConcurrentHashMap import javax.annotation.Nonnull +import kotlin.random.Random object PlatformSystem : Logger { class Client { @@ -126,6 +126,8 @@ }).build().awaitReady() qwilightPlatform = platform?.getGuildById(platformClient.qwilight) doCallPlatformAvatars() + + qwilightPlatform?.audioManager?.openAudioConnection(qwilightPlatform?.getVoiceChannelById(platformClient.siteAudio)) } catch (e: Throwable) { logFault(e) } @@ -175,16 +177,22 @@ "@Ability" -> { val twilightAbilitySiteYell = ObjectMapper().readValue(siteYellData.siteYell, JSON.TwilightAbilitySiteYell::class.java) + val inputMode = when (twilightAbilitySiteYell.inputMode) { + Component.InputMode.INPUT_MODE_51 -> "⑤K" + Component.InputMode.INPUT_MODE_71 -> "⑦K" + Component.InputMode.INPUT_MODE_9 -> "9K" + else -> "" + } val ability = twilightAbilitySiteYell.ability site?.sendMessage( if (ability < 0.01) String.format( platformClient.siteYellAbilityMini, twilightAbilitySiteYell.avatarName, - twilightAbilitySiteYell.inputMode + inputMode ) else String.format( platformClient.siteYellAbility, twilightAbilitySiteYell.avatarName, - twilightAbilitySiteYell.inputMode, + inputMode, ability ) ) diff --git a/src/main/kotlin/net/taehui/twilight/www/WwwAvatar.kt b/src/main/kotlin/net/taehui/twilight/www/WwwAvatar.kt index 07b8b86..babdf77 100644 --- a/src/main/kotlin/net/taehui/twilight/www/WwwAvatar.kt +++ b/src/main/kotlin/net/taehui/twilight/www/WwwAvatar.kt @@ -187,7 +187,18 @@ } } - "/qwilight/www/wow" -> DB.getWow().thenAccept { send(ctx, it) } + "/qwilight/www/hof/totalTotal" -> DB.getHOFTotalTotal().thenAccept { send(ctx, it) } + "/qwilight/www/hof/atTotal" -> DB.getHOFAtTotal().thenAccept { send(ctx, it) } + "/qwilight/www/hof/totalHighest" -> DB.getHOFTotalHighest().thenAccept { send(ctx, it) } + "/qwilight/www/hof/atHighest" -> DB.getHOFAtHighest().thenAccept { send(ctx, it) } + "/qwilight/www/hof/totalStand" -> DB.getHOFTotalStand().thenAccept { send(ctx, it) } + "/qwilight/www/hof/atStand" -> DB.getHOFAtStand().thenAccept { send(ctx, it) } + "/qwilight/www/hof/totalBand" -> DB.getHOFTotalBand().thenAccept { send(ctx, it) } + "/qwilight/www/hof/atBand" -> DB.getHOFAtBand().thenAccept { send(ctx, it) } + "/qwilight/www/hof/ability5K" -> DB.getHOFAbility5K().thenAccept { send(ctx, it) } + "/qwilight/www/hof/ability7K" -> DB.getHOFAbility7K().thenAccept { send(ctx, it) } + "/qwilight/www/hof/ability9K" -> DB.getHOFAbility9K().thenAccept { send(ctx, it) } + "/qwilight/www/hof/level" -> DB.getHOFLevel().thenAccept { send(ctx, it) } "/qwilight/www/etc" -> { DB.getEtc(language).thenAccept { send(ctx, it) }