flashwave/forum-leaderboard.php

## forum-leaderboard.php
<?php
chdir(__DIR__ . '/../../flashii.net/');

require_once 'misuzu.php';

define('FL_START_YEAR', 2018);
define('FL_START_MONTH', 12);

define('FL_TOTAL', '
    SELECT
        u.`user_id`, u.`username`,
        COUNT(fp.`post_id`) as `posts`
    FROM `msz_users` AS u
    LEFT JOIN `msz_forum_posts` AS fp
    ON fp.`user_id` = u.`user_id`
    WHERE fp.`post_deleted` IS NULL
    GROUP BY u.`user_id`
    HAVING `posts` > :threshold
    ORDER BY `posts` DESC
');
define('FL_YEAR', '
    SELECT
        u.`user_id`, u.`username`,
        COUNT(fp.`post_id`) as `posts`
    FROM `msz_users` AS u
    LEFT JOIN `msz_forum_posts` AS fp
    ON fp.`user_id` = u.`user_id`
    WHERE DATE(fp.`post_created`) BETWEEN \'%1$d-01-01\' AND \'%1$d-12-31\'
    AND fp.`post_deleted` IS NULL
    GROUP BY u.`user_id`
    HAVING `posts` > :threshold
    ORDER BY `posts` DESC
');
define('FL_MONTH', '
    SELECT
        u.`user_id`, u.`username`,
        COUNT(fp.`post_id`) as `posts`
    FROM `msz_users` AS u
    LEFT JOIN `msz_forum_posts` AS fp
    ON fp.`user_id` = u.`user_id`
    WHERE DATE(fp.`post_created`) BETWEEN \'%1$d-%2$d-01\' AND \'%1$d-%2$d-31\'
    AND fp.`post_deleted` IS NULL
    GROUP BY u.`user_id`
    HAVING `posts` > :threshold
    ORDER BY `posts` DESC
');

$threshold = (int)($_GET['threshold'] ?? 0);
$asMarkdown = (bool)($_GET['md'] ?? false);
$maxRanks = (int)($_GET['ranks'] ?? 0);

$leaderboards = [
    0 => ['All Time', FL_TOTAL],
];

for ($i = FL_START_YEAR; $i <= date('Y'); $i++) {
    $leaderboards[$i] = ["Leaderboard {$i}", sprintf(FL_YEAR, $i)];
}

for ($i = FL_START_YEAR, $j = FL_START_MONTH;;) {
    $leaderboards[sprintf('%d%02d', $i, $j)] = [sprintf('Leaderboard %d-%02d', $i, $j), sprintf(FL_MONTH, $i, $j)];

    if ($j >= 12) {
        $i++; $j = 1;
    } else $j++;

    if ($i >= date('Y') && $j > date('m'))
        break;
}

foreach ($leaderboards as $index => $board) {
    echo "<a href='?board={$index}'>{$board[0]}</a> ";
}
echo '<hr>';

$boardId = (int)($_GET['board'] ?? 0);
$leaderboard = array_key_exists($boardId, $leaderboards) ? $leaderboards[$boardId] : $leaderboards[0];
$getUsers = db_prepare($leaderboard[1]);
$getUsers->bindValue('threshold', $threshold);
$users = db_fetch_all($getUsers);

echo "<h1>{$leaderboard[0]}</h1>";

if (!$asMarkdown)
echo <<<HTML
<a href="?board={$boardId}&amp;threshold={$threshold}&amp;md=1&amp;ranks={$maxRanks}">export markdown</a><br><br>
<table border="1">
<tr>
    <th>#</th>
    <th>Username</th>
    <th>Posts</th>
</tr>
HTML;
else echo <<<MD
<textarea style="width: 1000px; height: 700px;">
# {$leaderboard[0]}

| Rank | Usename | Post count |
| ----:|:------- | ----------:|

MD;

$ranking = 0;
$posts = PHP_INT_MAX;

foreach ($users as $user) {
    if ($posts > $user['posts']) {
        $posts = $user['posts'];
        $ranking += 1;
    }

    if ($maxRanks > 0 && $ranking > $maxRanks)
        break;

    if (!$asMarkdown)
        echo '<tr><td>' . $ranking . '</td><td><a href="https://flashii.net/profile.php?u=' . $user['user_id'] . '">' . $user['username'] . '</a></td><td>' . number_format($user['posts']) . '</td></tr>';
    else
        echo "| #" . $ranking . " | [" . $user['username'] . '](https://flashii.net/profile.php?u=' . $user['user_id'] . ") | " . number_format($user['posts']) . ' |' . PHP_EOL;
}

if (!$asMarkdown)
echo '</table>';
else echo '</textarea>';
	<?php
	chdir(__DIR__ . '/../../flashii.net/');

	require_once 'misuzu.php';

	define('FL_START_YEAR', 2018);
	define('FL_START_MONTH', 12);

	define('FL_TOTAL', '
	SELECT
	u.`user_id`, u.`username`,
	COUNT(fp.`post_id`) as `posts`
	FROM `msz_users` AS u
	LEFT JOIN `msz_forum_posts` AS fp
	ON fp.`user_id` = u.`user_id`
	WHERE fp.`post_deleted` IS NULL
	GROUP BY u.`user_id`
	HAVING `posts` > :threshold
	ORDER BY `posts` DESC
	');
	define('FL_YEAR', '
	SELECT
	u.`user_id`, u.`username`,
	COUNT(fp.`post_id`) as `posts`
	FROM `msz_users` AS u
	LEFT JOIN `msz_forum_posts` AS fp
	ON fp.`user_id` = u.`user_id`
	WHERE DATE(fp.`post_created`) BETWEEN \'%1$d-01-01\' AND \'%1$d-12-31\'
	AND fp.`post_deleted` IS NULL
	GROUP BY u.`user_id`
	HAVING `posts` > :threshold
	ORDER BY `posts` DESC
	');
	define('FL_MONTH', '
	SELECT
	u.`user_id`, u.`username`,
	COUNT(fp.`post_id`) as `posts`
	FROM `msz_users` AS u
	LEFT JOIN `msz_forum_posts` AS fp
	ON fp.`user_id` = u.`user_id`
	WHERE DATE(fp.`post_created`) BETWEEN \'%1$d-%2$d-01\' AND \'%1$d-%2$d-31\'
	AND fp.`post_deleted` IS NULL
	GROUP BY u.`user_id`
	HAVING `posts` > :threshold
	ORDER BY `posts` DESC
	');

	$threshold = (int)($_GET['threshold'] ?? 0);
	$asMarkdown = (bool)($_GET['md'] ?? false);
	$maxRanks = (int)($_GET['ranks'] ?? 0);

	$leaderboards = [
	0 => ['All Time', FL_TOTAL],
	];

	for ($i = FL_START_YEAR; $i <= date('Y'); $i++) {
	$leaderboards[$i] = ["Leaderboard {$i}", sprintf(FL_YEAR, $i)];
	}

	for ($i = FL_START_YEAR, $j = FL_START_MONTH;;) {
	$leaderboards[sprintf('%d%02d', $i, $j)] = [sprintf('Leaderboard %d-%02d', $i, $j), sprintf(FL_MONTH, $i, $j)];

	if ($j >= 12) {
	$i++; $j = 1;
	} else $j++;

	if ($i >= date('Y') && $j > date('m'))
	break;
	}

	foreach ($leaderboards as $index => $board) {
	echo "<a href='?board={$index}'>{$board[0]}</a> ";
	}
	echo '<hr>';

	$boardId = (int)($_GET['board'] ?? 0);
	$leaderboard = array_key_exists($boardId, $leaderboards) ? $leaderboards[$boardId] : $leaderboards[0];
	$getUsers = db_prepare($leaderboard[1]);
	$getUsers->bindValue('threshold', $threshold);
	$users = db_fetch_all($getUsers);

	echo "<h1>{$leaderboard[0]}</h1>";

	if (!$asMarkdown)
	echo <<<HTML
	<a href="?board={$boardId}&threshold={$threshold}&md=1&ranks={$maxRanks}">export markdown</a><br><br>
	<table border="1">
	<tr>
	<th>#</th>
	<th>Username</th>
	<th>Posts</th>
	</tr>
	HTML;
	else echo <<<MD
	<textarea style="width: 1000px; height: 700px;">
	# {$leaderboard[0]}

	\| Rank \| Usename \| Post count \|
	\| ----:\|:------- \| ----------:\|

	MD;

	$ranking = 0;
	$posts = PHP_INT_MAX;

	foreach ($users as $user) {
	if ($posts > $user['posts']) {
	$posts = $user['posts'];
	$ranking += 1;
	}

	if ($maxRanks > 0 && $ranking > $maxRanks)
	break;

	if (!$asMarkdown)
	echo '<tr><td>' . $ranking . '</td><td><a href="https://flashii.net/profile.php?u=' . $user['user_id'] . '">' . $user['username'] . '</a></td><td>' . number_format($user['posts']) . '</td></tr>';
	else
	echo "\| #" . $ranking . " \| [" . $user['username'] . '](https://flashii.net/profile.php?u=' . $user['user_id'] . ") \| " . number_format($user['posts']) . ' \|' . PHP_EOL;
	}

	if (!$asMarkdown)
	echo '</table>';
	else echo '</textarea>';