public function getIndex($group = null)
{
$locales = $this->locales;
$currentLocale = \Lang::getLocale();
$primaryLocale = $this->primaryLocale;
$translatingLocale = $this->translatingLocale;
$groups = array('' => noEditTrans($this->packagePrefix . 'messages.choose-group')) + $this->manager->getGroupList();
if ($group != null && !array_key_exists($group, $groups)) {
return \Redirect::action(ManagerServiceProvider::CONTROLLER_PREFIX . get_class($this) . '@getIndex');
}
$numChanged = $this->getTranslation()->where('group', $group)->where('status', Translation::STATUS_CHANGED)->count();
// to allow proper handling of nested directory structure we need to copy the keys for the group for all missing
// translations, otherwise we don't know what the group and key looks like.
//$allTranslations = $this->getTranslation()->where('group', $group)->orderBy('key', 'asc')->get();
$displayWhere = $this->displayLocales ? ' AND locale IN (\'' . implode("','", explode(',', $this->displayLocales)) . "')" : '';
$ltm_translations = $this->manager->getTranslationsTableName();
$allTranslations = $this->getTranslation()->hydrateRaw($sql = <<<SQL
SELECT * FROM {$ltm_translations} WHERE `group` = ? {$displayWhere}
UNION ALL
SELECT DISTINCT
NULL id,
NULL status,
locale,
`group`,
`key`,
NULL value,
NULL created_at,
NULL updated_at,
NULL source,
NULL saved_value,
NULL is_deleted,
NULL was_used
FROM
(SELECT * FROM (SELECT DISTINCT locale FROM {$ltm_translations} WHERE 1=1 {$displayWhere}) lcs
CROSS JOIN (SELECT DISTINCT `group`, `key` FROM {$ltm_translations} WHERE `group` = ? {$displayWhere}) grp) m
WHERE NOT EXISTS(SELECT * FROM {$ltm_translations} t WHERE t.locale = m.locale AND t.`group` = m.`group` AND t.`key` = m.`key`)
ORDER BY `key` ASC
SQL
, [$group, $group], $this->getConnectionName());
$numTranslations = count($allTranslations);
$translations = array();
foreach ($allTranslations as $translation) {
$translations[$translation->key][$translation->locale] = $translation;
}
$this->manager->cacheGroupTranslations($group, $this->displayLocales, $translations);
$stats = $this->getConnection()->select(<<<SQL
SELECT (mx.total_keys - lcs.total) missing, lcs.changed, lcs.cached, lcs.deleted, lcs.locale, lcs.`group`
FROM
(SELECT sum(total) total, sum(changed) changed, sum(cached) cached, sum(deleted) deleted, `group`, locale
FROM
(SELECT count(value) total,
sum(CASE WHEN status = 1 THEN 1 ELSE 0 END) changed,
sum(CASE WHEN status = 2 AND value IS NOT NULL THEN 1 ELSE 0 END) cached,
sum(is_deleted) deleted,
`group`, locale
FROM {$ltm_translations} lt WHERE 1=1 {$displayWhere} GROUP BY `group`, locale
UNION ALL
SELECT DISTINCT 0, 0, 0, 0, `group`, locale FROM (SELECT DISTINCT locale FROM {$ltm_translations} WHERE 1=1 {$displayWhere}) lc
CROSS JOIN (SELECT DISTINCT `group` FROM {$ltm_translations}) lg) a
GROUP BY `group`, locale) lcs
JOIN (SELECT count(DISTINCT `key`) total_keys, `group` FROM {$ltm_translations} WHERE 1=1 {$displayWhere} GROUP BY `group`) mx
ON lcs.`group` = mx.`group`
WHERE lcs.total < mx.total_keys OR lcs.changed > 0 OR lcs.cached > 0 OR lcs.deleted > 0
SQL
);
// returned result set lists mising, changed, group, locale
$summary = [];
foreach ($stats as $stat) {
if (!isset($summary[$stat->group])) {
$item = $summary[$stat->group] = new \stdClass();
$item->missing = '';
$item->changed = '';
$item->cached = '';
$item->deleted = '';
$item->group = $stat->group;
}
$item = $summary[$stat->group];
if ($stat->missing) {
$item->missing .= $stat->locale . ":" . $stat->missing . " ";
}
if ($stat->changed) {
$item->changed .= $stat->locale . ":" . $stat->changed . " ";
}
if ($stat->cached) {
$item->cached .= $stat->locale . ":" . $stat->cached . " ";
}
if ($stat->deleted) {
$item->deleted .= $stat->locale . ":" . $stat->deleted . " ";
}
}
$mismatches = null;
$mismatchEnabled = $this->manager->config('mismatch_enabled');
if ($mismatchEnabled) {
// get mismatches
$mismatches = $this->getConnection()->select(<<<SQL
SELECT DISTINCT lt.*, ft.ru, ft.en
FROM (SELECT * FROM {$ltm_translations} WHERE 1=1 {$displayWhere}) lt
JOIN
(SELECT DISTINCT mt.`key`, BINARY mt.ru ru, BINARY mt.en en
FROM (SELECT lt.`group`, lt.`key`, group_concat(CASE lt.locale WHEN '{$primaryLocale}' THEN VALUE ELSE NULL END) en, group_concat(CASE lt.locale WHEN '{$translatingLocale}' THEN VALUE ELSE NULL END) ru
FROM (SELECT value, `group`, `key`, locale FROM {$ltm_translations} WHERE 1=1 {$displayWhere}
UNION ALL
SELECT NULL, `group`, `key`, locale FROM ((SELECT DISTINCT locale FROM {$ltm_translations} WHERE 1=1 {$displayWhere}) lc
CROSS JOIN (SELECT DISTINCT `group`, `key` FROM {$ltm_translations} WHERE 1=1 {$displayWhere}) lg)
) lt
GROUP BY `group`, `key`) mt
JOIN (SELECT lt.`group`, lt.`key`, group_concat(CASE lt.locale WHEN '{$primaryLocale}' THEN VALUE ELSE NULL END) en, group_concat(CASE lt.locale WHEN '{$translatingLocale}' THEN VALUE ELSE NULL END) ru
FROM (SELECT value, `group`, `key`, locale FROM {$ltm_translations} WHERE 1=1 {$displayWhere}
UNION ALL
SELECT NULL, `group`, `key`, locale FROM ((SELECT DISTINCT locale FROM {$ltm_translations} WHERE 1=1 {$displayWhere}) lc
CROSS JOIN (SELECT DISTINCT `group`, `key` FROM {$ltm_translations} WHERE 1=1 {$displayWhere}) lg)
) lt
GROUP BY `group`, `key`) ht ON mt.`key` = ht.`key`
WHERE (mt.ru NOT LIKE BINARY ht.ru AND mt.en LIKE BINARY ht.en) OR (mt.ru LIKE BINARY ht.ru AND mt.en NOT LIKE BINARY ht.en)
) ft
ON (lt.locale = '{$translatingLocale}' AND lt.value LIKE BINARY ft.ru) AND lt.`key` = ft.key
ORDER BY `key`, `group`
SQL
);
$key = '';
$rus = [];
$ens = [];
$rubases = [];
// by key
$enbases = [];
// by key
$extra = new \stdClass();
$extra->key = '';
$mismatches[] = $extra;
foreach ($mismatches as $mismatch) {
if ($mismatch->key !== $key) {
if ($key) {
// process diff for key
$txtru = '';
$txten = '';
if (count($ens) > 1) {
$maxen = 0;
foreach ($ens as $en => $cnt) {
if ($maxen < $cnt) {
$maxen = $cnt;
$txten = $en;
}
}
$enbases[$key] = $txten;
} else {
$txten = array_keys($ens)[0];
$enbases[$key] = $txten;
}
if (count($rus) > 1) {
$maxru = 0;
foreach ($rus as $ru => $cnt) {
if ($maxru < $cnt) {
$maxru = $cnt;
$txtru = $ru;
}
}
$rubases[$key] = $txtru;
} else {
$txtru = array_keys($rus)[0];
$rubases[$key] = $txtru;
}
}
$key = $mismatch->key;
$rus = [];
$ens = [];
}
if ($mismatch->key === '') {
break;
}
if (!isset($ens[$mismatch->en])) {
$ens[$mismatch->en] = 1;
} else {
$ens[$mismatch->en]++;
}
if (!isset($rus[$mismatch->ru])) {
$rus[$mismatch->ru] = 1;
} else {
$rus[$mismatch->ru]++;
}
}
array_splice($mismatches, count($mismatches) - 1, 1);
foreach ($mismatches as $mismatch) {
$mismatch->en_value = $mismatch->ru;
$mismatch->en = mb_renderDiffHtml($enbases[$mismatch->key], $mismatch->en);
$mismatch->ru_value = $mismatch->ru;
$mismatch->ru = mb_renderDiffHtml($rubases[$mismatch->key], $mismatch->ru);
}
}
// returned result set lists group key ru, en columns for the locale translations, ru has different values for same values in en
$displayLocales = array_intersect($locales, explode(',', $this->displayLocales));
// need to put display locales first in the $locales list
$locales = array_merge($displayLocales, array_diff($locales, $displayLocales));
$displayLocales = array_combine($displayLocales, $displayLocales);
$show_usage_enabled = $this->manager->config('log_key_usage_info', false);
return \View::make($this->packagePrefix . 'index')->with('controller', ManagerServiceProvider::CONTROLLER_PREFIX . get_class($this))->with('package', $this->package)->with('public_prefix', ManagerServiceProvider::PUBLIC_PREFIX)->with('translations', $translations)->with('yandex_key', !!$this->manager->config('yandex_translator_key'))->with('locales', $locales)->with('primaryLocale', $primaryLocale)->with('currentLocale', $currentLocale)->with('translatingLocale', $translatingLocale)->with('displayLocales', $displayLocales)->with('groups', $groups)->with('group', $group)->with('numTranslations', $numTranslations)->with('numChanged', $numChanged)->with('adminEnabled', $this->manager->config('admin_enabled') && UserCan::admin_translations())->with('mismatchEnabled', $mismatchEnabled)->with('stats', $summary)->with('mismatches', $mismatches)->with('show_usage', $this->showUsageInfo && $show_usage_enabled)->with('usage_info_enabled', $show_usage_enabled)->with('connection_list', $this->connectionList)->with('transFilters', $this->transFilters)->with('connection_name', $this->getConnectionName());
}