src/Eccube/Repository/ProductRepository.php line 54

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