src/Eccube/Repository/ProductRepository.php line 75

Open in your IDE?
  1. <?php
  2. /*
  3.  * This file is part of EC-CUBE
  4.  *
  5.  * Copyright(c) EC-CUBE CO.,LTD. All Rights Reserved.
  6.  *
  7.  * http://www.ec-cube.co.jp/
  8.  *
  9.  * For the full copyright and license information, please view the LICENSE
  10.  * file that was distributed with this source code.
  11.  */
  12. namespace Eccube\Repository;
  13. use Doctrine\Common\Collections\ArrayCollection;
  14. use Doctrine\Persistence\ManagerRegistry as RegistryInterface;
  15. use Eccube\Common\EccubeConfig;
  16. use Eccube\Doctrine\Query\Queries;
  17. use Eccube\Entity\Category;
  18. use Eccube\Entity\Master\ProductListMax;
  19. use Eccube\Entity\Master\ProductListOrderBy;
  20. use Eccube\Entity\Master\ProductStatus;
  21. use Eccube\Entity\Product;
  22. use Eccube\Entity\ProductStock;
  23. use Eccube\Entity\Tag;
  24. use Eccube\Util\StringUtil;
  25. /**
  26.  * ProductRepository
  27.  *
  28.  * This class was generated by the Doctrine ORM. Add your own custom
  29.  * repository methods below.
  30.  */
  31. class ProductRepository extends AbstractRepository
  32. {
  33.     /**
  34.      * @var Queries
  35.      */
  36.     protected $queries;
  37.     /**
  38.      * @var EccubeConfig
  39.      */
  40.     protected $eccubeConfig;
  41.     public const COLUMNS = [
  42.         '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',
  43.     ];
  44.     /**
  45.      * ProductRepository constructor.
  46.      *
  47.      * @param RegistryInterface $registry
  48.      * @param Queries $queries
  49.      * @param EccubeConfig $eccubeConfig
  50.      */
  51.     public function __construct(
  52.         RegistryInterface $registry,
  53.         Queries $queries,
  54.         EccubeConfig $eccubeConfig
  55.     ) {
  56.         parent::__construct($registryProduct::class);
  57.         $this->queries $queries;
  58.         $this->eccubeConfig $eccubeConfig;
  59.     }
  60.     /**
  61.      * Find the Product with sorted ClassCategories.
  62.      *
  63.      * @param integer $productId
  64.      *
  65.      * @return Product
  66.      */
  67.     public function findWithSortedClassCategories($productId)
  68.     {
  69.         $qb $this->createQueryBuilder('p');
  70.         $qb->addSelect(['pc''cc1''cc2''pi''pt'])
  71.             ->innerJoin('p.ProductClasses''pc')
  72.             ->leftJoin('pc.ClassCategory1''cc1')
  73.             ->leftJoin('pc.ClassCategory2''cc2')
  74.             ->leftJoin('p.ProductImage''pi')
  75.             ->leftJoin('p.ProductTag''pt')
  76.             ->where('p.id = :id')
  77.             ->andWhere('pc.visible = :visible')
  78.             ->setParameter('id'$productId)
  79.             ->setParameter('visible'true)
  80.             ->orderBy('cc1.sort_no''DESC')
  81.             ->addOrderBy('cc2.sort_no''DESC');
  82.         $product $qb
  83.             ->getQuery()
  84.             ->getSingleResult();
  85.         return $product;
  86.     }
  87.     /**
  88.      * Find the Products with sorted ClassCategories.
  89.      *
  90.      * @param array $ids Product in ids
  91.      * @param string $indexBy The index for the from.
  92.      *
  93.      * @return ArrayCollection|array
  94.      */
  95.     public function findProductsWithSortedClassCategories(array $ids$indexBy null)
  96.     {
  97.         if (count($ids) < 1) {
  98.             return [];
  99.         }
  100.         $qb $this->createQueryBuilder('p'$indexBy);
  101.         $qb->addSelect(['pc''cc1''cc2''pi''pt''tr''ps'])
  102.             ->innerJoin('p.ProductClasses''pc')
  103.             // XXX Joined 'TaxRule' and 'ProductStock' to prevent lazy loading
  104.             ->leftJoin('pc.TaxRule''tr')
  105.             ->innerJoin('pc.ProductStock''ps')
  106.             ->leftJoin('pc.ClassCategory1''cc1')
  107.             ->leftJoin('pc.ClassCategory2''cc2')
  108.             ->leftJoin('p.ProductImage''pi')
  109.             ->leftJoin('p.ProductTag''pt')
  110.             ->where($qb->expr()->in('p.id'$ids))
  111.             ->andWhere('pc.visible = :visible')
  112.             ->setParameter('visible'true)
  113.             ->orderBy('cc1.sort_no''DESC')
  114.             ->addOrderBy('cc2.sort_no''DESC');
  115.         $products $qb
  116.             ->getQuery()
  117.             ->useResultCache(true$this->eccubeConfig['eccube_result_cache_lifetime_short'])
  118.             ->getResult();
  119.         return $products;
  120.     }
  121.     /**
  122.      * get query builder.
  123.      *
  124.      * @param array{
  125.      *         category_id?:Category,
  126.      *         name?:string,
  127.      *         pageno?:string,
  128.      *         disp_number?:ProductListMax,
  129.      *         orderby?:ProductListOrderBy
  130.      *     } $searchData
  131.      *
  132.      * @return \Doctrine\ORM\QueryBuilder
  133.      */
  134.     public function getQueryBuilderBySearchData($searchData)
  135.     {
  136.         $qb $this->createQueryBuilder('p')
  137.             ->andWhere('p.Status = 1');
  138.         // category
  139.         $categoryJoin false;
  140.         if (!empty($searchData['category_id']) && $searchData['category_id']) {
  141.             $Categories $searchData['category_id']->getSelfAndDescendants();
  142.             if ($Categories) {
  143.                 $qb
  144.                     ->innerJoin('p.ProductCategories''pct')
  145.                     ->innerJoin('pct.Category''c')
  146.                     ->andWhere($qb->expr()->in('pct.Category'':Categories'))
  147.                     ->setParameter('Categories'$Categories);
  148.                 $categoryJoin true;
  149.             }
  150.         }
  151.         // name
  152.         if (isset($searchData['name']) && StringUtil::isNotBlank($searchData['name'])) {
  153.             $keywords preg_split('/[\s ]+/u'str_replace(['%''_'], ['\\%''\\_'], $searchData['name']), -1PREG_SPLIT_NO_EMPTY);
  154.             foreach ($keywords as $index => $keyword) {
  155.                 $key sprintf('keyword%s'$index);
  156.                 $qb
  157.                     ->andWhere(sprintf('NORMALIZE(p.name) LIKE NORMALIZE(:%s) OR
  158.                         NORMALIZE(p.search_word) LIKE NORMALIZE(:%s) OR
  159.                         EXISTS (SELECT wpc%d FROM \Eccube\Entity\ProductClass wpc%d WHERE p = wpc%d.Product AND NORMALIZE(wpc%d.code) LIKE NORMALIZE(:%s))',
  160.                         $key$key$index$index$index$index$key))
  161.                     ->setParameter($key'%'.$keyword.'%');
  162.             }
  163.         }
  164.         // Order By
  165.         // 価格低い順
  166.         $config $this->eccubeConfig;
  167.         if (!empty($searchData['orderby']) && $searchData['orderby']->getId() == $config['eccube_product_order_price_lower']) {
  168.             // @see http://doctrine-orm.readthedocs.org/en/latest/reference/dql-doctrine-query-language.html
  169.             $qb->addSelect('MIN(pc.price02) as HIDDEN price02_min');
  170.             $qb->innerJoin('p.ProductClasses''pc');
  171.             $qb->andWhere('pc.visible = true');
  172.             $qb->groupBy('p.id');
  173.             $qb->orderBy('price02_min''ASC');
  174.             $qb->addOrderBy('p.id''DESC');
  175.         // 価格高い順
  176.         } elseif (!empty($searchData['orderby']) && $searchData['orderby']->getId() == $config['eccube_product_order_price_higher']) {
  177.             $qb->addSelect('MAX(pc.price02) as HIDDEN price02_max');
  178.             $qb->innerJoin('p.ProductClasses''pc');
  179.             $qb->andWhere('pc.visible = true');
  180.             $qb->groupBy('p.id');
  181.             $qb->orderBy('price02_max''DESC');
  182.             $qb->addOrderBy('p.id''DESC');
  183.         // 新着順
  184.         } elseif (!empty($searchData['orderby']) && $searchData['orderby']->getId() == $config['eccube_product_order_newer']) {
  185.             // 在庫切れ商品非表示の設定が有効時対応
  186.             // @see https://github.com/EC-CUBE/ec-cube/issues/1998
  187.             if ($this->getEntityManager()->getFilters()->isEnabled('option_nostock_hidden') == true) {
  188.                 $qb->innerJoin('p.ProductClasses''pc');
  189.                 $qb->andWhere('pc.visible = true');
  190.             }
  191.             $qb->orderBy('p.create_date''DESC');
  192.             $qb->addOrderBy('p.id''DESC');
  193.         } else {
  194.             if ($categoryJoin === false) {
  195.                 $qb
  196.                     ->leftJoin('p.ProductCategories''pct')
  197.                     ->leftJoin('pct.Category''c');
  198.             }
  199.             $qb
  200.                 ->addOrderBy('p.id''DESC');
  201.         }
  202.         return $this->queries->customize(QueryKey::PRODUCT_SEARCH$qb$searchData);
  203.     }
  204.     /**
  205.      * get query builder.
  206.      *
  207.      * @param array{
  208.      *         id?:string|int|null,
  209.      *         category_id?:Category,
  210.      *         status?:ProductStatus[],
  211.      *         link_status?:ProductStatus[],
  212.      *         stock_status?:int,
  213.      *         stock?:ProductStock::IN_STOCK|ProductStock::OUT_OF_STOCK,
  214.      *         tag_id?:Tag,
  215.      *         create_datetime_start?:\DateTime,
  216.      *         create_datetime_end?:\DateTime,
  217.      *         create_date_start?:\DateTime,
  218.      *         create_date_end?:\DateTime,
  219.      *         update_datetime_start?:\DateTime,
  220.      *         update_datetime_end?:\DateTime,
  221.      *         update_date_start?:\DateTime,
  222.      *         update_date_end?:\DateTime,
  223.      *         sortkey?:string,
  224.      *         sorttype?:string
  225.      *     } $searchData
  226.      *
  227.      * @return \Doctrine\ORM\QueryBuilder
  228.      */
  229.     public function getQueryBuilderBySearchDataForAdmin($searchData)
  230.     {
  231.         $qb $this->createQueryBuilder('p')
  232.             ->addSelect('pc''pi''tr''ps')
  233.             ->innerJoin('p.ProductClasses''pc')
  234.             ->leftJoin('p.ProductImage''pi')
  235.             ->leftJoin('pc.TaxRule''tr')
  236.             ->leftJoin('pc.ProductStock''ps')
  237.             ->andWhere('pc.visible = :visible')
  238.             ->setParameter('visible'true);
  239.         // id
  240.         if (isset($searchData['id']) && StringUtil::isNotBlank($searchData['id'])) {
  241.             $id preg_match('/^\d{0,10}$/'$searchData['id']) ? $searchData['id'] : null;
  242.             if ($id && $id '2147483647' && $this->isPostgreSQL()) {
  243.                 $id null;
  244.             }
  245.             $qb
  246.                 ->andWhere('p.id = :id OR p.name LIKE :likeid OR pc.code LIKE :likeid')
  247.                 ->setParameter('id'$id)
  248.                 ->setParameter('likeid''%'.str_replace(['%''_'], ['\\%''\\_'], $searchData['id']).'%');
  249.         }
  250.         // code
  251.         /*
  252.         if (!empty($searchData['code']) && $searchData['code']) {
  253.             $qb
  254.                 ->innerJoin('p.ProductClasses', 'pc')
  255.                 ->andWhere('pc.code LIKE :code')
  256.                 ->setParameter('code', '%' . $searchData['code'] . '%');
  257.         }
  258.         // name
  259.         if (!empty($searchData['name']) && $searchData['name']) {
  260.             $keywords = preg_split('/[\s ]+/u', $searchData['name'], -1, PREG_SPLIT_NO_EMPTY);
  261.             foreach ($keywords as $keyword) {
  262.                 $qb
  263.                     ->andWhere('p.name LIKE :name')
  264.                     ->setParameter('name', '%' . $keyword . '%');
  265.             }
  266.         }
  267.        */
  268.         // category
  269.         if (!empty($searchData['category_id']) && $searchData['category_id']) {
  270.             $Categories $searchData['category_id']->getSelfAndDescendants();
  271.             if ($Categories) {
  272.                 $qb
  273.                     ->innerJoin('p.ProductCategories''pct')
  274.                     ->innerJoin('pct.Category''c')
  275.                     ->andWhere($qb->expr()->in('pct.Category'':Categories'))
  276.                     ->setParameter('Categories'$Categories);
  277.             }
  278.         }
  279.         // status
  280.         if (!empty($searchData['status']) && $searchData['status']) {
  281.             $qb
  282.                 ->andWhere($qb->expr()->in('p.Status'':Status'))
  283.                 ->setParameter('Status'$searchData['status']);
  284.         }
  285.         // link_status
  286.         if (isset($searchData['link_status']) && !empty($searchData['link_status'])) {
  287.             $qb
  288.                 ->andWhere($qb->expr()->in('p.Status'':Status'))
  289.                 ->setParameter('Status'$searchData['link_status']);
  290.         }
  291.         // stock status
  292.         if (isset($searchData['stock_status'])) {
  293.             $qb
  294.                 ->andWhere('pc.stock_unlimited = :StockUnlimited AND pc.stock = 0')
  295.                 ->setParameter('StockUnlimited'$searchData['stock_status']);
  296.         }
  297.         // stock status
  298.         if (isset($searchData['stock']) && !empty($searchData['stock'])) {
  299.             switch ($searchData['stock']) {
  300.                 case [ProductStock::IN_STOCK]:
  301.                     $qb->andWhere('pc.stock_unlimited = true OR pc.stock > 0');
  302.                     break;
  303.                 case [ProductStock::OUT_OF_STOCK]:
  304.                     $qb->andWhere('pc.stock_unlimited = false AND pc.stock <= 0');
  305.                     break;
  306.                 default:
  307.                     // 共に選択された場合は全権該当するので検索条件に含めない
  308.             }
  309.         }
  310.         // tag
  311.         if (!empty($searchData['tag_id']) && $searchData['tag_id']) {
  312.             $qb
  313.                 ->innerJoin('p.ProductTag''pt')
  314.                 ->andWhere('pt.Tag = :tag_id')
  315.                 ->setParameter('tag_id'$searchData['tag_id']);
  316.         }
  317.         // crate_date
  318.         if (!empty($searchData['create_datetime_start']) && $searchData['create_datetime_start']) {
  319.             $date $searchData['create_datetime_start'];
  320.             $qb
  321.                 ->andWhere('p.create_date >= :create_date_start')
  322.                 ->setParameter('create_date_start'$date);
  323.         } elseif (!empty($searchData['create_date_start']) && $searchData['create_date_start']) {
  324.             $date $searchData['create_date_start'];
  325.             $qb
  326.                 ->andWhere('p.create_date >= :create_date_start')
  327.                 ->setParameter('create_date_start'$date);
  328.         }
  329.         if (!empty($searchData['create_datetime_end']) && $searchData['create_datetime_end']) {
  330.             $date $searchData['create_datetime_end'];
  331.             $qb
  332.                 ->andWhere('p.create_date < :create_date_end')
  333.                 ->setParameter('create_date_end'$date);
  334.         } elseif (!empty($searchData['create_date_end']) && $searchData['create_date_end']) {
  335.             $date = clone $searchData['create_date_end'];
  336.             $date $date
  337.                 ->modify('+1 days');
  338.             $qb
  339.                 ->andWhere('p.create_date < :create_date_end')
  340.                 ->setParameter('create_date_end'$date);
  341.         }
  342.         // update_date
  343.         if (!empty($searchData['update_datetime_start']) && $searchData['update_datetime_start']) {
  344.             $date $searchData['update_datetime_start'];
  345.             $qb
  346.                 ->andWhere('p.update_date >= :update_date_start')
  347.                 ->setParameter('update_date_start'$date);
  348.         } elseif (!empty($searchData['update_date_start']) && $searchData['update_date_start']) {
  349.             $date $searchData['update_date_start'];
  350.             $qb
  351.                 ->andWhere('p.update_date >= :update_date_start')
  352.                 ->setParameter('update_date_start'$date);
  353.         }
  354.         if (!empty($searchData['update_datetime_end']) && $searchData['update_datetime_end']) {
  355.             $date $searchData['update_datetime_end'];
  356.             $qb
  357.                 ->andWhere('p.update_date < :update_date_end')
  358.                 ->setParameter('update_date_end'$date);
  359.         } elseif (!empty($searchData['update_date_end']) && $searchData['update_date_end']) {
  360.             $date = clone $searchData['update_date_end'];
  361.             $date $date
  362.                 ->modify('+1 days');
  363.             $qb
  364.                 ->andWhere('p.update_date < :update_date_end')
  365.                 ->setParameter('update_date_end'$date);
  366.         }
  367.         // Order By
  368.         if (isset($searchData['sortkey']) && !empty($searchData['sortkey'])) {
  369.             $sortOrder = (isset($searchData['sorttype']) && $searchData['sorttype'] == 'a') ? 'ASC' 'DESC';
  370.             $qb->orderBy(self::COLUMNS[$searchData['sortkey']], $sortOrder);
  371.             $qb->addOrderBy('p.update_date''DESC');
  372.             $qb->addOrderBy('p.id''DESC');
  373.         } else {
  374.             $qb->orderBy('p.update_date''DESC');
  375.             $qb->addOrderBy('p.id''DESC');
  376.         }
  377.         return $this->queries->customize(QueryKey::PRODUCT_SEARCH_ADMIN$qb$searchData);
  378.     }
  379. }