x-tools / xtools

     * @param string $username The username.

     * @return string[]

     */

    public function getMonthCounts(Project $project, User $user)

    {

        $username = $user->getUsername();

        $cacheKey = "monthcounts.$username";

        if ($this->cache->hasItem($cacheKey)) {

            return $this->cache->getItem($cacheKey)->get();

        }

        $revisionTable = $this->getTableName($project->getDatabaseName(), 'revision');

        $pageTable = $this->getTableName($project->getDatabaseName(), '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 " .

            " ORDER BY rev_timestamp DESC";

        $resultQuery = $this->getProjectsConnection()->prepare($sql);

        $resultQuery->bindParam(":username", $username);

        $resultQuery->execute();

        $totals = $resultQuery->fetchAll();

        $cacheItem = $this->cache->getItem($cacheKey);

        $cacheItem->expiresAfter(new DateInterval('PT10M'));

        $cacheItem->set($totals);

        $this->cache->save($cacheItem);

        return $totals;

    }

    /**

     * Get yearly edit totals for this user, grouped by namespace.

     * @param string $username

     * @return string[] ['<namespace>' => ['<year>' => 'total', ... ], ... ]

     */

    public function getYearCounts(Project $project, User $user)

    {

        $username = $user->getUsername();

        $cacheKey = "yearcounts.$username";

        if ($this->cache->hasItem($cacheKey)) {

            return $this->cache->getItem($cacheKey)->get();

        }

        $revisionTable = $this->getTableName($project->getDatabaseName(), 'revision');

        $pageTable = $this->getTableName($project->getDatabaseName(), 'page');

        $sql = "SELECT "

            . "     SUBSTR(CAST(rev_timestamp AS CHAR(4)), 1, 4) AS `year`,"

            . "     page_namespace,"

            . "     COUNT(rev_id) AS `count` "

            . " FROM $revisionTable    JOIN $pageTable ON (rev_page = page_id)"

            . " WHERE rev_user_text = :username"

            . " GROUP BY SUBSTR(CAST(rev_timestamp AS CHAR(4)), 1, 4), page_namespace "

            . " ORDER BY rev_timestamp DESC ";

        $resultQuery = $this->getProjectsConnection()->prepare($sql);

        $resultQuery->bindParam(":username", $username);

        $resultQuery->execute();

        $totals = $resultQuery->fetchAll();

        $cacheItem = $this->cache->getItem($cacheKey);

        $cacheItem->set($totals);

        $cacheItem->expiresAfter(new DateInterval('P10M'));

        $this->cache->save($cacheItem);

        return $totals;

    }

    /**

     * Get data for the timecard chart, with totals grouped by day and to the nearest two-hours.