EditCounterRepository::getMonthCounts() - Code Metrics - Inspection of "Centralize executing of queries, handling certain..." - x-tools/xtools - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( 9b6f1b...510c7f )

by MusikAnimal

created 2018-02-08 03:25 UTC

EditCounterRepository::getMonthCounts() B

↳ Parent: EditCounterRepository

Complexity

Conditions	2
Paths	2

Size

Total Lines	27
Code Lines	20

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	2
eloc	20
nc	2
nop	2
dl	0
loc	27
rs	8.8571
c	0
b	0
f	0

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

namespace Xtools;

use Mediawiki\Api\SimpleRequest;
use Xtools\AutoEditsRepository;

/**
 * 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 Repository
{
    /**
     * Get data about revisions, pages, etc.
     * @param Project $project The project.
     * @param User $user The user.
     * @return string[] With keys: 'deleted', 'live', 'total', 'first', 'last', '24h', '7d', '30d',
     * '365d', 'small', 'large', 'with_comments', and 'minor_edits', ...
     */
    public function getPairData(Project $project, User $user)
    {
        // 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 = $this->getTableName($project->getDatabaseName(), 'archive');
        $revisionTable = $this->getTableName($project->getDatabaseName(), 'revision');
        $sql = "
            -- Revision counts.
            (SELECT 'deleted' AS `key`, COUNT(ar_id) AS val FROM $archiveTable
                WHERE ar_user = :userId
            ) UNION (
            SELECT 'live' AS `key`, COUNT(rev_id) AS val FROM $revisionTable
                WHERE rev_user = :userId
            ) UNION (
            SELECT 'day' AS `key`, COUNT(rev_id) AS val FROM $revisionTable
                WHERE rev_user = :userId AND rev_timestamp >= DATE_SUB(NOW(), INTERVAL 1 DAY)
            ) UNION (
            SELECT 'week' AS `key`, COUNT(rev_id) AS val FROM $revisionTable
                WHERE rev_user = :userId AND rev_timestamp >= DATE_SUB(NOW(), INTERVAL 1 WEEK)
            ) UNION (
            SELECT 'month' AS `key`, COUNT(rev_id) AS val FROM $revisionTable
                WHERE rev_user = :userId AND rev_timestamp >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
            ) UNION (
            SELECT 'year' AS `key`, COUNT(rev_id) AS val FROM $revisionTable
                WHERE rev_user = :userId AND rev_timestamp >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
            ) UNION (
            SELECT 'with_comments' AS `key`, COUNT(rev_id) AS val FROM $revisionTable
                WHERE rev_user = :userId AND rev_comment != ''
            ) UNION (
            SELECT 'minor' AS `key`, COUNT(rev_id) AS val FROM $revisionTable
                WHERE rev_user = :userId AND rev_minor_edit = 1

            -- Dates.
            ) UNION (
            SELECT 'first' AS `key`, rev_timestamp AS `val` FROM $revisionTable
                WHERE rev_user = :userId ORDER BY rev_timestamp ASC LIMIT 1
            ) UNION (
            SELECT 'last' AS `key`, rev_timestamp AS `date` FROM $revisionTable
                WHERE rev_user = :userId ORDER BY rev_timestamp DESC LIMIT 1

            -- Page counts.
            ) UNION (
            SELECT 'edited-live' AS `key`, COUNT(DISTINCT rev_page) AS `val`
                FROM $revisionTable
                WHERE rev_user = :userId
            ) UNION (
            SELECT 'edited-deleted' AS `key`, COUNT(DISTINCT ar_page_id) AS `val`
                FROM $archiveTable
                WHERE ar_user = :userId
            ) UNION (
            SELECT 'created-live' AS `key`, COUNT(DISTINCT rev_page) AS `val`
                FROM $revisionTable
                WHERE rev_user = :userId AND rev_parent_id = 0
            ) UNION (
            SELECT 'created-deleted' AS `key`, COUNT(DISTINCT ar_page_id) AS `val`
                FROM $archiveTable
                WHERE ar_user = :userId AND ar_parent_id = 0
            )
        ";

        $resultQuery = $this->executeProjectsQuery($sql, ['userId' => $user->getId($project)]);
        $revisionCounts = [];
        while ($result = $resultQuery->fetch()) {
            $revisionCounts[$result['key']] = $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 integer[] Keys are "<log>-<action>" strings, values are counts.
     */
    public function getLogCounts(Project $project, User $user)
    {
        // Set up cache.
        $cacheKey = $this->getCacheKey(func_get_args(), 'ec_logcounts');
        if ($this->cache->hasItem($cacheKey)) {
            return $this->cache->getItem($cacheKey)->get();
        }
        $this->stopwatch->start($cacheKey, 'XTools');

        // 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_user = :userId
            GROUP BY log_type, log_action
        )";

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

        $logCounts = array_combine(
            array_map(function ($e) {
                return $e['source'];
            }, $results),
            array_map(function ($e) {
                return $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',
        ];
        foreach ($requiredCounts as $req) {
            if (!isset($logCounts[$req])) {
                $logCounts[$req] = 0;
            }
        }

        // Add Commons upload count, if applicable.
        $logCounts['files_uploaded_commons'] = 0;
        if ($this->isLabs()) {
            $commons = ProjectRepository::getProject('commonswiki', $this->container);
            $userId = $user->getId($commons);
            if ($userId) {
                $sql = "SELECT COUNT(log_id) FROM commonswiki_p.logging_userindex
                    WHERE log_type = 'upload' AND log_action = 'upload' AND log_user = :userId";
                $resultQuery = $this->executeProjectsQuery($sql, ['userId' => $userId]);
                $logCounts['files_uploaded_commons'] = $resultQuery->fetchColumn();
            }
        }

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

    /**
     * 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)
    {
        $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($sql, [
            'username' => $username
        ])->fetchAll();
    }

    /**
     * Get user rights changes of the given user.
     * @param Project $project
     * @param User $user
     * @return array
     */
    public function getRightsChanges(Project $project, User $user)
    {
        $loggingTable = $this->getTableName($project->getDatabaseName(), 'logging', 'logindex');
        $userTable = $this->getTableName($project->getDatabaseName(), 'user');
        $sql = "SELECT log_id, log_timestamp, log_comment, log_params, log_action,
                    IF(log_user_text != '', log_user_text, (
                        SELECT user_name
                        FROM $userTable
                        WHERE user_id = log_user
                    )) AS log_user_text
                FROM $loggingTable
                WHERE log_type = 'rights'
                AND log_namespace = 2
                AND log_title = :username
                ORDER BY log_timestamp DESC";
        $username = str_replace(' ', '_', $user->getUsername());

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

    /**
     * Get a user's total edit count on all projects.
     * @see EditCounterRepository::globalEditCountsFromCentralAuth()
     * @see EditCounterRepository::globalEditCountsFromDatabases()
     * @param User $user The user.
     * @param Project $project The project to start from.
     * @return mixed[] Elements are arrays with 'project' (Project), and 'total' (int).
     */
    public function globalEditCounts(User $user, Project $project)
    {
        // Get the edit counts from CentralAuth or database.
        $editCounts = $this->globalEditCountsFromCentralAuth($user, $project);
        if ($editCounts === false) {

            $editCounts = $this->globalEditCountsFromDatabases($user, $project);
        }

        // Pre-populate all projects' metadata, to prevent each project call from fetching it.
        $project->getRepository()->getAll();

        // Compile the output.
        $out = [];
        foreach ($editCounts as $editCount) {
            $out[] = [
                'project' => ProjectRepository::getProject($editCount['dbName'], $this->container),
                'total' => $editCount['total'],
            ];
        }
        return $out;
    }

    /**
     * Get a user's total edit count on one or more project.
     * Requires the CentralAuth extension to be installed on the project.
     *
     * @param User $user The user.
     * @param Project $project The project to start from.
     * @return mixed[] Elements are arrays with 'dbName' (string), and 'total' (int).
     */
    protected function globalEditCountsFromCentralAuth(User $user, Project $project)
    {
        // Set up cache and stopwatch.
        $cacheKey = $this->getCacheKey(func_get_args(), 'ec_globaleditcounts');
        if ($this->cache->hasItem($cacheKey)) {
            return $this->cache->getItem($cacheKey)->get();
        }
        $this->stopwatch->start($cacheKey, 'XTools');

        $this->log->debug(__METHOD__." Getting global edit counts from for ".$user->getUsername());

        // Load all projects, so it doesn't have to request metadata about each one as it goes.
        $project->getRepository()->getAll();

        $api = $this->getMediawikiApi($project);
        $params = [
            'meta' => 'globaluserinfo',
            'guiprop' => 'editcount|merged',
            'guiuser' => $user->getUsername(),
        ];
        $query = new SimpleRequest('query', $params);
        $result = $api->getRequest($query);
        if (!isset($result['query']['globaluserinfo']['merged'])) {
            return [];
        }
        $out = [];
        foreach ($result['query']['globaluserinfo']['merged'] as $result) {
            $out[] = [
                'dbName' => $result['wiki'],
                'total' => $result['editcount'],
            ];
        }

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

    /**
     * Get total edit counts from all projects for this user.
     * @see EditCounterRepository::globalEditCountsFromCentralAuth()
     * @param User $user The user.
     * @param Project $project The project to start from.
     * @return mixed[] Elements are arrays with 'dbName' (string), and 'total' (int).
     */
    protected function globalEditCountsFromDatabases(User $user, Project $project)
    {
        $this->log->debug(__METHOD__." Getting global edit counts for ".$user->getUsername());
        $stopwatchName = 'globalRevisionCounts.'.$user->getUsername();
        $allProjects = $project->getRepository()->getAll();
        $topEditCounts = [];
        foreach ($allProjects as $projectMeta) {
            $revisionTableName = $this->getTableName($projectMeta['dbName'], 'revision');
            $sql = "SELECT COUNT(rev_id) FROM $revisionTableName WHERE rev_user_text = :username";

            $resultQuery = $this->executeProjectsQuery($sql, [
                'username' => $user->getUsername()
            ]);
            $total = (int)$resultQuery->fetchColumn();
            $topEditCounts[] = [
                'dbName' => $projectMeta['dbName'],
                'total' => $total,
            ];
            $this->stopwatch->lap($stopwatchName);
        }
        return $topEditCounts;
    }

    /**
     * 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 integer[] Array keys are namespace IDs, values are the edit counts.
     */
    public function getNamespaceTotals(Project $project, User $user)
    {
        // Cache?
        $cacheKey = $this->getCacheKey(func_get_args(), 'ec_namespacetotals');
        $this->stopwatch->start($cacheKey, 'XTools');
        if ($this->cache->hasItem($cacheKey)) {
            return $this->cache->getItem($cacheKey)->get();
        }

        // Query.
        $revisionTable = $this->getTableName($project->getDatabaseName(), 'revision');
        $pageTable = $this->getTableName($project->getDatabaseName(), 'page');
        $sql = "SELECT page_namespace, COUNT(rev_id) AS total
            FROM $pageTable p JOIN $revisionTable r ON (r.rev_page = p.page_id)
            WHERE r.rev_user = :id
            GROUP BY page_namespace";

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

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

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

    /**
     * Get revisions by this user.
     * @param Project[] $projects The projects.
     * @param User $user The user.
     * @param int $lim The maximum number of revisions to fetch from each project.
     * @return array|mixed
     */
    public function getRevisions($projects, User $user, $lim = 40)
    {
        // Check cache.
        $cacheKey = $this->getCacheKey('ec_globalcontribs.'.$user->getCacheKey().'.'.$lim);
        $this->stopwatch->start($cacheKey, 'XTools');
        if ($this->cache->hasItem($cacheKey)) {
            return $this->cache->getItem($cacheKey)->get();
        }

        // Assemble queries.
        $queries = [];
        foreach ($projects as $project) {
            $revisionTable = $project->getTableName('revision');
            $pageTable = $project->getTableName('page');
            $sql = "SELECT
                    '".$project->getDatabaseName()."' AS project_name,
                    revs.rev_id AS id,
                    revs.rev_timestamp AS timestamp,
                    UNIX_TIMESTAMP(revs.rev_timestamp) AS unix_timestamp,
                    revs.rev_minor_edit AS minor,
                    revs.rev_deleted AS deleted,
                    revs.rev_len AS length,
                    (CAST(revs.rev_len AS SIGNED) - IFNULL(parentrevs.rev_len, 0)) AS length_change,
                    revs.rev_parent_id AS parent_id,
                    revs.rev_comment AS comment,
                    revs.rev_user_text AS username,
                    page.page_title,
                    page.page_namespace
                FROM $revisionTable AS revs
                    JOIN $pageTable AS page ON (rev_page = page_id)
                    LEFT JOIN $revisionTable AS parentrevs ON (revs.rev_parent_id = parentrevs.rev_id)
                WHERE revs.rev_user_text = :username
                ORDER BY revs.rev_timestamp DESC";
            if (is_numeric($lim)) {
                $sql .= " LIMIT $lim";
            }
            $queries[] = $sql;
        }
        $sql = "(\n" . join("\n) UNION (\n", $queries) . ")\n";

        $revisions = $this->executeProjectsQuery($sql, [
            'username' => $user->getUsername(),
        ])->fetchAll();

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

    /**
     * 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)
    {
        $cacheKey = $this->getCacheKey(func_get_args(), 'ec_monthcounts');
        $this->stopwatch->start($cacheKey, 'XTools');
        if ($this->cache->hasItem($cacheKey)) {
            return $this->cache->getItem($cacheKey)->get();
        }

        $revisionTable = $project->getTableName('revision');
        $pageTable = $project->getTableName('page');
        $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)"
            . " WHERE rev_user_text = :username"
            . " GROUP BY YEAR(rev_timestamp), MONTH(rev_timestamp), page_namespace";

        $totals = $this->executeProjectsQuery($sql, [
            'username' => $user->getUsername(),
        ])->fetchAll();

        // Cache and return.
        $this->stopwatch->stop($cacheKey);
        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)
    {
        $cacheKey = $this->getCacheKey(func_get_args(), 'ec_timecard');
        $this->stopwatch->start($cacheKey, 'XTools');
        if ($this->cache->hasItem($cacheKey)) {
            return $this->cache->getItem($cacheKey)->get();
        }

        $hourInterval = 2;
        $xCalc = "ROUND(HOUR(rev_timestamp)/$hourInterval) * $hourInterval";
        $revisionTable = $this->getTableName($project->getDatabaseName(), 'revision');
        $sql = "SELECT "
            . "     DAYOFWEEK(rev_timestamp) AS `y`, "
            . "     $xCalc AS `x`, "
            . "     COUNT(rev_id) AS `value` "
            . " FROM $revisionTable"
            . " WHERE rev_user_text = :username"
            . " GROUP BY DAYOFWEEK(rev_timestamp), $xCalc ";

        $totals = $this->executeProjectsQuery($sql, [
            'username' => $user->getUsername(),
        ])->fetchAll();

        // Cache and return.
        $this->stopwatch->stop($cacheKey);
        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)
    {
        // Set up cache.
        $cacheKey = $this->getCacheKey(func_get_args(), 'ec_editsizes');
        $this->stopwatch->start($cacheKey, 'XTools');
        if ($this->cache->hasItem($cacheKey)) {
            return $this->cache->getItem($cacheKey)->get();
        }

        // Prepare the queries and execute them.
        $revisionTable = $this->getTableName($project->getDatabaseName(), 'revision');
        $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
                    LEFT JOIN $revisionTable AS parentrevs ON (revs.rev_parent_id = parentrevs.rev_id)
                    WHERE revs.rev_user = :userId
                    ORDER BY revs.rev_timestamp DESC
                    LIMIT 5000
                ) sizes";

        $results = $this->executeProjectsQuery($sql, [
            'userId' => $user->getId($project),
        ])->fetch();

        // Cache and return.
        $this->stopwatch->stop($cacheKey);
        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)
    {
        $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			namespace Xtools;
7
8			use Mediawiki\Api\SimpleRequest;
9			use Xtools\AutoEditsRepository;
10
11			/**
12			* An EditCounterRepository is responsible for retrieving edit count information from the
13			* databases and API. It doesn't do any post-processing of that information.
14			* @codeCoverageIgnore
15			*/
16			class EditCounterRepository extends Repository
17			{
18			/**
19			* Get data about revisions, pages, etc.
20			* @param Project $project The project.
21			* @param User $user The user.
22			* @return string[] With keys: 'deleted', 'live', 'total', 'first', 'last', '24h', '7d', '30d',
23			* '365d', 'small', 'large', 'with_comments', and 'minor_edits', ...
24			*/
25			public function getPairData(Project $project, User $user)
26			{
27			// Set up cache.
28			$cacheKey = $this->getCacheKey(func_get_args(), 'ec_pairdata');
29			if ($this->cache->hasItem($cacheKey)) {
30			return $this->cache->getItem($cacheKey)->get();
31			}
32
33			// Prepare the queries and execute them.
34			$archiveTable = $this->getTableName($project->getDatabaseName(), 'archive');
35			$revisionTable = $this->getTableName($project->getDatabaseName(), 'revision');
36			$sql = "
37			-- Revision counts.
38			(SELECT 'deleted' AS `key`, COUNT(ar_id) AS val FROM $archiveTable
39			WHERE ar_user = :userId
40			) UNION (
41			SELECT 'live' AS `key`, COUNT(rev_id) AS val FROM $revisionTable
42			WHERE rev_user = :userId
43			) UNION (
44			SELECT 'day' AS `key`, COUNT(rev_id) AS val FROM $revisionTable
45			WHERE rev_user = :userId AND rev_timestamp >= DATE_SUB(NOW(), INTERVAL 1 DAY)
46			) UNION (
47			SELECT 'week' AS `key`, COUNT(rev_id) AS val FROM $revisionTable
48			WHERE rev_user = :userId AND rev_timestamp >= DATE_SUB(NOW(), INTERVAL 1 WEEK)
49			) UNION (
50			SELECT 'month' AS `key`, COUNT(rev_id) AS val FROM $revisionTable
51			WHERE rev_user = :userId AND rev_timestamp >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
52			) UNION (
53			SELECT 'year' AS `key`, COUNT(rev_id) AS val FROM $revisionTable
54			WHERE rev_user = :userId AND rev_timestamp >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
55			) UNION (
56			SELECT 'with_comments' AS `key`, COUNT(rev_id) AS val FROM $revisionTable
57			WHERE rev_user = :userId AND rev_comment != ''
58			) UNION (
59			SELECT 'minor' AS `key`, COUNT(rev_id) AS val FROM $revisionTable
60			WHERE rev_user = :userId AND rev_minor_edit = 1
61
62			-- Dates.
63			) UNION (
64			SELECT 'first' AS `key`, rev_timestamp AS `val` FROM $revisionTable
65			WHERE rev_user = :userId ORDER BY rev_timestamp ASC LIMIT 1
66			) UNION (
67			SELECT 'last' AS `key`, rev_timestamp AS `date` FROM $revisionTable
68			WHERE rev_user = :userId ORDER BY rev_timestamp DESC LIMIT 1
69
70			-- Page counts.
71			) UNION (
72			SELECT 'edited-live' AS `key`, COUNT(DISTINCT rev_page) AS `val`
73			FROM $revisionTable
74			WHERE rev_user = :userId
75			) UNION (
76			SELECT 'edited-deleted' AS `key`, COUNT(DISTINCT ar_page_id) AS `val`
77			FROM $archiveTable
78			WHERE ar_user = :userId
79			) UNION (
80			SELECT 'created-live' AS `key`, COUNT(DISTINCT rev_page) AS `val`
81			FROM $revisionTable
82			WHERE rev_user = :userId AND rev_parent_id = 0
83			) UNION (
84			SELECT 'created-deleted' AS `key`, COUNT(DISTINCT ar_page_id) AS `val`
85			FROM $archiveTable
86			WHERE ar_user = :userId AND ar_parent_id = 0
87			)
88			";
89
90			$resultQuery = $this->executeProjectsQuery($sql, ['userId' => $user->getId($project)]);
91			$revisionCounts = [];
92			while ($result = $resultQuery->fetch()) {
93			$revisionCounts[$result['key']] = $result['val'];
94			}
95
96			// Cache and return.
97			return $this->setCache($cacheKey, $revisionCounts);
98			}
99
100			/**
101			* Get log totals for a user.
102			* @param Project $project The project.
103			* @param User $user The user.
104			* @return integer[] Keys are "<log>-<action>" strings, values are counts.
105			*/
106			public function getLogCounts(Project $project, User $user)
107			{
108			// Set up cache.
109			$cacheKey = $this->getCacheKey(func_get_args(), 'ec_logcounts');
110			if ($this->cache->hasItem($cacheKey)) {
111			return $this->cache->getItem($cacheKey)->get();
112			}
113			$this->stopwatch->start($cacheKey, 'XTools');
114
115			// Query.
116			$loggingTable = $this->getTableName($project->getDatabaseName(), 'logging');
117			$sql = "
118			(SELECT CONCAT(log_type, '-', log_action) AS source, COUNT(log_id) AS value
119			FROM $loggingTable
120			WHERE log_user = :userId
121			GROUP BY log_type, log_action
122			)";
123
124			$results = $this->executeProjectsQuery($sql, [
125			'userId' => $user->getId($project)
126			])->fetchAll();
127
128			$logCounts = array_combine(
129			array_map(function ($e) {
130			return $e['source'];
131			}, $results),
132			array_map(function ($e) {
133			return $e['value'];
134			}, $results)
135			);
136
137			// Make sure there is some value for each of the wanted counts.
138			$requiredCounts = [
139			'thanks-thank',
140			'review-approve',
141			'newusers-create2',
142			'newusers-byemail',
143			'patrol-patrol',
144			'block-block',
145			'block-reblock',
146			'block-unblock',
147			'protect-protect',
148			'protect-modify',
149			'protect-unprotect',
150			'rights-rights',
151			'move-move',
152			'delete-delete',
153			'delete-revision',
154			'delete-restore',
155			'import-import',
156			'import-interwiki',
157			'import-upload',
158			'upload-upload',
159			'upload-overwrite',
160			];
161			foreach ($requiredCounts as $req) {
162			if (!isset($logCounts[$req])) {
163			$logCounts[$req] = 0;
164			}
165			}
166
167			// Add Commons upload count, if applicable.
168			$logCounts['files_uploaded_commons'] = 0;
169			if ($this->isLabs()) {
170			$commons = ProjectRepository::getProject('commonswiki', $this->container);
171			$userId = $user->getId($commons);
172			if ($userId) {
173			$sql = "SELECT COUNT(log_id) FROM commonswiki_p.logging_userindex
174			WHERE log_type = 'upload' AND log_action = 'upload' AND log_user = :userId";
175			$resultQuery = $this->executeProjectsQuery($sql, ['userId' => $userId]);
176			$logCounts['files_uploaded_commons'] = $resultQuery->fetchColumn();
177			}
178			}
179
180			// Cache and return.
181			$this->stopwatch->stop($cacheKey);
182			return $this->setCache($cacheKey, $logCounts);
183			}
184
185			/**
186			* Get data for all blocks set on the given user.
187			* @param Project $project
188			* @param User $user
189			* @return array
190			*/
191			public function getBlocksReceived(Project $project, User $user)
192			{
193			$loggingTable = $this->getTableName($project->getDatabaseName(), 'logging', 'logindex');
194			$sql = "SELECT log_action, log_timestamp, log_params FROM $loggingTable
195			WHERE log_type = 'block'
196			AND log_action IN ('block', 'reblock', 'unblock')
197			AND log_timestamp > 0
198			AND log_title = :username
199			AND log_namespace = 2
200			ORDER BY log_timestamp ASC";
201			$username = str_replace(' ', '_', $user->getUsername());
202
203			return $this->executeProjectsQuery($sql, [
204			'username' => $username
205			])->fetchAll();
206			}
207
208			/**
209			* Get user rights changes of the given user.
210			* @param Project $project
211			* @param User $user
212			* @return array
213			*/
214			public function getRightsChanges(Project $project, User $user)
215			{
216			$loggingTable = $this->getTableName($project->getDatabaseName(), 'logging', 'logindex');
217			$userTable = $this->getTableName($project->getDatabaseName(), 'user');
218			$sql = "SELECT log_id, log_timestamp, log_comment, log_params, log_action,
219			IF(log_user_text != '', log_user_text, (
220			SELECT user_name
221			FROM $userTable
222			WHERE user_id = log_user
223			)) AS log_user_text
224			FROM $loggingTable
225			WHERE log_type = 'rights'
226			AND log_namespace = 2
227			AND log_title = :username
228			ORDER BY log_timestamp DESC";
229			$username = str_replace(' ', '_', $user->getUsername());
230
231			return $this->executeProjectsQuery($sql, [
232			'username' => $username
233			])->fetchAll();
234			}
235
236			/**
237			* Get a user's total edit count on all projects.
238			* @see EditCounterRepository::globalEditCountsFromCentralAuth()
239			* @see EditCounterRepository::globalEditCountsFromDatabases()
240			* @param User $user The user.
241			* @param Project $project The project to start from.
242			* @return mixed[] Elements are arrays with 'project' (Project), and 'total' (int).
243			*/
244			public function globalEditCounts(User $user, Project $project)
245			{
246			// Get the edit counts from CentralAuth or database.
247			$editCounts = $this->globalEditCountsFromCentralAuth($user, $project);
248			if ($editCounts === false) {
			0 ignored issues – show introduced 2018-01-14 22:45 UTC by Report Bug Copy Issue Report The condition `$editCounts === false` can never be `true`. Loading history...
249			$editCounts = $this->globalEditCountsFromDatabases($user, $project);
250			}
251
252			// Pre-populate all projects' metadata, to prevent each project call from fetching it.
253			$project->getRepository()->getAll();
254
255			// Compile the output.
256			$out = [];
257			foreach ($editCounts as $editCount) {
258			$out[] = [
259			'project' => ProjectRepository::getProject($editCount['dbName'], $this->container),
260			'total' => $editCount['total'],
261			];
262			}
263			return $out;
264			}
265
266			/**
267			* Get a user's total edit count on one or more project.
268			* Requires the CentralAuth extension to be installed on the project.
269			*
270			* @param User $user The user.
271			* @param Project $project The project to start from.
272			* @return mixed[] Elements are arrays with 'dbName' (string), and 'total' (int).
273			*/
274			protected function globalEditCountsFromCentralAuth(User $user, Project $project)
275			{
276			// Set up cache and stopwatch.
277			$cacheKey = $this->getCacheKey(func_get_args(), 'ec_globaleditcounts');
278			if ($this->cache->hasItem($cacheKey)) {
279			return $this->cache->getItem($cacheKey)->get();
280			}
281			$this->stopwatch->start($cacheKey, 'XTools');
282
283			$this->log->debug(__METHOD__." Getting global edit counts from for ".$user->getUsername());
284
285			// Load all projects, so it doesn't have to request metadata about each one as it goes.
286			$project->getRepository()->getAll();
287
288			$api = $this->getMediawikiApi($project);
289			$params = [
290			'meta' => 'globaluserinfo',
291			'guiprop' => 'editcount\|merged',
292			'guiuser' => $user->getUsername(),
293			];
294			$query = new SimpleRequest('query', $params);
295			$result = $api->getRequest($query);
296			if (!isset($result['query']['globaluserinfo']['merged'])) {
297			return [];
298			}
299			$out = [];
300			foreach ($result['query']['globaluserinfo']['merged'] as $result) {
301			$out[] = [
302			'dbName' => $result['wiki'],
303			'total' => $result['editcount'],
304			];
305			}
306
307			// Cache and return.
308			$this->stopwatch->stop($cacheKey);
309			return $this->setCache($cacheKey, $out);
310			}
311
312			/**
313			* Get total edit counts from all projects for this user.
314			* @see EditCounterRepository::globalEditCountsFromCentralAuth()
315			* @param User $user The user.
316			* @param Project $project The project to start from.
317			* @return mixed[] Elements are arrays with 'dbName' (string), and 'total' (int).
318			*/
319			protected function globalEditCountsFromDatabases(User $user, Project $project)
320			{
321			$this->log->debug(__METHOD__." Getting global edit counts for ".$user->getUsername());
322			$stopwatchName = 'globalRevisionCounts.'.$user->getUsername();
323			$allProjects = $project->getRepository()->getAll();
324			$topEditCounts = [];
325			foreach ($allProjects as $projectMeta) {
326			$revisionTableName = $this->getTableName($projectMeta['dbName'], 'revision');
327			$sql = "SELECT COUNT(rev_id) FROM $revisionTableName WHERE rev_user_text = :username";
328
329			$resultQuery = $this->executeProjectsQuery($sql, [
330			'username' => $user->getUsername()
331			]);
332			$total = (int)$resultQuery->fetchColumn();
333			$topEditCounts[] = [
334			'dbName' => $projectMeta['dbName'],
335			'total' => $total,
336			];
337			$this->stopwatch->lap($stopwatchName);
338			}
339			return $topEditCounts;
340			}
341
342			/**
343			* Get the given user's total edit counts per namespace on the given project.
344			* @param Project $project The project.
345			* @param User $user The user.
346			* @return integer[] Array keys are namespace IDs, values are the edit counts.
347			*/
348			public function getNamespaceTotals(Project $project, User $user)
349			{
350			// Cache?
351			$cacheKey = $this->getCacheKey(func_get_args(), 'ec_namespacetotals');
352			$this->stopwatch->start($cacheKey, 'XTools');
353			if ($this->cache->hasItem($cacheKey)) {
354			return $this->cache->getItem($cacheKey)->get();
355			}
356
357			// Query.
358			$revisionTable = $this->getTableName($project->getDatabaseName(), 'revision');
359			$pageTable = $this->getTableName($project->getDatabaseName(), 'page');
360			$sql = "SELECT page_namespace, COUNT(rev_id) AS total
361			FROM $pageTable p JOIN $revisionTable r ON (r.rev_page = p.page_id)
362			WHERE r.rev_user = :id
363			GROUP BY page_namespace";
364
365			$results = $this->executeProjectsQuery($sql, [
366			'id' => $user->getId($project),
367			])->fetchAll();
368
369			$namespaceTotals = array_combine(array_map(function ($e) {
370			return $e['page_namespace'];
371			}, $results), array_map(function ($e) {
372			return $e['total'];
373			}, $results));
374
375			// Cache and return.
376			$this->stopwatch->stop($cacheKey);
377			return $this->setCache($cacheKey, $namespaceTotals);
378			}
379
380			/**
381			* Get revisions by this user.
382			* @param Project[] $projects The projects.
383			* @param User $user The user.
384			* @param int $lim The maximum number of revisions to fetch from each project.
385			* @return array\|mixed
386			*/
387			public function getRevisions($projects, User $user, $lim = 40)
388			{
389			// Check cache.
390			$cacheKey = $this->getCacheKey('ec_globalcontribs.'.$user->getCacheKey().'.'.$lim);
391			$this->stopwatch->start($cacheKey, 'XTools');
392			if ($this->cache->hasItem($cacheKey)) {
393			return $this->cache->getItem($cacheKey)->get();
394			}
395
396			// Assemble queries.
397			$queries = [];
398			foreach ($projects as $project) {
399			$revisionTable = $project->getTableName('revision');
400			$pageTable = $project->getTableName('page');
401			$sql = "SELECT
402			'".$project->getDatabaseName()."' AS project_name,
403			revs.rev_id AS id,
404			revs.rev_timestamp AS timestamp,
405			UNIX_TIMESTAMP(revs.rev_timestamp) AS unix_timestamp,
406			revs.rev_minor_edit AS minor,
407			revs.rev_deleted AS deleted,
408			revs.rev_len AS length,
409			(CAST(revs.rev_len AS SIGNED) - IFNULL(parentrevs.rev_len, 0)) AS length_change,
410			revs.rev_parent_id AS parent_id,
411			revs.rev_comment AS comment,
412			revs.rev_user_text AS username,
413			page.page_title,
414			page.page_namespace
415			FROM $revisionTable AS revs
416			JOIN $pageTable AS page ON (rev_page = page_id)
417			LEFT JOIN $revisionTable AS parentrevs ON (revs.rev_parent_id = parentrevs.rev_id)
418			WHERE revs.rev_user_text = :username
419			ORDER BY revs.rev_timestamp DESC";
420			if (is_numeric($lim)) {
421			$sql .= " LIMIT $lim";
422			}
423			$queries[] = $sql;
424			}
425			$sql = "(\n" . join("\n) UNION (\n", $queries) . ")\n";
426
427			$revisions = $this->executeProjectsQuery($sql, [
428			'username' => $user->getUsername(),
429			])->fetchAll();
430
431			// Cache and return.
432			$this->stopwatch->stop($cacheKey);
433			return $this->setCache($cacheKey, $revisions);
434			}
435
436			/**
437			* Get data for a bar chart of monthly edit totals per namespace.
438			* @param Project $project The project.
439			* @param User $user The user.
440			* @return string[] [
441			* [
442			* 'year' => <year>,
443			* 'month' => <month>,
444			* 'page_namespace' => <namespace>,
445			* 'count' => <count>,
446			* ],
447			* ...
448			* ]
449			*/
450			public function getMonthCounts(Project $project, User $user)
451			{
452			$cacheKey = $this->getCacheKey(func_get_args(), 'ec_monthcounts');
453			$this->stopwatch->start($cacheKey, 'XTools');
454			if ($this->cache->hasItem($cacheKey)) {
455			return $this->cache->getItem($cacheKey)->get();
456			}
457
458			$revisionTable = $project->getTableName('revision');
459			$pageTable = $project->getTableName('page');
460			$sql =
461			"SELECT "
462			. " YEAR(rev_timestamp) AS `year`,"
463			. " MONTH(rev_timestamp) AS `month`,"
464			. " page_namespace,"
465			. " COUNT(rev_id) AS `count` "
466			. " FROM $revisionTable JOIN $pageTable ON (rev_page = page_id)"
467			. " WHERE rev_user_text = :username"
468			. " GROUP BY YEAR(rev_timestamp), MONTH(rev_timestamp), page_namespace";
469
470			$totals = $this->executeProjectsQuery($sql, [
471			'username' => $user->getUsername(),
472			])->fetchAll();
473
474			// Cache and return.
475			$this->stopwatch->stop($cacheKey);
476			return $this->setCache($cacheKey, $totals);
477			}
478
479			/**
480			* Get data for the timecard chart, with totals grouped by day and to the nearest two-hours.
481			* @param Project $project
482			* @param User $user
483			* @return string[]
484			*/
485			public function getTimeCard(Project $project, User $user)
486			{
487			$cacheKey = $this->getCacheKey(func_get_args(), 'ec_timecard');
488			$this->stopwatch->start($cacheKey, 'XTools');
489			if ($this->cache->hasItem($cacheKey)) {
490			return $this->cache->getItem($cacheKey)->get();
491			}
492
493			$hourInterval = 2;
494			$xCalc = "ROUND(HOUR(rev_timestamp)/$hourInterval) * $hourInterval";
495			$revisionTable = $this->getTableName($project->getDatabaseName(), 'revision');
496			$sql = "SELECT "
497			. " DAYOFWEEK(rev_timestamp) AS `y`, "
498			. " $xCalc AS `x`, "
499			. " COUNT(rev_id) AS `value` "
500			. " FROM $revisionTable"
501			. " WHERE rev_user_text = :username"
502			. " GROUP BY DAYOFWEEK(rev_timestamp), $xCalc ";
503
504			$totals = $this->executeProjectsQuery($sql, [
505			'username' => $user->getUsername(),
506			])->fetchAll();
507
508			// Cache and return.
509			$this->stopwatch->stop($cacheKey);
510			return $this->setCache($cacheKey, $totals);
511			}
512
513			/**
514			* Get various data about edit sizes of the past 5,000 edits.
515			* Will cache the result for 10 minutes.
516			* @param Project $project The project.
517			* @param User $user The user.
518			* @return string[] Values with for keys 'average_size',
519			* 'small_edits' and 'large_edits'
520			*/
521			public function getEditSizeData(Project $project, User $user)
522			{
523			// Set up cache.
524			$cacheKey = $this->getCacheKey(func_get_args(), 'ec_editsizes');
525			$this->stopwatch->start($cacheKey, 'XTools');
526			if ($this->cache->hasItem($cacheKey)) {
527			return $this->cache->getItem($cacheKey)->get();
528			}
529
530			// Prepare the queries and execute them.
531			$revisionTable = $this->getTableName($project->getDatabaseName(), 'revision');
532			$sql = "SELECT AVG(sizes.size) AS average_size,
533			COUNT(CASE WHEN sizes.size < 20 THEN 1 END) AS small_edits,
534			COUNT(CASE WHEN sizes.size > 1000 THEN 1 END) AS large_edits
535			FROM (
536			SELECT (CAST(revs.rev_len AS SIGNED) - IFNULL(parentrevs.rev_len, 0)) AS size
537			FROM $revisionTable AS revs
538			LEFT JOIN $revisionTable AS parentrevs ON (revs.rev_parent_id = parentrevs.rev_id)
539			WHERE revs.rev_user = :userId
540			ORDER BY revs.rev_timestamp DESC
541			LIMIT 5000
542			) sizes";
543
544			$results = $this->executeProjectsQuery($sql, [
545			'userId' => $user->getId($project),
546			])->fetch();
547
548			// Cache and return.
549			$this->stopwatch->stop($cacheKey);
550			return $this->setCache($cacheKey, $results);
551			}
552
553			/**
554			* Get the number of edits this user made using semi-automated tools.
555			* @param Project $project
556			* @param User $user
557			* @return int Result of query, see below.
558			*/
559			public function countAutomatedEdits(Project $project, User $user)
560			{
561			$autoEditsRepo = new AutoEditsRepository();
562			$autoEditsRepo->setContainer($this->container);
563			return $autoEditsRepo->countAutomatedEdits($project, $user);
564			}
565			}
566

x-tools / xtools

Push — master ( 9b6f1b...510c7f )

EditCounterRepository::getMonthCounts() B

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like