src/Eccube/Repository/ProductRepository.php line 53

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