/src/Domain/Product/Repository/OfferRepository.php

<?php

namespace App\Domain\Product\Repository;

use App\Domain\Category\Entity\Category;
use App\Domain\City\Entity\City;
use App\Domain\Product\Collection\OfferCollection;
use App\Domain\Product\Entity\Offer;
use App\Domain\Product\Entity\Product;
use App\Domain\Product\Entity\Price;
use App\Domain\Product\Entity\ProductInfo;
use App\Domain\Shop\Entity\Shop;
use App\Traits\QueryBuilderTrait;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\ORM\Query\Expr\Join;
use Doctrine\ORM\QueryBuilder;
use Doctrine\Persistence\ManagerRegistry;

/**
 * @method Offer|null find($id, $lockMode = null, $lockVersion = null)
 * @method Offer|null findOneBy(array $criteria, array $orderBy = null)
 * @method Offer[]    findAll()
 */
class OfferRepository extends ServiceEntityRepository
{
    use QueryBuilderTrait;

    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, Offer::class);
    }

    public function findBy(
        array $criteria,
        array $orderBy = null,
        $limit = null,
        $offset = null
    ): OfferCollection {
        return new OfferCollection(
            parent::findBy(
                $criteria,
                $orderBy,
                $limit,
                $offset
            )
        );
    }


    public function getProductsWithOffersByCityAndCategory(City $city, Category $category): array
    {
        $offers = [];
        $data = $this->getOffersByCityAndCategory($city, $category);
        foreach ($data as $offer) {
            $offers[$offer['product_id']][$offer['id']] = $offer;
            unset($offers[$offer['product_id']][$offer['id']]['product_id']);
        }

        $products = $this->getProductsByIds(array_keys($offers));

        foreach ($products as &$row) {
            $row['offers'] = array_values($offers[$row['id']]);
        }

        return $products;
    }

    public function getProductsWithOffersByCityAndQuery(City $city, string $query, ?int $offset = 0): array
    {
        $offers = [];
        $data = $this->getOffersByCityAndQuery($city, $query, $offset);
        foreach ($data as $offer) {
            $offers[$offer['product_id']][$offer['id']] = $offer;
            unset($offers[$offer['product_id']][$offer['id']]['product_id']);
        }

        $products = $this->getProductsByIds(array_keys($offers));

        foreach ($products as &$row) {
            $row['offers'] = array_values($offers[$row['id']]);
        }

        return $products;
    }

    public function getOffersByCityAndCategory(City $city, Category $category): array
    {
        $shopsIds = $city->getShops()->getIds();
        $categoryIds = $category->getIds();

        $qb = $this->getEntityManager()->createQueryBuilder();
        $qb
            ->select('offer')
            ->addSelect('partial price.{id, shop, quantity, actual, old}')
            ->from(Price::class, 'price')
            ->join(
                Offer::class,
                'offer',
                Join::WITH,
                $qb->expr()->eq('price.offer', 'offer')
            )
            ->join(
                Product::class,
                'product',
                Join::WITH,
                $qb->expr()->andX(
                    $qb->expr()->in('product.category', $categoryIds),
                    $qb->expr()->eq('product', 'offer.product'),
                )
            )
            ->where($qb->expr()->in('price.shop', $shopsIds));

        $sql = $qb->getQuery()->getSQL();

        $result = $this->getEntityManager()
            ->getConnection()->executeQuery($sql)->fetchAllAssociative();

        $result = $this->clearKeys($result);

        $offers = [];
        foreach ($result as $row) {
            if (empty($offers[$row['product_id']][$row['id']])) {
                $offers[$row['id']] = array_filter([
                    'id' => (int) $row['id'],
                    'name' => $row['name'],
                    'product_id' => (int) $row['product_id'],
                    'barcode' => $row['bar_code'],
                ]);
            }
            $offers[$row['id']]['prices'][] = array_filter([
                'actual' => (int) $row['actual'],
                'old' => (int) $row['old'],
                'shop' => (int) $row['shop_id'],
            ]);
        }

        return $offers;
    }

    public function getOffersByCityAndQuery(City $city, string $query, ?int $offset = 0): array
    {
        $shopsIds = $city->getShops()->map(function (Shop $shop): int {
            return $shop->getId();
        })->toArray();
        sort($shopsIds);

        $qb = $this->getEntityManager()->createQueryBuilder();
        $qb
            ->select('offer')
            ->addSelect('partial price.{id, shop, quantity, actual, old}')
            ->from(Price::class, 'price')
            ->join(
                Offer::class,
                'offer',
                Join::WITH,
                $qb->expr()->eq('price.offer', 'offer')
            )
            ->join(
                Product::class,
                'product',
                Join::WITH,
                'product = offer.product'
            )

            ->join(
                ProductInfo::class,
                'info',
                Join::WITH,
                'info.product = product AND info.name LIKE \'%:query%\''
            )
            ->where($qb->expr()->in('price.shop', $shopsIds))
            ->setMaxResults(60)
            ->setFirstResult($offset);
        $qb->set(':query', $query);

        $sql = $qb->getQuery()->getSQL();

        $result = $this->getEntityManager()
            ->getConnection()->executeQuery($sql)->fetchAllAssociative();
        $result = $this->clearKeys($result);

        $offers = [];
        foreach ($result as $row) {
            if (empty($offers[$row['product_id']][$row['id']])) {
                $offers[$row['id']] = [
                    'id' => (int) $row['id'],
                    'product_id' => $row['product_id'],
                    'barcode' => $row['bar_code'],
                ];
            }
            $offers[$row['id']]['prices'][] = [
                'actual' => (int) $row['actual'],
                'old' => (int) $row['old'],
                'shop' => (int) $row['shop_id'],
            ];
        }

        return $offers;
    }

    public function getOffersByCityAndProduct(City $city, Product $product): array
    {
        $shopsIds = $city->getShops()->map(function (Shop $shop): int {
            return $shop->getId();
        })->toArray();
        sort($shopsIds);

        $qb = $this->getEntityManager()->createQueryBuilder();
        $qb
            ->select('offer')
            ->addSelect('partial price.{id, shop, quantity, actual, old}')
            ->from(Price::class, 'price')
            ->join(
                Offer::class,
                'offer',
                Join::WITH,
                $qb->expr()->eq('price.offer', 'offer')
            )
            ->join(
                Product::class,
                'product',
                Join::WITH,
                $qb->expr()->eq('offer.product', $product->getId())
            )
            ->where($qb->expr()->in('price.shop', $shopsIds));

        $sql = $qb->getQuery()->getSQL();

        $result = $this->getEntityManager()
            ->getConnection()->executeQuery($sql)->fetchAllAssociative();
        $result = $this->clearKeys($result);

        $offers = [];
        foreach ($result as $row) {
            if (empty($offers[$row['product_id']][$row['id']])) {
                $offers[$row['id']] = [
                    'id' => (int) $row['id'],
                    'product_id' => $row['product_id'],
                    'barcode' => $row['bar_code'],
                ];
            }
            $offers[$row['id']]['prices'][] = [
                'actual' => (int) $row['actual'],
                'old' => (int) $row['old'],
                'shop' => (int) $row['shop_id'],
            ];
        }

        return $offers;
    }

    public function getOffersAndProductsIdsBy(City $city, Category $category): array
    {
        $shopsIds = $city->getShops()->map(function (Shop $shop): int {
            return $shop->getId();
        })->toArray();
        sort($shopsIds);

        $categoryIds = $category->getIds();
        sort($categoryIds);
        $categoryIds = array_unique($categoryIds);

        $qb = $this->getEntityManager()->createQueryBuilder();
        $qb
            ->select('partial offer.{id, product}')
            ->from(Price::class, 'price')
            ->join(
                Offer::class,
                'offer',
                Join::WITH,
                $qb->expr()->eq('price.offer', 'offer')
            )
            ->join(
                Product::class,
                'product',
                Join::WITH,
                $qb->expr()->andX(
                    $qb->expr()->in('product.category', $categoryIds),
                    $qb->expr()->eq('product', 'offer.product'),
                )
            )
            ->where($qb->expr()->in('price.shop', $shopsIds));

        $sql = $qb->getQuery()->getSQL();

        $result = $this->getEntityManager()
            ->getConnection()->executeQuery($sql)->fetchAllAssociative();

        $result = $this->clearKeys($result);

        $offers = [];
        foreach ($result as $k => $row) {
            $offers[$row['id']] = (int) $row['product_id'];
            unset($result[$k]);
        }

        return $offers;
    }

    private function getProductsByIds(array $productIds): array
    {
        if (empty($productIds)) {
            $productIds[] = 0;
        }
        sort($productIds);

        $qb = $this->getEntityManager()->createQueryBuilder();
        $qb
            ->select('product')
            ->addSelect('info')
            ->from(Product::class, 'product')
            ->join(ProductInfo::class, 'info', Join::WITH, $qb->expr()->eq('info.product', 'product'))
            ->where($qb->expr()->in('product.id', $productIds));

        $sql = $qb->getQuery()->getSQL();

        $result = $this->getEntityManager()
            ->getConnection()->executeQuery($sql)->fetchAllAssociative();

        $result = $this->clearKeys($result);

        foreach ($result as &$row) {
            $row = [
                'id' => (int) $row['id'],
                'category' => (int) $row['category_id'],
                'name' => $row['name'],
                'slug' => $row['slug'],
                'article' => $row['article'],
                'code' => $row['code'],
                'weight' => $row['weight'],
                'image' => $row['code'],
                'images' => [],
            ];
        }

        return $result;
    }

    public function createOfferQueryBuilder(City $city, array $categoriesIds): QueryBuilder
    {
        $shopsIds = $city->getShops()->getIds();

        $qb = $this->getEntityManager()->createQueryBuilder();

        $qb
            ->select('partial offer.{id, product}')
            ->from(Price::class, 'price')
            ->join(
                Offer::class,
                'offer',
                Join::WITH,
                $qb->expr()->eq('price.offer', 'offer')
            )
            ->join(
                Product::class,
                'product',
                Join::WITH,
                $qb->expr()->andX(
                    $qb->expr()->in('product.category', $categoriesIds),
                    $qb->expr()->eq('product', 'offer.product'),
                )
            )
            ->where($qb->expr()->in('price.shop', $shopsIds));

        return $qb;
    }

    public function getRelatedProductsWithOffers(City $city, Product $product): array
    {
        $related = $product->getRelated();
        if ($related->count()) {
            return $related->toArray();
        }

        $categories = $product->getCategory()->getRelated();
        if (!$categories->count()) {
            return [];
        }

        $sql = $this
            ->createOfferQueryBuilder($city, $categories->getIds(false))
            ->getQuery()
            ->getSQL();

        $result = $this->getEntityManager()
            ->getConnection()->executeQuery($sql)->fetchAllAssociative();

        $productIds = [];
        foreach ($result as $k => $row) {
            $row = $this->clearKeysRow($row);
            $productIds[] = (int) $row['product_id'];
            unset($result[$k]);
        }

        return $productIds;
    }

    /**
     * For yandex market
     */
    public function getOffersByCity(City $city): array
    {
        $shopsIds = $city->getShops()->getIds();
        $qb = $this->getEntityManager()->createQueryBuilder();
        $qb
            ->addSelect('partial offer.{id, barCode}')
            ->addSelect('partial price.{id, shop, quantity, actual, old}')
            ->addSelect('partial info.{product, name, description}')
            ->addSelect('partial product.{id, category, code}')
            ->from(Price::class, 'price')
            ->join(
                Offer::class,
                'offer',
                Join::WITH,
                $qb->expr()->eq('price.offer', 'offer')
            )
            ->join(
                Product::class,
                'product',
                Join::WITH,
                $qb->expr()->eq('offer.product', 'product')
            )
            ->join(
                ProductInfo::class,
                'info',
                Join::WITH,
                $qb->expr()->eq('info.product', 'product')
            )
            ->where($qb->expr()->in('price.shop', $shopsIds))
            ->andWhere($qb->expr()->gt('price.actual', 200))
        ;

        $sql = $qb->getQuery()->getSQL();
        //dd($sql);
        $result = $this->getEntityManager()
            ->getConnection()->executeQuery($sql)->fetchAllAssociative();
        $result = $this->clearKeys($result);

        $offers = [];
        foreach ($result as $row) {
            // dd($row);
            if (empty($offers[$row['product_id']][$row['id']])) {
                $offers[$row['id']] = [
                    'id' => (int) $row['id'],
                    'product_id' => (int) $row['product_id'],
                    'category_id' => (int) $row['category_id'],
                    'barcode' => $row['bar_code'],
                    'code' => $row['code'],
                    'name' => $row['name'],
                    'description' => $row['description'],
                ];
            }
            $offers[$row['id']]['prices'][] = [
                'actual' => (int) $row['actual'],
                'old' => (int) $row['old'],
                'shop' => (int) $row['shop_id'],
            ];
        }

        return $offers;
    }
}