diff --git a/src/main/kotlin/net/taehui/twilight/system/DB.kt b/src/main/kotlin/net/taehui/twilight/system/DB.kt index 663eff1..9f74d11 100644 --- a/src/main/kotlin/net/taehui/twilight/system/DB.kt +++ b/src/main/kotlin/net/taehui/twilight/system/DB.kt @@ -1878,32 +1878,32 @@ "0" -> """ SELECT COUNT(Comment_ID) AS Total_Count, COUNT(DISTINCT tw_note.Note_ID, Avatar) AS Highest_Count, COUNT(DISTINCT tw_note.Note_ID) AS Note_Count FROM tw_note, tw_comment - WHERE tw_note.Note_ID = tw_comment.Note_ID AND MATCH(Title) AGAINST(?) $noteVarietyStatement1 + WHERE tw_note.Note_ID = tw_comment.Note_ID AND MATCH(Title) AGAINST(? IN BOOLEAN MODE) $noteVarietyStatement1 """.trimIndent() "1" -> """ SELECT COUNT(Comment_ID) AS Total_Count, COUNT(DISTINCT tw_note.Note_ID, Avatar) AS Highest_Count, COUNT(DISTINCT tw_note.Note_ID) AS Note_Count FROM tw_note, tw_comment, tn_avatar - WHERE tw_note.Note_ID = tw_comment.Note_ID AND Avatar = Avatar_ID AND MATCH(Avatar_Name) AGAINST(?) $noteVarietyStatement1 + WHERE tw_note.Note_ID = tw_comment.Note_ID AND Avatar = Avatar_ID AND MATCH(Avatar_Name) AGAINST(? IN BOOLEAN MODE) $noteVarietyStatement1 """.trimIndent() "2" -> """ SELECT COUNT(Comment_ID) AS Total_Count, COUNT(DISTINCT tw_note.Note_ID, Avatar) AS Highest_Count, COUNT(DISTINCT tw_note.Note_ID) AS Note_Count FROM tw_note, tw_comment - WHERE tw_note.Note_ID = tw_comment.Note_ID AND MATCH(Artist) AGAINST(?) $noteVarietyStatement1 + WHERE tw_note.Note_ID = tw_comment.Note_ID AND MATCH(Artist) AGAINST(? IN BOOLEAN MODE) $noteVarietyStatement1 """.trimIndent() "3" -> """ SELECT COUNT(Comment_ID) AS Total_Count, COUNT(DISTINCT tw_note.Note_ID, Avatar) AS Highest_Count, COUNT(DISTINCT tw_note.Note_ID) AS Note_Count FROM tw_note, tw_comment - WHERE tw_note.Note_ID = tw_comment.Note_ID AND MATCH(Genre) AGAINST(?) $noteVarietyStatement1 + WHERE tw_note.Note_ID = tw_comment.Note_ID AND MATCH(Genre) AGAINST(? IN BOOLEAN MODE) $noteVarietyStatement1 """.trimIndent() else -> null } ).use { dbStatement -> if (dbStatement != null) { - dbStatement.setString(1, want) + dbStatement.setString(1, "*$want*") dbStatement.executeQuery().use { rows -> if (rows.next()) { totalCount = rows.getInt("Total_Count") @@ -1915,7 +1915,7 @@ } } val asStatement = """ - DISTINCT tw_note.Note_ID, Note_Variety, Artist, Title, Genre, tw_note.Level_Text, tw_note.Level, ( + DISTINCT tw_note.Note_ID, Note_Variety, Artist, Title, Genre, Level_Text, Level, ( SELECT COUNT(DISTINCT Note_ID, Avatar) FROM tw_comment WHERE tw_note.Note_ID = tw_comment.Note_ID @@ -1952,7 +1952,7 @@ """ SELECT $asStatement FROM tw_note - WHERE MATCH(Title) AGAINST(?) $noteVarietyStatement1 $fitStatement LIMIT ?, ? + WHERE MATCH(Title) AGAINST(? IN BOOLEAN MODE) $noteVarietyStatement1 $fitStatement LIMIT ?, ? """.trimIndent() ) @@ -1960,7 +1960,7 @@ """ SELECT $asStatement FROM tw_note, tw_comment, tn_avatar - WHERE tw_note.Note_ID = tw_comment.Note_ID AND tw_comment.Avatar = tn_avatar.Avatar_ID AND MATCH(Avatar_Name) AGAINST(?) $noteVarietyStatement1 $fitStatement LIMIT ?, ? + WHERE tw_note.Note_ID = tw_comment.Note_ID AND tw_comment.Avatar = tn_avatar.Avatar_ID AND MATCH(Avatar_Name) AGAINST(? IN BOOLEAN MODE) $noteVarietyStatement1 $fitStatement LIMIT ?, ? """.trimIndent() ) @@ -1968,7 +1968,7 @@ """ SELECT $asStatement FROM tw_note - WHERE MATCH(Artist) AGAINST(?) $noteVarietyStatement1 $fitStatement LIMIT ?, ? + WHERE MATCH(Artist) AGAINST(? IN BOOLEAN MODE) $noteVarietyStatement1 $fitStatement LIMIT ?, ? """.trimIndent() ) @@ -1976,13 +1976,13 @@ """ SELECT $asStatement FROM tw_note - WHERE MATCH(Genre) AGAINST(?) $noteVarietyStatement1 $fitStatement LIMIT ?, ? + WHERE MATCH(Genre) AGAINST(? IN BOOLEAN MODE) $noteVarietyStatement1 $fitStatement LIMIT ?, ? """.trimIndent() ) else -> null } - dbStatement?.setString(1, want) + dbStatement?.setString(1, "*$want*") dbStatement?.setInt(2, viewUnit * (page - 1)) dbStatement?.setInt(3, viewUnit) } @@ -2143,10 +2143,10 @@ """ SELECT Avatar_ID, Avatar_Name, Avatar_Intro FROM tn_avatar - WHERE MATCH(Avatar_Name) AGAINST(?) + WHERE MATCH(Avatar_Name) AGAINST(? IN BOOLEAN MODE) """.trimIndent() ).use { dbStatement -> - dbStatement.setString(1, wantAvatarName) + dbStatement.setString(1, "*$wantAvatarName*") dbStatement.executeQuery().use { rows -> while (rows.next()) { wantAvatars.add(object { @@ -2373,7 +2373,7 @@ pool.connection.use { db -> db.prepareStatement( """ - SELECT tw_note.Note_ID, Note_Variety, Title, Artist, Genre, tw_note.Level_Text, Level, COUNT(Comment_ID) AS Count + SELECT tw_note.Note_ID, Note_Variety, Title, Artist, Genre, Level_Text, Level, COUNT(Comment_ID) AS Count FROM tw_note, tw_comment WHERE tw_note.Note_ID = tw_comment.Note_ID AND Avatar = ? AND Input_Mode = ? GROUP BY tw_note.Note_ID @@ -2409,7 +2409,7 @@ pool.connection.use { db -> db.prepareStatement( """ - SELECT MAX(Date) AS Date, tw_note.Note_ID, Note_Variety, Title, Artist, Genre, tw_note.Level_Text, Level + SELECT MAX(Date) AS Date, tw_note.Note_ID, Note_Variety, Title, Artist, Genre, Level_Text, Level FROM tw_note, tw_comment WHERE tw_note.Note_ID = tw_comment.Note_ID AND Avatar = ? AND Input_Mode = ? GROUP BY tw_note.Note_ID @@ -2486,12 +2486,80 @@ } } + interface IVSItem { + val noteID: String + val stand: Int + var vsStand: Int + } + + fun getAvatarVS(avatarID: String, targetID: String): CompletableFuture { + fun getVSItems(avatarID: String, vsItems: MutableMap): CompletableFuture { + return logFuture { + pool.connection.use { db -> + db.prepareStatement( + """ + SELECT tw_note.Note_ID, Note_Variety, Title, Artist, Genre, Level_Text, Level, Stand + FROM tw_note, tw_comment + WHERE tw_note.Note_ID = tw_comment.Note_ID AND Avatar = ? AND Is_Max = true + """.trimIndent() + ).use { dbStatement -> + dbStatement.setString(1, avatarID) + dbStatement.executeQuery().use { + while (it.next()) { + val noteID = it.getString("Note_ID") + vsItems[noteID] = object : IVSItem { + override val noteID = noteID + val noteVariety = it.getInt("Note_Variety") + 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 var vsStand = 0 + } + } + } + } + } + } + } + + val avatarVSMap = mutableMapOf() + val targetVSMap = mutableMapOf() + return CompletableFuture.allOf(getVSItems(avatarID, avatarVSMap), getVSItems(targetID, targetVSMap)).thenApply { + val avatarVSItems = mutableSetOf() + val targetVSItems = mutableSetOf() + avatarVSMap.keys.intersect(targetVSMap.keys).forEach { + val avatarVSItem = avatarVSMap[it] + val targetVSItem = targetVSMap[it] + if (avatarVSItem != null && targetVSItem != null) { + val vsStand = avatarVSItem.stand - targetVSItem.stand + if (vsStand > 0) { + avatarVSItem.vsStand = vsStand + avatarVSItems.add(avatarVSItem) + } else if (vsStand < 0) { + targetVSItem.vsStand = -vsStand + targetVSItems.add(targetVSItem) + } + } + } + + object { + val avatarVSCount = avatarVSItems.size + val targetVSCount = targetVSItems.size + val avatarVSItems = avatarVSItems.sortedBy { it.vsStand }.take(10) + val targetVSItems = targetVSItems.sortedBy { it.vsStand }.take(10) + } + } + } + private fun getAbilities5K(): Map, MutableCollection> { val abilities5K = mutableMapOf, MutableCollection>() 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 + SELECT tw_note.Note_ID, Note_Variety, Artist, Title, Genre, Level_Text, 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_note, tw_comment, tn_avatar WHERE tw_note.Note_ID = tw_comment.Note_ID AND tw_comment.Avatar = tn_avatar.Avatar_ID AND Ability_5K > 0.0 AND (Input_Favor_Mode = 0 OR Input_Favor_Mode = 5 OR Input_Favor_Mode = 10) AND (Hit_Points_Mode = 1 OR Hit_Points_Mode = 2 OR Hit_Points_Mode = 5) AND (Judgment_Mode = 1 OR Judgment_Mode = 2 OR Judgment_Mode = 4) AND Is_Max = true ORDER BY Value DESC @@ -2540,7 +2608,7 @@ 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 + SELECT tw_note.Note_ID, Note_Variety, Artist, Title, Genre, Level_Text, 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_note, tw_comment, tn_avatar WHERE tw_note.Note_ID = tw_comment.Note_ID AND tw_comment.Avatar = tn_avatar.Avatar_ID AND Ability_7K > 0.0 AND (Input_Favor_Mode = 0 OR Input_Favor_Mode = 7 OR Input_Favor_Mode = 11) AND (Hit_Points_Mode = 1 OR Hit_Points_Mode = 2 OR Hit_Points_Mode = 5) AND (Judgment_Mode = 1 OR Judgment_Mode = 2 OR Judgment_Mode = 4) AND Is_Max = true ORDER BY Value DESC @@ -2589,7 +2657,7 @@ 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 + SELECT tw_note.Note_ID, Note_Variety, Artist, Title, Genre, Level_Text, 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_note, tw_comment, tn_avatar WHERE tw_note.Note_ID = tw_comment.Note_ID AND tw_comment.Avatar = tn_avatar.Avatar_ID AND Ability_9K > 0.0 AND (Input_Favor_Mode = 0 OR Input_Favor_Mode = 9) AND (Hit_Points_Mode = 1 OR Hit_Points_Mode = 2 OR Hit_Points_Mode = 5) AND (Judgment_Mode = 1 OR Judgment_Mode = 2 OR Judgment_Mode = 4) AND Is_Max = true ORDER BY Value DESC diff --git a/src/main/kotlin/net/taehui/twilight/www/WwwAvatar.kt b/src/main/kotlin/net/taehui/twilight/www/WwwAvatar.kt index 89335b4..f4a6b61 100644 --- a/src/main/kotlin/net/taehui/twilight/www/WwwAvatar.kt +++ b/src/main/kotlin/net/taehui/twilight/www/WwwAvatar.kt @@ -291,6 +291,13 @@ send(ctx, it) } + "/qwilight/www/avatar/vs" -> { + val targetID = params.getOrDefault("targetID", "") + DB.getAvatarVS(avatarID, targetID).thenAccept { + send(ctx, it) + } + } + "/qwilight/www/hof/totalTotal" -> DB.getHOFTotalTotal().thenAccept { send(ctx, it) } "/qwilight/www/hof/atTotal" -> DB.getHOFAtTotal().thenAccept { send(ctx, it) } "/qwilight/www/hof/totalHighest" -> DB.getHOFTotalHighest().thenAccept { send(ctx, it) }