786 lines
31 KiB
PHP
786 lines
31 KiB
PHP
<?php
|
||
|
||
declare(strict_types=1);
|
||
|
||
namespace KupShop\CatalogBundle\Util;
|
||
|
||
use KupShop\CatalogBundle\Enum\Reviews\ReviewsOrdering;
|
||
use KupShop\I18nBundle\Translations\ProductsTranslation;
|
||
use KupShop\I18nBundle\Translations\ReviewsTranslation;
|
||
use KupShop\KupShopBundle\Context\CountryContext;
|
||
use KupShop\KupShopBundle\Context\LanguageContext;
|
||
use KupShop\KupShopBundle\Context\UserContext;
|
||
use KupShop\KupShopBundle\Util\Compat\ServiceContainer;
|
||
use KupShop\KupShopBundle\Util\Contexts;
|
||
use KupShop\KupShopBundle\Util\StringUtil;
|
||
use KupShop\RewriteBundle\Util\Rewrite;
|
||
use Query\Operator;
|
||
use Query\QueryBuilder;
|
||
use Query\Translation;
|
||
|
||
class ReviewsUtil
|
||
{
|
||
use \DatabaseCommunication;
|
||
|
||
public const RANK_UNCONFIRMED = 1;
|
||
public const RANK_CONFIRMED = 3;
|
||
public const RANK_TOP = 5;
|
||
public const RANK_DECLINED = 0;
|
||
|
||
public static $rank = [
|
||
self::RANK_UNCONFIRMED => 'not_confirmed',
|
||
self::RANK_CONFIRMED => 'confirmed',
|
||
self::RANK_TOP => 'top',
|
||
self::RANK_DECLINED => 'declined',
|
||
];
|
||
|
||
public static $sources = [
|
||
self::SOURCE_HEUREKA_CZ => self::SOURCE_HEUREKA_CZ,
|
||
self::SOURCE_HEUREKA_SK => self::SOURCE_HEUREKA_SK,
|
||
self::SOURCE_ZBOZI_CZ => self::SOURCE_ZBOZI_CZ,
|
||
self::SOURCE_ARUKERESO_HU => self::SOURCE_ARUKERESO_HU,
|
||
self::SOURCE_COMPARI_RO => self::SOURCE_COMPARI_RO,
|
||
];
|
||
|
||
public const SOURCE_HEUREKA_CZ = 'heureka.cz';
|
||
public const SOURCE_HEUREKA_SK = 'heureka.sk';
|
||
public const SOURCE_ZBOZI_CZ = 'zbozi.cz';
|
||
public const SOURCE_ARUKERESO_HU = 'arukereso.hu';
|
||
public const SOURCE_COMPARI_RO = 'compari.ro';
|
||
|
||
private $error;
|
||
|
||
public function getQueryBuilder(): QueryBuilder
|
||
{
|
||
$verifiedFields = ['r.source', 'r.id_order'];
|
||
|
||
if (findModule(\Modules::SALES)) {
|
||
$verifiedFields[] = 'r.id_sale';
|
||
}
|
||
|
||
$verified = 'IF('.implode(' OR ', $verifiedFields).', 1, 0)';
|
||
|
||
$qb = sqlQueryBuilder()
|
||
->select('r.*', "{$verified} as verified")
|
||
->from('reviews', 'r')
|
||
->leftJoin('r', 'users', 'u', 'u.id = r.id_user')
|
||
->leftJoin('r', 'orders', 'o', 'o.id = r.id_order')
|
||
->orderBy('r.figure', 'DESC')
|
||
->addOrderBy('r.date', 'DESC')
|
||
->groupBy('r.id');
|
||
|
||
$qb->andWhere('r.figure > '.self::RANK_UNCONFIRMED);
|
||
|
||
if (\Settings::getDefault()['review_verified_only'] == 'Y') {
|
||
$qb->andWhere("{$verified} = 1");
|
||
}
|
||
|
||
return $qb;
|
||
}
|
||
|
||
public function getForProduct($id_product, $translationLanguage = null)
|
||
{
|
||
return $this->getProductReviewsBaseQueryBuilder((int) $id_product, $translationLanguage)
|
||
->execute()->fetchAllAssociative();
|
||
}
|
||
|
||
/**
|
||
* Vraci vsechny recenze + k tomu nacte produkty. Pouziva se pro stranku recenze, kde je mozne prochazet vsechny recenze
|
||
* a pak pripadne pro slider s recenzema napr. na hlavni strance kam potrebujeme dostat treba nahodne recenze ze vsech.
|
||
*/
|
||
public function getReviews(?string $translationLanguage = null, array $languages = [], bool $withEmptyReviews = true, ?callable $spec = null): array
|
||
{
|
||
$qb = $this->getProductReviewsQueryBuilder(null, $translationLanguage, $languages, $withEmptyReviews);
|
||
|
||
if ($spec) {
|
||
$qb->andWhere($spec);
|
||
}
|
||
|
||
$reviews = $qb->execute()->fetchAllAssociative();
|
||
|
||
$productIds = array_unique(array_map(fn ($x) => $x['id_product'], $reviews));
|
||
|
||
// fetch products - potrebuju hlavne kvuli obrazkum produktu
|
||
$productList = ServiceContainer::getService(\KupShop\CatalogBundle\ProductList\ProductList::class);
|
||
$productList->andSpec(Operator::inIntArray($productIds, 'p.id'));
|
||
$productList->fetchImages(1);
|
||
$products = $productList->getProducts();
|
||
|
||
foreach ($reviews as &$review) {
|
||
$review['product'] = $products[$review['id_product']] ?? null;
|
||
}
|
||
|
||
// vratim recenze a i product collectionu, aby se pripadne dalo neco dofetchovat
|
||
return [$products, $reviews];
|
||
}
|
||
|
||
public function getReviewsTotals(): array
|
||
{
|
||
$result = $this->getQueryBuilder()
|
||
->select('AVG(r.rating) as rating, COUNT(distinct r.id) as count')
|
||
->resetQueryPart('groupBy')
|
||
->execute()->fetchAssociative();
|
||
|
||
return [
|
||
'rating' => $result['rating'] ?? null,
|
||
'count' => $result['count'] ?? 0,
|
||
];
|
||
}
|
||
|
||
public function getProductReviews(int $productId, ?string $translationLanguage = null, array $languages = [], ?int $limit = null, ?int $offset = null, ?ReviewsOrdering $ordering = null): array
|
||
{
|
||
if (findModule(\Modules::TRANSLATIONS) && $translationLanguage === null) {
|
||
$translationLanguage = Contexts::get(LanguageContext::class)->getActiveId();
|
||
}
|
||
|
||
$qb = $this->getProductReviewsQueryBuilder($productId, $translationLanguage, $languages);
|
||
|
||
if ($limit) {
|
||
$qb->setMaxResults($limit);
|
||
}
|
||
|
||
if ($offset) {
|
||
$qb->setFirstResult($offset);
|
||
}
|
||
|
||
if ($ordering) {
|
||
$qb->orderBy(
|
||
sort: $ordering->orderBy->value,
|
||
order: $ordering->orderDir->uppercase()
|
||
);
|
||
}
|
||
|
||
return $qb->execute()->fetchAllAssociative();
|
||
}
|
||
|
||
public function getProductReviewsCount(int $productId, ?string $translationLanguage, array $languages = []): int
|
||
{
|
||
return (int) $this->getProductReviewsQueryBuilder($productId, $translationLanguage, $languages)
|
||
->select('COUNT(DISTINCT r.id)')
|
||
->groupBy('r.id_product')
|
||
->execute()->fetchOne();
|
||
}
|
||
|
||
/**
|
||
* Returns an overview of product reviews for a given product.
|
||
*
|
||
* The returned array contains ratings (1–5) as keys and the number of reviews for each rating as values.
|
||
*
|
||
* @return array<int, int> Associative array of [rating => count]
|
||
*/
|
||
public function getProductReviewsOverview(int $productId, ?string $translationLanguage, array $languages = []): array
|
||
{
|
||
$result = $this->getProductReviewsQueryBuilder($productId, $translationLanguage, $languages)
|
||
->select('r.rating, COUNT(distinct r.id) as count')
|
||
->groupBy('r.rating')
|
||
->execute()->fetchAllKeyValue();
|
||
|
||
$overview = [];
|
||
|
||
foreach (range(5, 1) as $i) {
|
||
$overview[$i] = $result[$i] ?? 0;
|
||
}
|
||
|
||
return $overview;
|
||
}
|
||
|
||
protected function getProductReviewsQueryBuilder(?int $productId, ?string $translationLanguage, array $languages = [], bool $withEmptyReviews = false): QueryBuilder
|
||
{
|
||
$qb = $this->getProductReviewsBaseQueryBuilder($productId, $translationLanguage);
|
||
|
||
if (!$withEmptyReviews) {
|
||
// odfiltruju pryc recenze bez textu
|
||
$qb->andWhere(
|
||
$this->emptyReviewSpec()
|
||
);
|
||
}
|
||
|
||
// ofiltruju recenze podle jazyka
|
||
if ($translationLanguage) {
|
||
$filterLanguages = [$translationLanguage];
|
||
if (!empty($languages[$translationLanguage])) {
|
||
$filterLanguages = array_merge($filterLanguages, $languages[$translationLanguage]);
|
||
}
|
||
|
||
// chci recenze pouze pro aktualni jazyk
|
||
$qb->andWhere(
|
||
Operator::orX(
|
||
// bud pouze recenze, ktere jsou primo v danem jazyce
|
||
Operator::inStringArray($filterLanguages, 'r.id_language'),
|
||
// nebo recenze, ktere jsou prelozene
|
||
Operator::inStringArray($filterLanguages, "r_{$translationLanguage}.id_language"),
|
||
)
|
||
);
|
||
}
|
||
|
||
return $qb;
|
||
}
|
||
|
||
protected function getProductReviewsBaseQueryBuilder(?int $productId, ?string $translationLanguage = null): QueryBuilder
|
||
{
|
||
$qb = $this->getQueryBuilder()
|
||
->addSelect('COALESCE(u.name, o.invoice_name) user_name, COALESCE(u.city, o.invoice_city) user_city,
|
||
u.gender as user_gender');
|
||
|
||
if ($translationLanguage) {
|
||
$qb->andWhere(Translation::coalesceTranslatedFields(ReviewsTranslation::class, ['pros' => 'pros', 'cons' => 'cons', 'summary' => 'summary', 'response' => 'response', 'id_language' => 'translation_language'], $translationLanguage))
|
||
->addSelect('r.pros as pros_original, r.cons as cons_original, r.summary as summary_original, r.response as response_original');
|
||
}
|
||
|
||
if ($productId !== null) {
|
||
if (findModule(\Modules::PRODUCTS_COLLECTIONS) && ('Y' == (\Settings::getDefault()['review_collections'] ?? 'Y'))) {
|
||
$qb->addSelect('pc.id AS id_product_master, p.title AS product_title')
|
||
->joinSubQuery('r', \Query\Product::collectionsProductsSubQuery([$productId]), 'pc',
|
||
'r.id_product = pc.id OR r.id_product = pc.main_product OR r.id_product = pc.related_product')
|
||
->join('r', 'products', 'p', 'p.id = r.id_product')
|
||
->groupBy('r.id');
|
||
|
||
if ($translationLanguage) {
|
||
$qb->andWhere(Translation::coalesceTranslatedFields(
|
||
ProductsTranslation::class, ['title' => 'product_title'], $translationLanguage)
|
||
);
|
||
}
|
||
} else {
|
||
$qb->andWhere(Operator::inIntArray([$productId], 'r.id_product'));
|
||
}
|
||
}
|
||
|
||
return $qb;
|
||
}
|
||
|
||
public function hasComment($review)
|
||
{
|
||
return !empty($review['summary']) || !empty($review['pros']) || !empty($review['cons']);
|
||
}
|
||
|
||
public function hasTranslatedComment($review)
|
||
{
|
||
return ($review['translation_language'] ?? '') != $review['id_language'];
|
||
}
|
||
|
||
public function hasUserAlreadyRated($id_product, $id_user = null)
|
||
{
|
||
if (!$id_user) {
|
||
$id_user = Contexts::get(UserContext::class)->getActiveId();
|
||
}
|
||
|
||
if (!$id_user) {
|
||
return false;
|
||
}
|
||
|
||
return $this->selectSQL('reviews', ['id_user' => $id_user, 'id_product' => $id_product])->rowCount() > 0;
|
||
}
|
||
|
||
public function hasUserBoughtProduct($id_product, $id_user = null, $email = null, $orderNo = null)
|
||
{
|
||
if (!$id_user && !$email) {
|
||
if (!($id_user = Contexts::get(UserContext::class)->getActiveId())) {
|
||
return false;
|
||
}
|
||
}
|
||
|
||
if (!($result = $this->hasUserBoughtProductByType((int) $id_product, 'order', $id_user, $email, $orderNo))) {
|
||
$result = $this->hasUserBoughtProductByType((int) $id_product, 'sale', $id_user, $email, $orderNo);
|
||
}
|
||
|
||
return $result;
|
||
}
|
||
|
||
private function hasUserBoughtProductByType(int $productId, string $type = 'order', ?int $userId = null, ?string $email = null, ?string $code = null): ?array
|
||
{
|
||
if ($type === 'sale') {
|
||
if (!findModule(\Modules::SALES)) {
|
||
return null;
|
||
}
|
||
|
||
$qb = sqlQueryBuilder()
|
||
->select('s.id, si.id_product, "sale" as type')
|
||
->from('sales_items', 'si')
|
||
->join('si', 'sales', 's', 's.id = si.id_sale')
|
||
->leftJoin('s', 'users', 'u', 'u.id = s.id_user')
|
||
->andWhere(static::getUserBoughtProductSpec($productId, $userId, 's', 'si'));
|
||
|
||
if ($email && $code) {
|
||
$qb->andWhere(Operator::equals(['s.code' => $code, 'u.email' => $email]));
|
||
}
|
||
|
||
return $qb->execute()->fetchAssociative() ?: null;
|
||
}
|
||
|
||
$qb = sqlQueryBuilder()->select('o.id, oi.id_product, "order" as type, order_no')
|
||
->from('order_items', 'oi')
|
||
->join('oi', 'orders', 'o', 'oi.id_order = o.id')
|
||
->andWhere(static::getUserBoughtProductSpec($productId, $userId));
|
||
|
||
if ($email && $code) {
|
||
$qb->andWhere(Operator::equals(['o.order_no' => $code, 'o.invoice_email' => $email]));
|
||
}
|
||
|
||
return $qb->execute()->fetchAssociative() ?: null;
|
||
}
|
||
|
||
private static function getUserBoughtProductSpec(int $productId, ?int $userId, string $objectAlias = 'o', string $itemsAlias = 'oi'): callable
|
||
{
|
||
return function (QueryBuilder $qb) use ($productId, $userId, $objectAlias, $itemsAlias) {
|
||
$andX = [];
|
||
|
||
if ($userId) {
|
||
$andX[] = Operator::equals(["{$objectAlias}.id_user" => $userId]);
|
||
}
|
||
|
||
if (findModule(\Modules::PRODUCTS_COLLECTIONS) && ('Y' == (\Settings::getDefault()['review_collections'] ?? 'Y'))) {
|
||
$qb->setParameter('ids', [$productId], \Doctrine\DBAL\Connection::PARAM_INT_ARRAY)
|
||
->orderBy("{$itemsAlias}.id_product = {$productId}", 'DESC');
|
||
|
||
$collectionProducts = \Query\Product::inCollectionsProducts($qb, [$productId]);
|
||
|
||
$andX[] = Operator::orX(
|
||
Operator::equals(["{$itemsAlias}.id_product" => $productId]),
|
||
"{$itemsAlias}.id_product IN ({$collectionProducts})"
|
||
);
|
||
} else {
|
||
$andX[] = Operator::equals(["{$itemsAlias}.id_product" => $productId]);
|
||
}
|
||
|
||
return Operator::andX($andX);
|
||
};
|
||
}
|
||
|
||
/**
|
||
* @return bool
|
||
*
|
||
* @throws Exception
|
||
*/
|
||
public function importReviews($key = null)
|
||
{
|
||
$count = 0;
|
||
if (empty($key)) {
|
||
$dbcfg = \Settings::getDefault();
|
||
|
||
if (!empty($dbcfg['analytics']['heureka_overeno']['ID'])) {
|
||
$key = $dbcfg['analytics']['heureka_overeno']['ID'];
|
||
$count += $this->getHeurekaReviewsByKey($key, 'cs', 'https://www.heureka.cz/direct/dotaznik/export-product-review.php?key=');
|
||
}
|
||
|
||
if (findModule(\Modules::TRANSLATIONS)) {
|
||
$skSettings = \Settings::getFromCache('sk');
|
||
$heurekaSkKey = $skSettings['analytics']['heureka_overeno']['ID'] ?? null;
|
||
} else {
|
||
$heurekaSkKey = $dbcfg->analytics['heureka_overeno']['ID_SK'] ?? null;
|
||
}
|
||
|
||
if (!empty($heurekaSkKey)) {
|
||
$count += $this->getHeurekaReviewsByKey($heurekaSkKey, 'sk', 'https://www.heureka.sk/direct/dotaznik/export-product-review.php?key=');
|
||
}
|
||
}
|
||
|
||
$zbozi_cz = $dbcfg['analytics']['zbozi_cz_feedback'] ?? null;
|
||
if (!empty($zbozi_cz['API_key'])) {
|
||
$key = $zbozi_cz['API_key'];
|
||
$id = $zbozi_cz['ID'];
|
||
$this->importReviewsZbozi($key, $id);
|
||
}
|
||
|
||
foreach (array_intersect(array_keys(Contexts::get(CountryContext::class)->getAll()), ['HU', 'RO']) as $lang) {
|
||
$lang = strtolower($lang);
|
||
$dbcfg = \Settings::getFromCache($lang);
|
||
if (!empty($dbcfg['analytics']['trusted_shop']['id'])) {
|
||
$count += $this->importArukeresoAndCompari($dbcfg['analytics']['trusted_shop']['id'], $lang);
|
||
}
|
||
}
|
||
|
||
return $count;
|
||
}
|
||
|
||
private function importArukeresoAndCompari(string $key, string $lang)
|
||
{
|
||
$savedCount = 0;
|
||
$url = $lang == 'hu' ? 'https://www.arukereso.hu/admin/gettrustedproductreview.php?t=xml&wak=' : 'https://www.compari.ro/admin/gettrustedproductreview.php?t=xml&wak=';
|
||
$res = curl_init();
|
||
curl_setopt($res, CURLOPT_SSL_VERIFYPEER, false);
|
||
curl_setopt($res, CURLOPT_RETURNTRANSFER, true);
|
||
curl_setopt($res, CURLOPT_URL, $url.$key);
|
||
|
||
$result = curl_exec($res);
|
||
|
||
curl_close($res);
|
||
|
||
try {
|
||
$xml = simplexml_load_string($result);
|
||
} catch (\Exception $e) {
|
||
$xml = false;
|
||
}
|
||
|
||
if ($xml === false) {
|
||
return false;
|
||
}
|
||
|
||
sqlGetConnection()->transactional(function () use ($xml, &$savedCount, $lang) {
|
||
$reviewIDsForImport = [];
|
||
$skipIDs = [];
|
||
|
||
foreach ($xml->ProductReview as $review) {
|
||
$reviewIDsForImport[] = $this->getExternalId($review->ReviewDate, $lang);
|
||
}
|
||
|
||
$result = sqlQueryBuilder()->select('external_id')->from('reviews')
|
||
->where(Operator::inIntArray($reviewIDsForImport, 'external_id'))
|
||
->andWhere(Operator::equals(['source' => $lang == 'hu' ? self::SOURCE_ARUKERESO_HU : self::SOURCE_COMPARI_RO]))
|
||
->execute()->fetchAll();
|
||
foreach ($result as $row) {
|
||
$skipIDs[] = $row['external_id'];
|
||
}
|
||
|
||
foreach ($xml->ProductReview as $review) {
|
||
$externalId = $this->getExternalId($review->ReviewDate, $lang);
|
||
|
||
if (in_array($externalId, $reviewIDsForImport)
|
||
&& !in_array($externalId, $skipIDs)
|
||
&& $this->selectSQL('products', ['id' => $review->ItemCode])->rowCount()
|
||
) {
|
||
$values = [
|
||
'external_id' => $externalId,
|
||
'id_product' => trim((string) $review->ItemCode),
|
||
'figure' => static::RANK_UNCONFIRMED,
|
||
'date' => isset($review->ReviewDate) ? trim((string) $review->ReviewDate) : null,
|
||
'rating' => isset($review->SUM_Values) ? (float) $review->SUM_Values : null,
|
||
'pros' => isset($review->Pros) ? trim((string) $review->Pros) : null,
|
||
'cons' => isset($review->Cons) ? trim((string) $review->Cons) : null,
|
||
'summary' => isset($review->AspectsOfChoice) ? trim((string) $review->AspectsOfChoice) : null,
|
||
'source' => $lang == 'hu' ? self::SOURCE_ARUKERESO_HU : self::SOURCE_COMPARI_RO,
|
||
];
|
||
|
||
if (findModule(\Modules::TRANSLATIONS)) {
|
||
$values['id_language'] = $lang;
|
||
}
|
||
|
||
$this->insertSQL('reviews', $values);
|
||
|
||
$skipIDs[] = $externalId;
|
||
$savedCount++;
|
||
}
|
||
}
|
||
});
|
||
|
||
return $savedCount;
|
||
}
|
||
|
||
private function getExternalId($string, $lang)
|
||
{
|
||
// compari a arukereso nemaji id hodnoceni, tak si vytvorime z datumu a jazyka
|
||
return $lang.'_'.$string;
|
||
}
|
||
|
||
public function autoConfirm()
|
||
{
|
||
$dbcfg = \Settings::getDefault();
|
||
|
||
if (!empty($dbcfg->review_automatic_confirm)) {
|
||
return sqlQueryBuilder()->update('reviews')
|
||
->set('figure', self::RANK_CONFIRMED)
|
||
->where('date < DATE_SUB(NOW(), INTERVAL :review_automatic_confirm DAY)')
|
||
->andWhere('figure = '.self::RANK_UNCONFIRMED)
|
||
->setParameter('review_automatic_confirm', $dbcfg->review_automatic_confirm)
|
||
->execute();
|
||
}
|
||
|
||
return 0;
|
||
}
|
||
|
||
public function importReviewsZbozi($key, $id)
|
||
{
|
||
// load data from url
|
||
$res = curl_init();
|
||
curl_setopt($res, CURLOPT_SSL_VERIFYPEER, false);
|
||
curl_setopt($res, CURLOPT_RETURNTRANSFER, true);
|
||
curl_setopt($res, CURLOPT_URL, 'https://api.zbozi.cz/v1/shop/product-reviews?limit=1000');
|
||
curl_setopt($res, CURLOPT_USERPWD, $id.':'.$key);
|
||
|
||
$result = curl_exec($res);
|
||
|
||
curl_close($res);
|
||
|
||
$json_data = json_decode($result, true);
|
||
if (empty($data = ($json_data['data'] ?? null))) {
|
||
return;
|
||
}
|
||
|
||
$reviewIDsForImport = array_map(function ($call) {
|
||
return $call['productReviewId'];
|
||
}, $data);
|
||
// nechceme importovat duplicity po uprave delky external_id v tabulce reviews
|
||
// predtim se external_id orezavalo na 32 znaky
|
||
$reviewIDsForImport32 = array_map(function ($productReviewId) {
|
||
return substr($productReviewId, 0, 32);
|
||
}, $reviewIDsForImport);
|
||
$reviewIDsForImport = array_merge($reviewIDsForImport, $reviewIDsForImport32);
|
||
|
||
// ignore already saved reviews
|
||
$skipIDs = sqlFetchAll(sqlQueryBuilder()->select('external_id')->from('reviews')
|
||
->where(Operator::inStringArray($reviewIDsForImport, 'external_id'))
|
||
->andWhere(Operator::equals(['source' => self::SOURCE_ZBOZI_CZ]))
|
||
->execute(),
|
||
'external_id');
|
||
// save new reviews
|
||
foreach ($data as $values) {
|
||
if (!isset($skipIDs[$values['productReviewId']]) && !isset($skipIDs[substr($values['productReviewId'], 0, 32)])) {
|
||
// save reviews when have id of product/ variation
|
||
if (findModule('marketing', 'product_identifier') == 'code') {
|
||
$product = sqlQueryBuilder()->select('p.id as id_product, pv.id as id_variation')
|
||
->fromProducts()
|
||
->joinVariationsOnProducts()
|
||
->where(Operator::equals(['p.code' => $values['productData']['itemId'],
|
||
'pv.code' => $values['productData']['itemId'], ], 'OR'))
|
||
->execute()->fetch();
|
||
if (!$product) {
|
||
continue;
|
||
}
|
||
} else {
|
||
$productID = explode('_', $values['productData']['itemId']);
|
||
$product = ['id_product' => $productID[0], 'id_variation' => isset($productID[1]) ? $productID[1] : null];
|
||
}
|
||
$saving_reviews = [
|
||
'external_id' => $values['productReviewId'],
|
||
'id_product' => isset($product['id_product']) ? $product['id_product'] : $values['productData']['itemId'],
|
||
'id_product_variation' => isset($product['id_variation']) ? $product['id_variation'] : null,
|
||
'figure' => static::RANK_UNCONFIRMED,
|
||
'date' => $this->prepareDateTime((new \DateTime())->setTimestamp((int) $values['createTimestamp'])),
|
||
'rating' => isset($values['ratingStars']) ? (float) $values['ratingStars'] : null,
|
||
'pros' => isset($values['positiveComments']) ? trim((string) $values['positiveComments']) : null,
|
||
'cons' => isset($values['negativeComments']) ? trim((string) $values['negativeComments']) : null,
|
||
'summary' => isset($values['text']) ? trim((string) $values['text']) : null,
|
||
'name' => isset($values['userName']) ? trim((string) $values['userName']) : null,
|
||
'source' => self::SOURCE_ZBOZI_CZ,
|
||
];
|
||
try {
|
||
$this->insertSQL('reviews', $saving_reviews);
|
||
} catch (\Exception $e) {
|
||
break;
|
||
}
|
||
$skipIDs[$values['productReviewId']] = 1;
|
||
}
|
||
}
|
||
}
|
||
|
||
private function getHeurekaReviewsByKey($key, $lang, $url)
|
||
{
|
||
$res = curl_init();
|
||
curl_setopt($res, CURLOPT_SSL_VERIFYPEER, false);
|
||
curl_setopt($res, CURLOPT_RETURNTRANSFER, true);
|
||
// curl_setopt($res, CURLOPT_FOLLOWLOCATION, true); // Does not work in open_basedir
|
||
curl_setopt($res, CURLOPT_URL, $url.$key);
|
||
|
||
$result = curl_exec($res);
|
||
|
||
curl_close($res);
|
||
|
||
try {
|
||
$xml = simplexml_load_string($result);
|
||
} catch (\Exception $e) {
|
||
$xml = false;
|
||
}
|
||
|
||
if ($xml === false) {
|
||
if (StringUtil::startsWith($result, 'INFO')) {
|
||
$this->error = $result;
|
||
} else {
|
||
$this->error = $key.' -> Invalid xml';
|
||
echo $key.' -> Invalid xml<br/>';
|
||
}
|
||
|
||
return false;
|
||
}
|
||
|
||
$savedCount = 0;
|
||
|
||
sqlGetConnection()->transactional(function () use ($xml, &$savedCount, $lang) {
|
||
$reviewIDsForImport = [];
|
||
// rating_id is not as unique as it should be, so we skip duplicates
|
||
$skipIDs = [];
|
||
|
||
foreach ($xml->product as $product) {
|
||
foreach ($product->reviews->children() as $review) {
|
||
if (trim((string) $review->rating_id_type) === 'offer') {
|
||
// https://sluzby.heureka.cz/napoveda/widget-a-ikonky-ze-sluzby-overeno-zakazniky/
|
||
// rating_id_type (offer|product) - Recenze je vázána na nabídku (offer) nebo na produkt - ovlivňuje rating_id.
|
||
continue; // ignore offer reviews to avoid duplicates
|
||
}
|
||
$reviewIDsForImport[] = (int) $review->rating_id;
|
||
}
|
||
}
|
||
|
||
// ignore already saved reviews
|
||
$result = sqlQueryBuilder()->select('external_id')->from('reviews')
|
||
->where(Operator::inIntArray($reviewIDsForImport, 'external_id'))
|
||
->andWhere(Operator::equals(['source' => $lang == 'cs' ? self::SOURCE_HEUREKA_CZ : self::SOURCE_HEUREKA_SK]))
|
||
->execute()->fetchAll();
|
||
foreach ($result as $row) {
|
||
$skipIDs[] = $row['external_id'];
|
||
}
|
||
|
||
// save new reviews
|
||
foreach ($xml->product as $product) {
|
||
$shop_product = null;
|
||
|
||
foreach ($product->reviews->children() as $review) {
|
||
if (in_array((int) $review->rating_id, $reviewIDsForImport)
|
||
&& !in_array((int) $review->rating_id, $skipIDs)
|
||
) {
|
||
if (!$shop_product) {
|
||
$shop_product = $this->findReviewProduct($product);
|
||
|
||
if (!$shop_product) {
|
||
break;
|
||
}
|
||
}
|
||
|
||
$order = $this->findReviewOrder($product);
|
||
|
||
$values = [
|
||
'external_id' => (int) $review->rating_id,
|
||
'id_order' => $order ? $order->id : null,
|
||
'id_user' => $order ? $order->id_user : null,
|
||
'id_product' => $shop_product['id_product'],
|
||
'id_product_variation' => $shop_product['id_variation'],
|
||
'figure' => static::RANK_UNCONFIRMED,
|
||
'date' => $this->prepareDateTime((new \DateTime())->setTimestamp((int) $review->unix_timestamp)),
|
||
'rating' => isset($review->rating) ? (float) $review->rating : null,
|
||
'recommends' => isset($review->recommends) ? (int) $review->recommends : null,
|
||
'pros' => isset($review->pros) ? trim((string) $review->pros) : null,
|
||
'cons' => isset($review->cons) ? trim((string) $review->cons) : null,
|
||
'summary' => isset($review->summary) ? trim((string) $review->summary) : null,
|
||
'name' => isset($review->name) ? trim((string) $review->name) : null,
|
||
'source' => $lang == 'cs' ? self::SOURCE_HEUREKA_CZ : self::SOURCE_HEUREKA_SK,
|
||
];
|
||
|
||
if (findModule(\Modules::TRANSLATIONS)) {
|
||
$values['id_language'] = $lang;
|
||
}
|
||
|
||
$this->insertSQL('reviews', $values);
|
||
|
||
$skipIDs[] = (int) $review->rating_id;
|
||
$savedCount++;
|
||
}
|
||
}
|
||
}
|
||
});
|
||
|
||
return $savedCount;
|
||
}
|
||
|
||
public function removeDuplicates()
|
||
{
|
||
$conn = sqlGetConnection();
|
||
try {
|
||
$conn->beginTransaction();
|
||
|
||
sqlQuery('DELETE r1 FROM reviews r1, reviews r2
|
||
WHERE (
|
||
(r1.id_product > 0 AND r2.id_product > 0) AND r1.id_product = r2.id_product
|
||
OR r1.id_product_variation = r2.id_product_variation
|
||
)
|
||
AND r1.rating = r2.rating
|
||
AND (r2.summary IS NOT NULL OR r2.pros IS NOT NULL OR r2.cons IS NOT NULL)
|
||
AND ((r1.summary IS NULL AND r2.summary IS NULL) OR r1.summary = r2.summary)
|
||
AND ((r1.pros IS NULL AND r2.pros IS NULL) OR r1.pros = r2.pros)
|
||
AND ((r1.cons IS NULL AND r2.cons IS NULL) OR r1.cons = r2.cons)
|
||
AND r1.external_id IS NULL AND r2.external_id IS NOT NULL;
|
||
|
||
DELETE r1 FROM reviews r1, reviews r2
|
||
WHERE (
|
||
(r1.id_product > 0 AND r2.id_product > 0) AND r1.id_product = r2.id_product
|
||
OR r1.id_product_variation = r2.id_product_variation
|
||
)
|
||
AND r1.rating = r2.rating
|
||
AND (r2.summary IS NOT NULL OR r2.pros IS NOT NULL OR r2.cons IS NOT NULL)
|
||
AND ((r1.summary IS NULL AND r2.summary IS NULL) OR r1.summary = r2.summary)
|
||
AND ((r1.pros IS NULL AND r2.pros IS NULL) OR r1.pros = r2.pros)
|
||
AND ((r1.cons IS NULL AND r2.cons IS NULL) OR r1.cons = r2.cons)
|
||
AND r1.date < r2.date;
|
||
|
||
DELETE r1 FROM reviews r1, reviews r2
|
||
WHERE (
|
||
(r1.id_product > 0 AND r2.id_product > 0) AND r1.id_product = r2.id_product
|
||
OR r1.id_product_variation = r2.id_product_variation
|
||
)
|
||
AND r1.rating = r2.rating
|
||
AND (r2.summary IS NOT NULL OR r2.pros IS NOT NULL OR r2.cons IS NOT NULL)
|
||
AND ((r1.summary IS NULL AND r2.summary IS NULL) OR r1.summary = r2.summary)
|
||
AND ((r1.pros IS NULL AND r2.pros IS NULL) OR r1.pros = r2.pros)
|
||
AND ((r1.cons IS NULL AND r2.cons IS NULL) OR r1.cons = r2.cons)
|
||
AND r1.id < r2.id;
|
||
');
|
||
|
||
$conn->commit();
|
||
} catch (\Exception $e) {
|
||
$conn->rollBack();
|
||
throw $e;
|
||
}
|
||
}
|
||
|
||
public function findReviewProduct($product)
|
||
{
|
||
$regex = '@_z(\d+)([^=]+=(\d+))?@i';
|
||
|
||
if (!($matchOK = preg_match($regex, (string) $product->url, $matches))) {
|
||
// also try canonical version of product URL
|
||
if (findModule(\Modules::DB_REWRITE)) {
|
||
$rewriteBundle = ServiceContainer::getService(Rewrite::class);
|
||
$newUrl = $rewriteBundle->resolve(parse_url((string) $product->url, PHP_URL_PATH));
|
||
} else {
|
||
$rewrite = new \Rewrite();
|
||
$newUrl = $rewrite->resolve(parse_url((string) $product->url, PHP_URL_PATH));
|
||
}
|
||
$matches = null;
|
||
$matchOK = $newUrl ? preg_match($regex, $newUrl, $matches) : false;
|
||
}
|
||
|
||
if ($matchOK) {
|
||
$product = ['id_variation' => $matches[3] ?? null, 'id_product' => $matches[1]];
|
||
|
||
if ($product['id_variation']) {
|
||
if ($this->selectSQL('products_variations',
|
||
['id' => $product['id_variation'], 'id_product' => $product['id_product']])->rowCount()
|
||
) {
|
||
return $product;
|
||
}
|
||
|
||
$product['id_variation'] = null;
|
||
}
|
||
|
||
if ($this->selectSQL('products', ['id' => $product['id_product']])->rowCount()) {
|
||
return $product;
|
||
}
|
||
}
|
||
|
||
return null;
|
||
}
|
||
|
||
/**
|
||
* @return \Order|null
|
||
*/
|
||
private function findReviewOrder($product)
|
||
{
|
||
try {
|
||
return \Order::createFromDbOrderNo($product->order_id);
|
||
} catch (\InvalidArgumentException $e) {
|
||
return null;
|
||
}
|
||
}
|
||
|
||
public function getError()
|
||
{
|
||
return $this->error;
|
||
}
|
||
|
||
protected function emptyReviewSpec(): string|callable
|
||
{
|
||
return Operator::orX(
|
||
'r.summary IS NOT NULL AND r.summary != ""',
|
||
'r.pros IS NOT NULL AND r.pros != ""',
|
||
'r.cons IS NOT NULL AND r.cons != ""'
|
||
);
|
||
}
|
||
}
|