package net.taehui.twilight.system
import CommentOuterClass
import com.fasterxml.jackson.annotation.JsonIgnore
import net.taehui.twilight.*
import net.taehui.twilight.BundleIO.BundleVariety
import net.taehui.twilight.Component
import net.taehui.twilight.qwilight.QwilightAvatar
import org.apache.commons.compress.compressors.xz.XZCompressorInputStream
import org.apache.commons.dbcp2.BasicDataSource
import org.apache.commons.io.FileUtils
import org.apache.commons.io.FilenameUtils
import java.io.IOException
import java.lang.IllegalArgumentException
import java.nio.file.Files
import java.nio.file.Path
import java.sql.*
import java.time.Instant
import java.time.LocalDate
import java.time.ZoneId
import java.time.format.DateTimeFormatter
import java.util.*
import java.util.concurrent.CompletableFuture
import java.util.concurrent.atomic.AtomicInteger
import java.util.stream.Stream
import java.util.stream.StreamSupport
import kotlin.collections.ArrayList
import kotlin.math.ceil
import kotlin.math.max
import kotlin.math.pow
object DB : Logger {
private fun getAbilityFilter(vararg inputFavorModes: Int): String {
return "Judgment_Mode IN (${Component.DEFAULT_JUDGMENT_MODE}, ${Component.HIGHER_JUDGMENT_MODE}, ${Component.HIGHEST_JUDGMENT_MODE}) AND Hit_Points_Mode IN (${Component.DEFAULT_HIT_POINTS_MODE}, ${Component.HIGHER_HIT_POINTS_MODE}, ${Component.HIGHEST_HIT_POINTS_MODE}, ${Component.FAILED_HIT_POINTS_MODE}) AND Long_Note_Mode = ${Component.DEFAULT_LONG_NOTE_MODE} AND Input_Favor_Mode IN (${
inputFavorModes.joinToString()
}) AND Note_Modify_Mode = ${Component.DEFAULT_NOTE_MODIFY_MODE}"
}
private val pool = BasicDataSource().apply {
maxTotal = Int.MAX_VALUE
}
private val yyyyMMFormat = DateTimeFormatter.ofPattern("yyyy-MM").withZone(ZoneId.systemDefault())
private val yyyyMMDDFormat = DateTimeFormatter.ofPattern("yyyy-MM-dd").withZone(ZoneId.systemDefault())
fun getSiteYells(siteID: String): Collection<SiteYell> {
return pool.connection.use {
it.prepareStatement(
"""
SELECT Avatar_ID, Avatar_Name, Date, Site_Yell, Platform_ID, Translate
FROM tw_site
WHERE Site_ID = ?
ORDER BY Date
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, siteID)
dbStatement.executeQuery().use { rows ->
val data = LinkedList<SiteYell>()
var siteYellID = -1
while (rows.next()) {
data.add(
SiteYell(
rows.getString("Avatar_ID"),
rows.getString("Avatar_Name"),
rows.getLong("Date"),
rows.getString("Site_Yell"),
++siteYellID,
rows.getLong("Platform_ID"),
rows.getBoolean("Translate")
)
)
}
data
}
}
}
}
fun loadDB() {
val db = Configure.db
pool.url = "jdbc:mariadb://" + db.remote + "/" + db.db
pool.username = db.avatar
pool.password = db.auth
logInfo("Loaded DB")
pool.connection.use {
it.createStatement().use { dbStatement ->
dbStatement.executeUpdate(
"""
CREATE TABLE IF NOT EXISTS tw_note (
Note_ID VARCHAR(139) NOT NULL,
Note_ID_128 CHAR(32) NOT NULL,
Note_ID_256 CHAR(64) NOT NULL,
Note_Variety INTEGER NOT NULL,
Artist TEXT NOT NULL,
Title TEXT NOT NULL,
Genre TEXT NOT NULL,
Level_Text TEXT NOT NULL,
Level INTEGER NOT NULL,
Input_Mode INTEGER NOT NULL,
Total_Notes INTEGER NOT NULL,
Is_Salt BOOLEAN NOT NULL,
Ability_6K REAL NOT NULL,
Ability_5K REAL NOT NULL,
Ability_7K REAL NOT NULL,
Ability_9K REAL NOT NULL,
Length REAL NOT NULL,
Total_Count INTEGER NOT NULL,
Top_Count INTEGER NOT NULL,
Date DATETIME NOT NULL,
PRIMARY KEY (Note_ID),
KEY (Note_Variety),
FULLTEXT (Artist),
FULLTEXT (Title),
FULLTEXT (Genre),
KEY (Is_Salt),
KEY (Ability_6K),
KEY (Ability_5K),
KEY (Ability_7K),
KEY (Ability_9K),
KEY (Total_Count),
KEY (Top_Count),
KEY (Date)
) COLLATE=utf8mb4_general_ci ENGINE=InnoDB
""".trimIndent()
)
dbStatement.executeUpdate(
"""
CREATE TABLE IF NOT EXISTS tw_comment (
Date DATETIME NOT NULL,
Note_ID VARCHAR(139) NOT NULL,
Avatar VARCHAR(20) NOT NULL,
Multiplier REAL,
Auto_Mode INTEGER,
Note_Salt_Mode INTEGER,
Audio_Multiplier REAL,
Faint_Note_Mode INTEGER,
Judgment_Mode INTEGER,
Hit_Points_Mode INTEGER,
Note_Mobility_Mode INTEGER,
Long_Note_Mode INTEGER,
Input_Favor_Mode INTEGER,
Note_Modify_Mode INTEGER,
Lowest_Judgment_Condition_Mode INTEGER,
Stand INTEGER,
Band INTEGER,
Is_Band1 BOOLEAN,
Point REAL,
Salt INTEGER,
Comment_ID CHAR(128) NOT NULL,
Is_Max BOOLEAN,
Is_Paused BOOLEAN,
Input_Flags INTEGER NOT NULL,
Lowest_Audio_Multiplier REAL,
Highest_Audio_Multiplier REAL,
FOREIGN KEY (Avatar) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE,
KEY (Date),
KEY (Note_ID),
KEY (Avatar),
KEY (Judgment_Mode),
KEY (Hit_Points_Mode),
KEY (Input_Favor_Mode),
KEY (Stand),
KEY (Comment_ID),
KEY (Is_Max),
CHECK (Input_Flags >= 0 AND Input_Flags <= 15)
) COLLATE=utf8mb4_general_ci ENGINE=InnoDB
""".trimIndent()
)
dbStatement.executeUpdate(
"""
CREATE TABLE IF NOT EXISTS tw_handled (
Avatar VARCHAR(20) NOT NULL,
Note_ID VARCHAR(139) NOT NULL,
Handled INTEGER NOT NULL,
PRIMARY KEY (Avatar, Note_ID),
FOREIGN KEY (Avatar) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE
) COLLATE=utf8mb4_general_ci ENGINE=InnoDB
""".trimIndent()
)
dbStatement.executeUpdate(
"""
CREATE TABLE IF NOT EXISTS tw_bundle (
Avatar VARCHAR(20) NOT NULL,
Date DATETIME NOT NULL,
Name VARCHAR(191) NOT NULL,
Length LONG NOT NULL,
Competence INTEGER NOT NULL,
Variety INTEGER NOT NULL,
Etc TEXT NOT NULL,
PRIMARY KEY (Avatar, Name),
FOREIGN KEY (Avatar) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE,
CHECK (Competence IN (0, 1, 2)),
CHECK (Variety IN (0, 1, 2))
) COLLATE=utf8mb4_general_ci ENGINE=InnoDB
""".trimIndent()
)
dbStatement.executeUpdate(
"""
CREATE TABLE IF NOT EXISTS tw_ubuntu (
Avatar VARCHAR(20) NOT NULL,
Ubuntu VARCHAR(20) NOT NULL,
PRIMARY KEY (Avatar, Ubuntu),
FOREIGN KEY (Avatar) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (Ubuntu) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE
) COLLATE=utf8mb4_general_ci ENGINE=InnoDB
""".trimIndent()
)
dbStatement.executeUpdate(
"""
CREATE TABLE IF NOT EXISTS tw_configure (
Avatar VARCHAR(20) NOT NULL,
Silent_Site INTEGER,
Notify_Ubuntu INTEGER,
Default_Bundle_Competence INTEGER,
IO_Competence INTEGER,
Notify_Save_Bundle INTEGER,
Notify_Pass INTEGER,
PRIMARY KEY (Avatar),
FOREIGN KEY (Avatar) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE,
KEY (Notify_Ubuntu),
CHECK (Silent_Site IN (0, 1, 2, 3)),
CHECK (Notify_Ubuntu IN (0, 1)),
CHECK (Default_Bundle_Competence IN (0, 1, 2, 3)),
CHECK (IO_Competence IN (0, 1, 2, 3)),
CHECK (Notify_Save_Bundle IN (0, 1, 2, 3)),
CHECK (Notify_Pass IN (0, 1, 2))
) COLLATE=utf8mb4_general_ci ENGINE=InnoDB
""".trimIndent()
)
dbStatement.executeUpdate(
"""
CREATE TABLE IF NOT EXISTS tw_avatar (
Avatar VARCHAR(20) NOT NULL,
Avatar_XP REAL NOT NULL,
PRIMARY KEY (Avatar),
FOREIGN KEY (Avatar) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE
) COLLATE=utf8mb4_general_ci ENGINE=InnoDB
""".trimIndent()
)
dbStatement.executeUpdate(
"""
CREATE TABLE IF NOT EXISTS tw_title (
Avatar VARCHAR(20) NOT NULL,
Title_ID CHAR(36) NOT NULL,
PRIMARY KEY (Avatar),
FOREIGN KEY (Avatar) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE
) COLLATE=utf8mb4_general_ci ENGINE=InnoDB
""".trimIndent()
)
dbStatement.executeUpdate(
"""
CREATE TABLE IF NOT EXISTS tw_edge (
Avatar VARCHAR(20) NOT NULL,
Edge_ID CHAR(36) NOT NULL,
PRIMARY KEY (Avatar),
FOREIGN KEY (Avatar) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE,
KEY (Edge_ID)
) COLLATE=utf8mb4_general_ci ENGINE=InnoDB
""".trimIndent()
)
dbStatement.executeUpdate(
"""
CREATE TABLE IF NOT EXISTS tw_site (
Site_ID CHAR(36) NOT NULL,
Avatar_ID VARCHAR(20) NOT NULL,
Avatar_Name VARCHAR(255) NOT NULL,
Date LONG NOT NULL,
Site_Yell TEXT NOT NULL,
Platform_ID LONG NOT NULL,
Translate BOOLEAN NOT NULL,
KEY (Site_ID),
KEY (Platform_ID)
) COLLATE=utf8mb4_general_ci ENGINE=InnoDB
""".trimIndent()
)
dbStatement.executeUpdate(
"""
CREATE TABLE IF NOT EXISTS tw_level (
Avatar VARCHAR(20) NOT NULL,
Level_ID CHAR(36) NOT NULL,
Date DATETIME NOT NULL,
PRIMARY KEY (Avatar, Level_ID),
FOREIGN KEY (Avatar) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE
) COLLATE=utf8mb4_general_ci ENGINE=InnoDB
""".trimIndent()
)
dbStatement.executeUpdate(
"""
CREATE TABLE IF NOT EXISTS tw_commentary (
Note_ID VARCHAR(139) NOT NULL,
Avatar VARCHAR(20) NOT NULL,
Commentary TEXT NOT NULL,
PRIMARY KEY (Avatar, Note_ID),
FOREIGN KEY (Avatar) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE,
KEY (Note_ID)
) COLLATE=utf8mb4_general_ci ENGINE=InnoDB
""".trimIndent()
)
dbStatement.executeUpdate(
"""
CREATE TABLE IF NOT EXISTS tw_translated (
Text VARCHAR(766) NOT NULL,
Target_Language VARCHAR(5) NOT NULL,
Translated_Text TEXT NOT NULL,
PRIMARY KEY (Text, Target_Language)
) COLLATE=utf8mb4_general_ci ENGINE=InnoDB
""".trimIndent()
)
dbStatement.executeUpdate(
"""
CREATE TABLE IF NOT EXISTS tw_favor (
Note_ID VARCHAR(139) NOT NULL,
Avatar VARCHAR(20) NOT NULL,
Date DATETIME NOT NULL,
Favor BOOLEAN NOT NULL,
PRIMARY KEY (Avatar, Note_ID),
FOREIGN KEY (Avatar) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE,
KEY (Note_ID)
) COLLATE=utf8mb4_general_ci ENGINE=InnoDB
""".trimIndent()
)
dbStatement.executeUpdate(
"""
CREATE TABLE IF NOT EXISTS tw_pass (
Avatar VARCHAR(20) NOT NULL,
Title TEXT NOT NULL,
Artist TEXT NOT NULL,
Genre TEXT NOT NULL,
Level_Text TEXT NOT NULL,
Note_ID VARCHAR(139) NOT NULL,
Avatar_ID VARCHAR(20) NOT NULL,
PRIMARY KEY (Avatar, Note_ID, Avatar_ID),
FOREIGN KEY (Avatar) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (Avatar_ID) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE,
KEY (Avatar)
) COLLATE=utf8mb4_general_ci ENGINE=InnoDB
""".trimIndent()
)
}
}
}
fun setFavor(qwilightSetFavor: JSON.QwilightSetFavor, avatarID: String): CompletableFuture<Void> {
return logFuture {
pool.connection.use {
val noteID = qwilightSetFavor.noteID
val favor = qwilightSetFavor.favor
if (favor != null) {
it.prepareStatement(
"""
REPLACE INTO tw_favor
VALUES(?, ?, ?, ?)
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, noteID)
dbStatement.setString(2, avatarID)
dbStatement.setTimestamp(3, Timestamp(System.currentTimeMillis()))
dbStatement.setBoolean(4, favor)
dbStatement.execute()
}
} else {
it.prepareStatement(
"""
DELETE
FROM tw_favor
WHERE Note_ID = ? AND Avatar = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, noteID)
dbStatement.setString(2, avatarID)
dbStatement.execute()
}
}
}
}
}
fun loadTranslatedText(text: String, targetLanguage: String): CompletableFuture<String> {
return logValueFuture {
pool.connection.use {
it.prepareStatement(
"""
SELECT Translated_Text
FROM tw_translated
WHERE Text = ? AND Target_Language = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, text)
dbStatement.setString(2, targetLanguage)
dbStatement.executeQuery().use { rows ->
if (rows.next()) rows.getString("Translated_Text") else ""
}
}
}
}
}
fun saveTranslatedText(text: String, targetLanguage: String, translatedText: String): CompletableFuture<Void> {
return logFuture {
pool.connection.use {
it.prepareStatement(
"""
REPLACE INTO tw_translated
VALUES(?, ?, ?)
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, text)
dbStatement.setString(2, targetLanguage)
dbStatement.setString(3, translatedText)
dbStatement.execute()
}
}
}
}
fun getQwilightNames(): Collection<String> {
return pool.connection.use {
it.prepareStatement(
"""
SELECT Avatar_Name
FROM tn_avatar
ORDER BY Last_Date
LIMIT 100
""".trimIndent()
).use { dbStatement ->
dbStatement.executeQuery().use { rows ->
val avatarNames = mutableListOf<String>()
while (rows.next()) {
avatarNames.add(rows.getString("Avatar_Name"))
}
avatarNames
}
}
}
}
fun wipeNote(noteID: String, logger: Logger): CompletableFuture<Void> {
return logFuture {
pool.connection.use {
it.prepareStatement(
"""
DELETE
FROM tw_note
WHERE Note_ID = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, noteID)
logger.logInfo("Wiped ${dbStatement.executeUpdate()} Notes")
}
it.prepareStatement(
"""
SELECT Note_ID
FROM tw_note
WHERE Note_ID LIKE ?
""".trimIndent()
).use { dbStatement ->
val noteID512 = Utility.getNoteID512(noteID)
dbStatement.setString(1, "$noteID512:%")
dbStatement.executeQuery().use { rows ->
if (!rows.next() && Files.deleteIfExists(TwilightComponent.NOTE_ENTRY_PATH.resolve(noteID512))) {
logger.logInfo("Wiped Note File")
} else {
logger.logInfo("Wiped 0 Note Files")
}
}
}
it.prepareStatement(
"""
DELETE
FROM tw_comment
WHERE Note_ID = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, noteID)
logger.logInfo("Wiped ${dbStatement.executeUpdate()} Comments")
}
it.prepareStatement(
"""
SELECT Comment_ID
FROM tw_comment
WHERE Note_ID = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, noteID)
dbStatement.executeQuery().use { rows ->
var wipedCount = 0
while (rows.next()) {
if (Files.deleteIfExists(
TwilightComponent.COMMENT_ENTRY_PATH.resolve(Utility.getNoteID512(noteID))
.resolve("${rows.getString("Comment_ID")}.xz")
)
) {
++wipedCount
}
}
logger.logInfo("Wiped $wipedCount Comment Files")
}
}
}
}
}
fun getWipeNotes(): CompletableFuture<Collection<String>> {
return logValueFuture {
pool.connection.use {
it.prepareStatement(
"""
SELECT tw_note.Note_ID
FROM tw_comment
LEFT OUTER JOIN tw_note USING (Note_ID)
WHERE tw_comment.Note_ID IS NULL
""".trimIndent()
).use { dbStatement ->
dbStatement.executeQuery().use { rows ->
val data = mutableSetOf<String>()
while (rows.next()) {
val noteID = rows.getString("Note_ID")
if (!LevelSystem.levelNoteIDs.contains(noteID)) {
data.add(noteID)
}
}
data
}
}
}
}
}
fun pmsNote(noteID: String, logger: Logger): CompletableFuture<Void> {
return logFuture {
pool.connection.use {
it.autoCommit = false
try {
val pmsNoteID = "${Utility.getNoteID512(noteID)}:1"
it.prepareStatement(
"""
DELETE
FROM tw_note
WHERE Note_ID = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, noteID)
if (dbStatement.executeUpdate() > 0) {
logger.logInfo("Wiped Note")
}
}
it.prepareStatement(
"""
UPDATE tw_comment
SET Note_ID = ?
WHERE Note_ID = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, pmsNoteID)
dbStatement.setString(2, noteID)
logger.logInfo("PMSed ${dbStatement.executeUpdate()} Comments")
}
it.commit()
} catch (e: Exception) {
it.rollback()
}
}
}
}
fun getPMSNotes(): CompletableFuture<Collection<String>> {
return logValueFuture {
pool.connection.use {
it.prepareStatement(
"""
SELECT Note_ID, Title
FROM tw_note
WHERE Input_Mode = 12
""".trimIndent()
).use { dbStatement ->
dbStatement.executeQuery().use { rows ->
val data = mutableSetOf<String>()
while (rows.next()) {
val title = rows.getString("Title")
if (title.contains("9B", true) || title.contains("9K", true) || title.contains(
"PMS",
true
)
) {
data.add(rows.getString("Note_ID"))
}
}
data
}
}
}
}
}
fun setCommentMax(noteID: String): CompletableFuture<Void> {
data class Comment(
val stand: Int,
val date: Timestamp,
val commentID: String
)
return logFuture {
pool.connection.use {
it.autoCommit = false
try {
it.prepareStatement(
"""
UPDATE tw_comment
SET Is_Max = false
WHERE Note_ID = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, noteID)
dbStatement.executeUpdate()
}
val comments = mutableMapOf<String, Comment>()
it.prepareStatement(
"""
SELECT Avatar, Stand, Date, Comment_ID
FROM tw_comment
WHERE Note_ID = ? AND Avatar > ""
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, noteID)
dbStatement.executeQuery().use { rows ->
while (rows.next()) {
val avatar = rows.getString("Avatar")
val stand = rows.getInt("Stand")
val date = rows.getTimestamp("Date")
val commentID = rows.getString("Comment_ID")
val comment = comments.getOrPut(avatar) {
Comment(stand, date, commentID)
}
if (stand > comment.stand || (stand == comment.stand && date > comment.date)) {
comments[avatar] = Comment(stand, date, commentID)
}
}
}
}
comments.values.forEach { comment ->
it.prepareStatement(
"""
UPDATE tw_comment
SET Is_Max = true
WHERE Comment_ID = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, comment.commentID)
dbStatement.executeUpdate()
}
}
it.commit()
} catch (e: Exception) {
it.rollback()
}
}
}
}
fun getWipeComments(): CompletableFuture<Collection<Path>> {
return logValueFuture {
val commentIDs = mutableSetOf<String>()
pool.connection.use {
it.prepareStatement(
"""
SELECT Comment_ID
FROM tw_comment
WHERE Is_Max = true
""".trimIndent()
).use { dbStatement ->
dbStatement.executeQuery().use { rows ->
while (rows.next()) {
commentIDs.add(rows.getString("Comment_ID"))
}
}
}
}
Files.newDirectoryStream(TwilightComponent.COMMENT_ENTRY_PATH).use { commentEntryPaths ->
StreamSupport.stream(commentEntryPaths.spliterator(), true).use { parallel ->
parallel.map { commentEntryPath ->
Files.list(commentEntryPath).use { commentFilePaths ->
commentFilePaths.map { commentFilePath ->
if (commentIDs.contains(FilenameUtils.removeExtension(commentFilePath.fileName.toString()))) {
null
} else {
commentFilePath
}
}.toList()
}
}.toList().flatten().filterNotNull()
}
}
}
}
fun wipeComments(commentFilePaths: Collection<Path>): CompletableFuture<Int> {
return logValueFuture {
commentFilePaths.count {
try {
Files.deleteIfExists(it)
} catch (e: IOException) {
logFault(e)
false
}
}
}
}
fun learnAvatars(futureLearnAvatarsStatus: AtomicInteger): CompletableFuture<Void> {
return logFuture {
pool.connection.use {
it.prepareStatement(
"""
SELECT Avatar_ID
FROM tn_avatar
""".trimIndent()
).use { dbStatement ->
dbStatement.executeQuery().use { rows ->
while (rows.next()) {
setComputedAvatar(it, rows.getString("Avatar_ID"))
futureLearnAvatarsStatus.incrementAndGet()
}
}
}
}
}
}
fun learnNotes(futureLearnNotesStatus: AtomicInteger): CompletableFuture<Void> {
return logFuture {
pool.connection.use {
it.prepareStatement(
"""
DELETE
FROM tw_note
""".trimIndent()
).use { dbStatement -> dbStatement.execute() }
Files.newDirectoryStream(
TwilightComponent.NOTE_ENTRY_PATH
).use { noteEntryPaths ->
StreamSupport.stream(noteEntryPaths.spliterator(), true)
.use { parallel ->
parallel.forEach { noteFilePath ->
try {
val noteFileData =
Files.readAllBytes(noteFilePath)
BaseCompiler.handleCompile(noteFileData, -1)
.forEach { targetComputing ->
setNote(
targetComputing.noteID,
Utility.getID128(noteFileData),
Utility.getID256(noteFileData),
targetComputing
)
}
futureLearnNotesStatus.incrementAndGet()
} catch (e: Exception) {
logFault(e)
}
}
}
}
}
}
}
fun learnComments(futureLearnCommentsStatus: AtomicInteger): CompletableFuture<Void> {
return logFuture {
pool.connection.use {
Files.newDirectoryStream(TwilightComponent.COMMENT_ENTRY_PATH).use { commentEntryPaths ->
StreamSupport.stream(commentEntryPaths.spliterator(), true).use { parallel ->
parallel.forEach { commentEntryPath ->
Files.list(commentEntryPath).use { commentFilePaths ->
commentFilePaths.forEach { commentFilePath ->
try {
val commentID =
FilenameUtils.removeExtension(commentFilePath.fileName.toString())
it.prepareStatement(
"""
SELECT Audio_Multiplier
FROM tw_comment
WHERE Comment_ID = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, commentID)
dbStatement.executeQuery().use { rows ->
if (rows.next()) {
val audioMultiplier = rows.getDouble("Audio_Multiplier")
val comment =
XZCompressorInputStream(
Files.readAllBytes(commentFilePath).inputStream()
).use {
CommentOuterClass.Comment.parseFrom(it)
}
if (comment.audioMultipliersCount > 0) {
it.prepareStatement(
"""
UPDATE tw_comment
SET Lowest_Audio_Multiplier = ?, Highest_Audio_Multiplier = ?
WHERE Comment_ID = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setDouble(
1,
comment.audioMultipliersList.map { it.audioMultiplier }
.plus(audioMultiplier).min()
)
dbStatement.setDouble(
2,
comment.audioMultipliersList.map { it.audioMultiplier }
.plus(audioMultiplier).max()
)
dbStatement.setString(3, commentID)
dbStatement.executeUpdate()
}
} else {
it.prepareStatement(
"""
UPDATE tw_comment
SET Lowest_Audio_Multiplier = Audio_Multiplier, Highest_Audio_Multiplier = Audio_Multiplier
WHERE Comment_ID = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, commentID)
dbStatement.executeUpdate()
}
}
}
}
}
futureLearnCommentsStatus.incrementAndGet()
} catch (e: Exception) {
logFault(e)
}
}
}
}
}
}
}
}
}
fun learnHandled(futureLearnHandledStatus: AtomicInteger): CompletableFuture<Void> {
return logFuture {
pool.connection.use {
it.prepareStatement(
"""
DELETE
FROM tw_handled
WHERE Handled != ${Component.Handled.F}
""".trimIndent()
).use { dbStatement -> dbStatement.execute() }
}
data class Comment(
val avatar: String,
val noteID: String,
val hitPointsMode: Int,
val isBand1: Boolean,
val isYell1: Boolean,
val isDefaultHandled: Boolean
)
val comments = mutableListOf<Comment>()
pool.connection.use {
it.prepareStatement(
"""
SELECT Auto_Mode, Judgment_Mode, Hit_Points_Mode, Long_Note_Mode, Input_Favor_Mode, Note_Modify_Mode, Lowest_Audio_Multiplier, Avatar, Note_ID, Hit_Points_Mode, Is_Band1, Point
FROM tw_comment
WHERE Avatar > ""
""".trimIndent()
).use { dbStatement ->
dbStatement.executeQuery().use { rows ->
while (rows.next()) {
comments.add(
Comment(
rows.getString("Avatar"),
rows.getString("Note_ID"),
rows.getInt("Hit_Points_Mode"),
rows.getBoolean("Is_Band1"),
rows.getDouble("Point") == 1.0,
Utility.isStandardHandled(
rows.getInt("Auto_Mode"),
rows.getInt("Judgment_Mode"),
rows.getInt("Hit_Points_Mode"),
rows.getInt("Long_Note_Mode"),
rows.getInt("Input_Favor_Mode"),
rows.getInt("Note_Modify_Mode"),
rows.getDouble("Lowest_Audio_Multiplier")
)
)
)
}
}
}
}
StreamSupport.stream(
comments.groupBy { comment -> Pair(comment.avatar, comment.noteID) }.toList().spliterator(), true
).use { parallel ->
parallel.forEach { (avatarNoteID, comments) ->
try {
val defaultHandledComments = comments.filter { it.isDefaultHandled }
if (defaultHandledComments.isEmpty()) {
pool.connection.use {
it.prepareStatement(
"""
REPLACE INTO tw_handled
VALUES(?, ?, ?)
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarNoteID.first)
dbStatement.setString(2, avatarNoteID.second)
dbStatement.setInt(3, Component.Handled.ASSIST_CLEAR.value)
dbStatement.execute()
}
}
} else {
pool.connection.use {
it.prepareStatement(
"""
REPLACE INTO tw_handled
VALUES(?, ?, ?)
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarNoteID.first)
dbStatement.setString(2, avatarNoteID.second)
dbStatement.setInt(
3,
(if (defaultHandledComments.any { comment -> comment.isYell1 }) Component.Handled.YELL1 else if (defaultHandledComments.any { comment -> comment.isBand1 }) Component.Handled.BAND1 else {
when (defaultHandledComments.maxBy { comment ->
when (comment.hitPointsMode) {
Component.HIGHEST_HIT_POINTS_MODE, Component.FAILED_HIT_POINTS_MODE -> 2
Component.HIGHER_HIT_POINTS_MODE -> 1
Component.DEFAULT_HIT_POINTS_MODE -> 0
else -> throw IllegalArgumentException(comment.hitPointsMode.toString())
}
}.hitPointsMode) {
Component.HIGHEST_HIT_POINTS_MODE, Component.FAILED_HIT_POINTS_MODE -> Component.Handled.HIGHEST_CLEAR
Component.HIGHER_HIT_POINTS_MODE -> Component.Handled.HIGHER_CLEAR
Component.DEFAULT_HIT_POINTS_MODE -> Component.Handled.CLEAR
else -> throw NotImplementedError()
}
}).value
)
dbStatement.execute()
}
}
}
futureLearnHandledStatus.incrementAndGet()
} catch (e: Exception) {
logFault(e)
}
}
}
}
}
fun getWipeBundles(): CompletableFuture<Collection<Path>> {
return logValueFuture {
val bundleIDs = mutableSetOf<Pair<String, String>>()
pool.connection.use {
it.prepareStatement(
"""
SELECT Avatar, Name
FROM tw_bundle
""".trimIndent()
).use { dbStatement ->
dbStatement.executeQuery().use { rows ->
while (rows.next()) {
bundleIDs.add(Pair(rows.getString("Avatar"), rows.getString("Name")))
}
}
}
}
Files.newDirectoryStream(TwilightComponent.BUNDLE_ENTRY_PATH).use { bundleEntryPaths ->
StreamSupport.stream(bundleEntryPaths.spliterator(), true)
.use { parallel ->
parallel.map { bundleEntryPath ->
Files.list(bundleEntryPath).use { bundleFilePaths ->
bundleFilePaths.map { bundleFilePath ->
if (bundleIDs.contains(
Pair(
bundleEntryPath.fileName.toString(),
FilenameUtils.removeExtension(bundleFilePath.fileName.toString())
)
)
) {
null
} else {
bundleFilePath
}
}.toList()
}
}.toList().flatten().filterNotNull()
}
}
}
}
fun wipeBundles(bundleFilePaths: Collection<Path>): CompletableFuture<Int> {
return logValueFuture {
bundleFilePaths.count {
try {
Files.deleteIfExists(it)
} catch (e: IOException) {
logFault(e)
false
}
}
}
}
interface ICommentary {
val avatarID: String
var commentary: String
}
fun getComment(
noteID: String,
avatarID: String,
language: String,
viewUnit: Int,
isUbuntu: Boolean,
ubuntuID: String,
logger: Logger
): CompletableFuture<Any> {
val comments = mutableListOf<ICommentary>()
return logValueFuture {
val commentaryMap = mutableMapOf<String, String>()
pool.connection.use { db ->
db.prepareStatement(
"""
SELECT Avatar, Commentary
FROM tw_commentary
WHERE Note_ID = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, noteID)
dbStatement.executeQuery().use { rows ->
while (rows.next()) {
commentaryMap[rows.getString("Avatar")] = rows.getString("Commentary")
}
}
}
}
val favor = if (avatarID.isEmpty()) null else pool.connection.use {
it.prepareStatement(
"""
SELECT Favor
FROM tw_favor
WHERE Note_ID = ? AND Avatar = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, noteID)
dbStatement.setString(2, avatarID)
dbStatement.executeQuery().use { rows ->
if (rows.next()) rows.getBoolean("Favor") else null
}
}
}
val ubuntuIDs =
if (avatarID.isEmpty() || !isUbuntu) null else (if (ubuntuID.isEmpty()) getUbuntuIDs(avatarID).join()
.map { it[0] }.toMutableList().apply { add(avatarID) } else listOf(ubuntuID))
var totalFavor = 0
pool.connection.use {
it.prepareStatement(
"""
SELECT Favor
FROM tw_favor
WHERE Note_ID = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, noteID)
dbStatement.executeQuery().use { rows ->
while (rows.next()) {
totalFavor += if (rows.getBoolean("Favor")) 1 else -1
}
}
}
}
totalFavor = 0.coerceAtLeast(totalFavor)
pool.connection.use { db ->
db.prepareStatement(
"""
SELECT tw_comment.Date, Avatar_ID, Avatar_Name, Multiplier, Auto_Mode, Note_Salt_Mode, Audio_Multiplier, Faint_Note_Mode, Judgment_Mode, Hit_Points_Mode, Note_Mobility_Mode, Long_Note_Mode, Input_Favor_Mode, Note_Modify_Mode, Lowest_Judgment_Condition_Mode, Stand, Band, Is_Band1, Point, Salt, Comment_ID, Is_Paused, Input_Flags
FROM tn_avatar
INNER JOIN tw_comment ON tn_avatar.Avatar_ID = tw_comment.Avatar
WHERE Note_ID = ? AND Is_Max = true
ORDER BY Stand DESC
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, noteID)
dbStatement.executeQuery().use { rows ->
var avatarPlace = -1
while (rows.next()) {
val rowAvatarID = rows.getString("Avatar_ID")
if (ubuntuIDs?.contains(rowAvatarID) != false) {
comments.add(object : ICommentary {
override val avatarID = rowAvatarID
override var commentary = commentaryMap.getOrDefault(this.avatarID, "")
val date = rows.getTimestamp("Date")
val avatarName = rows.getString("Avatar_Name")
val multiplier = rows.getDouble("Multiplier")
val autoMode = rows.getInt("Auto_Mode")
val noteSaltMode = rows.getInt("Note_Salt_Mode")
val audioMultiplier = rows.getDouble("Audio_Multiplier")
val faintNoteMode = rows.getInt("Faint_Note_Mode")
val judgmentMode = rows.getInt("Judgment_Mode")
val hitPointsMode = rows.getInt("Hit_Points_Mode")
val noteMobilityMode = rows.getInt("Note_Mobility_Mode")
val longNoteMode = rows.getInt("Long_Note_Mode")
val inputFavorMode = rows.getInt("Input_Favor_Mode")
val noteModifyMode = rows.getInt("Note_Modify_Mode")
val lowestJudgmentConditionMode = rows.getInt("Lowest_Judgment_Condition_Mode")
val stand = rows.getInt("Stand")
val band = rows.getInt("Band")
val handled = getHandled(rowAvatarID, noteID)
@JvmField
val isBand1 = rows.getBoolean("Is_Band1")
val point = rows.getDouble("Point")
val salt = rows.getInt("Salt")
val commentID = rows.getString("Comment_ID") ?: ""
@JvmField
val isPaused = rows.getBoolean("Is_Paused")
val inputFlags = rows.getInt("Input_Flags")
val avatarPlace = ++avatarPlace
})
}
}
val commentsMe = mutableListOf<ICommentary>()
comments.forEach { comment ->
val isMe = comment.avatarID == avatarID
if (commentsMe.size < viewUnit || isMe) {
commentsMe.add(comment)
}
}
CompletableFuture.allOf(*commentsMe.filter { it.commentary.isNotEmpty() }.map {
Translator.translate(
language,
it.commentary,
logger
).thenApply { translatedText ->
it.commentary = translatedText
}
}.toTypedArray()).join()
object {
val favor = favor
val totalFavor = totalFavor
val comments = commentsMe
val totalComments = comments.size
}
}
}
}
}
}
fun setAvatarTitle(avatarID: String, titleID: String): CompletableFuture<Void> {
return logFuture {
pool.connection.use { db ->
if (titleID.isEmpty()) {
db.prepareStatement(
"""
DELETE
FROM tw_title
WHERE Avatar = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.execute()
}
} else {
if (TitleSystem.getTitleIDs(
getLevelIDs(avatarID), getNoteIDs(avatarID), getAvatarLevels(avatarID).first
).stream().anyMatch { it == titleID }
) {
db.prepareStatement(
"""
REPLACE INTO tw_title
VALUES(?, ?)
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.setString(2, titleID)
dbStatement.execute()
}
}
}
}
}
}
fun setCommentary(noteID: String, avatarID: String, commentary: String): CompletableFuture<Boolean> {
return logValueFuture {
pool.connection.use {
val hasComment: Boolean
it.prepareStatement(
"""
SELECT Comment_ID
FROM tw_comment
WHERE Note_ID = ? AND Avatar = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, noteID)
dbStatement.setString(2, avatarID)
dbStatement.executeQuery().use { rows -> hasComment = rows.next() }
}
if (commentary.isBlank()) {
if (hasComment) {
it.prepareStatement(
"""
DELETE
FROM tw_commentary
WHERE Note_ID = ? AND Avatar = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, noteID)
dbStatement.setString(2, avatarID)
dbStatement.execute()
}
}
true
} else {
if (hasComment) {
it.prepareStatement(
"""
REPLACE INTO tw_commentary
VALUES(?, ?, ?)
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, noteID)
dbStatement.setString(2, avatarID)
dbStatement.setString(3, commentary)
dbStatement.execute()
}
}
hasComment
}
}
}
}
fun getNotes(): CompletableFuture<Collection<String>> {
return logValueFuture {
pool.connection.use {
it.prepareStatement(
"""
SELECT Note_ID
FROM tw_note
""".trimIndent()
).use { dbStatement ->
dbStatement.executeQuery().use { rows ->
val data = mutableListOf<String>()
while (rows.next()) {
data.add(rows.getString("Note_ID"))
}
data
}
}
}
}
}
fun setBundle(qwilightSetBundle: JSON.QwilightSetBundle, avatarID: String): CompletableFuture<Void> {
return logFuture {
pool.connection.use {
it.prepareStatement(
"""
UPDATE tw_bundle
SET Competence = ?
WHERE Avatar = ? AND Name = ? AND Variety != ${BundleIO.BundleVariety.QWILIGHT.value}
""".trimIndent()
).use { dbStatement ->
dbStatement.setInt(1, qwilightSetBundle.bundleCompetence)
dbStatement.setString(2, avatarID)
dbStatement.setString(3, qwilightSetBundle.bundleName)
dbStatement.execute()
}
}
}
}
fun getBundles(avatarID: String): CompletableFuture<Pair<Collection<Array<Any>>, Long>?> {
return logValueFuture {
pool.connection.use {
val data = mutableListOf<Array<Any>>()
it.prepareStatement(
"""
SELECT *
FROM tw_bundle
WHERE Avatar = ?
ORDER BY Date DESC
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.executeQuery().use { rows ->
while (rows.next()) {
data.add(
arrayOf(
rows.getTimestamp("Date"),
rows.getString("Name"),
rows.getLong("Length"),
rows.getInt("Competence"),
rows.getInt("Variety")
)
)
}
}
}
it.prepareStatement(
"""
SELECT SUM(Length) AS Length
FROM tw_bundle
WHERE Avatar = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.executeQuery().use { rows ->
if (rows.next()) {
Pair(data, rows.getLong("Length"))
} else {
null
}
}
}
}
}
}
fun setConfigure(qwilightSetConfigure: JSON.QwilightSetConfigure, avatarID: String): CompletableFuture<Void> {
return logFuture {
pool.connection.use {
it.prepareStatement(
"""
REPLACE INTO tw_configure
VALUES(?, ?, ?, ?, ?, ?, ?)
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.setInt(2, qwilightSetConfigure.silentSiteCompetence)
dbStatement.setInt(3, qwilightSetConfigure.toNotifyUbuntu)
dbStatement.setInt(4, qwilightSetConfigure.defaultBundleCompetence)
dbStatement.setInt(5, qwilightSetConfigure.ioCompetence)
dbStatement.setInt(6, qwilightSetConfigure.toNotifySaveBundle)
dbStatement.setInt(7, qwilightSetConfigure.toNotifyPass)
dbStatement.execute()
}
}
}
}
fun setLastDate(avatarID: String): CompletableFuture<Void> {
return logFuture {
pool.connection.use {
it.prepareStatement(
"""
UPDATE tn_avatar
SET Last_Date = ?
WHERE Avatar_ID = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setTimestamp(1, Timestamp(System.currentTimeMillis()))
dbStatement.setString(2, avatarID)
dbStatement.execute()
}
}
}
}
fun wipeUbuntu(avatarID: String, ubuntuID: String): CompletableFuture<Void> {
return logFuture {
pool.connection.use {
it.prepareStatement(
"""
DELETE
FROM tw_ubuntu
WHERE Avatar = ? AND Ubuntu = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.setString(2, ubuntuID)
dbStatement.execute()
}
}
}
}
fun wipeBundle(avatarID: String, bundleName: String): CompletableFuture<Void> {
return logFuture {
var bundleVariety: BundleVariety? = null
pool.connection.use {
it.prepareStatement(
"""
SELECT Variety
FROM tw_bundle
WHERE Avatar = ? AND Name = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.setString(2, bundleName)
dbStatement.executeQuery().use { rows ->
if (rows.next()) {
bundleVariety = BundleVariety.getBundleVariety(rows.getInt("Variety"))
}
}
}
}
bundleVariety?.let {
pool.connection.use { db ->
db.prepareStatement(
"""
DELETE
FROM tw_bundle
WHERE Avatar = ? AND Name = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.setString(2, bundleName)
dbStatement.execute()
}
}
FileUtils.deleteQuietly(
TwilightComponent.BUNDLE_ENTRY_PATH.resolve(avatarID).resolve(bundleName + it.fileVariety)
.toFile().absoluteFile
)
}
}
}
fun silentSite(siteID: String): CompletableFuture<Boolean> {
return logValueFuture {
pool.connection.use {
it.prepareStatement(
"""
DELETE
FROM tw_site
WHERE Site_ID = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, siteID)
dbStatement.execute()
}
}
}
}
fun getUbuntuIDs(avatarID: String): CompletableFuture<Collection<Array<String>>> {
return logValueFuture {
pool.connection.use {
it.prepareStatement(
"""
SELECT Avatar_ID, Avatar_Name
FROM tw_ubuntu
INNER JOIN tn_avatar ON tw_ubuntu.Ubuntu = tn_avatar.Avatar_ID
WHERE Avatar = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.executeQuery().use { rows ->
val data = mutableListOf<Array<String>>()
while (rows.next()) {
data.add(arrayOf(rows.getString("Avatar_ID"), rows.getString("Avatar_Name")))
}
data
}
}
}
}
}
fun getAvatarName(avatarID: String): String {
return pool.connection.use {
it.prepareStatement(
"""
SELECT Avatar_Name
FROM tn_avatar
WHERE Avatar_ID = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.executeQuery().use { rows ->
if (rows.next()) rows.getString("Avatar_Name") else ""
}
}
}
}
fun getClearedLevelIDs(avatarID: String): CompletableFuture<Collection<String>> {
return logValueFuture {
pool.connection.use {
it.prepareStatement(
"""
SELECT Level_ID
FROM tw_level
WHERE Avatar = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.executeQuery().use { rows ->
val data = mutableListOf<String>()
while (rows.next()) {
data.add(rows.getString("Level_ID"))
}
data
}
}
}
}
}
fun setWwwLevel(avatarID: String, levelID: String) {
pool.connection.use {
it.prepareStatement(
"""
REPLACE INTO tw_level
VALUES (?, ?, ?)
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.setString(2, levelID)
dbStatement.setTimestamp(3, Timestamp(System.currentTimeMillis()))
dbStatement.execute()
}
}
}
fun getSilentSiteCompetence(avatarID: String): CompletableFuture<Int> {
return logValueFuture {
pool.connection.use {
it.prepareStatement(
"""
SELECT Silent_Site
FROM tw_configure
WHERE Avatar = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.executeQuery()
.use { rows ->
if (rows.next()) {
rows.getInt("Silent_Site")
} else {
QwilightAvatar.SILENT_SITE_CALLABLE
}
}
}
}
}
}
fun getIOCompetence(avatarID: String): CompletableFuture<Int> {
return logValueFuture {
pool.connection.use {
it.prepareStatement(
"""
SELECT IO_Competence
FROM tw_configure
WHERE Avatar = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.executeQuery()
.use { rows ->
if (rows.next()) {
rows.getInt("IO_Competence")
} else {
QwilightAvatar.IO_CALLABLE
}
}
}
}
}
}
fun getNotifySaveBundle(avatarID: String): Int {
return pool.connection.use {
it.prepareStatement(
"""
SELECT Notify_Save_Bundle
FROM tw_configure
WHERE Avatar = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.executeQuery()
.use { rows -> if (rows.next()) rows.getInt("Notify_Save_Bundle") else QwilightAvatar.NOTIFY_SAVE_BUNDLE_CALLABLE }
}
}
}
fun getNotifyPass(avatarID: String): Int {
return pool.connection.use {
it.prepareStatement(
"""
SELECT Notify_Pass
FROM tw_configure
WHERE Avatar = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.executeQuery()
.use { rows -> if (rows.next()) rows.getInt("Notify_Pass") else QwilightAvatar.NOTIFY_PASS_AVATAR }
}
}
}
fun getConfigure(avatarID: String): CompletableFuture<IntArray> {
return logValueFuture {
pool.connection.use {
it.prepareStatement(
"""
SELECT Silent_Site, Notify_Ubuntu, Default_Bundle_Competence, IO_Competence, Notify_Save_Bundle, Notify_Pass
FROM tw_configure
WHERE Avatar = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.executeQuery().use { rows ->
if (rows.next()) {
intArrayOf(
rows.getInt("Silent_Site"),
rows.getInt("Notify_Ubuntu"),
rows.getInt("Default_Bundle_Competence"),
rows.getInt("IO_Competence"),
rows.getInt("Notify_Save_Bundle"),
rows.getInt("Notify_Pass")
)
} else {
intArrayOf(
QwilightAvatar.SILENT_SITE_CALLABLE,
QwilightAvatar.NOTIFY_UBUNTU_UBUNTU,
QwilightAvatar.BUNDLE_VOID,
QwilightAvatar.IO_CALLABLE,
QwilightAvatar.NOTIFY_SAVE_BUNDLE_CALLABLE,
QwilightAvatar.NOTIFY_PASS_AVATAR
)
}
}
}
}
}
}
fun saveUbuntu(avatarID: String, ubuntuID: String): CompletableFuture<Boolean> {
return logValueFuture {
try {
pool.connection.use {
it.prepareStatement(
"""
INSERT INTO tw_ubuntu
VALUES(?, ?)
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.setString(2, ubuntuID)
dbStatement.execute()
true
}
}
} catch (e: SQLIntegrityConstraintViolationException) {
false
}
}
}
fun saveBundle(
avatarID: String,
bundleName: String,
bundleLength: Long,
bundleVariety: BundleVariety,
etc: String
) {
pool.connection.use {
it.prepareStatement(
"""
INSERT INTO tw_bundle
VALUES (?, ?, ?, ?, ?, ?, ?)
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.setTimestamp(2, Timestamp(System.currentTimeMillis()))
dbStatement.setString(3, bundleName)
dbStatement.setLong(4, bundleLength)
dbStatement.setInt(
5,
if (bundleVariety == BundleVariety.QWILIGHT) QwilightAvatar.BUNDLE_VOID else getDefaultBundleCompetence(
avatarID
)
)
dbStatement.setInt(6, bundleVariety.value)
dbStatement.setString(7, etc)
dbStatement.execute()
}
}
}
private fun getDefaultBundleCompetence(avatarID: String): Int {
return pool.connection.use {
it.prepareStatement(
"""
SELECT Default_Bundle_Competence
FROM tw_configure
WHERE Avatar = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.executeQuery()
.use { rows -> if (rows.next()) rows.getInt("Default_Bundle_Competence") else QwilightAvatar.BUNDLE_VOID }
}
}
}
fun isBundleLengthAvailable(avatarID: String, bundleLength: Long): Boolean {
return pool.connection.use {
it.prepareStatement(
"""
SELECT SUM(Length) AS Length
FROM tw_bundle
WHERE Avatar = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.executeQuery()
.use { rows -> rows.next() && bundleLength + rows.getLong("Length") < TwilightComponent.BUNDLE_LENGTH }
}
}
}
fun hasBundleBefore(avatarID: String, bundleName: String): CompletableFuture<Boolean> {
return logValueFuture {
pool.connection.use {
it.prepareStatement(
"""
SELECT *
FROM tw_bundle
WHERE Avatar = ? AND Name = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.setString(2, bundleName)
dbStatement.executeQuery().use { rows ->
rows.next()
}
}
}
}
}
fun getLastDate(avatarID: String): Long {
return pool.connection.use { dbComponent ->
dbComponent.prepareStatement(
"""
SELECT Last_Date
FROM tn_avatar
WHERE Avatar_ID = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.executeQuery().use {
(if (it.next()) it.getTimestamp("Last_Date")
.toInstant() else Instant.now()).toEpochMilli()
}
}
}
}
fun getBundle(avatarID: String, bundleName: String): CompletableFuture<Array<Any>?> {
return logValueFuture {
pool.connection.use {
it.prepareStatement(
"""
SELECT Competence, Variety, Etc, Name
FROM tw_bundle
WHERE Avatar = ? AND Name = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.setString(2, bundleName)
dbStatement.executeQuery().use { rows ->
if (rows.next()) {
arrayOf(
rows.getInt("Competence"),
rows.getInt("Variety"),
rows.getString("Etc"),
rows.getString("Name")
)
} else {
null
}
}
}
}
}
}
private fun setComputedNote(db: Connection, noteID: String) {
val topCount = db.prepareStatement(
"""
SELECT COUNT(Note_ID) AS Top_Count
FROM tw_comment
WHERE Note_ID = ? AND Is_Max = true
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, noteID)
dbStatement.executeQuery().use { rows ->
if (rows.next()) {
rows.getInt("Top_Count")
} else {
0
}
}
}
val totalCount = db.prepareStatement(
"""
SELECT COUNT(Note_ID) AS Total_Count
FROM tw_comment
WHERE Note_ID = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, noteID)
dbStatement.executeQuery().use { rows ->
if (rows.next()) {
rows.getInt("Total_Count")
} else {
0
}
}
}
val date = db.prepareStatement(
"""
SELECT MAX(Date) AS Date
FROM tw_comment
WHERE Note_ID = ? AND Is_Max = true
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, noteID)
dbStatement.executeQuery().use { rows ->
if (rows.next()) {
rows.getTimestamp("Date")
} else {
Timestamp.from(Instant.EPOCH)
}
}
}
db.prepareStatement(
"""
UPDATE tw_note
SET Top_Count = ?, Total_Count = ?, Date = ?
WHERE Note_ID = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setInt(1, topCount)
dbStatement.setInt(2, totalCount)
dbStatement.setTimestamp(3, date)
dbStatement.setString(4, noteID)
dbStatement.execute()
}
}
fun setNote(noteID: String, noteID128: String, noteID256: String, targetComputing: Computing) {
pool.connection.use {
it.autoCommit = false
try {
it.prepareStatement(
"""
REPLACE INTO tw_note
VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, 0, 0)
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, noteID)
dbStatement.setString(2, noteID128)
dbStatement.setString(3, noteID256)
dbStatement.setInt(4, targetComputing.noteVariety.value)
dbStatement.setString(5, targetComputing.artist)
dbStatement.setString(6, targetComputing.title)
dbStatement.setString(7, targetComputing.genre)
dbStatement.setString(8, targetComputing.levelText)
dbStatement.setInt(9, targetComputing.level)
dbStatement.setInt(10, targetComputing.inputMode.value)
dbStatement.setInt(11, targetComputing.totalNotes)
dbStatement.setBoolean(12, targetComputing.isSalt)
dbStatement.setDouble(
13,
AbilitySystem.getAbility(
AbilityClassSystem.AbilityClassVariety.INPUT_MODE_6K,
noteID128,
noteID256
)
)
dbStatement.setDouble(
14,
AbilitySystem.getAbility(
AbilityClassSystem.AbilityClassVariety.INPUT_MODE_5K,
noteID128,
noteID256
)
)
dbStatement.setDouble(
15,
AbilitySystem.getAbility(
AbilityClassSystem.AbilityClassVariety.INPUT_MODE_7K,
noteID128,
noteID256
)
)
dbStatement.setDouble(
16,
AbilitySystem.getAbility(
AbilityClassSystem.AbilityClassVariety.INPUT_MODE_9K,
noteID128,
noteID256
)
)
dbStatement.setDouble(17, targetComputing.length)
dbStatement.execute()
}
setComputedNote(it, noteID)
it.commit()
} catch (e: Exception) {
it.rollback()
}
}
}
private fun setComputedAvatar(db: Connection, avatarID: String) {
val avatarXP = db.prepareStatement(
"""
SELECT SUM(Total_Notes * Stand / 1000000) AS Value
FROM tw_comment
INNER JOIN tw_note USING(Note_ID)
WHERE Avatar = ? AND Is_Salt = false
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.executeQuery().use { rows ->
if (rows.next()) {
rows.getDouble("Value")
} else {
0.0
}
}
}
db.prepareStatement(
"""
REPLACE INTO tw_avatar
VALUES(?, ?)
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.setDouble(2, avatarXP)
dbStatement.execute()
}
}
fun learnAbility() {
data class NoteID(val noteID: String, val noteID128: String, val noteID256: String)
val noteIDs = mutableListOf<NoteID>()
pool.connection.use {
it.prepareStatement(
"""
SELECT Note_ID, Note_ID_128, Note_ID_256, Is_Salt
FROM tw_note
""".trimIndent()
).use { dbStatement ->
dbStatement.executeQuery().use { rows ->
while (rows.next()) {
noteIDs.add(
NoteID(
rows.getString("Note_ID"),
rows.getString("Note_ID_128"),
rows.getString("Note_ID_256")
)
)
}
}
}
}
noteIDs.parallelStream().use { parallel ->
parallel.forEach { (noteID, noteID128, noteID256) ->
pool.connection.use {
it.prepareStatement(
"""
UPDATE tw_note
SET Ability_6K = ?, Ability_5K = ?, Ability_7K = ?, Ability_9K = ?
WHERE Note_ID = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setDouble(
1,
AbilitySystem.getAbility(
AbilityClassSystem.AbilityClassVariety.INPUT_MODE_6K,
noteID128,
noteID256
)
)
dbStatement.setDouble(
2,
AbilitySystem.getAbility(
AbilityClassSystem.AbilityClassVariety.INPUT_MODE_5K,
noteID128,
noteID256
)
)
dbStatement.setDouble(
3,
AbilitySystem.getAbility(
AbilityClassSystem.AbilityClassVariety.INPUT_MODE_7K,
noteID128,
noteID256
)
)
dbStatement.setDouble(
4,
AbilitySystem.getAbility(
AbilityClassSystem.AbilityClassVariety.INPUT_MODE_9K,
noteID128,
noteID256
)
)
dbStatement.setString(5, noteID)
dbStatement.execute()
}
}
}
}
}
fun isCrossUbuntu(avatarID: String, ubuntuID: String): Boolean {
return pool.connection.use {
it.prepareStatement(
"""
SELECT *
FROM tw_ubuntu
WHERE Avatar = ? AND Ubuntu = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, ubuntuID)
dbStatement.setString(2, avatarID)
dbStatement.executeQuery().use { rows -> rows.next() }
}
}
}
fun getNotifyUbuntu(avatarID: String): Iterable<String> {
return pool.connection.use {
it.prepareStatement(
"""
SELECT Ubuntu, Notify_Ubuntu
FROM tw_ubuntu LEFT OUTER JOIN tw_configure
ON tw_ubuntu.Ubuntu = tw_configure.Avatar
WHERE tw_ubuntu.Avatar = ? AND Notify_Ubuntu = 0
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.executeQuery().use { rows ->
val data = mutableListOf<String>()
while (rows.next()) {
data.add(rows.getString("Ubuntu"))
}
data
}
}
}
}
fun saveSiteYell(siteID: String, siteYell: SiteYell): CompletableFuture<Void> {
return logFuture {
pool.connection.use {
it.prepareStatement(
"""
INSERT INTO
tw_site
VALUES(?, ?, ?, ?, ?, ?, ?)
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, siteID)
dbStatement.setString(2, siteYell.avatarID)
dbStatement.setString(3, siteYell.avatarName)
dbStatement.setLong(4, siteYell.date)
dbStatement.setString(5, siteYell.siteYell)
dbStatement.setLong(6, siteYell.platformID)
dbStatement.setBoolean(7, siteYell.translate)
dbStatement.execute()
}
}
}
}
fun doModifySiteYell(siteYell: SiteYell): CompletableFuture<Void> {
return logFuture {
pool.connection.use {
it.prepareStatement(
"""
UPDATE tw_site
SET Site_Yell = ?
WHERE Platform_ID = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, siteYell.siteYell)
dbStatement.setLong(2, siteYell.platformID)
dbStatement.execute()
}
}
}
}
fun wipeSiteYell(siteYell: SiteYell): CompletableFuture<Void> {
return logFuture {
pool.connection.use {
it.prepareStatement(
"""
DELETE
FROM tw_site
WHERE Platform_ID = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setLong(1, siteYell.platformID)
dbStatement.execute()
}
}
}
}
fun saveComment(
multiplier: Double,
autoMode: Int,
noteSaltMode: Int,
audioMultiplier: Double,
faintNoteMode: Int,
judgmentMode: Int,
hitPointsMode: Int,
noteMobilityMode: Int,
longNoteMode: Int,
inputFavorMode: Int,
noteModifyMode: Int,
lowestJudgmentConditionMode: Int,
stand: Int,
band: Int,
isBand1: Boolean,
point: Double,
salt: Int,
commentIDNew: String,
avatarID: String,
noteID: String,
isPaused: Boolean,
inputFlags: Int,
lowestAudioMultiplier: Double,
highestAudioMultiplier: Double
): Pair<String?, Int> {
var commentID: String? = null
var standBefore = 0
pool.connection.use {
it.autoCommit = false
try {
it.prepareStatement(
"""
SELECT Stand, Comment_ID
FROM tw_comment
WHERE Note_ID = ? AND Avatar = ? AND Is_Max = true
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, noteID)
dbStatement.setString(2, avatarID)
dbStatement.executeQuery().use { rows ->
if (rows.next()) {
standBefore = rows.getInt("Stand")
if (standBefore <= stand) {
commentID = rows.getString("Comment_ID")
}
} else {
commentID = ""
}
}
}
if (!commentID.isNullOrEmpty()) {
it.prepareStatement(
"""
UPDATE tw_comment
SET Is_Max = false
WHERE Comment_ID = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, commentID)
dbStatement.execute()
}
}
it.prepareStatement(
"""
INSERT INTO tw_comment
VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
""".trimIndent()
).use { dbStatement ->
dbStatement.setTimestamp(1, Timestamp(System.currentTimeMillis()))
dbStatement.setString(2, noteID)
dbStatement.setString(3, avatarID)
dbStatement.setDouble(4, multiplier)
dbStatement.setInt(5, autoMode)
dbStatement.setInt(6, noteSaltMode)
dbStatement.setDouble(7, audioMultiplier)
dbStatement.setInt(8, faintNoteMode)
dbStatement.setInt(9, judgmentMode)
dbStatement.setInt(10, hitPointsMode)
dbStatement.setInt(11, noteMobilityMode)
dbStatement.setInt(12, longNoteMode)
dbStatement.setInt(13, inputFavorMode)
dbStatement.setInt(14, noteModifyMode)
dbStatement.setInt(15, lowestJudgmentConditionMode)
dbStatement.setInt(16, stand)
dbStatement.setInt(17, band)
dbStatement.setBoolean(18, isBand1)
dbStatement.setDouble(19, point)
dbStatement.setInt(20, salt)
dbStatement.setString(21, commentIDNew)
dbStatement.setBoolean(22, commentID != null)
dbStatement.setBoolean(23, isPaused)
dbStatement.setInt(24, inputFlags)
dbStatement.setDouble(25, lowestAudioMultiplier)
dbStatement.setDouble(26, highestAudioMultiplier)
dbStatement.execute()
}
setComputedNote(it, noteID)
setComputedAvatar(it, avatarID)
it.commit()
} catch (e: Exception) {
it.rollback()
}
}
return Pair(commentID, standBefore)
}
fun getBetweenAvatarIDs(noteID: String, avatarID: String, standBefore: Int, stand: Int): Collection<String> {
return pool.connection.use {
it.prepareStatement(
"""
SELECT Avatar
FROM tw_comment
WHERE Note_ID = ? AND Avatar != ? AND ? <= Stand AND Stand < ? AND Is_Max = true
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, noteID)
dbStatement.setString(2, avatarID)
dbStatement.setInt(3, standBefore)
dbStatement.setInt(4, stand)
dbStatement.executeQuery().use { rows ->
val avatarIDs = mutableListOf<String>()
while (rows.next()) {
avatarIDs.add(rows.getString("Avatar"))
}
avatarIDs
}
}
}
}
fun saveHandled(
avatarID: String,
noteID: String,
isBand1: Boolean,
isYell1: Boolean,
autoMode: Int,
judgmentMode: Int,
hitPointsMode: Int,
longNoteMode: Int,
inputFavorMode: Int,
noteModifyMode: Int,
lowestAudioMultiplier: Double
) {
var handled = getHandled(avatarID, noteID)
if (Utility.isStandardHandled(
autoMode,
judgmentMode,
hitPointsMode,
longNoteMode,
inputFavorMode,
noteModifyMode,
lowestAudioMultiplier
)
) {
if (handled != Component.Handled.YELL1) {
if (isYell1) {
handled = Component.Handled.YELL1
} else {
if (handled != Component.Handled.BAND1) {
if (isBand1) {
handled = Component.Handled.BAND1
} else {
when (hitPointsMode) {
Component.HIGHEST_HIT_POINTS_MODE -> handled = Component.Handled.HIGHEST_CLEAR
Component.HIGHER_HIT_POINTS_MODE -> {
if (handled != Component.Handled.HIGHEST_CLEAR) {
handled = Component.Handled.HIGHER_CLEAR
}
}
Component.DEFAULT_HIT_POINTS_MODE -> {
if (handled != Component.Handled.HIGHER_CLEAR && handled != Component.Handled.HIGHEST_CLEAR) {
handled = Component.Handled.CLEAR
}
}
}
}
}
}
}
} else {
if (handled == Component.Handled.NOT || handled == Component.Handled.F) {
handled = Component.Handled.ASSIST_CLEAR
}
}
pool.connection.use {
it.prepareStatement(
"""
REPLACE INTO tw_handled
VALUES(?, ?, ?)
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.setString(2, noteID)
dbStatement.setInt(3, handled.value)
dbStatement.execute()
}
}
}
fun getTitle(avatarID: String, language: String): CompletableFuture<JSON.TwilightWwwTitle?> {
return logValueFuture {
pool.connection.use {
it.prepareStatement(
"""
SELECT Title_ID
FROM tw_title
WHERE Avatar = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.executeQuery().use { rows ->
if (rows.next()) {
val titleID = rows.getString("Title_ID")
if (titleID != null) {
val wwwTitle = TitleSystem.getTitle(language, titleID)
if (wwwTitle != null) {
JSON.TwilightWwwTitle(wwwTitle.title, wwwTitle.titleColor)
} else {
null
}
} else {
null
}
} else {
null
}
}
}
}
}
}
private fun getLevelIDs(avatarID: String): Collection<String> {
val levelIDs = mutableListOf<String>()
pool.connection.use {
it.prepareStatement(
"""
SELECT Level_ID
FROM tw_level
WHERE Avatar = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.executeQuery().use { rows ->
while (rows.next()) {
levelIDs.add(rows.getString("Level_ID"))
}
}
}
}
return levelIDs
}
private fun getNoteIDs(avatarID: String): Collection<String> {
val noteIDs = mutableListOf<String>()
pool.connection.use {
it.prepareStatement(
"""
SELECT Note_ID
FROM tw_handled
WHERE Avatar = ? AND Handled NOT IN (${Component.Handled.F}, ${Component.Handled.ASSIST_CLEAR})
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.executeQuery().use { rows ->
while (rows.next()) {
noteIDs.add(rows.getString("Note_ID"))
}
}
}
}
return noteIDs
}
fun getTitleItems(avatarID: String): Collection<TitleSystem.TitleItem> {
return TitleSystem.getTitleItems(
getLevelIDs(avatarID), getNoteIDs(avatarID), getAvatarLevels(avatarID).first
)
}
fun getTitles(avatarID: String, language: String): CompletableFuture<Collection<Any>> {
return logValueFuture {
TitleSystem.getTitles(
getLevelIDs(avatarID),
getNoteIDs(avatarID),
getAvatarLevels(avatarID).first,
language
)
}
}
fun getEdgeIDs(avatarID: String): CompletableFuture<Collection<String>> {
return logValueFuture {
EdgeSystem.getEdgeIDs(
getLevelIDs(avatarID), getNoteIDs(avatarID), getAvatarLevels(avatarID).first
)
}
}
fun setAvatarEdge(avatarID: String, edgeID: String): CompletableFuture<Void> {
return logFuture {
pool.connection.use {
if (
EdgeSystem.getEdgeIDs(
getLevelIDs(avatarID), getNoteIDs(avatarID), getAvatarLevels(avatarID).first
).contains(edgeID)
) {
it.prepareStatement(
"""
REPLACE INTO tw_edge
VALUES(?, ?)
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.setString(2, edgeID)
dbStatement.execute()
}
}
}
}
}
fun getAvatarEdge(avatarID: String): CompletableFuture<String> {
return logValueFuture {
pool.connection.use {
it.prepareStatement(
"""
SELECT Edge_ID
FROM tw_edge
WHERE Avatar = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.executeQuery()
.use { rows -> if (rows.next()) rows.getString("Edge_ID") else "Default" }
}
}
}
}
fun getNote(want: String, src: Int, fit: Int, page: Int, viewUnit: Int): CompletableFuture<Any> {
val isNotWant = want.isEmpty()
var totalCount = 0
var topCount = 0
var lastPage = 0
val notes = mutableListOf<Any>()
return CompletableFuture.allOf(logFuture {
pool.connection.use { db ->
if (isNotWant) {
db.prepareStatement(
"""
SELECT SUM(Total_Count) AS Total_Count
FROM tw_note
""".trimIndent()
).use { dbStatement ->
dbStatement.executeQuery().use { rows ->
if (rows.next()) {
totalCount = rows.getInt("Total_Count")
}
}
}
} else {
db.prepareStatement(
when (src) {
0 -> """
SELECT SUM(Total_Count) AS Total_Count
FROM tw_note
WHERE MATCH(Title) AGAINST(? IN BOOLEAN MODE)
""".trimIndent()
1 -> """
SELECT COUNT(Comment_ID) AS Total_Count
FROM tw_comment
INNER JOIN tn_avatar ON tw_comment.Avatar = tn_avatar.Avatar_ID
WHERE MATCH(Avatar_Name) AGAINST(? IN BOOLEAN MODE)
""".trimIndent()
2 -> """
SELECT SUM(Total_Count) AS Total_Count
FROM tw_note
WHERE MATCH(Artist) AGAINST(? IN BOOLEAN MODE)
""".trimIndent()
3 -> """
SELECT SUM(Total_Count) AS Total_Count
FROM tw_note
WHERE MATCH(Genre) AGAINST(? IN BOOLEAN MODE)
""".trimIndent()
else -> null
}
).use { dbStatement ->
if (dbStatement != null) {
dbStatement.setString(1, "\"$want*\"")
dbStatement.executeQuery().use { rows ->
if (rows.next()) {
totalCount = rows.getInt("Total_Count")
}
}
}
}
}
}
}, logFuture {
pool.connection.use { db ->
if (isNotWant) {
db.prepareStatement(
"""
SELECT SUM(Top_Count) AS Top_Count
FROM tw_note
""".trimIndent()
).use { dbStatement ->
dbStatement.executeQuery().use { rows ->
if (rows.next()) {
topCount = rows.getInt("Top_Count")
}
}
}
} else db.prepareStatement(
when (src) {
0 -> """
SELECT SUM(Top_Count) AS Top_Count
FROM tw_note
WHERE MATCH(Title) AGAINST(? IN BOOLEAN MODE)
""".trimIndent()
1 -> """
SELECT COUNT(tw_comment.Note_ID) AS Top_Count
FROM tw_comment
INNER JOIN tn_avatar ON tw_comment.Avatar = tn_avatar.Avatar_ID
WHERE MATCH(Avatar_Name) AGAINST(? IN BOOLEAN MODE) AND Is_Max = true
""".trimIndent()
2 -> """
SELECT SUM(Top_Count) AS Top_Count
FROM tw_note
WHERE MATCH(Artist) AGAINST(? IN BOOLEAN MODE)
""".trimIndent()
3 -> """
SELECT SUM(Top_Count) AS Top_Count
FROM tw_note
WHERE MATCH(Genre) AGAINST(? IN BOOLEAN MODE)
""".trimIndent()
else -> null
}
).use { dbStatement ->
if (dbStatement != null) {
dbStatement.setString(1, "\"$want*\"")
dbStatement.executeQuery().use { rows ->
if (rows.next()) {
topCount = rows.getInt("Top_Count")
}
}
}
}
}
}, logFuture {
pool.connection.use { db ->
if (isNotWant) {
db.prepareStatement(
"""
SELECT COUNT(Note_ID) AS Note_Count
FROM tw_note
""".trimIndent()
).use { dbStatement ->
dbStatement.executeQuery().use { rows ->
if (rows.next()) {
lastPage = max(1.0, ceil(rows.getInt("Note_Count").toDouble() / viewUnit)).toInt()
}
}
}
} else {
db.prepareStatement(
when (src) {
0 -> """
SELECT COUNT(Note_ID) AS Note_Count
FROM tw_note
WHERE MATCH(Title) AGAINST(? IN BOOLEAN MODE)
""".trimIndent()
1 -> """
SELECT COUNT(tw_note.Note_ID) AS Note_Count
FROM tw_comment
INNER JOIN tw_note USING(Note_ID)
INNER JOIN tn_avatar ON tw_comment.Avatar = tn_avatar.Avatar_ID
WHERE MATCH(Avatar_Name) AGAINST(? IN BOOLEAN MODE)
""".trimIndent()
2 -> """
SELECT COUNT(Note_ID) AS Note_Count
FROM tw_note
WHERE MATCH(Artist) AGAINST(? IN BOOLEAN MODE)
""".trimIndent()
3 -> """
SELECT COUNT(Note_ID) AS Note_Count
FROM tw_note
WHERE MATCH(Genre) AGAINST(? IN BOOLEAN MODE)
""".trimIndent()
else -> null
}
).use { dbStatement ->
if (dbStatement != null) {
dbStatement.setString(1, "\"$want*\"")
dbStatement.executeQuery().use { rows ->
if (rows.next()) {
lastPage = max(1.0, ceil(rows.getInt("Note_Count").toDouble() / viewUnit)).toInt()
}
}
}
}
}
}
}, logFuture {
val dataStatement =
"Note_ID, Note_Variety, Artist, Title, Genre, Level_Text, tw_note.Level, Top_Count, Total_Count, tw_note.Date"
val fitStatement = when (fit) {
0 -> " ORDER BY Top_Count DESC "
1 -> " ORDER BY Total_Count DESC "
2 -> " ORDER BY tw_note.Date DESC "
3 -> " ORDER BY Title "
4 -> " ORDER BY Artist "
5 -> " ORDER BY Genre "
6 -> " ORDER BY Level_Text "
else -> " "
}
pool.connection.use { db ->
val dbStatement: PreparedStatement?
if (isNotWant) {
dbStatement = db.prepareStatement(
"SELECT $dataStatement FROM tw_note $fitStatement LIMIT ?, ?"
)
dbStatement.setInt(1, viewUnit * (page - 1))
dbStatement.setInt(2, viewUnit)
} else {
dbStatement = when (src) {
0 -> db.prepareStatement(
"""
SELECT $dataStatement
FROM tw_note
WHERE MATCH(Title) AGAINST(? IN BOOLEAN MODE)
$fitStatement
LIMIT ?, ?
""".trimIndent()
)
1 -> db.prepareStatement(
"""
SELECT $dataStatement
FROM tw_comment
INNER JOIN tw_note USING(Note_ID)
INNER JOIN tn_avatar ON tw_comment.Avatar = tn_avatar.Avatar_ID
WHERE MATCH(Avatar_Name) AGAINST(? IN BOOLEAN MODE) AND Is_Max = true
$fitStatement
LIMIT ?, ?
""".trimIndent()
)
2 -> db.prepareStatement(
"""
SELECT $dataStatement
FROM tw_note
WHERE MATCH(Artist) AGAINST(? IN BOOLEAN MODE)
$fitStatement
LIMIT ?, ?
""".trimIndent()
)
3 -> db.prepareStatement(
"""
SELECT $dataStatement
FROM tw_note
WHERE MATCH(Genre) AGAINST(? IN BOOLEAN MODE)
$fitStatement
LIMIT ?, ?
""".trimIndent()
)
else -> null
}
dbStatement?.setString(1, "\"$want*\"")
dbStatement?.setInt(2, viewUnit * (page - 1))
dbStatement?.setInt(3, viewUnit)
}
dbStatement?.use {
it.executeQuery().use { rows ->
while (rows.next()) {
notes.add(object {
val noteID = rows.getString("Note_ID")
val artist = rows.getString("Artist")
val title = rows.getString("Title")
val genre = rows.getString("Genre")
val levelText = rows.getString("Level_Text")
val level = rows.getInt("Level")
val topCount = rows.getInt("Top_Count")
val totalCount = rows.getInt("Total_Count")
val date = rows.getTimestamp("Date")
})
}
}
}
}
}).thenApply {
object {
val totalCount = totalCount
val topCount = topCount
val lastPage = lastPage
val notes = notes
}
}
}
fun getLevels(levelGroup: LevelSystem.LevelGroup): CompletableFuture<Collection<Any>> {
return logValueFuture {
pool.connection.use {
levelGroup.levelItems.mapNotNull { levelItem ->
it.prepareStatement(
"""
SELECT COUNT(Avatar) AS Count
FROM tw_level
WHERE Level_ID = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, levelItem.levelID)
dbStatement.executeQuery().use { rows ->
if (rows.next()) {
object {
val levelID = levelItem.levelID
val title = levelItem.title
val comment = levelItem.comment
val levelText = levelItem.levelText
val level = levelItem.level
val avatars = rows.getInt("Count")
}
} else {
null
}
}
}
}
}
}
}
fun getLevelNote(levelItem: LevelSystem.LevelGroup.LevelItem): CompletableFuture<Collection<Any>> {
return logValueFuture {
pool.connection.use {
levelItem.noteID.split("/".toRegex()).map { noteID ->
it.prepareStatement(
"""
SELECT Note_ID, Note_Variety, Artist, Title, Genre, Level_Text, Level
FROM tw_note
WHERE Note_ID = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, noteID)
dbStatement.executeQuery().use { rows ->
if (rows.next()) {
object {
val noteID = rows.getString("Note_ID")
val noteVariety = rows.getInt("Note_Variety")
val artist = rows.getString("Artist")
val title = rows.getString("Title")
val genre = rows.getString("Genre")
val levelText = rows.getString("Level_Text")
val level = rows.getInt("Level")
}
} else {
object {
val noteID = noteID
val noteVariety = Component.NoteVariety.EVENT_NOTE
val artist = null
val title = null
val genre = null
val levelText = null
val level = 0
}
}
}
}
}
}
}
}
fun getClearedAvatars(levelID: String): Collection<Any> {
return pool.connection.use {
it.prepareStatement(
"""
SELECT Avatar_ID, Avatar_Name, tw_level.Date
FROM tw_level
INNER JOIN tn_avatar ON tw_level.Avatar = tn_avatar.Avatar_ID
WHERE Level_ID = ?
ORDER BY tw_level.Date
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, levelID)
dbStatement.executeQuery().use { rows ->
val data = mutableListOf<Any>()
while (rows.next()) {
data.add(object {
val avatarID = rows.getString("Avatar_ID")
val avatarName = rows.getString("Avatar_Name")
val date = rows.getTimestamp("Date")
})
}
data
}
}
}
}
fun getAvatarLevels(avatarID: String): Triple<Int, Double, Double> {
return pool.connection.use {
it.prepareStatement(
"""
SELECT Avatar_XP
FROM tw_avatar
WHERE Avatar = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.executeQuery().use { rows ->
val avatarXP = if (rows.next()) rows.getDouble("Avatar_XP") else 0.0
val avatarLevel = Utility.getAvatarLevel(avatarXP)
val avatarXPNext = Utility.getAvatarXP(avatarLevel + 1)
Triple(1 + avatarLevel, avatarXPNext - avatarXP, avatarXPNext - Utility.getAvatarXP(avatarLevel))
}
}
}
}
fun getAvatar(want: String): CompletableFuture<Any?> {
return logValueFuture {
var avatarID = ""
var avatarName = ""
var avatarIntro = ""
var totalCount = 0
var totalLength = 0.0
var topCount = 0
var date = 0L
var wwwLevelIDCount = 0
var avatarLevel = 0
var avatarXP = 0.0
var totalAvatarXP = 0.0
var avatarAbility6K = 0.0
var avatarAbility6KPlace = 0
var avatarAbility6KCount = 0
var avatarAbility6KStatus = 0.0
var avatarAbility5K = 0.0
var avatarAbility5KPlace = 0
var avatarAbility5KCount = 0
var avatarAbility5KStatus = 0.0
var avatarAbility7K = 0.0
var avatarAbility7KPlace = 0
var avatarAbility7KCount = 0
var avatarAbility7KStatus = 0.0
var avatarAbility9K = 0.0
var avatarAbility9KPlace = 0
var avatarAbility9KCount = 0
var avatarAbility9KStatus = 0.0
val dateSet = mutableListOf<Long>()
val dateValues = mutableListOf<Int>()
val quitStatusValues = IntArray(7)
val wantAvatars = mutableListOf<Any>()
pool.connection.use {
if (want.startsWith('!')) {
it.prepareStatement(
"""
SELECT Avatar_ID
FROM tn_avatar
WHERE Avatar_ID = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, Utility.getDefaultAvatarID(want.substring(1)))
dbStatement.executeQuery().use { rows ->
if (rows.next()) {
avatarID = rows.getString("Avatar_ID")
}
}
}
} else {
it.prepareStatement(
"""
SELECT Avatar_ID, Avatar_Name, Avatar_Intro
FROM tn_avatar
WHERE MATCH(Avatar_Name) AGAINST(? IN BOOLEAN MODE)
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, "$want*")
dbStatement.executeQuery().use { rows ->
while (rows.next()) {
wantAvatars.add(object {
val avatarID = rows.getString("Avatar_ID")
val avatarName = rows.getString("Avatar_Name")
val avatarIntro = rows.getString("Avatar_Intro")
override fun toString(): String {
return this.avatarID
}
})
}
avatarID = wantAvatars.singleOrNull()?.toString() ?: ""
}
}
}
}
if (avatarID.isEmpty()) {
if (wantAvatars.isEmpty()) {
null
} else {
wantAvatars
}
} else {
val futures = CompletableFuture.allOf(logFuture {
val avatarLevels = getAvatarLevels(avatarID)
avatarLevel = avatarLevels.first
avatarXP = avatarLevels.second
totalAvatarXP = avatarLevels.third
}, logFuture {
pool.connection.use { db ->
db.prepareStatement(
"""
SELECT COUNT(Comment_ID) AS Total_Count, COUNT(DISTINCT Note_ID) AS Top_Count, Avatar_ID, Avatar_Name, Avatar_Intro, Last_Date
FROM tw_comment RIGHT OUTER JOIN tn_avatar
ON tw_comment.Avatar = tn_avatar.Avatar_ID
WHERE Avatar_ID = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.executeQuery().use { rows ->
if (rows.next()) {
totalCount = rows.getInt("Total_Count")
topCount = rows.getInt("Top_Count")
avatarName = rows.getString("Avatar_Name")
avatarIntro = rows.getString("Avatar_Intro")
date = rows.getTimestamp("Last_Date")?.toInstant()?.toEpochMilli() ?: 0
}
}
}
}
}, logFuture {
pool.connection.use { db ->
db.prepareStatement(
"""
SELECT COUNT(Level_ID) AS Count
FROM tw_level
WHERE Avatar = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.executeQuery().use { rows ->
if (rows.next()) {
wwwLevelIDCount = rows.getInt("Count")
}
}
}
}
}, logFuture {
pool.connection.use { db ->
db.prepareStatement(
"""
SELECT SUM(Length) AS Total_Length
FROM tw_comment
INNER JOIN tw_note USING(Note_ID)
INNER JOIN tn_avatar ON tw_comment.Avatar = tn_avatar.Avatar_ID
WHERE Avatar = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.executeQuery().use { rows ->
if (rows.next()) {
totalLength = rows.getDouble("Total_Length")
}
}
}
}
}, logFuture {
pool.connection.use { db ->
val millis = System.currentTimeMillis()
for (i in 90 downTo 0) {
val yyyyMMDD = yyyyMMDDFormat.format(
Instant.ofEpochMilli(millis - 86400000L * i)
.atZone(ZoneId.systemDefault())
)
dateSet.add(millis - 86400000L * i)
db.prepareStatement(
"""
SELECT COUNT(Comment_ID) AS Count
FROM tw_comment
WHERE Date BETWEEN "$yyyyMMDD 00:00:00" AND "$yyyyMMDD 23:59:59" AND Avatar = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.executeQuery().use {
if (it.next()) {
dateValues.add(it.getInt("Count"))
}
}
}
}
}
}, logFuture {
pool.connection.use { db ->
db.prepareStatement(
"""
SELECT Note_ID, Stand, Point
FROM tw_comment
WHERE Avatar = ? AND Is_Max = true
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.executeQuery().use {
while (it.next()) {
++quitStatusValues[Utility.getQuitStatusValue(
it.getDouble("Point"), it.getInt("Stand")
)]
}
}
}
}
}, logFuture {
val rawAbilities6K = getAbilities6K()
val targetAbilities6K = getAbilities(rawAbilities6K).toList()
val validAbilities6K = targetAbilities6K.stream().filter { it.second.second }.toList()
val targetAbility6K =
targetAbilities6K.stream().filter { it.first.first == avatarID }.findAny().orElse(
Pair(Pair(avatarID, avatarName), Pair(0.0, false))
)
avatarAbility6K = targetAbility6K.second.first
avatarAbility6KPlace = validAbilities6K.indexOf(targetAbility6K)
avatarAbility6KCount = validAbilities6K.size
avatarAbility6KStatus = AbilityClassSystem.getStatus(
AbilityClassSystem.AbilityClassVariety.INPUT_MODE_6K,
avatarAbility6K
)
}, logFuture {
val rawAbilities5K = getAbilities5K()
val targetAbilities5K = getAbilities(rawAbilities5K).toList()
val validAbilities5K = targetAbilities5K.stream().filter { it.second.second }.toList()
val targetAbility5K =
targetAbilities5K.stream().filter { it.first.first == avatarID }.findAny().orElse(
Pair(Pair(avatarID, avatarName), Pair(0.0, false))
)
avatarAbility5K = targetAbility5K.second.first
avatarAbility5KPlace = validAbilities5K.indexOf(targetAbility5K)
avatarAbility5KCount = validAbilities5K.size
avatarAbility5KStatus = AbilityClassSystem.getStatus(
AbilityClassSystem.AbilityClassVariety.INPUT_MODE_5K,
avatarAbility5K
)
}, logFuture {
val rawAbilities7K = getAbilities7K()
val targetAbilities7K = getAbilities(rawAbilities7K).toList()
val validAbilities7K = targetAbilities7K.stream().filter { it.second.second }.toList()
val targetAbility7K =
targetAbilities7K.stream().filter { it.first.first == avatarID }.findAny().orElse(
Pair(Pair(avatarID, avatarName), Pair(0.0, false))
)
avatarAbility7K = targetAbility7K.second.first
avatarAbility7KPlace = validAbilities7K.indexOf(targetAbility7K)
avatarAbility7KCount = validAbilities7K.size
avatarAbility7KStatus = AbilityClassSystem.getStatus(
AbilityClassSystem.AbilityClassVariety.INPUT_MODE_7K,
avatarAbility7K
)
}, logFuture {
val rawAbilities9K = getAbilities9K()
val targetAbilities9K = getAbilities(rawAbilities9K).toList()
val validAbilities9K = targetAbilities9K.stream().filter { it.second.second }.toList()
val targetAbility9K =
targetAbilities9K.stream().filter { it.first.first == avatarID }.findAny().orElse(
Pair(Pair(avatarID, avatarName), Pair(0.0, false))
)
avatarAbility9K = targetAbility9K.second.first
avatarAbility9KPlace = validAbilities9K.indexOf(targetAbility9K)
avatarAbility9KCount = validAbilities9K.size
avatarAbility9KStatus = AbilityClassSystem.getStatus(
AbilityClassSystem.AbilityClassVariety.INPUT_MODE_9K,
avatarAbility9K
)
})
futures.join()
object {
var avatarID = avatarID
val avatarName = avatarName
val avatarIntro = avatarIntro
val totalCount = totalCount
val totalLength = totalLength
val topCount = topCount
val date = date
val wwwLevelIDCount = wwwLevelIDCount
val avatarLevel = avatarLevel
val avatarXP = avatarXP
val totalAvatarXP = totalAvatarXP
val avatarAbility6K = avatarAbility6K
val avatarAbility6KPlace = avatarAbility6KPlace + 1
val avatarAbility6KCount = avatarAbility6KCount
val avatarAbility6KStatus = avatarAbility6KStatus
val avatarAbility5K = avatarAbility5K
val avatarAbility5KPlace = avatarAbility5KPlace + 1
val avatarAbility5KCount = avatarAbility5KCount
val avatarAbility5KStatus = avatarAbility5KStatus
val avatarAbility7K = avatarAbility7K
val avatarAbility7KPlace = avatarAbility7KPlace + 1
val avatarAbility7KCount = avatarAbility7KCount
val avatarAbility7KStatus = avatarAbility7KStatus
val avatarAbility9K = avatarAbility9K
val avatarAbility9KPlace = avatarAbility9KPlace + 1
val avatarAbility9KCount = avatarAbility9KCount
val avatarAbility9KStatus = avatarAbility9KStatus
val levelNames = arrayOf("*").plus(AbilitySystem.getAbilityNames())
val dateSet = dateSet
val dateValues = dateValues
val quitStatusValues = quitStatusValues
}
}
}
}
fun getMaxAbilityName(avatarID: String?): String {
val rawAbilities6K = getAbilities6K()
val targetAbilities6K = getAbilities(rawAbilities6K).toList()
val targetAbility6K =
targetAbilities6K.stream().filter { it.first.first == avatarID }.map { it.second }.findAny().orElse(
Pair(0.0, false)
).first
val rawAbilities5K = getAbilities5K()
val targetAbilities5K = getAbilities(rawAbilities5K).toList()
val targetAbility5K =
targetAbilities5K.stream().filter { it.first.first == avatarID }.map { it.second }.findAny().orElse(
Pair(0.0, false)
).first
val rawAbilities7K = getAbilities7K()
val targetAbilities7K = getAbilities(rawAbilities7K).toList()
val targetAbility7K =
targetAbilities7K.stream().filter { it.first.first == avatarID }.map { it.second }.findAny().orElse(
Pair(0.0, false)
).first
val rawAbilities9K = getAbilities9K()
val targetAbilities9K = getAbilities(rawAbilities9K).toList()
val targetAbility9K =
targetAbilities9K.stream().filter { it.first.first == avatarID }.map { it.second }.findAny().orElse(
Pair(0.0, false)
).first
return AbilityClassSystem.getPlatformAbilityText(
minOf(
AbilityClassSystem.getValue(AbilityClassSystem.AbilityClassVariety.INPUT_MODE_6K, targetAbility6K),
AbilityClassSystem.getValue(AbilityClassSystem.AbilityClassVariety.INPUT_MODE_5K, targetAbility5K),
AbilityClassSystem.getValue(AbilityClassSystem.AbilityClassVariety.INPUT_MODE_7K, targetAbility7K),
AbilityClassSystem.getValue(AbilityClassSystem.AbilityClassVariety.INPUT_MODE_9K, targetAbility9K)
)
)
}
fun getHandled(avatarID: String, noteID: String): Component.Handled {
return pool.connection.use { db ->
db.prepareStatement(
"""
SELECT Handled
FROM tw_handled
WHERE Note_ID = ? AND Avatar = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, noteID)
dbStatement.setString(2, avatarID)
dbStatement.executeQuery().use { rows ->
if (rows.next()) Component.Handled.getHandled(rows.getInt("Handled")) else Component.Handled.NOT
}
}
}
}
fun getAvatarFavorites(inputMode: Component.InputMode, avatarID: String): CompletableFuture<Any> {
return logValueFuture {
pool.connection.use { db ->
db.prepareStatement(
"""
SELECT tw_note.Note_ID, Title, Artist, Genre, Level_Text, Level, COUNT(Comment_ID) AS Count
FROM tw_comment
INNER JOIN tw_note USING(Note_ID)
WHERE Avatar = ? AND Input_Mode = ?
GROUP BY tw_note.Note_ID
ORDER BY COUNT DESC
LIMIT 50
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.setInt(2, inputMode.value)
dbStatement.executeQuery().use {
val data = mutableListOf<Any>()
while (it.next()) {
val noteID = it.getString("Note_ID")
data.add(object {
val noteID = noteID
val artist = it.getString("Artist")
val title = it.getString("Title")
val genre = it.getString("Genre")
val levelText = it.getString("Level_Text")
val level = it.getInt("Level")
val handled = getHandled(avatarID, noteID)
val totalCount = it.getInt("Count")
})
}
data
}
}
}
}
}
fun getAvatarLasts(inputMode: Component.InputMode, avatarID: String): CompletableFuture<Any> {
return logValueFuture {
pool.connection.use { db ->
db.prepareStatement(
"""
SELECT MAX(tw_comment.Date) AS Date, tw_note.Note_ID, Title, Artist, Genre, Level_Text, Level
FROM tw_comment
INNER JOIN tw_note USING(Note_ID)
WHERE Avatar = ? AND Input_Mode = ?
GROUP BY tw_note.Note_ID
ORDER BY Date DESC
LIMIT 50
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.setInt(2, inputMode.value)
dbStatement.executeQuery().use {
val data = mutableListOf<Any>()
while (it.next()) {
val noteID = it.getString("Note_ID")
data.add(object {
val noteID = noteID
val artist = it.getString("Artist")
val title = it.getString("Title")
val genre = it.getString("Genre")
val levelText = it.getString("Level_Text")
val level = it.getInt("Level")
val handled = getHandled(avatarID, noteID)
val date = it.getTimestamp("Date")
})
}
data
}
}
}
}
}
fun getAvatarWwwLevels(avatarID: String): CompletableFuture<Any> {
return logValueFuture {
pool.connection.use { db ->
db.prepareStatement(
"""
SELECT Level_ID, Date
FROM tw_level
WHERE Avatar = ?
ORDER BY Date DESC
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.executeQuery().use {
val data = mutableListOf<Any>()
while (it.next()) {
LevelSystem.getLevelItem(it.getString("Level_ID"))?.let { levelItem ->
data.add(object {
val levelID = levelItem.levelID
val title = levelItem.title
val levelText = levelItem.levelText
val level = levelItem.level
val date = it.getTimestamp("Date")
})
}
}
data
}
}
}
}
}
fun getAvatarAbility(inputMode: Component.InputMode, avatarID: String): CompletableFuture<Any> {
return logValueFuture {
(when (inputMode) {
Component.InputMode.INPUT_MODE_6 -> getAbilities6K(true)
Component.InputMode.INPUT_MODE_5_1 -> getAbilities5K(true)
Component.InputMode.INPUT_MODE_7_1 -> getAbilities7K(true)
Component.InputMode.INPUT_MODE_9 -> getAbilities9K(true)
else -> emptyMap()
}.entries.singleOrNull { it.key.first == avatarID } ?: AbstractMap.SimpleEntry(
Pair(avatarID, ""),
ArrayList()
)).value.sortedByDescending { it.ability }
}
}
interface IAvatarHandledItem {
val noteID: String
@get:JsonIgnore
val noteID128: String
@get:JsonIgnore
val noteID256: String
val stand: Int
val handled: Component.Handled
}
fun getAvatarHandled(avatarID: String, levelName: String): CompletableFuture<Any> {
return logValueFuture {
val data = mutableMapOf<String, Any>()
val noteIDAvatarHandledItemMap = mutableMapOf<String, IAvatarHandledItem>()
pool.connection.use { db ->
db.prepareStatement(
"""
SELECT tw_note.Note_ID, Note_ID_128, Note_ID_256, Title, Artist, Genre, Level_Text, Level, Stand
FROM tw_comment
INNER JOIN (tw_note) USING (Note_ID)
WHERE Avatar = ? AND Is_Max = true
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.executeQuery().use {
while (it.next()) {
val noteID = it.getString("Note_ID")
noteIDAvatarHandledItemMap[noteID] = object : IAvatarHandledItem {
override val noteID = noteID
override val noteID128 = it.getString("Note_ID_128")
override val noteID256 = it.getString("Note_ID_256")
val artist = it.getString("Artist")
val title = it.getString("Title")
val genre = it.getString("Genre")
val levelText = it.getString("Level_Text")
val level = it.getInt("Level")
override val stand = it.getInt("Stand")
override val handled = getHandled(avatarID, noteID)
}
}
}
}
}
fun setLevelID(levelID: String) {
val handledAvatarHandledItemMap = (if (levelID == "*") noteIDAvatarHandledItemMap.filter {
AbilitySystem.getAbilityNames(it.value.noteID128, it.value.noteID256).contains(levelName)
} else noteIDAvatarHandledItemMap.filter {
AbilitySystem.getAbilityIDs(it.value.noteID128, it.value.noteID256).contains(levelID)
}).values.groupBy { it.handled }
data[levelID] = object {
val handledBand1Count =
handledAvatarHandledItemMap.getOrDefault(Component.Handled.BAND1, emptyList()).size
val handledYell1Count =
handledAvatarHandledItemMap.getOrDefault(Component.Handled.YELL1, emptyList()).size
val handledHighestClearCount =
handledAvatarHandledItemMap.getOrDefault(Component.Handled.HIGHEST_CLEAR, emptyList()).size
val handledHigherClearCount =
handledAvatarHandledItemMap.getOrDefault(Component.Handled.HIGHER_CLEAR, emptyList()).size
val handledClearCount =
handledAvatarHandledItemMap.getOrDefault(Component.Handled.CLEAR, emptyList()).size
val handledAssistClearCount =
handledAvatarHandledItemMap.getOrDefault(Component.Handled.ASSIST_CLEAR, emptyList()).size
val noteIDCount = AbilitySystem.getNoteIDCount(levelName, levelID)
val avatarHandledItems = handledAvatarHandledItemMap.map {
Pair(
it.key,
it.value.sortedByDescending { avatarHandledItem -> avatarHandledItem.stand }.take(50)
)
}.toMap()
}
}
if (levelName == "*") {
setLevelID("*")
} else {
arrayOf("*").plus(AbilitySystem.getAbilityIDs(levelName)).forEach { setLevelID(it) }
}
data
}
}
fun setPass(avatar: String, targetComputing: Computing, avatarID: String) {
pool.connection.use { db ->
db.prepareStatement(
"""
REPLACE INTO tw_pass
VALUES(?, ?, ?, ?, ?, ?, ?)
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatar)
dbStatement.setString(2, targetComputing.title)
dbStatement.setString(3, targetComputing.artist)
dbStatement.setString(4, targetComputing.genre)
dbStatement.setString(5, targetComputing.levelText)
dbStatement.setString(6, targetComputing.noteID)
dbStatement.setString(7, avatarID)
dbStatement.execute()
}
}
}
fun getPasses(avatar: String): Collection<Any> {
return pool.connection.use { db ->
val data = mutableListOf<Any>()
db.prepareStatement(
"""
SELECT Title, Artist, Genre, Level_Text, Note_ID, tn_avatar.Avatar_ID, tn_avatar.Avatar_Name
FROM tw_pass
INNER JOIN tn_avatar ON tw_pass.Avatar_ID = tn_avatar.Avatar_ID
WHERE Avatar = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatar)
dbStatement.executeQuery().use { rows ->
while (rows.next()) {
data.add(object {
val title = rows.getString("Title")
val artist = rows.getString("Artist")
val genre = rows.getString("Genre")
val levelText = rows.getString("Level_Text")
val avatarID = rows.getString("Avatar_ID")
val avatarName = rows.getString("Avatar_Name")
val noteID = rows.getString("Note_ID")
})
}
}
}
db.prepareStatement(
"""
DELETE
FROM tw_pass
WHERE Avatar = ?
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatar)
dbStatement.execute()
}
data
}
}
interface IAvatarLevelVSItem {
val noteID: String
@get:JsonIgnore
val noteID128: String
@get:JsonIgnore
val noteID256: String
val stand: Int
var levelVSStand: Int
val handled: Component.Handled
}
fun getAvatarLevelVS(avatarID: String, targetID: String, levelName: String): CompletableFuture<Any> {
fun getLevelVSItems(
avatarID: String,
noteIDAvatarLevelVSMap: MutableMap<String, IAvatarLevelVSItem>
): CompletableFuture<Void> {
return logFuture {
pool.connection.use { db ->
db.prepareStatement(
"""
SELECT tw_note.Note_ID, Note_ID_128, Note_ID_256, Title, Artist, Genre, Level_Text, Level, Stand
FROM tw_comment
INNER JOIN (tw_note) USING (Note_ID)
WHERE Avatar = ? AND Is_Max = true
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.executeQuery().use { rows ->
while (rows.next()) {
val noteID = rows.getString("Note_ID")
noteIDAvatarLevelVSMap[noteID] = object : IAvatarLevelVSItem {
override val noteID = noteID
override val noteID128 = rows.getString("Note_ID_128")
override val noteID256 = rows.getString("Note_ID_256")
val artist = rows.getString("Artist")
val title = rows.getString("Title")
val genre = rows.getString("Genre")
val levelText = rows.getString("Level_Text")
val level = rows.getInt("Level")
override val stand = rows.getInt("Stand")
override var levelVSStand = 0
override val handled = getHandled(avatarID, noteID)
}
}
}
}
}
}
}
val data = mutableMapOf<String, Any>()
val noteIDAvatarLevelVSItemMap = mutableMapOf<String, IAvatarLevelVSItem>()
val noteIDTargetLevelVSItemMap = mutableMapOf<String, IAvatarLevelVSItem>()
return CompletableFuture.allOf(
getLevelVSItems(avatarID, noteIDAvatarLevelVSItemMap),
getLevelVSItems(targetID, noteIDTargetLevelVSItemMap)
).thenApply {
fun setLevelID(levelID: String) {
val avatarLevelVSItems = mutableSetOf<IAvatarLevelVSItem>()
val targetLevelVSItems = mutableSetOf<IAvatarLevelVSItem>()
(if (levelName == "*") noteIDAvatarLevelVSItemMap.keys.intersect(noteIDTargetLevelVSItemMap.keys) else if (levelID == "*") noteIDAvatarLevelVSItemMap.filter {
AbilitySystem.getAbilityNames(it.value.noteID128, it.value.noteID256).contains(levelName)
}.keys
.intersect(noteIDTargetLevelVSItemMap.filter {
AbilitySystem.getAbilityNames(it.value.noteID128, it.value.noteID256).contains(levelName)
}.keys) else noteIDAvatarLevelVSItemMap.filter {
AbilitySystem.getAbilityIDs(it.value.noteID128, it.value.noteID256).contains(levelID)
}.keys
.intersect(noteIDTargetLevelVSItemMap.filter {
AbilitySystem.getAbilityIDs(it.value.noteID128, it.value.noteID256).contains(levelID)
}.keys))
.forEach { noteID ->
val avatarLevelVSItem = noteIDAvatarLevelVSItemMap[noteID]
val targetLevelVSItem = noteIDTargetLevelVSItemMap[noteID]
if (avatarLevelVSItem != null && targetLevelVSItem != null) {
val levelVSStand = avatarLevelVSItem.stand - targetLevelVSItem.stand
if (levelVSStand > 0) {
avatarLevelVSItem.levelVSStand = levelVSStand
avatarLevelVSItems.add(avatarLevelVSItem)
} else if (levelVSStand < 0) {
targetLevelVSItem.levelVSStand = levelVSStand
targetLevelVSItems.add(targetLevelVSItem)
}
}
}
data[levelID] = object {
val avatarLevelVSCount = avatarLevelVSItems.size
val targetLevelVSCount = targetLevelVSItems.size
val avatarLevelVSItems = avatarLevelVSItems.sortedBy { it.levelVSStand }.take(50)
val targetLevelVSItems = targetLevelVSItems.sortedByDescending { it.levelVSStand }.take(50)
}
}
if (levelName == "*") {
setLevelID("*")
} else {
arrayOf("*").plus(AbilitySystem.getAbilityIDs(levelName)).forEach { setLevelID(it) }
}
data
}
}
private fun getAbilities6K(getHandled: Boolean = false): Map<Pair<String, String>, MutableCollection<JSON.TwilightWwwAvatarAbility>> {
val abilities6K = mutableMapOf<Pair<String, String>, MutableCollection<JSON.TwilightWwwAvatarAbility>>()
pool.connection.use {
it.prepareStatement(
"""
SELECT tw_note.Note_ID, Note_Variety, Artist, Title, Genre, Level_Text, tw_note.Level, Stand, Avatar_ID, Avatar_Name, Ability_6K * (POWER(Stand / 1000000, 3) - 0.5 * POWER(Stand / 1000000, 2) + 0.5 * (Stand / 1000000)) AS Value
FROM tw_comment
INNER JOIN tw_note USING(Note_ID)
INNER JOIN tn_avatar ON tw_comment.Avatar = tn_avatar.Avatar_ID
WHERE Ability_6K > 0.0 AND ${
getAbilityFilter(
Component.DEFAULT_INPUT_FAVOR_MODE,
Component.MODE_5_INPUT_FAVOR_MODE,
Component.MODE_5_1_INPUT_FAVOR_MODE
)
} AND Is_Max = true
ORDER BY Value DESC
""".trimIndent()
).use { dbStatement ->
dbStatement.executeQuery().use { rows ->
while (rows.next()) {
val avatarID = rows.getString("Avatar_ID")
val noteID = rows.getString("Note_ID")
val abilities = abilities6K.computeIfAbsent(
Pair(avatarID, rows.getString("Avatar_Name"))
) { ArrayList() }
if (abilities.size < 50) {
abilities.add(
JSON.TwilightWwwAvatarAbility(
rows,
0.95.pow(abilities.size.toDouble()),
if (getHandled) getHandled(avatarID, noteID) else Component.Handled.NOT
)
)
}
}
}
}
}
return abilities6K
}
fun getAvatarAbility6K(avatarID: String): Double {
pool.connection.use { db ->
db.prepareStatement(
"""
SELECT Ability_6K * (POWER(Stand / 1000000, 3) - 0.5 * POWER(Stand / 1000000, 2) + 0.5 * (Stand / 1000000)) AS Value
FROM tw_comment
INNER JOIN tw_note USING(Note_ID)
INNER JOIN tn_avatar ON tw_comment.Avatar = tn_avatar.Avatar_ID
WHERE Avatar = ? AND Ability_6K > 0.0 AND ${
getAbilityFilter(
Component.DEFAULT_INPUT_FAVOR_MODE,
Component.MODE_5_INPUT_FAVOR_MODE,
Component.MODE_5_1_INPUT_FAVOR_MODE
)
} AND Is_Max = true
ORDER BY Value DESC
LIMIT 50
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.executeQuery().use { rows ->
val abilities = mutableListOf<Double>()
while (rows.next()) {
abilities.add(rows.getDouble("Value") * 0.95.pow(abilities.size.toDouble()))
}
return abilities.sumOf { it }
}
}
}
}
private fun getAbilities5K(getHandled: Boolean = false): Map<Pair<String, String>, MutableCollection<JSON.TwilightWwwAvatarAbility>> {
val abilities5K = mutableMapOf<Pair<String, String>, MutableCollection<JSON.TwilightWwwAvatarAbility>>()
pool.connection.use {
it.prepareStatement(
"""
SELECT tw_note.Note_ID, Note_Variety, Artist, Title, Genre, Level_Text, tw_note.Level, Stand, Avatar_ID, Avatar_Name, Ability_5K * (POWER(Stand / 1000000, 3) - 0.5 * POWER(Stand / 1000000, 2) + 0.5 * (Stand / 1000000)) AS Value
FROM tw_comment
INNER JOIN tw_note USING(Note_ID)
INNER JOIN tn_avatar ON tw_comment.Avatar = tn_avatar.Avatar_ID
WHERE Ability_5K > 0.0 AND ${
getAbilityFilter(
Component.DEFAULT_INPUT_FAVOR_MODE,
Component.MODE_5_INPUT_FAVOR_MODE,
Component.MODE_5_1_INPUT_FAVOR_MODE
)
} AND Is_Max = true
ORDER BY Value DESC
""".trimIndent()
).use { dbStatement ->
dbStatement.executeQuery().use { rows ->
while (rows.next()) {
val avatarID = rows.getString("Avatar_ID")
val noteID = rows.getString("Note_ID")
val abilities = abilities5K.computeIfAbsent(
Pair(avatarID, rows.getString("Avatar_Name"))
) { ArrayList() }
if (abilities.size < 50) {
abilities.add(
JSON.TwilightWwwAvatarAbility(
rows,
0.95.pow(abilities.size.toDouble()),
if (getHandled) getHandled(avatarID, noteID) else Component.Handled.NOT
)
)
}
}
}
}
}
return abilities5K
}
fun getAvatarAbility5K(avatarID: String): Double {
pool.connection.use { db ->
db.prepareStatement(
"""
SELECT Ability_5K * (POWER(Stand / 1000000, 3) - 0.5 * POWER(Stand / 1000000, 2) + 0.5 * (Stand / 1000000)) AS Value
FROM tw_comment
INNER JOIN tw_note USING(Note_ID)
INNER JOIN tn_avatar ON tw_comment.Avatar = tn_avatar.Avatar_ID
WHERE Avatar = ? AND Ability_5K > 0.0 AND ${
getAbilityFilter(
Component.DEFAULT_INPUT_FAVOR_MODE,
Component.MODE_5_INPUT_FAVOR_MODE,
Component.MODE_5_1_INPUT_FAVOR_MODE
)
} AND Is_Max = true
ORDER BY Value DESC
LIMIT 50
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.executeQuery().use { rows ->
val abilities = mutableListOf<Double>()
while (rows.next()) {
abilities.add(rows.getDouble("Value") * 0.95.pow(abilities.size.toDouble()))
}
return abilities.sumOf { it }
}
}
}
}
private fun getAbilities7K(getHandled: Boolean = false): Map<Pair<String, String>, MutableCollection<JSON.TwilightWwwAvatarAbility>> {
val abilities7K = mutableMapOf<Pair<String, String>, MutableCollection<JSON.TwilightWwwAvatarAbility>>()
pool.connection.use {
it.prepareStatement(
"""
SELECT tw_note.Note_ID, Note_Variety, Artist, Title, Genre, Level_Text, tw_note.Level, Stand, Avatar_ID, Avatar_Name, Ability_7K * (POWER(Stand / 1000000, 3) - 0.5 * POWER(Stand / 1000000, 2) + 0.5 * (Stand / 1000000)) AS Value
FROM tw_comment
INNER JOIN tw_note USING(Note_ID)
INNER JOIN tn_avatar ON tw_comment.Avatar = tn_avatar.Avatar_ID
WHERE Ability_7K > 0.0 AND ${
getAbilityFilter(
Component.DEFAULT_INPUT_FAVOR_MODE,
Component.MODE_7_INPUT_FAVOR_MODE,
Component.MODE_7_1_INPUT_FAVOR_MODE
)
} AND Is_Max = true
ORDER BY Value DESC
""".trimIndent()
).use { dbStatement ->
dbStatement.executeQuery().use { rows ->
while (rows.next()) {
val avatarID = rows.getString("Avatar_ID")
val noteID = rows.getString("Note_ID")
val abilities = abilities7K.computeIfAbsent(
Pair(avatarID, rows.getString("Avatar_Name"))
) { ArrayList() }
if (abilities.size < 50) {
abilities.add(
JSON.TwilightWwwAvatarAbility(
rows,
0.95.pow(abilities.size.toDouble()),
if (getHandled) getHandled(avatarID, noteID) else Component.Handled.NOT
)
)
}
}
}
}
}
return abilities7K
}
fun getAvatarAbility7K(avatarID: String): Double {
pool.connection.use { db ->
db.prepareStatement(
"""
SELECT Ability_7K * (POWER(Stand / 1000000, 3) - 0.5 * POWER(Stand / 1000000, 2) + 0.5 * (Stand / 1000000)) AS Value
FROM tw_comment
INNER JOIN tw_note USING(Note_ID)
INNER JOIN tn_avatar ON tw_comment.Avatar = tn_avatar.Avatar_ID
WHERE Avatar = ? AND Ability_7K > 0.0 AND ${
getAbilityFilter(
Component.DEFAULT_INPUT_FAVOR_MODE,
Component.MODE_7_INPUT_FAVOR_MODE,
Component.MODE_7_1_INPUT_FAVOR_MODE
)
} AND Is_Max = true
ORDER BY Value DESC
LIMIT 50
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.executeQuery().use { rows ->
val abilities = mutableListOf<Double>()
while (rows.next()) {
abilities.add(rows.getDouble("Value") * 0.95.pow(abilities.size.toDouble()))
}
return abilities.sumOf { it }
}
}
}
}
private fun getAbilities9K(getHandled: Boolean = false): Map<Pair<String, String>, MutableCollection<JSON.TwilightWwwAvatarAbility>> {
val abilities9K = mutableMapOf<Pair<String, String>, MutableCollection<JSON.TwilightWwwAvatarAbility>>()
pool.connection.use {
it.prepareStatement(
"""
SELECT tw_note.Note_ID, Note_Variety, Artist, Title, Genre, Level_Text, tw_note.Level, Stand, Avatar_ID, Avatar_Name, Ability_9K * (POWER(Stand / 1000000, 3) - 0.5 * POWER(Stand / 1000000, 2) + 0.5 * (Stand / 1000000)) AS Value
FROM tw_comment
INNER JOIN tw_note USING(Note_ID)
INNER JOIN tn_avatar ON tw_comment.Avatar = tn_avatar.Avatar_ID
WHERE Ability_9K > 0.0 AND ${
getAbilityFilter(
Component.DEFAULT_INPUT_FAVOR_MODE,
Component.MODE_9_INPUT_FAVOR_MODE
)
} AND Is_Max = true
ORDER BY Value DESC
""".trimIndent()
).use { dbStatement ->
dbStatement.executeQuery().use { rows ->
while (rows.next()) {
val avatarID = rows.getString("Avatar_ID")
val noteID = rows.getString("Note_ID")
val abilities = abilities9K.computeIfAbsent(
Pair(avatarID, rows.getString("Avatar_Name"))
) { ArrayList() }
if (abilities.size < 50) {
abilities.add(
JSON.TwilightWwwAvatarAbility(
rows,
0.95.pow(abilities.size.toDouble()),
if (getHandled) getHandled(avatarID, noteID) else Component.Handled.NOT
)
)
}
}
}
}
}
return abilities9K
}
fun getAvatarAbility9K(avatarID: String): Double {
pool.connection.use { db ->
db.prepareStatement(
"""
SELECT Ability_9K * (POWER(Stand / 1000000, 3) - 0.5 * POWER(Stand / 1000000, 2) + 0.5 * (Stand / 1000000)) AS Value
FROM tw_comment
INNER JOIN tw_note USING(Note_ID)
INNER JOIN tn_avatar ON tw_comment.Avatar = tn_avatar.Avatar_ID
WHERE Avatar = ? AND Ability_9K > 0.0 AND ${
getAbilityFilter(
Component.DEFAULT_INPUT_FAVOR_MODE,
Component.MODE_9_INPUT_FAVOR_MODE
)
} AND Is_Max = true
ORDER BY Value DESC
LIMIT 50
""".trimIndent()
).use { dbStatement ->
dbStatement.setString(1, avatarID)
dbStatement.executeQuery().use { rows ->
val abilities = mutableListOf<Double>()
while (rows.next()) {
abilities.add(rows.getDouble("Value") * 0.95.pow(abilities.size.toDouble()))
}
return abilities.sumOf { it }
}
}
}
}
private fun getAbilities(abilities: Map<Pair<String, String>, MutableCollection<JSON.TwilightWwwAvatarAbility>>): Stream<Pair<Pair<String, String>, Pair<Double, Boolean>>> {
return abilities.entries.stream().map { (avatar, abilities) ->
Pair(
avatar, Pair(
abilities.sumOf { it.ability }, !abilities.isEmpty()
)
)
}.sorted { o1, o2 ->
val value = o2.second.first - o1.second.first
if (value > 0) {
1
} else if (value < 0) {
-1
} else {
0
}
}
}
fun getHallTotalTotal(): CompletableFuture<Any> {
return logValueFuture {
pool.connection.use {
it.prepareStatement(
"""
SELECT Avatar_ID, Avatar_Name, COUNT(tw_comment.Note_ID) AS Value
FROM tw_comment
INNER JOIN tn_avatar ON tw_comment.Avatar = tn_avatar.Avatar_ID
GROUP BY Avatar_ID
ORDER BY Value DESC
LIMIT 50
""".trimIndent()
).use { dbStatement ->
dbStatement.executeQuery().use { rows ->
val data = mutableListOf<Any>()
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 getHallAtTotal(): CompletableFuture<Any> {
return logValueFuture {
pool.connection.use {
it.prepareStatement(
"""
SELECT Avatar_ID, Avatar_Name, COUNT(tw_comment.Note_ID) AS Value
FROM tw_comment
INNER JOIN tn_avatar ON tw_comment.Avatar = tn_avatar.Avatar_ID
WHERE "${
yyyyMMDDFormat.format(
LocalDate.now().withDayOfMonth(1)
)
}" <= tw_comment.Date
GROUP BY Avatar_ID
ORDER BY Value DESC
LIMIT 50
""".trimIndent()
).use { dbStatement ->
dbStatement.executeQuery().use { rows ->
val data = mutableListOf<Any>()
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 getHallTotalTop(): CompletableFuture<Any> {
return logValueFuture {
pool.connection.use {
it.prepareStatement(
"""
SELECT Avatar_ID, Avatar_Name, COUNT(Note_ID) AS Value
FROM tw_comment
INNER JOIN tn_avatar ON tw_comment.Avatar = tn_avatar.Avatar_ID
WHERE Is_Max = true
GROUP BY Avatar_ID
ORDER BY Value DESC
LIMIT 50
""".trimIndent()
).use { dbStatement ->
dbStatement.executeQuery().use { rows ->
val data = mutableListOf<Any>()
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 getHallAtTop(): CompletableFuture<Any> {
return logValueFuture {
pool.connection.use {
it.prepareStatement(
"""
SELECT Avatar_ID, Avatar_Name, COUNT(Note_ID) AS Value
FROM tw_comment
INNER JOIN tn_avatar ON tw_comment.Avatar = tn_avatar.Avatar_ID
WHERE "${
yyyyMMDDFormat.format(
LocalDate.now().withDayOfMonth(1)
)
}" <= tw_comment.Date AND Is_Max = true
GROUP BY Avatar_ID
ORDER BY Value DESC
LIMIT 50
""".trimIndent()
).use { dbStatement ->
dbStatement.executeQuery().use { rows ->
val data = mutableListOf<Any>()
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 getHallTotalStand(): CompletableFuture<Any> {
return logValueFuture {
pool.connection.use {
it.prepareStatement(
"""
SELECT Avatar_ID, Avatar_Name, SUM(Stand) AS Value
FROM tw_comment
INNER JOIN tn_avatar ON tw_comment.Avatar = tn_avatar.Avatar_ID
WHERE Is_Max = true
GROUP BY Avatar_ID
ORDER BY Value DESC
LIMIT 50
""".trimIndent()
).use { dbStatement ->
dbStatement.executeQuery().use { rows ->
val data = mutableListOf<Any>()
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 getHallAtStand(): CompletableFuture<Any> {
return logValueFuture {
pool.connection.use {
it.prepareStatement(
"""
SELECT Avatar_ID, Avatar_Name, SUM(Stand) AS Value
FROM tw_comment
INNER JOIN tn_avatar ON tw_comment.Avatar = tn_avatar.Avatar_ID
WHERE "${
yyyyMMDDFormat.format(
LocalDate.now().withDayOfMonth(1)
)
}" <= tw_comment.Date AND Is_Max = true
GROUP BY Avatar_ID
ORDER BY Value DESC
LIMIT 50
""".trimIndent()
).use { dbStatement ->
dbStatement.executeQuery().use { rows ->
val data = mutableListOf<Any>()
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 getHallTotalBand(): CompletableFuture<Any> {
return logValueFuture {
pool.connection.use {
it.prepareStatement(
"""
SELECT Avatar_ID, Avatar_Name, MAX(Band) AS Value
FROM tw_comment
INNER JOIN tn_avatar ON tw_comment.Avatar = tn_avatar.Avatar_ID
GROUP BY Avatar_ID
ORDER BY Value DESC
LIMIT 50
""".trimIndent()
).use { dbStatement ->
dbStatement.executeQuery().use { rows ->
val data = mutableListOf<Any>()
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 getHallAtBand(): CompletableFuture<Any> {
return logValueFuture {
pool.connection.use {
it.prepareStatement(
"""
SELECT Avatar_ID, Avatar_Name, MAX(Band) AS Value
FROM tw_comment
INNER JOIN tn_avatar ON tw_comment.Avatar = tn_avatar.Avatar_ID
WHERE "${
yyyyMMDDFormat.format(
LocalDate.now().withDayOfMonth(1)
)
}" <= tw_comment.Date
GROUP BY Avatar_ID
ORDER BY Value DESC
LIMIT 50
""".trimIndent()
).use { dbStatement ->
dbStatement.executeQuery().use { rows ->
val data = mutableListOf<Any>()
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 getHallAbility(inputMode: Component.InputMode): CompletableFuture<Any> {
return logValueFuture {
getAbilities(
when (inputMode) {
Component.InputMode.INPUT_MODE_6 -> getAbilities6K()
Component.InputMode.INPUT_MODE_5_1 -> getAbilities5K()
Component.InputMode.INPUT_MODE_7_1 -> getAbilities7K()
Component.InputMode.INPUT_MODE_9 -> getAbilities9K()
else -> emptyMap()
}
).limit(50).map { (avatar, abilities) ->
object {
val avatarID = avatar.first
val avatarName = avatar.second
val value = abilities.first
}
}.toArray()
}
}
fun getHallLevel(): CompletableFuture<Any> {
return logValueFuture {
pool.connection.use {
it.prepareStatement(
"""
SELECT Avatar_ID, Avatar_Name, Avatar_XP
FROM tw_avatar
INNER JOIN tn_avatar ON tw_avatar.Avatar = tn_avatar.Avatar_ID
GROUP BY Avatar_ID
ORDER BY Avatar_XP DESC
LIMIT 50
""".trimIndent()
).use { dbStatement ->
dbStatement.executeQuery().use { rows ->
val data = mutableListOf<Any>()
while (rows.next()) {
data.add(object {
val avatarID = rows.getString("Avatar_ID")
val avatarName = rows.getString("Avatar_Name")
val value = 1 + Utility.getAvatarLevel(rows.getDouble("Avatar_XP"))
})
}
data
}
}
}
}
}
fun getEtc(language: String): CompletableFuture<Any> {
val yyyyMM = yyyyMMFormat.format(Instant.now())
val totalDateSet = mutableListOf<String>()
val totalDateValues = mutableListOf<Int>()
val enrollDateSet = mutableListOf<String>()
val enrollDateValues = mutableListOf<Int>()
val avatarDateSet = mutableListOf<String>()
val avatarDateValues = mutableListOf<Int>()
val totalNoteFiles = mutableListOf<Any>()
val totalTitles = mutableListOf<Any>()
val totalEdges = mutableListOf<Any>()
val favorites = mutableListOf<Any>()
val favoritesAt = mutableListOf<Any>()
val inputModes = mutableListOf<Any>()
val autoModes = mutableListOf<Any>()
val noteSaltModes = mutableListOf<Any>()
val faintNoteModes = mutableListOf<Any>()
val judgmentModes = mutableListOf<Any>()
val hitPointsModes = mutableListOf<Any>()
val noteMobilityModes = mutableListOf<Any>()
val longNoteModes = mutableListOf<Any>()
val inputFavorModes = mutableListOf<Any>()
val noteModifyModes = mutableListOf<Any>()
val lowestJudgmentConditionModes = mutableListOf<Any>()
return CompletableFuture.allOf(
logFuture {
pool.connection.use {
it.prepareStatement(
"""
SELECT DATE_FORMAT(Date, "%Y-%m") AS Etc_Date, COUNT(Avatar) AS Value
FROM tw_comment
GROUP BY Etc_Date
HAVING Etc_Date != "0000-00"
""".trimIndent()
).use { dbStatement ->
dbStatement.executeQuery().use { rows ->
while (rows.next()) {
totalDateSet.add(rows.getString("Etc_Date"))
totalDateValues.add(rows.getInt("Value"))
}
}
}
}
},
logFuture {
pool.connection.use {
it.prepareStatement(
"""
SELECT DATE_FORMAT(Date, "%Y-%m") AS Etc_Date, COUNT(Avatar_ID) AS Value
FROM tn_avatar
GROUP BY Etc_Date
HAVING Etc_Date != "0000-00"
""".trimIndent()
).use { dbStatement ->
dbStatement.executeQuery().use { rows ->
while (rows.next()) {
enrollDateSet.add(rows.getString("Etc_Date"))
enrollDateValues.add(rows.getInt("Value"))
}
}
}
}
},
logFuture {
pool.connection.use {
it.prepareStatement(
"""
SELECT DATE_FORMAT(Date, "%Y-%m") AS Etc_Date, COUNT(DISTINCT Avatar) AS Value
FROM tw_comment
GROUP BY Etc_Date
HAVING Etc_Date != "0000-00"
""".trimIndent()
).use { dbStatement ->
dbStatement.executeQuery().use { rows ->
while (rows.next()) {
avatarDateSet.add(rows.getString("Etc_Date"))
avatarDateValues.add(rows.getInt("Value"))
}
}
}
}
},
logFuture {
pool.connection.use {
it.prepareStatement(
"""
SELECT tw_comment.Note_ID, Title, Artist, Genre, Level, Level_Text, COUNT(tw_comment.Note_ID) AS Value
FROM tw_comment
INNER JOIN (tw_note) USING (Note_ID)
WHERE tw_comment.Date BETWEEN "$yyyyMM-01" AND "$yyyyMM-31"
GROUP BY tw_comment.Note_ID
ORDER BY Value DESC
LIMIT 10;
""".trimIndent()
).use { dbStatement ->
dbStatement.executeQuery().use { rows ->
while (rows.next()) {
totalNoteFiles.add(object {
val noteID = rows.getString("Note_ID")
val title = rows.getString("Title")
val artist = rows.getString("Artist")
val genre = rows.getString("Genre")
val level = rows.getInt("Level")
val levelText = rows.getString("Level_Text")
val value = rows.getInt("Value")
})
}
}
}
}
},
logFuture {
pool.connection.use {
it.prepareStatement(
"""
SELECT Title_ID, COUNT(Title_ID) AS Value
FROM tw_title
GROUP BY Title_ID
ORDER BY Value DESC
LIMIT 10;
""".trimIndent()
).use { dbStatement ->
dbStatement.executeQuery().use { rows ->
while (rows.next()) {
val wwwTitle = TitleSystem.getTitle(language, rows.getString("Title_ID"))
if (wwwTitle != null) {
totalTitles.add(object {
val title = wwwTitle.title
val titleColor = wwwTitle.titleColor
val value = rows.getInt("Value")
})
}
}
}
}
}
},
logFuture {
pool.connection.use {
it.prepareStatement(
"""
SELECT Edge_ID, COUNT(Edge_ID) AS Value
FROM tw_edge
WHERE Edge_ID != "Default"
GROUP BY Edge_ID
ORDER BY Value DESC
LIMIT 10;
""".trimIndent()
).use { dbStatement ->
dbStatement.executeQuery().use { rows ->
while (rows.next()) {
totalEdges.add(object {
val edge =
Base64.getEncoder()
.encodeToString(EdgeSystem.getDrawing(rows.getString("Edge_ID")))
val value = rows.getInt("Value")
})
}
}
}
}
},
logFuture {
pool.connection.use {
it.prepareStatement(
"""
SELECT tw_favor.Note_ID, Title, Artist, Genre, Level, Level_Text, SUM(2 * Favor - 1) AS Value
FROM tw_favor
INNER JOIN tw_note USING(Note_ID)
GROUP BY tw_favor.Note_ID
HAVING Value > 0
ORDER BY Value DESC
LIMIT 10;
""".trimIndent()
).use { dbStatement ->
dbStatement.executeQuery().use { rows ->
while (rows.next()) {
favorites.add(object {
val noteID = rows.getString("Note_ID")
val title = rows.getString("Title")
val artist = rows.getString("Artist")
val genre = rows.getString("Genre")
val level = rows.getInt("Level")
val levelText = rows.getString("Level_Text")
val value = rows.getInt("Value")
})
}
}
}
}
},
logFuture {
pool.connection.use {
it.prepareStatement(
"""
SELECT tw_favor.Note_ID, Title, Artist, Genre, Level, Level_Text, SUM(2 * Favor - 1) AS Value
FROM tw_favor
INNER JOIN tw_note USING(Note_ID)
WHERE tw_favor.Date BETWEEN "$yyyyMM-01" AND "$yyyyMM-31"
GROUP BY tw_favor.Note_ID
HAVING Value > 0
ORDER BY Value DESC
LIMIT 10;
""".trimIndent()
).use { dbStatement ->
dbStatement.executeQuery().use { rows ->
while (rows.next()) {
favoritesAt.add(object {
val noteID = rows.getString("Note_ID")
val title = rows.getString("Title")
val artist = rows.getString("Artist")
val genre = rows.getString("Genre")
val level = rows.getInt("Level")
val levelText = rows.getString("Level_Text")
val value = rows.getInt("Value")
})
}
}
}
}
},
logFuture {
pool.connection.use {
it.prepareStatement(
"""
SELECT Input_Mode AS Mode, COUNT(Input_Mode) AS Value
FROM tw_comment
INNER JOIN tw_note USING(Note_ID)
GROUP BY Mode
ORDER BY Value DESC
""".trimIndent()
).use { dbStatement ->
dbStatement.executeQuery().use { rows ->
while (rows.next()) {
inputModes.add(object {
val mode = rows.getInt("Mode")
val value = rows.getInt("Value")
})
}
}
}
}
},
logFuture {
pool.connection.use {
it.prepareStatement(
"""
SELECT Auto_Mode AS Mode, COUNT(Auto_Mode) AS Value
FROM tw_comment
GROUP BY Mode
ORDER BY Value DESC
""".trimIndent()
).use { dbStatement ->
dbStatement.executeQuery().use { rows ->
while (rows.next()) {
autoModes.add(object {
val mode = rows.getInt("Mode")
val value = rows.getInt("Value")
})
}
}
}
}
},
logFuture {
pool.connection.use {
it.prepareStatement(
"""
SELECT Note_Salt_Mode AS Mode, COUNT(Note_Salt_Mode) AS Value
FROM tw_comment
GROUP BY Mode
ORDER BY Value DESC
""".trimIndent()
).use { dbStatement ->
dbStatement.executeQuery().use { rows ->
while (rows.next()) {
noteSaltModes.add(object {
val mode = rows.getInt("Mode")
val value = rows.getInt("Value")
})
}
}
}
}
},
logFuture {
pool.connection.use {
it.prepareStatement(
"""
SELECT Faint_Note_Mode AS Mode, COUNT(Faint_Note_Mode) AS Value
FROM tw_comment
GROUP BY Mode
ORDER BY Value DESC
""".trimIndent()
).use { dbStatement ->
dbStatement.executeQuery().use { rows ->
while (rows.next()) {
faintNoteModes.add(object {
val mode = rows.getInt("Mode")
val value = rows.getInt("Value")
})
}
}
}
}
},
logFuture {
pool.connection.use {
it.prepareStatement(
"""
SELECT Judgment_Mode AS Mode, COUNT(Judgment_Mode) AS Value
FROM tw_comment
GROUP BY Mode
ORDER BY Value DESC
""".trimIndent()
).use { dbStatement ->
dbStatement.executeQuery().use { rows ->
while (rows.next()) {
judgmentModes.add(object {
val mode = rows.getInt("Mode")
val value = rows.getInt("Value")
})
}
}
}
}
},
logFuture {
pool.connection.use {
it.prepareStatement(
"""
SELECT Hit_Points_Mode AS Mode, COUNT(Hit_Points_Mode) AS Value
FROM tw_comment
GROUP BY Mode
ORDER BY Value DESC
""".trimIndent()
).use { dbStatement ->
dbStatement.executeQuery().use { rows ->
while (rows.next()) {
hitPointsModes.add(object {
val mode = rows.getInt("Mode")
val value = rows.getInt("Value")
})
}
}
}
}
},
logFuture {
pool.connection.use {
it.prepareStatement(
"""
SELECT Note_Mobility_Mode AS Mode, COUNT(Note_Mobility_Mode) AS Value
FROM tw_comment
GROUP BY Mode
ORDER BY Value DESC
""".trimIndent()
).use { dbStatement ->
dbStatement.executeQuery().use { rows ->
while (rows.next()) {
noteMobilityModes.add(object {
val mode = rows.getInt("Mode")
val value = rows.getInt("Value")
})
}
}
}
}
},
logFuture {
pool.connection.use {
it.prepareStatement(
"""
SELECT Long_Note_Mode AS Mode, COUNT(Long_Note_Mode) AS Value
FROM tw_comment
GROUP BY Mode
ORDER BY Value DESC
""".trimIndent()
).use { dbStatement ->
dbStatement.executeQuery().use { rows ->
while (rows.next()) {
longNoteModes.add(object {
val mode = rows.getInt("Mode")
val value = rows.getInt("Value")
})
}
}
}
}
},
logFuture {
pool.connection.use {
it.prepareStatement(
"""
SELECT Input_Favor_Mode AS Mode, COUNT(Input_Favor_Mode) AS Value
FROM tw_comment
GROUP BY Mode
ORDER BY Value DESC
""".trimIndent()
).use { dbStatement ->
dbStatement.executeQuery().use { rows ->
while (rows.next()) {
inputFavorModes.add(object {
val mode = rows.getInt("Mode")
val value = rows.getInt("Value")
})
}
}
}
}
},
logFuture {
pool.connection.use {
it.prepareStatement(
"""
SELECT Note_Modify_Mode AS Mode, COUNT(Note_Modify_Mode) AS Value
FROM tw_comment
GROUP BY Mode
ORDER BY Value DESC
""".trimIndent()
).use { dbStatement ->
dbStatement.executeQuery().use { rows ->
while (rows.next()) {
noteModifyModes.add(object {
val mode = rows.getInt("Mode")
val value = rows.getInt("Value")
})
}
}
}
}
},
logFuture {
pool.connection.use {
it.prepareStatement(
"""
SELECT Lowest_Judgment_Condition_Mode AS Mode, COUNT(Lowest_Judgment_Condition_Mode) AS Value
FROM tw_comment
GROUP BY Mode
ORDER BY Value DESC
""".trimIndent()
).use { dbStatement ->
dbStatement.executeQuery().use { rows ->
while (rows.next()) {
lowestJudgmentConditionModes.add(object {
val mode = rows.getInt("Mode")
val value = rows.getInt("Value")
})
}
}
}
}
}
).thenApply {
object {
val totalDateSet = totalDateSet
val totalDateValues = totalDateValues
val enrollDateSet = enrollDateSet
val enrollDateValues = enrollDateValues
val avatarDateSet = avatarDateSet
val avatarDateValues = avatarDateValues
val totalNoteFiles = totalNoteFiles
val totalTitles = totalTitles
val totalEdges = totalEdges
val favorites = favorites
val favoritesAt = favoritesAt
val inputModes = inputModes
val autoModes = autoModes
val noteSaltModes = noteSaltModes
val faintNoteModes = faintNoteModes
val judgmentModes = judgmentModes
val hitPointsModes = hitPointsModes
val noteMobilityModes = noteMobilityModes
val longNoteModes = longNoteModes
val inputFavorModes = inputFavorModes
val noteModifyModes = noteModifyModes
val lowestJudgmentConditionModes = lowestJudgmentConditionModes
}
}
}
}
