l10n_server-2.x-dev/l10n_community/src/L10nTranslator.php

<?php

declare(strict_types=1);

namespace Drupal\l10n_community;

use Drupal\Core\Database\Connection;
use Drupal\Core\Database\Query\Condition;

/**
 * Service description.
 */
class L10nTranslator {

  /**
   * The database connection.
   *
   * @var \Drupal\Core\Database\Connection
   */
  protected Connection $connection;

  /**
   * Constructs a L10nTranslator object.
   *
   * @param \Drupal\Core\Database\Connection $connection
   *   The database connection.
   */
  public function __construct(
      Connection $connection
  ) {
    $this->connection = $connection;
  }

  /**
   * Get strings under some conditions.
   *
   * @param string $langcode
   *   Language code to use for the lookup.
   * @param array $filters
   *   Filters, with following elements:
   *   - 'project'
   *     Project object to look up strings for.
   *   - 'status'
   *     Filter strings by status. See L10N_STATUS_ALL,
   *     L10N_STATUS_UNTRANSLATED, L10N_STATUS_HAS_SUGGESTION and
   *     L10N_STATUS_TRANSLATED.
   *   - 'release'
   *     Release id of the particular project release to filter with.
   *     Use NULL to not filter on releases.
   *   - 'search'
   *     Substring to search for in all source and translation strings.
   *   - 'context'
   *     From Drupal 7, separate contexts are supported. POTX_CONTEXT_NONE is
   *     the default, if the code does not specify a context otherwise.
   * @param int|null $pager
   *   Number of strings to be returned in a pager. Should be NULL if
   *   no pager should be used.
   *
   * @return array
   *   An array of string records from database.
   */
  public function getStrings(string $langcode, array $filters, int $pager = NULL): array {
    $query = $this->connection
      ->select('l10n_server_string', 's');
    $query
      ->leftJoin('l10n_server_status_flag', 'ts', 's.sid = ts.sid AND ts.language = :language', [
        ':language' => $langcode,
      ]);
    $query
      ->leftJoin('l10n_server_translation', 't', 'ts.sid = t.sid AND ts.language = t.language AND t.status = 1 AND t.suggestion = 0 AND ts.has_translation = 1');
    // The translation table has a 1:N relationship to sources. We need to limit
    // results to unique sids. A group by on sid appears to be the fastest
    // solution (as opposed to eg. DISTINCT).
    // @note the D7 version also applied a groupBy('NULL'), but it just makes
    // the query crash.
    $query->groupBy('sid');
    $query
      ->leftJoin('users_field_data', 'u', 'u.uid = t.uid');
    $query
      ->fields('s', ['sid', 'value', 'context'])
      ->fields('t', [
        'tid', 'language', 'translation', 'uid', 'created', 'changed', 'suggestion', 'status',
      ])
      ->fields('ts', ['has_suggestion', 'has_translation']);
    $query
      ->addField('u', 'name', 'username');

    // Add sid filtering.
    if (!empty($filters['sid'])) {
      $query->condition('s.sid', $filters['sid']);
    }

    // Add submitted by condition.
    if (!empty($filters['author'])) {
      $query->condition('t.uid', $filters['author']->id());
    }

    // Release restriction.
    $release = empty($filters['release']) || $filters['release'] === 'all' ? NULL : $filters['release'];
    $project = $filters['project'];
    if ($release || $project) {
      $query->innerJoin('l10n_server_line', 'l', 's.sid = l.sid');
      // If we have a release we ignore the project.
      if ($release) {
        // Release restriction.
        $query->condition('l.rid', $release);
      }
      elseif ($project) {
        $query->condition('l.pid', $project->pid);
      }
    }

    // Context based filtering.
    if (isset($filters['context']) && $filters['context'] != 'all') {
      // We use 'none' for no context, so '' can be the defaut (for all
      // contexts).
      $context = $filters['context'] == 'none' ? '' : $filters['context'];
      $query->condition('s.context', $context);
    }

    if (!empty($filters['search'])) {
      // Search in the source or target strings.
      $search = $filters['search'];
      $query->condition((new Condition('OR'))
        ->condition('s.value', '%' . $this->connection->escapeLike($search) . '%', 'LIKE')
        ->condition('t.translation', '%' . $this->connection->escapeLike($search) . '%', 'LIKE')
      );
    }

    // Restriction based on string status by translation / suggestions.
    if (isset($filters['status'])) {
      if ($filters['status'] & L10N_STATUS_UNTRANSLATED) {
        $query->condition((new Condition('OR'))
          ->condition('ts.has_translation', 0)
          ->isNull('ts.has_translation')
        );
      }
      elseif ($filters['status'] & L10N_STATUS_TRANSLATED) {
        $query->condition('ts.has_translation', '1');
      }
      elseif ($filters['status'] & L10N_STATUS_IS_TRANSLATION) {
        $query->condition('t.is_suggestion', '0');
      }

      if ($filters['status'] & L10N_STATUS_HAS_SUGGESTION) {
        $query->condition('ts.has_suggestion', '1');
      }
      elseif ($filters['status'] & L10N_STATUS_NO_SUGGESTION) {
        $query->condition((new Condition('OR'))
          ->condition('ts.has_suggestion', 0)
          ->isNull('ts.has_suggestion')
        );
      }
      elseif ($filters['status'] & L10N_STATUS_IS_SUGGESTION) {
        $query->condition('t.is_suggestion', '1');
      }
    }

    // We either need a pager or a full result.
    if (isset($pager)) {
      $query = $query
        ->extend('Drupal\Core\Database\Query\PagerSelectExtender')
        ->limit($pager);
    }
    $strings = $query->execute()
      ->fetchAllAssoc('sid');

    // Also load suggestions, and add them.
    foreach ($this->getSuggestions($langcode, array_keys($strings)) as $suggestion) {
      $strings[$suggestion->sid]->suggestions[] = $suggestion;
    }

    return $strings;
  }

  /**
   * Get suggestions for a list of source strings.
   *
   * @param string $langcode
   *   Language code to use for the lookup.
   * @param array $sids
   *   Source string IDs.
   *
   * @return array
   *   Suggestions.
   */
  public function getSuggestions(string $langcode, array $sids): array {
    $query = $this->connection
      ->select('l10n_server_translation', 't');
    $query->leftJoin('users_field_data', 'u', 'u.uid = t.uid');
    $query->fields('t', [
      'tid', 'sid', 'translation', 'uid', 'created', 'changed', 'status', 'suggestion',
    ]);
    $query->addField('u', 'name', 'username');
    $query->condition('t.language', $langcode);
    $query->condition('t.sid', $sids, 'IN');
    $query->condition('t.status', 1);
    $query->condition('t.suggestion', 1);
    $query->orderBy('t.created');

    return $query->execute()->fetchAll();
  }

  /**
   * List of project releases where a source string is used.
   */
  public function getSourceDetails($sid) {
    $query = $this->connection
      ->select('l10n_server_line', 'l');
    $query->innerJoin('l10n_server_project', 'p', 'l.pid = p.pid');
    $query->innerJoin('l10n_server_release', 'r', 'l.rid = r.rid');
    $query->fields('l', ['pid', 'rid']);
    $query->addField('p', 'title', 'project_title');
    $query->addField('r', 'version');
    $query->addExpression('COUNT(l.lineno)', 'occurrence_count');
    $query->condition('l.sid', $sid)
      ->condition('p.status', 1)
      ->groupBy('l.rid')
      ->orderBy('l.pid')
      ->orderBy('l.rid');

    return $query->execute()->fetchAll();
  }

}