EditCounterRepository::getThanksReceived() - Code Metrics - Inspection of "EditCounter: show number of Thanks received by the..." - x-tools/xtools - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Pull Request — main (#415)

by MusikAnimal

created 2022-03-15 22:31 UTC

EditCounterRepository::getThanksReceived() A

↳ Parent: EditCounterRepository

Complexity

Conditions	2
Paths	2

Size

Total Lines	18
Code Lines	10

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	2
eloc	10
nc	2
nop	2
dl	0
loc	18
rs	9.9332
c	0
b	0
f	0

<?php
/**
 * This file contains only the EditCounterRepository class.
 */

declare(strict_types = 1);

namespace AppBundle\Repository;

use AppBundle\Model\Project;
use AppBundle\Model\User;
use Wikimedia\IPUtils;

/**
 * An EditCounterRepository is responsible for retrieving edit count information from the
 * databases and API. It doesn't do any post-processing of that information.
 * @codeCoverageIgnore
 */
class EditCounterRepository extends UserRightsRepository
{
    /**
     * Get data about revisions, pages, etc.
     * @param Project $project The project.
     * @param User $user The user.
     * @return string[] With keys: 'deleted', 'live', 'total', '24h', '7d', '30d',
     * '365d', 'small', 'large', 'with_comments', and 'minor_edits', ...
     */
    public function getPairData(Project $project, User $user): array
    {
        // Set up cache.
        $cacheKey = $this->getCacheKey(func_get_args(), 'ec_pairdata');
        if ($this->cache->hasItem($cacheKey)) {
            return $this->cache->getItem($cacheKey)->get();
        }

        // Prepare the queries and execute them.
        $archiveTable = $project->getTableName('archive');
        $revisionTable = $project->getTableName('revision');

        if ($user->isIpRange()) {
            $ipcTable = $project->getTableName('ip_changes');
            $ipcJoin = "JOIN $ipcTable ON ipc_rev_id = rev_id";
            $whereClause = "ipc_hex BETWEEN :startIp AND :endIp";
            $archiveQueries = '';
            $params = [];
            [$params['startIp'], $params['endIp']] = IPUtils::parseRange($user->getUsername());
        } else {
            $ipcJoin = '';
            $whereClause = 'rev_actor = :actorId';
            $params = ['actorId' => $user->getActorId($project)];
            $archiveQueries = "
                SELECT 'deleted' AS `key`, COUNT(ar_id) AS val FROM $archiveTable
                    WHERE ar_actor = :actorId
                ) UNION (
                SELECT 'edited-deleted' AS `key`, COUNT(DISTINCT ar_page_id) AS `val` FROM $archiveTable
                    WHERE ar_actor = :actorId
                ) UNION (
                SELECT 'created-deleted' AS `key`, COUNT(DISTINCT ar_page_id) AS `val` FROM $archiveTable
                    WHERE ar_actor = :actorId AND ar_parent_id = 0
                ) UNION (";
        }

        $sql = "
            ($archiveQueries

            -- Revision counts.
            SELECT 'live' AS `key`, COUNT(rev_id) AS val FROM $revisionTable
                $ipcJoin
                WHERE $whereClause
            ) UNION (
            SELECT 'day' AS `key`, COUNT(rev_id) AS val FROM $revisionTable
                $ipcJoin
                WHERE $whereClause AND rev_timestamp >= DATE_SUB(NOW(), INTERVAL 1 DAY)
            ) UNION (
            SELECT 'week' AS `key`, COUNT(rev_id) AS val FROM $revisionTable
                $ipcJoin
                WHERE $whereClause AND rev_timestamp >= DATE_SUB(NOW(), INTERVAL 1 WEEK)
            ) UNION (
            SELECT 'month' AS `key`, COUNT(rev_id) AS val FROM $revisionTable
                $ipcJoin
                WHERE $whereClause AND rev_timestamp >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
            ) UNION (
            SELECT 'year' AS `key`, COUNT(rev_id) AS val FROM $revisionTable
                $ipcJoin
                WHERE $whereClause AND rev_timestamp >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
            ) UNION (
            SELECT 'minor' AS `key`, COUNT(rev_id) AS val FROM $revisionTable
                $ipcJoin
                WHERE $whereClause AND rev_minor_edit = 1

            -- Page counts.
            ) UNION (
            SELECT 'edited-live' AS `key`, COUNT(DISTINCT rev_page) AS `val`
                FROM $revisionTable
                $ipcJoin
                WHERE $whereClause
            ) UNION (
            SELECT 'created-live' AS `key`, COUNT(DISTINCT rev_page) AS `val`
                FROM $revisionTable
                $ipcJoin
                WHERE $whereClause AND rev_parent_id = 0
            )";

        $resultQuery = $this->executeProjectsQuery($project, $sql, $params);

        $revisionCounts = [];
        while ($result = $resultQuery->fetch()) {
            $revisionCounts[$result['key']] = (int)$result['val'];
        }

        // Cache and return.
        return $this->setCache($cacheKey, $revisionCounts);
    }

    /**
     * Get log totals for a user.
     * @param Project $project The project.
     * @param User $user The user.
     * @return int[] Keys are "<log>-<action>" strings, values are counts.
     */
    public function getLogCounts(Project $project, User $user): array
    {
        // Set up cache.
        $cacheKey = $this->getCacheKey(func_get_args(), 'ec_logcounts');
        if ($this->cache->hasItem($cacheKey)) {
            return $this->cache->getItem($cacheKey)->get();
        }

        // Query.
        $loggingTable = $this->getTableName($project->getDatabaseName(), 'logging');
        $sql = "
        (SELECT CONCAT(log_type, '-', log_action) AS source, COUNT(log_id) AS value
            FROM $loggingTable
            WHERE log_actor = :actorId
            GROUP BY log_type, log_action
        )";

        $results = $this->executeProjectsQuery($project, $sql, [
            'actorId' => $user->getActorId($project),
        ])->fetchAll();

        $logCounts = array_combine(
            array_map(function ($e) {
                return $e['source'];
            }, $results),
            array_map(function ($e) {
                return (int)$e['value'];
            }, $results)
        );

        // Make sure there is some value for each of the wanted counts.
        $requiredCounts = [
            'thanks-thank',
            'review-approve',
            'newusers-create2',
            'newusers-byemail',
            'patrol-patrol',
            'block-block',
            'block-reblock',
            'block-unblock',
            'protect-protect',
            'protect-modify',
            'protect-unprotect',
            'rights-rights',
            'move-move',
            'delete-delete',
            'delete-revision',
            'delete-restore',
            'import-import',
            'import-interwiki',
            'import-upload',
            'upload-upload',
            'upload-overwrite',
            'abusefilter-modify',
            'merge-merge',
        ];
        foreach ($requiredCounts as $req) {
            if (!isset($logCounts[$req])) {
                $logCounts[$req] = 0;
            }
        }

        // Cache and return.
        return $this->setCache($cacheKey, $logCounts);
    }

    /**
     * Get counts of files moved, and files moved/uploaded on Commons.
     * Local file uploads are counted in getLogCounts() since we're querying the same rows anyway.
     * @param Project $project
     * @param User $user
     * @return array
     */
    public function getFileCounts(Project $project, User $user): array
    {
        // Anons can't upload or move files.
        if ($user->isAnon()) {
            return [];
        }

        // Set up cache.
        $cacheKey = $this->getCacheKey(func_get_args(), 'ec_filecounts');
        if ($this->cache->hasItem($cacheKey)) {
            return $this->cache->getItem($cacheKey)->get();
        }

        $loggingTable = $project->getTableName('logging');

        $sql = "SELECT 'files_moved' AS `key`, COUNT(log_id) AS `val`
                FROM $loggingTable
                WHERE log_actor = :actorId
                    AND log_type = 'move'
                    AND log_action = 'move'
                    AND log_namespace = 6";
        $results = $this->executeProjectsQuery($project, $sql, [
            'actorId' => $user->getActorId($project),
        ])->fetchAll();

        if ($this->isLabs() && 'commons.wikimedia.org' !== $project->getDomain()) {
            $results = array_merge($results, $this->getFileCountsCommons($user));
        }

        $counts = array_combine(
            array_map(function ($e) {
                return $e['key'];
            }, $results),
            array_map(function ($e) {
                return (int)$e['val'];
            }, $results)
        );

        // Cache and return.
        return $this->setCache($cacheKey, $counts);
    }

    /**
     * Get count of files moved and uploaded on Commons.
     * @param User $user
     * @return array
     */
    protected function getFileCountsCommons(User $user): array
    {
        $commonsProject = ProjectRepository::getProject('commonswiki', $this->container);
        $loggingTableCommons = $commonsProject->getTableName('logging');
        $sql = "(SELECT 'files_moved_commons' AS `key`, COUNT(log_id) AS `val`
                 FROM $loggingTableCommons
                 WHERE log_actor = :actorId AND log_type = 'move'
                 AND log_action = 'move' AND log_namespace = 6
                ) UNION (
                 SELECT 'files_uploaded_commons' AS `key`, COUNT(log_id) AS `val`
                 FROM $loggingTableCommons
                 WHERE log_actor = :actorId AND log_type = 'upload' AND log_action = 'upload')";
        return $this->executeProjectsQuery($commonsProject, $sql, [
            'actorId' => $user->getActorId($commonsProject),
        ])->fetchAll();
    }

    /**
     * Get the IDs and timestamps of the latest edit and logged action by the given user.
     * @param Project $project
     * @param User $user
     * @return string[] With keys 'rev_first', 'rev_latest', 'log_latest'.
     */
    public function getFirstAndLatestActions(Project $project, User $user): array
    {
        $cacheKey = $this->getCacheKey(func_get_args(), 'ec_first_latest_actions');
        if ($this->cache->hasItem($cacheKey)) {
            return $this->cache->getItem($cacheKey)->get();
        }

        $loggingTable = $project->getTableName('logging', 'userindex');
        if ($user->isIpRange()) {
            $fromTable = $project->getTableName('ip_changes');
            $idColumn = 'ipc_rev_id';
            $timestampColumn = 'ipc_rev_timestamp';
            $whereClause = "ipc_hex BETWEEN :startIp AND :endIp";
            $params = [];
            [$params['startIp'], $params['endIp']] = IPUtils::parseRange($user->getUsername());
            $logQuery = '';
        } else {
            $fromTable = $project->getTableName('revision');
            $idColumn = 'rev_id';
            $timestampColumn = 'rev_timestamp';
            $whereClause = 'rev_actor = :actorId';
            $params = ['actorId' => $user->getActorId($project)];
            $logQuery = "
                SELECT 'log_latest' AS `key`, log_id AS `id`,
                        log_timestamp AS `timestamp`, log_type AS `type`
                    FROM $loggingTable
                    WHERE log_actor = :actorId
                    ORDER BY log_timestamp DESC LIMIT 1
                ) UNION (";
        }

        $sql = "(
                $logQuery
                    SELECT 'rev_first' AS `key`, $idColumn AS `id`,
                        $timestampColumn AS `timestamp`, NULL as `type`
                    FROM $fromTable
                    WHERE $whereClause
                    ORDER BY $timestampColumn ASC LIMIT 1
                ) UNION (
                    SELECT 'rev_latest' AS `key`, $idColumn AS `id`,
                        $timestampColumn AS `timestamp`, NULL as `type`
                    FROM $fromTable
                    WHERE $whereClause
                    ORDER BY $timestampColumn DESC LIMIT 1
                )";

        $resultQuery = $this->executeProjectsQuery($project, $sql, $params);

        $actions = [];
        while ($result = $resultQuery->fetch()) {
            $actions[$result['key']] = [
                'id' => $result['id'],
                'timestamp' => $result['timestamp'],
                'type' => $result['type'],
            ];
        }

        return $this->setCache($cacheKey, $actions);
    }

    /**
     * Get data for all blocks set on the given user.
     * @param Project $project
     * @param User $user
     * @return array
     */
    public function getBlocksReceived(Project $project, User $user): array
    {
        $loggingTable = $this->getTableName($project->getDatabaseName(), 'logging', 'logindex');
        $sql = "SELECT log_action, log_timestamp, log_params FROM $loggingTable
                WHERE log_type = 'block'
                AND log_action IN ('block', 'reblock', 'unblock')
                AND log_timestamp > 0
                AND log_title = :username
                AND log_namespace = 2
                ORDER BY log_timestamp ASC";
        $username = str_replace(' ', '_', $user->getUsername());

        return $this->executeProjectsQuery($project, $sql, [
            'username' => $username,
        ])->fetchAll();
    }

    /**
     * Get the number of times the user was thanked.
     * @param Project $project
     * @param User $user
     * @return int
     */
    public function getThanksReceived(Project $project, User $user): int
    {
        $cacheKey = $this->getCacheKey(func_get_args(), 'ec_thanksreceived');
        if ($this->cache->hasItem($cacheKey)) {
            return $this->cache->getItem($cacheKey)->get();
        }

        $loggingTable = $project->getTableName('logging', 'logindex');
        $sql = "SELECT COUNT(log_id)
                FROM $loggingTable
                WHERE log_type = 'thanks'
                AND log_title = :username
                AND log_namespace = 2";
        $username = str_replace(' ', '_', $user->getUsername());

        return $this->setCache($cacheKey, (int)$this->executeProjectsQuery($project, $sql, [
            'username' => $username,
        ])->fetchColumn());
    }

    /**
     * Get the given user's total edit counts per namespace on the given project.
     * @param Project $project The project.
     * @param User $user The user.
     * @return array Array keys are namespace IDs, values are the edit counts.
     */
    public function getNamespaceTotals(Project $project, User $user): array
    {
        // Cache?
        $cacheKey = $this->getCacheKey(func_get_args(), 'ec_namespacetotals');
        if ($this->cache->hasItem($cacheKey)) {
            return $this->cache->getItem($cacheKey)->get();
        }

        // Query.
        $revisionTable = $project->getTableName('revision');
        $pageTable = $project->getTableName('page');
        $ipcJoin = '';
        $whereClause = 'r.rev_actor = :actorId';
        $params = ['actorId' => $user->getActorId($project)];

        if ($user->isIpRange()) {
            $ipcTable = $project->getTableName('ip_changes');
            $ipcJoin = "JOIN $ipcTable ON ipc_rev_id = rev_id";
            [$params['startIp'], $params['endIp']] = IPUtils::parseRange($user->getUsername());
            $whereClause = 'ipc_hex BETWEEN :startIp AND :endIp';
        }

        $sql = "SELECT page_namespace, COUNT(rev_id) AS total
            FROM $pageTable p JOIN $revisionTable r ON (r.rev_page = p.page_id)
            $ipcJoin
            WHERE $whereClause
            GROUP BY page_namespace";

        $results = $this->executeProjectsQuery($project, $sql, $params)->fetchAll();

        $namespaceTotals = array_combine(array_map(function ($e) {
            return $e['page_namespace'];
        }, $results), array_map(function ($e) {
            return (int)$e['total'];
        }, $results));

        // Cache and return.
        return $this->setCache($cacheKey, $namespaceTotals);
    }

    /**
     * Get data for a bar chart of monthly edit totals per namespace.
     * @param Project $project The project.
     * @param User $user The user.
     * @return string[] [
     *                      [
     *                          'year' => <year>,
     *                          'month' => <month>,
     *                          'page_namespace' => <namespace>,
     *                          'count' => <count>,
     *                      ],
     *                      ...
     *                  ]
     */
    public function getMonthCounts(Project $project, User $user): array
    {
        $cacheKey = $this->getCacheKey(func_get_args(), 'ec_monthcounts');
        if ($this->cache->hasItem($cacheKey)) {
            return $this->cache->getItem($cacheKey)->get();
        }

        $revisionTable = $project->getTableName('revision');
        $pageTable = $project->getTableName('page');
        $ipcJoin = '';
        $whereClause = 'rev_actor = :actorId';
        $params = ['actorId' => $user->getActorId($project)];

        if ($user->isIpRange()) {
            $ipcTable = $project->getTableName('ip_changes');
            $ipcJoin = "JOIN $ipcTable ON ipc_rev_id = rev_id";
            [$params['startIp'], $params['endIp']] = IPUtils::parseRange($user->getUsername());
            $whereClause = 'ipc_hex BETWEEN :startIp AND :endIp';
        }

        $sql = "
            SELECT YEAR(rev_timestamp) AS `year`,
                MONTH(rev_timestamp) AS `month`,
                page_namespace,
                COUNT(rev_id) AS `count`
            FROM $revisionTable JOIN $pageTable ON (rev_page = page_id)
            $ipcJoin
            WHERE $whereClause
            GROUP BY YEAR(rev_timestamp), MONTH(rev_timestamp), page_namespace";

        $totals = $this->executeProjectsQuery($project, $sql, $params)->fetchAll();

        // Cache and return.
        return $this->setCache($cacheKey, $totals);
    }

    /**
     * Get data for the timecard chart, with totals grouped by day and to the nearest two-hours.
     * @param Project $project
     * @param User $user
     * @return string[]
     */
    public function getTimeCard(Project $project, User $user): array
    {
        $cacheKey = $this->getCacheKey(func_get_args(), 'ec_timecard');
        if ($this->cache->hasItem($cacheKey)) {
            return $this->cache->getItem($cacheKey)->get();
        }

        $hourInterval = 1;

        if ($user->isIpRange()) {
            $column = 'ipc_rev_timestamp';
            $table = $project->getTableName('ip_changes');
            [$params['startIp'], $params['endIp']] = IPUtils::parseRange($user->getUsername());
            $whereClause = 'ipc_hex BETWEEN :startIp AND :endIp';
        } else {
            $column = 'rev_timestamp';
            $table = $project->getTableName('revision');
            $whereClause = 'rev_actor = :actorId';
            $params = ['actorId' => $user->getActorId($project)];
        }

        $xCalc = "ROUND(HOUR($column)/$hourInterval) * $hourInterval";

        $sql = "
            SELECT DAYOFWEEK($column) AS `day_of_week`,
                $xCalc AS `hour`,
                COUNT($column) AS `value`
            FROM $table
            WHERE $whereClause
            GROUP BY DAYOFWEEK($column), $xCalc";

        $totals = $this->executeProjectsQuery($project, $sql, $params)->fetchAll();

        // Cache and return.
        return $this->setCache($cacheKey, $totals);
    }

    /**
     * Get various data about edit sizes of the past 5,000 edits.
     * Will cache the result for 10 minutes.
     * @param Project $project The project.
     * @param User $user The user.
     * @return string[] Values with for keys 'average_size',
     *                  'small_edits' and 'large_edits'
     */
    public function getEditSizeData(Project $project, User $user): array
    {
        // Set up cache.
        $cacheKey = $this->getCacheKey(func_get_args(), 'ec_editsizes');
        if ($this->cache->hasItem($cacheKey)) {
            return $this->cache->getItem($cacheKey)->get();
        }

        // Prepare the queries and execute them.
        $revisionTable = $project->getTableName('revision');
        $ipcJoin = '';
        $whereClause = 'revs.rev_actor = :actorId';
        $params = ['actorId' => $user->getActorId($project)];

        if ($user->isIpRange()) {
            $ipcTable = $project->getTableName('ip_changes');
            $ipcJoin = "JOIN $ipcTable ON ipc_rev_id = revs.rev_id";
            [$params['startIp'], $params['endIp']] = IPUtils::parseRange($user->getUsername());
            $whereClause = 'ipc_hex BETWEEN :startIp AND :endIp';
        }

        $sql = "SELECT AVG(sizes.size) AS average_size,
                COUNT(CASE WHEN sizes.size < 20 THEN 1 END) AS small_edits,
                COUNT(CASE WHEN sizes.size > 1000 THEN 1 END) AS large_edits
                FROM (
                    SELECT (CAST(revs.rev_len AS SIGNED) - IFNULL(parentrevs.rev_len, 0)) AS size
                    FROM $revisionTable AS revs
                    $ipcJoin
                    LEFT JOIN $revisionTable AS parentrevs ON (revs.rev_parent_id = parentrevs.rev_id)
                    WHERE $whereClause
                    ORDER BY revs.rev_timestamp DESC
                    LIMIT 5000
                ) sizes";
        $results = $this->executeProjectsQuery($project, $sql, $params)->fetch();

        // Cache and return.
        return $this->setCache($cacheKey, $results);
    }

    /**
     * Get the number of edits this user made using semi-automated tools.
     * @param Project $project
     * @param User $user
     * @return int Result of query, see below.
     */
    public function countAutomatedEdits(Project $project, User $user): int
    {
        $autoEditsRepo = new AutoEditsRepository();
        $autoEditsRepo->setContainer($this->container);
        return $autoEditsRepo->countAutomatedEdits($project, $user);
    }
}


1			<?php
2			/**
3			* This file contains only the EditCounterRepository class.
4			*/
5
6			declare(strict_types = 1);
7
8			namespace AppBundle\Repository;
9
10			use AppBundle\Model\Project;
11			use AppBundle\Model\User;
12			use Wikimedia\IPUtils;
13
14			/**
15			* An EditCounterRepository is responsible for retrieving edit count information from the
16			* databases and API. It doesn't do any post-processing of that information.
17			* @codeCoverageIgnore
18			*/
19			class EditCounterRepository extends UserRightsRepository
20			{
21			/**
22			* Get data about revisions, pages, etc.
23			* @param Project $project The project.
24			* @param User $user The user.
25			* @return string[] With keys: 'deleted', 'live', 'total', '24h', '7d', '30d',
26			* '365d', 'small', 'large', 'with_comments', and 'minor_edits', ...
27			*/
28			public function getPairData(Project $project, User $user): array
29			{
30			// Set up cache.
31			$cacheKey = $this->getCacheKey(func_get_args(), 'ec_pairdata');
32			if ($this->cache->hasItem($cacheKey)) {
33			return $this->cache->getItem($cacheKey)->get();
34			}
35
36			// Prepare the queries and execute them.
37			$archiveTable = $project->getTableName('archive');
38			$revisionTable = $project->getTableName('revision');
39
40			if ($user->isIpRange()) {
41			$ipcTable = $project->getTableName('ip_changes');
42			$ipcJoin = "JOIN $ipcTable ON ipc_rev_id = rev_id";
43			$whereClause = "ipc_hex BETWEEN :startIp AND :endIp";
44			$archiveQueries = '';
45			$params = [];
46			[$params['startIp'], $params['endIp']] = IPUtils::parseRange($user->getUsername());
47			} else {
48			$ipcJoin = '';
49			$whereClause = 'rev_actor = :actorId';
50			$params = ['actorId' => $user->getActorId($project)];
51			$archiveQueries = "
52			SELECT 'deleted' AS `key`, COUNT(ar_id) AS val FROM $archiveTable
53			WHERE ar_actor = :actorId
54			) UNION (
55			SELECT 'edited-deleted' AS `key`, COUNT(DISTINCT ar_page_id) AS `val` FROM $archiveTable
56			WHERE ar_actor = :actorId
57			) UNION (
58			SELECT 'created-deleted' AS `key`, COUNT(DISTINCT ar_page_id) AS `val` FROM $archiveTable
59			WHERE ar_actor = :actorId AND ar_parent_id = 0
60			) UNION (";
61			}
62
63			$sql = "
64			($archiveQueries
65
66			-- Revision counts.
67			SELECT 'live' AS `key`, COUNT(rev_id) AS val FROM $revisionTable
68			$ipcJoin
69			WHERE $whereClause
70			) UNION (
71			SELECT 'day' AS `key`, COUNT(rev_id) AS val FROM $revisionTable
72			$ipcJoin
73			WHERE $whereClause AND rev_timestamp >= DATE_SUB(NOW(), INTERVAL 1 DAY)
74			) UNION (
75			SELECT 'week' AS `key`, COUNT(rev_id) AS val FROM $revisionTable
76			$ipcJoin
77			WHERE $whereClause AND rev_timestamp >= DATE_SUB(NOW(), INTERVAL 1 WEEK)
78			) UNION (
79			SELECT 'month' AS `key`, COUNT(rev_id) AS val FROM $revisionTable
80			$ipcJoin
81			WHERE $whereClause AND rev_timestamp >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
82			) UNION (
83			SELECT 'year' AS `key`, COUNT(rev_id) AS val FROM $revisionTable
84			$ipcJoin
85			WHERE $whereClause AND rev_timestamp >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
86			) UNION (
87			SELECT 'minor' AS `key`, COUNT(rev_id) AS val FROM $revisionTable
88			$ipcJoin
89			WHERE $whereClause AND rev_minor_edit = 1
90
91			-- Page counts.
92			) UNION (
93			SELECT 'edited-live' AS `key`, COUNT(DISTINCT rev_page) AS `val`
94			FROM $revisionTable
95			$ipcJoin
96			WHERE $whereClause
97			) UNION (
98			SELECT 'created-live' AS `key`, COUNT(DISTINCT rev_page) AS `val`
99			FROM $revisionTable
100			$ipcJoin
101			WHERE $whereClause AND rev_parent_id = 0
102			)";
103
104			$resultQuery = $this->executeProjectsQuery($project, $sql, $params);
105
106			$revisionCounts = [];
107			while ($result = $resultQuery->fetch()) {
108			$revisionCounts[$result['key']] = (int)$result['val'];
109			}
110
111			// Cache and return.
112			return $this->setCache($cacheKey, $revisionCounts);
113			}
114
115			/**
116			* Get log totals for a user.
117			* @param Project $project The project.
118			* @param User $user The user.
119			* @return int[] Keys are "<log>-<action>" strings, values are counts.
120			*/
121			public function getLogCounts(Project $project, User $user): array
122			{
123			// Set up cache.
124			$cacheKey = $this->getCacheKey(func_get_args(), 'ec_logcounts');
125			if ($this->cache->hasItem($cacheKey)) {
126			return $this->cache->getItem($cacheKey)->get();
127			}
128
129			// Query.
130			$loggingTable = $this->getTableName($project->getDatabaseName(), 'logging');
131			$sql = "
132			(SELECT CONCAT(log_type, '-', log_action) AS source, COUNT(log_id) AS value
133			FROM $loggingTable
134			WHERE log_actor = :actorId
135			GROUP BY log_type, log_action
136			)";
137
138			$results = $this->executeProjectsQuery($project, $sql, [
139			'actorId' => $user->getActorId($project),
140			])->fetchAll();
141
142			$logCounts = array_combine(
143			array_map(function ($e) {
144			return $e['source'];
145			}, $results),
146			array_map(function ($e) {
147			return (int)$e['value'];
148			}, $results)
149			);
150
151			// Make sure there is some value for each of the wanted counts.
152			$requiredCounts = [
153			'thanks-thank',
154			'review-approve',
155			'newusers-create2',
156			'newusers-byemail',
157			'patrol-patrol',
158			'block-block',
159			'block-reblock',
160			'block-unblock',
161			'protect-protect',
162			'protect-modify',
163			'protect-unprotect',
164			'rights-rights',
165			'move-move',
166			'delete-delete',
167			'delete-revision',
168			'delete-restore',
169			'import-import',
170			'import-interwiki',
171			'import-upload',
172			'upload-upload',
173			'upload-overwrite',
174			'abusefilter-modify',
175			'merge-merge',
176			];
177			foreach ($requiredCounts as $req) {
178			if (!isset($logCounts[$req])) {
179			$logCounts[$req] = 0;
180			}
181			}
182
183			// Cache and return.
184			return $this->setCache($cacheKey, $logCounts);
185			}
186
187			/**
188			* Get counts of files moved, and files moved/uploaded on Commons.
189			* Local file uploads are counted in getLogCounts() since we're querying the same rows anyway.
190			* @param Project $project
191			* @param User $user
192			* @return array
193			*/
194			public function getFileCounts(Project $project, User $user): array
195			{
196			// Anons can't upload or move files.
197			if ($user->isAnon()) {
198			return [];
199			}
200
201			// Set up cache.
202			$cacheKey = $this->getCacheKey(func_get_args(), 'ec_filecounts');
203			if ($this->cache->hasItem($cacheKey)) {
204			return $this->cache->getItem($cacheKey)->get();
205			}
206
207			$loggingTable = $project->getTableName('logging');
208
209			$sql = "SELECT 'files_moved' AS `key`, COUNT(log_id) AS `val`
210			FROM $loggingTable
211			WHERE log_actor = :actorId
212			AND log_type = 'move'
213			AND log_action = 'move'
214			AND log_namespace = 6";
215			$results = $this->executeProjectsQuery($project, $sql, [
216			'actorId' => $user->getActorId($project),
217			])->fetchAll();
218
219			if ($this->isLabs() && 'commons.wikimedia.org' !== $project->getDomain()) {
220			$results = array_merge($results, $this->getFileCountsCommons($user));
221			}
222
223			$counts = array_combine(
224			array_map(function ($e) {
225			return $e['key'];
226			}, $results),
227			array_map(function ($e) {
228			return (int)$e['val'];
229			}, $results)
230			);
231
232			// Cache and return.
233			return $this->setCache($cacheKey, $counts);
234			}
235
236			/**
237			* Get count of files moved and uploaded on Commons.
238			* @param User $user
239			* @return array
240			*/
241			protected function getFileCountsCommons(User $user): array
242			{
243			$commonsProject = ProjectRepository::getProject('commonswiki', $this->container);
244			$loggingTableCommons = $commonsProject->getTableName('logging');
245			$sql = "(SELECT 'files_moved_commons' AS `key`, COUNT(log_id) AS `val`
246			FROM $loggingTableCommons
247			WHERE log_actor = :actorId AND log_type = 'move'
248			AND log_action = 'move' AND log_namespace = 6
249			) UNION (
250			SELECT 'files_uploaded_commons' AS `key`, COUNT(log_id) AS `val`
251			FROM $loggingTableCommons
252			WHERE log_actor = :actorId AND log_type = 'upload' AND log_action = 'upload')";
253			return $this->executeProjectsQuery($commonsProject, $sql, [
254			'actorId' => $user->getActorId($commonsProject),
255			])->fetchAll();
256			}
257
258			/**
259			* Get the IDs and timestamps of the latest edit and logged action by the given user.
260			* @param Project $project
261			* @param User $user
262			* @return string[] With keys 'rev_first', 'rev_latest', 'log_latest'.
263			*/
264			public function getFirstAndLatestActions(Project $project, User $user): array
265			{
266			$cacheKey = $this->getCacheKey(func_get_args(), 'ec_first_latest_actions');
267			if ($this->cache->hasItem($cacheKey)) {
268			return $this->cache->getItem($cacheKey)->get();
269			}
270
271			$loggingTable = $project->getTableName('logging', 'userindex');
272			if ($user->isIpRange()) {
273			$fromTable = $project->getTableName('ip_changes');
274			$idColumn = 'ipc_rev_id';
275			$timestampColumn = 'ipc_rev_timestamp';
276			$whereClause = "ipc_hex BETWEEN :startIp AND :endIp";
277			$params = [];
278			[$params['startIp'], $params['endIp']] = IPUtils::parseRange($user->getUsername());
279			$logQuery = '';
280			} else {
281			$fromTable = $project->getTableName('revision');
282			$idColumn = 'rev_id';
283			$timestampColumn = 'rev_timestamp';
284			$whereClause = 'rev_actor = :actorId';
285			$params = ['actorId' => $user->getActorId($project)];
286			$logQuery = "
287			SELECT 'log_latest' AS `key`, log_id AS `id`,
288			log_timestamp AS `timestamp`, log_type AS `type`
289			FROM $loggingTable
290			WHERE log_actor = :actorId
291			ORDER BY log_timestamp DESC LIMIT 1
292			) UNION (";
293			}
294
295			$sql = "(
296			$logQuery
297			SELECT 'rev_first' AS `key`, $idColumn AS `id`,
298			$timestampColumn AS `timestamp`, NULL as `type`
299			FROM $fromTable
300			WHERE $whereClause
301			ORDER BY $timestampColumn ASC LIMIT 1
302			) UNION (
303			SELECT 'rev_latest' AS `key`, $idColumn AS `id`,
304			$timestampColumn AS `timestamp`, NULL as `type`
305			FROM $fromTable
306			WHERE $whereClause
307			ORDER BY $timestampColumn DESC LIMIT 1
308			)";
309
310			$resultQuery = $this->executeProjectsQuery($project, $sql, $params);
311
312			$actions = [];
313			while ($result = $resultQuery->fetch()) {
314			$actions[$result['key']] = [
315			'id' => $result['id'],
316			'timestamp' => $result['timestamp'],
317			'type' => $result['type'],
318			];
319			}
320
321			return $this->setCache($cacheKey, $actions);
322			}
323
324			/**
325			* Get data for all blocks set on the given user.
326			* @param Project $project
327			* @param User $user
328			* @return array
329			*/
330			public function getBlocksReceived(Project $project, User $user): array
331			{
332			$loggingTable = $this->getTableName($project->getDatabaseName(), 'logging', 'logindex');
333			$sql = "SELECT log_action, log_timestamp, log_params FROM $loggingTable
334			WHERE log_type = 'block'
335			AND log_action IN ('block', 'reblock', 'unblock')
336			AND log_timestamp > 0
337			AND log_title = :username
338			AND log_namespace = 2
339			ORDER BY log_timestamp ASC";
340			$username = str_replace(' ', '_', $user->getUsername());
341
342			return $this->executeProjectsQuery($project, $sql, [
343			'username' => $username,
344			])->fetchAll();
345			}
346
347			/**
348			* Get the number of times the user was thanked.
349			* @param Project $project
350			* @param User $user
351			* @return int
352			*/
353			public function getThanksReceived(Project $project, User $user): int
354			{
355			$cacheKey = $this->getCacheKey(func_get_args(), 'ec_thanksreceived');
356			if ($this->cache->hasItem($cacheKey)) {
357			return $this->cache->getItem($cacheKey)->get();
358			}
359
360			$loggingTable = $project->getTableName('logging', 'logindex');
361			$sql = "SELECT COUNT(log_id)
362			FROM $loggingTable
363			WHERE log_type = 'thanks'
364			AND log_title = :username
365			AND log_namespace = 2";
366			$username = str_replace(' ', '_', $user->getUsername());
367
368			return $this->setCache($cacheKey, (int)$this->executeProjectsQuery($project, $sql, [
369			'username' => $username,
370			])->fetchColumn());
371			}
372
373			/**
374			* Get the given user's total edit counts per namespace on the given project.
375			* @param Project $project The project.
376			* @param User $user The user.
377			* @return array Array keys are namespace IDs, values are the edit counts.
378			*/
379			public function getNamespaceTotals(Project $project, User $user): array
380			{
381			// Cache?
382			$cacheKey = $this->getCacheKey(func_get_args(), 'ec_namespacetotals');
383			if ($this->cache->hasItem($cacheKey)) {
384			return $this->cache->getItem($cacheKey)->get();
385			}
386
387			// Query.
388			$revisionTable = $project->getTableName('revision');
389			$pageTable = $project->getTableName('page');
390			$ipcJoin = '';
391			$whereClause = 'r.rev_actor = :actorId';
392			$params = ['actorId' => $user->getActorId($project)];
393
394			if ($user->isIpRange()) {
395			$ipcTable = $project->getTableName('ip_changes');
396			$ipcJoin = "JOIN $ipcTable ON ipc_rev_id = rev_id";
397			[$params['startIp'], $params['endIp']] = IPUtils::parseRange($user->getUsername());
398			$whereClause = 'ipc_hex BETWEEN :startIp AND :endIp';
399			}
400
401			$sql = "SELECT page_namespace, COUNT(rev_id) AS total
402			FROM $pageTable p JOIN $revisionTable r ON (r.rev_page = p.page_id)
403			$ipcJoin
404			WHERE $whereClause
405			GROUP BY page_namespace";
406
407			$results = $this->executeProjectsQuery($project, $sql, $params)->fetchAll();
408
409			$namespaceTotals = array_combine(array_map(function ($e) {
410			return $e['page_namespace'];
411			}, $results), array_map(function ($e) {
412			return (int)$e['total'];
413			}, $results));
414
415			// Cache and return.
416			return $this->setCache($cacheKey, $namespaceTotals);
417			}
418
419			/**
420			* Get data for a bar chart of monthly edit totals per namespace.
421			* @param Project $project The project.
422			* @param User $user The user.
423			* @return string[] [
424			* [
425			* 'year' => <year>,
426			* 'month' => <month>,
427			* 'page_namespace' => <namespace>,
428			* 'count' => <count>,
429			* ],
430			* ...
431			* ]
432			*/
433			public function getMonthCounts(Project $project, User $user): array
434			{
435			$cacheKey = $this->getCacheKey(func_get_args(), 'ec_monthcounts');
436			if ($this->cache->hasItem($cacheKey)) {
437			return $this->cache->getItem($cacheKey)->get();
438			}
439
440			$revisionTable = $project->getTableName('revision');
441			$pageTable = $project->getTableName('page');
442			$ipcJoin = '';
443			$whereClause = 'rev_actor = :actorId';
444			$params = ['actorId' => $user->getActorId($project)];
445
446			if ($user->isIpRange()) {
447			$ipcTable = $project->getTableName('ip_changes');
448			$ipcJoin = "JOIN $ipcTable ON ipc_rev_id = rev_id";
449			[$params['startIp'], $params['endIp']] = IPUtils::parseRange($user->getUsername());
450			$whereClause = 'ipc_hex BETWEEN :startIp AND :endIp';
451			}
452
453			$sql = "
454			SELECT YEAR(rev_timestamp) AS `year`,
455			MONTH(rev_timestamp) AS `month`,
456			page_namespace,
457			COUNT(rev_id) AS `count`
458			FROM $revisionTable JOIN $pageTable ON (rev_page = page_id)
459			$ipcJoin
460			WHERE $whereClause
461			GROUP BY YEAR(rev_timestamp), MONTH(rev_timestamp), page_namespace";
462
463			$totals = $this->executeProjectsQuery($project, $sql, $params)->fetchAll();
464
465			// Cache and return.
466			return $this->setCache($cacheKey, $totals);
467			}
468
469			/**
470			* Get data for the timecard chart, with totals grouped by day and to the nearest two-hours.
471			* @param Project $project
472			* @param User $user
473			* @return string[]
474			*/
475			public function getTimeCard(Project $project, User $user): array
476			{
477			$cacheKey = $this->getCacheKey(func_get_args(), 'ec_timecard');
478			if ($this->cache->hasItem($cacheKey)) {
479			return $this->cache->getItem($cacheKey)->get();
480			}
481
482			$hourInterval = 1;
483
484			if ($user->isIpRange()) {
485			$column = 'ipc_rev_timestamp';
486			$table = $project->getTableName('ip_changes');
487			[$params['startIp'], $params['endIp']] = IPUtils::parseRange($user->getUsername());
488			$whereClause = 'ipc_hex BETWEEN :startIp AND :endIp';
489			} else {
490			$column = 'rev_timestamp';
491			$table = $project->getTableName('revision');
492			$whereClause = 'rev_actor = :actorId';
493			$params = ['actorId' => $user->getActorId($project)];
494			}
495
496			$xCalc = "ROUND(HOUR($column)/$hourInterval) * $hourInterval";
497
498			$sql = "
499			SELECT DAYOFWEEK($column) AS `day_of_week`,
500			$xCalc AS `hour`,
501			COUNT($column) AS `value`
502			FROM $table
503			WHERE $whereClause
504			GROUP BY DAYOFWEEK($column), $xCalc";
505
506			$totals = $this->executeProjectsQuery($project, $sql, $params)->fetchAll();
507
508			// Cache and return.
509			return $this->setCache($cacheKey, $totals);
510			}
511
512			/**
513			* Get various data about edit sizes of the past 5,000 edits.
514			* Will cache the result for 10 minutes.
515			* @param Project $project The project.
516			* @param User $user The user.
517			* @return string[] Values with for keys 'average_size',
518			* 'small_edits' and 'large_edits'
519			*/
520			public function getEditSizeData(Project $project, User $user): array
521			{
522			// Set up cache.
523			$cacheKey = $this->getCacheKey(func_get_args(), 'ec_editsizes');
524			if ($this->cache->hasItem($cacheKey)) {
525			return $this->cache->getItem($cacheKey)->get();
526			}
527
528			// Prepare the queries and execute them.
529			$revisionTable = $project->getTableName('revision');
530			$ipcJoin = '';
531			$whereClause = 'revs.rev_actor = :actorId';
532			$params = ['actorId' => $user->getActorId($project)];
533
534			if ($user->isIpRange()) {
535			$ipcTable = $project->getTableName('ip_changes');
536			$ipcJoin = "JOIN $ipcTable ON ipc_rev_id = revs.rev_id";
537			[$params['startIp'], $params['endIp']] = IPUtils::parseRange($user->getUsername());
538			$whereClause = 'ipc_hex BETWEEN :startIp AND :endIp';
539			}
540
541			$sql = "SELECT AVG(sizes.size) AS average_size,
542			COUNT(CASE WHEN sizes.size < 20 THEN 1 END) AS small_edits,
543			COUNT(CASE WHEN sizes.size > 1000 THEN 1 END) AS large_edits
544			FROM (
545			SELECT (CAST(revs.rev_len AS SIGNED) - IFNULL(parentrevs.rev_len, 0)) AS size
546			FROM $revisionTable AS revs
547			$ipcJoin
548			LEFT JOIN $revisionTable AS parentrevs ON (revs.rev_parent_id = parentrevs.rev_id)
549			WHERE $whereClause
550			ORDER BY revs.rev_timestamp DESC
551			LIMIT 5000
552			) sizes";
553			$results = $this->executeProjectsQuery($project, $sql, $params)->fetch();
554
555			// Cache and return.
556			return $this->setCache($cacheKey, $results);
557			}
558
559			/**
560			* Get the number of edits this user made using semi-automated tools.
561			* @param Project $project
562			* @param User $user
563			* @return int Result of query, see below.
564			*/
565			public function countAutomatedEdits(Project $project, User $user): int
566			{
567			$autoEditsRepo = new AutoEditsRepository();
568			$autoEditsRepo->setContainer($this->container);
569			return $autoEditsRepo->countAutomatedEdits($project, $user);
570			}
571			}
572

x-tools / xtools

Pull Request — main (#415)

EditCounterRepository::getThanksReceived() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like