From e014d9778f739956104caf686c70639914045281 Mon Sep 17 00:00:00 2001 From: nsfisis Date: Sat, 9 Mar 2024 11:04:51 +0900 Subject: add /admin/stats/ --- services/app/src/App.php | 29 ++++++++ services/app/src/Repositories/AnswerRepository.php | 80 ++++++++++++++++++++++ services/app/src/Sql/Internal/Select.php | 4 +- services/app/src/Sql/QueryBuilder.php | 13 +++- services/app/templates/admin_overview.html.twig | 3 + services/app/templates/admin_stats.html.twig | 34 +++++++++ 6 files changed, 159 insertions(+), 4 deletions(-) create mode 100644 services/app/templates/admin_stats.html.twig diff --git a/services/app/src/App.php b/services/app/src/App.php index 77f24be..b2ffba0 100644 --- a/services/app/src/App.php +++ b/services/app/src/App.php @@ -142,6 +142,8 @@ final class App }); }); }); + + $group->get('/stats/', $this->handleAdminStats(...))->setName('admin_stats'); }) ->add(AdminRequiredMiddleware::create($app)) ->add(AuthRequiredMiddleware::create($app, 'login')); @@ -865,6 +867,33 @@ final class App return $this->makeRedirectResponse($response, $routeParser->urlFor('admin_testcase_list', ['qslug' => $qslug])); } + private function handleAdminStats( + ServerRequestInterface $request, + ResponseInterface $response, + QuizRepository $quizRepo, + AnswerRepository $answerRepo, + ): ResponseInterface { + $quizzes = $quizRepo->listAll(); + $rankings = []; + $best_codes = []; + foreach ($quizzes as $quiz) { + $rankings[] = $answerRepo->getRankingByBestScores($quiz->quiz_id, upto: 3); + $best_codes[] = $answerRepo->getBestCode($quiz->quiz_id) ?? ''; + } + + $n_attendees = $answerRepo->countUniqueAuthors(); + $n_answers = $answerRepo->countAll(); + + return $this->render($request, $response, 'admin_stats.html.twig', [ + 'page_title' => '管理画面 - 各種統計', + 'n_attendees' => $n_attendees, + 'n_answers' => $n_answers, + 'quizzes' => $quizzes, + 'rankings' => $rankings, + 'best_codes' => $best_codes, + ]); + } + private function handleApiAnswerStatuses( string $aid, ServerRequestInterface $request, diff --git a/services/app/src/Repositories/AnswerRepository.php b/services/app/src/Repositories/AnswerRepository.php index 77a0cc8..1fa3e08 100644 --- a/services/app/src/Repositories/AnswerRepository.php +++ b/services/app/src/Repositories/AnswerRepository.php @@ -117,6 +117,58 @@ final class AnswerRepository return array_map($this->mapRawRowToAnswer(...), $result); } + /** + * @param positive-int $upto + * @return Answer[] + */ + public function getRankingByBestScores(int $quiz_id, int $upto, bool $show_admin = false): array + { + $q = $this->conn + ->query() + ->select('answers') + ->leftJoin('users', 'answers.author_id = users.user_id') + ->fields([ + ...self::ANSWER_FIELDS, + ...self::ANSWER_JOIN_USER_FIELDS, + 'ROW_NUMBER() OVER(PARTITION BY answers.author_id ORDER BY answers.code_size ASC, answers.submitted_at ASC) AS r', + ]) + ->where( + 'quiz_id = :quiz_id AND execution_status = :execution_status' + . ($show_admin ? '' : ' AND users.is_admin = FALSE') + ); + + $result = $this->conn + ->query() + ->select($q) + ->fields([ + ...self::ANSWER_FIELDS, + 'author_name', + 'author_is_admin', + ]) + ->where('r = 1') + ->orderBy([['code_size', 'ASC'], ['submitted_at', 'ASC']]) + ->execute(['quiz_id' => $quiz_id, 'execution_status' => AggregatedExecutionStatus::OK->toInt()]); + return array_map($this->mapRawRowToAnswer(...), $result); + } + + public function getBestCode(int $quiz_id, bool $show_admin = false): ?string + { + $result = $this->conn + ->query() + ->select('answers') + ->leftJoin('users', 'answers.author_id = users.user_id') + ->fields([...self::ANSWER_FIELDS, ...self::ANSWER_JOIN_USER_FIELDS]) + ->where( + 'quiz_id = :quiz_id AND execution_status = :execution_status' + . ($show_admin ? '' : ' AND users.is_admin = FALSE') + ) + ->orderBy([['code_size', 'ASC'], ['submitted_at', 'ASC']]) + ->first() + ->execute(['quiz_id' => $quiz_id, 'execution_status' => AggregatedExecutionStatus::OK->toInt()]); + + return isset($result) ? $this->mapRawRowToAnswer($result)->code : null; + } + /** * @return Answer[] */ @@ -136,6 +188,34 @@ final class AnswerRepository return array_map($this->mapRawRowToAnswer(...), $result); } + public function countUniqueAuthors(bool $show_admin = false): int + { + $result = $this->conn + ->query() + ->select('answers') + ->leftJoin('users', 'answers.author_id = users.user_id') + ->fields(['COUNT(DISTINCT author_id) AS count']) + ->where($show_admin ? '' : 'users.is_admin = FALSE') + ->first() + ->execute(); + assert(isset($result['count'])); + return (int) $result['count']; + } + + public function countAll(bool $show_admin = false): int + { + $result = $this->conn + ->query() + ->select('answers') + ->leftJoin('users', 'answers.author_id = users.user_id') + ->fields(['COUNT(*) AS count']) + ->where($show_admin ? '' : 'users.is_admin = FALSE') + ->first() + ->execute(); + assert(isset($result['count'])); + return (int) $result['count']; + } + public function create( int $quiz_id, int $author_id, diff --git a/services/app/src/Sql/Internal/Select.php b/services/app/src/Sql/Internal/Select.php index cf3f77a..d5943aa 100644 --- a/services/app/src/Sql/Internal/Select.php +++ b/services/app/src/Sql/Internal/Select.php @@ -33,7 +33,7 @@ final class Select public function __construct( private readonly QueryBuilder $sql, - private readonly string $table, + private readonly string|Select $table, ) { } @@ -94,7 +94,7 @@ final class Select /** * @internal */ - public function _getTable(): string + public function _getTable(): string|Select { return $this->table; } diff --git a/services/app/src/Sql/QueryBuilder.php b/services/app/src/Sql/QueryBuilder.php index 3a5443b..f6aee00 100644 --- a/services/app/src/Sql/QueryBuilder.php +++ b/services/app/src/Sql/QueryBuilder.php @@ -27,7 +27,7 @@ final class QueryBuilder ) { } - public function select(string $table): Select + public function select(string|Select $table): Select { return new Select($this, $table); } @@ -57,6 +57,11 @@ final class QueryBuilder $this->conn->exec($sql); } + public function raw(string $sql): void + { + $this->conn->exec($sql); + } + /** * @internal * @param Select $select @@ -87,7 +92,11 @@ final class QueryBuilder return "SELECT " . implode(', ', $fields) . - " FROM $table" . + ( + $table instanceof Select + ? " FROM (" . $this->compileSelect($table) . ")" + : " FROM $table" + ) . ($join !== null ? " $join->type $join->table ON $join->on" : '') . ($where !== '' ? " WHERE $where" : '') . ( diff --git a/services/app/templates/admin_overview.html.twig b/services/app/templates/admin_overview.html.twig index 2103616..6256165 100644 --- a/services/app/templates/admin_overview.html.twig +++ b/services/app/templates/admin_overview.html.twig @@ -10,4 +10,7 @@
問題一覧
+
+ 各種統計 +
{% endblock %} diff --git a/services/app/templates/admin_stats.html.twig b/services/app/templates/admin_stats.html.twig new file mode 100644 index 0000000..7b1b2fa --- /dev/null +++ b/services/app/templates/admin_stats.html.twig @@ -0,0 +1,34 @@ +{% extends '_page.html.twig' %} + +{% block content %} +

+ このページは管理画面です +

+

統計

+ +

問題

+ {% for quiz in quizzes %} +

第{{ loop.index }}問: {{ quiz.title }}

+

+ {{ quiz.description }} +

+

最短解

+ 
{{ best_codes[loop.index - 1] }}
+ {% endfor %} +{% endblock %} -- cgit v1.2.3-70-g09d2