EditCounterRepository::getFileCountsCommons() - Code Metrics - Inspection of "Add support for new replicas design" - x-tools/xtools - Measure and Improve Code Quality continuously with Scrutinizer

Test Failed

Pull Request — master (#377)

by MusikAnimal

created 2021-02-19 21:46 UTC

EditCounterRepository::getFileCountsCommons() A

↳ Parent: EditCounterRepository

Complexity

Conditions	1
Paths	1

Size

Total Lines	15
Code Lines	8

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	1
eloc	8
nc	1
nop	1
dl	0
loc	15
rs	10
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;

/**
 * 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');

        $sql = "
            -- Revision counts.
            (SELECT 'deleted' AS `key`, COUNT(ar_id) AS val FROM $archiveTable
                WHERE ar_actor = :actorId
            ) UNION (
            SELECT 'live' AS `key`, COUNT(rev_id) AS val FROM $revisionTable
                WHERE rev_actor = :actorId
            ) UNION (
            SELECT 'day' AS `key`, COUNT(rev_id) AS val FROM $revisionTable
                WHERE rev_actor = :actorId AND rev_timestamp >= DATE_SUB(NOW(), INTERVAL 1 DAY)
            ) UNION (
            SELECT 'week' AS `key`, COUNT(rev_id) AS val FROM $revisionTable
                WHERE rev_actor = :actorId AND rev_timestamp >= DATE_SUB(NOW(), INTERVAL 1 WEEK)
            ) UNION (
            SELECT 'month' AS `key`, COUNT(rev_id) AS val FROM $revisionTable
                WHERE rev_actor = :actorId AND rev_timestamp >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
            ) UNION (
            SELECT 'year' AS `key`, COUNT(rev_id) AS val FROM $revisionTable
                WHERE rev_actor = :actorId AND rev_timestamp >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
            ) UNION (
            SELECT 'minor' AS `key`, COUNT(rev_id) AS val FROM $revisionTable
                WHERE rev_actor = :actorId AND rev_minor_edit = 1

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

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

        $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 integer[] 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');
        $revisionTable = $project->getTableName('revision');

        $sql = "(
                    SELECT 'rev_first' AS `key`, rev_id AS `id`,
                        rev_timestamp AS `timestamp`, NULL as `type`
                    FROM $revisionTable
                    WHERE rev_actor = :actorId
                    LIMIT 1
                ) UNION (
                    SELECT 'rev_latest' AS `key`, rev_id AS `id`,
                        rev_timestamp AS `timestamp`, NULL as `type`
                    FROM $revisionTable
                    WHERE rev_actor = :actorId
                    ORDER BY rev_timestamp DESC LIMIT 1
                ) UNION (
                    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
                )";

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

        $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 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 = $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_actor = :actorId
            GROUP BY page_namespace";

        $results = $this->executeProjectsQuery($project, $sql, [
            'actorId' => $user->getActorId($project),
        ])->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');
        $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_actor = :actorId"
            . " GROUP BY YEAR(rev_timestamp), MONTH(rev_timestamp), page_namespace";

        $totals = $this->executeProjectsQuery($project, $sql, [
            'actorId' => $user->getActorId($project),
        ])->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;
        $xCalc = "ROUND(HOUR(rev_timestamp)/$hourInterval) * $hourInterval";
        $revisionTable = $this->getTableName($project->getDatabaseName(), 'revision');
        $sql = "SELECT "
            . "     DAYOFWEEK(rev_timestamp) AS `day_of_week`, "
            . "     $xCalc AS `hour`, "
            . "     COUNT(rev_id) AS `value` "
            . " FROM $revisionTable"
            . " WHERE rev_actor = :actorId"
            . " GROUP BY DAYOFWEEK(rev_timestamp), $xCalc";

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

x-tools / xtools

Pull Request — master (#377)

EditCounterRepository::getFileCountsCommons() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like