diff options
| -rw-r--r-- | backend/admin/handler.go | 7 | ||||
| -rw-r--r-- | backend/admin/templates/dashboard.html | 2 | ||||
| -rw-r--r-- | backend/admin/templates/online_qualifying_ranking.html | 2 | ||||
| -rw-r--r-- | backend/db/query.sql.go | 17 | ||||
| -rw-r--r-- | backend/query.sql | 10 |
5 files changed, 30 insertions, 8 deletions
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 @@ <a href="{{ .BasePath }}admin/problems">Problems</a> </p> <p> - <a href="{{ .BasePath }}admin/online-qualifying-ranking?game_1=7&game_2=8">Online Qualifying Ranking</a> + <a href="{{ .BasePath }}admin/online-qualifying-ranking">Online Qualifying Ranking</a> </p> <form method="post" action="{{ .BasePath }}admin/fix"> <button type="submit">fix</button> 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 @@ <th scope="col">プレイヤー</th> <th scope="col">スコア1</th> <th scope="col">スコア2</th> + <th scope="col">スコア3</th> <th scope="col">合計スコア</th> <th scope="col">提出時刻1</th> <th scope="col">提出時刻2</th> @@ -24,6 +25,7 @@ <td>{{ .Username }}{{ if .UserLabel }} ({{ .UserLabel }}){{ end }}</td> <td>{{ .Score1 }}</td> <td>{{ .Score2 }}</td> + <td>{{ .Score3 }}</td> <td>{{ .TotalScore }}</td> <td>{{ .SubmittedAt1 }}</td> <td>{{ .SubmittedAt2 }}</td> 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 |