kupshop/bundles/KupShop/I18nBundle/Util/AutomaticReviewsTranslation.php

<?php

declare(strict_types=1);

namespace KupShop\I18nBundle\Util;

use KupShop\CatalogBundle\Util\ReviewsUtil;
use KupShop\I18nBundle\Translations\ReviewsTranslation;
use Query\Operator;

class AutomaticReviewsTranslation
{
    public const LIMIT_REVIEWS_CHAR_LENGTH = 500;

    /**
     * @var ReviewsTranslation
     */
    private $reviewsTranslation;

    /**
     * @var TranslationEngine
     */
    private $translationEngine;

    public function translateReviews(
        string $toLanguage,
        ?int $limitReviews = null,
        ?string $fromLanguage = null,
    ): array {
        if (empty($limitReviews)) {
            $dbcfg = \Settings::getDefault();
            $limitReviews = intval($dbcfg['automatic_translate']['limit_reviews']) ?? 10;
        }
        $translateCharacters = 0;
        $addedReviews = [];
        if ($reviews = $this->getReviewsForTranslation($toLanguage, $limitReviews, $fromLanguage)) {
            foreach ($reviews as $review) {
                if (!empty($addedReviews[$review['id_product']]) && (($review['actualCount'] + $addedReviews[$review['id_product']]) >= $limitReviews)) {
                    continue;
                }

                if (!empty($translate = $this->prepareDataForReview($review))) {
                    $translateData = $this->translationEngine->getTranslationMulti($translate, $review['id_language'], $toLanguage);
                    if (!empty($translateData) && $this->reviewsTranslation->saveSingleObject($toLanguage, $review['id'], $translateData)) {
                        !empty($addedReviews[$review['id_product']]) ? $addedReviews[$review['id_product']]++ : $addedReviews[$review['id_product']] = 1;

                        foreach ($translate as $text) {
                            $translateCharacters += strlen($text);
                        }
                    }
                }
            }
        }

        if ($responseReviews = $this->getReviewResponseForTranslation()) {
            foreach ($responseReviews as $review) {
                if (!empty($translate = $this->prepareDataForReview($review))) {
                    $translateData = $this->translationEngine->getTranslationMulti($translate, $review['id_language_from'], $review['id_language_to']);
                    if (!empty($translateData)) {
                        sqlQueryBuilder()->update('reviews_translations')->directValues([
                            'response' => $translateData['response'],
                        ])->andWhere(Operator::equals(['id' => $review['id']]))->execute();
                    }
                }
            }
        }

        return ['characters' => $translateCharacters, 'add_reviews' => $addedReviews];
    }

    protected function getReviewResponseForTranslation(): array
    {
        return sqlQueryBuilder()->select('rt.id as id, r.id_language as id_language_from, rt.id_language as id_language_to, r.response')
            ->from('reviews_translations', 'rt')
            ->innerJoin('rt', 'reviews', 'r', 'r.id=rt.id_review')
            ->andWhere(Operator::isNotNull('r.response'))
            ->andWhere(Operator::isNull('rt.response'))->execute()->fetchAllAssociative();
    }

    public function getReviewsForTranslation(
        string $toLanguage,
        ?int $limitReviews = null,
        ?string $fromLanguage = null,
    ) {
        if (empty($limitReviews)) {
            $dbcfg = \Settings::getDefault();
            $limitReviews = $dbcfg['automatic_translate']['limit_reviews'] ?? 10;
        }
        $doNotTranslate = [ReviewsUtil::RANK_DECLINED, ReviewsUtil::RANK_UNCONFIRMED];

        $subQuery = sqlQueryBuilder()->select('count(DISTINCT r2.id) as pocet')
            ->from('reviews', 'r2')
            ->leftJoin('r2', 'reviews_translations', 't', 'r2.id = t.id_review')
            ->where('r.id_product = r2.id_product')
            ->andWhere('t.id_language = :toLanguage or r2.id_language = :toLanguage')
            ->andWhere(Operator::not(Operator::inIntArray($doNotTranslate, 'r2.figure')))
            ->groupBy('id_product')
            ->having('pocet >= :limitReviews')
            ->setParameter('toLanguage', $toLanguage)
            ->setParameter('limitReviews', $limitReviews)
            ->setParameter('dontTranslate', $doNotTranslate)
            ->setParameter('limitChars', self::LIMIT_REVIEWS_CHAR_LENGTH);

        $qb = sqlQueryBuilder()->select('r.id, r.id_product, r.pros, r.cons, r.summary, r.response, r.id_language')
            ->from('reviews', 'r')
            ->leftJoin('r', 'reviews_translations', 'rt', 'r.id=rt.id_review')
            ->andWhere(Operator::not(Operator::exists($subQuery)))
            ->andWhere(Operator::not(Operator::equals(['r.id_language' => $toLanguage])))
            ->andWhere(Operator::not(Operator::inIntArray($doNotTranslate, 'r.figure')))
            ->andWhere('IFNULL(CHAR_LENGTH(r.pros),0) < :limitChars')
            ->andWhere('IFNULL(CHAR_LENGTH(r.cons),0) < :limitChars')
            ->andWhere('IFNULL(CHAR_LENGTH(r.summary),0) < :limitChars')
            ->andWhere('IFNULL(CHAR_LENGTH(r.response),0) < :limitChars')
            ->andWhere('NOT EXISTS (SELECT 1
                                        FROM reviews_translations rt2
                                        WHERE  rt2.id_review = r.id and rt2.id_language = :toLanguage)'
            )
            ->andWhere(
                'CHAR_LENGTH(CONCAT(IFNULL(r.pros,""),IFNULL(r.cons,""),IFNULL(r.summary,""),IFNULL(r.response,""))) > 1',
            )
            ->setParameter('toLanguage', $toLanguage)
            ->setParameter('limitReviews', $limitReviews)
            ->setParameter('limitChars', self::LIMIT_REVIEWS_CHAR_LENGTH)
            ->addOrderBy('FIELD(r.figure, '.ReviewsUtil::RANK_CONFIRMED.','.ReviewsUtil::RANK_TOP.')', 'DESC')
            ->addOrderBy('r.date', 'DESC');

        if ($fromLanguage) {
            $qb->andWhere(Operator::equals(['r.id_language' => $fromLanguage]));
        }

        $actualCount = sqlQueryBuilder()->select('IFNULL(count(DISTINCT r2.id),0) as actualCount')
            ->from('reviews', 'r2')
            ->leftJoin('r2', 'reviews_translations', 't', 'r2.id = t.id_review')
            ->where('r.id_product = r2.id_product')
            ->andWhere('t.id_language = :toLanguage or r2.id_language = :toLanguage')
            ->andWhere(Operator::not(Operator::inIntArray($doNotTranslate, 'r2.figure')))
            ->groupBy('id_product');

        $qb->addSubselect($actualCount, 'actualCount');

        return $qb->execute()->fetchAllAssociative();
    }

    private function prepareDataForReview(array $review): array
    {
        $translate = [];
        if (!empty($review['pros'])) {
            $translate['pros'] = $review['pros'];
        }
        if (!empty($review['cons'])) {
            $translate['cons'] = $review['cons'];
        }
        if (!empty($review['summary'])) {
            $translate['summary'] = $review['summary'];
        }
        if (!empty($review['response'])) {
            $translate['response'] = $review['response'];
        }

        return $translate;
    }

    /**
     * @required
     */
    public function setReviewsTranslation(ReviewsTranslation $reviewsTranslation)
    {
        $this->reviewsTranslation = $reviewsTranslation;
    }

    /**
     * @required
     */
    public function setTranslationEngine(TranslationEngine $translationEngine)
    {
        $this->translationEngine = $translationEngine;
    }
}