l10n_server-2.x-dev/l10n_community/src/L10nStatistics.php
l10n_community/src/L10nStatistics.php
<?php
declare(strict_types=1);
namespace Drupal\l10n_community;
use Drupal\Component\Datetime\TimeInterface;
use Drupal\Core\Cache\CacheBackendInterface;
use Drupal\Core\Database\Connection;
use Drupal\Core\Database\StatementInterface;
use Drupal\Core\Entity\EntityTypeManagerInterface;
use Drupal\Core\Language\LanguageManagerInterface;
use Drupal\l10n_server\Entity\L10nServerProjectInterface;
use Drupal\l10n_server\Entity\L10nServerReleaseInterface;
/**
* Service description.
*
* @todo Fix mysql performance problems.
* These queries are *slooow*. The query cache helps a lot with caching the
* result, so the slowness only shows for the first run, but still it would be
* good to look into optimizing these.
*/
final class L10nStatistics {
private const CACHE_EXPIRATION = 3600;
/**
* Constructs a L10nStatistics object.
*
* @param \Drupal\Core\Database\Connection $connection
* The database connection.
* @param \Drupal\Core\Entity\EntityTypeManagerInterface $entityTypeManager
* The entity type manager.
* @param \Drupal\Core\Cache\CacheBackendInterface $cacheBackend
* The cache backend.
* @param \Drupal\Component\Datetime\TimeInterface $time
* The time service.
* @param \Drupal\Core\Language\LanguageManagerInterface $languageManager
* The language manager.
*/
public function __construct(
private readonly Connection $connection,
private readonly EntityTypeManagerInterface $entityTypeManager,
private readonly CacheBackendInterface $cacheBackend,
private readonly TimeInterface $time,
private readonly LanguageManagerInterface $languageManager,
) {
}
/**
* Get count of all strings.
*/
public function getTotalStringCount(): int {
$cid = 'l10n_community_total_string_count';
if ($cache = $this->cacheBackend->get($cid)) {
return $cache->data;
}
$count = (int) $this->connection
->query("SELECT COUNT(sid) FROM {l10n_server_string};")
->fetchField();
$this->cacheBackend->set($cid, $count, $this->time->getRequestTime() + self::CACHE_EXPIRATION);
return $count;
}
/**
* Get count of all strings.
*/
public function getStringCount(): int {
return (int) $this->connection
->query("SELECT COUNT(sid) FROM {l10n_server_string}")
->fetchField();
}
/**
* Get language statistics by language.
*
* @param string $langcode
* A langcode string, e.g. 'en', 'fr' or 'de'.
*
* @return array
* An array of language statistics.
*/
public function getLanguageStatisticsByLanguage(string $langcode): array {
$stats = [];
$stats['strings'] = $this->connection
->query('SELECT COUNT(*) FROM {l10n_server_string}')
->fetchField();
$stats['translations'] = $this->connection
->query("SELECT COUNT(*) FROM {l10n_server_status_flag} WHERE has_translation = 1 AND language = :language", [
':language' => $langcode,
])
->fetchField();
$stats['suggestions'] = $this->connection
->query("SELECT COUNT(*) FROM {l10n_server_translation} WHERE suggestion = 1 AND status = 1 AND language = :language", [
':language' => $langcode,
])
->fetchField();
$stats['users'] = $this->connection
->query("SELECT COUNT(DISTINCT uid) FROM {l10n_server_translation} WHERE suggestion = 0 AND status = 1 AND language = :language", [
':language' => $langcode,
])
->fetchField();
return $stats;
}
/**
* Replacement for l10n_community_get_string_count when former stats are slow.
*
* @param \Drupal\l10n_server\Entity\L10nServerProjectInterface $project
* A l10n_server_project entity.
* @param \Drupal\l10n_server\Entity\L10nServerReleaseInterface|null $release
* The release to get string counts for. If not provided, the latest release
* of the project will be considered.
*
* @return array
* first element is the number of original strings, second element is the
* number of translations for each language
*
* @throws \Drupal\Component\Plugin\Exception\InvalidPluginDefinitionException
* @throws \Drupal\Component\Plugin\Exception\PluginNotFoundException
*
* @todo Fix mysql performance problems and check against the former l10n_community_get_string_count.
*/
public function getReleaseStringCount(L10nServerProjectInterface $project, ?L10nServerReleaseInterface $release = NULL): array {
$cid = 'l10n_community_release_string_count:' . $project->id() . ':' . ($release ? $release->id() : 'latest');
if ($cache = $this->cacheBackend->get($cid)) {
return $cache->data;
}
// Faster queries with l10n_packager, first get latest release id from the
// l10n_packager_file table for the project requested, then get the
// translation count of the release.
if (!$release) {
$rid = $this->connection
->query("SELECT pf.rid FROM {l10n_server_release} sr INNER JOIN {l10n_packager_file} pf ON sr.rid = pf.rid WHERE pid = :pid ORDER BY pf.rid DESC LIMIT 0, 1", [
':pid' => $project->id(),
])
->fetchField();
if (!$rid) {
$num_source = $sums = 0;
$this->cacheBackend->set($cid, [$num_source, $sums], $this->time->getRequestTime() + self::CACHE_EXPIRATION);
return [$num_source, $sums];
}
$release = \Drupal::entityTypeManager()
->getStorage('l10n_server_release')
->load($rid);
if (!$release) {
$num_source = $sums = 0;
$this->cacheBackend->set($cid, [$num_source, $sums], $this->time->getRequestTime() + self::CACHE_EXPIRATION);
return [$num_source, $sums];
}
}
$results = $this->connection
->query("SELECT language, sid_count FROM {l10n_packager_file} WHERE rid = :rid", [
':rid' => $release->id(),
])
->fetchAllKeyed();
foreach ($results as $language => $sid_count) {
$sums[$language]['translations'] = $sid_count;
}
ksort($sums);
// Finally, get the string count of the release.
$num_source = $this->connection
->query("SELECT COUNT(DISTINCT l.sid) FROM {l10n_server_line} l WHERE l.rid = :rid", [
':rid' => $release->id(),
])
->fetchField();
$this->cacheBackend->set($cid, [$num_source, $sums], $this->time->getRequestTime() + self::CACHE_EXPIRATION);
return [$num_source, $sums];
}
/**
* Get count of all strings in this project.
*
* @param \Drupal\l10n_server\Entity\L10nServerProjectInterface $project
* A l10n_server_project entity.
*/
public function getProjectStringCount(L10nServerProjectInterface $project): int {
$cid = ($project instanceof L10nServerProjectInterface) ? 'l10n_community_project_string_count:' . $project->id() : 'l10n_community_project_string_count';
if ($cache = $this->cacheBackend->get($cid)) {
return $cache->data;
}
$query = $this->connection
->query('SELECT COUNT(DISTINCT l.sid) FROM {l10n_server_line} l WHERE l.pid = :pid', [
':pid' => $project->id(),
]);
$count = (int) $query->fetchField();
$this->cacheBackend->set($cid, $count, $this->time->getRequestTime() + self::CACHE_EXPIRATION);
return $count;
}
/**
* Get count of all warnings in this project.
*
* @param \Drupal\l10n_server\Entity\L10nServerProjectInterface $project
* A l10n_server_project entity.
*/
public function getProjectWarningsCount(L10nServerProjectInterface $project): int {
$cid = 'l10n_community_project_warnings_count:' . $project->id();
if ($cache = $this->cacheBackend->get($cid)) {
return $cache->data;
}
$query = $this->connection
->query('SELECT COUNT(DISTINCT e.eid) FROM {l10n_server_project} p LEFT JOIN {l10n_server_release} r ON p.pid = r.pid LEFT JOIN {l10n_server_error} e ON r.rid = e.rid WHERE p.uri = :uri', [
':uri' => $project->getUri(),
]);
if (!$query instanceof StatementInterface) {
return 0;
}
$count = (int) $query->fetchField();
$this->cacheBackend->set($cid, $count, $this->time->getRequestTime() + self::CACHE_EXPIRATION);
return $count;
}
/**
* Get project release count.
*
* @param \Drupal\l10n_server\Entity\L10nServerProjectInterface $project
* The l10n_server_project entity.
*
* @return int
* The release count integer.
*/
public function getProjectReleases(L10nServerProjectInterface $project): int {
$release_ids = \Drupal::entityQuery('l10n_server_release')
->condition('pid', $project->id())
->accessCheck(FALSE)
->execute();
return count($release_ids);
}
/**
* Get project parsed release count.
*
* @param \Drupal\l10n_server\Entity\L10nServerProjectInterface $project
* The l10n_server_project entity.
*
* @return int
* The parsed release count integer.
*
* @throws \Drupal\Component\Plugin\Exception\InvalidPluginDefinitionException
* @throws \Drupal\Component\Plugin\Exception\PluginNotFoundException
*/
public function getProjectReleasesParsed(L10nServerProjectInterface $project): int {
$release_ids = \Drupal::entityQuery('l10n_server_release')
->condition('pid', $project->id())
->accessCheck(FALSE)
->execute();
$releases = \Drupal::entityTypeManager()
->getStorage('l10n_server_release')
->loadMultiple($release_ids);
$num_parsed = 0;
foreach ($releases as $release) {
if ($release->getLastParsed() > 0) {
$num_parsed++;
}
}
return $num_parsed;
}
/**
* Get summaries based on language codes.
*
* @param \Drupal\l10n_server\Entity\L10nServerProjectInterface|null $project
* A l10n_server_project entity.
*
* @return array
* An array containing translation and suggestion counts.
*/
public function getLanguagesStringCount(?L10nServerProjectInterface $project = NULL): array {
$cid = ($project instanceof L10nServerProjectInterface) ? 'l10n_community_languages_string_count:' . $project->id() : 'l10n_community_languages_string_count';
if ($cache = $this->cacheBackend->get($cid)) {
return $cache->data;
}
$sums = [];
if (!isset($project)) {
// Simple count query if we are not filtering by project.
$count1_sql = "SELECT COUNT(sid) AS translation_count, language FROM {l10n_server_status_flag} WHERE has_translation = 1 GROUP BY language";
$count2_sql = "SELECT COUNT(sid) AS translation_count, language FROM {l10n_server_status_flag} WHERE has_suggestion = 1 GROUP BY language";
$count_args = [];
}
else {
// More complex joins if we also need to factor the project in.
$count1_sql = "SELECT COUNT(DISTINCT ts.sid) AS translation_count, ts.language FROM {l10n_server_line} l INNER JOIN {l10n_server_status_flag} ts ON l.sid = ts.sid WHERE l.pid = :pid AND ts.has_translation = 1 GROUP BY ts.language";
$count2_sql = "SELECT COUNT(DISTINCT ts.sid) AS translation_count, ts.language FROM {l10n_server_line} l INNER JOIN {l10n_server_status_flag} ts ON l.sid = ts.sid WHERE l.pid = :pid AND ts.has_suggestion = 1 GROUP BY ts.language";
$count_args = [':pid' => $project->id()];
}
$result_query_count1 = $this->connection
->query($count1_sql, $count_args);
if (!$result_query_count1 instanceof StatementInterface) {
return [];
}
foreach ($result_query_count1 as $row) {
$sums[$row->language]['translations'] = $row->translation_count;
}
$result_query_count2 = $this->connection->query($count2_sql, $count_args);
if (!$result_query_count2 instanceof StatementInterface) {
return [];
}
foreach ($result_query_count2 as $row) {
$sums[$row->language]['suggestions'] = $row->translation_count;
}
$this->cacheBackend->set($cid, $sums, $this->time->getRequestTime() + self::CACHE_EXPIRATION);
return $sums;
}
/**
* Get summaries by projects.
*
* @param string|null $langcode
* A langcode string, e.g. 'en', 'fr' or 'de'.
* @param array|null $project_ids
* An array of project IDs.
*
* @return array
* An array containing project string counts.
*/
public function getProjectsStringCount(?string $langcode = NULL, ?array $project_ids = NULL): array {
$cid = ($project_ids) ? 'l10n_community_projects_string_count:' . implode(',', $project_ids) : 'l10n_community_projects_string_count';
if ($cache = $this->cacheBackend->get($cid)) {
return $cache->data;
}
// First get the count of strings available for translation.
$sums = [];
if ($project_ids === NULL) {
$result = $this->connection
->query("SELECT COUNT(DISTINCT sid) AS string_count, pid FROM {l10n_server_line} GROUP BY pid");
}
else {
$result = $this->connection
->query(
"SELECT COUNT(DISTINCT sid) AS string_count, pid FROM {l10n_server_line} WHERE pid IN (:ids[]) GROUP BY pid",
[
':ids[]' => $project_ids,
]
);
}
if (!$result instanceof StatementInterface) {
return [];
}
foreach ($result as $row) {
$sums[$row->pid] = ['count' => $row->string_count];
}
// Get the count of distinct strings having translations and suggestions per
// project. This is run per project because big installs of the module were
// choking on GROUP BY based solutions.
foreach (['translations' => 'has_translation', 'suggestions' => 'has_suggestion'] as $key => $column) {
foreach ($sums as $pid => &$data) {
$count_args = [];
$count_sql = "SELECT COUNT(DISTINCT ts.sid) AS translation_count FROM {l10n_server_line} l LEFT JOIN {l10n_server_status_flag} ts ON l.sid = ts.sid WHERE ts.$column = 1 ";
if (isset($langcode)) {
// Limit to language if desired.
$count_sql .= "AND ts.language = :language ";
$count_args[':language'] = $langcode;
}
$count_sql .= 'AND l.pid = :pid';
$count_args[':pid'] = $pid;
$data[$key] = $this->connection
->query($count_sql, $count_args)
->fetchField();
}
}
$this->cacheBackend->set($cid, $sums, $this->time->getRequestTime() + self::CACHE_EXPIRATION);
return $sums;
}
/**
* Get the project string stats by language.
*
* @param \Drupal\l10n_server\Entity\L10nServerProjectInterface $project
* A l10n_server_project entity.
*
* @return array
* An array containing the project string stats by language.
*/
public function getProjectStringStatsByLanguage(L10nServerProjectInterface $project): array {
$cid = 'l10n_community_project_string_stats_by_language:' . $project->id();
if ($cache = $this->cacheBackend->get($cid)) {
return $cache->data;
}
// First get the count of strings available for translation.
$stats = [];
$count_strings = $this->connection
->query(
"SELECT COUNT(DISTINCT sid) AS string_count, pid FROM {l10n_server_line} WHERE pid = :pid GROUP BY pid;",
[
':pid' => $project->id(),
]
)->fetchField();
$count_suggestions = $this->connection
->query(
"SELECT
count(DISTINCT ts.sid) as suggestion_count,
ts.`language`
FROM {l10n_server_status_flag} AS ts
WHERE
ts.sid IN
(
SELECT
DISTINCT (l.sid)
FROM {l10n_server_line} l
WHERE pid = :pid
)
AND ts.has_suggestion = 1
GROUP BY ts.language;",
[
':pid' => $project->id(),
]
)->fetchAllAssoc('language', \PDO::FETCH_ASSOC);
$count_translations = $this->connection
->query(
"SELECT
count(DISTINCT ts.sid) as translation_count,
ts.`language`
FROM {l10n_server_status_flag} AS ts
WHERE
ts.sid IN
(
SELECT
DISTINCT (l.sid)
FROM {l10n_server_line} l
WHERE pid = :pid
)
AND ts.has_translation = 1
GROUP BY ts.language;",
[
':pid' => $project->id(),
]
)->fetchAllAssoc('language', \PDO::FETCH_ASSOC);
foreach ($this->languageManager->getLanguages() as $language) {
$langcode = $language->getId();
$stats[$langcode] = [
'count' => (int) $count_strings,
'translations' => (int) (!empty($count_translations[$langcode])) ? $count_translations[$langcode]['translation_count'] : 0,
'suggestions' => (int) (!empty($count_suggestions[$langcode])) ? $count_suggestions[$langcode]['suggestion_count'] : 0,
];
}
$this->cacheBackend->set($cid, $stats, $this->time->getRequestTime() + self::CACHE_EXPIRATION);
return $stats;
}
/**
* Get summaries of people having most active translations per language.
*
* Skip anonymous since that is used for placeholders when there was no prior
* translations for a suggestion.
*
* @param string $langcode
* A langcode string, e.g. 'en', 'fr' or 'de'.
*/
public function getPeopleStringCount(string $langcode): array {
$cid = 'l10n_community_people_string_count:' . $langcode;
if ($cache = $this->cacheBackend->get($cid)) {
return $cache->data;
}
// Get summaries of people having most active translations per language.
// Skip anonymous since that is used for placeholders when there was no
// prior translations for a suggestion.
$result = $this->connection
->queryRange("SELECT COUNT(DISTINCT t.sid) AS sum, u.name, u.uid FROM {l10n_server_translation} t LEFT JOIN {users_field_data} u ON t.uid = u.uid WHERE t.suggestion = 0 AND t.status = 1 AND t.language = :lng AND t.uid != 0 GROUP BY t.uid ORDER BY sum DESC", 0, 10, [
':lng' => $langcode,
]);
$count = $result->fetchAll();
$this->cacheBackend->set($cid, $count, $this->time->getRequestTime() + self::CACHE_EXPIRATION);
return $count;
}
/**
* Get general language statistics.
*
* @return array
* An array of language statistics.
*/
public function getLanguageStatistics(): array {
$cid = 'l10n_community_language_statistics';
if ($cache = $this->cacheBackend->get($cid)) {
return (array) $cache->data;
}
$stats = [];
$stats['users'] = (int) $this->connection
->query("SELECT COUNT(DISTINCT uid) FROM {l10n_server_translation}")
->fetchField();
$stats['projects'] = $this->connection
->query('SELECT COUNT(*) FROM {l10n_server_project} WHERE status = 1')
->fetchField();
$stats['releases_parsed'] = $this->connection
->query('SELECT COUNT(*) FROM {l10n_server_release} WHERE last_parsed != 0')
->fetchField();
$stats['releases_queue'] = $this->connection
->query('SELECT COUNT(*) FROM {l10n_server_release} r INNER JOIN {l10n_server_project} p ON r.pid = p.pid WHERE p.status = 1 AND r.last_parsed = 0')
->fetchField();
$stats['files'] = $this->connection
->query('SELECT COUNT(*) FROM {l10n_server_file}')
->fetchField();
$stats['strings'] = $this->connection
->query('SELECT COUNT(*) FROM {l10n_server_string}')
->fetchField();
$stats['translations'] = $this->connection
->query('SELECT COUNT(*) FROM {l10n_server_status_flag} WHERE has_translation = 1')
->fetchField();
$stats['suggestions'] = $this->connection
->query('SELECT COUNT(*) FROM {l10n_server_translation} WHERE suggestion = 1 AND status = 1')
->fetchField();
if (\Drupal::moduleHandler()->moduleExists('l10n_groups')) {
$stats['groups'] = $this->connection
->query('SELECT COUNT(*) FROM {groups} WHERE type = \'translation\'')
->fetchField();
}
$this->cacheBackend->set($cid, $stats, $this->time->getRequestTime() + self::CACHE_EXPIRATION);
return $stats;
}
/**
* Get language statistics by language.
*
* @param string $langcode
* A langcode string, e.g. 'en', 'fr' or 'de'.
*
* @return array
* An array of language statistics.
*/
public function getLanguagesStatistics(?string $langcode = NULL): array {
$cid = ($langcode) ? 'l10n_community_languages_statistics:' . $langcode : 'l10n_community_languages_statistics';
if ($cache = $this->cacheBackend->get($cid)) {
return (array) $cache->data;
}
$stats = [];
$stats['strings'] = $this->connection
->query('SELECT COUNT(*) FROM {l10n_server_string}')
->fetchField();
$stats['translations'] = $this->connection
->query("SELECT COUNT(*) AS translation_count, language FROM {l10n_server_status_flag} WHERE has_translation = 1 GROUP BY language;", [])
->fetchAllAssoc('language', \PDO::FETCH_ASSOC);
$stats['suggestions'] = $this->connection
->query("SELECT COUNT(*) AS suggestion_count, language FROM {l10n_server_translation} WHERE suggestion = 1 AND status = 1 GROUP BY language;", [])
->fetchAllAssoc('language', \PDO::FETCH_ASSOC);
$stats['users'] = ($langcode) ? $this->getUsersCountByLanguage($langcode) : $this->getUsersCountGroupByLanguage();
$this->cacheBackend->set($cid, $stats, $this->time->getRequestTime() + self::CACHE_EXPIRATION);
return $stats;
}
/**
* Get users count by language.
*
* @param string $langcode
* A langcode string, e.g. 'en', 'fr' or 'de'.
*
* @return array|array[]
* An array of users count by language.
*/
public function getUsersCountByLanguage(string $langcode): array {
$cid = 'l10n_community_users_count_by_language:' . $langcode;
if ($cache = $this->cacheBackend->get($cid)) {
return (array) $cache->data;
}
$count = [
$langcode => [
'count' => $this->getUsersCountGroupByLanguage()[$langcode]['count'] ?? 0,
'language' => $langcode,
],
];
$this->cacheBackend->set($cid, $count, $this->time->getRequestTime() + self::CACHE_EXPIRATION);
return $count;
}
/**
* Get users count group by language.
*
* @return array
* An array of users count group by language.
*/
public function getUsersCountGroupByLanguage(): array {
$cid = 'l10n_community_users_count_group_by_language';
if ($cache = $this->cacheBackend->get($cid)) {
return (array) $cache->data;
}
$counts = $this->connection
->query("SELECT language, COUNT(DISTINCT uid) AS count FROM {l10n_server_translation} WHERE suggestion = 0 AND status = 1 GROUP BY language", [])
->fetchAllAssoc('language', \PDO::FETCH_ASSOC);
$this->cacheBackend->set($cid, $counts, $this->time->getRequestTime() + self::CACHE_EXPIRATION);
return $counts;
}
}
