diff options
| -rw-r--r-- | backend/src/routes/score.ts | 345 | ||||
| -rw-r--r-- | scripts/dancearound/dancearound_play_history.js | 164 | ||||
| -rw-r--r-- | scripts/dancerush/dancerush_play_history.js | 2 |
3 files changed, 434 insertions, 77 deletions
diff --git a/backend/src/routes/score.ts b/backend/src/routes/score.ts index 2190a38..1f6bdf6 100644 --- a/backend/src/routes/score.ts +++ b/backend/src/routes/score.ts @@ -3,6 +3,37 @@ import { prisma } from "../config/db"; import { PAGE_SIZE } from "../config/constants"; import crypto from "crypto"; +// Helper function to create a CASE statement for Japanese-style grade sorting +const createGradeCaseStatement = (sortKeyString: string, directionString: string): string => { + const gradeOrder = [ + 'F', 'F+', + 'E-', 'E', 'E+', + 'D-', 'D', 'D+', + 'C-', 'C', 'C+', + 'B-', 'B', 'B+', + 'A-', 'A', 'A+', + 'AA-', 'AA', 'AA+', + 'AAA-', 'AAA', 'AAA+', + 'S-', 'S', 'S+', + 'SS-', 'SS', 'SS+', + 'SSS-', 'SSS', 'SSS+' + ]; + + let caseStatement = `CASE `; + gradeOrder.forEach((grade, index) => { + caseStatement += `WHEN UPPER(TRIM(s.data->>'${sortKeyString}')) = '${grade}' THEN ${index} `; + }); + caseStatement += `ELSE 999 END`; + + if (directionString === 'desc') { + caseStatement += ' DESC'; + } else { + caseStatement += ' ASC'; + } + + return caseStatement; +}; + export const handleScoreUpload = async ( req: express.Request, res: express.Response, @@ -187,20 +218,49 @@ export const handleGetScores = async ( PAGE_SIZE, ); } else { - scores = await prisma.$queryRawUnsafe<any[]>( - ` - SELECT DISTINCT ON ("chartId") * - FROM "Score" - WHERE "gameInternalName" = $1 AND "userId" = $2 - ORDER BY "chartId", (data->>'${sortKeyString}')::numeric ${directionString.toUpperCase()} - OFFSET $3 - LIMIT $4 - `, + // Check if we need grade-based sorting by sampling one score + const sampleScore = await prisma.$queryRawUnsafe<any[]>( + `SELECT data->>'${sortKeyString}' as value FROM "Score" WHERE "gameInternalName" = $1 AND "userId" = $2 AND data->>'${sortKeyString}' IS NOT NULL LIMIT 1`, gameInternalName, - userIdNumber, - (pageNumber - 1) * PAGE_SIZE, - PAGE_SIZE, + userIdNumber ); + + // If it is not a numerical value + const isGradeSort = sampleScore.length > 0 && + sampleScore[0].value && + /^[A-Z]+[+-]?$/i.test(sampleScore[0].value.trim()); + + if (isGradeSort) { + scores = await prisma.$queryRawUnsafe<any[]>( + ` + SELECT DISTINCT ON ("chartId") * + FROM "Score" + WHERE "gameInternalName" = $1 AND "userId" = $2 + ORDER BY "chartId", ${createGradeCaseStatement(sortKeyString, directionString)} + OFFSET $3 + LIMIT $4 + `, + gameInternalName, + userIdNumber, + (pageNumber - 1) * PAGE_SIZE, + PAGE_SIZE, + ); + } else { + scores = await prisma.$queryRawUnsafe<any[]>( + ` + SELECT DISTINCT ON ("chartId") * + FROM "Score" + WHERE "gameInternalName" = $1 AND "userId" = $2 + ORDER BY "chartId", (data->>'${sortKeyString}')::numeric ${directionString.toUpperCase()} + OFFSET $3 + LIMIT $4 + `, + gameInternalName, + userIdNumber, + (pageNumber - 1) * PAGE_SIZE, + PAGE_SIZE, + ); + } } // Count distinct charts for pagination @@ -235,20 +295,47 @@ export const handleGetScores = async ( take: PAGE_SIZE, }); } else { - // everything else attempt to rawsql it - scores = await prisma.$queryRawUnsafe<any[]>( - ` - SELECT * FROM "Score" - WHERE "gameInternalName" = $1 AND "userId" = $2 - ORDER BY (data->>'${sortKeyString}')::numeric ${directionString.toUpperCase()} - OFFSET $3 - LIMIT $4 - `, + // Check if we need grade-based sorting by sampling one score + const sampleScore = await prisma.$queryRawUnsafe<any[]>( + `SELECT data->>'${sortKeyString}' as value FROM "Score" WHERE "gameInternalName" = $1 AND "userId" = $2 AND data->>'${sortKeyString}' IS NOT NULL LIMIT 1`, gameInternalName, - userIdNumber, - (pageNumber - 1) * PAGE_SIZE, - PAGE_SIZE, + userIdNumber ); + + const isGradeSort = sampleScore.length > 0 && + sampleScore[0].value && + /^[A-Z]+[+-]?$/i.test(sampleScore[0].value.trim()); + + if (isGradeSort) { + scores = await prisma.$queryRawUnsafe<any[]>( + ` + SELECT * FROM "Score" + WHERE "gameInternalName" = $1 AND "userId" = $2 + ORDER BY ${createGradeCaseStatement(sortKeyString, directionString)} + OFFSET $3 + LIMIT $4 + `, + gameInternalName, + userIdNumber, + (pageNumber - 1) * PAGE_SIZE, + PAGE_SIZE, + ); + } else { + // everything else attempt to rawsql it + scores = await prisma.$queryRawUnsafe<any[]>( + ` + SELECT * FROM "Score" + WHERE "gameInternalName" = $1 AND "userId" = $2 + ORDER BY (data->>'${sortKeyString}')::numeric ${directionString.toUpperCase()} + OFFSET $3 + LIMIT $4 + `, + gameInternalName, + userIdNumber, + (pageNumber - 1) * PAGE_SIZE, + PAGE_SIZE, + ); + } } } @@ -323,20 +410,47 @@ export const handleGetScoresByChartId = async ( PAGE_SIZE, ); } else { - scores = await prisma.$queryRawUnsafe<any[]>( - ` - SELECT DISTINCT ON (s."userId") s.*, u.username - FROM "Score" s - JOIN "User" u ON s."userId" = u.id - WHERE s."chartId" = $1 - ORDER BY s."userId", (s.data->>'${sortKeyString}')::numeric ${directionString.toUpperCase()} - OFFSET $2 - LIMIT $3 - `, - chartIdString, - (pageNumber - 1) * PAGE_SIZE, - PAGE_SIZE, + // Check if we need grade-based sorting by sampling one score + const sampleScore = await prisma.$queryRawUnsafe<any[]>( + `SELECT s.data->>'${sortKeyString}' as value FROM "Score" s WHERE s."chartId" = $1 AND s.data->>'${sortKeyString}' IS NOT NULL LIMIT 1`, + chartIdString ); + + const isGradeSort = sampleScore.length > 0 && + sampleScore[0].value && + /^[A-Z]+[+-]?$/i.test(sampleScore[0].value.trim()); + + if (isGradeSort) { + scores = await prisma.$queryRawUnsafe<any[]>( + ` + SELECT DISTINCT ON (s."userId") s.*, u.username + FROM "Score" s + JOIN "User" u ON s."userId" = u.id + WHERE s."chartId" = $1 + ORDER BY s."userId", ${createGradeCaseStatement(sortKeyString, directionString)} + OFFSET $2 + LIMIT $3 + `, + chartIdString, + (pageNumber - 1) * PAGE_SIZE, + PAGE_SIZE, + ); + } else { + scores = await prisma.$queryRawUnsafe<any[]>( + ` + SELECT DISTINCT ON (s."userId") s.*, u.username + FROM "Score" s + JOIN "User" u ON s."userId" = u.id + WHERE s."chartId" = $1 + ORDER BY s."userId", (s.data->>'${sortKeyString}')::numeric ${directionString.toUpperCase()} + OFFSET $2 + LIMIT $3 + `, + chartIdString, + (pageNumber - 1) * PAGE_SIZE, + PAGE_SIZE, + ); + } } // Count distinct users for pagination @@ -375,20 +489,46 @@ export const handleGetScoresByChartId = async ( take: PAGE_SIZE, }); } else { - // everything else attempt to rawsql it - scores = await prisma.$queryRawUnsafe<any[]>( - ` - SELECT s.*, u.username FROM "Score" s - JOIN "User" u ON s."userId" = u.id - WHERE s."chartId" = $1 - ORDER BY (s.data->>'${sortKeyString}')::numeric ${directionString.toUpperCase()} - OFFSET $2 - LIMIT $3 - `, - chartIdString, - (pageNumber - 1) * PAGE_SIZE, - PAGE_SIZE, + // Check if we need grade-based sorting by sampling one score + const sampleScore = await prisma.$queryRawUnsafe<any[]>( + `SELECT s.data->>'${sortKeyString}' as value FROM "Score" s WHERE s."chartId" = $1 AND s.data->>'${sortKeyString}' IS NOT NULL LIMIT 1`, + chartIdString ); + + const isGradeSort = sampleScore.length > 0 && + sampleScore[0].value && + /^[A-Z]+[+-]?$/i.test(sampleScore[0].value.trim()); + + if (isGradeSort) { + scores = await prisma.$queryRawUnsafe<any[]>( + ` + SELECT s.*, u.username FROM "Score" s + JOIN "User" u ON s."userId" = u.id + WHERE s."chartId" = $1 + ORDER BY ${createGradeCaseStatement(sortKeyString, directionString)} + OFFSET $2 + LIMIT $3 + `, + chartIdString, + (pageNumber - 1) * PAGE_SIZE, + PAGE_SIZE, + ); + } else { + // everything else attempt to rawsql it + scores = await prisma.$queryRawUnsafe<any[]>( + ` + SELECT s.*, u.username FROM "Score" s + JOIN "User" u ON s."userId" = u.id + WHERE s."chartId" = $1 + ORDER BY (s.data->>'${sortKeyString}')::numeric ${directionString.toUpperCase()} + OFFSET $2 + LIMIT $3 + `, + chartIdString, + (pageNumber - 1) * PAGE_SIZE, + PAGE_SIZE, + ); + } } } @@ -468,20 +608,47 @@ export const handleGetAllGameScores = async ( PAGE_SIZE, ); } else { - scores = await prisma.$queryRawUnsafe<any[]>( - ` - SELECT DISTINCT ON (s."chartId", s."userId") s.*, u.username - FROM "Score" s - JOIN "User" u ON s."userId" = u.id - WHERE s."gameInternalName" = $1 - ORDER BY s."chartId", s."userId", (s.data->>'${sortKeyString}')::numeric ${directionString.toUpperCase()} - OFFSET $2 - LIMIT $3 - `, - gameInternalName, - (pageNumber - 1) * PAGE_SIZE, - PAGE_SIZE, + // Check if we need grade-based sorting by sampling one score + const sampleScore = await prisma.$queryRawUnsafe<any[]>( + `SELECT s.data->>'${sortKeyString}' as value FROM "Score" s WHERE s."gameInternalName" = $1 AND s.data->>'${sortKeyString}' IS NOT NULL LIMIT 1`, + gameInternalName ); + + const isGradeSort = sampleScore.length > 0 && + sampleScore[0].value && + /^[A-Z]+[+-]?$/i.test(sampleScore[0].value.trim()); + + if (isGradeSort) { + scores = await prisma.$queryRawUnsafe<any[]>( + ` + SELECT DISTINCT ON (s."chartId", s."userId") s.*, u.username + FROM "Score" s + JOIN "User" u ON s."userId" = u.id + WHERE s."gameInternalName" = $1 + ORDER BY s."chartId", s."userId", ${createGradeCaseStatement(sortKeyString, directionString)} + OFFSET $2 + LIMIT $3 + `, + gameInternalName, + (pageNumber - 1) * PAGE_SIZE, + PAGE_SIZE, + ); + } else { + scores = await prisma.$queryRawUnsafe<any[]>( + ` + SELECT DISTINCT ON (s."chartId", s."userId") s.*, u.username + FROM "Score" s + JOIN "User" u ON s."userId" = u.id + WHERE s."gameInternalName" = $1 + ORDER BY s."chartId", s."userId", (s.data->>'${sortKeyString}')::numeric ${directionString.toUpperCase()} + OFFSET $2 + LIMIT $3 + `, + gameInternalName, + (pageNumber - 1) * PAGE_SIZE, + PAGE_SIZE, + ); + } } // Count distinct chart-user combinations for pagination @@ -520,20 +687,46 @@ export const handleGetAllGameScores = async ( take: PAGE_SIZE, }); } else { - // everything else attempt to rawsql it - scores = await prisma.$queryRawUnsafe<any[]>( - ` - SELECT s.*, u.username FROM "Score" s - JOIN "User" u ON s."userId" = u.id - WHERE s."gameInternalName" = $1 - ORDER BY (s.data->>'${sortKeyString}')::numeric ${directionString.toUpperCase()} - OFFSET $2 - LIMIT $3 - `, - gameInternalName, - (pageNumber - 1) * PAGE_SIZE, - PAGE_SIZE, + // Check if we need grade-based sorting by sampling one score + const sampleScore = await prisma.$queryRawUnsafe<any[]>( + `SELECT s.data->>'${sortKeyString}' as value FROM "Score" s WHERE s."gameInternalName" = $1 AND s.data->>'${sortKeyString}' IS NOT NULL LIMIT 1`, + gameInternalName ); + + const isGradeSort = sampleScore.length > 0 && + sampleScore[0].value && + /^[A-Z]+[+-]?$/i.test(sampleScore[0].value.trim()); + + if (isGradeSort) { + scores = await prisma.$queryRawUnsafe<any[]>( + ` + SELECT s.*, u.username FROM "Score" s + JOIN "User" u ON s."userId" = u.id + WHERE s."gameInternalName" = $1 + ORDER BY ${createGradeCaseStatement(sortKeyString, directionString)} + OFFSET $2 + LIMIT $3 + `, + gameInternalName, + (pageNumber - 1) * PAGE_SIZE, + PAGE_SIZE, + ); + } else { + // everything else attempt to rawsql it + scores = await prisma.$queryRawUnsafe<any[]>( + ` + SELECT s.*, u.username FROM "Score" s + JOIN "User" u ON s."userId" = u.id + WHERE s."gameInternalName" = $1 + ORDER BY (s.data->>'${sortKeyString}')::numeric ${directionString.toUpperCase()} + OFFSET $2 + LIMIT $3 + `, + gameInternalName, + (pageNumber - 1) * PAGE_SIZE, + PAGE_SIZE, + ); + } } } diff --git a/scripts/dancearound/dancearound_play_history.js b/scripts/dancearound/dancearound_play_history.js new file mode 100644 index 0000000..2a98994 --- /dev/null +++ b/scripts/dancearound/dancearound_play_history.js @@ -0,0 +1,164 @@ +// ==UserScript== +// @name DANCEAROUND Mirage Scraper +// @namespace http://tampermonkey.net/ +// @version 1.0 +// @description DANCEAROUND e-amusement site to Mirage import JSON +// @match https://p.eagate.573.jp/game/around/1st/playdata/index.html* +// @grant none +// @run-at document-idle +// ==/UserScript== + +(function () { + function waitFor(selector, timeout = 10000) { + return new Promise((resolve, reject) => { + const interval = 300; + let waited = 0; + const check = () => { + const el = document.querySelector(selector); + if (el) return resolve(el); + waited += interval; + if (waited >= timeout) return reject(`Timeout: ${selector}`); + setTimeout(check, interval); + }; + check(); + }); + } + + function getDifficulty(fumen, mdb) { + let difficulty, lamp; + + switch (fumen) { + case "ADVANCED": + difficulty = mdb.fumens.ADVANCED.level; + lamp = "ADVANCED"; + break; + case "BASIC": + difficulty = mdb.fumens.BASIC.level; + lamp = "BASIC"; + break; + case "MASTER": + difficulty = mdb.fumens.MASTER.level; + lamp = "MASTER"; + break; + } + + return { difficulty, lamp }; + } + function getLampText(status) { + switch (status) { + case 0: + return "C"; + case 1: + return "B"; + case 2: + return "A"; + case 3: + return "AA"; + case 4: + return "AAA"; + case 5: + return "AAA+"; + } + } + + function getClearStatusText(status){ + switch(status){ + case 1: + return "FAILURE"; + case 2: + return "PASSED"; + case 3: + return "FULL COMBO"; + case 4: + return "EXC"; + } + } + + async function fetchAndDownload() { + const url = "https://p.eagate.573.jp/game/around/1st/json/pdata_getdata.html"; + const payload = new URLSearchParams({ + service_kind: "play_hist", + pdata_kind: "play_hist", + }); + + try { + const response = await fetch(url, { + method: "POST", + credentials: "include", + headers: { + "Content-Type": "application/x-www-form-urlencoded", + "X-Requested-With": "XMLHttpRequest", + }, + body: payload.toString(), + }); + + if (!response.ok) throw new Error(`HTTP ${response.status}`); + + const data = await response.json(); + const play_hist = data.data.easite_get_playerdata.music_hist.music; + const song_db = data.data.easite_get_playerdata.mdb; + let mirage = { + meta: { + game: "dancearound", + playtype: "Single", + service: "e-amusement PLAY HISTORY", + }, + }; + const remappedList = play_hist.map((entry) => { + const diff = getDifficulty(entry.music_type, song_db[entry.music_id].difficulty) + const numPlayers = (entry.p1 && entry.p2) ? 2 : 1; + return { + title: song_db[entry.music_id].title_name, + artist: song_db[entry.music_id].artist_name, + diff_lamp: diff.lamp, + num_players: numPlayers, + score: entry.score, + lamp: getLampText(entry.rank), + clear_status: getClearStatusText(entry.clear_status), + difficulty: diff.difficulty, + timestamp: entry.play_date, + judgements: { + "perfect": entry.perfect, + "great": entry.great, + "good": entry.good, + "bad": entry.bad + }, + optional: { + maxCombo: entry.combo, + } + + }; + }); + mirage.scores = remappedList; + + const blob = new Blob([JSON.stringify(mirage, null, 2)], { + type: "application/json", + }); + + const a = document.createElement("a"); + a.href = URL.createObjectURL(blob); + a.download = "dancearound_scores_mirage_import.json"; + a.click(); + } catch (err) { + console.error("Fetch/download error:", err); + alert("Failed to fetch or process JSON. See console for details."); + } + } + + waitFor("#id_ctpl_body") + .then((container) => { + const btn = document.createElement("button"); + btn.textContent = "📥 DOWNLOAD PLAY HISTORY SCORE JSON"; + btn.style.cssText = ` + margin: 10px; padding: 8px 12px; + font-size: 14px; cursor: pointer; + background: #2563eb; color: white; + border: none; border-radius: 6px; + z-index: 9999; + `; + btn.onclick = fetchAndDownload; + + container.prepend(btn); + }) + .catch((err) => console.warn("Could not inject button:", err)); +})(); diff --git a/scripts/dancerush/dancerush_play_history.js b/scripts/dancerush/dancerush_play_history.js index 5cc338f..098f038 100644 --- a/scripts/dancerush/dancerush_play_history.js +++ b/scripts/dancerush/dancerush_play_history.js @@ -89,7 +89,7 @@ let mirage = { meta: { game: "DANCERUSH STARDOM", - playtype: "Singl2e", + playtype: "Single", service: "e-amusement PLAY HISTORY", }, }; |