From 3e77bb9ee1aabab22138de9850d8bd434eda6d91 Mon Sep 17 00:00:00 2001 From: nsfisis Date: Sat, 6 Sep 2025 01:44:25 +0900 Subject: feat(backend): update qualifying ranking page --- backend/admin/handler.go | 7 +++++++ backend/admin/templates/dashboard.html | 2 +- backend/admin/templates/online_qualifying_ranking.html | 2 ++ backend/db/query.sql.go | 17 +++++++++++++---- backend/query.sql | 10 +++++++--- 5 files changed, 30 insertions(+), 8 deletions(-) (limited to 'backend') diff --git a/backend/admin/handler.go b/backend/admin/handler.go index c045b7d..289a598 100644 --- a/backend/admin/handler.go +++ b/backend/admin/handler.go @@ -98,10 +98,15 @@ func (h *Handler) getOnlineQualifyingRanking(c echo.Context) error { if err != nil { return echo.NewHTTPError(http.StatusBadRequest, "Invalid game_2") } + game3, err := strconv.Atoi(c.QueryParam("game_3")) + if err != nil { + return echo.NewHTTPError(http.StatusBadRequest, "Invalid game_3") + } rows, err := h.q.GetQualifyingRanking(c.Request().Context(), db.GetQualifyingRankingParams{ GameID: int32(game1), GameID_2: int32(game2), + GameID_3: int32(game3), }) if err != nil { return echo.NewHTTPError(http.StatusInternalServerError, err.Error()) @@ -115,9 +120,11 @@ func (h *Handler) getOnlineQualifyingRanking(c echo.Context) error { "UserLabel": r.UserLabel, "Score1": r.CodeSize1, "Score2": r.CodeSize2, + "Score3": r.CodeSize3, "TotalScore": r.TotalCodeSize, "SubmittedAt1": r.SubmittedAt1.Time.In(jst).Format("2006-01-02T15:04"), "SubmittedAt2": r.SubmittedAt2.Time.In(jst).Format("2006-01-02T15:04"), + "SubmittedAt3": r.SubmittedAt3.Time.In(jst).Format("2006-01-02T15:04"), } } return c.Render(http.StatusOK, "online_qualifying_ranking", echo.Map{ diff --git a/backend/admin/templates/dashboard.html b/backend/admin/templates/dashboard.html index 1ea40c9..5a7b484 100644 --- a/backend/admin/templates/dashboard.html +++ b/backend/admin/templates/dashboard.html @@ -11,7 +11,7 @@ Problems

- Online Qualifying Ranking + Online Qualifying Ranking

diff --git a/backend/admin/templates/online_qualifying_ranking.html b/backend/admin/templates/online_qualifying_ranking.html index 663b68e..f6a9664 100644 --- a/backend/admin/templates/online_qualifying_ranking.html +++ b/backend/admin/templates/online_qualifying_ranking.html @@ -12,6 +12,7 @@ プレイヤー スコア1 スコア2 + スコア3 合計スコア 提出時刻1 提出時刻2 @@ -24,6 +25,7 @@ {{ .Username }}{{ if .UserLabel }} ({{ .UserLabel }}){{ end }} {{ .Score1 }} {{ .Score2 }} + {{ .Score3 }} {{ .TotalScore }} {{ .SubmittedAt1 }} {{ .SubmittedAt2 }} diff --git a/backend/db/query.sql.go b/backend/db/query.sql.go index 23be719..cdf1cc9 100644 --- a/backend/db/query.sql.go +++ b/backend/db/query.sql.go @@ -383,21 +383,26 @@ SELECT u.label AS user_label, s1.code_size AS code_size_1, s2.code_size AS code_size_2, - (s1.code_size + s2.code_size) AS total_code_size, + s3.code_size AS code_size_3, + (COALESCE(s1.code_size, 0) + COALESCE(s2.code_size, 0) + COALESCE(s3.code_size, 0)) AS total_code_size, s1.created_at AS submitted_at_1, - s2.created_at AS submitted_at_2 + s2.created_at AS submitted_at_2, + s3.created_at AS submitted_at_3 FROM game_states gs1 JOIN submissions s1 ON gs1.best_score_submission_id = s1.submission_id JOIN game_states gs2 ON gs1.user_id = gs2.user_id JOIN submissions s2 ON gs2.best_score_submission_id = s2.submission_id +JOIN game_states gs3 ON gs1.user_id = gs3.user_id +JOIN submissions s3 ON gs3.best_score_submission_id = s3.submission_id JOIN users u ON gs1.user_id = u.user_id -WHERE gs1.game_id = $1 AND gs2.game_id = $2 +WHERE gs1.game_id = $1 AND gs2.game_id = $2 AND gs3.game_id = $3 ORDER BY total_code_size ASC ` type GetQualifyingRankingParams struct { GameID int32 GameID_2 int32 + GameID_3 int32 } type GetQualifyingRankingRow struct { @@ -405,13 +410,15 @@ type GetQualifyingRankingRow struct { UserLabel *string CodeSize1 int32 CodeSize2 int32 + CodeSize3 int32 TotalCodeSize int32 SubmittedAt1 pgtype.Timestamp SubmittedAt2 pgtype.Timestamp + SubmittedAt3 pgtype.Timestamp } func (q *Queries) GetQualifyingRanking(ctx context.Context, arg GetQualifyingRankingParams) ([]GetQualifyingRankingRow, error) { - rows, err := q.db.Query(ctx, getQualifyingRanking, arg.GameID, arg.GameID_2) + rows, err := q.db.Query(ctx, getQualifyingRanking, arg.GameID, arg.GameID_2, arg.GameID_3) if err != nil { return nil, err } @@ -424,9 +431,11 @@ func (q *Queries) GetQualifyingRanking(ctx context.Context, arg GetQualifyingRan &i.UserLabel, &i.CodeSize1, &i.CodeSize2, + &i.CodeSize3, &i.TotalCodeSize, &i.SubmittedAt1, &i.SubmittedAt2, + &i.SubmittedAt3, ); err != nil { return nil, err } diff --git a/backend/query.sql b/backend/query.sql index 76af82b..2dc3084 100644 --- a/backend/query.sql +++ b/backend/query.sql @@ -146,15 +146,19 @@ SELECT u.label AS user_label, s1.code_size AS code_size_1, s2.code_size AS code_size_2, - (s1.code_size + s2.code_size) AS total_code_size, + s3.code_size AS code_size_3, + (COALESCE(s1.code_size, 0) + COALESCE(s2.code_size, 0) + COALESCE(s3.code_size, 0)) AS total_code_size, s1.created_at AS submitted_at_1, - s2.created_at AS submitted_at_2 + s2.created_at AS submitted_at_2, + s3.created_at AS submitted_at_3 FROM game_states gs1 JOIN submissions s1 ON gs1.best_score_submission_id = s1.submission_id JOIN game_states gs2 ON gs1.user_id = gs2.user_id JOIN submissions s2 ON gs2.best_score_submission_id = s2.submission_id +JOIN game_states gs3 ON gs1.user_id = gs3.user_id +JOIN submissions s3 ON gs3.best_score_submission_id = s3.submission_id JOIN users u ON gs1.user_id = u.user_id -WHERE gs1.game_id = $1 AND gs2.game_id = $2 +WHERE gs1.game_id = $1 AND gs2.game_id = $2 AND gs3.game_id = $3 ORDER BY total_code_size ASC; -- name: UpdateCode :exec -- cgit v1.2.3-70-g09d2