l10n_server-2.x-dev/l10n_statistics/src/Controller/L10nStatisticsSubmissionsController.php

<?php
declare(strict_types=1);

namespace Drupal\l10n_statistics\Controller;

use Drupal\Core\Controller\ControllerBase;
use Drupal\Core\Database\Database;
use Drupal\Core\Link;
use Drupal\Core\Url;
use Drupal\language\Entity\ConfigurableLanguage;

/**
 * Returns responses for l10n_statistics routes.
 */
class L10nStatisticsSubmissionsController extends ControllerBase {

  /**
   * Builds the response.
   */
  public function build(ConfigurableLanguage $language = NULL) {
    $build['subtitle'] = [
      '#type' => 'html_tag',
      '#tag' => 'h2',
    ];
    if ($language) {
      $build['subtitle']['#value'] = $this->t('@language string submissions', [
        '@language' => $language->getName(),
      ]);
    }
    else {
      $build['subtitle']['#value'] = $this->t('All languages string submissions');
    }

    $items = [];
    $items[] = [
      '#markup' => Link::fromTextAndUrl(
        $this->t('All languages'),
        Url::fromRoute('l10n_statistics.submissions')
      )->toString(),
    ];
    $languages = \Drupal::languageManager()->getLanguages();
    foreach ($languages as $configurable_language) {
      $items[] = [
        '#markup' => Link::fromTextAndUrl(
          $configurable_language->getName(),
          Url::fromRoute('l10n_statistics.submissions.language', [
            'language' => $configurable_language->getId(),
          ])
        )->toString(),
      ];
    }
    $build['languages'] = [
      '#theme' => 'item_list',
      '#type' => 'ul',
      '#title' => 'Language list',
      '#items' => $items,
      '#attributes' => ['class' => 'language-list'],
      '#wrapper_attributes' => ['class' => 'container'],
    ];

    $years = range($this->getTheBeginningOfTime(), date("Y"));
    rsort($years);
    foreach ($years as $year) {
      $build['chart_' . $year] = [
        '#type' => 'html_tag',
        '#tag' => 'div',
        '#attributes' => [
          'id' => ['l10n-statistics-chart-' . $year],
          'class' => ['l10n-statistics-chart', 'l10n-statistics-chart-' . $year],
        ],
      ];
      $build['#attached']['drupalSettings']['l10nStatistics']['submissions']['charts']['year' . $year] = $this->getSubmissions($year, $language);
    }
    $build['#attached']['library'][] = 'l10n_statistics/google-charts';
    $build['#attached']['library'][] = 'l10n_statistics/submissions';
    $build['#attached']['drupalSettings']['l10nStatistics']['submissions']['years'] = $years;
    return $build;
  }

  /**
   * Get submissions.
   *
   * @param int $year
   *   A year integer.
   * @param \Drupal\language\Entity\ConfigurableLanguage|null $language
   *   A configurable language or null.
   *
   * @return array
   *   An array of records.
   *
   * @throws \Exception
   */
  private function getSubmissions(int $year, ConfigurableLanguage $language = NULL) {
    $from = new \DateTime($year . '-01-01 00:00:00');
    $until = new \DateTime($year . '-12-31 23:59:59');

    // @todo Fix database access to default key.
    $connection = Database::getConnection('default', 'migrate');
    $query = $connection
      ->select('l10n_server_translation_history', 'th');
    if ($language instanceof ConfigurableLanguage) {
      $query
        ->innerJoin('l10n_server_translation', 't', 'th.tid = t.tid');
      $query
        ->condition('language', $language->getId());
    }
    $query
      ->condition('time_action', $from->getTimestamp(), '>=')
      ->condition('time_action', $until->getTimestamp(), '<=');
    $query
      ->addExpression("FROM_UNIXTIME(time_action, '%u')", 'week');
    $query->addExpression('SUM(IF(medium_action=0, 1, 0))', 'unknown');
    $query->addExpression('SUM(IF(medium_action=1, 1, 0))', 'web');
    $query->addExpression('SUM(IF(medium_action=2, 1, 0))', 'import');
    $query->addExpression('SUM(IF(medium_action=3, 1, 0))', 'remote');
    $query
      ->groupBy('week');
    $query
      ->orderBy('week', 'ASC');
    $result = $query->execute();
    $records = [];
    for ($i = 0; $i <= 52; $i++) {
      $records[sprintf('%02d', $i + 1)] = [sprintf('%02d', $i + 1), 0, 0, 0, 0];
    }
    foreach ($result as $record) {
      $records[$record->week] = [
        $record->week,
        (int) $record->unknown,
        (int) $record->web,
        (int) $record->import,
        (int) $record->remote,
      ];
    }
    return array_values($records);
  }

  /**
   * Get the beginning of time.
   *
   * @return int
   *   An integer representing a year.
   */
  private function getTheBeginningOfTime() {
    // @todo Fix database access to default key.
    $connection = Database::getConnection('default', 'migrate');
    $query = $connection
      ->select('l10n_server_translation_history', 'th');
    $query
      ->addExpression("FROM_UNIXTIME(time_action, '%Y')", 'year');
    $query
      ->orderBy('time_action,', 'ASC');
    $query
      ->range(0, 1);
    return (int) $query->execute()->fetchField();
  }

}