Symfony Profiler

<?php
/*
* This file is part of EC-CUBE
*
* Copyright(c) EC-CUBE CO.,LTD. All Rights Reserved.
*
* http://www.ec-cube.co.jp/
*
* For the full copyright and license information, please view the LICENSE
* file that was distributed with this source code.
*/
namespace Customize\Repository;
use Customize\Controller\Trait\ConstanceTrait;
use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\Persistence\ManagerRegistry as RegistryInterface;
use Eccube\Common\EccubeConfig;
use Eccube\Doctrine\Query\Queries;
use Eccube\Entity\Category;
use Eccube\Entity\Product;
use Eccube\Entity\ProductStock;
use Eccube\Util\StringUtil;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Customize\Common\Constants;
use Eccube\Repository\QueryKey;
use Carbon\Carbon;
use Doctrine\ORM\EntityManagerInterface;
use Eccube\Entity\Order;
use Eccube\Entity\OrderItem;
use Customize\Entity\OrderDetailAdditionalInfo;
/**
* ProductRepository
*
* This class was generated by the Doctrine ORM. Add your own custom
* repository methods below.
*/
class ProductRepository extends ServiceEntityRepository
{
use ConstanceTrait;
/**
* @var Queries
*/
protected $queries;
/**
* @var EccubeConfig
*/
protected $eccubeConfig;
public const COLUMNS = [
'product_id' => 'p.id', 'name' => 'p.name', 'product_code' => 'pc.code', 'stock' => 'pc.stock', 'status' => 'p.Status', 'create_date' => 'p.create_date', 'update_date' => 'p.update_date',
];
/**
* ProductRepository constructor.
*
* @param RegistryInterface $registry
* @param Queries $queries
* @param EccubeConfig $eccubeConfig
*/
public function __construct(
RegistryInterface $registry,
Queries $queries,
EccubeConfig $eccubeConfig,
private EntityManagerInterface $entityManager,
) {
parent::__construct($registry, Product::class);
$this->queries = $queries;
$this->eccubeConfig = $eccubeConfig;
$this->setUpValue();
}
/**
* Find the Product with sorted ClassCategories.
*
* @param integer $productId
*
* @return Product
*/
public function findWithSortedClassCategories($productId)
{
$qb = $this->createQueryBuilder('p');
$qb->addSelect(['pc', 'cc1', 'cc2', 'pi', 'pt'])
->innerJoin('p.ProductClasses', 'pc')
->leftJoin('pc.ClassCategory1', 'cc1')
->leftJoin('pc.ClassCategory2', 'cc2')
->leftJoin('p.ProductImage', 'pi')
->leftJoin('p.ProductTag', 'pt')
->where('p.id = :id')
->andWhere('pc.visible = :visible')
->setParameter('id', $productId)
->setParameter('visible', true)
->orderBy('cc1.sort_no', 'DESC')
->addOrderBy('cc2.sort_no', 'DESC');
$product = $qb
->getQuery()
->getSingleResult();
return $product;
}
/**
* Find the Products with sorted ClassCategories.
*
* @param array $ids Product in ids
* @param string $indexBy The index for the from.
*
* @return ArrayCollection|array
*/
public function findProductsWithSortedClassCategories(array $ids, $indexBy = null)
{
if (count($ids) < 1) {
return [];
}
$qb = $this->createQueryBuilder('p', $indexBy);
$qb->addSelect(['pc', 'cc1', 'cc2', 'pi', 'pt', 'tr', 'ps'])
->innerJoin('p.ProductClasses', 'pc')
// XXX Joined 'TaxRule' and 'ProductStock' to prevent lazy loading
->leftJoin('pc.TaxRule', 'tr')
->innerJoin('pc.ProductStock', 'ps')
->leftJoin('pc.ClassCategory1', 'cc1')
->leftJoin('pc.ClassCategory2', 'cc2')
->leftJoin('p.ProductImage', 'pi')
->leftJoin('p.ProductTag', 'pt')
->where($qb->expr()->in('p.id', $ids))
->andWhere('pc.visible = :visible')
->setParameter('visible', true)
->orderBy('cc1.sort_no', 'DESC')
->addOrderBy('cc2.sort_no', 'DESC');
$products = $qb
->getQuery()
->useResultCache(true, $this->eccubeConfig['eccube_result_cache_lifetime_short'])
->getResult();
return $products;
}
/**
* get query builder.
*
* @param array{
* category_id?:Category,
* name?:string,
* pageno?:string,
* disp_number?:ProductListMax,
* orderby?:ProductListOrderBy
* } $searchData
*
* @return \Doctrine\ORM\QueryBuilder
*/
public function getQueryBuilderBySearchData($searchData, $entityManager, $categories, $date = null)
{
$qb = $this->createQueryBuilder('p')
->andWhere('p.Status = 1');
// Remove id of product china and english
$connection = $entityManager->getConnection();
$productForeignSQL = "
SELECT plg_expand_product_columns_value.product_id
FROM plg_expand_product_columns_value
WHERE column_id=:column_id and value in (:china,:english)";
$productForeignIds = $connection->prepare($productForeignSQL);
$productForeignIdsResultSet = $productForeignIds->executeQuery(
[
'column_id' => Constants::LOCALE_RECORD_ID,
'china' => Constants::LOCALE_CHINA,
'english' => Constants::LOCALE_ENGLISH
]
);
$producForeigntNeedIgnore = $productForeignIdsResultSet->fetchAllAssociative();
$qb->andWhere($qb->expr()->notIn('p.id', ':product_foreign'))
->setParameter('product_foreign', $producForeigntNeedIgnore);
$categoryJoin = false;
if (!empty($searchData['category_id']) && $searchData['category_id']) {
$Categories = $searchData['category_id']->getSelfAndDescendants();
if ($Categories) {
$qb
->innerJoin('p.ProductCategories', 'pct')
->innerJoin('pct.Category', 'c')
->andWhere($qb->expr()->in('pct.Category', ':Categories'))
->setParameter('Categories', $Categories);
$categoryJoin = true;
}
}
// categories
if (!empty($categories) && $categories) {
$count = 0;
foreach ($categories as $category) {
$str = '';
$arr = [];
foreach ($category as $cat) {
if (is_numeric($cat)) {
$arr[] = $cat;
}
}
// Group category to select "and"
if (!empty($arr)) {
$count++;
$qb->innerJoin('p.ProductCategories', 'pct' . $count);
$str .= 'pct' . $count . '.Category IN (' . implode(',', $arr) . ')';
$qb->andWhere($str);
}
}
}
// name
if (isset($searchData['name']) && StringUtil::isNotBlank($searchData['name'])) {
$keywords = preg_split('/[\s　]+/u', str_replace(['%', '_'], ['\\%', '\\_'], $searchData['name']), -1, PREG_SPLIT_NO_EMPTY);
foreach ($keywords as $index => $keyword) {
$key = sprintf('keyword%s', $index);
$qb
->andWhere(sprintf(
'NORMALIZE(p.name) LIKE NORMALIZE(:%s) OR
NORMALIZE(p.search_word) LIKE NORMALIZE(:%s) OR
EXISTS (SELECT wpc%d FROM \Eccube\Entity\ProductClass wpc%d WHERE p = wpc%d.Product AND NORMALIZE(wpc%d.code) LIKE NORMALIZE(:%s))',
$key,
$key,
$index,
$index,
$index,
$index,
$key
))
->setParameter($key, '%' . $keyword . '%');
}
}
// Order By
// 価格低い順
$config = $this->eccubeConfig;
if (!empty($searchData['orderby']) && $searchData['orderby']->getId() == $config['eccube_product_order_price_lower']) {
// @see http://doctrine-orm.readthedocs.org/en/latest/reference/dql-doctrine-query-language.html
$qb->addSelect('MIN(pc.price02) as HIDDEN price02_min');
$qb->innerJoin('p.ProductClasses', 'pc');
$qb->andWhere('pc.visible = true');
$qb->groupBy('p.id');
$qb->orderBy('price02_min', 'ASC');
$qb->addOrderBy('p.id', 'DESC');
// 価格高い順
} elseif (!empty($searchData['orderby']) && $searchData['orderby']->getId() == $config['eccube_product_order_price_higher']) {
$qb->addSelect('MAX(pc.price02) as HIDDEN price02_max');
$qb->innerJoin('p.ProductClasses', 'pc');
$qb->andWhere('pc.visible = true');
$qb->groupBy('p.id');
$qb->orderBy('price02_max', 'DESC');
$qb->addOrderBy('p.id', 'DESC');
// 新着順
} elseif (!empty($searchData['orderby']) && $searchData['orderby']->getId() == $config['eccube_product_order_newer']) {
// 在庫切れ商品非表示の設定が有効時対応
// @see https://github.com/EC-CUBE/ec-cube/issues/1998
if ($this->getEntityManager()->getFilters()->isEnabled('option_nostock_hidden') == true) {
$qb->innerJoin('p.ProductClasses', 'pc');
$qb->andWhere('pc.visible = true');
}
$qb->orderBy('p.create_date', 'DESC');
$qb->addOrderBy('p.id', 'DESC');
} else {
if ($categoryJoin === false) {
$qb
->leftJoin('p.ProductCategories', 'pct')
->leftJoin('pct.Category', 'c');
}
$qb
->addOrderBy('p.id', 'DESC');
}
$dateObject = \DateTime::createFromFormat('Y-m-d', $date);
$isValidDate = $dateObject && $dateObject->format('Y-m-d') === $date;
// Search use day
if ($isValidDate) {
if ($_SERVER['SERVER_NAME'] == $this->eccubeConfig['salon_domain']) {
// Search use days for salon
return $this->searchDaysForSalon($date, $qb);
}
$beforeUseDay = $this->DEFAULT_BEFORE_USE_DAYS;
$afterUseDay = $this->DEFAULT_AFTER_USE_DAYS;
$conn = $entityManager->getConnection();
// Join 3 tables to get id products need ignore for filter feature
$sql = "
SELECT dtb_order_item.product_id
FROM plg_order_detail_additional_info
JOIN dtb_order_item ON dtb_order_item.id = plg_order_detail_additional_info.order_detail_id
JOIN dtb_order ON dtb_order.id = dtb_order_item.order_id
LEFT JOIN plg_product_use_days ON plg_product_use_days.product_id = dtb_order_item.product_id
WHERE
wear_date BETWEEN IF(
plg_order_detail_additional_info.before_use_days,
DATE_SUB(
:use_date,
INTERVAL IF(wear_date >= :use_date, plg_order_detail_additional_info.before_use_days, plg_order_detail_additional_info.after_use_days) DAY
),
DATE_SUB(:use_date, INTERVAL :before_use_day DAY)
)
AND
IF(
plg_order_detail_additional_info.after_use_days,
DATE_ADD(
:use_date,
INTERVAL IF(wear_date > :use_date, plg_order_detail_additional_info.before_use_days, plg_order_detail_additional_info.after_use_days) DAY
),
DATE_ADD(:use_date, INTERVAL :after_use_day DAY)
)";
$stmt = $conn->prepare($sql);
$resultSet = $stmt->executeQuery(
[
'use_date' => $date,
'before_use_day' => $beforeUseDay,
'after_use_day' => $afterUseDay,
]
);
$productNeedIgnore = $resultSet->fetchAllAssociative();
if ($productNeedIgnore) {
$qb->andWhere(
$qb->expr()->notIn('p.id', ':products_need_ignore')
)
->setParameter('products_need_ignore', $productNeedIgnore);
}
}
return $this->queries->customize(QueryKey::PRODUCT_SEARCH, $qb, $searchData);
}
/**
* Filter ignore product has order in salon
*
* @param $date
* @param $qb
* @return void
*/
private function searchDaysForSalon($date, $qb)
{
$dateFormat = Carbon::parse($date);
$fittingDateAddAfterUseDays = $dateFormat->copy()->addDays(Constants::FITTING_AFTER_USE_DAYS)->toDateString();
$fittingDateAddBeforeUseDays = $dateFormat->copy()->subDays(Constants::FITTING_BEFORE_USE_DAYS)->toDateString();
$normalDateAddAfterUseDays = $dateFormat->copy()->addDays($this->DEFAULT_AFTER_USE_DAYS)->toDateString();
$normalDateAddBeforeUseDays = $dateFormat->copy()->subDays($this->DEFAULT_BEFORE_USE_DAYS)->toDateString();
$usedProducts = $this->entityManager
->createQueryBuilder()
->select('odi')
->from(OrderDetailAdditionalInfo::class, 'odai')
->innerJoin(Order::class, 'od', 'WITH', 'odai.order_id = od.id')
->innerJoin(OrderItem::class, 'odi', 'WITH', 'odai.order_detail_id = odi.id')
->andWhere('(
(odai.wear_date BETWEEN :wear_date_fitting_before AND :wear_date_fitting_after AND odai.order_type = :fitting_type)
OR
(odai.wear_date BETWEEN :wear_date_normal_before AND :wear_date_normal_after AND odai.order_type <> :fitting_type)
)')
->setParameter('wear_date_fitting_after', $fittingDateAddAfterUseDays)
->setParameter('wear_date_fitting_before', $fittingDateAddBeforeUseDays)
->setParameter('wear_date_normal_after', $normalDateAddAfterUseDays)
->setParameter('wear_date_normal_before', $normalDateAddBeforeUseDays)
->setParameter('fitting_type', 'fitting')
->getQuery()
->getResult();
$productNeedIgnoreInSalon = [];
foreach ($usedProducts as $usedProduct) {
array_push($productNeedIgnoreInSalon, ['product_id' => $usedProduct->getId()]);
}
if ($productNeedIgnoreInSalon) {
$qb->andWhere(
$qb->expr()->notIn('p.id', ':products_need_ignore')
)
->setParameter('products_need_ignore', $productNeedIgnoreInSalon);
}
return $qb;
}
/**
* get query builder.
*
* @param array{
* id?:string|int|null,
* category_id?:Category,
* status?:ProductStatus[],
* link_status?:ProductStatus[],
* stock_status?:int,
* stock?:ProductStock::IN_STOCK|ProductStock::OUT_OF_STOCK,
* tag_id?:Tag,
* create_datetime_start?:\DateTime,
* create_datetime_end?:\DateTime,
* create_date_start?:\DateTime,
* create_date_end?:\DateTime,
* update_datetime_start?:\DateTime,
* update_datetime_end?:\DateTime,
* update_date_start?:\DateTime,
* update_date_end?:\DateTime,
* sortkey?:string,
* sorttype?:string
* } $searchData
*
* @return \Doctrine\ORM\QueryBuilder
*/
public function getQueryBuilderBySearchDataForAdmin($searchData)
{
$qb = $this->createQueryBuilder('p')
->addSelect('pc', 'pi', 'tr', 'ps')
->innerJoin('p.ProductClasses', 'pc')
->leftJoin('p.ProductImage', 'pi')
->leftJoin('pc.TaxRule', 'tr')
->leftJoin('pc.ProductStock', 'ps')
->andWhere('pc.visible = :visible')
->setParameter('visible', true);
// id
if (isset($searchData['id']) && StringUtil::isNotBlank($searchData['id'])) {
$id = preg_match('/^\d{0,10}$/', $searchData['id']) ? $searchData['id'] : null;
if ($id && $id > '2147483647' && $this->isPostgreSQL()) {
$id = null;
}
$qb
->andWhere('p.id = :id OR p.name LIKE :likeid OR pc.code LIKE :likeid')
->setParameter('id', $id)
->setParameter('likeid', '%' . str_replace(['%', '_'], ['\\%', '\\_'], $searchData['id']) . '%');
}
// category
if (!empty($searchData['category_id']) && $searchData['category_id']) {
$Categories = $searchData['category_id']->getSelfAndDescendants();
if ($Categories) {
$qb
->innerJoin('p.ProductCategories', 'pct')
->innerJoin('pct.Category', 'c')
->andWhere($qb->expr()->in('pct.Category', ':Categories'))
->setParameter('Categories', $Categories);
}
}
// status
if (!empty($searchData['status']) && $searchData['status']) {
$qb
->andWhere($qb->expr()->in('p.Status', ':Status'))
->setParameter('Status', $searchData['status']);
}
// link_status
if (isset($searchData['link_status']) && !empty($searchData['link_status'])) {
$qb
->andWhere($qb->expr()->in('p.Status', ':Status'))
->setParameter('Status', $searchData['link_status']);
}
// stock status
if (isset($searchData['stock_status'])) {
$qb
->andWhere('pc.stock_unlimited = :StockUnlimited AND pc.stock = 0')
->setParameter('StockUnlimited', $searchData['stock_status']);
}
// stock status
if (isset($searchData['stock']) && !empty($searchData['stock'])) {
switch ($searchData['stock']) {
case [ProductStock::IN_STOCK]:
$qb->andWhere('pc.stock_unlimited = true OR pc.stock > 0');
break;
case [ProductStock::OUT_OF_STOCK]:
$qb->andWhere('pc.stock_unlimited = false AND pc.stock <= 0');
break;
default:
// 共に選択された場合は全権該当するので検索条件に含めない
}
}
// tag
if (!empty($searchData['tag_id']) && $searchData['tag_id']) {
$qb
->innerJoin('p.ProductTag', 'pt')
->andWhere('pt.Tag = :tag_id')
->setParameter('tag_id', $searchData['tag_id']);
}
// crate_date
if (!empty($searchData['create_datetime_start']) && $searchData['create_datetime_start']) {
$date = $searchData['create_datetime_start'];
$qb
->andWhere('p.create_date >= :create_date_start')
->setParameter('create_date_start', $date);
} elseif (!empty($searchData['create_date_start']) && $searchData['create_date_start']) {
$date = $searchData['create_date_start'];
$qb
->andWhere('p.create_date >= :create_date_start')
->setParameter('create_date_start', $date);
}
if (!empty($searchData['create_datetime_end']) && $searchData['create_datetime_end']) {
$date = $searchData['create_datetime_end'];
$qb
->andWhere('p.create_date < :create_date_end')
->setParameter('create_date_end', $date);
} elseif (!empty($searchData['create_date_end']) && $searchData['create_date_end']) {
$date = clone $searchData['create_date_end'];
$date = $date
->modify('+1 days');
$qb
->andWhere('p.create_date < :create_date_end')
->setParameter('create_date_end', $date);
}
// update_date
if (!empty($searchData['update_datetime_start']) && $searchData['update_datetime_start']) {
$date = $searchData['update_datetime_start'];
$qb
->andWhere('p.update_date >= :update_date_start')
->setParameter('update_date_start', $date);
} elseif (!empty($searchData['update_date_start']) && $searchData['update_date_start']) {
$date = $searchData['update_date_start'];
$qb
->andWhere('p.update_date >= :update_date_start')
->setParameter('update_date_start', $date);
}
if (!empty($searchData['update_datetime_end']) && $searchData['update_datetime_end']) {
$date = $searchData['update_datetime_end'];
$qb
->andWhere('p.update_date < :update_date_end')
->setParameter('update_date_end', $date);
} elseif (!empty($searchData['update_date_end']) && $searchData['update_date_end']) {
$date = clone $searchData['update_date_end'];
$date = $date
->modify('+1 days');
$qb
->andWhere('p.update_date < :update_date_end')
->setParameter('update_date_end', $date);
}
// Order By
if (isset($searchData['sortkey']) && !empty($searchData['sortkey'])) {
$sortOrder = (isset($searchData['sorttype']) && $searchData['sorttype'] == 'a') ? 'ASC' : 'DESC';
$qb->orderBy(self::COLUMNS[$searchData['sortkey']], $sortOrder);
$qb->addOrderBy('p.update_date', 'DESC');
$qb->addOrderBy('p.id', 'DESC');
} else {
$qb->orderBy('p.update_date', 'DESC');
$qb->addOrderBy('p.id', 'DESC');
}
return $this->queries->customize(QueryKey::PRODUCT_SEARCH_ADMIN, $qb, $searchData);
}
public function getProductByIds(array $ids)
{
$products = $this->createQueryBuilder('p')
->addSelect(['p', 'pi', 'pc'])
->andWhere('p.id in (:ids)')
->leftJoin('p.ProductImage', 'pi')
->innerJoin('p.ProductClasses', 'pc')
->setParameter('ids', $ids)
->getQuery()
->execute();
// Create array null with count($ids) { [0 => null, 1=> null] }
$sortedResults = array_fill(0, count($ids), null);
// Rearrange the display array in the correctpassed in ids
foreach ($products as $result) {
// Find index id in $ids
$index = array_search($result->getId(), $ids);
if ($index !== false) {
$sortedResults[$index] = $result;
}
}
return $sortedResults;
}
}

app/Customize/Repository/ProductRepository.php line 156