From 4b5c0f0d1f9711b27ededbcdda785753325f04ea Mon Sep 17 00:00:00 2001 From: nsfisis Date: Thu, 20 Mar 2025 20:09:05 +0900 Subject: feat(backend): add /admin/online-qualifying-ranking --- backend/admin/handler.go | 38 ++++++++++++++ backend/admin/templates/dashboard.html | 3 ++ .../admin/templates/online_qualifying_ranking.html | 31 ++++++++++++ backend/db/query.sql.go | 58 ++++++++++++++++++++++ backend/query.sql | 16 ++++++ 5 files changed, 146 insertions(+) create mode 100644 backend/admin/templates/online_qualifying_ranking.html diff --git a/backend/admin/handler.go b/backend/admin/handler.go index 8daead0..161f85c 100644 --- a/backend/admin/handler.go +++ b/backend/admin/handler.go @@ -63,6 +63,7 @@ func (h *Handler) RegisterHandlers(g *echo.Group) { g.GET("/games/:gameID", h.getGameEdit) g.POST("/games/:gameID", h.postGameEdit) g.POST("/games/:gameID/start", h.postGameStart) + g.GET("/online-qualifying-ranking", h.getOnlineQualifyingRanking) } func (h *Handler) getDashboard(c echo.Context) error { @@ -388,3 +389,40 @@ func (h *Handler) postGameStart(c echo.Context) error { return c.Redirect(http.StatusSeeOther, basePath+"/admin/games") } + +func (h *Handler) getOnlineQualifyingRanking(c echo.Context) error { + game1, err := strconv.Atoi(c.QueryParam("game_1")) + if err != nil { + return echo.NewHTTPError(http.StatusBadRequest, "Invalid game_1") + } + game2, err := strconv.Atoi(c.QueryParam("game_2")) + if err != nil { + return echo.NewHTTPError(http.StatusBadRequest, "Invalid game_2") + } + + rows, err := h.q.GetQualifyingRanking(c.Request().Context(), db.GetQualifyingRankingParams{ + GameID: int32(game1), + GameID_2: int32(game2), + }) + if err != nil { + return echo.NewHTTPError(http.StatusInternalServerError, err.Error()) + } + + entries := make([]echo.Map, len(rows)) + for i, r := range rows { + entries[i] = echo.Map{ + "Rank": i + 1, + "Username": r.Username, + "Score1": r.CodeSize1, + "Score2": r.CodeSize2, + "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"), + } + } + return c.Render(http.StatusOK, "online_qualifying_ranking", echo.Map{ + "BasePath": basePath, + "Title": "Online Qualifying Ranking", + "Entries": entries, + }) +} diff --git a/backend/admin/templates/dashboard.html b/backend/admin/templates/dashboard.html index 15b10ff..2d030ef 100644 --- a/backend/admin/templates/dashboard.html +++ b/backend/admin/templates/dashboard.html @@ -7,6 +7,9 @@

Games

+

+ Online Qualifying Ranking +

diff --git a/backend/admin/templates/online_qualifying_ranking.html b/backend/admin/templates/online_qualifying_ranking.html new file mode 100644 index 0000000..3b00046 --- /dev/null +++ b/backend/admin/templates/online_qualifying_ranking.html @@ -0,0 +1,31 @@ +{{ template "base.html" . }} + +{{ define "breadcrumb" }} +Dashboard +{{ end }} + +{{ define "content" }} + + + + + + + + + + + {{ range .Entries }} + + + + + + + + + + {{ end }} + +
順位プレイヤースコア提出時刻
{{ .Rank }}{{ .Username }}{{ .Score1 }}{{ .Score2 }}{{ .TotalScore }}{{ .SubmittedAt1 }}{{ .SubmittedAt2 }}
+{{ end }} diff --git a/backend/db/query.sql.go b/backend/db/query.sql.go index 5e840fa..a478b69 100644 --- a/backend/db/query.sql.go +++ b/backend/db/query.sql.go @@ -273,6 +273,64 @@ func (q *Queries) GetLatestStatesOfMainPlayers(ctx context.Context, gameID int32 return items, nil } +const getQualifyingRanking = `-- name: GetQualifyingRanking :many +SELECT + u.username AS username, + s1.code_size AS code_size_1, + s2.code_size AS code_size_2, + (s1.code_size + s2.code_size) AS total_code_size, + s1.created_at AS submitted_at_1, + s2.created_at AS submitted_at_2 +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 users u ON gs1.user_id = u.user_id +WHERE gs1.game_id = $1 AND gs2.game_id = $2 +ORDER BY total_code_size ASC +` + +type GetQualifyingRankingParams struct { + GameID int32 + GameID_2 int32 +} + +type GetQualifyingRankingRow struct { + Username string + CodeSize1 int32 + CodeSize2 int32 + TotalCodeSize int32 + SubmittedAt1 pgtype.Timestamp + SubmittedAt2 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) + if err != nil { + return nil, err + } + defer rows.Close() + var items []GetQualifyingRankingRow + for rows.Next() { + var i GetQualifyingRankingRow + if err := rows.Scan( + &i.Username, + &i.CodeSize1, + &i.CodeSize2, + &i.TotalCodeSize, + &i.SubmittedAt1, + &i.SubmittedAt2, + ); err != nil { + return nil, err + } + items = append(items, i) + } + if err := rows.Err(); err != nil { + return nil, err + } + return items, nil +} + const getRanking = `-- name: GetRanking :many SELECT submissions.submission_id, submissions.game_id, submissions.user_id, submissions.code, submissions.code_size, submissions.status, submissions.created_at, diff --git a/backend/query.sql b/backend/query.sql index 2a95a23..4707eff 100644 --- a/backend/query.sql +++ b/backend/query.sql @@ -132,6 +132,22 @@ JOIN submissions ON game_states.best_score_submission_id = submissions.submissio WHERE game_states.game_id = $1 ORDER BY submissions.code_size ASC, submissions.created_at ASC; +-- name: GetQualifyingRanking :many +SELECT + u.username AS username, + s1.code_size AS code_size_1, + s2.code_size AS code_size_2, + (s1.code_size + s2.code_size) AS total_code_size, + s1.created_at AS submitted_at_1, + s2.created_at AS submitted_at_2 +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 users u ON gs1.user_id = u.user_id +WHERE gs1.game_id = $1 AND gs2.game_id = $2 +ORDER BY total_code_size ASC; + -- name: UpdateCode :exec INSERT INTO game_states (game_id, user_id, code, status) VALUES ($1, $2, $3, $4) -- cgit v1.2.3-70-g09d2